Archive

Archive for the ‘T-SQL’ Category

SQL Server Script user with SID

September 18th, 2015 No comments

The Problem

When you move sql logins around servers the logins are created with an SID that uniquely identifies the login.

Assume you setup two logins manually, one on your production server and one on your dev server.

When you restore your prod database to development your Login will be “disconnected” from the restored database and the SID of the “User” in the restored production database does not match the SID of the Login on your dev box.

The Solution

Create your login specifying the SID.
The easiest way is to let SQL set and SID and then read it off the DB and use it in your new login creation script
This script should do it

Use master
GO

-- Step 1 - Create Login
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'usr_website')
DROP LOGIN [usr_website]
GO

CREATE LOGIN [usr_website] WITH PASSWORD = 'password:-)'
GO

-- Step 2 - Get SID
Declare @name nvarchar(254)

set @name = 'usr_website'

SELECT 
	[sid]
	,[status]
	,[name]
	,[dbname]
	,[language]
	,[denylogin]
	,[hasaccess]
	,[isntname]
	,[isntgroup]
	,[isntuser]
	,[sysadmin]
	,[securityadmin]
	,[serveradmin]
	,[setupadmin]
	,[processadmin]
	,[diskadmin]
	,[dbcreator]
	,[bulkadmin]
	,[loginname]
FROM [msdb].[sys].[syslogins]
where name = @name or @name is null

-- Step 3 - Create Login with SID
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'usr_website')
DROP LOGIN [usr_website]
GO

CREATE LOGIN [usr_website] WITH PASSWORD = 'password:-)', SID = 0x346837D1EC9C484C91A3B46D7EC7EABF
GO

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server, T-SQL Tags:

T-SQL Drop temp table if it exists

September 9th, 2015 No comments

The Problem

I often work in SQL Server with temp tables and they can be a pain when developing as you may have code like

Create Table #tblTemp( Id int , Value nvarchar(32))

Insert Into #tblTemp
select Id, Value from SomeOtherTableAndLotsOfJoins

--Drop Table #tblTemp

You comment out the drop as you want to check the results and do some testing but next time you want to run the script you have to remember to call the “Drop Table”

The Solution

Put this at the top of your script

if exists(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tblTemp'))
begin
	drop table #tblTemp;
end
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: T-SQL Tags:

SQL Sever DB Mail Logs

January 6th, 2014 No comments

If you use SQL Server Database mail sometimes you will have trouble and you want to see a log of all the emails that have been sent or failed to send.

To do this there are some management views that will help check what has been going on:

select * from msdb.dbo.sysmail_allitems
Order By mailitem_id desc

select * from msdb.dbo.sysmail_faileditems;

Note Also if you need to force a restart of the DBMail service, then use the following:

exec msdb.dbo.sysmail_stop_sp;
exec msdb.dbo.sysmail_start_sp;
VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

Categories: SQL Server, T-SQL Tags:

exec sp_executesql nvarchar(max) truncating at 4000 characters

November 28th, 2013 1 comment

The Problem

Sometimes you may need to write dynamic SQL.

One example is writing query for running over linked server connections. You might write something like this

Create Procedure prcTest
    @start datetime
as
Begin
    Declare @sql nvarchar(max)

    set @sql = 'Insert Into #tblTemp
    select * from OpenQuery(LINKSERVER, ''Select * from dbo.tblRemote r
        inner join tblRemote2 r2 on r.Id = r2.fkId 
    .... lots more sql x 4000+ characters
    Where r.DateCreated > ''''' + Convert(varchar, @start, 106) + '''''
    '')';

    exec sp_executesql @sql

    select * from #tblTemp inner join tblOther on id = otherId

End

Assuming this has over 4000 characters it will truncate at 4000 characters, your SQL will be malformed as it is missing the closing quote ‘ and will crash.

The Resolution

So what is going on!

I’ve read everywhere on the internet about nvarchar(max) having a 2GB limit etc, but it is clearly truncating at 4000 characters.

Add in a

select Len(@sql)

to confirm that it is definitely a 4000 character limit.

So what is going on?

Implicit Type Conversion!!!

The innocent looking code adding a date to the query,

 ''''' + Convert(varchar, @start, 106) + '''''

is causing SQL Server to perform an implicit type conversion and our NVarchar(MAX) is somehow becoming an NVarchar(4000).

Change the addition of the date to the following

''''' + Convert(nvarchar(max), @start, 106) + '''''

The problem is averted and the select Len(@sql) will happily count beyond 4000.

(BTW convert(varchar, @date, 106), creates a ‘1-Jan-2000’ formatted date which resolves any issues that can occur with different regional settings on your sql server causing problems)

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server, T-SQL Tags: