question

bonskijr avatar image
bonskijr asked

What's the possible reason why OpenQuery is not working

What are the possible reason that an openquery stops functioning: INSERT INTO #someTempTable SELECT * FROM OPENQUERY(SOMELINKEDSERVER, "exec DB.dbo.uspProc") -- or even this line doesnt work. While fully qualified linked servers like: INSERT INTO #someTempTable exec SOMELINKEDSERVER.DB.dbo.uspProc works? **Updated**: fully qualified on the OpenQuery as well, what's odd is that was working previously. Now it only works w/ fully qualified execution.
linked-serveropenquery
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
What is your server version and what is the target (linked) server?
0 Likes 0 ·

2 Answers

·
Kev Riley avatar image
Kev Riley answered
Have you tried fully qualifying the procedure call? SELECT * FROM OPENQUERY(SOMELINKEDSERVER, "exec DB.dbo.uspProc")
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
gah - must type faster.....
0 Likes 0 ·
bonskijr avatar image bonskijr commented ·
yep, sorry it's already fully qualified as well. edited the code. thanks
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
You say that the query doesn't work - what error are you getting, if any? Looking at your example, your OPENQUERY doesn't seem to be qualifying the database and schema, where the linked server version does.
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bonskijr avatar image bonskijr commented ·
updated the code.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@bonskijr : and what is the error?
0 Likes 0 ·
bonskijr avatar image bonskijr commented ·
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.