May 29, 2002 at 11:15 am
I'm trying to export the results of a query from within a stored proc using bcp. The books online example says this:
bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c
to get it to run - I believe you have to use xp_cmdshell, so what I have is this:
exec master..xp_cmdshell 'bcp ''select name from adps..customer'' queryout g:\sbcdata\test1.txt -c'
xp_cmdshell gets as far as executing bcp - but then acts as if I didn't supply it with any of the parameters. I have tried replacing the query with a tablename, and that works fine. So it must be that I am not delimiting the query correctly.
Can anyone help me with this? Thanks!!
May 29, 2002 at 11:23 am
I think you need to provide the userid/password. When you do bcp from command line does it prompt you?
Andy
May 29, 2002 at 11:27 am
Our sql server is set for windows authentication, and I thought that if you left it blank it assumed windows authentication mode.
I did try using my windows password, and I am the dbo, and that didn't work.
Thanks for your help!!
May 29, 2002 at 11:35 am
Have you tried running this in QA to get the output information. I have no troubles with this format based on my databases but the output information should give a clue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 29, 2002 at 11:40 am
Yep : here is the output I get...
Copy direction must be either 'in', 'out' or 'format'.
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"]
NULL
That is what makes me think it is getting as far as executing bcp - but doesn't recognize the query, or anything that follows.
Thanks!!
May 29, 2002 at 12:08 pm
Just ran this:
xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c'
And got this:
Password:
NULL
Starting copy...
NULL
24 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 10
NULL
Andy
May 29, 2002 at 12:22 pm
Yes - a double quote instead of two single quotes seems to have made the difference.
Also, I see from other forum discussions that you can't bcp from a temporary table, and also apparently not from a table created in the same proc.
Is that correct?
Thanks very much for your help!!
May 30, 2002 at 10:39 am
Got a new question:
What is the trick to getting bcp to export a query using a format file? Now that I have the query itself able to export - the format file is causing an error (unable to read bcp format file). The format file I have is exactly the same fields/sizes/order as the data I am pulling - I have quadruple checked it.
Any ideas?
Thanks very much for your help!!
May 30, 2002 at 10:56 am
Could you post the file?
Andy
May 30, 2002 at 11:13 am
Absolutely!
Here is the format file
________________________
8.0
15
1 SQLCHAR 0 3 "\t" 1 Areacode
2 SQLCHAR 0 7 "\t" 2 Phone
3 SQLCHAR 0 3 "\t" 3 CustCode
4 SQLCHAR 0 50 "\t" 4 CustomerName
5 SQLCHAR 0 50 "\t" 5 Addr1
6 SQLCHAR 0 50 "\t" 6 Addr2
7 SQLCHAR 0 50 "\t" 7 CityState
8 SQLCHAR 0 2 "\t" 8 State
9 SQLCHAR 0 9 "\t" 9 Zip
10 SQLCHAR 0 4 "\t" 10 LanguageCode
11 SQLCHAR 0 1 "\t" 11 RegionCode
12 SQLCHAR 0 8 "\t" 12 CampaignCode
13 SQLCHAR 0 10 "\t" 13 GroupCode
14 SQLCHAR 0 50 "\t" 14 LetterCode
15 SQLCHAR 0 8 "\r\n" 15 FileDate
____________________________________
Here is the query that pulls data to be exported:
____________________________________
select
left(btn,3) as AreaCode,
right(btn,7) as Phone,
cust_code as CustCode,
cust_name as CustomerName,
cust_addr1 as Addr1,
cust_addr2 as Addr2,
city_state as CityState,
state,
zip_cd+zip_4 as Zip,
Null as languagecode,
Null as RegionCode,
Null as CampaignCode,
Null as GroupCode,
LetterCode,
left(convert(char(8), Filedate, 1),2)+substring(convert(char(8), Filedate, 1),4,2)+right(convert(char(8), Filedate, 1),2) as FileDate
from adps..temploadpac
______________________________
I have tried the format file with and without the tab field delimeters- makes no difference. Also, have checked field lengths to make sure that they are equal or less than what the format file expects.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply