SQL classic asp website getting error '800a0e78'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

SQL classic asp website getting error '800a0e78'

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 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

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

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 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

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 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

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources