September 4, 2013 at 6:55 am
Hi,
I'm writing a script that will gather server information from different versions of SQL, starting from 2005.
I cannot run the following query due to one or the other column not existing in the current version of SQL.
SELECTCASE
WHEN RIGHT(LEFT(@@VERSION, 25), 4) >= 2012
THEN ROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2)
ELSE ROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2)
END MemGB
FROMsys.dm_os_sys_info
Is it possible to turn off parsing in a HINT or SET?
Couldn't find anything here, but maybe someone somewhere knows a sneaky trick
http://technet.microsoft.com/en-us/library/ms190356.aspx
http://technet.microsoft.com/en-us/library/ms187713.aspx
I can of course create the queries dynamically, but am hoping to avoid this if possible.
Thanks!
BTW, how can I alias the URLs?
September 4, 2013 at 7:36 am
Hi,
It's only possible to create SPs that reference objects, columns, that don't exist... but it's the only exception on SQL... Executing the SP will throw an error.
The only way is to use EXEC with your statement:
IF @@VERSION LIKE '%2005%'
BEGIN
EXEC('SELECTCASE
WHEN RIGHT(LEFT(@@VERSION, 25), 4) >= 2012
THEN ROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2)
ELSE ROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2)
END MemGB
FROMsys.dm_os_sys_info')
END
Pedro
September 4, 2013 at 7:44 am
Pedro's method would be the only thing. The script itself needs to be parsed in order to generate a plan, and it can't do it if the columns don't exist. The same reason why select * from @myparameter doesn't work.
September 4, 2013 at 10:14 am
Dennis Post (9/4/2013)
BTW, how can I alias the URLs?
If you do it manually, you can use the following syntax (without the space).
[ url=http://technet.microsoft.com/en-us/library/ms190356.aspx]SET Statements (Transact-SQL)[/url]
Otherwise, you can write the alias, select it and click on the hyperlink icon on top of the text editor and past the url in there (some browsers might give you some trouble with it).
September 5, 2013 at 12:40 am
Thanks for your comments guys.
Unfortunately PiMané's suggestion doesn't work as it is still getting parse and failing due to the unknown column name.
IF @@VERSION LIKE '%2005%'
BEGIN
EXEC('SELECTCASE
WHEN RIGHT(LEFT(@@VERSION, 25), 4) >= 2012
THEN ROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2)
ELSE ROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2)
END MemGB
FROMsys.dm_os_sys_info')
END
ELSE
BEGIN
PRINT 'Didn''t parse'
END
But if you guys say that it can only be done dynamically then i'll do something like this:
DECLARE @SQL Varchar(1000)
SET @SQL = '
SELECT ' +
CASE
WHEN RIGHT(LEFT(@@VERSION, 25), 4) >= 2012
THEN + 'ROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2) MemGB '
ELSE + 'ROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2) MemGB '
END +
'FROM sys.dm_os_sys_info'
EXEC(@SQL)
Will probably have to use a TempTable to catch the value.
September 5, 2013 at 12:44 am
If you do it manually, you can use the following syntax (without the space).
[ url=http://technet.microsoft.com/en-us/library/ms190356.aspx]SET Statements (Transact-SQL)[/url]
Thanks Luis!!
September 5, 2013 at 2:38 am
Sorry, didn't notice the CASE in the SELECT.
You have to change it to something like this:
IF RIGHT(LEFT(@@VERSION, 25), 4) >= 2012
BEGIN
EXEC('SELECTROUND(1.0 * Physical_Memory_kb / 1024 / 1024, 2) FROMsys.dm_os_sys_info')
END
ELSE
BEGIN
EXEC ('SELECTROUND(1.0 * Physical_Memory_in_bytes / 1024 / 1024 / 1024, 2)FROMsys.dm_os_sys_info')
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply