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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-09-15 : 08:10:55
|
| Dave Brown writes "I have a need to connect to an ODBC source from within a Stored Procdure. Specifically, we're attempting to connect to a remote database under MSDE and compare records to the SQL database.Is it possible to use an ODBC connection within a Stored Proecudure? Is it possible to define that ODBC connection within a Stored Procedure?I want to use the procedure to return a set of values not found on the remote database (SELECT ID FROM MYTABLE WHERE ID NOT IN (SELECT ID From SQLTable WHERE MYTABLE.ID = SQLTable.ID)). The Stored Procedure would be called from a VB Script within a CRM application." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-15 : 08:15:47
|
| Yes, there are a couple of ways to do this:-Create a linked server on your SQL Server that points to the MSDE source. You can then query it using 4-part object names SELECT * FROM MSDESERVER.myDB.dbo.myTable, or with OPENQUERY SELECT * FROM OPENQUERY(MSDESERVER, 'SELECT * FROM myDB.dbo.myTable')-You can use OPENROWSET and pass the connection string and query to it-You can use OPENDATASOURCE and pass the connection string, and use regular 4-part names to query it.Books Online has the details on each approach, you should research them all to see which is best for your situation. |
 |
|
|
|
|
|
SP (Connecting to an ODBC source within a SP)