Azure SQL Data Warehouse ''NoCount' Error

Question

I just got approved for the Azure SQL Data Warehouse Preview, and just finished "provisioning" my new server and database. I followed a link to a 'Getting Started' page from Microsoft (Get started: Connect ...), and read that the two best (only?) ways of querying the database were through sqlcmd and Visual Studio 2013.

I ignored this advice first, and fired up SSMS, connected to the database, and then tried to open a new query window. I immediately received the following error: 'Unable to apply connection settings. The detailed error message is: 'NoCount' is not a recognized option.' After clicking OK, the query window did open up, but many T-SQL statements did not work such as a simple:

CREATE TABLE dbo.tblTest (acct_id nvarchar(255) NOT NULL)

I would receive a 'Command(s) completed successfully.' message, however no table was created.

Ok, I fired up Visual Studio 2013, connect to my Azure SQL Data Warehouse, and open it up in SQL Server Object Explorer. I right-clicked the specific database, and selected 'New Query', enter the same T-SQL CREATE TABLE statement as above, then hit execute. Once I hit execute, I get the exact same error message as above. This time around I was able to open a new query window at least, but after attempting to execute the query I still get the 'NoCount' is not a recognized option error.

I'm completely new to data warehouses, and still very much a beginner with T-SQL and SQL Server as well. But, I haven't been able to find anything that would explain why I'm receiving these errors, and right now I'm basically at a standstill until I can understand what's going on. Any help is appreciated, thanks.

Answer 1

I've experienced this. Your connection isn't actually recognised as a SQL DW connection. I bet your query window is a .sql file, not a .dsql as it needs to be.

Go back into the Azure portal and use the link to connect using SSDT from there. You should get a connection in the SQL Server Explorer pane which looks different, and when you start a New Query based on it, you should get a .dsql window, not a .sql one.

Answer 2

Have you checked the version of SSDT that you are using? It sounds like you may be on a version prior to 12.0.50623, which doesn't take into account that SET NO COUNT is not supported by SQL Data Warehouse at the moment. Newer versions of SSDT have adjusted for this. We recommend updating SSDT here and then connecting using the SQL Server Object Explorer with the directions in our documentation.