How to enumerate all tables, views, stored procs in a database....

I posted this a short while ago
I have three main database files on a SQL 2000 server. Each database has
about 200 tables, views, stored procs, etc. I need to be quckly able to run
something in SQL Query Analyzer that will enumerate all teh tables, or all
the views, or all the Stored Procs in a db, so I can then use that output to
run a Grant or deny statement. I don't want to use roles, but instead want to
write a query that will allow me to do this.
Thank you.
S
I posted a piece of T-SQL within your other post that allows you retrieve a
list of user tables. Here is that piece of sql once again:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
I am curious. Have you thought of using stored procedures to access your
tables instead of direct table access?
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:152C1771-FD5C-4229-A8B3-4E856D246247@.microsoft.com...
> I posted this a short while ago
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables, views, stored procs, etc. I need to be quckly able to
run
> something in SQL Query Analyzer that will enumerate all teh tables, or all
> the views, or all the Stored Procs in a db, so I can then use that output
to
> run a Grant or deny statement. I don't want to use roles, but instead want
to
> write a query that will allow me to do this.
> Thank you.
> S
>
sql

How to enumerate all tables, views, stored procs in a database....

I posted this a short while ago
I have three main database files on a SQL 2000 server. Each database has
about 200 tables, views, stored procs, etc. I need to be quckly able to run
something in SQL Query Analyzer that will enumerate all teh tables, or all
the views, or all the Stored Procs in a db, so I can then use that output to
run a Grant or deny statement. I don't want to use roles, but instead want t
o
write a query that will allow me to do this.
Thank you.
SI posted a piece of T-SQL within your other post that allows you retrieve a
list of user tables. Here is that piece of sql once again:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
I am curious. Have you thought of using stored procedures to access your
tables instead of direct table access?
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:152C1771-FD5C-4229-A8B3-4E856D246247@.microsoft.com...
> I posted this a short while ago
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables, views, stored procs, etc. I need to be quckly able to
run
> something in SQL Query Analyzer that will enumerate all teh tables, or all
> the views, or all the Stored Procs in a db, so I can then use that output
to
> run a Grant or deny statement. I don't want to use roles, but instead want
to
> write a query that will allow me to do this.
> Thank you.
> S
>

How to enumerate all tables, views, stored procs in a database....

I posted this a short while ago
I have three main database files on a SQL 2000 server. Each database has
about 200 tables, views, stored procs, etc. I need to be quckly able to run
something in SQL Query Analyzer that will enumerate all teh tables, or all
the views, or all the Stored Procs in a db, so I can then use that output to
run a Grant or deny statement. I don't want to use roles, but instead want to
write a query that will allow me to do this.
Thank you.
SI posted a piece of T-SQL within your other post that allows you retrieve a
list of user tables. Here is that piece of sql once again:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(object_id(TABLE_NAME), 'IsUserTable') = 1
I am curious. Have you thought of using stored procedures to access your
tables instead of direct table access?
--
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:152C1771-FD5C-4229-A8B3-4E856D246247@.microsoft.com...
> I posted this a short while ago
> I have three main database files on a SQL 2000 server. Each database has
> about 200 tables, views, stored procs, etc. I need to be quckly able to
run
> something in SQL Query Analyzer that will enumerate all teh tables, or all
> the views, or all the Stored Procs in a db, so I can then use that output
to
> run a Grant or deny statement. I don't want to use roles, but instead want
to
> write a query that will allow me to do this.
> Thank you.
> S
>

how to encrypt db files

Came across the following on best practises as well
" Use the SQL Server service account to encrypt database files with EFS."
How do you set it up ?Just because it is on someone's best practices list, doesn't mean it should
just be grabbed and executed. When you encrypt the files on the OS, you
incur a performance hit everytime they need to be accessed to do the
decrypt/encrypt. Since a database server never quits reading or writing to
the files, this can be a rather heavy load on the machine and cause
significant degradation in performance.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:exfOsOKIGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Came across the following on best practises as well
> " Use the SQL Server service account to encrypt database files with EFS."
> How do you set it up ?
>|||>decrypt/encrypt. Since a database server never quits reading or writing to
Actually SQL2005 seems to close the file if no active query is running...
-- AntiSpam/harvest --
Remove X's to send email to me.|||The server is supposed to hold the individual database files open and in
a ready state regardless of user activity. Are you sure you don't have
the autoclose property turned on for the database you are looking at?
That is:
select databaseproperty('<dbname>', 'IsAutoClose')
*mike hodgson*
http://sqlnerd.blogspot.com
Josh Assing wrote:

>Actually SQL2005 seems to close the file if no active query is running...
>
>-- AntiSpam/harvest --
>Remove X's to send email to me.
>|||Hi Hassan,
Encrypting your data files can be a prudent measure (MSDE instance on a
field salesperson's laptop with customer data, for example). I
wouldn't necessarily recommend it on the server side.
Here is a nice how-to on encrypting your files using EFS...
http://www.sqlservercentral.com/col...menting_efs.asp

How to enable Ah Hoc Query in SQL 2000

Hi guys

I have a program which imports data into sql server from csv files. This program uses the something like this

CREATE PROCEDURE MyImport

(

@.FileName AS VARCHAR(200)

)

AS

SET NOCOUNT ON

DECLARE @.SQL VARCHAR(4000)

SET @.SQL = ' SELECT *

FROM OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\;'', ''SELECT * FROM ' + QUOTENAME(@.FileName) + '' + ''') as t

'

EXEC (@.SQL)

when i excute this i get a permission error saying the account i specified in the web config doesn't have rights to run these type of scripts. I tried using the sa account which works. But its not recommended to setup applications to access the database using the sa account.

i did some research and i found out that yon can actually enable that by change a registry key. But i cant find the MSDASQL datasource under ms sql server providers in the registry.

Any other ways i can enable this?

Have a look at http://www.asp101.com/articles/john/connstring/default.asp|||

Excellent article. Will give it a go at it on monday.

Thanks

|||

Hi TATWORTH

The link you provided was good but not what im looking for. If you read my post carefully you will know. I have a stored procedure which will select from a file passed in as a parameter using theMSDASQL provider. NOW THIS IS ALL DONE IN SQL not in my app.

I was saying that the account i specified in the web.config does not have permissions to use the MSDASQL provider inside of SQL. The account i used only have rights to run stored procedures and that is it. But my script is written inside of a stored procedure. The reason for not being able to run the script inside of the procedure is because this procedure produces a dynamic script and executes that inside of the stored procedure.

i heard you can enable a registry key which will enable the use of that provider, but i cant find theMSDASQLprovider in the registry.

i was looking for a way to grant permissions to (web_user account) execute these type of queries.

Hope this cleared things up

Cheers

How to edit a asp.net2.0/C# project in VS2005, Plz HELP

Hi,

How can i run or edit my ASP.NET 2.0/C# project in VS2005. I have source code files (including database) but it doesn't have .sln file. Please guide me how can i edit or run this in VS2005

Hi,

if you are missing the sln file, you should create a new web project and import the existing files to your solution.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the help.

which files are necessary, while sending ASP.NET 2.0/C# project (for further development) to the developer, like .sln files, database.

please write the name of other files. and how to open that project in VS2005.

Thanks

vkkv

|||Every file except *user files as they store user specific information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to easily export users from Prod SQL and import into Test SQL

I hope this is a simple enough question to answer.
Every night we back up 2 large Production database files and then move them
over to our testing SQL environment (different) server, so that our
developers can play with one-day old production data. My boss wants me each
night to export out our list of users from the prod sql and then import this
into the test SQL. I understand enought to know that i will need a T-sql
script, but can I easily expect to dump all the users in one feel swoop, or
do I need to so this one user or one group at at time and then import them in
the same way? is there an easier way that I am missing? Thanks.
Hi,
The users will be automatically moved after the database restore. But if you
have
login syncronization issue then you could use the system procedure
sp_change_users_login
to sync the logins with users. Once you syncronize it is not required to
export and import the users.
See the below link to export and import the logins.
http://www.databasejournal.com/featu...le.php/2228611
Thanks
Hari
MCDBA
"Sam" wrote:

> I hope this is a simple enough question to answer.
> Every night we back up 2 large Production database files and then move them
> over to our testing SQL environment (different) server, so that our
> developers can play with one-day old production data. My boss wants me each
> night to export out our list of users from the prod sql and then import this
> into the test SQL. I understand enought to know that i will need a T-sql
> script, but can I easily expect to dump all the users in one feel swoop, or
> do I need to so this one user or one group at at time and then import them in
> the same way? is there an easier way that I am missing? Thanks.

How to easily export users from Prod SQL and import into Test SQL

I hope this is a simple enough question to answer.
Every night we back up 2 large Production database files and then move them
over to our testing SQL environment (different) server, so that our
developers can play with one-day old production data. My boss wants me each
night to export out our list of users from the prod sql and then import this
into the test SQL. I understand enought to know that i will need a T-sql
script, but can I easily expect to dump all the users in one feel swoop, or
do I need to so this one user or one group at at time and then import them in
the same way? is there an easier way that I am missing? Thanks.Hi,
The users will be automatically moved after the database restore. But if you
have
login syncronization issue then you could use the system procedure
sp_change_users_login
to sync the logins with users. Once you syncronize it is not required to
export and import the users.
See the below link to export and import the logins.
http://www.databasejournal.com/features/mssql/article.php/2228611
Thanks
Hari
MCDBA
"Sam" wrote:
> I hope this is a simple enough question to answer.
> Every night we back up 2 large Production database files and then move them
> over to our testing SQL environment (different) server, so that our
> developers can play with one-day old production data. My boss wants me each
> night to export out our list of users from the prod sql and then import this
> into the test SQL. I understand enought to know that i will need a T-sql
> script, but can I easily expect to dump all the users in one feel swoop, or
> do I need to so this one user or one group at at time and then import them in
> the same way? is there an easier way that I am missing? Thanks.

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||What if he doesn't have EM? He didn't say what version he was on. I only
have 2k5 with SMS on my dev box. This version seems to have resorted to
dumping procs out as strings submitted to sp_executesql for some ungodly
reason. This has frustrated me to no end!!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148392984.143435.54080@.j33g2000cwa.googlegroups.com...
> do you mean the source code?
> right click somewhere in the procedures window in Enterprise Manager
> all tasks-->generate sql script, select the procs you want to
> script,click on the options tab and select one file per object
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> samtil...@.gmail.com wrote:
>|||For SQL Server 2005 check out Bill graziano's scripting tool
http://weblogs.sqlteam.com/billg/ar...11/22/8414.aspx
and
http://weblogs.sqlteam.com/billg/ar...12/24/8613.aspx
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Your best bet is to use one of the API's which has such functionality built-
in: DMO (2000) or SMO
(2005). I have some info here http://www.karaszi.com/SQLServer/in...ip
t.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroups
.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||Oh, yes!! Thanks...that fits the bill perfectly!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148395150.393418.229440@.j33g2000cwa.googlegroups.com...
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/ar...11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/ar...12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>

>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO"
& vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO"
& vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFile
Number
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProc
s, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "G
O"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFi
leNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & v
bCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO
" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't kno
w how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only r
eturns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) cat
alog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not refle
ct that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed proc
edures - make sure you
test it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegroup
s.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||Your best bet is to use one of the API's which has such functionality built-
in: DMO (2000) or SMO
(2005). I have some info here http://www.karaszi.com/SQLServer/in...ip
t.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroup
s.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>

>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO"
& vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO"
& vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFile
Number
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProc
s, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "G
O"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFi
leNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & v
bCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO
" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't kno
w how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only r
eturns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) cat
alog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not refle
ct that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed proc
edures - make sure you
test it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegrou
ps.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>

How to Dump Multiple Stored Procedures to Multiple Files?

I would like to dump each of the stored procedures in one database to a
separate file.
Programmatically would be great (e.g., start with SELECT * FROM
sysobjects WHERE xtype='P'), so that I can have greater flexibility.
My last resort is to use some tool already written by somebody else
that achieves this same goal, especially if I could get the source code
on how to do it myself.
Any ideas?do you mean the source code?
right click somewhere in the procedures window in Enterprise Manager
all tasks-->generate sql script, select the procs you want to
script,click on the options tab and select one file per object
Denis the SQL Menace
http://sqlservercode.blogspot.com/
samtil...@.gmail.com wrote:
> I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?|||What if he doesn't have EM? He didn't say what version he was on. I only
have 2k5 with SMS on my dev box. This version seems to have resorted to
dumping procs out as strings submitted to sp_executesql for some ungodly
reason. This has frustrated me to no end!!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148392984.143435.54080@.j33g2000cwa.googlegroups.com...
> do you mean the source code?
> right click somewhere in the procedures window in Enterprise Manager
> all tasks-->generate sql script, select the procs you want to
> script,click on the options tab and select one file per object
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> samtil...@.gmail.com wrote:
>> I would like to dump each of the stored procedures in one database to a
>> separate file.
>> Programmatically would be great (e.g., start with SELECT * FROM
>> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>> My last resort is to use some tool already written by somebody else
>> that achieves this same goal, especially if I could get the source code
>> on how to do it myself.
>> Any ideas?
>|||For SQL Server 2005 check out Bill graziano's scripting tool
http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
and
http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Your best bet is to use one of the API's which has such functionality built-in: DMO (2000) or SMO
(2005). I have some info here 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/
<samtilden@.gmail.com> wrote in message news:1148392340.216845.94900@.j55g2000cwa.googlegroups.com...
>I would like to dump each of the stored procedures in one database to a
> separate file.
> Programmatically would be great (e.g., start with SELECT * FROM
> sysobjects WHERE xtype='P'), so that I can have greater flexibility.
> My last resort is to use some tool already written by somebody else
> that achieves this same goal, especially if I could get the source code
> on how to do it myself.
> Any ideas?
>|||Oh, yes!! Thanks...that fits the bill perfectly!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1148395150.393418.229440@.j33g2000cwa.googlegroups.com...
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||On 23 May 2006 06:52:20 -0700, samtilden@.gmail.com wrote:
in <1148392340.216845.94900@.j55g2000cwa.googlegroups.com>
>I would like to dump each of the stored procedures in one database to a
>separate file.
>Programmatically would be great (e.g., start with SELECT * FROM
>sysobjects WHERE xtype='P'), so that I can have greater flexibility.
>My last resort is to use some tool already written by somebody else
>that achieves this same goal, especially if I could get the source code
>on how to do it myself.
>Any ideas?
Here's a little VB6 code that does what you want. Just include the
microsoft SQLDMO Object Library. Functions, Triggers, and Stored
Procedures are extracted to one file and Views to another. Obviously
you'll need to modify the server and database names.
Option Explicit
Private Sub Main()
Dim oServer As SQLDMO.SQLServer2: Set oServer = New SQLDMO.SQLServer2
oServer.LoginSecure = True
oServer.Connect "STEFS-GEORGIA\HORSESHOWTIME"
Dim oDB As SQLDMO.Database2: Set oDB = oServer.Databases("ShowTime")
Dim sProcs() As String: ReDim sProcs(0 To oDB.StoredProcedures.Count - 1)
Dim oSP As SQLDMO.StoredProcedure
Dim lngN As Long: lngN = 2
For Each oSP In oDB.StoredProcedures
sProcs(lngN) = Trim$(oSP.Text)
lngN = lngN + 1
Next
Set oSP = Nothing
Dim oTable As SQLDMO.Table2
For Each oTable In oDB.Tables
Dim oTrigger As SQLDMO.Trigger2
For Each oTrigger In oTable.Triggers
Dim sTriggers As String: sTriggers = sTriggers & Trim$(oTrigger.Text) & "GO" & vbCrLf & vbCrLf
Next
Next
Set oTrigger = Nothing
Set oTable = Nothing
Dim oUDF As SQLDMO.UserDefinedFunction
For Each oUDF In oDB.UserDefinedFunctions
Dim sFunctions As String: sFunctions = sFunctions & Trim$(oUDF.Text) & "GO" & vbCrLf & vbCrLf
Next
Dim sViews() As String: ReDim sViews(0 To oDB.Views.Count - 1): lngN = 0
Dim oView As SQLDMO.View
For Each oView In oDB.Views
If (Not oView.Name Like "sys*") Then
sViews(lngN) = Trim$(oView.Text)
lngN = lngN + 1
End If
Next
Set oUDF = Nothing: Set oDB = Nothing
oServer.DisConnect: Set oServer = Nothing
Dim intFileNumber As Integer: intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\sp.txt" For Output Lock Write As #intFileNumber
Print #intFileNumber, Replace$(Replace$(Trim$(sFunctions) & Trim$(Join(sProcs, vbCrLf)) & vbCrLf & Trim$(sTriggers), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
intFileNumber = FreeFile
Open "D:\My Documents\ShowTime\SQL\view.txt" For Output Lock Write As #intFileNumber
Print #intFileNumber, Replace$(Replace$(Trim$(Join(sViews, vbCrLf & "GO" & vbCrLf)), vbCrLf & vbCrLf & "GO", vbCrLf & "GO"), "GO" & vbCrLf & vbCrLf, "GO" & vbCrLf)
Close #intFileNumber
End Sub
This posting is provided "AS IS" with no warranties and no guarantees either express or implied.
Stefan Berglund|||I thank all of you for your offerings to help me.
However, there must be some way to get the source code of stored
procedures directly from the database itself and not go through any
third party software or tool. After all, those third party tools do
it. But how?
For example, we can get table structures, column definitions, foreign
keys and indexes directly from the system tables. We should, I
believe, be able to get the stored procedures also from tables like
sysobjects, sysproperties, syscomments or some system table, right?|||You can get the source code in SQL 2005 by using OBJECT_DEFINITION like
this
SELECT SPECIFIC_NAME,OBJECT_DEFINITION( OBJECT_ID(SPECIFIC_NAME))
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE ='PROCEDURE'
or you can query sys.sql_modules for the definition
In 2000 you can use sp_helptext
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Sure:
2000:
Source code in syscomments
Also available though sp_helptext
Several rows for one object in syscomments if > 4000 characters, I don't know how well sp_help
handles this in current incarnation.
The ROUTINE_DEFINTION column in the INFORMATION_SCHEMA.ROUTINES view. Only returns first 4000
characters.
2005:
Above applies, and also:
Column named definition in the sys.sql_modules (probably others as well) catalog view.
OBJECT_DEFINITION() function
Above two returns nvarchar(max), so not problem with length of source code.
Be careful if you have renamed the procedure, the source code does not reflect that rename. I don't
know how clever the scripting stuff are (DMO and SMO) regarding renamed procedures - make sure you
test it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<samtilden@.gmail.com> wrote in message news:1148472967.521632.244810@.j73g2000cwa.googlegroups.com...
>I thank all of you for your offerings to help me.
> However, there must be some way to get the source code of stored
> procedures directly from the database itself and not go through any
> third party software or tool. After all, those third party tools do
> it. But how?
> For example, we can get table structures, column definitions, foreign
> keys and indexes directly from the system tables. We should, I
> believe, be able to get the stored procedures also from tables like
> sysobjects, sysproperties, syscomments or some system table, right?
>|||It's absolutely braindead that they left this out of the 2005 Management
Studio.
"SQL" wrote:
> For SQL Server 2005 check out Bill graziano's scripting tool
> http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx
> and
> http://weblogs.sqlteam.com/billg/archive/2005/12/24/8613.aspx
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

How to do certain task in SQL?

Hi Everyone,
I come from a Foxpro/dBase background (almost 20 yrs of DBF files) and
I'm new to SQL. I've been experimenting with VB.Net and MSDE for a few
months now and I'm very impressed. I got the go ahead to convert a
major xBase application to SQL/VB.Net.
I am not sure how to do certain tasks in SQL. I need to know how to do
the following...
Move to the last record in a table (in xBase, it is "go bottom")
Move to the first record in a table (in xBase, it is "go top")
Append a new blank record to a table (in xBase, it is "append blank")
Move to the next record in a table
In the DBF world, some tasks require filtering a file (say all records
belonging to invoice <n>) and then processing each record in a loop.
This involved moving a record pointer with either a "goto" or "skip"
command. I suspect the SQL equivalent is to use the "SELECT" statement
to filter the records and then to use the "UPDATE" or "DELETE" commands
to edit the table. Do I have the right idea?
Thanks.
Richard
Hi Richard,
Don't take this personally, but from the questions you are asking, you have
a bit of a learning curve ahead of you. First you need to stop looking at
SQL data as a list of sequential records. SQL data is retrieved and
manipulated as sets of data. There really is no equivalent to "move to last
record" or first record. If you need to update or select a particular record
then you need to define your records with either a primary key or some other
unique contraint on the data and provide the SQL statement that will extract
a distinct record. I suggest that you find a good general book on SQL and
study up on relational database theory. As a recommendation "Data &
Databases: Concepts In Practice" by Joe Celko is a very good book that lays
down the basics of relational database design.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:u%23WzFEO$EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I come from a Foxpro/dBase background (almost 20 yrs of DBF files) and I'm
> new to SQL. I've been experimenting with VB.Net and MSDE for a few months
> now and I'm very impressed. I got the go ahead to convert a major xBase
> application to SQL/VB.Net.
> I am not sure how to do certain tasks in SQL. I need to know how to do
> the following...
> Move to the last record in a table (in xBase, it is "go bottom")
> Move to the first record in a table (in xBase, it is "go top")
> Append a new blank record to a table (in xBase, it is "append blank")
> Move to the next record in a table
> In the DBF world, some tasks require filtering a file (say all records
> belonging to invoice <n>) and then processing each record in a loop. This
> involved moving a record pointer with either a "goto" or "skip" command.
> I suspect the SQL equivalent is to use the "SELECT" statement to filter
> the records and then to use the "UPDATE" or "DELETE" commands to edit the
> table. Do I have the right idea?
> Thanks.
> Richard
|||Hi Jim,
I'm not taking it personally, I appreciate you comments
I had a feeling that I'd have to use the "select" command to filter the
record(s) that I want to work with and then to think in terms of 'a set
of records'.
I have already defined primary keys for my imported DBF files. I know
how to 'update' information in existing records, but I was browsing the
'SQL Book Online' and couldn't find any references on how to combine
records from multiple tables. I know about the 'join' command, but my
request is a bit different. Say I had a existing table with 1000
records and I had the user input information into a similar table with
15 new records (exact same layout) and I wanted to merge the two tables
into one table with 1015 records, how would I do this?
Thanks for the recommendation, I'll check it out. Is it a general book
or one specific to MS SQL?
Richard
Jim Young wrote:
> Hi Richard,
> Don't take this personally, but from the questions you are asking, you have
> a bit of a learning curve ahead of you. First you need to stop looking at
> SQL data as a list of sequential records. SQL data is retrieved and
> manipulated as sets of data. There really is no equivalent to "move to last
> record" or first record. If you need to update or select a particular record
> then you need to define your records with either a primary key or some other
> unique contraint on the data and provide the SQL statement that will extract
> a distinct record. I suggest that you find a good general book on SQL and
> study up on relational database theory. As a recommendation "Data &
> Databases: Concepts In Practice" by Joe Celko is a very good book that lays
> down the basics of relational database design.
> Jim
|||I do not question why you have the extra table with exactly the same layout
(columns, I guess). It is very simple the do what you want. Assume, tableA
contains the 15 (or any number of) rows of user inputs and you want to add
all of them of some of them into tableB. You could use this SQL statement:
INERT INTO tableB (Col1,Col2,Col3...)
SELECT Filed1,Field2,Field3...
FROM tableA
WHERE ... /*here the WHERE clause allows you to choose what rows in tableA
being transfered to tableB.
You also can see from above SQL statement, tableA does not have to be the
same structure as tableB. You only need to make sure the fields selected in
SELECT...clause match the fields (field count and data type) those in INSERT
INTO clause.
Since you just moved to SQL Server/MSDE, I'd sit down for a couple of days
to stduy/investigete T-SQL, rather than browse SQL Book on-line for
particular processing.
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:e6Ad#LQ$EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> I'm not taking it personally, I appreciate you comments
> I had a feeling that I'd have to use the "select" command to filter the
> record(s) that I want to work with and then to think in terms of 'a set
> of records'.
> I have already defined primary keys for my imported DBF files. I know
> how to 'update' information in existing records, but I was browsing the
> 'SQL Book Online' and couldn't find any references on how to combine
> records from multiple tables. I know about the 'join' command, but my
> request is a bit different. Say I had a existing table with 1000
> records and I had the user input information into a similar table with
> 15 new records (exact same layout) and I wanted to merge the two tables
> into one table with 1015 records, how would I do this?
> Thanks for the recommendation, I'll check it out. Is it a general book
> or one specific to MS SQL?
> Richard
>
>
> Jim Young wrote:
have[vbcol=seagreen]
at[vbcol=seagreen]
last[vbcol=seagreen]
record[vbcol=seagreen]
other[vbcol=seagreen]
extract[vbcol=seagreen]
and[vbcol=seagreen]
lays[vbcol=seagreen]
|||The Celko book is about general relational database theory (very little
SQL). A good book about T-SQL, the SQL variant that SQL Server uses, is "The
Guru's Guide to Transact-SQL" by Ken Henderson. Also Microsoft Press's
"Inside SQL Server 2000" is an essential book for anyone that is in the
business of working with SQL Server.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:e6Ad%23LQ$EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> I'm not taking it personally, I appreciate you comments
> I had a feeling that I'd have to use the "select" command to filter the
> record(s) that I want to work with and then to think in terms of 'a set of
> records'.
> I have already defined primary keys for my imported DBF files. I know how
> to 'update' information in existing records, but I was browsing the 'SQL
> Book Online' and couldn't find any references on how to combine records
> from multiple tables. I know about the 'join' command, but my request is
> a bit different. Say I had a existing table with 1000 records and I had
> the user input information into a similar table with 15 new records (exact
> same layout) and I wanted to merge the two tables into one table with 1015
> records, how would I do this?
> Thanks for the recommendation, I'll check it out. Is it a general book or
> one specific to MS SQL?
> Richard
>
>
> Jim Young wrote:
|||Hi Norman,
That's exact what I was looking for, thanks!
I moved over all the DBF files into SQL. That extra table was the
template user entered data into before 'posting' the transaction.
I think I'll take yours (any others) advice and read a T-SQL specific
book. The book on-line I see are a great reference AFTER one becomes
more familiar with T-SQL.
I was intending to spend at least a week or two experimenting with
various database operations using small scale example, but I think I'll
order the book first
Thanks again.
Richard
Norman Yuan wrote:
> I do not question why you have the extra table with exactly the same layout
> (columns, I guess). It is very simple the do what you want. Assume, tableA
> contains the 15 (or any number of) rows of user inputs and you want to add
> all of them of some of them into tableB. You could use this SQL statement:
> INERT INTO tableB (Col1,Col2,Col3...)
> SELECT Filed1,Field2,Field3...
> FROM tableA
> WHERE ... /*here the WHERE clause allows you to choose what rows in tableA
> being transfered to tableB.
> You also can see from above SQL statement, tableA does not have to be the
> same structure as tableB. You only need to make sure the fields selected in
> SELECT...clause match the fields (field count and data type) those in INSERT
> INTO clause.
> Since you just moved to SQL Server/MSDE, I'd sit down for a couple of days
> to stduy/investigete T-SQL, rather than browse SQL Book on-line for
> particular processing.
>
|||Hi Jim,
Thanks for the recommendations. Those two sound more like what I am
looking for. I've been using relational databases (dBase, Clipper,
Paradox, FoxPro, etc) for years, but I agree that I should get one of
the T-SQL books.
My clients are all small businesses and use SBS 2000. In otherwords, I
don't want to spend time learning about larger enterprise systems, just
small, single server systems with 5-15 users.
I went to some seminars and they gave out SQL books but they were for
large organizations (multiple servers, load balancing, forest, trees,
advance security. etc).
Are either of these books geared for the small business? Is there one
you'd think suites my needs better?
Richard
p.s. Is the MS Press author is "Delaney"? I'd like to double check.
Jim Young wrote:

> The Celko book is about general relational database theory (very little
> SQL). A good book about T-SQL, the SQL variant that SQL Server uses, is "The
> Guru's Guide to Transact-SQL" by Ken Henderson. Also Microsoft Press's
> "Inside SQL Server 2000" is an essential book for anyone that is in the
> business of working with SQL Server.
> Jim
>
|||None of these books are written to address a specific implementation. They
will serve you well, no matter what your deployment size is. There have been
a couple of books written to address MSDE specifically. One that I have is
"MSDE Bible" by IDG Books. But, MSDE is so much like SQL Server that any
book that is useful for SQL Server will do for MSDE also. Most, if not all,
of the MSDE specific information can be found in the Books Online.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:%23VBz2KZ$EHA.1260@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> Thanks for the recommendations. Those two sound more like what I am
> looking for. I've been using relational databases (dBase, Clipper,
> Paradox, FoxPro, etc) for years, but I agree that I should get one of the
> T-SQL books.
> My clients are all small businesses and use SBS 2000. In otherwords, I
> don't want to spend time learning about larger enterprise systems, just
> small, single server systems with 5-15 users.
> I went to some seminars and they gave out SQL books but they were for
> large organizations (multiple servers, load balancing, forest, trees,
> advance security. etc).
> Are either of these books geared for the small business? Is there one
> you'd think suites my needs better?
> Richard
> p.s. Is the MS Press author is "Delaney"? I'd like to double check.
> Jim Young wrote:

how to do automatic backup for transaction log and truncate it?

Hi,
For example, i have set my log files to 2G maximum, if
reach 1.5G, i want the sql server to help me truncate the
log automatic. Can anyone help me?
Thanks a lot
regards,
florence
You could add a second step to your transaction log backup job that runs once
the backup step runs successfully:
USE Database_Name
GO
DBCC SHRINKFILE (Log_File_Name, 0)
GO
"florencelee" wrote:

> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence
>
|||hi..
create a Backup device for temp usage.. ( example name .. logresizebackup
Create Proc master.dbo._resizemydb
as
backup log ecatalog to logresizebackup
create a job that runs master.dbo._resizemydb dont shedule
Create an Alert ..
type must be : SQL Server Performance Condition alert
select SQL server databases as Object
select Data file(s) size as counter
select DB name instance as
and alert if Counter must be rises above and give the value of 15000000
then
in to the response tab.. Select execute Job and Select our Job that ll
execute master.dbo._resizemydb
thats it
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence
|||If you only want to truncate the log and not retain the information that
will allow you to do point in time restore you might want to think about
putting the recovery mode to SIMPLE. Then it will truncate the log when it
reaches 70% full which is pretty close to what you are after.
Andrew J. Kelly SQL MVP
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence

how to do automatic backup for transaction log and truncate it?

Hi,
For example, i have set my log files to 2G maximum, if
reach 1.5G, i want the sql server to help me truncate the
log automatic. Can anyone help me?
Thanks a lot
regards,
florenceYou could add a second step to your transaction log backup job that runs once
the backup step runs successfully:
USE Database_Name
GO
DBCC SHRINKFILE (Log_File_Name, 0)
GO
"florencelee" wrote:
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence
>|||hi..
create a Backup device for temp usage.. ( example name .. logresizebackup
Create Proc master.dbo._resizemydb
as
backup log ecatalog to logresizebackup
create a job that runs master.dbo._resizemydb dont shedule
Create an Alert ..
type must be : SQL Server Performance Condition alert
select SQL server databases as Object
select Data file(s) size as counter
select DB name instance as
and alert if Counter must be rises above and give the value of 15000000
then
in to the response tab.. Select execute Job and Select our Job that ll
execute master.dbo._resizemydb
thats it
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence|||If you only want to truncate the log and not retain the information that
will allow you to do point in time restore you might want to think about
putting the recovery mode to SIMPLE. Then it will truncate the log when it
reaches 70% full which is pretty close to what you are after.
--
Andrew J. Kelly SQL MVP
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence