Robust audit tools

With HIPAA security becoming law on April 20th, I've got some customers that
want to know about products that will log events on the server...
Does anyone have any knowledge or experience with tools like this.
For example, tracking...
1) Every call to a SPROC and what parameters were used
2) Every SQL statement executed against the database - from any client tool
3) Change events - original data, new data, who did it - stuff like that...
Thanks in advance.
Lumigent Entegra
http://www.lumigent.com/
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:C99ACADF-DD67-48A8-B3A7-FED13F1C99CE@.microsoft.com...
> With HIPAA security becoming law on April 20th, I've got some customers
> that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client
> tool
> 3) Change events - original data, new data, who did it - stuff like
> that...
> Thanks in advance.
|||sql profiler will do a nice job with #1 and #2
Greg Jackson
PDX, Oregon
|||You have to set up a pretty squeaky-clean trace to justify running profiler
24/7 in a production environment.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> sql profiler will do a nice job with #1 and #2
|||yes...very good point
gaj
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23U$iLYEQFHA.3988@.tk2msftngp13.phx.gbl...
> You have to set up a pretty squeaky-clean trace to justify running
> profiler 24/7 in a production environment.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
|||what about changes to the source code and having a complete history/audit
trail of those...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Z" wrote:

> With HIPAA security becoming law on April 20th, I've got some customers that
> want to know about products that will log events on the server...
> Does anyone have any knowledge or experience with tools like this.
> For example, tracking...
> 1) Every call to a SPROC and what parameters were used
> 2) Every SQL statement executed against the database - from any client tool
> 3) Change events - original data, new data, who did it - stuff like that...
> Thanks in advance.

Rights problem after replication

Hello,
I've got a snapshot replication problem. My master database has some
tables which are replicated on 2 read-only databases. In every
database, I have the same roles, but not the same users. When I
replicate the database, every rights on my replicated tables are
erased ! What's the problem ?
Thanks for your help !!
Are you using SQL Server 2000? In this case you'll need a pre-snapshot script
to create the user if he owns any tables. If not the user and the permissions
can be in a post-snapshot script. For SQL 2005 there is a new option on
sp_addarticle (0x40000000) to add the permissions.
HTH,
Paul Ibison
|||On 31 mai, 11:23, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> Are you using SQL Server 2000? In this case you'll need a pre-snapshot script
> to create the user if he owns any tables. If not the user and the permissions
> can be in a post-snapshot script. For SQL 2005 there is a new option on
> sp_addarticle (0x40000000) to add the permissions.
> HTH,
> Paul Ibison
Hello and thank you for your quick answer !
Yes, I'm using SQL Server 2000. How can I create this pre-snapshot ?
I've already a sql file that contain the grant's list.
TIA, pem
|||On the snapshot tab in the publication properties there is an option to
browse for a TSQL file.
Cheers,
Paul Ibison

right way to perfromance tune?

I captured a trace from a production box. I've mad some changes with
Indexes, Stats, etc. so now I want to replay. Im mostly interested in
lowering Duration and CPU at this point. If I replay this trace on a box
thats not as powerful as Production, is this even an accurate assessment? Is
there a better way?
SQL2K SP3
TIA, ChrisR
On Mon, 15 Nov 2004 15:07:08 -0800, "ChrisR" <bla@.noemail.com> wrote:
>I captured a trace from a production box. I've mad some changes with
>Indexes, Stats, etc. so now I want to replay. Im mostly interested in
>lowering Duration and CPU at this point. If I replay this trace on a box
>thats not as powerful as Production, is this even an accurate assessment? Is
>there a better way?
Even on a very different box, AS LONG AS THE DATA IS THE SAME, eg,
full production size, then at least the logical reads should be
comparable. That's so the statistics and execution plans are the
same, not to mention getting the same results!
CPU and duration will not be comparable.
Joshua Stern
|||On Mon, 15 Nov 2004 18:58:23 -0800, jxstern wrote:

>On Mon, 15 Nov 2004 15:07:08 -0800, "ChrisR" <bla@.noemail.com> wrote:
>Even on a very different box, AS LONG AS THE DATA IS THE SAME, eg,
>full production size, then at least the logical reads should be
>comparable. That's so the statistics and execution plans are the
>same, not to mention getting the same results!
>CPU and duration will not be comparable.
>Joshua Stern
Hi Joshua,
I don't think that this is correct. Completely different execution plans
can be chosen if there are more or less processors available. AFAIK, the
amount of available memory will also influence the choice.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
<hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
>I don't think that this is correct. Completely different execution plans
>can be chosen if there are more or less processors available. AFAIK, the
>amount of available memory will also influence the choice.
I didn't know that, and would like to hear more, but even so I'd bet
you could depend on logical read counts to give you a pretty good
indication of whether your database structure and queries are lined up
well, plus or minus, oh, maybe 20%.
I wonder if a parallel algorithm might do *more* logical reads, hmm.
J.
|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:pc5kp05cqkv13jj688b3pnjjvvid6vg9vk@.4ax.com...
> On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
> <hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
> I didn't know that, and would like to hear more, but even so I'd bet
> you could depend on logical read counts to give you a pretty good
> indication of whether your database structure and queries are lined up
> well, plus or minus, oh, maybe 20%.
>
I doubt that. Imagine your prod server has 2 gig of RAM and your test
server has 512K. The amount of memory available for caching will be
radically different. In that case your logical reads may be far lower and
your physical reads would be far higher.

> I wonder if a parallel algorithm might do *more* logical reads, hmm.
> J.
>
|||On Tue, 16 Nov 2004 07:03:31 -0800, JXStern wrote:

>On Tue, 16 Nov 2004 10:27:48 +0100, Hugo Kornelis
><hugo@.pe_NO_rFact.in_SPAM_fo> wrote:
>I didn't know that, and would like to hear more, but even so I'd bet
>you could depend on logical read counts to give you a pretty good
>indication of whether your database structure and queries are lined up
>well, plus or minus, oh, maybe 20%.
Hi J,
I wouldn't rely on it. I don't know the details (where's Kalen when you
need her? <g>), but I can easily imagine that the optimizer would choose
an execution plan using hash joins and sorting on a high-memory machine,
but prefer looping on a low-memory machine. Operations like sorting and
hashing tend to be relatively cheap if it's all in memory, but very
expensive if the amount of data is more than fits in memory.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Tue, 16 Nov 2004 15:21:33 GMT, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.com> wrote:
>I doubt that. Imagine your prod server has 2 gig of RAM and your test
>server has 512K. The amount of memory available for caching will be
>radically different. In that case your logical reads may be far lower and
>your physical reads would be far higher.
For a given execution plan (and server and data, etc), aren't the
logical reads constant no matter what the physical reads?
J.
|||jxstern wrote:
> On Tue, 16 Nov 2004 15:21:33 GMT, "Greg D. Moore \(Strider\)"
> <mooregr_deleteth1s@.greenms.com> wrote:
> For a given execution plan (and server and data, etc), aren't the
> logical reads constant no matter what the physical reads?
> J.
If the same query plan is used (you can't compare the count of a hash
with a loop join), and the databases have the same free space (caused by
updates and deletes), the same index depth and the same number of pages,
then the logical read count should be the same. As you can see, a lot
should match...
Gert-Jan
|||On Tue, 16 Nov 2004 23:23:17 +0100, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>If the same query plan is used (you can't compare the count of a hash
>with a loop join), and the databases have the same free space (caused by
>updates and deletes), the same index depth and the same number of pages,
>then the logical read count should be the same. As you can see, a lot
>should match...
Do logical reads really know about free space?
I'm talking about logical reads from the profiler. Oh, well, yeah, I
guess it would. But EVEN SO I'm betting you could get a pretty fair
correspondence.
Most tuning I do is in search of 10x or 100x gains from tracking down
missing indexes and really bad code. I think you can see changes of
that magnitude even through the noise. If your tuning goal is a 10%
improvement, then sure, the noise will prevent any real comparison
across environments.
But in any case, if you take the bad code off production, put it in
development and get a baseline, you can measure newly developed
changes pretty well and guestimate them back up to production based on
the ratio.
J.

right backup example

Please give me a good way to backup databases
on my MS SQL 2000 Server.
I've created a job in
"Enterprise Manager"..."SQL Server Agent"->Jobs
"BACKUP DATABASE [BUFF] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\BUFF.bkp' WITH NOINIT , NOUNLOAD , NAME = N'BUFF
backup', NOSKIP , STATS = 10, NOFORMAT"
and they creates the backup.
However, I don't like that BUFF.bkp grows too much.
How can I (probably using a third-party utility):
- create backups with file names with current date, for example
BUFF-DD_MM_YYYY.bkp,
- automatically delete too old backups
- move them on a network drive after creating
(I remember that SQL Server doesn't allow to backup directly to a network
drive).
You can use the maintenance wizard to handle the full backup tasks with all
the features you asked for. BOL explains it better than I do.
You can also back up to a network share, you just have to follow a simple
procedure.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Michael Smirnov" <smbsmb@.mail.ru> wrote in message
news:urqrZOl1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Please give me a good way to backup databases
> on my MS SQL 2000 Server.
> I've created a job in
> "Enterprise Manager"..."SQL Server Agent"->Jobs
> "BACKUP DATABASE [BUFF] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\BUFF.bkp' WITH NOINIT , NOUNLOAD , NAME = N'BUFF
> backup', NOSKIP , STATS = 10, NOFORMAT"
> and they creates the backup.
> However, I don't like that BUFF.bkp grows too much.
> How can I (probably using a third-party utility):
> - create backups with file names with current date, for example
> BUFF-DD_MM_YYYY.bkp,
> - automatically delete too old backups
> - move them on a network drive after creating
> (I remember that SQL Server doesn't allow to backup directly to a
network
> drive).
>
>
|||Our product, MiniSQLBackup, does all that you require, besides giving you
the option to compress and encrypt your backups. An example of how the
backup command would look like in MiniSQLBackup:
BACKUP DATABASE [BUFF] TO DISK = '<AUTO> WITH NAME = 'BUFF backup',
COPYTO = '<remote folder 1>', COPYTO = '<remote folder 2>'
where <AUTO> represents a naming convention you can set up using date time
elements, and COPYTO are extensions to copy the backup file to other folders
upon completion of the backup. If you're interested, MiniSQLBackup can also
perform mirrored backups i.e. simultaneous backups to two or more disks.
Price is USD99/- per named server, free for the Lite edition.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free.

Rewriting a query causing lock escalation

All,
I've identified a query that is causing deadlocks in our database. The
SELECT portion of this query seems to be causing lock escalation - causing
the whole RECORDS table to lock - and causing other processes to deadlock.
I'd like to somehow rewrite this query to decrease the lock escalation. Does
anyone have any ideas as to how I can do this? This query is part of a
stored procedure that gets called via an insert trigger on a different
table. All fields are integers. 5 variables are inputs to this query -
@.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
I was thinking of breaking up the query into smaller versions ... but not
sure where to start.
I should add that we added option (maxdop 1) to force this query to only run
on one thread - it seems to reduce the deadlocks - but not eliminate them.
Thanks in advance ... SS
option (maxdop 1)
INSERT INTO RECORDS
(
P_TID
,P_SID
,T_TID
,T_SID
,E_ID
,PCOUNT
)
SELECT
p1.P_TID
,p1.P_SID
,p2.T_TID
,p2.T_SID
,@.E_ID
,p1.PCOUNT*p2.PCOUNT
FROM
(RECORDS p1 WITH (ROWLOCK)
INNER JOIN RECORDS p2 WITH (ROWLOCK)
ON p1.T_TID=@.P_TID
AND p1.T_SID=@.P_SID
AND p2.P_TID=@.T_TID
AND p2.P_SID=@.T_SID
) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
ON p3.P_TID=p1.P_TID
AND p3.P_SID=p1.P_SID
AND p3.T_TID=p2.T_TID
AND p3.T_SID=p2.T_SID
AND p3.E_ID=@.E_ID
WHERE
p1.E_ID IN (0, @.E_ID)
AND p2.E_ID IN (0, @.E_ID)
AND p3.E_ID IS NULL
option (maxdop 1)
Hi Steph
How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
them into a temporary table. Also/alternatively check the query execution
plan to see if adding index will help, you may want to try the Index tuning
wizard to see if it comes up with anything.
John
"Steph" wrote:

> All,
> I've identified a query that is causing deadlocks in our database. The
> SELECT portion of this query seems to be causing lock escalation - causing
> the whole RECORDS table to lock - and causing other processes to deadlock.
> I'd like to somehow rewrite this query to decrease the lock escalation. Does
> anyone have any ideas as to how I can do this? This query is part of a
> stored procedure that gets called via an insert trigger on a different
> table. All fields are integers. 5 variables are inputs to this query -
> @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> I was thinking of breaking up the query into smaller versions ... but not
> sure where to start.
> I should add that we added option (maxdop 1) to force this query to only run
> on one thread - it seems to reduce the deadlocks - but not eliminate them.
> Thanks in advance ... SS
>
> option (maxdop 1)
> INSERT INTO RECORDS
> (
> P_TID
> ,P_SID
> ,T_TID
> ,T_SID
> ,E_ID
> ,PCOUNT
> )
> SELECT
> p1.P_TID
> ,p1.P_SID
> ,p2.T_TID
> ,p2.T_SID
> ,@.E_ID
> ,p1.PCOUNT*p2.PCOUNT
> FROM
> (RECORDS p1 WITH (ROWLOCK)
> INNER JOIN RECORDS p2 WITH (ROWLOCK)
> ON p1.T_TID=@.P_TID
> AND p1.T_SID=@.P_SID
> AND p2.P_TID=@.T_TID
> AND p2.P_SID=@.T_SID
> ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> ON p3.P_TID=p1.P_TID
> AND p3.P_SID=p1.P_SID
> AND p3.T_TID=p2.T_TID
> AND p3.T_SID=p2.T_SID
> AND p3.E_ID=@.E_ID
> WHERE
> p1.E_ID IN (0, @.E_ID)
> AND p2.E_ID IN (0, @.E_ID)
> AND p3.E_ID IS NULL
> option (maxdop 1)
>
>
|||John - thanks for the suggestion. I did look at the execution plans - and
saw where some bottlenecks could be. The Index Tuning Wizard suggested
putting an index on a column that either has the values 0, 1, 2 - so not
sure how useful the index would be ...
I'm considering doing the select - putting the results into a table variable
then inserting the values from the table variable. Don't want to add any
temp tables now - don't want to add more locks on tempdb.
Thanks for suggestions ...
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> Hi Steph
> How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
> them into a temporary table. Also/alternatively check the query execution
> plan to see if adding index will help, you may want to try the Index
tuning[vbcol=seagreen]
> wizard to see if it comes up with anything.
> John
>
> "Steph" wrote:
causing[vbcol=seagreen]
deadlock.[vbcol=seagreen]
Does[vbcol=seagreen]
not[vbcol=seagreen]
run[vbcol=seagreen]
them.[vbcol=seagreen]
|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"Steph" wrote:

> John - thanks for the suggestion. I did look at the execution plans - and
> saw where some bottlenecks could be. The Index Tuning Wizard suggested
> putting an index on a column that either has the values 0, 1, 2 - so not
> sure how useful the index would be ...
> I'm considering doing the select - putting the results into a table variable
> then inserting the values from the table variable. Don't want to add any
> temp tables now - don't want to add more locks on tempdb.
> Thanks for suggestions ...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>
|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/default...b;en-us;328551
John
"Steph" wrote:

> John - thanks for the suggestion. I did look at the execution plans - and
> saw where some bottlenecks could be. The Index Tuning Wizard suggested
> putting an index on a column that either has the values 0, 1, 2 - so not
> sure how useful the index would be ...
> I'm considering doing the select - putting the results into a table variable
> then inserting the values from the table variable. Don't want to add any
> temp tables now - don't want to add more locks on tempdb.
> Thanks for suggestions ...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>
sql

Reverse Drill down for recursive entity

Hi,
I've a table category which is recursive on it
following is the sample
Category_IdCategory_NameParent_Category
1Entertainment0
2Food0
3Edible Oils2
4Toys1
5Ch. Free Oils.3
6Bicycles4
i know only 6 as category id, i need the lightweight query/sp which drills
up to the parent(untill the parent is 0) and select each category as row like.
6Bicycles
4Toys
1Entertainment
please help. If it is wrong newsgroup, please tell me the correct one.
Fahad Ashfaque
Fahad,
See if you can adapt this to your situation:
CREATE TABLE hierarchy (
parent VARCHAR(20)NULL,
child VARCHAR(20) NOT NULL
)
GO
INSERT hierarchy VALUES(NULL,'World')
INSERT hierarchy VALUES('World','Europe')
INSERT hierarchy VALUES('World','North America')
INSERT hierarchy VALUES('Europe','France')
INSERT hierarchy VALUES('France','Paris')
INSERT hierarchy VALUES('North America','United States')
INSERT hierarchy VALUES('North America','Canada')
INSERT hierarchy VALUES('United States','New York')
INSERT hierarchy VALUES('United States','Washington')
INSERT hierarchy VALUES('New York','New York City')
INSERT hierarchy VALUES('Washington','Redmond')
GO
CREATE FUNCTION PathUp(
@.parent VARCHAR(20)
) RETURNS @.t TABLE (
parent VARCHAR(20),
child VARCHAR(20)
) AS BEGIN
DECLARE @.child VARCHAR(20)
WHILE @.parent IS NOT NULL BEGIN
SET @.child = @.parent
SET @.parent = (
SELECT parent
FROM hierarchy
WHERE child = @.parent
)
INSERT INTO @.t VALUES (@.parent, @.child)
END
RETURN
END
GO
SELECT * from PathUp('New York City')
GO
Steve Kass
Drew University
Fahad Ashfaque wrote:

>Hi,
>I've a table category which is recursive on it
>following is the sample
>Category_IdCategory_NameParent_Category
>1Entertainment0
>2Food0
>3Edible Oils2
>4Toys1
>5Ch. Free Oils.3
>6Bicycles4
>
>i know only 6 as category id, i need the lightweight query/sp which drills
>up to the parent(untill the parent is 0) and select each category as row like.
>6Bicycles
>4Toys
>1Entertainment
>
>please help. If it is wrong newsgroup, please tell me the correct one.
>Fahad Ashfaque
>
|||Thanks Steve,
That works.
"Steve Kass" wrote:

> Fahad,
> See if you can adapt this to your situation:
> CREATE TABLE hierarchy (
> parent VARCHAR(20)NULL,
> child VARCHAR(20) NOT NULL
> )
> GO
> INSERT hierarchy VALUES(NULL,'World')
> INSERT hierarchy VALUES('World','Europe')
> INSERT hierarchy VALUES('World','North America')
> INSERT hierarchy VALUES('Europe','France')
> INSERT hierarchy VALUES('France','Paris')
> INSERT hierarchy VALUES('North America','United States')
> INSERT hierarchy VALUES('North America','Canada')
> INSERT hierarchy VALUES('United States','New York')
> INSERT hierarchy VALUES('United States','Washington')
> INSERT hierarchy VALUES('New York','New York City')
> INSERT hierarchy VALUES('Washington','Redmond')
> GO
> CREATE FUNCTION PathUp(
> @.parent VARCHAR(20)
> ) RETURNS @.t TABLE (
> parent VARCHAR(20),
> child VARCHAR(20)
> ) AS BEGIN
> DECLARE @.child VARCHAR(20)
> WHILE @.parent IS NOT NULL BEGIN
> SET @.child = @.parent
> SET @.parent = (
> SELECT parent
> FROM hierarchy
> WHERE child = @.parent
> )
> INSERT INTO @.t VALUES (@.parent, @.child)
> END
> RETURN
> END
> GO
> SELECT * from PathUp('New York City')
> GO
> Steve Kass
> Drew University
> Fahad Ashfaque wrote:
>
|||Thanks a lot - this helped me too!

Reusing connections

Every time my asp.net app needs to open a connection, it tries to establish a new connection with the mssql server. I′ve already set the max pool size property in the connection string. After that, my app raises an "time out"error saying it couldn′t obtain a connection from the pool. The problem is that I have a lot of iddle connections. With the Enterprise Manager I can see the status of the connections. They′re all the same "awaiting command". How can I reuse this connections? I know that the connection string must be the same for all connections and it is. I′ve set it in the web.config file. If I remove the max pool size property from the connection string I get a lot, I mean A LOT of connections with the sql server. Any ideas?

You should be ensuring that all connections get closed using a try/catch/finally block. Also, take a look at the following articles:

Tuning Up ADO.NET Connection Pooling With ASP.NET Applications
ASP.NET and SQL Server Performance Tips
The .NET Connection Pool Lifeguard

HTH

Returning Stored Proceduers via ADO Schema Rowsets

Hello,
I've been trying to get Stored Procedures and parameter information
via the ADO Schema Rowsets.
However, it seems that the MS SQL driver does not support this.
Can someone confirm this?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
> However, it seems that the MS SQL driver does not support this.
The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
Set conn = CreateObject("ADODB.Connection")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open connectionString
MsgBox ListProcParameters("MyDatabase", "dbo", "MyProcedure")
conn.Close
Function ListProcParameters(Database, Schema, ProcName)
Dim result, filter(3)
filter(0) = Database
filter(1) = Schema
filter(2) = ProcName
Set rs = conn.OpenSchema(26, filter)
Do While rs.EOF = False
result = result & _
"PROCEDURE_SCHEMA" & "=" & _
rs.Fields("PROCEDURE_SCHEMA") & vbCrLf & _
"PROCEDURE_NAME" & "=" & _
rs.Fields("PROCEDURE_NAME") & vbCrLf & _
"PARAMETER_NAME" & "=" & _
rs.Fields("PARAMETER_NAME") & vbCrLf & _
vbCrLf
rs.MoveNext
Loop
rs.Close
ListProcParameters = result
End Function
Hope this helps.
Dan Guzman
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:%23B6mxWZRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been trying to get Stored Procedures and parameter information
> via the ADO Schema Rowsets.
> However, it seems that the MS SQL driver does not support this.
> Can someone confirm this?
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||Hello Dan,

> The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
Can you list procedures as well?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

> Set conn = CreateObject("ADODB.Connection")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open connectionString
> MsgBox ListProcParameters("MyDatabase", "dbo", "MyProcedure")
> conn.Close
> Function ListProcParameters(Database, Schema, ProcName)
> Dim result, filter(3)
> filter(0) = Database
> filter(1) = Schema
> filter(2) = ProcName
> Set rs = conn.OpenSchema(26, filter)
> Do While rs.EOF = False
> result = result & _
> "PROCEDURE_SCHEMA" & "=" & _
> rs.Fields("PROCEDURE_SCHEMA") & vbCrLf & _
> "PROCEDURE_NAME" & "=" & _
> rs.Fields("PROCEDURE_NAME") & vbCrLf & _
> "PARAMETER_NAME" & "=" & _
> rs.Fields("PARAMETER_NAME") & vbCrLf & _
> vbCrLf
> rs.MoveNext
> Loop
> rs.Close
> ListProcParameters = result
> End Function
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:%23B6mxWZRFHA.2744@.TK2MSFTNGP10.phx.gbl...
>
|||Hello Dan,

> Can you list procedures as well?
Gave it another try -- it seems that the first filter parameter "catalog"
needs
to be set, else it will return an error.
Works for other drivers though :-)
Thanks for the reality check.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||> > > > However, it seems that the MS SQL driver does not support this.
> Gave it another try -- it seems that the first filter parameter "catalog"
> needs
> to be set, else it will return an error.
> Works for other drivers though :-)
> Thanks for the reality check.
Funny, that wasn't it ... I did a filter (4th parameter) on DB_PT_PROCEDURE
which seems to fail...
This way, I should get procedures only and no functions...
Hmmm...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||> This way, I should get procedures only and no functions...
It seems both procedures and functions return DB_PT_PROCEDURE. To
distinguish between procs and functions, you can instead use the ANSI
standard INFORMATION_SCHEMA.ROUTINES view in a SQL query.
In my experience, schema rowset behavior can be inconsistent among various
providers and provider versions. If you need to retrieve meta-data from a
variety of DBMS vendors, you might consider developing your own interface so
that you can use the best technique depending on the underlying DBMS and
version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:eBLVI7jRFHA.4028@.tk2msftngp13.phx.gbl...
> Funny, that wasn't it ... I did a filter (4th parameter) on
> DB_PT_PROCEDURE
> which seems to fail...
> This way, I should get procedures only and no functions...
> Hmmm...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
>
|||> > This way, I should get procedures only and no functions...
> It seems both procedures and functions return DB_PT_PROCEDURE. To
Apparently so.

> distinguish between procs and functions, you can instead use the ANSI
> standard INFORMATION_SCHEMA.ROUTINES view in a SQL query.
Hmm... given that this isn't as supported as it should ... :-/

> In my experience, schema rowset behavior can be inconsistent among various
> providers and provider versions. If you need to retrieve meta-data from a
> variety of DBMS vendors, you might consider developing your own interface
so
> that you can use the best technique depending on the underlying DBMS and
> version.
Yes I know ... but for this particular tool, this is a no-go area...
I'll settle for somewhat weirdish results.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com