posted Apr 14, 2011, 5:05 AM by Boobalan Munusamy
[
updated Apr 14, 2011, 5:40 AM
]
1. First add MSAccess to SQL2008, So Open SQL2008->Expand Server Object from Server in the Object Explore -> Right click server object->New->Linked Server or right click Linked Server-> New Linked Server wait till dialog box appear. 2. Once it shown, Fill the required details. or execute following script
/****** Object: LinkedServer [GREATINDIA] Script Date: 04/14/2011 17:34:24 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'GREATINDIA', @srvproduct=N'GreateIndia', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'D:\MSAccess\Tryal.accdb'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'GREATINDIA',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,
@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'GREATINDIA', @optname=N'use remote collation', @optvalue=N'true'
GO
3. Use following query to get data from MS Access table SELECT [ID]
,[Id1]
,[Name]
,[Type]
FROM [GREATINDIA]...[MSObjects]
GO
4. The SQL Query used to get MS-Access table columns information is Syntax:
sp_columns_ex [ @table_server = ] 'table_server'
[ , [ @table_name = ] 'table_name' ]
[ , [ @table_schema = ] 'table_schema' ]
[ , [ @table_catalog = ] 'table_catalog' ]
[ , [ @column_name = ] 'column' ]
[ , [ @ODBCVer = ] 'ODBCVer' ]
[@table_server =] 'table_server' Is the name of the linked server for which to return column information. table_server is sysname, with no default. [@table_name =] 'table_name' Is the name of the table for which to return column information. table_name is sysname, with a default of NULL. [@table_schema =] 'table_schema' Is the schema name of the table for which to return column information. table_schema is sysname, with a default of NULL. [@table_catalog =] 'table_catalog' Is the catalog name of the table for which to return column information. table_catalog is sysname, with a default of NULL. [@column_name =] 'column' Is the name of the database column for which to provide information. column is sysname, with a default of NULL. [@ODBCVer =] 'ODBCVer'
exec sp_columns_ex 'GREATINDIA','EMPLOYEE',null,null,null
|
|
