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 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,
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
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 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
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?
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 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 was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered: "[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".€œ
View 12 Replies View RelatedHi
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
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
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 am trying to run a transfer database task in IS. The tasks fails saying that a defined role already exists in current database. This occurs if I try a destination overwrite to an existing database and even if the database is not currently allocated with destination overwrite = false.
Has anyone experienced these problems. I am just learning IS and have never experienced this type of problem with the old DTS version.
I am attempting to utilize the 'Transfer Database Task' to copy a SQL 2000 database to 2005.
I would like to set up the task as a job to run nightly and update the 2005 database from 2000.
I have the DestinationOverwrite set to true, the Action set to Copy, the Method is DatabaseOnline.
I receive the following message...
ERROR: errorCode=-1073548784 description=Executing the query CREATE ROLE[RSExecRole] failed with the following error: "User, group, or role 'RSExecRole' already exists in the current database..."
If I set overwrite to true...why is this an issue? Suggestions? Comments?
Thank you
Hi, can you not use this task in Microsoft SQL Server Integration Services Designer Version 9.00.3042.00? It keeps giving me an error when I try to copy a DB using this task. Says I cannot use this task in this edition of SSIS. Is there another way to copy a db so that I can schedule it to run or is there a SP I need to download for this to work? I'm using SQL Server Express as well.
thanks,
hi,
I have created a SSIS package that does nothing more than loop through all DBs and copies the userDBs to another server. However, I keep getting an error after the task has created the database during its execution of "Create Role" statements. Here is the error:
Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [aspnet_WebEvent_FullAccess] " failed with the following error: "User, group, or role 'aspnet_WebEvent_FullAccess' already exists in the current 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}). The Execute method must succeed, and indicate the result using an "out" parameter.
Now it appears to me that the Transfer DB task keeps using master as the current database even after it has created the new DB? Why would it does this when at the source the database role is under the usersDB?
thanks,
Derek
Hello everyone,
I am having a problem using the "Transfer Database Task" in SSIS. I want to copy (and overwrite) DestDB with SourceDB on one SQL Server, while SourceDB stays online.
So, I add the task to my control flow and go into the properties. Here's how I have the editor setup:
SourceConnection: MySQL2005Server
DestinationConnection: MySQL2005Server
DestinationDatabaseName: DestDB
DestinationDatabaseFiles: (I update all files to point to the correct destination folder)
DestinationOverwrite: True
Action: Copy
Method: DatabaseOnline
SourceDatabaseName: SourceDB
When I set these options and hit ok, I get validation errors. For every file in the source database, I get an error in the Transfer Database Task, 'Could not find the file "d:\SourceDB_XX" in the source database "SourceDB"'.
Does anyone have any ideas as to why I am getting this or of a way to fix it? Any reason why it puts 2 's in the file path?
Thanks in advance,
Jarret
I have successfully implemented forms authentication, that means, I can access it from a web browser, get the login page, add my credentials and log on to Reporting Services.
But now I try to access reporting services Web service from a winforms app, doing something like this:
ReportingService _rs = new ReportingService();
_rs.LogonUser("myUsername", "myPassword", "");
_rs.Url = "http://myServer/ReportServer/ReportService.asmx";
CatalogItem[] items = _rs.ListChildren("/", true);
The first three lines works fine, but he last line (ListChildren) throws a "Object reference not set to an instance of an object" exception. I have enabled remote error and looked in the RS log files, but nowhere I can find where this null-pointer exception occured.
Any idea about where to go from here?
Regards Andreas
Help anyone!! I fighting with the same error. I've tried all the suggestions on the web and more. Nothing seems to work. Has anyone found an answer from Microsoft?
Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_grantdbaccess @loginame = N'sctcsssoadmin', @name_in_db = N'ssoadmin' " failed with the following error: "User, group, or role 'ssoadmin' already exists in the current 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}). The Execute method must succeed, and indicate the result using an "out" parameter.
Hi,
I'm trying to set the DestinationDatabaseFiles property programmatically through an expression. I keep getting the error:
SSIS package "Package1.dtsx" starting.
SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Transfer Database Task' has been hit
Error: 0xC002929C at Transfer Database Task, Transfer Database Task: The transfer file information "","","" is missing the filename.
Error: 0xC002929D at Transfer Database Task, Transfer Database Task: The transfer file information "","","" is missing the folder part.
Error: 0xC0024107 at Transfer Database Task: There were errors during task validation.
Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (4) 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.
SSIS package "Package1.dtsx" finished: Failure.
I'm setting the property to a variable, copyDestFiles, which I've tried numerous ways to set, the latest being in a script.
Dts.Variables("User::copyDestFiles").Value = _
"""" + stageDataName + """,""D:Program FilesMicrosoft SQL ServerData"","""";""" + _
stageLogName + """ ,""D:Program FilesMicrosoft SQL ServerData"","""""
When I view the variable at runtime it looks like this: "Prod2.mdf","D:\Program Files\Microsoft SQL Server\Data","";"Prod_log2.ldf" ,"D:\Program Files\Microsoft SQL Server\Data",""
How does one set this property using an expression?
Thanks, Nathan Vollmer
I set up a basic Transfer Database task (online, copy) to copy a DB. It works great except for the fact that it isn't transferring the PK's and FK's. It also looks like it did not transfer the views. Any idea why? Anything else the Transfer Database task doesn't actually transfer?
View 2 Replies View RelatedHow to give authentication for Send Mail Task component?
View 4 Replies View RelatedThe Database Trasnfer Task has failed with the following error......failed with the following error: "Invalid object name 'dbo.exampleViewName.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 9 Replies View RelatedI set up a task to do a transfer of a SQL 2000 db to SQL 2005 in Integration Services (selected my servers, dbs, and chose DatabaseOnline method). In debug mode it processes for a little while and finally errors with:
[2] Progress: Starting database transfer.. Step 1 out of 2 complete
Error: The Execute method on the task returned error code 0x80131500 (An exception occurred while executing a Transact-SQL statement.). The Execute method must succeed, and indicate the result using an "out" parameter.
Task Transfer Database Task failed
Finished, 4:01:24 PM, Elapsed time: 00:10:39.422
Can't get over this error, and net searches reveal other postings similiar, but no answers.
SSIS database transfer task (with overwrite) from SQL 2k source to SQL 2k5 destination fails with:
Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [RFRSH_USER] " failed with the following error: "User, group, or role 'RFRSH_USER' already exists in the current 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}). The Execute method must succeed, and indicate the result using an "out" parameter.
The error seems the same regardless if the destination DB exists or not!
Anyone have a solution?
Dear All
Please I need an urgent help
After i finished all Transaction Log Shipping Configuration.
I tried to use the database in the secondary database but i couldn't access it
i saw it in SQL Managment Studio as (Restoring......)
i tired to make a database snapshot from it , i had a message
Msg 1822, Level 16, State 1, Line 1
The database must be online to have a database snapshot.
Please urgently