a*******t
发帖数: 891 | 1 Is there a way to get execution result back from Exec(@SQL) and store into a variable
I am doing this in SSIS, and all I am getting back is execution completed,
even if the real query in @SQL failed...
So I would like to write my own log with the result from exec(@SQL). |
j*****n
发帖数: 1781 | 2 have a temp table to capture the result if ur dynamic SQL returns data set.
eg.
CREATE TABLE #result
(rowCount int)
DECLARE @sql varchar(max)
SET @sql = 'select count(1) from tbl'
INSERT INTO #result
EXEC(@sql)
use @@rowcount if @sql stores a DML |
a*******t
发帖数: 891 | 3 I need to rephase my question. I want to get the error out of the execution.
So if the I do
SET @SQL = 'Wrong SQL command'
EXEC(@SQL)
and I want to capture the error message
There are some types of errors that'll stop the query execution, so I can't
even get the @@error after the execution.
Anyway, I am going to have to use the OnError event for that SQL Task in
SSIS. I was trying to avoid it because I have a lot of SQL Task in this
package.
Thanks anyway
【在 j*****n 的大作中提到】
: have a temp table to capture the result if ur dynamic SQL returns data set.
: eg.
: CREATE TABLE #result
: (rowCount int)
: DECLARE @sql varchar(max)
: SET @sql = 'select count(1) from tbl'
: INSERT INTO #result
: EXEC(@sql)
: use @@rowcount if @sql stores a DML
|
j*****n
发帖数: 1781 | 4 have you checked out sp_executesql ?
execution.
t
【在 a*******t 的大作中提到】
: I need to rephase my question. I want to get the error out of the execution.
: So if the I do
: SET @SQL = 'Wrong SQL command'
: EXEC(@SQL)
: and I want to capture the error message
: There are some types of errors that'll stop the query execution, so I can't
: even get the @@error after the execution.
: Anyway, I am going to have to use the OnError event for that SQL Task in
: SSIS. I was trying to avoid it because I have a lot of SQL Task in this
: package.
|
a*******t
发帖数: 891 | 5 was looking at it and then found out I need to make this work on SQL 2000 as
well because there are some servers will stay on 2000...
【在 j*****n 的大作中提到】
: have you checked out sp_executesql ?
:
: execution.
: t
|
j*****n
发帖数: 1781 | 6 sp_executesql works in 2000 too. M$ does not change anything for this sp.
as
【在 a*******t 的大作中提到】
: was looking at it and then found out I need to make this work on SQL 2000 as
: well because there are some servers will stay on 2000...
|
a*******t
发帖数: 891 | 7 that's right
but sp_executesql has the same behavior as exec(), in terms of, for certain
types of sql statment error, it won't continue with the rest of the
statments after it.
only Try Catch can solve this problem I guess
【在 j*****n 的大作中提到】
: sp_executesql works in 2000 too. M$ does not change anything for this sp.
:
: as
|
j*****n
发帖数: 1781 | 8 sp_executesql returns 0 if success, 1 if failure.
so that at least you will know the status of execution. |
a*******t
发帖数: 891 | 9 it behavies the same as exec()
just for example, if I do:
declare @sql nvarchar(512)
set @sql = 'update statistics abc'
exec sp_executesql @sql
select @@error
the query will fail because it cannot find table abc, it'll stop executing
before I can do the select @@error
the only way this can work is if
begin try
exec sp_executesql @sql
end try
begin catch
select @@error
end catch
but a business requirement is to consider SQL Server 2000, so, try catch is
out of the options
【在 j*****n 的大作中提到】
: sp_executesql returns 0 if success, 1 if failure.
: so that at least you will know the status of execution.
|
j*****n
发帖数: 1781 | 10 not sure what is going to happen in SSIS...
however, the select @@error is executed in query analyzer, even I can go
further steps...
eg. run following in query analyer:
declare @a nvarchar(200)
set @a = 'select * from abv'
exec(@a)
select @@error
print 'here is something'
it returned error 208, and the message box shows the following:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'abv'.
(1 row(s) affected)
here is something
【在 a*******t 的大作中提到】
: it behavies the same as exec()
: just for example, if I do:
: declare @sql nvarchar(512)
: set @sql = 'update statistics abc'
: exec sp_executesql @sql
: select @@error
: the query will fail because it cannot find table abc, it'll stop executing
: before I can do the select @@error
: the only way this can work is if
: begin try
|
a*******t
发帖数: 891 | 11 it's not because of SSIS
there are some types of errors that halt execution
a wrong Select statment will return error and continue execution
a wrong Update Stats or Rebuild Index, for example, will completely halt
further execution
I don't know the exactly which error types will continue, which ones will now. but I've observed those two types so far |
j*****n
发帖数: 1781 | 12 don't cry dude, be comfort. :)
I am doing DTS right now and will go for SSIS in the nearly future (2 months
? 3 months? later)... I hate to do those damn thing because VB/VB.net
scripts is killing me.
There is very high possibility that I am going to ask you dude questions
here.
LOL
now. but I've observed those two types so far
【在 a*******t 的大作中提到】
: it's not because of SSIS
: there are some types of errors that halt execution
: a wrong Select statment will return error and continue execution
: a wrong Update Stats or Rebuild Index, for example, will completely halt
: further execution
: I don't know the exactly which error types will continue, which ones will now. but I've observed those two types so far
|
j*****n
发帖数: 1781 | 13 dude, I got similar problem while I am creating a DTS package today.
my solution is to write a stored procedure and use a OUTPUT parameter to
return the error information back to Execute SQL Task.
Hope your SSIS can do it as well. |
