Post reply

Error arthematic operations inside varchar @variable

  • September 30, 2013 at 5:43 am

    #299302

    Hi I'm getting conversion error in the messages while executing below query.

    Please help me understand this.

    DECLARE @Linkedserver sysname = 'DataServerSeven';

    DECLARE @Databasename sysname = 'dbVinnyStaging';

    Declare @DeltaSizeInMB BIGINT

    Declare @EDWSourceSystemID INT = 4100

    Declare @SourceSystemID INT = 4100

    DECLARE @DeltaSize Table (DeltaSize BIGINT)

    Declare @DeltaSizeSQL Varchar(MAX)

    Declare @TempBatchNumber INT = 1736

    Set @DeltaSizeSQL =

    'Select SUM(A.DeltaCount * B.AvgRecordSize) from '+@Linkedserver+'.'+@Databasename+'.dbo.parameterhistory A

    Join Operations..DatasetTables B (Nolock) ON A.ReferenceObjectName = B.TableName

    where A.SourceExecutionKey in ('+@TempBatchNumber+') and A.SourceSystemID in ('+@SourceSystemID+') and B.EDWSourceSystemID in ('+@EDWSourceSystemID+');'

    Insert INTO @DeltaSize

    EXECUTE sp_executesql @DeltaSizeSQL

    SelecT @DeltaSizeInMB = (Select sum(cast(DeltaSize AS Numeric(15,4))/1024/1024) from @DeltaSize)

    select @DeltaSizeInMB

    Msg 245, Level 16, State 1, Line 9

    Conversion failed when converting the nvarchar value 'Select SUM(A.DeltaCount * B.AvgRecordSize) from DataServerSeven.dbVinnyStaging.dbo.parameterhistory A

    Join Operations..DatasetTables B (Nolock) ON A.ReferenceObjectName = B.TableName

    where A.SourceExecutionKey in (' to data type int.

  • September 30, 2013 at 6:20 am

    #1654508

    Cast following INT variables to varchar.

    @TempBatchNumber

    @SourceSystemID

    @EDWSourceSystemID

    Set @DeltaSizeSQL =

    'Select SUM(A.DeltaCount * B.AvgRecordSize) from '+'.'+@Databasename+'.dbo.parameterhistory A

    Join Operations..DatasetTables B (Nolock) ON A.ReferenceObjectName = B.TableName

    where A.SourceExecutionKey in ('+cast(@TempBatchNumber as varchar(4))+') and A.SourceSystemID in ('+cast(@SourceSystemID as varchar(4))+') and B.EDWSourceSystemID in ('+cast(@EDWSourceSystemID as varchar(4))+');'

  • September 30, 2013 at 6:22 am

    #1654509

    Set @DeltaSizeSQL =

    'Select SUM(A.DeltaCount * B.AvgRecordSize) from ' + +@Linkedserver +'.'+@Databasename+'.dbo.parameterhistory A Join Operations..DatasetTables B (Nolock) ON A.ReferenceObjectName = B.TableName

    where A.SourceExecutionKey in ('+cast(@TempBatchNumber as varchar(4))+') and A.SourceSystemID in ('+cast(@SourceSystemID as varchar(4))+') and B.EDWSourceSystemID in ('+cast(@EDWSourceSystemID as varchar(4))+');'

  • September 30, 2013 at 6:28 am

    #1654513

    Thanks a lot.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply