?
Solved

Cannot process the object

Posted on 2008-12-23
2
Medium Priority
?
1,338 Views
Last Modified: 2012-05-06
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?


0
Comment
Question by:DB_GIRL
2 Comments
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 23258616
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
 

Accepted Solution

by:
DB_GIRL earned 0 total points
ID: 23554800
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question