try... catch blocks with use [database]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

try... catch blocks with use [database]

Author

Topic

AussieDev
Starting Member

2 Posts

Posted - 2010-10-07 : 19:59:43

Hi All,

Please bear with me, I'm an Oracle programmer, trying to hack my way through a SQL server 2005 script written by a previous developer.

The script in question performs a lot of data transformations accross multiple databases and I would like to add some error handling around the entire thing so that if anything fails anywhere, everything is rolled back.

I'm having issues at the moment with putting a try..catch block around everything.

for example:

begin transaction

use [database_name]
go
begin try

works, but there are multiple USE statements in the script
and this:


begin try
use [database_name]
go

fails with a syntax error.

all I want to do is stop SQL server from happily carrying on with other statements after something bad goes wrong.... how can I do this?

sorry again for asking what is probably a very basic question.

AussieDev
Starting Member

2 Posts

Posted - 2010-10-07 : 20:33:17

Also, another question:

I have two variables at the start of the script:


DECLARE @sqlstmt varchar(500)
DECLARE @filepath varchar(100)

if I run the script and it fails somwhere, then next time I run the script I get :


Msg 134, Level 15, State 1, Line 240
The variable name '@sqlstmt' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 134, Level 15, State 1, Line 241
The variable name '@filepath' has already been declared. Variable names must be unique within a query batch or stored procedure.

but if I comment out the variables for the second attempt, it complains that they havn't been declared!

am I missing something very obvious here? how can I make variables and temporary tables exist only for the duration of the running of the script?

I realise that using SQL server management studio to execute scripts like this is similar to SQL*Plus for oracle and for these requirements I'd normally in Oracle I'd write an annonymous block (a sort of on-the-fly stored procedure) is there an equivalent I can do in SQL server to encapsulate a set of transactions and all bits and pieces related to them in one block of stand-alone logic?

Gayathri.Varadarajan
Starting Member

2 Posts

Posted - 2010-10-08 : 03:12:55

Hi,
1.The go statement ends a batch and hence the syntax error.

You can switch between databases with use statement without go
as
begin try
use db1
select * from tablex
use db2
select * from tabley
end try
begin catch
select 'error'
end catch

For your second query :attach the erroreous script.
Post separate questions in separate forum.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-08 : 04:45:36

Or if all the databases are on the same server you can rewrite all the queries to use 3 part naming conventions. [servername].[databaseName].[schemaname].[table]

I recognise that this would probably be some work!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources