CJSmith dot me

I dump stuff I find useful here

Category: SQL Server 2008

Powershell issue with output characters changing to ?

Published / by Chris Smith / Leave a Comment

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 (It will work but TSQL will add notices which may add pointless text to log files)

BULK INSERT #bcp_file_mem
                FROM @CSVFile
                                WITH (FORMAT = 'CSV', DATAFILETYPE = 'widechar', FIELDQUOTE = '"', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK);

Sage EDI IN Intermediary Email

Published / by Chris Smith / Leave a Comment

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 Sage Company MSSQL Database>
GO
CREATE procedure [dbo].[usp_email_edi_held_orders]
AS
-- =============================================
-- Author:		Christopher Smith
-- Create date: 01/08/2017
-- Description:	The procedure looks for orders held in the EDI IN Intermediary Tables
-- Changes:
-- 1.0.1 - 02/08/2017 - Added dateformat dmy as when run was reporting "Conversion failed when converting date and/or time from character string.".  Ensured all date values were cast as datetime as well.
-- 1.0.2 - 11/04/2019 - Corrected the Sales Order lookup to check the invoice customer instead of delivery customer on opheadm
-- =============================================
-- exec [dbo].[usp_email_edi_held_orders]
-- =============================================
--------------------------------------------------------------------------------------------------------------------------
print 'Change to DMY format'
--------------------------------------------------------------------------------------------------------------------------
SET DATEFORMAT dmy;
--------------------------------------------------------------------------------------------------------------------------
print 'Populate temp table'
--------------------------------------------------------------------------------------------------------------------------
Create Table #results(
	customer VARCHAR(8),
	existingorderno varchar(10),
	received_date char(8),
	customer_order VARCHAR(20),
	"filename" char(30),
	warehouse char(2),
	product char(20),
	long_description char(40),
	required_quantity int
	)
insert into #results (customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity) 
	SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
	FROM "scheme"."eiorhdm" t1
		INNER JOIN "scheme"."eiordtm" t2
			ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))
	WHERE t1.update_status = 'h' and CAST(received_date as datetime) BETWEEN CAST(GETDATE() -7 as datetime) and CAST(GETDATE() as datetime)
--------------------------------------------------------------------------------------------------------------------------
print 'Check if need to send email'
--------------------------------------------------------------------------------------------------------------------------
if (select count(*) AS nos from #results) = 0 
begin
print 'No Email to Send'
goto END1
end 
--------------------------------------------------------------------------------------------------------------------------
print 'Check if existing SOP number exists and update table'
--------------------------------------------------------------------------------------------------------------------------
update t1
	set t1.existingorderno = t2.order_no
		from #results t1
			INNER JOIN scheme.opheadm(nolock) t2
				ON t1.customer_order = t2.customer_order_no and t1.customer = t2.invoice_customer
update #results
	set existingorderno = 'NOMATCH'
		from #results
		where existingorderno IS NULL
--------------------------------------------------------------------------------------------------------------------------
print 'Create HTML for Email'
--------------------------------------------------------------------------------------------------------------------------

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Orders held in the EDI IN Intermediary Tables</H1>' +
	N'<font size="5" face="arial" color="red"> <H2>Please review orders held in the EDI IN module, if the order held does not match an existing order further checks may need to be done</H2></font>' +
    N'<table border="1">' +
    N'<tr><th>Customer No</th><th>Existing SO No</th>' +
    N'<th>Customer Order No</th><th>EDI Filename</th><th>Received Date</th>' +
	N'<th>WH</th><th>Product</th>' +
	N'<th>Description</th><th>Qty</th>' +
    CAST ( ( select td =   customer,'',
	td = existingorderno,'',
	td = customer_order,'',
	td = "filename",'',
	td = received_date,'',
	td = warehouse,'',
	td = product,'',
	td = long_description,'',
	td = required_quantity
from #results
order by customer, customer_order
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + 
N'</table>'  + 
N'<p> </p>' + 
N'<p> </p>' + 
N'<p>Regards.</p>' + 
N'<p> </p>' +
N'<p>usp_email_edi_held_orders</p>' 
--------------------------------------------------------------------------------------------------------------------------
print 'Send Email'
--------------------------------------------------------------------------------------------------------------------------
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '<SQL Email Profile>',
	@recipients = '<Recipients>',
    @copy_recipients = '<Copy_Address>',
	@body = @tableHTML,
    @body_format = 'HTML',
	@subject = 'Orders held in the EDI IN Intermediary Tables'
END1:

Use T-Sql to find triggers on specific database

Published / by Chris Smith / Leave a Comment

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 isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'

SQL Server 2008 – Find which stored procedure is part of a scheduled

Published / by Chris Smith / Leave a Comment

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
       FROM "msdb"."dbo"."sysjobsteps" t1
              INNER JOIN "msdb"."dbo"."sysjobs" t2 on t1.job_id = t2.job_id
       where t1."command" LIKE '%myjob%'

More info on SQL system tables here:
MS Docs
MS Docs

Maintain MSDB email tables

Published / by Chris Smith / Leave a Comment

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 of emails, attachments and logs are kept. I first run a stored procedure designed to maintain the tables and then directly delete from the tables (I would expect these to delete nothing).

use msdb
go
declare @DeleteToDate datetime
--I have set this to keep 60 days
set @DeleteToDate = DATEADD(d, -60, getdate())
--Print the date for logs
print @DeleteToDate
print 'Using inbuilt script to maintain tables'
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DeleteToDate
--Line below is SQL Server 2008 only
--EXECUTE msdb.dbo.Sysmail_delete_log_sp @logged_before = @DeleteToDate
--maintain sysmail_allitems
print 'maintain sysmail_allitems'
DELETE FROM msdb.dbo.sysmail_allitems
   where sent_date < @DeleteToDate
--maintain log file
print 'maintain sysmail_log'
DELETE FROM msdb.dbo.sysmail_log
   where log_date < @DeleteToDate
--remove old emails
print 'maintain sysmail_mailitems'
delete from msdb.dbo.sysmail_mailitems
   where sent_date < @DeleteToDate
--remove retries (Hopefully this shouldn;'t be to high a number)
print 'maintain sysmail_send_retries'
delete from msdb.dbo.sysmail_send_retries
       where last_send_attempt_date < @DeleteToDate
--remove attachments (If you are removing the email no point in keeping the attachments)
print 'maintain sysmail_attachments'
DELETE FROM dbo.sysmail_attachments
       WHERE last_mod_date < @DeleteToDate

Find processes which have been using tempdb

Published / by Chris Smith / Leave a Comment

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 a select statement it can of course be amended to your requirements (Adding an order by clause or just selecting the columns you require).

SELECT *
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

You should see something like the following, rather than kill of the transaction if it is a user try and educate the user to come out of transactions when finished.

Sage Line 500 EDI IN Intermediary tables inner join on sql query

Published / by Chris Smith / Leave a Comment

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 characters are in key_code from scheme.eiorhdm and use the SQL LEFT function on the join.
Additional columns can be added but this is just the defaults I use.

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
 FROM "scheme"."eiorhdm" t1
  INNER JOIN "scheme"."eiordtm" t2
   ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

Method of working out check digit for an SSCC code

Published / by Chris Smith / Leave a Comment

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 and Sage stock warehouse code as Sage Stock lot numbers may contain text and slashes.
If called from a Datalinx WHM telnet/SSH script all print lines must be commented out.
GS1 codes can be shorter or longer, remember only 16 digits can be used (The GS1 code goes in from the second digit and digit 18 is always a check code!)
After needing two different versions (One required spaces in between different blocks the other didn’t) I decided to be lazy and specify the field locations. This version expects no spaces, if you need spaces simply change the Substrings and increase the variable SSCC from varchar(18).
Another thing to mind is I don’t need to change the first digit (GS1 states the same SSCC code can not be used within a year).

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:      Generate an SSCC code with check digit
-- The first digit is always zero (Unless we re-use lot numbers within a year)
-- Digits 2 to 7 are our GS1 number of 1234567
-- Digits 9 to 17 are out lot number (With +0 taken off the front)
-- Digit 18 is a check digit.  Use GS1 Check Digit Rules to Calculate
-- =============================================
-- GS1 Check Digit Rules
-- 1 Starting with the right hand digit of the number, add all the alternate digit values
-- 2 Multiply the result of step 1 by 3
-- 3 Add up all the other remaining digits
-- 4 Add the result of step 2 to the result of step 3
-- 5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10
-- Check output with http://www.gs1.org/check-digit-calculator to ensure accurate
-- =============================================
-- Data sources used:
-- scheme.stquem
-- =============================================
-- Example execution
-- exec [dbo].[usp_generatecheckdigitfor_SSCC] @palletno = '+001234567', @wh = 'WH'
-- =============================================
CREATE PROCEDURE [dbo].[usp_generatecheckdigitfor_SSCC] 
       -- Add the parameters for the stored procedure here
       @palletno varchar(10) = '+000000000', 
       @wh varchar(2) = 'WH'
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
 
DECLARE @SSCC varchar(18)  --The SSCC Code
DECLARE @SSCCcd_working1 int  --For Step 1, 2 and 4 and for final calc
DECLARE @SSCCcd_working2 int  --For Step 3 and 4
DECLARE @SSCCcd_r int  --Rounded results calc
DECLARE @SSCCcd_dif int  --Used to calc check digit
 
--Input is 17 numbers for SSCC number.  A check digit must be calculated at the end
--Our GS1 Company code is 7 digit long and is "1234567".  An extra zero is added at the end as we can't send the plus symbol of out pallet id
       set @SSCC = (select '01234567' + SUBSTRING(users_text_field,2,8) as SSCC from scheme.stquem(nolock) where users_text_field = @palletno and warehouse = @wh)
       print @SSCC
 
--Work out Check Digit from @SSCC
print '1 Starting with the right hand digit of the number, add all the alternate digit values'
       set @SSCCcd_working1 = SUBSTRING(@SSCC,17,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,15,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,13,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,11,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,9,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,7,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,5,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,3,1)
       print @SSCCcd_working1
       set @SSCCcd_working1 = @SSCCcd_working1 + SUBSTRING(@SSCC,1,1)
       print @SSCCcd_working1
print '2 Multiply the result of step 1 by 3'
       set @SSCCcd_working1 = @SSCCcd_working1*3
       print @SSCCcd_working1
print '3 Add up all the other remaining digits'
       set @SSCCcd_working2 = SUBSTRING(@SSCC,16,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,14,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,12,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,10,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,8,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,6,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,4,1)
       print @SSCCcd_working2
       set @SSCCcd_working2 = @SSCCcd_working2 + SUBSTRING(@SSCC,2,1)
       print @SSCCcd_working2
print '4 Add the result of step 2 to the result of step 3'
       set @SSCCcd_working1 = @SSCCcd_working1 + @SSCCcd_working2
       print @SSCCcd_working1
print '5 The check digit is the smallest number that must be added to this sum to reach a multiple of 10'
       set @SSCCcd_r = ROUND(@SSCCcd_working1,-1)
       print @SSCCcd_r
-- Avoid issues from rounding down, add 10 to ensure @SSCCcd_r is always greater or equals to @SSCCcd_working1
       If @SSCCcd_r < @SSCCcd_working1
              set @SSCCcd_r = @SSCCcd_r+10
       print @SSCCcd_r
--Work out difference between rounding and check digit
       set @SSCCcd_dif = @SSCCcd_r - @SSCCcd_working1
       print @SSCCcd_dif
       print @SSCC
       set @SSCC = @SSCC + CAST(@SSCCcd_dif as varchar)  --Don't treat as number!
       print @SSCC
       select @SSCC as SSCC

END

Apply ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to SQL Server

Published / by Chris Smith / Leave a Comment

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 code.

--Set Db to single user mode, rollback if any process still connected
ALTER DATABASE "<Database>" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE "<Database>" SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE "<Database>" SET READ_COMMITTED_SNAPSHOT ON
GO
--Set Db to multi user mode
ALTER DATABASE "<Database>" SET MULTI_USER
GO

--Check options are enabled (Value=1) or disabled (Value=0)
SELECT snapshot_isolation_state_desc FROM sys.databases where name="<Database>"
SELECT is_read_committed_snapshot_on FROM sys.databases where name="<Database>"

Source: Microsoft MSDN
Microsoft MSDN
I am pretty sure the Check options are enabled must have been someone else’s code, when I find where I got this I will add a further credit.