Exporting Stored Procs

Hi,
Novice user here. I am developing a large number of stored procedures
and user defined functions and I want to be able to export them everynight
for back up. I know that I can user the Query Analyzer tool to export them
to .sql files one at a time, but I am approaching about 100 procedures and
functions and that can be quite tedious.
Anyone know how I can batch export a set of procs at one time?
JDWhy don't you just make a special backup of the database every night?
And have you considered using source control for database objects?
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:OmpYe.261467$E95.192029@.fed1read01...
> Hi,
> Novice user here. I am developing a large number of stored procedures
> and user defined functions and I want to be able to export them everynight
> for back up. I know that I can user the Query Analyzer tool to export
> them
> to .sql files one at a time, but I am approaching about 100 procedures and
> functions and that can be quite tedious.
> Anyone know how I can batch export a set of procs at one time?
>
> JD
>|||Joe
One way to do this is to create a SQL Server Job scheduled to run at the
desired frequency to run an ActiveX Task that scripts out the Stored
Procedures.
Below is a VBScript example that can be used to script all Stored Procedures
in a Database to a file.
~~~
Const LOG_FILE = "c:\script.sql"
Const SQL_INSTANCE = "(local)" ' SQL Server Instance where the DB to script
exists
Const DB_NAME = "pubs" ' This could be replaced with a loop to script all DB
's
' FSO I/O Mode
Const FORWRITING = 2
Const FORAPPENDING = 8
' DMO Scripting Constants
Const SQLDMOScript_ObjectPermissions = 2
Const SQLDMOScript_Default = 4
Const SQLDMOScript_OwnerQualify = 262144
Dim oStoredProcedure
Dim oSQLServer
Dim oDatabase
Dim oFSO
Dim sTexttoWrite
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oFSO = CreateObject("Scripting.FileSystemObject")
oSQLServer.LoginSecure = True
oSQLserver.Connect(SQL_INSTANCE)
Set oStoredProcedure = CreateObject("SQLDMO.StoredProcedure")
For Each oStoredProcedure In oSQLServer.Databases(DB_NAME).StoredProcedures
If Not oStoredProcedure.SystemObject Then ' Only Script User Objects
' Script the Object Permissions and Owner
sTexttoWrite =
oSQLServer.Databases(DB_NAME).StoredProcedures(oStoredProcedure.Name).Script
(SQLDMOScript_Default
+ SQLDMOScript_ObjectPermissions + SQLDMOScript_OwnerQualify)
oFSO.OpenTextFile(LOG_FILE, FORAPPENDING, True).WriteLine(sTexttoWrite)
End If
Next
oSQLServer.DisConnect
Set oFSO = Nothing
Set oStoredProcedure = Nothing
Set oSQLServer = Nothing
~~~
- Peter Ward
WARDY IT Solutions
"Joe Delphi" wrote:

> Hi,
> Novice user here. I am developing a large number of stored procedures
> and user defined functions and I want to be able to export them everynight
> for back up. I know that I can user the Query Analyzer tool to export the
m
> to .sql files one at a time, but I am approaching about 100 procedures and
> functions and that can be quite tedious.
> Anyone know how I can batch export a set of procs at one time?
>
> JD
>
>|||if your problem is only scripting then
you can use Entrprise Manager> All Tasks > Genrate sql secript
you can select all sprocs at a time to .sql
Regards
R.D
"P. Ward" wrote:
> Joe
> One way to do this is to create a SQL Server Job scheduled to run at the
> desired frequency to run an ActiveX Task that scripts out the Stored
> Procedures.
> Below is a VBScript example that can be used to script all Stored Procedur
es
> in a Database to a file.
> ~~~
> Const LOG_FILE = "c:\script.sql"
> Const SQL_INSTANCE = "(local)" ' SQL Server Instance where the DB to scrip
t
> exists
> Const DB_NAME = "pubs" ' This could be replaced with a loop to script all
DB's
> ' FSO I/O Mode
> Const FORWRITING = 2
> Const FORAPPENDING = 8
> ' DMO Scripting Constants
> Const SQLDMOScript_ObjectPermissions = 2
> Const SQLDMOScript_Default = 4
> Const SQLDMOScript_OwnerQualify = 262144
>
> Dim oStoredProcedure
> Dim oSQLServer
> Dim oDatabase
> Dim oFSO
> Dim sTexttoWrite
> Set oSQLServer = CreateObject("SQLDMO.SQLServer")
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> oSQLServer.LoginSecure = True
> oSQLserver.Connect(SQL_INSTANCE)
> Set oStoredProcedure = CreateObject("SQLDMO.StoredProcedure")
> For Each oStoredProcedure In oSQLServer.Databases(DB_NAME).StoredProcedure
s
> If Not oStoredProcedure.SystemObject Then ' Only Script User Objects
> ' Script the Object Permissions and Owner
> sTexttoWrite =
> oSQLServer.Databases(DB_NAME).StoredProcedures(oStoredProcedure.Name).Scri
pt(SQLDMOScript_Default
> + SQLDMOScript_ObjectPermissions + SQLDMOScript_OwnerQualify)
> oFSO.OpenTextFile(LOG_FILE, FORAPPENDING, True).WriteLine(sTexttoWrite)
> End If
> Next
> oSQLServer.DisConnect
> Set oFSO = Nothing
> Set oStoredProcedure = Nothing
> Set oSQLServer = Nothing
> ~~~
> - Peter Ward
> WARDY IT Solutions
>
> "Joe Delphi" wrote:
>|||Joe
In new groups. If you have a question do post as question. Never select as
comment. Question attract more responses.
Regards
R.D
"R.D" wrote:
> if your problem is only scripting then
> you can use Entrprise Manager> All Tasks > Genrate sql secript
> you can select all sprocs at a time to .sql
> Regards
> R.D
> "P. Ward" wrote:
>|||"R.D" <RD@.discussions.microsoft.com> wrote in message
news:76E5A96A-8011-4A72-9C96-E005877BAF63@.microsoft.com...
> Joe
> In new groups. If you have a question do post as question. Never select as
> comment. Question attract more responses.
> Regards
> R.D
In the English language, this is considered a question:
"Anyone know how I can batch export a set of procs at one time?"|||I think one of the frilly web guis to the newsgroup allows you to categorize
a post as a comment or a question, for some unknown reason.
A
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:k1yYe.261492$E95.127285@.fed1read01...
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:76E5A96A-8011-4A72-9C96-E005877BAF63@.microsoft.com...
> In the English language, this is considered a question:
> "Anyone know how I can batch export a set of procs at one time?"
>
>|||Joe
That is the problem. The people who knows only one language interpret in
terms of that only. NEWS GROUPS HAS LANGUAGE TOO. PLEASE READ HELP TO KNOW
THE DIFFERENCE BETWEEN COMMENT AND QUESTION.
what I meant was when you select new, you select it as a question for which
question mark appears besides your post.
Aaron
I dont expect those words that downgrade the norms of newgroups, from an MV
P.
Better ask MS why there are two varieties(comments and questions)
Regards
R.D
"Aaron Bertrand [SQL Server MVP]" wrote:

> I think one of the frilly web guis to the newsgroup allows you to categori
ze
> a post as a comment or a question, for some unknown reason.
> A
>
> "Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
> news:k1yYe.261492$E95.127285@.fed1read01...
>
>|||On Thu, 22 Sep 2005 23:03:02 -0700, R.D wrote:

>Joe
>That is the problem. The people who knows only one language interpret in
>terms of that only. NEWS GROUPS HAS LANGUAGE TOO. PLEASE READ HELP TO KNOW
>THE DIFFERENCE BETWEEN COMMENT AND QUESTION.
>what I meant was when you select new, you select it as a question for which
>question mark appears besides your post.
>Aaron
>I dont expect those words that downgrade the norms of newgroups, from an M
VP.
>Better ask MS why there are two varieties(comments and questions)
>Regards
>R.D
Hi R.D.,
What you seem to miss is the fact that these groups are actually usenet
groups, which be used in many ways. The oldest form includes the use of
some dedicated software that can access usenet groups. I use Agent for
example; Joe and Aaron appear to use Outlook Express.
Usenet groups have been around for some decades already. They were quite
popular before "Internet" became a hype.
Nowadays, having a "Forum", "Message board", or other "Community" on a
web site is the fashionable thing. Many companies have recognised the
value of those, but also recognise the value and strength of the
existing usenet groups. So they create a website that appears to be
their "own" message board, but that actually is just a mirror of one or
more usenet groups. Microsoft's community pages are an example of such a
portal; others are dbforums.com, examnotes.net, tech-archive.net,
and of course groups.google.com. All posts you read on those web sites
are taken from a usenet group, and all posts you write there are
directly forwarded to the same group.
Some of those web portals decided to add some extra frills and buttons.
If I recall correctly, the Microsoft site has buttons to say if a post
is usefull or not, and some kind of rating for authors. You seem to be
using thhat site; from your comments I understand that they also offer a
way to distinguish "questions" from "comments".
All that is fine for those who use that site. But it's not supported by
the decades-old usenet architecture. So the result is that the MS site
can only present the extra info for posts that originate from their own
site.
If you, like Aaron, me, and a bunch of other "regulars" in these groups,
attempt to keep up with several hundred new messages each day, and reply
to a dozen or more each day then you'll quickly find that the interface
offered by any of those web portals will constantly get in your way.
You'll want a program that allows you to download all new messages, that
will keep track of which messages you have already read and which
discussions you've decided to skip, etc.
If I had to find each message I post at the Microsoft site, only to make
a few mouseclicks to show others if it's a question or a comment (and
then probably do the same on a few other sites as well), that would cost
me so much time that my total contribution to these groups would be
reduced severly. And it would cost me so much energy that I'd probably
stop contributing and find another hobby before the month is over.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo
This is point is still valid.
Regards
R.D
"Hugo Kornelis" wrote:
> On Thu, 22 Sep 2005 23:03:02 -0700, R.D wrote:
>
> Hi R.D.,
> What you seem to miss is the fact that these groups are actually usenet
> groups, which be used in many ways. The oldest form includes the use of
> some dedicated software that can access usenet groups. I use Agent for
> example; Joe and Aaron appear to use Outlook Express.
> Usenet groups have been around for some decades already. They were quite
> popular before "Internet" became a hype.
> Nowadays, having a "Forum", "Message board", or other "Community" on a
> web site is the fashionable thing. Many companies have recognised the
> value of those, but also recognise the value and strength of the
> existing usenet groups. So they create a website that appears to be
> their "own" message board, but that actually is just a mirror of one or
> more usenet groups. Microsoft's community pages are an example of such a
> portal; others are dbforums.com, examnotes.net, tech-archive.net,
> and of course groups.google.com. All posts you read on those web sites
> are taken from a usenet group, and all posts you write there are
> directly forwarded to the same group.
> Some of those web portals decided to add some extra frills and buttons.
> If I recall correctly, the Microsoft site has buttons to say if a post
> is usefull or not, and some kind of rating for authors. You seem to be
> using thhat site; from your comments I understand that they also offer a
> way to distinguish "questions" from "comments".
> All that is fine for those who use that site. But it's not supported by
> the decades-old usenet architecture. So the result is that the MS site
> can only present the extra info for posts that originate from their own
> site.
> If you, like Aaron, me, and a bunch of other "regulars" in these groups,
> attempt to keep up with several hundred new messages each day, and reply
> to a dozen or more each day then you'll quickly find that the interface
> offered by any of those web portals will constantly get in your way.
> You'll want a program that allows you to download all new messages, that
> will keep track of which messages you have already read and which
> discussions you've decided to skip, etc.
> If I had to find each message I post at the Microsoft site, only to make
> a few mouseclicks to show others if it's a question or a comment (and
> then probably do the same on a few other sites as well), that would cost
> me so much time that my total contribution to these groups would be
> reduced severly. And it would cost me so much energy that I'd probably
> stop contributing and find another hobby before the month is over.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>