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!
New Member to group
I'm having trouble with what must be a common task as people migrate to 7.0. From the info I've read (MSDN, Books Online, etc) you cannot use the 7.0 Transfer GUI to access SQL Server 6.0, so I used a SQL 6.5 box to bring the old 6.0 DB half way.( all objects transfer well ).
However, I lose many types of objects whenever I attempt the 6.5 to 7.0 move.(views, contraints, rules, identity columns and seeds, etc.).
Does 7.0 require an ole connection to get these objects ? I believe it only gets an ODBC connection. Is there a required upgrade to the 6.5 box before the transfer? I have not found any articles mentioning this.
ps This is a 6.0->6.5->7.0 transfer, not using the upgrade wizard.
I have a client that is trying to use object transfer to move about a 1.4 GB db from one database to another. This transfer has been tried with both databases on the same server, and with the databases on different servers.
The transfer is taking over 4 hours..
The machines are single and dual processors respectively (pentium Pro 200)
with 1 GB of ram..
How long should it take? I would guess under an hour but I am not having any luck..
Thanks
Hi
All,
I am using Databaseobject transfer wizard to transfer objects and data
between two sql server 6.5 servers on schedule basis.unfortunately i specified non existing log directory for transfering these objects.Is there a way to change this this directory in 6.5 or to modify existing object transfer package..
I created several object transfers packages it will take lot of time for deleting and recreate them..
Thanks For your Help,
Kris
SQL 7.0 seems to be missing Database/Object Transfer tool, which allows to transfer single objects or whole database from one to another.
Also I couldn't find Backup/Restore one single object option eigther.
Could anyone can comment this, pls?
Michael Gladshteyn
When I am trying to transfer a database from one server to another everything comes over fine except the Stored Procedures and views. What am I doing wrong?
I'v tried just transfering the SP and views and I am still having the problem.
Thanks
Hi
All,
I am using Databaseobject transfer wizard to transfer objects and data
between two sql server 6.5 servers on schedule basis.unfortunately i specified non existing log directory for transfering these objects.Is there a way to change this this directory in 6.5 or to modify existing object transfer package..
I created several object transfers packages it will take lot of time for deleting and recreate them..
Thanks For your Help,
Kris
In SQL 6.5, you could Transfer (through a menu item in Enterprise Mgr) all or parts of a database to another server. It was the perfect 'copy' mechanism. What happened to it?
Thanks,
Judith
My applications group wants to do a table transfer from one server to another as a means of refreshing the information on the target on a scheduled basis. That means that reports could be running against the target table during the transfer. This has introduced erroneous reports because the object transfer initially truncates the table, then transfers the data. If reports are running during the transfer, they might retrieve no data or only part of a table. I wrote a procedure to make a copy of the table to be transferred, then transfer that new table to the target machine, then drop the target table, then rename the transferred table. Now, I have the slightly smaller problem of doing a DROP TABLE when users are accessing the table. I can't set the database to 'dbo use only' because there are other tables in the database that must be available. Is this really a replication application?
Cindy Rutherfurd
SQL Server DBA
ZC Sterling Corp.
Hi,
i am trying to transfer objects from SQL Server 2000 DB to SQL 2005 DB.
i have copy schema to true and i am only copying tables. when i have tried first time it worked fine but in next time it start to give error. here is the error description.
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR :
errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is
available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP
Provider: An existing connection was forcibly closed by the remote host. ". helpFile=dtsmsg.rll helpContext=0
idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Thanks,
Haroon
I am trying to alter the AdventureWorks database to transfer ownership from one schema to another...
Alter schema dbo transfer Person.CountryRegion
I get the following error..
"Cannot transfer a schemabound object."
Any help would be appreciated.
Trying to tranfer tables along with data using transfer sql server objects tasks and it is giving me an error <object> does not exists at source
for me It only works if table schema is owned by 'dbo ( even though Copy schema property is set to true)
Any workaround? Thanks in advance.
Hi,
I'm new to Integration Services and I have a problem when I try to make a simple transfer of tables and views between 2 DB.
The idea is to copy tables and views from DB1 to DB2.
If the object exists in the target DB, it must be dropped, then created and the data must be copied.
The problem occurs when a new object is created in the source DB and thus does not exist in the target DB. This throws an error because the system cannot drop an object wich does not exist.
What is the workaround ?
The idea is to execute this package every hour to have a cached DB. In this case, replication is not a solution.
Thanks in advance,
Patrice.
Good Afternoon,
Does anybody know if the DTS Transfer Object Task transfers tables with IDENTITY INSERT enabled? I'm copying objects, along with their data to another database, but I need to retain the values in my IDENTITY columns. I will do some manual checking but this is fairly tedious and error prone.
I've checked the Books Online but I couldn't find anything that answers my question.
Regards,
Robin
Hi Folks,
I have a problem when trying to schedule a Database /Object Transfer.
The transfer between two similiar databases located on differernt servers is consistantly failing even though it will be successful if I choose the
Start Transfer Option. The error message that appears is 'Process Exit Code 1. Cannot connect to source server'. This is even with the other server
regestered on both servers !
I have tried different levels of security (sa and trusted), re-regestering the servers, WINS settings in TCP/IP but all to no avail.
The servers are configured in the same way.
If you have come across the same problem, please share the solution. Any suggestions greatly appreciated.
Thanks in advance
Alan Shinnors
Does anyone know of a reported bug in the security scripting option of the database/object transfer GUI utility in 6.5? I created a mirror database on the same server and transfered all object including logins and permissions. There was a warning the the passwords were set to null on the destination database, but they were also set to null on the source database. Any experience with this?
View 1 Replies View RelatedHi all...
Sorry if this is a real simple question, but I just had a SQL 6.5 server dropped in my lap. I need to transfer all the data to a new box, which I did using the Tools mneu (Database/Object Transfer) in Enterprise Manager. I checked the databases after the transfer and all the data seems to be there, including the logins. However, if I try to connect the database, all logins fail.
Connection failed.
SQL State '28000'
SQL Server Error 4002
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed
Does any body know how to fix this easily without resetting every single user id?
Any help
would be greatly appreciated.
Thanks
Greg
Hi everyone
Can any one help me I am using Tranfer databse object task when I am trying to run it its throwing exception can not send null value in login name????
Hi all,
I am facing problem while using Transfer SQL Server Object Task in SSIS, it is not importing table & its attributes.
The details settings of Transfer SQL Server Object Task as follows:
clicking edit - Objects(left pane)
Right pane
Connection
Source Connection existing server name 1
Source Database ganny1
Destination Connection existing server name 1
Destination Database ganny2
Destination copy objects
Objects to copy - copy all tables - true
Table Objects
CopyPrimaryKeys - True
CopyForiegnKeys - True
After setting all this configurations, Executing task, I am not able to get table in my destination database.
Please suggest me to solve the issue.
Thanks in advance
Karna
Hi,
I am wondering using transfer sql server objects task in a sub-package and feeding tableslist property from the parent package. which works fine.
problem :
I want to be able to change the name in the fly so if I have
TableA I want to copy it for the destination as TableB
is there any work arround this just using transfer sql server objects task.
Thanks
I am trying to move some tables from one database to another(both 2005) using SSIS. The tables are not dbo tables. I am using sql authentication for the source and windows authentication for the destination SMO conection.
I get the following error:
Error: 0xC002F325 at Transfer Tables from Beta To test, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Invalid object name 'demo.rz_Analysis'.".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
I have no problem transferring the dbo tables.
i have no clue about this...
Does anyone know whats going on here?
I am trying to transfer a table form one server to another using object transfer.
I can not transfer this one table.
I can access and view the data
I get this error when transferring “Cannot Truncate table <table name> because this table does not exist in database
Attempt to use Bulk copy with a non-existent server table
Any ideas. If it were corrupt I am assuming I would not be able to view data and use table in production database.
Hi all,
I have created a package to copy 10 tables from a database to another one. I have created a datasource with SQL Authentication mode. And it couldn't run until I changed the datasource connection to Windows Authentication mode. How could it be like that, it's just the authentication mode?
Is there anyone have the right answer for this situation?
Hi all,
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?
Can anyone help me?
I will very appreciate you help?
Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @table_name (string).
Transfer SQL Server Object Task is with in foreachloop container
I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @table_name
I would be grateful if you can give me any hint.
Thanks
S
I'm using a Business Intelligence project to copy stored procedures and tables from one database to another across servers. I'm having trouble copying tables or stored procedures using the Management.SMO.Transfer class.
I tried copying stored procedure with the property transfer.CopyAllStoredProcedures = true. This didn't work. As a workaround, I used the StringCollection property and executed every string as sql.
Now I'm having trouble copying tables. I don't want to copy all the tables in the database. How do I go about selecting what tables to copy. I tried using ObjectList property and provided the names of the tables in an ArrayList. I get the error
"Transfer cannot process System.String. You need to pass an instance class object."
How can I pass an "instance class object" for something that's in the database? The ScriptTransfer method fails so I can't even see the script that is being generated. There is virtually no documentation for this class.
Any help will be appreciated.
Transfer transfer = new Transfer();
transfer.Database = sourceDB;
transfer.DestinationDatabase = DestinationDatabase;
transfer.DestinationServer = DestinationServer;
transfer.DropDestinationObjectsFirst = false;
transfer.CopyData = true;
transfer.CopySchema = true;
transfer.CopyAllObjects = false;
transfer.CopyAllTables = false;
transfer.CopyAllStoredProcedures = false;
transfer.Options.WithDependencies = false;
GetTablesToBeCopied();
transfer.ObjectList = tablesList;
transfer.Options.FileName = "C:\TransferScriptTables.sql";
StringCollection coll = transfer.ScriptTransfer();
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor
I wanted to create a package to copy the objects from one database to another and replace those object if they already exist. Therefore, before the package executes you do not know whether all the objects exist on the target server or only some of them.
Using the 'Transfer SQL Server Objects Task' I have found that I cannot get this to execute cleanly by itself. If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists and if I set it to true then an error is thrown if it does not exist.
In order to get round this I have had to create an 'Execute SQL Task' to list all the objects and then go through them dropping them on the target server in a for each loop before executing the 'Transfer SQL Server Objects Task' with 'Transfer SQL Server Objects Task' set to false.
However, is there a better way of achieving this or am I missing something in the 'Transfer SQL Server Objects Task'?
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.
Thanks
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
Hi There,This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.I have a project that I was working on with this ms access db and using sql controls, everything was working just finesince one day I started getting "Object reference not set to an instance of an object" messages when I try to designa query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lotof work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado componentsbut nothing seems to fix it, did this ever happen to any of you guys?any tip is really appreciated thanks a lot
View 2 Replies View RelatedDoes any one has any clue for this error ? I did went through a lot of articles on this error but none helped . I am working in Visual studie 2005 and trying to upload image in sql database through a simple form. Here is the code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.IO;
public partial class Binary_frmUpload : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void btnUpload_Click(object sender, EventArgs e)
{if (FileUpload.HasFile == false)
{
// No file uploaded!lblUploadDetails.Text = "Please first select a file to upload...";
}
else
{string str1 = FileUpload.PostedFile.FileName;
string str2 = FileUpload.PostedFile.ContentType; string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
//Initialize SQL Server Connection SqlConnection con = new SqlConnection(connectionString);
//Set insert query string qry = "insert into Officers (Picture,PictureType ,PicttureTitle) values(@ImageData, @PictureType, @PictureTitle)";
//Initialize SqlCommand object for insert. SqlCommand cmd = new SqlCommand(qry, con);
//We are passing Original Image Path and Image byte data as sql parameters. cmd.Parameters.Add(new SqlParameter("@PictureTitle", str1));
cmd.Parameters.Add(new SqlParameter("@PictureType", str2));Stream imgStream = FileUpload.PostedFile.InputStream;
int imgLen = FileUpload.PostedFile.ContentLength;byte[] ImageBytes = new byte[imgLen]; cmd.Parameters.Add(new SqlParameter("@ImageData", ImageBytes));
//Open connection and execute insert query.
con.Open();
cmd.ExecuteNonQuery();
con.Close(); //Close form and return to list or images.
}
}
}
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 32:
Line 33: string str2 = FileUpload.PostedFile.ContentType;
Line 34: string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
Line 35:
Line 36: //Initialize SQL Server Connection Source File: c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs Line: 34
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
Binary_frmUpload.btnUpload_Click(Object sender, EventArgs e) in c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs:34
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102