Delete via linked server

I am trying to delete records in a table pointing via linked server to Sybas
e
IQ
The login used in Linked server has all rights to do this
I can select using this linked server
I am gettng this msg, pls help
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"dbo"."all_metric"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation genera
ted
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [Sybase][ODBC Driver][Ada
ptive Server
IQ]Option value out of range: Update operation attempted on a read-only
cursor]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].Look like you're using Msdasql as the provider to your sybase and the remote
user for your linkedserver connection does not have permission to delete.
Also, one cannot do update on a readonly cursor.
If you're certain that you have permission to do update/delete, you can try
passthrough query. See Openquery(), OpenRowset(), or OpenDataSource() in
book online for syntax.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:586713B3-8E88-41D2-ABD6-7F6DC29680E8@.microsoft.com...
> I am trying to delete records in a table pointing via linked server to
Sybase
> IQ
> The login used in Linked server has all rights to do this
> I can select using this linked server
> I am gettng this msg, pls help
> Server: Msg 7345, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' could not delete from table
'"dbo"."all_metric"'.
> User did not have sufficient permission to delete the row.
> [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 provider returned message: [Sybase][ODBC Driver][A
daptive Server
> IQ]Option value out of range: Update operation attempted on a read-only
> cursor]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRow
s
> returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
>|||Remote LOgin i am using does have the permissions
Is there any way to make it work using Linked Server
Why is it a readonly cursor. ? is there any way i could change the ODBC
properties to make it work
"oj" wrote:

> Look like you're using Msdasql as the provider to your sybase and the remo
te
> user for your linkedserver connection does not have permission to delete.
> Also, one cannot do update on a readonly cursor.
> If you're certain that you have permission to do update/delete, you can tr
y
> passthrough query. See Openquery(), OpenRowset(), or OpenDataSource() in
> book online for syntax.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:586713B3-8E88-41D2-ABD6-7F6DC29680E8@.microsoft.com...
> Sybase
> '"dbo"."all_metric"'.
> generated
>
>|||The driver is the connector/translator between the two servers. A certain
provider/driver can only expose a readonly recordset.
You want to make sure you're running the latest driver on both boxes. You
can download latest mdac here:
http://microsoft.com/data
Also, see if you can use OLEDB provider instead of MSDASQL.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:A811897B-4C20-40DA-9F59-CE88B6B0247B@.microsoft.com...[vbcol=seagreen]
> Remote LOgin i am using does have the permissions
> Is there any way to make it work using Linked Server
> Why is it a readonly cursor. ? is there any way i could change the ODBC
> properties to make it work
>
> "oj" wrote:
>
remote[vbcol=seagreen]
delete.[vbcol=seagreen]
try[vbcol=seagreen]
done.][vbcol=seagreen]
Server[vbcol=seagreen]
read-only[vbcol=seagreen]
IRowsetChange::DeleteRows[vbcol=seagreen
]|||Perhaps the table doesn't have a primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"oj" <nospam_ojngo@.home.com> wrote in message news:OFaznY6jEHA.3624@.TK2MSFTNGP10.phx.gbl...[
vbcol=seagreen]
> The driver is the connector/translator between the two servers. A certain
> provider/driver can only expose a readonly recordset.
> You want to make sure you're running the latest driver on both boxes. You
> can download latest mdac here:
> http://microsoft.com/data
> Also, see if you can use OLEDB provider instead of MSDASQL.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:A811897B-4C20-40DA-9F59-CE88B6B0247B@.microsoft.com...
> remote
> delete.
> try
> done.]
> Server
> read-only
> IRowsetChange::DeleteRows
>[/vbcol]|||Good point, Tibor. A similar case can be observed between Access <-> Sqlserv
er.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4czv7%23jEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Perhaps the table doesn't have a primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
news:OFaznY6jEHA.3624@.TK2MSFTNGP10.phx.gbl...
>

Delete via linked server

