Sql Linked Table Default Value Bug 2

[PWise]

HI Everyone!

I am using an .MDB with A table linked to Sql Server BE. On the back end I put a default value on one field the Sql Server function Host_name(). This works fine on a .ADP it defaults the field to the name of the computer that the record was entered, in a pass-thru query it also returns the name of the computer that the query was run on but when i add a record via a linked table it returns the name of a different computer (always the same machine).

Any one has an idea of why this is happing and a fix to this

Thanks
PWise

 

[AlexCuse]

This is all speculation, but...

What machine name is it showing?

Are you running it from a PC that has SQL Server's client tools installed?

I tested this (have not tested on multiple machines), but when I run an insert through MS Access (via linked table with file data source or machine data source) or SQL Server I get the desired machine name. Can you explain your setup in greater detail?

Good Luck,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[jsteph]

The Host is whatever is entered in the ODBC Link box when you link it. You could put garbage in there if you want.

You can link in code, and put variables in there. But you need to make sure that the link procedure runs on each client machine at least on the first open.
--Jim

 

[AlexCuse]

That is what I was thinking jsteph.

A better option might be to have access identify the machine, and pass the value. This may have a slight cost in terms of overhead, but I think it will be the most solid and probably easiest to implement.

Ignorance of certain subjects is a great part of wisdom

 

[jsteph]

Yes, there is some overhead. What I do is on open of app, I run a re-link procedure. It simply loops through a local table with a list of linked tables and reconnects them, by deleting the tabledef, then reattaching with the dbAttachSavePWD option. The connection string is in the form:

strCnBase = "ODBC;DSN=MyDSN;UID=someuser;PWD=somepwd;WSID=" & GetMach() & ";APP=SalesOrder_" & format(now(),"mmddyyyy_hhnn") & ";Database=Sales;"

Where GetMach() is a wrapper for an api call to GetComputerName in the kernel32.dll library. I also add the App identifier, with a timestamp. Now doing an sp_Who2 will show not just the machine's connections, but the app as well, and the time the connection was made. This is good when you need to kill a spid, but want to make sure it's the right one. Due to connection pooling, lazy close, etc, the connection may not actually refresh each time the procedure is run, and of course you'll see only one connection per app, not one per table.
--Jim

 

[PWise]

Thank Alex for replying

this is what i picked up from google

Indeed, the host name must be set through the connection string. I don't
know how it works through Access, but the mechanism is such that the
client tells SQL Server about the name, and lie as much as it want.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

When you link a table access puts a WSID=xxxx in the connection string, where WSID= the machine that you created the link on. If you want to create a link without a WSID you must use a dsn with wsid= in it. By doing this at least i wont have wrong information in my table.

I would still like to find a way to have the right information in it.

 

[PWise]

thanks jim for replying

Have A star

I like you method to put the time stamep in the app name

Also a star for alex

 

[AlexCuse]

pwise - Let me know how Jim's method works out for you. It sounds better to me than using VBA to identify machine, and pass machine name in a query (rather than using default value in the table).

Ignorance of certain subjects is a great part of wisdom