January 14, 2014 at 10:31 am
People here helped me get started in the world of linked servers (See post # 1498950) And I've been using my newly linked server to execute queries successfully. I'd appreciate any help I can get with the next step.
I have a query that runs fine in SQL Server Management Studio. I run it several times a month and paste the results into excel for the end user.
When I tried to add it to my stable of Agent jobs, it won't run. The error is:
Linked server query in agent returns ERROR: Unable to open Step output file
Unfortunately that's all the information I get. I know that sometimes applies tothe name and location of the file, if you choose to create a file, but my output is attached to an email, not saved to a network drive.
So I tried to do an end-run around the problem and create a SP, thinking I'd call the SP from the agent, but I can't even CREATE PROCEDURE because I get this message:
Msg 102, Level 15, State 1, Procedure sp_QC_Holds, Line 48
Incorrect syntax near ')'.
It's barking about the very last line in the SP. When I comment out that line it barks about the next one up, and so forth.
I think I must be missing something that a query doesn't need but an agent job or a SP needs. Any advice would be appreciated. Thanks! (In fact I thought I posted this problem a week or two ago but can't find it.)
January 14, 2014 at 11:31 am
Regarding the first error "unable to open step output file": Do you use the SP_SEND_DBMAIL stored procedure? Does it work if you execute it as an SQL Agent job on the SQL instance itself (instead of through the linked server)?
The second error "syntax error near )" indicates an error in the code itself. It could be that you use a BEGIN statement but forget the associated END statement.
January 14, 2014 at 1:07 pm
Thank you for your questions, you got me part of the way to resolution!
To your first question, I do use exec msdb.dbo.sp_send_dbmail @profile_name = 'Send Mail'
a lot in SQL server agent jobs, and can execute the query in question. when I run it in SSMS, it executes and sends me an email with the results in a csv attachment. The results include fields from tables in both the server I'm executing from, and the linked server.
Your second question yielded some results. I did indeed have a BEGIN without an END. I added the END, and now the error message is probably closer to the heart of the matter:
Msg 4104, Level 16, State 1, Procedure sp_QC_Holds_For_Cost, Line 42
The multi-part identifier "DBS1\X3V6.x3v6.PROD.STOCK.lot_0" could not be bound.
Line 42 is the inner join.
I'm now wondering if there is some place where I have not forced the collation as I should.
Thanks again for looking at this. I hope to learn something more about resolving collation conflicts from this experience.
code = "sql"]
SELECT *
FROM qc.dbo.tblLot lot
INNER JOIN qc.dbo.tblOnHold oh
ON lot.LotID = oh.LotID
LEFT JOIN [DBS1\X3V6].x3v6.PROD.STOCK
--STOCK has one record for every status/loc combination
--left join because some QC records are not in ERP
ON lot.Lot COLLATE SQL_Latin1_General_CP1_CI_AS =[DBS1\X3V6].x3v6.PROD.STOCK.lot_0
INNER JOIN qc.dbo.tblLocation ON lot.LocID = tblLocation.LocID
INNER JOIN qc.dbo.tblWHLocation ON lot.WHLoc = qc.dbo.tblWHLocation.WHLocID[/code]
January 14, 2014 at 2:53 pm
SQLKnitter (1/14/2014)
Thank you for your questions, you got me part of the way to resolution!To your first question, I do use exec msdb.dbo.sp_send_dbmail @profile_name = 'Send Mail'
a lot in SQL server agent jobs, and can execute the query in question. when I run it in SSMS, it executes and sends me an email with the results in a csv attachment. The results include fields from tables in both the server I'm executing from, and the linked server.
If you temporaraly remove the linked server part from the query, will it run without error? If so, there is probably a permission issue with the SQL Agent account on the linked server connection.
Your second question yielded some results. I did indeed have a BEGIN without an END. I added the END, and now the error message is probably closer to the heart of the matter:
Msg 4104, Level 16, State 1, Procedure sp_QC_Holds_For_Cost, Line 42
The multi-part identifier "DBS1\X3V6.x3v6.PROD.STOCK.lot_0" could not be bound.
Line 42 is the inner join.
I'm now wondering if there is some place where I have not forced the collation as I should.
Your error messages are not related to COLLATION issues. An error related to the collation will clearly mention the related collation.
The message "... could not be bound..." indicates the mentioned object is not found. This could be a typo in the name, a reference to a non-existing object or a permissions issue. Can you double check the linked server/database/table/column exists and the account used has correct permissions?
I suggest you give the table in the linked server an alias and use that alias in all other places within the query. This will improve readability.
SELECT *
FROM qc.dbo.tblLot lot
INNER JOIN qc.dbo.tblOnHold oh
ON lot.LotID = oh.LotID
LEFT JOIN [DBS1\X3V6].x3v6.PROD.STOCK linked_stock
--STOCK has one record for every status/loc combination
--left join because some QC records are not in ERP
ON lot.Lot COLLATE SQL_Latin1_General_CP1_CI_AS =[DBS1\X3V6].x3v6.PROD.STOCK linked_stock.lot_0
INNER JOIN qc.dbo.tblLocation ON lot.LocID = tblLocation.LocID
INNER JOIN qc.dbo.tblWHLocation ON lot.WHLoc = qc.dbo.tblWHLocation.WHLocID
January 16, 2015 at 12:32 am
1.Put NT AUTHORITY\SYSTEM user in sysadmin server role 2.add this user into linked server login mappings with remote user name and pwd in security tab of linked server properties
January 19, 2015 at 8:29 am
Thank you, I will try this. 🙂
Unfortunately I had to set this project aside because:
1. I am the only one in the company who works with computing other than pcs.
2. Fires with taller flames and more visible projects are always a priority.
I hope to get back to this soon as it would save me at least an hour a week and could be used for other time savers.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply