Prev: computing a counter/counting column?
Next: fn_trace_gettable Read All Traces In Directory
From: Will the Thrill Will the on 1 Dec 2009 16:11
I have a SQLEXPRESS 2005 database linking to an Access 2003 file using the
following statement. The response is very slow. Is there any way I can speed
this up?

CREATE VIEW [dbo].[MyTable]
AS
SELECT FIELD1, FIELD2 FROM OPENROWSET 'Microsoft.Jet.OLEDB.4.0',
'\\MYSERVER\MYSHARE\MYDB.MDB'; 'admin'; '', MyTable) AS MyTable_1

The database on MYSERVER\MYSHARE must be an Access database.

Thanks
From: Will the Thrill on 1 Dec 2009 16:16
Can I create a Linked Server to an Access database? Would this help?

"Will the Thrill" wrote:

> I have a SQLEXPRESS 2005 database linking to an Access 2003 file using the
> following statement. The response is very slow. Is there any way I can speed
> this up?
>
> CREATE VIEW [dbo].[MyTable]
> AS
> SELECT FIELD1, FIELD2 FROM OPENROWSET 'Microsoft.Jet.OLEDB.4.0',
> '\\MYSERVER\MYSHARE\MYDB.MDB'; 'admin'; '', MyTable) AS MyTable_1
>
> The database on MYSERVER\MYSHARE must be an Access database.
>
> Thanks
From: Erland Sommarskog on 1 Dec 2009 17:52
Will the Thrill (WilltheThrill(a)discussions.microsoft.com) writes:
>> I have a SQLEXPRESS 2005 database linking to an Access 2003 file using
>> the following statement. The response is very slow. Is there any way I
>> can speed this up?
>>
>> CREATE VIEW [dbo].[MyTable]
>> AS
>> SELECT FIELD1, FIELD2 FROM OPENROWSET 'Microsoft.Jet.OLEDB.4.0',
>> '\\MYSERVER\MYSHARE\MYDB.MDB'; 'admin'; '', MyTable) AS MyTable_1
>>
>> The database on MYSERVER\MYSHARE must be an Access database.

I would test copying the Access database to the local disk. If that is
fast, then it's a network issue.

> Can I create a Linked Server to an Access database? Would this help?

I can't see that adding a linked server would help.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: computing a counter/counting column?
Next: fn_trace_gettable Read All Traces In Directory