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

Posted on 2010-04-07
Medium Priority
Last Modified: 2012-05-09
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.  
Question by:mprimmer
  • 4
  • 3
LVL 41

Accepted Solution

ralmada earned 2000 total points
ID: 30041739
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:
LVL 13

Expert Comment

ID: 30050812
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' "?

Author Comment

ID: 30144257
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!
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


Author Comment

ID: 30151615
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.

LVL 41

Expert Comment

ID: 30209803
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

LVL 41

Expert Comment

ID: 30210718
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
LVL 41

Expert Comment

ID: 30210796
oh well, typo there
sp_configure 'remote proc trans', '0'

sp_configure 'remote proc trans', '1'

Author Closing Comment

ID: 32800680
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.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question