October 16, 2015 at 5:14 am
Hello
I have a package with a variable @myDate
This is datatype Int32 and is set to 20100101
I then pass this through twice to an Execute SQL Task using Parameter Mapping (variable name - User::myDate; Parameter Name 0 and 1)
My code within the Execute looks like this
declare @transaction_date bigint
declare @accepted_date bigint
set @transaction_date = ?
set @accepted_date = ?
-- Delete #Temp if it exists
IF EXISTS (Select
1
FROM
tempdb.dbo.sysobjects (nolock)
WHERE
ID = OBJECT_ID(N'tempdb.dbo.#TempB') and
[Type] = 'U')
BEGIN
drop table #TempB
END
GO
-- Works to here
-- This fails
create table #TempB (ID int IDENTITY(1,1), [POLICY_TRANSACTION_SKEY] int)
go
I know it passes the values ok but it fails to create the table
I get:
SELECT..." failed with the following error: "Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Does anybody know why?
I've tried a lot of different approaches and can't seem to get this working
This includes amending the ResultSet value
Thanks
- Damian
October 16, 2015 at 5:25 am
I can't remember whether GO is valid inside an ExecuteSQL task, as it's not a valid SQL statement.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 16, 2015 at 5:28 am
Dropping your temp table is easier like this
if object_id('tempdb..#TempB', 'U') is not null
drop table #TempB
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 16, 2015 at 6:14 am
Perfect!
GO was the issue
Thanks
Damian.
- Damian
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply