September 30, 2011 at 9:06 am
Hi,
I wish to collect job history from several SQL2005 instances. 3 or 4 instances have a different collation. For now I use a UNION construction with linked-servers to get all the data I need. The basic SQL is like this:
SELECT 'S1',*
FROM OPENQUERY([SERVER1], 'select distinct j.Name COLLATE SQL_Latin1_General_CP1_CI_AS as "Job Name",j.enabled , j.category_id , j.description COLLATE SQL_Latin1_General_CP1_CI_AS as "Job Description", h.run_date as LastStatusDate, h.run_status
as "JobStatus"
from msdb..sysJobHistory h, msdb..sysJobs j
where j.job_id = h.job_id and h.run_date =
(select max(hi.run_date) from msdb..sysJobHistory hi where h.job_id = hi.job_id)
order by j.Name')
I use the COLLATE clause at the string columns and bind the different instances with UNION ALL clause.
However, when I run this code against a server with a different collation from my 'master' server I receive an error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I don't understand this. On an instance with the default collation, i.e. the same collation as the 'master' server here is no problem. When I use it on a different collated server, I get this message.
What causes this message? Is there a way to get around this?
Oh, I know about SQL2008 and server groups, but we don't have that here and I can't tell when and if we will be able to install SQL2008. I can find a way around this with jobs and SP's on different servers, but that would be a lot of trouble and inefficient. I really hope there is a simple solution for this.
TIA,
Greetz,
Hans Brouwer
September 30, 2011 at 9:09 am
could it be the Alias of your collated column?
you've called it 'Job Name' instead of 'Name'
October 3, 2011 at 12:49 am
Tnx for answering.
I skipped the AS clause, but the error stays the same, as expected. As I mentioned earlier, this error pops up when I run the query against a linked database with a different collation than the 'master' server.
Greetz,
Hans Brouwer
October 3, 2011 at 2:51 am
Well, problem seems solved, but the question remains. When I use COLLATE DATABASE_DEFAULT The query is executed without a problem. Why I receive the the error when I use COLLATE SQL_Latin1_General_CP1_CI_AS, which IS the actual database default collation, then this error occurs.
What am I missing?
Greetz,
Hans Brouwer
Viewing 4 posts - 1 through 3 (of 3 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