How to Determine the unique IDs of duplicated records

> This is a common problem with some solution

/************************************************** *********************************
*
* Problem:
* Determine the Duplicated Records in a table using single SELECT.
*
* We shall be using Northwind database, add some duplicate records.
*
* Here we want to know if 2 columns (CompanyName,
* PHone) are duplicated in a table.
*
*
* ShipperID CompanyName Phone
* ---- -------- ------
* 1 Speedy Express (503) 555-9831
* 2 United Package (503) 555-3199
* 3 Federal Shipping (503) 555-9931
* 4 Federal Shipping (503) 555-9931
* 5 Speedy Express (503) 555-9831
* 6 Federal Shipping (503) 555-9931
*
*
*
************************************************** **/

==================================================

SOLUTION 1: Gives me the IDs that are duplicated.

==================================================

SELECT
ShipperID, CompanyName, Phone
FROM
SHIPPERS
WHERE
EXISTS (
SELECT
NULL
FROM
SHIPPERS b
WHERE
b.CompanyName = SHIPPERS.CompanyName
AND b.Phone = SHIPPERS.Phone
GROUP BY
b.CompanyName, b.Phone
HAVING
SHIPPERS.ShipperID < MAX( b.ShipperID )
)

/* ********************
* Output results
********************/

ShipperID CompanyName Phone

---- ------------
--------
1 Speedy Express (503) 555-9831
3 Federal Shipping (503) 555-9931
4 Federal Shipping (503) 555-9931

(3 row(s) affected)

================================================== ===========

SOLUTION 2: Gives me the data which are duplicate but
not the IDs

================================================== ===========

SELECT
CompanyName, Phone
FROM
SHIPPERS
GROUP BY
CompanyName, Phone
HAVING
COUNT(*) > 1

/* ********************
* Output results
********************/

CompanyName Phone
------------ --------
Speedy Express (503) 555-9831
Federal Shipping (503) 555-9931

(2 row(s) affected)anonieko@.hotmail.com wrote:
> > This is a common problem with some solution
> /************************************************** *********************************
> *
> * Problem:
> * Determine the Duplicated Records in a table using single SELECT.
> *
> * We shall be using Northwind database, add some duplicate records.
> *
> * Here we want to know if 2 columns (CompanyName,
> * PHone) are duplicated in a table.
> *
> *
> * ShipperID CompanyName Phone
> * ---- -------- ------
> * 1 Speedy Express (503) 555-9831
> * 2 United Package (503) 555-3199
> * 3 Federal Shipping (503) 555-9931
> * 4 Federal Shipping (503) 555-9931
> * 5 Speedy Express (503) 555-9831
> * 6 Federal Shipping (503) 555-9931
> *
> *
> *
> ************************************************** **/
> ==================================================
> SOLUTION 1: Gives me the IDs that are duplicated.
> ==================================================
> SELECT
> ShipperID, CompanyName, Phone
> FROM
> SHIPPERS
> WHERE
> EXISTS (
> SELECT
> NULL
> FROM
> SHIPPERS b
> WHERE
> b.CompanyName = SHIPPERS.CompanyName
> AND b.Phone = SHIPPERS.Phone
> GROUP BY
> b.CompanyName, b.Phone
> HAVING
> SHIPPERS.ShipperID < MAX( b.ShipperID )
> )
> /* ********************
> * Output results
> ********************/
> ShipperID CompanyName Phone
> ---- ------------
> --------
> 1 Speedy Express (503) 555-9831
> 3 Federal Shipping (503) 555-9931
> 4 Federal Shipping (503) 555-9931
> (3 row(s) affected)
>
> ================================================== ===========
> SOLUTION 2: Gives me the data which are duplicate but
> not the IDs
> ================================================== ===========
>
> SELECT
> CompanyName, Phone
> FROM
> SHIPPERS
> GROUP BY
> CompanyName, Phone
> HAVING
> COUNT(*) > 1
>
> /* ********************
> * Output results
> ********************/
>
> CompanyName Phone
> ------------ --------
> Speedy Express (503) 555-9831
> Federal Shipping (503) 555-9931
> (2 row(s) affected)

Those aren't solutions, they are diagnostics. The solution is to fix
the stupid design of the Shippers table by adding a proper key.

:-)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sql

How to determinate a SqlConnection is open or not

I use a common connection cnn;
If the conn is opened, if I open it again, it will through a exception
How can I determinate a SqlConnection is open or not before I open it?
conn.State
Highly .State and click F1 in Visual Studio.NET
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23vHfoN8gFHA.576@.TK2MSFTNGP15.phx.gbl...
>I use a common connection cnn;
> If the conn is opened, if I open it again, it will through a exception
> How can I determinate a SqlConnection is open or not before I open it?
>
>

How to determinate a SqlConnection is open or not

I use a common connection cnn;
If the conn is opened, if I open it again, it will through a exception
How can I determinate a SqlConnection is open or not before I open it?conn.State
Highly .State and click F1 in Visual Studio.NET
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23vHfoN8gFHA.576@.TK2MSFTNGP15.phx.gbl...
>I use a common connection cnn;
> If the conn is opened, if I open it again, it will through a exception
> How can I determinate a SqlConnection is open or not before I open it?
>
>

How to determinate a SqlConnection is open or not

I use a common connection cnn;
If the conn is opened, if I open it again, it will through a exception
How can I determinate a SqlConnection is open or not before I open it?conn.State
Highly .State and click F1 in Visual Studio.NET
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23vHfoN8gFHA.576@.TK2MSFTNGP15.phx.gbl...
>I use a common connection cnn;
> If the conn is opened, if I open it again, it will through a exception
> How can I determinate a SqlConnection is open or not before I open it?
>
>

How to delete a group of stored procedures

For reasons I won't go into I have a need to delete over a 100 stored procedures from a database. The stored procedures have a common prefix such as dbo.aspnet_createUser.

I'm trying to come up with something that will allow me to delete all procedures that contain aspnet in their name.

The only thing close I've found is CONTAINS but I get an error I can use it against sysobjects because it isn't indexed.

Any ideas of how I can go about doing this other than deleting each one by hand?

ThanksExecute this in query analyzer or management studio, copy the output, paste

to the top pane, and run again:

SELECT 'DROP PROCEDURE dbo.'+ROUTINE_NAME+';'

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE='PROCEDURE'

AND ROUTINE_SCHEMA='dbo'

AND ROUTINE_NAME LIKE 'aspnet[_]%'

wrote in message

news:d05d74cc-5383-4e2c-93ab-3182561f2726@.discussions.microsoft.com...

> For reasons I won't go into I have a need to delete over a 100 stored

> procedures from a database. The stored procedures have a common prefix

> such as dbo.aspnet_createUser.

>

> I'm trying to come up with something that will allow me to delete all

> procedures that contain aspnet in their name.

>

> The only thing close I've found is CONTAINS but I get an error I can use

> it against sysobjects because it isn't indexed.

>

> Any ideas of how I can go about doing this other than deleting each one

> by hand?

>

> Thanks

>|||

Thank you! Worked like a champ.

Could you by chance recommend a good book with examples for somebody interested in learning Transact-SQL to come up with statements such as you provided?

Thanks again!

How to define an object in common place instead of every SP in SQL Server

hi,

I want to define a linked server object in one common place instead of every SP. (Because the UAT DB or SIT DB maybe the linked server with different name)

For Oracle we can define one common object in Package like following:

CREATE OR REPLACE PACKAGE FMS_PKG IS

TYPE T_CURSOR IS REF CURSOR;

END FMS_PKG;

Then we can use it in SP like FMS_PKG.T_CURSOR. So I wonder whether SQL Server 2005 has corresponding place can do this.

Thanks

Bily Jiang

Assuming that your Linked Server is another instance of SQL Server then you could create a Linked Server, named, for example, 'MyLinkedServer', and reference this in your stored procs / Views etc...

You can then use the CliConfg.exe application to create an Alias on the server hosting your primary SQL Server instance. Name the Alias 'MyLinkedServer' and specify the Server Alias (the instance of SQL Server to which your Linked Server should point) as appropriate to your environment.

This way you do not have to make changes to the code when you progress through each of the environments - all you have to do is update the Server Alias on the server and any Linked Server security settings on the primary SQL Server instance.

Chris

|||

Yes, I have found this tool.

Could you tell me how to use this tool to create one Linked Server Alias?

After I open it, I click Alias tab --> click Add --> should choose which option?

Thanks

Bily Jiang

|||

It depends on which network protocol you are using.

We use TCP/IP, so I would select 'TCP/IP', enter the Linked Server name in the Server Alias text box (e.g. MyLinkedServer), then enter the name of the server (plus instance name if necessary) that you wish to connect to in the 'Server Name' text box.

Chris

|||If you are using SQL Server 2005 you can create Synonyms for each table, view, procedure or function you are accessing via the linked server. Then you will just use the Synonym name to reference the object on the remote server, not the linked server syntax.|||

Thank you so much.

I feel synonym is the simplest resolution especially when we just access several remoting tables.

But I think package is the advantage of Oracle.

Regards

Ren Jie