Linking Microsft Access in Sql

Is it possible to link a Microsft Access table in Sql? What I am hoping to d
o
is to update an Access table using a DTS package. I need to join a SQL table
and a Access table and use the results to update anouther Access table. Is
this possible.
Thank you in advanceBob,
Sure - see:
sp_addlinkedserver
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/d... />
a_6e26.asp
HTH
Jerry
"bob at zachys" <bobatzachys@.discussions.microsoft.com> wrote in message
news:8B29F201-D4C7-4981-8E6B-26CAA6C7E855@.microsoft.com...
> Is it possible to link a Microsft Access table in Sql? What I am hoping to
> do
> is to update an Access table using a DTS package. I need to join a SQL
> table
> and a Access table and use the results to update anouther Access table. Is
> this possible.
> Thank you in advance

Linking a stored procedure to a button

Hi,

I have a web form linked to a SQL database.

I have created some stored procedures to Select, Update, Insert, and Delete records in the database.

Please could some tell me how to link buttons to call the stored procedures

I am new to ASP.NET.

Thank you.from BOL:

The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server? 2000.Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.

you might just want to use a regular table instead of temp table...
hth|||Hi, I do not have an anwser for question, but wanted to ask you if could send some code for the stored procedures to Select, Update, Insert, and Delete records, since I am trying to sort this out myself. Regards, Mikesql

Linking a Excel file to a Table

I have an excel file that I need to copy a column from the file to update a
column in the SQL Table. Is this possible, if so, how I can accomplish this
task?
Thanks in advance
Check out linked servers in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:F5C27639-4876-4777-A019-211D06D1A29E@.microsoft.com...
I have an excel file that I need to copy a column from the file to update a
column in the SQL Table. Is this possible, if so, how I can accomplish this
task?
Thanks in advance
sql

Linking a Excel file to a Table

I have an excel file that I need to copy a column from the file to update a
column in the SQL Table. Is this possible, if so, how I can accomplish this
task?
Thanks in advanceCheck out linked servers in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:F5C27639-4876-4777-A019-211D06D1A29E@.microsoft.com...
I have an excel file that I need to copy a column from the file to update a
column in the SQL Table. Is this possible, if so, how I can accomplish this
task?
Thanks in advance

servers to DB2 and AS400

Hi!
I would like to set up linked servers to DB2 and AS400 in SQL server and update the tables in these two databases via stored procedures in SQL servers.

I have read articles on Microsoft site which indicate installing Microsoft Host integration server and use SNA server 4.0 Service pack 4.0 to

configure DB2OLEDB drivers.

Could any one suggest how do go about doing this.

Do I need to buy Host integration server or is there any other way to do it.

your help is much appreciated

NalinaThere are more ways to skin a cat than there are cats. I can think of several ways to do this, from several different vendors.

My suggestion would be to approach your vendor of choice (probably IBM in this case), and ask them to propose a solution. If that solution works for you, and is affordable, I'd run with it. If not, open up the bidding to at least two more vendors (and inform your vendor of choice, of course), then see what happens.

The biggest problem that I see is that when you are doing this kind of integration, there are many factors that come into play. Without considerable knowledge of your circumstances it would be easy to lead you astray and cost you an enormous amount of extra money or create a solution that was only part of what you need.

-PatP

servers and transcations

Hello

I'm trying to run an update on data in one SQL Server, based on data from another SQL Server (they're linked). It seemed to take forever, even with just one row in the table, so I tried making a dummy query that just printed some stuff on the screen. This also took forever, so I tried doing the same thing, but not as a transaction. Then it worked perfectly!

What is the problem with linked servers and transactions? Does anybody have an explanation as to why they can't be combined? Or even better, does anybody have a solution for combining them?

Thanks

Martin N Jensenwhen you do transactions you log the info

are your log files easily accessible ?|||Originally posted by Karolyn
when you do transactions you log the info
are your log files easily accessible ?

The transaction log files are stored with the datafiles as per the default installation settings. Do I need to assign special rights to some account in order to run the transaction?

Then again, wouldn't it only be the transaction log files on the machine on which I'm running the transaction from that are accessed?

MNJ|||check the Modification date of the log files on the different machines

and try to set the logging to off before the updating
to see if the command runs fastersql

servers - Timeouts

I have a basic UPDATE that's something along the lines of:

UPDATE LocalTable SET LocalColumn = RemoteColumn
FROM LocalTable INNER JOIN server.catalog.dbo.RemoteTable ON (...)

After running in Query Analyzer for ten minutes, I get:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute returned 0x80040e31: Execution terminated by the provider because a resource limit was reached.].

I checked the linked server settings in EnterpriseManager and both timeout settings are at 0 (no timeout). Any idea how I can remove whatever timeout is causing this?

Anyone know how remote join performance compares to moving remote data over via DTS, running an UPDATE, and dropping the data?Found the answer in old archived forums:

sp_configure @.configname='remote query timeout (s)', @.configvalue=0

Thanks everyone!

server via ODBC and stored procedure

Hi

We're trying to use call a stored procedure to update information in a
remote Ingres database. We've linked the server, and can read
information using SELECT * FROM OPENQUERY (........), but we can't
find a suitable syntax for executing a procedure.

Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
string gives a message about not returning any columns - not surprising
as there aren't any, and trying to execute the procedure more directly
using:-

EXECUTE abrs..vipdba.ats_reader_pi0 ......

Gives the error

Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
[OLE/DB provider returned message: Parameter type cannot be determined
for at least one variant parameter.]

Any bright ideas?

Chloe(chloe.crowder@.bl.uk) writes:
> We're trying to use call a stored procedure to update information in a
> remote Ingres database. We've linked the server, and can read
> information using SELECT * FROM OPENQUERY (........), but we can't
> find a suitable syntax for executing a procedure.
> Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
> string gives a message about not returning any columns - not surprising
> as there aren't any, and trying to execute the procedure more directly
> using:-
> EXECUTE abrs..vipdba.ats_reader_pi0 ......
> Gives the error
> Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
> [OLE/DB provider returned message: Parameter type cannot be determined
> for at least one variant parameter.]

Assuming that you are on SQL 2000:

Does the procedure have any "difficult" parameters?

First of all, I would examine whether there is an OLE DB provider
for Ingres, rather than using the MSDASQL provider.

If there is no OLE DB provider available, I would first try a parameterless
stored procedure. If this fails, then it seems that the ODBC driver
have problems to retrieve parameter information at all.

If there is a tool similar to Profiler on the Ingres side, you could
use that to see what calls the ODBC driver makes.

One thing that looks suspicious to me is that the third component is
empty, but I don't know Ingres, so this may be alright.

Unfortuantely, linked servers to other products can be a bit of trial
and error. There is a generic OLE DB layer which you have little control
over.

If you are on SQL 2005, there may be an easy way out. To wit you
can say:

EXEC('ingres-SQL here') AT abrs

to send a pass-through query.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

server Update Problem

I am getting an error trying to update a table in DB2 via SQL Server using a Linked Server. I can query the table using OPENQUERY but not via four-part name.

I can do this...

SELECT

Select * from OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn = 2')

When I try to Query using four-part names I get the following error...

7399 OLE DB Provider 'MSDASQL' reported an error. The provider does not support the necessary method.

UPDATE

When I try to update using this...

Update OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn=2') Set frst_nm = 'Fred'

I get the following error...

The OLE DB provider 'MSDASQL' indicates that the object has no columns.

When I try to update using four part names I get the error that says that the provider does not support the necessary method.

OTHER INFO

I was originially using version 6.1 of the db2 client (db2odbc.dll) but tried the 7.1 client and got the same error.

I believe we are using version 6.1 of db2 connect.

The mainframe db2 is version 7.0 release 1.0

Any help would be greatly appreciated. I referenced Microsoft Knowledge Base Article #270119 to get the workarounds that I attempted...

GaryKBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;270119) to resolve the update error issue.|||Satya, thanks for the reply but in my post I referenced this KBA. Neither of the workarounds mentioned by microsoft worked.

Originally posted by Satya
KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;270119) to resolve the update error issue.

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

Does Anyone know what this means?

I run the query in the attached file on multiple servers with success but some of them refuse to cooperate. I've tried recreating the linked server on the problem servers but this doesn't help. All I want to do is check db file size and free space and get it to work. Why does it have to be so damn complicated?

Any ideas welcome - including an entirely different way of doing this.

The script and table definitions in question are attached

Could not open table '"Helpdesk_New"."dbo"."LogStats"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...The script fails at this line:

Update RemoteServer.RemoteDB.dbo.LogStats
Set Servername = @.Servername

Graham

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]