You can monitor the queries with sql profiler. Check on cpu, read, writes.

Have you checked the table scan, sql compilations, recompilations / sec counters in performance monitor?

If you execute the queries step by step, when does it error out?

You can split the stored procedure later in smaller parts.

--step 1

DECLARE @FinFileName varchar(500)

SET FinFileName ='myfile' -- The...

In which Access version?

We've been using docmd.transfertext in a VBA module to import text-files into tables in Access 97 using a import-specification (takes only 1 time to make them and...

Access 97 has a database limit of 2 gb, Access 2000 and higher 1 gb (not sure, check help)

Is the data already located in another database than the forms/queries/code?

Size doesn't...

exp_dt = cast('9999-12-31' as datetime) would be preferable, since sql server only has to convert once and can use an index on exp_dt.

Otherwise it has to convert each value and...

Ain't familiar with SHAPE.

Does the same happen when you make a new stored procedures that calls the other 3? It might be using 3 different connections.

In sql 2000 BOL:

Indexed views are meant for static tables that have very infrequent changes.

Indexed views can be more complex to maintain than indexes on base tables. You should create...

or with a join

UPDATE EMP

SET ID = ISNULL(DEPT.ID, 0)

FROM EMP LEFT JOIN DEPT ON EMP.NAME = DEPT.NAME

- If the application is holding locks, does that mean you suspect that it is executing more than one query apart from the one i'm analyzing and that is...

I can't find the exact definition for duration at the moment. But it could be that the application is holding locks till all rows are fetched.

The results are both executed...

nice one

identity(1,1)

Identity seed (=start) = 1 (first parameter)

Increase by 1 (second)

I see that the query executed directly on the database needs 65221 reads and takes 0.5 secs

And when it is the VB application that requests the query it needs also...

can I see a link to these best practises?

All best practises I've seen mention that all objects should be owned by dbo.

(permission chaining, limiting wildgrowth,...)

Databases are normally owned by sa...

out of my head:

varchar: easily searchable but limited to 8000 characters (4000 for nvarchar)

text: allows more but is not so searchable because it isn't stored in the "record" itself, just...