You are browsing archives for
I don’t know how long I’ve been waiting for this, but someone has finally built an Intellisense tool for SQL Server Management Studio & Query Analyzer. If you’ve been using Visual Studio.Net 2003 or 2005, you’ll know how useful Intellisense is in other programming languages (it’s like auto-complete in Word). I don’t know why it’s […]
Q: When I use the condition LEN(columnname) = 0, I don’t get columns where columnname value is null. Why? Shouldn’t LEN(NULL) = 0 ? Are my ANSI_NULLS or ANSI_DEFAULTS setting to blame? A: No. LEN(NULL) will always return NULL. From SQL Server Books Online Null Values A value of NULL indicates the value is unknown. […]
If you’re following Microsoft’s Best Practice recommendations for securing SQL Server 2000, you should be running the SQL Server and SQL Server Agent services under separate domain user accounts (not the Local System account). Whilst setting this up you may encounter this error: [LOG] The data portion of event 17052 from MSSQLSERVER is invalid Adding […]
Extended Properties in SQL Server give you the ablility to attach a comment or description to an object in the database. When you use Management Studio to add a Description to a table or column, it is stored as an Extended Properties with the name of MS_Description, and the object it relates to. You can […]
Joseph Sack over at SQL Server Centralhas written a short article on query optimisation using Query Analyzer’s graphical Show Plan. Certainly an under used resource, show plan can actually give you insight to how SQL Server intends to / did run your query, and where high i/o or cpu taxing tasks are undertaken. Joseph’s article […]
This is a really neat addition to SQL Server 2005. Database Snapshots are essentially a read-only, static view of an existing database, without duplicating all the data on disk. I use this feature a lot when I upgrade database schemas, or make major changes to data, and need to be able to compare the changes […]
Q: How do I declare a Stored Procedure’s parameter as NOT NULL ? A: You can’t. Remarks in the CREATE PROCEDURE t-sql documentation states: Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not […]
Q: If I create a temporary table with: SELECT OrderID,OrderDate,Total INTO #TempOrders FROM Invoices Can I then BCP the data into a file with ?: … SET @cmd = ‘bcp “#TempOrders” out “C:TempOrders.txt” -T EXEC xp_cmdshell ‘bcp ‘ + A: No. Because BCP will create a new connection, it will not be able to see […]
While SQL Server controls access to tables and objects on a user-by-user basis; restricting a user to see only certain rows in table is a common need. You might set permissions to allow users to see Orders in a e-commerce system, but you will want to prevent them from seeing orders by other users. If […]
As part of Microsoft’s push to improve dba/developer knowledge and uptake of SQL Server 2005, Microsoft have released free training for SQL Server 2005 available online until 1st November 2006. There’s currently seven free courses covering installation, administration, programming and other features of SQL Server 2005. Included in each course are hands-on virtual labs, and […]
