Duplicate Text data

Anyone have a method for identifiying dupes in a text field?
coromokes wrote:
> Anyone have a method for identifiying dupes in a text field?
By dupes, you mean the existence of the same text value in more than one
row? If so, you can group on the text column and use a having clause to
test for dupes.
You'll need to convert to a varchar or nvarchar data type first, which
means you'll only get access to teh first 4,000 or 8,0000 characters for
the test. But maybe that's good enough.
Select CAST(TextDataCol as VARCHAR(8000))
From TableName
Group By CAST(TextDataCol as VARCHAR(8000))
Having COUNT(*) > 1
David Gugick
Imceda Software
www.imceda.com
sql

Duplicate records - difference method?

Hi,
My scenario is that i have 2 system with name and adress (100.000 names),
that have to be merged into 1 system without any duplicates.
The problem is that the spelling is not 100% between the system.
One way to find duplicate is to group name,adress and count > 1.
My dream is to use the sound index "Difference" so can i get around the
spelling problem.
DIFFERENCE
Returns the difference between the SOUNDEX values of two character
expressions as an integer.
Syntax
DIFFERENCE ( character_expression , character_expression )
Is that possible to use DIFFERENCE to find duplicates?
And how should the t-sql look like?
Example
name adress city
charles way1 state1
charle waj1 stat1
charlez vay1 stat1
I want to find this example, that this 3 is duplicates.
Should i use ordinary way with group and count >1, this would not be
duplicates.
Help
Thanx
TwSo you want these to be considered duplicates:
FirstName LastName
Jon Smyth
John Smith
Jonathan Smythe
John Smyth
'|||Hi,
Yes, i want these to be considered duplicates.
But i also want to validate adress and city to see if these is duplicates.
FirstName LastName Adress City
Jon Smyth Street 1 Palace1
John Smith Stret1 Palac1
Jonathan Smythe Stret 1 Palac 1
John Smyth Street1 Palace 1
Who can i do that in a t-sql?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i
meddelandet news:uey1ZpUkFHA.2852@.TK2MSFTNGP15.phx.gbl...
> So you want these to be considered duplicates:
> FirstName LastName
> Jon Smyth
> John Smith
> Jonathan Smythe
> John Smyth
> '
>|||>> have 2 system with name and adress (100.000 names), that have to be mer
ged into 1 system without any duplicates. The problem is that the spelling i
s not 100% between the system. <<
Look up Melissa Data and get their software. Life is too short to
re-invent the wheel.|||But it cost a lot of money.
It should not be so difficult to do it in sql server.
I looking for a little help and after that i fix it, i hope :)
For the soundindex and difference function is in SQL Server.
Can i only run a t-sql and get out the difference integer, after that i fix
a algorithm.
But how should i run the t-sql to validate these values?
// tw
"--CELKO--" <jcelko212@.earthlink.net> skrev i meddelandet
news:1122318950.891968.136790@.g14g2000cwa.googlegroups.com...
> Look up Melissa Data and get their software. Life is too short to
> re-invent the wheel.
>|||>> But it cost a lot of money.<<
How much does doing it wrong cost you? How much is your time worth?|||> But how should i run the t-sql to validate these values?
T-SQL is supplying you the difference and soundex values, no?
So, you must do one of three things:
(a) determine beforehand what soundex/difference level means duplicate;
(b) inspect the results manually and make decisions; or,
(c) get software that does it right, and you might get some sleep at night.

Dts.log in script task

Hi

Can someone tell me where I can find the log file created by the Dts.log method in script task.

I have created a log provider file as Mylog.xml, but the messages recorded are from the Dts.Event.FireInformation method and not the Dts.log method.

I don't know where the messages are filed.

Regards

Baldev

To log the output of Dts.Log() to a Log Provider, go to the "Configure SSIS Logs" dialog (e.g. SSIS/Logging...). Change the Logging Mode on the Script Task from the default of "UseParentSetting" to "Enabled". That is , click on the check box next to the Script task until it is checked ( LoggingMode = "Enabled") and not checked and greyed out (LoggingMode = "UseParentSetting") or unchecked (LoggingMode = "Disabled").

With the Script Task node selected, navigate to the Details tab, and select the "ScriptTaskLogEntry" event. Dts.Log() calls will now be sent to whatever log providers are enabled for the Script Task itself. Also, make sure to select the log provider for the script task "again", since this is effectively overriding the parent containers logging settings.

|||

Great, that's exactly what I wanted.

Thanks a lot

Baldev

DTS transfer

Hi,

I have the following method which transfers data SQL to SQL on the same server but when I try to change the destination server it won't transfer the data. The method runs through as expected with no exceptions or errors but with no data transfered.

Private

Sub TransferSQLData(ByVal SourceDetailsAs Admin_upload.EnvironmentDetails,ByVal DestinationDetailsAs Admin_upload.EnvironmentDetails)

'Transfer database from source to destination.Dim oPackageAsNew DTS.Package2Dim oConnectionAs DTS.Connection2Dim oStepAs DTS.Step2Dim oTaskAs DTS.TaskDim oCustomTaskAs DTS.TransferObjectsTask2Try

oStep = oPackage.Steps.New

oTask = oPackage.Tasks.New(

"DTSTransferObjectsTask")

oCustomTask = oTask.CustomTask

oPackage.FailOnError =

FalseWith oStep

.Name =

"Copy Database design and data"

.ExecuteInMainThread =

TrueEndWithWith oTask

.Name =

"GenericPkgTask"EndWithWith oCustomTask

.Name =

"DTSTransferObjectsTask"

.SourceServer = "MYSERVER"

.SourceUseTrustedConnection =

True

.SourceDatabase = SourceDetails.MetaDB

.SourceLogin = SourceDetails.MetaUser

.SourcePassword = SourceDetails.MetaPWD

.DestinationServer = "MYSERVER"

.DestinationUseTrustedConnection =

True

.DestinationDatabase = DestinationDetails.MetaDB

.DestinationLogin = DestinationDetails.MetaUser

.DestinationPassword = DestinationDetails.MetaPWD

.CopyAllObjects =

True

.IncludeDependencies =

False

.IncludeLogins =

False

.IncludeUsers =

False

.DropDestinationObjectsFirst =

True

.CopySchema =

True

.CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData

EndWith

oStep.TaskName = oCustomTask.Name

oPackage.Steps.Add(oStep)

oPackage.Tasks.Add(oTask)

oPackage.Execute()

Catch exAs Exception

LabelUploadMeta.Text =

"Failed to Upload MetaData: " & ex.MessageThrow exFinally

oConnection =

Nothing

oCustomTask =

Nothing

oTask =

Nothing

oStep =

Nothing

IfNot (oPackageIsNothing)Then

oPackage.UnInitialize()

EndIfEndTryEndSub

This works fine but when I set the following within the method:

.DestinationServer = "ANOTHERSERVER"

It won't transfer the data.

I can access the remote server and read and write data to it.

Any ideas?

Is the second SQL server registered on the SQL server where you run your DTS?

Does you user under DTS is running has rights to access second server?

Thanks

DTS transactions

Hi!
I am using SQLServer DTS object to manage my database. There is
BeginTransaction method. My question is:
1. In which database's context SQLServer.BeginTransaction starts
transaction?
2. How can I know/change current context in which SQLServer object works?Igor Solodovnikov, The DTS Object model has two hierarchies: 1) The DTS
Application hierarchy, which contains information about components registere
d
with the system and packages stored in SQL Serverand Meta Data Services, 2)
The DTS package hierarchy which contains all the the functional DTS elements
- tasks, steps, connections and global variables. Which hierarchy are you
using? Also, SQL Server 2000 Books online has a wealth of information about
the DTS Object model and it's methods and properties. If you have any furthe
r
questions, my email is frank_chang91@.hotmail.com.
"Igor Solodovnikov" wrote:

> Hi!
> I am using SQLServer DTS object to manage my database. There is
> BeginTransaction method. My question is:
> 1. In which database's context SQLServer.BeginTransaction starts
> transaction?
> 2. How can I know/change current context in which SQLServer object works?
>