August 3, 2004 at 12:19 am
Firstly, the apology: I'm a relative new comer to SQL Server, so the answer to this question may be obvious but its not obvious to me.
Now the question, with some background:
I am trying to design a solution to add some functionality to an existing Access Client/SQL Server back-end application. The back-end is SQL Server 2000. For a number of techinical and political reasons, I want to avoid, if possible, changes to the Access client. The change I need to implement is to extend the attributes stored in certain tables. The values that should be stored in the new attributes will be constant for a given connection, but may be different from connection to connection.
My initial thought is to rename the tables, create views over the tables with the original names of the tables, and use instead of triggers to maintain the new attributes, so the client application will be unaware of the database changes. However, I'm stuck for a simple strategy for holding the connection specific values I want to use as defaults in the triggers.
I would like to be able to set the connection specific values once, and reference them thereafter in the triggers. One possible solution is to place them in a table (using, say, user or psid as a key into the table), but that creates possible issues of removing old values once they are no longer required (e.g. what happens if the client crashes?). Ideally, I would just like to define some object in memory that is private to the connection, which can hold the values for the duration of the connect, which can be referenced from the triggers, and which is automatically removed when the connection dies or is close.
For those of you familiary with Oracle RDBMS, what I'm looking for is something analogous to a package variable in a PL/SQL package specification. Each user session gets its own instance of the package variables, the package variables are effectively global to the session, they can be references from views and triggers, and there's no need to worry about conflicts between connections (even where it is the user connected multiple times) or any house-keeping to clean up old values as they're private to the session and only exist for the duration of the session?
Is there something similar in SQL Server? Can anyone suggest an alternative approach that would provide the same functionality?
Peter
August 3, 2004 at 3:41 am
I don't have a complete solution for you, but I can tell you that I thought about something similar and I encountered another problem: MS Access is using more than one connection to get data from the server (see KB308312). If you make something based on the connection you may have some problems setting the "global variable" for all the connections for a given instance of Access.
Some ideas that I have thought about:
- using SET CONTEXT_INFO: this is just like a global variable across the connection; A disadvantage is that is only one variable (possible conflicts if used by many developers). Another is the problem with multiple connections opened by Access.
- using something in the connection string, that can be retrieved by SQL Server (for example: the APP_NAME() function). This way, the problem with multiple connections is solved. Unfortunately, this doesn't seem to work with ADP-s; the only thing I could change is the HOST_NAME(), not the APP_NAME().
- writing something in a table (using a separate connection), within an open transaction. This way, the problem of removing old values (for crashed connections) is solved: SQL Server automatically rolls-back any uncommitted transaction if the connection is lost. But you have to identify all the connections from a client in some way, for example using HOST_NAME().
If you will find a solution to this problem, please let me know.
Razvan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply