| Author |
Topic |
|
jasonf
Starting Member
7 Posts |
 Posted - 2012-05-02 : 13:13:59
|
| I am not an SQL expert. We are willing to pay someone to fix this issue because we are no longer able to get any support from the developer. Our server is running MS SQL Server 2008 R2The server crashed while our shopping cart was performing maintenance on our SQL DB and when the server came back up we started getting this error on all of the pages on our website below the header:"DataEngine.Find" error '800a0e78' Operation is not allowed when the object is closed. DataEngine.Find/DIRECTIVES /Shopping.asp, line 597 An example of the error can be seen here: http://50.63.56.44/security-alarms.htmlWe do not think the problem is in the Shopping.asp page code as we have tried to replace Shopping.asp with several older versions of that page that we had saved as working backups and we continue to get the same error (although a different line number).I couldn't find much when i searched online for a solution.Again we are willing to pay for someone to fix this so if anyone thinks they can figure this out let me know by PM or email.Thanks,Jason F. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-05-02 : 13:56:51
|
| Do you have database backups? |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-05-02 : 14:22:12
|
| Also, let's see the code at and around that line. |
|
|
|
jasonf
Starting Member
7 Posts |
Posted - 2012-05-02 : 15:00:39
|
quote:
Originally posted by russell
Do you have database backups?
Yes |
|
|
|
jasonf
Starting Member
7 Posts |
Posted - 2012-05-02 : 15:34:02
|
quote:
Originally posted by russell
Also, let's see the code at and around that line.
Line 597 from the error should be the one in bold blue below: If Request.QueryString("Error") <> "" Then INCLUDE "include\error.asp" Else Dim cURLPanel Dim cURLTop Dim cURLBottom cDirective = Request.QueryString("Directive") cSubDirective = Request.QueryString("SubDirective") cURLContent = "" cPageContent = "" If cDirective <> "" Then cMainSQL = "SITECODE = 'Shopping' AND DIRECTIVE = '" + cDirective + "'" cSubSQL = "" lDirectiveFound = False If cSubDirective <> "" Then cSubSQL = cMainSQL + " AND SUBDIRECTIVE = '" + cSubDirective + "'" End If If cSubSQL <> "" Then If Not Session("oDE").Find("DIRECTIVES", CStr(cSubSQL)) Then lDirectiveFound = Session("oDE").Find("DIRECTIVES",cStr(cMainSQL) + " AND SUBDIRECTIVE=''") Else lDirectiveFound = True End If Else DirectiveFound = Session("oDE").Find("DIRECTIVES", CStr(cMainSQL) + " AND SUBDIRECTIVE=''") End If If lDirectiveFound Then cURLContent = Trim(Session("oDE").FieldValue("DIRECTIVES","URLBOTTOM")) Else cMainSQL = "SITECODE = '" + Session("oDE").FieldValue("ECOMMERCE", "SITECODE") + "' AND HOMEBASE=0 AND DIRECTIVE = '" + cDirective + "'" If Session("oDE").Find("ECOMMERCE", CStr(cMainSQL)) Then cPageContent = Session("oDE").Fieldvalue("ECOMMERCE","HTMLCenter") Session("oDE").Find "ECOMMERCE", "HOMEBASE=1" End If End If End If |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-02 : 15:59:57
|
| Looks like the Session("oDE") variable is an object of DataEngine type, whatever that is. Probably a custom object. If I had to guess, it's trying to access your database and it can't.Can you check the database via Management Studio or some other method and see if it's online? i.e. you can access data from it. Check if it's in Suspect status or just otherwise unavailable. |
|
|
|
jasonf
Starting Member
7 Posts |
Posted - 2012-05-02 : 16:34:48
|
quote:
Originally posted by robvolk
Looks like the Session("oDE") variable is an object of DataEngine type, whatever that is. Probably a custom object. If I had to guess, it's trying to access your database and it can't.Can you check the database via Management Studio or some other method and see if it's online? i.e. you can access data from it. Check if it's in Suspect status or just otherwise unavailable.
I rebooted the server and from management studio i have tried to connect/disconnect, start/stop, restart, take offline/bring online. I just checked and the DB is showing connected, started, online.Also I can see what appears to be the DB working in the activity monitor in management studio when I bring up the main homepage at http://50.63.56.44 and I believe the homepage is working with the DB because the featured items near the bottom of the page are dynamic and they are created on the fly from the DB. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-02 : 16:39:14
|
| My next suggestion is to run DBCC CHECKDB on the database in question, you can find more information here:http://msdn.microsoft.com/en-us/library/ms176064.aspxDO NOT run it with any repair options specified, just see if it returns any messages. It's possible that the table needed by that one page is corrupted.Note that this will cause a performance drop on your SQL server while it runs, so if you can't do it now, run it during a relatively quiet time for the server. |
|
|
|
jasonf
Starting Member
7 Posts |
Posted - 2012-05-02 : 17:07:17
|
| After running DBCC CHECKDB it gives a long list with this format: DBCC results for "(object name)"There are "x" rows in "x" pages for object "(object name)"No errors displayed, just how many rows and pages it found for each object.At the end of the list it says:CHECKDB forund 0 allocation errors and 0 consistency errors in the database... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-02 : 17:15:02
|
| OK, that's good. I'd recommend taking a fresh database backup at this point and keeping it safe.Do you know which table(s) this shopping page is trying to access? Can you check that they exist and have data in them?If not, do you have enough space to restore the most recent backup from before the problem started? You'll want to try comparing their database schemas to see if any objects changed. |
|
|
|
jasonf
Starting Member
7 Posts |
Posted - 2012-05-02 : 17:36:40
|
| I have made a recent backup already after the issue started so I think I'm ok there. The only other backup I have is much older. I cant tell how old it is. The file created date shows the date we moved to this server on 11/23/11, but it's probably much older. I'm not sure if anything major has changed since it was created. I tried to restore it once already. It appeared to restore it ok but the website did not work at all afterward so I restored it to the most recent backup again.I am not familar with schemas and I'm way past my comfort zone on most of this stuff already. I do have someone setup to look at it tonight so I'll see about having them restore the old one again. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-02 : 17:45:30
|
| You'd want to restore the old database to a separate database, not over the production copy, so you'd have 2 databases to compare.You can use RedGate's SQL Compare tool, available here: http://www.red-gate.com/products/sql-development/sql-compare/You'd compare the two databases to see if any tables got dropped or modified in the production database since the older backup was made. That might help pinpoint the problem. |
|
|
|
jasonf
Starting Member
7 Posts |
Posted - 2012-05-07 : 13:56:25
|
| Robvolk - thanks for your help, but I'm just not comfortable doing anything else myself on this. As of today I can't get ahold of the guy that said he was going to try to fix our issue so if you think you can fix it or if anyone else on here thinks they can fix it or knows anyone that could please get in touch with me. Again we are willing to pay someone. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2012-05-09 : 16:29:11
|
| I dont think this has to do with the error with the database, I simply think the code is not complete.The above code in my opinion is badly written for an enterprise system, as the connection string is encapsulated in a session variable called Session("oDE") Its classic ASP code written with VB scriptReason why I say its badly written is that it would 1. Not scale2. Be lost if the computer runs out of memory3. IF for some reason IIS is restarted or the server is restartedBasically it concatenates the sql command on the fly based on variable conditions and tries to access the database, and if for some reason its not complete it would throw the exception you are getting.... as the database in question is closed.Hope this helps.Ehi |
|
|
|
LukeLaCourse
Starting Member
1 Post |
Posted - 2012-05-17 : 17:18:43
|
| The error stems from a table update utility that is built into the system, it is referred to as Validate Data for the menu option however it reads the datadictionary in the /data/ folder where the asp sessions are also created. The following is the log from the update which is what initially created the issue.For now I also found that the SQL User had only "public" checked which is not correct and has been fixed. I too think it is a security issue however from the log info below I see that there are most likely database issues too, these are being rolled back and then will update again.If anyone knows though what the error "Err:Column, parameter, or variable #xxx:cannot find data type single" means or how to fix it that might help too, I cannot find anything online that looks like it truly relates here.Processing AccLinkProcessing Account ALTER TABLE [Account] ALTER COLUMN [budgetamt] Single ERR:Column, parameter, or variable #178: Cannot find data type Single.ALTER TABLE [Account] ALTER COLUMN [lastpaymnt] Single ERR:Column, parameter, or variable #178: Cannot find data type Single.ALTER TABLE [Account] ALTER COLUMN [discount] Single ERR:Column, parameter, or variable #178: Cannot find data type Single.ALTER TABLE [Account] ALTER COLUMN [ColorCode] Single ERR:Column, parameter, or variable #178: Cannot find data type Single.ALTER TABLE [Account] ALTER COLUMN [Response] Single ERR:Column, parameter, or variable #178: Cannot find data type Single.Processing ActivityProcessing ApptMask Processing ApptMast Processing Campaign ALTER TABLE [Campaign] ALTER COLUMN [Discount] Single ERR:Column, parameter, or variable #50: Cannot find data type Single.ALTER TABLE [Campaign] ALTER COLUMN [Hits] Single ERR:Column, parameter, or variable #50: Cannot find data type Single.ALTER TABLE [Campaign] ALTER COLUMN [OptOuts] Single ERR:Column, parameter, or variable #50: Cannot find data type Single.ALTER TABLE [Campaign] ALTER COLUMN [Invitations] Single ERR:Column, parameter, or variable #50: Cannot find data type Single.ALTER TABLE [Campaign] ALTER COLUMN [TriggerType] Single ERR:Column, parameter, or variable #50: Cannot find data type Single.ALTER TABLE [Campaign] ALTER COLUMN [TriggerTimeMode] Single ERR:Column, parameter, or variable #50: Cannot find data type Single.Processing CCProcProcessing Charges ALTER TABLE [Charges] ALTER COLUMN [BoxID] Single ERR:Column, parameter, or variable #97: Cannot find data type Single.Processing Codes Processing ComLog Processing Config ..... Inserted Value: USER_ID = 'CODES-ACCOUNTS' AND TYPE = 'Industry Type Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-ACCOUNTS' AND TYPE = 'Customer Product Status' into table Config ..... Inserted Value: USER_ID = 'CODES-CONTRACTS' AND TYPE = 'Service Contract Types' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'Inventory Part Status' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'Inventory Transfer Status' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'OEM Manufacturer Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-KNOWLEDGE' AND TYPE = 'Knowledge Base Categories' into table Config ..... Inserted Value: USER_ID = 'CODES-LEADS' AND TYPE = 'Lead Categories' into table Config ..... Inserted Value: USER_ID = 'CODES-MARKETING' AND TYPE = 'Promotion Types' into table Config ..... Inserted Value: USER_ID = 'CODES-ACCOUNTS' AND TYPE = 'Company Categories' into table Config ..... Inserted Value: USER_ID = 'CODES-PURCHASING' AND TYPE = 'Purchase Order Status' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Shipping Methods' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Sales Territory Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Pay Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Quotation Status Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-PIPELINE' AND TYPE = 'Pipeline Status' into table Config ..... Inserted Value: USER_ID = 'CODES-ACCOUNTS' AND TYPE = 'Product Categories' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Inventory Group' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Opportunity Pipeline Role' into table Config ..... Inserted Value: USER_ID = 'CODES-PIPELINE' AND TYPE = 'Competitor List' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Quotation Configurator Types' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Expense Item Type' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'FOB Shipping Option' into table Config ..... Inserted Value: USER_ID = 'CODES-QUOTES' AND TYPE = 'Quotation Inventory Groups' into table Config ..... Inserted Value: USER_ID = 'CODES-RESOURCES' AND TYPE = 'Department Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-RESOURCES' AND TYPE = 'Employee Groups' into table Config ..... Inserted Value: USER_ID = 'CODES-SCHEDULING' AND TYPE = 'Appointment Category Types' into table Config ..... Inserted Value: USER_ID = 'CODES-SERVICE' AND TYPE = 'Place Of Service Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-SERVICE' AND TYPE = 'Support Ticket Cause' into table Config ..... Inserted Value: USER_ID = 'CODES-SERVICE' AND TYPE = 'Service Rate Schedules' into table Config ..... Inserted Value: USER_ID = 'CODES-SERVICE' AND TYPE = 'Service Ticket Status' into table Config ..... Inserted Value: USER_ID = 'CODES-SERVICE' AND TYPE = 'Business Size Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-CONTRACTS' AND TYPE = 'Service Contract Cancellation' into table Config ..... Inserted Value: USER_ID = 'CODES-LEADS' AND TYPE = 'Reseller Referrals Code' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'Inventory Bin Segment 1 of 6' into table Config ..... Inserted Value: USER_ID = 'CODES-RESOURCES' AND TYPE = 'Resource Title' into table Config ..... Inserted Value: USER_ID = 'CODES-PIPELINE' AND TYPE = 'Pipeline Phase/Next Step' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'Inventory Bin Segment 2 of 6' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'Inventory Bin Segment 3 of 6' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'Inventory Bin Segment 4 of 6' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'Inventory Bin Segment 5 of 6' into table Config ..... Inserted Value: USER_ID = 'CODES-INVENTORY' AND TYPE = 'Inventory Bin Segment 6 of 6' into table Config ..... Inserted Value: USER_ID = 'CODES-ACCOUNTS' AND TYPE = 'Recall/Follow-Up Codes' into table Config ..... Inserted Value: USER_ID = 'CODES-ACCOUNTS' AND TYPE = 'Communication Types' into table Config ..... Inserted Value: USER_ID = 'CODES-ACCOUNTS' AND TYPE = 'Contact Classification Type' into table Config Processing Contacts Processing ContractALTER TABLE [Contract] ALTER COLUMN [PriceMode] Single ERR:Column, parameter, or variable #124: Cannot find data type Single.ALTER TABLE [Contract] ALTER COLUMN [Rollover_P] Single ERR:Column, parameter, or variable #124: Cannot find data type Single.ALTER TABLE [Contract] ALTER COLUMN [Rollover_L] Single ERR:Column, parameter, or variable #124: Cannot find data type Single.ALTER TABLE [Contract] ALTER COLUMN [Rollover_E] Single ERR:Column, parameter, or variable #124: Cannot find data type Single.ALTER TABLE [Contract] ALTER COLUMN [Rollover_T] Single ERR:Column, parameter, or variable #124: Cannot find data type Single.Processing CorporateALTER TABLE [Corporate] ALTER COLUMN [budgetamt] Single ERR:Column, parameter, or variable #96: Cannot find data type Single.ALTER TABLE [Corporate] ALTER COLUMN [lastpaymnt] Single ERR:Column, parameter, or variable #96: Cannot find data type Single.ALTER TABLE [Corporate] ALTER COLUMN [discount] Single ERR:Column, parameter, or variable #96: Cannot find data type Single.ALTER TABLE [Corporate] ALTER COLUMN [ColorCode] Single ERR:Column, parameter, or variable #96: Cannot find data type Single.ALTER TABLE [Corporate] ALTER COLUMN [Response] Single ERR:Column, parameter, or variable #96: Cannot find data type Single.ALTER TABLE [Corporate] ALTER COLUMN [CreditLimit] Single ERR:Column, parameter, or variable #96: Cannot find data type Single.Processing CountryProcessing Dashboards>> CREATING TABLE: DashboardsERR: Column, parameter, or variablLuke LaCourse |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-17 : 17:41:21
|
| Not sure why it's called a table update module, it's actually altering the table structure.A Visual Basic/VBA/VBScript Single will map to a SQL Server real, although real doesn't include the full range of values. In that case float may be used.If any of these columns are holding monetary values they should not use real or float (or Single for that matter), they should map to Decimal or possibly money in SQL Server. If the data dictionary can be modified I'd recommend making the change there. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-05-17 : 18:12:27
|
A quick fix would be to create a custom data type named Single and have it based on SQL Server REAL datatype.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
SQL classic asp website getting error '800a0e78'