run a query against another server

Hi, I have got SSMSE installed and am seeing how I can use it for my work stuff rather than MSDE.

I used to import using Enterprise Manager tables from our corp servers into my local MSDE to work with.

I have managed to create the required tables in SQL Exprerss and can see I have a connection to my local server (SQL Express) and my corp server.

When I query sys.servers I only see SQL Express and not the corp one. How do I run a select query that selects from the corp and inserts into the local. I have all the insert fields etc but it errors stating the corp database is not in sys.serevrs?

Thanks

hi,

you have to define a "linked server"..

select the Server Objects->Linked Servers node in SSMSE in your local connection server... add a linked server pointing to the corp server providing the appropriate credentials..

you can then query the linked server providing the 4 part naming of the object like

SELECT * FROM linked_server_name.database_name.schema_name.object_name;

once you have the connection working, you can then INSERT SELECT into your local database..

regards

|||Hi, I tried this but get errors?

I can connect in server explorer within VS.NET 2005 Pro IDE and get the following information:

Provider=
.NET Framework Data Provider for SQL Server

Connection String=
Data Source=Virtue;Initial Catalog=orbital;Integrated Security=True

I do not get an option to choose this provider in the combobox on the New Linked Server page?

So I get this error?

TITLE: Microsoft SQL Server Management Studio Express

"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.Express.ConnectionInfo)

The OLE DB provider "SQLNCLI" for linked server "VIRTUE" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "VIRTUE".
OLE DB provider "SQLNCLI" for linked server "VIRTUE" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "VIRTUE" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7399)

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

|||

hi,

pachjo wrote:

Hi, I tried this but get errors?

I can connect in server explorer within VS.NET 2005 Pro IDE and get the following information:

Provider=
.NET Framework Data Provider for SQL Server

Connection String=
Data Source=Virtue;Initial Catalog=orbital;Integrated Security=True

I do not get an option to choose this provider in the combobox on the New Linked Server page?

this information is relative to the "Add connection" wizard in the Server Explorer, isn't it?

actually the .NET Framework Data Provider for SQL Server is SQL Native Client and, in my installation, it is obviously available in SSMSE as well as SQLNCLI..

So I get this error?

TITLE: Microsoft SQL Server Management Studio Express

"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.Express.ConnectionInfo)

The OLE DB provider "SQLNCLI" for linked server "VIRTUE" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "VIRTUE".
OLE DB provider "SQLNCLI" for linked server "VIRTUE" returned message "Invalid authorization specification".
OLE DB provider "SQLNCLI" for linked server "VIRTUE" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7399)

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

did you provide the correct credential info for the remote server?

regards

|||Well I can connect no problem through server explorer and accerss the data. I used the same connection string from there and tried various things such as:

Linked Server = Virtue
Provider=SQL Native Client
Product Name=Virtue (not sure what to put here?)
Data source=Virtue
Connection String=Data Source=Virtue;Initial Catalog=orbital;Integrated Security=True
Catalog=blank

I am unsure what I am doing wrong here?
|||

hi,

Linked Server: network name of the remote SQL Server instance

Provider = SQL Native Client

Product Name = Provider = SQL Native Client

then you can go on bypassing the additional info of the "General" tab of the "Add linked server" dialog...

in the "Security" tab select the appropriate mapping or specify a specif SQL Server login (Be made using this security context)..

regards

|||

This is really getting me down as I am getting no where

I set the details as above and tried entering my nt logon details in various ways to include/omit the domain name but still no joy!

What I dont get is within VS.NET it connects straight away without bother and I can acces the tables! ?

|||

HI,

OK I am sort of there, but not completely?

I have created a connection to the remote server and created a linked server to it using the same coalation as the remote.

But when I create the tables, import the date all is well until I try to run a script and I get a coaltion conflict?

The remote is SQL_Latin1_General_CP1_CI_AS but I get an error complaining about

Latin1_General_CI_AS?

When I look at the remote and the linked they both say SQL_Latin1_General_CP1_CI_AS so I don't know where SQL_Latin1_General_CI_AS is coming from?

Help anyone....

|||

Oh...hold the phone!

I think I have found it?

The local database has the wrong coalation so.......I will start again ;-)