how to dump or append data to a text file

i am doing some insert or update on my db, and i want to dump the errors, if any, to a text file.

If the text file deosnt exist, then i want to create it. Then append data to it, in smthg like:

insert into mytable values (f1,f2,f3)

IF @.@.ERROR <> 0

begin

-- check if text file exist

-- if no create it and write 'an error occured etc...'

-- else append to it another line 'an error occured etc...

end

thanks for helping!!

Hi Terry,

If you're using 2000, then there really is no way to achieve what you are asking except to shell the command out via xp_cmdshell - which will do everything you asked with a little "creativity".

If using 2005, then your best bet would be to wrap the required functionality in a CLR stored proc. Again, BOL has some great info on this.

Cheers,
Rob

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 dump database to MSDE

Hi

In my app. i'm using sql server 2000 now I want to move to MSDE2000. I have two Queres. my application environment is Vb.net

1.how to dump my sqlserver database which i was already using to theMSDE and

2.can i connect this MSDE to MSSqlServer2000

hi,

GRK wrote:

Hi

In my app. i'm using sql server 2000 now I want to move to MSDE2000. I have two Queres. my application environment is Vb.net

1.how to dump my sqlserver database which i was already using to theMSDE and

you can "move" the database from the SQL Server 2000 to the MSDE instance in 2 easy ways..

1. you can backup (full backup) the database on the original machine, move the backup to the MSDE machine, restore the database onto the MSDE instance..

2. detach the database from the SQL Server 2000 instance, copy all database files to the MSDE machine, attach the database to the MSDE instance (and obviously reattach the original database to the original SQL Server 2000 instance)..

you obtain the very same result, and you have to deal with the very same issues.. eventual "Orphaned Users"..

2.can i connect this MSDE to MSSqlServer2000

the question is not clear to me.. if you mean to "access MSDE from the SQL Server 2000" instance as a linked server (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp as well), then yes, this is possible... a "commercial" issue is present as this connection has to be properly licensed...

regards

|||

Hi Andrea ,

Thank you verymuch.

-GRK

How to dump a database from mysql to Sql Server2005

Hi Every bdy,

I have a database which is in mysql. Now we want to port that database to Sql Server 2005. Please tell the steps to do this.

With Regards

Ravinder Reddy.K

Two options. One, use a DataReader Source to use the MySQL ODBC driver to connect to your database and the do whatever you wish to do with the data from there.

Two, you can use mysqldump to export the database to a text file, and from there you might be able to simply run the resulting SQL, correcting a few datatypes along the way to match SQL Server's.

|||need more explanation about using a datareader|||Create an ODBC connection to your MySQL database. In your data flow, drag a DataReader Source component onto the background. Double click on it and go from there. You'll need to pick the ODBC driver, and then specify the name of the MySQL connection (DSN) you created earlier.|||Sorry for dragging. You mentioned that "In your data flow" what is it. please give more explanation|||

Do you have any experience in SQL Server Integration Services?

Tutorials: http://msdn2.microsoft.com/en-us/library/ms167031.aspx

|||

Hold on....

How many records are in your MYSQL database? The answer will determine what method you use to import the data.

Small database - (<20 million rows). Create ODBC connection to MySql and use the DataReader to read data. Load directly into a SQL Server destination.

Large database (>20 million rows). My recommendation is to do #1 above, but output to a raw file format. Create a second DataFlow that reads from the raw file and loads to a SQL Server destination. This adds a safety valve against connectivity disruptions.

Do you need any more help?