Extended Stored Procedure with ODBC DSN

[Jason Sweby]

I have an extended stored procedure that makes its own connection via ODBC
and performs some processing - stuff that can't be done using T-SQL alone
(because it uses some of my other DLLs).

If I try to connect using the System DSN that is already set up on the PC
(e.g. named MyDataSource), the xp returns the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open database
requested in login 'MyDataSource'. Login fails.

However, if I connect using a connection string
(SERVER=MyServerName;DATABASE=MyDatabaseName;etc) then it connects & the xp
works.

I tested the code outside of an xp, just a Windows app, and the connection
using the DSN worked. I'd expect this as it is the same way I have connected
to a database for 10 years!

So I have it working but really want to know why it doesn't work using a
DSN. I also tried with a user DSN but no difference.

Any ideas? Thanks.

--
Jason Sweby
Software Development Manager,
Carval Computing Limited, Plymouth, UK

Payroll - HR - T&A - Access Control