execute SQL statements inside Stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » execute SQL statements inside Stored...

execute SQL statements inside Stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Wednesday, May 26, 2010 9:36 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Steve Jones - SSC Editor

Posted Wednesday, May 26, 2010 10:05 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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

river1

Posted Wednesday, May 26, 2010 10:12 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

yes, but this is an app that as to execute any text that is inside .SQL script.
That's the problem....

Post #928433

river1

Posted Wednesday, May 26, 2010 10:15 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Steve Jones - SSC Editor

Posted Wednesday, May 26, 2010 10:33 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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

river1

Posted Wednesday, May 26, 2010 10:37 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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 »

Permissions