August 13, 2003 at 8:06 pm
Let's say you have about a million SQL scripts and almost that many DTS packages, and a half millinog VC++, VB and similar programs. Well, maybe not literally, but it seems so...
Someone says "let's change this field in this table, make it a string and change it's name.
Once you get out of shock, you start searching and find it's easy -- search the scripts, search the .VB, .VCC files, a few other files maybe (+/- how you code). It's all going well, you make your changes, modify the scripts, modify the database, and...
All hell breaks loose when you find that DTS packages are failing. SQL steps in DTS scripts were forgotten. So you search the DTS files... ooops, nothing found. You can't search (at least with explorer or anyting I've tried) and find the contents of SQL statements in the DTS files.
How do you find out what you need to change?
- You can script all DTS packages to VB and search them.
- You can implement only stored procedure calls in SQL steps in DTS packages (if you thought of this in advance) so in searching your SP's you find it. BUT, this is really only a level of abstraction, you still can't find all the DTS packages that used the SP's by searching.
- Maybe there's some way to use all the repository stuff, but frankly after trying to figure out what it was for a couple times, I've ignored it. Haven't run across anyone else who understood it (I'm sure some here do, so maybe I'll learn something).
WHat's your approach to doing this? How do you find everywhere your DTS packages reference a SQL object?
August 13, 2003 at 8:48 pm
One way would certainly be to save them out as VB files and search the code in those files...
hth,
Michael
Michael Weiss
Michael Weiss
August 14, 2003 at 2:36 am
Hi there
What I do:
a) get latest version from VSS - search for the string
b) our sp sare not encrypted, so a simple text search over this (see script below)
c) DTS - like you say, unload them as vb files and search
its a boring and lengthly process overall and havent found tools to speed or enhance it.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 14, 2003 at 2:37 am
CREATE PROCEDURE usp_FindCodeStr
@dbname varchar(100),
@SearchStr varchar(1000),
@PrintOnly bit = 0
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SELECT
@sql = ISNULL(@sql + char(13) + 'UNION ALL' + char(13), '') +
'SELECT DISTINCT ''' + name + ''' as db, o.name
FROM ' + name + '.dbo.sysobjects o
JOIN ' + name + '.dbo.syscomments c
ON o.id = c.id
WHERE text like ''%' + @SearchStr+ '%''
/* Elminate system objects & VSS objects */
AND name not like ''sys%''
AND name not like ''dt/_%'' ESCAPE ''/''
AND name not like ''fn/_%'' ESCAPE ''/'''
FROM sysdatabases
WHERE name IN(@dbname)
IF @PrintOnly = 1
PRINT @sql
ELSE
EXEC(@sql)
GO
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply