Tuesday, 15 January 2013

sql server - Stop Running a Procedure in Batch File -


I am creating a deployment package which will run the stored procedure in VSS. I am able to do this but my problem is that I have at least 30 databases on which I need to deploy and I only have two databases for which there is no need to get the update. With this, I included the following code in the .sql file, which runs the batch file:

  like IF OBJECT_ID ('CONFIG') tap or DB_NAME () '% SampleDB%' Print 'This store is not a database, leaving this database. Set to return to SEEOXE (Select * FROM dbo.sysobjects WHERE ID = OBJECT_ID (N '[DBO]. [Sp_ThisIsMySampleProcedure]')) Drop Process [DBO]. [SP_ThisIsMySampleProcedure] Go Go SET QUOTED_IDENTIFIER OFF GO ANSI_NULLS OFF Go Go Process [DBO]. [Sp_ThisIsMySampleProcedure] - Everything else is as follows ...   

This SQL code runs entirely on SQL Server. Whenever I run it against sample DB, it automatically proceeds to check whether the process exists and after that everything is there.

But whenever I try to run against sample DB using my batch file, this indicates an error message, but ignore my return keyword If the process continues, then the message will look like this:

This store is not a database, leaving this database.
Message 3701, Level 11, State 5, Line 8
Can not leave the 'SP_ThisIsMySampleProcedure' process, because it does not exist or you do not have permission.

I understand the error message is correct because the process does not actually exist on my sample dB

thanks!

But why is it still going on when I have a return keyword that satisfies the state of leaving the whole script?

Thank you!

Edit:

Well, I think people do not understand me completely. This is the scenario: My process (see above) SQL Server works well on the management studio What do I mean when I try to run it on a sample DD, then gives me this message This store is not a database, leaving this database.

But whenever I try to run my batch-file which executes this archived process, I get this message at the command prompt:

This store is not a database, leave this database.
Message 3701, Level 11, State 5, Line 8
Can not leave the 'SP_ThisIsMySampleProcedure' process, because it does not exist or you do not have permission.

Basically this means that the batch-file will continue to execute the whole SQL script, even if the first condition is completed.

My question is how can I batch file that whenever SQL Server throws the message this store database is not leaving this database. The batch-file will immediately stop the execution of the SQL file.

First of all. The keyword GO divides the file into separate requests. Each request is processed separately by the server returns exits only the first request, other requests will be run.

Try it:

  1 Select Return 2 Select 3 Go   

Second, SET NOEXEC ON < / Code> Dangerous thing, it blocks the subsequent execution. Try it:

  Select 1 Select the return 2 on SEE NOEXEC SET NOEXEC OFF Go   

You can create a process on all servers, but Return to start from if the name of the database is something like this or you can delete GO and create a proc stored with dynamic SQL:

  If ' Like '% mydb%' 'BEGIN EXEC dbo.sp_executesql @statement = N' Create methodology [DBO]. Select BEGIN as 'my proc' 'END' END    

No comments:

Post a Comment