locked
Inserting 'Jul 23 2011 12:03PM' into a string variable and getting "Exception from HRESULT: 0xC0015005". RRS feed

  • Question

  • Team,

    What I'm doing wrong here?

    I'm trying to write from a Execute SQL Component into a string variable called "TempDBCreateDate"

    My SELECT statement below should insert the value from the #tmp table, into a single row resultset, pointed to a string variable called TempDBCreateDate

    SELECT

    CAST(cmd_output AS VARCHAR(30)) AS cmd_output FROM #tmp

    Results:
    cmd_output
    Jul 23 2011 12:03PM

    Error:
    [Execute SQL Task] Error: An error occurred while assigning a value to variable "TempDBCreateDate": "Exception from HRESULT: 0xC0015005".

    This should work, but it doesn't.

    Do you guys have any suggestions on how to fix this?


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Friday, August 12, 2011 3:07 PM

Answers

  • First, I don't see any parameters in your statement, so I'm not sure why you've configured the Parameters tab with anything.  If you want to make the connection dynamically connect to a server that you're specifying in the ServerName string, you do that with property expressions on the Connection Manager.

    Second, the #tmp table should be visible - you're only using it within the Execute SQL Task.

    Have you tried using a DateTime typed variable instead of a string?


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Eileen Zhao Friday, August 26, 2011 7:52 AM
    Friday, August 12, 2011 7:54 PM

All replies

  • Please describe how you have your Execute SQL Task configured.
    Todd McDermid's Blog Talk to me now on
    Friday, August 12, 2011 3:12 PM
  • General:

    Name: Check TempDB Create Date

    TimeOut: 0

    Code Page: 1252

    ResultSet: Single row

    ConnectionType: OLE DB

    Connection: DynnConn

    SQLSourceType: Direct input

    SQL Statement:

    CREATE TABLE #tmp
    (cmd_output datetime)

    INSERT INTO #tmp
    select crdate from master.dbo.sysdatabases where name = 'tempdb'
      
    --SELECT CAST(cmd_output AS VARCHAR(20)) AS cmd_output FROM #tmp
    --SELECT CAST(cmd_output AS VARCHAR(30)) AS cmd_output FROM #tmp
    --SELECT CONVERT(VARCHAR(30),cmd_output,100) AS cmd_output FROM #tmp
    SELECT cmd_output FROM #tmp

    BypassPrepare: True

    Parameter Mapping: --To populate the server name on the connection string and configure it dynamically

    VariableName: User::ServerName

    Direction: Input

    Data Type: VARCHAR

    Parameter Name: 0

    Parameter Size: -1

    Result Set:

    Result Name: 0

    Variable Name: User::TempDBCreateDate

     

    Error:

    [Execute SQL Task] Error: An error occurred while assigning a value to variable "TempDBCreateDate": "The type of the value being assigned to variable "User::TempDBCreateDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
    ".

     


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Friday, August 12, 2011 3:27 PM
  • As far as I remember once the connection is closed the #temp table is gone. Thus the remedy would be either to set retain connection property to true or not to use the temp tables.

    Picture describing this property: http://mcaf.ee/uxcb4


    Arthur My Blog
    By: TwitterButtons.com
    • Edited by ArthurZ Friday, August 12, 2011 3:55 PM added link to picture
    Friday, August 12, 2011 3:52 PM
  • The value from the select should still be inserted into the variable, don't you agree?


    --- Best Regards, Igor Santos - DBA igor.santos@citrix.com
    Friday, August 12, 2011 7:34 PM
  • Could have been inserted, but then you do a select from a table that perhaps is no longer accessible.

    Why not to do simply this:

    select crdate from master.dbo.sysdatabases where name = 'tempdb'
    
    ?


    Arthur My Blog
    By: TwitterButtons.com
    Friday, August 12, 2011 7:41 PM
  • First, I don't see any parameters in your statement, so I'm not sure why you've configured the Parameters tab with anything.  If you want to make the connection dynamically connect to a server that you're specifying in the ServerName string, you do that with property expressions on the Connection Manager.

    Second, the #tmp table should be visible - you're only using it within the Execute SQL Task.

    Have you tried using a DateTime typed variable instead of a string?


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Eileen Zhao Friday, August 26, 2011 7:52 AM
    Friday, August 12, 2011 7:54 PM