Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Save result of sp_who in a table with inserted date

Posted on 2006-11-08
3
Medium Priority
?
1,037 Views
Last Modified: 2008-02-01
All,

I created a table that matches the structure of sp_who:

table tbl_sp_who:
  spid            smallint,
  ecid            smallint,
  status            nchar(30),
  loginame            nchar(128),
  hostname      nchar(128),
  blk            char(5),
  dbname            nchar(128),
  cmd            nchar(16)

When you execute the below insert statement:
insert into tbl_sp_who execute sp_who

It saves the result into the table...

NOW, I want to add a column "insertDate" where when a record goes in, it has a time stamp of when it goes it.  This way I can look at the table like a log.

How do I do it?  I tried adding the extra column and going the 'getDate()' route but just adding the extra column crashes the insert statement.  

Any ideas?

Thanks in advance,
kris
0
Comment
Question by:kdunnett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 2000 total points
ID: 17901106
create table  tbl_sp_who
(
  spid          smallint,
  ecid          smallint,
  status          nchar(30),
  loginame          nchar(128),
  hostname     nchar(128),
  blk          char(5),
  dbname          nchar(128),
  cmd          nchar(16),
  insertdate datetime default(getdate())
)

insert into tbl_sp_who (spid,ecid,status,loginame,hostname,blk,dbname,cmd)
exec sp_who
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17901112
you create the column with the default detdate(), but then don't specify it in the values list.
0
 

Author Comment

by:kdunnett
ID: 17901400
Thanks!

Kris
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question