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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-20 : 08:29:27
|
| michael writes "Hellohope you can help...I've got a SP which accepts a string representing a table name in a Progress DB.I want to copy the table into SQLServer using SQL (its way faster than DataPumps etc)This is the SP below...it uses a linked server to connect to Progress with an ODBC DSNCREATE PROCEDURE [dbo].[xload_table]@tblname varchar(50) -- Input parameter representing name of table to load from linked server*/ASDECLARE @strsql varchar(200) SET @strsql = "Select * into " + @tblname + " FROM OPENQUERY(MultiLive, 'Select * from " + @tblname + "')"-- if input parameter is 'orders' then @strsql looks like Select * into orders FROM OPENQUERY(MultiLive, 'Select * from orders')EXEC(@strsql)GOProblem is the Progress datetime type has a much wider range than SQLServer. If a column in the Progress table is out of SQLServer range and I execute the following SQL in QueryAnalyzer then I get an error message ....Msg8114,Level16,State10,Line1 Error converting datatype DBTYPE_DBDATE to datetime.....and the table fails to load.....fair enoughexec xload_table 'orders'goif @@error <>0 insert into tblLoadFailures values('orders',@@error,getdate())goHowever, at least the IF statement catches the error and writes it to a table that lists tables that failed to load. However, my real problem is that if the SQL above is executed in a DTS Execute SQL Task then the task fails and it appears that the IF statement isn't executed. Why does this SQL work in QueryAnalyzer but not the DTS SQL Task. (Windows2000 SP2 SQL2000 SP2 @@version 8.00.534)CheerMike" |
|
|
|
|
|
Error Handling by DTS Exeute SQL Task