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
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
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
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)
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?)
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??
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.
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.
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.
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)
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
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, 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?
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.
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?
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!
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'
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...
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
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.
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
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
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
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?
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.
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 ?
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
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.
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.
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.
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.
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.
