[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Catch context switch failing

bob

3/19/2007 1:19:00 PM

I've encountered a problem where a database context switch fails ( ie USE
statement ) and scripts are ploughing on. What's a good way to prevent this
from happening?

Thanks

wBob

Example, with multiple batches:
USE master
GO

-- Do something in master ...

USE _otherdatabase
GO

-- Context switch has failed;

-- Oops we're still in master and script is ploughing on
CREATE TABLE OopsImInMaster ( test_id INT PRIMARY KEY )
GO

-- Cleanup
DROP TABLE OopsImInMaster
GO
4 Answers

Mubashir Khan

3/19/2007 1:52:00 PM

0

i always prefix my tables with the database name .....
like ......
Northwind..Authors

"Bob" <Bob@discussions.microsoft.com> wrote in message
news:68A33800-0B22-4C38-B528-E4C3312B3855@microsoft.com...
> I've encountered a problem where a database context switch fails ( ie USE
> statement ) and scripts are ploughing on. What's a good way to prevent
> this
> from happening?
>
> Thanks
>
> wBob
>
> Example, with multiple batches:
> USE master
> GO
>
> -- Do something in master ...
>
> USE _otherdatabase
> GO
>
> -- Context switch has failed;
>
> -- Oops we're still in master and script is ploughing on
> CREATE TABLE OopsImInMaster ( test_id INT PRIMARY KEY )
> GO
>
> -- Cleanup
> DROP TABLE OopsImInMaster
> GO


Roy Harvey

3/19/2007 2:08:00 PM

0

On Mon, 19 Mar 2007 08:52:15 -0500, "Mubashir Khan" <mubi@yahoo.com>
wrote:

>i always prefix my tables with the database name .....
>like ......
>Northwind..Authors

That is a good idea in scripts. It is a bad idea in a stored
procedure, view or trigger within the same database. In other words,
within the Northwind database no stored procedure, view or trigger
should use the Northwind qualifer. The problem this prevents is that
if a database is restored to another name, say with a _Temp suffix to
allow recovering some data, the stored procedure (or view or trigger)
in the _Temp copy will be referencing the original database, not
itself.

Roy Harvey
Beacon Falls, CT

bob

3/20/2007 1:36:00 PM

0

OK, raising an error of 20 stops the script dead.

RAISERROR( 'CREATE DATABASE failed. Script halting.', 20, 1 ) WITH LOG

You need sysadmin permissions for this.

wBob


"Bob" wrote:

> I've encountered a problem where a database context switch fails ( ie USE
> statement ) and scripts are ploughing on. What's a good way to prevent this
> from happening?
>
> Thanks
>
> wBob
>
> Example, with multiple batches:
> USE master
> GO
>
> -- Do something in master ...
>
> USE _otherdatabase
> GO
>
> -- Context switch has failed;
>
> -- Oops we're still in master and script is ploughing on
> CREATE TABLE OopsImInMaster ( test_id INT PRIMARY KEY )
> GO
>
> -- Cleanup
> DROP TABLE OopsImInMaster
> GO

Tibor Karaszi

3/20/2007 5:26:00 PM

0

Another option is to do raiserror with *state* 127. Some tools, like OSQL and probably SQLCMD will
terminate on that.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"Bob" <Bob@discussions.microsoft.com> wrote in message
news:8E0F4D95-2EBC-4298-8988-3B443B90AB5F@microsoft.com...
> OK, raising an error of 20 stops the script dead.
>
> RAISERROR( 'CREATE DATABASE failed. Script halting.', 20, 1 ) WITH LOG
>
> You need sysadmin permissions for this.
>
> wBob
>
>
> "Bob" wrote:
>
>> I've encountered a problem where a database context switch fails ( ie USE
>> statement ) and scripts are ploughing on. What's a good way to prevent this
>> from happening?
>>
>> Thanks
>>
>> wBob
>>
>> Example, with multiple batches:
>> USE master
>> GO
>>
>> -- Do something in master ...
>>
>> USE _otherdatabase
>> GO
>>
>> -- Context switch has failed;
>>
>> -- Oops we're still in master and script is ploughing on
>> CREATE TABLE OopsImInMaster ( test_id INT PRIMARY KEY )
>> GO
>>
>> -- Cleanup
>> DROP TABLE OopsImInMaster
>> GO