SQL Server - Spport for SQL Azure Database

As MS SQL Server support works like a breezeI tried to connect to a SQL Database hosted on Microsofts Azure datacenters.
- Connectkion works fine using TCP
* when I then try to open database resources I get the error that the USE statemant was not supported on Azure SQL Database (even though the database is already selected in the connection)

SOLUTION: maybe it would be sufficient to just leave away the USE statement in this situation to add support to Azure hosted SQL Databases as well.

I'd volunteer to test the case. If you'd need further info, feel free to get in touch (auch auf Deutsch ;)

Cheers for your great work!

Same problem here:

/* SQL Error (40508): USE statement is not supported to switch between databases. Use a new connection to connect to a different Database. */

Guidelines for Connecting to Azure SQL Database Programmatically:

https://msdn.microsoft.com/en-us/library/azure/ee336282.aspx

Same problem here. Once this feature is in and working I will be happy to donate. I need Azure SQL access.

I have no access to an Azure host. What I would need is to know what it returns from a version query:

SELECT @@VERSION;

HeidiSQL could detect an Azure host and rely on which database the user has selected in the session manager. Or, as a fallback, just use the "master" database, which seems to be the default. When clicking or expanding a database tree node, HeidiSQL could see that it's an Azure host and leave out the USE query.

I ran that query and this is the return

Microsoft SQL Azure (RTM) - 12.0.2000.8 Mar 16 2015 08:33:43 Copyright (c) Microsoft Corporation

If needed I could give you temporary access to an Azure DB or test for you.

Sounds good!

You could also, (maybe more robustly? according to this post) go for

Select SERVERPROPERTY('edition');

wich will return
SQL Azure

If you need a Test-DB, just drop me a note per email and I'll send you the credentials.

Btw: thanks for incorporating this so fast.

r4938 does the following:
* it passes the text from the "Database(s)" box on the session manager into the ADO connection string
* then it bypasses the USE <dbname> query if "azure" is found in the version string

Please update to the latest build and test on an Azure host.

If that's not working: A public or SSH-secured Azure host for testing purposes would be nice.

Yeah! Looks great on the first try. :-))

I'll try it during the next days and will see if there are any other issues. Will be glad to report.

One thing I found:
At the first glance there seems to be an issue with the data view of tables having geography columns.

How can I send you the credentials to the Azure DB?

Sorry, just found your email in the impress. You'll have the credentials tomorrow via mail.

Seems to work great. I will play with it some and let you know if anything is a problem. I do not see any geography columns that art-ist has.

@tlemmons: Geography (DbGeography or Spacial(Geography)) is a spacial datatype that supports geometrical and geographical indexing and operations (like PostGIS).

The Problems, but, dont seem to be with SQL Azure but a general lack off support for this datatype on SQL Server. I found to have the same issue on a local SQL Server.

In the table/general-tab, even though Spacial(geometry) is a known type, Spacial(geography) is not in the list and Greomatry columns are shown as NVARCHAR.

The data-tab then fails with error:

SQL Fehler (6522): A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 146744 bytes to a T-SQL type with a smaller size limit of 512 bytes.
System.Data.SqlServer.TruncationException: 
at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)
.

as HeidiSQL trys to cast the value to varchar (actually cool, but here it fails)

LEFT(CAST("Boundary" AS NVARCHAR(256)), 256)

just using

"Boundary"

could get HeidiSQL into troble dealing with the binary result
but

LEFT("Boundary".ToString(), 256)

works fine.

Here a complete working query for reference:

SELECT TOP 100  "Class",  "Id",  "Name", LEFT("Boundary".ToString(), 256) FROM "onyourway"."Lookup"."BaseMapFeatures";

Summary:
Support of Geography doesen't seem to be a Azure issue.
Solution needs modification in schema recognition to know datatype Spacial(geography) and cahnge for Select generation from CAST to ToString().

And hey, as many databases will not be going to use these special types:

Experimental support for SQL Azure by HeidiSQL can be definitley be considered as achieved.

Hurray!

r4940 readds the "USE <dbname>" query in Azure mode, which seems to be required for selecting the only preselected database. Plus it adds an Azure icon in the tree and in the statusbar.

How can I fix the non-working uptime detection?

SELECT DATEDIFF(SECOND, "login_time", CURRENT_TIMESTAMP) FROM "master"."dbo"."sysprocesses" WHERE "spid"=1;
/* SQL Error (40515): Reference to database and/or server name in 'master.dbo.sysprocesses' is not supported in this version of SQL Server. */

I didn't investigate that too deeply. But as this is a shared instamce of SQL Server providing your database as SAAS. I'm pretty shure, MS doesen't want you to know the actual server uptime. Obfuscating the actual implementation is what cloud computing is all about, isn't it ;-)

I did try to open a seperate connection to the master db. And make the query from there. But you don't have permission to view sysprocesses from there as well.

I think displaying "Uptime: unknown" as you do now, is a fair solution. Especially as no SQL Azure user is going to be responsible for server uptine himself and I'd guess MS would transparently fail over the DBs to a different "physical" server instance if needed.

What version are you guys using that works? I'm using the nightly and I can't get azure to work.
Connecting via TCP I am still seeing the USE errors and others:

SELECT @@SPID;
/* Connected. Thread-ID: 52 */
SELECT DATEDIFF(SECOND, "login_time", CURRENT_TIMESTAMP) FROM "master"."dbo"."sysprocesses" WHERE "spid"=1;
SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion');
SELECT "name" FROM "sys"."databases" ORDER BY "name";
/* Entering session "AZURE" */
SELECT *, SCHEMA_NAME("schema_id") AS 'schema' FROM "ATLAAS_Reporting"."sys"."objects" WHERE "type" IN ('P', 'U', 'V', 'TR', 'FN', 'TF');
/* SQL Error (40515): Reference to database and/or server name in 'ATLAAS_Reporting.sys.objects' is not supported in this version of SQL Server. */

USE "ATLAAS_Reporting";
/* SQL Error (40508): USE statement is not supported to switch between databases. Use a new connection to connect to a different database. */

that is, version 9.2.0.4961

Same results on Azure SQL v11 and v12.

You have to specify the database name in the session setting, then you should be able to connect successfully to an Azure host.

AH, so you can connect to specific databases but not AzureSQL Servers.

Cool enough! Thanks for all your hard work Ansgar.

@ansgar,

I am having quite a few small problems with the Azure SQL that you put together. They make it hard to use very much and I would sure love to see them fixed. If you still need an Azure DB I could make one available to you even with some of my data and how to make the errors pop up. Just let me know if that would help or if you have time.