OPENDATASOURCE Question
-
Then linked server is not your solution.
Linked server suppose to be static.
_____________
Code for TallyGenerator -
I absolutely concur with Noel. If the DTC service is not running on remote server, or DTC is not configured correctly, your OPENDATASOURCE queries will not run on those servers. This can be very frustrating in a large environment, as DTC is often ignored and poorly understood, so even in a supposedly "standardized" enterprise environment, its a gamble that any particular server will or will not have DTC turned on. I'd like to standardize this in our environment, but would like to find more info on any potential security risks, so I can weight the benefits against the risks.
I've also encountered lots of issues when pulling data out of or writing directly to Excel, Access, or text files. This requires careful consideration of where the files are located and permissions, and where the OPENDATASOURCE query is running. In some cases, I've had to scrap OPENDATASOURCE and use DTS or SSIS to just get the job done where permissions where a moving target between environments and remote locations.
You are absolutely correct...OPENDATASOURCE is a great way to remotely move data for exactly the reasons you mention, in particular, eliminating the need to set up hundreds of linked servers and the administrative nightmares they can cause.
Don't forget you can use CAST, CONVERT, LEFT/RIGHT, UPPER/LOWER, concatenations, etc to control data type conversion, as well as change column names, eliminate spaces in column names, add columns (CREATED_DATE, CREATE_BY, etc.) for clean import directly from user's dirty Excel or Access files, straight into tables.
Tom
-
Better have a good list of logins and passwords, then
And figure out a really good way to change them in the code if you actually want to be PCI compliant by changing the passwords every 3 months 
Of course, having the passwords in open code is a violation of PCI and other standards so you'll need to encrypt the procedures which brings up another whole ball of wax.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I've changed the code over to use linked servers and dynamic SQL. The stored procedure code is encrypted.
We're done...
-
If you use remote calls regulary and heavily you better have trusted connections established.
Then you don't need to use passwords at all.
_____________
Code for TallyGenerator -
Pretty sure (been a while since I've had to check, DBA's at work normally do this) PCI compliance doesn't allow trusted connections. If you're not worried about PCI, then that'll work.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
What is linked server then, if not another implementation of trusted connections?
_____________
Code for TallyGenerator -
Didn't think of them that way... operative words were "didn't think"
Thanks, Serqiy.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply