May 30, 2016 at 5:32 am
Gents,
Gathering different information from serveral servers I ran into a strange behavior.
When using a dynamic script, using a 2014 environment, to store error-log information from these different SQL servers I have to deal whith other SQL versions which need a slightly different syntax.
** First I will retrieve a versionnumber ( @SRVver ) from a central tabel I implemented to hold server information based upon the SERVERNAME.
** Based upon this nummer I use the "IF" statement to choose the proper syntax for this version and fill the SERVENAME ( @FS ) and version ( @SRVver ).
** finally I execute the script.
pre executing this script it fills in the variables @SRVver and @FS and than the strange behaviour occurs....
when a servername is filled in ( @SRVver ) both scripts in the "IF" are checked and when the SERVERNAME pointing to is not able to deal with this syntax it promts an error....but what is happening with the "IF" statement ??
As I logical expect, SQL-server will choose the way to go ( "IF" statement ) and check for the syntx for just one direction - but instead SQL-server checks them both.
How can I deal with this?
Regard,
Guus Kramer
--*********************************************************************************
set @string_RE = '
if object_id(''[tempdb]..[#TempResult_'+@FS+']'') IS NOT NULLdrop table [#TempResult_'+@FS+']
create table [#TempResult_'+@FS+']
(
LogData datetime, --smalldatetime,
ProcessInfo varchar(128),
LogText varchar(2000)
)
if '+@SRVver+' < 11
begin
insert into [#TempResult_'+@FS+'] select * FROM OPENROWSET( ''SQLNCLI11'' ,''server='+@FS+';trusted_connection=yes'',''SET FMTONLY OFF exec msdb.sys.xp_readerrorlog 0,1 '')
end
if '+@SRVver+' > 10
begin
insert into [#TempResult_'+@FS+'] select * FROM OPENROWSET( ''SQLNCLI11'' ,''server='+@FS+';trusted_connection=yes'',''SET FMTONLY OFF exec msdb.sys.xp_readerrorlog 0,1 WITH RESULT SETS ((Logdate varchar(20),ProcessInfo varchar(100),text varchar(2000)))'')
end
'
exec (@string_RE)
May 30, 2016 at 6:32 am
If the version number is 10.5 then it is going to be both less than 11 and greater than 10
May 30, 2016 at 6:38 am
FridayNightGiant,
thats not he issue - I deal wit version number in the SERVERTABLE (as mentioned).
Guus
May 30, 2016 at 7:44 am
Gents,
after some more scripting I found out that if I place the "IF" outside the dynamic part it will work fine....
only now I do have a lot of extra lines to maintain in my script 'çause every "IF" has a lot of the same line (duplicates) with only some small different part in it.......
BTW - Wether I use OPENROWSET or OPENQUERY I cannot make a dynamic "IF" 🙂
Guus
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply