I have a table of machine data that captures fault codes, time machine stopped and time machine started. I can easily calculate the downtime, but how do i take the last start time and subtract it from the next stop time (1 row from the next row) to get an up time. I need it to group by short date and fault as I intend to use Excel as a reporting tool and pulling in all data will not fit. What is the most efficient way?
Thanks,
Leei guess you could do it..... but it will be easier for us to explain if you could post some default table values and table structures...|||well u will have to make a self-join for this to happen. something like below
select top 1 from [same table] where [time machine stopped] > outer.[time machine started] and MachineCode=outer.MachineCode etc.. etc...|||Sorry for the delay. I am traveling. Here is some test data|||I tried the self join and did get a return for 1 row. However, I think that I did a poor job of stating my goal. I will try and clairfy what I am trying to accomplish. For a given date range I need to first return the difference in the DWNTIMESTAMP_VAL0 and UPTIMESTAMP_VALO for that given row (this gives me the outage time). I also need to take the DWNTIMESTAMP_VAL0 for the very next row and find the difference from the UPTIMESTAMP_VAL0 the previous row (this gives me the uptime between the two downtime events). I will need this for each row in the date range. If the math part makes this more difficult, I can get the math done a different way if I could return a RS with row 1's DWNTIMESTAMP_VAL0, UPTIMESTAMP_VAL0 and row 2's DWNTIMESTAMP_VAL0 for a new row 1, row 2's DWNTIMESTAMP_VAL0, UPTIMESTAMP_VAL0 and row 3's DWNTIMESTAMP_VAL0 for a new row 2, ... Is this at all possible to do within SQL?|||Hope following procedure will help to solve your problem:
Create Procedure sp_getNextUpTiming
As
Begin
Select * Into #TempDOWNTIMELOG From [DOWNTIMELOG]
Alter Table #TempDOWNTIMELOG Add RowId INT Identity(1, 1)
Select
DATEDIFF(SS, CONVERT(DATETIME, D1D, 101) , CONVERT(DATETIME, D1U, 101)) As DownTime,
DATEDIFF(SS, CONVERT(DATETIME, D1U, 101) , CONVERT(DATETIME, D2D, 101)) As NextUpTime
From
(
Select
D1.[DWNTIMESTAMP_VAL0] As D1D, D1.[UPTIMESTAMP_VAL0] As D1U,
D2.[DWNTIMESTAMP_VAL0] As D2D, D2.[UPTIMESTAMP_VAL0] As D2U
From #TempDOWNTIMELOG As D1
Inner Join #TempDOWNTIMELOG As D2 On D1.RowId In
(Select Max(RowId) From #TempDOWNTIMELOG Where RowId<D2.RowId)
) As tblTimings
End
Prajkta A.
------
Software Engineer
Clarion Technologies
(SEI CMMI Level 3)
Pune, India
www. clariontechnologies. co. in|||When I call the procedure, it fails with Invaild Column Name "RowId"|||I am so sorry for your inconvenience. Please try this:
Alter Procedure sp_getNextUpTiming
As
Begin
Select * Into #TempDOWNTIMELOG From [DOWNTIMELOG]
Alter Table #TempDOWNTIMELOG Add RowId INT Identity(1, 1)
DECLARE @.strTest vARCHAR(4000)
set @.strTest = 'Select
DATEDIFF(SS, CONVERT(DATETIME, D1D, 101) , CONVERT(DATETIME, D1U, 101)) As DownTime,
DATEDIFF(SS, CONVERT(DATETIME, D1U, 101) , CONVERT(DATETIME, D2D, 101)) As NextUpTime
From
(
Select
D1.[DWNTIMESTAMP_VAL0] As D1D, D1.[UPTIMESTAMP_VAL0] As D1U,
D2.[DWNTIMESTAMP_VAL0] As D2D, D2.[UPTIMESTAMP_VAL0] As D2U
From #TempDOWNTIMELOG As D1
Inner Join #TempDOWNTIMELOG As D2 On D1.RowId In
(Select Max(RowId) From #TempDOWNTIMELOG Where RowId<D2.RowId)
) As tblTimings'
EXEC(@.strTest)
End|||thanks so much!! able to return the uptime and downtime. i will now attempt to gain enough understanding so that i can do group by as there are different pieces of equipment and i need to make certain that i pull the data by line.
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Thursday, March 29, 2012
Determine the last time SP was executed
Hello -
I am trying to determine the last time a SP was executed. Does anyone
know how to do this? I'm trying to cleanup some databases.
Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You need to be running PROFILER...which would be a big drain to run it 100% in
parallel with a production server...
I suggest running it for a day or 2, during days when you know every part of an
application will get 'touched'. Then investigate the profiler output for your
target objects.|||I have a SP usage table that I use to see what SP's are being ran and
when. The table consists of an ID (autogen), sp_name, LastTimeUsed,
TimesUsed. I can keep track of what SP's are being used, when they
were last used and how many times they were used. I did this one day
as a CYA measure, and found it very useful. And the overhead for my
system is very small.
dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20031210191602.04241.00000665@.mb-m29.aol.com>...
> You need to be running PROFILER...which would be a big drain to run it 100% in
> parallel with a production server...
> I suggest running it for a day or 2, during days when you know every part of an
> application will get 'touched'. Then investigate the profiler output for your
> target objects.|||Thanks!
Thats what I thought but I was hoping there was somthing else.
Thanks Again!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!sql
Tuesday, March 27, 2012
Determine Late from Time
I have a datetime column named Timesheet.StartTime and a datetime column
also named Employee.StartTime and I want to compare the time portion of each
to see if the Timesheet.StartTime is later than the Employee.StartTime. My
problem is that I only need to compare the time portion as the date on
Employee.StartTime is constant. Can anyone give me a tip or where I can
find a solution? Thanks.
DavidCan you give some example?|||Even if you need only time portions - assuming your date portions are
the same, all you need is the difference between the two dates.
Datediff function can exactly give you the difference in hours, mins or
sec. Hope this helps.
Have a look at the following code
declare @.date1 datetime,
@.date2 datetime
set @.date1 = '2006-02-14 13:40:07.603'
set @.date2 = '2006-02-14 15:40:07.603'
select datediff(hh,@.date1,@.date2)
select datediff(mi,@.date1,@.date2)|||Try using datepart() function. This can give you the exact part of the
datetime variable you are after.|||Use datediff
declare @.d1 datetime,
@.d2 datetime
set @.d1 = '2006-02-13 11:44:07.102'
set @.d2 = '2006-02-13 12:44:07.102'
select datediff(ss,@.d1,@.d2)
result is 3600
select datediff(ss,@.d2,@.d1)
result is -3600
so if first date is less than or equal to second date then result will
be >= 0 else it will be < 0
Regards
Amish Shahsql
also named Employee.StartTime and I want to compare the time portion of each
to see if the Timesheet.StartTime is later than the Employee.StartTime. My
problem is that I only need to compare the time portion as the date on
Employee.StartTime is constant. Can anyone give me a tip or where I can
find a solution? Thanks.
DavidCan you give some example?|||Even if you need only time portions - assuming your date portions are
the same, all you need is the difference between the two dates.
Datediff function can exactly give you the difference in hours, mins or
sec. Hope this helps.
Have a look at the following code
declare @.date1 datetime,
@.date2 datetime
set @.date1 = '2006-02-14 13:40:07.603'
set @.date2 = '2006-02-14 15:40:07.603'
select datediff(hh,@.date1,@.date2)
select datediff(mi,@.date1,@.date2)|||Try using datepart() function. This can give you the exact part of the
datetime variable you are after.|||Use datediff
declare @.d1 datetime,
@.d2 datetime
set @.d1 = '2006-02-13 11:44:07.102'
set @.d2 = '2006-02-13 12:44:07.102'
select datediff(ss,@.d1,@.d2)
result is 3600
select datediff(ss,@.d2,@.d1)
result is -3600
so if first date is less than or equal to second date then result will
be >= 0 else it will be < 0
Regards
Amish Shahsql
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!
Sunday, March 25, 2012
determine db last access date?
Is there a way to tell the last time a database was accessed?
You can determine the last time a user logged into the SQL server, but I
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?
|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>
|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?
|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.co...filer_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/produc...k/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
sql
You can determine the last time a user logged into the SQL server, but I
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?
|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>
|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?
|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.co...filer_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/produc...k/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
sql
determine db last access date?
Is there a way to tell the last time a database was accessed?You can determine the last time a user logged into the SQL server, but I
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>> Is there a way to tell the last time a database was accessed?
>|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>> Is there a way to tell the last time a database was accessed?
>|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
determine db last access date?
Is there a way to tell the last time a database was accessed?You can determine the last time a user logged into the SQL server, but I
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.c...ofiler_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
don't know of any out of the box way to determine last DB access time.
"jason" <jason@.discussions.microsoft.com> wrote in message
news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
> Is there a way to tell the last time a database was accessed?|||I refer to SQL2000 - can't comment on 2005!
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uqxcWiw4GHA.3964@.TK2MSFTNGP04.phx.gbl...
> You can determine the last time a user logged into the SQL server, but I
> don't know of any out of the box way to determine last DB access time.
> "jason" <jason@.discussions.microsoft.com> wrote in message
> news:3CCD15E4-C6D0-467A-809E-222B801F9D91@.microsoft.com...
>|||If you haven't put in something to specifically track that (e.g. using SQL
trace), the answer is no.
Linchi
"jason" wrote:
> Is there a way to tell the last time a database was accessed?|||Hello Jason,
I agree with Linchi that if you did n't configure track on the database, we
cannot determine the last access time since this informaiton is not saved
automatically.
You may want to take a look at BOL for some explanations as well as
visiting at
http://www.sql-server-performance.c...ofiler_tips.asp
There is one column "databaseid" that you could add in profiler so that you
could get databaseid information of each event. You could get the name of
database from id by using:
select name from master..sysdatabases where dbid = <databaseid>
I understand it shall be convenient to store this information, and I will
forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below since they'd like to hear your
vocie:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
If you have further questions on the issue, please feel free to let's know.
Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Thursday, March 22, 2012
Determin whether port number assigned dynamically or hardcoded
I undstand that if you choose to dynamically assign a port number to an
instance this is a once only operation performed the first time that instance
is started and from then on it always attepts to use that port number.
Having just started with a new company I'm trying to work out if instance
port numbers were assigned dynamically or hardcoded during install as this
affect the client connection properties for connections (if hardcoded each
client needs to be configured with the port number rather than allowing an
instance to be resolved to a port).
Is there a regkey or anything that says how ports were assigned?
Steve
Steve Morgan
MCDBA
Snr Production DBA
If you are looking for a registry key to determine if SQL
Server is listening on the default port, it's located at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib\Tcp
That will give you the listening port.
You can also get the information from the SQL Server log.
You can also get the information using various network
commands, tools.
-Sue
On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
>I undstand that if you choose to dynamically assign a port number to an
>instance this is a once only operation performed the first time that instance
>is started and from then on it always attepts to use that port number.
>Having just started with a new company I'm trying to work out if instance
>port numbers were assigned dynamically or hardcoded during install as this
>affect the client connection properties for connections (if hardcoded each
>client needs to be configured with the port number rather than allowing an
>instance to be resolved to a port).
>Is there a regkey or anything that says how ports were assigned?
>Steve
|||Hi Sue
Thnx for the reply but thats not my question - I know how to check the port
numbers currently being used.
What I need to work out is whether during the install the option to
dynamically assign instance port numbers was choosen or if they were
pre-choosen & hardcoded.
According to a technet article on connectivity problems this decision
affects whether on the client machine you can use the <server>\<instance
name> in your connection string or whether you have to use <server>\<port
number>
I'm having intermittent connection problems usine <server>\<instance name>
and want to know if the install port decision could be the root cause.
Steve
"Sue Hoegemeier" wrote:
> If you are looking for a registry key to determine if SQL
> Server is listening on the default port, it's located at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib\Tcp
> That will give you the listening port.
> You can also get the information from the SQL Server log.
> You can also get the information using various network
> commands, tools.
> -Sue
> On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
> <SteveMorgan@.discussions.microsoft.com> wrote:
>
>
|||Check the same area in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<IYournstanceName>\MSSQLServer\SuperSocketN etLib\Tcp
The combination of the values for TCPDynamicPorts and
TCPPort will help you determine the setting. You can find
them outlined in the following article:
http://support.microsoft.com/?id=823938
-Sue
On Wed, 1 Dec 2004 02:11:02 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue
>Thnx for the reply but thats not my question - I know how to check the port
>numbers currently being used.
>What I need to work out is whether during the install the option to
>dynamically assign instance port numbers was choosen or if they were
>pre-choosen & hardcoded.
>According to a technet article on connectivity problems this decision
>affects whether on the client machine you can use the <server>\<instance
>name> in your connection string or whether you have to use <server>\<port
>number>
>I'm having intermittent connection problems usine <server>\<instance name>
>and want to know if the install port decision could be the root cause.
>Steve
>
>"Sue Hoegemeier" wrote:
instance this is a once only operation performed the first time that instance
is started and from then on it always attepts to use that port number.
Having just started with a new company I'm trying to work out if instance
port numbers were assigned dynamically or hardcoded during install as this
affect the client connection properties for connections (if hardcoded each
client needs to be configured with the port number rather than allowing an
instance to be resolved to a port).
Is there a regkey or anything that says how ports were assigned?
Steve
Steve Morgan
MCDBA
Snr Production DBA
If you are looking for a registry key to determine if SQL
Server is listening on the default port, it's located at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib\Tcp
That will give you the listening port.
You can also get the information from the SQL Server log.
You can also get the information using various network
commands, tools.
-Sue
On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
>I undstand that if you choose to dynamically assign a port number to an
>instance this is a once only operation performed the first time that instance
>is started and from then on it always attepts to use that port number.
>Having just started with a new company I'm trying to work out if instance
>port numbers were assigned dynamically or hardcoded during install as this
>affect the client connection properties for connections (if hardcoded each
>client needs to be configured with the port number rather than allowing an
>instance to be resolved to a port).
>Is there a regkey or anything that says how ports were assigned?
>Steve
|||Hi Sue
Thnx for the reply but thats not my question - I know how to check the port
numbers currently being used.
What I need to work out is whether during the install the option to
dynamically assign instance port numbers was choosen or if they were
pre-choosen & hardcoded.
According to a technet article on connectivity problems this decision
affects whether on the client machine you can use the <server>\<instance
name> in your connection string or whether you have to use <server>\<port
number>
I'm having intermittent connection problems usine <server>\<instance name>
and want to know if the install port decision could be the root cause.
Steve
"Sue Hoegemeier" wrote:
> If you are looking for a registry key to determine if SQL
> Server is listening on the default port, it's located at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib\Tcp
> That will give you the listening port.
> You can also get the information from the SQL Server log.
> You can also get the information using various network
> commands, tools.
> -Sue
> On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
> <SteveMorgan@.discussions.microsoft.com> wrote:
>
>
|||Check the same area in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\<IYournstanceName>\MSSQLServer\SuperSocketN etLib\Tcp
The combination of the values for TCPDynamicPorts and
TCPPort will help you determine the setting. You can find
them outlined in the following article:
http://support.microsoft.com/?id=823938
-Sue
On Wed, 1 Dec 2004 02:11:02 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue
>Thnx for the reply but thats not my question - I know how to check the port
>numbers currently being used.
>What I need to work out is whether during the install the option to
>dynamically assign instance port numbers was choosen or if they were
>pre-choosen & hardcoded.
>According to a technet article on connectivity problems this decision
>affects whether on the client machine you can use the <server>\<instance
>name> in your connection string or whether you have to use <server>\<port
>number>
>I'm having intermittent connection problems usine <server>\<instance name>
>and want to know if the install port decision could be the root cause.
>Steve
>
>"Sue Hoegemeier" wrote:
Determin whether port number assigned dynamically or hardcoded
I undstand that if you choose to dynamically assign a port number to an
instance this is a once only operation performed the first time that instanc
e
is started and from then on it always attepts to use that port number.
Having just started with a new company I'm trying to work out if instance
port numbers were assigned dynamically or hardcoded during install as this
affect the client connection properties for connections (if hardcoded each
client needs to be configured with the port number rather than allowing an
instance to be resolved to a port).
Is there a regkey or anything that says how ports were assigned?
Steve
Steve Morgan
MCDBA
Snr Production DBAIf you are looking for a registry key to determine if SQL
Server is listening on the default port, it's located at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketNet
Lib\Tcp
That will give you the listening port.
You can also get the information from the SQL Server log.
You can also get the information using various network
commands, tools.
-Sue
On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
>I undstand that if you choose to dynamically assign a port number to an
>instance this is a once only operation performed the first time that instan
ce
>is started and from then on it always attepts to use that port number.
>Having just started with a new company I'm trying to work out if instance
>port numbers were assigned dynamically or hardcoded during install as this
>affect the client connection properties for connections (if hardcoded each
>client needs to be configured with the port number rather than allowing an
>instance to be resolved to a port).
>Is there a regkey or anything that says how ports were assigned?
>Steve|||Hi Sue
Thnx for the reply but thats not my question - I know how to check the port
numbers currently being used.
What I need to work out is whether during the install the option to
dynamically assign instance port numbers was choosen or if they were
pre-choosen & hardcoded.
According to a technet article on connectivity problems this decision
affects whether on the client machine you can use the <server>\<instance
name> in your connection string or whether you have to use <server>\<port
number>
I'm having intermittent connection problems usine <server>\<instance name>
and want to know if the install port decision could be the root cause.
Steve
"Sue Hoegemeier" wrote:
> If you are looking for a registry key to determine if SQL
> Server is listening on the default port, it's located at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketN
etLib\Tcp
> That will give you the listening port.
> You can also get the information from the SQL Server log.
> You can also get the information using various network
> commands, tools.
> -Sue
> On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
> <SteveMorgan@.discussions.microsoft.com> wrote:
>
>|||Check the same area in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\<IYournstanceName>\MSSQLServer\SuperSocketNetLib\Tcp
The combination of the values for TCPDynamicPorts and
TCPPort will help you determine the setting. You can find
them outlined in the following article:
http://support.microsoft.com/?id=823938
-Sue
On Wed, 1 Dec 2004 02:11:02 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue
>Thnx for the reply but thats not my question - I know how to check the port
>numbers currently being used.
>What I need to work out is whether during the install the option to
>dynamically assign instance port numbers was choosen or if they were
>pre-choosen & hardcoded.
>According to a technet article on connectivity problems this decision
>affects whether on the client machine you can use the <server>\<instance
>name> in your connection string or whether you have to use <server>\<port
>number>
>I'm having intermittent connection problems usine <server>\<instance name>
>and want to know if the install port decision could be the root cause.
>Steve
>
>"Sue Hoegemeier" wrote:
>
instance this is a once only operation performed the first time that instanc
e
is started and from then on it always attepts to use that port number.
Having just started with a new company I'm trying to work out if instance
port numbers were assigned dynamically or hardcoded during install as this
affect the client connection properties for connections (if hardcoded each
client needs to be configured with the port number rather than allowing an
instance to be resolved to a port).
Is there a regkey or anything that says how ports were assigned?
Steve
Steve Morgan
MCDBA
Snr Production DBAIf you are looking for a registry key to determine if SQL
Server is listening on the default port, it's located at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketNet
Lib\Tcp
That will give you the listening port.
You can also get the information from the SQL Server log.
You can also get the information using various network
commands, tools.
-Sue
On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
>I undstand that if you choose to dynamically assign a port number to an
>instance this is a once only operation performed the first time that instan
ce
>is started and from then on it always attepts to use that port number.
>Having just started with a new company I'm trying to work out if instance
>port numbers were assigned dynamically or hardcoded during install as this
>affect the client connection properties for connections (if hardcoded each
>client needs to be configured with the port number rather than allowing an
>instance to be resolved to a port).
>Is there a regkey or anything that says how ports were assigned?
>Steve|||Hi Sue
Thnx for the reply but thats not my question - I know how to check the port
numbers currently being used.
What I need to work out is whether during the install the option to
dynamically assign instance port numbers was choosen or if they were
pre-choosen & hardcoded.
According to a technet article on connectivity problems this decision
affects whether on the client machine you can use the <server>\<instance
name> in your connection string or whether you have to use <server>\<port
number>
I'm having intermittent connection problems usine <server>\<instance name>
and want to know if the install port decision could be the root cause.
Steve
"Sue Hoegemeier" wrote:
> If you are looking for a registry key to determine if SQL
> Server is listening on the default port, it's located at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketN
etLib\Tcp
> That will give you the listening port.
> You can also get the information from the SQL Server log.
> You can also get the information using various network
> commands, tools.
> -Sue
> On Tue, 30 Nov 2004 08:55:09 -0800, Steve Morgan
> <SteveMorgan@.discussions.microsoft.com> wrote:
>
>|||Check the same area in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\<IYournstanceName>\MSSQLServer\SuperSocketNetLib\Tcp
The combination of the values for TCPDynamicPorts and
TCPPort will help you determine the setting. You can find
them outlined in the following article:
http://support.microsoft.com/?id=823938
-Sue
On Wed, 1 Dec 2004 02:11:02 -0800, Steve Morgan
<SteveMorgan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue
>Thnx for the reply but thats not my question - I know how to check the port
>numbers currently being used.
>What I need to work out is whether during the install the option to
>dynamically assign instance port numbers was choosen or if they were
>pre-choosen & hardcoded.
>According to a technet article on connectivity problems this decision
>affects whether on the client machine you can use the <server>\<instance
>name> in your connection string or whether you have to use <server>\<port
>number>
>I'm having intermittent connection problems usine <server>\<instance name>
>and want to know if the install port decision could be the root cause.
>Steve
>
>"Sue Hoegemeier" wrote:
>
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...
>
>
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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
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...
>
>
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...
>
>
Wednesday, March 21, 2012
Detecting Circular References.
Greetings All, this is my first time dealing with this particular issue
so I figured I would solicit your expertise :)
Annoted Version of DDL:
Create Table Company
(
ParentId int null,
PrimaryId int not null primary key,
CompanyName nvarchar(200) not null
)
Facts:
1.) PrimaryId is the PK of the Company.
2.) ParentId is the identifier of the Parent company if one exists.
3.) If ParentId is null then PrimaryId is its own parent.
4.) A company can have only one parent (hierarchcial graph).
5.) There is no one master company for all PrimaryId's. This is to say
you can have more than one hierarchy represented in the Company table:
A D
/ \ / \
B C E F
Where A and D are there own parents.
Problem:
I need to make sure that there can never be the case where a company is
simultaneously both the child and parent of another company, e.g.)
A is parent of B
B is parent of C
C is parent of D
D is parent of A <= Violation
Any help would be greatly appreciated.
TFDThis technique should work:
SET NOCOUNT ON
-- DROP TABLE Company
GO
Create Table Company
(
ParentId int null,
PrimaryId int not null primary key,
CompanyName nvarchar(200) --not null
)
GO
INSERT Company (ParentId, PrimaryId)
SELECT NULL, 1
UNION
SELECT 1, 2
UNION
SELECT 2, 3
UNION
SELECT 2, 4
UNION
SELECT 3, 5
UPDATE Company
SET ParentID = 5
WHERE PrimaryID = 1
GO
SELECT ParentId --Ancestor
, PrimaryId --Descendant
INTO #Hierarchy
FROM Company
WHERE ParentId IS NOT NULL
DECLARE @.BadHierarchy CHAR(1)
SET @.BadHierarchy = 'N'
WHILE 1 = 1 BEGIN
INSERT #Hierarchy
SELECT DISTINCT
p.ParentId
, c.PrimaryId
FROM #Hierarchy p
INNER JOIN #Hierarchy c
ON p.PrimaryId = c.ParentId
WHERE NOT EXISTS (SELECT * FROM #Hierarchy WHERE ParentID =
p.ParentID AND PrimaryId = c.PrimaryId)
IF EXISTS (SELECT * FROM #Hierarchy a WHERE EXISTS (SELECT * FROM
#Hierarchy b WHERE a.PrimaryID = b.ParentID AND a.ParentID =
b.PrimaryID)) BEGIN
SET @.BadHierarchy = 'Y'
BREAK
END
IF @.@.ROWCOUNT = 0 BREAK
END
SELECT @.BadHierarchy
SELECT * FROM #Hierarchy
DROP TABLE #Hierarchy|||I goofed on that one. That @.@.ROWCOUNT needs to be assigned to a
variable before the IF EXISTS statement above it, and that variable
needs to be checked where I'm checking @.@.ROWCOUNT.
-Alansql
so I figured I would solicit your expertise :)
Annoted Version of DDL:
Create Table Company
(
ParentId int null,
PrimaryId int not null primary key,
CompanyName nvarchar(200) not null
)
Facts:
1.) PrimaryId is the PK of the Company.
2.) ParentId is the identifier of the Parent company if one exists.
3.) If ParentId is null then PrimaryId is its own parent.
4.) A company can have only one parent (hierarchcial graph).
5.) There is no one master company for all PrimaryId's. This is to say
you can have more than one hierarchy represented in the Company table:
A D
/ \ / \
B C E F
Where A and D are there own parents.
Problem:
I need to make sure that there can never be the case where a company is
simultaneously both the child and parent of another company, e.g.)
A is parent of B
B is parent of C
C is parent of D
D is parent of A <= Violation
Any help would be greatly appreciated.
TFDThis technique should work:
SET NOCOUNT ON
-- DROP TABLE Company
GO
Create Table Company
(
ParentId int null,
PrimaryId int not null primary key,
CompanyName nvarchar(200) --not null
)
GO
INSERT Company (ParentId, PrimaryId)
SELECT NULL, 1
UNION
SELECT 1, 2
UNION
SELECT 2, 3
UNION
SELECT 2, 4
UNION
SELECT 3, 5
UPDATE Company
SET ParentID = 5
WHERE PrimaryID = 1
GO
SELECT ParentId --Ancestor
, PrimaryId --Descendant
INTO #Hierarchy
FROM Company
WHERE ParentId IS NOT NULL
DECLARE @.BadHierarchy CHAR(1)
SET @.BadHierarchy = 'N'
WHILE 1 = 1 BEGIN
INSERT #Hierarchy
SELECT DISTINCT
p.ParentId
, c.PrimaryId
FROM #Hierarchy p
INNER JOIN #Hierarchy c
ON p.PrimaryId = c.ParentId
WHERE NOT EXISTS (SELECT * FROM #Hierarchy WHERE ParentID =
p.ParentID AND PrimaryId = c.PrimaryId)
IF EXISTS (SELECT * FROM #Hierarchy a WHERE EXISTS (SELECT * FROM
#Hierarchy b WHERE a.PrimaryID = b.ParentID AND a.ParentID =
b.PrimaryID)) BEGIN
SET @.BadHierarchy = 'Y'
BREAK
END
IF @.@.ROWCOUNT = 0 BREAK
END
SELECT @.BadHierarchy
SELECT * FROM #Hierarchy
DROP TABLE #Hierarchy|||I goofed on that one. That @.@.ROWCOUNT needs to be assigned to a
variable before the IF EXISTS statement above it, and that variable
needs to be checked where I'm checking @.@.ROWCOUNT.
-Alansql
Sunday, March 11, 2012
Detached model and msdb - sql server stopped!
Hi,
I detached my model and msdb databases to move the data
and log files. The server went down prior to my
reattaching the model. Now, every time I start SQL, it
goes down when I try to connect, or change options, or try
to reattach model, etc.
Can you help?
Lisa.Perhaps you can start SQL Server from the command prompt with mimimal =configuration and then fix the problems. ---
This information is from Books Online. ---
Starting SQL Server with Minimal Configuration
If you have configuration problems that prevent the server from =starting, you can start an instance of Microsoft=AE SQL ServerT using =the minimal configuration startup option. This is the startup option -f. =Starting an instance of SQL Server with minimal configuration places the =server in single-user mode automatically.
When you start an instance of SQL Server in minimal configuration mode:
Only a single user can connect, and the CHECKPOINT process is not =executed.
Remote access and read-ahead are disabled.
Startup stored procedures do not run.
The sp_configure stored procedure allow updates option is enabled. By =default, the allow updates option is disabled. After the server has been started with minimal configuration, you should =change the appropriate server option value or values, stop, and then =restart the server.
Important Stop the SQL Server Agent service before connecting to an =instance of SQL Server in minimal configuration mode. Otherwise, the SQL =Server Agent service uses the connection, thereby blocking it.
To start SQL Server with minimal configuration
Command Prompt
How to start the default instance of SQL Server with minimal =configuration (Command Prompt)
To start the default instance of SQL Server with minimal configuration
From a command prompt, enter the following command to start the default =instance of Microsoft=AE SQL ServerT as a service: sqlservr -c -f
Note You must switch to the appropriate directory (for the instance of =SQL Server you want to start) in the command window before starting =sqlservr.exe.
-- Keith, SQL Server MVP
"Lisa" <lisajphillip@.kerzner.com> wrote in message =news:02f701c34b03$838bace0$a301280a@.phx.gbl...
> Hi,
> > I detached my model and msdb databases to move the data > and log files. The server went down prior to my > reattaching the model. Now, every time I start SQL, it > goes down when I try to connect, or change options, or try > to reattach model, etc.
> Can you help?
> > Lisa.
>|||Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>|||Great! -- Keith, SQL Server MVP
"Lisa" <lisaj.phillip@.kerzner.com> wrote in message =news:04ed01c34b0f$86f79130$a101280a@.phx.gbl...
Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>
I detached my model and msdb databases to move the data
and log files. The server went down prior to my
reattaching the model. Now, every time I start SQL, it
goes down when I try to connect, or change options, or try
to reattach model, etc.
Can you help?
Lisa.Perhaps you can start SQL Server from the command prompt with mimimal =configuration and then fix the problems. ---
This information is from Books Online. ---
Starting SQL Server with Minimal Configuration
If you have configuration problems that prevent the server from =starting, you can start an instance of Microsoft=AE SQL ServerT using =the minimal configuration startup option. This is the startup option -f. =Starting an instance of SQL Server with minimal configuration places the =server in single-user mode automatically.
When you start an instance of SQL Server in minimal configuration mode:
Only a single user can connect, and the CHECKPOINT process is not =executed.
Remote access and read-ahead are disabled.
Startup stored procedures do not run.
The sp_configure stored procedure allow updates option is enabled. By =default, the allow updates option is disabled. After the server has been started with minimal configuration, you should =change the appropriate server option value or values, stop, and then =restart the server.
Important Stop the SQL Server Agent service before connecting to an =instance of SQL Server in minimal configuration mode. Otherwise, the SQL =Server Agent service uses the connection, thereby blocking it.
To start SQL Server with minimal configuration
Command Prompt
How to start the default instance of SQL Server with minimal =configuration (Command Prompt)
To start the default instance of SQL Server with minimal configuration
From a command prompt, enter the following command to start the default =instance of Microsoft=AE SQL ServerT as a service: sqlservr -c -f
Note You must switch to the appropriate directory (for the instance of =SQL Server you want to start) in the command window before starting =sqlservr.exe.
-- Keith, SQL Server MVP
"Lisa" <lisajphillip@.kerzner.com> wrote in message =news:02f701c34b03$838bace0$a301280a@.phx.gbl...
> Hi,
> > I detached my model and msdb databases to move the data > and log files. The server went down prior to my > reattaching the model. Now, every time I start SQL, it > goes down when I try to connect, or change options, or try > to reattach model, etc.
> Can you help?
> > Lisa.
>|||Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>|||Great! -- Keith, SQL Server MVP
"Lisa" <lisaj.phillip@.kerzner.com> wrote in message =news:04ed01c34b0f$86f79130$a101280a@.phx.gbl...
Hi,
Thanks for the input. I did try it a few times, and finally got it right. I reattached the databases using isql, as enterprise manager couldn't connect.
Thanks again!
Lisa.
>--Original Message--
>Perhaps you can start SQL Server from the command prompt with mimimal configuration and then fix the problems. >---
>This information is from Books Online. >---
>Starting SQL Server with Minimal Configuration
>If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft=AE SQL ServerT using the minimal configuration startup option. This is the startup option -f. Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically.
>When you start an instance of SQL Server in minimal configuration mode: >Only a single user can connect, and the CHECKPOINT process is not executed.
>
>Remote access and read-ahead are disabled.
>
>Startup stored procedures do not run.
>
>The sp_configure stored procedure allow updates option is enabled. By default, the allow updates option is disabled. >After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
>
>Important Stop the SQL Server Agent service before connecting to an instance of SQL Server in minimal configuration mode. Otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
>
>To start SQL Server with minimal configuration
>Command Prompt
>
>How to start the default instance of SQL Server with minimal configuration (Command Prompt)
>To start the default instance of SQL Server with minimal configuration >From a command prompt, enter the following command to start the default instance of Microsoft=AE SQL ServerT as a service: >sqlservr -c -f
>
> >Note You must switch to the appropriate directory (for the instance of SQL Server you want to start) in the command window before starting sqlservr.exe.
>
>-- >Keith, SQL Server MVP
> >"Lisa" <lisajphillip@.kerzner.com> wrote in message news:02f701c34b03$838bace0$a301280a@.phx.gbl...
>> Hi,
>> >> I detached my model and msdb databases to move the data >> and log files. The server went down prior to my >> reattaching the model. Now, every time I start SQL, it >> goes down when I try to connect, or change options, or try >> to reattach model, etc.
>> Can you help?
>> >> Lisa.
>> >.
>
Saturday, February 25, 2012
Designing relational tables
Hi, not sure if this is the right forum for this question.
I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know...
Nick
Yes you can do this. Once you have your "big" table populated simply create the second smaller table and just execute an update statement:
update mySmallTable
set field1 = b.field1
, field2 = b.field2
, field3 = b.field3
from myBigTable b
Great. Im really moving forward now. thanks for your time
Nick
Designing Date and Time Dimension
I need to display reports based on an SSAS cube which requires both date and time as input parameters.
I'd like to know what is the best practice while designing dimensions in such a scenario. Do i need to design a separate dimension for date ( quarter, year, month, week, day) and time ( hour, minute, second) ?
Or will having just the date dimension with the date key pointing to the full datetime in the fact table suffice?
I guess there is no correct answer to this question, but in my opinion you should design two dimensions - one for the dates and one for the seconds (with related attributes, of course). If you put it all in one dimension, your time dimension would consist of 60 x 60 x 24 x 365 = 31,536,000 members for each year (!!!), whereas if you make two separate dimensions, you have a date dimension with 365 members for each year and a time dimension with 86,400 members (constant). This will be much more performant and simple to work with in most cases.Designate database in login
Is there a way to tell SQL Server 2000 which database you
want to connect to at the time of login? I have two
different database on my server and there are times I want
to log into the other database instead of the default
database. Can do this at login time?Hi
isqlw -S servername -E -d northwind
this way i can go to northwind database instead of default one( use -U -P
for sql authentication)
well with odbc u can do it your own way with connection string
Regards,
Mayur
"John Abate" <abatej@.noralnm.com> wrote in message
news:d34401c3ef37$98826cf0$a001280a@.phx.gbl...
> Is there a way to tell SQL Server 2000 which database you
> want to connect to at the time of login? I have two
> different database on my server and there are times I want
> to log into the other database instead of the default
> database. Can do this at login time?|||Thanks!
>--Original Message--
>Hi
>isqlw -S servername -E -d northwind
>this way i can go to northwind database instead of
default one( use -U -P
>for sql authentication)
>well with odbc u can do it your own way with connection
string
>Regards,
>Mayur
>
>"John Abate" <abatej@.noralnm.com> wrote in message
>news:d34401c3ef37$98826cf0$a001280a@.phx.gbl...
you
want
>
>.
>
want to connect to at the time of login? I have two
different database on my server and there are times I want
to log into the other database instead of the default
database. Can do this at login time?Hi
isqlw -S servername -E -d northwind
this way i can go to northwind database instead of default one( use -U -P
for sql authentication)
well with odbc u can do it your own way with connection string
Regards,
Mayur
"John Abate" <abatej@.noralnm.com> wrote in message
news:d34401c3ef37$98826cf0$a001280a@.phx.gbl...
> Is there a way to tell SQL Server 2000 which database you
> want to connect to at the time of login? I have two
> different database on my server and there are times I want
> to log into the other database instead of the default
> database. Can do this at login time?|||Thanks!
>--Original Message--
>Hi
>isqlw -S servername -E -d northwind
>this way i can go to northwind database instead of
default one( use -U -P
>for sql authentication)
>well with odbc u can do it your own way with connection
string
>Regards,
>Mayur
>
>"John Abate" <abatej@.noralnm.com> wrote in message
>news:d34401c3ef37$98826cf0$a001280a@.phx.gbl...
you
want
>
>.
>
Friday, February 24, 2012
Design Time XSD Metadata refresh
My SSIS package has 19 XML Source inputs constructed of 4 different (XSD) Schemas. I'm trying to find a convenient way to refresh the underlying metadata at design time so that I don't have to open each XML Source, change the XSD reference, click on the columns display, change the XSD back to the original but updated XSD just to get say a new default column width for string data. Does anyone have a quicker way to force an SSIS package to pick up changes in a referenced XSD?
Unfortunately, there is no an easy way to do this. Maybe the easiest would be to do this programmatically.
See the following thread for more details:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2073404&SiteID=1
Thanks,
Bob
Design the Database in SQL First time
Hi all,
I m desiging the database first time, so how can i design the database,how i set the datatype of Datafield.(means which creteria i use for giving data type).
how can i manage the Relationship....
plz discuss in detail....
thanx in advance
Sajjad Rizvi
I need information/suggestions on giving field types
and want suggestion on naming conventions on fields etc......
and also wanna know about various data type, like booleon,varchar,numeric....
thanx in advance
Sajjad|||sorry forget....
I m using Sql Server 2000
Sajjad|||
There are no shortcuts. Learning SQL will take some time. It'sworthwhile though so get to it and come back when you have *specific*questions.
|||
I m desiging the database first time, so how can i design the database,how i set the datatype of Datafield.(means which creteria i use for giving data type).
how can i manage the Relationship....
plz discuss in detail....
thanx in advance
Sajjad Rizvi
1. Do you need syntax on how to create table?
2. Or do you need just information/suggestions on giving field types?
3. Or do you want suggestion on naming conventions on fields etc
I need information/suggestions on giving field types
and want suggestion on naming conventions on fields etc......
and also wanna know about various data type, like booleon,varchar,numeric....
thanx in advance
Sajjad|||sorry forget....
I m using Sql Server 2000
Sajjad|||
Try these two links the first is Microsoft new sample database AdventureWorks2000 download and the second is the book used to create it right click and download the PPT slides. When you have installed AdventureWorks2000 look at the 113 tables and see if you could use some for your application. Data Modeling is very complex some one cannot teach you online, if you need a book buy the book in the link it comes with six complete databases. BTW if you have problem installing AdventureWorks use my email in my profile and I will email you the only file you need to install it, you need SP3 to install it. Hope this helps.
http://www.microsoft.com/downloads/details.aspx?FamilyID=487C9C23-2356-436E-94A8-2BFB66F0ABDC&displaylang=en
http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html
|||No one can teach you database design in a forum. That's what books andonline articles are for. Lacking that Microsoft provides a pile ofdocumentation as well. Not as accessible but it's included with whatyou already have.There are no shortcuts. Learning SQL will take some time. It'sworthwhile though so get to it and come back when you have *specific*questions.
|||
What your asking is like "can you tell me how to build a house using a Catipillar Crane and Earth Mover"?
Let's also not forget that SQL Server 2000 is a tool. You need to learn the fundamentals of relational theory and data modeling. Date's INTRODUCTION TO DATABASE SYTEMS is standard issue for fundamentals.
|||I agree that it will take time to learn SQL in and out. Database design is not easy if you have never done it before. However, there are some great links above that should point you in the right direction. If you have ever used Microsoft Access, you should be alright. In SQL 2000, you can use the "designer" (Enterprise Manager) to assist with developing the tables or you can use transact-SQL directly within Query Analyzer. The best suggestion I would have for you is to draw out everything first. Then determine what limitations need to be specified.|||Thanx to All for great reply,
I just wanna know Normilizaton, Relation ship and some introduction about common Datatype and their usage.....
Sajjad
Friday, February 17, 2012
Design problem
Hello. I'm using SQL2000, and I have a design problem.
I know that every time I make a primary key, sql server makes it by default
a clustered index. Since I have a large composite key in the table I don't
know if it's smart to leave it as a clustered index. This is the table:
I know that every time I make a primary key, sql server makes it by default
a clustered index. Since I have a large composite key in the table I don't
know if it's smart to leave it as a clustered index. This is the table:
CREATE TABLE [InputOutputItems] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[SerialNumber] [varchar] (50) COLLATE Croatian_CI_AI NOT NULL ,
[PartnerID] [int] NULL ,
[Barcode] [varchar] (50) COLLATE Croatian_CI_AI NULL ,
[DaysOfExpiration] [int] NULL ,
[DateOfValidation] [datetime] NULL ,
[Row] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Column] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[Level] [varchar] (20) COLLATE Croatian_CI_AI NULL ,
[UnitDimensionID] [int] NULL ,
[UnitPack] [decimal](18, 4) NULL ,
[TotalWeight] [decimal](18, 4) NULL ,
[PackageMachineID] [int] NOT NULL ,
[PackageEmployeeID] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[PaleteNumber] [int] NULL ,
[LinkedDocument] [int] NULL ,
CONSTRAINT [PK_InputOutputItems] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear],
[ProductID],
[SerialNumber],
[PackageMachineID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutputItems_InputOutput] FOREIGN KEY
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) REFERENCES [InputOutput] (
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
)
) ON [PRIMARY]
InputOutPutItems is actually an Item table for documents, here is the header
table for documents:
CREATE TABLE [InputOutput] (
[FromStorage] [smallint] NOT NULL ,
[ToStorage] [smallint] NOT NULL ,
[DocumentTypeID] [int] NOT NULL ,
[DocumentNumber] [int] NOT NULL ,
[StorageDocYear] [int] NOT NULL ,
[AppUserID] [smallint] NULL ,
[ManufactureIndent] [int] NULL ,
[DeliveryDate] [datetime] NULL ,
[OrgUnitID] [int] NULL ,
[TypeOfTransferID] [int] NULL ,
[Note] [varchar] (500) COLLATE Croatian_CI_AI NULL ,
[Status] [int] NULL ,
[UserIDCreated] [int] NULL ,
[DateCreated] [datetime] NULL ,
[UserIDChanged] [int] NULL ,
[DateChanged] [datetime] NULL ,
[TypeOfIndent] [char] (2) COLLATE Croatian_CI_AI NULL ,
[TMOrgUnitID] [smallint] NULL ,
CONSTRAINT [PK_InputOutput] PRIMARY KEY CLUSTERED
(
[FromStorage],
[ToStorage],
[DocumentTypeID],
[DocumentNumber],
[StorageDocYear]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_InputOutput_DocumentTypes] FOREIGN KEY
(
[DocumentTypeID]
) REFERENCES [DocumentTypes] (
[DocumentTypeID]
),
CONSTRAINT [FK_InputOutput_OrgUnits] FOREIGN KEY
(
[FromStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
),
CONSTRAINT [FK_InputOutput_OrgUnits1] FOREIGN KEY
(
[ToStorage]
) REFERENCES [OrgUnits] (
[OrgUnitID]
)
) ON [PRIMARY]
These tabels are used for documents in a Warehouse Managament System with
barcode scanners.
Because of the way that the system works the primary keys cannot be any
smaller. I never use the whole
primary key in a where clause, I just use parts of it.
Should I make somethig else a clustered index, or leave the clustered index
as a primary key?
Thanks in advance.
Drazen Grabovac.Grabi (drazen@.git.hr) writes:
> Hello. I'm using SQL2000, and I have a design problem. I know that every
> time I make a primary key, sql server makes it by default a clustered
> index. Since I have a large composite key in the table I don't know if
> it's smart to leave it as a clustered index. This is the table:
This question already has answers in microsoft.public.sqlserver.programming.
Please do not post the same question independly to several newsgroups,
as this can result in people wasting on a time on a problem that has
already been addressed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)