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

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

qualifying table variables

hi all!

I am using table variables instead of creating a temp table because it seems to be faster

But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?

ex: with temp table

create table #tmp... (F1...)
#tmp.f1

with table variable

declare @.temp table(...
@.table.f1 - can´t do it

the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name

thank to all and happiness for all 2004Hi all

I think I found the solution

It is not possible to qualify the talble variable because it is not part of a persistent table, so I cant assing table variables,

I saw it in

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

it is the same as in sql 2000 BOL

thanks. so I have to use a temp table to qualify it if I want to join the table. My thinking is that I can join table variables

hope it is useful to u|||Hi all!

Thanks for having the time to read it

I was wrong and found the solution.

I can join and qualify a table var, only aliasing the table as in

insert into @.table... join temp.field...
from @.vartemp temp

--

here temp is the alias of @.vartemp, so I can use this alias instead of @.vartemp when, for example, there are two fields with same name in two tables, and one is @.vartemp

Now, once solved it, my problem is with UPDATETEXT. I am updating a text field and need to qualify the @.vartable in order to update it using a pointer to the text field. Since there are not any FROM clause as above, I dont know where to qualify the @.vartable (tried qualifying in DECLARE, but cant do it there)

UPDATETEXT @.vartemp.pointer - cant use @.vartemp, need an alias, but where to give the alias?

Thanks all!

Qualify query with table name owner

All of the sudden when I do queries on my databases I have to include the ta
ble owner name
Before - Select * from people
After - Select * from carl.people
Don't know what changed... Haven't changed any table owners, config options
for the server or query tool.
Anyone know why?
Thanks,
CarlYou probably don't operate under the user name "carl", quite simply. Execute
SELECT SESSTION_USER and see what it returns.
Btw, it is a good practice to *always* owner-qualify in production code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Carl at pnm" <anonymous@.discussions.microsoft.com> wrote in message
news:EC65D713-D380-4A63-A367-E72CBB18FC6E@.microsoft.com...
> All of the sudden when I do queries on my databases I have to include the
table owner name
> Before - Select * from people
> After - Select * from carl.people
> Don't know what changed... Haven't changed any table owners, config
options for the server or query tool.
> Anyone know why?
> Thanks,
> Carl

Qualify customer information

Can anyone advise me of how to make sure that a customer is inputting
correct information into the database.
I am building a website, where customers are not logging in, but request
info. by name, addr. and postcode/city, email. I want to check if the
customer exists in the database or not.
Who has any knowledge of this i.e. validating customer name, addr. etc.
information. Which search technics in SQL Server should I make use of (i.e.
should I search exact match with addr. and name, or only with email, are
there any other searching technics to make use of ?).
Your post contains several broad questions which cannot be aptly answered
without more specifics. In general, when customer information is recorded in
a database uniquely, one can use a simple IF EXISTS() check to see if a
specific row exists in a table. For instance:
IF EXISTS ( SELECT * FROM tbl WHERE keycol = @.someval )
-- Data exists
ELSE
-- Data does not exist
Depending on what attributes ( like name, address, email etc. ) make up a
unique customer in your table, you may have to make use of a proper
predicate which can identify the row.
Anith

Qualified table names

I have a quick question on how to qualify table names as it relates to
"dbo" vs. user names. Suppose that I am a user named "dwuser1", and
that I need to create a table named "dw_stage_1". Do I use dbo as in
"dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
table name? Are both OK? If so, what would be the implications of
each?php newbie (newtophp2000@.yahoo.com) writes:
> I have a quick question on how to qualify table names as it relates to
> "dbo" vs. user names. Suppose that I am a user named "dwuser1", and
> that I need to create a table named "dw_stage_1". Do I use dbo as in
> "dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualified
> table name? Are both OK? If so, what would be the implications of
> each?

If you are user dwuser1, and you have CREATE TABLE permissions, and
you say:

CREATE TABLE dw_stage_1 (a int NOT NULL)

The full qualification for that table will be dwuser1.dw_stage_1. You
cannot refer to the table as dbo.dw_stage_1. As dwuser1 you can refer
to the table as dw_stage_1 and dwuser1.dw_stage_1. All other users,
including dbo, must refer to ut as dwuser1.dw_stage_1.

If you then log in as sa or any other login that map to dbo and say

CREATE TABLE dw_stage_1 (a int NOT NULL)

again, you have now created to dw_stage_1. As dbo you can refer to the
table as dbo.dw_stage_1 or dw_stage_1 only, and so can all other users
except for dwuser1, who must use dbo.dw_stage_1, since his only table
is ahead in the seatch path.

Best practice recommended my Microsoft is to refer to the table as
dbo.dw_stage_1. This is particularly important for loose SQL statements,
since SQL Server then can skip the search for user.dw_stage_1. They
say that this is also good in stored procedure, but in my opinion,
the dbo. becomes a four-letter line noice in a procedure that is owned
by dbo.

As for when to use objects not owned by dbo - beats me. I say, keep it
simple and only use dbo.

(In SQL 2005 where users and schema are separated, it's another story.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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