Executing Procedures through LInked Server

I'm trying to execute a procedure through linked server using 4 part names.
The Linked server is configured for PRC and RPCout .
Still I get this message... What Am I Missing...
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
Configuring a server for remote access and RPC are two
different things. You can configure a server to allow remote
access with sp_configure -
EXEC sp_configure 'remote access', 1
RECONFIGURE
-Sue
On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:

>I'm trying to execute a procedure through linked server using 4 part names.
>The Linked server is configured for PRC and RPCout .
>Still I get this message... What Am I Missing...
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
|||I have already configured the other server for remote access and reconfigured
with override option.
Still I get the message
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
All I want to do is
Execute a Proc -P Sitting on Machine A from Machine B.
"Sue Hoegemeier" wrote:

> Configuring a server for remote access and RPC are two
> different things. You can configure a server to allow remote
> access with sp_configure -
> EXEC sp_configure 'remote access', 1
> RECONFIGURE
> -Sue
> On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>
|||Also try executing:
sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
-Sue
On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have already configured the other server for remote access and reconfigured
>with override option.
>Still I get the message
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
>All I want to do is
> Execute a Proc -P Sitting on Machine A from Machine B.
>
>"Sue Hoegemeier" wrote:
|||Does not work .
"Sue Hoegemeier" wrote:

> Also try executing:
> sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
> -Sue
> On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>
|||Sorry, don't know what else to tell you - your missing one
of those settings on one of the server though. That's how
you get the error.
Double check all settings you thought were enabled - RPC,
remote access, data access.
-Sue
On Tue, 4 Oct 2005 10:45:08 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Does not work .
>"Sue Hoegemeier" wrote:
sql

Executing Procedures through LInked Server

I'm trying to execute a procedure through linked server using 4 part names.
The Linked server is configured for PRC and RPCout .
Still I get this message... What Am I Missing...
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.Configuring a server for remote access and RPC are two
different things. You can configure a server to allow remote
access with sp_configure -
EXEC sp_configure 'remote access', 1
RECONFIGURE
-Sue
On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:

>I'm trying to execute a procedure through linked server using 4 part names.
>The Linked server is configured for PRC and RPCout .
>Still I get this message... What Am I Missing...
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.|||I have already configured the other server for remote access and reconfigure
d
with override option.
Still I get the message
Could not execute procedure on remote server because SQL Server is not
configured for remote access. Ask your system administrator to reconfigure
SQL Server to allow remote access.
All I want to do is
Execute a Proc -P Sitting on Machine A from Machine B.
"Sue Hoegemeier" wrote:

> Configuring a server for remote access and RPC are two
> different things. You can configure a server to allow remote
> access with sp_configure -
> EXEC sp_configure 'remote access', 1
> RECONFIGURE
> -Sue
> On Thu, 29 Sep 2005 11:26:05 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>|||Also try executing:
sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
-Sue
On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I have already configured the other server for remote access and reconfigur
ed
>with override option.
>Still I get the message
>Could not execute procedure on remote server because SQL Server is not
>configured for remote access. Ask your system administrator to reconfigure
>SQL Server to allow remote access.
>All I want to do is
> Execute a Proc -P Sitting on Machine A from Machine B.
>
>"Sue Hoegemeier" wrote:
>|||Does not work .
"Sue Hoegemeier" wrote:

> Also try executing:
> sp_serveroption 'YourLinkedServer', 'data access', 'TRUE'
> -Sue
> On Mon, 3 Oct 2005 13:46:09 -0700, Rajesh Padmanabhan
> <RajeshPadmanabhan@.discussions.microsoft.com> wrote:
>
>|||Sorry, don't know what else to tell you - your missing one
of those settings on one of the server though. That's how
you get the error.
Double check all settings you thought were enabled - RPC,
remote access, data access.
-Sue
On Tue, 4 Oct 2005 10:45:08 -0700, Rajesh Padmanabhan
<RajeshPadmanabhan@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Does not work .
>"Sue Hoegemeier" wrote:
>

Executing Oracle Stored Procedure via SQL Server 2000 Linked Serve

All,
I am trying to execute a simple Oracle stored procedure from SQL Server
2000. The SP has no input or output parameters. I have created the linked
server correctly as I am able to execute Oracle functions just fine. I have
tried the following two methods to execute the SP, SKELETON:
{Call ORADB..MARCO.SKELETON()}
Execute ORADB..MARCO.SKELETON
Both methods result in the same error listed below:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SKELETON' on remote server 'ORADB'.
[OLE/DB provider returned message: One or more errors occurred during
processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Any ideas on how I can get this working properly?
Thanks,
MarcoHere is an old post by Umachandar. See if it helps:
http://tinyurl.com/7dxrr
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
> All,
> I am trying to execute a simple Oracle stored procedure from SQL Server
> 2000. The SP has no input or output parameters. I have created the
> linked
> server correctly as I am able to execute Oracle functions just fine. I
> have
> tried the following two methods to execute the SP, SKELETON:
> {Call ORADB..MARCO.SKELETON()}
> Execute ORADB..MARCO.SKELETON
> Both methods result in the same error listed below:
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'SKELETON' on remote server 'ORADB'.
> [OLE/DB provider returned message: One or more errors occurred during
> processing of command.]
> [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
>
> Any ideas on how I can get this working properly?
> Thanks,
> Marco
>

Executing Oracle Stored Procedure via SQL Server 2000 Linked S

Thanks for the link. I have a stored procedure in Oracle that was created a
long time ago by someone else. Without manipulating the stored proc at all,
I wanted to feed it the required input parameters via SQL Server and let it
do its magic. From what I gather, this is not possible? I must create the
proc within a package in Oracle?
"oj" wrote:

> Here is an old post by Umachandar. See if it helps:
> http://tinyurl.com/7dxrr
> --
> -oj
>
> "marco" <marco@.discussions.microsoft.com> wrote in message
> news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
>
>Yes. Creating a package wrapper is your ticket to get to oracle proc.
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:4653000F-F545-4044-9876-E113CD14AFE6@.microsoft.com...
> Thanks for the link. I have a stored procedure in Oracle that was created
> a
> long time ago by someone else. Without manipulating the stored proc at
> all,
> I wanted to feed it the required input parameters via SQL Server and let
> it
> do its magic. From what I gather, this is not possible? I must create
> the
> proc within a package in Oracle?
> "oj" wrote:
>

executing OPENQUERY

Hi,

I'm trying to execute an openquery statement from SQL Server 2005 against a linked server AS2005 (both on same machine) from Management Studio.

If I use a one connection with full rights I have no problem. But if I use the specific connection for that application I got this error:

OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2" returned message "The following system error occurred: A specified logon session does not exist. It may already have been terminated. .".

Msg 7303, Level 16, State 1, Line 5

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2".

"Ad hoc data-mining query" is checked.

MSOLAP Provider has "Allow inprocess" checked.

Code Snippet

SELECT * FROM OPENQUERY(ASLOCAL2,'SELECT non empty {[Measures].[Prices Avg] ,[Measures].[Prices Max] ,[Measures].[Prices Min] } ON COLUMNS,non empty [Tbl DW Dim Type].[Name].&[1] ON ROWS FROM [DW DEV] ')

Any ideas ?

Thnx.

Have you tried running a profiler trace against SSAS while trying to run the OPENQUERY() ?

I am wondering if it is a permissions issue for the account that the application is using for the connection.

|||The only difference between these tow accounts is that one account is Windows account and the other one is Sql server account.|||

Well that is probably your issue. SSAS only supports windows authentication. You would need to have the linked server setup to authenticate using the current login's security context, which works fine for windows accounts, but if you use a sql account, SQL Server will fall back to authenticating against the SSAS server using the account that the SQL Server is running under, which probably does not have permissions to query the cubes.

So your choices are to either only use windows accounts against the linked server or to make sure that the account that SQL Server is running under has access to the appropriate objects in SSAS.

|||

You're right.

I changed the way that the (web)application is connecting to the Sql server (until now I used sql account) and now I am using an windows account.

Executing an insert in a linked server.

Hi:
Im performing an insert in a table that has a trigger.
The propossal of this trigger is to keep update two
tables when an insert occurss in server A; it starts a
trigger to insert a row in one table in server B
The servers are linked and the user has permission to
perform insert in the remote server. Im getting the
following error:
Unable to start a nested transaction for OLE DB
provider 'SQLOLEDB'. A nested transaction was required
because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more
transactions on this session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionLocal::StartTransaction returned 0x8004d013:
ISOLEVEL=4096].
Can anybody helpme'
Regards"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
news:09fa01c38c59$79adb0f0$a101280a@.phx.gbl...
> Hi:
> Im performing an insert in a table that has a trigger.
> The propossal of this trigger is to keep update two
> tables when an insert occurss in server A; it starts a
> trigger to insert a row in one table in server B
> The servers are linked and the user has permission to
> perform insert in the remote server. Im getting the
> following error:
> Unable to start a nested transaction for OLE DB
> provider 'SQLOLEDB'. A nested transaction was required
> because the XACT_ABORT option was set to OFF.
That is - set XACT_ABORT ON at begining of your transaction
> [OLE/DB provider returned message: Cannot start more
> transactions on this session.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionLocal::StartTransaction returned 0x8004d013:
> ISOLEVEL=4096].
>
> Can anybody helpme'
> Regards|||Regards, I appreciate your help
>--Original Message--
>"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
>news:09fa01c38c59$79adb0f0$a101280a@.phx.gbl...
>> Hi:
>> Im performing an insert in a table that has a trigger.
>> The propossal of this trigger is to keep update two
>> tables when an insert occurss in server A; it starts a
>> trigger to insert a row in one table in server B
>> The servers are linked and the user has permission to
>> perform insert in the remote server. Im getting the
>> following error:
>> Unable to start a nested transaction for OLE DB
>> provider 'SQLOLEDB'. A nested transaction was required
>> because the XACT_ABORT option was set to OFF.
>That is - set XACT_ABORT ON at begining of your
transaction
>> [OLE/DB provider returned message: Cannot start more
>> transactions on this session.]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionLocal::StartTransaction returned
0x8004d013:
>> ISOLEVEL=4096].
>>
>> Can anybody helpme'
>> Regards
>
>.
>

