Importing Sql 6.5 data

I am trying to move my data from Sql Server 65 on Windows NT 4 to my Sql
Server 7.0 on W2K and can only seem to move the tables, but not the
objects (such as stored procedures) using DTS.
Is there a way to easily move/transform the whole database from 65 to 7?
Thanks,
Tom.Only DTS type which includes other stuff than tables is the Object Transfer. And that is not
available when you go from 6.5 (as I remember). You can scrip the stuff to move proc's etc over, or
use the Upgrade Wizard which comes with SQL7.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message news:3FC3F4AB.9050601@.deltanet.com...
> I am trying to move my data from Sql Server 65 on Windows NT 4 to my Sql
> Server 7.0 on W2K and can only seem to move the tables, but not the
> objects (such as stored procedures) using DTS.
> Is there a way to easily move/transform the whole database from 65 to 7?
> Thanks,
> Tom.
>|||Tibor Karaszi wrote:
> Only DTS type which includes other stuff than tables is the Object Transfer. And that is not
> available when you go from 6.5 (as I remember). You can scrip the stuff to move proc's etc over, or
> use the Upgrade Wizard which comes with SQL7.
>
Where would I find the Upgrage wizard?
Thanks,
Tom.
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:3FC3F4AB.9050601@.deltanet.com...
> I am trying to move my data from Sql Server 65 on Windows NT 4 to my Sql
> Server 7.0 on W2K and can only seem to move the tables, but not the
> objects (such as stored procedures) using DTS.
>
> Is there a way to easily move/transform the whole database from 65 to 7?
>
> Thanks,
>
> Tom.|||On the SQL Server 2000 machine, the program group is called "Microsoft SQL Server - Switch".
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message news:3FC45F9D.5030901@.deltanet.com...
>
> Tibor Karaszi wrote:
> > Only DTS type which includes other stuff than tables is the Object Transfer. And that is not
> > available when you go from 6.5 (as I remember). You can scrip the stuff to move proc's etc over,
or
> > use the Upgrade Wizard which comes with SQL7.
> >
> >
>
> Where would I find the Upgrage wizard?
> Thanks,
> Tom.
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:3FC3F4AB.9050601@.deltanet.com...
> > I am trying to move my data from Sql Server 65 on Windows NT 4 to my Sql
> > Server 7.0 on W2K and can only seem to move the tables, but not the
> > objects (such as stored procedures) using DTS.
> >
> > Is there a way to easily move/transform the whole database from 65 to 7?
> >
> > Thanks,
> >
> > Tom.
>sql

Importing into Test Database

SQL 2000, Windows 2000. I need to find a way to export our data from
our production database and import it into a test database. I can do
it to a freshly created test database I just have trouble doing it
once the test database is already populated. Is there a script I can
write that will automate it by
1) Deleting the test database
2) Creating a new one
3) Exporting the production tables and views
4) Importing into the new test database

What are my options here?

Thank you in advance."Justin Allen" <contactjustinallen@.hotmail.com> wrote in message
news:3b993d0.0402130629.581cecb4@.posting.google.co m...
> SQL 2000, Windows 2000. I need to find a way to export our data from
> our production database and import it into a test database. I can do
> it to a freshly created test database I just have trouble doing it
> once the test database is already populated. Is there a script I can
> write that will automate it by
> 1) Deleting the test database
> 2) Creating a new one
> 3) Exporting the production tables and views
> 4) Importing into the new test database
> What are my options here?
> Thank you in advance.

See my reply to your previous post.

Simon|||contactjustinallen@.hotmail.com (Justin Allen) wrote in message news:<3b993d0.0402130629.581cecb4@.posting.google.com>...
> SQL 2000, Windows 2000. I need to find a way to export our data from
> our production database and import it into a test database. I can do
> it to a freshly created test database I just have trouble doing it
> once the test database is already populated. Is there a script I can
> write that will automate it by
> 1) Deleting the test database
> 2) Creating a new one
> 3) Exporting the production tables and views
> 4) Importing into the new test database
> What are my options here?
> Thank you in advance.

If you are looking to simply bring all the data over from the source
to the destination db, you may try to dump the tables to a sql file
and run the load on the destination db.

Also have a look at sqlporter database migration tool
from realsoftstudio.com

Hope it helps.

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

Hi,

I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.

Please let me know.

Thanks

MSDAORA does not support 10g, have a look at INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
Try using OraOLEDB.Oracle instead.|||

Hi Anton,

Thanks for speedy reply.

Anton in my procedure if I remove transaction(Begin Tran, Commit, Rollback) then this procedure is working fine, but removal of it not possible, so it is not related with provider, I hope.

If I put tranaction it shows above maintain error.

Thanks

|||Is there any reason you cannot try Oracle's provider?|||

Hi Anton,

I am using OLEDB.ORACLE but it is not allowing me to make new Linked server.

It shows me this 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)

The OLE DB provider "OraOLEDB.Oracle" for linked server "AA" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "AA". (Microsoft SQL Server, Error: 7399)

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

BUTTONS:

&Yes
&No

|||Have you enabled AllowInProcess option in the provider options?|||

Hi Anton,

Yes, I did but it returns another error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Procedure PROC_VLD_BI_TRANSFER, Line 36
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

|||But MSDAORA is not an Oracle's provider. Did you try it for the OraOLEDB?|||

Hi Anton,

Yes, I did

But it is not allowing me to make a new linked Server.

Presently I am facing this problem:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Procedure PROC_VLD_BI_TRANSFER, Line 36
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

|||

Hi Anton,

Now I solved ORA-12154 Error but when I enabled "Allow InProcess" this gives ORA-12154 Error again.

After disable this option, I can connect Oracle server successfully but when I used to fire my procedure that is used to import records from oracle 10g to sqlserver 2005 using linked server.

It Shows me this error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" reported an error. The provider did not give any information about the error.
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" was unable to begin a distributed transaction.

Thanks for your nice support.

|||http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/816701|||

Hi Anton,

I got 2 kind of error:

checked "Allow inprocess" in the provider options, then it Error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS".

If NOT CHECKED then Error:

OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.

I used later one because by this i can access records from oracle but when i used this linked server in my Procedure using transaction then this error is reflected.

I find out a link of support.microsoft.com/kb/906954 but it can not make me out from this trouble.

Please advise me the solution.

Thanks for your support.