What tools has everyone used for cleaning name and address data
(including identifying not-immediately-obvious duplicates) in
connection with a CRM project or the Customer dimension of a data
warehouse? What did you like/dislike about the tool you used? How
customizable was the tool you used?
Hey gang, I have 2 100,000 record tables, with a ID & address field. (I've exclude all the other field for now) I need to make sure the ID field in table A is equal to table B and that the address in table A is equal to table B. If the Id's or address do not match create a separate table. Any good ideas
View 2 Replies View RelatedAll
I am in trying to clean and standardize the data during the ETL processes using the €œLookup Data Flow Transformation€? in SSIS€¦
I am able to clean data by replacing the values in columns with values from a reference table, using an exact lookup to locate values in a reference table.
What I would like to do is €œif there is NO exact match€? replace it by e.g. zero or some other value which means €œno reference data available€?, how do I do this?
Any help is much appreciated.
Thanks,
Manojkumar
hi everyone,
can any one suggest me how to clean the 'REPLDATA' folder created during snapshot replication. i want to automate the process.
can anyone suggest any undocumented sp like
'sp_MSDelete_publisherdb_trans'
thanks
I have few questions
1.Input column has dates in this format.
90Q1(this is 1990,march)
90Q2(this is 1990 june)
Is there a way to convert 90Q1 to 1990 march?
2.Patient Name column has to be split into Last name,First Name, Middle Name
Can this be done??
3.For one column i have _9 at the end of each ID
How do i remove _9??Is there a way to remove?
Please let me know
Hi everyone,
I'm trying to integrate SQLEXPRESS in a custom application ("the app"). On installation, a new instance ("myNewInstance") is created, and a new DB ("myDB") is created on that instance.
Upon de-installation of "the app", I remove the new instance. To that end, I'm calling the SQLEXPRESS setup like this:
Code Snippet
setup.exe /qb REMOVE=SQL_Engine INSTANCENAME=myNewInstance
The Problem: While the service and the instance are removed, the instance's data directory is not, and the DB Datafiles are still there. Now if I reinstall the app (re-creating myNewInstance), it uses the same directory structure, and when I try to re-create the DB, I get the following error message:
Code Snippet
Msg 5170, Level 16, State 1, Line 1
Cannot create file '[...]MSSQL.2MSSQLDATAmyDB.mdf' because it already exists. Change the file path or the file name, and retry the operation.
So here's the Question: is there
a) any way to tell setup.exe to completely remove the datafiles when uninstalling the instance, or
b) any way to tell TSQL to overwrite the old datafiles if they exist?
Thanks in advance,
Thorsten
Hi all,
When I get this message "The merge process is cleaning up meta data in database 'xDatabase'." at distributor MS SQL 2005 replicator monitor on one of his suscription, this proccess generate so proccess charge to the suscriber that users note difference in performance. I wonder if exist a way to change some kind of parameter for this proccess run on specific schedule ?
Any help would be appreciated.
other info I had suscribers that only replicate in one way, maybe this metadata is causing this overcharge.
Hi All,
I am new to SQL Server. I want to know what is data scrubbing (data cleaning). What are the different tools that can be used for data scrubbing.
Where can i find good tutorial or data for data scrubbing?
Thanks
Hi all,
I'm trying to join an Address table's columns:
ID, AddressLine1, AddressLine2, AddressLine3, ..., AddressLine7
into a contiguous layout. The issue is some fields are empty.
so I might have:
id ad1 ad2 ad3 ad4 ...
1 aa __ vv __
2 __ xx __ __
3 tt rr __ ee
I need to turn it into:
id ad1 ad2 ad3 ad4 ...
1 aa vv __ __
2 xx __ __ __
3 tt rr ee __
I've done this up with a cursor, a user defined function, but I need to do it up 2 other way. My mind is blank - any ideas would be great
colabus
Hi all,
I'd like to link a SQL Server database / table to Microsoft Exchange Server E-Mail-Address data, for an Employee Directory project.
I've read a little about "Web Store", a little about ExOLEDB, but I've come across nothing along practical lines. I.e., I still don't know where to begin!
For example the "Web Store" should be created on the SQL server, but how do you create it? Any insights will be greatly appreciated.
Gallagher
How should I go about releasing any objects I've created? I've got a very complex CLR stored proc that creates a dataset, many sqlcommand objects, an adapter, etc, etc. It's used to evaluate high school transcripts for completion of the core subjects. It's a very complex SP that takes a single SSN as a parameter.
Right now it works as expected when I call it from a query window in SQL Mgmt Studio, I can stack 39 calls in a row like:
exec usp_Evaluate_eTranscript '011...'
exec usp_Evaluate_eTranscript '012...'
...
exec usp_Evaluate_eTranscript '039...'
And if I build a little test harness in VB.Net calling it in a populated dataset For ... Next loop, it works just fine for the 39 valid values I have.
But, when I build a cursor in a query window in MgmtStudio I get some strange behavior, that just doesn't really make sense. I'm getting a foreign key violation and I'm not even trying to insert any rows, just updating existing ones.
So, the only possible cause that I haven't eliminated yet is that some objects are still hanging around when the cursor tries to call the SP again.
It's declared:
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub usp_Evaluate_eTranscript(ByVal SSN As String)
And exits with the standard End Sub.
Is there some way I can help the garbage collector along? Kick it into action? Maybe some
Set sqlcommand = Nothing or
sqlcommand.Dispose
dataset.Clear or
dataset.Dispose or somesuch thing?
I'm closing the context connection immediately after each time I use it (I have to so the next object can work).
Can someone give me some advise on how to clean out my log file?
Thanks :confused:
Hello
my 2 tables in MS SQL 2000
Report :
Report_id (PK)
name
Product :
Product_id (PK)
Report_id (FK)
name
the Foreign Key and Primary Key have been added later (when the tables were allready full)
product has a few millions of lines and report a few 10.thousand
now I want to clean the 2 tables and remove all the lines which are not conected by PK > FK or FK > PK
i am trying :
DELETE FROM Product WHERE (Product.Report_id NOT IN (SELECT Report.Report_id FROM Report))
DELETE FROM Report WHERE (Report.Report_id NOT IN (SELECT Product.Report_id FROM Product))
but the database crash : time overflow !
how can I do it ?
thank you
Hi there, I have the following set of records: (DDL and data provided)
create table dbo.myNames (
FilePersonId int,
Sex varchar(20),
Title varchar(20),
Forename varchar(50),
Surname varchar(50)
)
insert into dbo.myNames (FilePersonId, Sex, Title, Forename, Surname)
select 477827, 'F', 'MISS', 'NATALIE', 'MR NIEL MC DONOUGH' union all
select 516992, 'M', 'MR', 'MARIO.', 'MISS CARLA THRINCHEIRAS' union all
select 1725198, 'M', 'MR', 'THOMAS', 'MS TAGGERT' union all
select 1907673, 'M', 'MR', 'COLIN', 'MR C PATTERSON' union all
select 2282432, 'M', 'MR', 'JOHN', 'MR GOLDSMITH' union all
select 3172847, 'M', 'MR', 'JASON', 'MR JASON MARCUS RENDALL' union all
select 3173628, 'M', 'MR', 'STUART', 'MR STUART IAN JAMES CALLOW' union all
select 3174343, 'M', 'MR', 'JOHANNES', 'MR JOHANNES ARIE JAN DE JAGER' union all
select 3176784, 'F', 'MISS', 'MONICA', 'MISS MONICA S F ALMEIDA' union all
select 3177717, 'M', 'MR', 'MOHAMMAD', 'MR MOHAMMAD NASIR KHAN SABIR' union all
select 3180017, 'M', 'MR', 'MD', 'MR MD. MAMUN UR RASHID' union all
select 3180585, 'F', 'MISS', 'KATHRYN', 'MISS KATHRYN M J GRANT' union all
select 3182306, 'M', 'MR', 'ABDUL', 'MR ABUL K M H KABIR' union all
select 3182416, 'F', 'MRS', 'CATHERINE', 'MRS CATHERINE ANN LEA DOIG' union all
select 3183759, 'M', 'MR', 'PHILIP', 'MR PHILIP ADAM MOXON' union all
select 3183790, 'F', 'MISS', 'KELLY', 'MISS KELLY NEAVE CHAPPELL' union all
select 3191895, 'F', 'MISS', 'LUCY', 'MISS LUCY ANNE MAY GAULT' union all
select 3192189, 'M', 'MR', 'JOAQUIM', 'MR JOAQUIM FILIPE ABRANTES VAZ' union all
select 3192261, 'F', 'MRS', 'C', 'MRS C D V BALBO-BELL' union all
select 3193077, 'M', 'MR', 'NIDAL', 'MR NIDAL FATHEE ABY SABEI' union all
select 3194554, 'M', 'MR', 'STUART', 'MR STUART ROBERT JAMES DOYLE' union all
select 3201437, 'M', 'MR', 'IAN', 'MR IAN' union all
select 3206600, 'M', 'MR', 'ADRIAN', 'MR ADRIAN JOHN JAMES OWEN' union all
select 3206637, 'M', 'MR', 'J', 'MR J O GARCIA CHAURAN' union all
select 3206695, 'M', 'MR', 'SYED', 'MR SYED SAFDAR SHAH' union all
select 3206744, 'M', 'MR', 'LEE', 'MR LEE ALAN JOEL MAYNARD' union all
select 3206763, 'F', 'MISS', 'TANIA', 'MISS TANIA RACHEL NASH' union all
select 3207104, 'M', 'MR', 'ALEXANDER', 'MR ALEXANDER C D HOPWOOD' union all
select 3207577, 'M', 'MR', 'J', 'MR J H S CAMPBELL' union all
select 3207611, 'F', 'MISS', 'TERRI', 'MISS TERRI LOUISE KATE FLEMING' union all
select 3209050, 'F', 'MISS', 'MELISSA', 'MISS MELISSA NEKITA MAKEMSON' union all
select 3213291, 'M', 'MR', 'MALCOLM', 'MR MALCOLM PETER FORD CROOK' union all
select 3215920, 'M', 'MR', 'IAIN', 'MR IAIN ALASTAIR SMITH DOWNEY' union all
select 3889047, 'M', 'MR', 'PEDORA', 'MR LOFARO' union all
select 3974174, 'M', 'MR', 'MICHAEL', 'MR MICHAEL J KERR' union all
select 3977713, 'M', 'MR', 'XAVIER', 'MR XAVIER DE PONTE DE SOUSA' union all
select 3977753, 'M', 'MR', 'JEAN-DANIEL', 'MR JEAN-DANIEL KENNY FLORIAN' union all
select 6815795, 'U', 'UNKNOWN OR INVALID', 'M', 'MR M WOOD' union all
select 7617439, 'F', 'MRS', 'M', 'MRS MARYMARSHALL' union all
select 7929645, 'M', 'MR', 'RICHARD', 'MR PANTER' union all
select 9092854, 'M', 'MR', 'JARED', 'MR JARED ELVANDO BLAKE' union all
select 9109880, 'M', 'MR', 'MICHAEL', 'MR MICHAEL PAUL LLOYD RAMWELL' union all
select 9165326, 'M', 'MR', 'SCOTT', 'MR SCOTT DE LA FUENTE' union all
select 9321272, 'M', 'MR', 'MPHAMMAD', 'MR MPHAMMAD HAMIDI TABAR' union all
select 9461030, 'M', 'MR', 'KELVIN', 'MR KELVIN POH ANN TAN' union all
select 9508257, 'M', 'MR', 'HARDI', 'MR HARDI M IBRAHIM' union all
select 7899594, 'F', 'MRS', 'SANDRA', 'MRSTHPOMPSON' union all
select 9616820, 'U', 'UNKNOWN OR INVALID', 'HAMMOND', 'MRJOHNHAMMOND' union all
select 9617471, 'M', 'MR', 'PAUL', 'MRPAULWAYMAN' union all
select 9817442, 'M', 'MR', 'ADAM', 'MRADAMMARLEY' union all
select 9853852, 'F', 'MISS', 'P', 'MRBARRYGILMARTIN' union all
select 9895683, 'F', 'MRS', 'REBECCA', 'MRSREBECCAWILSON' union all
select 9924241, 'M', 'MR', 'JOHN', 'MRJOHNWORDEN' union all
select 9941612, 'M', 'MR', 'BRIAN', 'MRBRIANOKEEFFE' union all
select 10214780, 'M', 'MR', 'G', 'MRJAMESWADDLE'
What I am trying to do is cleanse the Surname field. I need to remove the Title/Initials and/or Forename from the Title field. But I am struggling to come up with a neat solution to this problem.
Your help would be much appreciated.
Thanks.
Am customizing SQL server MGMT tools 2012 for Mass deployment.Client had asked to remove Customer Feedback option from help menu.how to disable that.
View 6 Replies View RelatedThe installation SQL 2008 R2 Management Tools on a Windows 7 workstation fails with the error, The specified account already exists.
Final result:Â SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
 Exit code (Decimal):          -2068052700
 Exit facility code:           1212
 Exit error code:              1316
Â
[code]...
I am running SQL Server 2005 x64 Enterprise under Windows 2003 x64 Enterprise. My current backup strategy uses T-SQL jobs run by SQL Agent (writes out *.bak files) and then I have an Integration Services job that copies the *.bak files to our NAS device. I have performed a restore without issue. The jobs are all automated every four hours via SQL Agent. Is this a sound strategy or are there additional benefits to using 3rd party tools? If so, what are the advantages and which tool provides them?
does any one has any sql server function that passes some text and returns a string without html tags.
example:
nice day
should return nice day
or if other html tags strip them off.
thanks for your help.
-Fr
I have been getting some strange results while trying to reduce the size of a transaction log file. The background is the database was fully backed up last night. There has been no activity since the backup. A transaction backup was performed 30 minuted prior to my testing. Here is what I tried:
1. dbcc shrinkdatabase ('mydb',1)
Resulted in no change to the size of the log file (Still 110meg)
2. Right clicked on the database in Enterprise Manager and selected Shrink Database. I selected the log file to shrink and it shrunk to 29meg.
3. Tried dbcc shrinkdatabase ('mydb',1) again and no change.
4. Issued backup log mydb with truncate_only and
dbcc shrinkdatabase ('mydb',1) and the size shrunk to 2 meg.
Am I doing something wrong? I thought that after a full backup and backing up the log file, the out of date log records would be able to be shrunk out.
also, why does the Enterprise Manager shrink work differently from the dbcc command?
Any help would be greatly appreciated.
Thanks,
Ken Nicholson
Hey,
Does anyone know of a neat and easy way to modify this section of hardcode:
SELECT CASE dbo.requestsbyyeartemp.themonth when '1' then 'January' when '2' then 'Febuary' when '3' then 'March'
when '4' then 'April' when '5' then 'May' when '6' then 'June' when '7' then 'July' when '8' then 'August'
when '9' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as 'themonth', etc...
Requestsbyyeartemp is a table where the numbers corresponding to the months are stored. This statement is used to make a new table where the months are stored with the proper names and not numbers.
I would sooner not have any hardcode at all if there is a simple way to do it.
Thanks
Login failed for user 'TOSHIBA-USERASPNET'
I know that the file persmission for the web application have to include aspnet, so i keep resetting the folder permission for aspnet in file manager, but the login failed keeps coming back every day or two
problem is after working with VS05 Pro, SQL Server Management Studio CTP, somehow the aspnet persmission get changed, use alot of sqldatasource wizards and often there is a conflict/hang between the datasource wizard and the need to have the mdf in a dettached state within VS server explorer,
not sure but the procedure to fix this seems to be to reboot, detach and re-attach the mdf in the Sql server Studio tool, re-apply the aspnet file permission on the web app folders (wonder should i be doing this in IIS instead), make sure the mdf within server explorer is detached, the it works
anyway, getting real tired of the resulting delays and design time derailment, clues greatly appreciated, thanks
sometimes i can use View in Browser when in VS05 form view and i wont get the aspnet folder permission error and other times i do.
last thing, is it a bad idea to give aspnet full permission for the entire web applicaiton??
What is the best tool for converting data in to MSSQL 7.0? We get our data in dataflex format and currently use data
junction to migrate the data into tables in sqlserver. Can DTS do this and also include and scrubbing or translation script?
Hi,
does anyone know of any data modeling tools that support or intend to support SQL 7.0 ?
I`d appreciate any comments ...
Kris Klasen
Act. Manager Data Warehouse Project
Information Management Branch
Department of Education, Training, Community and Cultural Development
E-mail: Kris.Klasen@Central.Tased.Edu.Au
http://www.tased.edu.au
Tel: 03 6233 6900
Fax: 03 6233 6969
73 Murray Street
2nd Floor
Hobart 7000
Tasmania
Hello Everyone,
Does anyone have experience in migrating data (not structure) from an Oracle 7.x database to a new schema built on SQL 6.5? Especially using third party migration tools, for example Data Junction 6.5?
I would be interested in seeing any materials you can provide or advise as I start down this path.
Thanks in advance,
Troy
i have a database for an application, which has some particular fields in some table. i want to make sure that data update in those fields should be only thru the application, and no one (even dba) should be able to update data in those fields thru sql tools.
this is to make sure that no body is updating the field except the users thru the application. as these fields contain infor about "approval" of documents.
Hi,
I might be asking one of the most weird and most irrelevant question... but actually I am very new to this technology, and need help!
If I am asked about 'Statistical Tools For Data Mining', then in which way do I go?
Is it related to the various methods like Neural Network, K-Nearest Neighbour (KNN), etc. or is it related to something else?
(A few statistical tool examples would be highly appreciated!)
Thanx a lot for you time and advice!
Best Wishes.
For prototyping purposes, is there a tool that reads a database's schema and generates (simple) table editor that allows manual editing of data. It would be good if the tool somehow made it easy to add foreign key values to reference data e.g. gender, status, ...I can kind of do this using Management Studios "Edit Top 200 Rows", but I was wondering if there is a more user friendly tool out there.
View 3 Replies View Related
In the process of building a data warehouse is their an easy to test if the data received is right data.
Are there any tools free tools available
Or do i have to build these ssis packages
Please let me know
Hello,
Does anyone have any experience with a specialized Data Cleasing tool?
In what scenario will I need a specialized tool? why shouldn't just the SSIS controls provided out of the box to ensure data quality be sufficient?
regards,
Abhishek.
Is there - apart from the notorious RESTORE HEADERONLY - an tool which is able to tell which SQL Server version created a specific BAK file? I'm looking for a tool that can be used w/o an available/running SQL Server installation.
Alternatively, is there any documentation about what is read with RESTORE HEADERONLY so I could write a tool myself?
Where would I find the version "bytes" in a BAK file?
I have been researching on how to cleanup four backup system files located in the MSDB database. I currently have approximately 1 million rows in each of these tables which is making my MSDB database to be 4 gig in size. I cannot find any instructions on how to gracefully clean these tables up or what to do to keep the number of rows down in these tables. The tables are backupfile, backupmediafamily, backupmediaset, and backupset. Thanks.
View 1 Replies View RelatedBack Story...
i am building a new deployment routine for one of our products using NANT (.net version of ANT). I takes all of .sql files out of vault for all non table objects and concatenates them into larger .sql files with IF EXISTS .... DROP ... GO CREATE statements which will then be fired off by osql batch files that will log the errors.
Problem...
When I open the resulting files in notepad I have junk characters (microsoft OEM stuff) in some of my object definitions which you do not see in the QA or if you opnen the file in Vault. I need to get this stuff out of there and I do not want to write a bunch of filesystem code to do it because I need this tested and ready by Friday COB. I know I can solve this by opening the problematic files in ultraedit and saving them one by one but I do have that kind of time.
Any ideas?
It's going to be a long couple of days.
I am running a maintenance cleanup task as shown below but despite the log saying "success" it is not successful as nothing is cleaned up.
Maintenance Cleanup Task (VPS20604)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2015-07-08T16:14:24.
Task end: 2015-07-08T16:14:24.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''C:SQLBkp'',N'''',N''2015-06-10T16:14:24''
GO