Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Access table from different instance Expand / Collapse
Author
Message
Posted Friday, December 14, 2012 11:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 25, 2016 4:00 AM
Points: 218, Visits: 1,119
hello experts,
i want to write a select query which will access table from different instance, but cant create linked server on tht server bcos thts a production server.

please advice

thanks
Post #1396765
Posted Friday, December 14, 2012 11:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 15, 2016 2:34 PM
Points: 2,599, Visits: 3,382
Not sure if using OPENROWSET would help. Might depend on security settings. Look it up in Books Online (aka BOL) and see if that will work...


Steve
(aka sgmunson)

Health & Nutrition
Post #1396770
Posted Friday, December 14, 2012 12:06 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 25, 2016 4:00 AM
Points: 218, Visits: 1,119
i believe openrowset still require linked server name to pass.

dirent server name wont work in tht right?
Post #1396776
Posted Friday, December 14, 2012 12:17 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 15, 2016 2:34 PM
Points: 2,599, Visits: 3,382
That's why I suggested you look at BOL, as I can't recall the exact syntax, but I do know that OPENQUERY has to have a LINKED SERVER NAME, while OPENROWSET uses a connection string. I just don't know if using OPENROWSET can be locked down so that no temporary linked servers can be created via OPENROWSET.


Steve
(aka sgmunson)

Health & Nutrition
Post #1396781
Posted Friday, December 14, 2012 12:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 25, 2016 4:00 AM
Points: 218, Visits: 1,119
ok thanks a ton for your help
Post #1396782
Posted Sunday, December 16, 2012 6:04 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:30 PM
Points: 542, Visits: 3,021
Zeal-DBA (12/14/2012)
ok thanks a ton for your help


Did openrowset works for you?....I am curious.

SueTons.


Regards,
SQLisAwe5oMe.
Post #1397043
Posted Wednesday, December 19, 2012 2:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 25, 2016 4:00 AM
Points: 218, Visits: 1,119
yeah OPENROWSET works, it requires connection string, it does'nt require linked server to configure, but "ad hoc distributed queries" should be enabled through sp_configure.....

thank you so much
Post #1398242
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse