An error occurred while attempting to deploy project scripts Sqlcmd: Error: Syntax error at line xxx

atzino

I'm somewhat new to Redgate. I've used it for version control with a new small database, but I'm attempting to import an existing database into a Database Solution in Visual Studio 2015. When I click the Deploy Project button, my SQL Script runs, but I get the following error message:

An error occurred while attempting to deploy project scripts:
Sqlcmd: Error: Syntax error at line 182867 in file 'C:\tfs\Database Projects\obj\Debug\My! DB.sql

Each time I click the Deploy Project button, the line number containing the error changes. Any idea where I'm going wrong? Any help is much appreciated.

way0utwest

The exclamation point disturbs me, but I wouldn't think that would cause you an issue. Does the deployment complete? Meaning objects are moved?

How are you creating the file and deploying it? There's a Deploy with SQL Compare, but I don't see a Deploy Project button, unless I'm looking somewhere else. Can you provide more details about what you're doing to get to this point? That folder structure looks funny as well. Is this a SQL Source Control project, or are you trying to connect to a database project from SSDT/Visual Studio in TFS?

atzino

I haven't made any changes to the target yet. I created a new database project based on an existing database. When I imported the existing database, the migration script was automatically generated to create the database objects that already existed, but some of the views in the database reference fields in tables that no longer exist. I ran the script repeatedly to find all the missing objects in the database and commented out objects that no longer exist. When I got closer to the end of the migration script and clicked the Deploy button, I started getting messages that referenced the line numbers in the file path above instead of referencing the line number in Visual Studio. This is a SQL Source Control project and I am trying to connect to a database project from SSDT/Visual Studio in TFS.

atzino

Is it possible to bypass the initial migration script and test a change to my database? The necessary database objects were created successfully in my shadow database. The errors I'm getting now are referencing objects that have already been created and cannot be recreated.

Tianjiao_Li

Hi @atzino

Thanks for your clarification.

Do you use ReadyRoll. Can you please share a screenshot where we can see the error?

Thanks.

Oluwaseyi

I have been battling with this issue and i will love your input on how to solve it, I am using TeamCity as my CI tool, and in my build process, i use MSBuild as the runner type, and i have set all the necessary parmeters, but on building, i am seeing this error: 
C:\Program Files (x86)\MSBuild\ReadyRoll\ReadyRoll.Data.Schema.SSDT.targets(440, 5): An error occurred while attempting to create a patch script: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

way0utwest

Oluwaseyi, please start a new thread for new issues. In your case, this is a connection to SQL Server issue, not really a built issue. Somehow you have incorrect auth set for your agent or build task.