Problems With Joins, It Freezes

Mar 5, 2008

Hi all

I have a master table, with ID Column int, and Description column Varchar

And another details table, with MasterID Int, and Detail id INT

If i use this query:
SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col=b.id_col
Never returns data, and get freeze, and i have to kill process on Managment

But if i use this:
SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col=b.id_col+0

It returns data i Want, is this a Bug? Or i have an error?
The 2 columns is the same data.

But if I run this,
SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col+0=b.id_col
Nothing happens,

What could it be?

View 5 Replies


ADVERTISEMENT

Whole DB Freezes!

Feb 27, 2001

Ok,

My SQL Server production DB freezes for 20 minute periods about 3 times a day, during
this time no-one can do anything. I checked the perf. monitor counters (all
the ones there at default), the scary part is that they ALL fall to 0 during
the freeze, then rise to normal. Yes, that's zero %!

I checked the ID's connecting, there are no common ones that connect just
before a freeze, and there are no hardware issues. It's only a 400Mb DB,
with a trans log of 120Mb. Between 200-400 users are connected during
business hours, both from the LAN and off. Some users are running some
fairly heavy reports, but there's no correlation between these and the
freezes. The biggest table only has 1.5 million rows, and the average query
time is 232 milliseconds.

This whole mess has been going on since the 15th of Jan, we searched high
and low for any alterations made, but the client swears blind that no
changes were made. Any ideas anyone?

Thanks in advance all,


Jaishel.

View 2 Replies View Related

The DB Freezes For 5 Or 10 Minutes

Oct 9, 2007

Hi,
the SQL DB freezes and no one can access the DB for 5 or 10 minutes. Even select queries don t execute, nothing is displayed.

Until we kill the process id that s blocking in the sql activity monitor, only then the DB is released and people can work again.
What does it mean that no query executes until we kill the processes ID? what could it be?

Also, recently we created indexes and ran tuning adviser, is it possible that the creation of indexes cause the freeze of a DB? is that possible?

Thanks a lot for your help

View 6 Replies View Related

Joins On Views That Are Formed With Outer Joins

Nov 3, 2000

We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.

If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.


625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.

Error 4425 text would imply that joins with view formed by outer joins should be avoided.

Any ideas on the principles involved here.

View 1 Replies View Related

DTS Execute SQL Task Freezes

Nov 15, 2004

Hi all !

Hi have a package with 3 steps :
1 - import data from flat file into a temp table
2 - copy selected data from the temp table to permanent tables
3 - truncate temp table

The task #2 is a SQL statement

INSERT INTO MODELS (MODEL_ID)
SELECT DISTINCT MATNR
FROM STANDARD
WHERE MATNR NOT IN (SELECT MODEL_ID FROM MODELS)

where STANDARD is the temp table and MODELS a permanent one. My problem is this simple task freezes all the time. It stalls on 'Starting' and stays in this state forever when I execute the package.

Any idea why ?

View 3 Replies View Related

MSDE Installation Freezes

Feb 24, 2004

I am installtion MSDE on a Windows XP Pro. The install gets through to 5 seconds and freezes. THis sits for about an hour and nothing happens. The Microsoft SQL Server folder is created, but the services are not created. We tried to reinstall many times and removed the registry key before each reinstall (after uninstalling and rebooting). Any ideas about what could be wrong?

View 9 Replies View Related

Server Freezes While Doing Checkpoint ?!

Jul 24, 2007

We're running the Microsoft product SMS 2003 SP1 for software deployment, patching, hardware inventory, etc. The back-end is SQL 2000 Enterprise SP4 which is installed on the same box as the SMS 2003 SP1 product, and the DB is 145GB's.

We started noticing that the server would freeze every minute or so for 30 seconds. We started logging stats via perfmon and saw that the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 for 30 seconds at the same time the freezing occurred. I have determined that this is occurring during the checkpoint. The recovery interval option is set to the default of 0 on SQL, when I changed the setting to every 5 minutes, the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 every 5 minutes and would subside after 2 minutes. I understand the need for the checkpoint / recovery interval option, but don't believe this high average disk queue length should be occurring.

Does anyone know why this is happening and how to fix this ? The freezing of the box while checkpointing is killing me.

Thanks

View 6 Replies View Related

SQL Freezes Machine & Chews A Lot Of Memory

Jun 24, 2002

SQL server freezes my machine.l'm running sql2000 and sp2.Every now and then it freezes my machine as if its running a process in the background. How can l sort this out its slowing my machine down.

l've got 512mb of ram and l don't see why l should have problems with memory .Please help

View 2 Replies View Related

DB Freezes When Replication Agent Cant Deliver

Feb 27, 2004

Hi

We are repeatedly having the trouble of our publisher database becoming unresponsive during network problems.
We have publisher in carribean, distribution agent in canada and subscribers all over. The problem comes when publisher cant deliver to distributor due to network outages.

We are planning on moving the distributor to the carribean ultimately but that will take a while, and our application repeatedly needs to be restarted due to these freezes.

Does anyone know of this problem & possible ways to fix it in the short term (short of changing our replication topology).

is this a bug in replication?

ps.database is merge & transactional pubs. (some tables merge/some trans)

help much apprec,
des

View 6 Replies View Related

SQL Server Profiler Freezes Application?

Apr 4, 2008

Hi, I'm using SQL2005 v9.00.3042.00 (SP2) on Windows 2003. I have a situation, when I turn on and begin to trace a database using the "SQL server Profiler", that particular VB application that uses the databases freezes (hourglass) when user click on anywhere that require a data query (download list, etc.) until I either pause or stop the trace, what gives? Thanks in advance.

View 2 Replies View Related

Windows Server 2003 Freezes

Mar 7, 2008

Set up - windows server 2003 - SQL server 2000 + tools installed

Not sure why its happening but recently when running some scripts onto one of the databases held on the server windows crashes.

I say crashes - it is non responsive but the mouse cursor can be moved.

these scripts are not being run from the server but from one of many machines on our companies network.

Any suggestions would be great

View 1 Replies View Related

SQL Backup Hangs, Server Freezes

Feb 28, 2007

We have SQL 2000 running on Server 2003. The server has 4 GB RAM, RAID 1 and has the /3GB switch in Boot.ini. We have 6 databases, the largest is about 14 GB. Until recently I could use Enterprise Manager to perform database backups to a folder on the same server but now the operation fails. After selecting the location for the backup and clicking OK, the hard drive light comes on but goes out after 15 - 30 seconds. The progress bar does not move. I've waited and waited but nothing further happens and the server doesn't respond. The only solution is to push the power button and let the server re-boot. Very occasionally the backup will run for a minute or two and the progress bar might get to about one third of the way across, (in which case the destination file will show up with more than 0 bytes), but it never completes. I can find nothing in the event log relating to this problem. As far as I am aware, nothing has changed since the last successful backup. Backups to tape using Retrospect appear to work fine but I haven't done a test restore. Does anyone have any ideas as to what I can try to get the backups working again?

View 14 Replies View Related

Slowly Changing Dimension Freezes

Apr 9, 2008

I am trying to move data from a transactional database to a data warehouse using a slowly changing dimension. The transactional data comes from a view in SQL server that takes <60 seconds to run and returns about 60k rows. The warehouse table is currently 80k rows long (and growing), and contains 7 historical (type 2) dimensions. When I execute the package in BIDS the DataFlow Task begins to execute, and shows that between 20k and 30k rows have been pulled from the data source into the SCD Transform in the first hour before it simply stops doing anything. This is not to say execution stops; it continues. There is no error thrown. No warning given. System resources are 98% free. The database is not being hit at all. And yet, I have let the package sit 'still' as it were for over 8 hours, and nothing ever happens.

Here is a copy of one log:

starttime endtime message
4/8/2008 9:36 4/8/2008 9:36 Execute phase is beginning.

4/8/2008 9:36 4/8/2008 9:36 PrimeOutput will be called on a component. : 1715 : Union All
4/8/2008 9:36 4/8/2008 9:36 A component has returned from its PrimeOutput call. : 1715 : Union All
4/8/2008 9:36 4/8/2008 9:36 PrimeOutput will be called on a component. : 2912 : Staged Queues
4/8/2008 9:36 4/8/2008 9:36 Rows were provided to a data flow component as input. : : 2970 : DataReader Output : 70 : Slowly Changing Dimension : 81 : Slowly Changing Dimension Input : 9947
4/8/2008 9:37 4/8/2008 9:37 A component has returned from its PrimeOutput call. : 2912 : Staged Queues
4/8/2008 9:37 4/8/2008 9:37 A component has returned from its PrimeOutput call. : 2912 : Staged Queues
4/8/2008 9:59 4/8/2008 9:59 Rows were provided to a data flow component as input. : : 1718 : New Output : 1715 : Union All : 1716 : Union All Input 1 : 3825
4/8/2008 9:59 4/8/2008 9:59 Rows were provided to a data flow component as input. : : 1688 : Historical Attribute Inserts Output : 1682 : Get End Date : 1683 : Derived Column Input : 645
4/8/2008 9:59 4/8/2008 9:59 Rows were provided to a data flow component as input. : : 1702 : Derived Column Output : 1692 : Update End Date : 1697 : OLE DB Command Input : 645
4/8/2008 10:01 4/8/2008 10:01 Rows were provided to a data flow component as input. : : 1759 : OLE DB Command Output : 1715 : Union All : 1758 : Union All Input 2 : 645
4/8/2008 10:01 4/8/2008 10:01 Rows were provided to a data flow component as input. : : 2970 : DataReader Output : 70 : Slowly Changing Dimension : 81 : Slowly Changing Dimension Input : 9947
4/8/2008 10:24 4/8/2008 10:24 Rows were provided to a data flow component as input. : : 1718 : New Output : 1715 : Union All : 1716 : Union All Input 1 : 3859
4/8/2008 10:24 4/8/2008 10:24 Rows were provided to a data flow component as input. : : 1688 : Historical Attribute Inserts Output : 1682 : Get End Date : 1683 : Derived Column Input : 641
4/8/2008 10:24 4/8/2008 10:24 Rows were provided to a data flow component as input. : : 1702 : Derived Column Output : 1692 : Update End Date : 1697 : OLE DB Command Input : 641
4/8/2008 10:26 4/8/2008 10:26 Rows were provided to a data flow component as input. : : 1759 : OLE DB Command Output : 1715 : Union All : 1758 : Union All Input 2 : 641
4/8/2008 10:26 4/8/2008 10:26 Rows were provided to a data flow component as input. : : 2970 : DataReader Output : 70 : Slowly Changing Dimension : 81 : Slowly Changing Dimension Input : 9947
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1718 : New Output : 1715 : Union All : 1716 : Union All Input 1 : 3969
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1688 : Historical Attribute Inserts Output : 1682 : Get End Date : 1683 : Derived Column Input : 662
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1702 : Derived Column Output : 1692 : Update End Date : 1697 : OLE DB Command Input : 662
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1793 : Union All Output 1 : 1787 : Get Start Date : 1788 : Derived Column Input : 9947
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1814 : Derived Column Output : 1797 : Insert Destination : 1810 : OLE DB Destination Input : 9947
4/8/2008 15:34 4/8/2008 15:34 The pipeline received a request to cancel and is shutting down.
4/8/2008 15:34 4/8/2008 15:34 Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown or an error in another thread is causing the pipeline to shutdown.
4/8/2008 15:34 4/8/2008 15:34 Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown or an error in another thread is causing the pipeline to shutdown.
4/8/2008 15:34 4/8/2008 15:34 The pipeline received a request to cancel and is shutting down.
4/8/2008 15:34 4/8/2008 15:34 Thread "WorkThread1" has exited with error code 0xC0047039.
4/8/2008 15:34 4/8/2008 15:34 Thread "WorkThread1" has exited with error code 0xC0047039.


Notice the time difference between the last OnPipelineRowsSent event and the first OnError event (when I clicked the stop button): 5 hours! In all that time, SSIS did not log a single event, or use more than 2% of my processor or exceed 1GB page file or hit the database even once! I am assuming this means it is simply not doing anything. It is not failing, nor is it executing, it is just sitting there.

Has anyone experienced a similar problem? Does anyone know how I might troubleshoot this? Thanks in advance for any help, and let me know if I need to clarify. Also, I am new to SSIS, so if I am missing something obvious, go easy on me! Thanks.


Mitch Connors

View 6 Replies View Related

My App Temporary Freezes With SQL 2005 But Not With SQL 2000

May 29, 2007

Hi Everybody,



I am having a very weird problem, and I was wondering if you have any idea about what I could do to fix this.



In a nutshell:

* I have this application that uses ADODB and ODBC to connect to an SQL database.

* We have two installations: one running SQL 2000 sp 3, and the other SQL 2005 sp 1.

* The one that runs on SQL 2000, my app performs 8,500 transactions a day with no problem.

* The one that runs on SQL 2005, my app performs 200 transactions a day, and goes down at least once a day.



Trying to figure this out, I noticed that, from time to time, when working with SQL 2005, the application kind of freezes when it tries to read or write to the database, and after a while (say 20 seconds or more), it continues as if nothing had happened. Trying to debug this issue, I noticed that this wait time period is ended if I close one of the other open connections that I have (say, I close the query analyzer)



As people may say, ADODB and ODBC should no longer be used... however, I have noticed this same kind of behavior with other tools, like the query analyzer from the SQL 2000 client tools when connecting to the SQL 2005 server.



I suspected a Licensing issue... however, I haven't been able to replicate this problem by opening several query analyzer instances.



Any help is greatly appreciated!!!!!



Best Regards,



Manuel



View 5 Replies View Related

Changing Stored Procedure Freezes Crystal

Aug 18, 2006

After I change a fairly complex stored procedure and I run a reportagainst it, crystal hangs at "assesing database". I have verified thedatabase. When I run a trace on SQL is shows repeated cachemiss overand over. I let it run for 30 minutes and nothing.Anyone?

View 5 Replies View Related

Debugging CLR Stored Procedure Freezes VS 2005

Nov 20, 2007



To the Moderator:

I am not sure if this thread needs to go into this forum or there is one for C# stored procedures/CLR Database Objects.

To All:

Is this a known issue that when debugging CLR stored procedures the VS 2005 freezes even before reaching into the stored procedure? I can restart my computer and debugging works for a while and then it starts to take forever when I step into (F10) during debugging. I am running only one instance of sql server, and even that of my local machine as I have never been able to debug on a remote server.

This random and unstable behavior of the debugger is proving very unproductive and costly. Is there a microsoft update/fix/patch to make debugging CLR stored procedures a smooth process?

Thanks.

View 13 Replies View Related

Integration Services :: Connection Freezes All Time

Dec 12, 2013

I'm trying to use SSIS in MSSQL 2012 to extract data from MAS90 database.The connection string is tested to be working, because I can extract successfully using the same one in Excel.I follow the wizard of import data in management studio, but after selecting the tables and mapping all those things, when clicking finish, the management studio always freezes.

View 6 Replies View Related

SQL Server Freezes : Consumes High Disk Space

Dec 19, 2005

Hi,

The SQL Server Database hangs overnight and also consumes high disk space on one of our servers. This has been recurring for quite a few weeks and occurs daily.

Can somebody assist me in trouble-shooting the same

Thanks in Advance
Deepak

View 3 Replies View Related

Weird: BIDS Freezes When Opening OLE-DB Source With SQL-Query

Mar 7, 2007

Hello again!

Once again I got stuck with a weird problem and I´m dependent on you.

I built a Data Flow task in which I did an OLE-DB Source Adapter with a SQL-Query as Source.

The Query goes on a Linked Server which connects to a Informix Table via ODBC. When I enter the Query and klick "OK" or try to go on the "Columns"-Tab, the BIDS freezes completely and the "Delay Notification Message" appears in the System Tray. When I check the Activity Monitor of my SQL Server, there´s a process marked as "Runnable" (green) which contains the Query

"select collationname(0x0904D00034)"

This one is oviously run by Visual Studio and doesen´t execute properly. I waited for an hour or so, but nothing changes. I´m running SQL Server 2005 SP2 (9.00.3042)

Any help would be greatly appreciated!!!

Thanks in advance!

View 2 Replies View Related

JOINS To Sub-Queries -vs- JOINS To Tables

Aug 11, 2005

SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig

View 3 Replies View Related

Data Flow With Fuzzy Lookup Freezes On Pre-execute Phase

Dec 5, 2006

I have a fuzzy lookup in a data flow but this data flow never gets pass pre-execute phase. What is the problem?



Thanks

View 2 Replies View Related

DataReader Source Freezes In Validation Of MySQL Stored Procedure

Dec 10, 2007

I have a package that hangs in the designer after I change the sql statement in a DataReader Source from a 'select' to a 'call stored procedure'. The stored procedure takes 2 date parameters. I use an expression to build the 'call stored proc' statement and the 2 date strings. The data reader source uses an ADO.Net connection manager. The ADO.Net connection manager uses the provider for MySQL (Connector/.Net 5.1) which I installed from MySQL.com (http://dev.mysql.com/downloads/connector/net/5.1.html). Before creating the stored procedure I had been using an expression to build a 'select' statement with two date variables as follows:

select ...
where ads.last_seen >= "" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] )
+ "" and ads.first_seen <= "" + (DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] )+ "" group by sm.service_provider_id,lm.location_id,lm.web_sublocation_id;"

The sql for the data reader source is set via the sql command property of the data flow component.

After testing the sql, I created a stored proc from this sql and then changed the expression (using the sql command property of the the data flow component) to build the 'call stored proc' statement, like this.

"call usp_SEL_Rollup ("" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] ) + "","" +(DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] ) +"");"

then when I tried to switch to the data flow tab, the editor froze, with the status bar saying "validating datareader source". The data flow tab says "Loading...". I don't know how to troubleshoot this. Each time I have tried I have had to kill the application. Any ideas/suggestions?

Thanks,
Al

View 6 Replies View Related

Ansi Joins Vs. SQL Joins

Oct 12, 1999

Hi,

Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?

The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)

However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.

Angel

View 1 Replies View Related

UNION ALL, Joins And No Joins

Feb 29, 2008

I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,

Table A Contains a Main Image, this image is displayed in the results
Table B Contains an Icon, this image is displayed in the results
Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.

Select title, description, image from tableA
UNION ALL
Select title, description, icon as image from tableB
UNION ALL
title, description, ( inner Join SELECT top(1)
from imageTableC where imagetableC.FK = tableC.PK)
as image from tableC


Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.

View 14 Replies View Related

Multiple Left Joins (2 Left Joins 1 Big Headache)

Sep 1, 2005

Hi All,

Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.


My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx

I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.

View 2 Replies View Related

Inner Joins

Jan 27, 2004

When I add a constraint to the where part of the SQL statement below it will only show the nc_department.department where siteid equals it. How do I pull and display all of those departments regardless of where the siteid = the selected item? In other words doesnt the Left outer Join supposed to grab all contents from the left table regardless?

SQL = "SELECT nc_department.order_id, nc_department.department, Count(nonconformance.department_id) as 'events', ISNULL(SUM(nonconformance.nc_wafer_qty),0) as wafers FROM nc_department LEFT OUTER JOIN nonconformance ON nc_department.department_id = nonconformance.department_id WHERE nc_department.active = '1' AND nonconformance.site_id = '" & siteid.SelectedItem.Value & "' GROUP BY nc_department.department , nc_department.order_id"

thanks in advance

View 1 Replies View Related

JOINS And WHERE

May 21, 2001

JOIN MUDDLE

Soory if this is a bit basic, but I'm chasing my tail:
I have two select statements:
SELECT MyRows from Table1 where X = 1
SELECT OtherRows From Table 2 Where y = 3
I want to produce a LEFT JOIN between the result of each query, and return the resultant rows
I know this is not correct syntax:

(SELECT MyRows from Table1 where X = 1)
LEFT JOIN
(SELECT OtherRows From Table 2 Where y = 3)
ON
Tabel1.Row1 = Table2.Row2

but I think it illustrates what I want to do
If I do the WHERE after the join I only get where there is a righthand table
I cant get the syntax right, an example with the correct grammar would be very much appreciated

View 2 Replies View Related

Help-Joins

May 25, 2001

I am trying to figure out which Option is the best/fastest and why?
I have simplified the query a lot. The actual query consists of lots of tables.

OPTION A (the search for the specific id is in the JOIN)
select * from table A left join table B on a.id = b.id and a.id in (1,2,3)

OR
OPTION B(the search for specific id is in the WHERE clause)
select * from table A left join table B on a.id = b.id
where a.id in (1,2,3)

View 2 Replies View Related

Joins

Jun 29, 2000

Could somebody please tell me why we should never mix old-style and ANSI-style joins in the same query? What would be the consiquences?

Thank you

View 1 Replies View Related

Joins

Nov 16, 1999

Instead of inserting into, how can I use a join of multiple
tables to update a table.
This is the join that inserts.

INSERT INTO [table] (MyID, material, dollars)
SELECT l.MyID, material, dollars
FROM Tab_Client_Input l left outer JOIN
Tab_Special_tox r ON l.MyID = r.MyID

Thanks in advance

View 1 Replies View Related

Self Joins

Dec 15, 2007

can somebody give a good explanation of uisng a self join with an example
that would really help
thks

View 3 Replies View Related

MS SQL Joins

Jun 23, 2004

Hi Folks,
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:

Store Procedure:


--Create Procedure dbo.IMS_Donation

--AS

Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO

SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO

SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID

GO

SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE

Error Message:


(845 row(s) affected)


(844 row(s) affected)


(396 row(s) affected)

Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

View 2 Replies View Related

Too Many Joins?

Jul 9, 2004

Microsoft SQL Server 2000:

I am not a SQL programmer, but I am trying to use data collected in our Job Costing system to feed data to a crystal report that summarizes the current department name and the last date each job was scanned into a department.
I used aliases of the Process table to extract the max date for each department.

This statement works fine, but sometimes it hangs and locks my process table. I am not sure what event causes the lock, but I think it has to do with the users aborting the report during the SQL extract or multiple users trying to report at the same time (8am when they first arrive at work).

I showed this to a consultant and he showed me that the MAX statement in the 3rd to last join was eating most of the execution time. Also he told me I am using too many joins and should look into Table Views. The statement completes in 15 to 45 seconds, depending on the workload.

What can I do to improve the performance of this code and to avoid the locks?

SELECT
OH.JobNumber,
OH.PlantID,
OH.CreateOpr,
OC.ComponentNumber,
PJN.ProductionCode as ProductionMax,
P2.ProcessCode as MaxCC,
PS.Description AS MaxCCDesc,
PJ1.ProductionCode,
P.ProcessCode as ProdCC,
P51.CreateDatim AS SchCCDate,
P53.CreateDatim AS TypCCDate,
P55.CreateDatim AS OPrCCDate,
P57.CreateDatim AS HPrCCDate,
P59.CreateDatim AS CRmCCDate,
P61.CreateDatim AS CCeCCDate,
P63.CreateDatim AS PRmCCDate,
P65.CreateDatim AS BinCCDate,
P67.CreateDatim AS JbOCCDate,
P69.CreateDatim AS OnDCCDate,
P71.CreateDatim AS ShpCCDate,
PS1.Description AS CCDesc,
P.CreateDatim AS CCDate,
OQT.Quantity,
OH.JobDescription,
OH.FormNumber,
OH.JobDescription,
OH.USERDEFINED1 AS JobType,
OH.CustAccount,
OH.CustName,
(select OrderHeader.DueDate from OrderHeader where OrderHeader.JobNumber = OH.JobNumber and OH.NoDueDate = 0) as DueDate,
OH.ProofDate,
OH.OrderDate,
OH.SalesRepCode,
OH.PONumber,
OH.PrevPONumber,
OH.NoDueDate,
OC.UserDefined1,
OC.Description as ComponentDescription
FROM OrderComponent OC
INNER JOIN OrderHeader OH ON OC.JobNumber = OH.JobNumber
INNER JOIN OrderQtyTable OQT ON OC.JobNumber = OQT.JobNumber
and OC.ComponentNumber = OQT.ComponentNumber
and OC.QtyOrdIndex = OQT.QuantityLineNo
LEFT JOIN ProductionJobNumber PJ1 ON PJ1.JobNumber = OH.JobNumber and PJ1.ComponentNumber = OC.ComponentNumber
LEFT JOIN Production P ON PJ1.ProductionCode = P.Code
LEFT JOIN Production P51 ON PJ1.ProductionCode = P51.Code AND P51.ProcessCode = 9151
LEFT JOIN Production P53 ON PJ1.ProductionCode = P53.Code AND P53.ProcessCode = 9153
LEFT JOIN Production P55 ON PJ1.ProductionCode = P55.Code AND P55.ProcessCode = 9155
LEFT JOIN Production P57 ON PJ1.ProductionCode = P57.Code AND P57.ProcessCode = 9157
LEFT JOIN Production P59 ON PJ1.ProductionCode = P59.Code AND P59.ProcessCode = 9159
LEFT JOIN Production P61 ON PJ1.ProductionCode = P61.Code AND P61.ProcessCode = 9161
LEFT JOIN Production P63 ON PJ1.ProductionCode = P63.Code AND P63.ProcessCode = 9163
LEFT JOIN Production P65 ON PJ1.ProductionCode = P65.Code AND P65.ProcessCode = 9165
LEFT JOIN Production P67 ON PJ1.ProductionCode = P67.Code AND P67.ProcessCode = 9167
LEFT JOIN Production P69 ON PJ1.ProductionCode = P69.Code AND P69.ProcessCode = 9169
LEFT JOIN Production P71 ON PJ1.ProductionCode = P71.Code AND P71.ProcessCode = 9171
LEFT JOIN Process PS1 ON P.ProcessCode = PS1.ProcessCode
LEFT JOIN ProductionJobNumber PJN ON PJN.ProductionCode =
(select MAX(ProductionJobNumber.ProductionCode)
From ProductionJobNumber
where OH.Jobnumber = ProductionJobNumber.JobNumber
AND OC.ComponentNumber = ProductionJobNumber.ComponentNumber)
LEFT JOIN Production P2 ON PJN.ProductionCode = P2.Code
LEFT JOIN Process PS ON P2.ProcessCode = PS.ProcessCode
WHERE OH.JobStatus = 'IN PROCESS'

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved