HI,
I got error like this 'Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ClientAc410".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ClientAc410" returned message "Unspecified error".' while importing from msaccess to sql using linked server.
I have been doing this for past two weeks.Previously i got in 32 bit mechine and not working on 64 bit mechine.
But Now i got this error on 32 bit mechine. I am taking Nework service mechine and running this on server.
This is my code
try
{
cmd = new SqlCommand("SP_Create");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@servername", acc);
cmd.Parameters.AddWithValue("@datasr", path);
cmd.Parameters.AddWithValue("@table", sourcetable);
cmd.Parameters.AddWithValue("@table1", desttable);
cmd.Parameters.AddWithValue("@db", db);
cmd.CommandTimeout = 180;
cmd.Connection = cn1;
connection.Close();
if (cn1.State != ConnectionState.Open) { cn1.Open(); }
cmd.ExecuteNonQuery();
cn1.Close();
}
catch (Exception exc)
{
connection.Close();
}
And this is my stored procedure
ALTER proc [dbo].[SP_Create](@servername nvarchar(100),
@datasr nvarchar(MAX),@table nvarchar(100),@table1 nvarchar(100),
@db nvarchar(100)) as begin EXEC sp_addlinkedserver @server = @servername,
@provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet',
@datasrc = @datasr,@provstr=''exec sp_addlinkedsrvlogin @rmtsrvname=@servername,
@useself = N'false',@locallogin = NULL,@rmtuser = N'Admin',@rmtpassword = NULL
declare @sql nvarchar(MAX) set @sql= 'select * into '+@table1+' from '+@servername+'...'+@table+'
with (nolock)'Exec(@sql) declare @sql1 nvarchar(MAX) set @sql1 ='EXEC master.sys.sp_dropserver '
+@servername+',''droplogins''' exec(@sql1) end
Can u please resolve this error. I am doing this on client mechine.