This year I plan to create a Series of posts on various technical topics, one of which is Big Data Analytics. These posts will be based on first hand experiences I have had on projects, and tricks and tips on coding for Big Data. Hope this series is helpful and actually comes in use for other developers; sharing my own knowledge with the on-line tech community sounds like a neat idea to me :). I’m also open to feedback or do correct me if you feel I have made an error, so feel free to comment below.
When working on a Data Factory project it is common to use SQL Scripts either via stored procedures or standalone. This is more commonly known as ‘adding a post deploy script’ as part of a database project and then do a build.
This is all well said and done, however, with this comes a common known issue, which can take up so much time (for no reason). It turns out that the C# compiler will moan (at runtime) when you add the SQL script(s) to the project on Visual Studio. There error will be something like:
‘Error SQL71006: Only one statement is allowed per batch. A batch separator such as ‘GO’, might be required between statements.’
Any syntax error or something along the lines of the above will occur. Make sure the SQL Script is first tested and run in SQL Server Management Studio (SSMS) to make sure there isn’t an actual issue with the script. This is a nonsensical error.
To fix this error:
- Right click on the .sql script file and select ‘Properties’
2. Set the Build Action to ‘None’
3. Now rebuild and run
Basically, by setting the build action to ‘None’ the script(s) will not compile at runtime, overcoming the error. This is a known issue and let’s hope it gets resolved soon. The above is a workaround for the error and not a permanent fix so do bear this in mind. If the script runs in SSMS there is nothing actually wrong with the SQL, it is just a compiler issue.