mprimmer
asked on
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,''
EXEC(@SQL)
but it is failing with the following error.
Cannot process the object "SET NOCOUNT ON; SET FMTONLY OFF; EXEC DBSrrcv.dbo.costplus_ledge
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.
SELECT @SQL = 'SELECT * FROM OPENQUERY(smartstream60,''
EXEC(@SQL)
but it is failing with the following error.
Cannot process the object "SET NOCOUNT ON; SET FMTONLY OFF; EXEC DBSrrcv.dbo.costplus_ledge
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
alpmoon
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
ASKER
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!
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!
ASKER
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_ledge
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.
Thanks!
Insert INTO ss_balance_by_account
EXEC ('execute DBSrrcv.dbo.costplus_ledge
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.
Thanks!
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;
commit
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
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'
and
sp_configure 'remote proc trans', '1'
sp_configure 'remote proc trans', '0'
and
sp_configure 'remote proc trans', '1'
ASKER
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.