Programming

Hello again…
Today I wanted to share how to import a XML file into SQL Server 2005. There are different ways to import a XML file either through T-SQL, SSIS, VB Script, etc. I have found the easiest method for just getting and XML file into SQL Server 2005 is to use T-SQL.
I found [...]

Today I was looking for a “better way” to rebuild indexes in SQL Server 2005 and to minimize blocking\contention in the database. As I was looking through BOL I came across the ALTER INDEX statement. If you want to rebuild all indexes on a specified table you can run the following code:
ALTER INDEX [...]

Today I was searching the internet for an easier way to see if a file exists. As I was searching I came across several undocumented SQL Server 2005 procedures. They are:

xp_fileexist
sp_MSforeachdb
sp_msforeachtable
sp_msdependencies

Now knowing that these undocumented extended and stored procedures exist, they will definitely make my job a lot easier. The source that [...]

Hello again… I hope you had a good holiday weekend for those of you who had the holiday off. Last night I came across a stored procedure that used SQL Server reserved words in the SELECT and INSERT statements. Since I am using SQL Server 2005, the words that came up as “reserved” [...]

In SQL Server 2005 there several ways to detect a CPU bottlneck.  The first way to detect a bottleneck is by looking at the System Monitor counter Processor: % Processor Time.  If the value of this counter  is greater than 80% for  15 to 20 minutes in duration, then it is a good indication that [...]

Here is a stored procedure that will restore your database without having to remember the SQL syntax of the RESTORE DATABASE command in SQL Server 2000. This stored procedure is to be compiled and run from MSDB. The stored procedure reads from the registry to figure out the data and log path of [...]

Today I am going to cover how to mark a database in SQL Server 2000 for replication. This is important when you are attempting to shrink a transaction log that has transaction marked for replication but have not been flushed out of the log yet. I have come across this situation when restoring [...]

Have you come across a time when a stored procedure takes a lot longer to run than normal but the T-SQL itself runs a lot faster in Query Analyzer? This might be due to “parameter sniffing”. During compilation of a stored procedure the query optimizer attempts to “sniff” or “seek [...]

I thought today I would share with you a great way to move SQL Server logins from one SQL Server 2005 server (source) to another SQL Server 2005 server (destination).  In the past I have tried to use DTS to move logins (passwords and SIDs) from one server to another but without much success.  As I [...]

I have been looking for a more efficient way of getting space used requirements for all of my tables in a given database.  It seems like Microsoft has answered my prayers.  Microsoft has now given us the sp_MSforeachtable stored procedure.  This procedure is not currently documented within BOL.  The T-SQL script below demonstrates how to [...]