Outer join for two tables

Hi guys,

Please Help!

I am using Excel's VBA in order to retrieve sql Data.

I have three tables of the "my Company" DataBase (sql server 2000):

Table1, Table2, Table3

I need to join:

Table1.FieldXX=Table2.FieldYY

Table1.FieldWW= Table3.FieldZZ

In both cases, I should retrieve all data from Table1, even if Table2 or Table3 don't have the correspondent entries.

I am trying to use the code below, but getting "sql syntax error":

FROM myCompany.dbo.Table1 Table1

LEFT OUTER JOIN myCompany.dbo.Table2 Table2 ON (Table1.FieldXX=Table2.FieldYY)

AND

LEFT OUTER JOIN myCompany.dbo.Table3 Table3 ON (Table1.FieldWW= Table3.FieldZZ)

I will appreciatte any help.

Thanks in advance,

Aldo.Hi Guys,

This is the code working properly:

FROM myCompany.dbo.Table1 Table1

LEFT OUTER JOIN myCompany.dbo.Table2 Table2 ON Table1.FieldXX= Table2 .FieldYY

LEFT OUTER JOIN myCompany.dbo.Table3 Table3 ON Table1.FieldWW= Table3.FieldZZ

Aldo.sql

Out put of Select Statement Stored in a variable to another Variab

Hi Guys,
I need a solution in SQL Server 2000. I wanted to store the output of a
select query into a variable. The Select Statement is stored in a variable
.
the sample is given below.
declare @.Tmp Varchar(100),
@.Tmp1 Varchar(100),
@.Tmp2 Varchar(15)
set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
serialno=5'
set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
exec(@.Tmp1)
print @.Tmp2
Do any one have the idea.
Thanks in advance
Regds
Kag"Kag" <Kag@.discussions.microsoft.com> wrote in message
news:304B2B5C-AE55-491A-9D26-88F205861757@.microsoft.com...
> Hi Guys,
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
>
You will need to use a Temp table. The variables are only valid within the
scope in which they are defined. Temp tables are part of your session.
DECLARE @.tmp varchar(100),
@.tmp1 varchar(100)
CREATE TABLE #ReturnValue (tmp2 varchar(15) NULL)
SET @.tmp = 'columntitle FROM bworksheetdtl WHERE Worksheetcode=1 AND
serialno = 5'
SET @.tmp1 = 'INSERT #ReturnValue (tmp2) SELECT ' + @.tmp
EXECUTE (@.tmp1)
SELECT tmp2 FROM #ReturnValue
DROP TABLE #ReturnValue
Rick Sawtell
MCT, MCSD, MCDBA|||The @.Temp2 variable does not exist (is not declared) inside the context of
your EXECUTE statement.
Take a look at sp_executesql in Books Online. It accepts parameters and
really simplifies execution of dynamic SQL.
On the other hand, if you post DDL and sample data and specify what you're
actually trying to achieve, we can help you find a more efficient solution.
ML|||Hi Kag,
Would you like to try this?
DECLARE @.Tmp Varchar(100),
@.Tmp1 Varchar(100),
@.Tmp2 Varchar(15)
SELECT @.tmp2 = columntitle FROM bworksheetdtl WHERE Worksheetcode = 1 and
serialno = 5
Leo
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Hi Kag,
This may be what you want:
Declare @.tmp varchar(15)
SELECT @.temp = columntitle FROM bworksheetdtl WHERE Worksheetcode=1 and
serialno=5
PRINT @.tmp
Leo
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100), columntitle from bworksheetdtl where Worksheetcode=1
and
> serialno=5
> @.Tmp2 Varchar(15)
> set @.Tmp = ''
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Pl try code below
declare @.Tmp nVarchar(100), -- varchar changed to nvarchar
@.Tmp1 nVarchar(100), -- varchar changed to nvarchar
@.Tmp2 Varchar(15)
set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
serialno=5'
set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
exec sp_executesql @.Tmp1, N'@.Tmp2 Varchar(15) output', @.Tmp2 output -- alter
ed
print @.Tmp2
Rakesh
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kag|||Thanks Rakesh and ML. I tried the solution you have given with
sp_executesql and it was the one i was searching for. Special thanks to
Rakesh for the exact example. Thanks again guys..
Kag
"Kag" wrote:

> Hi Guys,
> I need a solution in SQL Server 2000. I wanted to store the output of a
> select query into a variable. The Select Statement is stored in a variab
le.
> the sample is given below.
> declare @.Tmp Varchar(100),
> @.Tmp1 Varchar(100),
> @.Tmp2 Varchar(15)
> set @.Tmp = 'columntitle from bworksheetdtl where Worksheetcode=1 and
> serialno=5'
> set @.Tmp1 = 'Select @.Tmp2 = ' + @.Tmp
> exec(@.Tmp1)
> print @.Tmp2
> Do any one have the idea.
> Thanks in advance
> Regds
> Kagsql

Out of options, Need performance help.

hey guys, i need your help please.
here is the scenario:

1. I need to return a data back to client (result set varies 20-10,000)
2. I only want to show 20 records at a time
3. To get info i need to display i need to join 10 tables

When there are small #s of records it works but when i get over 8000 then it becomes a problem:

1. The first version was:
Get all data using big query and return everything back to client and display only 20 at a time (not very proficient).
Takes around 15 seconds to view 20 records.

2. Inspired by 4GuysFromRolla (http://www.4guysfromrolla.com/webtech/062899-1.shtml)
Use Stored Procedure w/ server side paging logic to get 20 records at the time. I had to pass every filter parameter and stuff. SP had to sort resultset and return only 20 records i need to display.
Takes around 5 seconds to view 20 records.

I still think it's slow, i know this is a very broad question but is there any other way to do it, logically?

Any help is appreciated.
Wojo.You coud try running a few instances of the search in anticipation of the Next/Prev scenario.

While users are reading 20 in their window the application is downloading a further 20 behind the scenes asynchronously. I couldn't give an example. At least they wouldn't be aware of the 5 seconds if it took longer to read 20.

The only other example I have is for Delphi, sorry.|||Well...you need to order by something...and you need to know what page your on...

How about..

USE Northwind
GO

DECLARE @.Page int, @.sql varchar(8000)
SELECT @.Page = 2
SELECT @.sql = 'SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP '
+ CONVERT(varchar(10),@.Page * 20)
+ ' * FROM Orders ORDER by OrderId) AS A '
+ ' ORDER By OrderID Desc) AS B '
+ ' ORDER BY OrderId'
EXEC(@.sql)|||Thanks guys,
Brett, how does this work?|||To see it work...go to QA...cut and paste the code and execute it...

What it does roughly is that if you want page x, the inner query will select the TOP x * 20 rows (last 20 will be page x...

Then order by a key descending to get the last 20 rows..the reorder those by the key, so page 20 will be in the correct order to be displayed...

test it, let me know if it works for you...

Oh, and btw, this will only transmit the 20 rows across..you don't want to be moving large chunks for no reason...especially since data is fluid and may change at any time...|||Hmm,

What if i want to sort by other columns, plus my IDs aren't sequential :(|||You have to order by something...

The order of data in a database is meaningless....

And yes can can order by more than 1 thing...

And it doesn't have to be an int...

Look at a phonebook...they don't order by phone number...|||Thanks Brett,

it looks like it works preety well, i'm getting 1-2 seconds on couple of first pages!

One last question: is there any way to get total number of records from the inner query? and i would rather not requery this, any way to get the count while doing what we are doing?

thanks|||What's the total row count?

Did you try it for the last page yet?

And...

One last question: is there any way to get total number of records from the inner query? and i would rather not requery this, any way to get the count while doing what we are doing?

Why does this matter to you? Remembering the page number should be more important...no?

And cool site btw...do you sell just parts or whole systems...where are you based out of?|||The row count was ~9k
I did and it took 6-7 seconds, which is not that bad considering almost nobody looks at last pages :)

It matters to me because i'm displaying total number of records and i need this number to drive my paging logic (ex. page 1 out of 12)

Re: my site, its just my personal project, this question is for work :) and no i actually don't sell anything in there, i wrote this site to allow people to sell buy hardware for no charge...

thanks again for all your help man!|||What are you SELECTING, and what's the ORDER BY on?

Are they all indexed?

I don't like 6/7 seconds....|||i will look into indexes later, i would like to get this running first, so i need total count of records...|||Wouldn't that just be @.Pages*20?|||lol, i need to know what @.PAGES is don't i?

i don't think i made myself clear, i want to know the total number of records i'm gonna page myself though, ex:

Found 100 records, you are on page number 1 out of 5 (since there is 20 recs per page)

thanks.|||"i will look into indexes later"

If you are concerned about performance NOW, then you need to look into indexes NOW. I wondered what kind of indexes you had when I read that it started getting slow at 8000 records. 8000 records is peanuts.

Doing a select count(*) to get the rowcount should not be too expensive. Another method is to select the rowcount from the sysindexes table.|||I should have mentined before but I do this instinctivly, you may already be doing it.

When you join yuor tables dont use * and avoid large text fields. can be quite a surprise how long they take.

Try a skeleton version of the query that just selects and joins the keys. Then work up to include fields you need.

Sorry if you are allready doing that. And nice site you have there.

Out Of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.

---

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

---

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

---

Content of the .asp file is

<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

---

Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.

What should i do to solve this problem
Plz Help me out in this

Thanks in advance
Dinesh

I can't tell exactly what is going on here, but it seems to be limited to the web access method, since it works correctly from the batch file. So I would limit my search to that area - you might want to try turning on garbage collection:

http://support.microsoft.com/kb/911716

Buck Woody

Out of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.

---

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

---

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

---

Content of the .asp file is
<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

---

Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.

What should i do to solve this problem
Plz Help me out in this

Thanks in advance
Dinesh
You need to troubleshoot from ASP side. It is unlikely this has anything to do with SQL or the batch file itself.

Out Of Memory

Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.

In Detail.

This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.

---

Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause

---

Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO

DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)

SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error

IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO

---

Content of the .asp file is

<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>

---

Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.

What should i do to solve this problem
Plz Help me out in this

Thanks in advance
Dinesh

I can't tell exactly what is going on here, but it seems to be limited to the web access method, since it works correctly from the batch file. So I would limit my search to that area - you might want to try turning on garbage collection:

http://support.microsoft.com/kb/911716

Buck Woody

OT: "Profiler" for Access?

Guys,
Is there a tool that I can see what queries are running against an Access
database? The one is similar to Profiler for SQL.
Thanks.Hi
AFAIK three is no such tool, if you are using ODBC then you may want to try
using ODBC tracing, but this would need to be enabled on each client and
would severely impact performance. Another alternative would be to make the
m
linked tables to a SQL Server database and to actually use profiler.
You may want to ask this in an Access News Group.
John
"ME" wrote:

> Guys,
> Is there a tool that I can see what queries are running against an Access
> database? The one is similar to Profiler for SQL.
> Thanks.
>
>|||thanks anyway.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0CC54221-14C3-42D9-A296-46C75C5B1C29@.microsoft.com...[vbcol=seagreen]
> Hi
> AFAIK three is no such tool, if you are using ODBC then you may want to
> try
> using ODBC tracing, but this would need to be enabled on each client and
> would severely impact performance. Another alternative would be to make
> them
> linked tables to a SQL Server database and to actually use profiler.
> You may want to ask this in an Access News Group.
> John
> "ME" wrote:
>

OT: "Profiler" for Access?

Guys,
Is there a tool that I can see what queries are running against an Access
database? The one is similar to Profiler for SQL.
Thanks.Hi
AFAIK three is no such tool, if you are using ODBC then you may want to try
using ODBC tracing, but this would need to be enabled on each client and
would severely impact performance. Another alternative would be to make them
linked tables to a SQL Server database and to actually use profiler.
You may want to ask this in an Access News Group.
John
"ME" wrote:
> Guys,
> Is there a tool that I can see what queries are running against an Access
> database? The one is similar to Profiler for SQL.
> Thanks.
>
>|||thanks anyway.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0CC54221-14C3-42D9-A296-46C75C5B1C29@.microsoft.com...
> Hi
> AFAIK three is no such tool, if you are using ODBC then you may want to
> try
> using ODBC tracing, but this would need to be enabled on each client and
> would severely impact performance. Another alternative would be to make
> them
> linked tables to a SQL Server database and to actually use profiler.
> You may want to ask this in an Access News Group.
> John
> "ME" wrote:
>> Guys,
>> Is there a tool that I can see what queries are running against an Access
>> database? The one is similar to Profiler for SQL.
>> Thanks.
>>