I am trying to delete records in a table pointing via linked server to Sybase
IQ
The login used in Linked server has all rights to do this
I can select using this linked server
I am gettng this msg, pls help
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"dbo"."all_metric"'.
User did not have sufficient permission to delete the row.
[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 provider returned message: [Sybase][ODBC Driver][Adaptive Server
IQ]Option value out of range: Update operation attempted on a read-only
cursor]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
Look like you're using Msdasql as the provider to your sybase and the remote
user for your linkedserver connection does not have permission to delete.
Also, one cannot do update on a readonly cursor.
If you're certain that you have permission to do update/delete, you can try
passthrough query. See Openquery(), OpenRowset(), or OpenDataSource() in
book online for syntax.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:586713B3-8E88-41D2-ABD6-7F6DC29680E8@.microsoft.com...
> I am trying to delete records in a table pointing via linked server to
Sybase
> IQ
> The login used in Linked server has all rights to do this
> I can select using this linked server
> I am gettng this msg, pls help
> Server: Msg 7345, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' could not delete from table
'"dbo"."all_metric"'.
> User did not have sufficient permission to delete the row.
> [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 provider returned message: [Sybase][ODBC Driver][Adaptive Server
> IQ]Option value out of range: Update operation attempted on a read-only
> cursor]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
> returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
>
|||Remote LOgin i am using does have the permissions
Is there any way to make it work using Linked Server
Why is it a readonly cursor. ? is there any way i could change the ODBC
properties to make it work
"oj" wrote:

> Look like you're using Msdasql as the provider to your sybase and the remote
> user for your linkedserver connection does not have permission to delete.
> Also, one cannot do update on a readonly cursor.
> If you're certain that you have permission to do update/delete, you can try
> passthrough query. See Openquery(), OpenRowset(), or OpenDataSource() in
> book online for syntax.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:586713B3-8E88-41D2-ABD6-7F6DC29680E8@.microsoft.com...
> Sybase
> '"dbo"."all_metric"'.
> generated
>
>
|||The driver is the connector/translator between the two servers. A certain
provider/driver can only expose a readonly recordset.
You want to make sure you're running the latest driver on both boxes. You
can download latest mdac here:
http://microsoft.com/data
Also, see if you can use OLEDB provider instead of MSDASQL.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:A811897B-4C20-40DA-9F59-CE88B6B0247B@.microsoft.com...[vbcol=seagreen]
> Remote LOgin i am using does have the permissions
> Is there any way to make it work using Linked Server
> Why is it a readonly cursor. ? is there any way i could change the ODBC
> properties to make it work
>
> "oj" wrote:
remote[vbcol=seagreen]
delete.[vbcol=seagreen]
try[vbcol=seagreen]
done.][vbcol=seagreen]
Server[vbcol=seagreen]
read-only[vbcol=seagreen]
IRowsetChange::DeleteRows[vbcol=seagreen]
|||Perhaps the table doesn't have a primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"oj" <nospam_ojngo@.home.com> wrote in message news:OFaznY6jEHA.3624@.TK2MSFTNGP10.phx.gbl...
> The driver is the connector/translator between the two servers. A certain
> provider/driver can only expose a readonly recordset.
> You want to make sure you're running the latest driver on both boxes. You
> can download latest mdac here:
> http://microsoft.com/data
> Also, see if you can use OLEDB provider instead of MSDASQL.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:A811897B-4C20-40DA-9F59-CE88B6B0247B@.microsoft.com...
> remote
> delete.
> try
> done.]
> Server
> read-only
> IRowsetChange::DeleteRows
>
|||Good point, Tibor. A similar case can be observed between Access <-> Sqlserver.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u4czv7%23jEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Perhaps the table doesn't have a primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "oj" <nospam_ojngo@.home.com> wrote in message
news:OFaznY6jEHA.3624@.TK2MSFTNGP10.phx.gbl...
>

Delete using a linked server

Hi,

