January 6, 2014 at 2:38 pm
I have a linked server situation with different collations. I have resolved the collation conflicts in my little query and it runs fine in SSMS. I attempted to add it to my collection of SQL Server Agent jobs and have created a msdb.dbo.sp_send_dbmail script, which also runs fine in SSMS/Query Analyzer. It is my practice to get it all running correctly this way before setting it up in the job. This one, however, returns ERROR: Unable to open Step output file.
This is my first linked server query, so there must be something I am missing. I don't fill out the file path on the advanced tab because I attach the cvs file to the email. None of my other agent jobs use this feature and they all run fine. The only difference I can see is that it's querying across two servers with different collations.
Any advice would be appreciated. Here's my little script. I've tried to make it anon, so hope no errors crept in...the original runs in QA!
Thanks for reading about my problem.
SET NOCOUNT ON;
use QC
Declare @bodytext nvarchar(150)
Set @bodytext = ‘QCData’
exec msdb.dbo.sp_send_dbmail @profile_name = 'Send Mail'
,@recipients = ‘myemail@.com'
,@reply_to = ' myemail@.com'
,@subject = 'QC Data for people
,@execute_query_database = QC
, @body = @bodytext
,@attach_query_result_as_file = 1
,@query_result_width = 6500
,@query_attachment_filename = 'QC_Data.csv',
@query_result_separator = ' '
--''CSV and Using a ''''Separator (tab) using the keyboard tab''
,@query =
'
SELECT
Right(LEFT(lot,4),3)[Prod]
,lot.Lot
,left(Right(lot,4),3) [JulDay]
,RIGHT(Lot,1) [Year]
,lot.Archive
,CASE
--This causes collation conflict unless coerced using the expression COLLATE --Database_Default
WHEN loc_0 IS NULL
THEN qc.dbo.tblWHLocation.WHLoc COLLATE Database_Default
ELSE loc_0
END As Location
,COALESCE(CAST(qtystuact_0 AS DECIMAL),0)[LinkedQty]
,Total
,Released
,WRestr
,Inedible
,Rework
--,*
FROM qc.dbo.tblLot lot
INNER JOIN qc.dbo.tblOnHold oh
ON lot.LotID = oh.LotID
LEFT JOIN [Linkedserver\Database].Database.Environment.Table st
--Table has one record for every status/loc combination
--left join because some records are not in Linked db
ON lot.Lot COLLATE SQL_Latin1_General_CP1_CI_AS = ST.lot_0
INNER JOIN qc.dbo.tblLocation ON lot.LocID = tblLocation.LocID
INNER JOIN qc.dbo.tblWHLocation ON lot.WHLoc = qc.dbo.tblWHLocation.WHLocID
WHERE (Cat1 <> 0 or Cat2 <> 0 or Cat3 <> 0)
AND lot.Archive = 0
ORDER BY [Year]
‘
January 6, 2014 at 2:46 pm
I apologize for not posting the code in accepted format. I remember now that when I try to edit a topic I set up, I can get into edit mode, but not save my changes. I have no Post button when in edit mode.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply