Finding Duplicate Records

CREATE TABLE KeyLetter(
KeyNum int,
Letter char(1))
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(111111, 'b')
INSERT INTO KeyLetter VALUES(111111, 'c')
INSERT INTO KeyLetter VALUES(222222, 'a')
INSERT INTO KeyLetter VALUES(222222, 'b')
INSERT INTO KeyLetter VALUES(222222, 'c')
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(222222, 'a')
Given the above records, I have written the following to identify duplicate
records, but in my real life situation, I have a table with far more columns
to evaluate for duplication, and I was wondering if there is a better way to
identify duplicates such as this, without performing concatenation?
SELECT Cast(KeyNum as varchar(6)) + Letter),
COUNT(Cast(KeyNum as varchar(6)) + Letter))
FROM KeyLetter
GROUP BY Cast(KeyNum as varchar(6)) + Letter)
HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
--
Message posted via http://www.sqlmonster.comUmm, anything wrong with this:
select KeyNum, Letter, count(*)
from KeyLetter
group by KeyNum, Letter
having count(*)>1
MC
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7989a4847c279@.uwe...
> CREATE TABLE KeyLetter(
> KeyNum int,
> Letter char(1))
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(111111, 'b')
> INSERT INTO KeyLetter VALUES(111111, 'c')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'b')
> INSERT INTO KeyLetter VALUES(222222, 'c')
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> Given the above records, I have written the following to identify
> duplicate
> records, but in my real life situation, I have a table with far more
> columns
> to evaluate for duplication, and I was wondering if there is a better way
> to
> identify duplicates such as this, without performing concatenation?
> SELECT Cast(KeyNum as varchar(6)) + Letter),
> COUNT(Cast(KeyNum as varchar(6)) + Letter))
> FROM KeyLetter
> GROUP BY Cast(KeyNum as varchar(6)) + Letter)
> HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
> --
> Message posted via http://www.sqlmonster.com
>

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
Thanks
It was UDL, I remember now.
"me" wrote:

> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:

> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

Finding Changed Records

I need to create a table that would be the result set of a comparison
between table a and table b? Table a and b first 2 fields will always be
the same (CustomerName and CustomerNumber). But if the Address1 field
changes in table a, I would like to throw that whole row into my
comparison table. Almost like a Select Into with a sub query that would
include a WHERE TableA.field <> TableB.field. I would need to do this
comparison for about 8 fields. Help appreciated for my syntax is pretty
bad. Thanks.

Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

Assuming that the first two fields are a primary key then you need to join
on the primary keys and difference the other columns:

e.g

INSERT INTO DIFFERENCES ( Col1, Col2 , Col3, Col4, Col5, COl6, Col7, Col8 )
SELECT A.Col1, A.Col2, A.Col3, A.Col4
FROM TableA A JOIN TableB B JOIN A.Col1 = B.Col1AND A.Col2 = B.Col2
WHERE A.COl3 <> B.Col3
OR A.Col4 <> B.Col4
OR A.Col5 <> B.Col5
OR A.Col6 <> B.Col6
OR A.Col7 <> B.Col7
OR A.Col8 <> B.Col8

John

"Steve Bishop" <steveb@.viper.com> wrote in message
news:4008ae65$0$70301$75868355@.news.frii.net...
> I need to create a table that would be the result set of a comparison
> between table a and table b? Table a and b first 2 fields will always be
> the same (CustomerName and CustomerNumber). But if the Address1 field
> changes in table a, I would like to throw that whole row into my
> comparison table. Almost like a Select Into with a sub query that would
> include a WHERE TableA.field <> TableB.field. I would need to do this
> comparison for about 8 fields. Help appreciated for my syntax is pretty
> bad. Thanks.
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Finding and Killing Process's with Transact

Is it possible to create some Transact SQL which will find all the process's
locking a database and then kill those process's (I want to do this as a
precursor to running a database restore, my problem is that sometimes
someone has the database locked running a query and the restore fails as a
result).before restore:
alter database <db> set SINGLE_USER with ROLLBACK AFTER <seconds>
(to view all connected users on db use
select * from sysprocesses where dbid=db_id('db')
kill users with kill <spid> )
"quiglepops" wrote:

