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 […]
You are browsing archives for
Month: July 2005
List Extended Properties for all Tables and Columns
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’s Article: Seven SHOWPLAN Red Flags
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 […]
SQL Server 2005 Database Snapshots
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&A: How do I declare a Stored Procedure parameter as NOT NULL ?
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&A: Can I use system procedures like sp_help with a schema bound object name ?
Q: Can I use system procedures like sp_help with a schema bound object name ? When I run exec sp_help HumanResources.Resumes I get the error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘.’. A: Yes you can. You need to quote the object name as in: exec sp_help ‘HumanResources.Resumes’
Q&A: Can you BCP out a #Temporary table?
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 […]