SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 importing data from 2008 named instance to 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 05/29/2013 :  23:41:49  Show Profile  Reply with Quote
Hi All,

I am planning to copy a data from SQL server 2008 Enterprise Edition to SQL server 2005 Enterprise edition and data needs to be refreshed everyday ones.

What are the best possible options available to do this.

While connecting through SSIS using Microsoft OLEDB provider FOR SQL server i am getting below error :
Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Please help with any useful links or suggest



Thanks,
Gangadhara MS
SQL Developer and DBA

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/29/2013 :  23:46:26  Show Profile  Visit russell's Homepage  Reply with Quote
SSIS is probably your best option. Possibly linked server, if the data set is small and machines are on the same network.

Seems either you mis-typed server/instance name, or you don't have permission.

Did the connection manager connect ok?
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 05/30/2013 :  00:47:31  Show Profile  Reply with Quote
yes the data set is small and machine are in the same domain. Thanks for this.

No i have named instance and typed it correctly ..i have sysadmin permission for SQL server user.

Provider: Native OLE DB\Microsoft OLEDB Provider for SQL server
server name : AAA\XXX (ITS IN 2008 SQL SERVER)

SQL authentication : Usern name and password provided

But i am getting below error:

TITLE: Connection Manager
------------------------------

Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

------------------------------
BUTTONS:

OK
------------------------------




Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 05/30/2013 :  01:00:33  Show Profile  Reply with Quote


Sorry both are in different domain


Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 05/30/2013 :  01:17:19  Show Profile  Reply with Quote
Even when i try to do linked server set up i am getting the error.

TITLE: Microsoft SQL Server Management Studio
------------------------------

"The linked server has been created but failed a connection test. Do you want to keep the linked server?"

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Out-of-process use of OLE DB provider "SQLNCLI" with SQL Server is not supported. (Microsoft SQL Server, Error: 7430)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=7430&LinkId=20476

------------------------------
BUTTONS:

&Yes
&No
------------------------------


Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000