Determine last access of a database or table

Does anybody know of a way to determine the last date/time a table has been accessed (query/update)?

I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table.

I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience.

Any info will help greatly!

Thanks!

Why not put a SQL Trace on each suspect database? That way you can soon determine the activity.

|||

I like that idea. Thanks for the suggestion!

Determine if database objects are being accessed

Is there anyway of determining if a view or function is being accessed?
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.
Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:

> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>
|||I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi
|||A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
> Adi
|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:[vbcol=seagreen]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:[vbcol=seagreen]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
|||On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@.gmail.com> wrote:

>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.
|||Hi
If you never update the views change them into functions.
John
"Mives" wrote:

> Is it possible to have a Trigger that is fired when a view is read
> selected from?
> tootsuite@.gmail.com wrote:
>

Determine if database objects are being accessed

Is there anyway of determining if a view or function is being accessed?
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:

> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>|||I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi|||A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
> Adi|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:[vbcol=seagreen]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:[vbcol=seagreen]
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:|||On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@.gmail.com> wrote:

>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.|||Hi
If you never update the views change them into functions.
John
"Mives" wrote:

> Is it possible to have a Trigger that is fired when a view is read
> selected from?
> tootsuite@.gmail.com wrote:
>sql

Determine if database objects are being accessed

Is there anyway of determining if a view or function is being accessed?
I've moved a lot of objects between schemas and to prevent any code
breaking used views and functions to 'redirect' to the new schema.
Is there any way to determine if anything is accessing these objects so
I know I'm safe to remove them? The profiler doesn't seem to be
able to do it. I'm using SQL Server 2005.Hi
AFAIK there is not any very simple method of doing this, that can be 100%
guaranteed.
If you have scripted all your stored procedures etc. then you could use a
textual search to find references to these views. As you are already using
functions then you could add auditing to them.
John
"Mives" wrote:
> Is there anyway of determining if a view or function is being accessed?
> I've moved a lot of objects between schemas and to prevent any code
> breaking used views and functions to 'redirect' to the new schema.
> Is there any way to determine if anything is accessing these objects so
> I know I'm safe to remove them? The profiler doesn't seem to be
> able to do it. I'm using SQL Server 2005.
>|||I think that those queries might do the job:
--check stored procedures
select specific_name
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%ObjectName%'
--check views
select TABLE_NAME
from INFORMATION_SCHEMA.VIEWS
where VIEW_DEFINITION like '%ObjectName%'
The queries will help only for objects in the database, but if you
have an application that uses direct SQL statements, you'll get runtime
errors. You can try and use the profiler to see if external
applications use the old names in direct statements. You can also try
to create a synonym with the old name that will point to the new name
(didn't try it myself, but I think that this should work)
Adi|||A clunky, but effective way to track if someone is using the view or
trigger is:
1.) VIEW - add a trigger on the view to write to a table everytime the
view is accessed
2.) TRIGGER - have the trigger write to a table everytime the trigger
fires, as part of the trigger
You can include timestamp and system_user data in your table
Adi wrote:
> I think that those queries might do the job:
> --check stored procedures
> select specific_name
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_DEFINITION like '%ObjectName%'
> --check views
> select TABLE_NAME
> from INFORMATION_SCHEMA.VIEWS
> where VIEW_DEFINITION like '%ObjectName%'
> The queries will help only for objects in the database, but if you
> have an application that uses direct SQL statements, you'll get runtime
> errors. You can try and use the profiler to see if external
> applications use the old names in direct statements. You can also try
> to create a synonym with the old name that will point to the new name
> (didn't try it myself, but I think that this should work)
> Adi|||oops
2.) FUNCTION - have the function write to a table as part of the
function
tootsu...@.gmail.com wrote:
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
> > I think that those queries might do the job:
> >
> > --check stored procedures
> > select specific_name
> > from INFORMATION_SCHEMA.ROUTINES
> > where ROUTINE_DEFINITION like '%ObjectName%'
> >
> > --check views
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.VIEWS
> > where VIEW_DEFINITION like '%ObjectName%'
> >
> > The queries will help only for objects in the database, but if you
> > have an application that uses direct SQL statements, you'll get runtime
> > errors. You can try and use the profiler to see if external
> > applications use the old names in direct statements. You can also try
> > to create a synonym with the old name that will point to the new name
> > (didn't try it myself, but I think that this should work)
> >
> > Adi|||Is it possible to have a Trigger that is fired when a view is read
selected from?
tootsuite@.gmail.com wrote:
> A clunky, but effective way to track if someone is using the view or
> trigger is:
> 1.) VIEW - add a trigger on the view to write to a table everytime the
> view is accessed
> 2.) TRIGGER - have the trigger write to a table everytime the trigger
> fires, as part of the trigger
> You can include timestamp and system_user data in your table
>
> Adi wrote:
> > I think that those queries might do the job:
> >
> > --check stored procedures
> > select specific_name
> > from INFORMATION_SCHEMA.ROUTINES
> > where ROUTINE_DEFINITION like '%ObjectName%'
> >
> > --check views
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.VIEWS
> > where VIEW_DEFINITION like '%ObjectName%'
> >
> > The queries will help only for objects in the database, but if you
> > have an application that uses direct SQL statements, you'll get runtime
> > errors. You can try and use the profiler to see if external
> > applications use the old names in direct statements. You can also try
> > to create a synonym with the old name that will point to the new name
> > (didn't try it myself, but I think that this should work)
> >
> > Adi|||On 23 Oct 2006 09:40:08 -0700, "Mives" <michaelives@.gmail.com> wrote:
>Is it possible to have a Trigger that is fired when a view is read
>selected from?
No.|||Hi
If you never update the views change them into functions.
John
"Mives" wrote:
> Is it possible to have a Trigger that is fired when a view is read
> selected from?
> tootsuite@.gmail.com wrote:
> > A clunky, but effective way to track if someone is using the view or
> > trigger is:
> >
> > 1.) VIEW - add a trigger on the view to write to a table everytime the
> > view is accessed
> >
> > 2.) TRIGGER - have the trigger write to a table everytime the trigger
> > fires, as part of the trigger
> >
> > You can include timestamp and system_user data in your table
> >
> >
> > Adi wrote:
> > > I think that those queries might do the job:
> > >
> > > --check stored procedures
> > > select specific_name
> > > from INFORMATION_SCHEMA.ROUTINES
> > > where ROUTINE_DEFINITION like '%ObjectName%'
> > >
> > > --check views
> > > select TABLE_NAME
> > > from INFORMATION_SCHEMA.VIEWS
> > > where VIEW_DEFINITION like '%ObjectName%'
> > >
> > > The queries will help only for objects in the database, but if you
> > > have an application that uses direct SQL statements, you'll get runtime
> > > errors. You can try and use the profiler to see if external
> > > applications use the old names in direct statements. You can also try
> > > to create a synonym with the old name that will point to the new name
> > > (didn't try it myself, but I think that this should work)
> > >
> > > Adi
>

Determine date when each user last accessed the database

I need to clean up some security issues. We have numerous users who access the database and I'm not confident which logins can be deleted. I've already done sp_who and sp_who2 and neither of them gave me exactly what I wanted (I know they didn't an employee who was terminated but certainly access the database numerous times before he left). I also looked at the sys.sysprocesses table. Is there another system table that can be tapped into for this?

Thanks!

It depends on whether you're looking for a change to the objects or simply an access to the data. For data, you need to set some things up to track who has looked at it recently - and that will only work if they used an application that reports who the user was, such as SQL Server Management Studio. If you have not set the system up to track data access or the application they used to get to the data doesn't report the accurate name of the user, then you won't be able to go back in time to find out what they did.

Thanks -

Buck Woody

|||

One option would be to create a trigger to populate a table of user logins. This might be resource intensive if there is a lot of login activity.

Or, you can turn on login auditing for successful logins and search through the Event log.

You can use a third-party database auditing tool.

detecting database access

Hi just wondering if there is an easy way to tell the last time an
application accessed a database? I have several databases and believe some
may not be in use so would be nice if there was an easy way to tell the last
time something was read or written to a table or the last time a stored
procedure was run.
Paul G
Software engineer.
Paul,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in a the proc or use a trigger.
A third-party log viewing tool would be another option...though entailed.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> Hi just wondering if there is an easy way to tell the last time an
> application accessed a database? I have several databases and believe
> some
> may not be in use so would be nice if there was an easy way to tell the
> last
> time something was read or written to a table or the last time a stored
> procedure was run.
> --
> Paul G
> Software engineer.
|||ok thanks for the information. Am not familiar with triggers but have used
stored procedures, views dts packages ect.
Paul G
Software engineer.
"Jerry Spivey" wrote:

> Paul,
> You would need to have had Profiler or some third-party auditing tool
> running to determine this. Another option would be to use some extra
> auditing code in a the proc or use a trigger.
> A third-party log viewing tool would be another option...though entailed.
> HTH
> Jerry
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
>
>
|||I have recently built and am testing a simple tracking query that reads
master..sysprocesses every minute and records into a log table any logins
that were not already recorded. Hostname and program_name fields may be of
interest to you...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information. Am not familiar with triggers but have
> used
> stored procedures, views dts packages ect.
> --
> Paul G
> Software engineer.
>
> "Jerry Spivey" wrote:
|||Hi thanks for the information. Just wondering if this tool for sale, when is
final testing complete?
Paul G
Software engineer.
"Kevin3NF" wrote:

> I have recently built and am testing a simple tracking query that reads
> master..sysprocesses every minute and records into a log table any logins
> that were not already recorded. Hostname and program_name fields may be of
> interest to you...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
>
>
|||For sale? nah...its just a hacked together query that you would need to
modify to include the columns you care about. In my case, I am tracking old
dead logins...
The table I dump to:
CREATE TABLE [dbo].[DBA_LoginCapture] (
[LCID] [int] IDENTITY (1, 1) NOT NULL ,
[DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LoginName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NTName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginTime] [datetime] NOT NULL
--add fields here as appropriate...
) ON [PRIMARY]
GO
The stored proc:
/************************************************** ***********************************
**
** Purpose: Captures all active logins for the current server
**
** History: 2005-09-27 Kevin Hill, 3NF Consulting Created
**
** Parameters: None
**
** Notes: This procedure is intended to be executed via a scheduled
job
** for later analysis to determine active/unsused logins.
**
************************************************** ***********************************/
CREATE PROCEDURE
dbo.DBA_InsertLoginCapture
AS
SET NOCOUNT ON
DECLARE @.Error INT
DECLARE @.RowCount INT
DECLARE @.Msg VARCHAR(500)
SET @.Error = 0
SET @.RowCount = 0
SET @.Msg = NULL
INSERT [DBA].[dbo].[DBA_LoginCapture](
[DBName],
[LoginName],
[NTName],
[LoginTime])
SELECT
sd.[name] AS dbname,
loginame,
nt_username,
login_time
FROM
master..sysprocesses sp
INNER JOIN master..sysdatabases sd
ON sp.dbid = sd.dbid
LEFT JOIN DBA..DBA_LoginCapture lc
ON sp.Loginame = lc.LoginName
AND sp.login_time = lc.LoginTime
WHERE
lc.loginName IS NULL
AND lc.LoginTime IS NULL
AND sd.[name] <> 'DBA'
SELECT @.Error = @.@.Error, @.RowCount = @.@.Rowcount
IF @.Error <> 0
BEGIN
EXEC dbo.DBA_SendMail
@.Subject = 'Error in DBA_InsertLoginCapture',
@.Message = ''
END
GO
The first time this runs, it will grab all rows from sysprocesses. Every
time after that it will grab whatever is not already there.
If you run it too often, it will take up more resources than you want
(possibly). Too infrequently and you may miss a login that comes and goes
between runs.
IMPORTANT: use at your own risk. If your data goes missing, it ain't my
fault ;-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A63B31F8-BCF8-47A7-9CAF-80D3285C5D63@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the information. Just wondering if this tool for sale, when
> is
> final testing complete?
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
|||ok thanks for the information. Will look at it when I get a chance. We have
a new server not in production yet so I can safely test it there.
Paul G
Software engineer.
"Kevin3NF" wrote:

> For sale? nah...its just a hacked together query that you would need to
> modify to include the columns you care about. In my case, I am tracking old
> dead logins...
> The table I dump to:
> CREATE TABLE [dbo].[DBA_LoginCapture] (
> [LCID] [int] IDENTITY (1, 1) NOT NULL ,
> [DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LoginName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [NTName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LoginTime] [datetime] NOT NULL
> --add fields here as appropriate...
> ) ON [PRIMARY]
> GO
>
> The stored proc:
> /************************************************** ***********************************
> **
> ** Purpose: Captures all active logins for the current server
> **
> ** History: 2005-09-27 Kevin Hill, 3NF Consulting Created
> **
> ** Parameters: None
> **
> ** Notes: This procedure is intended to be executed via a scheduled
> job
> ** for later analysis to determine active/unsused logins.
> **
> ************************************************** ***********************************/
> CREATE PROCEDURE
> dbo.DBA_InsertLoginCapture
> AS
> SET NOCOUNT ON
> DECLARE @.Error INT
> DECLARE @.RowCount INT
> DECLARE @.Msg VARCHAR(500)
> SET @.Error = 0
> SET @.RowCount = 0
> SET @.Msg = NULL
> INSERT [DBA].[dbo].[DBA_LoginCapture](
> [DBName],
> [LoginName],
> [NTName],
> [LoginTime])
> SELECT
> sd.[name] AS dbname,
> loginame,
> nt_username,
> login_time
> FROM
> master..sysprocesses sp
> INNER JOIN master..sysdatabases sd
> ON sp.dbid = sd.dbid
> LEFT JOIN DBA..DBA_LoginCapture lc
> ON sp.Loginame = lc.LoginName
> AND sp.login_time = lc.LoginTime
> WHERE
> lc.loginName IS NULL
> AND lc.LoginTime IS NULL
> AND sd.[name] <> 'DBA'
> SELECT @.Error = @.@.Error, @.RowCount = @.@.Rowcount
> IF @.Error <> 0
> BEGIN
> EXEC dbo.DBA_SendMail
> @.Subject = 'Error in DBA_InsertLoginCapture',
> @.Message = ''
> END
> GO
> The first time this runs, it will grab all rows from sysprocesses. Every
> time after that it will grab whatever is not already there.
> If you run it too often, it will take up more resources than you want
> (possibly). Too infrequently and you may miss a login that comes and goes
> between runs.
> IMPORTANT: use at your own risk. If your data goes missing, it ain't my
> fault ;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A63B31F8-BCF8-47A7-9CAF-80D3285C5D63@.microsoft.com...
>
>

detecting database access

Hi just wondering if there is an easy way to tell the last time an
application accessed a database? I have several databases and believe some
may not be in use so would be nice if there was an easy way to tell the last
time something was read or written to a table or the last time a stored
procedure was run.
--
Paul G
Software engineer.Paul,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in a the proc or use a trigger.
A third-party log viewing tool would be another option...though entailed.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> Hi just wondering if there is an easy way to tell the last time an
> application accessed a database? I have several databases and believe
> some
> may not be in use so would be nice if there was an easy way to tell the
> last
> time something was read or written to a table or the last time a stored
> procedure was run.
> --
> Paul G
> Software engineer.|||ok thanks for the information. Am not familiar with triggers but have used
stored procedures, views dts packages ect.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> You would need to have had Profiler or some third-party auditing tool
> running to determine this. Another option would be to use some extra
> auditing code in a the proc or use a trigger.
> A third-party log viewing tool would be another option...though entailed.
> HTH
> Jerry
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> > Hi just wondering if there is an easy way to tell the last time an
> > application accessed a database? I have several databases and believe
> > some
> > may not be in use so would be nice if there was an easy way to tell the
> > last
> > time something was read or written to a table or the last time a stored
> > procedure was run.
> > --
> > Paul G
> > Software engineer.
>
>|||I have recently built and am testing a simple tracking query that reads
master..sysprocesses every minute and records into a log table any logins
that were not already recorded. Hostname and program_name fields may be of
interest to you...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
> ok thanks for the information. Am not familiar with triggers but have
> used
> stored procedures, views dts packages ect.
> --
> Paul G
> Software engineer.
>
> "Jerry Spivey" wrote:
>> Paul,
>> You would need to have had Profiler or some third-party auditing tool
>> running to determine this. Another option would be to use some extra
>> auditing code in a the proc or use a trigger.
>> A third-party log viewing tool would be another option...though entailed.
>> HTH
>> Jerry
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
>> > Hi just wondering if there is an easy way to tell the last time an
>> > application accessed a database? I have several databases and believe
>> > some
>> > may not be in use so would be nice if there was an easy way to tell the
>> > last
>> > time something was read or written to a table or the last time a stored
>> > procedure was run.
>> > --
>> > Paul G
>> > Software engineer.
>>|||Hi thanks for the information. Just wondering if this tool for sale, when is
final testing complete?
--
Paul G
Software engineer.
"Kevin3NF" wrote:
> I have recently built and am testing a simple tracking query that reads
> master..sysprocesses every minute and records into a log table any logins
> that were not already recorded. Hostname and program_name fields may be of
> interest to you...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
> > ok thanks for the information. Am not familiar with triggers but have
> > used
> > stored procedures, views dts packages ect.
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Paul,
> >>
> >> You would need to have had Profiler or some third-party auditing tool
> >> running to determine this. Another option would be to use some extra
> >> auditing code in a the proc or use a trigger.
> >>
> >> A third-party log viewing tool would be another option...though entailed.
> >>
> >> HTH
> >>
> >> Jerry
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> >> > Hi just wondering if there is an easy way to tell the last time an
> >> > application accessed a database? I have several databases and believe
> >> > some
> >> > may not be in use so would be nice if there was an easy way to tell the
> >> > last
> >> > time something was read or written to a table or the last time a stored
> >> > procedure was run.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >>
> >>
> >>
>
>|||For sale? nah...its just a hacked together query that you would need to
modify to include the columns you care about. In my case, I am tracking old
dead logins...
The table I dump to:
CREATE TABLE [dbo].[DBA_LoginCapture] (
[LCID] [int] IDENTITY (1, 1) NOT NULL ,
[DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LoginName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NTName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginTime] [datetime] NOT NULL
--add fields here as appropriate...
) ON [PRIMARY]
GO
The stored proc:
/*************************************************************************************
**
** Purpose: Captures all active logins for the current server
**
** History: 2005-09-27 Kevin Hill, 3NF Consulting Created
**
** Parameters: None
**
** Notes: This procedure is intended to be executed via a scheduled
job
** for later analysis to determine active/unsused logins.
**
*************************************************************************************/
CREATE PROCEDURE
dbo.DBA_InsertLoginCapture
AS
SET NOCOUNT ON
DECLARE @.Error INT
DECLARE @.RowCount INT
DECLARE @.Msg VARCHAR(500)
SET @.Error = 0
SET @.RowCount = 0
SET @.Msg = NULL
INSERT [DBA].[dbo].[DBA_LoginCapture](
[DBName],
[LoginName],
[NTName],
[LoginTime])
SELECT
sd.[name] AS dbname,
loginame,
nt_username,
login_time
FROM
master..sysprocesses sp
INNER JOIN master..sysdatabases sd
ON sp.dbid = sd.dbid
LEFT JOIN DBA..DBA_LoginCapture lc
ON sp.Loginame = lc.LoginName
AND sp.login_time = lc.LoginTime
WHERE
lc.loginName IS NULL
AND lc.LoginTime IS NULL
AND sd.[name] <> 'DBA'
SELECT @.Error = @.@.Error, @.RowCount = @.@.Rowcount
IF @.Error <> 0
BEGIN
EXEC dbo.DBA_SendMail
@.Subject = 'Error in DBA_InsertLoginCapture',
@.Message = ''
END
GO
The first time this runs, it will grab all rows from sysprocesses. Every
time after that it will grab whatever is not already there.
If you run it too often, it will take up more resources than you want
(possibly). Too infrequently and you may miss a login that comes and goes
between runs.
IMPORTANT: use at your own risk. If your data goes missing, it ain't my
fault ;-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A63B31F8-BCF8-47A7-9CAF-80D3285C5D63@.microsoft.com...
> Hi thanks for the information. Just wondering if this tool for sale, when
> is
> final testing complete?
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
>> I have recently built and am testing a simple tracking query that reads
>> master..sysprocesses every minute and records into a log table any logins
>> that were not already recorded. Hostname and program_name fields may be
>> of
>> interest to you...
>> --
>> Kevin Hill
>> President
>> 3NF Consulting
>> www.3nf-inc.com/NewsGroups.htm
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
>> > ok thanks for the information. Am not familiar with triggers but have
>> > used
>> > stored procedures, views dts packages ect.
>> > --
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Paul,
>> >>
>> >> You would need to have had Profiler or some third-party auditing tool
>> >> running to determine this. Another option would be to use some extra
>> >> auditing code in a the proc or use a trigger.
>> >>
>> >> A third-party log viewing tool would be another option...though
>> >> entailed.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >>
>> >>
>> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> >> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
>> >> > Hi just wondering if there is an easy way to tell the last time an
>> >> > application accessed a database? I have several databases and
>> >> > believe
>> >> > some
>> >> > may not be in use so would be nice if there was an easy way to tell
>> >> > the
>> >> > last
>> >> > time something was read or written to a table or the last time a
>> >> > stored
>> >> > procedure was run.
>> >> > --
>> >> > Paul G
>> >> > Software engineer.
>> >>
>> >>
>> >>
>>|||ok thanks for the information. Will look at it when I get a chance. We have
a new server not in production yet so I can safely test it there.
--
Paul G
Software engineer.
"Kevin3NF" wrote:
> For sale? nah...its just a hacked together query that you would need to
> modify to include the columns you care about. In my case, I am tracking old
> dead logins...
> The table I dump to:
> CREATE TABLE [dbo].[DBA_LoginCapture] (
> [LCID] [int] IDENTITY (1, 1) NOT NULL ,
> [DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LoginName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [NTName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LoginTime] [datetime] NOT NULL
> --add fields here as appropriate...
> ) ON [PRIMARY]
> GO
>
> The stored proc:
> /*************************************************************************************
> **
> ** Purpose: Captures all active logins for the current server
> **
> ** History: 2005-09-27 Kevin Hill, 3NF Consulting Created
> **
> ** Parameters: None
> **
> ** Notes: This procedure is intended to be executed via a scheduled
> job
> ** for later analysis to determine active/unsused logins.
> **
> *************************************************************************************/
> CREATE PROCEDURE
> dbo.DBA_InsertLoginCapture
> AS
> SET NOCOUNT ON
> DECLARE @.Error INT
> DECLARE @.RowCount INT
> DECLARE @.Msg VARCHAR(500)
> SET @.Error = 0
> SET @.RowCount = 0
> SET @.Msg = NULL
> INSERT [DBA].[dbo].[DBA_LoginCapture](
> [DBName],
> [LoginName],
> [NTName],
> [LoginTime])
> SELECT
> sd.[name] AS dbname,
> loginame,
> nt_username,
> login_time
> FROM
> master..sysprocesses sp
> INNER JOIN master..sysdatabases sd
> ON sp.dbid = sd.dbid
> LEFT JOIN DBA..DBA_LoginCapture lc
> ON sp.Loginame = lc.LoginName
> AND sp.login_time = lc.LoginTime
> WHERE
> lc.loginName IS NULL
> AND lc.LoginTime IS NULL
> AND sd.[name] <> 'DBA'
> SELECT @.Error = @.@.Error, @.RowCount = @.@.Rowcount
> IF @.Error <> 0
> BEGIN
> EXEC dbo.DBA_SendMail
> @.Subject = 'Error in DBA_InsertLoginCapture',
> @.Message = ''
> END
> GO
> The first time this runs, it will grab all rows from sysprocesses. Every
> time after that it will grab whatever is not already there.
> If you run it too often, it will take up more resources than you want
> (possibly). Too infrequently and you may miss a login that comes and goes
> between runs.
> IMPORTANT: use at your own risk. If your data goes missing, it ain't my
> fault ;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A63B31F8-BCF8-47A7-9CAF-80D3285C5D63@.microsoft.com...
> > Hi thanks for the information. Just wondering if this tool for sale, when
> > is
> > final testing complete?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "Kevin3NF" wrote:
> >
> >> I have recently built and am testing a simple tracking query that reads
> >> master..sysprocesses every minute and records into a log table any logins
> >> that were not already recorded. Hostname and program_name fields may be
> >> of
> >> interest to you...
> >>
> >> --
> >> Kevin Hill
> >> President
> >> 3NF Consulting
> >>
> >> www.3nf-inc.com/NewsGroups.htm
> >>
> >>
> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
> >> > ok thanks for the information. Am not familiar with triggers but have
> >> > used
> >> > stored procedures, views dts packages ect.
> >> > --
> >> > Paul G
> >> > Software engineer.
> >> >
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Paul,
> >> >>
> >> >> You would need to have had Profiler or some third-party auditing tool
> >> >> running to determine this. Another option would be to use some extra
> >> >> auditing code in a the proc or use a trigger.
> >> >>
> >> >> A third-party log viewing tool would be another option...though
> >> >> entailed.
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >>
> >> >>
> >> >> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> >> >> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> >> >> > Hi just wondering if there is an easy way to tell the last time an
> >> >> > application accessed a database? I have several databases and
> >> >> > believe
> >> >> > some
> >> >> > may not be in use so would be nice if there was an easy way to tell
> >> >> > the
> >> >> > last
> >> >> > time something was read or written to a table or the last time a
> >> >> > stored
> >> >> > procedure was run.
> >> >> > --
> >> >> > Paul G
> >> >> > Software engineer.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

detecting database access

Hi just wondering if there is an easy way to tell the last time an
application accessed a database? I have several databases and believe some
may not be in use so would be nice if there was an easy way to tell the last
time something was read or written to a table or the last time a stored
procedure was run.
--
Paul G
Software engineer.Paul,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in a the proc or use a trigger.
A third-party log viewing tool would be another option...though entailed.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
> Hi just wondering if there is an easy way to tell the last time an
> application accessed a database? I have several databases and believe
> some
> may not be in use so would be nice if there was an easy way to tell the
> last
> time something was read or written to a table or the last time a stored
> procedure was run.
> --
> Paul G
> Software engineer.|||ok thanks for the information. Am not familiar with triggers but have used
stored procedures, views dts packages ect.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:

> Paul,
> You would need to have had Profiler or some third-party auditing tool
> running to determine this. Another option would be to use some extra
> auditing code in a the proc or use a trigger.
> A third-party log viewing tool would be another option...though entailed.
> HTH
> Jerry
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:599A3E79-65D1-41B9-9B61-8356705A523C@.microsoft.com...
>
>|||I have recently built and am testing a simple tracking query that reads
master..sysprocesses every minute and records into a log table any logins
that were not already recorded. Hostname and program_name fields may be of
interest to you...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...[vbcol=seagreen]
> ok thanks for the information. Am not familiar with triggers but have
> used
> stored procedures, views dts packages ect.
> --
> Paul G
> Software engineer.
>
> "Jerry Spivey" wrote:
>|||Hi thanks for the information. Just wondering if this tool for sale, when i
s
final testing complete?
--
Paul G
Software engineer.
"Kevin3NF" wrote:

> I have recently built and am testing a simple tracking query that reads
> master..sysprocesses every minute and records into a log table any logins
> that were not already recorded. Hostname and program_name fields may be o
f
> interest to you...
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:47D3B512-CA5B-40D0-A79B-D0EB56428FC3@.microsoft.com...
>
>|||For sale? nah...its just a hacked together query that you would need to
modify to include the columns you care about. In my case, I am tracking old
dead logins...
The table I dump to:
CREATE TABLE [dbo].[DBA_LoginCapture] (
[LCID] [int] IDENTITY (1, 1) NOT NULL ,
[DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[LoginName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[NTName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginTime] [datetime] NOT NULL
--add fields here as appropriate...
) ON [PRIMARY]
GO
The stored proc:
/ ****************************************
***********************************
**********
**
** Purpose: Captures all active logins for the current server
**
** History: 2005-09-27 Kevin Hill, 3NF Consulting Created
**
** Parameters: None
**
** Notes: This procedure is intended to be executed via a scheduled
job
** for later analysis to determine active/unsused logins.
**
****************************************
************************************
*********/
CREATE PROCEDURE
dbo.DBA_InsertLoginCapture
AS
SET NOCOUNT ON
DECLARE @.Error INT
DECLARE @.RowCount INT
DECLARE @.Msg VARCHAR(500)
SET @.Error = 0
SET @.RowCount = 0
SET @.Msg = NULL
INSERT [DBA].[dbo].[DBA_LoginCapture](
[DBName],
[LoginName],
[NTName],
[LoginTime])
SELECT
sd.[name] AS dbname,
loginame,
nt_username,
login_time
FROM
master..sysprocesses sp
INNER JOIN master..sysdatabases sd
ON sp.dbid = sd.dbid
LEFT JOIN DBA..DBA_LoginCapture lc
ON sp.Loginame = lc.LoginName
AND sp.login_time = lc.LoginTime
WHERE
lc.loginName IS NULL
AND lc.LoginTime IS NULL
AND sd.[name] <> 'DBA'
SELECT @.Error = @.@.Error, @.RowCount = @.@.Rowcount
IF @.Error <> 0
BEGIN
EXEC dbo.DBA_SendMail
@.Subject = 'Error in DBA_InsertLoginCapture',
@.Message = ''
END
GO
The first time this runs, it will grab all rows from sysprocesses. Every
time after that it will grab whatever is not already there.
If you run it too often, it will take up more resources than you want
(possibly). Too infrequently and you may miss a login that comes and goes
between runs.
IMPORTANT: use at your own risk. If your data goes missing, it ain't my
fault ;-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A63B31F8-BCF8-47A7-9CAF-80D3285C5D63@.microsoft.com...[vbcol=seagreen]
> Hi thanks for the information. Just wondering if this tool for sale, when
> is
> final testing complete?
> --
> Paul G
> Software engineer.
>
> "Kevin3NF" wrote:
>|||ok thanks for the information. Will look at it when I get a chance. We hav
e
a new server not in production yet so I can safely test it there.
--
Paul G
Software engineer.
"Kevin3NF" wrote:

> For sale? nah...its just a hacked together query that you would need to
> modify to include the columns you care about. In my case, I am tracking o
ld
> dead logins...
> The table I dump to:
> CREATE TABLE [dbo].[DBA_LoginCapture] (
> [LCID] [int] IDENTITY (1, 1) NOT NULL ,
> [DBName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [LoginName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
> [NTName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [LoginTime] [datetime] NOT NULL
> --add fields here as appropriate...
> ) ON [PRIMARY]
> GO
>
> The stored proc:
> / ****************************************
*********************************
************
> **
> ** Purpose: Captures all active logins for the current server
> **
> ** History: 2005-09-27 Kevin Hill, 3NF Consulting Created
> **
> ** Parameters: None
> **
> ** Notes: This procedure is intended to be executed via a schedule
d
> job
> ** for later analysis to determine active/unsused logins.
> **
> ****************************************
**********************************
***********/
> CREATE PROCEDURE
> dbo.DBA_InsertLoginCapture
> AS
> SET NOCOUNT ON
> DECLARE @.Error INT
> DECLARE @.RowCount INT
> DECLARE @.Msg VARCHAR(500)
> SET @.Error = 0
> SET @.RowCount = 0
> SET @.Msg = NULL
> INSERT [DBA].[dbo].[DBA_LoginCapture](
> [DBName],
> [LoginName],
> [NTName],
> [LoginTime])
> SELECT
> sd.[name] AS dbname,
> loginame,
> nt_username,
> login_time
> FROM
> master..sysprocesses sp
> INNER JOIN master..sysdatabases sd
> ON sp.dbid = sd.dbid
> LEFT JOIN DBA..DBA_LoginCapture lc
> ON sp.Loginame = lc.LoginName
> AND sp.login_time = lc.LoginTime
> WHERE
> lc.loginName IS NULL
> AND lc.LoginTime IS NULL
> AND sd.[name] <> 'DBA'
> SELECT @.Error = @.@.Error, @.RowCount = @.@.Rowcount
> IF @.Error <> 0
> BEGIN
> EXEC dbo.DBA_SendMail
> @.Subject = 'Error in DBA_InsertLoginCapture',
> @.Message = ''
> END
> GO
> The first time this runs, it will grab all rows from sysprocesses. Every
> time after that it will grab whatever is not already there.
> If you run it too often, it will take up more resources than you want
> (possibly). Too infrequently and you may miss a login that comes and goes
> between runs.
> IMPORTANT: use at your own risk. If your data goes missing, it ain't my
> fault ;-)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A63B31F8-BCF8-47A7-9CAF-80D3285C5D63@.microsoft.com...
>
>

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
David
With ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.co m...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David
|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.co m...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
DavidWith ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David

Design for Store Procedure if return more than 1 record

Hi Experts,
I would like to seek your opinion on how to solve or handle this type
of scenario.
Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
and another Store procedure calls.
CREATE PROCEDURE p_GetCustomerEmail
@.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
My question, how can I accept data returned from store procedures that
call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
say ...
p_GetCustomerData calls p_GetCustomerEmail
If 1 record returned, no problem for me. More than 1 record, I am not
sure how to do it.
Or I should use temporary tables instead ? or maybe FUNCTIONS instead
of store procedure ?
Thanks for your advice.
Regards,
DavidWith ADO, you can use the Recordset NextRecordset method to retrieve
multiple resultsets. For example:
Set rs = command.Execute
'process first result here
Set rs = rs.NextRecordset
'process second result here
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David|||David
Yes, use a temporary table
"David" <davidku@.rocketmail.com> wrote in message
news:4458d940.0410181926.a521494@.posting.google.com...
> Hi Experts,
> I would like to seek your opinion on how to solve or handle this type
> of scenario.
> Store procedure - p_GetCustomerEmail can be accessed from ASP webpage
> and another Store procedure calls.
> CREATE PROCEDURE p_GetCustomerEmail
> @.CustNo INT, @.CustName CHAR(50), @.CustEmail CHAR(50) OUTPUT
> My question, how can I accept data returned from store procedures that
> call p_GetCustomerEmail if it returns more than 1 row of data ? Let's
> say ...
> p_GetCustomerData calls p_GetCustomerEmail
> If 1 record returned, no problem for me. More than 1 record, I am not
> sure how to do it.
> Or I should use temporary tables instead ? or maybe FUNCTIONS instead
> of store procedure ?
> Thanks for your advice.
> Regards,
> David