Problem restoring database with encrypted columns to different database or server with encrypted

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:\storedcerts\encryptiontestcert'

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.

I also saw a posting here where it referenced running the below first:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

However when i then try to open the key it stil gives an error:

An error occurred during decryption.

|||

So now I'm confused.

Where this did not work is when i created the intial database using SQL Server Express edition on a Win XP workstation and then was restoring to a database located on a SQL Server Standard edition on a Windows 2003 Server.

I just tried creating everything from scratch on one WIndows 2003 Server with SQL Server Standard edition and then restoring that database to a different Windows 2003 Server also with SQL Server Standard and this time it worked as long as i did the below first:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

My question (comes to reliabilty concerns) is why did it not work when i did:

Orig database SQLSvr Express on Win XP

Restore to SQL Svr Std on Win2003Svr

But did work when I did

Orig database SQL Svr Std on Win 2003Svr

Restore to different SQL Svr Std on different Win 2003 Svr

What would the reason for this be?

Thx.

|||

See this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177863&SiteID=1

There is an issue if you restore the database on a different time zone, but otherwise, the steps described in that thread are the ones that should be performed after restoring a database that has encrypted data. The time zone issue may also affect you if the times of the machines that you use are not in sync.

You should NOT drop and recreate the symmetric key, that will just create a different key and you'll have no way to decrypt the data in the database.

To figure out what's going on, I suggest the following:

1. Make sure this is not a time issue; check the times of the two machines. If there is a difference, wait for the amount of time before trying to decrypt.

2. If it's not a time issue, try protecting the symmetric key with a password as well as with a certificate. Try opening the key using both the password and the certificate. This should allow you to figure out if the problem is with the certificate or with the key. Also, please post the entire error message that you receive, including the error number and error state - the state may provide some additional information.

Thanks
Laurentiu

|||

Thanks. What you say makes sense and I'm going to try it again. Like I said, everything was fine when the both servers involved were Windows 2003 with SQL Svr Std.

But it didn't work when one was SQL Svr Express on WinXP. So that's the part I'm confused about.

But I will try it again in that scenario later today to see if it is reproducible.

The timezone is the same on all machines involved in these tests.

Thanks,
R-

|||

Have you been able to repro this issue. Have you tried using a password to isolate the problem?

Thanks
Laurentiu

sql

problem querying a TEXT field

I have a query that works, until I try to also grab one of the fields that
is set to a 'text' datatype. I can grab any field, and it works fine, but
once I try to grab the data from the 'text' field, I get the following
error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image
data types cannot be compared or sorted, except when using IS NULL or LIKE
operator.
If I add an ISNULL to this field, I still get the same error. I'm not
explicitely sorting by thie field either. I haven't found a specific
solution via google other than 'change your TEXT field to VARCHAR(7000)'
which doesn't seem like a proper solution.
-DarrelImpossible to say without seeing the query.
ML|||Are you using a DISTINCT or GROUP BY or something else that might require a
sort? Can you post the query?
HTH
Jerry
"darrel" <notreal@.hotmail.com> wrote in message
news:O8PYwpEyFHA.700@.TK2MSFTNGP11.phx.gbl...
>I have a query that works, until I try to also grab one of the fields that
> is set to a 'text' datatype. I can grab any field, and it works fine, but
> once I try to grab the data from the 'text' field, I get the following
> error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image
> data types cannot be compared or sorted, except when using IS NULL or LIKE
> operator.
> If I add an ISNULL to this field, I still get the same error. I'm not
> explicitely sorting by thie field either. I haven't found a specific
> solution via google other than 'change your TEXT field to VARCHAR(7000)'
> which doesn't seem like a proper solution.
> -Darrel
>
>sql

Problem propagating identity fields for multiple tables in SQLXML Bulkload

I am having a problem importing XML documents into SQL using SQLXML Bulkload with an annotated XSD schema. I have three, hierarchical tables in a SQL 2000 DB: tblBatches -> tblRecords -> tblDetails. The XML document I want to import does not contain identity fields. So I am using '.KeepIdentity = False' in my ActiveX VB Script, so SQL Server will generate the key fields for me. When I run the script, the message I get is 'No data was provided for column 'record_ID' on table 'tblDetails', and this column cannot contain NULL values.' So, basically, it's not inserting the matching foreign key generated for tblRecords into tblDetails.
I have been playing around with this for several days, and if I remove all reference to the third table in the hierarchy, tblDetails, from my XSD schema, the data imports and SQL successfully inserts the identity fields for me for the first two tables. If it will insert the identity fields for two tables, why is it having so much trouble doing it for three? I thought SQLXML 3.0 SP2 was supposed to fix the identity propagation problem. I am using SQLXML 3.0 SP2, Windows 2000, VS.NET 2002, and SQL Server 2000.
I am pasting my VB code, XSD schema, and XML document below. I will also attach copies of them, if that is easier.
Here is the VB code I am using to run the bulkload:
Dim BulkLoad As SQLXMLBulkLoad3
Try
'Create the new XML Bulk Load object
BulkLoad = New SQLXMLBulkLoad3()
'Set the connection string.
BulkLoad.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;User ID=mmsuser;Initial Catalog=MMS;Data Source=NDEWITT\NetSDK;"
'Log any errors.
BulkLoad.ErrorLogFile = "C:\TestSQLErrorLog.xml"
'Have it automatically add the identity fields.
BulkLoad.KeepIdentity = False
BulkLoad.CheckConstraints = True
BulkLoad.Transaction = False
'And perform the data import.
BulkLoad.Execute("C:\TestMMSSchemaNETxsd.xsd", _
"C:\TestXDRNoIdentity.xml")
'Clean up.
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
BulkLoad = Nothing
End Try
Here is my XSD schema:
<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msch="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BatchRec" parent="tblBatches" parent-key="batch_ID" child="tblRecords" child-key="batch_ID" />
<sql:relationship name="RecDetail" parent="tblRecords" parent-key="record_ID" child="tblDetails" child-key="record_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="statement_batch" sql:relation="tblBatches" sql:key-fields="batch_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="print_batch_key" type="xs:string" />
<xs:element name="print_batch_type_value" type="xs:string" />
<xs:element name="client_program_key" type="xs:string" />
<xs:element name="print_item_quantity" type="xs:int" />
<xs:element name="start_date" type="xs:date" />
<xs:element name="end_date" type="xs:date" />
<xs:element name="client_key" type="xs:string" />
<xs:element name="client_name" type="xs:string" />
<xs:element name="program_key" type="xs:string" />
<xs:element name="program_name" type="xs:string" />
<xs:element name="return_address_line_1" type="xs:string" />
<xs:element name="return_address_line_2" type="xs:string" />
<xs:element name="return_city" type="xs:string" />
<xs:element name="return_state" type="xs:string" />
<xs:element name="return_zip" type="xs:string" />
<xs:element name="miles_synonym" type="xs:string" />
<xs:element name="statement_synonym" type="xs:string" />
<xs:element name="member_id_synonym" type="xs:string" />
<xs:element name="member_synonym" type="xs:string" />
<xs:element name="redemption_http" type="xs:string" />
<xs:element name="redemption_phone" type="xs:string" />
<xs:element name="print_logo" type="xs:string" />
<xs:element name="run_date" type="xs:date" />
<xs:element name="custom_message" type="xs:string" />
<xs:element name="summary_text" type="xs:string" />
<xs:element name="format_type" type="xs:string" />
<xs:element name="envelope_type" type="xs:string" />
<xs:element name="paper_type" type="xs:string" />
<xs:element name="statement_record" sql:relation="tblRecords" sql:relationship="BatchRec" sql:key-fields="record_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="member_id" type="xs:string" />
<xs:element name="first_name" type="xs:string" />
<xs:element name="last_name" type="xs:string" />
<xs:element name="address_line_1" type="xs:string" />
<xs:element name="address_line_2" type="xs:string" />
<xs:element name="city" type="xs:string" />
<xs:element name="state" type="xs:string" />
<xs:element name="zip" type="xs:string" />
<xs:element name="country_id" type="xs:string" />
<xs:element name="employer" type="xs:string" />
<xs:element name="begin_balance" type="xs:int" />
<xs:element name="award_activity" type="xs:int" />
<xs:element name="redeem_activity" type="xs:int" />
<xs:element name="expired_miles" type="xs:int" />
<xs:element name="adjusted_miles" type="xs:int" />
<xs:element name="end_balance" type="xs:int" />
<xs:element name="unvested_balance" type="xs:int" />
<xs:element name="detail_record" sql:relation="tblDetails" sql:relationship="RecDetail" sql:key-fields="detail_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="effective_date" type="xs:date" />
<xs:element name="activity_type" type="xs:unsignedByte" />
<xs:element name="activity_description" type="xs:string" />
<xs:element name="activity_miles" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MMSSchema" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="statement_batch" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is a sample XML document I want to import:
<statement_batch>
<print_batch_key>609167</print_batch_key>
<print_batch_type_value>Member Statement</print_batch_type_value>
<client_program_key>857148</client_program_key>
<print_item_quantity>108</print_item_quantity>
<start_date>02-13-2004</start_date>
<end_date>03-04-2004</end_date>
<client_key>317229</client_key>
<client_name>CARLSON SYSTEMS</client_name>
<program_key>857143</program_key>
<program_name>Carlson Systems -The Green Mile</program_name>
<return_address_line_1>3475 Piedmont Rd. NE</return_address_line_1>
<return_address_line_2>Suite 300</return_address_line_2>
<return_city>Atlanta</return_city>
<return_state>GA</return_state>
<return_zip>30305</return_zip>
<miles_synonym>Points</miles_synonym>
<statement_synonym>Statement</statement_synonym>
<member_id_synonym>Participant ID</member_id_synonym>
<member_synonym>Participant</member_synonym>
<redemption_http>www.webrewards.com</redemption_http>
<redemption_phone>Merchandise- (877) 690-2080; Travel- (800) 210-8539</redemption_phone>
<print_logo>http://www.webrewards.com/carlson_sy...sonSystems.jpg</print_logo>
<run_date>03-04-2004</run_date>
<custom_message><![CDATA[
<BR>
<BR>]]></custom_message>
<summary_text>
</summary_text>
<format_type>STATEMENT_DETAIL_LOGO</format_type>
<envelope_type>PISTOL_STATEMENT</envelope_type>
<paper_type>STATEMENT_OFFSET_GRAY</paper_type>
<statement_record>
<member_id>859953</member_id>
<first_name>MICHAEL</first_name>
<last_name>STAPLES</last_name>
<address_line_1>4300 DELLWOOD LANE</address_line_1>
<address_line_2>
</address_line_2>
<city>MOUND</city>
<state>MN</state>
<zip>55364</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>313152</begin_balance>
<award_activity>21924</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>335076</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>176</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>265</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1344</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>235</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>3103</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>5435</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>154</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>66</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>500</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>597</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1014</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>756</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>56</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>490</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>212</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1875</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>256</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>240</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>202</activity_miles>
</detail_record>
</statement_record>
<statement_record>
<member_id>859954</member_id>
<first_name>M</first_name>
<last_name>LEGAULT</last_name>
<address_line_1>13156 BITTERSWEET ST NW</address_line_1>
<address_line_2>
</address_line_2>
<city>COON RAPIDS</city>
<state>MN</state>
<zip>55448</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>91188</begin_balance>
<award_activity>5569</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>96757</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>80</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>105</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>891</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1543</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
</statement_record>
</statement_batch>
Any help will be greatly appreciated. Thanks!I discovered your post at Midtown Computer Systems Enterprise web site about SQLXMLBulkLoad and identity columns
in 3 hierarchical tables. I have having similar problems as the ones you described in your post. Were you able to find
a solution? If so, can you share it here? Thanks.sql

Problem populating table with a cursor from VB GUI

Hi all
I have a store proc that that does various bits of data manipulation, but
the last part I have used a cursor to update 2 fields in a table. This sp i
s
called by a VB front end and then the contents of the table in question
displayed.
The problem is, if I run the sp directly (within VB's Server Explorer) these
2 fields are always fully populated. If however I call the sp from within
code in a GUI, after each call (which should fully populate the table based
on certain criteria) the 2 fields become less populated. What I mean by thi
s
is on the 1st call the 2 fields are 100% populated, with each subsequent cal
l
only populating maybe 50% of records, then 20%, until no records are
populated.
Would anyone have any ideas where I may look.
thanks"Chubbly Geezer" <ChubblyGeezer@.discussions.microsoft.com> wrote in message
news:1BE8A023-7631-49AA-85BC-E5FD5808EF00@.microsoft.com...
> Hi all
> I have a store proc that that does various bits of data manipulation, but
> the last part I have used a cursor to update 2 fields in a table. This sp
is
> called by a VB front end and then the contents of the table in question
> displayed.
> The problem is, if I run the sp directly (within VB's Server Explorer)
these
> 2 fields are always fully populated. If however I call the sp from within
> code in a GUI, after each call (which should fully populate the table
based
> on certain criteria) the 2 fields become less populated. What I mean by
this
> is on the 1st call the 2 fields are 100% populated, with each subsequent
call
> only populating maybe 50% of records, then 20%, until no records are
> populated.
> Would anyone have any ideas where I may look.
> thanks
If you post the code and any necessary DDL, we can give you a lot better
answer.
From the limited information you have provided, I would guess that your VB6
application may be timing out. Then again, without any idea of what is
really going on in the sproc, it's hard to say.
Rick Sawtell
MCT, MCSD, MCDBA

Problem inserting decimal value into SQL Server 2000

In my VS 2005 windows control I am inserting a record into a table using a proc.

One of the fields "Accuracy" should look like this 66.4, but when I isnert it from the proc itlooks like 66.0. If I bypass my proc and use an inser statement from SQL quey analyzer it look like it should 66.4.

What am I doing wrong in my proc...

CREATE PROCEDURE [dbo].[insMyLameProc]

@.PlayerName nvarchar(255),
@.Score int,
@.Rounds int,
@.Accuracy decimal,
@.CorrectPicks int,
@.IncorrectPicks int

AS

--Insert the new game score
--===============================================================================================
insert into wmTurnTileScores
(PlayerName, Score, Rounds, Accuracy, CorrectPicks, IncorrectPicks)

values (@.PlayerName, @.Score, @.Rounds, @.Accuracy, @.CorrectPicks, @.IncorrectPicks)
--===============================================================================================
GO

@.Accuracy decimal(18,2)

--The 2 gives it 2 decimal places.

|||

rpack79:

@.Accuracy decimal(18,2)

--The 2 gives it 2 decimal places.

This is right, but as a tip: don't use the 18 if you are sure you will not need it. (faster query + save space).

Good luck.

|||

Yup, that worked, thanks

Problem inserting calculated data using sqldatasource, PLEASE HELP....!

Hi, I have created a website and I have a web form, I need to capture data and calculate fields and then insert the calculated field including the data into a sqlserver database. I created a store procedure and the parameters for the txt boxes is working fine. But the calculated fieldds are not getting inserted. Can someone please help? the code for insert is below: For the calculated field, I used txtboxes which "hold" the calculated field in the submit button event. I also tried using query string field

like this: but that did not work either. Help will be very much appreciated.

<asp:QueryStringParameterDefaultValue="1"Name="totlabchg"QueryStringField="laborcharge"Type="Decimal"/>__________________<InsertParameters><asp:FormParameterDefaultValue="0"FormField="txtaccount"Name="ActNumber"Type="String"/><asp:FormParameterDefaultValue="0"FormField="txtmeter"Name="MeterNumber"Type="String"/><asp:FormParameterDefaultValue="0"FormField="txtdov"Name="VisitDate"Type="DateTime"/><asp:FormParameterDefaultValue=""FormField="txtsvadd"Name="SvAdd"Type="String"/><asp:ParameterName="TampId2"Type="Int32"/><asp:ParameterDefaultValue="64.44"Name="charge1"Type="Decimal"/><asp:FormParameterFormField="txtq1"Name="quantity1"Type="Int32"/><asp:QueryStringParameterDefaultValue="1"Name="subtotal1"QueryStringField="sub1"Type="Decimal"/><asp:FormParameterDefaultValue="1"FormField="txtpeople"Name="labor"Type="Int32"/><asp:FormParameterDefaultValue="1"FormField="txthrs"Name="laborhrs"Type="Decimal"/><asp:FormParameterDefaultValue="1"FormField="txttotlab"Name="totlabchg"Type="Decimal"/><asp:ParameterDefaultValue="300.00"Name="flatrate"Type="Decimal"/><asp:FormParameterDefaultValue="1"FormField="txttotalcharge"Name="totalcharge"Type="Decimal"/><asp:FormParameterDefaultValue="1"FormField="txtgrandtotal"Name="grandtotal"Type="Decimal"/><asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"/></InsertParameters>

Thanks.

First, I have no idea what you're doing, so I'm working a little blind. Can you put a breakpoint in your app to see whether or not the calculated field is populated before the insert? You might have to put code into an event to see what's going on. Second, if you're using a stored procedure to do the insert, and it has the values that you're doing the calculation on, why not have it generate the calculated field? That might be easier in the short term.