Sql Cannot connect: table

Showing posts with label table. Show all posts

Thursday, March 29, 2012

Cannot execute sub-report in list or table

I have a report that needs to invoke a sub-report for each row in a dataset.
I can execute the sub-report manually without problems. I can place the
sub-report on the main report and set the parameter to the first ID in my
recordset, and the report executes. If I place the sub-report in a list or
table, I get the following error:
An error has occurred while processing a sub-report. Details: Object
reference not set to an instance of an object
For both the table and list, I just added the container control to the main
report and just set the data source. I then add the subreport to the
container and hook up the parameters. I don't modify any other attributes
on the container. To experiment, I added a list to my main report and then
dragged the single-instance sub-report control that was working into the
container but I recieved the same error (I then drag the sub-report to
outside of the container and it renders just fine). I tried hard-coding the
parameters, but I continue to receive the error. I recieve this error in
both VS 2005 and SRS 2005 SP1.
Since the sub-report will execute outside the container, I believe I am
doing something incorrectly with having the sub-report in a container.
1) Does SRS support invoking a sub-report in a container, once per row?
2) If so, what is the recommend container? List, Table, or Matrix?
3) I found the log files to be of little help. I'm including the stack
trace for this error below. Any suggestions on how to troubleshoot this
issue?
Any help would be appreciated
- Chris
==============[ BEGIN STACK TRACE]=============================== w3wp!library!6!09/22/2006-15:50:27:: i INFO: Call to CreateReport( 'Trip',
'/BBSReporting', def)
w3wp!library!6!09/22/2006-15:50:27:: i INFO: Call to SetReportDefinition(
'/BBSReporting/Trip', def )
w3wp!library!7!09/22/2006-15:51:35:: i INFO: Call to GetPermissions:/
w3wp!library!7!09/22/2006-15:51:35:: i INFO: Call to GetSystemPermissions
w3wp!library!e!09/22/2006-15:51:40:: i INFO: Call to
GetPermissions:/BBSReporting
w3wp!library!e!09/22/2006-15:51:40:: i INFO: Call to GetSystemPermissions
w3wp!library!1!09/22/2006-15:51:43:: i INFO: Call to
GetPermissions:/BBSReporting/Trip
w3wp!library!1!09/22/2006-15:51:43:: i INFO: Call to GetSystemPermissions
w3wp!library!7!09/22/2006-15:51:50:: i INFO: Call to
GetPermissions:/BBSReporting/Trip
w3wp!library!1!09/22/2006-15:51:50:: i INFO: Call to GetSystemPermissions
w3wp!library!7!09/22/2006-15:51:52:: i INFO: Call to RenderFirst(
'/BBSReporting/Trip' )
w3wp!processing!7!9/22/2006-15:51:56:: e ERROR: An error has occurred while
processing a sub-report. Details: Object reference not set to an instance of
an object. Stack trace:
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessingContext.GetScopeValues(GroupingList
containingScopes, IScope containingScope)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateSubReportInstance(SubReport
subReport, ProcessingContext processingContext, Int32 index, IScope
containingScope, String& label)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateInstance(ReportItem
reportItem, Boolean setupEnvironment, Int32 index)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateInstances(ReportItemColInstance
collectionInstance, ReportItemCollection reportItemsDef)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.Merge.CreateInstances(ParameterInfoCollection
parameters, Boolean noRows)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.Merge.Process(ParameterInfoCollection
parameters, Boolean mergeTran)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateSubReportInstance(SubReport
subReport, ProcessingContext processingContext, Int32 index, IScope
containingScope, String& label)
w3wp!webserver!7!09/22/2006-15:51:57:: i INFO: Processed report.
Report='/BBSReporting/Trip', Stream=''Hello Chris,
Based on my research, the sub report could be in the data region such as
table, matrix and list.
The error message showes that the seb report could not render correctly.
Since you could render the sub report correctly out of the data region, it
may be related with your parameter.
Would you please let me know the parameter you use in the sub report?
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Wei,
The sub-report takes a single integer parameter named CompanyID. I have a
recordset that returns a CompanyID. I bound the recordset to the list
control. I have the Parameter Name set to CompanyID and the Paramter Value
set to =Fields!CompanyID.Value. I selected both using the Sub-Report
Properties dialog box, the Parameters tab, using the dropdown arrows.
To continue to troubleshoot the issue, I've done the following:
=========================* Added a textbox to the list that displays the CompanyID value
(=Fields!CompanyID.Value). It displays the expected value.
* I attempted to hard-code the parameter value using the following
syntax:
="100"
=100
100
=CInt(100)
=CInt("100")
* I'm using the following SQL to simplify the main report: SELECT 100 as
CompanyID
* I created a new Main Report that had no controls other than the
sub-report. I used the query above, binding the sub-report parameter to
=First(Fields!CompanyID.Value, "DataSet1"). The sub-report renders. If I
add a list control, bind it to the dataset, drag the sub-report into it, it
will not render. If I drag it out of the list control, it will render. I
didn't change a thing on the sub-report control.
Is there anyway to find out what *exactly* is NULL?
I appreciate your assistance with this.
- Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:dtkYvMG4GHA.4280@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Based on my research, the sub report could be in the data region such as
> table, matrix and list.
> The error message showes that the seb report could not render correctly.
> Since you could render the sub report correctly out of the data region, it
> may be related with your parameter.
> Would you please let me know the parameter you use in the sub report?
> Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
Based on my test, I put the sub-report into the List control successfully.
I would like to send the sample to you. To get the sample, could you send
me a email to me? Please remove the ONLINE in my email address. I will keep
the information secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I sent my email address yesterday morning.
This afternoon I was able to recreate the problem with simple reports. If
necessary, I can send this to you.
Create three (3) reports, each with a single textbox with their name. I
named them "Report A", "Report B", and "Report C".
I add "C" to "B" as a sub-report. B renders fine - I see B and C.
I add "B" to "A" as a sub-report. A renders fine - I see A, B, and C.
I then added a dataset to A.
Declare @.Report table ( ID int )
INSERT INTO @.Report values (1);
INSERT INTO @.Report values (2);
SELECT * FROM @.Report
I added a List control and bound it to the dataset.
I then added a second instance of report B to the list.
At this point I get: Warning 1 [rsErrorExecutingSubreport] An error occurred
while executing the subreport 'subreport1': Object reference not set to an
instance of an object. [Path]\ReportA.rdl 0 0
I'm not accessing the database.
I'm not passing parameters.
If I add Report C to the list, it renders correctly, but B still does not.
This leads me to believe that there may be an issue with multiple levels of
nested reports within a list. This should be very easy to recreate. I can
zip my samples and send them to you if need be.
Visual Studio 2005
SQL Server Reporting Services 2005 SP1
Thanks,
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:tFEApXf4GHA.4344@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Based on my test, I put the sub-report into the List control successfully.
> I would like to send the sample to you. To get the sample, could you send
> me a email to me? Please remove the ONLINE in my email address. I will
> keep
> the information secure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
Thanks for the example of your issue.
I have reproduced it on my side.
I checked our internal database and found a workaround:
In "ReportB", you could add a List control and bound to a dataset which
have only one record. Then put the subreport for "ReportC" in the list
control.
Then in the "ReportA", you could add the subreport and put it in to the
list control without any error.
Please try this workaround on your side and let me know the result. Thank
you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I was able to resolve this as you suggested, but I ended up having to modify
other reports as once I added the report in question to the "dummy" list
control, any subsequent sub-reports resulted in the same error.
Any ideas when this will be fixed? A hot fix, SP2, etc?
Thanks for your help with this.
- Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:jceAud54GHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Thanks for the example of your issue.
> I have reproduced it on my side.
> I checked our internal database and found a workaround:
> In "ReportB", you could add a List control and bound to a dataset which
> have only one record. Then put the subreport for "ReportC" in the list
> control.
> Then in the "ReportA", you could add the subreport and put it in to the
> list control without any error.
> Please try this workaround on your side and let me know the result. Thank
> you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
Thanks for the update.
The workaround need to be done on all the sub-reports.
After contact with the product team, this issue will be fixed in SQL 2005
SP2.
If you have any questions, or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
If you have any question or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Tuesday, March 27, 2012

Cannot drop the table transactional replication error 3724

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.

In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.

I presume that the sp_addarticle option @.pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)

Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).

/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @.distributor = N'MyInstance', @.password = N''
GO
exec sp_adddistributiondb @.database = N'distribution', @.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.data_file_size = 4, @.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data', @.log_file_size = 2, @.min_distretention = 0, @.max_distretention = 72, @.history_retention = 48, @.security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:\ThisFolder\MY2005DBWorking\repldata', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @.publisher = N'MyInstance', @.distribution_db = N'distribution', @.security_mode = 1, @.working_directory = N'C:\ThisFolder\MY2005DBWorking\repldata', @.trusted = N'false', @.thirdparty_flag = 0, @.publisher_type = N'MSSQLSERVER'
GO

use [DatabaseA]
exec sp_replicationdboption @.dbname = N'DatabaseA', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @.publication = N'PubAtoB', @.description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @.publication = N'PubAtoB', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1

use [DatabaseA]
exec sp_addarticle @.publication = N'PubAtoB', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseA]
exec sp_addsubscription @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseB', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubAtoB', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseB', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--

use [DatabaseB]
exec sp_replicationdboption @.dbname = N'DatabaseB', @.optname = N'publish', @.value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @.publication = N'PubBtoC', @.description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @.sync_method = N'concurrent', @.retention = 0, @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true', @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true', @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.allow_subscription_copy = N'false', @.add_to_active_directory = N'false', @.repl_freq = N'continuous', @.status = N'active', @.independent_agent = N'true', @.immediate_sync = N'true', @.allow_sync_tran = N'false', @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false', @.allow_dts = N'false', @.replicate_ddl = 1, @.allow_initialize_from_backup = N'false', @.enabled_for_p2p = N'false', @.enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @.publication = N'PubBtoC', @.frequency_type = 1, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1

use [DatabaseB]
exec sp_addarticle @.publication = N'PubBtoC', @.article = N'MyReplTable', @.source_owner = N'dbo', @.source_object = N'MyReplTable', @.type = N'logbased', @.description = null, @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509F, @.identityrangemanagementoption = N'manual', @.destination_table = N'MyReplTable', @.destination_owner = N'dbo', @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_dboMyReplTable', @.del_cmd = N'CALL sp_MSdel_dboMyReplTable', @.upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO

--BEGIN: Script to be run at Publisher 'MyInstance'--
use [DatabaseB]
exec sp_addsubscription @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.destination_db = N'DatabaseC', @.subscription_type = N'Push', @.sync_type = N'automatic', @.article = N'all', @.update_mode = N'read only', @.subscriber_type = 0
exec sp_addpushsubscription_agent @.publication = N'PubBtoC', @.subscriber = N'MyInstance', @.subscriber_db = N'DatabaseC', @.job_login = null, @.job_password = null, @.subscriber_security_mode = 1, @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20060314, @.active_end_date = 99991231, @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
GO
--END: Script to be run at Publisher 'MyInstance'--

May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

The reason I ask this is because transactional replication is not designed for a republishing scenario. It can be done, but it involves a lot of hacks and so forth. Merge replication is typically used, and designed, for republishing scenario.

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

The process thinking is that multiple Level B servers on other servers, spread the processing burden of moving the data from A to C. The C servers generally are physically clustered in the same general geographic area as the B server from which they source their data. Realistically, the A server might be feeding data to multiple B servers which in turn feed multiple C servers.

I hope that clarifies it for you. You can see that my little test scenario is much simplified from the physical reality. Our existing SQL 2000 replication makes use of both transactional and merge replication in this environment. I've only had light experience with transactional replication and not with either merge or republishing.

|||

Hi,

From your description, I assume you saw the error message from distribution agent, when replicating from B to C. Because you set @.pre_creation_cmd = N'drop', distribution agent needs to drop the table at C because it can replicate other transactions. But for some reason, this table is marked published.

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

For the moment, I'm back to working on my actual script (vs the sample one). I can tell you that I do not have DatabaseC as a publisher in my actual script. However, I will need to make it a publisher because there is one more replication layer that I did not mention. Practically every table will be transactionally replicated to another server (DatabaseD) to support another process.

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

|||I've just discovered that SQL 2000 replication was probably interfering with my SQL 2005 replication, so I removed SQL 2000 from my workstation (except for Virtual PC) and will try to work on this again tomorrow to see if anything has changed.

Cannot drop the table transactional replication error 3724

This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?

Command attempted:
drop Table "dbo"."MyReplTable"

(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)

Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)

Perhaps the sp_addpublication @.repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.

May I ask why you need a republisher scenario, and not just have C subscriber to A?|||

Maybe I should ask what your business needs are, and then we can discuss a solution.

|||

This is the nature of the system I've inherited. I'll represent the A,B,C level as servers because in reality, they often are a server with a single database.

|||

Hi,

So my question is: have you use DatabaseC as publisher? If it is the case, this may be the reason.

Peng

|||

Peng,

This is the way we are actually doing things in production now, in SQL 2000, without error.

Paul

Sunday, March 25, 2012

Cannot drop the table "xyz" as it is being used for replication

I have a replication setup running on 2 Win2K Server PCs with SQL
Server 2000.
Both the PCs have a common DB installed on them.
The setup takes care of starting one PC as primary and other as
backup.
It also starts the SQL replication (transactional), where both the PCs
are configured as publisher, distributor and subscriber.
Now the replication service creates a publication ONLY ON
PRIMARY and a subscription ONLY ON BACKUP. The replication starts.
When a switch-over of primary and backup is done, the existing
publication and subscription are deleted. A new publication is created
on new PRIMARY and a new subscription on the new BACKUP. Again,
replication works without a problem.
HOWEVER, sometimes, (I'm not able to reproduce & it is very
infrequent) on a switch-over I get thee error--"Cannot drop the table
"xyz" as it is being used for replication." The table is the one I
used for replication. Also, the primary keys exist on this table in
this case even though it's a part of subscription. The only solution
till now is to delete the entire replication on both machines and
re-create it.
I'm not using this DB anywhere else for any other purpose. Any
clues, why am I getting this problem occasionaly?
Thanks in advance.
Nelabh,
it's sometimes the case that some replication metadata is left hanging
around in system tables. sp_removedbreplication usually fixes these issues,
or the more granular sp_MSunmarkreplinfo may also be of interest.
HTH,
Paul Ibison

Cannot drop Temporary Table linked to Connection

Hi there!

I have a Java-Servlet that opens a connection to a SQL-Server 2000
With this connection I create a temporary local table.
I can call the temporary table within this connection as often as I want ... it's there as long as the connection is open. It's not dropped automatically as long as the connection is open. But when want to drop that table MANUALLY, I get a

[Microsoft][SQLServer JDBC Driver][SQLServer]Cannot drop the table '#tablename', because it does not exist in the system catalog.

I don't use any stored procedures that could drop the table before I do.
The database is accessed only by "normal" sql-statements through the JDBC-Driver. I can read the content of the table 10 times but if I try to drop it just in the next line, I get the above error message.

Now I know that the table will be dropped as soon as I close the connection, but anyway it's safer to do it manually ... so has somebody an idea?

Thanks

LookDo you need repeated access to the temp table or is it used just once ? Also, can you send the code you are using ? Is it straight sql or are you using a stored procedure (it appears that you are doing straight sql)?|||Found this in MSDN. Look for the article "Coding T-SQL like an Expert"

IF (object_id('tempdb..#mytemp') IS not Null)
Drop Table #mytemp

Although `tempdb..#authors_temp' is a truncation of the actual physical name of the object as held in tempdb..sysobjects, somehow OBJECT_ID() recognizes that you mean "your" temporary table called #mytemp.

Cannot Drop Table?

I have a table that is not functioning properly, so I have recreated it under a different name. Now I want to delete the problem table but it wont let me. This table has no dependencies, but I still can't drop it.

Here is the error message:

Lock request Time out period exceeded, error 1222

Any ideas on how to drop this table?

Did you check for locks with sp_who2 or Activity Monitor/Process Info (2005) Current Activity/Process Info (2000)? If there is a lock KILL the SPID(connection).

See:

http://msdn2.microsoft.com/en-gb/library/aa213032(SQL.80).aspx

|||

check whether this table is reference by any other table.... use sp_help/sp_fkeys etc....

Run DBCC CheckTable to check for any corruption.

Madhu

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.
What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com
|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.
|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:

> What does "cannot" mean? Do you get an error message? If so, what is it?
>
|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:

> CR wrote:
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:
> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:
> CR wrote:
>> I have a table with no dependencies. I cannot drop the table. I
>> tried from EM to delete it. I tried from Query Analyzer. I cannot
>> even delete an index that it has. It's not a big table and it only
>> has about 6,000 rows. Any ideas where to look? Thanks.
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>

Cannot Drop Table

I have a table with no dependencies. I cannot drop the table. I tried
from EM to delete it. I tried from Query Analyzer. I cannot even
delete an index that it has. It's not a big table and it only has about
6,000 rows. Any ideas where to look? Thanks.What does "cannot" mean? Do you get an error message? If so, what is it?
http://www.aspfaq.com/
(Reverse address to reply.)
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:#TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||CR wrote:
> I have a table with no dependencies. I cannot drop the table. I
> tried from EM to delete it. I tried from Query Analyzer. I cannot
> even delete an index that it has. It's not a big table and it only
> has about 6,000 rows. Any ideas where to look? Thanks.
More information please. Can you post the error you are seeing.
Also, grab the id for the table from sysobjects. Then, run sp_lock and
see if any other spid has a lock on the table.
David Gugick
Imceda Software
www.imceda.com|||Are you SURE that someone or something doesn't have a lock on it?
"CR" <chuck._rich7ardson@.sfcc.edu> wrote in message
news:%23TO86GevEHA.1260@.TK2MSFTNGP12.phx.gbl...
> I have a table with no dependencies. I cannot drop the table. I tried
> from EM to delete it. I tried from Query Analyzer. I cannot even
> delete an index that it has. It's not a big table and it only has about
> 6,000 rows. Any ideas where to look? Thanks.|||That's part of the problem -- there is no error message -- just hangs
and I have to kill application from task manager.
Aaron [SQL Server MVP] wrote:

> What does "cannot" mean? Do you get an error message? If so, what is it?
>|||It is looking like a lock now. Thanks to all. I just didn't think of that.
David Gugick wrote:

> CR wrote:
>
>
> More information please. Can you post the error you are seeing.
> Also, grab the id for the table from sysobjects. Then, run sp_lock and
> see if any other spid has a lock on the table.
>sql

Cannot Drop Index and PK Constraints

I am trying to create a new column on a table, but realized that there is already an PK index created. I am unable to drop the PK, or even delete the table. I also am unable to delete the index.Please be more specific - what error messages are you receiving ? Is it because of a system error or a constraint error (like you are deleting a primary key which is a foreign key in another table(s)) ?

Cannot divide

I'm having a real hard time with a division in my table. It is in a field in a group that I have no problem getting the correct result by these expressions:
=SUM(Fields!NrOfOptysTotal.Value + Fields!NrOfActivities.Value) or
=SUM(Fields!NrOfOptysTotal.Value - Fields!NrOfActivities.Value)
But if I do this:
=SUM(Fields!NrOfOptysTotal.Value / Fields!NrOfActivities.Value) or
=SUM(Fields!NrOfOptysTotal.Value * Fields!NrOfActivities.Value)
,I don't get the correct results, I only get Infinity or NaN, but I shouldn't. I have tried manipulating the expressions like
=SUM(Fields!NrOfOptysTotal.Value) / SUM(Fields!NrOfActivities.Value) and several other combinations, but it never works. I really don't understand the problem! Please help...Somehow I got it to work now with
=SUM(Fields!NrOfOptysTotal.Value) / SUM(Fields!NrOfActivities.Value)
I'm SURE I tried that before, I don't get it, but whatever...
"FredrikT" wrote:
> I'm having a real hard time with a division in my table. It is in a field in a group that I have no problem getting the correct result by these expressions:
> =SUM(Fields!NrOfOptysTotal.Value + Fields!NrOfActivities.Value) or
> =SUM(Fields!NrOfOptysTotal.Value - Fields!NrOfActivities.Value)
> But if I do this:
> =SUM(Fields!NrOfOptysTotal.Value / Fields!NrOfActivities.Value) or
> =SUM(Fields!NrOfOptysTotal.Value * Fields!NrOfActivities.Value)
> ,I don't get the correct results, I only get Infinity or NaN, but I shouldn't. I have tried manipulating the expressions like
> =SUM(Fields!NrOfOptysTotal.Value) / SUM(Fields!NrOfActivities.Value) and several other combinations, but it never works. I really don't understand the problem! Please help...
>

Thursday, March 22, 2012

cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
Bing
For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
WHERE clause (find the rows).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:

> For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/
|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:

> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>
|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:

> Check to ensure the user's login is correctly mapped to the desired database
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Online
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
>
|||bing skrev:

> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson
|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.

> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...[vbcol=seagreen]
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
|||"impslayer" wrote:

> bing skrev:
>
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bing

cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
BingFor some strange reason, you need SELECT as well. I guess it is because SQL
Server need to match the
WHERE clause (find the rows).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
I
> looked, he did not have DELETE on that table. Then I checked DELETE for h
is
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:

> For some strange reason, you need SELECT as well. I guess it is because SQ
L Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:

> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:

> Check to ensure the user's login is correctly mapped to the desired databa
se
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Onlin
e
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>
>|||bing skrev:

> Hello,
> One of our users said he was not able to delete records in a table. When
I
> looked, he did not have DELETE on that table. Then I checked DELETE for h
is
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.

> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...[vbcol=seagreen]
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
>|||"impslayer" wrote:

> bing skrev:
>
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bingsql

cannot delete with DELETE permission?

Hello,
One of our users said he was not able to delete records in a table. When I
looked, he did not have DELETE on that table. Then I checked DELETE for his
account. But he still cannot delete any records. I don't think there is
anything more I need to do to make the permission change take effect.
Correct me if I'm wrong. So what I'm missing here?
Thanks in advance,
BingFor some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
WHERE clause (find the rows).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Oh, sorry, I forgot to mention I also granted this user other permissions
INSERT, SELECT and UPDATE along with DELETE at the same time. He uses a
domain account accessing SQL 2000 from Access. Anything else could prevent
him from deleting records?
Thanks for the good clue you gave. I did not think of that.
Bing
"Tibor Karaszi" wrote:
> For some strange reason, you need SELECT as well. I guess it is because SQL Server need to match the
> WHERE clause (find the rows).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When I
> > looked, he did not have DELETE on that table. Then I checked DELETE for his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
>|||Does this table have a delete trigger that does insert/update on
another table (where the user does not have permissions)?
Use sp_helptrigger to find out
http://sqlservercode.blogspot.com/|||TableDeleteTrigger is 0. So I assume the table has no connection with other
tables, right?
Bing
"SQL" wrote:
> Does this table have a delete trigger that does insert/update on
> another table (where the user does not have permissions)?
> Use sp_helptrigger to find out
> http://sqlservercode.blogspot.com/
>|||Check to ensure the user's login is correctly mapped to the desired database
user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
mapping is incorrect following a database restore/attach. If that is you
problem, you can correct using sp_change_users_login. See the Books Online
for usage details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> Hello,
> One of our users said he was not able to delete records in a table. When
> I
> looked, he did not have DELETE on that table. Then I checked DELETE for
> his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing|||Thanks much for the information. sp_helplogins shows:
LoginName SID DefDBName DefLangName AUser ARemote
DEPT\jsmith <omitted> Staff us_english yes no
LoginName DBName UserName UserOrAlias
DEPT\jsmith Staff db_owner MemberOf
DEPT\jsmith Staff Staffadmins MemberOf
DEPT\jsmith Staff dept\jsmith User
(Note, sp_helplogins shows 'dept' in lower case in the above last line. Not
my typo.)
We did not do restore/attach on this database lately. From my
understanding, user level permissions should override group or role level
permissions, yes? Staffadmins is defined as a role in Roles of the Staff
database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
EXEC, DRI) were not checked for the role Staffadmins. Does this matter? I
think it should not because user DEPT\jsmith have been granted all the
permissions.
Biing
"Dan Guzman" wrote:
> Check to ensure the user's login is correctly mapped to the desired database
> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
> mapping is incorrect following a database restore/attach. If that is you
> problem, you can correct using sp_change_users_login. See the Books Online
> for usage details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When
> > I
> > looked, he did not have DELETE on that table. Then I checked DELETE for
> > his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
>
>|||bing skrev:
> Hello,
> One of our users said he was not able to delete records in a table. When I
> looked, he did not have DELETE on that table. Then I checked DELETE for his
> account. But he still cannot delete any records. I don't think there is
> anything more I need to do to make the permission change take effect.
> Correct me if I'm wrong. So what I'm missing here?
> Thanks in advance,
> Bing
Is there a DENY somewhere, perhaps? Possibly for a role this user is
member of.
/impslayer, aka Birger Johansson|||Permissions are cumulative and include those directly assigned plus role
membership. When conflicting permissions exist, DENY takes precedence over
GRANT.
> DEPT\jsmith Staff db_owner MemberOf
db_owner role is a very powerful role and is not normally used for normal
users because you can't limit database object permissions for db_owner role
members. Consequently, even if you denied permissions to this user, the
user should still have full object permissions.
You mentioned in your original message that the user could not DELETE from
the table. Are you certain this is permission related? What is the exact
error?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:4C564F8E-8F72-4DB4-9A54-06CA72CA3D28@.microsoft.com...
> Thanks much for the information. sp_helplogins shows:
> LoginName SID DefDBName DefLangName AUser ARemote
> DEPT\jsmith <omitted> Staff us_english yes
> no
> LoginName DBName UserName UserOrAlias
> DEPT\jsmith Staff db_owner MemberOf
> DEPT\jsmith Staff Staffadmins MemberOf
> DEPT\jsmith Staff dept\jsmith User
> (Note, sp_helplogins shows 'dept' in lower case in the above last line.
> Not
> my typo.)
> We did not do restore/attach on this database lately. From my
> understanding, user level permissions should override group or role level
> permissions, yes? Staffadmins is defined as a role in Roles of the Staff
> database. And all the permission boxes (SELECT, INSERT, UPDATE, DELETE,
> EXEC, DRI) were not checked for the role Staffadmins. Does this matter?
> I
> think it should not because user DEPT\jsmith have been granted all the
> permissions.
> Biing
> "Dan Guzman" wrote:
>> Check to ensure the user's login is correctly mapped to the desired
>> database
>> user (EXEC sp_helplogins 'SomeLogin'). It may be that the login/user
>> mapping is incorrect following a database restore/attach. If that is you
>> problem, you can correct using sp_change_users_login. See the Books
>> Online
>> for usage details.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "bing" <bing@.discussions.microsoft.com> wrote in message
>> news:86D16D62-EC76-4DA6-9B9C-6129B9E781C4@.microsoft.com...
>> > Hello,
>> >
>> > One of our users said he was not able to delete records in a table.
>> > When
>> > I
>> > looked, he did not have DELETE on that table. Then I checked DELETE
>> > for
>> > his
>> > account. But he still cannot delete any records. I don't think there
>> > is
>> > anything more I need to do to make the permission change take effect.
>> > Correct me if I'm wrong. So what I'm missing here?
>> >
>> > Thanks in advance,
>> >
>> > Bing
>>|||"impslayer" wrote:
> bing skrev:
> > Hello,
> >
> > One of our users said he was not able to delete records in a table. When I
> > looked, he did not have DELETE on that table. Then I checked DELETE for his
> > account. But he still cannot delete any records. I don't think there is
> > anything more I need to do to make the permission change take effect.
> > Correct me if I'm wrong. So what I'm missing here?
> >
> > Thanks in advance,
> >
> > Bing
> Is there a DENY somewhere, perhaps? Possibly for a role this user is
> member of.
> /impslayer, aka Birger Johansson
>
Thanks all who replied.
Yeah, as my sp_helplogins shows, this user is member of db_owner. He
actually is not a regular user. He is indeed the database owner. So I
really don't understand why he cannot delete. This user did not tell me the
exact error message he got if there was any. I need to check back with him
on that.
I don't see any explicit DENY related to this user. These are what've
checked:
1. Security->Login
Permit Database User
X Staff DEPT\jsmith
2. Staff -> Users
Database role membership->Permit in Database Role
public
db_owner
staffadmins
DEPT\jsmith permissions:
ME538 (SELECT, INSERT, UPDATE, DELETE)
3. Staff -> Roles
Staffadmins->Permissions:
ME538 is the only table that does not have SELECT, INSERT, UPDATE and
DELETE explicitly checked. And this is the table the user needs to delete
from but he cannot.
What am I missing?
Bing|||bing skrev:
> This user did not tell me the
> exact error message he got if there was any. I need to check back with him
> on that.
Ah, here's the problem, I guess :)
/impslayer

Cannot delete rows that contain same data

Using SQL Sever 2005 Management Studio I am trying to delete rows that are
duplicated in a table. There is no Primary Key. I get an error message to
the effect that the rows cannot be deleted because it would effect other
rows. If I attempt to change a field in the row I get the same error. In
this application there may be many duplicate rows that will need to deleted
at various times.
Thanks,
Bob HillerNo primary key is often an indication of a data model problem. Even if this
is simply a staging table used as part of an ELT process, you can add a
surrogate key to facilitate set-based processing and use GUI tools.
Do all columns of 'duplicate' rows contain the same values? In SQL 2005,
you can specify a TOP clause on a delete statement to delete only a
specified number of rows like the example below. Similarly, you can use SET
ROWCOUNT in earlier version but need to be careful to execute SET ROWCOUNT 0
afterward.
CREATE TABLE Table1 (Col1 int)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
SELECT * FROM Table1
DELETE TOP (2) FROM Table1 WHERE Col1 = 1
SELECT * FROM Table1
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uLNoND3SGHA.4752@.TK2MSFTNGP10.phx.gbl...
> Using SQL Sever 2005 Management Studio I am trying to delete rows that are
> duplicated in a table. There is no Primary Key. I get an error message to
> the effect that the rows cannot be deleted because it would effect other
> rows. If I attempt to change a field in the row I get the same error. In
> this application there may be many duplicate rows that will need to
> deleted at various times.
> Thanks,
> Bob Hiller
>|||Dan,
Yes, All of the columns contain the same values. Is there any way to delete
these rows by hitting the delete key or right click/delete?
Thanks,
Bob Hiller
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uHlyng3SGHA.5736@.TK2MSFTNGP10.phx.gbl...
> No primary key is often an indication of a data model problem. Even if
> this is simply a staging table used as part of an ELT process, you can add
> a surrogate key to facilitate set-based processing and use GUI tools.
> Do all columns of 'duplicate' rows contain the same values? In SQL 2005,
> you can specify a TOP clause on a delete statement to delete only a
> specified number of rows like the example below. Similarly, you can use
> SET ROWCOUNT in earlier version but need to be careful to execute SET
> ROWCOUNT 0 afterward.
> CREATE TABLE Table1 (Col1 int)
> INSERT INTO Table1 VALUES(1)
> INSERT INTO Table1 VALUES(1)
> INSERT INTO Table1 VALUES(1)
> SELECT * FROM Table1
> DELETE TOP (2) FROM Table1 WHERE Col1 = 1
> SELECT * FROM Table1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:uLNoND3SGHA.4752@.TK2MSFTNGP10.phx.gbl...
>|||AFAIK, you can't delete rows using the GUI when all columns have the same
value. This is because the tools generate a DELETE statement behind the
scenes and the row(s) you want to delete is ambiguous when all columns have
the same value. If you must use a GUI, you'll need to add a column to
uniquely identify a row:
ALTER TABLE Table1
ADD TempID int IDENTITY(1, 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Dan,
> Yes, All of the columns contain the same values. Is there any way to
> delete these rows by hitting the delete key or right click/delete?
> Thanks,
> Bob Hiller
>|||Bob,
As Dan has already said, you will probably struggle to delete these rows
using a GUI tool (e.g. Enterprise Manager) because there is no key defined
for the table. You should probably first add a key to the table, then do
your DELETE operations, and then, if you really don't want a key any more
you could remove the key definition (and in doing so, re-introduce the
design flaw).
There are probably ways around this, if you're prepared to write your
own SQL DELETEs rather than use a GUI to perform the delete ops. But that
doesn't change the fact that there is a design problem which needs to be
fixed. A correct RDBMS (database) design will always see a field (or set of
fields) defined as a unique key, even on temporary tables. Perhaps the only
exception would be staging tables used to clean data, but those will,
nevertheless, need a key defined at some point during the migration process.
HTH
Robert
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Dan,
> Yes, All of the columns contain the same values. Is there any way to
> delete these rows by hitting the delete key or right click/delete?
> Thanks,
> Bob Hiller
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uHlyng3SGHA.5736@.TK2MSFTNGP10.phx.gbl...
>|||The Table I am referring to is just being used for testing. I was using a
random number generator in my program that I developed to send the data to
the table. The random number generator at some point started duplicating
numbers. I am now going to implement the table in production mode and there
will of course be a key. It is very important because I am recording
automotive serial numbers which must be unique.
Thanks,
Bob Hiller
"Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message
news:%23wuSOn4SGHA.424@.TK2MSFTNGP12.phx.gbl...
> Bob,
> As Dan has already said, you will probably struggle to delete these
> rows using a GUI tool (e.g. Enterprise Manager) because there is no key
> defined for the table. You should probably first add a key to the table,
> then do your DELETE operations, and then, if you really don't want a key
> any more you could remove the key definition (and in doing so,
> re-introduce the design flaw).
> There are probably ways around this, if you're prepared to write your
> own SQL DELETEs rather than use a GUI to perform the delete ops. But that
> doesn't change the fact that there is a design problem which needs to be
> fixed. A correct RDBMS (database) design will always see a field (or set
> of fields) defined as a unique key, even on temporary tables. Perhaps the
> only exception would be staging tables used to clean data, but those will,
> nevertheless, need a key defined at some point during the migration
> process.
> HTH
> Robert
>
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:u$RtNV4SGHA.5156@.TK2MSFTNGP10.phx.gbl...
>|||Hey Bob,
Since this sounds like it's an issue of data cleansing for development
work, try something like the following:
SELECT DISTINCT *
INTO newTable
FROM oldTable
TRUNCATE TABLE oldTable
INSERT INTO oldTable
SELECT *
FROM newTable
DROP TABLE newTable
HTH,
Stu|||Bob,
Fair enough mate!
Just a small observation, for what it may be worth, (and yes, I'm being
somewhat pedantic here):
If you've got a scenario where you're first building a test system, and
then shifting to production (which is practically always the case with any
form of software development) then you might as well spend the extra time
developing your test DDL as fully as possible, because doing so will aid you
when it comes to evaluating the behaviour and performance of your client
software. The point here is, if a unqiue constraint had been enforced on
your test table, you're client program would never have been able to insert
those "duplicate" rows -- and so, you'd have been a step ahead...
Wishing you well with your project,
Regards,
Robert
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uWN7VS5SGHA.736@.TK2MSFTNGP12.phx.gbl...
> The Table I am referring to is just being used for testing. I was using a
> random number generator in my program that I developed to send the data to
> the table. The random number generator at some point started duplicating
> numbers. I am now going to implement the table in production mode and
> there will of course be a key. It is very important because I am recording
> automotive serial numbers which must be unique.
> Thanks,
> Bob Hiller
>
> "Robert Ellis" <robe_2k5@.n0sp8m.hotmail.co.uk> wrote in message
> news:%23wuSOn4SGHA.424@.TK2MSFTNGP12.phx.gbl...
>

Tuesday, March 20, 2012

cannot delete duplicate rows

Is there any query which will delete the dulpicate rows in all manner of a
table.
But one row of each, duplicate row should remain the table after deletion.
The table does not contain any primary key.
I donot want to use temporary table.
One query only no script or cursor.
Oracle uses rowid in this situation.
Do SQL Server have any trick to do it in one line?
Thanks PaulAnibran
Since you did not post DDL +samole data please look at this example removes
duplications
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>|||Dear Uri Dimant
Please do not insert an primary key. As my table do not have any primary key
nor I am authorised to do that.
Please keep one row of the data which was present as duplicate.
Please do it in one query
<urid@.iscar.co.il> wrote in message
news:uxlNcYA5DHA.2008@.TK2MSFTNGP10.phx.gbl...
> Anibran
> Since you did not post DDL +samole data please look at this example
removes
> duplications
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> > Is there any query which will delete the dulpicate rows in all manner of
a
> > table.
> > But one row of each, duplicate row should remain the table after
deletion.
> > The table does not contain any primary key.
> > I donot want to use temporary table.
> > One query only no script or cursor.
> > Oracle uses rowid in this situation.
> > Do SQL Server have any trick to do it in one line?
> >
> >
> > Thanks Paul
> >
> >
> >
>|||Anirban,
You simply can't do this as a single query. The only way to specify which
rows to delete is to identify those rows based on the values of their
columns. The column values of identical rows are identical, so no
expression can be true for one row and false for an identical row.
You can do this with a temporary table, and you can hide the use of a
temporary table by doing this with a trigger (which creates the temporary
inserted and deleted tables):
create table T (
CustomerID char(5)
)
insert into T
select CustomerID
from Northwind..Orders
go
create trigger T_del on T for delete as
if not exists (
select * from T
)
insert into T
select distinct * from deleted
go
delete from T
go
select * from T
order by CustomerID
go
drop table T
SK
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> Is there any query which will delete the dulpicate rows in all manner of a
> table.
> But one row of each, duplicate row should remain the table after deletion.
> The table does not contain any primary key.
> I donot want to use temporary table.
> One query only no script or cursor.
> Oracle uses rowid in this situation.
> Do SQL Server have any trick to do it in one line?
>
> Thanks Paul
>
>|||Thanks steve,
I was not sure some people keep telling me this is possible.
Are you sure this is not possible in sql 2000 also?
Thanks,
Paul
Steve Kass <skass@.drew.edu> wrote in message
news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...
> Anirban,
> You simply can't do this as a single query. The only way to specify which
> rows to delete is to identify those rows based on the values of their
> columns. The column values of identical rows are identical, so no
> expression can be true for one row and false for an identical row.
> You can do this with a temporary table, and you can hide the use of a
> temporary table by doing this with a trigger (which creates the temporary
> inserted and deleted tables):
> create table T (
> CustomerID char(5)
> )
> insert into T
> select CustomerID
> from Northwind..Orders
> go
> create trigger T_del on T for delete as
> if not exists (
> select * from T
> )
> insert into T
> select distinct * from deleted
> go
> delete from T
> go
> select * from T
> order by CustomerID
> go
> drop table T
> SK
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
> > Is there any query which will delete the dulpicate rows in all manner of
a
> > table.
> > But one row of each, duplicate row should remain the table after
deletion.
> > The table does not contain any primary key.
> > I donot want to use temporary table.
> > One query only no script or cursor.
> > Oracle uses rowid in this situation.
> > Do SQL Server have any trick to do it in one line?
> >
> >
> > Thanks Paul
> >
> >
> >
>|||I don't think it matters whether you use 7.0 or 2000. It might be
possible in other RDBMS products, and it would be possible in SQL Server
if some sort of unique ROWID value were available on every table. Then
you could do
delete from T
where exists (
select *
from T T2
where T.column1 = T2.column1
and T.column2 = T2.column2
and -- same for all columns
and T.ROWID > T2.ROWID
)
But there is no exposed ROWID in any version of SQL Server
SK
Anirban wrote:
>Thanks steve,
>I was not sure some people keep telling me this is possible.
>Are you sure this is not possible in sql 2000 also?
>Thanks,
>Paul
>Steve Kass <skass@.drew.edu> wrote in message
>news:OZUGSLe5DHA.2696@.TK2MSFTNGP09.phx.gbl...
>
>>Anirban,
>>You simply can't do this as a single query. The only way to specify which
>>rows to delete is to identify those rows based on the values of their
>>columns. The column values of identical rows are identical, so no
>>expression can be true for one row and false for an identical row.
>>You can do this with a temporary table, and you can hide the use of a
>>temporary table by doing this with a trigger (which creates the temporary
>>inserted and deleted tables):
>>create table T (
>> CustomerID char(5)
>>)
>>insert into T
>>select CustomerID
>>from Northwind..Orders
>>go
>>create trigger T_del on T for delete as
>>if not exists (
>> select * from T
>>)
>>insert into T
>>select distinct * from deleted
>>go
>>delete from T
>>go
>>select * from T
>>order by CustomerID
>>go
>>drop table T
>>SK
>>"Anirban" <tulu_paul@.hotmail.com> wrote in message
>>news:u6LVe0$4DHA.2736@.TK2MSFTNGP09.phx.gbl...
>>
>>Is there any query which will delete the dulpicate rows in all manner of
>>
>a
>
>>table.
>>But one row of each, duplicate row should remain the table after
>>
>deletion.
>
>>The table does not contain any primary key.
>>I donot want to use temporary table.
>>One query only no script or cursor.
>>Oracle uses rowid in this situation.
>>Do SQL Server have any trick to do it in one line?
>>
>>Thanks Paul
>>
>>
>>
>
>

Thursday, March 29, 2012

Cannot execute sub-report in list or table

Tuesday, March 27, 2012

Cannot drop the table transactional replication error 3724

Cannot drop the table transactional replication error 3724

Sunday, March 25, 2012

Cannot drop the table "xyz" as it is being used for replication

Cannot drop Temporary Table linked to Connection

Cannot Drop Table?

Cannot Drop Table

Cannot Drop Table

Cannot Drop Table

Cannot Drop Index and PK Constraints

Cannot divide

Thursday, March 22, 2012

cannot delete with DELETE permission?

cannot delete with DELETE permission?

cannot delete with DELETE permission?

Cannot delete rows that contain same data

Tuesday, March 20, 2012

cannot delete duplicate rows

cannot delete duplicate rows

Sql Cannot connect

Blog Archive

About Me