EM Transfer Manager Fails Moving Data After 80th Table?
Sep 14, 1999
Hello:
I am running on mssql 6.5, sp4. We have been trying to use EM transfer manager to move one test database on one server to another database on another server.
We are dealing with 135 tables on this database. The transfer works up until about the 80th atble and then just dies but the scheduled task says it failed and check error log. The transfer creates the tables on the destination database but only loads the data until this one table.
WE use all of the options in EM Transfer manager which are st as defaults.
THere is no one on the source or destination databases locking this table.
Other smaller databases were successfully transferred from one database on one server to the other database on the other server without any problems today and yesterday.
Has any one run across something like this?
THanks.
David Spaisman
View 1 Replies
ADVERTISEMENT
Aug 10, 2006
Hi
We used to used Sql Server 2000 DTS to perform daily backups to remote servers very successfully but just cannot do the same with SSIS.
I set my Transfer Sql Transfer Objects Task to use the following settings:
CopyData = true
ObjectsTocopy = All Tables
As soon as it hits a table that has a foreign key, I get the following error:
"Truncate data failed for Table nnn"
I've tried setting DropObjectsFirst = True but this fails too with foreign key violations.
First of all, am I doing something obviously wrong? Is SSIS not smart enough to drop tables in the right order so that we don't get foreign key violations?
Am I totally barking up the wrong tree with this and can anyone suggest a better way of doing remote backups?
Kind regards
Ben
View 3 Replies
View Related
May 30, 2008
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.
Thanks
View 5 Replies
View Related
Oct 22, 1998
I am using transfer manager in SQL 6.5 to copy a database and
all objects with data to another server. Transfer manager is not recreating all stored procedures. This even happens when I used it to another database on the same server. Any ideas??
View 3 Replies
View Related
Feb 16, 1999
Hello:
I have been trying to run transfer manager to transfer all of the data
from the production database on one server to a test database on another
server(to refresh it). In order to make sure it runs on the server, I have
been scheduling it under EM to do so and I am pointing to the log on the
destination server on the EM Transfer panel.
For some reason, I am getting the following message in the destination
server error log:
99/02/16 10:24:41.42 ods Error : 17824, Severity: 10, State: 0
99/02/16 10:24:41.42 ods Unable to write to ListenOn connection
'.pipesqlquery', loginname 'sa', hostname 'TEMP09'.
99/02/16 10:24:41.42 ods OS Error : 232, The pipe is being closed.
99/02/16 10:24:41.42 spid17 Error : 1608, Severity: 21, State: 2
99/02/16 10:24:41.42 spid17 A network error was encountered while sending
results to the front end. Check the SQL Server errorlog for more
information.
I checked the event viewer error log and see no messages for today.
Can any one advise me what I need to do for this to run successfully?
Thanks. Any information furnished will be greatly appreciated.
David Spaisman
View 1 Replies
View Related
Jan 14, 2014
What is the best way to transfer data from the staging table into the main table.
Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
View 9 Replies
View Related
Jun 9, 2013
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?
View 5 Replies
View Related
Nov 15, 2006
I have an excel sheet that contain colummns as in a table in a sql database i want to transfer this data from the sheet to the table frombusiness logic code layer not from the enterprise manager by wizardwhat can i do?? ...please urgent
View 1 Replies
View Related
Nov 26, 2015
I am newbie to MDS of SQL,want to know how we can transfer tables from two different SQL Databases to MDS.Suggest me the steps to proceed with any examples.
View 2 Replies
View Related
Sep 7, 2005
I need to transfer data from one table to another.I will be using the SQL Query Analyzer to do this.This is not a simple transfer of data to the same structured tables.These tables are completely different, for the most part.For instance, I will be selecting certain fields of one table.....SELECT fldOne, fldTwo FROM someTableI need to take this information, one row at a time and input it into a different type table.So, something like this to insert into the other table...INSERT INTO otherTable( fld1, fld2) VALUES( value1, value2 )I've looked around for a sample to achieve this, but may have overlooked it?Anyone have a link to show this or a quick sample?Thanks all,Zath
View 3 Replies
View Related
May 27, 1999
Hi all,
This is (probably) a simple question, but I'm new to SQL Server scripting.
What I want be able to do is move data from one table in a database to another table in the same database, once one of the fields (a date field) reaches a certain value.
Specifically, we are inserting rows into a table that are stamped with an insert date and an expiry date. When the expiry date is reached, we want to move the applicable row from the original table to an identically structured table.
Ideally, we want this to be a script that is run daily.
Does anyone have any ideas or examples that we could use.
Any help is much appreciated!
Tim
View 3 Replies
View Related
Nov 10, 2004
I imported a little over 9 million records into my database without "cleaning" the data.
I'm looking for suggestions on what would be the most efficient way to get all the fields to the correct types and to insert decimals on the fields that need them.
My only idea thus far is to make another table with the correct field types and append the data to the new table.
View 1 Replies
View Related
Jan 30, 2008
for bcp in,
1. use fixed length format file or delimitered file?
2. table w/o index including primary key?
3. sort the text file before bcp in (will it speed up indexes creation after data uploading?)
which pt will or will not improve the overall bcp in processing?
thx...
View 4 Replies
View Related
Dec 9, 2007
Hello,
Just requesting for help about transferring data from one table to another.
For example:
In Table1, there are 3 columns:
Col1 Col2 Col3
T1C1R1 T1C2R1 T1C3R1
T1C1R2 T1C2R2 T1C3R2
T1C1R3 T1C2R3 T1C3R3
T1C1R4 T1C2R4 T1C3R4
In the target Table2, Col1 should be a string constant,e.g. "A" and Col2 from Col1 of T1 and Col3 from Col2 of T1:
Col1 Col2 Col3
A T1C1R1 T1C2R1
A T1C1R2 T1C2R2
A T1C1R3 T1C2R3
A T1C1R4 T1C2R4
I found something like
"INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1"
but this code requires colomn one of the table 1 be selected as well but the column one of the
target table 2 requires a constant string.
I also tried this but doesn't seem to work:
Code Block
dtTable1 = ds.Tables(0) ' Table1
bSQL.Length = 0
For Each dr In dtTable1.Rows
bSQL.Append("INSERT INTO Table2(Col1, Col2, Col3) ")
bSQL.Append(" VALUES(")
bSQL.Append("'" & "A" & "',") ' Constant for column 1 of Table2
bSQL.Append("'" & dr(0).ToString() & "',") ' All data in the first column of Table1
bSQL.Append("'" & dr(1).ToString() & "')") ' All data in the second column of Table1
.......
.......
Next
.....
.....
Thanks,
K
View 3 Replies
View Related
Apr 21, 2008
Guys
i have two database on my sql server,, and i m trying to create a report in whch both database need to share their data.. @ the moment what i did, i simply create a view on one database to access the table of other database.. but is anyone has a better idea how can i transfer data from one database to another database... i think if i do with creating temp table that might resolve this problem.. but when it comes on another server of another database , how can i will do this ...
please give me any idea if u got my point .
Thanks and looking forward.
View 5 Replies
View Related
Feb 19, 2008
hi iam new to writting stored procedures
so anyone please help me out.
i have to transfer data from one table to three other new tables.
and if there are any duplicates in original table i have to send them to
duplicates table.the remaining data should be send to three other tables.
so can anyone help writting stored procedure for this.
thanks for your suggestions and answers
regards
ramya.
View 20 Replies
View Related
May 1, 2008
Hi,
Here is my scenario simplified.
tbl1
userid, secondaryid
tbl2
userID, secondaryID
tbl1 is already populated. I just want to transfer the secondary ID to tbl2 based on the userID. I don't want to store secondaryID in tbl1 anymore.
View 4 Replies
View Related
Oct 20, 2013
I need to move data from one table to another using variable percentages and business days.
Here is the basic idea:
Variables: varTable1, varTable2, varPercentage
Get varPercentage of rows of VarTable1 that have a date of "current business date -1" and place into varTable2.
View 1 Replies
View Related
Aug 29, 2006
Is there a way to transfer ntext data from one table to another?I tried thisUPDATE [projects]SET [description] = (SELECT [description_ntext] FROM [table] WHERE[id]=1)WHERE [id_project] = 1;and thisDECLARE @DESCRIPTION ntextSET @DESCRIPTION = (SELECT [bids].[bid_conditions] FROM [bids],[projects] WHERE [bid_accepted_id] = [bids].[id_bid] AND [id_project] =@ID_PROJECT);UPDATE [projects]SET [description] = @DESCRIPTIONWHERE [id_project] = 1;none of those work in MSSQL2K,error reported is "The text, ntext, and image data types are invalidfor local variables."
View 2 Replies
View Related
May 3, 2000
Hello,
I have some trouble with a couple DTS package transfering table contents using "Transfer Objects" from one server to another. Every other night they fail, without leaving any error messages in either the SQL Server Log, NT Event Log or SQL Agent Log.
The server running DTS is a 7.0 SP1, and it transfers data to and from other servers running 7.0 without any service packs.
Brgds
Jonas Hilmersson
View 1 Replies
View Related
Oct 20, 2000
I have a database that I tried to shrink with no luck and have read to use the transfer Manager to transfer the data to a smaller database. Is this possible
to do?? Right now the database size is 31.5 Gig though the backups are only 14 Gig. I want to set the
database to about 20 gig.
Can anyone help me??
View 1 Replies
View Related
Nov 17, 1998
When using transfer manager between two sql servers on two different machines, is it necessary to use the same character set, sort order?
View 1 Replies
View Related
Sep 25, 2003
In order to export data to a 3rd party provider, I build five separate tables to store the data. Every table has a different layout, except for the first four columns. They are record type, SSN, employee id and another id number.
Basically, I have to sort that data by SSN then by record type. Each employee will have multiple records.
However, that data will need to be "merged" into one table to be exported.
I have created a table that defines the first four columns, but then has one large "filler" field that will contain the rest of the data. How can I copy data from five different tables with five different layouts into one table?
Any suggestions?
Thanks,
Steve Hanzelman
View 6 Replies
View Related
Aug 8, 2006
Hello. I want to ask about the possibility of copying both a tablestructure and it's contents from aSQL server table to a table within MS access. The problem cannot besolve with a permanent table structure at the target location.The names of the columns are essentially data with the application andso are subject to change. I am targeting a solution using SQL QueryManager.The approach I have tried (with failure) isSELECT *INTO <linkedserver table>FROM <local table>This should create and copy. However, I am not sure if this isachievable with this approach.Refer to the dialogue;-------------------------------------------------------USE MASTERGOEXEC sp_addlinkedserver@SERVER = 'Freddie',@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',@SRVPRODUCT = 'OLE DB Provider for Jet',@DATASRC = 'C: empHMIS_Recipe.mdb'-- I am not sure if this is requiredEXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULLSELECT * FROM Freddie...FRED -- This is OKSELECT * INTO #Temp FROM Freddie...FRED -- This is OK-- This fails - Refer errorSELECT * INTO Freddie.FRED65from #tempServer: Msg 2760, Level 16, State 1, Line 1Specified owner name 'Freddie' either does not exist or you do not havepermission to use it.-- This also fails and I thought reflected the above select with naming- Refer errorSELECT * INTO Freddie...FRED65from #tempServer: Msg 117, Level 15, State 1, Line 2The object name 'Freddie...' contains more than the maximum number ofprefixes. The maximum is 2.EXEC sp_dropserver 'Freddie',@droplogins = 'droplogins'------------------------------------------------------------Thank you.Regards JC...
View 3 Replies
View Related
Sep 9, 2005
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
View 1 Replies
View Related
May 30, 2006
How to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition.
i did by scripting but i transfer tables and procedures and not data
data is the problem.
tnx
View 1 Replies
View Related
Jan 5, 2008
Hi,
I have an excel sheet in which there is some data in sheet1,sheet2.I need to transfer this 2 sheets data to single table using a single package.How can i do this in SSIS.
thanks in advance
Pradeep
View 4 Replies
View Related
Apr 2, 2008
Hi All,
can anybody help me in creating the SSIS package to transfer the data from SQL table in database engine to OLAP cube in Analysis services
Thanks in Advance.
Archana
View 1 Replies
View Related
Oct 15, 2014
I have a SSIS package set up that will transfer a file from a location on the network drive and transfer it over FTP to another location.
When I manually run the package, the file is transfer with no errors. But when the job is automated (via Job Activity Monitor) the transfer fails?
I have set the ProtectionLevel of the package to "EncryptSensitiveWithUserKey" and also converted the package to a Development Model. The settings for the FTP is saved within the package.
What am I missing? below is the error message
Executed as user: UHBInfoSQLAgent. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 08:43:02 Error: 2014-10-13 08:43:03.72
Code: 0xC001405F Source: ResearchWebsite
[Code] .....
View 2 Replies
View Related
Jun 14, 2000
Hello:
I need to develop an off-line database that replicates/copies some key tables for reporting. The info is fairly static (codes. etc.)and does not need to be real-time.
Is it feasible that I can set up a scheduled task on SQL 6.5 using Transfer Manager to copy & append data to this off-line db on a schedule.
I may not be able to set-up replication on our production server.
So, I am considering alternatives. Appreciate any input. Thank you :)
View 3 Replies
View Related
Oct 2, 2007
Hi everybody.
I have such problem:
After I move my integration services packege to another machine it failed. I tried to recreate connection manager, but result is the same. If I create a simple SQL task in that package and try to execute it - it fails with info:
SSIS package "Main_Package.dtsx" starting.
Error: 0xC00291EC at Execute SQL Task, Execute SQL Task: Failed to acquire connection "SRV-DSA3.TIA_EXTRACTS_OriginalData.sa". Connection may not be configured correctly or you may not have the right permissions on this connection.
Task failed: Execute SQL Task
SSIS package "Main_Package.dtsx" finished: Success.
But when I create it(SQL task) in the new package - it works perfectly. Maybe somebody could help solving this problem.
Thanks for any information.
View 4 Replies
View Related
Mar 28, 2007
When we move reports from one folder to another in Report Manager we have to delete the report in the destination folder first before moving it over if a version is already there. If not we get the error that an item with that name already exists. Is there a way around this? Is there a setting which will allow over-write of a report?
View 1 Replies
View Related
Sep 18, 2015
I've a SSIS 2008 parent/child package solution to manage data transfers between two different data sources, so we can copy multiple tables and capture how many rows were transferred and duration for each transfer. This solution was working fine up until last week, when I made some changes to allow the package to perform a source count using standard SQL determined by an expression, or SQL provided from configuration tables, I also changed the package to Truncate or not the destination table, again controlled by configuration settings in a table. The child packages which perform the data flows have not changed!
The day after the controlling package promotion to live, I saw the bizarre behaviour of the Package log stating all rows transferred, but the actual table counts were not what the log stated, see attached file. The package solution works ok on other servers and was ok in DEV, but there were less tables and rows transferred.Re-running the package gave the same errors, but on some of the same tables and some different ones.
As it is the child packages doing the transfers and nothing has changed in them. I cannot see how the log would be able to say all rows are transferred and yet not all of the rows are actually moved?
Process output - where you can see counts and log Table transfer controller (as txt not dtsx)
An example of the data transfer child packages (as txt not dtsx)When I set the ExecuteOutOfProcess = True the package worked fine, unfortunately, this is not a good solution as SSIS 2008 does not tidy up the Dtshost.exe processes it starts and I'd be left with a memory issue after a very short time, we transfer hundreds of tables each day. ( I could write a .net script in the controlling package to kill the child processes, but that would still have hundreds of processes running before I could end them, as we have three parallel streams to allow a bit better performance.
View 6 Replies
View Related
