Problems W/DTS - Simple Transfer
Jul 9, 1999
We are seeing errors trying to transfer some (most of our db tables ) when using the copy tables option on the wizard (simple transfer, no transformations).
We see an 'error occurred' notation on the Transferring Data dialog - when we click on the individual tables to get a detailed description of the error, we get "General error - -2147217887(80040E21). Errors occurred" notation. We can't figure out what the error was - running a Profiler trace with all events doesn't give us any clues. If there is a DTS log (similar to the old Transfer Manager log), we can't find it....
Interestingly, we get the same error number for all errant tables and the error isn't always consistent - tables that didn't work yesterday do work today, but new ones are erring...
Any info would be greatly appreciated... Many thanks
View 3 Replies
ADVERTISEMENT
Dec 27, 2007
Greetings all,
I have two databases and need to transfer some data from one to the other based on the "employee_Number".
"Table 1 and 2" both have a complete listing of employees but in "Table 1" certain columns are empty. I need to see if "Table 2" has got data in those columns and if yes import them to the "Table 1" based on the "Employee Number"
What is the simplest way to do this. both are on SQl server.
please Help
Thanks in advance........
View 3 Replies
View Related
Feb 1, 2006
creating a simple package and cant get it to run below is the error. when i check the source 2k db its there see below...
i am clue free of the issus...
SSIS error message
Error: 0xC002F325 at Transfer SQL Server Objects Task, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=0 description='FK_MoreBDRInformation_dbo_BatchDataReports' is not a constraint. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
source Server DB sysobjects table record
name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
FK_MoreBDRInformation_dbo_BatchDataReports 1618573300 F 1 0 0 0 0 86043838 2006-01-31 12:35:41.530 0 0 0 F 0 11 0 2006-01-31 12:35:41.530 0 0 0 0 0 0 0
View 1 Replies
View Related
May 28, 2007
I'd like to transfer some records between the following 2 tables. Surely this should be a no-brainer - what am i missing that is making this so impenetrable?
I am currently: Hoping someone can help me get here: (this is my first time of using SSIS btw).
here is the source table (MS Sql Server 2005, SP 2)
CREATE TABLE [dbo].[imagine_divs](
[div_mnemonic] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_date] [int] NULL,
[div_amount] [float] NULL,
[div_status] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_curr_mnem] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_upd_date] [int] NULL,
[div_payment_date] [int] NULL
) ON [PRIMARY]
here is the target table (Oracle 9i)
CREATE TABLE myschema.imagine_divs
(div_mnemonic NVARCHAR2(11),
div_date NUMBER(*,0),
div_amount NUMBER(20,5),
div_status NVARCHAR2(16),
div_curr_mnem NVARCHAR2(11),
div_upd_date NUMBER(*,0),
div_payment_date NUMBER(*,0))
/
I used the SSIS Import and Export wizard to copy data between the two tables, and attempted to execute it. I use Sql Native Provider on source, and Native Ole DBOracle Provider for OLEDB. however, I get an error:
[Destination - IMAGINE_DIVS [37]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12571: TNSacket writer failure".
I notice that the wizard has created a data flow task with 3 steps: source - imagine_divs, Destination - IMAGINE_DIVS and "data conversion 1".
Data Conversion 1 seems to be taking my source nvarchar columns and converting them to DT_STR with twice the size (for example div_mnemonic become DT_STR, size: 22).
If I change the mappings in the OLE DB Destination Editor, such that only the numeric and date-typed columns are included in the transfer, it works fine.
If I include any string-typed column in the destination editor mappings, I get the TNS Packet Writer error. If I remove the Data Conversion step and connection teh source and destination tasks directly, i get validation errors saying that:
Error 2 Validation error. Data Flow Task: OLE DB Destination [294]: Columns "div_mnemonic" and "DIV_MNEMONIC" cannot convert between unicode and non-unicode string data types. Package4.dtsx 0 0
this is despite the fact that everything is unicode here (right?)
what in the world is going on?
View 4 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
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
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
May 26, 2004
Hey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
View 1 Replies
View Related
Feb 21, 2007
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
View 7 Replies
View Related
Aug 19, 2006
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
View 5 Replies
View Related
Mar 19, 2014
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?
View 6 Replies
View Related
Mar 1, 2007
Hi,
I have the following method which transfers data SQL to SQL on the same server but when I try to change the destination server it won't transfer the data. The method runs through as expected with no exceptions or errors but with no data transfered.
Private Sub TransferSQLData(ByVal SourceDetails As Admin_upload.EnvironmentDetails, ByVal DestinationDetails As Admin_upload.EnvironmentDetails)
'Transfer database from source to destination.
Dim oPackage As New DTS.Package2
Dim oConnection As DTS.Connection2
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.TransferObjectsTask2
Try
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSTransferObjectsTask")
oCustomTask = oTask.CustomTask
oPackage.FailOnError = False
With oStep
.Name = "Copy Database design and data"
.ExecuteInMainThread = True
End With
With oTask
.Name = "GenericPkgTask"
End With
With oCustomTask
.Name = "DTSTransferObjectsTask"
.SourceServer = "MYSERVER"
.SourceUseTrustedConnection = True
.SourceDatabase = SourceDetails.MetaDB
.SourceLogin = SourceDetails.MetaUser
.SourcePassword = SourceDetails.MetaPWD
.DestinationServer = "MYSERVER"
.DestinationUseTrustedConnection = True
.DestinationDatabase = DestinationDetails.MetaDB
.DestinationLogin = DestinationDetails.MetaUser
.DestinationPassword = DestinationDetails.MetaPWD
.CopyAllObjects = True
.IncludeDependencies = False
.IncludeLogins = False
.IncludeUsers = False
.DropDestinationObjectsFirst = True
.CopySchema = True
.CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData
End With
oStep.TaskName = oCustomTask.Name
oPackage.Steps.Add(oStep)
oPackage.Tasks.Add(oTask)
oPackage.Execute()
Catch ex As Exception
LabelUploadMeta.Text = "Failed to Upload MetaData: " & ex.Message
Throw ex
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try
End Sub
This works fine but when I set the following within the method:
.DestinationServer = "ANOTHERSERVER"
It won't transfer the data.
I can access the remote server and read and write data to it.
Any ideas?
View 1 Replies
View Related
Jun 16, 2000
Hi ,
Can anyone suggest me to transfer a database from 65 to 70.
I trasfer a database using DTS, but i am only getting data. I need my users , views, stored procedures, permissions.
I need to upgrade three databases from 65 to another 70 server.
i also generated a script from 65, but which is showing errors.
Could u pls give any suggestions.
thank u.
--Raj
View 1 Replies
View Related
Oct 8, 1999
I recently transferred a db from our production machine to a test machine. After the transfer was complete I realized that all copied logins were copied with blank passwords. Is this default behavior? Can I set the transfer to include passwords?
Dave King
View 3 Replies
View Related
Sep 13, 2005
Hi,
stupid question but i have forgotten how to transfer a DTS package between servers, its been so long since i last had to!!!
TIA
View 1 Replies
View Related
Feb 4, 1999
Hi Everybody...
I have recently tried to transfer my database over from one machine to another, but failed to do so because it gets jammed on 30%. I am just wondering if there is any other ways I can take to migrate the database over.
I am currently running sql server 6.5 in concurrent with iis 4 and nt4. I am trying to seperate the iis to a new server machine and sql server to another server.
Please advise me which route to take if you have any suggestions and your solution is important to me. Thanks!
-Ken
View 1 Replies
View Related
Oct 19, 2007
Hi,
I want to transfer rows from one database to another.
Source My Source tables in database 'Staging'
CustomersEng
Name
Age
StCode
CityCode
InternalID
CustomersSpanish
Name
Age
StCode
CityCode
InternalID
CustomersEng and CustomersSpanish contains a replica..only difference being that 'Name' in the CustomerSpanish table is written in spanish language. The Stcode, citycode and Internalid together makes the row unique.
Code BlockSample script
Create table #CustomersEng (name nvarchar(20),age int, st_code int,
citycode int, internalid int)
Insert into #CustomersEng values('Aron',23,1,1,12);
Insert into #CustomersEng values('Cathy',28,1,1,13);
Insert into #CustomersEng values('Zarog',33,1,1,14);
Insert into #CustomersEng values('Michale',25,1,2,12);
Insert into #CustomersEng values('Linda',43,1,2,13);
Insert into #CustomersEng values('Burt',53,1,2,14);
Create table #CustomersSpanish (name nvarchar(20),age int, st_code int,
citycode int, internalid int)
Insert into #CustomersSpanish values('Arona',23,1,1,12);
Insert into #CustomersSpanish values('Cathylo',28,1,1,13);
Insert into #CustomersSpanish values('Zarogo',33,1,1,14);
Insert into #CustomersSpanish values('Michalo',25,1,2,12);
Insert into #CustomersSpanish values('Lindalo',43,1,2,13);
Insert into #CustomersSpanish values('Burto',53,1,2,14);
---------------------------------------------------------------------------------------------------------------
Destination tables in database 'CMO'
CustomerMaster
CustomerId (identity) (pk)
StCode
CityCode
InternalID
CustomerDetails [customerid and languagecode together are primary keys]
CustomerId (fk)
Name
Age
languagecode
Language
languagecode (pk)
languagename
Code Block
Create table #CustomerMaster (customerid int identity(1,1), st_code int,
citycode int, internalid int)
Create table #CustomerDetails (customerid int,name nvarchar(20),age int,
languagecode int)
Create table #CustLanguage (languagecode int, languagename varchar(10))
Insert into #CustLanguage VALUES (1,'English')
Insert into #CustLanguage VALUES (2,'Spanish')
Code BlockEXPECTED OUTPUT
CustomerMaster
1 1 1 12
2 1 1 13
3 1 1 14
4 1 2 12
5 1 2 13
6 1 2 14
CustomerDetails
1 Aron 23 1
1 Arona 23 2
2 Cathy 28 1
2 Cathylo 28 2
3 Zarog 33 1
3 Zarogo 33 2
4 Michale 25 1
4 Michalo 25 2
5 Linda 43 1
5 Lindalo 43 2
6 Burt 53 1
6 Burto 53 2
How can i write t-sql for this?
thanks
View 4 Replies
View Related
Feb 23, 2007
Hi All;
I know that in the SQL Server, the logins are not contained in the database (thanks to Kevin Yu) but I don't know where are they stored. For example if I want to transfer to another server I'll have to backup users too; but HOW?
In order to be more clear: When I simply take a backup from my server and restore it to my computer I can not login again with the user login information which is created on the server; However even if I make some changes; after backing the DB up from my computer and restoring to the server the logins are working (with re-mapping)
Thanks...
View 7 Replies
View Related
Mar 26, 2007
I had Sql Server 2005 installed on my machine, with that I am working on Visual Studio 2005. I just need to add an existing database into the app_data folder. The database is existing in the Sql server, it contains 180 tables so I cannot manually copy that tables in the app_data folder. for that is there any another way to copy all the tables into the database into the folder app_data with the data in the table.
Thanks & Regards
Girish Nehte
View 4 Replies
View Related
Apr 16, 2004
I recently transferred my SQL DB from one server to another, I backed the DB up, copied across and restored it into the new location, the problem is that I get the following error.
System.Data.SqlClient.SqlException: Invalid object name 'Table Name'
I have created a new user for this database and changed my code accordingly.
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Page Language="VB" Debug="true" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
Dim cmdSelectContacts As SqlCommand
Dim dtrContacts As SqlDataReader
conPubs = New SqlConnection("Server=Treebeard;UID=Intranet;Pwd=glglgl;Database=Contacts")
ConPubs.Open()
cmdSelectContacts = New SqlCommand ("Select * From Clientdata", ConPubs)
dtrContacts = cmdSelectContacts.ExecuteReader()
While dtrContacts.Read()
Response.Write( "<li>" )
Response.Write( dtrContacts( "c_forename" ))
End While
dtrContacts.Close
conPubs.Close()
End Sub
</script>
Any Ideas?
View 4 Replies
View Related
Aug 22, 2005
Hi everyone,I need some help transfering my database(SQL2005) to the host's server.In SQL 2000 I had the Management tools > DTS or ImportExport tools.SQL2005 lacks this, all I've got is Express Manager > can't connect to multiple databases.Can connect to one database and has a screen to excute TSQL commands.I don't want to have to recreate the whole database on the server.....This would be crazy... 150 stored procedures and 30 tables. Not to mention all the individual settings for each column.I know there is SQL Studio Management tools for the full version of SQL 2005. Anyone got any idea's ?Any input/suggestions is greatly appreciated.Thanks,JShep
View 1 Replies
View Related
Jan 16, 2006
I am trying to transfer my database from my computer where built it to another computer. I have microsoft SQL server 2005 CTP on my computer and the other computer has Microsoft SQL server.
Does anyone have any suggestions on how to do this??? I cannot find much info on SQL server 2005 CTP. I have found some info about the copy database wizzard, but I cannot find that in CTP.
Please any ideas or info would be wonderful
Thanks
Sarah
View 6 Replies
View Related
Mar 1, 2002
If I’m using the DTS Transfer SQL Object to move all objects and data from one server to another server and I make a schema change to source server, why is the dts package failing the next time I run it? Do I have to do some sort of refresh?
Any help!
View 1 Replies
View Related
May 11, 2001
I have a corrupt master db due to a rogue script that inserted lots of information into it. I have two SQL servers, and I could use some advice on the best way to copy the files over to the second server, then back after a reload of the first server. My main concern is the DTS packages I have built- I can't afford to lose those.
View 4 Replies
View Related
Jul 26, 2000
Hi,
I am in the process of upgrading database in sql server 6.5 to 7.0.
For that i am following one computer upgrade.
For creating dev environment in my computer i am using Transfer/Objects in 6.5
for transferring database from remote(production) computer to my computer.
Do i need only to transfer Userdatabase alone or do i need to Transfer Master and other ?
Or Is it better to restore dump ?
Thanks
View 4 Replies
View Related
Jul 12, 2000
HI,
If i transfer logins from one server to another server using dts, I am getting all the logins and permissions... How will i get passwords too..
-thank u.
--Siva
View 2 Replies
View Related
Sep 18, 2000
Does anyone have a sample SQL DMO script to transfer SQL Server logins from one server to another including the passwords ? any pointers will be greatly appreciated
View 1 Replies
View Related
Jul 30, 2000
Hello. Firstly apologies for what might appear as a basic question, but being a newbie and all :-). .
I have a SQL 7 DB, loads of sp, vw, tables etc. I want to copy this DB to another server so that all the DB and sp permissions stay current, all the sql logins come across etc etc.
I have tried restoring a backup to the new server (which worked fine), but then I appear to have lost the permissions for the tables and sp's.
I have then tried taking a copy of the original SQL master table and restoring this to the new server - but no luck.
Can anyone advise a sure method of copying an entire table and related entries (meaning sql logins etc) to a new server.
Appreciate any comments.
Regards
View 3 Replies
View Related
Aug 3, 2000
can any body can tell how data trnsfer from oracle database to sql server database. what is the methods i can use? is it required to write scripts.
if scripts required, can anybody can give me sample scipt.
View 1 Replies
View Related
Apr 6, 2000
Does anyone know whats the best to transfer sqlserver to new server but maintain the same Sqlserver name. The reason for this is that we have thousands of users out in the field and it would be too much trouble to identify these users and update the odbc entry to point to new sqlserver.
Thanks in advance for all your help.
View 1 Replies
View Related
Nov 10, 2000
When i try to load tables from DB2 OS390 Via DTS and DB2 ODBC it
give me an error with the timestamp field. Its (DTS) says the timestamp
field on SQL is marked read only.
Is there anyway around this problem?
View 1 Replies
View Related
Nov 16, 2000
SQL v7.0 - DTS
Tying to transfer all object info, sp's, constraints and data in to a fresh new database in the same instance, but it's hit and miss when it comes to getting everything transferred. Followed step by step instructions (GUI/Scripts) and sometimes the table structure will transfer without PK's, as well as some data and if you're lucky, some constraints. Stored proc's have a tendency to show up 20 minutes later. I'm testing this on a machine with IE 5, SP2 and fresh new Dll's (just in case something was corrupted). Is there a priority as far as what to transfer first? For example, tables only 1st, then constraints on down to records parent to child? I am assumming you should be able to transfer all at the same time since it's built in to the utility. Or, does it come down to something in the schema? If anyone out there has experienced something similar and found a solution, please advise.
View 1 Replies
View Related
