Schedule query, export to file, inc file name

Last Post 04 Sep 2009 03:04 PM by brianmultilanguage. 12 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
brianmultilanguage
New Member
New Member

--
27 Aug 2009 11:22 AM
I need to do four things.
1. build query(done)
1.1 export results to a file(or even a table with a Order & Date column)
1.2 run each night
1.3 save 61 days of query results.

2. Repeat.

Is it more efficient to export to a file and increment the file name instead of appending a table?
The schedule can run as a job.(?)
Keep 61 days only. first in/first out?
brianmultilanguage
New Member
New Member

--
27 Aug 2009 01:07 PM
Thanks.
How do I add the output to the query? Like this? (for example,

EXEC master..xp_cmdshell
'bcp "select * from Order, Date" queryout c:\date_1.txt'

brianmultilanguage
New Member
New Member

--
27 Aug 2009 07:49 PM
Query expert I am not.
the first set - sets dt = to the current date.
the second set runs the query and outputs it to a file with the 'date'_1.
And so each time it's ran, the date changes.
And this all resides in the query.
brianmultilanguage
New Member
New Member

--
28 Aug 2009 10:11 AM
I was trying to understand what I was given.
So I have the query and you gave me the rest.
So to be clear, from the top.
Manually it's cake : SELECT Order_Num FROM Orders.
The requirement.
I need to check what orders (Order_Num) are open at 2031(0830pm) each day which comes from Orders.
However it needs to be a scheduled job and the output of the query needs to be saved and not over written. So the results can be viewed in file, query of a new table. Doesn't matter.




brianmultilanguage
New Member
New Member

--
28 Aug 2009 11:58 AM
So this is saved a query in SQL man. studio? or something else?


Thanks I will look at bcp.
brianmultilanguage
New Member
New Member

--
28 Aug 2009 04:19 PM
I have an error.
Warning: -c overrides -c.
bcp: unknown option l
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
brianmultilanguage
New Member
New Member

--
01 Sep 2009 09:38 AM
No.
sqlstate = 08001 native error = 2
I am checking the remote connections but they appear to be set on.
brianmultilanguage
New Member
New Member

--
01 Sep 2009 10:48 AM
Will do. AND I DO THANK YOU for the help.

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\orders_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "SELECT Order_No FROM OrderTel" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sa -P password -c'

EXEC master..xp_cmdshell @bcpCommand
brianmultilanguage
New Member
New Member

--
01 Sep 2009 03:23 PM
I believe we hit pay dirt. I will run it again tomorrow~! Thanks for the help~!
-----
Declare @cmd varchar(255)
Declare @dt char(8)

set @dt = convert(char(8), getdate(), 112)
set @cmd = 'bcp "SELECT column FROM dbName..tablel" queryout c:\' + @dt + '_1.txt -c -S servername\instance -T'

exec master..xp_cmdshell @cmd
-----
brianmultilanguage
New Member
New Member

--
02 Sep 2009 10:27 AM
Ok this works. I'll save it and run it as a job.
What is the line continuation symbol so the command is on two lines instead of one long line?
brianmultilanguage
New Member
New Member

--
04 Sep 2009 03:04 PM
Thanks for the input and help.
can I use bcp to copy a table from one server\db to server_1\db?
brianmultilanguage
New Member
New Member

--
08 Sep 2009 06:39 AM
Thank you. One final thing that I am still torn on and it's the end result of this process. I have a reports server facing the internet that is secured and separated (FW, subnet, AFW). The data (4 tables) for this server resides inside the domain. In keeping the db and the inside server safe, those 4 tables need to be copied over weekly. SSIS to pull the data to reports server facing the internet or push from the inside to reports server facing the internet?
brianmultilanguage
New Member
New Member

--
29 Sep 2009 10:10 AM
Thanks again for the help and work.


Acceptable Use Policy
---