Scripting automation....

Is there any stored procedure for scripting the database?
SQL 2K.
Thanks,
Johnhttp://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith John" <Smith_Jr@.hotmail.com> wrote in message news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.g
bl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>|||There is no sp; you can use DMO (Distributed Management Objects) in some
scripting language. You can execite ActiveX script task in a scheduled job.
Here is an example of using DMO in vbscript :
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Smith John" <Smith_Jr@.hotmail.com> wrote in message
news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>|||I was searching for this article of yours a minute ago, but somehow I
couldn't find it...
Dejan Sarka
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uhNyQuK3FHA.472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Smith John" <Smith_Jr@.hotmail.com> wrote in message
> news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...

Scripting automation....

Is there any stored procedure for scripting the database?
SQL 2K.
Thanks,
John
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith John" <Smith_Jr@.hotmail.com> wrote in message news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>
|||There is no sp; you can use DMO (Distributed Management Objects) in some
scripting language. You can execite ActiveX script task in a scheduled job.
Here is an example of using DMO in vbscript:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Smith John" <Smith_Jr@.hotmail.com> wrote in message
news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>
|||I was searching for this article of yours a minute ago, but somehow I
couldn't find it...
Dejan Sarka
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uhNyQuK3FHA.472@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> http://www.karaszi.com/SQLServer/inf...ate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Smith John" <Smith_Jr@.hotmail.com> wrote in message
> news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...

Scripting automation....

Is there any stored procedure for scripting the database?
SQL 2K.
Thanks,
Johnhttp://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith John" <Smith_Jr@.hotmail.com> wrote in message news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>|||There is no sp; you can use DMO (Distributed Management Objects) in some
scripting language. You can execite ActiveX script task in a scheduled job.
Here is an example of using DMO in VBScript:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Smith John" <Smith_Jr@.hotmail.com> wrote in message
news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
> Is there any stored procedure for scripting the database?
> SQL 2K.
> Thanks,
> John
>
>
>|||I was searching for this article of yours a minute ago, but somehow I
couldn't find it...
--
Dejan Sarka
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uhNyQuK3FHA.472@.TK2MSFTNGP15.phx.gbl...
> http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Smith John" <Smith_Jr@.hotmail.com> wrote in message
> news:OZXYRiK3FHA.548@.TK2MSFTNGP10.phx.gbl...
>> Is there any stored procedure for scripting the database?
>> SQL 2K.
>> Thanks,
>> John
>>
>>

Script works in DTS, but has problems as a Stored Procedure

I have a script that builds multiple tables and then builds tables from those tables, etc..

Usually, I run the script as a DTS package, and it doesn't have any problems. However, when I save the script as a stored procedure, I think it is compiling the table builds into a different sequence.

As a result, some of the tables are blank when this script is run as a stored procedure.

Do I need to use transactions to prevent this compilation problem, or is there an easier setting that I can use to keep everything in the original sequence?

Thanks in advance.Anyone have any ideas on this? I can't use "GO", but I am still still having this compilation issue.sql

Script wackiness

I have tried to script all the tables and stored procedures from several
SQL2005 dbs and I noticed that I get double-quotes for single quotes and a
lot of extra single quotes near the END statements -- making the whole thing
unusable. Anyone know what this is?Could you please post some code ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Here are some examples. What the sprocs do is irrelevant, they've been used
for a long time. But this is script output from 2005 (previously scripted in
from 2000).
****************************************
*
CAST(IsNull(ME.ActualH,0) AS nvarchar(5))+'' ''+
CASE
WHEN ActualHFrac = 0 THEN ''''
WHEN ActualHFrac = 0.0625 THEN ''1/16''
..
/* note the double quotes for singles */
****************************************
**
...
COMMIT TRANSACTION
SELECT @.err = @.@.error
IF @.err <> 0
RETURN @.err
'
END
/* note the wacky little single quote before END, this does not show in 2000
output */
****************************************
**
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[sel_ContactBrandsCRM]') AND type in (N'P', N'PC')
)
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROCEDURE [dbo].[sel_ContactBrandsCRM]
@.ContactID int,
@.SaleID int
AS
...
/* note the single quote after the N on the EXEC line */
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168464966.601900.217650@.p59g2000hsd.googlegroups.com...
> Could you please post some code ?
> HTH, Jens K. Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
>|||Hi
you told that the scripts are unusable, if that means that the script
break on execution or creation you will have to use the SET
QUOtED_IDENTIFIERS OFF option .
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Script wackiness

I have tried to script all the tables and stored procedures from several
SQL2005 dbs and I noticed that I get double-quotes for single quotes and a
lot of extra single quotes near the END statements -- making the whole thing
unusable. Anyone know what this is?
Could you please post some code ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Here are some examples. What the sprocs do is irrelevant, they've been used
for a long time. But this is script output from 2005 (previously scripted in
from 2000).
*****************************************
CAST(IsNull(ME.ActualH,0) AS nvarchar(5))+'' ''+
CASE
WHEN ActualHFrac = 0 THEN ''''
WHEN ActualHFrac = 0.0625 THEN ''1/16''
...
/* note the double quotes for singles */
******************************************
...
COMMIT TRANSACTION
SELECT @.err = @.@.error
IF @.err <> 0
RETURN @.err
'
END
/* note the wacky little single quote before END, this does not show in 2000
output */
******************************************
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[sel_ContactBrandsCRM]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROCEDURE [dbo].[sel_ContactBrandsCRM]
@.ContactID int,
@.SaleID int
AS
...
/* note the single quote after the N on the EXEC line */
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168464966.601900.217650@.p59g2000hsd.googlegr oups.com...
> Could you please post some code ?
> HTH, Jens K. Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
>
|||Hi
you told that the scripts are unusable, if that means that the script
break on execution or creation you will have to use the SET
QUOtED_IDENTIFIERS OFF option .
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Script wackiness

I have tried to script all the tables and stored procedures from several
SQL2005 dbs and I noticed that I get double-quotes for single quotes and a
lot of extra single quotes near the END statements -- making the whole thing
unusable. Anyone know what this is?Could you please post some code ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Here are some examples. What the sprocs do is irrelevant, they've been used
for a long time. But this is script output from 2005 (previously scripted in
from 2000).
*****************************************
CAST(IsNull(ME.ActualH,0) AS nvarchar(5))+'' ''+
CASE
WHEN ActualHFrac = 0 THEN ''''
WHEN ActualHFrac = 0.0625 THEN ''1/16''
...
/* note the double quotes for singles */
******************************************
...
COMMIT TRANSACTION
SELECT @.err = @.@.error
IF @.err <> 0
RETURN @.err
'
END
/* note the wacky little single quote before END, this does not show in 2000
output */
******************************************
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[sel_ContactBrandsCRM]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROCEDURE [dbo].[sel_ContactBrandsCRM]
@.ContactID int,
@.SaleID int
AS
...
/* note the single quote after the N on the EXEC line */
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1168464966.601900.217650@.p59g2000hsd.googlegroups.com...
> Could you please post some code ?
> HTH, Jens K. Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
>|||Hi
you told that the scripts are unusable, if that means that the script
break on execution or creation you will have to use the SET
QUOtED_IDENTIFIERS OFF option .
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Script to store stored procedures in seperate files

How does MicroSoft store the stored procedures in seperate files.
What tools are used.
Cause that's what I like to do too.

Arno de Jong,

The Netherlands.
(SCPTFXR does not have the option to store the stored procedures in
different files
I think)Enterprise Manager will do this for you: Right-click on the database, click
"Generate SQL script", select the option "Create one file per object".

You can do this programmatically using the Script method of the DMO
StoredProcedure object. Call .Script once for each SP with an appropriate
file name.

http://msdn.microsoft.com/library/e...ef_m_s_0n1g.asp

--
David Portas
----
Please reply only to the newsgroup
--

"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bpshql$oie$1@.reader11.wxs.nl...
> How does MicroSoft store the stored procedures in seperate files.
> What tools are used.
> Cause that's what I like to do too.
> Arno de Jong,
> The Netherlands.
> (SCPTFXR does not have the option to store the stored procedures in
> different files
> I think)|||SQL Server stores the text of stored procedures/views/triggers in a
table called syscomments.

you could use SP_HELPTEXT spname to retrieve text for any stored
procedure
you could study the SP_HELPTEXT procedure and it will show you exactly
how microsoft retrieves the TEXT:) ENJOY

Peace:)

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<g8GdnSrn59RfdVyi4p2dnA@.giganews.com>...
> Enterprise Manager will do this for you: Right-click on the database, click
> "Generate SQL script", select the option "Create one file per object".
> You can do this programmatically using the Script method of the DMO
> StoredProcedure object. Call .Script once for each SP with an appropriate
> file name.
> http://msdn.microsoft.com/library/e...ef_m_s_0n1g.asp
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --
> "A.M. de Jong" <arnojo@.wxs.nl> wrote in message
> news:bpshql$oie$1@.reader11.wxs.nl...
> > How does MicroSoft store the stored procedures in seperate files.
> > What tools are used.
> > Cause that's what I like to do too.
> > Arno de Jong,
> > The Netherlands.
> > (SCPTFXR does not have the option to store the stored procedures in
> > different files
> > I think)

Script to grant execute for sprocs

Hello.

I'm using what looks to be a popular script to grant execute privileges to stored procedures, and it works great as long as the user account that you want to grant to is not a domain account.

For example, I need to grant execute to myDomain\dbUsers, but get a syntax error when the script tries to execute this statement:

SET @.SQL = 'GRANT EXECUTE ON [' + @.Owner

+ '].[' + @.StoredProcedure

+ '] TO myDomain\dbUsers'

Incorrect syntax near '\'.

The script works fine if a non-concatenated user account is given.

We use Active Directory to manage our access, thus the domain\group.

Has anyone found a way around this?

Thanks in advance.

Tess

Here's the entire script for anyone who's interested:

USE whateverDatabase

GO

DECLARE @.SQL nvarchar(4000),

@.Owner sysname,

@.StoredProcedure sysname,

@.RETURN int

-- Cursor of all the stored procedures in the current database

DECLARE cursStoredProcedures CURSOR FAST_FORWARD

FOR

SELECT USER_NAME(uid) Owner, [name] StoredProcedure

FROM sysobjects

WHERE xtype = 'P'

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_NAME(uid)) + '.' + QUOTENAME(name)), 'IsMSShipped') = 0

AND name LIKE 'p%'

OPEN cursStoredProcedures

-- "Prime the pump" and get the first row

FETCH NEXT FROM cursStoredProcedures

INTO @.Owner, @.StoredProcedure

-- Set the return code to 0

SET @.RETURN = 0

-- Encapsulate the permissions assignment within a transaction

BEGIN TRAN

-- Cycle through the rows of the cursor

-- And grant permissions

WHILE ((@.@.FETCH_STATUS = 0) AND (@.RETURN = 0))

BEGIN

-- Create the SQL Statement. Since we’re giving

-- access to all stored procedures, we have to

-- use a two-part naming convention to get the owner.

SET @.SQL = 'GRANT EXECUTE ON [' + @.Owner

+ '].[' + @.StoredProcedure

+ '] TO myDomain\dbUsers'

-- Execute the SQL statement

EXEC @.RETURN = sp_executesql @.SQL

-- Get the next row

FETCH NEXT FROM cursStoredProcedures

INTO @.Owner, @.StoredProcedure

END

-- Clean-up after the cursor

CLOSE cursStoredProcedures

DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.

IF (@.RETURN = 0)

BEGIN

-- Exited fine, commit the permissions

COMMIT TRAN

END

ELSE

BEGIN

-- Exited with an error, rollback any changes

ROLLBACK TRAN

-- Report the error

SET @.SQL = 'Error granting permission to ['

+ @.Owner + '].[' + @.StoredProcedure + ']'

RAISERROR(@.SQL, 16, 1)

END

GO

Just like with table and other object names, if the user contains special characters it must be "quoted" so, add either [] or "" around the user

|||

Thank you very much!

Tess

sql

Script to Add WITH ENCRYPTION to all SPs, Vs and Fns

I'm about to roll out a new database to another site and would like to encrypt all stored procedures, views and functions. I know this can be done by simply adding WITH ENCRYPTION in the appropriate spot, but I don't want to edit every single stored procedure, view and function one by one.

Is there a way (and if so, how do I go about it) to first of all output all stored procedures, views and scripts to a file, (not using enterprise manager, but rather using a sql script) then encrypt them all?

Essentially, I need to output them all to a file first just in case I have to edit them again.

Any ideas on how I would go about this?

Thanks in advanceAnyone have any clues?|||you need to script out the sp/fn/etc, then use FileSystemObject to read the file in and look for " AS" and prefix it with "with encryption". There isn't any *automated* way or sql builtin tool.

Script Table, Index, Stored Procedures from Table list

I have a list of approximately 2000 objects that need to create a sql script
to generate these objects. The script will be applied to another datbase to
generate these objects.
The 2000 objects name have been loaded into a Table A.
Please help put together a program that will read the object names from
Table A and generate a sql script for objects.
Thank You,Option#1
Try from Enterprise Manager (Tools -> Generate SQL Scripts)
Option#2
Try Import\Export utility from Enterprise Manager
Thanks,
RK
"Joe K." wrote:

> I have a list of approximately 2000 objects that need to create a sql scri
pt
> to generate these objects. The script will be applied to another datbase
to
> generate these objects.
> The 2000 objects name have been loaded into a Table A.
> Please help put together a program that will read the object names from
> Table A and generate a sql script for objects.
> Thank You,
>|||I would like to way read from a table to "Generate SQL Scripts".
I would like to automate the "Generate SQL Scripts" procedure.
Thanks,
"Ram Kumar Koditala" wrote:
> Option#1
> Try from Enterprise Manager (Tools -> Generate SQL Scripts)
> Option#2
> Try Import\Export utility from Enterprise Manager
> Thanks,
> RK
> "Joe K." wrote:
>|||You'll have to use SQL DMO to do this. See if the following links help:
http://www.karaszi.com/SQLServer/in...rate_script.asp
http://www.databasejournal.com/feat...cle.php/1480901
Anith

Script Stored Procedures based on dependency

I don't follow the logic of a sql server farm as if they
were entirely different databases without incorporating
replication. Clustering is largely for failover I believe.
That said, this script does a nice job of creating
a sql script of all your procs from your qa/development
database in order by dependency if you really do
need to copy the procs to multiple databases:
http://www.eggheadcafe.com/articles/20030609.asp
Robbe Morris - 2004/2005 Microsoft MVP C#
Earn money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uCg%230cOpFHA.3244@.TK2MSFTNGP09.phx.gbl...
> How are people dealing with SPs in an n-tiered environment?
> If you have x number of machines and they all have to have the same SPs,
> how do you deal with changes to the SPs?
> Do you make changes to one Server and then copy them to the rest?
> We are concerned with this as we are expecting to be setting up a Web Farm
> as well as multiple Sql Servers to spread out the load.
> Thanks,
> Tom
>"Robbe Morris [C# MVP]" <info@.eggheadcafe.com> wrote in message
news:%23yphCPPpFHA.2156@.TK2MSFTNGP14.phx.gbl...
>I don't follow the logic of a sql server farm as if they
> were entirely different databases without incorporating
> replication. Clustering is largely for failover I believe.
I am not really sure, either.
This was something that was brought up at one of our development meetings
that one of our partners used (with oracle, actually I believe). They were
talking about outgrowing the server they had and needed to add servers to
spread the load. Not really sure how they were doing it.

> That said, this script does a nice job of creating
> a sql script of all your procs from your qa/development
> database in order by dependency if you really do
> need to copy the procs to multiple databases:
> http://www.eggheadcafe.com/articles/20030609.asp
Like it.
Worked like a charm, with no changes at all.
Thanks,
Tom
> --
> Robbe Morris - 2004/2005 Microsoft MVP C#
> Earn money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:uCg%230cOpFHA.3244@.TK2MSFTNGP09.phx.gbl...
>

Script SQL 2005 database to individual files?

I am trying to script the DROP(IF EXISTS) and CREATE for all of my
tables, views, stored procs, and functions to individual SQL text files
(one per object). This was trivially done in SQL 2000 with Enterprise
Manager, but when I try in SQL 2005 through Management Studio my only
"script mode" options are:

- Script to file (which is one huge file with everything)
- Script to Clipboard
- Script to New Query Window

FYI, I get to this screen through Management Studio by right clicking
on a database and selecting Tasks > Generate Scripts... > Next (doesn't
seem to matter what combo of objects I select to script or what other
options). I am using SQL Server 2005 Developer (which is Microsoft SQL
Server Management Studio 9.00.1399.00).

Any solution to this (i.e. via Management Studio, command line, etc.)
would be greatly appreciated.

Thanks.
TedMSDN/BOL indicates that this is possible here
http://msdn2.microsoft.com/en-us/library/ms191299.aspx. However, it
doesn't give any clue on how to accomplish it.

Quote from that page: "The schema for generated objects can be saved in
a single SQL Script file, or in several files with each file containing
the schema of just one object."|||Ted O'Connor (toconnor@.gmail.com) writes:
> I am trying to script the DROP(IF EXISTS) and CREATE for all of my
> tables, views, stored procs, and functions to individual SQL text files
> (one per object). This was trivially done in SQL 2000 with Enterprise
> Manager, but when I try in SQL 2005 through Management Studio my only
> "script mode" options are:
> - Script to file (which is one huge file with everything)
> - Script to Clipboard
> - Script to New Query Window
> FYI, I get to this screen through Management Studio by right clicking
> on a database and selecting Tasks > Generate Scripts... > Next (doesn't
> seem to matter what combo of objects I select to script or what other
> options). I am using SQL Server 2005 Developer (which is Microsoft SQL
> Server Management Studio 9.00.1399.00).
> Any solution to this (i.e. via Management Studio, command line, etc.)
> would be greatly appreciated.

Rather than hackiong SMO on your own, I don't think there is one.
Note that there are really two features from SQL 2000 you are missing:
1) One object per file.
2) A script that performs both DROP and CREATE.

There is this suggestion on MSDN Product Feedback Centre,
http://lab.msdn.microsoft.com/produ...px?feedbackid=9
eb6c773-2dbb-4a27-b9d8-225d6ed4385a
the by far most voted-on item for SQL Server.

I did actually only find one item that brings up one file per object,
http://lab.msdn.microsoft.com/produ...px?feedbackid=2
7695db7-cef6-42c3-9cb0-ac30583bbee9
there are no votes here, beside the submitted, but that is because the
bug has not been validated.

In any case, that would be better as a suggestion, as it is not a bug
that you can't script per object, just a poor design. So if you can't
find a suggestion on that theme - submit one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Script out objects

Is there any command to script out tables/stored procedures?
I want to make a process to script out tables every night.

thanks,"neo" <second714@.hotmail.com> wrote in message
news:155f8e7d.0311201526.39f956fb@.posting.google.c om...
> Is there any command to script out tables/stored procedures?
> I want to make a process to script out tables every night.
> thanks,

There's no TSQL command to do this, but you can use the SQLDMO interface to
generate the scripts. Although you could do it from a procedure using
sp_OACreate, it would probably be better to write an external script in your
preferred scripting language - VBScript, Perl, etc. - because it will be
much easier to work with the output files.

Simon|||any sample code in perl or VBScript?

Thanks,|||second714@.hotmail.com (neo) wrote in message news:<155f8e7d.0311211101.32d01304@.posting.google.com>...
> any sample code in perl or VBScript?
> Thanks,

Here's a snippet of VBScript (untested) to script all the tables in a
DB - check the docs for the constants available with the Script
method:

Set oSQL = WScript.CreateObject("SQLDMO.SQLServer2")
oSQL.Name = "MyServer"
oSQL.LoginSecure = True
oSQL.Connect

'Script all tables
For Each oTable In oSQL.Databases("MyDatabase").Tables
If Not oTable.SystemObject Then
oTable.Script 4 Or 262144 Or 520093696 Or 131072 Or 2 Or 64,
"C:\OutputFolder\" + oTable.Name + ".sql"
End If
Next

Simon

Script or Stored Procedure that Collects Information from a Database

Information such as:
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollinger
If you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger

Script or Stored Procedure that Collects Information from a Database

Information such as:
1. Database Name
2. Users
3. Database Properties (e.g., size, number of tables, etc.)
4. Other information of interest to a DBA (e.g., Maintenance Plan and
if the last job ran successfully)
I know there are umteen utilities out there that can derive this
information. I would like the product to derive its own information
using scripts or stored procs.
Thanks for any suggestions!
RBollingerIf you run profiler while you look through the GUI's for this info, you can
see the tsql they are generating. This can help you build your own tools.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"robboll" <robboll@.hotmail.com> wrote in message
news:3ffe6464-ae36-47f9-94c4-14fb02266d23@.60g2000hsy.googlegroups.com...
> Information such as:
> 1. Database Name
> 2. Users
> 3. Database Properties (e.g., size, number of tables, etc.)
> 4. Other information of interest to a DBA (e.g., Maintenance Plan and
> if the last job ran successfully)
> I know there are umteen utilities out there that can derive this
> information. I would like the product to derive its own information
> using scripts or stored procs.
>
> Thanks for any suggestions!
> RBollinger

Script Help

Would really appreciate some help/ideas. The stored procedures for my
application are all saved to a folder in the file system of my deeloper work
station. What is the best way to loop through the folder and execute the sql
(create procedure...)in each file?
Thanks for any tipsOne method is to run your scripts with a FOR command from the command-prompt
that invokes OSQL. For example:
FOR %f IN (*.SQL) DO OSQL -i "%f" /E /S MyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:F9E35C8D-A44A-484A-A63B-D3571A908AF8@.microsoft.com...
> Would really appreciate some help/ideas. The stored procedures for my
> application are all saved to a folder in the file system of my deeloper
> work
> station. What is the best way to loop through the folder and execute the
> sql
> (create procedure...)in each file?
> Thanks for any tips|||FYI, I generally just copy all of the procedures to one file and run that:
COPY *.* SP.sql
But this will only work if you have a GO statement at the bottom of each.
"Dan Guzman" wrote:

> One method is to run your scripts with a FOR command from the command-prom
pt
> that invokes OSQL. For example:
> FOR %f IN (*.SQL) DO OSQL -i "%f" /E /S MyServer
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mardy" <Mardy@.discussions.microsoft.com> wrote in message
> news:F9E35C8D-A44A-484A-A63B-D3571A908AF8@.microsoft.com...
>
>

Script for Creating a Stored Procedure??

Hello,
I'm trying to create a store procedure from an interface which is similiar to Query Analyzer, but I can't get it to work and I don't get an error message...so I'm not sure what error I am receiving. Please see the below sp_get_topics (#1) which is not saving and sp_get_topics (#2 works). Thanks in advance...

--#1--
CREATE PROCEDURE [sp_get_topics] AS
SELECT t_1.Topic, t_1.Message, t_1.Create_Date, t_2.Username
FROM t_1 WITH (READUNCOMMITTED) LEFT OUTER JOIN
t_2 WITH (READUNCOMMITTED) ON t_1.User_ID = t_2.ID
GO

--#2--
CREATE PROCEDURE [sp_get_topics]
AS
SELECT Topic, Message, Create_Date
FROM t_1
GOWhen you're saying that it "is not saving" you mean you can't compile it?|||I hope this is intentional to demonstrate your dilemna but both procs have the same name.|||Which version of sql server are you using ?|||Originally posted by rnealejr
Which version of sql server are you using ?

1) I hope this is intentional to demonstrate your dilemna but both procs have the same name. -- right...I'm trying to compile it with different names, so there's no conflict.

2) Which version of sql server are you using ? -- 2000|||Originally posted by rdjabarov
When you're saying that it "is not saving" you mean you can't compile it?

Yes, I meant "compile it".|||Replace READUNCOMMITTED with NOLOCK|||uuummmmmmmmmmmmmmmmmmmmmmmmmm

Why aren't you using QA?|||Originally posted by rdjabarov
Replace READUNCOMMITTED with NOLOCK

Yeah...I've tried with & without WITH (READUNCOMMITTED) and WITH (NOLOCK), still doesn't work. I'm working with a crappy Entreprise Manager which is web based interface, so that's probably the problem.

Any ideas? Thanks again...|||Originally posted by Brett Kaiser
uuummmmmmmmmmmmmmmmmmmmmmmmmm

Why aren't you using QA?

please read below...thanks.|||Why do you want uncommitted data?

Don't you think that would be dangerous and be a problem with data integrity?

And why can't you install the client tools locally?|||Originally posted by Brett Kaiser
Why do you want uncommitted data?

Don't you think that would be dangerous and be a problem with data integrity?

And why can't you install the client tools locally?

I'm not planning on sticking with the "WITH (readuncommitted)"...I'm just trying every possible way to get this annoying interface to work.

You're right if I was worried about data integrity I wouldn't want to go this route...but it's just a personal site of mine. And still I don't want to use it.

Install the client tools locally? -- I'm using a my web hosts crappy ASP.Net Enterprise Manager (www.aspenterprisemanager.com) version to manipulate my sql tables (which is I guess an old version), so I have to use scripts to create all tables & stored procedures within their version of Query Analyzer. I guess there is a better version out there somewhere, but I haven't been able to get the feedback to know how to setup it up. I would love to use my SQL Server Enterprise Manager locally, but don't know how. If you have any ideas...please let me know.

script for all dependencies

Hi All,
Is there any script to get all the dependencies on all the tables or all the
Stored Procedures in a database? I need to check the dependencies are still
there after I upgrade a database.
Thanks
JuliaTry this script. The output from this is the sql code you can run.
select 'sp_depends ' + '''' + name + '''' + char(13) + 'GO' from sysobjects
where type = 'U' and name not like 'dt%'
The output will look something like this:
sp_depends 'Orders'
GO
sp_depends 'Products'
GO
sp_depends 'Order Details'
GO
sp_depends 'CustomerCustomerDemo'
GO
sp_depends 'CustomerDemographics'
GO
sp_depends 'Region'
GO
sp_depends 'Territories'
GO
sp_depends 'EmployeeTerritories'
GO
sp_depends 'Employees'
GO
sp_depends 'Categories'
GO
"Julia" wrote:
> Hi All,
> Is there any script to get all the dependencies on all the tables or all the
> Stored Procedures in a database? I need to check the dependencies are still
> there after I upgrade a database.
> Thanks
> Julia

script file from stored procedure

Hi
Can anybody tell me how I call a sql script file(or any file for that
matter) from within a stored procedure? e.g. file on c:\filename.sql
I want the procedure to retrieve the file and also to run it.
Thanks in advance
The simplest way to do this is using xp_cmdshell to call ISQL or OSQL.
There is no direct TSQL command that allows you to run a script stored in a
file.

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:21A98D8B-C58A-46E3-B8A4-D6D115AE9744@.microsoft.com...
> Hi
> Can anybody tell me how I call a sql script file(or any file for that
> matter) from within a stored procedure? e.g. file on c:\filename.sql
> I want the procedure to retrieve the file and also to run it.
> Thanks in advance
|||You can use xp_cmdshell and oSql to do this.
Andrew J. Kelly SQL MVP
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:21A98D8B-C58A-46E3-B8A4-D6D115AE9744@.microsoft.com...
> Hi
> Can anybody tell me how I call a sql script file(or any file for that
> matter) from within a stored procedure? e.g. file on c:\filename.sql
> I want the procedure to retrieve the file and also to run it.
> Thanks in advance