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.