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
October 23, 2020 at 3:42 pm
How do I fix this?
And that should be it.
October 23, 2020 at 3:47 pm
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.
October 23, 2020 at 10:03 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 23, 2020 at 10:18 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 26, 2020 at 3:19 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy