Load XML data into SQL 2000

I recently had the need (read "somebody wanted a big favor") to load XML data into a SQL Server 2000 database. I came across an easy way for them (being a developer to push the data into SQL) per the article (http://support.microsoft.com/kb/316005).

After creating the table on SQL, you have to create a mapping file to tell it which fields go with which columns in the SQL database. The article above has a good basic but working example. Modify away.

Basically, I created a VB script as follows:
Note you can use localhost or replace with a SQL instance (default or named). You can also use paths relative to the executable.

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")objBL.ConnectionString = "provider=SQLOLEDB.1;data source=localhost;database=XMLImport;Integrated Security=SSPI"objBL.ErrorLogFile = "error.log"objBL.Execute "XML_Schema.xml", "XML_Data.xml"Set objBL = Nothing

The biggest "gotchas" were the case-sensitivity of the field names and it didn't like spaces either. Also, you had to have the right version to run the VB script. Since I did not, my workaround was to execute the script remotely.

List Table Rowcount and Size

Here's a handy way to get a list of table rowcounts and space used. If you've not used the sp_msforeachtable just plug in the question mark (?) as a token for the name of the table.

--create a temporary table to hold the results
CREATE TABLE #TableStats (
 [name] varchar(128),
 [rows] varchar(128),
 [reserved] varchar(128),
 [data] varchar(128),
 [index_size] varchar(128),
 [unused] varchar(128)
)
GO
--loop through each table and populate the temp table
--NOTE: we can't use table variables with the sp_msforeachtable
sp_msforeachtable "INSERT INTO #TableStats EXEC sp_spaceused '?'"

SELECT * FROM #TableStats
--ORDER BY LEN([rows]) DESC, [rows] DESC --sort by # of rows
ORDER BY LEN([reserved]) DESC, [reserved] DESC --sort by space used
DROP TABLE #TableStats
GO

List of DTS packages

I was recently asked by a fellow DBA if I had "one of my scripts" to give a list of DTS package on a SQL Server 2000 instance. Since the majority of my work is in 2005 and 2008, I just ran the following query.

select * from msdb..sysdtspackages

The results were not helpful because most of the packages had multiple versions stored so instead of listing the 200+ packages, it listed the 7000+ versions of those packages. After a few minutes, I came up with the following query which I've now added to my script library.

SELECT
 pkg.[name],
 pkg.[owner],
 pkg.[description],
 cnt.[CreateDate],
 cnt.[LastModified],
 cnt.[NumOfVersions]
FROM
 [msdb].[dbo].[sysdtspackages] pkg
INNER JOIN
(SELECT
 [id],
 COUNT(*) [NumOfVersions],
 MIN([createdate]) [CreateDate],
 MAX([createdate]) [LastModified]
FROM
 [msdb].[dbo].[sysdtspackages]
GROUP BY
 [id]) AS cnt
ON
 pkg.id = cnt.id AND
 pkg.createdate = cnt.LastModified

SQL Server Profiler Tools

I learned several things about SQL Profiler by watching this video. I found it especially interesting that you can view the ShowPlan and the Perfmon data right from profiler.
You have to setup a free account to view the video but it's worth it. There are dozens of great tutorials like this.
http://www.jumpstarttv.com/using-sql-server-2005-profiler_59.aspx

Move TempDB, Recreate missing TempDB

You can move tempdb by running the following script. If tempdb is missing (due a drive failure, for example), you will need to start the SQL Service with -T3608 for the startup option and then run the script.

/*If tempdb is unavailable, stop and start the SQL service with the
startup option -T3608
This allows you to start SQL without tempdb.
*/
USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb.ldf')
GO

Insert Picture into Image Field using T-SQL

Here's an example of how to quickly load a picture into an image field using T-SQL.

INSERT INTO [hr].[NTUsers] (
 [userid],
 [username],
 [title],
 [reportsto],
 [pic]
)
SELECT
 '00001',
 'Chip Porter',
 'Technology Services',
 '00000',
 BulkColumn FROM OPENROWSET(BULK 'c:\noimage.bmp', SINGLE_BLOB) AS ImageLoad
GO