SQL_variant to varchar error

  • DECLARE @Temptable TABLE
    (
    TotalPhysicalMemory varchar(1000),
    MemoryValueinUse varchar(1000),
    MaxMemorySettingsInUse varchar(1000)
    )
    insert into @Temptable
    select
    A1.[TotalPhysicalMemory],A1.MemoryValueinUse,A1.[MaxMemorySettingsInUse]
    from
    (
    select
    (select total_physical_memory_kb/1024 from sys.dm_os_sys_memory) as 'TotalPhysicalMemory',
    (select cast(value_in_use as int) from sys.configurations where name like '%max server memory%') as 'MemoryValueinUse',
    (select case
    when value_in_use = 2147483647 then 'Default Memory Settings - needs to be configured'
    else value_in_use end as SQLMemoryInUse
    from sys.configurations
    where name like '%max server memory (MB)%') as MaxMemorySettingsInUse
    )
    A1
    select * from @Temptable

    Msg 257, Level 16, State 3, Line 7

    Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

    How do I fix this.Thanks

  • How do I fix this?

    1. Find out which of the columns you are referencing is of type SQL_VARIANT
    2. Use the CONVERT function to convert them to VARCHAR

    And that should be it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Have you tried using CAST or CONVERT to pick a data type on the column that has an unknown datatype?  Not the best solution, but will solve the problem.

    What I would recommend is having a 4th column indicating if the memory setting is default or not rather than having 2 possible datatypes combing back in a column.

    A quick look at that, your MaxMemorySettingInUse column is the problem as that is returning either an INT or a CHAR so SQL doesn't have a way to know what to return, so it is picking SQL_Variant.  You will need to cast/convert it before putting into the table, either at the first SELECT or the nested SELECT, assuming you don't want to add the 4th column to the table.

    Alternately, you could make the column in @Temptable sql_variant, but I would not advise this as it is bad practice.  sql_variant is similar to NULL values - it can be any datatypes and it can make doing anything on the column more complicated or impossible.  A lot of operations are not valid on a sql_variant data type.

     

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • You can fix the immediate problem by changing the code like so:

    else cast(value_in_use as varchar(50)) end as SQLMemoryInUse

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I believe the error was because the first data type returned by the CASE is a varchar -- specifically 'Default Memory Settings - needs to be configured' -- and thus SQL was trying to convert the next value, value_in_use, to a varchar, in order to match the first value. However, SQL, by its own rules, cannot implicitly (automatically) convert a sql_variant to a varchar.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • This was removed by the editor as SPAM

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

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