|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Database Script Does Not Account for DependenciesI don't know if I have missed something here, but I blindly thought that if
I used SSMS to script my database, I could run the script on a new instance and get a database out of it. The script fails, and I get an error saying that a stored procedure requires an object that doesn't exist. That is because the object hasn't been created yet. It appears later on in the script. How can I get the database scripting wizard to script objects in the correct order, that is in an order that takes account of dependencies? Either that, or does anyone know of a tool/product that does do this? TIA Charles Usually, I put my scripts together manually, because this issue troubled me
and I never got around to trying to find a solution for it. I did try checking off "Generate scripts for all dependent objects" and that did not help at all, although it did change the order of the script, just in useless ways. If you do find an answer, please let us all know. Show quote "Charles Law" <bl***@nowhere.com> wrote in message news:Odetnj3BIHA.3564@TK2MSFTNGP04.phx.gbl... >I don't know if I have missed something here, but I blindly thought that if >I used SSMS to script my database, I could run the script on a new instance >and get a database out of it. > > The script fails, and I get an error saying that a stored procedure > requires an object that doesn't exist. That is because the object hasn't > been created yet. It appears later on in the script. > > How can I get the database scripting wizard to script objects in the > correct order, that is in an order that takes account of dependencies? > > Either that, or does anyone know of a tool/product that does do this? > > TIA > > Charles > > Hi Jim
Will do :-) Charles Show quote "Jim Underwood" <james.underwood_nospam@fallonclinic.org> wrote in message news:eEWo$v3BIHA.5868@TK2MSFTNGP05.phx.gbl... > Usually, I put my scripts together manually, because this issue troubled > me and I never got around to trying to find a solution for it. I did try > checking off "Generate scripts for all dependent objects" and that did not > help at all, although it did change the order of the script, just in > useless ways. > > If you do find an answer, please let us all know. > > "Charles Law" <bl***@nowhere.com> wrote in message > news:Odetnj3BIHA.3564@TK2MSFTNGP04.phx.gbl... >>I don't know if I have missed something here, but I blindly thought that >>if I used SSMS to script my database, I could run the script on a new >>instance and get a database out of it. >> >> The script fails, and I get an error saying that a stored procedure >> requires an object that doesn't exist. That is because the object hasn't >> been created yet. It appears later on in the script. >> >> How can I get the database scripting wizard to script objects in the >> correct order, that is in an order that takes account of dependencies? >> >> Either that, or does anyone know of a tool/product that does do this? >> >> TIA >> >> Charles >> >> > >
http://www.red-gate.com/products/sql_packager/index.htm?gclid=CO-Gz5nn-I4CFQGnPAodaUROEw
I never used it myself. If you google there are more (don't miss the sponsored links). hth Quentin Show quote "Charles Law" <bl***@nowhere.com> wrote in message news:Odetnj3BIHA.3564@TK2MSFTNGP04.phx.gbl... >I don't know if I have missed something here, but I blindly thought that if >I used SSMS to script my database, I could run the script on a new instance >and get a database out of it. > > The script fails, and I get an error saying that a stored procedure > requires an object that doesn't exist. That is because the object hasn't > been created yet. It appears later on in the script. > > How can I get the database scripting wizard to script objects in the > correct order, that is in an order that takes account of dependencies? > > Either that, or does anyone know of a tool/product that does do this? > > TIA > > Charles > > Hi Quentin
Thanks for the suggestion. I will take a look. Charles Show quote "Quentin Ran" <remove_qr***@yahoo.com> wrote in message news:uYe4x$5BIHA.4836@TK2MSFTNGP06.phx.gbl... > http://www.red-gate.com/products/sql_packager/index.htm?gclid=CO-Gz5nn-I4CFQGnPAodaUROEw > > I never used it myself. If you google there are more (don't miss the > sponsored links). > > hth > > Quentin > > "Charles Law" <bl***@nowhere.com> wrote in message > news:Odetnj3BIHA.3564@TK2MSFTNGP04.phx.gbl... >>I don't know if I have missed something here, but I blindly thought that >>if I used SSMS to script my database, I could run the script on a new >>instance and get a database out of it. >> >> The script fails, and I get an error saying that a stored procedure >> requires an object that doesn't exist. That is because the object hasn't >> been created yet. It appears later on in the script. >> >> How can I get the database scripting wizard to script objects in the >> correct order, that is in an order that takes account of dependencies? >> >> Either that, or does anyone know of a tool/product that does do this? >> >> TIA >> >> Charles >> >> > > I agree with Jim; do it manually. SQL Server scripting is nice *sometimes*
most of times it causes more problems. Besides nothing like doing it yourself ;-) more fun, and you get to make sure everything is named how you like it. Instead of using SQL naming convention for keys and such. -- Mohit K. Gupta B.Sc. CS, Minor Japanese MCTS: SQL Server 2005 Show quote "Charles Law" wrote: > I don't know if I have missed something here, but I blindly thought that if > I used SSMS to script my database, I could run the script on a new instance > and get a database out of it. > > The script fails, and I get an error saying that a stored procedure requires > an object that doesn't exist. That is because the object hasn't been created > yet. It appears later on in the script. > > How can I get the database scripting wizard to script objects in the correct > order, that is in an order that takes account of dependencies? > > Either that, or does anyone know of a tool/product that does do this? > > TIA > > Charles > > > Hi Mohit
Maintaining a script manually is really not an option. There are too many objects to control in that way. Perhaps if SQL Server objects could be properly source controlled then it might be an option, but not at the moment. Thanks. Charles Show quote "Mohit K. Gupta" <mohitkgu***@msn.com> wrote in message news:AFF81E72-8292-462D-89FA-667E4CBDDB2B@microsoft.com... >I agree with Jim; do it manually. SQL Server scripting is nice *sometimes* > most of times it causes more problems. Besides nothing like doing it > yourself ;-) more fun, and you get to make sure everything is named how > you > like it. Instead of using SQL naming convention for keys and such. > > -- > Mohit K. Gupta > B.Sc. CS, Minor Japanese > MCTS: SQL Server 2005 > > > "Charles Law" wrote: > >> I don't know if I have missed something here, but I blindly thought that >> if >> I used SSMS to script my database, I could run the script on a new >> instance >> and get a database out of it. >> >> The script fails, and I get an error saying that a stored procedure >> requires >> an object that doesn't exist. That is because the object hasn't been >> created >> yet. It appears later on in the script. >> >> How can I get the database scripting wizard to script objects in the >> correct >> order, that is in an order that takes account of dependencies? >> >> Either that, or does anyone know of a tool/product that does do this? >> >> TIA >> >> Charles >> >> >> Charles Law (bl***@nowhere.com) writes:
> Maintaining a script manually is really not an option. There are too many It isn't what? We keep all our SQL Server objects under version control> objects to control in that way. Perhaps if SQL Server objects could be > properly source controlled then it might be an option, but not at the > moment. and have done so for many years. There's nothing special with SQL objects. Source code is source code. Just do it. The database on the other hand, is just a place were you put your binaries. (Nevermind that the binaries in this case have a very strong resemblence to the source code. It's nevertheless to be regarded as binaries.) -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland
What do you use as your version control repository? The problem we get is when someone makes a change to a database object, in the database, this is not tracked and controlled. Is there a way of 'locking' objects until they are checked out to the database, where developers can use SSMS to manage their tables, views and stored procedures? Charles Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns99C1EF8DD5814Yazorman@127.0.0.1... > Charles Law (bl***@nowhere.com) writes: >> Maintaining a script manually is really not an option. There are too many >> objects to control in that way. Perhaps if SQL Server objects could be >> properly source controlled then it might be an option, but not at the >> moment. > > It isn't what? We keep all our SQL Server objects under version control > and have done so for many years. There's nothing special with SQL objects. > Source code is source code. Just do it. > > The database on the other hand, is just a place were you put your > binaries. (Nevermind that the binaries in this case have a very strong > resemblence to the source code. It's nevertheless to be regarded as > binaries.) > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Charles Law (bl***@nowhere.com) writes:
> What do you use as your version control repository? The problem we get is We use Visual SourceSafe, although we really grew out of it long ago.> when someone makes a change to a database object, in the database, this is > not tracked and controlled. Is there a way of 'locking' objects until they > are checked out to the database, where developers can use SSMS to manage > their tables, views and stored procedures? Database objects is not any different from any other type of objects. You check it out when you work with it, you check it in when you are done. If it is not checked in, it does not exist, it's as simlpe as that. Someone might be changing things in a development database without checking things out, but that is completely irrelevant. If it is not checked in, it does not exist. And, yes, someone could change a stored procedure in the production environment without going the SourceSafe route. Just as someone could change a C++ file, compile a DLL and install it in production without going through SourceSafe. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland
I suppose the problem I have is that with database objects there is an intermediate step/object, i.e. the script. If I were in VS, I could select my objects, add them to VSS, and check them in and out. In SQL Server, I have to create an intermediate object - the script file - and put that into VSS. There is no direct control over the object in the database. So, for example, there is no way for one person to tell if someone else is working on a SP just by looking in the database. Perhaps that level of tight integration and control will come in a future release, but for now, it is not really adequate for our needs. Charles Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns99C2B32BCB55EYazorman@127.0.0.1... > Charles Law (bl***@nowhere.com) writes: >> What do you use as your version control repository? The problem we get is >> when someone makes a change to a database object, in the database, this >> is >> not tracked and controlled. Is there a way of 'locking' objects until >> they >> are checked out to the database, where developers can use SSMS to manage >> their tables, views and stored procedures? > > We use Visual SourceSafe, although we really grew out of it long ago. > > Database objects is not any different from any other type of objects. > You check it out when you work with it, you check it in when you are done. > If it is not checked in, it does not exist, it's as simlpe as that. > > Someone might be changing things in a development database without > checking > things out, but that is completely irrelevant. If it is not checked in, > it does not exist. > > And, yes, someone could change a stored procedure in the production > environment without going the SourceSafe route. Just as someone could > change a C++ file, compile a DLL and install it in production without > going through SourceSafe. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > If I were in VS, I could select my objects, add them to VSS, and check I suggest you change your mindset to view the VSS SQL Server object script > them in and out. In SQL Server, I have to create an intermediate object - > the script file - and put that into VSS. There is no direct control over > the object in the database. So, for example, there is no way for one > person to tell if someone else is working on a SP just by looking in the > database. file as the master copy rather than an "intermediate" file. This way, you need only look in source control to see who is working on an object and can follow the same sort of practices as you do for application code. We use VSS as our source control repository (although we've outgrown it like Erland) and are looking at TFS source control. I've also used ClearCase and MKS in past positions. I don't think the source control tool is nearly as important as the processes surrounding it. Sound development and release management practices are mandatory, especially for larger teams. -- Hope this helps. Dan Guzman SQL Server MVP Show quote "Charles Law" <bl***@nowhere.com> wrote in message news:OWsJUDaCIHA.972@TK2MSFTNGP05.phx.gbl... > Hi Erland > > I suppose the problem I have is that with database objects there is an > intermediate step/object, i.e. the script. > > If I were in VS, I could select my objects, add them to VSS, and check > them in and out. In SQL Server, I have to create an intermediate object - > the script file - and put that into VSS. There is no direct control over > the object in the database. So, for example, there is no way for one > person to tell if someone else is working on a SP just by looking in the > database. > > Perhaps that level of tight integration and control will come in a future > release, but for now, it is not really adequate for our needs. > > Charles > > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns99C2B32BCB55EYazorman@127.0.0.1... >> Charles Law (bl***@nowhere.com) writes: >>> What do you use as your version control repository? The problem we get >>> is >>> when someone makes a change to a database object, in the database, this >>> is >>> not tracked and controlled. Is there a way of 'locking' objects until >>> they >>> are checked out to the database, where developers can use SSMS to manage >>> their tables, views and stored procedures? >> >> We use Visual SourceSafe, although we really grew out of it long ago. >> >> Database objects is not any different from any other type of objects. >> You check it out when you work with it, you check it in when you are >> done. >> If it is not checked in, it does not exist, it's as simlpe as that. >> >> Someone might be changing things in a development database without >> checking >> things out, but that is completely irrelevant. If it is not checked in, >> it does not exist. >> >> And, yes, someone could change a stored procedure in the production >> environment without going the SourceSafe route. Just as someone could >> change a C++ file, compile a DLL and install it in production without >> going through SourceSafe. >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > Sound development and release management practices are mandatory, I agree entirely. However, the script _is_ an intermediate file because > especially for larger teams. developers do not work on it directly. They work in SSMS on its version of the object. By the same token, developers do not _need_ to check out an object to work on it, only when they want to check a change into VSS. If this were C# source, then the developer would have to check out a file before working on it in VS, because it is the same file. Procedures are fine, and necessary, but the more they can be enforced the better. It just seems like an omission, or a gap, in what should be a joined up process, that SQL Server and VSS do not integrate to the same extent as VS and VSS. Charles Show quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:EA5D139B-B8F6-40AA-9765-44FC573C522B@microsoft.com... >> If I were in VS, I could select my objects, add them to VSS, and check >> them in and out. In SQL Server, I have to create an intermediate object - >> the script file - and put that into VSS. There is no direct control over >> the object in the database. So, for example, there is no way for one >> person to tell if someone else is working on a SP just by looking in the >> database. > > I suggest you change your mindset to view the VSS SQL Server object script > file as the master copy rather than an "intermediate" file. This way, you > need only look in source control to see who is working on an object and > can follow the same sort of practices as you do for application code. > > We use VSS as our source control repository (although we've outgrown it > like Erland) and are looking at TFS source control. I've also used > ClearCase and MKS in past positions. I don't think the source control > tool is nearly as important as the processes surrounding it. Sound > development and release management practices are mandatory, especially for > larger teams. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > > "Charles Law" <bl***@nowhere.com> wrote in message > news:OWsJUDaCIHA.972@TK2MSFTNGP05.phx.gbl... >> Hi Erland >> >> I suppose the problem I have is that with database objects there is an >> intermediate step/object, i.e. the script. >> >> If I were in VS, I could select my objects, add them to VSS, and check >> them in and out. In SQL Server, I have to create an intermediate object - >> the script file - and put that into VSS. There is no direct control over >> the object in the database. So, for example, there is no way for one >> person to tell if someone else is working on a SP just by looking in the >> database. >> >> Perhaps that level of tight integration and control will come in a future >> release, but for now, it is not really adequate for our needs. >> >> Charles >> >> >> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >> news:Xns99C2B32BCB55EYazorman@127.0.0.1... >>> Charles Law (bl***@nowhere.com) writes: >>>> What do you use as your version control repository? The problem we get >>>> is >>>> when someone makes a change to a database object, in the database, this >>>> is >>>> not tracked and controlled. Is there a way of 'locking' objects until >>>> they >>>> are checked out to the database, where developers can use SSMS to >>>> manage >>>> their tables, views and stored procedures? >>> >>> We use Visual SourceSafe, although we really grew out of it long ago. >>> >>> Database objects is not any different from any other type of objects. >>> You check it out when you work with it, you check it in when you are >>> done. >>> If it is not checked in, it does not exist, it's as simlpe as that. >>> >>> Someone might be changing things in a development database without >>> checking >>> things out, but that is completely irrelevant. If it is not checked in, >>> it does not exist. >>> >>> And, yes, someone could change a stored procedure in the production >>> environment without going the SourceSafe route. Just as someone could >>> change a C++ file, compile a DLL and install it in production without >>> going through SourceSafe. >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>> >>> Books Online for SQL Server 2005 at >>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>> Books Online for SQL Server 2000 at >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> >> > Charles Law (bl***@nowhere.com) writes:
> I agree entirely. However, the script _is_ an intermediate file because As Dan said, this is a question of mindset. There is no developer in my> developers do not work on it directly. They work in SSMS on its version of > the object. By the same token, developers do not _need_ to check out an > object to work on it, only when they want to check a change into VSS. > > If this were C# source, then the developer would have to check out a file > before working on it in VS, because it is the same file. shop that would understand what your talking about. For them, a stored procedure is a file just like C# program is. Of course, it may help that we don't use QA or SSMS to edit SQL code, but a third-party editor, so there is still a clear notion of a compile step. But there is not really any difference even if you work in SSMS. You have a file and you edit it. Yes, you can modify an object in the database without checking it out. Just as you can modify a C# file without checking in out. > Procedures are fine, and necessary, but the more they can be enforced Using the Source Control APIs it's possible to lock objects in SQL > the better. It just seems like an omission, or a gap, in what should be > a joined up process, that SQL Server and VSS do not integrate to the > same extent as VS and VSS. Server with database triggers, so that any attempt to alter it is met with "Check out first". But how do you know that the developer is not modifying the object in another database? By the way, it is possible to connect Mgmt Studio with at least SourceSafe. (I'm uncertain about TFS). You still work with files, though. Which is the way it should be. That's all what source code is about. Files. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Again, I'm not really disagreeing, but I see the database as the repository
for all these objects, where we are given convenient tools for editing and maintaining the objects. We seem to be advancing in one way, having a GUI that allows us to view and change these objects, but we still have to go back to a script file if we want to make a permanent change. To me, it's a bit like having Windows Explorer to look at the contents of a directory, but opening a command prompt if we want to move a file. Anyway, what I was really after was some means to simplify the task of managing database objects, whether that be a database wizard or a scripting tool, or something else, and something to make it easier to control changes. Perhaps we will end up creating script files and manually manage a batch file to run them in the correct order. Charles Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns99C459CDD66EYazorman@127.0.0.1... > Charles Law (bl***@nowhere.com) writes: >> I agree entirely. However, the script _is_ an intermediate file because >> developers do not work on it directly. They work in SSMS on its version >> of >> the object. By the same token, developers do not _need_ to check out an >> object to work on it, only when they want to check a change into VSS. >> >> If this were C# source, then the developer would have to check out a file >> before working on it in VS, because it is the same file. > > As Dan said, this is a question of mindset. There is no developer in my > shop that would understand what your talking about. For them, a stored > procedure is a file just like C# program is. Of course, it may help > that we don't use QA or SSMS to edit SQL code, but a third-party editor, > so there is still a clear notion of a compile step. But there is not > really any difference even if you work in SSMS. You have a file and > you edit it. > > Yes, you can modify an object in the database without checking it out. > Just as you can modify a C# file without checking in out. > >> Procedures are fine, and necessary, but the more they can be enforced >> the better. It just seems like an omission, or a gap, in what should be >> a joined up process, that SQL Server and VSS do not integrate to the >> same extent as VS and VSS. > > Using the Source Control APIs it's possible to lock objects in SQL > Server with database triggers, so that any attempt to alter it is > met with "Check out first". But how do you know that the developer is > not modifying the object in another database? > > By the way, it is possible to connect Mgmt Studio with at least > SourceSafe. > (I'm uncertain about TFS). You still work with files, though. Which is > the way it should be. That's all what source code is about. Files. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Charles Law (bl***@nowhere.com) writes:
> Again, I'm not really disagreeing, but I see the database as the And that is flat wrong in my opinion. In my shop there is no such things> repository for all these objects, as "the database". There is a unknown number of development and test databases, all stemming from the same schema. In our case this is amplified by the fact that our customers have so different configuration, that you may need a particular configuration when you develop and test a certain feature. But even without that, developers may need their own sandboxes where they can work without disturbing other developers. This does not at least apply if needs to change tables. I don't know if you have looked at Visual Studio Team System for Database Developers, also known as DataDude. As I understand DataDude, you work solely against a database on your local machine, you cannot work against a central database. > Anyway, what I was really after was some means to simplify the task of Keep in mind that if you work from scripting the database, you probably > managing database objects, whether that be a database wizard or a > scripting tool, or something else, and something to make it easier to > control changes. Perhaps we will end up creating script files and > manually manage a batch file to run them in the correct order. get a bunch of junk objects that should not be in the source control system. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I'm starting to be persuaded. I've been looking at a silhouette of two
candlesticks, and suddenly I see two faces. I think you mentioned that you use another tool to manage your scripts. What is that tool? There is a problem that developers - well, the ones here - like the GUI, draggy, droppy, clicky tools like SSMS, and given the choice between that and a text editor to edit boring old scripts, you know which they will choose. Perhaps what I want, then, is something that manages the scripts (files) in a visual way (like VS does with source code), and enables changes to be deployed to the database of choice. Does that sound better? Charles Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns99C4EFFCF5F73Yazorman@127.0.0.1... > Charles Law (bl***@nowhere.com) writes: >> Again, I'm not really disagreeing, but I see the database as the >> repository for all these objects, > > And that is flat wrong in my opinion. In my shop there is no such things > as "the database". There is a unknown number of development and test > databases, all stemming from the same schema. In our case this is > amplified > by the fact that our customers have so different configuration, that > you may need a particular configuration when you develop and test a > certain > feature. > > But even without that, developers may need their own sandboxes where they > can work without disturbing other developers. This does not at least > apply if needs to change tables. > > I don't know if you have looked at Visual Studio Team System for > Database Developers, also known as DataDude. As I understand DataDude, you > work solely against a database on your local machine, you cannot work > against a central database. > >> Anyway, what I was really after was some means to simplify the task of >> managing database objects, whether that be a database wizard or a >> scripting tool, or something else, and something to make it easier to >> control changes. Perhaps we will end up creating script files and >> manually manage a batch file to run them in the correct order. > > Keep in mind that if you work from scripting the database, you probably > get a bunch of junk objects that should not be in the source control > system. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Charles Law (bl***@nowhere.com) writes:
> I'm starting to be persuaded. I've been looking at a silhouette of two We have a toolset that I have developed over the years, and by now> candlesticks, and suddenly I see two faces. I think you mentioned that you > use another tool to manage your scripts. What is that tool? they have achieved quite a level of complexity. Although they are fairly streamlined to our needs, so about everyone else would probably find them quirky. And if you are looking for drag and drop, just forget it - it's all command-line. (And all Perl.) Unfortunately this toolset is not available to the public. Maybe I will be able to make it avilable in some form in the future. But I can give you some of the essence of the toolset, as it may give you inspiration to devise your own; of course you would start off with something very simple. The core is a file-loading module that reads an SQL file and performs a lot of processing of it, for instance changing CREATE to ALTER if required. There is a preprocessor and lots of bells and whistles. But the really important one is CREATE vs. ALTER, which you can handle in this way: IF object_id('some_proc') IS NULL EXEC ('CREATE PROCEDURE some_proc AS PRINT 1') go ALTER PROCEDURE some_proc -- real procedure follows here. You would have this prologue in every file. Or, if you write a simple file-loader in your favourite language, you can generate that code from the filename. Assume that procedure name and file name agrees and whip up all programmers that violate the rule. Of course, you can parse the code, but parsing T-SQL is messy, so you will avoid it if you can. Then there is one tool that builds an empty database from SourceSafe or from a structure on disk. We have a certain organisation with different types of files in different projects. The DB build tool just loads files type by type in alphabetic order. To handle dependency between stored procedure, we load those twice. Since you have views that refer to each other you will need to find a way to control the order. One way is to have a file that holds the load order of the views. For a long time this was never a problem for us, simply because we hardly used views at all. They have started to pop up, it we handle them with help of our preprocessor. Finally, there is a tool that reads SourceSafe between to two labels and generate a script to load all changed objects. The tricky part here is of course changed tables. Our tool generates a template to handle the change which builds on the principle create new table, move data and referencing FKs over, drop old table. In step one, you will probably prefer to just insert whatever migration you want manually. (Although it's quite boring.) > There is a problem that developers - well, the ones here - like the GUI, Then again, there is not much you can point and click with in SSMS for> draggy, droppy, clicky tools like SSMS, and given the choice between that > and a text editor to edit boring old scripts, you know which they will > choose. development. Oh, yeah, there is the Table Designer, but if they use that one, really make sure they stay away. There are several serious bugs in the Table Designer when it comes to change an existing table. > Perhaps what I want, then, is something that manages the scripts (files) There are certainly some options on the market, although I have not> in a visual way (like VS does with source code), and enables changes to > be deployed to the database of choice. Does that sound better? investigated the options very closely. DataDude that I mention is definitely worth looking at if you are a fan of Visual Studio. Red Gate's SQL Compare has no integration with version-control system, but their most recent version permits you to compare a database with files on disk. That permits you compare the current development with what you have in the version-control system, although the checkin- checkout process would be a two-step process. You can also generate update scripts from it. SQL Compare does not really align with the way I like to work, but I'm impressed by the tool as such. Not the least in comparison with SSMS which have quite a few shortcomings with scripting. Not the least in performance... -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland, thanks for bearing with me through this process. I will inwardly
digest and assimilate (makes me sound like the Borg). I will continue to look into the possibility of a tool that will help here or, who knows, even create one myself, as you have done. Many thanks Charles Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns99C647B2101DYazorman@127.0.0.1... > Charles Law (bl***@nowhere.com) writes: >> I'm starting to be persuaded. I've been looking at a silhouette of two >> candlesticks, and suddenly I see two faces. I think you mentioned that >> you >> use another tool to manage your scripts. What is that tool? > > We have a toolset that I have developed over the years, and by now > they have achieved quite a level of complexity. Although they are fairly > streamlined to our needs, so about everyone else would probably find > them quirky. And if you are looking for drag and drop, just forget it - > it's all command-line. (And all Perl.) > > Unfortunately this toolset is not available to the public. Maybe I will > be able to make it avilable in some form in the future. > > But I can give you some of the essence of the toolset, as it may give > you inspiration to devise your own; of course you would start off with > something very simple. > > The core is a file-loading module that reads an SQL file and performs a > lot of processing of it, for instance changing CREATE to ALTER if > required. > There is a preprocessor and lots of bells and whistles. But the really > important one is CREATE vs. ALTER, which you can handle in this way: > > IF object_id('some_proc') IS NULL > EXEC ('CREATE PROCEDURE some_proc AS PRINT 1') > go > ALTER PROCEDURE some_proc -- real procedure follows here. > > You would have this prologue in every file. Or, if you write a simple > file-loader in your favourite language, you can generate that code from > the filename. Assume that procedure name and file name agrees and > whip up all programmers that violate the rule. Of course, you can parse > the code, but parsing T-SQL is messy, so you will avoid it if you can. > > Then there is one tool that builds an empty database from SourceSafe > or from a structure on disk. We have a certain organisation with different > types of files in different projects. The DB build tool just loads > files type by type in alphabetic order. To handle dependency between > stored procedure, we load those twice. Since you have views that > refer to each other you will need to find a way to control the order. > One way is to have a file that holds the load order of the views. For a > long time this was never a problem for us, simply because we hardly > used views at all. They have started to pop up, it we handle them with > help of our preprocessor. > > Finally, there is a tool that reads SourceSafe between to two labels > and generate a script to load all changed objects. The tricky part > here is of course changed tables. Our tool generates a template to > handle the change which builds on the principle create new table, > move data and referencing FKs over, drop old table. In step one, you > will probably prefer to just insert whatever migration you want manually. > (Although it's quite boring.) > >> There is a problem that developers - well, the ones here - like the GUI, >> draggy, droppy, clicky tools like SSMS, and given the choice between that >> and a text editor to edit boring old scripts, you know which they will >> choose. > > Then again, there is not much you can point and click with in SSMS for > development. Oh, yeah, there is the Table Designer, but if they use > that one, really make sure they stay away. There are several serious > bugs in the Table Designer when it comes to change an existing table. > >> Perhaps what I want, then, is something that manages the scripts (files) >> in a visual way (like VS does with source code), and enables changes to >> be deployed to the database of choice. Does that sound better? > > There are certainly some options on the market, although I have not > investigated the options very closely. DataDude that I mention is > definitely worth looking at if you are a fan of Visual Studio. > > Red Gate's SQL Compare has no integration with version-control system, > but their most recent version permits you to compare a database with > files on disk. That permits you compare the current development with > what you have in the version-control system, although the checkin- > checkout process would be a two-step process. You can also generate > update scripts from it. SQL Compare does not really align with the > way I like to work, but I'm impressed by the tool as such. Not the > least in comparison with SSMS which have quite a few shortcomings > with scripting. Not the least in performance... > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx On Oct 10, 5:21 pm, Erland Sommarskog <esq***@sommarskog.se> wrote:
Show quote > yep. On the other hand it allows you to choose whatever version> There are certainly some options on the market, although I have not > investigated the options very closely. DataDude that I mention is > definitely worth looking at if you are a fan of Visual Studio. > > Red Gate's SQL Compare has no integration with version-control system, > but their most recent version permits you to compare a database with > files on disk. That permits you compare the current development with > what you have in the version-control system, although the checkin- > checkout process would be a two-step process. You can also generate > update scripts from it. SQL Compare does not really align with the > way I like to work, but I'm impressed by the tool as such. Not the > least in comparison with SSMS which have quite a few shortcomings > with scripting. Not the least in performance... colntrol you prefer. I personally prefer Subversion which also easily integrates with JIRA/Fisheye/Crucible. Show quote
On Oct 5, 12:55 pm, "Charles Law" <bl***@nowhere.com> wrote: I regularly use Red Gate's SQL Compare for such tasks.> I don't know if I have missed something here, but I blindly thought that if > I used SSMS to script my database, I could run the script on a new instance > and get a database out of it. > > The script fails, and I get an error saying that a stored procedure requires > an object that doesn't exist. That is because the object hasn't been created > yet. It appears later on in the script. > > How can I get the database scripting wizard to script objects in the correct > order, that is in an order that takes account of dependencies? > > Either that, or does anyone know of a tool/product that does do this? > > TIA > > Charles Hi Alex
I've just downloaded the toolkit trial which contains several utilities, so I'll have a look at it now. Cheers. Charles Show quote "Alex Kuznetsov" <alk***@gmail.com> wrote in message news:1191640297.834009.175120@d55g2000hsg.googlegroups.com... > On Oct 5, 12:55 pm, "Charles Law" <bl***@nowhere.com> wrote: >> I don't know if I have missed something here, but I blindly thought that >> if >> I used SSMS to script my database, I could run the script on a new >> instance >> and get a database out of it. >> >> The script fails, and I get an error saying that a stored procedure >> requires >> an object that doesn't exist. That is because the object hasn't been >> created >> yet. It appears later on in the script. >> >> How can I get the database scripting wizard to script objects in the >> correct >> order, that is in an order that takes account of dependencies? >> >> Either that, or does anyone know of a tool/product that does do this? >> >> TIA >> >> Charles > > I regularly use Red Gate's SQL Compare for such tasks. > Charles Law wrote:
> [...] You can use the Database Publishing Wizard from> How can I get the database scripting wizard to script objects in the > correct order, that is in an order that takes account of dependencies? > > Either that, or does anyone know of a tool/product that does do this? http://www.codeplex.com/sqlhost. It's made by Microsoft, it's free and it takes care of the dependencies quite good (at least on SQL 2005). -- Razvan Socol SQL Server MVP Hi Razvan
Thanks for the reply. I have downloaded the wizard and run it. It looks identical to the scripting wizard built into SS Management Studio. Do you know if it behaves differently from the built-in version? Charles Show quote "Razvan Socol" <rso***@gmail.com> wrote in message news:uI$0Lq9BIHA.4584@TK2MSFTNGP06.phx.gbl... > Charles Law wrote: >> [...] >> How can I get the database scripting wizard to script objects in the >> correct order, that is in an order that takes account of dependencies? >> >> Either that, or does anyone know of a tool/product that does do this? > > You can use the Database Publishing Wizard from > http://www.codeplex.com/sqlhost. It's made by Microsoft, it's free and > it takes care of the dependencies quite good (at least on SQL 2005). > > -- > Razvan Socol > SQL Server MVP I wrote a database scripting tool that probably can solve your issues.
Regards Robert Allen Schambach www.dbconstructor.com Show quote "Charles Law" <bl***@nowhere.com> wrote in message news:Odetnj3BIHA.3564@TK2MSFTNGP04.phx.gbl... >I don't know if I have missed something here, but I blindly thought that if >I used SSMS to script my database, I could run the script on a new instance >and get a database out of it. > > The script fails, and I get an error saying that a stored procedure > requires an object that doesn't exist. That is because the object hasn't > been created yet. It appears later on in the script. > > How can I get the database scripting wizard to script objects in the > correct order, that is in an order that takes account of dependencies? > > Either that, or does anyone know of a tool/product that does do this? > > TIA > > Charles > > Hi Robert
Looks very interesting. I will take a look. Thanks. Charles Show quote "rallen" <ral***@dbconstructor.com> wrote in message news:uUs74pBEIHA.2004@TK2MSFTNGP06.phx.gbl... >I wrote a database scripting tool that probably can solve your issues. > > Regards > > Robert Allen Schambach > www.dbconstructor.com > > "Charles Law" <bl***@nowhere.com> wrote in message > news:Odetnj3BIHA.3564@TK2MSFTNGP04.phx.gbl... >>I don't know if I have missed something here, but I blindly thought that >>if I used SSMS to script my database, I could run the script on a new >>instance and get a database out of it. >> >> The script fails, and I get an error saying that a stored procedure >> requires an object that doesn't exist. That is because the object hasn't >> been created yet. It appears later on in the script. >> >> How can I get the database scripting wizard to script objects in the >> correct order, that is in an order that takes account of dependencies? >> >> Either that, or does anyone know of a tool/product that does do this? >> >> TIA >> >> Charles >> >> > > Show quote
On Oct 16, 9:35 pm, "Charles Law" <bl***@nowhere.com> wrote: Charles,> Hi Robert > > Looks very interesting. I will take a look. > > Thanks. > > Charles > > "rallen" <ral***@dbconstructor.com> wrote in message > > news:uUs74pBEIHA.2004@TK2MSFTNGP06.phx.gbl... > > > > >I wrote a database scripting tool that probably can solve your issues. > > > Regards > > > Robert Allen Schambach > >www.dbconstructor.com > > > "Charles Law" <bl***@nowhere.com> wrote in message > >news:Odetnj3BIHA.3564@TK2MSFTNGP04.phx.gbl... > >>I don't know if I have missed something here, but I blindly thought that > >>if I used SSMS to script my database, I could run the script on a new > >>instance and get a database out of it. > > >> The script fails, and I get an error saying that a stored procedure > >> requires an object that doesn't exist. That is because the object hasn't > >> been created yet. It appears later on in the script. > > >> How can I get the database scripting wizard to script objects in the > >> correct order, that is in an order that takes account of dependencies? > > >> Either that, or does anyone know of a tool/product that does do this? > > >> TIA > > >> Charles- Hide quoted text - > > - Show quoted text - Please let me know if you need any help using dbConstructor, there will be better documentation shortly, if you have any problems or suggestions please drop me a line, use the sites Contact Us screen and I will gladly give you any assistance necessary. Regards, Robert Allen Schambach  |
|||||||||||||||||||||||
Other interesting topics