Run openrowset with sp_add_jobschedule with error Msg 7357, but sproc still works

Run openrowset with sp_add_jobschedule with error Msg 7357, but sproc still works by Chjquest

Closed

Type:	Bug
ID:	778984
Opened:	2/11/2013 10:42:25 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When I run the following script,
----
exec sp_configure 'show advanced options', 1;
reconfigure;

exec sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure;

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=servername;Database=msdb;Uid=user;Pwd=pass;', 'SET FMTONLY OFF; EXEC sp_add_jobschedule @job_name = ''job_name'', @name = ''name'', @freq_type = 1, @freq_interval = 0, @active_start_date = 20130101, @active_start_time = 10000')
----

I got this error message,

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SET FMTONLY OFF; EXEC sp_add_jobschedule @job_name = 'job_name', @name = 'name', @freq_type = 1, @freq_interval = 0, @active_start_date = 20130101, @active_start_time = 10000". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

But, the stored procedure actually works. When I use the same way just change sp_add_jobschedule top sp_help_jobschedule, I see the job just added.

The error message should not display.

The SQL Server is 2008 R2 Enterprise Edition 10.50.4263 x64

Details (expand)

English

SQL Server 2008 R2 SP2 CTP

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows Server 2008 R2 Standard

English

Compare the following 2 scripts,

exec sp_configure 'show advanced options', 1;
reconfigure;

exec sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure;

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=servername;Database=msdb;Uid=user;Pwd=pass;', 'SET FMTONLY OFF; EXEC sp_add_jobschedule @job_name = ''job_name'', @name = ''name'', @freq_type = 1, @freq_interval = 0, @active_start_date = 20130101, @active_start_time = 10000')

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=servername;Database=msdb;Uid=user;Pwd=pass;', 'SET FMTONLY OFF; EXEC sp_help_jobschedule @job_name = ''job_name''')

exec sp_configure 'Ad Hoc Distributed Queries', 0;
reconfigure;

exec sp_configure 'show advanced options', 0;
reconfigure;

Messages:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SET FMTONLY OFF; EXEC sp_add_jobschedule @job_name = 'job_name', @name = 'name', @freq_type = 1, @freq_interval = 0, @active_start_date = 20130101, @active_start_time = 10000". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

The expected message should be:

Command(s) completed successfully.

X64

0 attachments

Posted by Microsoft on 3/26/2013 at 2:19 PM

Hello Chjquest. The correct URL for the original item is as follows: https://connect.microsoft.com/SQLServer/feedback/details/737341 Investigations also suggest that the error comes from using OPENROWSET, seeking to return rows, in conjunction with sp_add_jobschedule, offering to return a binary. Please let us know if you have any questions. -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)

Posted by Microsoft on 3/21/2013 at 5:22 PM

Hello Chjquest. Let me investigate and see what happened, here. I may have crossed some wires, here. Thanks for correcting me. -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)

Posted by Chjquest on 3/21/2013 at 1:40 PM

I go to the link
https://connect.microsoft.com/SQLServer/feedback/details/920970
The link is no longer available. Here is the message:

Page Not Found

The content that you requested cannot be found or you do not have permission to view it.

If you believe you have reached this page in error, click the Help link at the top of the page to report the issue and include this ID in your e-mail: dcda860f-c819-427e-a360-9fba5fae4a59

Microsoft Connect welcome page.

Posted by Microsoft on 3/7/2013 at 10:30 AM

Hello Chjquest. This issue has been tracked in a different connect item. Please look for updates here: https://connect.microsoft.com/SQLServer/feedback/details/920970 Please let us know if you have any questions. -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)

Posted by Microsoft on 3/7/2013 at 10:30 AM