Database

Causes of Urecognized Database Format

6-Aug-12 1:18

asimptota777 wrote:
I must admit that your signature really suits you...

Thank you - it was hard to earn that title Big Grin | :-D

You could repost the question with a link to this thread. State in the new post that this wasn't helpful, and someone might come up with something better.

asimptota777 wrote:
<layer>Dude, I know what full text search is and how it functions. I just don't know how to implement the functionality I've mentioned.

I did not look at the requirements; technically, you want to retrieve documents based on a searchterm. You get a list of documents that contain that term. You can retrieve the document.

What's keeping you from doing a substring on that document and parse out the line? You can fetch the index where the word is in the document, then you work your way back to the first word with a capital letter, and forward to the first interpunction.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

ASPnoob2-Aug-12 23:25

ASPnoob

2-Aug-12 23:25

Hi all,
Every time my code tries to connect to my 2007 Access database, the message Unrecognized Database Format pops up. I've read that it is usually due to a corrupt database file or opening an old Access database file in newer version of Access Database. I can rule out opening an old file in newer Access because I've created and run the file in Access 2007. I can reasonably assume that since I can open the file, it is not corrupt. If those two aren't the causes what else could be causing it? The connection string in my web.config file takes the following form:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

I don't know if that has anything to do with it, any suggestion will be greatly appreciated, thanks for your time.

Mycroft Holmes2-Aug-12 23:34

Mycroft Holmes

2-Aug-12 23:34

This information is REALLY old, there used to be an option to compile and compress an Access database. If this option is still available I suggest you use that. Being able to open the database does not mean it is not corrupted.

Never underestimate the power of human stupidity
RAH

Eddy Vluggen2-Aug-12 23:40

2-Aug-12 23:40

ASPnoob wrote:
I can reasonably assume that since I can open the file, it is not corrupt.

Wrong assumption. The database is corrupt; usually when Access terminates within a write - it can not ammend the database with random bites when it comes back online, so, you get the next best thing; you get to see the database, as Access thinks that the underlaying data should be.

So, install Sql Server Express and start upsizing all the tables that are still within the database, and have the Wizard migrate the data with it.

Did your database allow access to multiple users simultaneous?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

ASPnoob3-Aug-12 0:13

ASPnoob

3-Aug-12 0:13

Thank you, everyone for responding. My database was created less than 10 minutes prior to testing it in my code. It only has one table with one field in the table. I just cannot understand how it could be corrupted. I will give everyone's suggestion a try, thanks again for your reply.

Eddy Vluggen3-Aug-12 0:21

3-Aug-12 0:21

Did you use Microsoft Access in an environment where multiple users would manipulate data at the same time? Is your database on some network-share?

Microsoft Access is a damn good tool to manipulate single-user local-file databases with strong reporting capabilities.

It's not built to be a sharing-facility for data. If that's what you need, you'll need to switch to a real server - otherwise crap like this will happen again.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

On error resume next

vanikanc2-Aug-12 4:44

2-Aug-12 4:44

If we are selecting a set of data, and one of the fields is erroring out, with an arithematic over flow error, how to move this row aside and continue processing the remaining rows so that the entire stored procedure does not fail?

More like move this row causing the issue into a table and continue processing remaining rows?

Is there like on error resume next?

I tried a try catch block.

With in my try I had a select statement with a good value and one with a bad value, I wanted the select to print the good and not the bad one.

======================

declare @dummy int
begin try
select
--right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
select
--right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)
end try

begin catch
set @dummy = 1
end catch

print @dummy

modified 2-Aug-12 11:06am.

Eddy Vluggen2-Aug-12 9:46

2-Aug-12 9:46

try a bigint, in place of the int.. Care to explain why you are rounding the 50000, and what the casting is all about?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

vanikanc2-Aug-12 9:50

2-Aug-12 9:50

To give a bit more history, we are creating this view to feed into another application so it has to be set length.

Suppose this view returns 100 rows, and I am having an arthimetic overflow in on row 56 only. I want to leave 56 and continue from 57. WhenI tried the try catch block, while it catches the error, but on once it hits 56, then the whole thing stops.

Eddy Vluggen2-Aug-12 10:02

2-Aug-12 10:02

vanikanc wrote:
To give a bit more history

Did you try what I suggested? Y/N?

vanikanc wrote:
I want to leave 56 and continue from 57.

Filter the row out before casting it into infinity, make the result-variable bigger, or try padding it with zeroes after the cast.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

vanikanc2-Aug-12 10:14

2-Aug-12 10:14

I tried with bigint - still errored out.

Eddy Vluggen2-Aug-12 12:33

2-Aug-12 12:33

vanikanc wrote:
I tried with bigint - still errored out.

Was worth a shot.

SQL

right('00000000' + cast(cast(round(500000,0)*10000 as int) as varchar(8)),8)

Where is the @dummy used here? 500 000 * 10 000 = 5 000 000 000. That's bigger than an int, and probably wider than eight characters. Some suggestions;

Lose the round function
Don't multiply by 10k, pad it with 4 zero's and save the string-representation.
If the dummy needs be multiplied by 500k, then multiply it by 5 and pad zeroes again
Don't limit the varchar to eight characters - use varchar(50)

What kind of number are you trying to display? Can you give us an example of a number "before" (the original dummy) and the one "after" (the resulting dummy)?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

vanikanc3-Aug-12 3:05

3-Aug-12 3:05

Thank you for all your suggestions!

It is just some inherited code, and really don't know the in and out of it. It has been working for years, so don't want to mess with what comes in and goes out.

There was an error entering the value, it was supposed to be 50 and the user keyed in 500000. So, the business wants us to put aside such errors and continue processing the data. I guess we have to code for human errors!!

Thanks for all your time and suggestions!

Eddy Vluggen3-Aug-12 4:02

SQL Query - write all in one query

3-Aug-12 4:02

vanikanc wrote:
Thank you for all your suggestions!

My pleasure, hope I wasn't too rude.

vanikanc wrote:
It is just some inherited code,

Ah, that's always a good one to include on the first post. If we see code, we assume you wrote it, and partially understand it.

vanikanc wrote:
There was an error entering the value, it was supposed to be 50 and the user keyed in 500000. So, the business wants us to put aside such errors and continue processing the data.

On Error Resume Next indeed. That's not how engineers work; if it fails, it fails for a reason. It get's corrected or excluded beforehand, not ignored. It's how managers work; if it fails, and nothing is burning, it's not a problem. Just ship the damn product already, we'll fix the bugs later.

Perhaps this would be a good time to add a validator to the entry-field of that user, and sanitize his/her input before it gets into the system. Once the value has been entered, it should be treated as "correct".

You heard about Knight Capital? Seems they had an "On Error Resume Next" idea to, and the algo kept buying stocks in packages of 100 at a time, 20 to 25 times a second - for over an half hour! (Total >440 million* losses - let's just be glad they weren't a hospital and relying on that software)

Ignoring errors is the worst offence in IT; the system could have skipped customer 59 for all we know. The best approach is preached by (forgive me) PHP, and it's called "do or die". Either the app does what it should do, or there's an unexpected exception - and since we cannot guarantee that the we're still working with valid data (unexpected situation, who knows what variables are loaded and not?) we have only one realistic option; let the app die. Terminate.

That's always better than continuing and writing records with an outdated identity-value after an exception, and not nowing that you're corrupting a database that was still correct when the app died.

_{*) I checked this time whether I should use million or billion.}

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Raman samineni1-Aug-12 6:30

1-Aug-12 6:30

Hello Friends

I have 3 select statments I need to write all in one query How, is it works in MS SQL in Management Studio

Select E.FirstName +' ' + E.LastName as Supervisor
INNER JOIN [cgs].dbo.tems h ON(h.Supervisor = E.EmploId)
where h.temId = 336

Select E.FirstName +' ' + E.LastName as Agent
From [cgs].dbo.Employ E with (NoLock)
where E.EmploId = 2305

Select count(*) As Tras
From CGCSLF
Where Dispo = 'Tras'

Select count(*) As Comp
From CGCSLF
Where Dispo = 'Comp'

SELECT AgeID Sum(Hours) As HOURS
FromCGCSLF
Group By AgeID
Raman

Thank you in advance

PIEBALDconsult1-Aug-12 8:42

PIEBALDconsult

1-Aug-12 8:42

I'm unclear what you require.

If you are using the ADO.net classes in SqlClient then you can put all those in one Command -- just separate them with semi-colons (;). You can then use ExecuteReader to get a DataReader to read the results -- use the NextResult method to advance to the results from the next statement. I'm fairly sure that DataAdapters will handle it as well, but I haven't used one for several years.

If you need to do something else, then please clarify your question.

Raman samineni1-Aug-12 16:02

1-Aug-12 16:02

hello sir,
I call this query in the program, before I do, I am testing query in the SQL management studio. In program Code is like that

String query = @"SELECT t.firstName + '' + t.LastName

Regards
Raman

PIEBALDconsult1-Aug-12 18:23

PIEBALDconsult

1-Aug-12 18:23

I don't think you finished what you intended to write, but what you did write looks bad.

Mycroft Holmes1-Aug-12 13:17

Mycroft Holmes

1-Aug-12 13:17

Raman samineni wrote:
I have 3 select statments

Funny I count 5, the ability to count is reasonably fundamental to stating your problem. You also need to be much clearer what your goal is.

Do some reading on UNION and UNION ALL, these will help. Your queries must have the same fields and formats so your first query should be something like

SQL

Select E.FirstName +' ' + E.LastName as FullName, 'Supervisor' as TypeName
INNER JOIN [cgs].dbo.tems h ON(h.Supervisor = E.EmploId)
where h.temId = 336

Never underestimate the power of human stupidity
RAH

Raman samineni1-Aug-12 15:56

1-Aug-12 15:56

I put Union and Union all, it does not work.

Thanks
Regards
Raman

Mycroft Holmes1-Aug-12 16:25

Mycroft Holmes

1-Aug-12 16:25

Did you do some reading on how to use UNION or did you just stick it in between the select queries. RTFM

Never underestimate the power of human stupidity
RAH

Raman samineni2-Aug-12 8:22

2-Aug-12 8:22

Hello sir,

Just stick in between the select queries. I dont want use Union , Because this qry goes to in the program.

Regards

Raman

Eddy Vluggen1-Aug-12 22:34

1-Aug-12 22:34

Raman samineni wrote:
I need to write all in one query

What would you gain from having it in a single statement? As PIEBALD stated, you can put them in a single SqlCommand and execute them.

The first Sql-statement will not work. Test it again, it lacks a FROM clause.

you could embed the count as a sub-select, like this;

SQL

SELECT E.FirstName +' ' + E.LastName AS Agent
      ,(SELECT COUNT(*) FROM CGCSLF WHERE Dispo = 'Comp') AS [Comp]
 FROM [cgs].dbo.Employ E with (NoLock)
WHERE E.EmploId = 2305

What the ELEPHANT is that NOLOCK doing there??
You need to pay attention to your formatting
EmploId is redundant; you're already in the "E" table, just name the damn thing "Id". That way all keys are named Id, saves some time typing and thinking, less chances on a typo, and a lot clearer when reading the code.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Raman samineni2-Aug-12 8:19