Open Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Open Query Problem

Author

Topic

JulianGill
Starting Member

3 Posts

Posted - 2011-10-07 : 03:11:15

I want to do a simple select from a stored procedure using open query using the following code (I have set up a linked server 'local')

SELECT * FROM OPENQUERY([LOCAL],'[warehouse].[wh_warehouse].[spProcessingActivityCompletedSummary]')

I am getting the following error

---------------------------------

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "[warehouse].[wh_warehouse].[spProcessingActivityCompletedSummary]". The OLE DB provider "SQLNCLI10" for linked server "LOCAL" indicates that either the object has no columns or the current user does not have permissions on that object.

--------------------------------

linked server 'local' as below, I have sysadmin privilages

/****** Object: LinkedServer [LOCAL] Script Date: 10/06/2011 17:31:26 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'LOCAL', @srvproduct=N'(local)\warehouse', @provider=N'SQLNCLI', @datasrc=N'(local)\warehouse'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LOCAL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'LOCAL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 03:43:07

does the person executing code has access to the object?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 04:12:31

Does it have a set nocount on at the top
Does the sp use temp tables? If so you might need a statement at the top to give the resultset format or to include set fmtonly off in the call (which could mean that it gets executed twice)

note you can also try
exec ('exec mydb..mysp') at myserver

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JulianGill
Starting Member

3 Posts

Posted - 2011-10-07 : 09:34:15

quote:
Originally posted by visakh16

does the person executing code has access to the object?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Executed the stored procedure succesfully using the following command, so therefore yes we have access to the object but the query is still failing

EXEC [local].Warehouse.wh_warehouse.spProcessingActivityCompletedSummary

JulianGill
Starting Member

3 Posts

Posted - 2011-10-07 : 09:50:15

quote:
Originally posted by nigelrivett

Does it have a set nocount on at the top
Does the sp use temp tables? If so you might need a statement at the top to give the resultset format or to include set fmtonly off in the call (which could mean that it gets executed twice)

note you can also try
exec ('exec mydb..mysp') at myserver

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yes it has a set nocount on at the top and uses temp tables, i have tried the set fmtonly off but it still failed.

What do you mean by 'give the resultset format', do you have an example? For reference here is the stored procedure and the query it is producing

CREATE PROCEDURE [wh_warehouse].[spProcessingActivityCompletedSummary]

AS BEGIN

SET NOCOUNT ON;

DECLARE @columns VARCHAR(8000)
,@SELECTSTMT VARCHAR(MAX)
, @COUNTER INT
, @ROWS INT
, @FROMSTATEMENT NVARCHAR(MAX)
, @MEASURE NVARCHAR(50)
, @SQL nVARCHAR(MAX)

SELECT @columns = COALESCE(@columns + ',[' + CAST(parameter AS VARCHAR)
+ ']', '[' + CAST(parameter AS VARCHAR) + ']')

FROM ( SELECT parameter
, Paramset
FROM wh_warehouse.tbparam
WHERE Paramset = 4
) params
GROUP BY parameter

IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL
DROP TABLE #temptable ;

SELECT ROW_NUMBER() OVER ( ORDER BY ID ) AS ID
, Parameter
INTO #temptable
FROM wh_warehouse.tbParam
WHERE ParamSet = 5
SET @ROWS = @@ROWCOUNT

SELECT @SELECTSTMT = COALESCE(@SELECTSTMT + ',[' + CAST(TT.Parameter+ '].['+P.PARAMETER + '] '+ 'AS'+' [' + TT.Parameter+ '_'+P.PARAMETER +']' AS VARCHAR (200))
+ '', '[' + CAST(TT.Parameter+ '].['+P.PARAMETER + '] '+ 'AS'+' [' + TT.Parameter+ '_'+P.PARAMETER+']' AS VARCHAR (200)))
FROM #temptable TT CROSS JOIN wh_warehouse.tbParam P
WHERE P.ParamSet = 4

SET @COUNTER = 0
SET @FROMSTATEMENT = 'SELECT CAS.[CASEID],'+@SELECTSTMT+'
FROM wh_zeus.vwCase Cas'

--SELECT * FROM #temptable

WHILE @COUNTER < @ROWS
BEGIN
SET @COUNTER = @COUNTER + 1

SELECT @MEASURE = Parameter
FROM #temptable
WHERE ID = @COUNTER

SET @FROMSTATEMENT = @FROMSTATEMENT +
'

LEFT JOIN(
SELECT *
FROM (SELECT caseid, ' + @measure + ', filtertype
FROM wh_warehouse.vwNBProcessingActivityCompletedSummary
) PACS

PIVOT (MAX(' + @measure + ')
FOR FilterType IN ('+@columns+')

) AS P) as ' + @measure + ' ON CAS.caseId = ' + @measure + '.caseID

';
--PRINT @FROMSTATEMENT
END

PRINT @FROMSTATEMENT

EXEC sp_executesql @FROMSTATEMENT
END

GO

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

This is the query it produces

SELECT CAS.[CASEID]
, [FirstCompletedDateTime].[ACTION-REASSIGN] AS [FirstCompletedDateTime_ACTION-REASSIGN]
, [LastCompletedDateTime].[ACTION-REASSIGN] AS [LastCompletedDateTime_ACTION-REASSIGN]
, [CountActivity].[ACTION-REASSIGN] AS [CountActivity_ACTION-REASSIGN]
, [MinsBetweenProcessingStartAndLastCompletedDateTime].[ACTION-REASSIGN] AS [MinsBetweenProcessingStartAndLastCompletedDateTime_ACTION-REASSIGN]
, [FirstCompletedDateTime].[CASE_NOTE_PRIVATE] AS [FirstCompletedDateTime_CASE_NOTE_PRIVATE]
, [LastCompletedDateTime].[CASE_NOTE_PRIVATE] AS [LastCompletedDateTime_CASE_NOTE_PRIVATE]
, [CountActivity].[CASE_NOTE_PRIVATE] AS [CountActivity_CASE_NOTE_PRIVATE]
, [MinsBetweenProcessingStartAndLastCompletedDateTime].[CASE_NOTE_PRIVATE] AS [MinsBetweenProcessingStartAndLastCompletedDateTime_CASE_NOTE_PRIVATE]
FROM wh_zeus.vwCase Cas
LEFT JOIN ( SELECT *
FROM ( SELECT caseid
, FirstCompletedDateTime
, filtertype
FROM wh_warehouse.vwNBProcessingActivityCompletedSummary
) PACS PIVOT ( MAX(FirstCompletedDateTime) FOR FilterType IN ( [ACTION-REASSIGN], [CASE_NOTE_PRIVATE] ) ) AS P
) AS FirstCompletedDateTime ON CAS.caseId = FirstCompletedDateTime.caseID
LEFT JOIN ( SELECT *
FROM ( SELECT caseid
, LastCompletedDateTime
, filtertype
FROM wh_warehouse.vwNBProcessingActivityCompletedSummary
) PACS PIVOT ( MAX(LastCompletedDateTime) FOR FilterType IN ( [ACTION-REASSIGN], [CASE_NOTE_PRIVATE] ) ) AS P
) AS LastCompletedDateTime ON CAS.caseId = LastCompletedDateTime.caseID
LEFT JOIN ( SELECT *
FROM ( SELECT caseid
, CountActivity
, filtertype
FROM wh_warehouse.vwNBProcessingActivityCompletedSummary
) PACS PIVOT ( MAX(CountActivity) FOR FilterType IN ( [ACTION-REASSIGN], [CASE_NOTE_PRIVATE] ) ) AS P
) AS CountActivity ON CAS.caseId = CountActivity.caseID
LEFT JOIN ( SELECT *
FROM ( SELECT caseid
, MinsBetweenProcessingStartAndLastCompletedDateTime
, filtertype
FROM wh_warehouse.vwNBProcessingActivityCompletedSummary
) PACS PIVOT ( MAX(MinsBetweenProcessingStartAndLastCompletedDateTime) FOR FilterType IN ( [ACTION-REASSIGN], [CASE_NOTE_PRIVATE] ) ) AS P
) AS MinsBetweenProcessingStartAndLastCompletedDateTime ON CAS.caseId = MinsBetweenProcessingStartAndLastCompletedDateTime.caseID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources