server: WHERE clause not being executed

Hi all,
One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.
Query: select * from oas_company where code = 'TEST'
If I look on serverB via Profiler the query is executed without the where
statement.
Query: select * from oas_company where tstamp = 0
Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)
So it looks like if there is a selection on a text field (varchar) the where
statement is not included.
For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.
Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?
Any suggestions would be greatly appreciated.
BoB
Hi
Have you tried:
select * from oas_company where code = N'TEST'
or
select * from oas_company where code IN (N'TEST')
Sometimes the re-writing of a query might solve the problem for a specific
build of SQL Server.
Regards
Mike
"BoB Teijema" wrote:

> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers, could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
|||"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?
select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:
http://support.microsoft.com/default...b;en-us;276225
http://groups.google.ch/groups?q=sql...al&sa=N&tab=wg
The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:
http://groups.google.ch/groups?hl=en...GP09.p hx.gbl
If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.
Simon
|||Thanks for your reply. We will investigate your solution asap.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...[vbcol=seagreen]
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
a[vbcol=seagreen]
where[vbcol=seagreen]
The[vbcol=seagreen]
contains[vbcol=seagreen]
It[vbcol=seagreen]
it
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/default...b;en-us;276225
>
http://groups.google.ch/groups?q=sql...collation& hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=en...org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.p hx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon
>

server: WHERE clause not being executed

Hi all,
One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.
Query: select * from oas_company where code = 'TEST'
If I look on serverB via Profiler the query is executed without the where
statement.
Query: select * from oas_company where tstamp = 0
Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)
So it looks like if there is a selection on a text field (varchar) the where
statement is not included.
For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.
Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?
Any suggestions would be greatly appreciated.
BoBHi
Have you tried:
select * from oas_company where code = N'TEST'
or
select * from oas_company where code IN (N'TEST')
Sometimes the re-writing of a query might solve the problem for a specific
build of SQL Server.
Regards
Mike
"BoB Teijema" wrote:

> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's a
sk
> me why)
> So it looks like if there is a selection on a text field (varchar) the whe
re
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers, cou
ld
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>|||"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?
select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:
http://support.microsoft.com/defaul...kb;en-us;276225
http://groups.google.ch/groups? q=s...ial&sa=N&tab=wg
The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:
http://groups.google.ch/groups?hl=e...FTNGP09.phx.gbl
If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.
Simon|||Thanks for your reply. We will investigate your solution asap.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
a[vbcol=seagreen]
where[vbcol=seagreen]
The[vbcol=seagreen]
contains[vbcol=seagreen]
It[vbcol=seagreen]
it[vbcol=seagreen]
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/defaul...kb;en-us;276225
>
http://groups.google.ch/groups? q=s...ati
on&hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=e...=org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon
>

server: WHERE clause not being executed

Hi all,

One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.

Query: select * from oas_company where code = 'TEST'

If I look on serverB via Profiler the query is executed without the where
statement.

Query: select * from oas_company where tstamp = 0

Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)

So it looks like if there is a selection on a text field (varchar) the where
statement is not included.

For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.

Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?

Any suggestions would be greatly appreciated.

BoB"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB

Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?

select * from serverB.MyDB.dbo.oas_company where code = 'TEST'

You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:

http://support.microsoft.com/defaul...kb;en-us;276225
http://groups.google.ch/groups?q=sq...ial&sa=N&tab=wg

The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:

http://groups.google.ch/groups?hl=e...FTNGP09.phx.gbl

If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.

Simon|||Thanks for your reply. We will investigate your solution asap.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
> > Hi all,
> > One of our companies is having problems with a query on a linked server.
> > They have two servers, serverA and serverB. On serverA they have set up
a
> > linked server to serverB.
> > Query: select * from oas_company where code = 'TEST'
> > If I look on serverB via Profiler the query is executed without the
where
> > statement.
> > Query: select * from oas_company where tstamp = 0
> > Again via profiler I can see that the where statement is now included.
The
> > tstamp field is a numerical field, the code field is a text field (don's
> > ask
> > me why)
> > So it looks like if there is a selection on a text field (varchar) the
> > where
> > statement is not included.
> > For the above selection this is not a problem as this table only
contains
> > 10 - 20 records. However the real problem is on the oas_balance table.
It
> > should only return 3 records, but because it skips the where statement,
it
> > returns 10.000.000 records.
> > Any idea? I know the default collations are different on both servers,
> > could
> > that be the cause of the problem? And if so, what could I do to overcome
> > this problem?
> > Any suggestions would be greatly appreciated.
> > BoB
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/defaul...kb;en-us;276225
http://groups.google.ch/groups?q=sq...%20collation&hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=e...=org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon

