Hi there guys.

The whole thing started when I tried to modify a foreign key constraint. Then when trying to deploy the package I got an exception saying that a certain field (which is the primary key) could not be renamed because it was a member of a list. I have removed the constraint and add it again, and the same error.

After that I have removed the whole table and recreated it. Not I get four exceptions: One that says that a certain view (which depends on the table) could not be created because the table is not there. I have remove and recreated the view too, but still the same error.

The other two exceptions complain that they cannot verify the existence of the indexes in the table and view which I have removed and recreated.

The fourth complains about not being able to verify the existence of foreign keys in the table that has been removed and recreated.

Is there a chance that I'm doing something terribly wrong with DDT? I have imported the tables from a SQL2005 database and created all indexes and stored procedures and views manually in the DDT. After that all I do is modifying the schema. Is there a certain way I must be using the DDT tool? Is this because DDT is a rather new tool?

I have attached a rar file that contains my database, my StrataFrame database, the package, and the result log files of the deployments, in case you'll need to look more into it.

Thank you in advance.

I will look at your packages and see what is going on with your data.

Ths DDT is not that new of a package and is a tool that we use every day in the field distributing our medical software as well as StrataFrame structures and even our CRM structures....this has been used thousands of times in the field on practically every imaginable platform that you can think of.  So the DDT is a very solid and tried tool. 

Generally when you have a problem deploying something through the DDT it generally is because of some type of contraint of other rule that is being violated on the SQL Server side.

We use SMO (SQL Server Management Objects) to deploy the structures....so when you have errors, they are generally related to some SQL violation.  But I will look at your packages.

I cannot import your package because there is a password on it and you did not provide it in your post or your .rar file.  You can email the password if you like.  Thanks.

OK...there is nothing wrong with the DDT...the message that was given to you was given by SQL Server itself and it was related to the Fk_Employees_Transactions relationship that you had setup.  SQL Server was telling you that this was not a safe relationship because it would produce multiple-cycles on the cascades due to other relationships on these same fields from other foreign keys that were setup.  If you remove this relationship from the Employees table you will see that it will deploy without issue.  I did not sit down (nor is this something that we do) and analyze your table structures past determining where your issue resides.  I would revisit my relationships on my foreign key constraints to prevent a circular relationship from occuring.

You can see in the TendersDB_results14.txt that the exception I get says that the package cannot create the TransactionsFullView because the Transactions table does not exist.

FailedOperationException       
X 1/2/2008 2:53:52 μμ ->   Create failed for View 'dbo.TransactionsFullView'.        
X 1/2/2008 2:53:52 μμ -> ExecutionFailureException       
X 1/2/2008 2:53:52 μμ ->   An exception occurred while executing a Transact-SQL statement or batch.       
X 1/2/2008 2:53:52 μμ -> SqlException       
X 1/2/2008 2:53:52 μμ ->   Invalid object name 'dbo.Transactions'.

Also the rest of the exceptions say that the package cannot verify indexes for the Transactions table, foreign keys for the Transactions table, and indexes for the TransactionsFullView view, which just failed  to create.

It looks like the package is trying to create the view and the table in the wrong order or, there's something wrong with my database. Either way I thought you might need to have as much information as I could provide you, to find out what is going on. I did this, because this is what I ask when one of my clients reports something that looks like a bug in my applications. I never wanted you analyze my table structure.

Take care

George,

I think that you have a lot of "residue" or "trash" in the mdf or ldf of that current deployment of your package file.  I think that you have had one issue after another on this particular database and it has manifested itself into a "black hole of issues."   I would refer to my other post and try to start fresh to see if you have the same problems...if not, then you can work backwards and compare the differences to see what is wrong with your current database.

George,



I'd highly recommend what Trent is saying. I've had a number of problems, in various arenas, and when stuff like this starts happening (just weird stuff, that after 3 or 4 times looking into it, is just getting weirder) its time to start limiting what could be wrong. In this case, its using the package to deploy to a brand new database, with nothing in it. If it works, then it isn't the package. You can compare the two databases (the one driving you crazy and the new one) to see what's different. When I'm coding in VS and problems like this start happening, first I usually post here. There have been times when my weird problem is actually a known issue and there is a way to deal with it. If it isn't and if it starts getting weird on me, I often start a new project, and try to isolate the problem, or do a fresh check out from source control or try another machine. I try to do this before I start banging my head on the desk...though if you peruse the forums you will see a few times when I received the same advice and much head banging had already happened. Limiting the problem has helped every time. Good luck!

This might be the case. But please try to see it my way too. Let me remind you the whole story. I tried to import the database into the DDT, and the package failed to deploy. I have removed all user data types as you told me, still the same problem. After searching a lot what might be wrong, I found that the primary keys were imported both as contraints and as unique indexes. This is a bug I have reported to Ben and he recorded it. After that I imported the database without the indexes. Unfortunately leaving out the indexes, leaves out the foreign keys also. So I had to insert manually all foreign keys. After that it worked fine for a while and I was really happy. Until a few days ago, which I added a new foreign key contraint, which produced the exception of the circular path you said before. I modified the foreign key. Then I got an exception saying that "the object is a member of a collection". I dropped the constraint and recreated it, the same exception. I have removed the table and recreated it, I got all those exdeptions for which I started the topic.

Just right know I have a database, which I try to import in the DDT. Without making a single change in the DDT, the package fails to deploy on the SQL server with exceptions. I can send you this database if you like to try this yourself.

What I want to say is, I only imported the database and modified the schema through the DDT. Nothing else. Shouldn't that be enough?

I could try to start a new database. After all these exceptions, how can I be sure that all will go fine? I hate to be the one to bring you bad news. But this is exactly what happened. I really like that framework. You have done a great job with it. But with DDT, I think there are still things to be done.

I just wanted to tell you the whole story, you don't have to answer this.

I hope you all have a nice weekend.

George,

I know your whole story...and I am not sure what you want me to do.  I have given you the best advice that I know to give as it relates to working through your problem.  You are correct that there was an import bug in the DDT which is on the list to be fixed....but this is not the crux of all of your issues.  There is literally NOTHING that I can do about a circular reference being created...this is a design issue with your structures and it is not related in any form or fashion to the DDT or StrataFrame.  SQL Server is the entity not allowing this relationship to be created.

As I previously mentioned, I am aware of the import bug in the DDT and we will address that, but the remainder of the problems that you have encountered are more akin to your database design and structure which, as much as I would like to help, is nothing I have any control over or can help.  I hope that this makes sense and that you understand where you are having issues, but at this point I am not sure that there is anything that I can do for you on this topic past the suggestions that I have made in helping you to work through the problem.

I guess that the best way to put this is that there is not some magical button you can press through the DDT to resolve this issue.  The DDT is many times more forgiving the SQL Server, which I believe where you are having the issue.

Trent,

Just because I don't want to leave the wrong impression. I have resolved the circular reference error. The error I was getting, was about the DDT not beeing able to create a certain view, because a table that it depended on was not there. If the view depends on a table, then the table should be created first, and the view second. If DDT does this in the reverse order, the creation will fail in all types of databases. This is a bug and I think you should try and fix it. That is why I have sent you my database. To reproduce the bug.

Nevertheless now I have abondomed that DDT project. As you have suggested, I have created a new database and a new DDT project. I have inserted in the DDT the whole database schema. I did everything entirely in the DDT. No importing what's so ever. That worked fine for a while.

Then I had to modify an index. The primary key is automatically declared a clustered index. I added a second index, I removed the clustered property from the primary key, and checked the clustered property in my new index. When I try to deploy the package I get an exception :

Modifying the IsClustered property of the Index object is not allowed. You must drop and recreate the object with the desired property.

I think the error message is pretty clear. The IsClustered property is not just a flag. It changes the way indexes are organized in a SQL Server database, and you have to drop and recreate indexes in order to modify it. If DDT is not doing it that way, then this is a bug and I think you should try and fix it.

Thanks