I have migrated databases and logins from SQL server 2008 R2->SQL Server 2014. Now I also want to migrate/copy the "linked servers". Do you have a step-by-step for this, so that i will copy everything that is necessary regarding logins etc ?
View 2 RepliesI have SQL Server 2005 on a DEV box and I have a linked server on that box.
It has several logins that I would like to move over to our production server when I re-create the linked server.
Now I can export the script to create that linked server, but when I do so it doesn't provide me with the user's login password.
Is there a way to export the password as part of the script so I can move the linked server AND all the users associated to it over to a new server?
Can anyone refer me to good 'recipes' or sources of information on thistopic??I have Visual Studio Tools for Office, which installs SS 2005 Expresslocally to my XP box, and I want to develop in SS 2005, then copy thetables or queries or reports etc. to a SS 2005 Standard server.Thank you, Tom
View 3 Replies View RelatedI have a database that is off site, it has a database one it that I have a Linked server connection to. We have no other means of connecting to that server database except via that linked server connection. Is there a way I can Copy eplicatemoves etc... that database to an internal server with out doing it table by table. It is apx 80gig db
Copying a backup file is not an option either, and Mailing it on media make it a significant delay.
I'm trying to find a way to insert data from a TableA on ServerA into TableB on ServerB using SSIS in Visual Studio.
The specification I was given is basically
Insert INTO TableB AS (Select * from TableA WHERE NOT EXISTS on TableB).
I can't use a linked server unfortunately.
I wonder if it possible to move data from tables on a linked server to a "normal database"? What am I doing wrong?
Hi All - does anyone have a step by step guide as to what needs to be set up on the Server from the Oracle side, for Oracle linked servers to work ??
I keep getting the Eror 7399 ; OLEDB provider 'MSDAORA' reported an error.
Thanks,Jazz.
Hi All - does anyone have a step by step guide as to what needs to be set up on the Server from the Oracle side, for Oracle linked servers to work ??
I keep getting the Eror 7399 ; OLEDB provider 'MSDAORA' reported an error.
Thanks,Jazz.
I'm working with SQL Server 2000 installed in Windows 2000. When i tried to add the 'servername' as a linked server , i got the following message:
" Error: 15038. The Server 'servername' already exists."
When i execute "sp_linkedservers" , i could see the name "servername" in the list. But before doing this i had Restored the "MSDB" database on this server. Should i use "sp_droplinkedservers" and add them again.
Any comments are most welcome.
Thanks, Santha.
I've set up a Linked server connection to a Windows 2003 server running MySql using SSL.
I can run SQL and T-SQL from the query window using the Server management studio on the
server, and everything is good.
As soon as I apply a trigger to a table on our local server running the same
script, I get a "linked server unable to begin a distributed transaction"
error.
I've google'ed my brains out on this error and tried both Mysql ODBC drivers
3.51 and 5.1 and keep getting the same error.
Any suggestions? Thanks, Tony T
Hi Everybody,
Can anyone tell me what is the difference between Remote Server and Linked Servers.
thks in advance,
Srinivasan.
Does anyone know where I can get some good examples of how to set up a linked
server to an Oracle data source in SQL 2000? I can't seem to get a link
set up to my Oracle database. In particular I am wondering how the communication works between OLEDB to the Oracle Instance and how to trouble shoot issues (for example, would is the Oracle Listener what OLEDB is talking to?) Thanks!!
Hi, I am trying to use SQL Server 2005 Enterprise Edition to copy tables from my local server to my hosts server at brinkster.com. In the old version you could use DTS, but everything seems to have got far more complicated now! Please can anybody guide me in the right direction? Thanks a lot.
Below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3
/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'
This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".
How to setup the linked server........... Below are the drivers installed on the SQL server
PostgreSQL35W
PostgreSQL30
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
I have a schedule job that I would like to run on a MSDE database. Thestored proc executes just fine if I run it manually. But trying toschedule it through the Enterprise Manager (or Management Studio)generates an error saying the the "Remote Access not allowed forWindows NT user activated by SETUSER.Essentially I have tables in the MSDE which are being updated based ontables from a LINKED SQL Server.I can not appear to get this job to execute unattended.Please advise,Rob(rkershberg@gmail.com)
View 2 Replies View RelatedIs there a way in SQL2005 to copy the linked server password from SQL2000?
We are about to copy our SQL 2000 database to SQL 2005 (and change operating systems and make other changes to the system). We can't do an upgrade in place because of the other changes, plus we want to have the original in case we can't get it updated before the start of business the next day.
I plan to move the databases by copying the files and attaching them, and I have a program that pulls users and encrypted passwords and adds them to the new system without encrypting the already encrypted passwords. Jobs I can script and run on the new system, DTS jobs I can open and save to the new system, and linked servers I can get the names of and link on the new server.
In SQL 2000 I would have "cheated" and copied the "DTS" and "Jobs" tables from one server to the other, but SQL 2005 has made it more complicated, so I will do it the slower way.
The problem comes with the linked server passwords. While I can see the linked server data in the master system tables, including the names and that there is an encrypted password. The problem is I don't know the passwords, so I can't enter it myself, and I can't update the system table, so I can't insert it as is. And sp_addlinkedsrvlogin does not have an option to not re-encrypt the password (like @encryptopt = 'skip_encryption' in sp_addlogin).
Is there a way to figure out the password, or copy/insert the password without having it re-encrypt? In SQL2000 I would have done:
update A set
name = b.name,
password = b.password
from sysxlogins A, sysxlogins B
where A.srvid = 22
and B.srvid = 11
But SQL 2005 will not allow you to do this kind of update (allow updates has no effect).
Thanks, Tim
PS - Anything I might have missed that anyone can suggest, or any faster method of copying the database? We have a limited maintenance window to do this upgrade, so anything I can do ahead of time will help. Thanks again. Tim
I have installed SQL 2005 (X64) on 64-bit Windows 2003 server (AMD). One of my databases need to connect several Oracle databases on 32 bit servers.
I managed to install Oracle 32 bit Client (was unsuccessful with 64 bit installation) and could set up the odbc (32). But I coul'nt find Oracle under linked servers!
Does anyone know what I need to do?
Can I move a database from one server to another server using QA?
EXEC sp_detach_db @dbname = 'IISLOG'
EXEC sp_attach_single_file_db @dbname = 'IISLOG',
@physname = 'c:Program FilesMicrosoft SQL ServerMSSQLDataIISLOG.mdf'
Or would I need a tape backup to move this database to another server?
Thanks
Lystra
Hello,I need to move 6 SQL Servers from existing domain to a new domain.Currently all SQL Server servies are started using the domain accountof old domain.What precautions and steps are necessary for migration from one domainto another. Also note that the name of the servers needs to be changedin the new domain .ThanksSrinivas
View 5 Replies View Related Hello,
I have a problem: I have created an Integration Services in SQL Server 2005 that moves data from a table in a server to another table in another server. I have set the protection level property to "don't save sensitive".
The problem is when I try to execute it with a dtexec command. I get this errors:
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".'
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host.'
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'xxxxx'.".'
I don't know why the Integration Services does not work, and what are this errors about. Maybe the problem is with the protection level property, because when I set it to "don't save sensitive" it has some warnings, but when I set the property to "Encrypt sensitive with user key" the error messages are the same.
Thanks,
Pablo Orte
Hi Guys,
What is the fast way to move huge table (77 million) records with 25 columns across servers? The servers are not linked though.
Thanks for the help.
I need to learn how to move or copy a couple of tables from one database to another. The tables are defined but contain no data.
View 2 Replies View RelatedThis might sound dumb, but BOL says you can use detach/attach to "MOVE" a database but can you detach and copy the files to a new server, leaving the files on the original and running the attach on the new and the old? (effectually a way to copy)? I know how to use data transformations/import,export was curious?
Help appreciated!!
Is there any way to move a db with full-text indexes from one physical machine to another without having to drop catalog/indexes and the re-create them?
Cheers!
/Eskil
Good day ,
Does anyone know how to moe / copy database users from sql server 2000 to sql server 2005. I ave been successful will everything except this in my migration. I can copy the database users themselves but not the users under the security section.
Please help
1. Replication Transactional between two servers
-- SQL Server 2005 as Distributors and Subscribers
---SQL Server 2000 as Publishers
2. Linked Servers errors:
" Server (Publication Server) is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)"
Did anyone familiar with this problem?
Thankssss
TJ_1
hi, I do have over 30 scheduled jobs in one sql server in which I want to have the same jobs in another sql server. What is the best way to copy those jobs?
I thought of backing up the msdb from one sql server then restoring msdb into another sql server..... someone advice me NOT to do so due to potential compatibility problems that he did not explicitly state.
I am hoping to get the answer here.
Thanks
Ahmed
Hi, How can you copy a table from one server to another server using SELECT statement?
View 1 Replies View RelatedHi,
I've got two servers with sql 2005 express on, the first is a shared server at our isp, we rent one database from them. The other server is our own and we have full sa access.
Is there an easy way to copy the database from the shared server onto our own server. We're unable to use the backup.
Thanks
Hi,
Once a report is deployed to a specific server (server A), is there a way to "copy" it to a different server (servers B, C), without having to actually deploy it server by server?
In my case, I am trying to have the developers deploy the reports on the development server first (server A), and then manually or automatically copy them to a Preview server (server B), and later to a production server (server C). I cannot grant the developers full permissions on the production server, so a "copy" is what I'm thinking about....
Thanks in advance for any ideas.
Edwin.
Is it possible to link to an SQL 2005 server db from a SQL 2000 server? Is there a driver for this?
View 1 Replies View Relatedcan anybody tell me about Linked Servers and their uses and how to add a linked server to my Sql Server 2005.
any help on this would be highly appreciated.
Hello All,
I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?
Thanks