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 RepliesWhat tools has everyone used for cleaning name and address data(including identifying not-immediately-obvious duplicates) inconnection with a CRM project or the Customer dimension of a datawarehouse? What did you like/dislike about the tool you used? Howcustomizable was the tool you used?
View 10 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
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.
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
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
I've recently inhereted an environment in which we have over 600 storedprocedures. The documentation is either very poor or non-existant andam needing advice on how to determine if a stored procedure has beenused. Does SQL Server have any sort of ticker that indicates when astored procedure was last used?Thanks and any additional information or experience would be greatlyappreciated.
View 2 Replies View RelatedHi all,
I'm trying delete metadata of a sql 2005 sp1 subscriber from a sql 2005 sp1 merge publication, but is not working, the "retention" parameter for the publication is 999 and this is the code I'm using:
declare @num_genhistory_rows int,
@num_contents_rows int,
@num_tombstone_rows int
declare @retcode smallint
--select count(*) from msmerge_contents
-- records before 2,633,848
exec @retcode = sys.sp_mergemetadataretentioncleanup @num_genhistory_rows OUTPUT , @num_contents_rows OUTPUT , @num_tombstone_rows OUTPUT
select retcode =@retcode
select num_genhistory_rows =@num_genhistory_rows
select num_contents_rows=@num_contents_rows
select num_tombstone_rows=@num_tombstone_rows
--select count(*) from msmerge_contents
-- records after 2,633,8
Results :
retcode
0
num_genhistory_rows
0
num_contents_rows
0
num_tombstone_rows
0
Has omebody any idea why this is not working ?
I did check "sp_mergemetadataretentioncleanup " and I note that is using a function to calculate the limit date, but I could not testing because it give me the below error :
declare @curdate datetime, @cutoffdate datetime
select @curdate = getdate()
select @cutoffdate = null
-- find max retention of all pubs the article belongs to.
-- add some safety margin to compensate for different clock speeds
select @cutoffdate = min(sys.fn_subtract_units_from_date(isnull(retention,0), retention_period_unit, @curdate))
from dbo.sysmergepublications where
pubid in (select pubid from dbo.sysmergearticles where nickname = 5088000)
select @cutoffdate
and this is the message error:
Msg 4121, Level 16, State 1, Line 7
Cannot find either column "sys" or the user-defined function or aggregate "sys.fn_subtract_units_from_date", or the name is ambiguous.
I looked this function but I didn't find it.
any help will be appreciated !
For anyone who can help me resolve this minor data issue. I am trying to clean a phone number column so that all numbers are reflected in the same way. I have phone numbers in my column in this this format:
(123)123-1234
123-123-1234
(123) 123-1234
How can I clean this column so that the numbers are reflected in this way: 1231231234
I'm having brain freez right now. Help me please!!! Thanks in Advance
Hi, all here,
Thank you very much for your kind attention.
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
Thank you very much in advance for any help.
With best regards,
Yours sincerely,
After testing out the application i write on the local pc. I deploy it to the webserver to test it out. I get this error.
System.Data.SqlClient.SqlException: The conversion of a char data type to a
datetime data type resulted in an out-of-range datetime value.
Notes: all pages that have this error either has a repeater or datagrid which load data when page loading.
At first I thought the problem is with the date, but then I can see
that some other pages that has datagrid ( that has a date field) work
just fine.
anyone having this problem before?? hopefully you guys can help.
Thanks,
I have used both data readers and data adapters(with datasets) in the projects that I have worked on. I am trying to get some clarification on when I should be using which one. I think I am doing this correctly but I want to be sure I am developing good habits.
As the name might suggest, it seems like a datareader is for only reading data. I have read that the data adapter and dataset are for a disconnected architecture. Or, that they can be used for this type of set up. I have been using the data adapter and datasets when writing to a database and the datareader when reading from a database.
Is this how these should be used? Is the data reader the best choice for reading data? Am I doing this the optimal way from a performance stand point?
......................................................thanks in advance
We already integrated different client data to MDS with MS Excel plugin, now we want to push back updated or new added record to source database. is it possible do using MDS? Do we have any background sync process to which automatically sync data to and from subscriber and MDS?
View 4 Replies View RelatedWhen I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
View 7 Replies View RelatedI have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
Hi,This is driving me nuts, I have a table that stores notes regarding anoperation in an IMAGE data type field in MS SQL Server 2000.I can read and write no problem using Access using the StrConv function andI can Update the field correctly in T-SQL using:DECLARE @ptrval varbinary(16)SELECT @ptrval = TEXTPTR(BITS_data)FROM mytable_BINARY WHERE ID = 'RB215'WRITETEXT OPERATION_BINARY.BITS @ptrval 'My notes for this operation'However, I just can not seem to be able to convert back to text theinformation once it is stored using T-SQL.My selects keep returning bin data.How to do this! Thanks for your help.SD
View 1 Replies View RelatedI'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below:Â
SQL Server：
Oracle:
DDL:
create table Person
(
BusinessEntityID Integer,
FirstName nvarchar2(50),
MiddleName nvarchar2(50),
LastName nvarchar2(50)
);
Result:
I follow up this article:Â [URL] ....
VB Script:Â
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
[Code] ..........
Hi all, i got this error:
[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
and also this:
[Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection.
I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected.
It seems that somehow the 3 columns are not read in from the source file?
ans alslo fiscal year, fiscal week is not set up up properly in my data destination?
anyone faced such errors before?
Thanks
When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".
USE [FileSharing]
GO
/****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] .....
Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
(1 row(s) affected)
is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
RE: XML Data source .. Expression? Variable? Connection? Error: unable to read the XML data.
I want my XML Data source to be an expression as i will be looping through a directory of xml files.
I don't see the expression property or the connection property??
I tried setting the XMLData property to @[User::filename], but that results in:
Information: 0x40043006 at Load XML Files, DTS.Pipeline: Prepare for Execute phase is beginning.
Error: 0xC02090D0 at Load XML Files, XML Source [108]: The component "XML Source" (108) was unable to read the XML data.
Error: 0xC0047019 at Load XML Files, DTS.Pipeline: component "XML Source" (108) failed the prepare phase and returned error code 0xC02090D0.
Information: 0x4004300B at Load XML Files, DTS.Pipeline: "component "OLE DB Destination" (341)" wrote 0 rows.
Task failed: Load XML Files
Information: 0xC002F30E at Bad, File System Task: File or directory "d:jcpxmlLoadjcp2.xml.bad" was deleted.
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
The program '[3312] Package.dtsx: DTS' has exited with code 0 (0x0).
Thanks for any help or information.