SQL query results to file script

Question

I am running SQL express on a windows 2008 R2 server and trying to write a script to periodically query a database and save the results to a file. I have used:

sqlcmd -Q SELECT DISTINCT Date from MMD_Scale ORDER BY Date ASC -o testresults.txt

and

bcp "select distinct Date from MMD_Scale ORDER BY Date ASC" queryout testresults.txt -c -T

The problem is that both times it comes back saying the table MMD_Scale cant be resolved. I have verified the query in the Server management studio. There was also some online sources that say to specify the database with -D but when I add it it says -D is obsolete and is ignored. Any help is appreciated.

Can you run that query interactively? If so, you might have to qualify the table name with the server, schema, and/or owner information. — Dan Bracuk
– Dan Bracuk, Commented Sep 26, 2013 at 15:24

RB. · Accepted Answer · 2013-09-26 15:26:05Z

4

The issue is that when sqlcmd or bcp connects, they will connect to your default database. In this instance, it appears that is not the database that your table resides in.

Try putting "USE [database_name]" in your query. e.g.

sqlcmd -Q "USE MyDatabase; SELECT DISTINCT Date from MMD_Scale ORDER BY Date ASC" -o testresults.txt

Or fully-qualify the table name:

sqlcmd -Q "SELECT DISTINCT Date from MyDatabase.dbo.MMD_Scale ORDER BY Date ASC" -o testresults.txt

answered Sep 26, 2013 at 15:26

RB.

37.4k13 gold badges103 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dpw · Accepted Answer · 2013-09-26 17:14:12Z

0

The -D flag is not the same as -d. Try sqlcmd -d YourDatabase -Q SELECT DISTINCT Date from MMD_Scale ORDER BY Date ASC -o testresults.txt

answered Sep 26, 2013 at 17:14

dpw

1,5869 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL query results to file script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related