Multi-Table Update?

Being an access guy, I am having a hard time understanding why I can't do a join statement on an UPDATE?

What is the alternatives??

I have to match two tables up and use records from one to update the other and creating a view isn't working...I would have suggested a view. What kind of problems are you getting with this approach? Are you trying to update a key value?|||
Another option is to use a stored procedure. You can update multiple tables, one after the other, using a stored procedure. Within the stored procedure you can use variables to hold values retrieved from one table and insert them into the next one.

|||you tend to lose update capabilities with a join when its a many to many join.

if your joining with a primary key it should work|||I'm going to have to learn some more about stored procedures because that would be excellent to do multiple updates like that..

Is there a code sample or link where I can learn more about doing the updates in that manor?

thx|||
Probably your best bet is to find a good book on the topic. In the meantime, you can hava look at the books online:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_4hk5.asp?frame=true

You can also download some of theStarter Kits from this website for lots of great samples (see the tab above).

Hope this helps.

MULTI-Table Update Queries

I'm new to adp w/ sql server but I have to use it on a project i'm
doing...
One of the MUSTS for this project is the ability to update a 00 - 09
text value with the appropriate text description from another table...
Easy as pie in .mdb. Of course In the stored procedure it barks at me
and tells me that an update query can only have one table.. ouch that
hurts...

I'm currently reading on the subject but this group has been very
helpful in the past....

I found this link...
http://www.sqlservercentral.com/col...stheeasyway.asp

Unfortunetly I'm using MSDE not Enterprise so I don't think I can use
the query analyser.. But I tryed it in my Access ADP anyway
it barked at me..

I tried to go from this...

SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1
FROM dbo.TEST INNER JOIN
dbo.LU_SEX ON dbo.TEST.SEX_DP1 =
dbo.LU_SEX.SEX_DEC

To this...

UPDATE dbo.TEST.SEX_DP1
SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODE
FROM dbo.LU_SEX INNER JOIN
dbo.TEST ON dbo.LU_SEX.SEX_DEC =
dbo.TEST.SEX_DP1

Maybe I need a good book on this?

Thanks,
CharlesIt looks like you wanted:

UPDATE dbo.TEST
SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODE
FROM dbo.LU_SEX
JOIN dbo.TEST
ON dbo.LU_SEX.SEX_DEC = dbo.TEST.SEX_DP1

The table name follows the UPDATE keyword, not the column name.

This is the MS proprietary syntax for an UPDATE statement. There is a
potential problem with that method if you try to update using a join
that doesn't return a unique value for each row. No error is returned
but the result is undefined and may be unreliable. This is one reason I
prefer the ANSI Standard UPDATE syntax, which SQL Server also supports:

UPDATE Test
SET sex_dp1 =
(SELECT sex_code
FROM LU_Sex
WHERE sex_dec = Test.sex_dp1)

I'm guessing this will be the equivalent of what you posted. It will be
so long as sex_dec is unique in LU_Sex and all the values of sex_dp1
also exist in that table.

Presumably you ultimately intend to use one set of codes througout the
database. There is in fact an International standard for gender codes
(0=not known 1=male 2=female 9=not specified). You could also use views
to convert the codes if you need to do so for display for example.

--
David Portas
SQL Server MVP
--|||(meyvn77@.yahoo.com) writes:
> I tried to go from this...
> SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1
> FROM dbo.TEST INNER JOIN
> dbo.LU_SEX ON dbo.TEST.SEX_DP1 =
> dbo.LU_SEX.SEX_DEC
> To this...
> UPDATE dbo.TEST.SEX_DP1

You have a database dbo with a schmea TEST and table SEX_DP1?
Drop SEX_DP1 to start with.

> SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODE

And here drop dbo.TEST on the left-hand side. You can uses prefixes on
the left-hand side, but I don't recall under which circumstances. In any
case, since you can only update one table at a time, it's redundant.

> FROM dbo.LU_SEX INNER JOIN
> dbo.TEST ON dbo.LU_SEX.SEX_DEC =
> dbo.TEST.SEX_DP1

This should be fine, although I recommend you to use aliases to make
your queries less verbose.

> Maybe I need a good book on this?

Books Online, see signature.

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

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

Multi-table UDF not returning all rows

I've been tearing my hair out over this UDF. The code works within a
stored procedure and also run ad-hoc against the database, but does not
run properly within my UDF. We've been using the SP, but I do need a
UDF instead now.

All users, including branch office, sub-companies and companies and so
on up the lines are in the same table. I need a function which returns
a row for each level, eventually getting to the master company all the
way at the top, but this UDF acts as though it can't enter the loop and
only inserts the @.userID and @.branchID rows. I have played with the
WHILE condition to no avail.

Any ideas on what I am missing?

(Running against SQL Server 2000)
----------------

ALTER FUNCTION udfUplineGetCompany (@.userID int)

RETURNS @.upline table (companyID int, companyname varchar(100), info
varchar(100))
AS
BEGIN

DECLARE @.branchID int
DECLARE @.companyID int
DECLARE @.tempID int

--Insert the original user data
INSERT INTO @.upline
SELECT tblusersid, companyname, 'userID'
FROM tblusers
WHERE tblusersid = @.userid

SELECT @.branchID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid

--Up one level
INSERT INTO @.upline
SELECT tblusersid, companyname, 'branchID'
FROM tblusers
WHERE tblusersid = @.branchid

SET @.tempID = @.branchID

WHILE @.@.ROWCOUNT <> 0
BEGIN
SELECT @.companyID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
AND tblUsersId <> 6

--Insert a row for each level up
INSERT INTO @.upline
SELECT tblusersid, companyname, 'companyID'
FROM tblusers
WHERE tblusersid = @.companyID

SET @.tempID = @.companyID

END

RETURN

ENDWell, if I try to run in as an ad-hoc query, it doesn't run properly at
all.

The use of @.@.rowcount is bound to go wrong. Also, the INSERT into the
table variable will be executed regardless of a match (or miss) of the
parent selection.

You should check the @.@.rowcount immediately after the "SELECT @.CompanyID
= ..." statement. If you do not want to handle the @.@.rowcount result at
that point, then make sure you move the @.@.rowcount status to a variable
immediately after the SELECT statement.

Gert-Jan

P.S. If you have proper primary key/foreign keys in place, then joining
to the tblUsers table in the SELECT statement is unnecessary.

nicolec@.octitle.com wrote:
> I've been tearing my hair out over this UDF. The code works within a
> stored procedure and also run ad-hoc against the database, but does not
> run properly within my UDF. We've been using the SP, but I do need a
> UDF instead now.
> All users, including branch office, sub-companies and companies and so
> on up the lines are in the same table. I need a function which returns
> a row for each level, eventually getting to the master company all the
> way at the top, but this UDF acts as though it can't enter the loop and
> only inserts the @.userID and @.branchID rows. I have played with the
> WHILE condition to no avail.
> Any ideas on what I am missing?
> (Running against SQL Server 2000)
> ----------------
> ALTER FUNCTION udfUplineGetCompany (@.userID int)
> RETURNS @.upline table (companyID int, companyname varchar(100), info
> varchar(100))
> AS
> BEGIN
> DECLARE @.branchID int
> DECLARE @.companyID int
> DECLARE @.tempID int
> --Insert the original user data
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'userID'
> FROM tblusers
> WHERE tblusersid = @.userid
> SELECT @.branchID = tblUsers.tblUsersID
> FROM tblUsers
> INNER JOIN tblUsersUsersLnk
> ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
> WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid
> --Up one level
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'branchID'
> FROM tblusers
> WHERE tblusersid = @.branchid
> SET @.tempID = @.branchID
> WHILE @.@.ROWCOUNT <> 0
> BEGIN
> SELECT @.companyID = tblUsers.tblUsersID
> FROM tblUsers
> INNER JOIN tblUsersUsersLnk
> ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
> WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
> AND tblUsersId <> 6
> --Insert a row for each level up
> INSERT INTO @.upline
> SELECT tblusersid, companyname, 'companyID'
> FROM tblusers
> WHERE tblusersid = @.companyID
> SET @.tempID = @.companyID
> END
> RETURN
> END|||Sometimes you look at something SO long you don't see the obvious...
thanks for the pointer, that worked perfect. For future newsgroup
reference, the new function text is at the bottom.

>P.S. If you have proper primary key/foreign keys in place, then joining
to the tblUsers table in the SELECT statement is unnecessary.

No keys anywhere in this database and it's driving me nuts. Every join
must be explicit.

----------------

ALTER FUNCTION udfUplineGetCompany (@.userID int)

RETURNS @.upline table (companyID int, companyname varchar(100), info
varchar(100), rows int)
AS
BEGIN

DECLARE @.branchID int
DECLARE @.companyID int
DECLARE @.tempID int
DECLARE @.rows int

SET @.rows = 1

INSERT INTO @.upline
SELECT tblusersid, companyname, 'userID', @.rows
FROM tblusers
WHERE tblusersid = @.userid

SELECT @.branchID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.userid

INSERT INTO @.upline
SELECT tblusersid, companyname, 'branchID', @.rows
FROM tblusers
WHERE tblusersid = @.branchid

SET @.tempID = @.branchID

WHILE @.rows = 1
BEGIN
SELECT @.companyID = tblUsers.tblUsersID
FROM tblUsers
INNER JOIN tblUsersUsersLnk
ON tblUsers.tblUsersID = tblUsersUsersLnk.tblUsersID_Parent
WHERE tblUsersUsersLnk.tblUsersID_Child = @.tempID
AND tblUsersId <> 6

SET @.rows = @.@.rowcount

INSERT INTO @.upline
SELECT tblusersid, companyname, 'companyID', @.rows
FROM tblusers
WHERE tblusersid = @.companyID
AND @.rows = 1

SET @.tempID = @.companyID

END

RETURN

END

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner jo
in
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get s
ome
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resourc
e
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/defaul...kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get
some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resou
rce
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, look
s
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was ab
le
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BT
W,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if thi
s
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However
if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveK
Could you please post your join query ?
-jens Suessmeyer.
|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.
|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default...b;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>
|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?
|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default.aspx?scid=kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:
> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:
> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004sql

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ? Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time?
Or does SQL do this automatically?
Thanks
Sean
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.c om...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ? Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time?
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

multi-table join problem

I have three tables that all contain different types of data but have a customer name in common. I can join the three of them into one view that gives me an overview of what issues they are having by using the following query:

SELECT dbo.ProjectContentCount.SiteName AS SiteContent, dbo.ProjectIssueCount.SiteName AS SiteIssue,
dbo.ProjectFeatureCount.SiteName AS SiteFeature, dbo.ProjectContentCount.[Open] AS OpenContent,
dbo.ProjectContentCount.Fixed AS FixedContent, dbo.ProjectContentCount.Closed AS ClosedContent, dbo.ProjectIssueCount.[Open] AS OpenIssue,
dbo.ProjectIssueCount.Pending AS PendingIssue, dbo.ProjectIssueCount.Closed AS ClosedIssue, dbo.ProjectFeatureCount.[Open] AS OpenFeature,
dbo.ProjectFeatureCount.Pending AS PendingFeature, dbo.ProjectFeatureCount.Closed AS ClosedFeature
FROM dbo.ProjectFeatureCount FULL OUTER JOIN
dbo.ProjectIssueCount ON dbo.ProjectFeatureCount.SiteName = dbo.ProjectIssueCount.SiteName FULL OUTER JOIN
dbo.ProjectContentCount ON dbo.ProjectIssueCount.SiteName = dbo.ProjectContentCount.SiteName AND
dbo.ProjectFeatureCount.SiteName = dbo.ProjectContentCount.SiteName

This works fine because it gets a list of all clients and data for whichever row there exists data of that type, whether or not there is data for a given client on each table.

However, I now need to join this group of data to a master client table that contains contact information. The master table has info about all the clients but every join I try breaks the join I have working above and causes duplicate rows.

Any ideas would be greatly appreciated!Well, I found my answer after many iterations in query analyzer and here is what I came up with. I hope it helps someone else...

SELECT dbo.SiteMaster.ItemID, dbo.SiteMaster.ModuleID, dbo.SiteMaster.SiteID, dbo.SiteMaster.SiteManager, dbo.SiteMaster.SiteStatus,
dbo.SiteMaster.SiteName, dbo.ProjectIssueCount.[Open] AS OpenIssue, dbo.ProjectIssueCount.Pending AS PendingIssue,
dbo.ProjectIssueCount.Closed AS ClosedIssue, dbo.ProjectContentCount.[Open] AS OpenContent, dbo.ProjectContentCount.Fixed AS FixedContent,
dbo.ProjectContentCount.Closed AS ClosedContent, dbo.ProjectFeatureCount.[Open] AS OpenFeature,
dbo.ProjectFeatureCount.Pending AS PendingFeature, dbo.ProjectFeatureCount.Closed AS ClosedFeature
FROM dbo.ProjectContentCount FULL OUTER JOIN
dbo.ProjectIssueCount FULL OUTER JOIN
dbo.SiteMaster ON dbo.ProjectIssueCount.SiteName = dbo.SiteMaster.SiteName FULL OUTER JOIN
dbo.ProjectFeatureCount ON dbo.ProjectIssueCount.SiteName = dbo.ProjectFeatureCount.SiteName AND
dbo.SiteMaster.SiteName = dbo.ProjectFeatureCount.SiteName ON dbo.ProjectContentCount.SiteName = dbo.SiteMaster.SiteName AND
dbo.ProjectContentCount.SiteName = dbo.ProjectIssueCount.SiteName

Multi-Table IDENTITY

Is there a way to associate IDENTITY columns from two different tables, so that new records created in either table will have mutually unique values?

That is, a new record in table A will be given value 1, and then a new record created in table B will be given value 2 (because value 1 was already used by the IDENTITY column in table A). Can this be done?

No, identity fields are only unique within the table.

Multi-table DataSets in VS2005 SRS Report designer

Hi all,

I need to use a multi-table dataset as a datasource for my report items in a SRS2005 report.

But from designer, I can only see the first table.

Can any one tell me if it is possible to use multiple tables in a dataset for SRS report?

Thanks,

Samson

If the report is deployed to the server, you will need a custom data extension to support multi-table datasets, as demonstrated here. Alternatively, you can use the ReportViewer controls for local report rendering. Then you can bind the report to any table in the dataset, as explained here.

sql