importing data from 2008 named instance to 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

importing data from 2008 named instance to 2005

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-05-29 : 23:41:49

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

5072 Posts

Posted - 2013-05-29 : 23:46:26

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?

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-05-30 : 00:47:31

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

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-05-30 : 01:00:33

Sorry both are in different domain

Thanks,
Gangadhara MS
SQL Developer and DBA

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2013-05-30 : 01:17:19

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources