Database Script Does Not Account for Dependencies

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
CharlesUsually, 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" <blank@.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
"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" <blank@.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
"Charles Law" <blank@.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
"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
>
>|||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:
> [...]
> 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|||Hi Quentin
Thanks for the suggestion. I will take a look.
Charles
"Quentin Ran" <remove_qran2@.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" <blank@.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 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
"Mohit K. Gupta" <mohitkgupta@.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
>>|||Hi Alex
I've just downloaded the toolkit trial which contains several utilities, so
I'll have a look at it now.
Cheers.
Charles
"Alex Kuznetsov" <alkuzo@.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.
>|||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
"Razvan Socol" <rsocol@.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|||Charles Law (blank@.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, esquel@.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
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C1EF8DD5814Yazorman@.127.0.0.1...
> Charles Law (blank@.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, esquel@.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 (blank@.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, esquel@.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
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
> Charles Law (blank@.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, esquel@.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
> 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" <blank@.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" <esquel@.sommarskog.se> wrote in message
> news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
>> Charles Law (blank@.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, esquel@.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,
> especially for larger teams.
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.
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
"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" <blank@.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" <esquel@.sommarskog.se> wrote in message
>> news:Xns99C2B32BCB55EYazorman@.127.0.0.1...
>> Charles Law (blank@.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, esquel@.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 (blank@.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, esquel@.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
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C459CDD66EYazorman@.127.0.0.1...
> Charles Law (blank@.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, esquel@.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 (blank@.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, esquel@.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
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C4EFFCF5F73Yazorman@.127.0.0.1...
> Charles Law (blank@.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, esquel@.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 (blank@.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, esquel@.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
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99C647B2101DYazorman@.127.0.0.1...
> Charles Law (blank@.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, esquel@.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:
> 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...
yep. On the other hand it allows you to choose whatever version
colntrol you prefer. I personally prefer Subversion which also easily
integrates with JIRA/Fisheye/Crucible.|||I wrote a database scripting tool that probably can solve your issues.
Regards
Robert Allen Schambach
www.dbconstructor.com
"Charles Law" <blank@.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
"rallen" <rallen@.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" <blank@.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
>>
>|||On Oct 16, 9:35 pm, "Charles Law" <bl...@.nowhere.com> wrote:
> 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 -
Charles,
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