I am trying to delete records in a file on the AS400 using a linked server
and I am getting the following error message:

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"catalog"."schema"."table"'. There was a recoverable, provider-specific error, such as an RPC failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

The linked server is configured as follows:
"server type": other data source
"provider name": Microsoft OLE DB provider for ODBC drivers
"data source" references a system DSN name that uses the driver
"Client Access ODBC driver(32 bit)"
linked server options: "Data access" and "Use remote collation" boxes checked
provider options: "Dynamic parameters", "Nested queries", "Allow in process",
"Non transacted updates" boxes checked

The file on the AS400 is journaled and has a unique key.

The Select and Insert statements work like a charm but I can not for the life of me get the Delete or Update statements to work.

I have been struggling with this problem for over a month, so any help whatsoever would be tremendously appreciated.

Thanks!On the properties page for the linked server, are the RPC and RPC OUT check boxes checked?

That said, I never tried manipulating data on a linked server; only selects. Also, there is a different driver for AS/400s, I' racking my brain for the name, but it's not provided by IBM.

Regards,

hmscott|||Thanks for the reply.

I tried checking the RPC and RPC out boxes and I get the same error message.

As for the other driver, you're probably thinking of OLE DB provider for DB2.
I've have also tried using this driver without success.

My hunch is that there is an ODBC provider property(such as DBPROPSET_PROVIDERROWSET) that's not configured to handle updates or deletes, but I don't know how to display this information.

Are you familiar with OLE DB providers for ODBC or can you direct me to a
resource?|||Can you post the delete and update statements.|||Here's the statement I'm using in query analyzer.

DELETE FROM AS400LINKEDSERVER.SYSTEMNAME.LIBRARYNAME.FILENAME

The four part names have been changed here for privacy.

I have also tried OPENQUERY. Again, same error message.

Thanks.

DELETE statement conflicted

Hello

I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.

However, when I tried to delete a row in the first table I saw this error:

DELETE FROM [dbo].[Names_DB]
WHERE [LName_Name]=N'andrews'

Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.

I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.

Please advise.

I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.

Thanks

Regards

Lynn

Have you enabled the Cascade Delete Related Records for all the relations of the maintable and related tables etc? I think you have missed it somewhere.|||

Hello Fredrik

Thanks for the swift reply.

No I have not enabled the Cascade Delete Related Record I didn't know this was required, I am still a novice. Yes I have certainly missed this.

Now that I understand that this is required, I need to learn how to do this. Can you please direct me to a suitable turorial or perhaps explain how this is carried out and what is the required code for this process?

Thanks

Lynn

|||

When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.

Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)

|||

Fredrik N:

When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.

Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)

Hello Fredrik

Within table properties where do I enable the delete to the existing tables. Can I add update also?

Thanks

Lynn

|||

Hi

You can add constrains to a table it could be something like this:

CREATE TABLE Books ( BookIDINTNOT NULLPRIMARY KEY, AuthorIDINTNOT NULL, BookNameVARCHAR(100)NOT NULL, PriceMONEYNOT NULL)GOCREATE TABLE Authors ( AuthorIDINTNOT NULLPRIMARY KEY,Name VARCHAR(100)NOT NULL)GOALTER TABLE BooksADD CONSTRAINT fk_authorFOREIGN KEY (AuthorID)REFERENCES Authors (AuthorID)

ON DELETE CASCADE

ON UPDATE CASCADEGOYou can take a look atDatabase Objects: Constraints for more

Hope this helps.|||

Hi Thanks for the post and the info.

As I have already created tables I am a little wary about altering tables in case I loose the data.

The tables I have already have foreign keys and primary keys.

I have a parent table called: DomNames

Primary Key = DomNamesID

I have recently inserted a foreign key:

Foreign Key = CatA_ID (taken from the Catagory A child table)

Then I have child category tables from A - Z

In category table A

Primary Key = CatA-ID

Foreign Key = DomNamesID (taken from the DomNames table)

In category table B

Primary Key = CatB-ID

Foreign Key = CatA_ID (taken from the previous A category table)

Each following table uses the Category ID alphabetical letter as a primary key and the previous tables Category ID as the foreign key, each table has a DomNameID column.

All tables contain data.

Do I miss out the first section of the code you mentioned and just put this:

ALTER TABLE DomNames
ADD CONSTRAINT fk_CatA_ID
FOREIGN KEY (CatAID)
REFERENCES DomNames (DomNamesID)

ON DELETE CASCADE

ON UPDATE CASCADE

I would be grateful if you would confirm before I alter my database.

Thanks

Regards

Lynn

delete sql syntax for linked notes table

I am hoping this is a quick easy question for someone! :)

I am trying (struggling) with moving data from Sql Server to a Lotus
Notes table.

I am using SQL Server 2000, I have a Lotus Notes linked server (using
NotesSQL), and I wasnt to clear the table (delete all records) and then
reload it from my data on SQL Server.

What is the syntax to delete the records?

My select statement would be like this:
select * from openquery([LinkedServer], 'select * from NotesTable')
where lastName='Smith'

My delete statement ? -- cant quite figure out the syntax of this
one...
select * from openquery([LinkedServer], 'delete from NotesTable') where
lastName='Smith'
(this doesnt work)

Thanks!ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> My delete statement ? -- cant quite figure out the syntax of this
> one...
> select * from openquery([LinkedServer], 'delete from NotesTable') where
> lastName='Smith'
> (this doesnt work)

DELETE LinkedServer...NotesTable

You may need something between the dots as well, but I don't know Lotus
Notes.

--
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|||Yes, I dont think I can use this syntax.

select * from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name]

delete from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name] where
CaseNum='PROD055344'

Returns:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the
necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].|||ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> Yes, I dont think I can use this syntax.
> select * from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name]
> delete from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name] where
> CaseNum='PROD055344'
> Returns:
> Server: Msg 7312, Level 16, State 1, Line 1
> Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
> four-part name was supplied, but the provider does not expose the
> necessary interfaces to use a catalog and/or schema.
> OLE DB error trace [Non-interface error].

You should certainly not specify dbo for something in Lotus Notes,
as dbo is very SQL Server-specific.

Try one of

select * from [Notes_DRS_CaseName DEV].[CaseName]..[case_name]
select * from [Notes_DRS_CaseName DEV]...[case_name]

You could also try

delete from openquery(LinkedServer, 'SELECT * FROM ...')

although it looks completely crazy!

--
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|||I tried all different versions of the selects... no luck.
Still the same "no schema exposed message"

Here is the error from the delete...

delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
) where CaseNum='PROD007586'

Server: Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB
provider 'MSDASQL' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
returned 0x80004002].|||Hi

Try using two part names for your oracle table by adding the schema that
your oracle table is in.

John

"ProgrammerGal" <carolyn_graf@.ahm.honda.com> wrote in message
news:1145398887.423877.242610@.t31g2000cwb.googlegr oups.com...
>I tried all different versions of the selects... no luck.
> Still the same "no schema exposed message"
>
> Here is the error from the delete...
> delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
> ) where CaseNum='PROD007586'
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB
> provider 'MSDASQL' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
> returned 0x80004002].|||John Bell (jbellnewsposts@.hotmail.com) writes:
> Try using two part names for your oracle table by adding the schema that
> your oracle table is in.

ProgrammerGal is using LotusNotes... (Of course, I don't know LotusNotes
at all. Maybe there is an Oracle database in the bottom?)

--
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|||ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> I tried all different versions of the selects... no luck.
> Still the same "no schema exposed message"
>
> Here is the error from the delete...
> delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
> ) where CaseNum='PROD007586'
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB
> provider 'MSDASQL' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
> returned 0x80004002].

I'm afraid that I'm out of ideas. Maybe you should try a LotusNotes forum,
to here if anyone in that community has been able to solve this.

--
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|||Hi Erland

You are right I confused the ODBC OLE DB Provider MSDASQL with the Oracle
OLE DB Provider MSDAORA!

John

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AA642F4AEE2Yazorman@.127.0.0.1...
> John Bell (jbellnewsposts@.hotmail.com) writes:
>> Try using two part names for your oracle table by adding the schema that
>> your oracle table is in.
> ProgrammerGal is using LotusNotes... (Of course, I don't know LotusNotes
> at all. Maybe there is an Oracle database in the bottom?)
>
>
> --
> 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|||Hi

Check out http://tinyurl.com/kj3ow on how to determine how to use 4 part
naming. I am not sure if you need this now
http://www.databasejournal.com/feat...cle.php/3462011 but you
may want to check it out.

You may also want to check that this is not a read-only interface, also see
if there is a OLEDB interface available so you don't have to go through
ODBC.

John

"ProgrammerGal" <carolyn_graf@.ahm.honda.com> wrote in message
news:1145398887.423877.242610@.t31g2000cwb.googlegr oups.com...
>I tried all different versions of the selects... no luck.
> Still the same "no schema exposed message"
>
> Here is the error from the delete...
> delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
> ) where CaseNum='PROD007586'
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB
> provider 'MSDASQL' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
> returned 0x80004002].

Delete query in Linked server

Hi,
I have a pair of SQL Server 2005 (SQL Server 2005 - 9.00.3054.00 Build 3790:
Service Pack 2) running on Windows 2K OS. Linked server is established and
working fine for all type of queries except the following delete query.
DELETE A from ttdb1.sasixp.sasi.aprn7030 A
WHERE
not EXISTS ( SELECT stulink
FROM ttdb1.sasixp.sasi.astu7030 B
WHERE B.stulink = A.stulink )
But the same query is working fine in test server. Test SQL Server 2005
version is SQL Server 2005 - 9.00.3042.00 Build 3790: Service Pack 2). But
production server ver is SQL Server 2005 - 9.00.3054.00 Build 3790: Service
Pack 2. This is the only difference between test server env & production
env.
When I run this query got the following error:
OLE DB provider "SQLNCLI" for linked server "ttdb1" returned message
"Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.".
Msg 7202, Level 11, State 2, Line 1
Could not find server 'TTDB1' in sys.servers. Verify that the correct server
name was specified. If necessary, execute the stored procedure
sp_addlinkedserver to add the server to sys.servers.
Any idea what causes this problem?
Thanks in advance for your help.
VenkatPlease read the post below, I had the same issue and got it fixed by changing
the servername.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=274270&SiteID=1
Regards,
Henry
"Venkat N" wrote:
> Hi,
> I have a pair of SQL Server 2005 (SQL Server 2005 - 9.00.3054.00 Build 3790:
> Service Pack 2) running on Windows 2K OS. Linked server is established and
> working fine for all type of queries except the following delete query.
> DELETE A from ttdb1.sasixp.sasi.aprn7030 A
> WHERE
> not EXISTS ( SELECT stulink
> FROM ttdb1.sasixp.sasi.astu7030 B
> WHERE B.stulink = A.stulink )
> But the same query is working fine in test server. Test SQL Server 2005
> version is SQL Server 2005 - 9.00.3042.00 Build 3790: Service Pack 2). But
> production server ver is SQL Server 2005 - 9.00.3054.00 Build 3790: Service
> Pack 2. This is the only difference between test server env & production
> env.
> When I run this query got the following error:
> OLE DB provider "SQLNCLI" for linked server "ttdb1" returned message
> "Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.".
> Msg 7202, Level 11, State 2, Line 1
> Could not find server 'TTDB1' in sys.servers. Verify that the correct server
> name was specified. If necessary, execute the stored procedure
> sp_addlinkedserver to add the server to sys.servers.
> Any idea what causes this problem?
> Thanks in advance for your help.
> Venkat
>
>