SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Other Development Tools
 SQL classic asp website getting error '800a0e78'
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jasonf
Starting Member

7 Posts

Posted - 05/02/2012 :  13:13:59  Show Profile  Reply with Quote
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 R2

The 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.html

We 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

USA
5072 Posts

Posted - 05/02/2012 :  13:56:51  Show Profile  Visit russell's Homepage  Reply with Quote
Do you have database backups?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/02/2012 :  14:22:12  Show Profile  Visit russell's Homepage  Reply with Quote
Also, let's see the code at and around that line.
Go to Top of Page

jasonf
Starting Member

7 Posts

Posted - 05/02/2012 :  15:00:39  Show Profile  Reply with Quote
quote:
Originally posted by russell

Do you have database backups?


Yes
Go to Top of Page

jasonf
Starting Member

7 Posts

Posted - 05/02/2012 :  15:34:02  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 05/02/2012 :  15:59:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

jasonf
Starting Member

7 Posts

Posted - 05/02/2012 :  16:34:48  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 05/02/2012 :  16:39:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.aspx

DO 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.
Go to Top of Page

jasonf
Starting Member

7 Posts

Posted - 05/02/2012 :  17:07:17  Show Profile  Reply with Quote
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...
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 05/02/2012 :  17:15:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

jasonf
Starting Member

7 Posts

Posted - 05/02/2012 :  17:36:40  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 05/02/2012 :  17:45:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

jasonf
Starting Member

7 Posts

Posted - 05/07/2012 :  13:56:25  Show Profile  Reply with Quote
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.
Go to Top of Page

afrika
Flowing Fount of Yak Knowledge

Nigeria
2706 Posts

Posted - 05/09/2012 :  16:29:11  Show Profile  Reply with Quote
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 script

Reason why I say its badly written is that it would
1. Not scale
2. Be lost if the computer runs out of memory
3. IF for some reason IIS is restarted or the server is restarted

Basically 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
Go to Top of Page

LukeLaCourse
Starting Member

USA
1 Posts

Posted - 05/17/2012 :  17:18:43  Show Profile  Reply with Quote
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 AccLink
Processing 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 Activity
Processing 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 CCProc
Processing 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 Contract
ALTER 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 Corporate
ALTER 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 Country
Processing Dashboards
>> CREATING TABLE: Dashboards
ERR: Column, parameter, or variabl


Luke LaCourse
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 05/17/2012 :  17:41:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 05/17/2012 :  18:12:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.26 seconds. Powered By: Snitz Forums 2000