Database

Re: Migrating from SQL Server 2000 to 2005

14-Dec-05 21:18

Check this link

http://www.databasejournal.com/features/mssql/article.php/3564291

PrashantJ15-Dec-05 6:31

PrashantJ

15-Dec-05 6:31

Thanks.

Delete duplicate records

munklefish14-Dec-05 6:34

14-Dec-05 6:34

Hi,

Im using classic asp with an MS SQL server.

I have a table hosting a number of contact for a number of different users.

How do i go about deleting duplicate records in the table for a given user?
Obviously i need to keep 1 copy of every contact, and simply remove any additional records that might exist for this contact.

Any help would be greatly appreciated.

Thanks!!!!!!!!!!!!!

enjoycrack14-Dec-05 7:13

enjoycrack

14-Dec-05 7:13

Hi there,

Not sure exactly it's true or not. Should do a backup one first Poke tongue | ;-P

...
- select distinct rows from table1 and copy into table1
- delete table1
- insert back rows from table2 to table1

<< >>

munklefish14-Dec-05 11:46

14-Dec-05 11:46

enjoycrack wrote:
Not sure exactly it's true or not. Should do a backup one first ...
- select distinct rows from table1 and copy into table1
- delete table1
- insert back rows from table2 to table1

Hi

not sure that would work. As each contact has unique ID yet several users might have a contact that is on someone elses contact list also.

Needs a more robust solution.

Thanks anyway.

enjoycrack14-Dec-05 11:49

enjoycrack

14-Dec-05 11:49

Hi there,

So basing on what to consider that 2 records are duplicated?

<< >>

munklefish14-Dec-05 11:54

14-Dec-05 11:54

If one user has two contacts records the same eg:

where the AccountHolderID, FirstName, and Email are the same for two or more records.

enjoycrack14-Dec-05 14:53

enjoycrack

14-Dec-05 14:53

just do what I suggest... what things do you worry about???
IMO, this can be done with sql script described above....
And remember to do back up first. Poke tongue | ;-P

Gud luke
<< >>

albCode15-Dec-05 6:00

15-Dec-05 6:00

here is ur solution

http://support.microsoft.com/default.aspx?scid=kb;en-us;139444

munklefish15-Dec-05 6:15

Help please ... i don't know what is wrong!!

15-Dec-05 6:15

Thanks!

Didnt think it would be so complicated!!!

UltraMAX2K514-Dec-05 5:05

UltraMAX2K5

14-Dec-05 5:05

Hello, i'm novice with Visual C# and SQL Server, so can someone help me please ... i want search for a record in the database, and if it is found then i move the CurrencyManager position to its position but i got this error:

ConstraintException was unhandled:
Column 'cd_id' is constrained to be unique.  Value '9463216602' is already present.

in my database this field [cd_id] in both tables aren't the primary key, are just simple fields ... what's i'm doing wrong, can someone help me please, i'm using this code:

the general variables:

<br />
SqlConnection myConnection;<br />
SqlDataAdapter daCD, daTemas;<br />
DataSet myDataSet = new DataSet("MusicaTeca");<br />
DataViewManager myDVM;<br />
CurrencyManager cm;<br />

in the form_load:

<br />
private void frmProduto_Load(object sender, EventArgs e)<br />
{<br />
   myConnection = new SqlConnection(<br />
      "Server=(local)\\SQLEXPRESS;DataBase=musicateca;" +<br />
      "integrated Security=SSPI");<br />
<br />
   daCD = new SqlDataAdapter("SELECT * FROM tbl_cds", myConnection);<br />
   daCD.TableMappings.Add("Table", "tbl_scds");<br />
   daCD.Fill(myDataSet, "tbl_cds");<br />
<br />
   daTemas = new SqlDataAdapter("SELECT * FROM tbl_temas", myConnection);<br />
   daTemas.TableMappings.Add("Table", "tbl_temas");<br />
   daTemas.Fill(myDataSet, "tbl_temas");<br />
<br />
   myDVM = myDataSet.DefaultViewManager;<br />
<br />
   DataRelation relCD_Tema;<br />
   DataColumn parentCol, childCol;<br />
   parentCol = myDataSet.Tables["tbl_cds"].Columns["cd_id"];<br />
   childCol = myDataSet.Tables["tbl_temas"].Columns["cd_id"];<br />
   relCD_Tema = new DataRelation("RelCD_Temas", parentCol, childCol);<br />
   myDataSet.Relations.Add(relCD_Tema);<br />
<br />
   txtAlbum.DataBindings.Add("Text", myDVM, "tbl_cds.album");<br />
   txtAno.DataBindings.Add("Text", myDVM, "tbl_cds.ano");<br />
   txtArtista.DataBindings.Add("Text", myDVM, "tbl_cds.artista");<br />
   txtEditora.DataBindings.Add("Text", myDVM, "tbl_cds.editora");<br />
   txtGenero.DataBindings.Add("Text", myDVM, "tbl_cds.genero");<br />
   txtID.DataBindings.Add("Text", myDVM, "tbl_cds.cd_id");<br />
<br />
   dbGrid.DataSource = myDVM;<br />
   dbGrid.DataMember = "tbl_cds.RelCD_Temas";<br />
   dbGrid.Columns["reg_id"].Visible = false;<br />
   dbGrid.Columns["cd_id"].Visible = false;<br />
<br />
   cm = (CurrencyManager)this.BindingContext[myDVM, "tbl_cds"];<br />
}<br />

and the search button_click:

<br />
private void btnLocalizar_Click(object sender, EventArgs e)<br />
{<br />
    int intRow;<br />
<br />
    myDVM.DataViewSettings["tbl_cds"].Sort = "cd_id";<br />
    intRow = myDataSet.Tables["tbl_cds"].DefaultView.Find(txtID.Text);<br />
<br />
    if (intRow > 0)<br />
    {<br />
        cm.Position = intRow;   // The Error is in this line.<br />
        cm.Refresh();<br />
    }<br />
    else<br />
    {<br />
         MessageBox.Show("Registo não localizado!");<br />
    }<br />
}<br />

can someone help me please ... and forgive me my bad english.

Thanks
UltraMAX

Re: Help please ... i don't know what is wrong!!

Rob Graham14-Dec-05 5:22

Rob Graham

14-Dec-05 5:22

A field need not be a primary key to have a unique constraint. Sounds like cd_id has a unique constraint or index defined for it. Check the db definitions.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

Re: Help please ... i don't know what is wrong!!

agauga14-Dec-05 5:24

agauga

14-Dec-05 5:24

i think you have set up the relation between the two tables in the dataset memory, hence your parent table which contains is taken as the primary key.
Please check out this. Smile | :)

Re: Help please ... i don't know what is wrong!!

UltraMAX2K514-Dec-05 8:09

UltraMAX2K5

14-Dec-05 8:09

Thank you guys for helping me ... jesus ... like i said i'm novice in .NET, i've used VB6 before, but it's completly different, and i've used with Ms ACCESS Blush | :O

... please, can you guys give me just a little example using the code that i'm using or just the code that is missing to resolve that, please!!!

SQL-SERVER 2000 storing documents

agauga14-Dec-05 5:02

agauga

14-Dec-05 5:02

I am using sql-server 2000 database. i am trying out document managment . can anybody tell me How do i store an MS-OFFICE document into a database table. i have created a table with the the column name as "document" datatype as varbinary.
Any help would be welcome Confused | :confused:

anil g

-- modified at 11:41 Wednesday 14th December, 2005

Re: SQL-SERVER 2000 storing documents

Colin Angus Mackay14-Dec-05 6:43

Colin Angus Mackay

14-Dec-05 6:43

varbinary is unlikely to be large enough (unless you are using SQL Server 2005) as its maximum size is just under 8K. You need to create the column as an image (the 2Gb limit should be sufficient for office files.)

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: SQL-SERVER 2000 storing documents

agauga14-Dec-05 19:08

agauga

14-Dec-05 19:08

thanks for the tip. Also can you guide me with the sql statements for storing
it as an image ?

Simple SQL delete statement

munklefish14-Dec-05 2:30

Re: Simple SQL delete statement

14-Dec-05 2:30

Ok im sure this is simple but im new to SQL and dont want to mess things up so your assistance please.

Im using a MS SQL server, which holds client contact details. However in the email field some contacts do not contain properly formatted email adresses.

eg www.somewhere.com rather than someone@somwehere.com

So what sql statement would i run to delete these fields?

Is it as simple as:

Delete * From table WHERE email NOT LIKE %@%
(im looking for records without a '@' symbol)

Thanks!

albCode14-Dec-05 4:19

Re: Simple SQL delete statement

14-Dec-05 4:19

try this

delete from table where email not like %@%

--this query deletes all rows in which doesnt exists character @

munklefish14-Dec-05 4:20

Re: Simple SQL delete statement

14-Dec-05 4:20

Yeah,

Thought so!

Thanks for confirming.

albCode14-Dec-05 4:25