Importing views from Oracle

I've been able to import tables successfully from Oracle
to a SQL Server database. However, when I try to import
views from Oracle it wants to import them as tables in
SQL Server. Any ideas how I can import views as views
from Oracle to SQL Server?A view is a virtual table. Technically, to import just the
view you would only be importing the view definition, not
the data. Import the data and then create a view in SQL
Server.
-Sue
On Mon, 23 Aug 2004 13:49:01 -0700, "Scott"
<anonymous@.discussions.microsoft.com> wrote:

>I've been able to import tables successfully from Oracle
>to a SQL Server database. However, when I try to import
>views from Oracle it wants to import them as tables in
>SQL Server. Any ideas how I can import views as views
>from Oracle to SQL Server?

Importing views from Oracle

I've been able to import tables successfully from Oracle
to a SQL Server database. However, when I try to import
views from Oracle it wants to import them as tables in
SQL Server. Any ideas how I can import views as views
from Oracle to SQL Server?
A view is a virtual table. Technically, to import just the
view you would only be importing the view definition, not
the data. Import the data and then create a view in SQL
Server.
-Sue
On Mon, 23 Aug 2004 13:49:01 -0700, "Scott"
<anonymous@.discussions.microsoft.com> wrote:

>I've been able to import tables successfully from Oracle
>to a SQL Server database. However, when I try to import
>views from Oracle it wants to import them as tables in
>SQL Server. Any ideas how I can import views as views
>from Oracle to SQL Server?

Importing to SQL Express using SSMSE

When I used Enterprise Manager it was very easy to import required tables from another server into my local MSDE server.

Now I am using SQL Express and SSMSE I cannot seem to find any other way of importing data other than creating the insert scripts manually which is a very painful and tediuos operation!

Can anyone advise if I have missed something and there is a way to import easily using SSMSE?

Thanks.......in hope

hi,

SSMSE does not provide the wizards included in SSIS or the like, so you have to do it "your way"...

this usually means BCP data in, use INSERT INTO scripts, connect to linked servers (ifa available) and INSERT..SELECT data in...

regards|||

Bums!

Oh well at least I know now.....

Thanks for the info ;-)

|||

I cannot seem to find any other way of importing data

You can import data (but not table definitions) using the DTSWizard. It is a simplied version of the Import/Export Wizard from Enterprise Manager.

It is located in: {installdirectory}\Microsoft SQL Server\90\DTS\Binn\DTSWizrd.exe

|||When I look for the DTS Wizard in the location you specify, it isn't there!!

Man, it's frustrating not to have it...
|||

Download from here:

DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111

Importing to SQL Express using SSMSE

When I used Enterprise Manager it was very easy to import required tables from another server into my local MSDE server.

Now I am using SQL Express and SSMSE I cannot seem to find any other way of importing data other than creating the insert scripts manually which is a very painful and tediuos operation!

Can anyone advise if I have missed something and there is a way to import easily using SSMSE?

Thanks.......in hope

hi,

SSMSE does not provide the wizards included in SSIS or the like, so you have to do it "your way"...

this usually means BCP data in, use INSERT INTO scripts, connect to linked servers (ifa available) and INSERT..SELECT data in...

regards|||

Bums!

Oh well at least I know now.....

Thanks for the info ;-)

|||

I cannot seem to find any other way of importing data

You can import data (but not table definitions) using the DTSWizard. It is a simplied version of the Import/Export Wizard from Enterprise Manager.

It is located in: {installdirectory}\Microsoft SQL Server\90\DTS\Binn\DTSWizrd.exe

|||When I look for the DTS Wizard in the location you specify, it isn't there!!

Man, it's frustrating not to have it...
|||

Download from here:

DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111

sql

Importing times into SQL Server

I am trying to import an access database into SQL Server 2000.
I am using the DTS Import wizard to to this.

Most of the tables import fine, but a few are not importing.

Here is the error message that occurs on most of them:

Code Snippet

Table Name Here
Error at destination for Row number 1. Error encountered so far in this task:

