CJSmith dot me

Just another blog

Category: SQL Server

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

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

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

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.

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

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.

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.

Add a user to DatabaseMailUserRole on msdb

Published / by Chris Smith / Leave a Comment

I had a user unable to use a script which generated an email, I found the user didn’t have permission, Microsoft state:
To reduce the surface area of SQL Server, Database Mail stored procedures are disabled by default.
To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

You can add the user manually by:
Opening SQL Server Management Studio
Connecting to the server in question
Expand Databases
Expand System Databases
Expand msdb
Expand Security
Expand Users, right click the user or group and click properties
If the user is not in the list right click users and add the user or group
On the Membership tab ensure the user or group is a member of DatabaseMailUserRole

If you are lazy use a script like the one below (It will return a 1 if SQL can’t find the user or the user already has access, 0 means it was successful)

Source: Microsoft TechNet
Microsoft MSDN

Notes:
I found using Windows Active Directory Groups required an extra step, by default all users have access to the Public Profile but not groups on SQL Mail.
Groups need to be added via “Manage Profile Security” –> “Private Profiles” tab.
As the group would be added select the AD group from the drop down box.
Ensure Access is ticked and click Next and OK.
As I have only done this the once I haven’t looked up if this can be done directly in T-SQL.

Simple error checking when running batch files from T-SQL in SQL Server

Published / by Chris Smith / Leave a Comment

A while ago we added error trapping to the batch file called by a SQL script, this was to ensure if a program failed the rest of the process would not run:

After ensuring the program would return an error level if something went wrong we then I then used the ErrorLevel and an if statement. If the error level is equal to or greater than 1, I make it go to the end of the file and exit with code 1 (By default its zero which normally means the batch or program executed as expected)

We then modified our existing code in SQL to ensure the batch program is run and the exit code is captured in a variable. It can then be dealt with and the example below can easily be changed to suit whatever is required.
 

Credits:
SS64
Microsoft MSDN (Example Set C)

T-SQL Get current Unix Time

Published / by Chris Smith / Leave a Comment

The following allows you to work out the current time on the Unix Environment. The Datediff function is used to work out the number of seconds between 01/01/1970 and the current date.
I have used this against data which is stored and as Unix time.

Other resources for T-SQL Dates:
DwainCSQL

Compress and move existing Windows 2012 and Microsoft SQL Server 2008 Express backups

Published / by Chris Smith / Leave a Comment

The following has been used to backup a server with Cisco Cubac on. The software runs on Windows 2012 machine and uses SQL Server 2008 Express.
As its not on a managed server I had to find a way to backup the server and the database, plus keep the backup as small as possible.
I get the Windows backup tool to backup once a day and save it to D:\WindowsImageBackup\, I then get the script below to run which then backs up SQL Server 2008 Express separately. I then get the backups compressed with 7zip and move them to a network storage device.
The destination NAS required a username and password, I had to put in a username and password (Not recommended although I believe SAMBA may not have been configured correctly).
If the server was on the same domain as the backup destination then get rid of “/user:username Password” from the net use line, normally having an account with the same username and password should work.

This batch requires RoboCopy (I believe is installed with the OS), 7Zip, a destination device and lots of disc space to fit the backups.

This has been used on a server with Windows 2012 and Microsoft SQL Server 2008 Express to backup a Cisco Cubac install.
The script will call SqlBackup.sql to tell SQL to backup the databases (As it installed with SQL Server 2008 Express).
Everything surrounded by <> needs to be changed, anything calling a directory needs to be checked.
%hostname% is not used for ServerHostName variable, its used for the SQL instance name (Which normally is the hostname).

This second file (SQL Backup File for Microsoft SQL Server 2008 Express) is called separately, above I have called it SqlBackup.sql in the folder specified in variable BackupScript.