AZUR - Export Using BCP with Query

Question

I want to export data from a database Azur to an Excel file. To do this, i use the command :

bcp [DatabaseName].[Table] OUT C:...\Test_Export.xls -c -U [email protected] -S tcp:ServerName.database.windows.net -P xxxxxxxx

--> It Works

BUT, when i want do the same with SQL Query in the command, like this :

bcp "Select field1, Field2 FROM [dbo].[ForecastTrialDisag]" QUERYOUT C:..\Test_Export.xls -d [DataBaseName] -c -U [email protected] -S tcp:ServerName.database.windows.net -P xxxxxx

I have errors :

SQLState = 37000, NativeError = 4060 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open database "[DataBaseName]" requested by the login. The login failed.

SQLState = 28000, NativeError = 18456 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'UserName'.

I don't understand why it doesn't work with a SQL query. UserName, databaseName and password are OK.

Thank you for your help,

Simon

Answer 1

I believe the username is in the format of: "user@server" without the "databases.windows.net" part at the end.

Have you tried that?

Answer 2

If the password is a strong one - contains punctuation for example, in my case I had a * and and ^ character in the password - than the use of double quotes for the password is needed:

bcp "Select field1, Field2 FROM [dbo].[ForecastTrialDisag]" QUERYOUT C:..\Test_Export.xls -d [DataBaseName] -c -U [email protected] -S tcp:ServerName.database.windows.net -P "xxxxxx"

Answer 3

I struggled with this for some hours by using the main db admin user define in Azure and never got it to work - I'm sure I was missing something simple.

What got me over the hump was to use SQL and CREATE USER plus GRANT a user inside of SQL, the kind of user that's not apparently visible in the Azure portal but is easily visible inside of SQL Management Studio.

This worked super well for me.