January 7, 2009 at 9:52 am
Hi guys,
I know openrowset is possible to query result from sp, but want I need to query is something on a linked server, when I tried to use openrowset, it returns the following error message. The remote server is not under my control, So how do I do with this need?
Thanks.
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.
January 7, 2009 at 10:18 am
Well... you could always contact the admin like the message says.....
Or, you could create a temporary table or table variable and store the output from the stored procedure there. Then query the temp table or variable.
-- must define columns to match every column returned by stored proc
declare @temp table (blah blah blah)
insert into @temp
exec spWhateverTheHeck blah, blah, blah
select blah
from @temp
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 7, 2009 at 11:35 am
You can create a linked server using that user and then use 4 part naming to execute the sp.
linkedserver.database.schema.storedprocedure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2009 at 12:11 pm
When I tried to run the sp using the 4 part naming convention, it gives me:
Msg 7411, Level 16, State 1, Line 1
Server 'CHRGBKP2NT' is not configured for RPC.
January 7, 2009 at 12:14 pm
You just need to go into your linked server properties and enable RPC and you should be all set.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2009 at 12:37 pm
Jack,
I thought Enabling RPC is a job of admin of the remote server, I didn't know it can be done on my side.
Thank you, working perfectly now.
January 7, 2009 at 12:42 pm
Great glad I could help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2009 at 1:03 pm
Hold on a second, not finished yet.
I am able to perform:
EXEC CHRGBKP2NT.CBReports.[dbo].[spBNS_MappedServerWithIPV2] '2008-08'
with enabling RPC, However, when I tried this, I still got error message:
select * into #temp from
OPENROWSET
(
'SQLOLEDB.1',
'Data Source=CHRGBKP2NT;Initial Catalog=CBReports;User ID=xxxx;Password=xxxxxxxx',
'select * from chrgbkp2nt.cbreports.dbo.vwbns_util_validperiods'
)
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.
It seems I am not allowed to do the Surface Area Configuration for the remote server.
Thanks again for the help.
PS. 'select * from chrgbkp2nt.cbreports.dbo.vwbns_util_validperiods' should actually be replaced by
EXEC CHRGBKP2NT.CBReports.[dbo].[spBNS_MappedServerWithIPV2] '2008-08', they result in the same error message. sorry for the confusion.
--Edited by Halifaxdal
January 7, 2009 at 1:14 pm
You don't need to use openrowset. You just use Bob's solution with the EXEC.
What are you doing with data after you retrieve it from the remote server? Does it have to go into a temp table? Do you have control over the stored procedure?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2009 at 1:31 pm
The sp is out of my control, it returns too many columns, I just don't want to manually create replicate those columns on my side, and I might need to do that again if they change the returned column in the result. So I just can't create my own table, it needs to go with whatever the sp returns.
I need to filter some results from my side, the remote guy can't do this right now.
January 7, 2009 at 1:53 pm
halifaxdal (1/7/2009)
The sp is out of my control, it returns too many columns, I just don't want to manually create replicate those columns on my side, and I might need to do that again if they change the returned column in the result. So I just can't create my own table, it needs to go with whatever the sp returns.I need to filter some results from my side, the remote guy can't do this right now.
Then you need to get the DBA on the other side to allow ad hoc distributed queries. They may balk at that if you are not the only user as that is a server wide setting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply