Solved

error trap in sql server 2008 r2

Posted on 2014-02-28
2
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 250 total points
ID: 39895935
Use BEGIN/TRY/CATCH new SQL functionality:

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

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 39896105
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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