November 5, 2009 at 1:19 pm
Hi All,
I executed the below query in SQL Server 2005.
if 1=3
BEGIN
select [Job_id], [name], [freq_interval] from msdb..sysjobschedules where freq_type = 8
END
This should not give any result as the condition 1 =3 does not match. But when, I execute this, I get the following error :w00t:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'freq_type'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'name'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'freq_interval'.
I am aware that these columns does not exist in the sysjobschedules table in SQL Server 2005. But, I think as per my query, since the conditions are not met, this error should not occur at all.
Please correct me if i am wrong....
November 5, 2009 at 1:28 pm
Hi
SQL Server first parses your query and creates a complete execution plan. After that it becomes executed. If your statement references not existing columns it fails previous work.
Greets
Flo
November 5, 2009 at 1:31 pm
The only way to do this is with dynamic SQL.
if 1=3
exec('select [Job_id], [name], [freq_interval] from msdb..sysjobschedules where freq_type = 8');
Of course, that raises the question of why you'd want to do that, but I'm assuming your code was annonymized and the "if" test is actually something that could be true at some point.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 1:55 pm
I am trying to get the backup information (backup job name, schedule etc.,) from multiple servers.. Some server have 2000 installed and some have 2005 installed..
I m using 2 scripts. One uses sysjobschedules table to get the backup information from the server that has sql server 2000 installed. Another script uses sysschedules table to get the information from the server that has sql server 2005 installed.
I would like to extract the information from all the server at the same time. Hence, I have used the following if condition..
if substring(@@version, 22,5) = 2005
begin
Declare C cursor for select distinct sj.Job_id, sj.name, ss.freq_interval from msdb..sysschedules ss join msdb..sysjobschedules sjs on ss.schedule_id=sjs.schedule_id join msdb..sysjobs sj on sjs.job_id=sj.job_id where ss.freq_type = 8
.......................
................
end
ELSE
begin
Declare C cursor for select Job_id, name, freq_interval from msdb..sysjobschedules where freq_type = 8
.................
...
end
Now, this is where the problem is.. This script does not execute in SQL Server 2005 because of the sysjobschedules table.. It gives the following error..
Msg 207, Level 16, State 1, Line 5
Invalid column name 'freq_type'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'name'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'freq_interval'.
Is there any other alternative way to do this???
November 5, 2009 at 2:01 pm
My suggestion is build a DBA database on each server, and have a view on it that pulls the columns you need in the format you need from the table you need.
Then have the job pull data from that view on each server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 2:06 pm
Yes.. I thought of that... But creating a view in 80+ servers is hard task to do.. :w00t:
I thought of using osql to pull data from each and every server instead of logging in each server...
does that idea sound crazy??? Do let me know if you know any other shoftcuts? 😀
November 5, 2009 at 2:11 pm
Dynamic SQL is the only other way I know of, like my first example.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 2:14 pm
Thanks for the reply.. Is this a valid statement?
Declare Cur cursor for exec('select js.Job_id, js.name, js.freq_interval from msdb..sysjobschedules js where js.freq_type = 8')
How should i use dynamic sql here?
Thanks!!!
November 5, 2009 at 2:18 pm
Yes.. I thought of that... But creating a view in 80+ servers is hard task to do.. [w00t]
.
No that is not an issue at all !
Create a script for each version you want to support and use SQLCMD to check the version and execute the correct (dynamic sql) create statement.
Or create a vbs application which connects to your instances, checks the @@version info and executes the correct script.
You only need to maintaine a single script or a single script per major version ! (I use a version library, having a script for each version)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 5, 2009 at 2:28 pm
Creating the databases with the views is going to be easier than creating the necessary dynamic SQL statements, in the long run.
You wouldn't use the exec command for the select when you declare the cursor. If you decide to go that route, and I recommend against it, then you'd create a temp table, populate that with the exec command, and then query the temp table when you declare the cursor.
Creating a DBA database on each server isn't that big a deal. Plus, it gives you a lot of options for consolidating maintenance scripts, etc.
If you use replication or some other synchronization method, you only have to update one central copy of the databases in order to add new functions and features.
It's a really efficient and effective DBA tool.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 2:30 pm
I m not good in vbs.. I m hoping to do this using SQL script.. How can i check the version of sql server using SQL CMD...
I m using this statement..
sqlcmd -E -S <server name> -d msdb -i <input file> -o <output file> -u
The input file contains the script to be executed. I used the if statement in the script to check the version but that does not work as I mentioned earlier..
how should i implement dynamic sql in this case? an e.g. will help me understand.. 😀
thanks!!!
November 5, 2009 at 2:35 pm
@Gsquare..
Thanks for the reply.. In the long run, I will create a view which pulls the data...
thanks again....
November 6, 2009 at 12:51 am
I've attached a little primitive example that may get you started.
Run this using SQLCMD or using SSMS in SQLCMD mode;
I did put my scripts at c:\SQLCMD
-- write the output to this file
:out testoutput.txt
Print 'MyScript running for ' + @@servername + ' - ' + db_name() + ' -- ' + convert(varchar(23), getdate(),121)
if cast(serverproperty('ProductVersion') as varchar(15)) like '8.%'
begin
print 'going for sql2000'
-- execute this script
:r "SQL2000_Script.sql"
print 'went for sql2000'
end
if cast(serverproperty('ProductVersion') as varchar(15)) like '9.%'
begin
print 'going for sql2005'
-- execute this script
:r "SQL2005_Script.sql"
print 'went for sql2005'
end
if cast(serverproperty('ProductVersion') as varchar(15)) like '10.%'
begin
-- execute this script
print 'going for sql2008'
:r "SQL2008_Script.sql"
print 'went for sql2008'
end
GO
!!:Quit
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 6, 2009 at 7:53 am
Thanks for the reply & the code as well... 🙂 That was lot helpful.. 😀
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply