Hi all
I am trying to copy data from one database server to another. I only
want to copy one table's data, not the entire database. The part that
I am having trouble with is connecting from one database server and
connect to another then telling it to insert into the second database
server.
Not sure if this is how it works.
any help is appreciated.
thanks
Harold 9 15738
You can add linked server and use it to copy the table data across server.
Creating linked server is done using sp_addlinkedser ver:
EXEC sp_addlinkedser ver 'RemoteServer', N'SQL Server'
Based on security settings you may need to map remote server logins. This is
done using sp_addlinkedsrv login:
EXEC sp_addlinkedsrv login 'RemoteServer', 'false', 'LocalUser',
'RemoteUser', 'RemotePassword '
Then you just run a normal query referencing the linked server table with 4
part name:
INSERT INTO TargetTable
SELECT <columns>
FROM RemoteServer.Re moteDB.dbo.Remo teTable
HTH,
Plamen Ratchev http://www.SQLStudio.com
On Apr 22, 8:49 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:
You can add linked server and use it to copy the table data across server.
Creating linked server is done using sp_addlinkedser ver:
EXEC sp_addlinkedser ver 'RemoteServer', N'SQL Server'
Based on security settings you may need to map remote server logins. This is
done using sp_addlinkedsrv login:
EXEC sp_addlinkedsrv login 'RemoteServer', 'false', 'LocalUser',
'RemoteUser', 'RemotePassword '
Then you just run a normal query referencing the linked server table with 4
part name:
INSERT INTO TargetTable
SELECT <columns>
FROM RemoteServer.Re moteDB.dbo.Remo teTable
HTH,
Plamen Ratchevhttp://www.SQLStudio.c om
How if i need to : 'SET IDENTITY_INSERT ON' before execute insert
command ?
i have try it before :
SET IDENTITY_INSERT [remoteservernam e].Library2005.db o.tblLanguages ON
--will result error
[remoteservernam e].Library2005.db o.sp_executesql N'SET IDENTITY_INSERT
dbo.tblLanguage s ON' --no error
That command executes without error, but the problem is that I cannot
perform the actual insert, because it is not within the execute
statement. In other words, the following doesn't work:
EXECUTE [remoteservernam e].Library2005.db o.sp_executesql N'SET
IDENTITY_INSERT dbo.tblLanguage s ON'
INSERT INTO [remoteservernam e].Library2005.db o.tblLanguages
(colLangID, colEnglish, colGerman, colSpanish)
SELECT colLangID, colEnglish, colGerman, colSpanish FROM tblLanguages
This results in the error:
Msg 7344, Level 16, State 1, Line 2
OLE DB provider 'SQLOLEDB' could not INSERT INTO table
'[remoteservernam e].[Library2005].[dbo].[tblLanguages]' because of
column 'colLangID'. The user did not have permission to write to the
column.
The remote server is linked correctly on my end via the
sp_addlinkedser ver and sp_addlinkedsrv login. Is there any way to force
the remote server to turn IDENTITY_INSERT ON permanently and then let
me execute as many INSERTS as I want and then turn it back OFF?
If you can set up a linked server the other way around (from your remote
server to the local server), then executing the query on the remote server
will have no problems setting IDENTITY_INSERT ON. The option cannot be set
permanently and it has to be set in the same session as the INSERT
statement.
Alternative is to use BCP or Bulk Insert to insert the data which have
option to keep identity.
HTH,
Plamen Ratchev http://www.SQLStudio.com
i have 2 server Server A and Server B.
On Server A, I have table Stock :
CREATE TABLE [dbo].[Stock] (
[Stock_Id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
On Server B, I have table Stock too:
CREATE TABLE [dbo].[Stock] (
[Stock_Id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
i want copy from server A to Server B, using this :
sp_dropserver 'mylink'
go
sp_addlinkedser ver 'mylink', '', 'SQLOLEDB', NULL, NULL, 'DRIVER={SQL
Server};SERVER= B02;UID=sa;PWD= QWERTY;'
go
exec sp_serveroption @server='mylink ', @optname='rpc out',
@optvalue='true '
go
EXECUTE [mylink].[serverB].[dbo].[sp_executesql] N'SET IDENTITY_INSERT
dbo.Stock ON
INSERT INTO Tr_Stock (Stock_Id) SELECT Stock_Id FROM ( ???? i have
active in remote server now ??? )Stock
SET IDENTITY_INSERT dbo.Stock OFF'
but i Still get error.
I think, the problem is when using : EXECUTE [mylink].[serverB].[dbo].
[sp_executesql] . How to execute insert ?
On May 15, 1:18 pm, imo...@gmail.co m wrote:
i have 2 server Server A and Server B.
On Server A, I have table Stock :
CREATE TABLE [dbo].[Stock] (
[Stock_Id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
On Server B, I have table Stock too:
CREATE TABLE [dbo].[Stock] (
[Stock_Id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
i want copy from server A to Server B, using this :
sp_dropserver 'mylink'
go
sp_addlinkedser ver 'mylink', '', 'SQLOLEDB', NULL, NULL, 'DRIVER={SQL
Server};SERVER= B02;UID=sa;PWD= QWERTY;'
go
exec sp_serveroption @server='mylink ', @optname='rpc out',
@optvalue='true '
go
EXECUTE [mylink].[serverB].[dbo].[sp_executesql] N'SET IDENTITY_INSERT
dbo.Stock ON
INSERT INTO Tr_Stock (Stock_Id) SELECT Stock_Id FROM ( ???? i have
active in remote server now ??? )Stock
SET IDENTITY_INSERT dbo.Stock OFF'
but i Still get error.
I think, the problem is when using : EXECUTE [mylink].[serverB].[dbo].
[sp_executesql] . How to execute insert ?
Sory, i have to remote from local server to remote
server, so i must using set identity on, before execute insert
command.
regards
On May 15, 12:40 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:
If you can set up a linked server the other way around (from your remote
server to the local server), then executing the query on the remote server
will have no problems setting IDENTITY_INSERT ON. The option cannot be set
permanently and it has to be set in the same session as the INSERT
statement.
Alternative is to use BCP or Bulk Insert to insert the data which have
option to keep identity.
HTH,
Plamen Ratchevhttp://www.SQLStudio.c om
Dear Mr Plamen,
I can not set up a linked server the other way around (from your
remote
server to the local server).
Now, i want try using Bulk Insert the data which have option to keep
identity, but
my problem is, i can't access data from local server cause i have
ready come in to
remote server.
before, i just want execute :
INSERT INTO mylink.serverB. dbo.Tr_Stock (Stock_Id) SELECT Stock_Id
FROM Stock
coz i just 1 option to execute identity_insert using :
EXECUTE [mylink].[serverB].[dbo].[sp_executesql] N'SET IDENTITY_INSERT
dbo.Stock ON
, so now i cant using this again : INSERT INTO
mylink.serverB. dbo.Tr_Stock (Stock_Id) SELECT Stock_Id FROM Stock
Regards,
Thanks
One way to work around the issue is to create a staging table on the remote
server. The table will not have IDENTITY column, so you can transfer the
data. Then have a stored procedure to transfer between the staging table and
the production table. Inside the stored procedure you can set
IDENTITY_INSERT ON and complete the insert.
HTH,
Plamen Ratchev http://www.SQLStudio.com
On May 15, 8:01 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:
One way to work around the issue is to create a staging table on the remote
server. The table will not have IDENTITY column, so you can transfer the
data. Then have a stored procedure to transfer between the staging table and
the production table. Inside the stored procedure you can set
IDENTITY_INSERT ON and complete the insert.
HTH,
Plamen Ratchevhttp://www.SQLStudio.c om
Ok, thanks for all. Regards.
(im****@gmail.c om) writes:
That command executes without error, but the problem is that I cannot
perform the actual insert, because it is not within the execute
statement. In other words, the following doesn't work:
EXECUTE [remoteservernam e].Library2005.db o.sp_executesql N'SET
IDENTITY_INSERT dbo.tblLanguage s ON'
This is because the effect of a SET command is reverted when the
scope in which the SET command is executed exists. You would
need to have the SET command and the INSERT statement in the
same batch.
Overall, this is a good example how IDENTITY can buy you a load of
troubles. Had you rolled your own, you would have copied the data
long ago.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Robert Tarantino |
last post by:
Hello,
I am trying to find a way to create a scheduled task or
service that will copy my local profile folders
under "Documents and settings" to a network drive. This
would allow me to restore my settings if my profile became
tampered with or corrupt. Is there any sample code
available out there?
-Robert
|
by: 21novembre |
last post by:
Hi all,
I made a question several days before to describe my strange trouble of
mysqldump. But I still can't figour it out. Well, I just want to ask
another question whether I could just backup my databases by copying
the data folder to some place? Then if I meet some disaster, could I
just copy the backup folder back to recover my databases?
Thank you.
Zh.y
|
by: Don |
last post by:
Using JS, how do I copy an associative array from one frame to another?
Here's what I tried, and didn't work.
In "main" frame referencing "header" frame:
var cookies = new Object();
cookies = parent.header.cookies;
Actually, I'm having the same problem with copying a simple variable. The error message says the
|
by: TB |
last post by:
Hi All:
This news group is proving to be great help on my path towards
mastering ASP.NET thanks to all of you helpful souls out there. I am
looking forward to the day when I can contribute with a few ounces of
hard-won knowledge as well.
Meanwhile I have this (hopefully small) problem.
On my local computer where I am developing a web application, I have
|
by: TB |
last post by:
Hi All:
This news group is proving to be great help on my path towards
mastering ASP.NET thanks to all of you helpful souls out there. I am
looking forward to the day when I can contribute with a few ounces of
hard-won knowledge as well.
Meanwhile I have this (hopefully small) problem.
| |
by: zMisc |
last post by:
Is it possible to copy a table from one schema to another schema by just
copying the frm file to the directory for the new schema?
What is the best way to create a new database with all the tables in it
automatically?
I was hoping to have the tables (the frm files) included in a subdirectory
and when required, just create a new schema then copy all the frm files into
it.
|
by: maflatoun |
last post by:
Hi,
In SQL 2000 if I wanted to take a complete copy of another running sql
database all did was create a new database locally and right-click it
and select import and point to another database and click copy
everything (stored procedures as well) and it did it for. I can't seem
to find the same functionality in SQL 2005. You can copy tables and
views but not the whole database. Is there another way of doing this?
Our SQL database is...
|
by: John |
last post by:
Hi all,
My application updates a sql server 2005 express database prior to copying
it with the result being the "in use by another process" and I cannot copy
it as a result. I've posted the code that updates the database below. Please
could someone let me know how I free up the mdf file properly prior to
copying it.
Otherwise, does someone know how I can free it up programmatically or simply
copy it without receiving the errror?
|
by: TC |
last post by:
I need an automated procedure to copy data from an Access table to a
SQL Server table. Speed is important. What is the recommended
technique?
I can export the data from Access, copy it via FTP, then import it into
SQL Server. I've tried that, and the speed is acceptable. It is an ugly
solution, however, and I expect to find a better one -- preferably a
solution better integrated with the Access RDBMS.
I've tried using an ODBC...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |