Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Friday, October 17, 2014 4:05 AM
Points: 208, Visits: 1,031
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 1,669, Visits: 2,220
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)

Internet ATM Machine
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: Friday, October 17, 2014 4:05 AM
Points: 208, Visits: 1,031
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 1,669, Visits: 2,220
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)

Internet ATM Machine
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: Friday, October 17, 2014 4:05 AM
Points: 208, Visits: 1,031
ok thanks a ton for your help
Post #1396782
Posted Sunday, December 16, 2012 6:04 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:12 AM
Points: 422, Visits: 2,645
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: Friday, October 17, 2014 4:05 AM
Points: 208, Visits: 1,031
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