SQL Server 2000: export SQL Query Analyzer result data to Excel/CSV and more
Posted by jpluimers on 2011/11/16
I just visited a client which is still using SQL Server 2000, and not upgraded their tool set, nor allows different tools to be installed.
Which means back to basics, re-adjusting the project planning and frantically trying to remember things from the past.
Boy am I spoiled with a current toolset :)
This goes from simple things like saving a result set from the SQL Query Analyzer:
- Select a cell
- Press Ctrl-A to select all rows
- Right click in the grid and select “Copy” (to copy the cells as CSV) or “Save As” (to export the cells as CSV)
along the absence of support for XML, MARS, error handling (not even talking about spatial data!) to the way that in SQL Server 2000 DTS (Data Transformation Services) has its own mind of date/time format handing while importing stuff.
But it sure helps setting aims for the scheduled migration process to the far more current SQL Server 2008 R2 :)
(Mental note: migrating the SQL Server 2000 DTS packages to SQL Server 2008 R2 will be a challange).
Oh, some of the SQL Queries that come in handy when moving stuff around in an OTAP/DTAP environment:
1. Selecting relevant DTS packages (that are always in the MSDB database)
select name from msdb..sysdtspackages -- 2005: sysdtspackages90; 2008: sysssispackages where name like '%my-app-ID%'
2. Selecting relevant objects from a database (watch the xtype values that can exist in SQL Server 2000)
using my-database
select
name, xtype
from
sysobjects
where
name like '%my-app-id%'
and xtype in ('U', 'V', 'TR', 'P', 'X') -- tables/views/triggers/procedures/xprocedures only
order by xtype, name
Blast from the past :)
–jeroen
via: export sql query analyzer data to excel SQL Server.
PS: For the statistics, SQL Server 2000 has been EOL for a while; mainstream support ended in 2008, extended support ends in 2013.
The Wiert Corner - irregular stream of stuff 
Leave a comment