Transfer Specified Data From One Server To Another
Apr 10, 2008
New to sql scripting and am having a problem. I have two SQL servers:
1. Keeps track of all sales related activities.
2. I would like to use for backup and reporting of sales only.
I am wondering if there is a script that can be run to transfer only specific data from the first server to the second, and if so what is the setup and syntax.
I am looking to do something like:
insert into [database#2] [table]
* from [database#1] [table]
where database#1-table.result='Sale'
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?
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.
hi can somebody help me out on this.. is there a way to consolidate the data in different Sql Server 2005 database servers scattered at different locations into a single database without using REPLICATION.
p.s Data transfer should also be conditional and bi-directional
Ive have a website on SQL Server 2005 on my Localhost, that I need to transfer to a live test server, in MYSQL you can use dump to get the SQL script,can I do this in SQL server? If so how? if not how do I transfer the DB?
We are developing a data migration tool that transfer data from the live sql server (used by customer) to another sql server. we use SSIS packages to perform the migration. we have a engine (developed in c#) that reads the SSIS package and executes it. Now, as the source sql server is live, it is certain to undergo changes during the data transfer operation. Changes would mostly be insert or update. After the data migration is completed the delta has to be taken care of. I am looking for an efficient mechanism to transfer the delta from the live sql server to the target sql server.
HI, Recently i copied data from one server to another server. Later i checked the data too. It was fine. I checked no of tables, views ,sps, users , roles and also permissions and no of records in each table. Every thing looked fine, but my users are saying data got meshed up. I dont know why they are complainting like that? Can anyone tell me why it is happening. Did i made any mistake while copying data. i copied data using bcp. Before that i copied structure without data using DTS. pls tell me. thank u.
i have an old database in foxpro. The table in foxpro now has been broken into more than tables in sql server 6.5 . how do i append the data to sql server database to the respective tables from the foxpro database. vineet
I'm looking for some advice. We have multiple remote SQL Server 2005 systems at various clients that all contain the same WorkTrack database. We need to send this data on a daily basis from all of these remote servers to our main server (ChemWeb/Spindle) in our corporate office. The thing is that we need to do this over standard HTTP - no VPN connection, etc.
So I'm thinking of using SQL 2005 Web services to do such. Here's my plan (the name of the Database on the client side and server is WorkTrack, and SDP is the acronym I am using for a Spindle Data Packet which contains the data to be updated from the client)
1. Every day update a WorkTrack_SDP database on the client server that will contain all of the data from WorkTrack (name of Spindle Client Database) that has changed since the last SDP transfer. This WorkTrack_SDP database will only contain the tables from the WorkTrack database that are necessary for transfer to the ChemWeb/Spindle server
2. The WorkTrack_SDP database will have the same schema as the WorkTrack database with one additional Customer_No column in each table.
3. All of the tables in WorkTrack_SDP database will be truncated before insertion of updated data.
4. An acknowledgement of the previous SDP transfer and ChemWeb/Spindle Server update will be required before this process will proceed.
5. There will be an additional table in the Client WorkTrack database to track SDP transfers. a. Date/Time range of updated data of each transfer b. When the transfer was sent c. When the transfer was completed.
6. Create SPROC’s on the ChemWeb/Spindle server to serve as SQL Endpoints (Web Services) to update/insert data from client systems to WorkTrack_SDP database on the ChemWeb/Spindle Server.
7. Create SPROC on ChemWeb/Spindle server that will transfer data from WorkTrack_SDP into WorkTrack
I am new to SQL server world. I have a task to transfer data from excel sheet(s) to sql server. I read I could do that using DTS.
But the problem is I have more than 1 excel files and in my table I need to get records from both the files with out duplication the data. ex: one excel sheet conatins field 1,2,3 second sheet contains field 1,4,5,6 (now I should put data in the sql table as 1,2,3,4,5,6)
I will have to continue to do add data to this table as I get new excel sheets (the fields will remain same).
Can somebody help me with this? If you can also give me some references for this it will be very helpful.
I have a live server running windows Nt and sql server We brought a new backup server of higher configuration we wanted to swap the data we installed nt and sql server in the new server will a cut paste of the data directory of the live server to the backup server is sufficient or the data directory to be restored to the respective files through sql enterprise manager please do clarify me
When executing an inline insert into command from isqw/w the command executes perfectly. Yet when I try to create a stored procedure with exactly the same insert into statement it will not create. It does not like the database.owner.table prefix on the selected rows. Any suggestions ??
I'm new to SQL server, and I would like to achieve below tasks. Kindly provide brief guildline on how to achieve these:
1.) I have managed to schedule job in SQL server to download the access database from remote server. 2.) Second, I would like to transfer all the data from my acccess database to my SQL tables.
May I know how do I achieve my task no 2???? I believe I need to write some script (procedure) in order to achieve this.
Hi All,1. i want to transfer the .csv file data into sql server table, itried with the DTS but while creating DSN it not prompt to attechthe .csv file. give me the proper steps to perform the datatransfer...2. i want result of my query into excel or text file by using the sqlquery( like Select * from employee where emp_salary>10000 to 'c:emp.xls).i know the other way right click into query analyzer windowand select option result to file, but want the result by using SQLquery.....
Hi all, Thanks in advance, I have to transfer data from sql express 2005 to sql mobile(.sdf).
Actually i have a sql server express 2005 (sqlee) in laptop... and the .sdf (sqlce) handheld device (ppc) that has been connected by activesync.
Can you please make it clear for my following questions? 1. Is it possible to write a query like " insert into ....sdf select from...sqlexp...." between 2 servers...? 2. Is there any copy command like a BULKCOPY .. to insert all records to .sdf (sqlce) from sql server express?
(or) 3. Is it correct to create a smart device application (CF 2.0) to transfer data between sql express and .sdf by using active sync...?
I want to transfer data between an sql server 2005 and my pocket pc. So i've installed a sql server CE on it.
I know that i can use the merge replication but, i don't have any IIS. So, i've got to transmit data though email that have got a file attached. I Know how to email from sql server 2005. When the pocket receive that email, i don't know how to insert the data in sql ce from outlook compact. Doi have tu use SSIS , in that case how can i run the package?
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
Our mobile application using VS2005 with VB.net is ready and we are trying to set up the SQL CE on the server to test the remote connection procedures. Sql 2005 is based on a dedicated server and is accessed via the Web server which does not have Sql installed on it. We have installed the Sql Server Me Server Tools on the Web server and configured the Virtual directory with IIS. What we cannot get our heads around is how we link the database on the separate Sql server via the web server. When the Pda contacts the Web server via IIS we somehow need to redirect to the dedicated Sql server as it cannot be contacted outside of our network. We have been told that Sql Ce can link with an existing Sql 2005 database, how can we do this?
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?
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.
I have a task as creating a SSIS package to transfer all the 10 tables from a database to another. And I have used the Transfer SQL Objects Task to select a table list and let the component do the transfering.
But my problem is that the source database have bad data and some null data, so I have to find a way to transfer only 'NOT BAD' data, and remove the bad data (lost relationship) and change NULL to "N/A".
And I can't find a way to do this. Is there anyone have experiences with this problem?
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
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?
