problem with select * from [remote server] in QA

In Query Analyzer connected to my local server using Windows authenticatio I
tried to run this select statement against my remote server:
select * from [11.22.333.444].remoteDB.dbo.tbl1
I got this error:
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
I can open a new connection in QA using an ID and password to this remote
server (which is not the same userID and password for my local server), and
then I can successfully run the select statement above, but I also need to
run statements against my local tables. So If I log into the remote server
and then try to reach my local server like this:
select * from myLocalServer.myLocalDB.dbo.myTable
--now I get the error message that :
"Server 'myLocalServer' is not configured for DATA ACCESS."
I believe the remote server IS configured for data access. So my question
is if it is possible to append the userID and password to the select
statement from the local connection?
select * from [11.22.333.444].remoteDB.dbo.tbl1, 'joey', 'password123'
or is there some way to supply the credentials along with the select
statement? Any suggestions appreciated.
Thanks,
RichRich,
I don't know exactly what you meant when you said 'remote server', but
you can only exec stored procedures but not query on a remote server,
use linked server instead.

>From BOL:
========================================
==========
If you want to set up a server configuration in order to execute stored
procedures on another server and do not have existing remote server
configurations, use linked servers instead of remote servers. Both
stored procedures and distributed queries are allowed against linked
servers; however, only stored procedures are allowed against remote
servers.
Note Support for remote servers is provided for backward compatibility
only. New applications that must execute stored procedures against
remote instances of SQL Server should use linked servers instead.
========================================
===================
Example,
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
You can define your remote password/login using sp_addlinkedsrvlogin on
the linked server.
Mel|||Rich
Have you created linked server?
exec sp_serveroption 'servername','data access','true'
go
select * from servername.database.dbo.tablename where ...
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D3D8127B-DE34-4598-9A11-88AE2FF24F3A@.microsoft.com...
> In Query Analyzer connected to my local server using Windows authenticatio
> I
> tried to run this select statement against my remote server:
> select * from [11.22.333.444].remoteDB.dbo.tbl1
> I got this error:
> "Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection."
> I can open a new connection in QA using an ID and password to this remote
> server (which is not the same userID and password for my local server),
> and
> then I can successfully run the select statement above, but I also need to
> run statements against my local tables. So If I log into the remote
> server
> and then try to reach my local server like this:
> select * from myLocalServer.myLocalDB.dbo.myTable
> --now I get the error message that :
> "Server 'myLocalServer' is not configured for DATA ACCESS."
> I believe the remote server IS configured for data access. So my question
> is if it is possible to append the userID and password to the select
> statement from the local connection?
> select * from [11.22.333.444].remoteDB.dbo.tbl1, 'joey', 'password123'
> or is there some way to supply the credentials along with the select
> statement? Any suggestions appreciated.
> Thanks,
> Rich
>

Problem with Provider=SQLXMLOLEDB.3.0

Hi!

When I make a connection to the sqlserver database, everything work
fine.
Recently I connected to a huge db 1.4 GB.
The querys are executed with the same time however making a connection
is taking a long time.

I am using the following connection string.

My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
"Data Provider=SQLOLEDB;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=" & MY_SERVER & ";" & _
"Initial Catalog=BigDB;" & _
"User Id=sa;" & _
"Password=sa"

Thanks is advance,
JaiJai (jaijai_kumar@.hotmail.com) writes:
> When I make a connection to the sqlserver database, everything work
> fine.
> Recently I connected to a huge db 1.4 GB.
> The querys are executed with the same time however making a connection
> is taking a long time.
> I am using the following connection string.
> My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
> "Data Provider=SQLOLEDB;" & _
> "Network Library=DBMSSOCN;" & _
> "Data Source=" & MY_SERVER & ";" & _
> "Initial Catalog=BigDB;" & _
> "User Id=sa;" & _
> "Password=sa"

Take out Network Library from the connection string. Not sure if it
helps, but I don't think that DBMSOCN is one of the preferred onces.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks a lot for ur message...
It did not help?
Any other idea...?

Thanks,
Jai

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94934E9BA02EYazorman@.127.0.0.1>...
> Jai (jaijai_kumar@.hotmail.com) writes:
> > When I make a connection to the sqlserver database, everything work
> > fine.
> > Recently I connected to a huge db 1.4 GB.
> > The querys are executed with the same time however making a connection
> > is taking a long time.
> > I am using the following connection string.
> > My_CONNECTION = "Provider=SQLXMLOLEDB.3.0;" & _
> > "Data Provider=SQLOLEDB;" & _
> > "Network Library=DBMSSOCN;" & _
> > "Data Source=" & MY_SERVER & ";" & _
> > "Initial Catalog=BigDB;" & _
> > "User Id=sa;" & _
> > "Password=sa"
> Take out Network Library from the connection string. Not sure if it
> helps, but I don't think that DBMSOCN is one of the preferred onces.|||Jai (jaijai_kumar@.hotmail.com) writes:
> Thanks a lot for ur message...
> It did not help?
> Any other idea...?

There was not an overflow of information in your post, so it is
difficult to suggest anything. But let's look at it again.

>> > When I make a connection to the sqlserver database, everything work
>> > fine.
>> > Recently I connected to a huge db 1.4 GB.
>> > The querys are executed with the same time however making a connection
>> > is taking a long time.

Is the database that is slow to connect to on the same server as
the one that is fast to connect to? (My assumption is that is not.)

If you connect to the slow server from Query Analyzer, does that
also take time? If that is fast, what if you connect with SQLOLEDB
only; that is "Provider=SQLOLEDB"?

Assuming that you connect slow no matter the method, the problem may
be in the network somewhere. Not really my field of expertise to
say what that may be. Did you try connecting by IP-addrress directly?

Finally, most people would find a 1.4GB a moderately size database,
or even smallish...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp