BCP problem running from EM and Query analyzer

I am running on W2K server with Sql Server 7. I am having a lot of
problems getting some of my stuff to run. I suspect it has to do with
permissions.
But in this case I am trying to run the following command from EM as a
Job as well as directly from Query Analyzer.
BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
I get the following error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
I copied and pasted it into a cmd window and it worked fine.
What is the problem here? It is getting very frustrating.
Thanks,
TomHi,
You cant execute directly a BCP command from Query analyzer. As a job you
can execute it but the job type should be "operating system command".
Otherwise use xpcmdshell to execute the BCP from query analyzer
master..xp_cmdshell 'BCP pubs..phone_book out c:\temp\authors.txt -c -U
sa -S DINO'
Thanks
Hari
MCDBA
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:3FC704D7.1030207@.deltanet.com...
> I am running on W2K server with Sql Server 7. I am having a lot of
> problems getting some of my stuff to run. I suspect it has to do with
> permissions.
> But in this case I am trying to run the following command from EM as a
> Job as well as directly from Query Analyzer.
> BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
> I get the following error.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> I copied and pasted it into a cmd window and it worked fine.
> What is the problem here? It is getting very frustrating.
> Thanks,
> Tom
>|||Hari wrote:
> Hi,
> You cant execute directly a BCP command from Query analyzer. As a job you
> can execute it but the job type should be "operating system command".
> Otherwise use xpcmdshell to execute the BCP from query analyzer
> master..xp_cmdshell 'BCP pubs..phone_book out c:\temp\authors.txt -c -U
> sa -S DINO'
>
That was it.
I was having trouble getting it to work in EM, where I don't use the
xp_cmdshell SP and just copied it directly into the query analyzer.
All works fine now
Thanks,
Tom.
> Thanks
> Hari
> MCDBA
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:3FC704D7.1030207@.deltanet.com...
>>I am running on W2K server with Sql Server 7. I am having a lot of
>>problems getting some of my stuff to run. I suspect it has to do with
>>permissions.
>>But in this case I am trying to run the following command from EM as a
>>Job as well as directly from Query Analyzer.
>>BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
>>I get the following error.
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near '.'.
>>I copied and pasted it into a cmd window and it worked fine.
>>What is the problem here? It is getting very frustrating.
>>Thanks,
>>Tom
>>
>

BCP out and headers

I'm no dummy when it comes to this stuff but for the life of me I can't figure out how (if it is possible) to get column headers to go when exporting data. Here is the syntax I'm using:

BCP "select ATSCLAIMNUMBER, ALTERNATECLAIMNUMBER, LNAME , FNAME, MNAME, convert(varchar(10),LOSSDATE,101), convert(varchar(10),ERNOTIFIEDDATE,101), convert(varchar(10),CLOSINGDATE,101), STATUSCODE, INDPAID,MEDPAID,REHABPAID,EXPPAID,LEGALPAID,TOTALP AID,INDFUTURERES,MEDFUTURERES,REHABFUTURERES,EXPFU TURERES,LEGALFUTURERES,TOTALFUTURERES,EXCESSRECOVE RY1,EXCESSRECOVERY2,EXCESSRECOVERY3,EXCESSRECOVERY 4,EXCESSRECOVERY5,EXCESSRECOVERY,OTHERRECOVERY1,OT HERRECOVERY2,OTHERRECOVERY3,OTHERRECOVERY4,OTHERRE COVERY5,OTHERRECOVERY FROM ##MMAACTUARY" queryout C:\mma03182004.TXT /c /t, /r \n /U demo /P demo /S ATSDEV\ATS2K

It spits out the file fine. This is all built with dynamic SQL and needs to email the file which is does perfectly. My only problem is that there are no headers which I can't have. Any suggestions?How about using osql with a "-s," parameter? Might want to add a -n -w 5000 also.|||I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott|||It's not what I imagined but it does look like it would work. I really don't care at this point how it happens so long as it does! I'll try this and report back with the results.|||Originally posted by hmscott
I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott

Hey, thnaks fo rremebering...and you know what?

bcp -c

and, what's not elegant about it?

I use Union ALL btw

AND to make sure the rows come out correctly...

For fixed width:

SELECT Col1,Col2,Col3 FROM (
SELECT 'Heading1' AS Col1
, 'Heading2' AS Col2
, 'Heading3' AS Col3
, 1 AS RowOrder
UNION ALL
SELECT Col1
, CONVERT(varchar(25), myDatetimeCol99)
, CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

For Delimeted:

SELECT datarow FROM (
SELECT 'Heading1,Heading2,Heading3' AS Datarow
, 1 AS Roworder
UNION ALL
SELECT Col1+','
+','CONVERT(varchar(25), myDatetimeCol99)
+','CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

And usually with delimeted, I wrap all the data in quotes

''''+ Col1 + ''''
','+ ''''+ Col2 + ''''|||Hey, and dig it, you can even add a trailer with audit counts...

Just make it datarow 3 and do a select count(*)...you could even sum amount if you want to go crazy...

AND you could add a row "type" to each one, to make it easier for extraction of the non data row

WHERE SUBSTRING(datarow,1,1) IN ('H','T')|||Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott|||Originally posted by hmscott
Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott

IMNSHO, I wouldn't be to quick to give up DTS. I don't think there is much you can do with BPC that you can't do with DTS. There is a bunch you can do with DTS that you can't do with BCP. There isn't an "ELEGANT" solution here just not as ugly.|||It does work but the string got too long. So, what si did was create a seondary table and inserted the header values. This table only has one row so when using that select statement unioned with the real select statement it works perfectly. If any one wants a copy of the procedure email me jfogel3@.msn.com|||Originally posted by hmscott
BTW, I changed jobs and have been thrown into what seems to be a real lion's den.


Sorry to hear that...

I avoid DTS for any production related issues...seen to many thing I couldn't explain...

Mostly with connections and changing them...Seems like it's lookin at an earlier version sometimes...no thanks...

And I love Nigels sig...

Cursors are useful if you don't know SQL
DTS can be used in a similar manner
Beer is not cold and fizzy

Or a paraphrase...since it seems like sqlteam is down...again....

Oh, and no apologies please....

(otherwise I'll have to do it all the time...)|||ooopps...here's the thread

They're back up...

and it's...

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BCP Help

I'm tryping to use BCP to transfer some data to a file. In an effort ot learn about it, I'm trying some simple stuff with the pubs database and some examples from BOL. However, none of them are working.

I'm trying this command:

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword

Of course i'm changing servername, sa, and password to the appropriate stuff..

I get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'queryout'.

Any ideas why this example from BOL isn't working for me?

Thanks in advance!

MarkThat looks right. If you are doing this from within SQL (constructing a statement and executing xp_cmdshell, are you sure this is the statement being formed?|||Originally posted by bpdWork
That looks right. If you are doing this from within SQL (constructing a statement and executing xp_cmdshell, are you sure this is the statement being formed?

I'm actually using Query Analyzer and getting that error. Would that make any difference?|||Ah, yes.

BCP is a command line utility, not a SQL command. To run it from QueryAnalyser, you need to shell the command to the operating system with xp_cmdshell.

Try running it froma Command Prompt.

-b|||Originally posted by bpdWork
Ah, yes.

BCP is a command line utility, not a SQL command. To run it from QueryAnalyser, you need to shell the command to the operating system with xp_cmdshell.

Try running it froma Command Prompt.

-b

Awesome. Thanks so much.. Worked great. I feel like an idiot now.

Thanks again|||You mean, you feel smarter now.|||Originally posted by bpdWork
You mean, you feel smarter now.

Something like that. Maybe one day i'll beable to get a job as a DBA.|||DBA is boring. Programming is where it's at man! (Let the hell-fire begin!)|||DBA is boring. Programming is where it's at man! (Let the hell-fire begin!)

Am having a different kinda
discussion (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31111) right now.

BTW ... there is a difference between a support DBA and a development DBA.