0

I am using sqlcmd to query a SQL server database from a UBUNTU machine (an ODBC driver was installed for this purpose)

If I pass the following command: 

sqlcmd -S xyz -d xyz -U xyz

I am prompted for a password, and I can then successfully query the database in question. But, if pass the command below, I get the message 'Login failed for user ****':

sqlcmd -S xyz -d xyz -U xyz -P xyz

I need to be able to send the password in one pass, as I need to pass an input query file and send the output to a specific location. Once I log into the database by waiting for a password prompt, I can no longer specify input/output files. Any insights would be much appreciated

aizaz
  • 3,056
  • 9
  • 25
  • 57
ALB
  • 3
  • 1
  • 3
  • This works fine for me: `sqlcmd -S MyServer -U MyLogin -P password1$`. You might be able to check the error log for more detailed information on [why the login failed](http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx). And which version of SQL Server do you have? You can actually specify input and output files [interactively](http://msdn.microsoft.com/en-us/library/ms162773.aspx) in sqlcmd using `:r` and `:out`. – Pondlife Jun 04 '13 at 16:07
  • Hi Pondlife, thanks a lot that was really helpful. I've managed to use :r and out: to get an output in the specified location, but struggling to format the output e.g. if I build the following into the input file: -o -h -1 -s , -k2 -W I get a blank output csv. Do you have any pointers on getting a comma separated output? Thanks again – ALB Jun 04 '13 at 17:48
  • See [this question](http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd/425463#425463) but it won't work well if your data contains commas. In my experience sqlcmd is a very poor tool for generating CSV or any other other formatted output. For CSV it's better to use [bcp](http://msdn.microsoft.com/en-us/library/ms162802.aspx) if possible, but if you're copying data to another SQL Server then use the native (binary) file format to avoid formatting issues completely. Otherwise, a script in your preferred language usually gives better results. – Pondlife Jun 04 '13 at 18:33

1 Answers1

1

Have you tried it without the space between the -P and the value? The docs show them glued together.

souplex
  • 981
  • 6
  • 16
  • Hi, yes but the same login failed message is returned. The password contains $ character and a number, but these appear to be allowed: http://msdn.microsoft.com/en-us/library/ms161962(v=sql.105).aspx – ALB Jun 04 '13 at 15:10