Free Encryption
-
Hi Ed,
Hmm... I've resolved the memory fragmentation issue with the 0.9 version. I also can't reproduce your performance issue. I would suspect that the joins of millions of rows to multiple other tables with millions of rows might be starving your SQL Server for memory, but can't be sure without knowing your SQL Server configuration. Do you have anything more specific you can tell me that might be helpful in troubleshooting the problem?
Thanks,
Michael
-
Here is one of the the messages that the folks executing the updates are getting...
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The SQL Server error log shows:
CImageHelper::Init () Failed load of dbghelp.dll - Not enough storage is available to process this command.
Error: 18002, Severity: 20, State: 1.
External dump process return code 0x20002001.
The error information has been submitted to Watson error reporting.
Stack Signature for the dump is 0x633A25D9
CImageHelper::Init () Failed load of dbghelp.dll - Not enough storage is available to process this command.
CImageHelper::Init () Version-specific dbghelp.dll is not used
633A25D9 Module(UNKNOWN+00000000)
* Short Stack Dump
* -------------------------------------------------------------------------------
* *******************************************************************************
* SegSs: 00000023:
* Esp: 649CC5F8: 00000019 00000003 76B59F78 042F0488 76B59FA0 633A26C7
* EFlags: 00010206: 005C0053 00790073 00740073 006D0065 00320033 0063005C
* SegCs: 0000001B:
* Ebp: 00000001:
* Eip: 633A25D9: CA8BABF3 F303E183 10468DAA 0C468B50 5C8B5350 468D2424
* Edx: 00000019:
* Ecx: 00000006:
* Ebx: 76B59FA4: 00000018 00000FA0 00000000 00000000 00000000 000000A7
* Eax: 00000000:
* Esi: 76B59FA0: 76B59F78 00000018 00000FA0 00000000 00000000 00000000
* Edi: 00000000:
*
* MODULE BASE END SIZE
*
*
* ement */ -- BEGIN TRANS
* rd_number), @enc_key) -- COMMIT /* line_item_payment_arrang
* set credit_card_number = dbo.fsns_encrypt(convert(varchar, credit_ca
* g */ -- BEGIN TRANSACTION update ar_payment_import_log
* t_card_number), @enc_key) -- COMMIT /* ar_payment_import_lo
* set credit_card_number = dbo.fsns_encrypt(convert(varchar, credi
* ce_detail */ -- BEGIN TRANSACTION update ar_invoice_detail
.
.
.
Although the error message above refers to "Not enough memory", I don't believe this is a disk or RAM issue. I'm running this on a Dell server with 500GB disk, 4GB RAM, and 2 Dual core, hypertreaded CPUs which shows a 8 logical CPUs. I did find a reference on Google that extended stored procedures are prone to memory leaks.
I have been able to get a successful upgrade if I run the script up until the encryption. Then I run the encryption logic one table at a time, and then finish the remaining part of the script. I'm still worried that we'll encounter this problem during the DTS process when it's encrypting the CC information.
-
Here is another on SQL Server 2005:
I'm testing 8.9 on SQL Server 2005 to see if I still have problems with the convertion script 8.9.01. I received the following while encrypting ar_invoice_detail.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Event Viewer:
Exception happened when running extended stored procedure 'xp_aesencrypt' in the library 'xp_aesencrypt.dll'. SQL Server is terminating process 57. Exception type: Win32 exception; Exception code: 0xc0000005.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
EventType sql90exception, P1 sqlservr.exe, P2 2005.90.2047.0, P3 443fa399, P4 xp_aesencrypt.dll, P5 0.0.0.0, P6 42d6c59c, P7 0, P8 000023bb, P9 07bb23bb, P10 NIL.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
SQL Server Log:
2006-04-28 13:46:28.06 spid57 Using 'xp_aesencrypt.dll' version 'UNKNOWN' to execute extended stored procedure 'xp_aesencrypt'. This is an informational message only; no user action is required.
2006-04-28 14:17:24.84 spid57 CImageHelper::Init () Version-specific dbghelp.dll is not used
2006-04-28 14:17:24.86 spid57 CImageHelper::Init () Failed load of dbghelp.dll - Not enough storage is available to process this command.
2006-04-28 14:17:24.86 spid57 CImageHelper::Init () Version-specific dbghelp.dll is not used
2006-04-28 14:17:24.86 spid57 CImageHelper::Init () Failed load of dbghelp.dll - Not enough storage is available to process this command.
2006-04-28 14:17:24.86 spid57 ***Symbol load failed - Proceed to dump without any symbols
2006-04-28 14:17:24.86 spid57 ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0002.txt
2006-04-28 14:17:24.86 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-04-28 14:17:24.86 spid57 * *******************************************************************************
2006-04-28 14:17:24.86 spid57 *
2006-04-28 14:17:24.86 spid57 * BEGIN STACK DUMP:
2006-04-28 14:17:24.86 spid57 * 04/28/06 14:17:24 spid 57
2006-04-28 14:17:24.86 spid57 *
2006-04-28 14:17:24.86 spid57 *
2006-04-28 14:17:24.86 spid57 * Exception Address = 07BB23BB Module(UNKNOWN+00000000)
2006-04-28 14:17:24.86 spid57 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-04-28 14:17:24.86 spid57 * Access Violation occurred writing address 00000000
2006-04-28 14:17:24.86 spid57 * Input Buffer 424 bytes -
2006-04-28 14:17:24.86 spid57 * DECLARE @enc_key VARCHAR(32) SET @enc_key = dbo.fsns_get_e
2006-04-28 14:17:24.86 spid57 * nc_key(0) update ar_invoice_detail set credit_card_number
2006-04-28 14:17:24.86 spid57 * = dbo.fsns_encrypt(convert(varchar, credit_card_number), @enc_key)
2006-04-28 14:17:24.86 spid57 *
2006-04-28 14:17:24.86 spid57 *
2006-04-28 14:17:24.86 spid57 * MODULE BASE END SIZE
2006-04-28 14:17:24.86 spid57 *
2006-04-28 14:17:24.86 spid57 * Edi: 0000000D:
2006-04-28 14:17:24.86 spid57 * Esi: 00000000:
2006-04-28 14:17:24.86 spid57 * Eax: 07BC2668: B60864F0 1C9BADDE 48D5390D 15583458 001F0003 000801CC
2006-04-28 14:17:24.86 spid57 * Ebx: 0000009E:
2006-04-28 14:17:24.86 spid57 * Ecx: E4B29E2A:
2006-04-28 14:17:24.86 spid57 * Edx: 462DE3F3: E179266D 30202081 ADC5B31A 0D003048 C67D0100 00000007
2006-04-28 14:17:24.86 spid57 * Eip: 07BB23BB: 5C8B1E88 EBC11424 015E8810 14245C8B 8808EBC1 5C8A025E
2006-04-28 14:17:24.86 spid57 * Ebp: 3F0C8488: 3F0C8348 3F0C9FF0 3F0C8368 FFFFFFFF 00010038 00000000
2006-04-28 14:17:24.86 spid57 * SegCs: 0000001B:
2006-04-28 14:17:24.86 spid57 * EFlags: 00010202: 0057004F 005C0053 00790073 00740073 006D0065 00320033
2006-04-28 14:17:24.86 spid57 * Esp: 083DC5EC: 06674FD8 00000002 00000020 5FDA8BF5 462DE3F3 9E5F6FFD
2006-04-28 14:17:24.86 spid57 * SegSs: 00000023:
2006-04-28 14:17:24.86 spid57 * *******************************************************************************
2006-04-28 14:17:24.86 spid57 * -------------------------------------------------------------------------------
2006-04-28 14:17:24.86 spid57 * Short Stack Dump
2006-04-28 14:17:24.86 spid57 07BB23BB Module(UNKNOWN+00000000)
2006-04-28 14:17:24.86 spid57 CImageHelper::Init () Version-specific dbghelp.dll is not used
2006-04-28 14:17:24.86 spid57 CImageHelper::Init () Failed load of dbghelp.dll - Not enough storage is available to process this command.
2006-04-28 14:17:24.86 spid57 Stack Signature for the dump is 0x07BB23BB
2006-04-28 14:17:25.87 spid57 External dump process return code 0x20002001.
The error information has been submitted to Watson error reporting. 2006-04-28 14:17:25.87 spid57 Error: 18002, Severity: 20, State: 1.
2006-04-28 14:17:25.87 spid57 Exception happened when running extended stored procedure 'xp_aesencrypt' in the library 'xp_aesencrypt.dll'. SQL Server is terminating process 57. Exception type: Win32 exception; Exception code: 0xc0000005.
I think this may be related to a "memory leak" which occurs with extended stored procedures.
-
This is not designed to work on SQL 2005. SQL 2005 has built in encryption facilities that should be used instead of extended stored procedures. On SQL 2005, extended stored procedures have been deprecated.
-
Memory leaks can cause a problem with XP's, if they don't release memory correctly. The XP's here properly allocate and deallocate memory. Which service pack are you running? If you run the encryption on a single table at a time you don't encounter the out of memory problem? I don't have enough data available to test this, but I can create some and test it when I get home tonight. Can you send me the Query Execution Plan for the query that you're running? That might help me eliminate some possible issues.
Thanks
-
The script that does the actual encryption has the following code in it 18 times for each of the 18 tables that are being encrypted. The encryption key is executed only once at the beginning...
DECLARE @enc_key VARCHAR(32)
SET @enc_key = dbo.fsns_get_enc_key(0)
/* ar_cash_receipt */
BEGIN TRANSACTION
update ar_cash_receipt
set credit_card_number = dbo.fsns_encrypt(convert(varchar, credit_card_number), @enc_key)
GO
COMMIT
-
Just wondering, but what is dbo.fsns_encrypt?
I suspect this might be an issue with parallelism, which is why I asked which service pack you are on and also asked for a query execution plan. Try adding the MAXDOP 1 option to your UPDATE statements and see if you get a better result.
-
That is our local adaptation of your encryption function that calls your encryption function. We did it so that we could do data type conversions in our function and not have to change 5000 places in our applications.
-
OK, just want to make sure we're talking about the same thing here.
-
This problem is caused by a couple of possible things:
1. The DLL's for the XP's are not in the MSSQL/BINN directory. Make sure you've copied them into that directory.
2. The Open Data Services DLL and/or Visual C++ Runtime Library aren't on a default path for your SQL Server. The easiest fix for this issue is to copy these two DLL's to you MSSQL/BINN directory. This seems to be a recurring issue on SQL 2003 Server and SQL 2000 installations mostly. I've included both of the versions of these libraries that I compiled with in the newest download.
3. Less likely, but you might have to change the build scripts as outlined in the README files in the download. In particular, you might have to put the full path to the DLLs in the statements that install the extended stored procs. I ran into this issue on a SQL 2003 test install.
I've copied the opends60.dll and msvcr71.dll to every directory that I could find that had a BINN for it under SQL2K, I put them in c:\winnt, I put them in the directory with the xp dll's and I still get the error message :Cannot load the DLL C:\Microsoft SQL Server\MSSQL\Binn\xp_dirscan.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
At least one of the directories I copied the DLLs to a directory that is in the PATH and that didn't work either.
I'm only trying to run the xp_dirscan and this is what I am getting. I am running Windows 2000 Server with SP4 and SQL 2k with SP3a.
Ad maiorem Dei gloriam
-
OK, so let me make sure we're on the same page:
1. You copied the file xp_dirscan.dll to the MSSQL\BINN directory for the instance of SQL Server that you want to run it on. You seem to indicate that you have multiple instances installed on this machine. Is that correct?
2. You copied the two DLL's from the \Redist directory to the same MSSQL\BINN directory for the instance of SQL Server that you want to run it on?
3. You correctly modified the INSTALL.SQL script as specified in the INSTALL.SQL script file and in the CHM help file?
If all you want to run is xp_dirscan, then use these steps:
1. Copy xp_dirscan.dll to the correct MSSQL\BINN directory for the instance of SQL Server that you want to run it on.
2. In the MASTER database for the correct instance of SQL Server, right click on Extended Stored Procedures and choose New Extended Stored Procedure...
3. In the box that pops up, enter xp_dirscan in the top box (spelling and capitalization count!) In the bottom box, click on the ... button.
4. In the Locate Extended Stored Procedure dialog box that pops up, navigate to the correct MSSQL\BINN directory for the instance of SQL Server that you want to run it on.
5. Select xp_dirscan.dll and click OK.
6. Click OK again.
If you continue to receive Error 126, copy the two DLL files from the \REDIST directory to the same MSSQL\BINN directory that contains the xp_dirscan.dll file. No need to copy them to other directories.
If these steps don't solve the problem, I have a tool that can grab a more specific error message that we might try.
-
I followed the steps you had said (I only have one instance of SQL Server running) and I had uninstalled everything based on your uninstall script. I added the xp via EM and I get the same message. I verified that the opends60.dll and msvcr71.dll are in the same directory that I installed the xp to and still the same.
If you can let me try that tool to see what is going wrong, that would be great.
Bill
Ad maiorem Dei gloriam
-
OK, now that I think about it, xp_dirscan also relies on a few other libraries from the STL. It may be having a hard time locating those libraries.
I posted the utility for download at http://www.thesqlfaq.com/loadlib/loadlib.zip. It's called Loadlib.exe, and it's described here on Microsoft's website: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q151596
Here are the steps to take:
1. Extract and copy the loadlib.exe file to the MSSQL\BINN directory
2. Run it from the command line as follows:
loadlib xp_dirscan.dll xp_dirscan
It will tell you the exact name of the DLL file it's having trouble locating on your system path
3. Locate that DLL file
4. Either move a copy to MSSQL\BINN or add it's current location to the system path
5. Repeat the process until the utility comes back with this message: "xp_dirscan found and loaded successfully."
-
This worked great - it turns out I was missing MSVCP71.dll. Put that into the directory and bingo.
Now one last question
- Can I pipe the output of this XP to a table? Kind of like an insert into? I'm looking through BOL, but haven't found anything, and I swear I saw an example years ago for xp_cmdshell. But I'm not having any luck tracking it down.Thanks for the toolkit - I can see a lot of usefull tools here.
Bill
Ad maiorem Dei gloriam
-
Good to hear
You can use INSERT INTO to insert the results into a temporary or other table. The exact format and datatypes of the output table are described in the .CHM file. You can do something like this:CREATE TABLE #DirTemp
(
RowNum INT PRIMARY KEY,
Level INT,
IsDir CHAR(1),
FullPath VARCHAR(8000),
FileName VARCHAR(8000),
Size BIGINT,
FileCount INT
)
INSERT INTO #DirTemp
EXEC dbo.xp_dirscan 'C:\Program Files\Microsoft SQL Server', '*.DLL'
SELECT *
FROM #DirTemp
DROP TABLE #DirTemp
Viewing 15 posts - 76 through 90 (of 153 total)
You must be logged in to reply to this topic. Login to reply