How to remove Duplicated data in SQL Table?

Hi

I want to know, how to remove duplicated data in Sql Table using a SQL query? Im using SQL Management Studio Express Edition to connect to database.

Your help will be highly appreciated.

See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1425992&SiteID=1

Or these articles:

Duplicates –Delete
http://www.sql-server-performance.com/rd_delete_duplicates.asp

Duplicates –Find
http://www.aspfaq.com/2431

Duplicates –How do I remove duplicates from a table?
http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html

Duplicates –How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444

How to remove a database without deleting the mdf file ?

I have both sql 2005 express and developer edition installed.
If I have attached a mdf file to my local sql server default instance, how can I remove that database from this instance without deleting the underlying mdf file ? I have attached an mdf file under sql 2005 (dev edition) default instance, but now I want to detach it and reattach it under my sql 2005 express named instance. Why is there not a "detach" menu option ?

help ?for some strange reason, a "detach" menu option is there when I select the database node in the left panel of mgt studio, then right-click the db and choose tasks > detach.

Oh wait, its there under the right-click menu in the left pane as well. disregard everything i just said

how to remote connection to an SQL server on the same network?

hi
i added 2 databases to my SQL server on a machine next door using Microsoft SQL server management studio express... I made sure that the server allows remote connection

then from this computer i pass a connection string to my SQLCLient in VB2005 that looks a bit like this

Code: ( text )

1. ConnString = "Server=USER-PTDH8Z5ESH\SQLEXPRESS;Database=FSS.MDB;Trusted_Con nection=True;User Instance=True"

for which it gives me this error when i open the connection
SQLConnecton.Open()

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.Att emptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.Log inNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.Ope nLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Flying_School_Scheduler.DBEngine.StartConnection() in

whats the big idea?
i'm in a bit of a tight schedule here... can anyone help me here? ?

First, let me express my aplogies for not getting to your question sooner. It would be not too trite to say that since most of us that take our time to respond to questions such as yours, are volunteers, and we are often also on a tight schedule. Simple, straightforward requests for assistance are looked at and perhaps even responded to far sooner than demanding, whining ones...

As to the 'big deal' with your connection string: There are a couple of possibilites:

Often using 'prohibited' characters in a server name requires extra handling, such as enclosing the name in square brackets. You might try:
Server=[USER-PTDH8Z5ESH\SQLEXPRESS]; Is the database itself named FSS.MDB, or could that, by mistake, be the database file name?|||

Normally, error 26 is caused by a few common problems that are easily fixed. Please take a look at this posting as it should probably fix your problem: http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

Thanks,

Il-Sung.

how to reduce DOS attacks

Hello to everyone

I am running MS SQL 2005 Express I get per day 2-4 hackers attacks trying to login from “sa”

Some 37 calls times per second one of attack was continuing 4 days

Is there some setting into MS SQL 2005 to reduce that?

Can you recommend me good firewall for DDOS attacks?

Is it there some legal action that I can take to this people I have their IPs most are from US and Canada?

Thank you in advance

val

Hi Val,

If you are seeing a lot of attacks, the best thing to do would be to move your db server to a different machine behind your firewall entirely. You can restrict external access to your database by using another web or application server to handle any requests.

You can also consider disabling your sa account just to be safe.

I'm not sure what sort of legal actions you can take, perhaps consult with a lawyer? You may have some options available.

=== Edited by Sung MSFT @. 16 Aug 2006 3:01 AM UTC===
Also, you can have your firewall block all the IP addresses that you have identified as hostile. This should hopefully cut down on the number of attacks as well.

Sung|||

To make the connection attempts fail even earlier you could also rename the sa account.

Thanks
Laurentiu

|||

Hi

when I setup first time the server I disable the "sa" account , and I am behind Windows firewall

I am looking something like if there is too many call just server to drop this IP

I have 8-10 MB log file just from fail to access to MS SQL server

I hope there will be way to be droped before that

10x

val

|||

There is no such feature currently in SQL Server, but we have received this suggestion before: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124700. You can add your comments to this feedback report or just vote for its importance.

Thanks
Laurentiu

|||I normally recommend that you use a named instance instead of a default instance. Also, do a port redirection from your firewall. Do not use port 1433 or 1434 on your SQL Server. Let the firewall resolve it for the clients.

How to read from Sql express

Hi,

i have a program written in VB6 and writes without any problem in sql

express 2005 but when i try to Select information from SQL

to VB6 i always have a error " Object Required"

The link is ODBC Source with SQL NATIVE CLIENT

and the code is something like this

dbname = txtDb

Set Db = Ws.OpenDatabase(txtDb, False, False, "ODBC")

txtDb = Db.Name

logMessage = "Ligar a " & txtDb

UpdateLog (logMessage)

lblDbStatus = "Ligar"

picDbStatus.Visible = True

lblFileStatus = "Parado"

picFileStatusOff.Visible = True

tmr.Enabled = True

SQLStateQuery = "SELECT STATE FROM DYELOTS where dyelot = '" & Header(0) & "'"

Set rsState = Db.OpenRecordset(SQLStateQuery)

any one have a idea where is the problem

ThanksOk - ignore the text boxes and all that other stuff: can you just issue a SQL statement using your VB6 code without getting an error?

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/

how to query db on dedicated server from my lan ?

what is the best way to send data to and query SQL 2005 server express
edition db hosted on a dedicated server with ISP ?
i need to query it directly from an application on my LAN (asp site on same
machine as sql db also needs to query db)
i can obviously use port 1433 but what other option do i have ? i.e ssl
port, xml web service ?
Any information is helpful
ScottScott,
For security reasons, I would change the port from 1433 and use encryption.
If the server is dedicated to your use configure have multiple NICs in the
server (about least three). Alow one for the ISP to use, allow one or two
for your site to use but restrict the incoming allowed IP range to your
systems, another two if you have external customers that will be accessing
the Web application.
"scott" <aintnospamhere@.yahoo.com> wrote in message
news:Oqsm3aNeGHA.1204@.TK2MSFTNGP02.phx.gbl...
> what is the best way to send data to and query SQL 2005 server express
> edition db hosted on a dedicated server with ISP ?
> i need to query it directly from an application on my LAN (asp site on
> same machine as sql db also needs to query db)
> i can obviously use port 1433 but what other option do i have ? i.e ssl
> port, xml web service ?
> Any information is helpful
> Scott
>
>
>|||perfect thanks, i understand all of that apart from how to use encryption on
the port ?
is it just a case of using the encryption key in the connection string from
our app i.e
"data source=SERVER;initial catalog=sms" (with encryption)"
if so how do you tell SQL server to only accept encyted data / queries on
the port we decided to use ?
thanks for your help Danny
Scott.
(ps what are PIPES for then ?)|||For SQL 2005 it's recommended to use an SSL certificate, however you can get
some benefit by enabling Force encryption in the SQL Server Configuration
Manager.
"scott" <aintnospamhere@.yahoo.com> wrote in message
news:OTWBbCOeGHA.2188@.TK2MSFTNGP05.phx.gbl...
> perfect thanks, i understand all of that apart from how to use encryption
> on the port ?
> is it just a case of using the encryption key in the connection string
> from our app i.e
> "data source=SERVER;initial catalog=sms" (with encryption)"
> if so how do you tell SQL server to only accept encyted data / queries on
> the port we decided to use ?
> thanks for your help Danny
> Scott.
> (ps what are PIPES for then ?)
>