Category: SQL Server 2008
Powershell issue with output characters changing to ?
On PowerShell command pipe to export CSV add Encoding Unicode if characters change to ? Credit: Idera execute xp_cmdshell ‘powershell -Command “Get-ChildItem -File -Path @path -filter *.* -Recurse | ForEach-Object {[PsCustomObject]@{Fullname = “$($_.FullName)”; LastWriteTime = “$($_.LastWriteTime)”}} | Sort Fullname -Descending | Export-Csv -Path @CSVFile -NoTypeInformation -Encoding unicode”‘ If using BULK INSERT ensure widechar is used…
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…
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…
Apply ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to SQL Server
We needed to enable ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to specific databases on a SQL Server 2008 instance, I found to do this even with users off I was being shown errors stating it must be done in Single User Mode. I decided I wanted to do this as quickly as possible so slapped together some SQL…