Following on from Leo Pasta’s lead, I’ve created two stored procedures to make printing out a message without waiting for a batch or transaction to completed. InstantPrint and InstantPrintTime
SQL SNIPPET: Instant Print a Message from within a Batch
You are browsing archives for
Category: SQL Server 2005
Answer: How do you create a byte with the value of 00000001 in SQL Server?
My team mate Pi asked me today: how do you create a byte with the value 00000001 in SQL Server? The answer is to create an integer representation of the byte’s value, and cast it to binary. SELECT CAST(CAST(1 AS TINYINT) AS BINARY(1)) The output in Management Studio is: 0x01 (Management Studio outputs binary fields […]
Hey Yo! SQLCMD may truncate your XML!
While trying to use SQLCMD to produce an app.config file, I encountered problems with the output being truncated. The script was similar to: DECLARE @appconfig XML SET @appconfig = ‘<configuration></configuration>’ … Insert configuration nodes — Output the config SELECT @appconfig as ApplicationConfiguration The default options for SQLCMD produced output like: ApplicationConfiguration ————————- <configuration><connecti (the value […]
Updated SQL Server 2005 Books Online (help) Available Now (Feb 2007)
As SQL Server 2005 continues to grow and change, the Books Online / help files are also updated. Keep your help files up-to-date by downloading the latest copy. Download SQL Server 2005 Books Online Feb 2007 version.
SQL Server 2005 – Service Pack 2a (SP2a) Update
Downloaded and installed Service Pack 2 for SQL Server 2005? Well here’s a reason to download and apply it again. A few days after releasing Service Pack 2 for SQL Server 2005, Microsoft has re-released it with an additional fix for maintenance plans. If you have already applied SP2, you will need to apply this […]
SQL Truncation Attacks
SQL injection attacks are nothing new and hopefully everyone is protecting themselves from it. An interesting by-product of the standard method of protection from the basic sql injection attack (replace single quotes with two single quotes) is increasing the size of the original value. When building a command in a variable, its possible to truncate […]
Shrinking Databases bit-by-bit to avoid long delays
Michael Jones has a neat script for shrinking databases in small chunks to avoid having the server locked up for a long period. declare @sql varchar(8000) declare @name sysname declare @sizeMB int declare @UsedMB int declare @FreeMB int declare @ShrinkMB int — Desired free space in MB after shrink set @FreeMB = 1000 — Increment […]
Troubleshooting Delegation in SQL Server
I just came across a good article by Keith Brown on a common trap when setting up SQL Server with kerberos delegation. As he noted, the port number for the Sql Server Service should be specified when setting the service principal name (SPN). Delegation is a powerful feature of Active Directory but can be difficult […]
Cannot BACKUP or RESTORE a database snapshot
I’ve been beta testing SQL Backup 5 and came across an old problem. If you iterate through all the databases on a server and issue a BACKUP DATABASE for Database Snapshot you will receive an error message similar to: Msg 3002, Level 16, State 1, Line 1 Cannot BACKUP or RESTORE a database snapshot. Msg […]
Unsigned Integer Datatype in SQL Server 2005
There’s been some interesting questions by users on the SQL Server Engine Tips blog. While almost all of them seem to be off topic, there are some interesting ideas. One of the fun ones was from Dave asking “Can we have some unsigned int data types?”. As a basic background for those of us who […]