1. Insert error, column 7 (‘CallTime’, DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.

Here is an example of an entry in one of the 'Calltime' rows: 7:45:30PM.
This is a date/time field in Access.

I looked at this SQL table after the import finished and there was no data.
Also, it tried to import as a smalldatetime data type.

Is there something I can change in Accesss to fix this problem?

Perhpas you should identify with a query instead of a pure table mapping which values break the import process. Use the ISDATE function for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Importing tables/cells into another database

I am running a web site that that classifieds and using MS SQL 2000 or 2005
(I got both installed and afraid If I take 2000 off the classifieds will no
longer function).
Anyway, I want to put a forum on the site also.
The setup includes a scrip to create al the tables for the new database.
I would rather use the original database with the users on it and modify it
some way so that I can continue to have current users on the new forum.
Is there a way to simplify a merge between these two?
I am guessing I will have to modify the forum software to look for the
element names of user/password for the classifieds database.
Thanks for any help, hints or tips.
GB
hi,
GeekBoy wrote:
> I am running a web site that that classifieds and using MS SQL 2000
> or 2005 (I got both installed and afraid If I take 2000 off the
> classifieds will no longer function).
> Anyway, I want to put a forum on the site also.
> The setup includes a scrip to create al the tables for the new
> database.
> I would rather use the original database with the users on it and
> modify it some way so that I can continue to have current users on
> the new forum.
> Is there a way to simplify a merge between these two?
>
without elements such as table design it's actually impossible to answer to
this one..
you can probably "add" you old users into the new database model, but,
again, it's impossibile to answer without elements

> I am guessing I will have to modify the forum software to look for the
> element names of user/password for the classifieds database.
>
you probably will, and I'd guess it'll not work at all if you just maintain
the old database you already have.. just a bet...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:5ealvmF37mtgoU1@.mid.individual.net...
> hi,
> GeekBoy wrote:
> without elements such as table design it's actually impossible to answer
> to this one..
> you can probably "add" you old users into the new database model, but,
> again, it's impossibile to answer without elements
> you probably will, and I'd guess it'll not work at all if you just
> maintain the old database you already have.. just a bet...
>
Well it seems it is a bit more complicated than that. The passwords on the
database for the forum are encrypted.
Any more suggestions on how to use both?
Thanks for the kind reply so far.
|||hi,
GeekBoy wrote:
> Well it seems it is a bit more complicated than that.
:D you can take it for granted :D

>The passwords
> on the database for the forum are encrypted.
> Any more suggestions on how to use both?
>
you can't for sure "use both".. you "coul'd" transfer users (and encrypted
pwds) to the "new" user's table via a "traditional" INSERT INTO .. SELECT
statement, but you obviously have to consider the metadata difference of the
2 tables... if the 2 designs are quiet "close", you can probably get it
done, but if they strongly differ you'll get in troubles..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:5ecnouF38c1sbU1@.mid.individual.net...
> hi,
> GeekBoy wrote:
> :D you can take it for granted :D
> you can't for sure "use both".. you "coul'd" transfer users (and encrypted
> pwds) to the "new" user's table via a "traditional" INSERT INTO .. SELECT
> statement, but you obviously have to consider the metadata difference of
> the 2 tables... if the 2 designs are quiet "close", you can probably get
> it done, but if they strongly differ you'll get in troubles..
> --
what about making 2 tables in the same database?
Then making a routine for writing user name and password into other
database?

> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>

Importing tables with relationship

When importing tables from Access/Oracle to SQL Server, how can I import tables with relationship as well?I might be wrong, but I don't think you can!
If you're doing it throught the DTS wizard, it's data only if the source is a non-sql server database.
As far as I'm aware you'd have to create the RI after the import.|||Is there any 3rd party tool to perform the same? :)sql

Importing Tables with Identity properties (uisng Import wizard)

I am using the Import wizard to import a SQL2000 database to SQL2005 and noticed 2 problems:

1. all tables and views were selected; the tables were imported correctly but the views were created as tables, ignoring the "Create view" syntax. The SQL generated contains "Create table" syntax instead of "Create View".

