[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question
[x]
Attachment Details

How to execute Alter/Create View scripts from an SP

Asked by Mach1pro in MS SQL Server

I have the following procedure that I use with VB/ADO to execute scripts and create tables. the script works great as long as I purge out the "GO" words from the scripting syntax before sending it as the SP parameter.

ALTER procedure [dbo].[SP_UpdateDBObjects]

       @str nText
AS
Exec(@str)


I want to use this SP to update views and other SPs as well, but the problem I run into is when I send the script text to my SP_UpdateDBObjects, if the GO statement is in the script then I get an error that says "Incorrect syntax near 'GO'.
If I purge out the 'GO' words from the parameter then I get an error that says 'CREATE VIEW' must be the first statement in a query batch.
[+][-]10/23/09 11:48 AM, ID: 25647190Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/09/09 11:08 AM, ID: 25778932Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091118-EE-VQP-93 - Hierarchy / EE_QW_3_20080625