Post reply

TSQL Not doing what I expect

  • September 15, 2014 at 1:06 pm

    #311615

    I'm trying to run this TSql so I can enable/disabled jobs in MSDB: use msdb;

    go

    select 'exec dbo.sp_updateJob @job_name = N' + (Char(39) + name + char(39) + ',') + '@enabled = ' + enabled from sysjobs

    When I run the script, I get the following error, what I'm I doing wrong?:

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the nvarchar value 'exec dbo.sp_updateJob @job_name = N'MyJobName',' to data type tinyint.

    I'm not trying to convert anything.

    Thanks

  • September 15, 2014 at 1:09 pm

    #1745578

    use msdb;

    go

    select 'exec dbo.sp_updateJob @job_name = N' + (Char(39) + name + char(39) + ',') + '@enabled = ' + CAST(enabled AS CHAR(1)) from sysjobs

    Cast the integer data type column (tinyint in this case) to char, otherwise the implicit conversion is string to int, which will fail.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 15, 2014 at 1:14 pm

    #1745580

    Thanks GilaMonster, that did the trick for me.

  • September 15, 2014 at 1:27 pm

    #1745584

    It's because + is both the addition operator and concatenation operator.

    When you say String + Int, SQL assumes you want addition, so it does CAST(String AS INT) + INT. If you want concatenation, you have to make sure that all the values you're concatenating are strings. Enabled, the column, is a tinyint.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 15, 2014 at 2:33 pm

    #1745603

    Quick thought, use the CONCAT function, no need to worry about overlapping operators

    😎

    DECLARE @INT INT = 10000;

    DECLARE @TINYINT TINYINT = 128;

    DECLARE @VARCHAR VARCHAR(10) = 'ABC';

    SELECT CONCAT(@INT,@TINYINT,@VARCHAR);

    Returns

    10000128ABC

  • September 15, 2014 at 4:32 pm

    #1745630

    Thanks everyone for the help and suggestions.

Viewing 6 posts - 1 through 5 (of 5 total)

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