Cannot process the object "SET FMTONLY ON EXEC DBName..StoredProc". The OLE DB provide

I am getting an error when creating MyView:
Cannot process the object "SET FMTONLY ON EXEC DBName..StoredProc". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

MyView is accessing a stored procedure as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create VIEW [dbo].[MyView]
AS
SELECT * FROM
OPENROWSET ( 'SQLOLEDB', 'SERVER=.;Trusted_Connection=Yes',
'SET FMTONLY ON EXEC DBName..StoredProc' )
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

StoredProc has a dynamically created synonym to avoid conflicts as follows:

declare @.DB as varchar(25)
SET @.DB = db_name()
DECLARE @.TblName as varchar(50)
DECLARE @.cx as varchar(100)
SET @.TblName = 'MyFunction'
declare @.DySynName as varchar(200)
declare @.intI as int

SELECT @.DySynName = 'dsicx' + @.TableName + '_' + system_user + '_' + CONVERT(char(12), GETDATE(), 14)

- The following while loop strips off the colon
SELECT @.intI = charindex(':',@.DySynName)
WHILE @.intI > 1
BEGIN
SELECT @.DySynName = substring(@.DySynName,1,@.intI - 1) +
substring(@.DySynName,@.intI + 1, LEN(@.DySynName)-@.intI )
SELECT @.intI = charindex(':',@.DySynName)
END

SET @.cx= substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))

EXEC ('CREATE SYNONYM '+@.DySynName +' FOR '+@.cx+'..'+@.TblName+'

select *
from '+@.DySynName +'('''+@.DB+''', 5, 0)

drop synonym '+@.DySynName )

The StoredProc procedure works fine on it's own, and the View works fine if I use a static name instead of @.DySynName when creating, using and dropping the synonym.
MyView will work with the StoredProc procedure if I use @.DySynName for the syonym but I have to hard code the login and password in the OPENROWSET function instead of using Trusted_Connection=Yes, since multiple users will be accessing MyView a hard coded login and password will not suffice.

Is there something I can use other than Trusted_Connection=Yes that will allow me to use the @.DySynName variable for my synonym? Any ideas would be greatly appreciated, please keep in mind that I cannot exclude the dynamic synonym name and I cannot use temporary tables or create and delete the view upon use because of the load on the system.

Use following statements, when you create View:

Code Snippet

CREATE VIEW [dbo].[MyView]

AS

SELECT * FROM

OPENROWSET ( 'SQLOLEDB', 'SERVER=.;Trusted_Connection=Yes',

'SET FMTONLY ON; EXEC DBName..StoredProc' )

GO

I just add semicolon and SQL Server execute "SET FMTONLY ON; EXEC DBName..StoredProc" as two statements

|||Still no luck, thanks for trying though.

Cannot obtain recordset from stored procedure that contains Exec c

Hopefully someone can help. Using vbscript and ADO, I can call a very simpl
e
stored procedure that returns a recordset w/o any problems. However, when I
call a stored procedure that stuffs records into another table using a SQL
string built and the Exec command and then select the records from that
table--ADO always reports the recordset is closed.
The stored procedure works fine in Query Analyzer. It also doesn't matter
if table is temp or permanent. My workaround is to first call the stored
procedure followed by a second call to get the records. My goal is to use a
temp table in the stored procedure.
Psuedo code of stored procedure (which also uses cursor):
declare @.sqlstr varchar (100)
set @.sqlstr = 'insert into tmptable select field from anothertable'
exec(@.sqpstr)
select * from tmptableAre you setting SET NOCOUNT ON at the begining of the sp?
AMB
"reedholm" wrote:

> Hopefully someone can help. Using vbscript and ADO, I can call a very sim
ple
> stored procedure that returns a recordset w/o any problems. However, when
I
> call a stored procedure that stuffs records into another table using a SQL
> string built and the Exec command and then select the records from that
> table--ADO always reports the recordset is closed.
> The stored procedure works fine in Query Analyzer. It also doesn't matter
> if table is temp or permanent. My workaround is to first call the stored
> procedure followed by a second call to get the records. My goal is to use
a
> temp table in the stored procedure.
> Psuedo code of stored procedure (which also uses cursor):
> declare @.sqlstr varchar (100)
> set @.sqlstr = 'insert into tmptable select field from anothertable'
> exec(@.sqpstr)
> select * from tmptable
>