Azure SQL Data Warehouse ''NoCount' Error

6
votes

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.

3
sql-serverazureazure-sql-databaseazure-sqldw
nocount is an option that disables the printing of the number of rows affected by an insert, update, or delete after the query is executed. You can set it to on or off. But it sounds like you're starting with a fresh, blank query window and receiving the message before you execute anything? – rwking
Can you please post your connection string? – rwking
Yeah, I'm aware of what NoCount is, and you are also correct that I'm starting with a completely fresh, blank query window and in no way using SET NOCOUNT ON|OFF. As for the connection string... I'm not sure how I would do that. I'm connecting by filling in the inputs from a connection window which asks for the server, and then my user and password. I can also optionally set the specific database to connect to, but I don't know where I'd find the "connection" string I'm using. – Daniel A. Burke

3 Answers

7
votes

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.

2
votes

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.

0
votes

I am running Visual Studio 2015 Enterprise, and what I had to do to get this error resolved was install SSDT October 2015 Preview in Visual Studio. The link I used is: https://msdn.microsoft.com/en-us/library/mt204009.aspx

Prior to installing this, I had the latest released version for VS2015...had to install the Preview to get it to work. Also, it solved the issue where I was not able to see ANY of the tables in the Azure Data Warehouse database - just a couple of views, which made querying difficult (could still get a list of available tables from sys.tables)

I hope this helps somebody!