execute stored procedure in remote server

execute stored procedure in remote server

  
Hello,
How can I execute a stored procedure in a remote server using an advanced query?

Best Regards,
Crisna Silva
Hi Crisna, 

to execute a stored procedure in a remote server, you must create a linked server between your outsystems database and the remote one. Remember to set the right privileges in the remote database user.

Cheers, 
RNA
Hello Ricardo,
Thanks for your answer. I tried that and now I have this error "The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "serverName" was unable to begin a distributed transaction."
 In the Local DTC properties I set the "Network DTC Access" to "Enable" and in 
Transaction Manager Communication I set "Allow Inbound" and "Allow OutBound" to "Enable" too, but it still doesn't work. Any suggestions?

Regards,
Crisna Silva
Are you trying by any chance connecting a SQL 2005 to a SQL 2008 server?

Best regards,
PC
yes, I'm trying to connect a 2005 to a 2008...
Well that is the problem right there

Technicaly it's not possible to do it...I had this problem, Microsoft support was envolved and in the end they told me to upgrade the 2005 instance to 2008 because it couldn't be done

Sorry to be the bearer of bad news, but you have to think of another solution for that problem...

Best regards,
PC
ok :(
thanks 

Regards
CS
Hi Pedro,
I made an  upgrade from 2005 instance to 2008 but I still have the same error "The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "serverName" was unable to begin a distributed transaction."

Regards,
CS
So now you have 2 SQL 2008 trying to communicating and failing right?

Are they in the same domain? Are any of them VM's? The DB User used to create the linked server has permissions to execute the store procedure in the destination server?

Best regards,
PC
yes, I have 2 sql 2008. They are in the same domain. the bd user has db_owner role.

regards
CS
Can you check if the MSDTC is setup properly? Here is a link for you to check.

Best regards,
PC
 Hi,
I have the same configurations on my MSDTC but I don't have the option "Enable  SNA LU 6.2 Transactions".
Can you do something like they mention in the top of the post, directly from SQL Management Studio from the server that is making the request? Something like:

BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM <Linked server>.<Database>.<schema>.<table>

Best regards,
PC
BTW I found a post (need to get the link from history) that would mention if the machines were virtual environments, and most of all created from the same virtual machine skeleton, they would have a problem with MSDTC, but the solution was simple, just uninstall and reinstall MSDTC

*Edit* Found the link.

Best regards,
PC
I got this:
OLE DB provider "SQLNCLI10" for linked server "server" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "server" was unable to begin a distributed transaction.
I still have the same problem after re-install MSDTC..
There's a way to execute a stored procedure in a remote server without using a linked server? I'm using a Database connection to access the tables and views in the database in this remote server and it works perfectly... the problem is only int this stored procedure execution :S

I have this string in an advanced query exec [serverName].[databaseName].[dbo].[storedProcedureName] @Param1, @Param2, @Param3 .