Query across servers

I'm new to SQL and I'm trying to write a stored procdeure in my current database that queries a different database on a different server. What is the best way to do this?
Thanks and sorry if it's too remedial a question.you will need to set up a linked server. there is a great product documentation tool called Books Online under Start\Programs\Micorsoft SQL Server. Use the search feature for "linked server". You can then reference your the linked server using openquery or you can create a view that created with openquery. again see books online about openquery and views.|||You can use OpenDataSource. Check that from BOL.

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories

Query Across Servers

Thanks! I'll try again.

quote:

---

>--Original Message--
>Yes it is possible. You will need to fully qualify your
>select path. For example
>SELECT A.TESTDATA1, B.TESTDATA2
> FROM <Server1>.<Database>.dbo.<Table> A,
> <Server2>.<Database>.dbo.<Table> B
>WHERE A.TESTDATA1 = B.TESTDATA2
>As long as the User account you are using has access to
>both database (Trusted Connection) it should work fine.
>
data[QUOTE]
>.
>

---

you can indeed do joins across tables between sqlservers. However watch out
for performance. If you want it done more flexiibly & faster or want to do
updates, check out my company.
Regards
David Penney - MetaMatrix
http://www.metamatrix.com
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:8de901c3ea6d$a19c8470$a101280a@.phx.gbl...
> Thanks! I'll try again.
>
> datasql

Query Across Servers

Hi,
Is it possible to execute a query asking for joined data
from different tables on two different servers and
databases?
SQLServer1 SQLServer2
-- --
09.0.0.30\TestData 198.160.30.20\TestData2
I need to run a query against tables in the TestData &
TestData2 databases located at the I.P. locations
simulated above. Joins are necessary.
Thanks in advance.You can use linked servers and then fully qualify database objects, for
example:
SELECT * FROM SQLServer1.database.dbo.tablename -- from server2
You can find more info about linked servers in books online...
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"DBa" <anonymous@.discussions.microsoft.com> wrote in message
news:639101c3e5cb$e41662e0$a601280a@.phx.gbl...

quote:

---

> Hi,
> Is it possible to execute a query asking for joined data
> from different tables on two different servers and
> databases?
> SQLServer1 SQLServer2
> -- --
> 09.0.0.30\TestData 198.160.30.20\TestData2
> I need to run a query against tables in the TestData &
> TestData2 databases located at the I.P. locations
> simulated above. Joins are necessary.
> Thanks in advance.

---

|||alternate solution is to use OPENROWSET if you dont have permissions to
create linked server
Ex:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','server1';'user1';
'password1',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
FROM OPENROWSET('SQLOLEDB','server2';'user1';
'password1',
'SELECT * FROM pubs.dbo.titleauthor') AS b
where a.au_id = b.au_id
GO
SQL Booksonline has more samples on OPENROWSET
Sethu Srinivasan
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:O2EjQ6h5DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> You can use linked servers and then fully qualify database objects, for
> example:
> SELECT * FROM SQLServer1.database.dbo.tablename -- from server2
> You can find more info about linked servers in books online...
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "DBa" <anonymous@.discussions.microsoft.com> wrote in message
> news:639101c3e5cb$e41662e0$a601280a@.phx.gbl...
>

---

Query Across Multiple SQL Server Registrations

I have defined 2 SQL Server registrations in Ent Mgr. One is running locally and the other is located on another server. I want to be able to create a stored procedure in the local database that pulls data into a local table from the remote server.

I have wasted much time trying to define Linked Servers and Remote servers, and find the documentation all confusing and subsequently I have gotten nowhere (except frustrated). How do I configure the remote database so I can access it from a stored procedure in the local database?

Here is the code for creating the linked server. Run script against your local database. Hope you have access permissions on remote db. Also replace the IP address 999.999.999.999 with your remote system's IP address. You can query the tables on remote db with four part name.

Code Snippet

USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'Lnk_RemoteDB', @.srvproduct=N'sqlserver', @.provider=N'SQLOLEDB',
@.datasrc = '999.999.999.999', -- IP Address
@.catalog=N'RemoteDB'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'Lnk_RemoteDB', @.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'Lnk_RemoteDB', @.locallogin = NULL , @.useself = N'True'

|||

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

|||

Sniegel wrote:

I ran the script and it created Lnk_RemoteDB. I want to access a table called Agents from a database named AgentDB, what's the correct syntax?

Thanks

select * from Lnk_RemoteDB.AgentDB.dbo.Agents|||I get an Authentication failed error. I tried switching the mode through EM and entering a username/password, but it didn't seem to work.

Query across multiple db's

I have two databases on one server. I want do a query across the databases. I
was moving the data and out of 370K records, 24 did not come across. How do I
query to find out which records did not make it across.
Hi,
select * from DB1..tablename where id not in(select id from DB2..tablename)
Thanks
Hari
MCDBA
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> I have two databases on one server. I want do a query across the
databases. I
> was moving the data and out of 370K records, 24 did not come across. How
do I
> query to find out which records did not make it across.
|||Did not work. I think because the PK is across 5 fields
"Hari Prasad" wrote:

> Hi,
> select * from DB1..tablename where id not in(select id from DB2..tablename)
>
> Thanks
> Hari
> MCDBA
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> databases. I
> do I
>
>
|||One way to finese this problem is:
SELECT * FROM DB1..tablename
WHERE CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
NOT IN
(SELECT
CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
FROM DB2..tablename)
That assumes that CHAR(10) will hold the conversion of your identifying
columns. Adjust appropriately.
Russell Fields
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:8655C387-155D-4AE6-BFAF-7DB3DFD50593@.microsoft.com...[vbcol=seagreen]
> Did not work. I think because the PK is across 5 fields
> "Hari Prasad" wrote:
DB2..tablename)[vbcol=seagreen]
How[vbcol=seagreen]
|||Or you could use NOT EXISTS, which in my opinion reads a bit better:
SELECT *
FROM DB1..tablename T1
WHERE NOT EXISTS
(SELECT *
FROM DB2..tablename T2
WHERE T2.IDCol1 = T1.IDCol1
AND T2.IDCol2 = T2.IDCol2
AND T2.IDCol3 = T2.IDCol3
AND T2.IDCol4 = T2.IDCol4
AND T2.IDCol5 = T2.IDCol5)
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23hli%23S7hEHA.3536@.TK2MSFTNGP12.phx.gbl...
> One way to finese this problem is:
> SELECT * FROM DB1..tablename
> WHERE CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> NOT IN
> (SELECT
> CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> FROM DB2..tablename)
> That assumes that CHAR(10) will hold the conversion of your identifying
> columns. Adjust appropriately.
> Russell Fields

Query across multiple db's

I have two databases on one server. I want do a query across the databases.
I
was moving the data and out of 370K records, 24 did not come across. How do
I
query to find out which records did not make it across.Hi,
select * from DB1..tablename where id not in(select id from DB2..tablename)
Thanks
Hari
MCDBA
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> I have two databases on one server. I want do a query across the
databases. I
> was moving the data and out of 370K records, 24 did not come across. How
do I
> query to find out which records did not make it across.|||Did not work. I think because the PK is across 5 fields
"Hari Prasad" wrote:

> Hi,
> select * from DB1..tablename where id not in(select id from DB2..tablename
)
>
> Thanks
> Hari
> MCDBA
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> databases. I
> do I
>
>|||One way to finese this problem is:
SELECT * FROM DB1..tablename
WHERE CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
NOT IN
(SELECT
CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
FROM DB2..tablename)
That assumes that CHAR(10) will hold the conversion of your identifying
columns. Adjust appropriately.
Russell Fields
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:8655C387-155D-4AE6-BFAF-7DB3DFD50593@.microsoft.com...[vbcol=seagreen]
> Did not work. I think because the PK is across 5 fields
> "Hari Prasad" wrote:
>
DB2..tablename)[vbcol=seagreen]
How[vbcol=seagreen]|||Or you could use NOT EXISTS, which in my opinion reads a bit better:
SELECT *
FROM DB1..tablename T1
WHERE NOT EXISTS
(SELECT *
FROM DB2..tablename T2
WHERE T2.IDCol1 = T1.IDCol1
AND T2.IDCol2 = T2.IDCol2
AND T2.IDCol3 = T2.IDCol3
AND T2.IDCol4 = T2.IDCol4
AND T2.IDCol5 = T2.IDCol5)
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23hli%23S7hEHA.3536@.TK2MSFTNGP12.phx.gbl...
> One way to finese this problem is:
> SELECT * FROM DB1..tablename
> WHERE CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> NOT IN
> (SELECT
> CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> FROM DB2..tablename)
> That assumes that CHAR(10) will hold the conversion of your identifying
> columns. Adjust appropriately.
> Russell Fields

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardF
Yes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_nam e
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
sql

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF

Query across all colums

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
Thanks
Shabassanot really|||Ok, thanks was worth a question.|||If you are comparing all the columns in one table to all the columns in another, you may be able to use the CHECKSUM or BINARYCHECKSUM functions:

select subA.APKey, sbuB.BPKey
from
(select A.PKey as APKey, CHECKSUM(*) as ChecksumA from A) subA
full outer join
(select B.PKey as BPKey, CHECKSUM(*) as ChecksumB from B) subB
on subA.ChecksumA = subB.ChecksumB
where ...|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:|||While Rudy is quite right in terms of how SQL itself does things, there are extensions to many database engines that make this kind of search easier. In MS-SQL, this is called Full Text Indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_5dro.asp).

-PatP|||pat, all things considered, full text indexing is not "simpler" (one of shabassa's original requirements)

:)|||From the standpoint of managing the server, you are quite right. Full-Text Indexing brings its own problems to the table.

From the standpoint of writing a search query though, I feel that it is simpler and I don't think anyone would argue it is much less code.

-PatP|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:

Could be. Without a doubt he needs to be more specific.|||I doubt it though....

I think they're looking for duplicates...

Or they're just trying to establish a join between to related tables, just in the keys?|||Or attempting to create a search feature for their application that searches against a catalog of objects where the entry could match a value in any number of fields (Ex: Name, Description, Price, etc).|||Step right up, folks! Play "Guess the user requirements" and win a prize for the little lady! Who's next? Every player has a chance to win! You sir, yes you...!|||Well if I was programming an application that searched across the table there would be no problem doing it in some kind of loop. I was just lazy and wanted to look for a certain row where I entered a date in an application accessing that db. But I didn't know to which colum the text field in the app corresponded to. So I wanted to search across all columns at once using the Enterprise Manager.
I hope that clears things up. ;)

How would this be possible with full text indexing? Do I magically get some kind of search field?

Shabassa

Query 2 databases

Hi all,
Does anyone know how can I select data across 2 databases in SQL ? I want
to do something like this:
Select A.SentDate from dbo.DBA.TblA A, dbo.DBB.TblB B
Where B.FileName = '01012005.txt'
And A.AKey = B.BKey
But it doesn't work and give me an error "Invalid object name dbo.DBA.TblA",
guessing could be syntax error (?)... any idea?
Thanks !!
K.K>It should be
Database.owner.objectname as in
DBA.dbo.TblA
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"K.K." <someone@.microsoft.com> wrote in message
news:%23F$cuW6IFHA.4060@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> Does anyone know how can I select data across 2 databases in SQL ? I want
> to do something like this:
> Select A.SentDate from dbo.DBA.TblA A, dbo.DBB.TblB B
> Where B.FileName = '01012005.txt'
> And A.AKey = B.BKey
> But it doesn't work and give me an error "Invalid object name
> dbo.DBA.TblA", guessing could be syntax error (?)... any idea?
> Thanks !!
> K.K>
>
>

Query - concatenating across related records

Here's the setup:
Table A
--
ID........Code
A............X
B............X
C............Y
Table B
--
Code......Desc_Line......Desc
X.............01.........This is a description
X.............02.........for Code X
Y.............01.........Code Y is one line
I'd like the query to return:
A...X...This is a description for Code X
B...X...This is a description for Code X
C...Y...Code Y is one line
Question: How can I concatenate the multiple description lines in table B
to display on a single line with the corresponding data from Table A? I'd
like something easily re-usable (e.g. User Defined Function), but I'm not
sure how to go about it. Any help/suggestions would be greatly appreciated.SELECT A.id, B.code,
MAX(CASE WHEN desc_line = '01' THEN [desc] ELSE '' END)+
MAX(CASE WHEN desc_line = '02' THEN [desc] ELSE ' ' END)+
MAX(CASE WHEN desc_line = '03' THEN [desc] ELSE ' ' END)+
..
FROM TableA AS A
JOIN TableB AS B
ON A.code = B.code
GROUP BY A.id, B.code
"desc" is a reserved word. Avoid using it as a column name.
David Portas
SQL Server MVP
--|||I change the field Desc to Descrip, since Desc is a key word (Abbreviation
for Descending)
Create Function dbo.GetDescription (@.Code Char(1))
Returns VarChar(300)
As
Begin
Declare @.Out VarChar(300) Set @.Out = ''
Declare @.Lin Integer Set @.Lin = 0
While Exists (Select * From TableB
Where Code = @.Code
And Desc_Line > @.Lin)
Begin
Select @.Lin = Min(Desc_Line)
From TableB
Where Code = @.Code
And Desc_Line > @.Lin
-- --
Select @.Out = @.Out + Descrip + ' '
From TB
Where Code = @.Code
And Desc_Line = @.Lin
End
Return RTrim(@.Out)
End
-- ----
You use it like this:
dbo.GetDescription('X')
as in:
Select dbo.GetDescription('X')
"bradm98" wrote:
> Here's the setup:
> Table A
> --
> ID........Code
> A............X
> B............X
> C............Y
> Table B
> --
> Code......Desc_Line......Desc
> X.............01.........This is a description
> X.............02.........for Code X
> Y.............01.........Code Y is one line
> I'd like the query to return:
> A...X...This is a description for Code X
> B...X...This is a description for Code X
> C...Y...Code Y is one line
>
> Question: How can I concatenate the multiple description lines in table B
> to display on a single line with the corresponding data from Table A? I'd
> like something easily re-usable (e.g. User Defined Function), but I'm not
> sure how to go about it. Any help/suggestions would be greatly appreciated.[/colo
r]|||OH, I missed one TB -> TableB ... Here's correctted...
Create Function dbo.GetDescription (@.Code Char(1))
Returns VarChar(300)
As
Begin
Declare @.Out VarChar(300) Set @.Out = ''
Declare @.Lin Integer Set @.Lin = 0
While Exists (Select * From TableB
Where Code = @.Code
And Desc_Line > @.Lin)
Begin
Select @.Lin = Min(Desc_Line)
From TableB
Where Code = @.Code
And Desc_Line > @.Lin
-- --
Select @.Out = @.Out + Descrip + ' '
From TableB
Where Code = @.Code
And Desc_Line = @.Lin
End
Return RTrim(@.Out)
End
"CBretana" wrote:
> I change the field Desc to Descrip, since Desc is a key word (Abbreviation
> for Descending)
> Create Function dbo.GetDescription (@.Code Char(1))
> Returns VarChar(300)
> As
> Begin
> Declare @.Out VarChar(300) Set @.Out = ''
> Declare @.Lin Integer Set @.Lin = 0
> While Exists (Select * From TableB
> Where Code = @.Code
> And Desc_Line > @.Lin)
> Begin
> Select @.Lin = Min(Desc_Line)
> From TableB
> Where Code = @.Code
> And Desc_Line > @.Lin
> -- --
> Select @.Out = @.Out + Descrip + ' '
> From TB
> Where Code = @.Code
> And Desc_Line = @.Lin
> End
> Return RTrim(@.Out)
> End
> -- ----
> You use it like this:
> dbo.GetDescription('X')
> as in:
> Select dbo.GetDescription('X')
>
> "bradm98" wrote:
>|||Thanks so much. David's query worked, but your function really hit the nail
on the head!
"CBretana" wrote:
> OH, I missed one TB -> TableB ... Here's correctted...
> Create Function dbo.GetDescription (@.Code Char(1))
> Returns VarChar(300)
> As
> Begin
> Declare @.Out VarChar(300) Set @.Out = ''
> Declare @.Lin Integer Set @.Lin = 0
> While Exists (Select * From TableB
> Where Code = @.Code
> And Desc_Line > @.Lin)
> Begin
> Select @.Lin = Min(Desc_Line)
> From TableB
> Where Code = @.Code
> And Desc_Line > @.Lin
> -- --
> Select @.Out = @.Out + Descrip + ' '
> From TableB
> Where Code = @.Code
> And Desc_Line = @.Lin
> End
> Return RTrim(@.Out)
> End
>
> "CBretana" wrote:
>|||Just be aware, That using UDFs which read data from database tables is
frowned upon because in SQL 7/ SQL2k, every time the UDF Runs, it is
recompiled... So if, or example, you run
Select ID, Code, dbo.GetDescription(Code)
From TableA
And TableA has a million rows, then you will be recompiling the UDF a
million times. It can be a real performance hog...
I'm hoping MS fixes this in SQL 2005...
UDFs are at their best when they do NOT access data from database tables
"bradm98" wrote:
> Thanks so much. David's query worked, but your function really hit the na
il
> on the head!
> "CBretana" wrote:
>sql

Queries Across Servers

Hey all,

Is there a way to qualify a table name or whatever with the SQL name as well? We have serveral SQL Servers and I want to be able to update/query multiple SQL Servers from one statement.

For example:

select * from "is-dbdev".master.dbo.sysfiles

where "is-dbdev" is the name of the server. Can you do this? I have not had any luck thus far...and I get errors when I try to add the other SQL Server as a linked server.

Can anyone help?

Thank you.You have to add the remote server as a linked server, you also have to have a linked server login. Lookup sp_addlinkedserver in BOL.

Once added the syntax is

servername.databasename.ownername.tablename

HTH