March 5, 2010 at 1:59 am
Hello,
I'm checking wheather the query return data from database. every loop, i pass in a query through a variable to check, if no result, it will update the variable 0 and 1 if data return.
I'm executing this using Execute SQL Task using the below syntax. this Execute SQL Task was put inside a For loop object. This Execute SQL Task is using OLE connection.
DECLARE @Query VARCHAR(8000);
DECLARE @DetectionQuery VARCHAR(8000);
DECLARE @err int = 0;
set @Query = ?
SET @DetectionQuery = 'IF Not Exists ( ' + @Query + ' ) Select 0 [NewResultName] ELSE Select 1 [NewResultName] '
Begin try
exec(@DetectionQuery)
end try
begin catch
SELECT @err = @@error
IF @err <> 0
SELECT 2 [NewResultName]
end catch
I'm getting the below error. I'm using the same process in another server (ms sql enterprise edition) and it is running fine. But it is not running in new server (ms sql standard edition).:( Is there any issue in SSIS or missing any Software.
I appreciate your suggestion on my issue.
failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
March 5, 2010 at 6:55 am
What is the resultset set as?
March 5, 2010 at 6:57 am
It could be that you may be getting an empty result set vs a NULL result set, which is causing the error.
See this blog for more details
http://sqlscape.blogspot.com/2009/04/execute-sql-task-failure-possible.html
March 7, 2010 at 8:21 pm
my resultset was set to single row.
my query was set,
it return 0 when no result, and return 1 if there is result.
i even try to run in on the server manually and it return me 0.
any idea why??
March 7, 2010 at 8:55 pm
i remove all the codes and change the code to only
exec(?)
if came back with the same syntax error, i guess something to do with the parameter passing.
It works properly in the other Two server(ms sql enterprise) but not this server (standard edition)..
would it becuase ms sql version?? or there is other cause??
March 7, 2010 at 11:41 pm
I guess i found the problem..
it because of the RTM version i am using that has a bug in Bypassprepare. see page below.
http://killspid.blogspot.com/2007/01/ssis-bypassprepare.html
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply