Escaping Backslash causes complication error

This is a very strange situation. The biml file I created worked fine when connecting to a local sql instance, but it gave me the compilation error - Incorrect syntax near ')' - after the connection string is changed to a remote one with a named instance.

The following code works fine.

string CnSrcDB = "Data Source=(local);Initial Catalog=Reporting;Provider=SQLNCLI11.1;Integrated Security=SSPI;"; 

The following code showed no compilation error but, as expected, did not produce any result due to the wrong connection string.

string CnSrcDB = @"Data Source=marketingsql\\sql1;Initial Catalog=Reporting;Provider=SQLNCLI11.1;Integrated Security=SSPI;";

Both the following two variations gave me the compilation error.

string CnSrcDB = "Data Source=marketingsql\\sql1;Initial Catalog=Reporting;Provider=SQLNCLI11.1;Integrated Security=SSPI;"; 

string CnSrcDB = @"Data Source=marketingsql\sql1;Initial Catalog=Reporting;Provider=SQLNCLI11.1;Integrated Security=SSPI;";

I am using Visual Studio 2015 with Biml Express 2017 Build 5.0.61915.0. It looks like not a C# error but a Biml error. Because no biml code is generated, except for the error - Incorrect syntax near ')' , I do not know where the issue is. I believe this line of code causing the compilation error, but still do not know why.

var sourceConnection = SchemaManager.CreateConnectionNode("SchemaProvider",CnSrcDB);
0

Comments

3 comments
  • Could you also upload your biml code with any sensitive information redacted? If you do not feel comfortable posting it here, feel free to email it to support@varigence.com 

    0
    Comment actions Permalink
  • jacob12, Thanks for the response. I emailed the script to support@varigence.com.
    0
    Comment actions Permalink
  • I finally figured out where the issue is. The root cause is at a piece of code, which generated a sql query and is not related to those I posted before. It is very strange how Biml Express produces those compilation errors. One possible explanation is that, when the connection string is wrong, the query is not executed and therefore no compilation error is produced. The real lesson here is how I debugged the issue. Basically, I created a new blank Biml file and copied code over, one piece a time. After each copy, I saved the file and let Biml Express compile. I repeated the process until I ran into the error again. So constantly saving the Biml file and confirming the output is the best approach to debug, especially when the compilation error is not informative.
    0
    Comment actions Permalink

Please sign in to leave a comment.