Database Discussion Boards

kubben25-Jul-07 2:17

25-Jul-07 2:17

The query I gave you is to identify the duplicates. Once you have the duplicates there are several things you can do to delete them. If you already know how to identify the duplicates then you should be able to delete them. You can not use a distinct since that removes the duplicates. Do you have any id column or key column to uniquely identify each column?

Ben

Re: deleting duplicate rows

okkishore25-Jul-07 2:25

okkishore

25-Jul-07 2:25

any one i what that means the table that can have duplicate rows or particular rows.i already mension i can't give any constraints .

Re: deleting duplicate rows

kubben25-Jul-07 2:34

kubben

25-Jul-07 2:34

Ok well, if you don't have any unique key you should create one. Add a new column that is an identity. Then if you really don't which record you keep you can do a delete statement like this:

delete from yourTable yt
join
(Select max(id),col1, col2, col3, col4, col5 from yourTable
group by col1, col2, col3, col4, col5
having count(*) > 1) temp on yt.id = temp.id

Ben

How to insert large amount of data into column of a table

yuvachandra25-Jul-07 0:47

yuvachandra

25-Jul-07 0:47

Hi all,
I am using SQLServer2000,
I am trying to insert large amount of data into column of a table but i found only part of the data getting inserted into the table.
Then i have tried the following option to set the column size
sp_tableoption 'testmail', 'text in row', '7000'
After the above line get executed its better but still i am lagging behind to store total text .please help me some one

Thanks in advance
Yuva

Re: How to insert large amount of data into column of a table

originSH25-Jul-07 0:54

originSH

25-Jul-07 0:54

the most data you can store in a row is 8000 bytes (so thats 8000 ascii charecters or 4000 unicode). If you need to store more than that you need to chop up the text and store it in multiple rows.

Re: How to insert large amount of data into column of a table

chandragupta.k25-Jul-07 1:39

chandragupta.k

25-Jul-07 1:39

hi originSH ,
Thanks for your reply but my requiremet is store all the date ito one column i cannot chop up teh thing because i am getting that text dynamically..The text in the form of html

Thanks in Advance
Chandra Gupta

Re: How to insert large amount of data into column of a table

Pete O'Hanlon25-Jul-07 1:07

Pete O'Hanlon

25-Jul-07 1:07

Change the datatype to text (or ntext for Unicode). Text stores 2^31 characters, and NText stores 2^30 characters.

Deja View - the feeling that you've seen this post before.

Re: How to insert large amount of data into column of a table

chandragupta.k25-Jul-07 1:37

chandragupta.k

25-Jul-07 1:37

hi Pete O'Hanlon,
I have used text data type only but it is not allowing me store whole text(Actually the text in the form of HTml) into my Database

SQL Server DB Problem

bassrider24-Jul-07 23:08

bassrider

24-Jul-07 23:08

Hi There,
I need some assistance with a database problem.

I am using Microsoft SQL Server 2005, one of the tables primary key has been deleted. How can I check when the changed was made and by which user as there are multiple users with administrative rights?

All of your help is greatly appreciated. D'Oh! | :doh:

Regards,
BassRider

Re: SQL Server DB Problem

blakey40425-Jul-07 2:25

blakey404

25-Jul-07 2:25

check your transaction log - if someone is messing around with this kind of stuff get triggers or alerts set up on your database when they are modifed, that way you can find out imediately and record user, date, time to get a better iddea of what's going on!

Performance problem with large Image

Tiger45624-Jul-07 22:59

Tiger456

24-Jul-07 22:59

Hi,

In SQl seerver 2005 we have a table with a coloum as Image. data type In the Stored procedure we are using Insert statement to add the image stream to coloum. All our image files are nearly 400 KB of size. The insert statement is doing its job.Our problem its talke nearly 18 seconds to do the insert. Is there any way we can tune up performance or any alternates for it.

Regard

Re: Performance problem with large Image

blakey40425-Jul-07 2:26

blakey404

25-Jul-07 2:26

does this have to be stored in sql server? it is not a recommended practice, a better solution would be to store them in a location and hold a simple location within your database.

Re: Performance problem with large Image

Tiger45625-Jul-07 18:16

Tiger456

25-Jul-07 18:16

Its a customer requirement.

Is there an way to solve it.

problem with saving imagepath

imranafsari24-Jul-07 19:44

imranafsari

24-Jul-07 19:44

Dear All,

I am using asp.net2.0, C#, sql2005 using Visual studio 2005

I am storing imagepath in field of table whose datatype is nvarchar(250).

My problem is when I pass as argument of \\\\servername\\directory\\imagename it stores as \\servername\directory\imagename I would like to it as argument passed so that I can just fetch the url and display image

Please guide me
Thanks

imaran khan

Re: problem with saving imagepath

Mike Dimmick25-Jul-07 0:19

Mike Dimmick

25-Jul-07 0:19

Backslash doubling is an issue for literal strings in C-derived languages because the backslash character is used as an escape, to allow non-printable characters to be specified. Therefore to get an actual backslash in the output, you must double it. This is the same as using "" in a VB literal string to indicate a single " character (otherwise a " ends the string). If you output the final string to the console, you will see that any doubled backslashes in the literal have become single backslashes in the actual string.

C# also supports @-literals, where you place an @ before the first " character. In this type of string literal, the backslash character does not operate as an escape, it represents an actual backslash. The only escape sequence is "" to represent a single " character.

