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 […]
You are browsing archives for
Category: SQL Server / Microsoft SQL Server / MSSQL
SQL Server / Microsoft SQL Server / MSSQL
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 […]
What with this database called [$(DatabaseName)] ?
I just handed a work mate an output script from DataDude, which he ran in Management Studio. Instead of producing the expected database he ended up with one called $(DatabaseName) Remember database pro uses SQLCMD to deploy databases If you use Management Studio to run an output script, you’ll need to swap into SQLCMD mode […]
Tip: Use Named Parameters with Stored Procedures when Unit Testing (VSTS Database Professionals)
I’ve been migrating unit tests to a new Visual Studio Database Professionals project. So far there’s been no problems (other than trying to find a naming standard). This problem had me scratching my head for a few minutes today. I defined a unit test for a stored procedure as: DECLARE @ItemCode varchar(32), @PriceLevel varchar(32) SELECT […]
Simon Sabin’s SSIS Raw File Reader
Ever used a Raw File destination in SSIS ? They’re FAST! Writing rows from the pipeline to disk as quickly as possible. Raw Files are useful for increasing the throughput speed of a data flow task by using them in Extract / Transform procedures. The reason they are so efficient is they stored the data […]
The Five Easy Steps of Unit Testing for Database Professionals
Now that Microsoft have released Visual Studio Team Suite for Database Professionals (aka [story:datadude]) its time for SQL Server developers and dbas to learn more about Unit Testing. There is many different methods to test a database within the framework set by Visual Studio. So how do you know which way is best? Well one […]
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 […]
LEN(NULL) != 0
Its pretty often that I run across SQL code that supposed to handle empty or null varchar values. But a lot of the time it doesn’t. And usually its because a developer has made the false assumption that null has a length of 0. It doesn’t. LEN(NULL) IS NULL. LEN(NULL) does not = anything. LEN(NULL) […]