4

I get the following error when trying to select a database with php's standard mssql_select_db function: USE statement is not supported to switch between databases. Use a new connection to connect to a different Database. (severity 16). So i'm stumped as in where to go from here.

Connection Code:

$link = mssql_connect('dsn', 'user@server', 'password');

if (!$link) {
   die('Unable to connect!');
}

if (!mssql_select_db('db', $link)) {
   die('Unable to select database!');
}

$result = mssql_query('SELECT * FROM yourtable');

while ($row = mssql_fetch_array($result)) {
   var_dump($row);
}

My system setup is as follows:

  • Ubuntu 12.10
  • PHP5
  • Apache2
  • freeTDS
  • unixODBC
  • SQL Azure

Also I was following this guide.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
camelCaseD
  • 2,483
  • 5
  • 29
  • 44
  • What are the SQL statements that you are sending to the database? – Ivan Golović Dec 20 '12 at 06:46
  • Nothing but a simple list all from a table. `SELECT * FROM someTbl` – camelCaseD Dec 20 '12 at 06:47
  • Are you calling `mssql_select_db` more than once on the same connection (`$link` variable from tutorial) with different database name? – Ivan Golović Dec 20 '12 at 06:55
  • Nope. I'm using the exact same script given in the guide I listed above except filled in my credentials to connect properly. – camelCaseD Dec 20 '12 at 06:58
  • Run the SQL Profiler tool to intercept queries that come from your client (PHP application) to the server, see if the `USE` statement is being sent more than once... – Ivan Golović Dec 20 '12 at 09:24
  • Can you post the PHP code that connects to the database and sends query? – Ivan Golović Dec 20 '12 at 09:34
  • What version and edition of SQL Server are you using? This is a [known issue](http://msdn.microsoft.com/en-us/library/hh974668.aspx) with SQL Azure. If you're not using Azure, please post the connection code, as others have asked. – Pondlife Dec 20 '12 at 14:15
  • @Pondlife it using sql server 10.0 and it is hosted on Azure. – camelCaseD Dec 20 '12 at 15:07
  • 4
    I have no experience with Azure myself, but the error message is very clear: [you can't change databases with `USE` in SQL Azure](http://msdn.microsoft.com/en-us/library/windowsazure/ee336288.aspx). I don't know how or if you can prevent your particular client libraries issuing a `USE` command, but I would try removing the `mssql_select_db()` call completely and specify the correct database name in your ODBC DSN. Hopefully that will connect you directly to the database you want to use. – Pondlife Dec 20 '12 at 15:17

1 Answers1

3

In response to @Pondlife's last comment above and since he has not made it an answer I will do it for him until he posts it himself.

@Pondlife comment:

I have no experience with Azure myself, but the error message is very clear: you can't change databases with USE in SQL Azure. I don't know how or if you can prevent your particular client libraries issuing a USE command, but I would try removing the mssql_select_db() call completely and specify the correct database name in your ODBC DSN. Hopefully that will connect you directly to the database you want to use. - PondLife

camelCaseD
  • 2,483
  • 5
  • 29
  • 44