Therefore the following literals are equivalent:

string regular = "\\\\servername\\directory\\imagename";
string atLiteral = @"\\servername\directory\imagename";
System.Diagnostics.Debug.Assert( regular == atLiteral );

You do not need to pass - indeed, it is strictly an error to pass - doubled backslashes to the filesystem. It treats multiple backslashes as indicating no change of directory. You can make a canonical path, that is with any multiple backslashes removed and any directory change (. or ..) parts processed, by calling Path.GetFullPath. A double backslash is only significant at the beginning of a path, where it indicates that the first part of the path is a machine name for a UNC path.

Stability. What an interesting concept. -- Chris Maunder

Query Question [modified]

Sean G. Murphy24-Jul-07 11:47

Sean G. Murphy

24-Jul-07 11:47

I have three tables
Item(Item_ID, Item_Name)
1 shirt
2 hat
Color(Color_ID, Color_Name)
1 blue
2 red
3 green
Item_Color(Item_Color_ID, Item_ID, Color_ID)
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3

What is the query that would give me the result:
Item_ID Item_Name Color_Name1 Color_Name2 Color_Name3
1 shirt blue red null
2 hat blue red green

I seem to continue to come up short.
I tried:
Select Item_Name, GROUP_CONCAT(DISTINCT Item_Color) FROM Item Left Join Item_Color On Item_Color.Item_Id=Item.Item_ID Left Join Color ON Item_Color.Color_ID=Color.Color_ID Group By Item_Name

but this returns the colors in a blue,red,green format, not in seperate columns.

-- modified at 20:01 Tuesday 24th July, 2007

Thanks!
Sean Murphy
"All things great and small start at the same point, the first step."

Re: Query Question

andyharman24-Jul-07 22:34

andyharman

24-Jul-07 22:34

Hi Sean

The following would give you the results that you asked-for:

select I.Item_ID, I.ItemName,
  case when Blue.Item_ID is not null then 'Blue' else null end,
  case when Red.Item_ID is not null then 'Red' else null end,
  case when Green.Item_ID is not null then 'Green' else null end
from Item I
left outer join Item_Color Blue
  on Blue.Item_Id = I.Item_Id
  and Blue.Color_Id = 1
left outer join Item_Color Red
  on Red.Item_Id = I.Item_Id
  and Red.Color_Id = 2
left outer join Item_Color Green
  on Green.Item_Id = I.Item_Id
  and Green.Color_Id = 3
order by I.Item_ID

However, it would need to be amended to handle additional colors. If you're using SQL-Server 2005 then you could use the new Pivot[^] query operator.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

verry urgent

rajesh rajbhar24-Jul-07 4:31

rajesh rajbhar

24-Jul-07 4:31

Hi,all
i m using sqlserver 2005 and asp.net 2.0
i m using ODBC provider for database access

connection string is

<add key="odbConn" value="Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Mydatabase;user id=sa;password=sa"/>

but when i m debuging the program it's show error

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

What is the problem Please help me its urgent

i have installed sqlserver2005 in server mixedmode authentication

rajesh

Re: verry urgent

leckey24-Jul-07 4:50

leckey

24-Jul-07 4:50

Please rename your message title to something with meaning. Please read the forum guidelines. Messages with 'urgent' or 'please help' are considered rude and may not get you any help.

_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!

Re: verry urgent

Blue_Boy24-Jul-07 5:05

Blue_Boy

24-Jul-07 5:05

did you try to make a GOOGLE[^]search?

I Love SQL

Re: verry urgent

Colin Angus Mackay24-Jul-07 5:06

Colin Angus Mackay

24-Jul-07 5:06

rajesh rajbhar wrote:
SQL Server does not exist or access denied

It means that it either cannot access SQL Server via a TCP connection (you gave it a network address in the connection string) or you have not supplied the correct credentials to gain access.

Also, why are you using an ODBC connection to conned to a SQL Server database? Why aren't you using the SQL Provider built in to .NET?

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Re: verry urgent

Mike Dimmick24-Jul-07 9:33

Mike Dimmick

24-Jul-07 9:33

I don't think he knows what he's using, because that's an OLE DB connection string (Provider= is a dead giveaway, ODBC uses Driver).

Stability. What an interesting concept. -- Chris Maunder

Re: verry urgent

originSH24-Jul-07 5:14

originSH

24-Jul-07 5:14

For a guess your using Express? It helps if you give the exact details of what your using :P

http://www.datamasker.com/SSE2005_NetworkCfg.htm[^]

Re: verry urgent

Paul Conrad24-Jul-07 5:55

Paul Conrad

24-Jul-07 5:55

First of all, don't put urgent in your thread subject line. It just pisses people off.

rajesh rajbhar wrote:
SQL Server does not exist or access denied

Check that the service is running and you don't have a firewall blocking it or something silly like that.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

Re: verry urgent

Pete O'Hanlon24-Jul-07 10:38

Pete O'Hanlon

24-Jul-07 10:38

Please tell me that you aren't connecting with the sa account. This is extremely bad practice. sa has access to pretty much everything in SQL Server and you are basically opening up your dataaccess code to the point where your attack surface is too large. Use a limited access account only.

And, as others have stated, don't use urgent in your title. Just because you think it's urgent it doesn't mean that anybody else will care.

Deja View - the feeling that you've seen this post before.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.