Peter Larsson Blog

Patron Saint of Lost Yaks

Strange happening

I had a strange scenario today and I can't reproduce it. I changed current database to adventureworks and ran following code DECLARE@SQL NVARCHAR(200) SET@SQL = 'SELECT DB_NAME()' EXECsp_executesql@SQL EXEC (@SQL) The sp_executesql statement returned "master" and exec statement returned "adventureworks". Read more →

SQL Server 2008 with MERGE and triggers

I come across a question today if SQL Server 2008 MERGE command call table triggers one per hit, or one per statement as it normally does. The short conclusion is that the MERGE command splits the source data into three “streams” and internally executes INSERT, UPDATE and DELETE statements. Read more →

How to script out all your objects one per file

/******************************************************************************* Initialize communication variables *******************************************************************************/ SETNOCOUNT ON DECLARE@pathProc VARCHAR(255), @pathFunc VARCHAR(255), @pathTrig VARCHAR(255), @pathView VARCHAR(255), @cmd NVARCHAR(4000), @pathBase VARCHAR(256) SELECT@pathBase = '\\Archive\Documents\Projects\Peso\Code\', @pathProc = @pathBase + 'Stored Procedures\', @pathFunc = @pathBase + 'Functions\', @pathTrig = @pathBase + 'Triggers\', @pathView = @pathBase + 'Views\' SET@cmd = 'md "' + @pathProc + '"' EXECmaster. Read more →

Get the job name for current context

Today I had to write some code to dynamically get the job name currently running. DECLARE@SQL NVARCHAR(72), @jobID UNIQUEIDENTIFIER, @jobName SYSNAME SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)' EXECsp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT SELECT @jobName = name FROM msdb. Read more →

How to get IP address

DECLARE@Interfaces TABLE ( RowID INT IDENTITY(0, 1), Interface CHAR(38), IP VARCHAR(15) )   INSERT@Interfaces ( Interface ) EXECmaster..xp_regenumkeys N'HKEY_LOCAL_MACHINE', N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces'   DECLARE@RowID INT, @IP VARCHAR(15), @Key NVARCHAR(200)   SELECT@RowID = MAX(RowID) FROM@Interfaces   WHILE@RowID >= 0 BEGIN SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface FROM @Interfaces WHERE RowID = @RowID   EXEC master. Read more →

How to get authentication mode in SQL Server

CREATEFUNCTION dbo.fnGetSQLServerAuthenticationMode ( ) RETURNSINT AS   BEGIN DECLARE @InstanceName NVARCHAR(1000), @Key NVARCHAR(4000), @LoginMode INT   EXEC master..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', N'MSSQLSERVER', @InstanceName OUTPUT   IF @@ERROR <> 0 OR @InstanceName IS NULL RETURN NULL   SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'   EXEC master. Read more →

How to change authentication mode in SQL Server

CREATEPROCEDURE dbo.uspSetSQLServerAuthenticationMode ( @MixedMode BIT ) AS   SETNOCOUNT ON   DECLARE@InstanceName NVARCHAR(1000), @Key NVARCHAR(4000), @NewLoginMode INT, @OldLoginMode INT   EXECmaster..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', N'MSSQLSERVER', @InstanceName OUTPUT   IF@@ERROR <> 0 OR @InstanceName IS NULL BEGIN RAISERROR('Could not read SQL Server instance name. Read more →

Some SQL Server network properties

DECLARE@Stage TABLE ( RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Data VARCHAR(90), Section INT )   INSERT@Stage ( Data ) EXECxp_cmdshell 'ipconfig /all'   DECLARE@Section INT   SET@Section = 0   UPDATE @Stage SET@Section = Section = CASE WHENASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE@Section END   SELECTMAX(CASE WHEN x. Read more →

Horizontal partitioning, Enterprise style

CREATEPARTITION FUNCTION pfOrderDate ( DATETIME ) ASRANGE RIGHT FOR VALUES ( '20000101', '20010101', '20020101' ) GO   CREATEPARTITION SCHEME psYak ASPARTITION pfOrderDate ALLTO ([PRIMARY]) GO   CREATETABLE Orders ( OrderID INT NOT NULL, CustomerID VARCHAR(15) NOT NULL, OrderDate DATETIME NOT NULL ) ONpsYak(OrderDate) GO   CREATECLUSTERED INDEX IX_OrderID ONOrders ( OrderID ) CREATENONCLUSTERED INDEX IX_OrderDate ONOrders ( OrderDate ) INCLUDE( OrderID, CustomerID ) GO INSERTOrders ( OrderID, CustomerID, OrderDate ) SELECT1, 'Peso', '20011225' UNIONALL SELECT2, 'Jennie', '20020314' SELECTOrderID, OrderDate FROMOrders WHEREOrderDate = '20011225'   SELECTOrderID, OrderDate FROMOrders WHEREOrderID = 1   DROPTABLE Orders DROPPARTITION SCHEME psYak DROPPARTITION FUNCTION pfOrderDate   Read more →

SQL Injection

Every now and then I see sites where commands are concatenated and sent to database server. The author must really trust the user inputs! For every system built this way, you can expect at least one attack with SQL injection. Read more →