February 8, 2006 at 9:56 am
Hi, I hope someone can help.
We have an application which writes user specific data to a table identifying the users data by their machine name (HOST_NAME), i.e. it writes the machine name to each row. We have now deployed the application in a Terminal Server environment and this no longer works as the adp's for all users is held on the terminal server, i.e. all users have the same HOST_NAME.
The app has been migrated from Jet to SQL Server and under Jet these were local tables so no problem.
The main problem is that one of the functions in the app uses 'DoCmd.OutputTo acOutputServerView' and the first parameter to this command is the view name. We cannot pass a parameter to the view within the code so it has a criteria of column_name = HOST_NAME(). But as HOST_NAME is no longer unique this no longer works.
What we are looking for is some sort of unique session identifier that we can use. I've looked at SUSER_SID() but I need a value I can see/understand. I've also looked at SPIDs but I don't think these are the answer as I'm not sure what SPID would be used when the view is executed. Also had a look at SYSTEM_USER but we allow both Windows and SQL authentication.
I suppose we could use temporary tables but that would require a fair bit of a re-write.
Any help would be greatly apprecited.
February 9, 2006 at 11:32 am
How about suser_sname() ? It works for both Windows and SQL logins, although the Windows accounts are prefaced with the domain name.
February 13, 2006 at 5:10 am
Many thanks for that. Problem solved!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply