error-checking all in one place

In my SQL 2000 stored proc I have a few dozen 'UPDATE mytable...'
statements, all updating the same table. I'd like to do error-checking
for these UPDATE statements all in one place, and I figured I'd create a
separate proc. I found out quickly that this is a bad idea, but my plan
was to pass two args to the other proc: (1) the column name and (2) the
desired new value. For all sorts of reasons, this fails badly. What's
the best way to do this? Thanks.Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> In my SQL 2000 stored proc I have a few dozen 'UPDATE mytable...'
> statements, all updating the same table. I'd like to do error-checking
> for these UPDATE statements all in one place, and I figured I'd create a
> separate proc. I found out quickly that this is a bad idea, but my plan
> was to pass two args to the other proc: (1) the column name and (2) the
> desired new value. For all sorts of reasons, this fails badly. What's
> the best way to do this? Thanks.
Error-check after each statement. Error-checking in SQL 2000 is a tedious
task. It's a lot better in SQL 2005.
For some more detailed tips I have an article on my web site:
http://www.sommarskog.se/error-handling-II.html.
And don't pass column or table names as parameters. That will leave you
in a quagmire of dynamic SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I have a few dozen 'UPDATE mytable...' <<
Try to combine the UPDATEs into one statement (or as few as possible)
using CASE expressions, subqueries, etc. Constraints on the base table
should be doing the data scrubbing for you.|||Thanks for this excellent article. So there's no way (other than using
dynamic SQL) that will allow me to put my UPDATE statements in a
separate object where I can then do my error-checking? (Sorry if I'm
being repetitive!)
In article <Xns97D35E5686AD1Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Error-check after each statement. Error-checking in SQL 2000 is a tedious
> task. It's a lot better in SQL 2005.
> For some more detailed tips I have an article on my web site:
> http://www.sommarskog.se/error-handling-II.html.
> And don't pass column or table names as parameters. That will leave you
> in a quagmire of dynamic SQL.
>
>|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks for this excellent article. So there's no way (other than using
> dynamic SQL) that will allow me to put my UPDATE statements in a
> separate object where I can then do my error-checking? (Sorry if I'm
> being repetitive!)
Well,
UPDATE tbl
SET col1 = CASE @.column WHEN 'col1' THEN @.value ELSE col1 END,
col2 = CASE @.column WHEN 'col1' THEN @.value ELSE col2 END,
..
But if the columns are different data type, you will have to deal
with that in the CASE statement. You could pass @.value as sql_variant,
and have an explicit convert in each CASE.
But by now, having multiplied error-checking is starting to sound really
palatable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error: unable to retrieve column information from the data source

Hi,

I am trying to set up a data flow task. The source is "SQL Command" which is
a stored procedure. The proc has a few temp tables that it outputs the final
resultset from. When I hit preview in the ole db source editor, I see the
right output. When I select the "Columns" tab on the right, the "Available
External Column List" is empty. Why don't the column names appear? What is
the work around to get the column mappings to work b/w source and
destination in this scenario.

In DTS previously, you could "fool" the package by first compiling the
stored procedure with hardcoded column names and dummy values, creating and
saving the package and finally changing the procedure back to the actual
output. As long as the columns remained the same, all would work.
Thats not working for me in SSIS.

Thanks in advance.
Asim.

Anyone?

|||I am having the same problem. I assume it is because the stored procedure I am using is long running (1min 20s) The designer looks like it starts to run the sproc to get the metadata, but then gives up after 15 or 30 seconds. Any solutions?|||

Hi,

If your final select statement is for a temporary table, u will get this problem. Use table variable or actual table instead of temp table.

|||

Yes, I figured out a solution. Basically create a hard coded resultset (select stmt) at the top of the procedure with the same columns as the actual resultset.

That did it for me.

Asim.

|||

Ok, that solution does not work. By adding the header, the package compiles and runs, but the row from the header is inserted in the destination, not from the (actual) second resultset. So I am back to square one.

Can anyone please help.

Asim.

|||This solved my problem. However, it was not enough to change the last select from a temp table to table variable. I had to change all occurences of temp tables to table variables. I also found that it sped up my query by a factor of 16x from 1min 20s to 5s.|||My workaround is to create output columns that are being returned by the query manually.