May 27, 2003 at 8:54 am
I am needing to find out the names of all the tables in an Access database. The context is that user's will be uploading data and we will be importing into a temporary area and then analyzing them. So the table names and column names will not be known. I need to get a list of the table names (and preferrably column names as well) from any Access database.
Any help will be most appreciated.
Thanks,
Richard
May 27, 2003 at 9:03 am
Dynamically add Access database as linked server and use sp_table_ex.
May 27, 2003 at 10:14 am
THANKS!!!
That worked like a charm abd I appreciate the quick response.
I have a second question though. I am still new to a lot of this stuff, but how do I use the return of the sp_tables_ex? I can return it to my application and I know how to handle it there, but how do I get access to that recordset within the same procedure?
For example, I need to grab the first two column names from sp_columns_ex in order to do the processing I need to do.
Thanks,
Richard
May 27, 2003 at 1:05 pm
create table #T1
(table_cat sysname null,
table_schem sysname null,
table_name sysname,
table_type sysname,
remarks sysname null)
insert into #T1 exec sp_tables_ex 'PROFILES'
May 27, 2003 at 2:23 pm
Thanks once again! That really helped me out and nailed it for me.
Richard
May 28, 2003 at 3:40 pm
I am sorry to be bothersome, but I got it to work manually the way you described, but when I put it into a stored procedure it gave me the error "General Network Error. Check Your Documentation". Everything is all on one machine. I shouldn't be accessing the network at all.
Here is the code I am using:
--Create a temp table to hold
create table #Tables
(
table_cat sysname null,
table_schem sysname null,
table_name sysname,
table_type sysname,
remarks sysname null
)
set @Table = @SessionID + '_' + @File
EXEC sp_addlinkedserver
@server = @SessionID,
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = @FileName
--Create the authoriztion
Exec sp_addlinkedsrvlogin @SessionID, 'false', 'sa' , 'Admin', null
insert into #Tables exec sp_tables_ex @SessionID
delete from #Tables where table_type <> 'Table'
select table_name from #Tables
exec sp_dropserver @SessionID, 'droplogins'
Any help would be appreciated :>
Thanks,
Richard
May 28, 2003 at 6:52 pm
login QA with SQL Server authentication and run your script. If it still doesn't work, run following example.
--Create a temp table to hold
create table #Tables
(
table_cat sysname null,
table_schem sysname null,
table_name sysname,
table_type sysname,
remarks sysname null
)
declare @SessionID varchar(20)
declare @File varchar(20)
declare @FileName sysname
set @SessionID = 'PROFILES1'
set @FileName = '\\server1\access\profiles.mdb'
EXEC sp_addlinkedserver
@server = @SessionID,
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = @FileName
--Create the authoriztion
Exec sp_addlinkedsrvlogin @SessionID, 'false', 'sa' , 'Admin', null
insert into #Tables exec sp_tables_ex @SessionID
delete from #Tables where table_type not like 'TABLE'
select table_name from #Tables
drop table #Tables
exec sp_dropserver @SessionID, 'droplogins'
May 29, 2003 at 9:07 am
Ok,
I tried the script a couple of different ways using QA and Profiler. When I tried just running the script exactly as you had it, it failed during the insert into #Tables, about half way through it, no surprise there. When I changed @Filename to a valid file on my system using the UNC, I got this error half way through the insert into #Tables
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
When I changed the local login (sa) on the sp_addlinkedsrvlogin to the login that my application uses it worked fine.
I watched with the profiler as my application tried to run the stored procedure, the profiler list that it starts the insert into #Tables, but then nothing else, it doesn't even get into the sp_tables_ex.
This is weird.
Thank you for all of your help,
Richard
May 29, 2003 at 9:19 am
Your Access database is password protected, Is it?
May 29, 2003 at 9:33 am
No its not. That's why I am confused. It is when I choose a different SQL user it works.
Richard
Edited by - rbinnington on 05/29/2003 09:34:58 AM
May 29, 2003 at 1:57 pm
Allen,
I have learned more about the problem. I thought the error might be a resource problem, so I moved the sever over to a different machine. I got a slightly different error.
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\server\sharename\rvpvx445oals1wze1mdlwp45\file.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
But if I go in through EM and take a look at the tables it doesn't have any problems. I watched it through the Profiler, and then tried the same thing through QA and it bomed on me with the previous error. Nothing else is looking at the file, or has it opened.
Any help would be GREATLY appreciated,
Richard
May 29, 2003 at 2:09 pm
Which authentication method Did you register your SQL Server in EM? Try login QA with SQL Server Authentication.
May 29, 2003 at 3:05 pm
I found the problem! The files being accessed were on the Web server and the script source access was not set, so it was not letting it look at the file/data.
But that raises another question. Why did it allow SQL Server to look at it through EM? I used SQL Server authentication for both, using the same account on both.
The question is mostly rhetorical. I am glad it works!
THANK YOU very much for your help. You have been a great help.
Ricahrd
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply