Having an issue wth a bcp string

Question

Post reply

Having an issue wth a bcp string

itmasterw 60042

SSCrazy

Points: 2858
More actions
April 14, 2020 at 2:45 pm

#3742760
Hi,
I have been trying to do this for a while now to get this and, and while I have done this before without a problem, somehow I have something wrong; probably a quote or something. now maters what I do the file is not created and I get the following error message each time. "Copy direction must be either 'in', 'out' or 'format'."
If someone can show me what I am doing wrong I would greatly appreciate it.
Thank you
```
 set @qry='bcp ''select * from  ##TEMP1 " queryout "\\DELLX2010\c$\Vend\Inter Data\to ID\DMP '''+   CONVERT(VARCHAR(8), SYSDATETIME(), 112) +'''" +"_BK.txt"  -T -c -t,'
       print @qry
EXEC xp_cmdshell @qry
```

Viewing 10 posts - 1 through 9 (of 9 total)

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

It looks like you have two single quotes before the select instead of a double quote 😛

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Change the two single quotes before select and after DMP to a double quote.

John

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 3

Thanks that helped a great deal, but now I am having some kind of issue on the other end. Because it now gives :

"User name not provided, either use -U to provide the user name or use -T for Trusted Connection" So I just tried different quotes but nothing worked, and ideas here?

Thank you

frederico_fonseca SSCoach Points: 16156 More actions · Answer 4

you do need to look at the output of your print command - that will show it what is wrong.

try the following - removed some of the single quotes on the filename that most likely should not be there.

and do you really want a filename with a space between DMP and the date portion?

set @qry='bcp "select * from  ##TEMP1" queryout "\\DELLX2010\c$\Vend\Inter Data\to ID\DMP '+   CONVERT(VARCHAR(8), SYSDATETIME(), 112) +'_BK.txt"  -T -c -t,'
print @qry

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

How are you going to connect - with Windows authentication or with a SQL login? In the former case, use the -T switch; in the latter use -U and -P to specify the login name and the password. It's all in here.

John

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 6

itmasterw 60042

SSCrazy

Points: 2858

April 14, 2020 at 3:55 pm

#3742790

Trusted connection

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 7

This is what my code now looks like:

     set @qry='bcp "select * from  ##TEMP1 " queryout "\\DELLX2010\c$\Vend\Inter Data\to ID\DMP " "'+   CONVERT(VARCHAR(8), SYSDATETIME(), 112) +'''" +"_BK.txt"  -T -c -t,'
       print @qry
EXEC xp_cmdshell @qry

frederico_fonseca SSCoach Points: 16156 More actions · Answer 8

frederico_fonseca

SSCoach

Points: 16156

April 14, 2020 at 4:31 pm

#3742803

did you bother reading my reply?

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 9

Sorry, somehow I did not see your reply, maybe I needed to refresh. I will try this now.

Thanks