Remove Article from Merge replication

Hi NG
Publisher:
Win 2003 Server
SQL 2000 - SP3a
Subscriber:
Win 2000 - SP4
SQL 2000 - SP3a
After many headaches with dog servers and virtually non-existent bandwidth,
I have finally got my Merge Replication up and running OK.
However, I have inadvertently selected 2 tables for replication which should
not be replicated. The reason for this is that these tables are used as
staging tables when consolidating large amounts of data. They get 200,000
inserts/updates/deletes per day. Normally we only expect to be replicating
1,000 inserts/updates/deletes per day.
Is there a way to remove these 2 articles form the replication process.
If not, I will simply have to create 2 new tables, and ensure that all
references to the existing tables are changed to the new non-replicated
tables. However, I dislike workarounds and orphan items.
I have seen a couple of posts indicating sp_MSunmarkreplinfo. However, I
have been unable to find any reference to this SP in Books online.
Regards
Des Norton
Des,
unlike transactional replication this is not possible via normal means. Your
best bet is to drop the publication and recreate without the 2 tables. This
can be made more palatable - especially if you have large tables - by doing a
nosync one, provided you can prevent updates to the data during this period.
The other option is to disable the merge triggers on these tables, but again
this is a bit of a hack.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Kick the users off the subscribers, drop the subscriptions, drop the
articles using sp_dropmergerarticle, or by right clicking on your
publication and selecting properties, then in the articles tab uncheck the
articles you wish to drop.
Then redeploy your subscribers doing a no-sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Des Norton" <desREMOVEnortonUPPERCASE@.gmail.com> wrote in message
news:uncu3HIAGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi NG
> Publisher:
> Win 2003 Server
> SQL 2000 - SP3a
> Subscriber:
> Win 2000 - SP4
> SQL 2000 - SP3a
>
> After many headaches with dog servers and virtually non-existent
> bandwidth, I have finally got my Merge Replication up and running OK.
> However, I have inadvertently selected 2 tables for replication which
> should not be replicated. The reason for this is that these tables are
> used as staging tables when consolidating large amounts of data. They get
> 200,000 inserts/updates/deletes per day. Normally we only expect to be
> replicating 1,000 inserts/updates/deletes per day.
> Is there a way to remove these 2 articles form the replication process.
> If not, I will simply have to create 2 new tables, and ensure that all
> references to the existing tables are changed to the new non-replicated
> tables. However, I dislike workarounds and orphan items.
> I have seen a couple of posts indicating sp_MSunmarkreplinfo. However, I
> have been unable to find any reference to this SP in Books online.
>
> Regards
> Des Norton
>

Remove Article

sp_droparticle

>--Original Message--
>How do I remove a table (aka article) from replication?
I have 19 tables replicating to 15 servers, i need to
remove 4 of the 19 tables from the replication. How?
>Thanks so much for your assistance,
>Adam
>.
>
(applies to snapshot or transactional)
Paul
sql

Remote Stored Procedures

I have a stored proc on a server called lax-dd and the
stored proc name is test. Both servers are in the same
domain and the sa passwords are the same on both servers.
I in query analyzer run exec lax-dd.dbname.dbo.test.
I get errors it doesn't understand the dash(-) in the
servername. I've tried placing the string in quot and
have problems finding the store proc...
Any ideas?
When you get errors and are having problems troubleshooting
those, it's always a good idea to post the error numbers and
the exact error messages.
Anyway...I'm guessing the problem is that you need to set
up a linked server to the other remote SQL Server. See the
books online topic: OLE DB Provider for SQL Server
You can also find more information in books online under
sp_addlinkedserver
-Sue
On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
<jay_bukstein@.hotmail.com> wrote:

>I have a stored proc on a server called lax-dd and the
>stored proc name is test. Both servers are in the same
>domain and the sa passwords are the same on both servers.
>I in query analyzer run exec lax-dd.dbname.dbo.test.
>I get errors it doesn't understand the dash(-) in the
>servername. I've tried placing the string in quot and
>have problems finding the store proc...
>Any ideas?
|||No, in SQL 2000, This is Remote Server, not a linked
server, it looks like the difference is a remote server
is only for other MS SQL server's using native SQL
Drivers where link servers a OLE DB providers, and there
is no error number associated with it.
I also had no trouble define the Remote server, I just
can't execute a remote storder Procedure using the
following Query analyzer command
exec lax-dd.dbname.dbo.test.
Any other ideas.

>--Original Message--
>When you get errors and are having problems
troubleshooting
>those, it's always a good idea to post the error numbers
and
>the exact error messages.
>Anyway...I'm guessing the problem is that you need to
set
>up a linked server to the other remote SQL Server. See
the[vbcol=seagreen]
>books online topic: OLE DB Provider for SQL Server
>You can also find more information in books online under
>sp_addlinkedserver
>-Sue
>On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
><jay_bukstein@.hotmail.com> wrote:
servers.
>.
>
|||One of the biggest differences is that remote servers are for
backwards compatability. You should be using linked servers if this is
to access a SQL Server that is higher than version 6.5.
If there is no error message and no error number, how do you know you
are getting errors? If you actually do have error messages, please
post the exact error message.
And what steps did you take to set this up as a remote server?
-Sue
On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
<jay_bukstein@.hotmail.com> wrote:
[vbcol=seagreen]
>No, in SQL 2000, This is Remote Server, not a linked
>server, it looks like the difference is a remote server
>is only for other MS SQL server's using native SQL
>Drivers where link servers a OLE DB providers, and there
>is no error number associated with it.
>I also had no trouble define the Remote server, I just
>can't execute a remote storder Procedure using the
>following Query analyzer command
>exec lax-dd.dbname.dbo.test.
>Any other ideas.
>
>troubleshooting
>and
>set
>the
>servers.
|||In a query window I receive a message that it can't find
the store procedure. I can run the store procedure in
query analyzer when logged in directly on that server.
I've even tryed running system store procedures like
sp_helpdb, or sp_who and get the same message 'can't find
stored procedure.'
Too also mention that I can't create these as Link
servers because I have SQL replication running.
Replication creates the other servers as remote servers.

>--Original Message--
>One of the biggest differences is that remote servers
are for
>backwards compatability. You should be using linked
servers if this is
>to access a SQL Server that is higher than version 6.5.
>If there is no error message and no error number, how do
you know you
>are getting errors? If you actually do have error
messages, please
>post the exact error message.
>And what steps did you take to set this up as a remote
server?[vbcol=seagreen]
>-Sue
>On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
><jay_bukstein@.hotmail.com> wrote:
there[vbcol=seagreen]
numbers[vbcol=seagreen]
under[vbcol=seagreen]
the[vbcol=seagreen]
same[vbcol=seagreen]
and
>.
>
|||I had the problem myself.
Call it through
EXEC [<ServerName>].[<DBName>].[<Owner>].[<ObjectName>]
(i.e., add [ ] on the server name).
After a whole day of testing this trivial solution came up. duh
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Remote Stored Procedures

I have a stored proc on a server called lax-dd and the
stored proc name is test. Both servers are in the same
domain and the sa passwords are the same on both servers.
I in query analyzer run exec lax-dd.dbname.dbo.test.
I get errors it doesn't understand the dash(-) in the
servername. I've tried placing the string in quot and
have problems finding the store proc...
Any ideas?When you get errors and are having problems troubleshooting
those, it's always a good idea to post the error numbers and
the exact error messages.
Anyway...I'm guessing the problem is that you need to set
up a linked server to the other remote SQL Server. See the
books online topic: OLE DB Provider for SQL Server
You can also find more information in books online under
sp_addlinkedserver
-Sue
On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
<jay_bukstein@.hotmail.com> wrote:

>I have a stored proc on a server called lax-dd and the
>stored proc name is test. Both servers are in the same
>domain and the sa passwords are the same on both servers.
>I in query analyzer run exec lax-dd.dbname.dbo.test.
>I get errors it doesn't understand the dash(-) in the
>servername. I've tried placing the string in quot and
>have problems finding the store proc...
>Any ideas?|||No, in SQL 2000, This is Remote Server, not a linked
server, it looks like the difference is a remote server
is only for other MS SQL server's using native SQL
Drivers where link servers a OLE DB providers, and there
is no error number associated with it.
I also had no trouble define the Remote server, I just
can't execute a remote storder Procedure using the
following Query analyzer command
exec lax-dd.dbname.dbo.test.
Any other ideas.

>--Original Message--
>When you get errors and are having problems
troubleshooting
>those, it's always a good idea to post the error numbers
and
>the exact error messages.
>Anyway...I'm guessing the problem is that you need to
set
>up a linked server to the other remote SQL Server. See
the
>books online topic: OLE DB Provider for SQL Server
>You can also find more information in books online under
>sp_addlinkedserver
>-Sue
>On Thu, 24 Jun 2004 13:26:08 -0700, "Remote server"
><jay_bukstein@.hotmail.com> wrote:
>
servers.[vbcol=seagreen]
>.
>|||One of the biggest differences is that remote servers are for
backwards compatability. You should be using linked servers if this is
to access a SQL Server that is higher than version 6.5.
If there is no error message and no error number, how do you know you
are getting errors? If you actually do have error messages, please
post the exact error message.
And what steps did you take to set this up as a remote server?
-Sue
On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
<jay_bukstein@.hotmail.com> wrote:
[vbcol=seagreen]
>No, in SQL 2000, This is Remote Server, not a linked
>server, it looks like the difference is a remote server
>is only for other MS SQL server's using native SQL
>Drivers where link servers a OLE DB providers, and there
>is no error number associated with it.
>I also had no trouble define the Remote server, I just
>can't execute a remote storder Procedure using the
>following Query analyzer command
>exec lax-dd.dbname.dbo.test.
>Any other ideas.
>
>
>troubleshooting
>and
>set
>the
>servers.|||In a query window I receive a message that it can't find
the store procedure. I can run the store procedure in
query analyzer when logged in directly on that server.
I've even tryed running system store procedures like
sp_helpdb, or sp_who and get the same message 'can't find
stored procedure.'
Too also mention that I can't create these as Link
servers because I have SQL replication running.
Replication creates the other servers as remote servers.

>--Original Message--
>One of the biggest differences is that remote servers
are for
>backwards compatability. You should be using linked
servers if this is
>to access a SQL Server that is higher than version 6.5.
>If there is no error message and no error number, how do
you know you
>are getting errors? If you actually do have error
messages, please
>post the exact error message.
>And what steps did you take to set this up as a remote
server?
>-Sue
>On Fri, 25 Jun 2004 08:54:59 -0700, "Jay Bukstein"
><jay_bukstein@.hotmail.com> wrote:
>
there[vbcol=seagreen]
numbers[vbcol=seagreen]
under[vbcol=seagreen]
the[vbcol=seagreen]
same[vbcol=seagreen]
and[vbcol=seagreen]
>.
>|||I had the problem myself.
Call it through
EXEC [<ServerName>].[<DBName>].[<Owner>].[<ObjectName>]
(i.e., add [ ] on the server name).
After a whole day of testing this trivial solution came up. duh
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.

Remote Stored Procedure exec from variable

Hello Folks,

I have a server that is linked to several other servers. I have been able to successfully execute a procedure manually to each from the common server by executing this:

exec server1.dbname.owner.procedure
go
exec server2.dbname.owner.procedure
go
exec server3.dbname.owner.procedure
go

While this is ok, I'd like to wrap this in a loop to execute the procedure but switch out the server name in each iteration (something like this):

while @.variable is not null
begin
select @.server = 'change name in loop'
select @.str = @.server+'.dbname.owner.procedure'
exec @.str
end

This unfortunately does not work. The execute is acting like the server portion of the name does not exist (defaulting to local). I have attempted to use the AT SERVERNAME syntax in a similar fashion and been unsuccessful.

Is there some way I could dynamically create the four part name and execute it?

Any assistance would be greatly appreciated.

Thanks, Mark

DECLARE @.server nvarchar(128)

DECLARE @.cmd nvarchar(1000)

SET @.server = 'MyServer'

SET @.cmd = 'EXEC ' + @.server + '.MyDatabase.MySchema.MySproc'

EXEC (@.cmd)

Remote stored procedure

I am trying to run a stored procedure on a remote server and insert the
output into a table on the local machine.
The two servers are linked and I am using the code as follows
BEGIN DISTRIBUTED TRAN
insert into ServerA.Database.dbo.drv_total
exec ServerB.master.dbo.xp_fixeddrives
COMMIT TRAN
The MSDTC is started on both machines.
I am getting this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
Any thoughts?
TIA,
nivek
Hi Nivek,
Are you running on Windows 2003? If so, the following link may apply to you:
http://support.microsoft.com/default...b;en-us;329332
"The problem occurs because Microsoft Distributed Transaction Coordinator
(MS DTC) is not configured for network access. By default, the network access
settings of MS DTC are disabled on new installations of SQL Server 2000 on
computers running Windows Server 2003, Enterprise Edition. "
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"nivek" wrote:

> I am trying to run a stored procedure on a remote server and insert the
> output into a table on the local machine.
> The two servers are linked and I am using the code as follows
> BEGIN DISTRIBUTED TRAN
> insert into ServerA.Database.dbo.drv_total
> exec ServerB.master.dbo.xp_fixeddrives
> COMMIT TRAN
> The MSDTC is started on both machines.
> I am getting this error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
>
> Any thoughts?
>
> TIA,
> nivek
>
>
sql

Remote stored procedure

I am trying to run a stored procedure on a remote server and insert the
output into a table on the local machine.
The two servers are linked and I am using the code as follows
BEGIN DISTRIBUTED TRAN
insert into ServerA.Database.dbo.drv_total
exec ServerB.master.dbo.xp_fixeddrives
COMMIT TRAN
The MSDTC is started on both machines.
I am getting this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
Any thoughts?
TIA,
nivekHi Nivek,
Are you running on Windows 2003? If so, the following link may apply to you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
"The problem occurs because Microsoft Distributed Transaction Coordinator
(MS DTC) is not configured for network access. By default, the network access
settings of MS DTC are disabled on new installations of SQL Server 2000 on
computers running Windows Server 2003, Enterprise Edition. "
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"nivek" wrote:
> I am trying to run a stored procedure on a remote server and insert the
> output into a table on the local machine.
> The two servers are linked and I am using the code as follows
> BEGIN DISTRIBUTED TRAN
> insert into ServerA.Database.dbo.drv_total
> exec ServerB.master.dbo.xp_fixeddrives
> COMMIT TRAN
> The MSDTC is started on both machines.
> I am getting this error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
>
> Any thoughts?
>
> TIA,
> nivek
>
>

remote sql server is the same as local in em

Hello,
In EM under node Remote Servers I see the remote server with the same name
as a local. For example my server name is WP200 and remote server shows
WP200. It is sql 2000. The RCP is checked and map all logins to different
logins selected but there is nothing in the selection grid.
I can't figure out for what purpose it was set up this way.
Please help.
thank you very much.
JanHi Jan
Your local server will appear under the remote servers, if it doesn't then
@.@.SERVERNAME will probably return the wrong value. Reading sp_addserver in
BOL and the @.LOCAL parameter may help!
John
"Jan" wrote:

> Hello,
> In EM under node Remote Servers I see the remote server with the same name
> as a local. For example my server name is WP200 and remote server shows
> WP200. It is sql 2000. The RCP is checked and map all logins to different
> logins selected but there is nothing in the selection grid.
> I can't figure out for what purpose it was set up this way.
> Please help.
> thank you very much.
> Jan|||For remote server access to work, the 'remote access' configuration option,
which controls logins from remote servers, must be set to 1 can be done usin
g
sp_configure
refer Configuring Remote Servers chapter in BOL
Amol Lembhe
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jan
> Your local server will appear under the remote servers, if it doesn't then
> @.@.SERVERNAME will probably return the wrong value. Reading sp_addserver in
> BOL and the @.LOCAL parameter may help!
> John
> "Jan" wrote:
>

remote sql server is the same as local in em

Hello,
In EM under node Remote Servers I see the remote server with the same name
as a local. For example my server name is WP200 and remote server shows
WP200. It is sql 2000. The RCP is checked and map all logins to different
logins selected but there is nothing in the selection grid.
I can't figure out for what purpose it was set up this way.
Please help.
thank you very much.
JanHi Jan
Your local server will appear under the remote servers, if it doesn't then
@.@.SERVERNAME will probably return the wrong value. Reading sp_addserver in
BOL and the @.LOCAL parameter may help!
John
"Jan" wrote:
> Hello,
> In EM under node Remote Servers I see the remote server with the same name
> as a local. For example my server name is WP200 and remote server shows
> WP200. It is sql 2000. The RCP is checked and map all logins to different
> logins selected but there is nothing in the selection grid.
> I can't figure out for what purpose it was set up this way.
> Please help.
> thank you very much.
> Jan|||For remote server access to work, the 'remote access' configuration option,
which controls logins from remote servers, must be set to 1 can be done using
sp_configure
refer Configuring Remote Servers chapter in BOL
Amol Lembhe
"John Bell" wrote:
> Hi Jan
> Your local server will appear under the remote servers, if it doesn't then
> @.@.SERVERNAME will probably return the wrong value. Reading sp_addserver in
> BOL and the @.LOCAL parameter may help!
> John
> "Jan" wrote:
> > Hello,
> > In EM under node Remote Servers I see the remote server with the same name
> > as a local. For example my server name is WP200 and remote server shows
> > WP200. It is sql 2000. The RCP is checked and map all logins to different
> > logins selected but there is nothing in the selection grid.
> > I can't figure out for what purpose it was set up this way.
> > Please help.
> > thank you very much.
> >
> > Jan

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remote
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUERY
but still takes ages to run.
Thanks
Open query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks
|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

remote sql query

we are trying to execute a remote sql query from one sql server against
another. Both servers are in the same domain. The query is a simple select
* from tablename where surname = whatever. We precede this with an
'opendatasource' statement. The query returns only a few rows. When we run
the query between two sql servers on the same lan segment the query runs in
a
second or two. When we run the query between servers on two different
network segments (seperated by a firewall) but still both in the same domain
the same query takes 50+ seconds to run. Looking further, it seems that the
query is actually being executed on the local server so the entire table
(over 1 million rows) is copied locally before running the query. How can I
run the query as a remote query so that the entire query is run on the remot
e
sql server and only the query results are passed accross the network to the
local server? I have also tried creating a linked server and using OPENQUER
Y
but still takes ages to run.
ThanksOpen query should pass the query to the other server for execution. Are you
sure you are not doing a JOIN to a local table? In ay case you can create a
stored procedure on the remote server and execute that.
Andrew J. Kelly SQL MVP
"lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
> we are trying to execute a remote sql query from one sql server against
> another. Both servers are in the same domain. The query is a simple
> select
> * from tablename where surname = whatever. We precede this with an
> 'opendatasource' statement. The query returns only a few rows. When we
> run
> the query between two sql servers on the same lan segment the query runs
> in a
> second or two. When we run the query between servers on two different
> network segments (seperated by a firewall) but still both in the same
> domain
> the same query takes 50+ seconds to run. Looking further, it seems that
> the
> query is actually being executed on the local server so the entire table
> (over 1 million rows) is copied locally before running the query. How can
> I
> run the query as a remote query so that the entire query is run on the
> remote
> sql server and only the query results are passed accross the network to
> the
> local server? I have also tried creating a linked server and using
> OPENQUERY
> but still takes ages to run.
> Thanks|||Perhaps it's the location of the WHERE clause? If you say
SELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees')
WHERE EmployeeID > 5
You are asking for all the rows and then you will apply the where clause
locally.
If you saySELECT LastName
FROM
OPENQUERY(Otherserver, 'SELECT EmployeeID, LastName FROM
Northwind.dbo.Employees WHERE EmployeeID > 5')
You are sending the where clause to the remote server - a good thing, and
only getting the qualified rows returned to the local server.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OB7ad5%23KGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Open query should pass the query to the other server for execution. Are
> you sure you are not doing a JOIN to a local table? In ay case you can
> create a stored procedure on the remote server and execute that.
>
> --
> Andrew J. Kelly SQL MVP
>
> "lightningtechie" <lightningtechie@.discussions.microsoft.com> wrote in
> message news:4EB250D4-A5D1-4C5C-AC6D-DFA30B09F930@.microsoft.com...
>

Remote servers

I am trying to use a remote distributor and I think my settings are wrong
for RPC and mapping. How should these be mapped so that the publisher can
see the SQL Server Agent on the distributor?
--Kristy
Whats the error message?
"Kristy" <pleasepostreply@.here.com> wrote in message
news:OCz1FewMFHA.2736@.TK2MSFTNGP09.phx.gbl...
>I am trying to use a remote distributor and I think my settings are wrong
> for RPC and mapping. How should these be mapped so that the publisher can
> see the SQL Server Agent on the distributor?
> --Kristy
>
|||Publisher can not connect to ditributor because it is not configured for
remote access. I looked in the sysservers table and it is configured for rpc
and data access on both machines.
--Kristy
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GX4QgHNFHA.1308@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Whats the error message?
> "Kristy" <pleasepostreply@.here.com> wrote in message
> news:OCz1FewMFHA.2736@.TK2MSFTNGP09.phx.gbl...
can
>

Remote Server question

sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRThis is a multi-part message in MIME format.
--090909090705070004050507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
--090909090705070004050507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
ChrisR wrote:
<blockquote cite="mid%230BGYuarFHA.3424@.TK2MSFTNGP14.phx.gbl"
type="cite">
<pre wrap="">sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
</pre>
</blockquote>
</body>
</html>
--090909090705070004050507--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that =doesn't imply you can actually login using that standard SQL login.)
--
mike hodgson
blog: http://sqlnerd.blogspot.com=20
ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =
login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR
--=_NextPart_000_0008_01C5AE09.DF950070
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks Mike, but no thats not the case.
"Mike Hodgson" wrote in message news:O$vQwAdrFHA.908@.t=k2msftngp13.phx.gbl...Is it possible that your remote server is set up to use Windows =Authentication only? (Even though you can set the 'sa' password, that doesn't =imply you can actually login using that standard SQL login.)
--mike =hodgsonblog: http://sqlnerd.blogspot.com ChrisR wrote: sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote =Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, =which is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while =logged in as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote =login at the server.
The SA password is the same on both these boxes, so while logged in as =SA I:
sp_addlinkedsrvlogin @.rmtsrvname =3D 'cpdbovs01'
, @.useself =3D 'true'
but that doesnt make my problem go away. The really funny thing here is =that I googled it, saw that I posted the same question years ago, see that I =got it, but cant read the answer.
TIA, ChrisR

---

--=_NextPart_000_0008_01C5AE09.DF950070--

Remote Server question

sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisRIs it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:

>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged i
n
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I
:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is tha
t
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>|||Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:O$v
QwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows Authenticati
on only? (Even though you can set the 'sa' password, that doesn't imply you
can actually login using that standard SQL login.)
mike hodgson
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR

Remote Server question

sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR
Is it possible that your remote server is set up to use Windows
Authentication only? (Even though you can set the 'sa' password, that
doesn't imply you can actually login using that standard SQL login.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ChrisR wrote:

>sql2k sp3
>
>Ive always used Linked Servers and as such dont really know about Remote
>Servers. Anyways, here is the deal.
>Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
>is a Subscriber to Box1.
>So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
>as SA. But I get the message:
>Server: Msg 18483, Level 14, State 1, Line 1
>Could not connect to server 'BLA'because 'sa' is not defined as a remote
>login at the server.
>The SA password is the same on both these boxes, so while logged in as SA I:
>
>sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
> , @.useself = 'true'
>but that doesnt make my problem go away. The really funny thing here is that
>I googled it, saw that I posted the same question years ago, see that I got
>it, but cant read the answer.
>TIA, ChrisR
>
>
>
|||Thanks Mike, but no thats not the case.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:O$vQwAdrFHA.908@.tk2msftngp13.phx.gbl...
Is it possible that your remote server is set up to use Windows Authentication only? (Even though you can set the 'sa' password, that doesn't imply you can actually login using that standard SQL login.)
mike hodgson
blog: http://sqlnerd.blogspot.com
ChrisR wrote:
sql2k sp3
Ive always used Linked Servers and as such dont really know about Remote
Servers. Anyways, here is the deal.
Box1 -- Replication Publisher. Has a Remote Server defined for Box2, which
is a Subscriber to Box1.
So, Im trying to exec a proc on Box2 while logged on to Box1 while logged in
as SA. But I get the message:
Server: Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'BLA'because 'sa' is not defined as a remote
login at the server.
The SA password is the same on both these boxes, so while logged in as SA I:
sp_addlinkedsrvlogin @.rmtsrvname = 'cpdbovs01'
, @.useself = 'true'
but that doesnt make my problem go away. The really funny thing here is that
I googled it, saw that I posted the same question years ago, see that I got
it, but cant read the answer.
TIA, ChrisR

Remote Server Not Visible

Hi All,
I am trying to set up log shipping between SQL servers on two different
networks (diff forests) with a two way trust relationship.
Problem is that I can't see the remote SQL server in the destination
server drop down list (I can see local SQL servers).
The SQL agents on both machines are running under different accounts.
I have tried to add the account running the remote SQL agent to my local
SQL servers administrators group but this has no effect.
I can ping the server by name and it resolves OK.
I have registered the server with Enterprise manager, and even tried to
add it as a linked server to no effect.
Any ideas to resolve this would be much appreciated.
Ranj.
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
Ranj,
I have no way of emulating your situation here, but one thing I'd try is
using the client network utility to create an alias to the other server
based on IP address, register the alias in EM and then see if that appears
in the Log-shipping wizard.
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the response.
I tried adding the server through the client network utility with tcp/IP
and then named pipes, but neither worked.
Is it possible to script transaction log shipping through stored
procedures ?
Ranj.
On Wed, 14 Jul 2004 17:09:24 +0100, Paul Ibison
<Paul.Ibison@.Pygmalion.Com> wrote:

> Ranj,
> I have no way of emulating your situation here, but one thing I'd try is
> using the client network utility to create an alias to the other server
> based on IP address, register the alias in EM and then see if that
> appears
> in the Log-shipping wizard.
> HTH,
> Paul Ibison
>
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||Ranejet,
there is a list of SPs for log shipping (and other sections) in BOL :
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sp_00_519s.htm
It is possible to script it, but not possible to script it out from an
existing one, so this could take some time.
Please can you post back to let me know what OSs you are using?
Regards,
Paul Ibison
|||Hi Paul,
thanks for the info - I am using Windows 2000 Server edition on local and
remote - both have SQL Enterprise installed.
Both networks have their own forests and are connected with a two way
trust.
There is a checkpoint firewall between them used to esatblish a VPN.
Ranj.
|||Ranj,
you can setup logshipping using the resource kit utilities. I use the ones
that was for sql7 on sql2000 with no problems at all, it is eay to setup as
well.
let me know if you need the scripts and documentation from resource kit from
sql7.
regards
m
"ranejet" <not-tellint> wrote in message
news:opsa55bmjgcakp78@.idt.toare.co.uk...
> Hi Paul,
> Thanks for the response.
> I tried adding the server through the client network utility with tcp/IP
> and then named pipes, but neither worked.
> Is it possible to script transaction log shipping through stored
> procedures ?
> Ranj.
> On Wed, 14 Jul 2004 17:09:24 +0100, Paul Ibison
> <Paul.Ibison@.Pygmalion.Com> wrote:
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||Ranejet,
pinging the server doesn't mean port 1433 is open in the firewall. I'd check
that.
Also, use dnslookup to verify the ip address after you have successfully
pinged the server - it may be another server in a different domain.
HTH,
Paul Ibison
|||try an odbcping to verify 1433 is open.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eeD$nBoaEHA.3596@.tk2msftngp13.phx.gbl...
> Ranejet,
> pinging the server doesn't mean port 1433 is open in the firewall. I'd
check
> that.
> Also, use dnslookup to verify the ip address after you have successfully
> pinged the server - it may be another server in a different domain.
> HTH,
> Paul Ibison
>
|||If You can ping the Server, that probably means You have full IP connectivity, since ICMP is the first thing to block in a firewall.
What You might lack, however, is NetBIOS resolution, which Is possibly what the Enterprise Manager is looking for. Do You see the remote Server in the "network" in Windows Explorer ?
If not, activating NetBIOS over TCP/IP might work, alternatively, You need a common WINS server between the two servers to establish a browse list.

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc o
n
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same
,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
--
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:
> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> > I've got an interesting situation. I have two SQL 2000 servers and a proc
> > on
> > one that updates data on the other. If I use literal values in the where
> > clause of the update statement then the query plan uses a "remote query"
> > to
> > do the update. If I use variables then it uses a "remote scan" and
> > filters
> > locally. Obviously that takes too long.
> >
> > Here is some sample script.
> > -- This uses a remote query
> > UPDATE [Server2].Orders.dbo.OrderHistory
> > SET Salesperson = 'ROB'
> > WHERE OrderNumber = 12345
> >
> > -- This uses a remote scan
> > DECLARE @.Salesperson CHAR(3)
> > DECLARE @.OrderNumber INT
> > SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> >
> > UPDATE [Server2].Orders.dbo.OrderHistory
> > SET Salesperson = @.Salesperson
> > WHERE OrderNumber = @.OrderNumber
> >
> > -- This uses a remote query; same table and index structure; linked server
> > defined the same
> > UPDATE [Server3].Orders.dbo.OrderHistory
> > SET Salesperson = @.Salesperson
> > WHERE OrderNumber = @.OrderNumber
> >
> > I can't figure out any reason why it does it that way. To make things
> > more
> > odd, that proc updates a couple other servers in the same way for the same
> > table and those use remote queries. The linked servers are defined the
> > same,
> > the table structure and indexes are the same.
> >
> > Does anyone have any idea why it would work this way?
> >
> > Thanks.
> >
> > Dale.
> >
>
>

Remote scan on linked server

I've got an interesting situation. I have two SQL 2000 servers and a proc on
one that updates data on the other. If I use literal values in the where
clause of the update statement then the query plan uses a "remote query" to
do the update. If I use variables then it uses a "remote scan" and filters
locally. Obviously that takes too long.
Here is some sample script.
-- This uses a remote query
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = 'ROB'
WHERE OrderNumber = 12345
-- This uses a remote scan
DECLARE @.Salesperson CHAR(3)
DECLARE @.OrderNumber INT
SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
UPDATE [Server2].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
-- This uses a remote query; same table and index structure; linked server
defined the same
UPDATE [Server3].Orders.dbo.OrderHistory
SET Salesperson = @.Salesperson
WHERE OrderNumber = @.OrderNumber
I can't figure out any reason why it does it that way. To make things more
odd, that proc updates a couple other servers in the same way for the same
table and those use remote queries. The linked servers are defined the same,
the table structure and indexes are the same.
Does anyone have any idea why it would work this way?
Thanks.
Dale.
Is it possible the datatypes of those declared variables are not equal to
the corresponding data types on the linked server? In any case you should
try using OPENQUERY() as it will pass thru all queries so they are always
interpreted on the other side just as you wrote them with no odbc
intervention.
Andrew J. Kelly SQL MVP
"Dale M." <DaleM@.discussions.microsoft.com> wrote in message
news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
> I've got an interesting situation. I have two SQL 2000 servers and a proc
> on
> one that updates data on the other. If I use literal values in the where
> clause of the update statement then the query plan uses a "remote query"
> to
> do the update. If I use variables then it uses a "remote scan" and
> filters
> locally. Obviously that takes too long.
> Here is some sample script.
> -- This uses a remote query
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = 'ROB'
> WHERE OrderNumber = 12345
> -- This uses a remote scan
> DECLARE @.Salesperson CHAR(3)
> DECLARE @.OrderNumber INT
> SELECT @.Salesperson = 'ROB', @.OrderNumber = 12345
> UPDATE [Server2].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> -- This uses a remote query; same table and index structure; linked server
> defined the same
> UPDATE [Server3].Orders.dbo.OrderHistory
> SET Salesperson = @.Salesperson
> WHERE OrderNumber = @.OrderNumber
> I can't figure out any reason why it does it that way. To make things
> more
> odd, that proc updates a couple other servers in the same way for the same
> table and those use remote queries. The linked servers are defined the
> same,
> the table structure and indexes are the same.
> Does anyone have any idea why it would work this way?
> Thanks.
> Dale.
>
|||Thanks for the reply.
The data types are the same.
I've tried several variations of OPENQUERY syntax but it doesn't like
variables in its parameters, so I'm not sure how to get the update done.
Plus, I'd sorta like to know why anyways.
"Andrew J. Kelly" wrote:

> Is it possible the datatypes of those declared variables are not equal to
> the corresponding data types on the linked server? In any case you should
> try using OPENQUERY() as it will pass thru all queries so they are always
> interpreted on the other side just as you wrote them with no odbc
> intervention.
> --
> Andrew J. Kelly SQL MVP
> "Dale M." <DaleM@.discussions.microsoft.com> wrote in message
> news:5EAABBC0-BFF8-4DC6-BF0C-911DBAAB5B5C@.microsoft.com...
>
>

Remote queries using sp_executesql run inconsistently

This one has stumped me!
I recently implemented a process to monitor database usage and growth
on our production servers. I use on server as the "master" that
collects data from all the other servers into one database. The
problem I'm having is that only every other day the process completes
after having successfully collected data from all servers. On the
alternate days, only data from the "master" server is collected. I've
used some debugging code to determine that my process is successfully
communicating with each server each day, but I can reliably plan on the
every-other-day behavior.
I apologize if this explanantion is too vague. Here is some code...
This is the SP on the "master" server that contacts all the other
servers and collects the data (this is within a cursor that loops
through the list of server names as stored in a local table):
-- Creates the generic linked server
select @.svrlgn = lower(left(@.svr_nm, @.svr_nm_len))
exec sp_addlinkedserver 'MSSQL', '', 'SQLOLEDB', @.svr_nm
exec sp_addlinkedsrvlogin 'MSSQL', 'false', null, @.svrlgn, @.svrlgn
exec sp_serveroption 'MSSQL', 'rpc', 'true'
exec sp_serveroption 'MSSQL', 'rpc out', 'true'
set @.sqlstr = 'insert into temp_drives (DriveLetter, MBFree) exec
MSSQL.master..xp_fixeddrives; update temp_drives set ServerName = ''' +
@.svr_nm + ''' where ServerName = ''new'''
execute sp_executesql @.sqlstr
set @.sqlstr = 'exec master.dbo.mjr_GetDatabaseSize_Data'
execute MSSQL.master.dbo.sp_executesql @.sqlstr
-- clean-up
exec sp_droplinkedsrvlogin 'MSSQL',null
exec sp_dropserver 'MSSQL'Still reviewing your code, but just wondering why are you adding and
dropping links to the remote servers each time you run the job? Why not
just permanently link the servers?
Will get back to you on the rest when I can review your code in more detail.
Thx
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:1B4E5251-D928-4156-A19D-41F24C745456@.microsoft.com...
> This one has stumped me!
>
> I recently implemented a process to monitor database usage and growth
> on our production servers. I use on server as the "master" that
> collects data from all the other servers into one database. The
> problem I'm having is that only every other day the process completes
> after having successfully collected data from all servers. On the
> alternate days, only data from the "master" server is collected. I've
> used some debugging code to determine that my process is successfully
> communicating with each server each day, but I can reliably plan on the
> every-other-day behavior.
>
> I apologize if this explanantion is too vague. Here is some code...
>
> This is the SP on the "master" server that contacts all the other
> servers and collects the data (this is within a cursor that loops
> through the list of server names as stored in a local table):
>
> -- Creates the generic linked server
> select @.svrlgn = lower(left(@.svr_nm, @.svr_nm_len))
>
> exec sp_addlinkedserver 'MSSQL', '', 'SQLOLEDB', @.svr_nm
> exec sp_addlinkedsrvlogin 'MSSQL', 'false', null, @.svrlgn, @.svrlgn
> exec sp_serveroption 'MSSQL', 'rpc', 'true'
> exec sp_serveroption 'MSSQL', 'rpc out', 'true'
>
> set @.sqlstr = 'insert into temp_drives (DriveLetter, MBFree) exec
> MSSQL.master..xp_fixeddrives; update temp_drives set ServerName = ''' +
> @.svr_nm + ''' where ServerName = ''new'''
>
> execute sp_executesql @.sqlstr
>
> set @.sqlstr = 'exec master.dbo.mjr_GetDatabaseSize_Data'
> execute MSSQL.master.dbo.sp_executesql @.sqlstr
>
> -- clean-up
> exec sp_droplinkedsrvlogin 'MSSQL',null
> exec sp_dropserver 'MSSQL'
>|||We would prefer not to leave permanent linked servers out our servers if not
for a specific database or purpose; we've found that developers can sometime
s
abuse them. Also, the dynamic nature of the script allows us to add and
remove servers from the process more easily.
Thanks for reviewing my code. I look forward to your feedback!
"Michael C#" wrote:

> Still reviewing your code, but just wondering why are you adding and
> dropping links to the remote servers each time you run the job? Why not
> just permanently link the servers?
> Will get back to you on the rest when I can review your code in more detai
l.
> Thx
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:1B4E5251-D928-4156-A19D-41F24C745456@.microsoft.com...
>
>|||Nothing's jumping out at me, other than you're not fully-qualifying all of
the tables (i.e., temp_drives). Are you seeing anything in your Event Logs
on either the local computer or remote linked servers? My best guess would
be a security/login failure on the remote machine, but you'd have to check
the logs for that. Could be that the commands are timing out, for instance
if you're running intensive operations every other day like backups and
index rebuilds, etc. Look for any other activities that are occurring on
your server on days of failure. It might end up just being a case of
scheduling the job to run earlier or later in the day.
There might be additional info in the SQL Server Logs (under "Management" in
EM).
Let me know if you see anything in your logs.
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:B4EDC587-A45D-4FBE-9F28-F3C2FE46A7E1@.microsoft.com...
> We would prefer not to leave permanent linked servers out our servers if
> not
> for a specific database or purpose; we've found that developers can
> sometimes
> abuse them. Also, the dynamic nature of the script allows us to add and
> remove servers from the process more easily.
> Thanks for reviewing my code. I look forward to your feedback!
>
> "Michael C#" wrote:
>|||No, nothing in the event logs. The security is set up correctly. It could
be a timeout issue, but I would assume that I'd receive an error message in
that case.
Also, there are no other long-running jobs during this time, and no
processes that run only every other day.
I'm going to be adding a bit more code to the process this w, so
hopefully my additional testing will help to reveal the answer.
Thanks for your help
"Michael C#" wrote:

> Nothing's jumping out at me, other than you're not fully-qualifying all of
> the tables (i.e., temp_drives). Are you seeing anything in your Event Log
s
> on either the local computer or remote linked servers? My best guess woul
d
> be a security/login failure on the remote machine, but you'd have to check
> the logs for that. Could be that the commands are timing out, for instanc
e
> if you're running intensive operations every other day like backups and
> index rebuilds, etc. Look for any other activities that are occurring on
> your server on days of failure. It might end up just being a case of
> scheduling the job to run earlier or later in the day.
> There might be additional info in the SQL Server Logs (under "Management"
in
> EM).
> Let me know if you see anything in your logs.
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:B4EDC587-A45D-4FBE-9F28-F3C2FE46A7E1@.microsoft.com...
>
>|||Did you find a resolution on this?
"vogelm" <vogelm@.discussions.microsoft.com> wrote in message
news:277700A6-9FDD-44E7-847B-EF51E572387A@.microsoft.com...
> No, nothing in the event logs. The security is set up correctly. It
> could
> be a timeout issue, but I would assume that I'd receive an error message
> in
> that case.
> Also, there are no other long-running jobs during this time, and no
> processes that run only every other day.
> I'm going to be adding a bit more code to the process this w, so
> hopefully my additional testing will help to reveal the answer.
> Thanks for your help
> "Michael C#" wrote:
>|||No, not yet. This is only something I can work on when I have all my other
"regular" work done. :-(
"Michael C#" wrote:

> Did you find a resolution on this?
> "vogelm" <vogelm@.discussions.microsoft.com> wrote in message
> news:277700A6-9FDD-44E7-847B-EF51E572387A@.microsoft.com...
>
>