2. when a table contained a column with an "identity" property, the data was successfully imported, but the values for the "identity" column were not preserved, instead they were resquenced from 1 with an increment of 1 (the default values for an identity). When I opened the "Edit" (under "Mapping"), "enable identity insert" was not checked.

A further note: I created all tables in the SQL2005 database before running the Import.

1 - How would you represent a selection of tables, calculations and some view DDL in a text file? SSIS is about data movement, not object migration. This is what happens and is entirely correct. You often use views to expose a limited set of data, perhaps including calculations or such like, and you would then use SSIS to take that data and move it to a new system.

2 The wizard does not expose all options otherwise if would not be a very good wizard, and this is just one of those assumptions. It is in part no doubt based on the difficult of determining an identity column through the OLE-DB provider specifications, and it is the same old issue you had in DTS.

If you want to migrate SQL 2000 to SQL 2005 I suggest you have a look in Books Online it covers several options. A backup and restore is perhaps the easiest method I have found to take a SQL 2000 DB to a new SQL 2005 server.

|||

Darren:

Your reply was typical of someone who believes that Microsoft is incapable of making an error, in this case a grevious error since it MODIFIES data defined as an IDENTITY, instead of COPYING it. The SSIS wizard clearly needs work; it is unacceptable in its present form. (If you need an example of how a data migration wizard SHOULD work, review the one used by SQL SERVER 7 to migrate data from SQL SERVER 6.5).

I have manged to MOVE all of the data from SQL2000 to SQL2005 using a 3-step process: 1) run a SQL script that creates the table structures complete with all constraints. (This is ANOTHER area where the wizard is incomplete; it creates ONLY the table structure, minus all constraints). 2). Use the wizard to copy all tables that DO NOT CONTAIN identity columns. 3). Use the wizard to copy ONLY tables that DO CONTAIN identity columns, but EDIT each table entry checking ON "ENABLE IDENTITY INSERT".Using the 3-step process, I was able to bring ALL of the data over in one session, not following your suggestion of bringing the data across in a piece-meal process, never knowing if I had copied ALL of the data).

So, the task CAN BE DONE, if one is able to understand the failings of the wizard, and work around them appropriately.

|||

Thanks for adding your experiences.

I don't see that I suggested a piece-meal approach, I just stated there is an issue, and whilst not necessary to the answer I do think is big problem, I have hit it myself, and even logged a PPS call for the same issue in DTS. Have you feedback at all?

I recall the migration wizard and yes it was ideal for the job, but personally I am happy to accept the limitations covered with SSIS, because SSIS is really an ETL tool, not a migration tool. A tool designed specifically for SQL to SQL migrations would not be an effective ETL tool. Just because the wizard can be used does not mean it is ideal or even the best tool. There are much better tools for generating scripts and transferring structures for example, but the SSIS limitation of not creating constraints sounds like a bonus. Having constraints in place when loading data increases the complexity as you need to load related tables in order, and tables with circular references just cannot be loaded. Performance will also be much faster will with them as well. I would apply constraints after the load. That would all be me defending MS of course, but I try and think what the role for the product is too.

A non-piecemeal method, in just 1 step, which I did suggest was a backup and restore. It suffers none of the issues you covered, and is in many ways faster and more reliable than anything else. It may of course introduce additional implications, but then we have choices.

Importing tables to Microsoft SQL Server Management Studio Express

Hello,

I'm trying to import some tables from another sql server to my laptop. I just downloaded Microsoft SQL Server Management Studio Express and i'm not sure how to import things. Could anyone help me with that?

Thanks a bunch

j

Imports rely on SSIS which isn't supported in the express edition. However you can use the bcp utility or backup and restore the database.

|||

Thanks,

