September 14, 2011 at 11:08 am
Hi All,
I have requirement where we have table on one instance and i need export all the data on that table to other few instances in same domain, This should be done by the sql agent job everyday night. please help me with the process and steps if you can.
September 14, 2011 at 2:09 pm
are you just replacing the tables on the other databases? do you need to ensure that the primary key inseted into these new tables are the same? are the DB's all on the same SQL instance?
***The first step is always the hardest *******
September 14, 2011 at 2:15 pm
do that by import, export task on the instance
and den select save ssis package in end
then make a new job with tht ssis package running the time u want to
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
September 14, 2011 at 6:50 pm
Please reply to Glen questions and also please post as many details as possible..
Thank You,
Best Regards,
SQLBuddy
September 14, 2011 at 7:41 pm
Consider using BCP out to create a file on file server that all the other instances can see, then use BCP in from each of the instances that need the data. This will mean only on read through the whole table to extract the data, and all instances will get the same data from the flat file, no chance that the data can change between different instancs reading it.
I haven't tested it recently, but genrally I've found BCP to be faster than and SSIS package.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 15, 2011 at 4:00 am
🙂
Try Using REgistered Server. You Need To Register All You Instances and Run Query It Will Create User In All The Instances.
IF Not Exists(Select Name From Syslogins where name='Dummy')
CREATE LOGIN [Dummy] WITH PASSWORD=N'password@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
Go To Menu : View-Registered Server. See Following Image For Help. So What Ever Query U Run In this Query Window It Will Be Executed for every Registered Server.
September 15, 2011 at 4:02 am
Hi All,
Actually my requirement is we have 10 instances in QA in same domain, if we create login on one instance then it should be created on other 9 instances, second thing is we had taken all the users from 10 instances and kept them in a table, so when ever we craete new login we need to add the new login to that table, so everynight this table should replicate all the instances. where this table will be present on all other instances. Any help will be helpful..
September 15, 2011 at 4:53 am
madhuu1905 (9/15/2011)
Hi All,Actually my requirement is we have 10 instances in QA in same domain, if we create login on one instance then it should be created on other 9 instances, second thing is we had taken all the users from 10 instances and kept them in a table, so when ever we craete new login we need to add the new login to that table, so everynight this table should replicate all the instances. where this table will be present on all other instances. Any help will be helpful..
Well, Logins are stored in master database for each instance seperately and it is not the best of ideas to make any modifications to the system tables/catalogs especially of master database. You might break something up which will take much more effort to fix than creating logins on the 10 instances.
If all members need same privilages, one way is to create a domain group and assign appropriate privileges to that group. Any new login addition should be done to that group at a domain level which inherits the privilages of the group.
add this group on all instances. this will be a one time task.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply