Using openrowset to execute procedure

[simon]

I have procedure on SQL2008 R2, which has one update statement:

CREATE PROCEDURE dbo.test
AS
SELECT 1 --because of openrowset

UPDATE table set daeOfExecute=getdate()

If I execute this procedure on this server, the update works.
EXEC dbo.test

If I execute this procedure on sql2000 server, using openrowset,
update works:
SELECT a.* FROM
OPENROWSET('SQLOLEDB','serverIP';'user';'password','exec
dbName.dbo.test')a
I get 1 as result.

If I execute the same on SQL2008 server, than I get 1 as result but
nothing happened. The update is not done.
There is no error message.
Is there some setting on SQL2008 different than on SQL2000 or
something similar. Any idea?

Thanks,
Simon

[Gert-Jan Strik]

With respect to this, nothing has changed from SQL Server 2000 to 2008.

However, I am guessing that this is not your actual statement, because
"table" is a reserved word. If your table name is really "table", then
you need to use double quotes or square brackets around the table name
to indicate that it is an object reference and not a keyword.

If "table" is not the name of the table, and you need more help, then
please post the entire query.
--
Gert-Jan

[Erland Sommarskog]

simon (zupa...@gmail.com) writes:
> If I execute this procedure on sql2000 server, using openrowset,
> update works:
> SELECT a.* FROM
> OPENROWSET('SQLOLEDB','serverIP';'user';'password','exec
> dbName.dbo.test')a
> I get 1 as result.
>
> If I execute the same on SQL2008 server, than I get 1 as result but
> nothing happened. The update is not done.
> There is no error message.
> Is there some setting on SQL2008 different than on SQL2000 or
> something similar. Any idea?

What's wrong with just setting up a linked server, and then say

EXEC THATSERVER.dbName.dbo.test

OPENROWSET is not meant for UPDATE actions, so if it doesn't work, it
doesn't work.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[simon]

On Jun 21, 9:55 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:

Hi Erland,

everything had worked until we moved to sql2008.

(Gert, table names and db names are fictitious)

I use OPENROWSET command from server1 (S1) to execute procedure on
server2 (S2).
On S2 in this procedure I use openrowset to return some rows from S1
into #temp table and than execute some updates and inserts on S2.
And at the end I call another OPENROWSET on S2 to execute one update
on S1.

So, communication is in both ways. I don't use Linked server because
of performance reason
(when returning large sets of data it's much difference).

So, you suggest combination - for executing procedure I should use
linked server and inside procedure for returning large sets of data, I
should use OPENROWSET?

Thank you for your help
Simon

[Gert-Jan Strik]

> (Gert, table names and db names are fictitious)

I'm afraid I can't help much without seeing the real code.
--
Gert-Jan

[Erland Sommarskog]

simon (zupa...@gmail.com) writes:
> So, communication is in both ways. I don't use Linked server because
> of performance reason
> (when returning large sets of data it's much difference).

Linked server or not is hardly the issue. But returning data through
OPENQUERY may be diffrently than calling a remote procedure. (OPENROWSET
is just a different interface for the samething ias OPENQUERY.)

But in this particular situation, you only perform an UPDATE, so the
argument about performance is not relevant.