Strange behavior during deploy

Question

I am working on a GDR project that I inherited from a colleague.

The issue I'm dealing with is this:

• At one point, the project included a stored procedure, let's call it ProcA that included the following code: backup log tempdb with truncate_only

• This proc was later deleted (since SQL 2005 does not support the Truncate_only option) and a new version of the proc (ProcB ) was created and added to the project. This new ProcB did not have the Backup Log statement in it.
• The current state of the GDR project shows the new ProcB and shows no traces of the old ProcA.
• When the project is deployed, it fails with the following error message : Gen-363(330,24)Error TSD02010: Incorrect syntax near truncate_only. Proj_DB.dbschema(0,0)Error TSD01234: The application encountered an unexpected error. To diagnose this problem, enable tracing.
  Proj_DB.dbschema(0,0)Error TSD01234: Failed to import target model Proj_DB. Detailed message A fatal error occurred while the model was being imported.
  
• I ran a search in the entire GDR solution for the words 'truncate_only' and it yielded no results.
• Since I could not figure out the source of this piece of code, I turned on tracing and ran the deploy. Within the output of the trace I found that ProcA was still being included in the deployment.
• I deleted the .dbdml file assuming that there may have been traces of the old proc left behind in the model.
• It still did not work. Every time I deploy, it fails with this error message.
• Why is it still including ProcA in the deployment ?

Answer 1

Hi,

Are you deploying this to a brand-new, empty database? If that's the case and you still see it, most likely the proc definition is still somewhere in the project. You can compile the project again and then search through the generated .sql files to see if you can find the code there.

Thanks,
Alin,
PRAKTIK Consulting
TFS Hosting and TFS Consulting Services.

Answer 2

I have my deployment settings set to 'Generate deployment script only'. My deployment target is set to an existing databas e. I cannot get to the point where a .sql file is generated because the deploy fails. I have attampted a compile several times and the compile succeeds. I have searched for the proc through the entire solution but it yields no results. I have even searched within the .dbschema file that gets generated after the build and I see no sign of the proc there.

Answer 3

If ProcA is not in the project, it may be possible that the deployment fails to parse the actual SP in the existing database - can you check if the old proc is in the existing database and delete it?

Thanks,
Alin,
PRAKTIK Consulting
TFS Hosting and TFS Consulting Services.

Answer 4

Yes indeed the proc does exist on the target database. However this seems strange. Is the tool so sensitive ? So if there is any code in any of my stored procedures on the target database that does not comply the deploy will fail ??
Isn't there a way to workaround that ? Is there a deployment setting to ask it not to do that ?

Answer 5

Hi,

What actually happens is that Visual Studio is trying to create an INCREMENTAL deployment script - so, before creating the deployment script it does an analysis of the existing database (to determine what are the changes between them). It is possible an error is raised as it is checking procA. If you delete the procA and it works, that will tell for sure that's the case.

Thanks,
Alin,
PRAKTIK Consulting
TFS Hosting and TFS Consulting Services.

Answer 6

Yes that worked. So does this happen if there is ANY kind of error in any database object on the destination database ? Or are there specific kinds of errors that triggers the failure of the deployment ?
This means that if I wanted to generate a deployment script by comparing my project with a Production database instance (to which I do not have direct write access), the deployment could fail because of some code in a proc on the target production database ? That concerns me.

Answer 7

Hi,

Is is not just some code, it is some broken code. It is my guess that these issues don't happen for all broken code, though.

I would look at this as a good thing. The proc was already broken in production, yet, nobody knew. At least now, the deployment will raise that as an issue, so you can fix it.

Thanks,
Alin,
PRAKTIK Consulting
TFS Hosting and TFS Consulting Services.

Answer 8

Alin is correct, this happens during deployment, when we reverse engineer the schema of the target database in to a model, in order to compare it, to detect the differences. If you have an object on the target server that was there since it was upgrade from version to version, and now contains syntax which is no longer supported, it will raise an error like this. Which I agree is not discoverable enough with regards to why it is raised and how to resolve it.

But it does raise a valid error or warning with regards to objects that are using syntax which is no longer valid.

---

GertD @ www.DBProj.com