Adatis BI Blogs

Reminder: Support for SQL 2000 expiry date

Not new news but a useful reminder! SQL 2000 will no longer be supported under standard support agreements after 8th April 2008 (or August 4th depending on how you read the date format!).  I'm guessing there is still a lot of DTS packages out there!

Scripting SQL Server 2000 Jobs

SQL Server Agent Jobs can be scripted with the following SQL DMO VB Script:   Dim conServer Dim fso Dim iFile Dim oJB Dim strJob Dim strFilename Const ioModeAppend = 8 Set conServer = CreateObject("SQLDMO.SQLServer") conServer.LoginSecure = True conServer.Connect "SQL2K_Server" strFilename = "c:\SQLJobs.sql" For Each oJB In conServer.JobServer.Jobs     strJob = strJob & "--------------------------------------------------" & vbCrLf     strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf     strJob = strJob & "--------------------------------------------------" & vbCrLf     strJob = strJob & oJB.Script() & vbCrLf Next Set conServer = Nothing Set fso = CreateObject("Scripting.FileSystemObject") Set iFile = fso.CreateTextFile(strFilename, True) iFile.Write (strJob) iFile.Close Set fso = Nothing

Automated SQL Server 2000 Data Dictionary

The two stored procedures below will help with automating the generation and maintenance of a database data dictionary.   If you provide a description for each database field in a SQL Database, these stored procedures can be used to extract that description information together with some other useful column details.   The first stored proc simply details the column information for a single table:   CREATE PROCEDURE uxp_Table_Data_Dictionary    @Table_Name varchar(50)AS -- Extract the table data dictionary SELECT     TABLE_NAME,    ORDINAL_POSITION,     COLUMN_NAME,     CASE DATA_TYPE         WHEN 'varchar' THEN 'varchar('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20))+')'        ELSE DATA_TYPE    END AS DATA_TYPE,    ISNULL(COLUMN_DEFAULT, '') AS COLUMN_DEFAULT,    IS_NULLABLE,    value AS PROPERTY_VALUE FROM    INFORMATION_SCHEMA.COLUMNS AS info         JOIN ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @Table_Name, 'column', default) AS extended ON info.COLUMN_NAME = extended.objname WHERE     TABLE_NAME = @Table_Name The second stored procedure extends this a little and reports the column information for all user tables within the current database:   CREATE PROCEDURE uxp_System_Data_DictionaryAS     -- Create a cursor containing all user tables     DECLARE @tableName varchar(40)      DECLARE table_cursor CURSOR FOR     SELECT name FROM sysobjects WHERE type = 'U' AND name <> 'dtproperties'     OPEN table_cursor      -- Perform the first fetch.     FETCH NEXT FROM table_cursor INTO @tableName      -- Check @@FETCH_STATUS to see if there are any more rows to fetch.     WHILE @@FETCH_STATUS = 0     BEGIN         -- Execute the table data dictionary generator         EXEC uxp_Table_Data_Dictionary @tableName          -- Fetch the next table         FETCH NEXT FROM table_cursor INTO @tableName    END      -- Tidy Up    CLOSE table_cursor    DEALLOCATE table_cursor