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
Showing posts with label row. Show all posts
Showing posts with label row. Show all posts
Thursday, March 29, 2012
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 Paulhttp://support.microsoft.com/default.aspx?scid=kb;en-us;70956
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:OikSLw$4DHA.504@.TK2MSFTNGP11.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
>|||Sorry Sir this is not the answer I am expecting. Please follow my query.
Do it in one query.
No temp table please.
You can not have a Primary key on the table.
You need to keep one row of data which was duplicate earlier.
i.e. donot delete all the duplicate recordset.
Paul
Carlos Eduardo Rojas <carloser@.mindspring.com> wrote in message
news:uShY#UB5DHA.1632@.TK2MSFTNGP12.phx.gbl...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;70956
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:OikSLw$4DHA.504@.TK2MSFTNGP11.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
> >
> >
>
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 Paulhttp://support.microsoft.com/default.aspx?scid=kb;en-us;70956
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:OikSLw$4DHA.504@.TK2MSFTNGP11.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
>|||Sorry Sir this is not the answer I am expecting. Please follow my query.
Do it in one query.
No temp table please.
You can not have a Primary key on the table.
You need to keep one row of data which was duplicate earlier.
i.e. donot delete all the duplicate recordset.
Paul
Carlos Eduardo Rojas <carloser@.mindspring.com> wrote in message
news:uShY#UB5DHA.1632@.TK2MSFTNGP12.phx.gbl...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;70956
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:OikSLw$4DHA.504@.TK2MSFTNGP11.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
> >
> >
>
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
>>
>>
>>
>
>
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
>>
>>
>>
>
>
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...
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...
removes
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...
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...
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:
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...
quote:|||Dear Uri Dimant
> 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
>
>
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...
quote:
> Anibran
> Since you did not post DDL +samole data please look at this example
removes
quote:|||Anirban,
> 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...
a[QUOTE]
deletion.[QUOTE]
>
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...
quote:|||Thanks steve,
> 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 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...
quote:|||I don't think it matters whether you use 7.0 or 2000. It might be
> 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...
a[QUOTE]
deletion.[QUOTE]
>
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:
quote:sql
>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...
>
>a
>
>deletion.
>
>
>
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 Paulhttp://support.microsoft.com/defaul...=kb;en-us;70956
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:OikSLw$4DHA.504@.TK2MSFTNGP11.phx.gbl...
Do it in one query.
No temp table please.
You can not have a Primary key on the table.
You need to keep one row of data which was duplicate earlier.
i.e. donot delete all the duplicate recordset.
Paul
Carlos Eduardo Rojas <carloser@.mindspring.com> wrote in message
news:uShY#UB5DHA.1632@.TK2MSFTNGP12.phx.gbl...
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 Paulhttp://support.microsoft.com/defaul...=kb;en-us;70956
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Anirban" <tulu_paul@.hotmail.com> wrote in message
news:OikSLw$4DHA.504@.TK2MSFTNGP11.phx.gbl...
quote:|||Sorry Sir this is not the answer I am expecting. Please follow my query.
> 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
>
Do it in one query.
No temp table please.
You can not have a Primary key on the table.
You need to keep one row of data which was duplicate earlier.
i.e. donot delete all the duplicate recordset.
Paul
Carlos Eduardo Rojas <carloser@.mindspring.com> wrote in message
news:uShY#UB5DHA.1632@.TK2MSFTNGP12.phx.gbl...
quote:
> http://support.microsoft.com/defaul...=kb;en-us;70956
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Anirban" <tulu_paul@.hotmail.com> wrote in message
> news:OikSLw$4DHA.504@.TK2MSFTNGP11.phx.gbl...
a[QUOTE]
deletion.[QUOTE]
>
Thursday, March 8, 2012
Cannot create a row of size 8075 which is greater than... (-214721
Hello all,
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/d...serr_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCulloughHi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/d...serr_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/d...serr_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCulloughHi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/d...serr_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough
Cannot create a row of size 8075 which is greater than... (-214721
Hello all,
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/de...err_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCullough
Hi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/de...err_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/de...err_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCullough
Hi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/de...err_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough
Cannot create a row of size %d which is greater than the allowable maximum of %d
I'm trying to execute an insert with a PreparedStatement object and I am getting the following Error message:
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8320 which is greater than the allowable maximum of 8060.
Does anyone have a solution for this?You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).|||Originally posted by Mulligan
You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).
Thanks for the link. I had already taken a look there before finding this forum but I couldn't understand why my data was longer than the page size for SQL because I was only inserting a small amount of data.
I found that my problem due to MS SQL creating a temporary table based on my Insert Statement using the table definition for the size of the columns. In this case, I had a nvarchar(4000) column. This then generated a temp table with a column size equivalent to 8363 bytes but the page size is 8060. This happens even though the data I was inserting was much smaller than the max length.
To resolve the problem I had to reduce to the column size to a maximum of 3868. This resolved the problem.|||Nice bit of detective work. And thanks for posting back with the solution - it makes a refreshing change from all the "How can I fix problem X?" "Like this." "Bye" postings :)
Mull.
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8320 which is greater than the allowable maximum of 8060.
Does anyone have a solution for this?You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).|||Originally posted by Mulligan
You're inserting data that is larger than the maximum page size for SQL Server. See this page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_20hd.asp).
Thanks for the link. I had already taken a look there before finding this forum but I couldn't understand why my data was longer than the page size for SQL because I was only inserting a small amount of data.
I found that my problem due to MS SQL creating a temporary table based on my Insert Statement using the table definition for the size of the columns. In this case, I had a nvarchar(4000) column. This then generated a temp table with a column size equivalent to 8363 bytes but the page size is 8060. This happens even though the data I was inserting was much smaller than the max length.
To resolve the problem I had to reduce to the column size to a maximum of 3868. This resolved the problem.|||Nice bit of detective work. And thanks for posting back with the solution - it makes a refreshing change from all the "How can I fix problem X?" "Like this." "Bye" postings :)
Mull.
Subscribe to:
Posts (Atom)