Saturday, April 4, 2009

See Unencrypted DTSRun Commands

If you come across encrypted DTSRun commands, here's a way to decrypt them.

Drop down to the command prompt and paste in the full DTSRun string:

C:\>DTSRun /~ZThisWouldBeYourBigUglyDTSRunCommandString /!X /!C

Everything after C:\> is directly from the Job's Command text box. You have to add /!X and /!C to the end. Hit Enter to run it and the results then end up in your "paste" buffer, so you can just Ctrl+V into Notepad or wherever.

This seems to fail if the DTS is connecting to a server not setup on your machine. You may see something like the error below. Just add the remote machine to Enterprise Manager.

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Thanks to the guys over at RDA Blogs for posting about this.

T-SQL SPROC to Re-Create a Full Text Catalog

I cannot claim any ownership over this code. It's a colleague's, but it's so handy that I have to document it somewhere.

This will create an SPROC to re-create a full text catalog, which of course can then be tied to a Job.


CREATE PROCEDURE p_RecreateFT_Articles

AS

ALTER TABLE [dbo].[Articles] WITH NOCHECK ADD
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[Article_ID]
) ON [PRIMARY]

IF EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE NAME = 'FT_Articles')
EXEC sp_fulltext_catalog 'FT_Articles', 'drop'

-- Create the catalog if it doesn't already exist.
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE NAME = 'FT_Articles')
EXEC sp_fulltext_catalog 'FT_Articles', 'create'

-- Add the full text index to the table
EXEC sp_fulltext_table '[dbo].[Articles]', 'create', 'FT_Articles', 'PK_Articles'

-- Add the columns to the full text index
EXEC sp_fulltext_column '[dbo].[Articles]', 'ArticleTitle', 'add'
EXEC sp_fulltext_column '[dbo].[Articles]', 'ArticleBody', 'add'

-- Activate the index
EXEC sp_fulltext_table '[dbo].[Articles]', 'activate'

-- Start population
EXEC sp_fulltext_catalog 'FT_Articles', 'start_full'

GO