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