Transfer SQL Server Objects Task Doesn't Work With Schemas
The task has a fault if you try to copy a table with a schema like [test].[table1]
The error says
Error: 0xC002F363 at Flyt til DM, Transfer SQL Server Objects Task: Table "table1" does not exist at the source.
Even though i just selected it from the table list minutes before.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Transfer SQL Server Objects Task Doesn't Work
Source Database: SQL Server 2000 Destination Database: SQL Server 2005 No matter what table, view, or stored proc I pick, it always says that it doesn't exist at the source. I know it exists because I am picking it from the list of tables, etc. that the GUI provides. Any suggestions? Jonathan Allen
View Replies !
View Related
SSIS Transfer Objects Report An Error: 0xC002F325 At Transfer SQL Server Objects Task
hi folks i try to copy all objects from one dt to another db on the same SQL-Cluster. The transfer work some minutes (generate skripts for creating objects) and will fail if the data would be copy see errormessege below SSIS package "AdminContent to MCAdminContent.dtsx" starting. Error: 0xC002F325 at Transfer SQL Server Objects Task, 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 'dbo.AllDocs'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". Task failed: Transfer SQL Server Objects Task SSIS package "AdminContent to MCAdminContent.dtsx" finished: Success. have any one an work arround for my problem or have i a bad configuration? thanks for help roland
View Replies !
View Related
SSIS (Integration Services) Transfer SQL Server Objects Task: This Task Can Not Participate In A Transaction
In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions? In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?. I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA. Here€™s the error info€¦ SSIS package "Development BusinessContacts and Products Migration.dtsx" starting. Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container. Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction. Task failed: Copy BusinessContacts database table data 1 Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction. Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction. SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure. The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
View Replies !
View Related
Using Transfer SQL Server Objects Task
Hi All, When using the Transfer SQL Server Objects Task I€™m having the following error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name '«dbo.table_name»'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". When I change the CopySchema attribute to €œTrue€?, I got the following error: EventCode: -1073548507 ErrorDescription: Execution failed with the following error: "ERROR : errorCode=0 description='«FK_Constraint»is not a constraint. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". The first error just occurs when running the package on the SSIS Server, when running the same package on my local machine I have no errors, it run€™s just fine. Anyone knows why this happens!?The configurations (User, password, authentication mode) are exactly the same on both machines.On the second error, i think it occurs because the task is trying to first eliminate the constraint on the target machine, but this constraint doesn't exists. Thanks, VÃtor Ferreira
View Replies !
View Related
Transfer SQL Server Objects Task
Hi, When i'm trying to transfer SQL Server Objects from a SQL Server 2000 Database to another i got the following error: [Transfer SQL Server Objects Task] Error: Table "bank" does not exist at the source. This just appens in some spcific situations: - When i select the tables i want to transfer, using the option "CopyAllTables" it works fine; - When i use specific instance as source. Using the Development machine as source it works fine, when i use the machine from Pre-Production (the one i whant to use) it doesn't . Can anyone tell me why does this appens!? Thanks, VÃtor Ferreira
View Replies !
View Related
Transfer SQL Server Objects Task: Nothing Happens
Hello, I am trying to copy a subset of tables, stored procedures, and views from one database to another database on a named instance of SQL Server 2005. I am attempting to use the Transfer SQL Server Objects task in Integration Services. I am able to create both Source and Destination connections, and specify the objects I want to transfer. When I run the package, the task turns yellow while it's processing, then green when it completes. At this point, no objects have been transferred to my destination database. No tables, no procs, no views, nothing. No error has been generated. No information is written to an SSIS log file, other than the fact that objects are being transferred, and then the package is finished. Does this have something to do with the fact that I'm using a named instance of SQL Server 2005? Thanks, Ken
View Replies !
View Related
Transfer SQL Server Objects Task
Is there a configuration for this task that allows one to change a table name? I want to append a prefix to the table name for the destination. I have been able to handle this in other manners, but was curious as to whether or not I could leverage this task component. Thanks
View Replies !
View Related
Transfer SQL Server Objects Task
Hi all, I am using SSIS Transfer SQL Server Objects Task to copy all the objects of a database to another without the data. The properties that i have set for this task is as: DropObjectsFirst = True IncludeExtendedProperties = True CopyData = false CopySchema = True UseCollation =True IncludeDependentObjects = True CopyAllObject = False CopyAllTables = True CopyAllViews = True CopyAllStoredProcedures = True CopyAllUserDefinedFunctions = True CopyAllDefaults = True CopyAllUserDefinedDataTypes = True CopyAllPartitionFunctions = True CopyAllPartitionSchemes = True CopyAllSchemas = True CopyAllSqlAssemblies = True CopyAllUserDefinedAggregates = True CopyAllUserDefinedTypes = True CopyAllXMLSchemaCollections = True CopyIndexes = True CopyTriggers = True CopyFullTextIndexes = True CopyPrimaryKeys = True CopyForeignKeys = True All the security options are set to false. When I execute this task, it fails (with the errorCode=-1073548784) at a point where it tries to create a table which is inside a schema, because the script for creating the table is executed before creating the schema. So, if have a table [Person].[Employee], the script "Create Table [Person].[Employee]" gives an error The specified schema name "Person" either does not exist or you do not have permission to use it. It cannot be a permission related issue as am using the sa account to connect to both the source and the destination. Hence, I conclude that the SSIS task here tries to copy the table first without creating the schema and so the excution fails. Is this a bug in SSIS? or am missing anything? and what can be the workaround this?
View Replies !
View Related
Transfer SQL Server Objects Task - Problems
Hello all, A little background... I have a 25GB database (called DevDB) that my co-workers use for SQL development. The data in this database isn't important and all I really need are the SQL objects. So instead of doing a database backup, which includes the data as well, I was planning on just copying the database objects to another database called DevObjects (on the same server) and backing it up instead. This is SQL 2005 SP2. I've created an IS package and have 3 items... 1. Check for existance and drop DevObjects database. This is to eliminate the need for dropping the objects first. Successful. 2. Create a new database DevObjects. Successful. 3. Transfer SQL Server Objects Task. Fails. My transfer objects task is setup like this: The connections are to the same server, source db: DevDB, destination db: DevObjects. DropObjectsFirst: False IncludeExtendedProperties: True CopyData: False CopySchema: True UseCollation: True IncludeDependendObjects: True CopyAllObjects: True CopyDatabaseUsers: True CopyDatabaseRoles: True CopySqlServerLogins: False CopyObjectLevelPermissions: True CopyIndexes: True CopyTriggers: True CopyFullTextIndexes: True CopyPrimaryKeys: True CopyForeignKeys: True GenerateScriptsInUnicode: True I get an error trying to create a login that's not even in the database I am trying to copy the objects from. Since this is the same server, the login is already there. Is there a way to not copy the server logins (so that CopyAllObjects works)? Anyway, I got past that error by changing CopyAllObjects to False and setting all the subgroups to True. Now, I get an error creating one of the FK's, it seems that the PK it references isn't created first. Is there a way to force creating the PK's before the FK's? If I set CopyForeignKeys to False, then it completes successfully, but I need the FK's to be transferred as well. Any ideas? Does anyone have a better way to accomplish what I'm trying to do? Thanks in advance. Jarret
View Replies !
View Related
Problem With Transfer SQL Server Objects Task
Hello, I am using the Transfer SQL Server Objects Task to copy Stored Procedures from one DB to another. I quite painfully discovered the problems with using the "DropObjectsFirst" flag in that if you set it to true and the object does not exist in the destination DB, SSIS throws a "Object does not exist" error. If you set it to false and the object exists at the destination DB, SSIS throws a "Object already exists" error. Sort of a catch 22 problem, so I decided to use a Data Flow task to build a Recordset of SP names that were common between the Source and Destination DB's and then feed that to a For Each task to drop the existing SP's from the destination DB before running the "Transfer SQL Server Objects" task. So the problem is that I am getting 3 SP's copied over to the destination DB that do not exist on the source DB. If I delete all SP's on the destination DB and run the package it works fine the first time, when I run the package again, I get an "Object already exists" error at the destination DB for one of the SP's that don't exist on the source DB. To explain the details of how I am building the list of SP names to be dropped, my Data Flow Task uses 2 DataReaders with a query to sys.objects to get a list of SP's from both DB's. This is then fed to the required Sort transforms and into a "Merge Join" Transform (Joint Type = Inner Join). The Merge Join Transform output is then passed to a RecordSet Destination variable. When I query the source DB sys.objects, I can see all the SP's that were copied to the destination DB EXCEPT these 3 SP's. All the other SP's are successfully dropped on subsequent executions of the package except these 3. One of the 3 SP's (...Yardrequest...) does exist on the Source server (sort of) but when it gets copied over to the destination server, the Case of the name gets changed (only 1 letter in the middle of the name) to "...YardRequest...". I can't believe SQL is smart enough to capitalize each word in a SP name let alone what reasoning there could possibly be for doing such a thing. Any ideas on where these 3 SP's are coming from? My gut is telling me the DB got hosed somehow and therefore the Transfer SQL Objects Task is somehow seeing these addtional SP's that a simple query on the sys.objects view does not see. Not knowing how the "Transfer SQL Server Objects" task works internally (what queries it uses to identify what objects will be transferred from the source DB), I am not able to figure out where these SP's are located in the DB tables or views. Any pointers would be helpful. Thanks
View Replies !
View Related
Trouble With Transfer SQL Server Objects Task
I am struggling to copy a 2000 DB to 2005 using transfer sql server objects task. I can get it to work, but without the foreign keys, which I also need. When also copying the foreign keys, I get the following message:" [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=0 description=There are no primary or candidate keys in the referenced table 'SVS' that match the referencing column list in the foreign key 'FK_WRM_SVS_WRM_SVS'. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". Apparently, SQL tries to create a foreign key on the WRM table, while the primary key on SVS is not there yet. Since it used to work in 2000, I am pretty sure I am doing something wrong. I can harldy believe this is by design. (By the way, the CopyAllSchemas is set to True). Can anyone please help? Regards, Pipo
View Replies !
View Related
Problem With Transfer SQL Server Objects Task
I have the following problem: All Sp's and UDF's in one base database have to be compiled into five identical databases located in the same SQL Server in order to keep all those five db's stored procedures synchronized. I made a simple SSIS package which content one "Transfer SQL Server Objects Task" component. Hardcoded connection/Source db/Destination db and setup properties as "Drop object first" = True "Copy All Stored procedures" = True "Copy All UDF" = True Then I executed the Package right from Visual Studio environment and after the substantial thinking finally it show me a green light. But when I verified the result no Sp's in the destination database were re-compiled/copied from the source database. Is it kind of bug or I did something wrong ? Any advice will be appreciated.
View Replies !
View Related
SSIS Transfer SQL Server Objects Task
What is the security requirement for running this task? I have no problem running this taks as a system administrator but all my users who are in the db_dtsltduser cannot run the same task successfully. They are DBOs in both databases involded in the task. Thank you in advance for your help.
View Replies !
View Related
Question About Transfer SQL Server Objects Task
I was wondering if there could be a race condition when using the Transfer SQL Server Objects Task to copy all tables from an online database. I'm thinking of a scenario that while the copy task is executing, 2 different tables are updated in the online copy, with one new row each, and the new rows are linked by a key. Could it happen that when these values are inserted, one table has already been copied so the new value doesn't make it over, but the second table hasn't yet been copied so its new value does make into the destination (thereby breaking the relationship)? Or does this task take care of the details automatically? Thanks!
View Replies !
View Related
Bug?? Transfer SQL Server Objects Task - Error When Copying Views
I'm trying to copy data over using a Transfer SQL Server Objects Task however I get problems trying to copy over views. I get the similar error (pre sp1) when using import/export wizard and copying over views. Now with SP1 the views just don't transfer. I'm transferring from SQL 2005 to SQL 2005. The error I get is: Error: 0xC002F363 at Transfer Data (NEW), Transfer SQL Server Objects Task: Table "vw_XXXX" does not exist at the source. Task failed: Transfer Data (NEW) I know the view exists at the source because: A) I can select it in the Views List Collection within the Transfer SQL Server Objects Task B) I'm using the view on the source Somehow the views are not transferring. Anyone have any ideas?
View Replies !
View Related
Transfer SQL Server Objects Task Not Working With Stored Procedures
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.
View Replies !
View Related
The Task Transfer SQL Server Objects Task Cannot Run On This Edition Of Integration Services. It Requires Higher Level Edition.
Error code: 0xc0012024 Using "Integration Services Project" template in Business Intelligence Studio. Using platforms Visual Studio 2005 along with SQL Server 2005. Getting the error while trying to execute package after loading it programmaticaly. I've just one task "Transfer SQL Server Objects Task" on my Integration Services package. But when I try to execute it from VS 2005 project programmaticaly, it gives the above mentioned error. The commands I use: Package pkg = new Package(); pkg = a.LoadPackage(@"C:Documents and SettingsabcMy DocumentsVisual Studio 2005ProjectslSSISSSISPackage.dtsx", null, true); DTSExecResult dResult = pkg.Execute(); The the error comes like: error: 0xc0012024 The task Transfer SQL Server Objects Task cannot run on this edition of Integration Services. It requires higher level edition. Please help me. Thanks in advance, Bhupesh
View Replies !
View Related
SSIS Task &&"Transfer SQL Server Objects Task&&" And Default Constraints On Tables
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data. The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables. I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-) Any option anyone knows will help. Thanks.
View Replies !
View Related
Transfer Sql Server Objects Task Truncate Table Fails Due To Foreign Key
Hi We used to used Sql Server 2000 DTS to perform daily backups to remote servers very successfully but just cannot do the same with SSIS. I set my Transfer Sql Transfer Objects Task to use the following settings: CopyData = true ObjectsTocopy = All Tables As soon as it hits a table that has a foreign key, I get the following error: "Truncate data failed for Table nnn" I've tried setting DropObjectsFirst = True but this fails too with foreign key violations. First of all, am I doing something obviously wrong? Is SSIS not smart enough to drop tables in the right order so that we don't get foreign key violations? Am I totally barking up the wrong tree with this and can anyone suggest a better way of doing remote backups? Kind regards Ben
View Replies !
View Related
[Transfer SQL Server Objects Task] Error: Table Does Not Exist At The Source.
Hello, I am running a package that used to transfers data from one SQL2005 to another SQL2005. There are multiple schemas associated with the database. Until recently, this pacakage would work. Now I am getting the following error for all the tables not owned by dbo: Any help on this would be appreciated. Thanks, sck10 [Transfer SQL Server Objects Task] Error: Table "tblAudiocast" does not exist at the source. Microsoft SQL Server Management Studio 9.00.3042.00 Microsoft Analysis Services Client Tools 2005.090.3042.00 Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.11 Microsoft .NET Framework 2.0.50727.832 Operating System 5.1.2600
View Replies !
View Related
Transfer SQL Server Objects Task - Cannot Use SQL Server Authentication
I created a simple Transfer SQL Server Objects Task to copy several tables from one SQL Server 2005 database to another. I created and tested two connections. When I tried to execute the task I received the error: "Cannot apply value null to property Login: Value cannot be null..". I am running Visual Studio 2005 SP1 and SQL Server 2005 SP2. After checking numerous forums, etc, the consensus seems to be that this is caused by a bug in SSIS ( as described here http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127100) that occurs when the destination connection uses SQL Server Authentication (which I need to do). I have not been able to find any reference to anyone successfully solving this problem, other than Microsoft claiming that the bug is fixed and users claiming that it isn't. Has anyone been able to get around this problem?
View Replies !
View Related
Transfer SQL Server Objects Task Throws An Error Because The DropObjectsFirst Property Does Not Check Whether Object Exists.
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'?
View Replies !
View Related
Login Dropped By Transfer Objects Task
Hello all :) First, sorry for my english but I don't practice everday So, I'm a new user of Microsoft SQL 2005 and I'm testing some functionalities in SSIS. I used the Transfer Objects Task to copy one database to another. The both db's are on the same instance. I chose these options for the transfer -SourceConnection : My_Instance -SourceDatabase : My_DB -DestinationConnection : My_Instance -DestinationDatabase : CopyOfMy_DB -DropObjectsFirst = True -IncludeExtendedProperties = Fasle -CopyData = True -ExistingData = Replace -CopySchema = True -UseCollation = True IncludeDependentObjects =False CopyAllObjects = True The others options are set on FASLE When I execute the package, I got an error message, the logins are dropped. I cannot connect to the database engine, even with my admin account. I have to restore the master database ! So my question is : why this SSIS task drop all the logins in sql server in place of just copy one database?????? What's the solution for this problem? Thanks for your comments ! Matt
View Replies !
View Related
Transfer SQLServer Objects Task Failure
I have written an SSIS package with a Transfer SQLServer Objects task which I want to use to copy database objects from a SQL Server 2000 database to SQL Server 2005. When I run this task, I find the following error: [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Version80 database compatibility level is not supported Is there a way around this aside from changing the compatibility level of my SQL Server 2005 database? Any help is much appreciated.
View Replies !
View Related
No &&"Enable Identity Insert&&" For Transfer SQL Server Objects Task
Environment: Integration Services is being used to transfer tables from a SS2005 (80) database to a SS2000 database. Tables cannot be transferred successfully because the "Enable Identiy Insert" option is not being set predictably in the Transfer SQL Server Objects task. Without this option being ON, the key values will not be the same in the duplicated table. Example: If CategoryID has values 3, 4, and 5 in the source table, then in the destination table these values will be 1, 2, and 3. Is there a way to get around this? Will this functionality be added at a later date (SP2)?
View Replies !
View Related
Simplest &&"Transfer SQL Server Objects Task&&" Failure
I'm perplexed and in need of a sanity check ... Instance of SQL2K5-SP1 on Server2003R2. Created two databases Test1 and Test2, create single 2-column table (tblTest)in Test1 with one row of data. Create SSIS package to transfer the table and its data to database Test2 using "Transfer Objects Task". Fails with error code 1073548784. I have tested for security issues and don't beleive these to be the cause: I have monster privelidges and can successfully use SSIS packages to execute CREATE TABLE etc. Have also tried most combinations of Task options (eg COPY ALL OBjects etc etc) Have tried this on three different servers: same results. This simple test is the result of distilling down problems I'm having with SSIS manipulating some 60G within some 300 tables, so I'd really like to use the power this task promises! My head is getting a little sore from scratching - any advice gratefully received. Thanks
View Replies !
View Related
Problem With &&"Transfer SQL Server Objects Task&&"
Hi everyone, I'm currently trying to copy a database from one server to another (both SQL2005) using Business Intelligence Development Studio. I've created an SSIS package. The following parameters are defined: DropObjectsFirst true IncludeExtendedProperties true CopyData true ExistingData Replace CopySchema false UseCollation false IncludeDependentObjects true CopyPrimaryKeys true CopyForeignKeys true The package fails with the following error: Violation of PRIMARY KEY constraint 'PK_tblCallStatus' Cannot insert duplicate key in object 'dbo.tblCallStatus' I know what that error means but I don't understand why I get it. Isn't the package supposed to completely overwrite the destination database ? It obviously does not. When I manually delete all records from 'tblCallStatus' in the destination database it works fine. I can't remember I had to do that in a SQL2000 environment using DTS. Hope anyone can help since this is almost driving me nuts ;-) Thanks in advance, Kevin
View Replies !
View Related
Another Problem With &&"Transfer SQL Server Objects Task&&"
Hi everyone, I'm currently having some trouble with the "Transfer SQL Server Objects Task". All I wanna do is to copy a simple database from one server (SQL2000) to another one (SQL2005). A small excerpt from my settings: DropObjectsFirst: true ExistingData: Replace All table options are set to true . No matter what I do the package always appends the data from the source to the destination database. Is this a bug or am I missing something ? Thanks in advance, Kevin
View Replies !
View Related
Default Values Of Columns Not Transferred In SSIS Transfer Objects Task
I am working on a project that is creating a new application, my area of the project being the migration of data from the old application database, transforming it, and populating the new database. The transformations to the old data are done in a staging database. At the end of the process, the staging database ends up with a lot of new applications tables, populated with the migrated legacy data. We need to move these tables from the staging database to (initially) our test databases, but ultimately what will be the live database. We have tried using the "Transfer SQL Server Objects Task" in SSIS, but have ran into a problem that a lot of the database tables have default values for columns. These default values are not brought over. Example. Tables contain a "GUID" field, which has a default of value of newid() Right clicking and the table generating the CREATE script generates [GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_tbCRM_Client_GUID] DEFAULT (newid()), However, the Transfer objects task does not create this default of newid() Examining the SQL generated by the Import / Export Wizard when investigating this shows that the wizard generates this column as [GUID] uniqueidentifier NOT NULL and the column default value is lost. Is there something i should be setting somewhere to force SSIS to bring these column definitions over correctly?
View Replies !
View Related
Package With File System Task Doesn't Work Without Sensitive Data With User Keys
This problem is a bit weird but I'm just wondering if anybody else experienced this. I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter. Just wondering if anybody else experienced this problem and of course if there's a way to solve it. Thanks.
View Replies !
View Related
Transfer Table With Schemas Other Than Dbo
My problem is very simple and that is that I'm trying to copy some tables between databases, but these tables are in different schemas. let's say I have dbo.tableA sch1.tableA sch2.tableA sch3.tableA And I just want to copy let's say sch1.tableA to a Different DB. If I use Transfer SQL Server Object task and select the table and save the package and try to open the task again, all the tables with name TableA will be selected!! it seems like although it does show the schema ( when I am selecting the table manually ) but it doe snot store the schema detail in the tablelist collection property of the task. Would please recommend any other way to achieve this. Many Thanks in advance
View Replies !
View Related
Transfer Sql Server Objects
Is there a way to set up the "Transfer SQL Server objects" task and have it dynamically copy of certain tables/data? I see the TablesList values in the "Objects to copy" section and I'm seeing it can be an expression (which I can assign a variable too), but what value is that string if you have multiple tables? Thanks, Phil
View Replies !
View Related
SqlServer2005: Transfer Sql Server Objects
I'm looking for help in getting the Transfer Sql Server Objects task to work. The goal is to transfer the tables, views, sprocs,udfs from a SqlServer 2000 database to a Sql Server 2005 database.When I set the task up and run it, it fails complaining about an invalid foreign key relation. The relation in question is valid,and used in the current production environment under SS2000, so I think the real problem is something else. I've seen reports thatthis kind of error occurs when the Transfer task encounters a foreign key involving a table that hasn't been transferred yet.As an aside, this kind of transfer worked flawlessly under Enterprise Manager when I used the DTS Wizard to move the objects betweentwo instances of SqlServer2000. Unfortunately, Enterprise Manager doesn't appear to work with SqlServer2005.Any help and/or pointers would be greatly appreciated (including other strategies on how to affect the transfer -- I tried using theTransfer Database task, too, but it failed when it supposedly couldn't find the path on the target server -- I say supposedlybecause it was able to find the path in order to delete the pre-existing files on the target server)!- Mark
View Replies !
View Related
Transfer SQL Server Objects Errors
Hi. I'm totally new to SSIS (SP1), and I'm having a raft of troubles transferring DB's using the 'Transfer SQL Server Objects Task', which seems to be the closest replacement for the old DTS Transfer Objects tool. I'm trying to transfer a DB from an SQL Server 2000, where I only have SQL Server authentication (it's a shared hosting environment) to my local SQL Server 2005 server, where I'm logged in using Windows Authentication (although I have the same issues if I use SQL Authentication locally). Here's the list of errors I'm getting. If I don't select 'copy all tables', I receive a "table does not exist at source" error. If I do copy all tables, suddenly it can find the same table.If I select "Drop objects first", and the object doesn't exist in the destination, in throws an error.The "replace data" doesn't appear to work - it throws a "cannot insert duplicate key" error if the table I'm copying already contains data.If I copy Primary Keys, and one of the tables doesn't have a primary key, I get a "set identity" error, presumably because it's trying to set an ID on that table at the destination.Finally, if I delete all data at the destination, I'm currently receiving a "Invalid character value for cast specification". Which I don't get at all - why is it casting at all, if it's copying objects - they should be identical on both sides?Any and all suggestions would be really appreciated. I'm also having (different) problems transferring objects between SQL Server 2005 servers, but nothing in production, so it's less important. Cheers, Steve.
View Replies !
View Related
SSIS SQL Server Transfer Objects
Hi, i have a ssis package that copies a database using online method of the database transfer task. I realize that this task does not copy my keys and indexes, therefore i went and used the SQL Transfer Object Task which would transfer my keys and indexes, but does not copy the default value of a column, is there something i need to change/turn on for this to work ? Thanks, Christopher
View Replies !
View Related
Error In Transfer SQL Server Objects
Hi , i am copying objects from one DB to other usign Transfer SQL Server Objects Task. i am getting this error [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "DROP TABLE [dbo].[testCopy] " failed with the following error: "Cannot drop the table 'dbo.testCopy', because it does not exist or you do not have permission.". 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}". it is true that object doesnt exist on the destination. but i want it to see if the object is there then drop otherwise just copy the object there, Regards, Haroon
View Replies !
View Related
DTS Transfer All Objects Failing Due To Invalid Objects
I'm having difficulty using DTS to transfer all objects from one database to a new database on the same server: it keeps failing due to Invalid Objects. The objects it lists as being invalid may have existed once upon a time but certainly are long deleted. Has anyone else had this problem, or does anyone have any idea of how to fix it?? Thanks in advance Bernard lerouxb@hotmail.com
View Replies !
View Related
Trouble With SQL2005/SSIS And Transfer SQL Server Objects
New to SQL2005/SSIS, somewhat experienced with SQL2000/DTS. I have two identical databases, "AppMaster" and "AppTest", on the same instance of SQL Server 2005 Developer. I make changes to the table structure of AppMaster, and then propagate the database structure (no data) to AppTest. This used to work fine with SQL 2000/DTS, but I can't get it to work with SQL 2005/SSIS. I created an SSIS package with a single Transfer SQL Server Objects task with the following: - single connection to server (local) - SourceDatabase = "AppMaster" - DestinationDatabase = "AppTest" - CopyAllTables = True - CopyAllStoredProcedures = True - CopyIndexes = True - CopyPrimaryKeys = True - CopyForeignKeys = True When I execute the package it completes successfully. I get several messages in the Output Windows stating that: - "...There are no Logins to transfer." - "...There are no Users to transfer." and on and on... There are no references to tables, stored procs, or any of the other things for which I set Copy = True. When I check the AppTest database in Management Studio, the AppTest database has not changed. BTW - I tried running the package using a single connection for both databases, then with separate connections. No difference. So I'm at a loss. Am I missing something or is this a bug? Thanks for any help.
View Replies !
View Related
SQL Server 2000: TCP/IP Doesn't Seem To Work
I have the following situation: A webserver in a DMZ which connects to a DbServer in a Domain. but when I try to make a ODBC connection on the webserver I get the well-known "Server does not exist or access denied" error. I tried using IP address as well as FQDN. I turned off the firewall in the router (I'm able to access shares and what not from the webserver so the firewall is really turned off). I think however i have narrowed down the problem to the TCP/IP connection of the SQL Server. Normally when you try from a command prompt "telnet dbserver 1433" you get a connection. But when I try this to the specific DbServer I get an "unable to connect" error. Even when I try it on the dbserver itself (so telnet localhost 1433). This should work always? In the network configuration of the sql server the Named Piped as well as the TCP/IP protocols are enabled. TCP/IP is offcourse configured on port 1433. How can I fix this problem?
View Replies !
View Related
ASP.NET Cachin With SQL Server Doesn't Work. Help Needed.
Hello! I'm trying to get caching work with ASP.NET 2.0 and SQL Server 2005, but I have a problem and I can't figure out what I'm doing wrong.I have tried to do everything according to the instructions. I have enabled the database for cache notification by running the aspnet_regsql tool on the command line like this:aspnet_regsql -S server -E -d database -etThen I have run the aspnet_regsql tool to enable each table in the database for cache notification:aspnet_regsql -S server -E -d database -et -t tablenameI have added the necessary parts into web.config file, so that it is like this:<connectionStrings><add name="ConnectionString1" connectionString="DataSource=servername;Database=databasename;Integrated Security=True"providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <caching> <sqlCacheDependency enabled="true" pollTime="2000"> <databases> <add connectionStringName="ConnectionString1" name="KJ"/> </databases> </sqlCacheDependency> </caching>I have added this into global.asax file for the application start event: System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())To test caching I wrote this piece of code where I get data from the table MAAKUNNAT in the database.----------SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString(),"MAAKUNNAT")Dim maakunnatDS As New DataSetIf Cache.Get("maakunnat") Is Nothing Then Dim myConnection As NewSqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString()) myConnection.Open() Dim cmd As New SqlCommand("haemaakunnat", myConnection) cmd.CommandType = CommandType.StoredProcedure Dim riippuvuus As New SqlCacheDependency("KJ", "MAAKUNNAT") Dim aggDep As New AggregateCacheDependency aggDep.Add(riippuvuus) Dim maakunnatDA As SqlDataAdapter = New SqlDataAdapter maakunnatDA.SelectCommand = cmd maakunnatDA.Fill(maakunnatDS, "MAAKUNTA") myConnection.Close() Cache.Insert("maakunnat", maakunnatDS, aggDep) Else Response.Write("The data is in the cache") maakunnatDS = Cache.Get("maakunnat")End If-----------------But when I run the page I get this error.------------------------------------------The 'MAAKUNNAT' table in the database 'KJ' is not enabled for SQL cachenotification.Please make sure the table exists, and the table name used for cachedependency matches exactly the table name used in cache notificationregistration.To enable a table for SQL cache notification, please useSqlCacheDependencyAdmin.EnableTableForNotifications method, or the commandline tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?'for more information.To get a list of enabled tables in the database, please useSqlCacheDependencyManager.GetTablesEnabledForNotifications method, or thecommand line tool aspnet_regsql.exe.----------------------------------------------When I run the aspnet_regsql tool with the -lt parameter to get a list of enabled tables in the database they are all enabled. I have searched many pages in the internet and I cannot find out what I'm still doing wrong. I'd appreciate it very much if someone could help me with this problem.Toni S.
View Replies !
View Related
SQL Server Destination Doesn't Work On Vista?
All, I have a package that works fine on XP. I copy it (and the databases used within it) to my new Vista Business Edition box (with SQL 05 SP2 with Hotfix.) Both the source and destination databases are on the 05 SP2 with Hotfix installation on this Windows Vista box (this is a completely local transformation.) The account I am logged in with is an Administrator on the box (and I have done the whole Vista provisioning deal.) Furthermore BUILTINAdmins is not disabled. Furthermore I went so far as giving this account every server role. Short of it is when I use SQL Destination it fails - regardless of the options used. When I use an Ole Db Destination it works. So, to prove that I am not crazy I recreated this package from scratch, and used a SQL Server Destination. The account I am using has essentially every permission imagineable. And to repeat: yes, I'm transferrring from a local database (on this box) to another database (on this box) and SSIS is running through BIDS on this box. At this point I'm beginnning to think that it is broken on Vista, but I can't believe someone else hasn't run into this. However, when I look at the groups - nothing. Any ideas? Thanks, dcb99
View Replies !
View Related
Sql Server Doesn't Work Well With 250ms+ Network Latency
Hello,I'm an absolute newbie when it comes to SQL. I was told that SQLserver does not function well on a WAN where network latency between,say, the SQL server and a front-end server is greater than 250ms.I can't find anything information supporting this claim online, so Iwas hoping someone here could tell me if this is true or not?Thank You!!
View Replies !
View Related
SQL Server 2005 Query Logging Doesn't Work
I have been following 3 tutorials to activate and use SQL Server 2005 Query Logging feature in the Analysis Server but none seem to work. I first tested that the connection string worked and it did work. I set the flag to create the table to TRUE and it does not create the table. I tried using filebased logging and no file gets created. I made certain i use QueryLogginFrequency of 1/1 so it logs everything. I second thought was that maybe only .NET applications got logged, but i checked out my current app i'm developping and nothing gets logged. I'm a bit clueless, can anyone point me to something i may have missed that is not obvious in the first few tutorials you find on google...? PS: I did restart my services (MSSQLSERVER and Analysis Server)
View Replies !
View Related
Maintenance Plan For SQL Server 2005 64-bit Doesn't Work!
Hi all, We have a SQL Server 2005 64-bit, and recently I upgrade from build 3042 to 3054 and I try to do a maintenance plan for transaction logs(TL) backup, including cleanup for two days (have full backup every night). Problem I have is that I want the TL files to dump in a different location(due to disk space), so I put in the UNC path in the "Create a backup file for every database - >Folder:\FileServerTLDBLogs" NB: if using the local drives, it work Check List Security: - The account that I used to create the plan is an sa account - The location that I dump the TL files, I have full access to the folder SQL Statement: exec xp_cmdshell 'dir FileServerTLDBLogs' (it list all files) Is this a bug for 64-bit? because I can do this on SQL Server 2005 32-bit and it's work perfectly
View Replies !
View Related
|
TRACKER
