Category: SQL Server
Force TSQL dateformat
If you get the following error on scheduled stored procedures, force the dateformat (The Microsoft SQL Server scheduler will probably be running the default mdy), you can use Set Language but I tend to change the dateformat. [SQLSTATE 01000] (Message 0) The conversion of a nvarchar data type to a datetime data type resulted in…
Use a case to make sure provided data is a date
I find this useful for using validating spreadsheet imports, using ISDATE can just blank the field if crap data is provided –Use Case to make sure a data field is a date –Will Work and produce the inserted field select CASE WHEN ISDATE(t1.datefield) =1 THEN t1.datefield ELSE ” END as datefield_full from (select ’14/12/2054′ as…
Sage EDI IN Intermediary Email
I have found users don’t always read the logs from the Adaptus EDI Module anymore in Sage Line 500. There are a few reasons why orders get stuck, the most common is a customer has sent a duplicate EDI file although one was caused by incorrect product supersession setup causing an endless loop. USE <Affected…
Use T-Sql to find triggers on specific database
Credit: Joe Stefanelli on Stack Overflow I wanted a simple way to find triggers on a specific database, Joe Stefanelli posted SQL which did exactly what I wanted. SELECT sysobjects.name AS trigger_name ,USER_NAME(sysobjects.uid) AS trigger_owner ,s.name AS table_schema ,OBJECT_NAME(parent_obj) AS table_name ,OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’) AS isupdate ,OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’) AS isdelete ,OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’) AS…
Find unused MSSQL indexs on a database
Credit: Basit’s SQL Server Tips The following is very handy to check for any unused index’s on a MSSQL database, this should only be run once the database has been up for at least a week or when all scheculed jobs have been allowed to run at least once. The only change I made is…
SQL Server 2008 – Find which stored procedure is part of a scheduled
Find a stored procedure (or any text) inside a scheduled on on SQL Server 2008 and 2012 by querying the sysjobs and sysjobsteps system tables. The following provides the name of the job and the step name. It uses the standard % wildcard so replace myjob in “%myjob%” below SELECT name, “description”, “enabled”, database_name,step_name,step_id,command …
Maintain MSDB email tables
Credits: SQL Server Central SQL Authority Microsoft Docs Microsoft Docs We recently had an issue where MSDB was growing very quickly, it turned out it was due to a huge increase in data we was directly emailing to users. We now run a weekly job just to maintain the MSDB email tables as whole copies…
Find processes which have been using tempdb
Credit: Microsoft TechNet On some occasions a few MS SQL databases have exhausted tempdb space, this was due to users leaving computers running for weeks on end and the program using different isolation levels or open transactions. I use the following in SQL in see which process ID have been running the longest, as its…
Sage Line 500 EDI IN Intermediary tables inner join on sql query
On ocassion I need to look at what is in the EDI IN modules intermediary tables, scheme.eiordtm has an annoying key_code which it combines multiple objects. It also doesn’t always have the same amount of characters. As the key_code on scheme.eiordtm starts with the key_code from scheme.eiorhdm all I do is find out how many…
Method of working out check digit for an SSCC code
Method of working out check digit for an SSCC shipping code in SQL on a system with Sage Line 500 and Datalinx WHM (Although its easy enough to change, the first “set @SSCC” basically gets the digits, the rest of the code simply calculates the check digit). This proc uses the Datalinx WHM Pallet ID…