Hi experts. I need to send data to a database instance that is not on our domain/network. Can someone give me some options? Also, am I limited to using an ODBC connection? They're typically slow when sending large datasets.
Thanks. Jack
Assuming that the firewalls allow it you can create a Linked Server at a remote location and communicate with it. Watch out for performance though ... we avoid using 4-part naming:
LinkedServerName.DatabaseName.dbo.TableName
and use OPENQUERY(LinkedServerName, 'SELECT ...') instead as we find it performance better and we can control whether any JOINs happen locally or not.
Alternatively you can export / import data using BCP. I strongly recommend that you use its "Native" data format (because you are transferring data From/To SQL Server, rather than Comma Delimited or somesuch. My preference is to have BCP call an SProc for its data, because we usually want to only transmit "recent data" or something like that, but querying a VIEW is also worth considering - then you can adjust the VIEW easily - e.g. if the people at the remote end want the data "massaged" a bit, or most/fewer columns included.
Send the people at the remote end a CREATE TABLE statement that suits YOU!! (or get them to send you THEIRs ...)
1 Like
Thank you, Kristen. These are very helpful ideas. We'll be able to get past any firewall issues. I will compare the LinkedServer technique against OpenQuery to see what works best for me.