Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
OwenW
Starting Member
11 Posts |
 Posted - 2007-01-22 : 12:36:44
|
| I have an application that connects to a SQL Server 2005 database via an ODBC machine DSN. I need to find a way to store the connection string of the application to a field in every record that is created or updated. The reason for this is the application (third party - unalterable) does not track this data, but I need to know which path the data took to get to the database. Different projects within the third party app use different ODBC connectors, but I have no way of knowing which path was used to alter or create a record. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-22 : 12:57:20
|
| Can you get the different projects to set a different hostname then pick that up from sysprocesses?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
OwenW
Starting Member
11 Posts |
Posted - 2007-01-22 : 14:03:29
|
| I have no control of how the application connects to the database, other than what I name the ODBC DSN when I configure it. I need record level entries of the path the data that altered the record took to reach the DB. The path would not necessarily be needed, but I do need to be able to differentiate between the various DSNs that the app used. The DSNs are project specific.Thanks for the reply! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-22 : 16:46:27
|
| Well have a look at sysprocesses when the app is connected and see if there's anything you can use.Also try using the profiler to trace a connection and see if there are any useful attributes.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
OwenW
Starting Member
11 Posts |
Posted - 2007-01-22 : 18:03:09
|
| I checked that out, but the app connects too briefly for sysprocesses to be useful. I am looking at trying to get ahold of the connection's metadata, which should include the DB name, the name of the table (or view), and some data about the connection. Does anyone know how to access the connection metadata and pass that data to a table for each record that is updated or inserted? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-01-23 : 05:37:26
|
| When the app is inserting the connection will instantiated and the sysprocesses entry available.It sounds like you might want a trigger on the table (maybe an instead of trigger).The trigger will know about the database and table name.The connection isn't made to a database or table so there's no point looking there. It is made to a server - the metadata you are talking about is held in sysprocesses.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
OwenW
Starting Member
11 Posts |
Posted - 2007-01-23 : 10:31:38
|
| The more I look at it, the more I like the idea of using an INSTEAD OF trigger. I'm not too familiar with them, so I'm wondering if I can insert values into fields based on some code in the trigger, as well as doing an INSERT from the 'inserted' table. As an example, if I have a view on a table, and I create an INSTEAD OF trigger on that view which, in addition to putting data in the target table, puts a value in a field which references which view (and trigger) was used to update the record. Is this possible?Thanks for all your input. |
|
|
|
|
|
|
|
|
Populate a Field With Connection String