Menu
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.
791
SET NOCOUNT ON prevents the sending of DONEINPROC messages to the client for each statement in a stored procedure.
SQL Server includes the ANSI_WARNINGS database option. This is equivalent to SET ANSI_WARNINGS. When SET ANSI_WARNINGS is ON, errors or warnings are raised in divide-by-zero, string too large for database column, and other similar errors. When SET ANSI_WARNINGS is OFF, these errors and warnings are not raised.
The fastest way to import data into SQL Data Warehouse is to use PolyBase to load data from Azure blob storage. PolyBase uses SQL Data Warehouse's massively parallel processing (MPP) design to load data in parallel from Azure blob storage. To use PolyBase, you can use T-SQL commands or an Azure Data Factory pipeline.
1 SET NOCOUNT ON; Meaning (from here): Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
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.
60
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.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
