Deleting Parameter Error

I have a report with 2 parameters, that are part of a hierarchy, that I want
to delete. I delete the query parameter via the designer and the query
mapping dialog and then the report parameters. When I build I get an error
saying the the query parameter I have deleted cannot find the the deleted
reporting parameter. In other situations this deletion seems to work - could
the hierarchy be a factor. This is SO frustrating. Can anyone point me in
the right direction or is it a bug. Regards, Chris.open up the file in XML and do a search on the deleted parameter name. that
will help you find where to look in the designer.
Basically there are three general places for parameters to live, in the
Report Parameters applet; Data Source -->parameters tab or Data Source Text
window depending on whether you are using a sproc or sql code; and in text
boxes in the layout, usually in the header section.
the easiest place to over look is the layout where sometimes parameters are
sometimes displayed or used as filters.
"Fresno Bob" wrote:
> I have a report with 2 parameters, that are part of a hierarchy, that I want
> to delete. I delete the query parameter via the designer and the query
> mapping dialog and then the report parameters. When I build I get an error
> saying the the query parameter I have deleted cannot find the the deleted
> reporting parameter. In other situations this deletion seems to work - could
> the hierarchy be a factor. This is SO frustrating. Can anyone point me in
> the right direction or is it a bug. Regards, Chris.
>
>|||I wouldn't know what to do with the report in XML format. I have looked in
there an I can see the parameter cropping as a query parameter in an
unrelated report parameter.
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:4EFB8F4F-0670-4898-B557-F1AB625CE083@.microsoft.com...
> open up the file in XML and do a search on the deleted parameter name.
> that
> will help you find where to look in the designer.
> Basically there are three general places for parameters to live, in the
> Report Parameters applet; Data Source -->parameters tab or Data Source
> Text
> window depending on whether you are using a sproc or sql code; and in text
> boxes in the layout, usually in the header section.
> the easiest place to over look is the layout where sometimes parameters
> are
> sometimes displayed or used as filters.
> "Fresno Bob" wrote:
>> I have a report with 2 parameters, that are part of a hierarchy, that I
>> want
>> to delete. I delete the query parameter via the designer and the query
>> mapping dialog and then the report parameters. When I build I get an
>> error
>> saying the the query parameter I have deleted cannot find the the deleted
>> reporting parameter. In other situations this deletion seems to work -
>> could
>> the hierarchy be a factor. This is SO frustrating. Can anyone point me in
>> the right direction or is it a bug. Regards, Chris.
>>

Deleting duplicate records from a table.....

I loaded one table via SSIS and found that it contained many duplicate records (from the input source). I can create a SQL task to delete them, but I wonder if SSIS offers and task "out of the box" to delete dups?

TAI,

barkingdog

I don't know about anything in SSIS to do so but here's a great way to do it using CTE's and Row_Number()

http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp

|||

Use a Sort transform from SSIS is a possible alternation - Sort on certain keys and check "remove duplicate records" at Sort transform.

hth

wenyang

Deleteing a RO Warm Standby

I restored a a dB and for what ever resaon, it is a warm
standby that is replicated. I am attempting to delete
the dB via EP, but...
I cannot delete it because it is being used for
replication.
I cannot remove replication because it is read-only.
I cannot remove the read-only flag because it is a warm
stand by.
HELP!!!
How do I go about getting rid of this dB?
Larry...
Larry,
before altering the database in any way you'll need to recover it. Please
try this script:
restore database xxx with recovery
go
exec sp_removedbreplication xxx
go
use master
go
drop database xxx
go
HTH,
Paul Ibison
|||Thanks, this did the trick!!!

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 trigger & COM+

Hi, I'm building a website that interacts with MS SQLserver(v7) via a Buisiness Rule-layer developed in COM+ with VB(v6 sp5). The website is an add-on on a existing client/server app. Now I have delete triggers on almost every table and they work perfectly in the c/s app and when runned in the query analyzer. But when I try to do the same with COM+ I get the the next error message on line 10:
"Cannot use SAVE TRANSACTION within a distributed transaction."

Here follows the code. Hopefully anybody can help me with this problem.

SQL-statement:

Function getDeleteRequestSQL(ByRef strRequestId As String) As String
Dim strSQL As String
strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'"
getDeleteRequestSQL = strSQL
End Function

And then the place where the error occurs.

GetConnection cnConn
strSQL = getDeleteRequestSQL(reqId)
10 cnConn.Execute strSQL, , adExecuteNoRecords

And finaly the trigger:

create trigger td_thmld2 on thmld2 for delete as
begin
declare
@.numrows int,
@.errno int,
@.errmsg varchar(255)

select @.numrows = @.@.rowcount
if @.numrows = 0
return

select @.numrows = (select count(*) from trigstat with (nolock) )
if @.numrows > 0
return

save transaction trans_td_thmld2 <-- REASON FOR ERROR?

/* delete all children in "thmstat" */
delete thmstat
from thmstat t2, deleted t1
where t2.hdmcode = t1.hdmcode

/* delete all children in "thmldlk1" */
delete thmldlk1
from thmldlk1 t2, deleted t1
where t2.hdmlmldcode = t1.hdmcode

/* errors handling */
error:
raiserror @.errno @.errmsg
rollback transaction trans_td_thmld2
endDon't know what that is, but it has nothing to do with the trigger...

Well that me guessing again...

Sounds more like you're establishing a tran across many servers...|||The components I've created are all located within one dll. The dll is active in the component services on my own webserver and is working fine. So the action isn't distributed over the network. :(

Delete statement times out and blocks reads

Hi. Periodically I need to run a delete statement that deletes old data. The problem is that this can timeout using ODBC (via the CDatabase and CRecordSet classes in legacy code). Also, while its running the delete, the table its operating on is locked and my application can't continue to run and operate on rows not affected by the delete.

Are there any workarounds for this? Can the timeout be set in the connect string?

Thanks,

Brian

No, you will have to set the timeout value on the appropiate command object.

Jens K. Suessmeyer

http://www.sqlserver2005.de

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