Using ODBC connections in stored procedures

[spence74]

Does anyone have any ideas how to use an ODBC connection in a stored procedure? (MS SQL Server 2000). My goal is to transfer data from a database on one server to a database on a completely different server without having to use DTS packages.

Thanks,
Josh

 

[vongrunt]

Have you tried OPEN*() stuff (query/datasource/rowset - see Books Online)?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

Why not just create a linked server and then use Select Into to transfer the data?

Jim

 

[spence74]

I tried to create a link, but it only opens to what I am assuming is the default catalog. Is there a way to specify which catalog to go to when using a linked server?

 

[jbenson001]

Check the syntax for the sp_addlinkedserver stored procedure on the Master database.