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 of mirrored rows

hi.
I've seen ways to delete duplicate rows.
Can someone give me some sql to do this?

I have a table with varchar table_name_start, varchar column_name,
varchar table_name_end;

it has rows like this:
table1 col1 table2
table1 col2 table 3
table2 col1 table1

I'd lke to delete the rows if they exist with the names swapped
around, i.e. like above since the first and third share a column name
and the table_name_start/end matches the others table_name_end/start,
I'd like to delete one and leave the other.

I'm scratching my head trying to figure this out.

thanksWhat is the key of this table? Please post proper DDL so that we don't
have to guess. I'm going to assume that the combination of all three
columns is unique, in which case try this:

DELETE FROM tbl
WHERE EXISTS
(SELECT *
FROM tbl AS T
WHERE T.table_name_start = tbl.table_name_end
AND T.table_name_end = tbl.table_name_start
AND T.column_name = T.column_name)
AND table_name_start > table_name_end ;

--
David Portas
SQL Server MVP
--|||On 6 Oct 2005 01:00:14 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>What is the key of this table? Please post proper DDL so that we don't
>have to guess. I'm going to assume that the combination of all three
>columns is unique, in which case try this:
>DELETE FROM tbl
> WHERE EXISTS
> (SELECT *
> FROM tbl AS T
> WHERE T.table_name_start = tbl.table_name_end
> AND T.table_name_end = tbl.table_name_start
> AND T.column_name = T.column_name)
> AND table_name_start > table_name_end ;
>--
>David Portas
>SQL Server MVP
David,
Very sorry but I did not realize that would make a difference. (really
I didn't).
There actually was not a primary key (this was a temporary working
table).
I've put a primary key into place now, but I used the script before
that.
It seemed to work fine I've never used the 'AS' before.. I guess I
better study up a bit on it.
Oh.. the ddl was this:
CREATE TABLE [allEdges] (
[table_name_start] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[column_name] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[table_name_end] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO|||In this case the key makes a difference to the extent that my DELETE
statement will delete "mirrored" rows but won't delete all duplicates
(all three columns identical). For that you would need a key, a cursor
or an intermediate table. Also, my DELETE won't remove rows with NULLs,
which I can see may be an issue now that you've posted DDL with
nullable columns.

In any case, it makes sense to include keys with your DDL or to state
that your table doesn't have a key. Keys and constraints can make a big
difference to the solution.

--
David Portas
SQL Server MVP
--|||On 6 Oct 2005 03:53:10 -0700, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>In this case the key makes a difference to the extent that my DELETE
>statement will delete "mirrored" rows but won't delete all duplicates
>(all three columns identical). For that you would need a key, a cursor
>or an intermediate table. Also, my DELETE won't remove rows with NULLs,
>which I can see may be an issue now that you've posted DDL with
>nullable columns.
>In any case, it makes sense to include keys with your DDL or to state
>that your table doesn't have a key. Keys and constraints can make a big
>difference to the solution.
>--
>David Portas
>SQL Server MVP
I understand completely. Thanks for the edifications.
(that's what we're here for, eh?)
Cheers|||another way of removing duplicates is to
select distinct * into new table
drop old table
rename the new table

Could be much faster|||On 6 Oct 2005 11:32:52 -0700, "Alexander Kuznetsov"
<AK_TIREDOFSPAM@.hotmail.COM> wrote:

>another way of removing duplicates is to
>select distinct * into new table
>drop old table
>rename the new table
>Could be much faster
I'm not sure because they are not exact duplicates.. just mirrors