I'll look into the bcp utility, i'm not really looking to move the whole database (it's really big) just a few tables for a demo.

thanks again.

|||Anytime

Importing tables to Microsoft SQL Server Management Studio Express

Hello,

I'm trying to import some tables from another sql server to my laptop. I just downloaded Microsoft SQL Server Management Studio Express and i'm not sure how to import things. Could anyone help me with that?

Thanks a bunch

j

Imports rely on SSIS which isn't supported in the express edition. However you can use the bcp utility or backup and restore the database.

|||

Thanks,

I'll look into the bcp utility, i'm not really looking to move the whole database (it's really big) just a few tables for a demo.

thanks again.

|||Anytime

Importing tables into SQLServer

Hi, I've just got my brand new shiny SQLServer and would like to set some new tables set up etc. But I'm really lazy and I was wondering if there is any way of importing table layouts, coulmns, whole structure type thing into SQLServer from Microsoft Access using DTS? I know it's a bit optimistic, but just wondered...

Any help gratefully accepted.

Thanks,
Paula.DTS could do it, but it would be ugly. I'd suggest the Upsizing Wizard (http://support.microsoft.com/default.aspx?scid=kb;en-us;325017) for this job.

-PatP

Importing Tables from MS Access

I m trying to import Tables from MS Access and I m having this error

- Copying to [Manpower].[dbo].[MAINTABLE] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "PPExpiry" (157) on input "Destination Input" (113). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (113)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (113)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - MAINTABLE" (100) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

any hekp on this

Regards

How you're importing the tables from MS Access, is it DTS or import/export tool, any other tool?

Also check what kind of data or schema exists on the specified tables.

|||

I was import/export tool. I solved the problem. The Proble was with data type. There were some wrong entries in dates. after deleting those i was able to import the database.

Thanx

|||

kinda intrested how did u solve problem. i am trying to import access tables from harddrive into sql server which is my backend database.

i used

docmd.transferdatabse acimport,"microsoft Access",dbname,actable,Me.listtable,me.listtable

my tables are listed on listbox, call listtable.

i get error so i do not know what to do.

sql

Importing Tables from MS Access

I m trying to import Tables from MS Access and I m having this error

- Copying to [Manpower].[dbo].[MAINTABLE] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "PPExpiry" (157) on input "Destination Input" (113). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (113)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (113)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - MAINTABLE" (100) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

any hekp on this

Regards

How you're importing the tables from MS Access, is it DTS or import/export tool, any other tool?

Also check what kind of data or schema exists on the specified tables.

|||

I was import/export tool. I solved the problem. The Proble was with data type. There were some wrong entries in dates. after deleting those i was able to import the database.

Thanx

|||

kinda intrested how did u solve problem. i am trying to import access tables from harddrive into sql server which is my backend database.

i used

docmd.transferdatabse acimport,"microsoft Access",dbname,actable,Me.listtable,me.listtable

my tables are listed on listbox, call listtable.

i get error so i do not know what to do.

importing tables from Acess to SQL

Hello
I am trying to import tables with data from Access to SQL.
The problem occures when the Primary keys and identity are not being saved
and i need to do it manually.
There is a way to import this tables with all the connection regulations
like Primary key will be saved ?
thanks
GalWe have found that the safest way to move from Access to SQL Server is to
convert the Access file to 2002, then use the Upsize Wizard to move the
schema only. Finally we use the Data Transformation Services of SQL Server
to import the data from Access.
Make sure that all the tables have a key index.
Ragnar
"'" <@.discussions.microsoft.com> wrote in message
news:B93E10E0-1C9B-4C46-83AA-32F90A07E96D@.microsoft.com...
> Hello
> I am trying to import tables with data from Access to SQL.
> The problem occures when the Primary keys and identity are not being saved
> and i need to do it manually.
> There is a way to import this tables with all the connection regulations
> like Primary key will be saved ?
> thanks
> Gal

importing tables from Acess to SQL

Hello
I am trying to import tables with data from Access to SQL.
The problem occures when the Primary keys and identity are not being saved
and i need to do it manually.
There is a way to import this tables with all the connection regulations
like Primary key will be saved ?
thanks
Gal
We have found that the safest way to move from Access to SQL Server is to
convert the Access file to 2002, then use the Upsize Wizard to move the
schema only. Finally we use the Data Transformation Services of SQL Server
to import the data from Access.
Make sure that all the tables have a key index.
Ragnar
"?" <@.discussions.microsoft.com> wrote in message
news:B93E10E0-1C9B-4C46-83AA-32F90A07E96D@.microsoft.com...
> Hello
> I am trying to import tables with data from Access to SQL.
> The problem occures when the Primary keys and identity are not being saved
> and i need to do it manually.
> There is a way to import this tables with all the connection regulations
> like Primary key will be saved ?
> thanks
> Gal

Importing tables

I am trying to import tables in an Access *.ADP file or using the Enterprise
Manager inteface.
My problem is that all the tables that I import (logging in as DBO) are lock
ed and I cannot update, delete or insert on them. Instead, all of the tables
wich I created using Access's *.ADP files or under the Enterprise Manager c
an be updated, have deleted
or inserted lines.
How can I solve it?What do you want to solve?
you want the tables in SQL server read-only as well?

>--Original Message--
>I am trying to import tables in an Access *.ADP file or
using the Enterprise Manager inteface.
>My problem is that all the tables that I import (logging
in as DBO) are locked and I cannot update, delete or
insert on them. Instead, all of the tables wich I created
using Access's *.ADP files or under the Enterprise
Manager can be updated, have deleted or inserted lines.
>How can I solve it?
>.
>

Importing table question

I am part of a group that is developing a SQL database. When I import the ne
w tables from another server, They primary keys for the tables do not import
with the table. Does any one know why this would be happening?Do you mean the primary key column or the primary key definition
(constraint)? Also, what tool (exactly) are you using for the import?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"jstokes" <anonymous@.discussions.microsoft.com> wrote in message
news:09B8020D-AF6F-4139-8365-B8AD3A965359@.microsoft.com...
> I am part of a group that is developing a SQL database. When I import the
new tables from another server, They primary keys for the tables do not
import with the table. Does any one know why this would be happening?

Importing Stored procedures from one database to another

Hi,
Any one knows how to import stored produre from one DB another?
I imporeted tables and data by using import data wizard.
but i couldnt get the SPs
Can anyone help me?
Hello,
In Enterprise manager / SSMS right click above the dataabase, Tasks,
Genenrate SQL Scripts, Choose all stored procedures
and generate a SQL Script. Open the script and execute in target database.
Thanks
Hari
<parasu0107@.gmail.com> wrote in message
news:1173874578.990775.49490@.e1g2000hsg.googlegrou ps.com...
> Hi,
> Any one knows how to import stored produre from one DB another?
> I imporeted tables and data by using import data wizard.
> but i couldnt get the SPs
> Can anyone help me?
>
sql

Importing Stored procedures from one database to another

Hi,
Any one knows how to import stored produre from one DB another?
I imporeted tables and data by using import data wizard.
but i couldnt get the SPs
Can anyone help me?Hello,
In Enterprise manager / SSMS right click above the dataabase, Tasks,
Genenrate SQL Scripts, Choose all stored procedures
and generate a SQL Script. Open the script and execute in target database.
Thanks
Hari
<parasu0107@.gmail.com> wrote in message
news:1173874578.990775.49490@.e1g2000hsg.googlegroups.com...
> Hi,
> Any one knows how to import stored produre from one DB another?
> I imporeted tables and data by using import data wizard.
> but i couldnt get the SPs
> Can anyone help me?
>

Importing Stored procedures from one database to another

Hi,
Any one knows how to import stored produre from one DB another?
I imporeted tables and data by using import data wizard.
but i couldnt get the SPs
Can anyone help me?Hello,
In Enterprise manager / SSMS right click above the dataabase, Tasks,
Genenrate SQL Scripts, Choose all stored procedures
and generate a SQL Script. Open the script and execute in target database.
Thanks
Hari
<parasu0107@.gmail.com> wrote in message
news:1173874578.990775.49490@.e1g2000hsg.googlegroups.com...
> Hi,
> Any one knows how to import stored produre from one DB another?
> I imporeted tables and data by using import data wizard.
> but i couldnt get the SPs
> Can anyone help me?
>