Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

execute SQL statements inside Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, May 26, 2010 9:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 755, Visits: 1,075
Hi,

I have a application that executes scripts (.SQL).
The user tells to the app the path where the script is and the app opens the script and saves the script to a variable, and then , this app as a stored procedure to where the variable value (the script text) is passed.

This works very well, but i have a problem....

When i have script that have inside stored procedures like :

EXEC sp_dbcmptlevel @dbname, 90


The app returns an error, because the sp_dbcmptlevel can only be executed in ad hoc context....


I do not what to send my SQL statments without being to a stored procedure, but not everything can be executed from inside a stored procedure....

Can you please help on what to do?

Thank you.

Post #928390
Posted Wednesday, May 26, 2010 10:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
I think you'd have to execute some of these steps separately. These commands aren't usually meant to be run in a proc, or often, as they make a substantial change to the way the db works. You typically set this once and leave it alone.

And you should use ALTER DATABASE instead.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #928421
Posted Wednesday, May 26, 2010 10:12 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 755, Visits: 1,075
yes, but this is an app that as to execute any text that is inside .SQL script.
That's the problem....
Post #928433
Posted Wednesday, May 26, 2010 10:15 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 755, Visits: 1,075
it's like:

if you can run it (.sql script) from query window in MS, then, this app as to execute it too.

I don't know if the best thing to do is pass the script text into a stored procedure, because there will be some commands that can not be inside a stored procedure...


Post #928436
Posted Wednesday, May 26, 2010 10:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
You can parse out things possibly that can't run. There aren't a lot of commands. I would question the need for this particular command.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #928447
Posted Wednesday, May 26, 2010 10:37 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 755, Visits: 1,075
there is no need for this particular command , but the client (to who i'm making this app) needs that the app runs every thing that can be run from inside query window...
Because programmers will use this app to run scripts agains databases...


Post #928452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse