|
Hi all,
i have one table, it does n't have identity column,primary key,foriegn key,unique key and no constraints.
Now i want to delete duplicate records.
ex: table : sample (before deletion)
cno cname salry
10 xxx 1000
10 xxx 1000
10 xxx 1000
20 yyy 2000
20 yyy 2000
i want the result like this after deletion:
cno cname salary
10 xxx 1000
20 yyy 2000
Please help me.
|
|
|
|
|
Take a look at this:
http://www.databasejournal.com/features/mssql/article.php/1438651
Gautham
|
|
|
|
|
Hi gautham,
Thanx for your response.
|
|
|
|
|
No probs! You are welcome.
Gautham
|
|
|
|
|
|
Hi!
I wrote before but i want to refrase my quastion, i created a function that returns a CLOB object that contains a where clause.
I am doing a select command and in the where i put the function, i get an error, but if i print the content of the object(hard coded) to the where i get the answer, can you please tell me why?
Thanks 
|
|
|
|
|
i have a gridview and bind it to datasource. gridview has a bounded boolean column. i want if grid to be not editable expect those cells in the boolean column where value is false.
in other words:
all columns are readonly expect the boolean column not readonly.
if the value of the cell in boolean column
1- true: the cell is readonly
2- false: the cell is not readonly
i tried this code but i do not know why it does not work.
For Each x As DataGridViewRow In DataGridView1.Rows
If x.Cells("DeductionAdded").Value.ToString.ToLower = "true" Then
x.Cells("DeductionAdded").ReadOnly = True
End If
Next
|
|
|
|
|
Dear Team,
While Running the Sync PAckage through SQL 2005, i am getting this error message , please help if any one has faced this issue before :
- Copying to [DWH].[dbo].[DW_IP_ACTIVITY] (Error)
Messages
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid column value from bcp client for colid 19.".
(SQL Server Import and Export Wizard)
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - DW_IP_ACTIVITY" (73) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Jayaraman B
|
|
|
|
|
how we can change db from sql server 2000 to sqlserver 2005.
Jes M George.
|
|
|
|
|
1. take backup from SQL Server 2000
2. create new database in SQL Server 2005
3. restore the backup on to the new database.
OR
Detach the data base from SQL Server 2000 and Attach to SQL Server 2005 (this option i've not tried)
Regards
KP
|
|
|
|
|
U Can use DTS in SQL 2000.
Regards,
Arun Kumar.A
|
|
|
|
|
Hi all,
I want to get all the sundays between 2 dates in sql.
Please help..
Thanks and regards,
CST
|
|
|
|
|
//////////////////////
Table Name ='sysatt'
Column Name ='DtAtt'
/////////////////////
select DtAtt FROM sysatt
WHERE
DtAtt BETWEEN '2007-01-01' AND '2007-04-17'
AND DATENAME(Dw,DtAtt)='Sunday'
vivek
|
|
|
|
|
Thanks a lot for ur reply.
CST
|
|
|
|
|
while(date1<>date2)
begin
if(datepart(dw, date1)=sunday)
begin
@sundaycount = @sundaycount +1
end
DATEADD ( dd , 1, date1 )
end
print @sundaycount
Gautham
|
|
|
|
|
I'm going to say a bunch of stuff here that ASSUMES SQL Server uses the same date system that Microsoft Offices uses. If that's wrong, then what I say here won't apply.
Microsoft Office uses date serial number zero to (sort of) represent Saturday, Dec 30, 1899 (actually, their system is broken below serial number 61 because they assume the year 1900 was a leap year, which it was not -- but that doesn't affect this discussion). Thus, the date serial number modulo 7 gives you the day of the week, counting Sunday as day #1. Taking the interval between two dates, and dividing by 7 gets the number of weeks (and most weeks have one Sunday), then you need to correct for the beginning and end dates in case you get a fraction of a week, or the beginning or end dates are Sunday.
numSundays( date1, date2 )
{
dayOfWeek1 = MOD(date1,7)
dayOfWeek2 = MOD(date2,7)
numSundays = (date2-date1)/7
If(dayOfWeek2 < dayOfWeek1) numSundays++
ElseIf(dayOfWeek2=1 OR dayOfWeek1=1) numSundays++
}
This should give you the number of Sundays INCLUSIVE (includes both start and end dates). BUT, like I said earlier: won't work if SQL Server doesn't use the same dating system as MS Office.
Hope this helps.
|
|
|
|
|
Thanks a lot for ur reply.
CST
|
|
|
|
|
I re-read your post, and realized I hadn't really answered your question in my first response. You asked how to do it in SQL, and you didn't specify which flavor. I answered in pseudo code; not SQL. Here is another shot at it, this time putting the previous pseudo code into SQL.
I looked thru the (XOpen) SQL standard, and couldn't find the functions I was looking for, so had to settle for generic functions that you will have to substitute for if your system doesn't support them. In both the procedures below I've assumed:
1) the way to get an integer divide is with the '\' operator. This works in Access, but is not universal across DBMs.
2) the whole part of a date interval is actually the number of days between the two dates. The standard does NOT specify this.
3) the existence of a "WEEKDAY" function.
4) that date2 >= date1
5) that you specify a DATE-LITERAL by delimiting it with # signs.
Note that Jan 2, 2000 was a Sunday.
First, a procedure that uses ISO SQL standard CASE notation:
CREATE PROCEDURE numSundays(date1 DATE, date2 DATE) AS
SELECT CINT(date2 - date1) \ 7
+ CASE WEEKDAY(date2) < WEEKDAY(date1)
WHEN TRUE THEN 1 ELSE
CASE WEEKDAY(date2) = WEEKDAY(#JAN-02-2000#)
OR WEEKDAY(date1) = WEEKDAY(#JAN-02-2000#)
WHEN TRUE THEN 1 ELSE 0
END
END
;
And here is the same thing in MS Access notation.
CREATE PROCEDURE numSundays(date1 DATE, date2 DATE) AS
SELECT CINT(date2 - date1) \ 7
+ IIF( WEEKDAY(date2) < WEEKDAY(date1), 1,
IIF( WEEKDAY(date2) = WEEKDAY(#JAN-02-2000#)
OR WEEKDAY(date1) = WEEKDAY(#JAN-02-2000#), 1, 0 ))
;
While more complicated than a looping approach to counting the number of Sundays, they are much more efficient as they are effectively only a simple formula.
Hope this helps
-- modified at 16:35 Tuesday 17th April, 2007
|
|
|
|
|
Thanks a lot for ur reply.
found it quite useful.
CST
|
|
|
|
|
hi,
According to my knowledge , when a column is an IDENTITY it increments by 1 , now even if a row has a value of , lets say 5 , and i delete it , the next row will have a value that increments the previous value , even if it was deleted.
So i need to figure the Next number that will get into the IDENTITY column or the highest number(including deletions) of that IDENTITY column.
I'm working with C# 2.0 in .NET 2.0 using SQL Server 2005.
Thanks so much
Gideon
|
|
|
|
|
Use SCOPE_IDENTITY() like this:
-- begin sql --
DECLARE @Id INT
INSERT INTO myTable (val1, val2, val3)
SET @Id = SCOPE_IDENTITY()
-- end sql --
|
|
|
|
|
hi Mark,
Thanks so much for your reply but i'm afraid i have to be a little more annoying.
How do i use the syntax you've mentioned??. I did search msdn and google but came up with some confusing results. In my little DataLayer , i have 2 small functions that [1] Execute a query and returns a DataTable and [2] execute a non-query sql statement.
So far , i've only used non-query sql statements to do all my edit/delete operations. I dont know much about anything else like stored procedures or triggers
I'm guessing i'm supposed to use stored procedures? Is this [^]a good article?
Thanks so much
Gideon
|
|
|
|
|
guess a little more searching and reading is all it took!
I put it all in a stored proc and It works a treat now!
Thanks so much.
|
|
|
|
|
I have a customer that is a one off because they have Vista on their computer so SQL Server 2000 is not compatible. Does VB.NET use a specific provider for 2000 and 2005 or can I just set the database up the same and it will work?
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
CleaKO wrote: I have a customer that is a one off because they have Vista on their computer so SQL Server 2000 is not compatible. Does VB.NET use a specific provider for 2000 and 2005 or can I just set the database up the same and it will work?
It is the same provider (in System.Data.SqlClient) for both SQL Server 2000 and 2005
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
