I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
I need to encrypt some column level data in multiple tables in SQL server 2014. I've never tried encryption in SQL server 2014. How can I achieve it?
View 4 Replies View RelatedI want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!'
   CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs'
   CREATE SYMMETRIC KEY ERKEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
       Email
      -------
   1 | 0x00703529AF46D24BA863A3534260374E01000000328909B51BA44A49510F24DF31
C46F2E30977626D96617E2BD13D9115EB578852EEBAE326B8F3E2D422230478A29767C
   2 | 0x00703529AF46D24BA863A3534260374E01000000773E06E1B53F2C57F97C54370FECBB45B
C8A154FEA5CEEB9B6BB1133305282328AAFAD65B9BDC595F0006474190F6482
   3 | 0x00703529AF46D24BA863A3534260374E01000000C9EDB1C83B52E60598038D832D34
D75867AB0ABB23F9044B7EBC76832F22C432A867078D10974DC3717D6086D3031BDB
But, how do I work around this?
Does anyone have any insight as to whether the new data encryption features of SQL Server 2005 work when the database is set to compatibility level 80?
View 1 Replies View RelatedWhere can I get some information on field-level encryption in SQL Server? My users will be installing my ASP.NET app on a laptop and taking it out "into the field". If it gets stolen they want some safeguards.I've suggested that they encrypt the hard drive and things, but they want field-level encryption for the data. I know how to do things like hash a password, but that doesn't help with retrieval. I have to be able to search on and decode the data. For example, I have SSN as a field. I need to be able to search on SSN, display it to the user, and allow them to change it.The other problem is partial searches. If I encrypt SSN in the database then I can probably just search on the hashed value, but if I want to search on "smith" and get all of the smiths, smithes, etc., then I can't search on the encrypted value. I have about 1 million records, so decrypting each name to do the search would be prohibitive.Anyway, I'm sure this has been done, so if there is a thread or a site that discusses this I'd be willing to read up on it.
View 1 Replies View RelatedDeveloping an application, which is handling encrypting for a SQL server 2005 DB at the application level, using symmetric AES encryption. After being encrypted the data is being sent to the SQL server 2005 DB to be inserted. This is what I want to accomplish. But I have one or two questions as I am going through the research for my project.
Encrypting the data shouldn€™t be that big a deal, but can SQL server 2005 handle to insert these data? If yes how? I am thinking just simple SQL INSERT statements?
When the data are inserted can SQL server, as it supports AES encryption, through the DBMS decrypt the data as they have been encrypted else where and then perform specific functions? (by specific function I just mean any function that may have to run, it could be any function one could think of)
How difficult would it be to take the encrypted data from SQL server to the application and then at the application level decrypt these?
Any help on the above questions is highly appreciated or if people have any comment on the subject I would be more than happy to receive these.
Thank you
Kenn Kikkenborg
Developing an application, which is handling encrypting for a SQL server 2005 DB at the application level, using symmetric AES encryption. After being encrypted the data is being sent to the SQL server 2005 DB to be inserted. This is what I want to accomplish. But I have one or two questions as I am going through the research for my project.
Encrypting the data shouldn€™t be that big a deal, but can SQL server 2005 handle to insert these data? If yes how? I am thinking just simple SQL INSERT statements.
When the data are inserted can SQL server, as it support AES encryption, through the DBMS decrypt the data as they have been encrypted else where and then perform specific functions? (by specific function I just mean any function that may have to run, it could be any function one could think of)
How difficult would it be to take the encrypted data from SQL server to the application and then at the application level decrypt these?
Any help on the above questions is highly appreciated or if people have any comment on the subject I would be more than happy to receive these.
Thank you
Kenn Kikkenborg
We have a need for getting data from sqlserver DB on premise to the cloud. DB is not encrypted currently, other applications are accessing it but those applications are on premise. Following link talks about encrypted connection, but is it possible to encrypt only one of the port connection. we can add a custom port.URL...
View 3 Replies View RelatedThe problem that I am having is that with Visual Web Developer I am creating a webpage and having it directly put online, so for example when I start a new ASP.NET page, I select the location to be HTTP, with the location http://MYWEBSERVER/Website and for the language and Visual BasicI notice a couple of things, first that there is no longer a a link under the Main toolbar "Website" selection called the ASP.NET configuration. So how can I configure what I want to have users be able to do? It seems that this choice is only available if I am building the ASP.NET page on my "localhost". So that is the first problem. So I am able to get the pages to work, atleast the things such as the textboxes to show up etc, (Even things as advanced as the "Login" box). How ever when I try to get someone to try to login I am taken to a page that has an server error. The error is:
"The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. "
The Stack Trace Errors are at the bottom. I think this is happening because the automatically generated databases are not getting built online as they are on my computer. On my computer I have MSSQL express. So either the databases are not getting built for some reason, (and I think that is the case as I don't see any in the folder). So I think that somehow I have to create a database on my server, and then somehow configure the ASP.NET file, perhaps in the Web.Config file to look for that new database. Is this the correct methodology? Is there some simpler way that I can just somehow upload things as they are and have them work correctly on my server? The error says that either the Server did not find the database or that the trust level was insufficient. I don't think that is it as I just looked again and I don't see any .MDF files. So how would I go about getting this to work right? Is there a way to do this with MySQL also? So that I don't have to use MSSQL? My server only allows 1 DataBase for that.
Thanks and I hope my question makes sense. It is basically how can I get it to be able to create and check users etc. online?
Brian
[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.] System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2555237 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
I have created mirroring... one of the column is encrypted on mirror database and I can see the decrypted result when I do query when I actually logged into server (through remote connection) but when I use the same query through using SSMS from my laptop the query result come as the column is not decrypted,
View 0 Replies View Related
Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance
I have created two user defined functions for encryption and decryption using passphrase mechanism. When I call encryption function, each time I am getting the different values for the same input. While I searching a particular value, it takes long time to retrieve due to calling decryption function for each row.
best way to encrypt and decrypt using user defined functions.Below is the query which is taking long time.
SELECT ID FROM table WITH (NOLOCK)
                    WHERE dbo.DecodeFunction(column) = 'value'
When I try to use symetric or asymetric encryption, I am not able to put "OPEN SYMETRIC KEY" code in a function. So, I am using PassPhrase mechanism.
When you utilize transactions in ADO.NET are the locks put on the entire TABLE used or at the row level?
For instance if you do a SELECT within a transaction if you only pull 5 rows out of a 1000 row table can you just make it lock the rows that have been pulled? It seems like it locks the entire table?
Thanks
Hi, Can anybody please explain me, what is low level and high level locking in SQL Server 2005 database.
Also what is the name of process which converts low level locking into high level locking and vise versa.
-Sanjeev
Hi..I'd very much appreciate it if someone would tell me how to translatea statement level trigger written in Oracle to its equivalent (if there isone)in MS SQL Server. Ditto for a row level trigger.If this is an old topic, I apologize. I'm very much a newbie to SQL Server.Regards,Allan M. Hart
View 2 Replies View RelatedHello all.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
tia,
Jeremy
Is it possible to have a corruption in one of the columns in a table at a specific row? If yes, what could be the possible causes for such corruption?
Why I am asking? Because it happened that I wasn't able to read the value of a specific column but I was able to update it with other value! it sounds weird if you are able to write but not to read!!
**I wasn't able to read but after I updated it with another value...it was readable...
Please advise...??
Using VS05 pro SP1...
(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)
This is for a telephony application using Last In Wins concurrency...
Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.
We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...
- Fill table A and table B identically
- change column1 value in table A
- Update table A
- change column2 value in table B
- Update table B - this will generate a concurrency error.
Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.
So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.
I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.
Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.
Thanks!
Bob
Hey,
I have 3 columns in a table Ex:
Select Column1,Column2,Column from TableName
No. 1. Person A should have permission to read values only (Column1, Column3 of the table) -
2. Person B, should have permission to read only (Column 2).
Here my question is , I have to write one single stored procedure to statisfy both conditions. Which means, if person A execute this stored procedure , he shoud get only column 1 & 3 values . similarly other person b should get column 2 value. Ex:
Column 1 - Empid
Column 2 - SSN (Only for Top user display)
Column 3 - Join Date
Person A & B as a SQL or Windows login
Thanks
Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008.Â
Ex.:Â
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states.  Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.Â
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
Is there a graphical tool to manage column-level permissions just as it was possible in Enterprise Manager for Server 6.X ?
View 1 Replies View Related
Hi,
I am trying to find the best way to implement a complex column based security.
Basically i need to secure data among lot of tables with millions of rows, so that some users may see some columns and others see diferent ones...
Whats the best way to implement this kind of procedure securely and flexible?
Best Regards,
This function is used to initialize the seed for the RC4 algorithmCREATE FUNCTION dbo.fnInitRc4
(
@Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS
BEGIN
DECLARE@Key TABLE (i TINYINT, v TINYINT)
DECLARE@Index SMALLINT,
@PwdLen TINYINT
SELECT@Index = 0,
@PwdLen = LEN(@Pwd)
WHILE @Index <= 255
BEGIN
INSERT@Key
(
i,
v
)
VALUES(
@Index,
ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
)
INSERT@Box
(
i,
v
)
VALUES(
@Index,
@Index
)
SELECT@Index = @Index + 1
END
DECLARE@t TINYINT,
@b SMALLINT
SELECT@Index = 0,
@b = 0
WHILE @Index <= 255
BEGIN
SELECT@b = (@b + b.v + k.v) % 256
FROM@Box AS b
INNER JOIN@Key AS k ON k.i = b.i
WHEREb.i = @Index
SELECT@t = v
FROM@Box
WHEREi = @Index
UPDATEb1
SETb1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
FROM@Box b1
WHEREb1.i = @Index
UPDATE@Box
SETv = @t
WHEREi = @b
SELECT@Index = @Index + 1
END
RETURN
ENDANd this function does the encrypt/decrypt partCREATE FUNCTION dbo.fnEncDecRc4
(
@Pwd VARCHAR(256),
@Text VARCHAR(8000)
)
RETURNSVARCHAR(8000)
AS
BEGIN
DECLARE@Box TABLE (i TINYINT, v TINYINT)
INSERT@Box
(
i,
v
)
SELECTi,
v
FROMdbo.fnInitRc4(@Pwd)
DECLARE@Index SMALLINT,
@i SMALLINT,
@j SMALLINT,
@t TINYINT,
@k SMALLINT,
@CipherBy TINYINT,
@Cipher VARCHAR(8000)
SELECT@Index = 1,
@i = 0,
@j = 0,
@Cipher = ''
WHILE @Index <= DATALENGTH(@Text)
BEGIN
SELECT@i = (@i + 1) % 256
SELECT@j = (@j + b.v) % 256
FROM@Box b
WHEREb.i = @i
SELECT@t = v
FROM@Box
WHEREi = @i
UPDATEb
SETb.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
FROM@Box b
WHEREb.i = @i
UPDATE@Box
SETv = @t
WHEREi = @j
SELECT@k = v
FROM@Box
WHEREi = @i
SELECT@k = (@k + v) % 256
FROM@Box
WHEREi = @j
SELECT@k = v
FROM@Box
WHEREi = @k
SELECT@CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
@Cipher = @Cipher + CHAR(@CipherBy)
SELECT@Index = @Index +1
END
RETURN@Cipher
END
Peter Larsson
Helsingborg, Sweden
This is related to post :
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78552
got a issue with this one..im not sure why..
My results are as follows:
Select dbo.fnEncDecRc4('Orange12345', 'Hello123')
Output : ,M gl
Select dbo.fnEncDecRc4('Orange12345', ',Mgl')
Output : M
i am not able to decrypt it. Any idea why this is hapenning? Does it has to do something with regional settings?
Hi,
Does any body have a stored procedure or a function I can use? What I need is to encrypt and decrypt a password using Tiny Encryption Algorithm, SO I have an encryption scalar valued function or sproc and similarly decryption function or sproc.Now I need rolling keys to encrypt and decrypt, so I have a table which has keys used for encryption and decryption and depending on the dtae the keys are different.So I alos need a sproc to retrieve the keys.If anybody has done it before or can point me to where can I go let me know?
Thanks
Database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.
We got the code working for the seond case but for the first, WHEN:
CREATE SYMMETRIC KEY sym_Key WITH ALGORITHM =
AES_256 ENCRYPTION BY ASYMMETRIC KEY asym_Key
GO
THEN:
-- Msg 15314, Level 16, State 1, Line 1
-- Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.
What can be the way out to be able to create the AES 256 Symmetric key.
Below is my sample table and data
With Item as(
Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all
Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all
Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all
Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all
Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all
Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all
Select 7 as ItemId,'ItemName7' as ItemName,700 as position),
Mapping as (
Select 1 as Parent, 2 as child union all
Select 1 as Parent, 3 as child union all
Select 1 as Parent, 4 as child union all
Select 5 as Parent, 6 as child union all
Select 5 as Parent, 7 as child )Expected Result:
ParentItemIdParentItemNameParentpositionChildItemIdChildItemNameChildposition
1ItemName11002ItemName2200
1ItemName11003ItemName3300
1ItemName11004ItemName4400
5ItemName55006ItemName6600
5ItemName55007ItemName7700
I was thinking to achieve using union all but if i use union all it will combine the result in rows level. but i need in column level.
Hi,
When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.".
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.
On further trial and error I found that if I remove the fast load option, it works without glitch.
Its kind of wierd though....
Any body has faced this situation?
Thanks
Karunakaran
Hi,
I have developed a Smart Device Application using VB.NET 2003 on top of Windows Mobile 2003 platform which is running on Symbol PPT 8846, a model used in our construction sites for gathering data bar-coded on Pipes, trucks...etc. This application uses SQL CE as its back-end database and since its difficult to cover our sites with WI-FI coverage, most of the time the application is running offline. As for that, on a daily basis, synchronization is done in the offices before the system is released into sites. I have been doing several implementations in several projects and till now everything is running fine but until I got a native exception on exporting the data back into the server database. The server database or main database is using Fox Pro and this is what led me to build a Web Service for communication between SQL CE and Fox Pro. Anyways, going back to that mysterious exception, I did some of my intensive debug on what caused it. As a start, I went through the code line by line to check if there was a memory leak from P/Invokes; but I was shocked that the exception was raised from "SQLCEDataAdapter.Fill(Dataset)" function. First thought came to mind was to hard reset and to re-install the application but no luck. Since it was about SQLCE, I moved towards checking the data found in the SQLCE database and tried to de-fragment the data found under the table needed for export. What I mean by de-fragmenting is that I grouped the data under different classes or families and created different tables depending on these classes. Then tried exporting again and Bingo the data was exported except one record...That was so weird!! huh? (The database had 515 records)
So I went for the Query Analyzer to try to view that record but no hope, it was taking too long to view the record, I just left it for more than 15 minutes and still no response from the Analyzer...it was so strange since there was only one record in that table!!!?!!! As going further, I was so curious why this record causing that native and inability to be viewed by the Analyzer?? I did testing on the column-level of that record and found out that you can view all the columns except two : nvarchar(200) and nvarchar (20)....I was able to resolve this problem with an SQL statement to update both columns with empty or dummy values...!!!!!
But after this resolution, I really don't know how it could be controlled later on!! and why it happened??
I would be glad if anyone can give me any advise on why such problem popped out and especially on the column-level in SQL CE?? And how to overcome it?
Thanks for your time and effort,
Looking forward to hear from as soon as possible...
Just for Clarification:
.NET CF 1.0 SP3
SQLCE 2.0
were used
Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.
View 5 Replies View RelatedI am planning to use XP_CRYPT for encrypting and decrypting cc#'s, passwords etc., at database level. Any suggestions or experiences on this. More info about this product at
http://www.activecrypt.com/faq.htm
I found that while using encryption and decryption by keys and certificates thsere is no security at all.
if we uses master key the sysadmin can decrypt
but if we use private key (encryption by password), how do we pass the password so that profiller didn't show it?
Hi,
I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.
My question is as to what is the best way to do this?CLR integration or T-SQL?
Also I would appreciate any ideas as to how to go about using either approaches.
One of the steps of processing involved per column is as follows:-
1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0
2)find the invalid value depth of each column(no of successive invalid values from start)
3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value.
ie replace invalid value only if it has a valid value above it.
4)find the column with maximum invalue value depth and delete that many rows from the table.
Here's am example:-
Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc.
For simplicity colA and colB are ints.
RowID is keeping track of the row number.
suppose colA has the following data
RowID ColA
-----------
1 0
2 -5
3 -3
4 1
5 4
6 -9
7 5
8 8
Step1)successive invalid values from start=0,-5,-3
Step2)Invalid value depth=3(because there are 3 rows from step 1)
Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.
so colA after the algo will look as follows
RowID ColA
------------
1 0
2 -5
3 -3
4 1
5 4
6 4(replaced -9 with 4)
7 5
8 8
Now do the next column colB
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 -5
8 -8
Step1)successive invalid values from start=-6,0,0,-7
Step2)depth of invalid values=4
Step3)Next invalid value -5 occurs at RowID 7
and has a valid value 8 above it.
Replace -5 with previous valid vlaue ie 8.
RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8
Output at the end of these steps
RowID ColA
------------
1 -6
2 0
3 0
4 -7
5 4
6 8
7 8(replaced -5 with 8)
8 8(replaced -8 with 8)
Step4:Get the maximum invalid value depth
In this case ColB had depth=4 which is greater than ColA which had dept=3.so delete 4 rows from the beginning of the table
So the table will be
RowID colA colB
----------------------------------------
5 4 4
6 4(replaced -9 with 4) 8
7 5 8 (replaced -5 with 8)
8 8 8(replaced -8 with 8)
Thanks in advance for your help
coolaqua