Cannot process the object

I have a job that runs every half hour.

It calls a procedure that has the following code
CREATE TABLE ' + @v_tablename  + ' (key_field INT IDENTITY, data NVARCHAR(MAX))'
EXEC sp_executesql @v_sql

SET @v_sql = '
SELECT * INTO ' + @v_tablename + '1' + '
FROM OPENQUERY(' + @v_connectionstring + ',
''SET FMTONLY on {call [' + @v_database_name + '].' + @v_name + '}'')'
EXEC sp_executesql @v_sql

The procedure runs fine most of the time, but every once in a while it'll error out with the following error message:

Cannot process the object 'SET FMTONLY ON {call [XXX].dbo.sp_XXX}'
The OLE DB provider "SQLNCLI" for linked server "xx" indicates that
either the object has no columns or the current user
does not have permissions on that object. [SQLSTATE 42000] (Error 7357).  
The step failed.

Like I said, the job runs fine most of the time, my Ad Hoc Distributed Queries is enabled.
Any ideas why it doesn't work sometimes?


DB_GIRLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DBAduck - Ben MillerPrincipal ConsultantCommented:
Well, you are using ODBC calling style with SQLNCLI or SQLOLEDB, and that may cause some heartburn for your process.

you should just use

SET FMTONLY ON EXEC [XXX].dbo.[sp_XXX]

0
DB_GIRLAuthor Commented:
It seems like other people have similar issue. I was able to resolve the issue by setting the retrys for the job. It seems that there is a connectivity issue every once in a while (job fails) but it gets fixed on its own in a second. So I set my job to retry in a minute, and it never reported failure after that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.