Solved

error trap in sql server 2008 r2

Posted on 2014-02-28
2
305 Views
Last Modified: 2014-02-28
I am importing data from another server into an archive database.  I'm doing it the "easy" way by executing a dynamic sql statement as such:

select @qst = 'SELECT * INTO ' + @schema + '.' + @table + ' FROM AS400.TMS.' + @schema + '.' + @table
	
execute(@qst)
	
select @err = @@ERROR

Open in new window



I'm doing this in a loop to move all the tables in a particular library (schema).  Here is the problem:

When the query fails it bombs out of the batch entirely, such as when the table has no columns.  I'm trying to catch the error and then continue on.  Any ideas on how to accomplish this?
0
Comment
Question by:dhenderson12
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
Comment Utility
Use BEGIN/TRY/CATCH new SQL functionality:

http://technet.microsoft.com/en-us/library/ms175976.aspx
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
Comment Utility
Create a table to catch the error and use this:
create table ErrTable(ID int identity, table_name varchar(100), err varchar(8000))
GO

-- LOOP here
select @qst = 'SELECT * INTO ' + @schema + '.' + @table + ' FROM AS400.TMS.' + @schema + '.' + @table


BEGIN TRY	

	execute(@qst)

END TRY
BEGIN CATCH

	INSERT INTO ErrTable(table_name, err)
	select  @schema + '.' + @table, @@ERROR

END CATCH

Open in new window

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now