April 21, 2016 at 12:53 pm
Thanks in advance... I am trying to develop a stored procedure to pull Access data into SQL Server.
I created a linked server object using:
EXEC sp_addlinkedserver
@server = 'GCDF'
,@provider = 'Microsoft.ACE.OLEDB.12.0'
,@datasrc = '\\denalibcc\databases\GCDF\BE\GCDF_Web_Master.mdb'
,@srvproduct='Access'
GO
Then I run SELECT * FROM GCDF...ApplicantMaster to test the connection but get:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "GCDF" returned message "The Microsoft Office Access database engine cannot open or write to the file '\\denalibcc\databases\GCDF\BE\GCDF_Web_Master.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "GCDF".
I'm lost on the below also with creating the sp... obviously needs some work:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[pullAccessData]
(
@[origDBId] int,
@[First Name] nvarChar(25),
@[Last Name] nvarChar(30),
@[City] nvarChar(100),
@[State/Prov] nvarChar(100),
@[PostalCode] nvarChar(50),
@[Country] nvarChar(25),
@[origDB] nvarChar(255),
@[certs] nvarChar(255),
@[Region] int,
@[Certification Type] nvarChar(20)
)
As
INSERT INTO [GCDFImport] ([origDBId],[First Name],[Last Name],[City],
[State/Prov],[PostalCode],[Country],[origDB],[certs],[Region],[Certification Type])
VALUES (SELECT * FROM GCDF...ApplicantMaster)
GO
April 21, 2016 at 3:46 pm
briancampbellmcad (4/21/2016)
Thanks in advance... I am trying to develop a stored procedure to pull Access data into SQL Server.I created a linked server object using:
EXEC sp_addlinkedserver
@server = 'GCDF'
,@provider = 'Microsoft.ACE.OLEDB.12.0'
,@datasrc = '\\denalibcc\databases\GCDF\BE\GCDF_Web_Master.mdb'
,@srvproduct='Access'
GO
Then I run SELECT * FROM GCDF...ApplicantMaster to test the connection but get:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "GCDF" returned message "The Microsoft Office Access database engine cannot open or write to the file '\\denalibcc\databases\GCDF\BE\GCDF_Web_Master.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "GCDF".
I'm lost on the below also with creating the sp... obviously needs some work:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[pullAccessData]
(
@[origDBId] int,
@[First Name] nvarChar(25),
@[Last Name] nvarChar(30),
@[City] nvarChar(100),
@[State/Prov] nvarChar(100),
@[PostalCode] nvarChar(50),
@[Country] nvarChar(25),
@[origDB] nvarChar(255),
@[certs] nvarChar(255),
@[Region] int,
@[Certification Type] nvarChar(20)
)
As
INSERT INTO [GCDFImport] ([origDBId],[First Name],[Last Name],[City],
[State/Prov],[PostalCode],[Country],[origDB],[certs],[Region],[Certification Type])
VALUES (SELECT * FROM GCDF...ApplicantMaster)
GO
1. Try creating a linked server to your access file with specific security context of a user/account you know has access to the file.
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST', @locallogin = NULL , @useself = N'False', @rmtuser = N'DOMAIN\TEST', @rmtpassword = N'TEST'
GO
2. The Stored Proc looks ok but be wary of large data imports, there are various ways to handle this.
Just a thought. This is what SSIS is for and removes the need for linked servers etc. You can even schedule the package to run at regular intervals to automate the process....
April 21, 2016 at 4:05 pm
If you're importing from an .MDB (as opposed to an .ACCDB) file, I'd use the JET 4.0 drivers.
April 22, 2016 at 9:37 am
I used the 'Microsoft.Jet.OLEDB.4.0', and redid the addlinkserver:
EXEC sp_addlinkedserver
@server = 'GCDF'
,@provider = 'Microsoft.Jet.OLEDB.4.0'
,@datasrc = '\\denalibcc\databases\GCDF\BE\GCDF_Web_Master.mdb'
,@srvproduct='Access'
GO
I'm confused with the below, should I be looking at the security on the Access database's properties for the information below. My network guy set up within the domain cc-us.org an account called AccessAdmin and gave it a password of pass123, but after running the below I get an error upon testing the connection in SQL Server I get an error that 'authentication failed...... the domain and user are not valid'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'GCDF'
,@locallogin = NULL
,@useself = N'False'
,@rmtuser = N'cc-us.org\AccessAdmin'
,@rmtpassword = N'pass123'
GO
April 22, 2016 at 11:31 am
I should have said to add those parameters to yours in the addlinkserver so combine your parameters and the user parameters and try again.
April 22, 2016 at 11:54 am
It gives me an error that there are too many parameters.
Could I use something like this? There is no formal security on the database, just the folder in which it resides. I tried the below and still no connectivity:
EXEC sp_addlinkedserver
@server = N'GCDF'
,@provider = N'Microsoft.Jet.OLEDB.4.0'
,@srvproduct= N'OLE DB Provider for Jet'
,@datasrc = N'\\denalibcc\databases\GCDF\BE\GCDF_Web_Master.mdb'
GO
exec sp_addlinkedsrvlogin @rmtsrvname='GCDF',
@useself='false',
@rmtuser='Admin',
@rmtpassword=''
GO
April 22, 2016 at 12:03 pm
I didn't even realize there were two sp at work here. What I normally do is use the GUI and then script it out which is what I showed in my original response. So yes that makes sense to create a login for the linked servername.
April 22, 2016 at 12:48 pm
I went through the GUI with it failing the 'test connection'. I'm trying to put together some code in VBA/Access that I could use to push the data when someone closes the database, but struggling with that too.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply