I'm looking for a way to ensure a value of a column in an insert or update is unique over multiple columns. For example, in this table
accounts
id varchar(32)
readkey varchar(16)
writekey varchar(16)
I want to ensure that when a row of accounts is inserted or updated that the union of all readkey values and writekey values contains no duplicates.
I know some "hard" ways to do this (like a second table of all keys, or a trigger that tests new readkey values against all readkey and writekey values, and likewise new writekey values against all readkey and writekey values, and so on). But I'm betting that savvy SQL folks know a better way. In case it matters, I'm using IBM DB2 8.1.
Thanks
Billcreate a composite key with unique attribute.
not sure if it will work in DB2 though|||Thanks but that doesn't do it. I'm not trying to ensure that no combination of readkey || writekey ever occurs twice. I need to ensure that no readkey is the same as any other readkey or writekey, and no other writekey is the same as any other readkey or writekey.
Bill|||I don't know DB2. In standard SQL you can create a constraint something like:
ALTER TABLE accounts a1
ADD CONSTRAINT c1
CHECK (NOT EXISTS (SELECT NULL FROM accounts a2 WHERE a2.readkey = a1.writekey));
That, along with UNIQUE constraints on the 2 columns, would do it.
Alternatively, perhaps a Materialized View based on:
SELECT 'R' AS mode, readkey AS key FROM accounts
UNION
SELECT 'W' AS mode, writekey AS key FROM accounts
... with a unique constraint on (key).|||Thanks for the suggestions. I couldn't make either work, but the ideas in them provided a way.
The CHECK constraint was my first idea, but I learned that CHECK constraints cannot depend on values from more than one row, so the SELECT which examines the whole table will not work.
The materialized view was a good idea but doesn't work, since materialized views do not permit UNION - the query has to be a subset query.
What I did get to work was to create a (regular) VIEW using the UNION roughly as you suggested, then create triggers for insert and update that throw an error if the key already exists in the union view. The view and two triggers is more complex than I hoped, but at this point I'm happy just to have a solution.
Thanks again for the help.|||In DB2 v8 you can use a sequence object for this purpose.
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
How to dynamically assign database name in query or store procedure?
Hello,
I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?
something like
Declare @.MyDatabaseName varchar(30)
set @.MyDatabaseName = "MyDB"
SELECT * from MyDatabaseName.dbo.MyTable
Any suggestion? Please.
Thanks in advance
declare @.cmd nvarchar (2000)
declare @.MyDatabaseName nvarchar(30)
select @.MyDatabaseName = 'northwind'
select @.cmd = 'SELECT * from '+ @.MyDatabaseName +'.dbo.employees'
exec (@.cmd)
It is much easier and cleaner if you simply deploy your stored procedure in each database. Using dynamic SQL has lot of security implications and you don't really simplify your code.
For a future version of SQL Server, we are looking at features that will enable you to parameterize identifiers without using dynamic SQL. And also the ability to deploy SPs in one module & resolve objects in the execution context database.
|||Thanks Uma and Joeydj
I can't use dynamic query like Joeydj, simply because my stps are huge; but I am curious about Uma's statement about the security implication in Dynamic query. Could you elaborate a little Uma? thanks
|||
For one, you need to grant more permissions to users on your SPs if you use dynamic SQL. So you increasing the attack surface area of your database by using dynamic SQL. Additionally, if you don't protect against malicious inputs then you are vulnerable to SQL injection attacks which can compromise the database, entire server or network. Lastly, there is also the performance and maintainence aspect of dynamic SQL depending on the usage. So there are many risks in using dynamic SQL. However, for some problems in SQL Server there is no way other than using dynamic SQL (like parameterizing DDL statements, running DDLs against multiple dbs etc). But you can do these carefully by protecting your code against SQL injection attacks, using QUOTENAME for values that can be used as identifiers etc.
Below is a good link on the issues of using dynamic SQL in SQL Server. Also, search the WWW for "SQL Injection" and you will find lot of articles.
http://www.sommarskog.se/dynamic_sql.html
|||Thank you Uma for your explaination and thanks for the link about dynamic sql. It's a great article!
appreciated.
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?
>
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?
>
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/
>
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/
>
Friday, February 24, 2012
How to do multiple rows insert?
I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;
I need to do it using System.Data.SqlServerCe namespace;
Already tryed 2 methods.
1)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.
2)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText
= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
Using this method i found out bug (or so i think).
I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.
Any help would be appreciated. Thnx in advance.
P.S.
Sorry for bad english.
Hi,
why open, execute and close?
Why not open at the beginning, do all your executes, and only close when you exit the program?
That should be much quicker
Pete
|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.
If I could cut data incertion to ~2 mins than it would be OK.|||
Try using parameterized queries for bulk inserts.
// Arranging Data in an Array.
const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;
// Do the inserts using Parameterized queries.
Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";
for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}
Connection.Close();
|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert
Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer
cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn
Try
cn.Open()
cmd.CommandText = "INSERT INTO TestTable (col1, col2) VALUES (@.col1, @.col2)"
cmd.Parameters.Add("@.col1", SqlDbType.Int)
cmd.Parameters.Add("@.col2", SqlDbType.NVarChar, 100)
cmd.Prepare()
For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount
Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
|||Since version 3.0, Microsoft provided the SqlCeResultSet class that allows for direct table insertions - bypassing the SQL query processor altogether. And this means *very fast* insertions.|||Here is the code using a recordset. In back to back tests with the 'INSERT' method, there was no significant difference for 10,000 records - both very fast. I guess at the end of the day it is down to personal preference.
Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer
cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn
Try
cn.Open()
cmd.CommandText = "SELECT * FROM TestTable"
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
Debug.Print(Date.Now)
For loopCount = 1 To 10000
rec.SetInt32(0, loopCount)
rec.SetString(1, "Sample text")
rs.Insert(rec)
Next loopCount
Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
Debug.Print(Date.Now)
End Sub|||Thank u very much.
Using SqlCeResultSet time of bulk inserting droped from ~4min. to 35s. (240s -> 35s)
That is wonderfull.
My solution in the end.
connection.Open();
while ((buffer = reader.ReadLine()) != null) {
if (buffer.Length == 0) { continue; } //skip empty lines
strArray = buffer.Trim().Split(separator);
if (strArray[0] == "HEADER:") {
// remove any previous data
command = connection.CreateCommand();
command.CommandText = "DELETE " + strArray[1];
command.ExecuteNonQuery();
strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + strArray[2]);
for (int i = 4;i < strArray.Length;i += 2) {
strBuilder.Append(", " + strArray[ i ]);
}
strBuilder.Append(" FROM " + strArray[1]);
command.CommandText = strBuilder.ToString();
resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable);
}
else {
//tables data rows
resultRecord = resultSet.CreateRecord();
resultRecord.SetValues(strArray);
resultSet.Insert(resultRecord);
}
}
connection.Close();
Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.
By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)
And in data file values are writen:
someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....
But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.
P.S.
Again thank you very much.
|||I was surpised that it took 35 seconds - I believe that if you moved " resultRecord = resultSet.CreateRecord();" into the "HEADER" block it would run faster still as you only need to create the record once rather than 10,000 times.
|||It is not often that I find a straightforward no-nonsense sample that I do not need to battle to put to use... nice job! I wish 90% of the info in cyberspace was like that(instead of 10%)
kudos to Mohit Khullar
How to do multiple rows insert?
I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;
I need to do it using System.Data.SqlServerCe namespace;
Already tryed 2 methods.
1)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.
2)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText
= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";
if (Connection.State != System.Data.ConnectionState.Closed) {
Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();
Using this method i found out bug (or so i think).
I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.
Any help would be appreciated. Thnx in advance.
P.S.
Sorry for bad english.
Hi,
why open, execute and close?
Why not open at the beginning, do all your executes, and only close when you exit the program?
That should be much quicker
Pete
|||U r absolutly right. Not opening and closing connection every time reduced incertion time from 10min. to ~4min.But even if opening and closing connection only once data incertion last for ~4 mins. and that is unappropriate.
If I could cut data incertion to ~2 mins than it would be OK.|||
Try using parameterized queries for bulk inserts.
// Arranging Data in an Array.
const int no_of_values = 2;
int[] val1 = new int[no_of_values];
int[] val2 = new int[no_of_values];
val1[0] = val1;
val2[0] = val2;
val1[1] = val11;
val2[1] = val22;
// Do the inserts using Parameterized queries.
Connection.Open();
SqlCeCommand command = Connection.CreateCommand();
command.CommandText = "Insert INTO [Table] (col1, col2) Values (@.val1, @.val2)";
for (int i = 0; i < no_of_values; i++)
{
command.Parameters.Clear();
command.Parameters.Add("@.val1", val1[ i ]);
command.Parameters.Add("@.val2", val2[ i ]);
command.ExecuteNonQuery();
}
Connection.Close();
|||Here is a piece of VB code that does what you want - takes 2 seconds on a slowish PC. This uses a prepared parameterised INSERT statement, with the values of the parameters changed for each insert
Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer
cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn
Try
cn.Open()
cmd.CommandText = "INSERT INTO TestTable (col1, col2) VALUES (@.col1, @.col2)"
cmd.Parameters.Add("@.col1", SqlDbType.Int)
cmd.Parameters.Add("@.col2", SqlDbType.NVarChar, 100)
cmd.Prepare()
For loopCount = 1 To 10000
cmd.Parameters("@.col1").Value = loopCount
cmd.Parameters("@.col2").Value = "abc"
cmd.ExecuteNonQuery()
Next loopCount
Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
|||Since version 3.0, Microsoft provided the SqlCeResultSet class that allows for direct table insertions - bypassing the SQL query processor altogether. And this means *very fast* insertions.|||Here is the code using a recordset. In back to back tests with the 'INSERT' method, there was no significant difference for 10,000 records - both very fast. I guess at the end of the day it is down to personal preference.
Dim cn As SqlCeConnection
Dim cmd As New SqlCeCommand
Dim loopCount As Integer
cn = New SqlCeConnection()
cn.ConnectionString = "Data Source = |DataDirectory|\test.sdf"
cmd.Connection = cn
Try
cn.Open()
cmd.CommandText = "SELECT * FROM TestTable"
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
Debug.Print(Date.Now)
For loopCount = 1 To 10000
rec.SetInt32(0, loopCount)
rec.SetString(1, "Sample text")
rs.Insert(rec)
Next loopCount
Catch ex As Exception
Debug.Print(ex.ToString)
Finally
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
Debug.Print(Date.Now)
End Sub|||Thank u very much.
Using SqlCeResultSet time of bulk inserting droped from ~4min. to 35s. (240s -> 35s)
That is wonderfull.
My solution in the end.
connection.Open();
while ((buffer = reader.ReadLine()) != null) {
if (buffer.Length == 0) { continue; } //skip empty lines
strArray = buffer.Trim().Split(separator);
if (strArray[0] == "HEADER:") {
// remove any previous data
command = connection.CreateCommand();
command.CommandText = "DELETE " + strArray[1];
command.ExecuteNonQuery();
strBuilder = new StringBuilder();
strBuilder.Append("SELECT " + strArray[2]);
for (int i = 4;i < strArray.Length;i += 2) {
strBuilder.Append(", " + strArray[ i ]);
}
strBuilder.Append(" FROM " + strArray[1]);
command.CommandText = strBuilder.ToString();
resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable);
}
else {
//tables data rows
resultRecord = resultSet.CreateRecord();
resultRecord.SetValues(strArray);
resultSet.Insert(resultRecord);
}
}
connection.Close();
Allso want to mention that i was afraid that such insert can intermix data, but it worked like a charm.
By intermix i mean:
CREATE TABLE SOME_TABLE (
someCol1 [int],
someCol2 [nvarchar](50)
)
And in data file values are writen:
someCol2Val1, SomeCol1Val1
someCol2Val2, SomeCol1Val2 ....
But if Select query is writen indicating cols names, then insert is using the same order of cols as in Select query.
P.S.
Again thank you very much.
|||I was surpised that it took 35 seconds - I believe that if you moved " resultRecord = resultSet.CreateRecord();" into the "HEADER" block it would run faster still as you only need to create the record once rather than 10,000 times.
|||It is not often that I find a straightforward no-nonsense sample that I do not need to battle to put to use... nice job! I wish 90% of the info in cyberspace was like that(instead of 10%)
kudos to Mohit Khullar
Sunday, February 19, 2012
How to do a SELECT ROW LOCK or READPAST
I have an application which use a table to figure out the next work it need to process. Application is running from multiple machines & multi-threaded also. I am using a database table, let say WorkQueue to track all works.
WorkID, WorkName, Status are the column name of the table.
Status='NEW' is a new work
Status='INPROCESS' is in process
Status='COMPLETE' is complete
I am trying to write a stored procedure, which will update single record status from NEW to INPROCESS and return WorkID. I'm trying to prevent more than one instances of the process grabbing the same record where the status = 'NEW'
Help....
I have 2 suggestions:First Suggestion:
Create a stored procedure that will return the next work item to be processed. In the case that there has been a conflict, the stored procedure will return a certain value (I've assumed this to be -2). From the code itself you will manage that if a -2 value returns, you need to re-execute the stored procedure. If a -1 value is retuned then there are no new items. Here is the procedure I wrote:
CREATE PROCEDURE GetNextWorkItem
AS
BEGIN
-- @.WorkID will contain either the next work item to be processed
-- or -1 if there is no available 'New' work items
-- or -2 if there was a conflict and it needs to be executed again
DECLARE @.WorkID AS INT
-- Get the next 'New' work item and put the ID in @.WorkID
SELECT TOP 1 @.WorkID = WorkID
FROM WorkQueue
WHERE Status = 'New'
-- If @.@.RowCount is 0 then there are no 'New' work items
-- Return -1
IF @.@.RowCount = 0
BEGIN
SELECT -1 AS WorkID
RETURN
END
-- Update the status of the work item you've selected
-- making sure that its status is still 'New'
UPDATE WorkQueue
SET Status = 'In Progress'
WHERE WorkID = @.WorkID
AND Status = 'New'
-- If @.@.RowCount is 0 then the status of this work item was changed before you could update it
-- A -2 value will be returned and the code should execute this stored procedure again
IF @.@.RowCount = 0
SELECT -2 AS WorkID
ELSE
SELECT @.WorkID AS WorkID
END
GO
Second Suggestion:
Add a new column called WorkKey (in my example its datatype is UniqueIdenitfier). You will randomly generate a value in the stored procedure and update the WorkKey field of the first 'New' work item with this value. A certain value will indicate that there are no new work items (-1 in my procedure). Here is the procedure I wrote:
CREATE PROCEDURE GetNextWorkItem
AS
BEGIN
-- @.WorkKey is a temporary variable that you will use to select the work item that you will process next
DECLARE @.WorkKey AS UniqueIdentifier
SET @.WorkKey = NewID()
-- Update the first 'New' available work item with the key you have just generated
UPDATE WorkQueue
SET WorkKey = @.WorkKey, Status = 'In Progress'
WHERE WorkID IN
(SELECT TOP 1 WorkID
FROM WorkQueue
WHERE Status = 'New')
-- If @.@.RowCount is 0 then there are no 'New' work items. Re-execute this stored procedure later
IF @.@.RowCount = 0
BEGIN
SELECT -1 AS WorkID
RETURN
END
-- Get the WorkID that you have just updated
SELECT WorkID
FROM WorkQueue
WHERE WorkKey = @.WorkKey
END
GO
Please tell me if this answers your question.
Best regards,
Sami Samir
|||
You could make use of the OUTPUT clause that's available in SQL Server 2005, see the example below. This would avoid you having to use table hints.
Chris
SET NOCOUNT ON
DECLARE @.WorkQueue TABLE (WorkID INT, WorkName VARCHAR(100), Status VARCHAR(10))
INSERT INTO @.WorkQueue VALUES (1, 'Answer a forum question.', 'NEW')
INSERT INTO @.WorkQueue VALUES (2, 'Wash the car.', 'NEW')
INSERT INTO @.WorkQueue VALUES (3, 'Have a beer.', 'LATER')
INSERT INTO @.WorkQueue VALUES (4, 'Feed the cat.', 'COMPLETE')
DECLARE @.Output TABLE (WorkID INT)
UPDATE w
SET w.Status = 'INPROCESS'
OUTPUT inserted.WorkID INTO @.Output
FROM @.WorkQueue w
WHERE w.WorkID = (SELECT TOP 1 w2.WorkID FROM @.WorkQueue w2 WHERE Status = 'NEW' ORDER BY w2.WorkID)
DECLARE @.WorkID INT
SELECT @.WorkID = WorkID FROM @.Output
SELECT *
FROM @.WorkQueue
WHERE WorkID = @.WorkID
Below is a code sample that you could put in a procedure. The sample starts a transaction a does a select to get the workid and workstatus. I used 3 lock hints readpast, rowlock, holdlock and updlock. The readpast will allow better concurrency in a multiple user system, however it is possible that a row may not be returned in all pages or rows are locked at the time the procedure is invoked. The rowlock hint makes sure that a pagelock isn't used so more users can access the data. The holdlock and updlock will lock the rows for the duration of the transaction and make sure that no other user can acquire the data. I have used this technique before for "work queue" and it works well. HTH.
DECLARE @.WorkID INT
,@.WorkName VARCHAR(20)
BEGIN TRAN
SELECT @.WorkID = WorkID, @.WorkName = WorkName
FROM WorkQueue(HOLDLOCK,UPDLOCK,READPAST,ROWLOCK)
UPDATE WorkQueue SET status = 'In Process'
WHERE
WorkID = @.WorkID;
COMMIT TRAN
|||we are planning to sql server 2000. Sorry I forget to mention it.
I see different way do it. any suggestion which one I should use
|||
I think your answer is correct one. does this Guarantee uniqueness between concurrent & multiple user system. Do anyone see a problem in using it
|||
I AM GETTING FOLLOWING ERROR WITH YOUR SQL
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels
|||I added WITH before the locking hints and it worked fine. Here is the updated query:SELECT @.WorkID = WorkID, @.WorkName = WorkName
FROM WorkQueue WITH (HOLDLOCK, UPDLOCK, READPAST, ROWLOCK)
Best regards,
Sami Samir
|||
Sorry about forgetting the with clause. Yes it will guarantee concurrency and uniqueness. The readpast technique is great for a work queue. Haven't found it to be helpful in many other situations. Since this is a work queue make sure you do not put any uncessary indexes on it and/or have connections access it via different paths (i.e. indexes). Since work queues by nature are "busy" tables you do not want to introduce deadlocking by having multiple procedures access the data down different retrieval paths. HTH.
|||
Look at the below sql and tell me what is wrong with it. it doesn't have any transaction before it. does this works
CREATE PROC dbo.GetNextWork @.WorkItem BIGINT=0 OUTPUT as
UPDATE dbo.WorkQueue
SET Status='InProcess',@.WorkItem=WorkItemID
WHERE WorkItemID = ( SELECT TOP 1 WorkItemID
FROM dbo.WorkQueue WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE Status = 'NEW' ORDER BY WorkItemID )
Variable @.WorkItem will be returned from stored procedure
|||
That would work, I created some sample code below for the forum. Learned something new today, never tried variable assignment in an update statement.
create table #workqueue (
workitemid int,
status varchar(30)
);
insert into #workqueue (workitemid,status) values (1,'new')
insert into #workqueue (workitemid,status) values (2,'new')
Declare @.WorkItem int;
UPDATE #WorkQueue
SET Status='InProcess',@.WorkItem=WorkItemID
WHERE WorkItemID = ( SELECT TOP 1 WorkItemID
FROM #WorkQueue WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE Status = 'NEW' ORDER BY WorkItemID )
select @.workitem
How to do a join in multiple datasets
I have two datasets accessed from two different data sources.
I would like to display the report by doing a join between the two. How can
I achieve that?
This join can not take place at data source level.Man that's a tough one.
I wonder if you could set the reports' data source to a custom assembly
which brings the two data sources together?
Alternatively, I suppose you could link the two datasources through another
database say Access if that's an option.
HTH
Matt
"Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
news:A729C0A5-ECC4-4337-B88F-0DF072FA2202@.microsoft.com...
> I have two datasets accessed from two different data sources.
> I would like to display the report by doing a join between the two. How
can
> I achieve that?
> This join can not take place at data source level.
>
I would like to display the report by doing a join between the two. How can
I achieve that?
This join can not take place at data source level.Man that's a tough one.
I wonder if you could set the reports' data source to a custom assembly
which brings the two data sources together?
Alternatively, I suppose you could link the two datasources through another
database say Access if that's an option.
HTH
Matt
"Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
news:A729C0A5-ECC4-4337-B88F-0DF072FA2202@.microsoft.com...
> I have two datasets accessed from two different data sources.
> I would like to display the report by doing a join between the two. How
can
> I achieve that?
> This join can not take place at data source level.
>
Subscribe to:
Posts (Atom)