How do you import a view from one database to another? I tried using the DTS wizard but when I pick the view I want to import, it is imported as a table and not a view. And I don't see anything in Management studio then can help me. Does anyone have any suggestions.
hi,
try generating the DDL script for the desire view, and then run it against the desired database..
regards
sqlI 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
Hi,
I'm trying to import text file (generated by UNIX - collation ISO LATIN 2) into the database using SQL SERVER 2005 Import and Export Wizard. I have got a problem with importing a decimal number, because in that column are not only decimal numbers (that's OK), but there are also spaces (not null, the column is filled by spaces and it looks like | |). When I'm trying import that file, then will occur the problem of truncation and import stops.
I can import that data by BULK INSERT, but I would like to import it by Import and Export Wizard at once without using subsequent conversions.
I *think* you'll have to do this in two steps. One to load that field as a varchar field. Two to convert that data (excluding spaces) to decimals.|||You could use the package generated by the import/export wizard, and the edit it with BIDS to add the logic necessary to convert/replace those column before the insertion...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.
I've tried to import a text file into a table using the Import/export wizard.
My problem starts with some columns that should have NULL values (i.e. zero-length string) but the wizard doesn't recognize that it's NULL.
How do I solve the problem?
Thanks,
Mich
First, a zero-length is not NULL. NULL is a special character.
If you want NULLs to be inserted, open up the package and edit it (that the import/export wizard created for you) and add a derived column transformation before going to the destination.
Use an expression similar to this for each of your columns:
[myColumn] == "" ? NULL(DT_WSTR,20) : [myColumn]
I'm going by memory, but I believe that would work. The above assumes myColumn has a length of 20 bytes.
|||Thank you for your answer.
My knowledge of the import/export wizard is limited.
I would like to know whether the expression should be added in the "Suggest Types" button or elsewhere.
Is the expression related to the destination column names or the columns the Wizard created?
I'd appreciate it if you could give me a step-by-step guide on how to do it, since I found the sql help file very unhelpful.
Thank you again.
The first time we did this it worked fine no problems. However we had
to recreate the database, and it has not worked since.
The error message is reported as ' the log file for 'dbname' is full'.
This happens regardless of the fact that there is 100GB free on disk,
and that the database data and log files are both set to autogrow. The
recovery model is set to simple.
When imported the data.mdf file should be around 20GB.
Would anyone know what is causing this or how to get around this
without going down the SQL7 install, restore and upgrade to sql2000
route?
Any help would be appreciated greatly.Hi
You may want to try attaching the mdf and ldf files from the SQL 7 box onto
the SQL 2000 box and then making any modifications required or restore a
backup.
You don't say how big the log file is. If it is set to grow by a percentage
that value may be larger than the available disk space.
Check out
http://support.microsoft.com/defaul...b;en-us;Q314546
http://support.microsoft.com/defaul...ben-us%3b224071
http://msdn.microsoft.com/library/d...ar_da2_1uzr.asp
John
"John Gill" <pizzaboyo@.yahoo.co.uk> wrote in message
news:a8be1015.0311070405.17c37eb@.posting.google.co m...
> We are trying to import data from a sql 7 machine into sql 2000 using
> import wizard in DTS. One of the tables has in excess of 80 million
> rows.
> The first time we did this it worked fine no problems. However we had
> to recreate the database, and it has not worked since.
> The error message is reported as ' the log file for 'dbname' is full'.
> This happens regardless of the fact that there is 100GB free on disk,
> and that the database data and log files are both set to autogrow. The
> recovery model is set to simple.
> When imported the data.mdf file should be around 20GB.
> Would anyone know what is causing this or how to get around this
> without going down the SQL7 install, restore and upgrade to sql2000
> route?
> Any help would be appreciated greatly.
Hi,
I would like to import an Excel spreadsheet into SQL Server 2005. I can do this quite easily using the Import/Export wizard, and have each row in the spreadsheet transfer to a new row in the database table.
However, I want to import the first few columns of the spreadsheet row into one table (called Products), but put the remaining columns into a related, three-column table, called Product_Details. In the Product_Details table, one column would hold the spreadsheet column value, the other column would be a FK integer value linked to the PK in the Products table, and the third column the primary key as normal.
So, somehow, I would need to get hold of the primary key value when the first spreadsheet columns are inserted into the Products table and then insert the remaining columns into the Product_Details table with two values per row - one value being the spreadsheet cell value, the second being the primary key of the new product in the Products table.
TIA,
Graham.
I would approach the problem by importing all of the spreadsheet rows and then process to the required table.
From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.
Please suggest me to solve the problem
Thanks in advance
Karna
You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.
Right click on a database and choose tasks-> export data
which then lets you copy tables.
I hope that is what you are looking for.
|||Hi,Thanks for reply,
As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.
My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).
Please suggest me to solve my problem
Thanks in advance
Karna|||
Have you looked at the Copy Objects task available in the SSIS designer?
Thanks.
|||Hi,Thanks for the reply.
It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.
Thanks in advance
Karna
From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.
Please suggest me to solve the problem
Thanks in advance
Karna
You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.
Right click on a database and choose tasks-> export data
which then lets you copy tables.
I hope that is what you are looking for.
|||Hi,Thanks for reply,
As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.
My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).
Please suggest me to solve my problem
Thanks in advance
Karna|||
Have you looked at the Copy Objects task available in the SSIS designer?
Thanks.
|||Hi,Thanks for the reply.
It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.
Thanks in advance
Karna
I saw this post by dterrie in the Wishlist thread and I just wanted to second it:
"How about bringing back a simple dBase import. The SSIS guys are clearly FAR out of touch with reality if they think people who handle data no longer need to work with dbf files. I've seen alot of dumb stuff in my day, bit this is just sheer brilliance. I just love the advice of first importing into Access and then importing the Access table. Gee, why didn't I think of such a convenient solution. I could have had a V-8."
I've been struggling with this the last couple days and finally decided to import the dBase III file into Access and then import that into SQL Server 2005. Imagine my surprise when I discovered this was the current recommended method.
That's just ridiculous. Can someone tell me why they would reduce some of the functionality of SQL Server from 2000 to 2005? This was a very easy process in SQL Server 2000...
Philip,
Could you record your request here:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
That way a request will be passed directly to our bug system. It will increase a chance to address it sooner, and you will be informed about the progress.
Thanks.
|||Thanks Bob! That's a great idea.
I know you guys have been catching some flack over the anemic ODBC support.
Here's hoping there is a service pack for it soon!
Hi All,
I have become frustrated and I am not finding the answers I expect.
Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.
So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.
I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good
Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.
When I get to the end it progresses to about 20% and then throws this error about 300 or so times:
Could not connect source component.
Warning 0x80202066: Source - AM_ALERTS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!
Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.
This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!
This generates 2 questions:
1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection
2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.
Help!!!
Hi Irish
I Don't have an answer for you, but since we are in the same situation as you I thought that I would share my thoughts on this.
At first I tried to use Microsoft SSMA tool to do the migration of both schema and data, but soon I came to the conclusion that it still has too many bugs to be to any help in migrating the data. And according to the SSMA team, they won't release a new version until they have rewritten the current java version to .Net.
So I tried the "Import Data" like you did. But because of the AlwaysUseDefaultCodePage issue and because I need to change the Oracle schema to dbo which I can't do for all tables at once in the import wizard, I have moved to the SSIS package solution like you have.
Creating a Data Flow task 600 time (a little less in my case though) is not what I had planned to use a week for so I will now try to create the SSIS package programatically.
I have found this documentation:
http://msdn2.microsoft.com/en-us/library/ms135946.aspx
about how to create a dtsx package in code which I will try to do the next days.
I'm sorry that I wasn't for much help, but hopefully we can find a solution to our mutal problem together.
Best regards
Claus Pedersen
Claus,
Thanks for the reference. I will try that block of code as well, but I'm not optimitic.
What ever happened to "DTS will be easier in 2005"?
|||Generally I like the new features of Sql 2005 SSIS, but I agree that for tasks like this they still have a lot to do. I don't think that Sql 2000 would be easier.
Did you try to create a SSIS programmatically?
I'm working on it at the moment, but get some strange errors with the connection. It fails with a HRESULT: 0xC0048021 error as soon as it tries to connect to a OleDb connection (to populate columns). I have tried to load a working package in my code and proceed with that, but it still fail.
Best regards
Claus Pedersen
Hi Irish
Have you tried to use the ForEach control?
I have experimented a little with it, and it seems like it could work. There are some problems with update of column names though.
Claus
|||Hey Claus,
I have not had a single oppourtunity to work on this since making the last post.
I agree that SQL 2000 would not have been any less of a challenge. However, based on the documentation I've read this is supposed to be a simple process allowing a full conversion of an Oracle Database to SQL 2005. I've not seen it so far. That's the whole point of my excursion.
I will try the ForEach and see what I get. Just seem like a major pain to have to go through all of the objects piecemeal.
|||I agree. I can imagine a developer on Microsoft Sql server team telling his boss that "it could be worse" which converted to marketing language is "extreemly simple".
Wait a moment with the foreach solution. It might not work anyway because of the different metadata from table to table. I'll keep you updated.
/Claus
|||Run through all of the steps, but uncheck Execute Immediately, and check Save SSIS package. Save it to the file system, and then edit the AlwaysUseDefaultCodePage property.
Or are you saying it won't even create the package for you?
|||Sean,
Yes, that's exactly what I am saying. When I was building this the plan was to create the SSIS Package and then see how I could edit it to apply to a variety of different Oracle Instances and versions.
So, I would change the AlwaysUseDefaultCodePage property if I could, but since the package is never created because of the messages received I am not able to do so.
