Re: A peculiar problem

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 06/11/04 

Date: Fri, 11 Jun 2004 08:50:59 -0500

The thing that bothers me about this question is:

> The catch is, I can't use any of the database API's like ADO, VBscript
etc.

Why not? Because SQL is not the way to do this. Even find and replace
would be easier.

> I need to alter data types in all my stored procedures and triggers using
TSQL(~1000 in number)
> and then to execute them. But it's not only the parameter data types which
need to be changed,
> it's all the variables, temp tables etc present in the entire text of a
stored procedure.I am doing the following:

Text manipulation in SQL Server is very hard, if not impossible. But sounds
of either bad management or insane punishment homework to me :) 

-- 
----------------------------------------------------------------------------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Sheetal" <Sheetal@discussions.microsoft.com> wrote in message
news:9DE82548-8B5D-406E-A965-B9CA2A45A0FD@microsoft.com...
> Hello newsgroup,
>
> I have a problem quite specific to my project.The catch is, I can't use
any of the database API's like ADO, VBscript etc.I need to alter data types
in all my stored procedures and triggers using TSQL(~1000 in number) and
then to execute them. But it's not only the parameter data types which need
to be changed, it's all the variables, temp tables etc present in the entire
text of a stored procedure.I am doing the following:
> 1.Create a batch file with isql utility using an input file and putting
the result in an output file.My input file does the following:
> a. List all the SP's in a temp table.
> b. Use cursors to print the text of each stored procedure one by one
(sp_helptext)
> c. Use a SP to serach and replace the regular expressions
> d. Concatenate the modified syntax into one single line and assign it to a
text variable (declared a 3rd temp table) usinf 'textptr' and 'updatetext'
>
> 2. Export the generated file(with the modified stored procedures) using
isql to be executed.
>
> Only the problem is that they can't be executed since a text variable has
been used.And EXEC does not allow that.
> I'd appreciate if you could help me out on this.
> Sheetal Verghese.
Quantcast