> Is it possible to create some Transact SQL which will find all the process
's
> locking a database and then kill those process's (I want to do this as a
> precursor to running a database restore, my problem is that sometimes
> someone has the database locked running a query and the restore fails as a
> result).
>
>|||Here's a script I use... replace <your db> <password> and <server> with your
info.
Let me know if it's useful. Note that it shells out using xp_cmdshell so
you'll need
symin rights.
use master
go
declare @.dbname sysname, @.spid int, @.sqlstring varchar(8000)
set @.dbname = <your db>
Create Table #spid
(
SPID int,
Status varchar(130),
Login varchar(50),
HostName varchar(50),
BlkBy char(5),
DBName varchar(30),
Command varchar(16),
CPUTime int,
DiskIO bigint,
LastBatch varchar(20),
ProgramName varchar(128),
SPID2 int
)
Insert Into #spid
Exec master..sp_who2
Delete From #spid
Where dbname <> @.dbname
Or dbname is null
/************************/
/* Kill all connections */
/************************/
While Exists (Select * From #spid)
Begin
Select @.spid = SPID
From #spid
If @.spid is null
break
-- Clean up the table so it's not processed again
Delete From #spid
Where spid = @.spid
Set @.sqlstring = 'isql /Usa /n /dmaster /Q"kill ' + cast(@.spid as varchar) +
'" /h-1 /P<password> /S<server>'
Exec master..xp_cmdshell @.sqlstring
End
"quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:uTsbBmuWFHA.1508@.tk2msftngp13.phx.gbl...
> Is it possible to create some Transact SQL which will find all the
process's
> locking a database and then kill those process's (I want to do this as a
> precursor to running a database restore, my problem is that sometimes
> someone has the database locked running a query and the restore fails as a
> result).
>|||In SQL Server 2000, you do not have to enumerate and kill all connections
individually. Instead, you can issue the following which will set the
database to single user mode. The option "with rollback immediate" should
automatically terminate all connections and rollback active transactions.
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
"quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:uTsbBmuWFHA.1508@.tk2msftngp13.phx.gbl...
> Is it possible to create some Transact SQL which will find all the
process's
> locking a database and then kill those process's (I want to do this as a
> precursor to running a database restore, my problem is that sometimes
> someone has the database locked running a query and the restore fails as a
> result).
>|||Thanks for that.
Setting it to Single User mode sounds good, but will it do that when there
are users already in locking the database. Also, will that command on its
own do the job, without me having to find and kill the SPIDS ?
What do I then have to do after the restore to make it available to everyone
again ?
"Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
message news:72376937-9C88-4A0D-972C-558C687FC21F@.microsoft.com...
> before restore:
> alter database <db> set SINGLE_USER with ROLLBACK AFTER <seconds>
> (to view all connected users on db use
> select * from sysprocesses where dbid=db_id('db')
> kill users with kill <spid> )
>
> "quiglepops" wrote:
>
process's
a|||Yes, the IMMEDIATE option should automatically kill all processes (locked or
not). Now that I think about it, the option RESTRICTED_USER may be more
appropriate than SINGLE_USER. This will allow only users with DBO or SA
rights to subsequently connect.
Look up the ALTER DATABASE command in SQL Server Books Online for more
details. If you want, perform a test of this process on another
non-production database like Pubs or Northwind.
After the restore is complete, perform the same command, but with the option
MULTI_USER to allow multiple connections.
"quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:uLQmG0uWFHA.4036@.TK2MSFTNGP10.phx.gbl...
> Thanks for that.
> Setting it to Single User mode sounds good, but will it do that when there
> are users already in locking the database. Also, will that command on its
> own do the job, without me having to find and kill the SPIDS ?
> What do I then have to do after the restore to make it available to
everyone
> again ?
>
> "Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
> message news:72376937-9C88-4A0D-972C-558C687FC21F@.microsoft.com...
> process's
a
as
> a
>|||Thanks for your help guys. The ALTER DATABASE command is perfect. The
scripts are also useful for some of my SQL 7 DB's, so thanks for your help
everyone.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:uxfiDyuWFHA.4036@.TK2MSFTNGP10.phx.gbl...
> Here's a script I use... replace <your db> <password> and <server> with
your
> info.
> Let me know if it's useful. Note that it shells out using xp_cmdshell so
> you'll need
> symin rights.
> use master
> go
> declare @.dbname sysname, @.spid int, @.sqlstring varchar(8000)
> set @.dbname = <your db>
> Create Table #spid
> (
> SPID int,
> Status varchar(130),
> Login varchar(50),
> HostName varchar(50),
> BlkBy char(5),
> DBName varchar(30),
> Command varchar(16),
> CPUTime int,
> DiskIO bigint,
> LastBatch varchar(20),
> ProgramName varchar(128),
> SPID2 int
> )
> Insert Into #spid
> Exec master..sp_who2
> Delete From #spid
> Where dbname <> @.dbname
> Or dbname is null
>
> /************************/
> /* Kill all connections */
> /************************/
> While Exists (Select * From #spid)
> Begin
> Select @.spid = SPID
> From #spid
> If @.spid is null
> break
> -- Clean up the table so it's not processed again
> Delete From #spid
> Where spid = @.spid
> Set @.sqlstring = 'isql /Usa /n /dmaster /Q"kill ' + cast(@.spid as varchar)
+
> '" /h-1 /P<password> /S<server>'
> Exec master..xp_cmdshell @.sqlstring
> End
>
>
> "quiglepops" <dave_quigley@.hotmail.com> wrote in message
> news:uTsbBmuWFHA.1508@.tk2msftngp13.phx.gbl...
> process's
a
>|||Actually another question. The ALTER DATABASE SET SINGLE_USER WITH ROLLBACK
IMMEDIATE
kills all connections and rolls back straight away.
I want to run this command in a job, then on success in the next step of the
job run the database restore command.
Would it be true to say that some particularly long transactions that would
take a long time to roll back would still be running when the restore
attempts to run,
thus causing a failure, or would it be the case that the job would wait for
the first step to complete (i.e. wait until all transactions, including long
running ones were complete, before proceeding to step 2 and restoring the DB
?
"quiglepops" <dave_quigley@.hotmail.com> wrote in message
news:ekVB5PvWFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Thanks for your help guys. The ALTER DATABASE command is perfect. The
> scripts are also useful for some of my SQL 7 DB's, so thanks for your help
> everyone.
>
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:uxfiDyuWFHA.4036@.TK2MSFTNGP10.phx.gbl...
> your
so
varchar)
> +
a
as
> a
>sql

Finding a creator of object

Hi all,

whenever dbo is prifixed with the create script the owner becomes dbo.
If the below script
run by the sam the owner becomes dbo

create proc dbo.test
as
print 'hello'

Is there any place where SQL server keeps the record of creator?shiju (shiju.samuel@.gmail.com) writes:

Quote:

Originally Posted by

whenever dbo is prifixed with the create script the owner becomes dbo.
If the below script
run by the sam the owner becomes dbo
>
create proc dbo.test
as
print 'hello'
>
Is there any place where SQL server keeps the record of creator?

No. You would have to have trace running that captutes the Object:Created
event. In SQL 2005 you could also set up a DLL trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Find out users in a particular Windows group ?

We have our admins create groups containing Windows users ... Is there a
way to check through SQL which members belong to that group ? Using SQL 2000Hi Hassan,
SQL Server doesn't store which Windows users are in a Windows group. So the
only way to find out is to use the command shell with something like (I
don't know the exact syntax of the NET command):
EXEC xp_cmdshell 'NET GROUP <group name> /DOMAIN'
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e5a3$$rxDHA.1996@.TK2MSFTNGP12.phx.gbl...
> We have our admins create groups containing Windows users ... Is there a
> way to check through SQL which members belong to that group ? Using SQL
2000
>

Find gaps in sequential numbering

I would like to find the missing numbers in a sequential increase in a
column
So for instance
Create table #test
(numcol int)
insert #test values(1)
insert #test values(2)
insert #test values(3)
insert #test values(4)
insert #test values(7)
insert #test values(8)
insert #test values(9)
insert #test values(11)
insert #test values(100)
Would be nice to get the range of missing values such as
Minvalue Maxvalue
5 6
10 10
12 99
ThanksHere is one solution:
SELECT minval + 1 AS MinValue,
maxval - 1 AS MaxValue
FROM
(SELECT numcol,
(SELECT MIN(numcol)
FROM #test AS T2
WHERE T2.numcol > T1.numcol)
FROM #test AS T1) AS T3(minval, maxval)
WHERE maxval - minval > 1
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hassan
select
min(i) as low,
max(i) as high
from (
select
N1.num,
count(N2.num) - N1.num
from Numbers as N1, Numbers as N2
where N2.num <= N1.num
group by N1.num
) as N(i,gp)
group by gp
"Hassan" <hassan@.hotmail.com> wrote in message
news:u5PlkdgXIHA.6140@.TK2MSFTNGP02.phx.gbl...
>I would like to find the missing numbers in a sequential increase in a
>column
> So for instance
> Create table #test
> (numcol int)
> insert #test values(1)
> insert #test values(2)
> insert #test values(3)
> insert #test values(4)
> insert #test values(7)
> insert #test values(8)
> insert #test values(9)
> insert #test values(11)
> insert #test values(100)
>
> Would be nice to get the range of missing values such as
> Minvalue Maxvalue
> 5 6
> 10 10
> 12 99
> Thanks
>

Filters for dataset from Parameter - How to ignore if the parameter is NULL ?

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.

I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :

Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.

Thanks

One way would be to use this in your stored procedure or query, with coalesce...

eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]

That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.

|||

No I don't want to do that,

The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.

The result is that the DB will not get hammered everytime a report is run but only once at night.

So the coalesce will not work.

|||

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

|||

Andrew - T4G wrote:

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

Thanks, I'll try something like that.

FilterRows

Hi, I posted this a while back but had no response. I wonder if anyone can
help.
I am using transactional replication.
CREATE TABLE T1 (LogID INT, Quantity INT)
Table T1 is an article to be published. I only want to publish rows that
have a quantity > 5. The initial snapshot filters replicates all rows that
meet the criteria.
However, when a quantity changes to above 5, ie becomes eligible to be
replicated, this row is not replicated at the subscriber.
Publisher
Table T1 all rows with Quantities 0 - 10
Subscriber
Table T1 only rows with Quantities 5 - 10
I run the following update at the publisher
UPDATE T1
SET Quantity = 6
WHERE Quantity = 4
These rows do not get replicated.
Am i right in thinking this is by design and that it takes the exact DML
statement from the publisher and runs it on the subsciber?
Does anyone have a workaround for this or do i have to replicate the entire
table?
Thanks again
Rich
I just set this up and rows the were < than 5 and changed to be greater than
5 were replicated...
"Rich" wrote:

> Hi, I posted this a while back but had no response. I wonder if anyone can
> help.
> I am using transactional replication.
> CREATE TABLE T1 (LogID INT, Quantity INT)
> Table T1 is an article to be published. I only want to publish rows that
> have a quantity > 5. The initial snapshot filters replicates all rows that
> meet the criteria.
> However, when a quantity changes to above 5, ie becomes eligible to be
> replicated, this row is not replicated at the subscriber.
> Publisher
> Table T1 all rows with Quantities 0 - 10
> Subscriber
> Table T1 only rows with Quantities 5 - 10
> I run the following update at the publisher
> UPDATE T1
> SET Quantity = 6
> WHERE Quantity = 4
> These rows do not get replicated.
> Am i right in thinking this is by design and that it takes the exact DML
> statement from the publisher and runs it on the subsciber?
> Does anyone have a workaround for this or do i have to replicate the entire
> table?
> Thanks again
> Rich
>
>
|||I've tried again and this time its worked. Thanks!
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:DB145F6B-5C28-48F1-B714-2EED6C02F8E7@.microsoft.com...[vbcol=seagreen]
>I just set this up and rows the were < than 5 and changed to be greater
>than
> 5 were replicated...
> "Rich" wrote: