Scheduled queries in Server Agent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Scheduled queries in Server Agent

Author

Topic

learzbu
Starting Member

27 Posts

Posted - 2011-11-20 : 09:43:45

I have read about scheduling jobs in the sql server agent, I set one if these up but now i need to know how to save the results of the query (job) somewhere?

Do I need to add a command to my query instructing this or do I need to add a step to the job that will do this for me?

Does anyone have some experience with this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 10:54:56

you need to save results of query to where? table or output file. in both cases you need to do this via another step in job where you need to write t-sql query for that.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-20 : 11:09:30

I want to save to an output file, .csv or excel. Which ever is easier/possible

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 11:59:45

quote:
Originally posted by learzbu

I want to save to an output file, .csv or excel. Which ever is easier/possible

why not use bcp with query out option then?

http://dotnetkeeda.blogspot.com/2009/09/bcp-command-to-export-data-to-excel.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-20 : 14:19:11

I've been trying to test with :

DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp " SELECT.....I put my query in here" queryout'
SET @bcpCommand = @bcpCommand + ' C:\Temp\test.csv -c -w -T -U sa -P sa","-CRAW'
EXEC cs3tge251011..xp_cmdshell @bcpCommand
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end

but it fails each time. I tried to enter a simple query for output but this fails also. I've been trying to figure out why but can't. I did also enter a table from the master database and this worked with out issue. Any suggestions here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 23:41:05

you dont need -T if you're using -U -P. -T is for windows authentication and -U & -P for sql authentication. you cant use both

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-21 : 08:40:34

DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp " SELECT * from dbo.spt_values" queryout'
SET @bcpCommand = @bcpCommand + ' C:\Temp\test.csv -w -S ' + @@ServerName + ' -T'
EXEC master..xp_cmdshell @bcpCommand
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end

The above works fine with no errors on my master DB

When I try this on the DB that I want to work with:

DECLARE @bcpCommand VARCHAR(8000)
SET @bcpCommand = 'bcp " SELECT * from scheme.ambcodem" queryout'
SET @bcpCommand = @bcpCommand + ' C:\Temp\test.csv -w -S ' + @@ServerName + ' -T'
EXEC cs3tge251011..xp_cmdshell @bcpCommand
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end

I get a string of errors:

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'scheme.ambcodem'.

Why does this work with the master DB but with another DB it gives me this error?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 08:52:12

do you've such a table in your db?

scheme.ambcodem'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learzbu
Starting Member

27 Posts

Posted - 2011-11-21 : 10:58:13

Yes, I've tried with a few different tables but none work from my cs3tge251011 DB.

It's the fact that this will work for my Master DB but not my cs3tge251011 DB that I'm most confused about

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 11:06:06

quote:
Originally posted by learzbu

Yes, I've tried with a few different tables but none work from my cs3tge251011 DB.

It's the fact that this will work for my Master DB but not my cs3tge251011 DB that I'm most confused about

thats means simple. All the tables you need exist only on master db and not on cs3tge251011

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources