February 28, 2012 at 7:32 am
Is there a way to connect to two SQL Server 2008 R2 databases on different servers *without* using a Linked Server?
February 28, 2012 at 7:46 am
How completely random that this question was asked twice on the same day!?!?!?!?
Here is the other thread. http://www.sqlservercentral.com/Forums/Topic1258889-391-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2012 at 7:52 am
Hi,
in the SSMS Go to Local server groups/New server registration/just put in the servername and connection info and you should be good to go...
Hope this help full..
February 28, 2012 at 7:56 am
Hi,
in the SSMS Go to Local server groups/New server registration/just put in the servername and connection info and you should be good to go...
Hope this help full..
February 28, 2012 at 8:26 am
As I asked in the other thread: why not using linked servers?
-- Gianluca Sartori
February 28, 2012 at 8:31 am
Gianluca Sartori (2/28/2012)
As I asked in the other thread: why not using linked servers?
Because the homework question said to not use them??? Such a strange request and twice on the same day within a couple minutes it MUST be homework.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2012 at 8:32 am
ganeshnarim (2/28/2012)
Hi,in the SSMS Go to Local server groups/New server registration/just put in the servername and connection info and you should be good to go...
Hope this help full..
Hmmmm... I already have lots instances registered. What is the T-SQL syntax to point to one of the registered instances?
February 28, 2012 at 8:34 am
Sean Lange (2/28/2012)
How completely random that this question was asked twice on the same day!?!?!?!?
That is wild. I googled a while prior to posting, but I didn't find anything useful. Then someone else posts the same question almost at the same time. LOL
February 28, 2012 at 8:38 am
Gianluca Sartori (2/28/2012)
As I asked in the other thread: why not using linked servers?
We have audit restrictions. It's just waaaaaaaay too much hassle to get permission, document what we are doing with them, audit them to death over time, etc...
On my home network, I am happy to use Linked Servers. On my work's audit-happy network, not so much. LOL
February 28, 2012 at 8:41 am
Sean Lange (2/28/2012)
Gianluca Sartori (2/28/2012)
As I asked in the other thread: why not using linked servers?Because the homework question said to not use them??? Such a strange request and twice on the same day within a couple minutes it MUST be homework.
Sean,
No, it's not homework. It's just an audit-happy work environment that I am trying to keep simple.
February 28, 2012 at 8:48 am
shew (2/28/2012)
ganeshnarim (2/28/2012)
Hi,in the SSMS Go to Local server groups/New server registration/just put in the servername and connection info and you should be good to go...
Hope this help full..
Hmmmm... I already have lots instances registered. What is the T-SQL syntax to point to one of the registered instances?
Or, are you advocating right clicking on a Local Server Group and then clicking "New Query?" What I am looking for is a way to select data in InstanceB while doing the majority of my work in InstanceA during a T-SQL session that I can run via sqlcmd.exe in a PowerShell script.
Someone referenced OPENROWSET in the similar post. (http://www.sqlservercentral.com/Forums/Topic1258889-391-1.aspx) That might work for me.
February 28, 2012 at 8:52 am
If the two database have to actually "talk" to each other, you need to use one among:
1) Linked Servers
2) Ad-hoc datasources (OPENROWSET, OPENDATASOURCE)
3) CLR procedures
4) ETL (SSIS or other similar products)
5) Custom applications
Anyway you will have to log in to the remote data source and will fall into some auditing category, so, if you need Linked Servers, which is the most sensible option IMHO, use Linked Servers and let them audit that.
-- Gianluca Sartori
February 28, 2012 at 8:55 am
shew (2/28/2012)
Sean Lange (2/28/2012)
Gianluca Sartori (2/28/2012)
As I asked in the other thread: why not using linked servers?Because the homework question said to not use them??? Such a strange request and twice on the same day within a couple minutes it MUST be homework.
Sean,
No, it's not homework. It's just an audit-happy work environment that I am trying to keep simple.
I hear ya there. Of course maybe simply explaining that you can get around their auditing for something like this might loosen the noose a little bit? Or maybe it would become even worse...at any rate the other thread has a few examples and ideas of how to get around this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2012 at 9:00 am
It turns out that OPENROWSET will not work for me:
SELECT *
FROM OPENROWSET('SQLOLEDB','Server=sql_server_network_name\instance_name;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
February 28, 2012 at 9:16 am
Gianluca Sartori (2/28/2012)
If the two database have to actually "talk" to each other, you need to use one among:1) Linked Servers
2) Ad-hoc datasources (OPENROWSET, OPENDATASOURCE)
3) CLR procedures
4) ETL (SSIS or other similar products)
5) Custom applications
Anyway you will have to log in to the remote data source and will fall into some auditing category, so, if you need Linked Servers, which is the most sensible option IMHO, use Linked Servers and let them audit that.
Unfortunately, we just have most of the "fun" features (including OPENROWSET, OPENDATASOURCE, CLR, etc.) turned off. I am a contractor, and the customer is not receptive to "opening things up."
Alternatively, I could write the small amount of data that I need from the source instance to a temporary file as SQL insert statements and use sqlcmd.exe to execute the temporary file to insert the data into a table in the destination instance. Or, I could use bcp.
I was just looking for a way to skip the intermediate temporary files.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply