Articles
--SELECT * FROM UPDATE IN_Part SET Detail = '' WHERE Detail IS NULL
--SELECT * FROM UPDATE IN_Part SET Detail = '' WHERE Detail IS NULL
--PartIds that had NULL in their Detail column
--123
--1225
--1226
--1228
--1229
--1230
--1233
--1235
--1237
--1...
Auto-Aging
In SQL Manager on SQL server, under SQL Agent, under jobs, Auto-Aging. Add, put in Company name and db Go to Autoaging (doubleclick and look at steps) Copy Command from another step: Exec All_Customer_Aging Then need to move a...
AWS
EXEC msdb.dbo.rds_backup_database
@source_db_name = 'rds_backup_db',
@s3_arn_to_backup_to = 'arn:aws:s3:::sedonaasprdsmanualbackups/rds-backup_db.bak',
@overwrite_S3_backup_file = 1;
...
Drop and Recreate Users Instance to autofix Sedona Reports
DECLARE @DatabaseName varchar(100), @SqlStatement varchar(1024) SET ROWCOUNT 1 SELECT @DatabaseName = NAME FROM SYSDATABASES ORDER BY NAME WHILE @@ROWCOUNT > 0 BEGIN SET @SqlStatement = 'sp_change_users_login ''Auto_Fix'', ''SedonaUse...
Embed SQL Connection in Excel
https://stackoverflow.com/questions/49802471/embed-sql-server-credentials-in-excel-2016-o365-to-refresh-data-on-demand You need to use the legacy "Microsoft Query" (rather than Power Query) to create the data connection. MS have explicitly remo...
Grant SedonaReports CalculateDueDate
GRANT EXECUTE ON dbo.CalculateDueDate TO [SedonaReports]
SQL Index Job Script - SQL Fragmentation
Script/Job to check index fragmentation: --Fragmentation Test (only for testing, do not add as part of the job)
--Check Recent Expensive Queries/Active Expensive Queries
--Right click show execution plan
SELECT OBJECT_NAME(ind.OBJEC...
Log File Cleanup
-- Check log and database sizes might be culprit. -- Run log file cleanup script, ensure it is in the correct database and you are shrinking the correct log. -- You may need to run multiple times to get size down. If something doesn't run in SQ...
Profiler
On SQL server Deselect all but Stored Procedures and TSQL Select above 2, and Errors Run against the process id for the app with errors. ...
Querying
Check you're on the right node, replicated node will be read only and not work.
Recreate Administrator SO user
SET IDENTITY_INSERT SedonaMaster.dbo.SY_User ON
INSERT INTO SedonaMaster.dbo.SY_User
([User_Id],User_Code,User_Description,Password,First_Name,Last_Name,Middle_Init,Inactive,EMail_Address,CreateUsers,Password_Changed,Last_Login,Last_P...
Script to fix transaction errors with invoices in sql2016?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Function [dbo].[CalculateDueDate] (@InvoiceDate Datetime,
@AgingDate DateTime, --or the due date in AP Invoice
@DateType Int,
@Da...
Sedona-X Add Columns
Alter Table SY_FSUWEB_Technician
Add EMail nvarchar(256),
Role_Group int; ...
Sedona-X Tech Import
Update prd
S et email = x.EMail, role_group=x.Role_Group
--select *
from SY_FSUWeb_Technician prd
I nner Join dbo.[7856_Technician] x on x.FSUWeb_Technician_Id = prd.FSUWeb_Technician_Id ...
SedonaDocs SQL timeout
Opening ticket fails w/ SQL error Check SQL Activity monitor for processes, all users, sort by header. If nothing big touching the database, check log file size for docs Check that server can be pinged from another machine on custome...
SQL Server Database Stuck in Restoring State
https://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state https://www.mssqltips.com/sqlservertip/5460/sql-server-database-stuck-in-restoring-state/ SQL Server Database Stuck in Restoring State By: Daniel C...
SQL Troubleshooting
For issues without obvious cause/solution: check activity monitor for high usage when replicating problem, check log files and indexing, check profiler (can access from management studio, look up process id for problem application, use that as filt...
SS_Setup_Modules (Turning on features in database)
Check company table in Sedona Master to find DBs of customer SS_Setup_Modules Add appropriate letter for the service/feature needed (yes that's it!) Barcode scanning needs our OPOS driver and may need barcode setup in main app ...
Syncing DB
Make sure in Full recovery mode. Properties>advanced>recovery model Make backup (NOT copy only) of DB into SQLClusterTemp with exact name of DB.bak High Availability Groups -> Add -> they should now meet prerequisites. ...