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??
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
I'm trying to get everything I need for membership transferred from my local ASPNetdb.mdf file to another server.
When I use the Management Studio and do an Import or Export of data - it only gives me the Tables or Views as a possibility -
How can I transfer the stored Procedures from a SQL Express database to a SQL 2000 database?
Hi,
I have around 25 stored procedures kept in a DB1
I want to transfer all of them to DB2. How can i do that.
I tried Database Publishing Wizard to generate all stored procedure script, but it generates the scripts for table as well.
thanks.
Hi,
Is there a practical way to transfer Stored Procedures to another database?
Thanks.
How do you transfer stored procedures from SQL Express 2005 to MS SQL 200 ? I am using the Personal home page starter kit and also need to know if I have to attach and detach the database to my ISP's server?thanks,
View 2 Replies View RelatedI have two databases named A and B. They are exactly same table structure, but the records are different, and we have near 400 stored procedures and over100 views in the database A. Now my question is that how can I transfer all stored procedures and views from database A to database B?
Thank you very much!
Hi,
I uploaded a database to a remote server (ISP) who has SQL2005 as do I. They got the database okay, but not all the stored procedures went along for the ride
Can anyone tell me how to just simply transfer the stored procedures only to the remote server?
Failing that, can you tell me how to export the entire database and be sure that it all goes, not just the tables. I assume you would have to do this via a script because the export wizard only lets you copy the files and I have dome this but the stored procedures do go along.
Any help?
Tia
Hi gurus
i have a assignment on hands to transfer store procedures of 2005 to 2000. I have tryed by scripting SPs and then runing them in 2000 QA, but it gives me error every time ....... so please help me to resolve this problem.
thanks
shekhar
I am trying to copy several stored procedures from one server to another.
I am using Transfer SQL Server Objects Task in integration services.
Some of the stored procedures already exist on the destination server and should be overwritten and part of them is new.
I turned DropObjectsFirst option on.
Package fails to execute and returns error that the procedure can't be dropped on the destination server because it doesn't exist.
Does anybody know how to configure the task so it automatically check the existense of copying stored procedure on the destination server?
I'm unable to copy my Stored Procedures from one database to another. I'm using mixed mode authentication. I have set CopyAllStoredProcedures to True, DropObjectsFirst to True and CopySchema to True.
Nothing gets copied. I have followed many web sites that say Transfer SQL Server Objects Task is broken. Is this true and I should give up?
Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume is the update for SSIS? yes ?
Thanks for any help
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "CREATE PROCEDURE [dbo].[del_Admin_RemoveContractorFromContract] @ContractID int, @ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID " failed with the following error: "There is already an object named 'del_Admin_RemoveContractorFromContract' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
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 RelatedI 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??
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 RelatedIs 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
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 :)
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
QuestionWhat is the best way to transfer
Stored Procedures to another db without having to script
it? Or is scripting the best way?
WHYWe need to transfer stored procedures from
one development database to another on different machines not on the
same
network.
PlatformStudio
2005
SQL Server Management Studio Express
advTHANKSance
Hi
we are having SQL server7.0 with DPT raid card which gives the problem of backup/restore through normal SQL backup in Enterprise manager.So we are not using the above procedure
but we have moved all the tables using SQL EXPORT to another new server.
Can you please give a solution to transfer Stored procedure from the server to new server
Regards
Rajakumar
hi,
I need to transfer my new stored procedure from local database to the database of my site.
What should I do? Could I use DTS ? I have built my databases in SQL2000
Please help me.
Thanks alot.
I am trying to use a Transfer SQL Server Objects Task to copy a portions of tables from one db to a second db.
the connection is using Windows Authentication.
I am logged in as the Administrator
That user has sysadmin rights on the Sql Server in Question.
DropObjectsFirst = True
But when I execute the task I get the follwing warning
[Transfer SQL Server Objects Task] Warning: Stored procedure "{0}" already exists at destination.
What am I missing?
Thanks,Kim
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?
Hello,
I am running into trouble when transferring my stored procedures to my hosting provider.
I use the transfer objects selection in the import wizard,
Do i need to unblock any ports on my firewall for this to work?
Do my login details have to be the same on both the local server and on the hosting server?
Any help is appreciated.
Cheers,
Padraic
How can I transfer stored procedure from SERVER A to SERVER B, running SQL Server.
When i choose Export/Import Wizard, it only let me transfer Tables and views , but not SPs,
Many Thanks,
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.
--Raj
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
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
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
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
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.
<%@ 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?