Post reply

SQL 2008 R2 convert styles running 90 database

  • August 11, 2010 at 1:11 am

    #236477

    Hi guys,

    I have found some issue running a SQL 2005 database in a SQL 2008 R2 instance. the issue is related with the convert function.

    When try to excutte this query:

    OPEN SYMMETRIC KEY KeyXXX DECRYPTION BY CERTIFICATE CertXXXX

    select

    CONVERT(VARCHAR,decryptByKey(e.Fecha_nacimiento),20)

    from T_CMS__EDUCAMOS_Usuarios e

    I get

    The style 20 is not supported for conversions from varbinary to varchar.

    So by moment I only go to move the database in the actual version to SQL 2008 R2, I need to know if I can remain the actual code and only change some setting in the database or instance support this kind of paramater, without change the code of the stored procedures.

    Thanks in advance.

    Francisco Racionero
    twitter: @fracionero

  • August 11, 2010 at 2:35 am

    #1205201

    I think the answer is no, not without changing the code. This error is not suppressed by changing the compatibility mode.

    Style codes are used to convert dates to a particular string format, why are you trying to apply one to a VARBINARY output?

    It was a bug that SQL 2005 allowed you to do this and SQL 2008 (and R2) correctly give you an error when you try to do this, so you should change your code.

  • August 11, 2010 at 2:46 am

    #1205211

    I'm afraid that, but that's the reason to stay in 90 version of databse.

    Francisco Racionero
    twitter: @fracionero

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

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