1. i want to transfer the .csv file data into sql server table, i
tried with the DTS but while creating DSN it not prompt to attech
the .csv file. give me the proper steps to perform the data
transfer...
2. i want result of my query into excel or text file by using the sql
query( like Select * from employee where emp_salary>10000 to 'c:
emp.xls).i know the other way right click into query analyzer window
and select option result to file, but want the result by using SQL
query.....
hi all,I am trying to create a dts package to transfer data from tables in mysql server 2000 database to a db2 UDB v8.1.4 database. When I use ibmdb2 oledb driver for the destination this works fine except that rowsare committed on a row by row basis thus performance is slow. I wantto use the odbc driver but when I define it as the destination, i geterrors saying that the maximum number of rows failed has exceeded theminimum.Does anybody know what to do in order to get the transfer workingusing ibm db2 odbc as the datasource?ThanksLyn
Hi, I have a Users table in Oracle database and same table (Users) in SQL Server 2000 database. I want to create a DTS Package through which I can copy the data from Oracle database to SQL Server 2000 database. This package should run automatically at mid-night daily so that if there are some entries done in Oracle database then it get copied in SQL Server 2000 database. Also is there any way to copy only those entries from Oracle database which are not present in SQL Server 2000 database. Please help me in this regard as I am new to DTS.
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?
Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett
We are planning to upgrade from sql server 7 to sql server 2000.The sql server 2000 will be installed on another server. If we have to move back from sql server 2000 to sql server 7 (if there are any problems on sql server 2000).is there a way to do it? Is there a way to restore the changes made on sql server 2000 to 7?
I am trying to copy a database from sql2000 to sql2005 but it is giving me the following error: To connect to this server you must use SQL Server Management studio or SQL server management objects (SMO)
i have a old database in foxpro and it has to be converted to sql server 6.5 database . the table in the foxpro has been broken into more than 1 tables in the sql server . so how can i transfer the data from 1 foxpro table to different tables in sql server 6.5. vineet
Hi, I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this.. Insert into SqlServer2005comp.Databasename..TableName ( Columns ) Select Columns From sqlserver2000comp.Databasename..tablenames Where UserId = @UserId. Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db any help or ideas will be appreciated. Regards... Karen
Hi All! i have a database in SQLEXPRESS 2005 but i need to this Database in SQL 2000...there is any way to move data from sqlexpress 2005 to sql 2000.... please help me.....
As per the querys: ------------------------ 1) I am trying to use the Database of my host (hosting server of datapacket). They have a support for MS-SQL-2000. Therefore surely the server is there. After my CPanel configuration i got the Database Server IP (67.122.X.X), Port - 1433, SQL Server user and pasword.
2) In my local machine SQL Server 2000 is installed. Where the databse and DNS is configured.
Now, from this local machine how can i transfer the data to the host machine (remote SQL Server).
Simpliy copying Files *.MDG and *.LDf files it is not working. And i don't have access to the remote SQL-SERVER as it is in the host machine of datapacket. I can only configure it throuch there Control Panel and throuch FTP can copy the required files in there machine. I have a user in that remote MS-Sql server that all i have.
As per my understanding after the control panel configuration the database and the DNS is created in the remote Sql-Server of datapacket. Now all i have to do is transfer the data & tables. HOW TO DO THAT?????
I have tried using Export but it fails to connet the remote database.
I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this..
Insert into SqlServer2005comp.Databasename..TableName
(
Columns
)
Select
Columns
From
sqlserver2000comp.Databasename..tablenames
Where UserId = @UserId.
Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db
To extract data from an ODBC source, try the following:
Add an ADO.Net Connection Manager. Edit the Connection Manager editor and select the ODBC Data Provider Configure the Connection Manager to use your DSN or connection string Add a Data Flow Task to your package. Add a Data Reader Source adapter to your data flow Edit the Data Reader source adapter to use the ADO.Net connection manager that you added. Edit the Data Reader source to query for the data you wish to extract.
hth
Donald
Using the steps outlined above as described by Donald Farmer in another post on this forum, I have created an SSIS package which retrieves data from Lotus Notes 6.55. The DSN referenced by the ADO.Net Connection Manager connects to Lotus Notes via the NotesSQL ODBC driver 3.02g.
When I execute the dataflow, data is transferred from Lotus Notes, but the data transfer rate is extremely slow compared to SQL 2000 DTS. In SQL 2000 DTS, we can retrieve just under half a million records from Lotus Notes in about 13 minutes. Utilizing the same DSN on the same machine, SQL 2005 SSIS completes the transfer in about 57 minutes.
Is there anything that can be done to improve the performance in SSIS to retrieve data from Lotus Notes via ADO.Net ODBC?
Hi All, i have mutiple text file. let us say,a1.txtb1.txtc1.txt i have to port this text file data into the table (SqlServer Database) which have the same file structure.(i.e)x1 (SqlServer table)y2 (SqlServer table)z3 (SqlServer table) now i have to transfer a1.txt file data ----to--- x1b1.txt file data ----to--- y2c1.txt file data ----to--- z3 using SSIS. like that, i have to transfer more than 250 files at a time.manually binding 250 files into the package is very cumbersome and time consuming process. so, can any one give ur valuable sugession to solve this issue.
Hi All, I am trying to import data via DTS from a CSV file. I have the "empty" tables already created with proper column names. Now, I have a subset data of each of these tables, which I am trying to import. I am facing problem in that, there is a datatype conflict between the source (CSV file) and destination (table already in the DB). All the data in the CSV file appears to be of DBTYPE_WSTR, where as in the table it is different (some are DBTYPE_WSTR, some are DBTYPE_DATE, and so on). Is there a way that I can import data successfully? This has become a work stoppage issue now. I had to actually go for this approach of creating the empty tables first and then importing data because, the backup file was very very large and could not be copied to our domain. Please help me out in this.
Can anyone please help me on how to export data from SQL server 2000 to text file using C#. I could use bcp command to directly import data, but there are some changes need to be made to some codes from other tables in database and the data to be downloaded is also very huge. probably 10 million records.
Hi I'm going to put my database in setup file that I've been ready before. My Software could be use from database after installing in every system , but other users won't be able to access to data or structure of database , even with adding of database to SQLServe Enterprise Manager . How Should I can do about this ? Tanks very much
How can i transfer a database from Access to Sql Server 2000?
Exactly : i want to transfer a .mdb into .mdf, and i want to keep the structure of .mdb, a structure which have tables, queries, macros, modules... When i start the transfer with Data Transformation Services ( DTS- Import Data ) , i can see that DTS shows me only tables and queries. At the end of transfer, in my .mdf database, on server, i have only tables, without queries, macros, modules.
My question is how can i transfer the queries and macros ( exactly how are in .mdb ) to .mdf database?
I'm having problems designing a package to attempt to execute a fast load data transfer but failback to regular speed with error redirection in the event of an error.
The way I designed this was to add one data flow task to my package called "DFT FASTLOAD". The data flow copies a table SRC to another table DEST in the same SQL Server database. In the error handler for the data flow task I copied the original data flow task and changed the name to "DFT REGULARLOAD with Error redirection". In this data flow task I did not use fast load and addtionally redirected errors to a text file.
In the Data Flow Task "DFT FASTLOAD". I am copying from a varchar source field(with non-date strings) to a datetime destination field to force errors. However the Data Flow Task "DFT REGULARLOAD with Error redirection" never seems to start transferring data from source to destination. The data Flow Task "DFT REGULARLOAD with Error redirection" turns yellow (after the error occurs in "DFT FASTLOAD"), but no data is being transferred). It seems like it hangs.
Do I need to increase the MaximumError Count or something? The data flow task "DFT FASTLOAD" does not turn red when the error occurs it just remains yellow, so i assume I'm on the right track since it seems the error is caught.
I have added screenshots ... hopefully these screenshots will clarify my problem.
what's the easiest way to do this. i have 5 tables within the mdb filei tried the "easiest way" shown in a website but it does not work.the way i did it was on access(2007) > database tools tab > sql server buttonit gave me an error when it ask for an login IDi do not have any password/id..please helpthanks
I am trying to transfer 200 txt files into SQL server by using query analyzer. The command is 'Bulk insert [tableName] from 'pathfilename.txt' However, I need to read and modifiy the txt file. I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily.
Thank you for the help in advance!
Here is the raw data layout, which is comma delimited. BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Fq D Fq D Fq D Fq D Date R P M E D Date R P M E D Date R P M E D Date R P M E D 1/1/90 1 2 3 4 5 1/1/90 2 3 4 5 6 1/1/90 3 4 5 6 7 1/1/90 4 5 6 7 8 2 3 4 5 6 1 2 3 4 5 3 4 5 6 7 6 7 8 9 1 1/1/05 ...... 1/1/05 .... 1/1/05 ..... 1/1/05 .....
This is the desired output after load into the table, which is tacking each repeating block on top of each other. Date R P M E D 1/1/90 1 2 3 4 5 2 3 4 5 6 1/1/05 ...... 1/1/90 2 3 4 5 6 2 3 4 5 6 1/1/05 ...... 1/1/90 3 4 5 6 7 3 4 5 6 7 1/1/05 ...... 1/1/90 4 5 6 7 8 6 7 8 9 1 1/1/05 ......
My vendor requires data to be sent in Excel format. Some of my tables have rows over 65,536 so I need to use Excel 2007 (Max of 1,048,576). Right now my data sits in SQL 2000. I am using MS SQL Enterprise Manager 8.0 to prepare the data. Is there some kind of add on or selection I am missing to use DTS to export from SQL to Excel 2007?Thanks in advance.
I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
I need to transfer dbf file to sql server 2005 express edition with some periodic interval. Can any one please recommend which is the easiest and efficient method to do?. Like polling every 5 to 10 seconds transfer data to sql server 2005 ex edition. Is it recommend to do it visual basic program?, how to do it. pls help
Is it possible/advisable when transfering very large amounts of data from server to server to: trasnfer the data to a new table first second alter new table adding indexes, defaults, ets based on original table
if it is what flow item would be used to transfer/alter the indexes and defaults?
I'm very new to ssis so the more detail you can give the better.
Please give me a direction where to go to solve this problem... our organization regularly pumps data from an AS/400 database (db2) to SQL Server 7 tables with the help of DTS. Some of the data transfers are straight column copy and several are ActiveX transformations. Also, we've got some of the transformations defined directly inside the select statement in the Source tab of the DTS designer. There is one stored procedure which collects and does computations with the data to put into a number of Excel reports. The strange thing is that some of the data seems to be getting corrupted somehow as it is transferred, because the resulting data in sql server is not identical to that which was transferred. We get outrageously high values for one of the columns with no apparent pattern. There are no error log entries for this, and we've already confirmed that all the transformation scripts and stored procedure functions that we've been using are correct. Would doing a server trace be of any help, and if so what should I be looking for and could you just brief me on how to start one, since I've never had the need to do one so far. Thanks for the help in advance.
