I'm attempting to leverage a linked server entry and exec an existing proc on a sybase database from sql2008.
SELECT @SQL = 'SELECT * FROM OPENQUERY(smartstream60,''SET NOCOUNT ON; SET FMTONLY OFF; EXEC DBSrrcv.dbo.costplus_ledger_check ''''' + @statement_dt_char + ''''' '')'
but it is failing with the following error.
Cannot process the object "SET NOCOUNT ON; SET FMTONLY OFF; EXEC DBSrrcv.dbo.costplus_ledger_check '03/31/2010' ". The OLE DB provider "MSDASQL" for linked server "smartstream60" indicates that either the object has no columns or the current user does not have permissions on that object.
I believe the permissions to be correct as I can execute the proc from crystal using the same userid. I can also execute other queries (non-proc queries) successfully using the linked server entry. The dba has assured me the permissions are in place for executing a proc using the linked server entry but I have no other proc to execute so I can't prove it. My research (aka googling :-) ) has brought me potentially to a complexity issue with the proc which isn't all that complex but it does use #temp tables. We thought that the SET FMTONLY OFF would do the trick but to no avail. Any ideas of other things I can try? We did try an OPENRowset approach but "The OLE DB provider "SYBASE ASE ODBC Driver" has not been registered." so we have not progressed there.
Your help would be greatly appreciated.