Post reply

conversion error

  • August 24, 2010 at 8:04 pm

    #225186

    Hi all,

    I'm running this select from a table test but it fails with this error..

    select name, pwd from test

    name (varchar) pwd (varchar)

    -----------------------------------

    Mary At connect

    select datalength(name) + left (pwd,4) from test

    Msg 245, Level 16, State 1, Line 2

    Conversion failed when converting the varchar value 'My' to data type int.

    But the above sql should return '4At c'

    Please help me.Thanks much

  • August 24, 2010 at 9:51 pm

    #1211432

    The datalength function returns an integer. The left function returns a character string. SQL Server is attempting an implicit data conversion of the result of the left function to the datatype of the data length function.

    This would work if the string from the left function happened to be a number - in your scenario it isn't. Hence, the conversion error.

    To correct this, cast the result from the datalength to either char or varchar and concatenate that to the LEFT

    e.g.

    SELECT CAST (DATALENGTH (YourColumn) AS VARCHAR (10)) + LEFT (YourColumn, 4) FROM yourtable

  • August 25, 2010 at 12:34 am

    #1211471

    DECLARE @T TABLE ( NAME VARCHAR(20), PWD VARCHAR(20))

    INSERT INTO @T

    SELECT 'Mary' ,'At connect'

    select CAST(datalength(name) AS VARCHAR) + left (pwd,4) from @T

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • August 25, 2010 at 7:39 am

    #1211635

    Thank you so much..

    I'm executing this as a dynamic sql in a procedure. I'm trying to see the dynamic build sql by 'print @sql' but when I create the procedure in management studio the procedure creates successfully but I'm not able to see the built dynamic sql..

    CREATE proc [dbo].[testproc] as

    declare @roleval varchar(20)

    declare @sql nvarchar(3000)

    declare @rolepwd varchar(30)

    select @roleval = name, @rolepwd = cast(datalength(name) as varchar(20) + left(pwd,2) from test;

    set @sql = 'create application role ' + @roleval + ' with password' + '='' + @rolepwd + '''

    print @sql

    execute sp_executesql @sql

    Is there any other way to print the dynamic sql in this code? thanks a lot

  • August 25, 2010 at 8:59 am

    #1211694

    You realise that

    select @roleval = name, @rolepwd = cast(datalength(name) as varchar(20)) + left(pwd,2) from test

    will only be meaningful if you have only one record in test since if you have multiple records it will only select the last one it finds. Also I think you are missing a quote either side of @rolepwd

  • August 25, 2010 at 9:14 am

    #1211707

    Thanks Steve.

  • August 25, 2010 at 9:35 am

    #1211737

    Looks to me like you are still missing a quote either side of @rolepwd

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

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