Post reply

sysprocesses.hostprocess unique?

  • October 28, 2015 at 12:05 pm

    #322210

    I hope this is the right forum for my question, in fact it is quite simple: is the column hostprocess from sysprocesses unique?

    We have a client/server Visual Basic application that is accessed from multiple workstations (and the server itself), sometimes more than once from each device. We use this field to control the flow of data for different DB (main office and branches). For such we need to ensure that each connection has a unique id. But from time to time we have a kind of errors in the data distribution which suggests that a second user has connected to the DB with the same sysprocesses.

  • October 28, 2015 at 12:26 pm

    #1836181

    It is not unique.

    It's not the session id from sysprocesses. It's the process ID (windows process ID) that the client application has on the client machine.

    Go to a client, open Task Manager and look at the PID column. That's the Host Process ID.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 29, 2015 at 9:01 am

    #1836414

    Thanks Gail, I have suspected that since I checked the Task Manager in Windows. So, this data is set by Windows itself, instead of SQL and it is possible to be repeated for a few machines in a network.

    In our application this function was designed 10 years ago, to work with SQL 7. I wonder why the consultant created the PK of our table based in the hostprocess of the sysprocesses instead of the spid.

    I understand that the spid column may be repeated, in case of parallelism, but only for the same connection. SQL will never give a repeated spid number for two different connections at same time, correct?

    Thanks again for your prompt reply.

  • October 29, 2015 at 11:39 am

    #1836471

    marcolorado (10/29/2015)

    SQL will never give a repeated spid number for two different connections at same time, correct?

    Correct, just be careful, the SPID can be reused very quickly after a session disconnects.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply