Question

How to disable SQL script compile time errors in VS 2013 Database Project 24 Jun 2016

0

=

1.03

+

0

No specific Bitcoin Bounty has been announced by author. Still, anyone could send Bitcoin Tips to those who provide a good answer.

0

How to disable SQL script compile time errors in VS 2013 while trying to build solution that includes "SQL Server Database Project"? I want to disable SQL validation b/c it is really annoying to see those SQL compile errors in the build.

We are not interested in "building" SQL files. We use that DB project as a structured storage of SQL scripts since we have custom SQL deployment process. Those SQL errors are causing build status to Fail, so it does not start Default Project automatically but instead shows the dialog box asking if I want to run a "failed" build.

How to suppress Database Project errors and warnings in Visual Studio 2013?

Compile Time Errors in SQL files - Visual Studio 2013

enter image description here

Error   1   SQL46010: Incorrect syntax near '<Procedure name>'.   C:...\Src\QrDatabase\Procedures\Example.sql 8   17  QrDatabase
Error   2   SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.   C:...\Src\QrDatabase\Procedures\Example.sql 1   1   QrDatabase
Error   3   SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.   C:...\Src\QrDatabase\Scripts\sp_gen_all.sql 2   1   QrDatabase
Error   4   SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.   C:...\Src\QrDatabase\Scripts\run_sp_gen_insert_update_delete.sql    3   1   QrDatabase
Error   5   SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.   C:...\Src\QrDatabase\Scripts\drop_gen_sp.sql    1   1   QrDatabase
Error   6   SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.   C:...\Src\QrDatabase\Scripts\drop_gen_sp.sql    7   1   QrDatabase
Error   7   SQL71006: Only one statement is allowed per batch. A batch separator, such as 'GO', might be required between statements.   C:...\Src\QrDatabase\Scripts\drop_gen_sp.sql    13  1   QrDatabase
Warning 8   SQL71502: Procedure: [dbo].[sp_gen_update] has an unresolved reference to object [dbo].[sp_pkeys].  C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  32  18  QrDatabase
Warning 9   SQL71502: Procedure: [dbo].[sp_gen_update] has an unresolved reference to object [sys].[schemas].   C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  54  13  QrDatabase
Warning 10  SQL71502: Procedure: [dbo].[sp_gen_update] has an unresolved reference to object [sys].[schemas].   C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  122 13  QrDatabase
Warning 11  SQL71502: Procedure: [dbo].[sp_gen_update] has an unresolved reference to object [sys].[tables].    C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  55  18  QrDatabase
Warning 12  SQL71502: Procedure: [dbo].[sp_gen_update] has an unresolved reference to object [sys].[tables].    C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  123 18  QrDatabase
Warning 13  SQL71502: Procedure: [dbo].[sp_gen_update] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [sys].[schemas].[SCH]::[schema_id], [sys].[schemas].[schema_id] or [sys].[tables].[SCH]::[schema_id].  C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  55  35  QrDatabase
Warning 14  SQL71502: Procedure: [dbo].[sp_gen_update] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [sys].[schemas].[SCH]::[schema_id], [sys].[schemas].[schema_id] or [sys].[tables].[SCH]::[schema_id].  C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  123 35  QrDatabase
Warning 15  SQL71502: Procedure: [dbo].[sp_gen_update] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [sys].[schemas].[ST]::[schema_id], [sys].[tables].[schema_id] or [sys].[tables].[ST]::[schema_id]. C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  55  51  QrDatabase
Warning 16  SQL71502: Procedure: [dbo].[sp_gen_update] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [sys].[schemas].[ST]::[schema_id], [sys].[tables].[schema_id] or [sys].[tables].[ST]::[schema_id]. C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  123 51  QrDatabase
Warning 17  SQL71502: Procedure: [dbo].[sp_gen_update] has an unresolved reference to object [sys].[columns].   C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  56  18  QrDatabase
Warning 18  SQL71502: Procedure: [dbo].[sp_gen_update] has an unresolved reference to object [sys].[columns].   C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  124 18  QrDatabase
Warning 19  SQL71502: Procedure: [dbo].[sp_gen_update] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [sys].[columns].[ST]::[object_id], [sys].[schemas].[ST]::[object_id], [sys].[tables].[object_id] or [sys].[tables].[ST]::[object_id].  C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  56  36  QrDatabase
Warning 20  SQL71502: Procedure: [dbo].[sp_gen_update] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [sys].[columns].[ST]::[object_id], [sys].[schemas].[ST]::[object_id], [sys].[tables].[object_id] or [sys].[tables].[ST]::[object_id].  C:...\Src\QrDatabase\Scripts\sp_gen_update.sql  124 36  QrDatabase

Tags: vs-2013, ms-sql-server, msbuild

Edited May 13'15 at 02:07

alex

Created Apr 14'15 at 18:21

alex

ADD COMMENT | SEND TIPS

2 answers

2

=

10

=

$4.01

10 tips with total amount of 7.2200003 mBTC($4.01 USD) have been sent by lampego, lampego@qrid, Anonimous, alex@qrid

Below are 2 solutions to this problem:

Manually editing .SQLPROJ file to redefine "Build" and "Rebuild" targets - solves it for all existing and future SQL files to be included in that particular database project. Here is a good article to learn more about customizing Visual Studio / MsBuild behavior
selecting all existing SQL files and changing "Build Action" property to "None", and then remember to use "Script(Not in build)" template for adding all new SQL scripts (less convenient but a bit easier to understand)

Solution 1 - Editing .SQLPROJ file to redefine "Build" and "Rebuild" targets:

While your solution is closed, open .SQLPROJ file in a text editor and remove these sections that import definitions of SqlBuild Targets:

  <!-- REMOVE:  -->
  <Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />

Then define your own "Build" and "Rebuild" targets that simply do nothing - in my case I put them at the same place where import tags were located (the order of things may be important in PROJ files). Insert these lines :

<!-- INSERT:  -->
<Target Name="Build"  />
<Target Name="Rebuild"  />

Below is a screenshot comparing Before and After in a Source Control

...

enter image description here

...

Solution 2 - Changing "Build Action" to "None" for all files:

Select all SQL files in your project, then right-click and select "Properties" then set Build Action to "None" instead of "Build"

enter image description here

After that, for each new Script you have an option to make it "Build" or "Not in Build":

enter image description here

SEND BITCOIN TIPS

Edited May 14'15 at 05:20

lampego1@qrid

Created Apr 14'15 at 19:48

alex

ADD COMMENT

phpjaxtest4@qrid Dec 16'15 at 04:14

Good answer. Thanks

Reply | Send Tips

lampego1@qrid Dec 21'15 at 13:49

0.23 mBTC | 0.1 USD → phpjaxtest4@qrid

Some message for new tip

Reply | Send Tips

lampego1@qrid Feb 10'16 at 00:42