sql server 2005 express edition,
sql server management studio express,
oracle tables (original source data currently linked through odbc)
adp database file (new db where i'm trying to import the above oracle tables)
i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.
what is the fastest option? some of these tables hold over 1m records.
i have also heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.
I am trying to import 6M records in a table from an Oracle 10g databaseinto a SQL Server 2000 database.After importing 1.5M+ records I get the following error:Ora-01555: snapshot too old: rollback segment number 129 with name"_SYSSMU129$" too smallHow can I speed up the import process between Oracle and SQL Server?The database box I'm using has 12GB of memory and 1.5 TB of space. Itshouldn't be a hardward problem. :-)Thanks in advance.
I am trying to import a variety of data from an Oracle 10g database. When I import the data using the OLE DB it takes a long time (100K records an hour).
Hi all, Please let me know if it is possible to import an Oracle .dmp binary file into SQL using DTS. DTS has ODBC and OLE DB drivers for Oracle but both require specifying the Oracle database name, user name and password. i.e., it looks like the only way to import the .dmp file into SQL would be to first load the .dmp file into Oracle and then tranfer it from Oracle to SQL using DTS. Is there any way to load the .dmp file directly into SQL? The .dmp file is a binary file that has been generated from Oracle using the 'Export' utility.
Hello all, I hoping to get some input on how to get around this odd problem. I use the SSIS Import Wizard to create a package that imports data from an Oracle 9 database into SQL 2005. One of the 'columns' selected from Oracle is actually two floating point columns multiplied together.
ie: SELECT one * two FROM table
In the SSIS wizard the data preview for this shows up as it should, with the column data in floating point form. However after actually importing the data, all the numbers get imported as integers even though they are going into a floating point column.
Using the DTS Wizard, on the Choose a Data Source screen, I pick the following:
Data Source: Microsoft ODBC Drive for Oracle Server: Username: Password:
I don't know what I should put into the other fields, given what I have above, i.e. what to put in for Server, Username, and Password! Or should I use a different procedure to import this Oracle data? I am almost positive I was not supplied the username/password. I was told I could connect given the above TNS information. What do you all recommned?
I am trying to import application tables from oracle but they do not show up in the list of tables. I suspect that this is because the tables are owned by System. Is there any way other than changing the owner to get these tables to appear in the import list?
Hi, I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.
But after Importation it is showing Junk values. Please advise me what step I should take next.
OK so there is some data in an Oracle DB that I have to summarize based on grouping info stored in a SqlServer DB. How can I import the Oracle data into a SqlServer temp table using SqlServer Express? Thanks.
I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
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.
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.
Hi All,Here is my code Dim strCon As String = System.Configuration.ConfigurationSettings.AppSettings("OwnerTrader") con = New SqlConnection(strCon) con.Open() Dim strselect As String = "" Try strselect = "INSERT INTO tbl_CSV(CSV_TIME,CSV_SIZE,CSV_LOCATION,CSV_COUNTRY,CSV_LAT,CSV_LON,CSV_COMMENTS)" strselect = strselect & " SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:UploadFiles;Extensions=CSV;','SELECT * FROM TestNew.csv')" cmd = New SqlCommand(strselect, con) cmd.ExecuteNonQuery() con.Close()i have defined connection string in web.config file and my csv file is inside C:UploadFiles with the name TestNew.csv file.Can u please check it out the code?is it correct or not.I am getting this error"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online."Regards
I use ms sql 2000 and i wanted to import a new backup database into a new ms sql 2000 on a different machine but it does not work so i wanted to know if it can be done or i have to do something else thank u.
As a newbie, I'm having problems importing a copy of my SQLServer2k development database from my laptop (via a CD), to my production SQL Server2k database.
I've tried the DTS Import/Export wizard & also the Copy wizard, but can't seem to see the dialog that prompts & identifies the source path & filename.
I've tried setting up an ODBC DSN for the database, but get the same problem.
Coming from an Access background, I've been used to the copying of *.mdb files, and half expected that level of simplicity with SQLS2K - but clearly, I'm wrong and am missing something, which the on-line help isn't able to clarify for me...
Can anyone please give me any pointers for me to get a copy of my development database along with its stored procedures, from my laptop (not networked), to my desktop server. Both use SQLS2k.
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
SQL 2000, Windows 2000. I need to find a way to export our data fromour production database and import it into a test database. I can doit to a freshly created test database I just have trouble doing itonce the test database is already populated. Is there a script I canwrite that will automate it by1) Deleting the test database2) Creating a new one3) Exporting the production tables and views4) Importing into the new test databaseWhat are my options here?Thank you in advance.
Hello Everyone, I would like to import a text file which contains one string (a large integer) per line not separated by commas or anything else except a carriage return. Does anyone know of an easy way to store this in a database file? I'm open to suggestions if there is more than one way to save this kind of information within a database. I have SQL server 2005 developer edition if that helps in any way. I'm also starting to learn about Linq so if there is some other way you would store this information for that purpose I would love to hear about that as well. C# code is preferable, but I can use the automatic translators if that's all you have. By the way, I'm a newbie to this subject (if you couldn't tell). Thanks in advance. Robert
Hi, i have a new site built in .net using the login/membership system. i have a legacy database (access) of users from the previous site with email, name, password, etc.Is it possible to import this data straight into my new membership tables through some insert statement, software or through some other way?
Could some1 pls help me, how do I import a word doc into a sql server database, and it mus t be displayed in a panel I need to know how to do it, step by step
Does anyone out there have a good example of a VB Application for importing data from an Access DB into a SQL Server DB using DTS. Any help with this would be greatly appreciated.
I am trying to import tables from my access database.
1st trying simple table, In ent mgr i right clicked the db folder all task import data data source : Microsoft access file name : c:Data Management SystemDMS.mdb User name : mike (same used for lonin in access) password : (same used for lonin in access)
advanced button: jet Oledb: System database c:Data Management SystemSecured1.mdw
windows authentication server (local) database New -> dms
copy tables
now i see the items in my access database so i am guessing every step so far was correct
hey it worked !
ha,, hope u dont mind if i leave this here for anyone else, i ansd my own ?
Has anyone done this before, I have been given some flat files for a Sybase database to import into SQL server. Yet SQL server doesn't want to know. I have a .db and .log, but SQLserv er doesn't like either of them.
However I'm not sure what to do, I have been given a .db and .log . but SQL server doesn't like either of these, is their something I should do to make it compatable? First tie I've ever look at Sysserver so please use short words, Thanks Ed
I need to import data from excel to sql database. I have an excel that has a column called dept_name that is a string. In the database I have a table called tb_depts with the following columns: dept_id int, identity dept_name nvarchar (200)
In addition I have another table called tb_extUsers with the following columns: user_id int, identity user_name nvarchar (200) dept_id int
I need to import the excel into the table tb_extUsers however my problem is that the value in excel is the name of the department and not the department id and the column in tb_extUsers requires the id. How can i insert the correct dept_id into tb_extUsers ?
Can someone please assist me?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
Hey guys,I am trying to import data from an Excel spreadsheet into my SQLdatabase. I am running SQL 2005.I following Microsoft's instructions for creating a linked server, andit appeared to work. However when I run this query:SELECT * INTO test FROM OPENQUERY(EFORMS,'SELECT * FROM [Form Tracker Baseline$]')I get the following error:The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"EFORMS" reported an error. The provider did not give any informationabout the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider"Microsoft.Jet.OLEDB.4.0" for linked server "EFORMS".Test is the table that will receive the imported data. Eforms is thename of my linked server, and Form Tracker Baseline is the worksheetname.The error sounds like it can not locate my spreadsheet. Any ideas whyI am getting this message? Is there an easier way to do this import?Thanks,
How to check if a record is already in the database I want to import to before importing?
My table have 2 primary key, and the DTS is suppose to be schedule to run every night, though it can be run whenever a user want to get the most updated data.
Hi, I am very new to ssis, I am trying to set up a package that I can set to run every hour or so, what it will do is look for all excel files in a certain folder and import them into a table on an sql server.
I managed to get it working but my problem is that my data is survey answers and some columns contain a comment. I get these files on a weekly basis and some weeks the length of the longest comment makes ssis want to use a different data type for the comment column (sometimes it wants DT_NTEXT, other times it wants DT_WSTR). as this column is filled out by a human I guess aposrtophies and other characters may affext this as well.
when I made the data flow task, I used the wizard on a file which contained about 8 weeks worth of data. when I use 1 weeks worth of data where the comment length is very low, the task gives a validation error saying the metadata is out of sync. if I go back and set the data type for that column to DT_WSTR and rerun the task, it works but then when it tries to process a different weeks worth of data it will fail again
here is an example of an error I get when it thinks the data type is wrong. [Source - 'raw data$' [1]] Error: The output column "Question ID50# (Verbatim)" (439) on the error output has properties that do not match the properties of its corresponding data source column.
I played around with the data types for a while and managed to get it to process the first file and then try to process the secondfile, in the second file it got around the validation but then got this error: [Source - 'raw data$' [1]] Error: Failed to retrieve long data for column "Question ID3# (Verbatim)".
is there a way to make it recalculate the data types by itself for each excel file?
I am stuck trying to figure this one out. sorry if I havent provided enough information, I am not sure which direction to head with this
This may be a simple question with an obvious answer, but I'm fairly new to databases and I can't seem to find the right procedure.
I created a database on my machine. If I want to copy the information from this database (columns, tables, and entries specifically) from my machine to another SQL server on a different machine, what is the best way to do this without manually re-entering all of the information?
Is there a simple export/import of a database for transferring it from one server to another?
I am trying to complete a lab in the Microsoft self-paced training kit, Developing Web Applications with Microsoft Visual C# .NET.
They have provided an Access database named Contacts.mdb and a batch file named InstContacts.bat that uses InstContacts.sql to import the data.
I have installed SQLExpress, but when I run the batch file I get the following:
C:Microsoft Press...data>rem The following command line installs the Contact SQL database
C:Microsoft Press...data>osql -i InstContacts.Sql -E [SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQL Native Client]Login timeout expired [SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
