executing sybase proc from sql 2008 using openquery - fails w/object has not columns

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.  
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Are you sure that the stored procedure is returning any resultset? Maybe you need to add this line at the end of your procedure
select 1
If not, try using EXEC AT like below:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If it is trying to run these statements as they are, it would fail because ';' is not default command terminator in Sybase. Have you tried it with only "EXEC BSrrcv.dbo.costplus_ledger_check '03/31/2010' "?
mprimmerAuthor Commented:
The EXEC AT does allow me to execute the proc without error.  Thank you for that!

Now I need to capture the result set and write it to a table on the server that I'm performing the EXEC AT command on.  So far, I haven't been able to find SQL syntax that will let me do that.  Do you know how I might go about doing this?

Thank you!
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mprimmerAuthor Commented:
I think this might work so I'm giving it a try in the interim:

Insert INTO ss_balance_by_account
EXEC ('execute DBSrrcv.dbo.costplus_ledger_check  ?','01/31/2010') AT smartstream60;

It errors with:
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "smartstream60" was unable to begin a distributed transaction.

so I'm checking to see if DTS is on that server.  If you have any other ideas, let me know.  I am trying to do this without having to make a change to the proc being called if at all possible.

what if you do
begin distributed transaction

Insert INTO ss_balance_by_account
EXEC ('execute DBSrrcv.dbo.costplus_ledger_check  ?','01/31/2010') AT smartstream60;


Open in new window

or maybe the problem is that sybase is not supporting the distributed transaction, you might want to check that. I'm not an expert in sybase so can't help you on that, but you might want to double check if this type of transactions is supported in your server.
If not, you could try using
sp_reconfigure remote proc trans 0
before the transaction
and then reenabling it.
sp_reconfigure remote proc trans 1
oh well, typo there
sp_configure 'remote proc trans', '0'

sp_configure 'remote proc trans', '1'
mprimmerAuthor Commented:
I ended up using a combination of solutions with this being part of it.  I think we don't have something setup correctly in our msdtc but we couldn't seem to get that worked out.  I ended up writing the results to a table on the other server and then accessing that table.  I used the exec at to run the proc and pull the results from the table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.