Running and debugging a SQL Server 2000 DTS Package in Visual Basic 6.0

If you have a fairly complex DTS-package (Data Transformation Services) in SQL-Server 2000 and don’t have a clue of what it does and have a limited understanding of dts read this. You can ”export” the package to VB6 and by doing so you can read the code and run and debug the script from the VB6-IDE. You can even run it in VB.NET. This is quite old school but sometimes necessary to do in legacy systems if you want to edit a complex dts-script.

Step 1 – Export the dts-package to visual basic

Open your DTS-package in SQL-server 2000.

Choose ”Package -> Save as” in the menu and then change ”Location” to ”Visual Basic file” in the dialog. Press OK and then a bas-file (VB-module) is saved to disk.

Step 2 – Create a visual basic project

Open Microsoft Visual Basic IDE. Choose to create a new ”Standard EXE” project then click ”Open”. Remove the default Forms1 from the project. Then you add the dts bas-file to the project with ”Project -> Add module”. The result should should look like below.

Step 2 – Executing and debugging the dts-package

Go to ”Project -> References” and add references to ”Microsoft DTSPackage Object Library” and ”Microsoft DTS Custom Task Object Library”. Now you are ready to edit your package.

To debug the package you simply set a breakpoint and hit F5.

Now feel free to fool around with the package/module and make the changes that you need. It is not easy at first but after a while you get the hang of it.

Step 3 -Saving the dts-package to SQL-server

When your package is ready for deployment you can deploy it through VB. Instead of executing the package you can easily save it to your SQL-server.

Search for the ”Save or execute package” section in the module. Comment the Execute statement and uncomment the SaveToSQLServer row.

Hit F5 to run the module. If everything works fine your new dts-package shows up in SQL-server.

More help with DTS can be found and at sqldts.com/

Query designer in management studio sql server 2005

I have been using Management Studio for SQL-server 2005 for a while now. However, I have never really liked it because of the really strange user interface. Compared to the simple Enterprise manager I first thought that Management studio was a nightmare. But I guess it takes some time to get used to…

I have searched for a simple and quick way to start the query designer. I only want to do a couple of querys and exit. In enterprise manager you only had to browse for a table, right click it and choose open table -> query. The same procedure in management studio takes a lot more clicks and is not worth the effort.

Today I found this post which describes how to access the query designer in SQL-server 2005 Management studio in a quick way. I also found out that it had a lot more features than the earlier version. That’s really good.

How to access the query designer the fastest way:

1. Start Management studio and connect to server.
2. Browse for the database and select it.
3. Press Ctrl+N for a new query window.
4. Press Ctrl+Shift+Q to get the query designer.
5. Rock on.

This is a great feature that is added to what previously was named ”Query analyzer”. The results of these queries are however not editable like they were in Enterprise managers query designer. I guess I have to through the ”open table” (and get all rows) procedure for that functionality. That is really strange, why couldn’t thay have left the ”open table -> query” option?

Shrinking ALL Truncate Log Files

The transaction logs on your SQL-server can fill up a lot of disk space. In my case, I monthly needed to truncate all the log files from the databases that I get from our production environment. These log files are really huge. I need to truncate them because I do not need the logs on my development machine. I made this little T-SQL script to make my (and your) life easier.

NOTE! This works for pre ”SQL Server 2008” because BACKUP LOG with TRUNCATE_ONLY is discontinued. An updated script can be found here.

declare @db varchar(300)
declare @logname as varchar(300)
declare @sql nvarchar(1000)

declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name NOT IN('master','model','pubs','msdb')
ORDER BY name

open cDB
fetch cDB into @db

while (@@fetch_status = 0)
begin

--print @db
SET @sql='SELECT @logname=name FROM '+@db+'.dbo.Sysfiles WHERE fileid=2'
--print @sql
EXEC sp_executesql @sql,N'@logname varchar(300) out,@db varchar(300)',@logname out,@db
--print @logname

SET @sql='Use '+@db+';'
SET @sql=@sql+'Backup Log '+@db+' with truncate_only;'
SET @sql=@sql+'DBCC shrinkfile ('+@logname+', 2);'
--print @sql
EXEC sp_executesql @sql,N'@logname varchar(300)',@logname

fetch cDB into @db
end
close cDB
deallocate cDB