server: WHERE clause not being executed

Hi all,
One of our companies is having problems with a query on a linked server.
They have two servers, serverA and serverB. On serverA they have set up a
linked server to serverB.
Query: select * from oas_company where code = 'TEST'
If I look on serverB via Profiler the query is executed without the where
statement.
Query: select * from oas_company where tstamp = 0
Again via profiler I can see that the where statement is now included. The
tstamp field is a numerical field, the code field is a text field (don's ask
me why)
So it looks like if there is a selection on a text field (varchar) the where
statement is not included.
For the above selection this is not a problem as this table only contains
10 - 20 records. However the real problem is on the oas_balance table. It
should only return 3 records, but because it skips the where statement, it
returns 10.000.000 records.
Any idea? I know the default collations are different on both servers, could
that be the cause of the problem? And if so, what could I do to overcome
this problem?
Any suggestions would be greatly appreciated.
BoBHi
Have you tried:
select * from oas_company where code = N'TEST'
or
select * from oas_company where code IN (N'TEST')
Sometimes the re-writing of a query might solve the problem for a specific
build of SQL Server.
Regards
Mike
"BoB Teijema" wrote:
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers, could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>|||"BoB Teijema" <BTE@.euroforum.nl> wrote in message
news:csnvc4$eeg$1@.reader11.wxs.nl...
> Hi all,
>
> One of our companies is having problems with a query on a linked server.
> They have two servers, serverA and serverB. On serverA they have set up a
> linked server to serverB.
> Query: select * from oas_company where code = 'TEST'
> If I look on serverB via Profiler the query is executed without the where
> statement.
> Query: select * from oas_company where tstamp = 0
> Again via profiler I can see that the where statement is now included. The
> tstamp field is a numerical field, the code field is a text field (don's
> ask
> me why)
> So it looks like if there is a selection on a text field (varchar) the
> where
> statement is not included.
> For the above selection this is not a problem as this table only contains
> 10 - 20 records. However the real problem is on the oas_balance table. It
> should only return 3 records, but because it skips the where statement, it
> returns 10.000.000 records.
> Any idea? I know the default collations are different on both servers,
> could
> that be the cause of the problem? And if so, what could I do to overcome
> this problem?
>
> Any suggestions would be greatly appreciated.
>
> BoB
>
>
Presumably your real query looks like this? Or are you using OPENQUERY()
perhaps?
select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
You don't mention your MSSQL version(s), but a bit of Googling shows that
there are sometimes issues with linked servers having different collations,
especially with SQL 7:
http://support.microsoft.com/default.aspx?scid=kb;en-us;276225
http://groups.google.ch/groups?q=sql%202000%20linked%20server%20collation&hl=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
The information in BOL (for SQL 2000) seems to suggest that if both servers
are running SQL Server, then collations shouldn't be a problem, however it
isn't particularly clear - see "Optimizing Distributed Queries," for
example. Assuming you have SQL 2000, you could experiment with using COLLATE
in your queries to see if forcing a different collation does make a
difference:
http://groups.google.ch/groups?hl=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
If you don't get any more useful suggestions, and if tinkering with COLLATE
doesn't help, I would consider contacting MS - the effect of collations on
remote query plans is a fairly obscure area (at least for me), so it might
be worth opening a case with PSS.
Simon|||Thanks for your reply. We will investigate your solution asap.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41efb920$1_1@.news.bluewin.ch...
> "BoB Teijema" <BTE@.euroforum.nl> wrote in message
> news:csnvc4$eeg$1@.reader11.wxs.nl...
> > Hi all,
> >
> >
> >
> > One of our companies is having problems with a query on a linked server.
> > They have two servers, serverA and serverB. On serverA they have set up
a
> > linked server to serverB.
> >
> > Query: select * from oas_company where code = 'TEST'
> >
> > If I look on serverB via Profiler the query is executed without the
where
> > statement.
> >
> > Query: select * from oas_company where tstamp = 0
> >
> > Again via profiler I can see that the where statement is now included.
The
> > tstamp field is a numerical field, the code field is a text field (don's
> > ask
> > me why)
> >
> > So it looks like if there is a selection on a text field (varchar) the
> > where
> > statement is not included.
> >
> > For the above selection this is not a problem as this table only
contains
> > 10 - 20 records. However the real problem is on the oas_balance table.
It
> > should only return 3 records, but because it skips the where statement,
it
> > returns 10.000.000 records.
> >
> > Any idea? I know the default collations are different on both servers,
> > could
> > that be the cause of the problem? And if so, what could I do to overcome
> > this problem?
> >
> >
> >
> > Any suggestions would be greatly appreciated.
> >
> >
> >
> > BoB
> >
> >
> >
> Presumably your real query looks like this? Or are you using OPENQUERY()
> perhaps?
> select * from serverB.MyDB.dbo.oas_company where code = 'TEST'
> You don't mention your MSSQL version(s), but a bit of Googling shows that
> there are sometimes issues with linked servers having different
collations,
> especially with SQL 7:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;276225
>
http://groups.google.ch/groups?q=sql%202000%20linked%20server%20collation&hl
=en&lr=&client=firefox-a&rls=org.mozilla:en-GB:official&sa=N&tab=wg
> The information in BOL (for SQL 2000) seems to suggest that if both
servers
> are running SQL Server, then collations shouldn't be a problem, however it
> isn't particularly clear - see "Optimizing Distributed Queries," for
> example. Assuming you have SQL 2000, you could experiment with using
COLLATE
> in your queries to see if forcing a different collation does make a
> difference:
>
http://groups.google.ch/groups?hl=en&lr=&client=firefox-a&rls=org.mozilla:en
-GB:official&selm=OW9tG29cDHA.2564%40TK2MSFTNGP09.phx.gbl
> If you don't get any more useful suggestions, and if tinkering with
COLLATE
> doesn't help, I would consider contacting MS - the effect of collations on
> remote query plans is a fairly obscure area (at least for me), so it might
> be worth opening a case with PSS.
> Simon
>

server Update Error

Hi,
I have a linked server that is having problems when I run an Update query
against it.
I have a stored procedure in ServerA that updates records on my linked
server, ServerB.
This stored procedure accepts 2 pieces of criteria and applies the Update to
seven tables in the procedure.
A representative line would be:
UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I pass in the two parameters and lets say for @.ImportID I pass in the value
410, when the Procedure is run, it will set the ImportID field to a
different number.
The different number almost always seems to be a number that was ONCE in
this field for a since deleted record.
This behavior is identical in the 7 different tables in this stored
procedure. It happens when I use a .NET Command object (OLEDBCommand in this
case) as well as
when I run the procedure through the Query Analyzer. The only time it does
not happen is when I run a local version of the stored procedure on the
linked server (ServerB) itself:
UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
@.BillingInvoice
I have restarted my servers, shrunk the database, inspected and re-inspected
my Command objects parameters and can not get this odd behavior to go away.
I should note that no errors are returned and all my Select and Delete
queries seem to work fine. But it seems like old values are being restored.
Any ideas?
What operating system are you running on? What is the exact error message
that you are getting?
"Walter" wrote:

> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?
>
>
|||Hi,
Both the Linked Server and the server that calls the linked server are XP
Pro with SQL2k.
As for an error message, there is none. The proc completes without error,
even in QA but when you inspect the results in the tables, you see that the
incorrect values were applied.
"Pat Brisbine" <PatBrisbine@.discussions.microsoft.com> wrote in message
news:0C3AE65A-C357-4CB4-820E-C38A7311CD8F@.microsoft.com...[vbcol=seagreen]
> What operating system are you running on? What is the exact error message
> that you are getting?
> "Walter" wrote:
query[vbcol=seagreen]
Update to[vbcol=seagreen]
value[vbcol=seagreen]
this[vbcol=seagreen]
does[vbcol=seagreen]
BILLG_INVC_ID=[vbcol=seagreen]
re-inspected[vbcol=seagreen]
away.[vbcol=seagreen]
restored.[vbcol=seagreen]
|||> The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
So even when you run the stored proc through query analyzer on ServerA you
do not get the expected results?
It is very strage that you only get the expected results on ServerB. Have
you tried debugging in query analyzer or putting multiple print statements in
to track the values through the life of the procedure?
If you could post the entire stored proc it might help.
"Walter" wrote:

> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?
>
>
|||Walter,
Could this article help you?
http://support.microsoft.com/default...b;en-us;293328
BTW, It says "This problem was first corrected in SQL Server 2000 Service Pack 1"
HTH,
Igor
"Walter" <never@.mind.com> wrote in message news:<OGDykJWhEHA.704@.TK2MSFTNGP12.phx.gbl>...
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?

server Update Error

Hi,
I have a linked server that is having problems when I run an Update query
against it.
I have a stored procedure in ServerA that updates records on my linked
server, ServerB.
This stored procedure accepts 2 pieces of criteria and applies the Update to
seven tables in the procedure.
A representative line would be:
UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I pass in the two parameters and lets say for @.ImportID I pass in the value
410, when the Procedure is run, it will set the ImportID field to a
different number.
The different number almost always seems to be a number that was ONCE in
this field for a since deleted record.
This behavior is identical in the 7 different tables in this stored
procedure. It happens when I use a .NET Command object (OLEDBCommand in this
case) as well as
when I run the procedure through the Query Analyzer. The only time it does
not happen is when I run a local version of the stored procedure on the
linked server (ServerB) itself:
UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I have restarted my servers, shrunk the database, inspected and re-inspected
my Command objects parameters and can not get this odd behavior to go away.
I should note that no errors are returned and all my Select and Delete
queries seem to work fine. But it seems like old values are being restored.
Any ideas?Hi,
Both the Linked Server and the server that calls the linked server are XP
Pro with SQL2k.
As for an error message, there is none. The proc completes without error,
even in QA but when you inspect the results in the tables, you see that the
incorrect values were applied.
"Pat Brisbine" <PatBrisbine@.discussions.microsoft.com> wrote in message
news:0C3AE65A-C357-4CB4-820E-C38A7311CD8F@.microsoft.com...
> What operating system are you running on? What is the exact error message
> that you are getting?
> "Walter" wrote:
> > Hi,
> > I have a linked server that is having problems when I run an Update
query
> > against it.
> >
> > I have a stored procedure in ServerA that updates records on my linked
> > server, ServerB.
> > This stored procedure accepts 2 pieces of criteria and applies the
Update to
> > seven tables in the procedure.
> >
> > A representative line would be:
> >
> > UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> > ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> >
> > I pass in the two parameters and lets say for @.ImportID I pass in the
value
> > 410, when the Procedure is run, it will set the ImportID field to a
> > different number.
> > The different number almost always seems to be a number that was ONCE in
> > this field for a since deleted record.
> >
> > This behavior is identical in the 7 different tables in this stored
> > procedure. It happens when I use a .NET Command object (OLEDBCommand in
this
> > case) as well as
> > when I run the procedure through the Query Analyzer. The only time it
does
> > not happen is when I run a local version of the stored procedure on the
> > linked server (ServerB) itself:
> >
> > UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND
BILLG_INVC_ID=> > @.BillingInvoice
> >
> > I have restarted my servers, shrunk the database, inspected and
re-inspected
> > my Command objects parameters and can not get this odd behavior to go
away.
> > I should note that no errors are returned and all my Select and Delete
> > queries seem to work fine. But it seems like old values are being
restored.
> >
> > Any ideas?
> >
> >
> >|||> The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
So even when you run the stored proc through query analyzer on ServerA you
do not get the expected results?
It is very strage that you only get the expected results on ServerB. Have
you tried debugging in query analyzer or putting multiple print statements in
to track the values through the life of the procedure?
If you could post the entire stored proc it might help.
"Walter" wrote:
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?
>
>|||Walter,
Could this article help you?
http://support.microsoft.com/default.aspx?scid=kb;en-us;293328
BTW, It says "This problem was first corrected in SQL Server 2000 Service Pack 1"
HTH,
Igor
"Walter" <never@.mind.com> wrote in message news:<OGDykJWhEHA.704@.TK2MSFTNGP12.phx.gbl>...
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the value
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in this
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspected
> my Command objects parameters and can not get this odd behavior to go away.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored.
> Any ideas?

server Update Error

Hi,
I have a linked server that is having problems when I run an Update query
against it.
I have a stored procedure in ServerA that updates records on my linked
server, ServerB.
This stored procedure accepts 2 pieces of criteria and applies the Update to
seven tables in the procedure.
A representative line would be:
UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
I pass in the two parameters and lets say for @.ImportID I pass in the value
410, when the Procedure is run, it will set the ImportID field to a
different number.
The different number almost always seems to be a number that was ONCE in
this field for a since deleted record.
This behavior is identical in the 7 different tables in this stored
procedure. It happens when I use a .NET Command object (OLEDBCommand in this
case) as well as
when I run the procedure through the Query Analyzer. The only time it does
not happen is when I run a local version of the stored procedure on the
linked server (ServerB) itself:
UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_ID=
@.BillingInvoice
I have restarted my servers, shrunk the database, inspected and re-inspected
my Command objects parameters and can not get this odd behavior to go away.
I should note that no errors are returned and all my Select and Delete
queries seem to work fine. But it seems like old values are being restored.
Any ideas?What operating system are you running on? What is the exact error message
that you are getting?
"Walter" wrote:

> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update
to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the valu
e
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in th
is
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_I
D=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspect
ed
> my Command objects parameters and can not get this odd behavior to go away
.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored
.
> Any ideas?
>
>|||Hi,
Both the Linked Server and the server that calls the linked server are XP
Pro with SQL2k.
As for an error message, there is none. The proc completes without error,
even in QA but when you inspect the results in the tables, you see that the
incorrect values were applied.
"Pat Brisbine" <PatBrisbine@.discussions.microsoft.com> wrote in message
news:0C3AE65A-C357-4CB4-820E-C38A7311CD8F@.microsoft.com...[vbcol=seagreen]
> What operating system are you running on? What is the exact error message
> that you are getting?
> "Walter" wrote:
>
query[vbcol=seagreen]
Update to[vbcol=seagreen]
value[vbcol=seagreen]
this[vbcol=seagreen]
does[vbcol=seagreen]
BILLG_INVC_ID=[vbcol=seagreen]
re-inspected[vbcol=seagreen]
away.[vbcol=seagreen]
restored.[vbcol=seagreen]|||> The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
So even when you run the stored proc through query analyzer on ServerA you
do not get the expected results?
It is very strage that you only get the expected results on ServerB. Have
you tried debugging in query analyzer or putting multiple print statements i
n
to track the values through the life of the procedure?
If you could post the entire stored proc it might help.
"Walter" wrote:

> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update
to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the valu
e
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in th
is
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_I
D=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspect
ed
> my Command objects parameters and can not get this odd behavior to go away
.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored
.
> Any ideas?
>
>|||Walter,
Could this article help you?
http://support.microsoft.com/defaul...kb;en-us;293328
BTW, It says "This problem was first corrected in SQL Server 2000 Service Pa
ck 1"
HTH,
Igor
"Walter" <never@.mind.com> wrote in message news:<OGDykJWhEHA.704@.TK2MSFTNGP12.phx.gbl>...[vb
col=seagreen]
> Hi,
> I have a linked server that is having problems when I run an Update query
> against it.
> I have a stored procedure in ServerA that updates records on my linked
> server, ServerB.
> This stored procedure accepts 2 pieces of criteria and applies the Update
to
> seven tables in the procedure.
> A representative line would be:
> UPDATE ServerB.MyDatabase.dbo.MyTable SET ImportID = @.ImportID WHERE
> ImportID=0 AND BILLG_INVC_ID= @.BillingInvoice
> I pass in the two parameters and lets say for @.ImportID I pass in the valu
e
> 410, when the Procedure is run, it will set the ImportID field to a
> different number.
> The different number almost always seems to be a number that was ONCE in
> this field for a since deleted record.
> This behavior is identical in the 7 different tables in this stored
> procedure. It happens when I use a .NET Command object (OLEDBCommand in th
is
> case) as well as
> when I run the procedure through the Query Analyzer. The only time it does
> not happen is when I run a local version of the stored procedure on the
> linked server (ServerB) itself:
> UPDATE MyTable SET ImportID = @.ImportID WHERE ImportID=0 AND BILLG_INVC_I
D=
> @.BillingInvoice
> I have restarted my servers, shrunk the database, inspected and re-inspect
ed
> my Command objects parameters and can not get this odd behavior to go away
.
> I should note that no errors are returned and all my Select and Delete
> queries seem to work fine. But it seems like old values are being restored
.
> Any ideas?[/vbcol]