SQL Server Agent Job question

Question

SQL Server Agent Job question

celia 34404

SSC Enthusiast

Points: 191
More actions
April 3, 2018 at 6:52 am

#328130

I'm new to SQL Server Agent. What I want to do is execute a small script to run a stored procedure and copy the results of the query to a file in a specific location. After searching the internet, I have the following one step in the job:

EXEC xp_cmdshell 'bcp "exec CMS_THERAPIST_LISTING" queryout " C:\Users\Public\Public Documents\abc.csv" -T -c'
The log file viewer says the job succeeded, but there is no file in C:\Users\Public\Public Documents.
Am I using the commands in my script correctly?
Any help will be VERY MUCH appreciated!

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110651 More actions · Answer 1

Make sure your job step actually fails the job if it fails. Also check your stored procedure to at least be sure it produces a recordset. Finally, verify that your BCP options are set correctly, and that the SQL Agent Service Account has NTFS write permissions to the file folder location.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101210 More actions · Answer 2

bcp exports the results of a table, view or query, not the results of a procedure execution.

You'll need to capture the results of the proc into a table, then export that, something like:

CREATE TABLE dbo.listing
(
column1 ...,
column2 ...,
...
)

INSERT INTO dbo.listing
EXEC dbo.CMS_THERAPIST_LISTING
EXEC xp_cmdshell 'bcp "select * from dbo.listing" queryout "C:\Users\Public\Public Documents\abc.csv" -T -c'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

celia 34404 SSC Enthusiast Points: 191 More actions · Answer 3

Thank you for your reply. I used your suggestion and am testing the following in a SQL Server Mgmt Studio window. The statements execute and the file #QlikTemp gets created, but I get error on the last line of my script. The error says

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#QlikTemp'.

here is the script:

USE [Live_PTS]
DROP TABLE #QlikTemp
CREATE TABLE #QlikTemp (
districtid int,
schoolid int,
therapistid int,
disciplineid int,
discipline nvarchar(200),
directorid int,
tdate datetime,
direct float,
IEPdirect float,
consult float,
reeval float,
indirect float,
screen float,
eval float,
rti float,
other float,
individual float,
intervention float,
ei float,
billrate decimal(6,2),
payrate decimal(6,2),
schoolyear nvarchar(10))
insert into #QlikTemp
EXEC dbo.QLIK_EXEC_2017_2018
EXEC xp_cmdshell 'bcp "select * from #QlikTemp" queryout "C:\Users\Public\Public Documents\abc.csv" -T -c'

celia 34404 SSC Enthusiast Points: 191 More actions · Answer 4

After the script fails with the error, I type
select * from #QlikTemp, and all the table rows are returned. Perhaps it doesn't like the # symbol?

sgmunson SSC Guru Points: 110651 More actions · Answer 5

celia 34404 - Wednesday, April 4, 2018 6:22 AM
After the script fails with the error, I type
select * from #QlikTemp, and all the table rows are returned. Perhaps it doesn't like the # symbol?

The problem here is that the execution context changes between the time that you create the temp table and the time you go to access it. Use a GLOBAL temp table by just changing the single # character at the beginning of your temp table name to a doubled one (##). Understand that this table will NOT get dropped at the end of any stored procedure as GLOBAL temp tables are more permanent objects. I don't recall if they go away when the SQL Server service is restarted, but you usually manually drop them when they're no longer needed. Making that change should solve the execution context change issue.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101210 More actions · Answer 6

Don't use a temp table (I didn't). Just use a "real" table name. Not a bad idea to keep a copy of what was exported until the next run anyway. Of course you can truncate the table after the export if you prefer.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

celia 34404 SSC Enthusiast Points: 191 More actions · Answer 7

Thank you Again for the replies. Based on your good advice, I now have this script:

truncate table QlikSenseData
insert into QlikSenseData
EXEC dbo.QLIK_EXEC_2017_2018
EXEC xp_cmdshell 'bcp "select * from dbo.QlikSenseData" queryout "C:\Users\Public\Public Documents\abc.csv" -T -c'

it returns the following "results" in 5 rows: same as before:
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'dbo.QlikSenseData'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL

The table QlikSenseData is populated as I want it to be, but it is not being put out to the Public Documents\abc.csv file. Does this abc.csv file need to already exist?

Sue_H SSC Guru Points: 90860 More actions · Answer 8

celia 34404 - Wednesday, April 4, 2018 11:20 AM
Thank you Again for the replies. Based on your good advice, I now have this script:
truncate table QlikSenseData
insert into QlikSenseData
EXEC dbo.QLIK_EXEC_2017_2018
EXEC xp_cmdshell 'bcp "select * from dbo.QlikSenseData" queryout "C:\Users\Public\Public Documents\abc.csv" -T -c'
it returns the following "results" in 5 rows: same as before:
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'dbo.QlikSenseData'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL
The table QlikSenseData is populated as I want it to be, but it is not being put out to the Public Documents\abc.csv file. Does this abc.csv file need to already exist?

Try specifying the database name. You can add -d DatabaseName to the bcp command. Or use three part name to reference the table such as select * from YourDB.dbo.QlikSenseData
Don't use both as you will get an error. Use one or the other.

Sue

celia 34404 SSC Enthusiast Points: 191 More actions · Answer 9

I used Sue's advice and my script looks like this now:

truncate table QlikSenseData
insert into QlikSenseData
EXEC dbo.QLIK_EXEC_2017_2018
EXEC xp_cmdshell 'bcp "select * from Live_PTS.dbo.QlikSenseData" queryout "C:\Users\Public\Public Documents\abc.csv" -T -c'
The results are different now, 3 output rows were returned like these:

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
NULL

which file is the host BCP data-file?

celia 34404 SSC Enthusiast Points: 191 More actions · Answer 10

I change the queryout file to a different path and the copy happened. Thanks for all the suggestions.

Now what I really want to do is queryout the file to .XML format.

ScottPletcher SSC Guru Points: 101210 More actions · Answer 11

Try using the
FOR XML
option in the SQL query (SELECT).\

I'm not sure if that will work as you want it to (I think it probably should), and if it does, it will be the easiest way.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sgmunson SSC Guru Points: 110651 More actions · Answer 12

celia 34404 - Wednesday, April 4, 2018 12:07 PM
I change the queryout file to a different path and the copy happened. Thanks for all the suggestions.
Now what I really want to do is queryout the file to .XML format.

Pretty good bet that is was a permissions issue, such that the account that runs the code doesn't have NTFS-based permissions access to the original folder specified.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)