Executing a stored procedure that uses linked server from vb.NET

I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikaelbermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?
> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>> How long does it take to run in QA, and how long is your timeout set for
>> in .NET?
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> > I'm no .NET programmer, but with typical web apps you have a CONNECTION
> > timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> > So, in a nutshell, if your query takes longer than 30 seconds, the web
> > connection is going to give up and report a timeout.
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)

Executing a stored procedure that uses linked server from vb.NET

bermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?
> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?

> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||
> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)|||I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikael|||bermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||
> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?

> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||
> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M

ExecuteQuery taking a long time

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup
Try using a stored procedure if you can (dont know if you can do that in JDBC )|||

Hello Arup,

I would agree that the best way to solve long running query executions is to use a stored procedure which does all the processing of the data on the server and only return to the client the necessary amount of processed data via output parameters. You can use the CallableStatement API provided by the JDBC driver for doing this.

That being said, the Microsoft SQL Server 2005 v1.2 JDBC driver includes a critical 'feature' called "Adaptive Response Buffering" which allow developers to retrieve large amounts of data. The lengthy latency you are encountering is not because of the query execution, but because of the time it takes the driver to spool the data on the client machine before control is given back to the application.

You can ready more about "Adaptive Reponse Buffering" at http://blogs.msdn.com/jdbcteam/archive/2007/05/02/what-is-adaptive-response-buffering-and-why-should-i-use-it.aspx. This functionality if available in the V1.2 CTP1 driver which has the version 1.2.2323.101. You can download it at http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en.

We appreciate your feedback using the newly added functionality of this driver.

Kind Regards,

Jaaved

Execute SP on linked server?

I've created a stored procedure on several servers. All with the same name and all in a database with same names.
Now Im trying to execute the stored procedures by executing a script... I dont get it to work!
I can execute it successfully by starting it manually:
exec [CIMPDB01\CIMT1].zz_am.dbo.usp_SpaceMon
exec [CIMPDB01\CIMT2].zz_am.dbo.usp_SpaceMon
and so on.... no problem

But when I try to execute it by using a script (and substitute the instance names with values taken from a table) it wont:

declare @.x int
declare @.dbname varchar(500)
declare @.SQL nvarchar(600)
set @.x = 1

create table #databases
(ID int IDENTITY,name varchar(500))

insert #databases select instancelongname from instances where actief='J'

while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
print @.dbname
select @.SQL='exec ' + @.dbname + '.zz_am.dbo.usp_SpaceMon'
print @.SQL
execute @.SQL

set @.x = @.x + 1
end

drop table #databases

Msg 203, Level 16, State 2, Line 17
The name 'exec [CIMPDB01\CIM].zz_am.dbo.usp_SpaceMon' is not a valid identifier.

Please help...declare @.x int
declare @.dbname varchar(500)
declare @.SQL nvarchar(600)
set @.x = 1

create table #databases
(ID int IDENTITY,name varchar(500))

insert #databases select instancelongname from instances where actief='J'

while @.x <= (select max(id) from #databases)
begin
select @.dbname = name from #databases where id = @.x
print @.dbname
select @.SQL= @.dbname + '.zz_am.dbo.usp_SpaceMon'
print @.SQL
execute @.SQL

set @.x = @.x + 1
end

drop table #databases

Don't need to use 'exec'.

Hope this helps,

David

execute remote stored procedure failed

I have set up a linked server to Sybase. I can run
distributed queries, but I'm trying to execute a stored
procedure on the remote server and I receive the
following message:
Error 7212: Could not execute procedure on remote server.
Any ideas as to what I'm doing wrong?
Thanks,
MTMT
Perhaps you want to look into permission issues
"MT" <anonymous@.discussions.microsoft.com> wrote in message
news:0d4b01c39e26$7ddf7c10$a301280a@.phx.gbl...
> I have set up a linked server to Sybase. I can run
> distributed queries, but I'm trying to execute a stored
> procedure on the remote server and I receive the
> following message:
> Error 7212: Could not execute procedure on remote server.
> Any ideas as to what I'm doing wrong?
> Thanks,
> MT
>
>
>|||Was there ever a satisfactory solution to this? I am having very similar problems when using a Sybase OLE DB linked server. No such problems when using an ODBC link
-- MT wrote: --
I have set up a linked server to Sybase. I can run
distributed queries, but I'm trying to execute a stored
procedure on the remote server and I receive the
following message
Error 7212: Could not execute procedure on remote server
Any ideas as to what I'm doing wrong
Thanks
M

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can
you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...[vbcol
=seagreen]
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>[/vbcol]|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it
interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I d
on't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand wh
at you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...en">
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
Jack
We need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>
|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I don't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand what you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>

execute DBCC on linked server

Hi,

How do you execute 'DBCC' statements or 'EXEC ...' against a linked server (SQL 2000 sp4) ?

Regards,

A.E

EXEC [linkedserver].master.dbo.sp_executesql N'DBCC USEROPTIONS'

|||

Thanks Mark, your suggestion works fine but now I have another problem when I do the following for example:

INSERT [sometable] EXEC [LinkedServer].pubs.dbo.sp_executesql N'EXEC sp_helpfile'

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
The transaction active in this session has been committed or aborted by another session.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I followed the instructions in article 839279 but still get the error, any ideas?. I don't need DTC transaction support, is there a way to get the results without involving MSDTC ?

Regards,

A.E

|||

If you've tried everything in

http://support.microsoft.com/kb/839279

then there's not a lot more I can add.

BTW, the last time I saw this, it was fixed simply by specifying
SET XACT_ABORT ON before the EXEC.