Thursday, October 30, 2008

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

Tuesday, October 28, 2008

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

Tuesday, October 21, 2008

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

Friday, October 10, 2008

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

Thursday, October 9, 2008

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