February 2, 2015 at 1:38 pm
Hi all,
I have a procedure and part of it is the following query (from sql server 2K8R2 to mysql)
exec('
select a, b, c
from openquery (REM_SERV,''select a,b,c from X, Y, Z'') a
left join T b on b.pkey = a.pkey
')
When I run this proc, I am receiving: SQLSTATE 01000] (Message 15477) Cannot process the object "select ......"
If I take and try to run just exec (...) part of it I am receiving: The OLE DB provider "MSDASQL" for linked server "REM_SERVER" indicates that either the object has no columns or the current user does not have permissions on that object.
I tried to find a solution and ran across people recommending to place SET NOCOUNT ON; in front of select ... statement, but it did not work for me.
What else can I do and why am I getting this error in a first place as this same proc used to run just fine before?
Thanks,
February 10, 2015 at 7:49 pm
Break down the query and start answering what is and is not working.
select a, b, c
from openquery (REM_SERV,'select a,b,c from X, Y, Z') ;
Does this Execute?
select a, b, c
from openquery (REM_SERV,'select a,b,c from X, Y, Z') a
left join T b on b.pkey = a.pkey
How about this?
I may be mistaken but I believe you only need single quotes on the query itself. It is when you are using string literals or variables that you need to start using double single quotes. This is my first answer ever on this site so cut me some slack. 🙂
February 25, 2015 at 4:38 am
Ted's approach will help you figure out what part of the code has the problem and I was taught this same approach to troubleshooting query errors. It has been very helpful for me to tear the query apart and rebuild it piece by piece until the error happens.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply