Post reply

Multiple-step OLE DB issue

  • October 16, 2015 at 5:14 am

    #309006

    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

    #1833749

    I can't remember whether GO is valid inside an ExecuteSQL task, as it's not a valid SQL statement.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 16, 2015 at 5:28 am

    #1833751

    Dropping your temp table is easier like this

    if object_id('tempdb..#TempB', 'U') is not null

    drop table #TempB

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 16, 2015 at 6:14 am

    #1833767

    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