Today I am showing you the difference between @@ERROR, BEGINTRY/CATCH and XACT_ABORT. The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process.
Read more →
SELECT@@SERVERNAME AS SqlServerInstance, db.name AS DatabaseName, SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize, SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize, SUM(af.
Read more →
SELECTdb.name AS DatabaseName, bf.logical_name AS LogicalName, CASE bs.[type] WHEN 'D' THEN 'Database' WHEN 'I' THEN 'Differential database' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'File or filegroup' WHEN 'G' THEN 'Differential file' WHEN 'P' THEN 'Partial' WHEN 'Q' THEN 'Differential partial' ELSE 'Unknown' END AS BackupType, CASE bf.
Read more →
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 →
SELECTcpu_count AS [Logical CPUs], cpu_count / hyperthread_ratio AS [Physical CPUs] FROMsys.dm_os_sys_info
Read more →
Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table. The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources.
Read more →
If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously. What if you want to wait for the job to finished?
Read more →
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 →
/******************************************************************************* 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 →
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 →
This is a piece of code I use to create a resultset from and display in Outlook calendar.
CREATEPROCEDURE dbo.uspGetScheduleTimes ( @startDate DATETIME, @endDate DATETIME ) AS /* This code is blogged here http://weblogs.
Read more →
This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008. For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to.
Read more →
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 →
When Windows Server 2008 is more common, you will be surprised how the Firewall is blocking all versions of SQL Server when upgrading the OS. Here are some links to remedy the problems http://msdn.
Read more →
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 →
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 →
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 →
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 →
For this to work, disconnect and reconnect the current query window in SSMS and then copy, paste and run the code below. Books Online says IDENT_CURRENT "Returns the last identity value generated for a specified table or view in any session and any scope.
Read more →
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 →