[Geek] SQL Query

[dancefloorlandmine]

Joy of Microsoft - one of our SQL databases is accessed via an Access front-end. Up until recently, ResearchFinn has been able to remove erroneous rows from the tables by bringing up the table in Access, selecting the row, and pressing Delete. Now, when she does that, an error box pops up which says:

ODBC--delete on a linked table 'dbo_tblAllData' failed.
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

It also failed to delete when trying to use a delete query directly within SQL Server itself - it appeared to work when the query was run, but when the query box was then closed, it came up with an Operation cancelled by user message, and didn't commit. However, runnning a delete query from the Access front end worked. Eh?

I'm wondering whether this is any way connected with the recent server C drive problem (see posts passim). I'm going to see whether a reboot (to be scheduled for tonight) solves the problem. I've already tried stopping and restarting the SQLServer service.

Comments

[incy.livejournal.com]

you need to set up cascading deletes, suspect it is et up in access front end to do just that.

[d-floorlandmine.livejournal.com]

The problem is that the behaviour has changed - ResearchFinn used to be able to select a record in the Access linked table, and delete it from there. Now it gives a timeout message? Any idea as to what may have happened, other than perhaps a corrupted database (hopefully not)? Hopefully tonight's scheduled reboot should do the job ...

[gaius-octavian.livejournal.com]

If you're worried about that, dbcc> will tell you.

[d-floorlandmine.livejournal.com]

Ooh, ta very much!

[rich-r.livejournal.com]

I was going to suggest wading in with the dbcc consistency checker commands. Quite often you can fix a database that's got some minor corruption in without anyone noticing.

[d-floorlandmine.livejournal.com]

Quite often you can fix a database that's got some minor corruption in without anyone noticing.
But ... but ... I want them to worship me for my l33t skillz. Well, OK, you're right, just getting the damn thing working the way it used to will make ResearchFinn happy, and she used to work at the Marquee, so that's fine ...

[gaius-octavian.livejournal.com]

We'll make a DBA out of you yet! :-P

[d-floorlandmine.livejournal.com]

I hope so - then I might have an income! Ta! Erm - does that stuff work/is it available on SQL Server 2000? [rueful grin] (Not that I've had a look yet - I'm having a lunchbreak)

[gaius-octavian.livejournal.com]

It was a long time ago but I'm pretty sure I remember doing that on MSSQL 6.5...

[d-floorlandmine.livejournal.com]

Excellent! I was hoping it wasn't one of Billy-boy's incentives to upgrade ... Ta!

[rich-r.livejournal.com]

We're still using SQL2000 for the bulk of our SQL servers (that's about 20 big beefy HP dual Xeon servers with around a terabyte of databases between them). SQL2005 does have lots of nice features and we will be migrating to it at some point - but our current CRM system doesn't like it, so we're not moving just yet...