August 18, 2004 at 12:18 pm
I am thinking of creating a tmptable for each user that logs on to store key data used in WHERE statements in SPs in order to only return data pertaining to the user.
For Example ( WHERE tblInstallations.I_Key = tmpInstallation.I_Key)
Can tmp tables be linked in sps?
Will the Tmp table be available to the user until they log off??
Is this a bad idea??
Any Comments, suggestions, or opinions would be appreciated.
Thanks.
fryere - A novice sql server user
fryere
August 18, 2004 at 2:39 pm
Depending on what you are doing this may be or may not be a bad idea. And most times you are going to close your connection ASAP and thus the temp table will go away. You should not leave open connections unless transaction is in process or you need a specific locking to occurr on the DB.
What type of application (VB, C++, Web) is it and what are your other options?
August 18, 2004 at 6:26 pm
I would use a permanent table, save the overhead of creating/removing the temp table each time. Definitely you want to close the connection and that makes temp tables expensive.
August 19, 2004 at 5:30 am
Thanks you for the replys. I think that I have dropped the temp table idea since I finally got something else to work. I am using MS ACCESS 2003 on the front end. The problem that I was having is that although the SPs had the installationkey as an input parameter I was having a tough time calling the SPs and passing in the InstallationKey together.
Could you explain your permenant table idea a little more?
Thanks.
Fryere
fryere
August 19, 2004 at 5:54 pm
Not much to it. Temp tables are private to the user. To use a permanent table to do the same thing you first create the table and grant appropriate users access, either via stored procs or on the table itself. The one thing you need to add is a way to identify each users data in the table - will be all massed together. You can use their login if using NT authentication, I prefer @@spid because sometimes you'll have users running multiple instances of an application. Then you use just as you would the temp table except you add the additional where clause to get only that users data.
August 20, 2004 at 6:00 am
I have decided to go with a single 'User' Table that gets updated with the user's SYSTEM_Name, Installation_Key, and Current Date when they log on. This seems to work well since I can build the Link to the User table in the SPs to filter the data and not have to worry about any input parameters. Hopefully there will be no performance problems with this method.
Thanks for all the input. I know you guys have way way more experience with this than I do.
fryere
P.S. Great Web Site
fryere
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply