Keeping Dirty Data
Jan 24, 2008
This may be more of a data design question and not an ssis question, but figured folks here could have a good idea.....the organization I'm in has the business need of collecting data from outside organizations and tracking what data is bad and what data is good. When I say bad data I mean everything from things outside of range to absolute *** - characters in integer columns, integers in character columns, special characters, etc. The data comes in in the form of flat file so it's a free for all until it hits ssis & the db engine.
Eventually of course they work to get the data corrected at the source & resubmitted but in the meantime, they have the legitimate need of not only pushing the data into the database (dirty or not), but keeping all the bad stuff. I can't in good conscience make everything a varchar to catch everything - that would go against the database gods. IMO - I still must make an integer be an integer , characters are characters, etc. But what do I do with the junk? Any thoughts?
View 4 Replies
ADVERTISEMENT
Jun 11, 2007
Is it possible to store data as varchar and keep line breaks, spaces, indents, and all that stuff intact?
Thanks again for all the help.
View 2 Replies
View Related
Oct 15, 2006
Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis? If I simply delete all the records the identities for the tables do not revert back to 1.
View 2 Replies
View Related
Jan 14, 2004
I want the users of my site to be able to write a couple of paragraphs, save it to SQL Server and then have it read back and look the same. I don't want the users to be able to add any html to the text they submit. I just want them to be able to seperate or indent their paragraphs.
At one point, I had a textbox that saved text to the server and read it back the same way it was originally inputed. However, I can't figure our what I had done to make it work.
Any ideas?
Thanks
View 2 Replies
View Related
Jun 22, 2015
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example
PostCode Latitude Longitude
66000/66100 42.696595 2.899370
20251/20270 42.196471 9.404951
Would become
PostCode Latitude Longitude
66000 42.696595 2.899370
66100 42.696595 2.899370
20251 42.196471 9.404951
20270 42.196471 9.404951
View 6 Replies
View Related
Jun 22, 2015
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example:
PostCodeLatitudeLongitude
66000/6610042.6965952.899370
20251/2027042.1964719.404951
Would become
PostCodeLatitudeLongitude
6600042.6965952.899370
6610042.6965952.899370
2025142.1964719.404951
2027042.1964719.404951
how this can be done?
View 2 Replies
View Related
Jun 10, 1999
Can someone tell me what a dirty read is?
View 1 Replies
View Related
Aug 1, 2001
If I'm doing a dirty reads and a someone updates a record when I'm trying to read it is it possible to read both the old and new records thereby retrieving two records?
View 2 Replies
View Related
Mar 29, 2006
Does any have a link, or know of an MS book(s) that details theunderlying database structure, tables, processes? Something thatexplains in detail how/why this stuff is configured and works, likeDDL, TDS, varchar, int, index, tables, normalization, DML, PrimaryKey/Foreign key.
View 4 Replies
View Related
Nov 19, 2007
Hi all,
Can anyone tell me how I can do a "Dirty Read" on a SqlDataSource Control? I'm affraid that the record locks are causing problems on the live system.
It is connected to an infromix db using odbc. Thanks.
View 1 Replies
View Related
Aug 26, 2005
Hi all
Using SQL 2000 MSDE
I'm bulk inserting about 3.200.000 records into a table
unfortunately all memory dissapears and never returns the dirty buffers count goes up to 48000 approx.
any ideas on how to rectify this ..... ?
View 2 Replies
View Related
Apr 17, 2008
Problem Statement........
Lets say user A accesses a record and is making an update to a column... next user B accesses the same record and makes an update to the same column and saves the data... how can user A check to see if an update has been made to prevent overwriting the data..
Is there a query statement that user A can write to check for this?
I understand locking can be used to prevent this but is there an alternative to locking.
View 5 Replies
View Related
Dec 1, 2005
All my queries are being blocked while the tables are being replicatedand it is causing some 2 minute blocking. Is there a way for theReplication to allow dirty reads because I really don't care aboutthat, I would rather have dirty reads than 2 minute waits.Thanks.
View 1 Replies
View Related
Feb 28, 2007
Hi all,
Hope this helps someone. My experience is that an interrupted install of SP2 screws things royally, despite signifying success.
My initial attempt at installing SQL Server 2005 SP2 (64-bit) on our dev server resulted in massive corruption of at least one SSIS solution. Without the option to uninstall the SP2, the prospect of a complete reinstall/recovery totally ruined my day.
When I loaded the SSIS solution, all packages returned the following connection based errors "connection manager will not acquire a connection because the package OffLineMode property is TRUE". OffLineMode property was in fact set to the default "False" for all packages. Individual tasks were broken also. I did not search all packages, but FTP and File System tasks were all corrupted. Nor could I successfully create these tasks in a new packages, even within a new solution.
During the install I recived the following error:
Error Log:
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2153
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29506
Error Description : MSP Error: 29506 SQL Server Setup failed to modify security permissions on file D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData for user xxxxx. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.
The SP2 install continued and with the exception of database Services, all other components were installed successfully.
A very helpful person had posted this solution:
The problem is that there exists a file in this Data directory that the user running SP1 does not have permissions to modify. The workaround is to figure out which file this is (typically a user created DB or some backup program files) and to temporarily assign permission to that file(s). You can revert back to the intended permissions after running SP1.
Sure enough, there was a data and a log file with permission restricted to a single user. Granting permission to the account doing the install worked.
I ran the SP2 again and the database Services component "succeeded". In fact, as outlined, my SSIS packages were unusable.
I re-ran the SP2 install. No components were selected for update, so I manually rechecked each component and executed. This time the install completed successfully for all components, and much to my relief, SSIS functionality was restored.
Pretty scrappy install package IMHO. There are obviously dependencies between the different components being updated, but if these dependencies, and therefore integrity of the update, are broken, the installer nevertheless reports success.
Cheers, Jeremy
View 8 Replies
View Related
Jul 23, 2005
Is it possible to set READ UNCOMMITTED to a user connecting to an SQL2000 server instance? I understand this can be done via a front endapplication. But what I am looking to do is to assign this to aspecific user when they login to the server via any entry application.Can this be set with a trigger?
View 1 Replies
View Related
Aug 26, 2002
SQl7, sp3, NT4
How do I keep th job history of a job, say if I re-create the job?
We recreate the jobs often as part of a code move, but I'd like to retain the history of the previous jobs?
** sp_help_jobhistory -- only shows the jobs that exist, and not old jobs that no longer reside on the server.
Thanks,
AJ
View 1 Replies
View Related
Jan 10, 2005
Hi all, here are my goals: Have the same DB on two different stand-alone computers, and keep them up-to-date from each other.
Basically a user would input to a DB for a week. Then every week or two, update the other stand alone DB with the new input. The DB would be exactly the same.
What are my options for this? I'd like it as easy as possible! Are there any software packages that deal with this type of transfer, etc.? Thank you!
View 5 Replies
View Related
Sep 24, 2006
Hello
my table :
Report :
R_id (PK)
RName
RDate
i am having a few 10.0000 lines and i want to keep the last 10 (or less if not in the table) rows maximum for each name
i can have 100 report by name (100 rows with the same name and of course R_id and RDate are different)
how can i do it ?
thanks a lot for helping
View 1 Replies
View Related
May 23, 2008
Hi, I have the following code in a query:
SELECT [Issue date],DATEDIFF("dd",[Issue date],[Start date])/365 AS runningdays
FROM Database1..[Insurance Policies Working DB]
WHERE [Policy Number] LIKE '%1368529%'
The part 'DATEDIFF("dd",[Issue date],[Start date])' comes out as 364 if calculated on its own. However, then when it is divided by 365 it comes out as 0. How do I get it to show as a decimal instead of just rounding it down automatically? (Hope I've made sense)
Thanks in advance
View 3 Replies
View Related
Nov 28, 2003
we have a simple table
Key, Name, Address, City, State, Zip ................ect
I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.
I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.
Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.
Appleby
Jones
Robertson
This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.
thanks in advance -arthur
View 3 Replies
View Related
Nov 7, 2001
Hi
We need to keep track of all changes that are made to our tables.
The changes will be saved in a table that records:
- the table in which the change was made
- the name of the field that was changed
- the old data for the field
- the new data for the field etc..
I've seen a few examples that record the name of the table that was
modified but none that record done to the field level.
Can anybody give some guidance?
Thanks..
Wayne
View 1 Replies
View Related
Feb 5, 2004
I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?
View 4 Replies
View Related
Feb 27, 2007
hi, is there any possiblity to keep break point and run the step by step compilation as like we do in .net,if means please telll me how to do?
View 3 Replies
View Related
Nov 13, 2007
Hello All,
I have a problem concerning keeping track of a value within a query.
I have a table that tracks invoices recieved and payments made.
For each invoice number there may be multiple payments made against it.
I need something that will check and make sure that each invoice number has its payments equal to its received amount.
Any help would be greatly appreciated.
Thanks,
View 5 Replies
View Related
Oct 16, 2006
Hello everyone,
I have a winform application with C# front end and sql express 05 backend.
In this database i have a table that holds manufacturer provided pricing and the manufacturers we work with update pricing constantly.
We have one table called "manufacturerpricing" which we are constantly inserting and deleting pricing records to/from to keep manufacturer pricing up to date. We may insert and delete as many as 2,000,000 records per month into this table.
This works perfectly fine and we have no problems here at all.
But with that being said, I am worried about the size of the database growing out of control due to temporary space etc. The database just keeps getting bigger and bigger.
How do I run some maintenance to keep the database size under control.
I would like to run this automatically from the C# front end so if ther is a stored proc I can call or an C# assembly I can reference that would be ideal.
Any help is greatly appreciated.
View 3 Replies
View Related
Dec 14, 2006
Hi,
I'd like to keep state between calls to a UDF (mainly for caching purposes). I can shove an object into the appdomain using SetData and read it using GetData, but that requires the assembly to be set to UNSAFE. I'm confident I can secure the DB and the assembly fairly well, but I like defense in depth, and if there's another way to save state between calls to a UDF, I would prefer those.
Is there another way to store state between calls to a UDF, without putting data into DB tables or using things that will require the assembly to have such a wide permission set?
Thanks,
Alex
View 4 Replies
View Related
May 8, 2007
Let's say I develop a 'version 1' of a program/database and ship it to some customers.
Then I continue work on version 2 with some databasechanges etc.
When version 2 is shipped to the customers, they need a way to upgrade their database
from version 1 to version 2. I guess this is easiest done with one/several sql-scripts.
What I want is your opinion about the best way to keep track of the changes between
version 1 and 2. Here are two ways with pros and cons I've thought of:
1 - Keep manual track of every change (index, changed columns, relations etc) and update a scriptfile with all changes.
Pros: Full control of what is happening
Cons: Pretty much extra work and risk of missing some changes.
2 - When version 2 is ready, run some third party tool to get a diff between the databases
Pros: Fast and easy
Cons: The DB-changes may need to be applied in a special order, with default values, etc etc and I'm not sure all tools handle this in a good way...?
So... what is your recomendations? I'm sure this must be a headace in every development project?
Regards Andreas
View 13 Replies
View Related
May 16, 2006
I am newish to databases and would appreciate some
advise. I think I have a solution to my
problem but it is going to take me a lot of time to get it running. If there is a better way of doing it I would
like to know.
I have a table :-
“eventDates? with
columns (id, date, eventID,
eventCount)
The id auto increments as a Primary Key.
date holds the date of the
event.
EventID references another
table with info about the events
Up to 9 eventIDs can be added
for each date and I want eventCount to hold an integer (1 to 9) to allow me to “pivot?
the data to the table below
“results? with columns (date,
eventCount1, eventCount2 …..eventCount9) so each row will hold a date and non
to nine eventIDs occurring on that date.
Is there an easy way to keep eventCount accurate or do I
just have to write a lot of code? I
will need to be able to remove events as well as add them. I will use a mixture of stored procedures
and VB.Net I guess?
Many thanks for any advice.
Mike
View 4 Replies
View Related
Jul 2, 2001
Hi, I am trying to find a way to capture all the status (Start time, execution time, Status messages etc) from executing a DTS package in to a table I will create in a database, does anyone know, where those information being kept?
When I excute the DTS package manually, a window will come up and show the status of each step within the DTS package. I am hoping to capture these information and load it to my log table.
Thanks in advance.
View 1 Replies
View Related
Aug 17, 1999
How can I instruct SQL Server to keep entire table in memory? ie the memory pages should not be swapped to HD.
View 2 Replies
View Related
Sep 19, 2000
I'm exporting a large database. In enterprise manager the settings on
all of the PK/FK relationships are that
"Enable relationship for Insert AND Update" is UNchecked.
I need it this way, so I can delete and insert to the tables
without being hassled by THE MAN.
When I export the database, using DTS I export all the objects (EVERYTHING),
and all the data too. When I open the freshly copied database and get
properties on any relationship "Enable relationship for Insert AND Update"
is CHECKED! ARGH!
How do I keep this from happening? I'm so frustrated.
It is very time consuming to uncheck that darn box on hundreds
of relationships. Why doesn't it just stay the way it is set in
the original source DB ??
Is there a way to export a database and keep it EXACTLY the same?
If anyone can help me with this it would save me dozens of hours in work.
Thanks in advance.
Josh
View 10 Replies
View Related
Aug 28, 2015
I have a trigger that keeps track of status changes...
IF UPDATE(STATUS)
BEGIN
DECLARE @currentdate datetime
DECLARE @currentstatus integer
DECLARE @UserID integer
DECLARE @PermitID integer
DECLARE @Status integer
[Code] .....
It works but not the way I want it to. The @currentstatus and @newstatus are the same. I want the status before and after the update. I asked around as to how to do this and some one told me to use the Deleted table.
View 3 Replies
View Related
May 14, 2004
I have just finished upsizing an Access database to SQL Server 2k. Now the SQL Server need to be run on a test basis to determine if i need to make more changes to the front-end (Access). The problem I am facing is how to keep the two databases in sync while I am testing. Any suggestions?
Also any suggestion or comments on how to run a test setup like this (in parrallel) are also welcome since this is my first time attempting a project like this.
Let me know if anyone needs more info.
Thanks in advance.
View 1 Replies
View Related
