Avatar of Michael Katz
Michael KatzFlag for United States of America asked on

Is it possible to script an insert statement from 1 sql instance to another sql instance??

Hi Experts,

Trying to script an insert into between 1 sql instance to another sql instance that are on the same sql box..

thanks
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Qlemo

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
cyberkiwi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
lomo74

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Michael Katz

I cant connect via "New Linked Server"

any ideas?

TITLE: Microsoft SQL Server Management Studio
------------------------------

The linked server has been created but failed a connection test. Do you want to keep the linked server?

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "SERVER2" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "SERVER2" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.4000&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

------------------------------
BUTTONS:

&Yes
&No
------------------------------
lomo74

did you specify correct server name / instance name?
can you connect to second instance using query analyzer?
why is ole db provider using named pipes? the two instances are on the same box so it should use shared memory, unless you told it not to do so, I suppose...
anyway - check if named pipes are enabled: go to sql server configuration center, select the correct instance (the one you want to connect to), then network protocols and make sure named pipes (and eventually tcp-ip) are enabled. restart sql server.
also make sure sql server browser service is running, otherwise client library can't map instance name -> port number or pipe name.
ASKER
Michael Katz

did you specify correct server name / instance name?
Yes..

can you connect to second instance using query analyzer?
yes

why is ole db provider using named pipes? the two instances are on the same box so it should use shared memory, unless you told it not to do so, I suppose...
I clicked "SQL Server" for Server type

anyway - check if named pipes are enabled: go to sql server configuration center, select the correct instance (the one you want to connect to), then network protocols and make sure named pipes (and eventually tcp-ip) are enabled. restart sql server.
also make sure sql server browser service is running, otherwise client library can't map instance name -> port number or pipe name.

All is done..

Still getting same error
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
lomo74

I see "SERVER2" in your error log --
what is SERVER2, the machine name or the instance name?
ASKER
Michael Katz

Well I guess that means I am an idiot... I didnt think that had to be the actual name of the server.. That is the link name i gave it...the name of the machine is SPD
ASKER
Michael Katz

ok
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.