none
VS Project build fails on TSQL Code in script.

    Question

  • Hi there,

    I have the following issue on which I've done some research, but nothing helps.

    The database project I am working on compiles everything but one script. The scripts folder has 3 scripts, a controller that calls the remainder two.

    One of these scripts fail on SQL syntax which is weird because they seem to be okay and SSMS would not complain. Having experimented a little I found that any SQL statement fails on syntax, there must be a configuration issue here.

    What I have tried so far:

    -add the script as a stored procedure and reference it within the script, so that the script will have one line only (EXEC usp_BuildTestData - it is needless to say that the stored procedure compiles well...)

    -create a new script file (without build) from within VS.

    -check if the script file BuildAction property is set to None

    -load the script file and switch off the SQLCMD mode in t-SQL Editor Execution settings.

    Build still fails with SQL72007: The syntax check failed 'Incorrect syntax near EXEC' in the batch near 'EXEC usp_BuildTestData '.

    I thought this was a common error or at least something I could easily find a solution for.

    Unfortunately VS is somewhat new to me.

    Any ideas are appreciated.

    Versions:

    Visual Studio Pro 2012 (Version 11.0.50727.1 RTMREL) (.Net FW: 4.5.50709)

    Target database version: SQL 2008R2 (this is set in the project's target platform setting)


    • Edited by László Kátai-Pál Monday, February 9, 2015 10:15 AM
    • Moved by Tina-Shi Thursday, February 19, 2015 1:30 AM I did not know which forum is support this issue
    Monday, February 9, 2015 10:14 AM

Answers

  • Hi  LASZLO,

    Thanks for your reply.

    >>So 1 of the invoked scripts fail on the SQL syntax. This is a simple line EXEC usp_BuildTestData.

    From your description, since I am not the TSQL experts and I think that the issue maybe related to the TSQL, so I suggest you can consult this issue to TSQL forum: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql , you will get better support.

    In addition, I find a link about how to Post deployment Scripts using SQL syntax as reference:

    https://msdn.microsoft.com/en-us/library/jj889461(v=vs.103).aspx

    Thanks for your understanding.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 13, 2015 8:10 AM

All replies

  • Hi LASZLO,

    According to your description, to ensure if the issue is related to your VS IDE or the TSQL, please tell me more message about your issue.

    For example:

    (1) How did you create a database project in the Visual Studio Pro 2012? If possible, I suggest you could share me detailed steps about how to create a database project.

    (2) What script you create in the VS, is it a database script?

    (3) If possible, please share your TSQL script code for us so that we will further understand your issue.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Tina-Shi Tuesday, February 10, 2015 10:37 AM
    Tuesday, February 10, 2015 10:37 AM
  • Hi Tina,

    Thank you for getting back to me. I inherited the project file in subject therefore I cannot tell how it was created. But, I tried re-creating the database project from scratch that resulted in the same error. Steps below:

    -Select New Solution/ new SQL Server database project

    -Copy the sql files and folders into solution folder then map them into the project file

    -build the project.

    The database is based upon C#.

    The idea of the post deployment script is that it builds test data in the freshly deployed application. It uses a linked server that points to production and selects some data.

    I continued experimenting with the post deployment script and found something interesting.

    So 1 of the invoked scripts fail on the SQL syntax. This is a simple line EXEC usp_BuildTestData.

    The other that compiles well is an ALTER VIEW. I pasted the EXEC... line after this and it worked. It builds now. (I have removed the remaining empty script file, so I have 1 post deployment script file now that invokes 1 script only.

    I tried comparing the two files but concluded in the fact that each property is the same (Buildaction=None, CopyAlways) in both files.

    This proves to me that the SQL code was correct, I did not leave obvious mistakes in it.

    The "bad" script file fails on syntax, regardless of what I give in. I tried recreating by clicking 'Add new item' then selecting Script (Not in build). Did not help unfortunately. Once referenced in the PostDeployment script, build fails.

    Thursday, February 12, 2015 8:33 AM
  • Hi  LASZLO,

    Thanks for your reply.

    >>So 1 of the invoked scripts fail on the SQL syntax. This is a simple line EXEC usp_BuildTestData.

    From your description, since I am not the TSQL experts and I think that the issue maybe related to the TSQL, so I suggest you can consult this issue to TSQL forum: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql , you will get better support.

    In addition, I find a link about how to Post deployment Scripts using SQL syntax as reference:

    https://msdn.microsoft.com/en-us/library/jj889461(v=vs.103).aspx

    Thanks for your understanding.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 13, 2015 8:10 AM