Articles
SedonaOffice - SQL Script - Transaction Logs Full - Shrink Fix
BACKUP LOG [CHANGE_ME]
TO DISK = 'nul:' WITH STATS = 10
GO
dbcc shrinkfile ('Security_log', 0, TRUNCATEONLY)
GO
Random Scripts
//Update gl_accounting_period Set Status = 'C' where start_date = '2020-08-1
-- Select to reopen_close fiscal year
SELECT * FROM GL_Accounting_Period ORDER BY Accounting_Period_ID DESC
-- to see which Accounting_Period_Id you wanna target...
Find Database Table information
Description of Issue:
How can we find the information on the tables and columns in the database?
Resolution:
We do not have a full Database Reference Manual, but the table information can be seen if you have access t...
Orphaned SQL Users Script
--Use this script to fix permissions for SQL Server SedonaUser and Sedona Reports
DECLARE @DatabaseName varchar(100), @SqlStatement varchar(1024)
SET ROWCOUNT 1
SELECT @DatabaseName = NAME FROM SYSDATABASES ORDER BY NAME
WHILE @@ROWC...
How to back up a single table in SQL
Anytime you are doing any changes in a production SQL database you should back up the information being changed. If you only need to change a couple of tables, you can use the script below to backup each table individually.
It is important to ...
Remove Whitespaces
Sometimes when importing data, whitespaces sneak in at the front or back of a piece of data. This can mess up searches on the front end so you can use the script below to remove said whitespaces. You will need to know the name of the table as well a...
How to Find Tables with field names in a SQL database
If you are unable to determine what table you need to look for a certain item, but you know the name of the field, you can use the script below to try and find the Table.
SELECT distinct c.name AS 'ColumnName'
,t.name AS 'T...
Sedona Office Null Values
SedonaOffice in general does not allow any of its fields to have the value of null. However, not all fields are used by all customers. Due to this there are three “Null” Value defaults based on the data type of the field. If you receive an error of ...
How to Find the Size of a SedonaOffice Database
Issue: Is there an easy wat to determine the size of the database file on the SQL server? Resolution: The size of a SQL database can be seen by using a stored procedure. Open the SQL Query Window inside of the SedonaOffice application. ...
How to Run SQL Profiler Trace to Capture Information
Issue: Customer is receiving errors in their SedonaOffice company. Is there a way to get mor information on why the errors are occurring? Resolution: SQL server Profiler can be used to capture information from processes accessing the SQL ser...
SQL Profiler Trace to Capture Deadlock Information
Issue: Customer is receiving Deadlock errors in their SedonaOffice company. Is there a way to get mor information on why the lock is occurring? Resolution: SQL server Profiler can be used to identify the cause of a deadlock. A deadlock occur...
How to login to Cloud SQL Server
The following steps will get you logged into a customer’s Microsoft SQL Server Management Studio. Step 1.) Go to search and type SSMS on SQL server. This will activate the Management Studio. Step 2.) The login page will appear. ** Server n...
Create Stored Procedure - NumbersOnly
Stored procedure used to filter out all data that does not include numbers. Useful for importing data into integer fields. IF OBJECT_ID(N'NumbersOnly', N'FN') IS NOT NULL DROP FUNCTION [dbo].[NumbersOnly] GO SET ANSI_NULLS ON GO ...
How to see the SQL scripts used in a SQL Job
Issue:
We would like to know the scripts that are used when a SQL Job runs. Where can we see this information?
Resolution:
You can view the contents of the SQL Job under SQL Server Agent.
1. Open SQL Server Management S...
SQL DateAdd Function
This article is to explain how to use the dateadd function in SQL. The Function requires three arguments in order separated by commas: Interval – This will always be either a DAY, MONTH, or YEAR Increment – This is the positive or negative inte...
Check if Sandbox Data is Current
Issue:
A customer has a problem that needs testing in their Sandbox company.
How can we tell if the Sandbox company has current data?
Resolution:
You can look at the transactions in the GL_Register table for recent tran...
Script to List Functions in a Database (Internal)
Issue: Is there a way to get a listing of the functions used in a database? Resolution: The Function in a database can be listed using the SQL query below. SELECT O.name, M.definition, O.type_desc, O.type FROM sys.sql_modules M ...
How to Run SQL Profiler Trace to Capture Information (Internal)
Issue:
The customer is receiving errors in their SedonaOffice company. Is there a way to get more information on why the errors are occurring?
Resolution:
SQL Server Profiler can be used to capture information from processes acce...
Inventory Script to Select Parts With or Without Journal Entries
146031 Inventory Script to Select Parts With or Without Journal Entries This script is to create a list of parts that either does or does not have journal entries listed for that part. The Parts Query Builder is not able to get this list for th...
Extreme Lag After Updating to Sedona Office version 6.2.0.20/21
148384 Extreme Lag After Updating to Sedona Office version 6.2.0.21 After updating to Sedona Office version 6.2.0.20 or .21, some users may experience significant lag in the program. This is especially noticeable in the Accounts Payable (AP) modu...
Duplicate CC Deposit Removal
153392 /* Script: Delete AR_Deposit with Safety Checks and Reversal Logic Purpose: Safely delete deposit records if allowed, or create reversal entries if period is closed Author: Emeka D */ ---This is will hep you identify the ...
NoSQLButton (Disabled SQL Query Window) - Trigger Find Script
00153453 NoSQLButton (Disabled SQL Query Window) - Trigger Find Script Older customers of SedonaOffice may have had their SQL Query Window Button disabled from within the application for all users. Despite attempts for an admin to enable the SQL...