Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2008
    Posts
    8

    Unanswered: Autopopulating SQL Fields

    I have created a SQL table to record VBA coding errors. How can I get the table to auto populate the following fields whenever a record is inserted?

    Current date and time
    IP, MAC, and PC name of PC the insert statement came from
    An auto number column for unique ID purposes

    My VBA code could do this but I would prefer to have it done via SQL to reduce the chance of error, improve efficiency, and have cleaner looking VBA code.

    Thank you,
    Scott

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For date and time, just use the GetDate() function.
    For other connection information, look up "niladic functions" in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by scott_h
    I have created a SQL table to record VBA coding errors. How can I get the table to auto populate the following fields whenever a record is inserted?

    Current date and time
    IP, MAC, and PC name of PC the insert statement came from
    An auto number column for unique ID purposes

    My VBA code could do this but I would prefer to have it done via SQL to reduce the chance of error, improve efficiency, and have cleaner looking VBA code.

    Thank you,
    Scott
    for AutoNumber, set column to IDENTITY

  4. #4
    Join Date
    Nov 2008
    Posts
    8
    I am using SQL Server 2000

    I have tried the GETDATE() funtion. It returned the current date and time when a select statement is ran not when the record was inserted.

    IDENTITY is not an available data type in Enterprise Manager

    I searched for "niladic functions" and did not find any regarding IP, MAC, or PC name of the workstation.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you give up too easy skywalker

    IDENTITY is not a data type but a column property. you can find it in the enterprise Mangler.

    As for the functions, have you seen..
    .
    SELECT HOST_NAME()

    As for GETDATE(), of you set it as a default constraint on a column in the table, it does what you are after.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    GetDate returns the current date and time when the record is inserted if it is set as the default for the column.

    Here are the niladic security and connection functions. SQL Server, of course, only knows what information is passed to it through the connection:

    Code:
    select	Current_User as 'Current_User',
    		Session_User as 'Session_User',
    		SUser_ID() as 'SUser_ID',
    		SUser_SName() as 'SUser_SName',
    		System_User as 'System_User',
    		SUser_Name() as 'SUser_Name',
    		User_ID() as 'User_ID',
    		User_Name() as 'User_Name',
    		Host_ID() as 'Host_ID',
    		Host_Name() as 'Host_Name'
    Quote Originally Posted by scott_h
    IDENTITY is not an available data type in Enterprise Manager
    It's not a "datatype". It is a property of an int or bigint datatype.

    You need to hit Books Online real bad, or get some serious help with this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by PMASchmed
    for AutoNumber, set column to IDENTITY
    Can the same IP throw two errors simultaneously?
    I'd argue that there's a perfectly good natural key available instead
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Maybe he WANTS to use a surrogate key....?

    Do we need to start this battle again? I'm ready for ya, Rudy.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    As long as the OP has considered using the natural key before opting for a surrogate, then that's fine by me.
    Sadly, the word AutoNumber and VBA appeared so I put 1 + 1 together and got slightly less than 2 - Access - so I figure this hasn't been thought of
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I forget where I read it but "IDENTITY" is strictly a table property (hence why you can have only one per table). Might have been "Inside SQL Server"....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I've read that too Poots.

    To the archivist-mobile!
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good call Boy Wonder

    Unfortunately, I can't now find that reference in my Archive, nor in Google. Humiliatingly, extract from "Inside SQL Server":
    Quote Originally Posted by The Bible
    IDENTITY isn't a data type; it's a column property
    Damn!

    Yet there are T-SQL codez that act as if the IDENTITY is a table property:
    Code:
    SELECT    IDENTITYCOL
    FROM    AdventureWorks.HumanResources.Employee
    
    SELECT    IDENTITYCOL
    FROM    AdventureWorks.Person.Contact
    
    SELECT    OBJECTPROPERTY (OBJECT_ID('AdventureWorks.Person.Contact'), 'TableHasIdentity')
    Last edited by pootle flump; 11-20-08 at 06:10.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Me either... must be an [strikethrough]urban[/strikethrough] SQL myth
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    I'm ready for ya, Rudy.
    nothing wrong with using a surrogate key in the right circumstances
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    Unfortunately, I can't now find that reference in my Archive, nor in Google. Humiliatingly, extract from "Inside SQL Server":
    Damn!
    yes I cleared that up many posts ago. see above.

    of course it is a column property silly flump. that's why its called an identity column.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •