I have a table having 1 column whose value have to update,the column is encrypted using symmetric encryption. Value in column is 0 in char form, I have to increment it, when retrieving it return 0 to increment i have to convert it to int,it becomes 48 after increment it is 49 after conversion to char it becomes 1. It is going right upto 9 after 10 it jumps to very large number on increment.
View 5 RepliesTo 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
Hi all,
I am trying to join 2 tables in my database on an encrypted column. I am using a symmetric encryption, but because of the IV, a string encrypted at 2 different times produces 2 different encrypted strings. Therefore, I can not merely join the 2 tables on the encrypted columns. Has anyone any insight on techniques for solving this problem? The most naïve way, would be to decrypt both tables, join them, and re-encrypt them again. But, the tables are quite large, and this would be very processor (and time) intensive.
Any help would be greatly appreciated. Thanks in advance for any suggestions.
Is it possible to do a wildcard search on an encrypted column?
Many thanks!
Dear All,
I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '??????' instead of showing the encrypted value that I sent with the insert statement.3
............................ Can anyone tell me how to get rid of this?
Thanks and regards,
Z Z.
Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.
How could I backup/restore master key, symmetric and asymmetric key created for my database?
Thank you
Hi...
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
Thank you.
Bal.
I 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?
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies View RelatedHello,
I want to update a field value with its default constraint value defined. how can i do that?
Suppose if it is a Date field, my default date value is 1/1/2000 and currently it contains 6/18/2007, i would like to know how can we update this field with default value.
Thanks for all your help,
General Problem
I have table in that created column is there
it has the data like
23
24
45
45
43
67
by mistake if i updated that column as 77
can we roolback to old date
Hi all,
I am using the odbc connector to get a local table. I want that table to get populated every night automatically. How can i do that? Also i want to check if any existing record is updated/changed in the dsn database. Any help is highly appreciated.
Thanks,
Jeff.
Hi Guys,
When iam trying to update the table with the field called "openstatus" is smallint . Iam trying to do following like this
update table1
set openstatus=2 where openstatus=NULL
But
iam getting error like this:
"Syntax error converting the varchar value 'NULL' to a column of data type smallint."
Could you please let me how to solve this proble. i really appreciate it.
Thanks in advance
Garir
In SQL SERVER if there is failure during updation of certain rows, what will be the state?
View 3 Replies View RelatedDear all,
i've one database in my server namely mydb1, and another database mydb2. same type of tables are there. in those tables, when ever developers are entering data, that should be automatically updated here also.
i think replication suits this.
daily maximum 200 transactions may occur in the mydb1.
if replication suits this, which replication can i use..and please guide me for the process....
and one more doubt...actually only 22 tables out 0f 1300 tabls will modify regularly... rest of those are not changed frequently...
Vinod
Even you learn 1%, Learn it with 100% confidence.
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
  1       |John            |Baker
  2       |Carl             |Lennon
  3       |Marion           |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
  1     |1/1/14    |12/30/14
  2     |1/1/15    |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeIDÂ sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeIDÂ Â AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeIDÂ Â AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeIDÂ Â AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
hi i cant update the values in the table from data base if i do so an error message will show as below
No row was updated .
The data in row 37 was not committed.
Error Source: Microsoft.VisualStudio.DataTool.
Error Message: The row values updated or deleated either do not make the row unique or they alter multiple rows(2 rows)
Hello all
i am trying to update the database with the DataSet (Updated from the DataGrid) but it is giving the error as
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
the code is as follows
public DataSet bindControlToGrid(DataSet dataSet)
{
SqlConnection objConn= null;
SqlCommand objComm = null;
try
{
objConn = new SqlConnection(@"User ID=sa;Password=sa;database=ExtraNet;server=EAGLESEAGLES");
SqlDataAdapter objAdapter=new SqlDataAdapter();
objAdapter.SelectCommand= new SqlCommand("SELECT sUserID,sUserName FROM tblUser WHERE dUserDeActivated IS NULL ORDER BY sUserName,nUsrSerialNo",objConn);
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(objAdapter);
objConn.Open();
DataSet objdataSet= new DataSet("ko");
string d = (dataSet.Tables[0].Rows[0].ItemArray.GetValue(0).ToString());
objAdapter.Fill(objdataSet);
objAdapter.Update(dataSet);
objAdapter.Dispose();
}
catch(Exception ex)
{
string s= ex.Message;
}
finally
{
//objComm.Dispose();
objConn.Close();
}
return dataSet;
}
kindly help
i have a table which have 64 columns.
Problem:
When i update a column through stored procedure like
Column1=400
then after update when i see that value which is 100 but that will 400
So how this problem occured.
I am updating a table in store procedure. I want to check whether the statement has successfully updated the table or not. i know in mySql i can handle it using ROW_COUNT() function. but how can i do it in MS-SQL?
View 2 Replies View RelatedDear Folks,
Is it possible to update sys.databases table?
i need to update one column in this table.........
but it is showing some error
Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.
how can i update this table?
Vinod
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. Â To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.Â
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1Â
€œI have a scenario where i am trying to insert 200,000 lac records & update 200,000 lac record in destination table using SISS Package (SQL SERVER 2005) but I am not able to neither update nor insert the records . while executing the package its not showing any error also . what could be the problem ? €œ
We have business logic in Package creation 1) Insert New records and 2) Update Existing Records using the follow Data flow diagram
For update we are using OLEDB command, for insert we are using OLEDB Destination.
We are using merge join for spliting record into insert and update.
My CTE is failing and I don't know why...Is there a Common Table Expression column name length restriction???
View 2 Replies View RelatedI have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1
I supposed to get last row asÂ
  cc  Zz  bab
Instead I am gettingÂ
 cc  Zz  rrrÂ
which is wrong
#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View RelatedIs there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text                           QueryPlan       Plan_handle Â
     Name     Value
select id,name,Address from person   <showPlznXML...  010101         Table       Person
select id,name,Address from person   <showPlznXML...  010101         column     id
select id,name,Address from person   <showPlznXML...  010101         Table       Person
[code]....
How I can calculate the 'SUM of 100' of EDSUM column for EDCOST column. Every EDCOST should have sum of 100 on the calculation of EDSUM. I just want to know which is the EDCOST which has <>sum of 100.
Create table #sum (ED numeric, EDCOST numeric, EDName char(6), EDSum numeric, EDCode char(2))
Insert into #sum values (121, 2000,'HLMO',98,'DT')
Insert into #sum values (122, 2000,'HLMT',2,'DT')
Insert into #sum values (123, 2001,'HLMO',100,'DT')
Insert into #sum values (124, 2002,'HLMD',97,'DT')
[Code] ...
Expeced Output:
ED EDCOST EDName EDSum EDCode
126Â 2003Â HLMRÂ Â 98Â DT
130Â 2005Â HLMRÂ Â 98Â DT
I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the original database:
create master key
encryption by password = 'testAppleA3';
create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));
select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and then symmetric key:
drop symmetric key sk_test
drop certificate test
drop master key
Then recreate the master key:
create master key
encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test
FROM FILE = 'c:storedcertsencryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
again but this does not resolve the issue.
Thx.
Hi,
I am trying to set up Transactional replication with immediate
updation.
The configuration is as follows:
1. Publisher is SQL server 2000 Enterprise Edition, and Distributor is
on the same server.
2. Publisher SQL server Standard Edition is installed on Windows 2003 with sp1.
3. Subscriber is an SQLServer 2000 Evaluation version on Windows
Professional 2000 with sp2.
When I set up the Publisher with transactional replication with
immediate updation only, the set up was successful.
I subscribed 2 instances of SQL server from Windows 2000 professional
on the local LAN in the same domain. The set up went fine.
When I update from the publisher end the updation is successfully
replicating to all the subscribers. But when I try to insert any
records in any one of the subscribers, I am getting the following
error:
[Microsoft] [ODBC SQL Server Driver ][SQL Server
][DBNETLIB][ConnectionOpen(Connect()).] SQL Server does not exists or
access denied.
I am beginner to this replication technology, but I have to set up in
my organisation. Please anybody help me in figuring out the actual
problem and resolving it.
Thanks in advance.
Prashant N M
create table #t
(
id int,
col1 decimal(18,2)
)
go
[Code] ...
-- I want to subtract @X and col1. But my variable @X must be reduced for each value in col1 for each next row until it reaches zero.
-- OUTPUT:
-- id col1 col2
--@X at starting point is 15000
-- 1 5000.00 0 --@X IS 10000 = 15000 - 5000(col1)
-- 2 1000.00 0 --@X IS 9000 = 10000 - 1000
-- 3 10000.00 1000.00 --@X IS 1000 = 9000 - 10000
-- 4 12000.00 12000.00
-- 5 300.00 300.00
-- 6 35000.00 35000.00
--in col2 i just put zero where col1 is substract from @X and continue for every subsequent order.
-- in 3 row value is 1000 becouse @X is that big (1000 left from col1)
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (
[code]....
I want to add spaces (like space - len(col)) to first column so that second column will be aligned when exported to email (text).
DECLARE @ColumnSpaces TABLE (
 Col_1 VARCHAR(50),
 Col_2 VARCHAR(50)
 )
INSERT INTO @ColumnSpaces VALUES ('AAA', '123')
INSERT INTO @ColumnSpaces VALUES ('AAAAAAAAAAAAAAA', '123')