query from different sql servers

how can i get data from different sqlservers in tsql.
is there anyway different from below
Select * from opendatasource('SQLOLEDB','Data Source=...;User
ID=...;Password=...').dbname.dbo.tblname
Sabri,
From the SQL BOL:
This example accesses data from a table on another instance of SQL Server.
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
HTHJerry"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:29DFD275-8D2B-438C-8BFF-9C9B743EFC76@.microsoft.com...
> how can i get data from different sqlservers in tsql.
> is there anyway different from below
> Select * from opendatasource('SQLOLEDB','Data Source=...;User
> ID=...;Password=...').dbname.dbo.tblname
>
sql

query from different sql servers

how can i get data from different sqlservers in tsql.
is there anyway different from below
Select * from opendatasource('SQLOLEDB','Data Source=...;User
ID=...;Password=...').dbname.dbo.tblnameSabri,
From the SQL BOL:
This example accesses data from a table on another instance of SQL Server.
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
HTHJerry"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:29DFD275-8D2B-438C-8BFF-9C9B743EFC76@.microsoft.com...
> how can i get data from different sqlservers in tsql.
> is there anyway different from below
> Select * from opendatasource('SQLOLEDB','Data Source=...;User
> ID=...;Password=...').dbname.dbo.tblname
>

query from different sql servers

how can i get data from different sqlservers in tsql.
is there anyway different from below
Select * from opendatasource('SQLOLEDB','Data Source=...;User
ID=...;Password=...').dbname.dbo.tblnameSabri,
From the SQL BOL:
This example accesses data from a table on another instance of SQL Server.
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
HTHJerry"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:29DFD275-8D2B-438C-8BFF-9C9B743EFC76@.microsoft.com...
> how can i get data from different sqlservers in tsql.
> is there anyway different from below
> Select * from opendatasource('SQLOLEDB','Data Source=...;User
> ID=...;Password=...').dbname.dbo.tblname
>

Query From 3 Database Servers

I have a t-sql query that I need to execute weekly on three different
database servers. The same t-sql query will executed on all three database
servers.
I have created DTS package with the three different database servers.
Should I create database link to the different database servers with a user
account that has db_datareader privileges to the database that is linked?
Please help me with this process.
Thanks,
Hi
I preseume that in the DTS package you have a connection to the given
database server? What do you specify as the login? Whatever login is used,
the associated database user will need enough privileges to run your
query/procedure. This should be kept to a minimum so if you are only wanting
to return data from a give table select privilege on that table should be
enough; if you use a stored procedure then you would (hopefully) be able to
use ownership chains and therefore only execute permission on the given
procedure will be necessary.
HTH
John
"Joe K." wrote:

> I have a t-sql query that I need to execute weekly on three different
> database servers. The same t-sql query will executed on all three database
> servers.
> I have created DTS package with the three different database servers.
> Should I create database link to the different database servers with a user
> account that has db_datareader privileges to the database that is linked?
> Please help me with this process.
> Thanks,

Query From 3 Database Servers

I have a t-sql query that I need to execute weekly on three different
database servers. The same t-sql query will executed on all three database
servers.
I have created DTS package with the three different database servers.
Should I create database link to the different database servers with a user
account that has db_datareader privileges to the database that is linked?
Please help me with this process.
Thanks,Hi
I preseume that in the DTS package you have a connection to the given
database server? What do you specify as the login? Whatever login is used,
the associated database user will need enough privileges to run your
query/procedure. This should be kept to a minimum so if you are only wanting
to return data from a give table select privilege on that table should be
enough; if you use a stored procedure then you would (hopefully) be able to
use ownership chains and therefore only execute permission on the given
procedure will be necessary.
HTH
John
"Joe K." wrote:

> I have a t-sql query that I need to execute weekly on three different
> database servers. The same t-sql query will executed on all three databas
e
> servers.
> I have created DTS package with the three different database servers.
> Should I create database link to the different database servers with a use
r
> account that has db_datareader privileges to the database that is linked?
> Please help me with this process.
> Thanks,

Query for available servers

Is there anything built into the .Net framework to query which SQL servers are available from the current pc, or do I have to resort to using windows APIs like in VB6?

Thanks,

AndrewThe post below discusses one way to do it.
view post 332996|||Thanks, that post uses a com object, so I may as well take my vb6 code which calls the API and wrap them directly in .net code. At least then I won't have to depend on registering a control. I will post the code when I am done.

Andrew

Query Error: Collation Conflict (on tables on 2 different servers)

Hi all,
I have a query that I need to run where I join two tables that bothreside on different servers. I use an INNER JOIN statement to attemptto join these tables, but for some reason I am getting the followingerror message...
"Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Thanks
Tryst

SQL JOINs are from Rene Descartes math so you can only create INNER JOIN if the two tables are equal, if they are not equal you use OUTER JOIN. The other problem the error did not show is you need LINKED Server to run your query so your tables maybe equal to qualify for INNER JOIN but SQL Server is not seeing that because the servers are not linked. Try the links below to get started, the Microsoft link have the System stored procs you need to create LINKED Server. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3085211

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp

|||Hi Caddre, thanks for the reply.
I did manage to run the sp_addlinkedserver stored procedure to create a linked server instance.
The problem may be the column types between the two tables.
I have now tried using a LEFT OUTER JOIN, but still get the same error message...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
LEFT OUTER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Tryst
|||

It is Column collation related try these links to resolve it. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7ory.asp

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8818馠

Query constantly failing.

Hi Guys,
I am trying to do a simple bill run on one of our SQL servers and it keeps
terminating prematurely with the following error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (rec
v()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation
The query used to run fine, nothing has changed hardware wise and the server
seems to have no obvious problems. Does this sound like KB article: 872968?
If not, what do you belive this issue can be?
Thanks for all your help guys!Hard to say if it's related to the KB article - just not
enough info to say. Have you tried running a trace or
running profiler while you execute the query? Did you check
the SQL Server error log for more information?
-Sue
On Wed, 5 Jul 2006 04:41:02 -0700, Jonny
<Jonny@.discussions.microsoft.com> wrote:

>Hi Guys,
>I am trying to do a simple bill run on one of our SQL servers and it keeps
>terminating prematurely with the following error:
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (re
cv()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation
>The query used to run fine, nothing has changed hardware wise and the serve
r
>seems to have no obvious problems. Does this sound like KB article: 872968?
>If not, what do you belive this issue can be?
>Thanks for all your help guys!

query analyzer's issue

Dear all,
Because of the large amount of live servers i am obligated to have in my
desktop
six or seven query analyzer sessions (linking servers is forbidden) against
them.
Sometimes and suddenly i lost my keyboard configuration and when I touch the
* key appears another character (this happen in one query analyzer not for
all)
It's a very strange behaviour.
Is there anybody here is suffering a similar problem?
Thanks in advance and best regards,XP professional with SP2.
language sp: english
keyboard: spanish.
Cheers,
"Enric" wrote:

> Dear all,
> Because of the large amount of live servers i am obligated to have in my
> desktop
> six or seven query analyzer sessions (linking servers is forbidden) agains
t
> them.
> Sometimes and suddenly i lost my keyboard configuration and when I touch t
he
> * key appears another character (this happen in one query analyzer not for
> all)
> It's a very strange behaviour.
> Is there anybody here is suffering a similar problem?
> Thanks in advance and best regards,