I want to create an automatic job on SQL 2005 to transfer logins between 2 servers, (Principle and Mirror). If I use the transfer logins task I get the error:
"The database "x" cannot be opened. It is acting as a mirror database."
I guess this is because it is trying to assign the default databases for the logins??
I've also looked at [sp_help_revlogin] procs but that requires manual intervention.
How does everyone else transfer logins in this situation?
I have a SSIS package that transfers a couple of tables from one database to another database on the same server. It works fine in most of the machines we tested with. However on one of the customer machines, it consistently fails with the error message, The Return value was unknown. The process exit code was -1073741795. The step failed.
This package runs as a scheduled job on the sql agent. When i did a sql profiler to see what is going on, i noticed that the last step before the bulk insert, it gets the collation and the schema id of the 2 tables. My guess is, it compares these values from the source and destination databases and makes sure everything is ok before copying.
On a machine where this works, it goes ahead with the the next step which is the bulk insert itself. Whereas on the machines where it doesn't work, it stops right after this step. i.e. it does not even bother to call the 'bulk insert' api. Which makes me think it is doing some kind of validation with these values and it is getting something that is not expected.
If the collation or the schema was an issue, why throw an 'return value unknown' error? Can the error be more specific? Any other possible reasons for such a behavior? Any clues?
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?
I have to transfer a database to a new machine. I am planning on doing a backup and restore. My question is, will the logins and roles be transferred along with the database?
I've created some SSIS packages to move logins from the Primary server to the Mirror server. But I'm having a small glitch. Lets say I have a database dbA that's mirrored. I then add a login of some sort, say userA. userA's default database is dbA. When I add that login to the mirrored server, how do I get the default database to be dbA? Currently I'm getting the following error from Alter Login:
ALTER LOGIN [userA] WITH DEFAULT_DATABASE=[dbA]
Msg 954, Level 14, State 1, Line 1 The database "dbA" cannot be opened. It is acting as a mirror database. I'm currently adding the logins with master as the default and then using Alter to attempt setting it. I can mask the error with a TRY block, but if I fail over it seems I'm going to have an issue. Any thoughts on this would be greatly appreciated.
I've actually only one user. I've replicated the user with the sp_help_revlogin Store Procedure to create the logins with the same SID on both SQL servers.
Now the application that is used with this DB will handle per user authentication.
Of course the application will use SQL login for the users connections.
Now, I can easily automate the sp_help_revlogin usage for the creation of new users, but I've another problem. The users can change their passwords through the application.
I'm wondering which process using for this:
Dropping the users on the Mirror server €“ running the sp_help_revlogin on the primary to re-extract the login information and applying the login creation against the mirror server
Checking the modify date of the login in the master DB and dropping this login on the mirror and re-creating it from the primary
I am using SQL 2005 (Enterprise Edition, SP2) Mirroring with a Principal, Mirror and Witness set to High Safety mode and am a little confused!
As far as I understand it, during a failover, although the database content will automatically be available on the Mirror any user accounts will need to be either manually created or manually mapped to the new principal. Is this the case? If so, I can't really see the purpose of using Mirroring (in my scenario anyway).
For example - We principally use the database servers for our web applications which we connect to using ADO.NET. Each database and Web app has a different SQL user account to allow access etc. If we experience a failover this means the users will not be able to use the web app until we have manually recreated/mapped the SQL accounts for each and every database. Surely this is a massive oversight?! Or am I simply getting the wrong end of the stick.
I am in the process of implementing database mirroring. I am trying to get an understanding about manageable ways of keeping sql user logins synced between instances.
I have looked at KB918992 which makes sense for the initial intallation, but what about keeping passwords synced in the long-term. I don't want to have to manually set a users password on the failover every time they change it.
How is everyone else accomplishing this using sql logins (not windows auth)? Pointers to articles and/or documentation would be great.
I am hoping someone can help me with a problem I am having with SSIS. I am attempting to move certain sql agent jobs from our older sql 2000 server to our new sql 2005 server and I am receiving the error [Transfer Jobs Task] Error: Execution failed with the following error: "The specified '@server' is invalid (valid values are returned by sp_helpserver).".
Does my server name need to be the same if I am using SSIS to transfer jobs? Is there a way to update the server name during the execution process?
This seems like it should be a relatively easy task but has not been so far.
I am transferring DB2 data to SQL server 2005 in ssis. the connection i I am using is the IBM OLEDB driver for DB2 which uses the ODBC connection on the machine. The issue is that while transferring data i get errors reg unicode vs non unicode data. the db2 data is showing unicode data, but when i see the datatype in the db2 database it is a varchar. so when i transfer the data to a varchar in sql server 2005, it errors out....meesageis unicode vs non unicode data. Why is the data from db2 coming across as unicode string. Also the transfer works fine in SQL server 2000, why is is not working in sql server 2005. Please help !!!!
As we all know logins and jobs aren't replicated when using AllwaysOn. What i wanna know is if the "tranfer login taks" will be enough to cover the transfer of logins between servers.
I need to make a general package to copy SQL databases between servers. A package with fixed task properties work fine. But my plan is to make a general package and use varables to handle parameters like connection strings, server names, database names etc. My problem is the Transfer Logins Task and its collection properties like LoginsList and DatabasesList which uses string collections. In my understanding I can't make a ordinary variable in "design mode" for string collection. For that reason I wrote some code in a Script Task to set an Object variable to a string collection. And then use the object variable to set the LoginsList or DatabasesList property.
But I get following error: TITLE: Microsoft Visual Studio ------------------------------ Nonfatal errors occurred while saving the package: Error at Transfer Logins Task: The data type of variable "User:ourceDB_list_o" is not supported in an expression. Error at Transfer Logins Task: Reading the variable "User:ourceDB_list_o" failed with error code 0xC00470D0. Error at Transfer Logins Task: The expression "@[User:ourceDB_list_o]" on property "DatabasesList" cannot be evaluated. Modify the expression to be valid.
My code to set the object variable:
Dim DBs As System.Collections.Specialized.StringCollection = New System.Collections.Specialized.StringCollection()
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Hi, I have to transfer data from JDEdwards source system to SQL Server 2005 using SSIS. Is it possible for me to connect to JDEdwards directly from ODBC Connection Manager provided by SSIS. If not then what is the way to implement this
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 :-)
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).
I am in the process of setting up a SQL 2005 environment with mirrored database. We have run into a couple issues with mirroring and I have been told that SSIS (which I know little about) may be able to solve. First, when we failover a database I receive access denied messages because the user does not exist on the secondary instance. Second, I need to copy the maintenance jobs to the secondary database.
1. Is there a way to copy the users from the primary to secondary (and associate with the database) using SSIS?
2. Is there a way to copy the mainteance jobs to the secondary?
...or I am just looking in the wrong place with SSIS.
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
Please can anybody help me in transferring existing SSIS Packages saved in a shared folder location from development server 2ED to Live server TWD1. Both has SQL server 2005 running and has visual studio 2005 Currently about 25 SSIS packages are executed from the development server transferring data on Live server TWD1...these ETL process is called from development server but executed on live server. Now the problem is when i call these packages from the shared folder from live server it crashes.....i need to changes something to shift the whole package to the live server..and execute on live server itself instead of recreating the whole 25 process from scratch.....also i use optimize for many tables ..and run in a single trancastion....so how can i see the mappings of source and destination tables.
Please let me know the process how i can achieve this. Thanks George
I've created my own posting for this. The original post was here, I apologize: http://forums.microsoft.com/forums/ShowPost.aspx?PostID=2906512&SiteID=1
According to the poster it's not possible. But there has to be some way to do it? Reflection (don't know how)?
I need to get a reference to the task host in an SSIS Task component.
Basically the scenario is this:
I have a custom task I have created. However I would like to validate that the ExecValueVariable is infact a string variable during the validate event of the task. I know how to verify its a string variable. But I can't figure out how to read what the user selected (such as User::Myvariable). The only way I've been able to figure out how to do it, but it only works if you open my custom task UI.
What I did is this:
I've implemented IDtsTaskUI and during the initialize section I wrote:
Sub Initialize(ByVal taskHost As TaskHost, ByVal serviceProvider As IServiceProvider) Implements IDtsTaskUI.Initialize ' Store the TaskHost of the task. Me.taskHostValue = taskHost Dim myTask As CustomTask= CType(taskHost.InnerObject, CustomTask) myTask.myTaskHost = taskHost End Sub
My Task is named: CustomTask. I have a public variable in my task as follows:
Public NotInheritable Class CustomTask Inherits Task Implements IDTSComponentPersist Public myTaskHost As TaskHost = Nothing
Therefore I pass back the taskhost value to the CustomTask class, and voila I have it.
Problem is, this only works if the custom task calls the initialize method, and this only happens when you open the custom editor.
I then do the validation in my CustomTask class and it works fine, but myTaskHost is null/nothing until you actually open the custom task UI
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem? Please, help Thanks
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service. When we install the app, we run a process that builds 2 dbs to include: Tables, indexes, stored procedures, views and user accounts. SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names. We have a client that wants to remove our standard logins so that they can run on only a Windows login. I know I should be able to tie the db users to a Windows login. And I can do the same for the service.
But I am at a loss as to how to get this done. How do you associate db users with a Windows login? When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Sorry guys, I'm really new to this. Can somebody tell me the easiest way to use the FTP task with a dynamic local path. I just want the FTP task to send whatever file is in the directory I specify.
I'm creating a package that extracts data from a table to a pipe-delimited file. I then use an external process (since using Expressions for it was a nightmare) to rename the file so that it'll reflect the previous month (e.g. April2008_report.txt). Then, I have to FTP that file. That's where my problem is. It seems I need to specify the exact filename on the local path of the FTP task, and since the filename will change each month... well, you see my problem.
Is there a way to tell the FTP task to just send *.txt, or something to that effect?
I have a master database. Based on certain criteria's I will be creating one new database. Once I have finished the creation, I have to check the value of a particular column in both database tables.
This is actually a status check. if both are not equal i have to update detail database table column value with master column value.
I thought i can use lookup transform for this. The problem is master database table's status datatype and detail table's status datatype is diff.
Can anyone suggest me any other way for this problem?
In my package i am loading data from a flat file into multiple tables like table1, table2, table3 all tables have a column called cust_num. But data for this column cust_num is only coming in table1 and i need to load the data from this column to other tables how can i do this.
I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.
I have some 15 tables being populated using this SPROC.
Here is some helpful description ENTITYNAME gives me the table i need to work FIELDNAME gives me the field i have to work on CHANGEDVALUE gives me the value changed in that field ( This three i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )
while in Cursors i need to get using these above variables other variables like FLAG KeyName
Thrugh SQL1 I get the KeyValue
then using this KeyValue check if the data exist update else insert new data.
QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?
I m having an FTP Task in SSIS project, from where I have downloaded .csv file.
And also having a Processed Folder in which the processed .csv files are kept.
Main task is to download file from FTP and check , if the date of downloaded file is newer than file in processed folder, then only process that file. Else skip the processing
