February 18, 2009 at 2:07 am
am trying to access data from microsoft access using link servers
RAIS
Microsoft.Jet.OLEDB.4.0
Microsoft Acess
C:\Users\babygirl\accessdb\Ayawaso_East.mdb
and the db is in microsoft access with full permision being
am getting an error like this
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "rais".
but the strange thing is that i did thesame on a collegue's pc and it worked . some help plse cus this issue is frustrating me
February 18, 2009 at 2:12 am
Hi,
Are you able to expand linked servers in Management Studio and list the Tables?
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 18, 2009 at 2:19 am
not quiet sure wat u mean but i guess am able to extend link servers on management studio
thus how i get to see the various providers..... can u help me solve my problem?
February 18, 2009 at 2:23 am
Hi,
Try this Management Studio --> Server Objects --> Linked Servers --> Double click Linked Server Name --> Catalogs --> Click any database --> Tables
Are you able to see the table list here? Whether you are getting any error message at this level?
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 18, 2009 at 2:43 am
There is nothing like catalogs when i double click my linked server name
what i see is a window divided in to two parts, the first part is
General>
security>local login|impersonate|Remote User|Remote password
server options >collation capatible|Data Acess|Rpc....
whilst the other side its just my linked server script
which is
Link Server: RAIS
provider: Microsoft Jet 4.0 OLE DB Provider
product name: Microsoft Acess
DATASOURCE:C:\Users\babygirl\acessdb\Ayawaso_East.mdb
forgive me if am a lil basic , am still a beginning programmer so plse help me sort it out
February 18, 2009 at 2:44 am
ok ok don't worry... We are here to help you...
Go to security option of linked server select "Be Made using this security context" and type username and password both as admin.
Now click OK to dismiss windows and try querying your linked server....It should work now!
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 18, 2009 at 3:01 am
sadly its still giving me that error
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "rais" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 5
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "rais".
or maybe i need to make some configurations on ma sql server be4 cus everything seems rite so my quetion is y still not working
thanks for ur help so far n encouragement ba the problem is still at large
February 18, 2009 at 3:09 am
sadly its still giving me that error
after i create my linked server i executed a simple select statement and i get this error
select * from rais ... Community
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "rais" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 5
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "rais".
or maybe i need to make some configurations on ma sql server be4 cus everything seems rite so my quetion is y still not working
thanks for ur help so far n encouragement ba the problem is still at large
February 18, 2009 at 5:45 am
Can you ensure that you have providers properly installed by performing below operation?
In SQL Server Management Studio, expand "Server Objects->Linked
Servers->Providers" to see if there is a provider named
Microsoft.Jet.OLEDB.4.0.
Is there any 64-bit SQL Server involved in your operation?
Is your SQL Server running with Local account or Service Account? Reason for this question is SQL Server account should have necessary NT level permissions to access the folder (RW) where your access MDB file is stored.
Can you drop the existing linked server and run below script to create a new linked server and check it out?
EXEC sp_addlinkedserver
@server = N'Database1',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'\\srv1\Database1.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'Database1',
@useself = N'FALSE',
@locallogin = NULL,
@rmtuser = N'Admin',
@rmtpassword = 'admin'
GO
-- List the tables on the linked server
EXEC sp_tables_ex N'Database1'
GO
Also this link can give you more help...
feel free to revert...
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 18, 2009 at 7:08 am
THANKS SKAHT BUT STILL IT AINT WORKING THIS is wat i did exactly wat u suggested
EXEC sp_addlinkedserver
@server = 'GAME',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\Users\babygirl\accessdb\Ayawaso_East.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'GAME',
@useself = 'FALSE',
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = 'admin'
GO
EXEC sp_tables_ex 'GAME'
and here is the error
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "GAME" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "GAME" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "GAME".
February 18, 2009 at 7:38 am
Do the access db is password protected? If not then drop & create the linked server using the following script..
DECLARE @strLinkedServer NVARCHAR(100)
SELECT @strLinkedServer = 'SomeAccessServer'
EXECUTE master.dbo.sp_dropserver
@server = @strLinkedServer,
@droplogins = 'droplogins'
EXECUTE master.dbo.sp_addlinkedserver
@server = @strLinkedServer,
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\Users\babygirl\accessdb\Ayawaso_East.mdb'
EXECUTE master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = @strLinkedServer,
@useself = N'True',
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword = NULL
EXECUTE master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = @strLinkedServer,
@useself = N'False',
@locallogin = N'sa', -- replace the login name "sa" with the login you are currently using
@rmtuser = N'Admin',
@rmtpassword = NULL
GO
EXEC sp_tables_ex @strLinkedServer
--Ramesh
February 19, 2009 at 3:16 am
am not really sure wat to put in the someAccessServer part cus my acess db is on thesame pc as my sql server ... so plse elaborate .... anyway i kept in sth there and am getting this error
SELECT @strLinkedServer = 'SomeAccessServer'
and wuld really appereciate it if u cud explain ur script a lil bit..... especially the first part
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MANY" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 5
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MANY" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 5
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MANY".
February 19, 2009 at 3:33 am
Well, the "SomeAccessServer" is just a name to the linked server, and by which you will accessing the tables in your access db.
Have you replaced the login "sa" in the script before executing?
@locallogin = N'sa', -- replace the login name "sa" with the login you are currently using
Do you see any *.lock files in the folder "C:\Users\babygirl\accessdb"? If yes, then delete those files and re-check the server by doing a select on the linked server.
--Ramesh
February 19, 2009 at 6:52 am
hi ramesh
thanx for the help but still the errors below keeps poping up, maybe is not the matter of syntax ba rather some configurations ..... am just sugesting try n am get back to me cus am getting worried
Msg 137, Level 15, State 1, Line 5
Must declare the scalar variable "@strLinkedServer".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "doit" returned message "'C:\Users\babygirl\accessdb\Ayawaso_East.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "doit".
February 19, 2009 at 6:57 am
Have you tried my previous suggestions? What script are you executing? I see a variable declaration error.
--Ramesh
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply