Copy Direction Must be in, out or format

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 2005 Forums

Transact-SQL (2005)

Copy Direction Must be in, out or format

Author

Topic

Dhyerwolf
Starting Member

6 Posts

Posted - 2015-02-12 : 19:15:25

I wrote a program in early 2010 that ran a command line function that pulled data from SQL Server 2005. The program worked at the time, but does not seem to work now (for various reasons, it was not used for about a 1 year).

BCP "EXEC emdb..Patch2" QUERYOUT D:\shares\patch2.txt -S GEN-SERVER\EMMSDE -t "~" -c

The error message says "Copy direction must be in, out or format." I'm trying to figure out what I need to change to make this work; Please note that I don't actually have any programming background (it was extensive research into books that my boss no longer has). Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 19:22:06

Try this:

BCP "EXEC emdb..Patch2" QUERYOUT D:\shares\patch2.txt -S GEN-SERVER\EMMSDE -t "~" -c

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Dhyerwolf
Starting Member

6 Posts

Posted - 2015-02-12 : 19:27:41

Hi,

My apologies, but the extra " in the first part was errorneously typed in onto this website, but isn't present in the code (it's on a protected computer with no internet access, so no copy and paste).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 19:47:43

Try this:

BCP "EXEC emdb.dbo.Patch2" QUERYOUT D:\shares\patch2.txt -T -S GEN-SERVER\EMMSDE -t~ -c

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Dhyerwolf
Starting Member

6 Posts

Posted - 2015-02-12 : 19:55:30

I made all those changes and unfortunately, I'm still getting the same error message.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 20:24:27

Let's make it simpler just as a test:

BCP "select * from master..sysdatabases" QUERYOUT D:\shares\patch2.txt -S GEN-SERVER\EMMSDE -t, -c

Does that error out?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Dhyerwolf
Starting Member

6 Posts

Posted - 2015-02-12 : 20:43:40

Hi,

That one does not error out. It goes to asking for a password (which is exactly what the original one was supposed to do), so it seems like it is on the right track.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 20:48:11

Sorry forgot the authentication switch:

BCP "select * from master..sysdatabases" QUERYOUT D:\shares\patch2.txt -S GEN-SERVER\EMMSDE -t, -c -T

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 20:52:05

If that works, I would test out if the Patch2 stored procedure works. We can use osql.exe or sqlcmd.exe for that.

From a cmd window: sqlcmd.exe -E -S GEN-SERVER\EMMSDE
Hit enter. You should see 1>.
Type in EXEC emdb.dbo.Patch2
Hit enter. Type in GO. Hit enter.

Do you see the stored procedure output (what you would normally see in the file, though formatting will be off)?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Dhyerwolf
Starting Member

6 Posts

Posted - 2015-02-12 : 21:15:41

Yes, all the data does appear to be there on the cmd screen.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 21:19:04

One last try, otherwise I'm stumped:

bcp "EXEC emdb.dbo.Patch2" queryout D:\shares\patch2.txt -S GEN-SERVER\EMMSDE -t~ -c -T

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Dhyerwolf
Starting Member

6 Posts

Posted - 2015-02-12 : 21:23:36

Success! Thank you so much; this was a real lifesaver.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 21:25:06

I think the issue was with the two dots. The account probably has a different default schema, so adding dbo in there fixed it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-12 : 21:25:17

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources