Poor Performance from Web Server

Hi,

I am having a problem with one of my stored procedures in SQL Server 2005. Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET.

I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it. More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.

We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig. I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.

If anyone has got any ideas on this then please let me know as I am completely stuck. I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...

Thanks in advance

Tom

maybe put some trace statements to echo out the time it execute a line of code. This way you can maybe find the bottleneck in your DAL. Are you using the Data Access Application Blocks. I have found those to be very valuable to manage my connections. I never see issues like you are describing anymore. Back in the day, like 4 years ago maybe when I was doing things on my own. Are you trying to fill a custom list or collection with a large set of records? I found that takes way too long and just op for datasets, readers or change my procedure to return a fixed set of rows.

But try the tracing thing to see what line(s) take the longest to execute and I think you will find your issue.

PLZZ HELP ! ERROR CONNECTING TO SQL SERVER

Hello everybody

i am getting problem in connecting my asp.net(c#) web application with Sql Server 2000 programatically on the page load of my web form

I wrote following code in the page load

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page
SqlConnection myConnection = new SqlConnection("server=(local);database=master;Integrated Security=SSPI");
SqlDataAdapter myCommand = new SqlDataAdapter("select * from emp", myConnection);

DataSet ds = new DataSet();
myCommand.Fill(ds, "emp");

DataGrid1.DataSource=ds.Tables["emp"].DefaultView;
DataGrid1.DataBind();
}

Build OK but following exception occurred:

Server Error in '/datagridtest' Application.
------------------------

Login failed for user 'MATRIX\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'MATRIX\ASPNET'.

Source Error:

Line 27:
Line 28: DataSet ds = new DataSet();
Line 29: myCommand.Fill(ds, "emp"); //THIS APPEARS IN RED ON THE PAGE
Line 30:
Line 31: DataGrid1.DataSource=ds.Tables["emp"].DefaultView;

----------------------------
PLZ HELP ME, I THINK THE PROBLEM IS WITH CONNECTIONSTRING, I STUCK UP HERE FROM LAST 3 DAYS AND COULD NOT PROCEED WITH MY FURTHER STUDY AND PRACTICE

THANX IN ADVANCE

BSSGlad you got this worked out!view post 806235

plz help me

Hello every one ...

i m developing a website using asp.net and sql server .. & need to send email after registration and i know i need to configure Sql Mail ...

so plz tel me wht i needto do, to configure sql mail ... i m using sql server 2000 ..

OS: XP Pro with service pack 2

can i configure my mail client through Outlook 2003 or i need to have Ms Exchange server ...

plz guyz guide me step by step wht i need to do ...

Thanx for ur Cooperation
Kamran SorathiaHere is a link that might help:

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci980136,00.html

Pls Help... How can I retieve the value of fields in my sqldatabase using sqldatareader

Please help me with my thesis... I am using ASP.NET

How can I retieve the value of fields in my sqldatabase using sqldatareader

thnks

Try the training videos in the SQL section.....http://www.asp.net/learn/sql-videos/

These will help you get started learning how to retreive data from the database

Burl

|||Dim DBConnAsNew SqlConnection("Server=localhost;Password=YourPWD;Persist Security Info=False;User ID=YourID;Initial Catalog=Northwind;Data Source=YourDataSource")Dim DBCmdAsNew SqlCommandDim DRAs SqlDataReaderDBCmd =New SqlCommand("SELECT Id, Name FROM Customers WHERE CustomerID = @.CustomerID", DBConn)DBCmd.Parameters.Add("@.CustomerID", SqlDbType.NChar, 5).Value = txtQuery.TextDR = DBCmd.ExecuteReader()

While(DR.Read())
{
id as string = DR["ID"].ToString()
}

|||

Hey it is good to read the DataTable values from Data Reader. Try using the linkhttp://dotnetsavvyblog.blogspot.com/2007/11/to-retrieve-fields-using-sqlreader.html.

Do let me know if you still face problems.

Pls help me with this SQL CASE WHEN (on ASP)

Hi and thanx for reading my post..

Well.. my problem is that i cannot seem to get the CASE syntax correct within my ASP page. I'm not sure where to break the lines and all that.

I have searched the net for quiiite a while before posting this :)

Will be VERY glad if someone can help me to put the puzzle in the right ASP-way :)

-----START-----
strSQL = "SELECT enavn,fnavn,fodselsnr,fangenr,convert(char(10),reg _date,104) " &_

"'status'= CASE when legal = '1' then "beginner" when legal = '2' then "intermediate" when legal = '3' then "professional" when kan_skal = 'kan' then "darlig foto" else 'status ikke angitt' END " &_

"FROM BILDE WHERE accepted LIKE " & "'1'" & " AND marked LIKE " & "'0'" & " AND pkode LIKE " & strDistrikt & " AND kan_skal LIKE " & "'kan'" & " AND arstall LIKE " & strArstall
-----END------

What i want to do is getting the three values from the SQL DB which are in the "legal" field (1, 2 or 3), and based on what it is display "beginner", "intermediate", "professional" or "darlig foto".

Best regards
Miradoryou have single quotes around status -- remove those

i prefer the as alias syntax instead of the alias = syntax

you have doublequotes around some strings ("beginner") which should be singlequotesselect enavn,fnavn,fodselsnr,fangenr
, convert(char(10),reg_date,104)
, case when legal = '1' then 'beginner'
when legal = '2' then 'intermediate'
when legal = '3' then 'professional'
when kan_skal = 'kan' then 'darlig foto'
else 'status ikke angitt'
end as status
from bilde
where ...also, none of your LIKE strings appear to have wildcards in them|||What language are you using here? What server? I suspect you're terminating your string improperly.

edit: r937 beat me to it.|||Programming in ASP, SQL towards MS SQL.
Tnx for the hints ppl...

I'm not very good at SQL... yet.. could anyone correct my code so that it might work ? thinking about where i should use double-qoutes in the start of lines and where i should use " &_

Mirador

Originally posted by Teddy
What language are you using here? What server? I suspect you're terminating your string improperly.

edit: r937 beat me to it.|||Tnx !!...

Hmm.. btw: what do u mean by Wildcards on the "LIKE"'s ?`

Mir..

Originally posted by r937
you have single quotes around status -- remove those

i prefer the as alias syntax instead of the alias = syntax

you have doublequotes around some strings ("beginner") which should be singlequotesselect enavn,fnavn,fodselsnr,fangenr
, convert(char(10),reg_date,104)
, case when legal = '1' then 'beginner'
when legal = '2' then 'intermediate'
when legal = '3' then 'professional'
when kan_skal = 'kan' then 'darlig foto'
else 'status ikke angitt'
end as status
from bilde
where ...also, none of your LIKE strings appear to have wildcards in them|||Wildcards can differ per application, but in SQL Server an example of a wild card in a like statement would be as follows:

Select legal
from bilde
where accepted like '%&%' (in this case the % are the wild cards).

This would find all records of [legal] from the table [bilde] where the amperstand (&) appears somewhere in the field [accepted].

Originally posted by Mirador
Tnx !!...

Hmm.. btw: what do u mean by Wildcards on the "LIKE"'s ?`

Mir..

Pls help me

Hi
I had windows integrated authication in directory
security in both folder reports and reportserver.
But I need reportserver as normal asp page so I just
changed to allow annoynymus access to reportserver folder.
So any once can access report using specifi url and it's
working without any problem.
But funny part is when I access
http://servername/reports (and logged in as administrator)
It doesnt show anything. Before that It was showing my
report and I could change and configure my report.
Please note that I didnt change any security setting in
reports folder and only thing I changed was allowing
annoynymus access to reporserver folder.
Any help would be grateful
Thanx
KenWhen you configure the Report Server for anonymous access, it will see the
requests coming under the anonymous account (IUSR_<computer name>) which
most likely doesn't have access to the report catalog in your case.
I would recommend you reconsider the Anonymous access strategy. When
Anonymous access is enabled, the Report Server won't be able to
differentiate the users which IMO is a recipe for a total chaos.
Not sure what you mean by I need reportserver as normal asp page.
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
www.manning.com/lachev
"Ken" <anonymous@.discussions.microsoft.com> wrote in message
news:212b601c45a4c$15af4980$a101280a@.phx.gbl...
> Hi
> I had windows integrated authication in directory
> security in both folder reports and reportserver.
> But I need reportserver as normal asp page so I just
> changed to allow annoynymus access to reportserver folder.
> So any once can access report using specifi url and it's
> working without any problem.
> But funny part is when I access
> http://servername/reports (and logged in as administrator)
> It doesnt show anything. Before that It was showing my
> report and I could change and configure my report.
> Please note that I didnt change any security setting in
> reports folder and only thing I changed was allowing
> annoynymus access to reporserver folder.
> Any help would be grateful
> Thanx
> Ken
>

pls advise me. HELP pls...

on the 1st opening of my web page it uses the membership control of asp.net
for logging on. sometimes it takes time to logon and worse is, it will
encounter sql server error. that can be resolved by just restarting that
specific page and it will be alright, it will logon normally. this only issue happens only on the first opening of the logon page.

i have read a certain article and it says there that on the first time
you access the sql server, sometimes the sql server needs a little more
time communicating back and forth.

how can i make my log-on page wait a little longer till it communicates
or finishes communicating to the sql server?

im afraid that this may happen once i deploy my web project. i am
expecting over 100 users and they might save to the database all
together. will i encounter problem on this particular?

i am using ASP.NET 2.0 and SQLExpress for this project. pls advise me.
BIG THANKS.

Hi,

what sort of error do you get ? If you are getting a commandTimeout error you can set the commandTimeout property in your application a bit higher to achieve that the command can have a bit more time to execute.

HTH;, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens Suessmeyer wrote:

Hi,

what sort of error do you get ? If you are getting a commandTimeout error you can set the commandTimeout property in your application a bit higher to achieve that the command can have a bit more time to execute.

HTH;, Jens Suessmeyer.

http://www.sqlserver2005.de

thanks Jens. that is exactly what i need. thanks a lot man!
|||just a question....

my web project is using SQL Express2005 and ASP.NET and C#.
my web hosting company only have MSsql2000. would there beany conflict with regards to my database? im sorry if i sound dumb. im a newbie to this.
thanks
|||Hi,

if you hosting company only supports MSDE or MSSQL 2000, I would not use SQL Server Express as a basis of development. I would rather use 2000, because you won't have any problem transfering the database from your development pc to the hosting company.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||i am only using one mdf file and my asp.net program just deals with the common add, edit, delete transactions. would these transactions differ from version to version of MSSQL?

honestly i did not think there would be a problem coz these transactions are very common. pls correct me if i am wrong.
|||No, you are sure right, but as of my experiences as a developer I would rather rely on the same system (even on the same SP level) than using a more sophisticated version of the database. But you are right that in case you don`t use any other stuff like doing standard DML there should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlsever2005.de
--|||i just installed MSDE. i tried attaching my mdf file (created by SQL Express) to the MSDE but i got this error message:

"An error occured when attaching the database"

i attached that mdf file to the SQL Express and i encountered no problem at all. is this the problem you are talking abt Jens? do you think i will encounter the same problem with my hosting company?

thanks a lot for your time Jens.
|||SQL Express datafile have a different storage structure than the SQL2k files. YOu can't do that. If you want transfer the database to the SQL2k you have to do scripting of the objects to transfer the data to the prior SQL2k5 databases.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||can I implement the membership control (provided by ASP.NET) on MSDE? i can try migrating from SQLEXxpress to MSDE but im not sure if membership control works on that MSDE. sorry if i really sound dumb. im a newbie.
|||

Hi,

habe a look here:

http://aspnet.4guysfromrolla.com/articles/120705-1.aspx

"Use the ASP.NET SQL Server Registration Tool (aspnet_regsql.exe) command-line tool (use this tool to implement the schema in a SQL Server 2000 or 2005 database) "

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

PLEASE TELL ME WHAT TO DO NEXT _ 2000 instead of 2005

Hi all.

My webhost prepared a server for me with sql 2000 instead of sql 2005.
Will my asp.net 2005 application work there?
I generated a script to export my sql server 2005 objects
Lots of failures appeard.

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name =
N'aspnet_WebEvent_FullAccess' AND type = 'R')
CREATE ROLE [aspnet_WebEvent_FullAccess]
GO

This is the error:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'ROLE'.

The object is part of the asp.net security/login components - I did not create these
objects myself!

Please tell me what to do next........

TIA
Guy

You should not have any problem from 2005 to 2000, however looks like you didn't install the whole database as does not understand ROLE

|||

Dear Al

THanks for the reply

Please explain more... what should I do......?????

When I generated the script I chose "export all objects"

Guy

|||

There is an .sql file, can you install that?

|||

You need to be certain that the scripts are exported for SQL Server 2000.

When you go to "generate scripts" (right click on DB, Tasks->Generate Scripts), after you choose the DB and pick to export all objects, you come across a long list of options. One is called "Script for Server Version". That needs to be set to SQL Server 2000. By default it is set fo SQL Server 2005. If you do not do this syntactic differences will throw many, many errors.

As long as your database doesn't use anything that is 2005 specific, you should be able to run the script.

|||Tony is right, a lot of things have been changed in SQL2005, such as system objects, some T-SQL syntax, etc. For example in the script we can see sys.database_principals, which is a view in SQL2005 but does not exist in SQL2000; and CREATE ROLE is a new command in SQL2005, but invalid in SQL2000, in SQL2000 you need to use sp_addrole.

Please suggest type Of Replication suitable in this scenario !

Hi All:
I have 2 sites, one here in India & one at Montreal working on same
database, hosted here (India), using ASP Application.
The link is dedicated over WAN. Now I want both sites to access their own DB
at their local ends (structure being same) & then replicate/snycronize both
Databases periodically, say every 60 mins or in worst case once every day.
Both sites receive customer inbound calls & people take orders, troubleshoot
product. The DB size is 3 GB & growing, though slowly.
What is best type of replication which i can use here?
Please suggest!
Thanks,
Anukul
anukuls@.yahoo.com
If your schema is static, and your users will never be accessing these
databases at the same time or if you can minimize conflicts using
partitioning you could use bi-directional transactional replication.
Otherwise you should look at merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <Anukul@.discussions.microsoft.com> wrote in message
news:10746DEA-A21D-4702-A44B-B46631DC89FC@.microsoft.com...
> Hi All:
> I have 2 sites, one here in India & one at Montreal working on same
> database, hosted here (India), using ASP Application.
> The link is dedicated over WAN. Now I want both sites to access their own
DB
> at their local ends (structure being same) & then replicate/snycronize
both
> Databases periodically, say every 60 mins or in worst case once every day.
> Both sites receive customer inbound calls & people take orders,
troubleshoot
> product. The DB size is 3 GB & growing, though slowly.
> What is best type of replication which i can use here?
> Please suggest!
> Thanks,
> Anukul
> anukuls@.yahoo.com
|||Thanks Hilary!
Yes the Database structure is static, but may change once every 1 month.
Also, users will be accessing the DB simultaneously at both ends, as Inbound
customer calls will be received at both sites, so changes relating to a
customer's address, name, order may get updated. I am over looking the
scenario where same customer calls at same time at both sites, but the same
customer's data may be worked on at both sites at same time. My ASP
application will change as it uses identities right now, but will I will
create site specific ids.
Please suggest.
Thanks,
Anukul
"Hilary Cotter" wrote:

> If your schema is static, and your users will never be accessing these
> databases at the same time or if you can minimize conflicts using
> partitioning you could use bi-directional transactional replication.
> Otherwise you should look at merge replication.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <Anukul@.discussions.microsoft.com> wrote in message
> news:10746DEA-A21D-4702-A44B-B46631DC89FC@.microsoft.com...
> DB
> both
> troubleshoot
>
>
|||merge replication will probably work best for you then.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <Anukul@.discussions.microsoft.com> wrote in message
news:0A5E7A9E-43E7-43DC-8F45-0926FD654F0C@.microsoft.com...
> Thanks Hilary!
> Yes the Database structure is static, but may change once every 1 month.
> Also, users will be accessing the DB simultaneously at both ends, as
Inbound
> customer calls will be received at both sites, so changes relating to a
> customer's address, name, order may get updated. I am over looking the
> scenario where same customer calls at same time at both sites, but the
same[vbcol=seagreen]
> customer's data may be worked on at both sites at same time. My ASP
> application will change as it uses identities right now, but will I will
> create site specific ids.
> Please suggest.
> Thanks,
> Anukul
> "Hilary Cotter" wrote:
own[vbcol=seagreen]
day.[vbcol=seagreen]
|||Thanks Again!
Is there a Book which you can recommend to setup Merge Replication?
Are there any 3rd party tools for Conflict Resolution available?
Please guide.
Thanks,
Anukul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eqXWnHp8EHA.2876@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> merge replication will probably work best for you then.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <Anukul@.discussions.microsoft.com> wrote in message
> news:0A5E7A9E-43E7-43DC-8F45-0926FD654F0C@.microsoft.com...
> Inbound
> same
their[vbcol=seagreen]
> own
replicate/snycronize[vbcol=seagreen]
every
> day.
>
|||Well I am working on a merge book. There are no third party tools I know of.
The default conflict resolvers which ship with SQL Server are pretty good.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <anukuls@.yahoo.com> wrote in message
news:u961qLp8EHA.3988@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks Again!
> Is there a Book which you can recommend to setup Merge Replication?
> Are there any 3rd party tools for Conflict Resolution available?
> Please guide.
> Thanks,
> Anukul
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eqXWnHp8EHA.2876@.TK2MSFTNGP12.phx.gbl...
month.[vbcol=seagreen]
a[vbcol=seagreen]
will[vbcol=seagreen]
these[vbcol=seagreen]
replication.[vbcol=seagreen]
same
> their
> replicate/snycronize
> every
>
|||Hi Hilary:
Just another query:
As I mentioned , what would be the best way of adding / dropping columns to
an existing Database, which is published for replication (merge
replication)?
Would sp_addrplcolumn & sp_deleterplcolumn work or are there any other ways
to it ? Even datatype changes or size changes.
Thanks,
Anukul
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eu7mcWp8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Well I am working on a merge book. There are no third party tools I know
of.[vbcol=seagreen]
> The default conflict resolvers which ship with SQL Server are pretty good.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Anukul" <anukuls@.yahoo.com> wrote in message
> news:u961qLp8EHA.3988@.TK2MSFTNGP10.phx.gbl...
> month.
to[vbcol=seagreen]
> a
the[vbcol=seagreen]
the
> will
> these
> replication.
> same
>
|||sp_repladdcolumn and sp_repldropcolumn are the best ways.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Anukul" <anukuls@.yahoo.com> wrote in message
news:O59cEGA9EHA.3640@.tk2msftngp13.phx.gbl...
> Hi Hilary:
> Just another query:
> As I mentioned , what would be the best way of adding / dropping columns
to
> an existing Database, which is published for replication (merge
> replication)?
> Would sp_addrplcolumn & sp_deleterplcolumn work or are there any other
ways[vbcol=seagreen]
> to it ? Even datatype changes or size changes.
> Thanks,
> Anukul
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eu7mcWp8EHA.3236@.TK2MSFTNGP15.phx.gbl...
> of.
good.[vbcol=seagreen]
as[vbcol=seagreen]
> to
> the
> the
ASP[vbcol=seagreen]
I[vbcol=seagreen]
using[vbcol=seagreen]
access[vbcol=seagreen]
once[vbcol=seagreen]
orders,
>
|||Also, have a look at an article on
www.replicationanswers.com for methods of changing an
existing datatype, and Kestutis's post from yesterday.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Please Help. Connecting To Server

Hi All,

I have been trawling the net to find a decent example of a way to connect to an sql 2000 server using asp.net and run a few commands.

One of the commands i need to run is: "EXEC sp_addlogin 'Albert', 'B1r12-36', 'forum2'"

Please, please, please could someone show me how to do this, and display a 'successful' message on the page if the command completed successfully...i will be eternally greatful.Hi,

This article might help:
Writing and Using Stored Procedures

Regards,|||Sorry to be thick, but after viewing that page i am still stuck.

I am not sure how to actualy connect to the server. The examples on that page show connecting to a specific database using 'trusted connections' , but what i want to do is really connect to the server using a username/password and run commands like, CREATE DATABASE.

If you could spare the time to knock up some code for me, i would be most greatful, and will even load some $ into a paypal account if you have one, just for the effort.

Please help.

I am newbie in ASP.NET and MSDE arena. I understand that MSDE has no GUI and I need to have MSDE tools to work with it. I have already installed ASP.NET 1.1 and Microsoft.NET SDK 1.1 and MSDE in my Windown Professional 2000 PC and I have run and tested for .aspx and its working perfectly. But I want to use MSDE to create some database driven web applications.

Could you please help me how I can do that? BTW, I installed Microsoft SQL Web Data Administrator and its giving me SQLWebAdminStart.exe Application error and asking for debugging while opening the program. I am stranded what to do now?

I really appreciate your help.

regards,
Peter-TorontoTry launching your DOS environment and writ 'osql help' at the prompt. That should get u started.

Please help! TSQL problems!

hi everyone,

although, this is not an asp.net problem but i really need your help!
For example, i had 3 tables (TableA, TableB, TableC)

(Relationship example)

(TableA) (TableB) (TableC)
ID 1--- 1 --1
TableAID ---ID -
TableCID ID----

if i have a relationship same as the above, but the TableB only contain the value for TableA but no TableC. how can i generate out the data for TableB if a data is missing in one field.

(Sql statement) 'This is my sql statement that cannot output anydata from TableB
select * from TableB where
TableA.ID = TableB.TableAID And
TableC.ID = TableB.TableCID

Thanks everyoneYou need to create an inner join.

Are you using SQL Server?

If so you can do this graphically by creating a new view. Then add the tables you want to use and then select the columns you want to pull data from. Sql then generates the syntax for you.

You have to relate your tables. Each must carry a PrimaryKey and a Foreign key that relates to the PrimaryKey on the other table. eg

Table1
PK_Tbl1
FK_Tbl2_ID

Table2
PK_Tbl2_ID
FK_Tbl3_ID

Table3
PK_Tbl3_ID

An inner join is much more efficient than a cross join.|||I'm struggling to undestand the question, but given...
>> if i have a relationship same as the above, but the TableB only contain the value for TableA but no TableC. how can i generate out the data for TableB if a data is missing in one field

...sounds like you need to look at LEFT joins.|||thanks for the reply!!!

actually, my project has multiple tables need to be join like this with relationship missing in tables....i am also wondering how to use left or outer join to join multi tables.

thanks|||Reading midi25s reply has made me wonder, do you actually need to have this "missing data"? Are the tables there already and you're working with them or are you also trying to create a data schema?|||i am generating reports from those tables...the report that i am dealing with now is approximately 7 tables, some of the table has missing data and need to be present in the report.
OK for example. i make a sale, but the customer did not put in the shipping address...therefore the shipping address id is missing from the sales table. but in my case, i had around 7 tables with this kind of senerio...

i think i should use left join to solve this problem but do you have any example using left join to join mulitple tables

thank you for helping and hope you can solve my problem