How Can I Mask A Column In A Sql Server Database
Sep 26, 2007Hi i have a table in my database and i want to mask or encrypt a particular column in it? How can i do it.
Any help will be appreciated.
Regards
Karen
ADVERTISEMENT
How Can I Encrypt Or Mask A Column In The Database
Sep 26, 2007
How can i encrypt or mask a column in the database?
Any help will be appreciated
Regards
Karen
The Affinity Mask Specified Does Not Match The CPU Mask On This System.
Nov 17, 2007We are having a problem with one of our SQL servers, and in comparing it to the backup server which is working fine, I noticed some differences. I attempted to correct the differences, but no luck.
The dell server has 4 dual-core processors and at one point hyper-threading was enabled. One of our DBAs recommended that it be turned off. We didn't have any major problems until recently and it seems that getting this setting right is the lynchpin. Any suggestions?
John
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'affinity mask', N'0'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
-----------------------------------------
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity mask' does not exist, or it may be an advanced option.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity I/O mask' does not exist, or it may be an advanced option.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
SELECT - BETWEEN &<mask&> AND &<mask&>
May 10, 2004I have a table into which users can enter a To and From range, and an application returns all the values in between. Naturally the users can enter to and from values that don't exist, as all they need to do is satisfy a "between" expression. eg they can enter '1100' to '1399' and even though neither value actually exists in the data, the query will return all the values that fall into the desired range.
However the application now demands a bit more of a sophisticated approach. The target data may have the form 'nn-nnnn-nn' where n is '0' to '9', and the user wants to select all the values (in pseudo-code) which satisfy a pattern like '%-7040-%' and '%-7400-%'
A simple LIKE expression with a mask containing a RegEx won't work - ie LIKE '%-7[0-4][0-4]0-%' doesn't work because (eg) '00-7150-00' won't satisfy the mask because of the 5 falling outside [0-4], although it is within the range I want.
I have come up with a solution to this problem by using MIN and MAX aggregate functions on LIKE [From] and LIKE [To] respectively and this works fine. eg
"SELECT A.<something> from <target_table1> A, <table_with_ranges> B WHERE A.<something> BETWEEN (SELECT MIN(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[From]) AND (SELECT MAX(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[To])"
It's actually quite a bit more complicated than that - I have simplified the SQL a bit, but that is the basic algorithm.
The problem is that for the BETWEEN expression to work, both the MAX and MIN functions have to return a value that is not NULL- ie there has to be a value in the target table which satisfies the mask. In the application, this is not always going to be the case - Users will want to create large ranges to allow for future growth so every time they enter new data into target_table1 the don't then have to go out and redfine the [From] and [To] ranges to satisfy the new data. What I really need is an SQL expression along the lines of "SELECT .... FROM ... WHERE A BETWEEN <pattern1> AND <pattern2>" where <pattern1> and <pattern2> contain wildcards.
Anyone got any suggestions??? By the way, using SUBSTRING or other string functions is NOT an option for a variety of reasons.
What Happens If Affinity Mask And Affinity I/O Mask Are Not Mutually Exclusive?
Jan 21, 2008
I have a dual 64 bit quad core server with 16 GB of memory. We are going to run an application server and SQL Server 2005 SP2 CU4 64 bit on this hardware, but we only want to purchase a single CPU license for SQL Server. The obvious choice is to use the affinity settings to prevent SQL Server from using one of the CPUs.
Initially, the development team simply went into SSMS and unchecked affinity mask and affinity io mask for the first four processors. This appeared to work fine in their testing. A problem arose when we started monitoring the maintenance plan and saw that the database integrity check was failing. The root problem was this invalid state that the affinity masks were in.
I have seen a lot of documentation stating the SQL Server will ignore an invalid mask setting, but in our testing, it appears that SQL Server respects the setting. For example, when we set CPU 7 to be available for processing and IO, Performance Monitor showed that only CPU 7 was used during a load test.
So from our preliminary testing, it looks like SQL Server will use a single CPU for both processing and IO if you tell it to. Is there some other reason why these affinity settings need to be mutually exclusive? Is there a test I can run that can illustrate why?
Affinity Mask And Affinity I/O Mask Configuration
Sep 17, 2007I'm experiencing some problem with the affinity mask and affinity I/O mask configuration.
We are running SQL Server Enterprise Edition 2005 64bit on Windows 2003 Server 64bit with 4 dual core CPU. The actuelly configuration is:
Affinity Mask Affinity I/O Mask
CPU0 0 0
CPU1 0 0
CPU2 1 1
CPU3 1 1
CPU4 1 1
CPU5 1 1
CPU6 1 1
CPU7 1 1
We did it so, because we are running LiteSpeed Tool for the backup. LiteSpeed is configured to use CPU0 and CPU1.
Now, we know that the settings are wrong. If Affinity mask is 1 affinity I/O mask should be 0 and vice-versa.
My question is: how many CPU should I enable for affinity mask and how many for affinity I/O mask. Which are the correct criterias.
Thx in advence.
Laurent
Copy Database With Encrypted Column To New Server And Decrypt Column There
Aug 17, 2006To do this successfully do I need to backup the Service master, Database master, and database itself from the the Source server, then restore all three of them on the destination server?
(I'm concerned that restoring the source Service Master key to a new target server with an existing sql 2005 install will screw things up big time.)
TIA,
Barkingdog
SQL 2012 :: Config Value For Affinity Mask
Aug 7, 2014I ran ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3 that means the SQL server 2012 is using the 2nd and 3rd CPU's respectively..
Now, when I run sp_configure 'affinity mask' I get the config_value as 12, so how it is getting calculated?
is there a code for each CPU?? wanted to know about this.
IP Address And Subnet Mask Calculations
Nov 12, 2007-- Prepare sample data
DECLARE@Sample TABLE (ID INT, NetworkIP VARCHAR(15), SubnetMask VARCHAR(15))
INSERT@Sample
SELECT1, '192.168.1.0', '255.255.255.128' UNION ALL
SELECT2, '10.1.1.64', '255.255.255.240' UNION ALL
SELECT3, '172.16.11.0', '255.255.255.252'
-- Show the expected output
SELECTID,
NetworkIP,
SubnetMask,
dbo.fnIsInRangeIP('192.168.1.5', NetworkIP, SubnetMask) AS IsInRange
FROM@SampleAnd here is the function codeCREATE FUNCTION dbo.fnIsInRangeIP
(
@IP VARCHAR(15),
@NetIP VARCHAR(15),
@MaskIP VARCHAR(15)
)
RETURNS BIT
AS
BEGIN
RETURNCASE
WHENCAST(PARSENAME(@IP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT) = CAST(PARSENAME(@NetIP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT)
AND CAST(PARSENAME(@IP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT) = CAST(PARSENAME(@NetIP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT)
AND CAST(PARSENAME(@IP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT) = CAST(PARSENAME(@NetIP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT)
AND CAST(PARSENAME(@IP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT) = CAST(PARSENAME(@NetIP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT)
THEN1
ELSE0
END
ENDE 12°55'05.25"
N 56°04'39.16"
ForEach File - Filename Mask
Dec 20, 2007
Hello
I'm using the ForEach File task and masking the files I want to process as ABC??.TXT.
It all works well until I drop a file there like ABC12 Copy.TXT, which I don't expect to be process, but the loop still picks it up. Am I using the wrong wildcards?
newstar1860
Enter Data In A Mask Format
Aug 20, 2006sorry for my question, maybe it seems un professional but I need to know the answer,
is it possible to enter the data in a sql table in a specific format and how it could be?
for example I want to have a mask in the table to enter the data in this format ##.## so it will not accept any other data to be typed in without this format.
Regards
Stripping Input Mask From Phone Numbers
Mar 28, 2006Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
View 2 Replies View RelatedSyntax Problem With Mask Special Characters
Sep 29, 2006I have the following t-sql syntax
problem is masking information after like as special characters.
I will search for all entries in column filedirectory which begins with \
DECLARE @cmd varchar(255)
SET @cmd = 'SELECT id,name,filedirectory FROM MR_ReqPro.RQDOCUMENTS WHERE FILEDIRECTORY LIKE ' \%' '
print @cmd
EXEC master.dbo.xp_sendmail @recipients = 'kalleOO7@web.de',
@query = @cmd , @subject = 'Fehler', @message = 'Fehler :', @attach_results = 'TRUE', @width = 2000
Thx
Kalle
here is the solution
DECLARE @cmd varchar(255)
SET @cmd = 'SELECT id, name, filedirectory FROM MR_ReqPro.RQDOCUMENTS WHERE filedirectory LIKE ''\%'' '
print @cmd
seems to work :-)
Forcing Data Format Mask Without Modifting Code
Sep 24, 2004I have statement which is comparing a smalldatetime column to literal string as follows:
sales_date ='21-9-2004 0:0:0.000'
when I run the statement in query analyzer it bombs out with:
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.
Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?
How To Mask Password Text In SQL Field Data Type.
Mar 12, 2007AnupG writes "hi,
the most common feature that any RDBMS provides should be that if we want to store the data in the MSSQL tables then the data should be presented in masked/formatted like for eg.the password text in field should be like "*****". I am using SQL 2000 but i unofrtunately i couldn't find any such feature in defining the data type in SQL server 2000 ...?"
How To Get The SQL Affinity Mask Value In Sql 2005, And Table That Store This Info
Aug 29, 2006
Hi All
Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.
and what is the table/views name that store this info.
for SQL 2000 , we were using following query:
SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v,"
"master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 "
"and v.number = r.config and v.name ='affinity mask'");
lErrCode=SQLExecDirect(hStmt,(SQLCHAR*)pszSQLStatement,strlen(pszSQLStatement));
lSQLBindCol(nAffMask);
But With SQL 2005 , we are getting ZERO(0) ROWS SELECTED.
SQL Server 2008 :: Capture Database / Server Name In A Derived Column To Identify Source Of Data?
Feb 1, 2012I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
How To Put URL Column In Server Database And Table
Feb 13, 2015I have researched how to put a URL column in my SQl Server 2008 database and table. I have tried varchar(max) and varchar(1000) and then done what I found online by inserting into the url with ' ' around it and then I ODBC into Access and it does not look like a link. Cannot link on it and have it go where it needs to. I have also tried this in the front-end application and it does not look like a link. It does not respond either.
View 4 Replies View RelatedUnsorted Primary Key Column In SQL Server Database
Oct 25, 2007Update: Changed original post as I figured it was not the problem I stated.
Hi all,
Heres the problem. I have a table in SQL Server with two fields, PartSubCategory and PartCatID. PartSubCategory is the primary key. When I import an excel sheet with identical columns into this table, the primary key column in the table gets sorted automatically (alphabetically). I don't want this column sorted but to import the rows from excel in the order as they were. Is this some indexing issue? Is there a fix?
Thanks a lot...Bullpit
How To Change The Value Of A Column In Sql Server 2005 Database When A Button Is Clicked In Asp.net 2.0?
Nov 22, 2007hi all,
I have created a table in sql server 2005 as follows:
table name --> sampletest
1.id -->int ,identity,primary key
2. student_name --> varchar(50),not null
2. active --> bit, default value=1
Using vb.net 2005 , i entered names into sampletest. And when i check the table in the sql server 2005 the names are inserted and the active value is 1.
In another page i added a drop down list which bind all the student_name from sampletest and a delete button.
Now when i select a name and click the delete button that respective item's active column should be changed to 0. if any one who knows how to do this please help me..
thanks
swapna.
When i enter names into the student_name it's active state is "true".I do this programmatically using vb.net 2005.
SQL Server 2012 :: Count From Multiple Tables For Same Column Exists In Database?
May 19, 2014i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.
View 9 Replies View RelatedSQL Server Admin 2014 :: Recover Value Of Column In Database Hosted Online
Jun 3, 2015SQL server, by-mistake I updated values of a column in a database hosted online, is there any way undo the transaction. I didn't created any backup of the database. I read that still it can be recovered through the .ldf (log file) but unable to access it. Is there anyway to get access of the Log file or is there any way to recover the data.
View 1 Replies View RelatedDatabase Diagrams : Showing Relations Column To Column
Dec 19, 2005Hi to all,
As I am going to deal with a huge number of database tables, I thought that drawing their diagrams will be the most professional way of keeping track of what I am doing. So In Enterprise Manager using the Wizard I made it to be drawn nicely.
But I have a problem now. I see that the relations defined among the tables are represented correctly but the line connecting the two table is drawn randomly. I mean the starting point of the line doesnt start from the column having the primary key and doesnt end near to the column having the foreign key. Viewing my diagram I want to see the lines to start and end showing the 2 related key columns.
Is there a way to accomplish this like setting an option, clicking somewhere or should I try to drag the lines to manually?
Thanks in advance
Update Column Value In Whole Database (based On Column Value)?
Aug 27, 2015How to Update Column Value in the whole data base (based on Column Value)?
View 2 Replies View RelatedSQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?
Feb 26, 2015Is there a way to display a column alias as part of the result set column labels?
View 9 Replies View RelatedSQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table
May 26, 2014I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies View RelatedImporting Data From Oracle9i CLOB Column To MS SQL Server Text Column
Jul 20, 2005Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies View RelatedSQL Server 2012 :: Format Value Of A Column And Insert It Into Another Column Of The Same Table?
Sep 18, 2014A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?
Mar 11, 2015when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies View RelatedSQL Server 2008 :: Error - The Column Delimiter For Column Was Not Found
Mar 26, 2010I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
View 9 Replies View RelatedSQL Server 2012 :: Group Column Based On Another Column
Jul 11, 2014I have Table Like this
t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail
[Code] ....
SQL Server 2008 :: Storing Column Value To A Column
Sep 9, 2015I just have a question regarding storing values to a column in ms sql 2008.
Why is it that the value I inserted at the column is truncated when selected in a query.
The column for this is created to accept max. values.
-> Message VARCHAR(MAX) NULL
The string which I need to insert is a combination of characters with a length of 14,720.
According to some forums, the max value that a column can hold is 8000 chars. only (Is this true? even though I set it to MAX?)
SQL Server 2012 :: Update A Column Using Value Of Another Column
Sep 9, 2015I have a student table like this studentid, schoolID, previousschoolid, gradelevel.
I would like to load this table every day from student system.
During the year, the student could change schoolid, whenever there is a change, I would put current records schoolid to the previous schoolid column, and set the schoolid as the newschoolid from student system.
My question in my merge statement something like below
Merge into student st
using (select * from InputStudent ins)
on st.id=ins.studentid
When matched then update
set st.schoolid=ins.schoolid
, st.previouschoolid= case when (st.schoolid<>ins.schoolid) then st.schoolid
else st.previouschoolid
end
, st.grade_level=ins.grade_level
;
My question is since schoolid is et at the first line of set statement, will the second line still catch what is the previous schoolid?