Connection Timeouts - A Tough One!

Dec 16, 2003

I'm having major problems with SqlClient Data provider connections in my application. I'm using Win 2003 Server with SQL Server 2000 (both on the same machine), and my application is developed in VB.NET.

I'm using the following db connection string:

Data Source=(local);Initial Catalog=database;Uid=user;Pwd=password;max pool size=100;

As you can see max pool size is set to 100. The SQL Database has it's MAX_CONNECTIONS property set to >32000.

The symptoms are that at seemingly random times, not nessecarily during peak hours, the application becomes extremely slow and stops responding. The error message that is beeing generated is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

After monitoring and debugging, I've found two things.

1) At random times, zero-three times a day, the number of database connections (according to performance monitor) rises from normally 2-8 up to 20-25. After a while, usually 10-60 minutes, it drops back to normal. This is directly related to when the timeout errors occur. It doesn't seem to happen more often during peak load, for some reason. I suspect there is connection leakage in the application, but I've done extensive debugging, and I just can't find any. Also, since the peaks occur randomly and I can't reproduce the error in my development environment, I have no solution to this problem. A few weeks ago, the application ran during heavy load without this problem for 7 days. Before and after that, there have been errors at least every second day. That confuses me even more.

2) The number of connections (monitored in perf mon) never reach above 25. Why? I'm using SQL Server enterprise edition, it's connection limit is over 32000. ADO pool size is 100, and I can't find anything else that would limit the amount of concurrent connections. I'm totally confused. Why does number of connections never rise above 25? Is there a setting hidden somewhere that I just can't find? Also, according to Performance Monitor, I don't get any failed connects, I just get failed commands. Maybe that's a clue? Could the problem some kind of deadlocks in the database that causes timeout?

I realize this entire post is rather confusing. It reflects the state of my mind, I guess, after working around the clock on this issue for a month :)

Any help is deeply appreciated!

View 2 Replies

Intermittent Connection Timeouts

Apr 6, 2007

I'm having an issue with what appears to be SQL Server 2005 deciding to randomly ignore new connections.

I currently have two virtual servers - one running just SQL Server 2005, the other running Reporting Services, Windows Sharepoint Services and Team Foundation Server.

For 3 weeks, it was all working perfectly, then on Wednesday night the server (and both Virtual Servers) was rebooted after installing the latest updates for Windows. Since then, I've had this issue.

It will work fine for a while, then it'll start throwing loads of Errors and Warnings into the Event Log, all along the lines of unable to connect to the database. The Reporting Services Configuration utility throws up the same problem. Then randomly, it'll start working again.

If anyone has any ideas, they would be much appreciated as this is driving me crazy!

Thanks!

View 2 Replies View Related

Tough One - 28000 (18452) Not Associated With A Trusted Connection

Sep 6, 2007

Here's a real puzzler. I have a remote SQL Server running on a different domain than the one I am logged in to. From my own PC, logged in as myself, I can create an ODBC connection to this remote server (using integrated authentication) with no problem. When I log in to any other PC as myself, I get the "28000 (18452) Not associated with a trusted connection" error. This would therefore seem to be a PC-specific problem, but I can't see any difference between the PCs. All PC's are running the same version of MDAC.

Firewall is down, no proxy is involved, and I have given myself Administrative privelege on the PCs.

Can anyone explain this? Thanks in advance.

View 9 Replies View Related

SQL Server Admin 2014 :: How To Close Active Connections Or Create Connection Timeouts

Dec 9, 2014

we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server. When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server. Or how can i automatically close connections when they get higher than 50 connections.

This settings should be sql server 2008 related.

View 5 Replies View Related

This Is A Tough One

Jun 27, 2007

The following table is counts of the patients that are currently in these statuses. For example, there are 4 people in Triage/Greenhttp://www.helixpoint.com/sql/TriageStatusGrid.jpgIf you look at a screen shot of the tables below, Red/Yellow/Green... These are in the Priority table. Red Being "Immediate or ID of 1http://www.helixpoint.com/sql/db.gifNow the grid and the db do not match...but here is a scenario from the db screen shot.. Patient 1, 2, 3, and 4 are currently in Sector 2 (transport)Patient 1, 3, and 4 are currently in Priority 1(Immediate)Here is a kicker. Patient 4 can not be counted because he has a dischargeDateTime in the Patient table.So here are the counts I need to get:So there are 3 patients in the transport column ( Patient 1, 2, 3) “4 has been discharged�?2 patients in the immediate/Red column ( Patient 1, 3) “4 has been discharged�?I would need to return a number 2 for the Transport/Red columnHow can I do this sql? Would I do this in multiple sql calls? Can you give me an example?This is what I triedIt does not seem to use the current status SELECT COUNT(*) AS Expr1FROM Patient LEFT OUTER JOINSectorHistory ON Patient.PatientID = SectorHistory.PatientID LEFT OUTER JOINPriorityHistory ON Patient.PatientID = PriorityHistory.PatientIDWHERE (Patient.DischargeDateTime IS NULL) AND (PriorityHistory.PriorityID = 1) AND (SectorHistory.SectorID = 2)

View 1 Replies View Related

Timeouts

Jul 30, 2001

Hello,

Has anyone experienced timeouts on SQL Server 2000 that happen at regular intervals?

Regards,
Brent.

View 2 Replies View Related

Timeouts

Apr 21, 2004

Anyone else expirencing some heavy duty timeouts?

View 2 Replies View Related

Timeouts: What Are All Of The Possible Causes

Apr 1, 2008

of query timeouts in SQL Server Management Studio?

I have increased the timeout setting in many places and still receive a timeout message within 40 seconds for certain update queries that involve large tables and many records. The only workaround is to break up the job into smaller queries but this makes management, unmanageable.

Is there a solid alternative to SQL Server Management Studio?

View 6 Replies View Related

Timeouts!!

Jul 23, 2005

hi!I am having some timeout issues.I am running sql 2k with 3gig available ram.I did a 600,000 record delete on a table that gets written to by theactive/production application and my application timed out when it wasdoing the delete.why did stored procedures that ran fine before I started the largedelete slow down?Some of the procedures which slowed down were accessing the same tablewhere i was doing the delete.Thanking you in advance!!parez

View 1 Replies View Related

Tough Question

Jun 25, 2004

I have an employee table, which i have to join with itself because I have to findout someones primary boss. An employee can have several bosses...

This is an example of the table.

Emp_Id 1
Emp_name John
Boss 'n/a'

Emp_Id 2
Emp_name Peter
Boss 1

Emp_Id 3
Emp_name Mary
Boss_Id 1

Emp_Id 3
Emp_name Mary
Boss_Id 2

I know this isnt a normalized table, but anyway... for each boss that an employee has there is one record depicting the employes boss.

In this case Mary has 2 bosses, John and Peter.

What I need is a query that returns the employee information and the primary boss( in this case the boss with the lowest id)

So for mary the query would return

Emp_iD, Emp_name, Boss_Id
3 Mary 1

Anyone know how could i do this?

View 2 Replies View Related

Tough Problem...

Jun 29, 2004

I need a query that will return data in the following format.

Col1 Col2 Count
---------------
A | B | 1
A | C | 2
A | D | 3
A | E | 4
B | A | 1
B | B | 2
B | C | 3
--------------

In other words I want to group the results by col1 and when col1 one changes I want to restart my rowcount. I also want to return the row count for each record.

I have tried many different methods, but I am starting to think that this is not even possible.

Any ideas?

Thanks...

View 13 Replies View Related

Tough Question

Apr 21, 2008

Ok, I'm needing to write a view for a 3rd party scripting tool to call. Apparently this scrpting tool cannot call a function or SP.

Here is the problem. The view needs to take a notes col and a date col
and concat them together as one col and the cus acct # as 2nd col.
There will be several rows of notes to one cus acct #.

So if a normal select against this returns

note date acct
================================
txt1 1/1/00 1
txt2 1/2/00 2
txt3 1/3/00 1

I need to return this through a view

notes acct
===========================
txt1 1/1/00, txt3 1/3/00 1
txt2 1/2/00 2

I realize there are several ways to do this in a SP or function
but through a view?

View 2 Replies View Related

Tough Query

Feb 13, 2004

Hi,

I have a table with a couple of million rows. Each row as its datetime field spilt between numerous columns (year, month, day, hour... it's a datawarehouse fact table).

Here's what I'd like to do in a query:

If a value is missing (in the column value) it's set to -999. What I'd like to do is to set this column to the value of the hour before this one. For example, here's some data before and after an update:

BEFORE:
MONTH DAY HOUR VALUE
==================
01 31 21 33.5
01 31 22 -999
03 07 24 87.6
03 08 01 -999

AFTER:
MONTH DAY HOUR VALUE
==================
01 31 21 33.5
01 31 22 33.5
03 07 24 87.6
03 08 01 87.6

Here's the complete design of my table
IDENT nvarchar 50
THEDATE datetime
THEDAY int
THEMONTH int
THEYEAR int
THEVALUE real
SOURCE nvarchar 255

How can I do this?

Thanks,

Skip.

View 2 Replies View Related

Tough Little Trigger

Jul 23, 2005

Given the following 3 Tables:CREATE TABLE [Company] ([CompanyID] [int] NOT NULL ,[DateTimeCreated] [datetime] NOT NULL CONSTRAINT[DF_Company_DateTimeCreated] DEFAULT (getdate()),[DateTimeModified] [datetime] NULL ,CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED([CompanyID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [CompanyOffice] ([CompanyID] [int] NOT NULL ,[OfficeID] [int] NOT NULL ,[IsActive] [bit] NOT NULL ,CONSTRAINT [PK_CompanyOffice] PRIMARY KEY CLUSTERED([CompanyID],[OfficeID]) ON [PRIMARY] ,CONSTRAINT [FK_CompanyOffice_Company] FOREIGN KEY([CompanyID]) REFERENCES [Company] ([CompanyID]),CONSTRAINT [FK_CompanyOffice_Office] FOREIGN KEY([OfficeID]) REFERENCES [Office] ([OfficeID])) ON [PRIMARY]GOCREATE TABLE [Office] ([OfficeID] [int] NOT NULL ,[DateTimeCreated] [datetime] NOT NULL CONSTRAINT[DF_Office_DateTimeCreated] DEFAULT (getdate()),[DateTimeModified] [datetime] NULL ,[FullOfficeName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,CONSTRAINT [PK_Office] PRIMARY KEY CLUSTERED([OfficeID]) ON [PRIMARY]) ON [PRIMARY]GOThe CompanyOffice.dbo.IsActive bit field is supposed to be marked"true" for 1 record per a given Office (i.e. there can only be a single"Active" Company for any given Office). I decided the best way toenforce is through a trigger...My initial thoughts were a toggling effect (similar to the behaviorthat a radio button exhibits)... which would work like a champ for aSingle Row Insert or Update but for a Multi Row Insert/Update not thatstaight forward... I fooled around a little with some complicatedsub-queries that did not pan out. The only other way to do this is toutilize a cursor (at least that I can think of). Because of theoverhead with a cursor, I find this incredibly undesirable.My secondary thought was to just restrict an Insert or Update Statementthat leaves the Table in an "error" state (2 or 0 Active Companies peran Office). Then I realized that if the "Toggling Trigger" did notexist from above, it will often be the case that the Table would haveto be left in an "error" state for a short while, until a second updatestatement is run. (example, I insert a new active Company in theCompanyOffice table for an Office, then I go to the other activeCompany record for this Office and set the IsActive flag to false...for that short period of time between the 2 statement the DB is an"error" state, because there are 2 Active Companies for that singleOffice.) That makes this solution very undesirable.Any suggestions?Thanks in Advance --Rich

View 7 Replies View Related

Tough Sql Query

Feb 18, 2006

I am going mad with this Query. I need to join 3 Tables. Their FormatsareVouchers[VoucherID] [uniqueidentifier] NOT NULL ,[VoucherTypeID] [int] NOT NULL ,[VoucherNo] [int] NULL ,[VoucherDate] [datetime] NOT NULL ,[VoucherNarration] [varchar] (255)CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED([VoucherID]) ON [PRIMARY]Ledgers[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,[LedgerName] [varchar] (50) COLLATECONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED([LedgerID]) ON [PRIMARY]CREATE TABLE [Transactions] ([TransactionID] [uniqueidentifier] NOT NULL ,[VoucherID] [uniqueidentifier] NOT NULL ,[ByTo] [char] (1)[LedgerID] [int] NOT NULL ,[Credit] [money] NOT NULL ,[Debit] [money] NOT NULL ,CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED([TransactionID]) ON [PRIMARY] ,CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY([LedgerID]) REFERENCES [Ledgers] ([LedgerID]),CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY([VoucherID]) REFERENCES [Vouchers] ([VoucherID])) ON [PRIMARY]GOThe Required Output isID VoucherNo VoucherDate LedgerName Amount1 1 2001-09-03 Bank-1 2400.002 2 2001-09-03 Cash 600.003 3 2001-09-03 TAX A/C 0.004 4 2001-09-03 Bank-1 4000.005 5 2001-09-03 Bank-10.00But, I am getting More than One row from the transactions table. I justneed the first matching rowID VoucherNo VoucherDate LedgerName Amount1 1 2001-09-03 Bank-1 2400.002 2 2001-09-03 Cash 600.003 3 2001-09-03 TAX A/C 0.004 4 2001-09-03 Bank-1 4000.005 4 2001-09-03 Cash 400.006 5 2001-09-03 Bank-1 0.007 5 2001-09-03 Cash 5035.00The Query I am using isSELECTdbo.Vouchers2001.VoucherID,dbo.Vouchers2001.VoucherNo,dbo.Vouchers2001.VoucherDate,dbo.Ledgers.LedgerName,SUM(dbo.Transactions2001.Debit) AS AmountFROM dbo.Vouchers2001 INNER JOINdbo.Transactions2001ON dbo.Vouchers2001.VoucherID =dbo.Transactions2001.VoucherID INNER JOINdbo.Ledgers ON dbo.Transactions2001.LedgerID =dbo.Ledgers.LedgerIDWHERE (dbo.Vouchers2001.VoucherTypeID = 1)GROUP BY dbo.Vouchers2001.VoucherID,dbo.Ledgers.LedgerName,dbo.Vouchers2001.VoucherDate,dbo.Vouchers2001.VoucherNo,dbo.Vouchers2001.VoucherTypeIDORDER BY dbo.Vouchers2001.VoucherID,dbo.Ledgers.LedgerName,dbo.Vouchers2001.VoucherDate,dbo.Vouchers2001.VoucherNoPlz help Out*** Sent via Developersdex http://www.developersdex.com ***

View 12 Replies View Related

Tough Query?

Aug 5, 2006

The following data set is building inspection visits. It consists ofmultiple visits (2+) made to the same building on the same day.I want to get a list of visits made to the same building on the same day,but by different employees, and for different visit codes (eg records 5-6,or 9-11)Here's the table=====================================CREATE TABLE VISITS(VISITID NUMBER(5,0) NOT NULL ,BLDGCODE VARCHAR2(10) NOT NULL ,VISITDATE DATE NOT NULL ,EMPID NUMBER(5,0) NOT NULL ,VISITCODE VARCHAR2(5) NOT NULL);ALTER TABLE VISITSADD CONSTRAINT PK_VISITS PRIMARY KEY(VISITID);CREATE UNIQUE INDEX UIDX_VISITS ON VISITS(BLDGCODE,VISITDATE,EMPID,VISITCODE);=====================================And here's the data:=====================================VISITID,BLDGCODE,VISITDATE,EMPID,VISITCODE1, BLDG1, 10/18/2005, 128, V62, BLDG1, 10/18/2005, 128, V93, BLDG2, 1/24/2006, 128, V84, BLDG2, 1/24/2006, 165, V225, BLDG3, 2/15/2006, 13, V146, BLDG3, 2/15/2006, 143, V87, BLDG4, 8/1/2006, 319, V98, BLDG4, 8/1/2006, 390, V99, BLDG4, 8/2/2006, 319, V910, BLDG4, 8/2/2006, 390, V911, BLDG4, 8/2/2006, 390, V812, BLDG5, 8/28/2006, 318, V1113, BLDG5, 8/28/2006, 376, V1114, BLDG5, 8/29/2006, 318, V1115, BLDG5, 8/29/2006, 334, V1116, BLDG5, 8/29/2006, 376, V1117, BLDG5, 8/30/2006, 318, V1118, BLDG5, 8/30/2006, 376, V1119, BLDG5, 8/30/2006, 334, V1120, BLDG5, 8/31/2006, 318, V1121, BLDG5, 8/31/2006, 376, V1122, BLDG5, 8/31/2006, 334, V1123, BLDG6, 10/11/2005, 323, V1924, BLDG6, 10/11/2005, 323, V2725, BLDG6, 11/8/2005, 323, V826, BLDG6, 11/8/2005, 323, V2727, BLDG7, 10/18/2005, 323, V328, BLDG7, 10/18/2005, 323, V2729, BLDG7, 11/14/2005, 14, V330, BLDG7, 11/14/2005, 323, V331, BLDG7, 11/14/2005, 143, V332, BLDG7, 12/15/2005, 143, V333, BLDG7, 12/15/2005, 323, V334, BLDG8, 3/8/2006, 15, V2335, BLDG8, 3/8/2006, 120, V2336, BLDG9, 5/22/2006, 25, V237, BLDG9, 5/22/2006, 391, V1438, BLDG10, 11/3/2005, 310, V639, BLDG10, 11/3/2005, 310, V840, BLDG10, 3/15/2006, 139, V2841, BLDG10, 3/15/2006, 310, V2842, BLDG10, 3/16/2006, 139, V2843, BLDG10, 3/16/2006, 310, V2844, BLDG11, 11/3/2005, 323, V2245, BLDG11, 11/3/2005, 323, V2746, BLDG12, 4/18/2006, 71, V247, BLDG12, 4/18/2006, 337, V1348, BLDG12, 4/19/2006, 71, V249, BLDG12, 4/19/2006, 337, V1350, BLDG13, 10/3/2005, 142, V2251, BLDG13, 10/3/2005, 142, V2752, BLDG14, 5/23/2006, 32, V2353, BLDG14, 5/23/2006, 139, V2354, BLDG14, 5/24/2006, 32, V2355, BLDG14, 5/24/2006, 139, V2356, BLDG15, 5/30/2006, 141, V1757, BLDG15, 5/30/2006, 141, V2258, BLDG16, 6/1/2006, 71, V1859, BLDG16, 6/1/2006, 336, V1860, BLDG16, 6/1/2006, 123, V1861, BLDG17, 2/21/2006, 34, V862, BLDG17, 2/21/2006, 34, V1963, BLDG18, 12/14/2005, 141, V764, BLDG18, 12/14/2005, 141, V1765, BLDG19, 10/18/2005, 320, V1466, BLDG19, 10/18/2005, 320, V1667, BLDG20, 3/6/2006, 141, V868, BLDG20, 3/6/2006, 141, V2269, BLDG21, 10/11/2005, 324, V670, BLDG21, 10/11/2005, 324, V771, BLDG22, 7/10/2006, 38, V2372, BLDG22, 7/10/2006, 252, V1173, BLDG22, 7/11/2006, 38, V2374, BLDG22, 7/11/2006, 252, V1175, BLDG22, 7/11/2006, 142, V2276, BLDG23, 11/10/2005, 308, V777, BLDG23, 11/10/2005, 308, V878, BLDG23, 5/11/2006, 308, V879, BLDG23, 5/11/2006, 391, V280, BLDG24, 3/23/2006, 143, V2481, BLDG24, 3/23/2006, 155, V282, BLDG24, 3/24/2006, 143, V2483, BLDG24, 3/24/2006, 155, V2584, BLDG25, 10/3/2005, 31, V1485, BLDG25, 10/3/2005, 31, V1986, BLDG26, 2/20/2006, 31, V1487, BLDG26, 2/20/2006, 31, V2288, BLDG27, 2/15/2006, 13, V1489, BLDG27, 2/15/2006, 143, V890, BLDG28, 10/12/2005, 141, V891, BLDG28, 10/12/2005, 141, V1792, BLDG29, 10/4/2005, 32, V2293, BLDG29, 10/4/2005, 310, V294, BLDG30, 9/12/2005, 53, V2395, BLDG30, 9/12/2005, 123, V2196, BLDG30, 9/12/2005, 141, V2397, BLDG30, 9/13/2005, 53, V2398, BLDG30, 9/13/2005, 141, V2399, BLDG30, 9/13/2005, 123, V21100, BLDG30, 9/14/2005, 53, V23101, BLDG30, 9/14/2005, 141, V23102, BLDG30, 9/14/2005, 123, V21103, BLDG31, 2/14/2006, 13, V14104, BLDG31, 2/14/2006, 143, V8105, BLDG32, 11/1/2005, 320, V3106, BLDG32, 11/1/2005, 320, V27107, BLDG33, 11/3/2005, 34, V7108, BLDG33, 11/3/2005, 34, V19109, BLDG34, 7/10/2006, 37, V23110, BLDG34, 7/10/2006, 62, V23111, BLDG34, 7/11/2006, 37, V23112, BLDG34, 7/11/2006, 62, V23113, BLDG34, 7/12/2006, 37, V23114, BLDG34, 7/12/2006, 62, V23115, BLDG35, 11/21/2005, 78, V27116, BLDG35, 11/21/2005, 334, V8117, BLDG36, 7/10/2006, 358, V1118, BLDG36, 7/10/2006, 358, V8119, BLDG37, 8/14/2006, 50, V14120, BLDG37, 8/14/2006, 71, V11121, BLDG37, 8/15/2006, 50, V14122, BLDG37, 8/15/2006, 71, V11123, BLDG38, 9/13/2005, 130, V6124, BLDG38, 9/13/2005, 130, V8125, BLDG39, 2/22/2006, 34, V8126, BLDG39, 2/22/2006, 34, V14127, BLDG40, 2/14/2006, 13, V14128, BLDG40, 2/14/2006, 143, V8129, BLDG41, 5/22/2006, 252, V17130, BLDG41, 5/22/2006, 326, V17131, BLDG41, 5/23/2006, 252, V17132, BLDG41, 5/23/2006, 326, V17133, BLDG42, 7/10/2006, 309, V2134, BLDG42, 7/10/2006, 318, V23135, BLDG42, 7/11/2006, 309, V2136, BLDG42, 7/11/2006, 318, V23137, BLDG42, 7/12/2006, 309, V2138, BLDG42, 7/12/2006, 318, V23139, BLDG43, 10/18/2005, 206, V8140, BLDG43, 10/18/2005, 206, V14141, BLDG44, 3/9/2006, 142, V24142, BLDG44, 3/9/2006, 233, V23143, BLDG44, 3/9/2006, 319, V24144, BLDG44, 3/10/2006, 142, V24145, BLDG44, 3/10/2006, 319, V24146, BLDG44, 3/10/2006, 233, V23147, BLDG45, 9/15/2005, 128, V6148, BLDG45, 9/15/2005, 128, V9149, BLDG46, 5/24/2006, 25, V2150, BLDG46, 5/24/2006, 391, V8151, BLDG47, 1/17/2006, 321, V6152, BLDG47, 1/17/2006, 321, V22153, BLDG48, 7/13/2006, 38, V18154, BLDG48, 7/13/2006, 318, V11155, BLDG49, 7/12/2006, 142, V23156, BLDG49, 7/12/2006, 263, V23157, BLDG50, 4/11/2006, 62, V24158, BLDG50, 4/11/2006, 142, V24159, BLDG50, 4/12/2006, 62, V24160, BLDG50, 4/12/2006, 142, V24161, BLDG51, 10/13/2005, 78, V13162, BLDG51, 10/13/2005, 325, V13163, BLDG52, 5/2/2006, 145, V9164, BLDG52, 5/2/2006, 390, V12165, BLDG52, 5/2/2006, 390, V9166, BLDG52, 5/3/2006, 145, V8167, BLDG52, 5/3/2006, 390, V9168, BLDG52, 5/3/2006, 390, V12169, BLDG53, 12/14/2005, 76, V9170, BLDG53, 12/14/2005, 322, V9171, BLDG53, 12/15/2005, 76, V9172, BLDG53, 12/15/2005, 322, V9173, BLDG53, 12/15/2005, 322, V22174, BLDG54, 9/6/2005, 323, V3175, BLDG54, 9/6/2005, 323, V27176, BLDG54, 12/13/2005, 323, V22177, BLDG54, 12/13/2005, 323, V27178, BLDG55, 9/6/2005, 129, V21179, BLDG55, 9/6/2005, 233, V23180, BLDG55, 9/7/2005, 38, V23181, BLDG55, 9/7/2005, 233, V23182, BLDG55, 9/7/2005, 142, V23183, BLDG55, 9/7/2005, 129, V21184, BLDG55, 9/8/2005, 38, V23185, BLDG55, 9/8/2005, 233, V23186, BLDG55, 9/8/2005, 142, V23187, BLDG55, 9/8/2005, 129, V21188, BLDG55, 9/9/2005, 129, V21189, BLDG55, 9/9/2005, 233, V23190, BLDG55, 6/20/2006, 142, V8191, BLDG55, 6/20/2006, 142, V29192, BLDG56, 6/28/2006, 131, V13193, BLDG56, 6/28/2006, 319, V13194, BLDG56, 6/29/2006, 131, V13195, BLDG56, 6/29/2006, 319, V13196, BLDG57, 11/8/2005, 320, V22197, BLDG57, 11/8/2005, 320, V27198, BLDG58, 1/25/2006, 13, V3199, BLDG58, 1/25/2006, 14, V13200, BLDG59, 11/29/2005, 233, V9201, BLDG59, 11/29/2005, 233, V14202, BLDG60, 2/8/2006, 323, V22203, BLDG60, 2/8/2006, 323, V27204, BLDG61, 1/17/2006, 166, V3205, BLDG61, 1/17/2006, 166, V22206, BLDG62, 9/27/2005, 320, V3207, BLDG62, 9/27/2005, 320, V22208, BLDG62, 2/21/2006, 115, V9209, BLDG62, 2/21/2006, 320, V9210, BLDG62, 2/22/2006, 115, V9211, BLDG62, 2/22/2006, 320, V9212, BLDG63, 11/14/2005, 87, V11213, BLDG63, 11/14/2005, 129, V27214, BLDG63, 11/14/2005, 323, V27215, BLDG63, 11/15/2005, 129, V11216, BLDG63, 11/15/2005, 143, V11217, BLDG63, 11/16/2005, 129, V11218, BLDG63, 11/16/2005, 143, V11219, BLDG63, 11/17/2005, 129, V11220, BLDG63, 11/17/2005, 143, V11221, BLDG63, 11/18/2005, 129, V27222, BLDG63, 11/18/2005, 143, V11223, BLDG64, 6/7/2006, 253, V2224, BLDG64, 6/7/2006, 391, V6225, BLDG65, 6/7/2006, 253, V2226, BLDG65, 6/7/2006, 391, V14227, BLDG66, 1/11/2006, 39, V25228, BLDG66, 1/11/2006, 141, V25229, BLDG66, 1/12/2006, 39, V25230, BLDG66, 1/12/2006, 141, V25231, BLDG66, 3/20/2006, 39, V23232, BLDG66, 3/20/2006, 76, V23233, BLDG66, 3/21/2006, 39, V23234, BLDG66, 3/21/2006, 115, V23235, BLDG66, 3/21/2006, 76, V23236, BLDG66, 3/22/2006, 39, V23237, BLDG66, 3/22/2006, 115, V23238, BLDG66, 3/22/2006, 76, V23239, BLDG67, 5/26/2006, 141, V7240, BLDG67, 5/26/2006, 141, V17241, BLDG68, 12/21/2005, 141, V8242, BLDG68, 12/21/2005, 141, V17243, BLDG69, 5/23/2006, 50, V3244, BLDG69, 5/23/2006, 50, V8245, BLDG70, 2/1/2006, 114, V17246, BLDG70, 2/1/2006, 114, V22247, BLDG71, 10/11/2005, 131, V8248, BLDG71, 10/11/2005, 334, V8249, BLDG71, 3/10/2006, 334, V8250, BLDG71, 3/10/2006, 334, V22251, BLDG72, 7/31/2006, 398, V2252, BLDG72, 7/31/2006, 398, V22253, BLDG73, 11/30/2005, 129, V23254, BLDG73, 11/30/2005, 326, V24255, BLDG74, 11/29/2005, 143, V22256, BLDG74, 11/29/2005, 143, V23257, BLDG75, 4/26/2006, 12, V18258, BLDG75, 4/26/2006, 17, V18259, BLDG76, 6/6/2006, 320, V14260, BLDG76, 6/6/2006, 320, V15261, BLDG77, 1/10/2006, 78, V2262, BLDG77, 1/10/2006, 325, V9263, BLDG77, 1/11/2006, 78, V2264, BLDG77, 1/11/2006, 325, V8265, BLDG77, 1/11/2006, 325, V6266, BLDG78, 5/17/2006, 141, V17267, BLDG78, 5/17/2006, 141, V22268, BLDG79, 9/13/2005, 37, V19269, BLDG79, 9/13/2005, 318, V19270, BLDG80, 12/20/2005, 34, V13271, BLDG80, 12/20/2005, 250, V13272, BLDG81, 4/19/2006, 25, V22273, BLDG81, 4/19/2006, 391, V2274, BLDG82, 5/3/2006, 108, V14275, BLDG82, 5/3/2006, 391, V2276, BLDG83, 6/19/2006, 36, V8277, BLDG83, 6/19/2006, 393, V8278, BLDG84, 2/13/2006, 13, V14279, BLDG84, 2/13/2006, 143, V8280, BLDG85, 5/8/2006, 308, V8281, BLDG85, 5/8/2006, 391, V2282, BLDG86, 4/25/2006, 322, V8283, BLDG86, 4/25/2006, 322, V22284, BLDG87, 7/14/2006, 322, V13285, BLDG87, 7/14/2006, 322, V15286, BLDG88, 2/2/2006, 322, V8287, BLDG88, 2/2/2006, 322, V22288, BLDG89, 4/13/2006, 390, V2289, BLDG89, 4/13/2006, 390, V8290, BLDG90, 10/12/2005, 131, V8291, BLDG90, 10/12/2005, 334, V8292, BLDG91, 6/26/2006, 131, V22293, BLDG91, 6/26/2006, 319, V22294, BLDG91, 6/27/2006, 131, V22295, BLDG91, 6/27/2006, 319, V22296, BLDG92, 3/1/2006, 39, V23297, BLDG92, 3/1/2006, 141, V23298, BLDG92, 3/2/2006, 39, V23299, BLDG92, 3/2/2006, 115, V23300, BLDG92, 3/2/2006, 141, V23301, BLDG92, 3/3/2006, 39, V23302, BLDG92, 3/3/2006, 141, V23303, BLDG92, 3/3/2006, 115, V23304, BLDG92, 7/20/2006, 115, V23305, BLDG92, 7/20/2006, 141, V25306, BLDG92, 7/21/2006, 115, V23307, BLDG92, 7/21/2006, 141, V25308, BLDG93, 5/8/2006, 78, V2309, BLDG93, 5/8/2006, 325, V9310, BLDG93, 5/9/2006, 78, V2311, BLDG93, 5/9/2006, 78, V9312, BLDG93, 5/9/2006, 325, V9313, BLDG94, 6/19/2006, 128, V9314, BLDG94, 6/19/2006, 358, V9315, BLDG94, 6/20/2006, 128, V9316, BLDG94, 6/20/2006, 358, V9317, BLDG95, 6/6/2006, 253, V2318, BLDG95, 6/6/2006, 391, V14319, BLDG96, 6/6/2006, 253, V2320, BLDG96, 6/6/2006, 391, V14321, BLDG97, 6/5/2006, 253, V7322, BLDG97, 6/5/2006, 391, V7323, BLDG98, 1/24/2006, 322, V9324, BLDG98, 1/24/2006, 322, V22325, BLDG99, 10/12/2005, 323, V7326, BLDG99, 10/12/2005, 323, V15327, BLDG100, 12/21/2005, 320, V14328, BLDG100, 12/21/2005, 320, V22329, BLDG100, 2/23/2006, 115, V9330, BLDG100, 2/23/2006, 320, V9331, BLDG100, 2/24/2006, 115, V9332, BLDG100, 2/24/2006, 320, V9333, BLDG101, 2/22/2006, 115, V9334, BLDG101, 2/22/2006, 320, V9335, BLDG101, 2/23/2006, 115, V9336, BLDG101, 2/23/2006, 320, V9337, BLDG102, 10/13/2005, 131, V8338, BLDG102, 10/13/2005, 334, V8339, BLDG103, 1/12/2006, 119, V7340, BLDG103, 1/12/2006, 119, V22341, BLDG104, 5/17/2006, 233, V23342, BLDG104, 5/17/2006, 243, V23343, BLDG104, 5/18/2006, 233, V23344, BLDG104, 5/18/2006, 243, V23345, BLDG105, 11/22/2005, 309, V6346, BLDG105, 11/22/2005, 309, V22347, BLDG106, 1/12/2006, 166, V8348, BLDG106, 1/12/2006, 166, V22349, BLDG107, 9/27/2005, 206, V7350, BLDG107, 9/27/2005, 206, V20351, BLDG108, 4/12/2006, 322, V14352, BLDG108, 4/12/2006, 322, V22353, BLDG109, 3/27/2006, 17, V11354, BLDG109, 3/27/2006, 358, V11355, BLDG109, 3/27/2006, 127, V11356, BLDG109, 3/27/2006, 142, V11357, BLDG109, 3/27/2006, 144, V11358, BLDG109, 3/27/2006, 318, V11359, BLDG109, 3/27/2006, 129, V11360, BLDG109, 3/28/2006, 17, V11361, BLDG109, 3/28/2006, 115, V11362, BLDG109, 3/28/2006, 358, V11363, BLDG109, 3/28/2006, 334, V11364, BLDG109, 3/28/2006, 323, V11365, BLDG109, 3/28/2006, 318, V11366, BLDG109, 3/28/2006, 144, V11367, BLDG109, 3/28/2006, 142, V11368, BLDG109, 3/28/2006, 129, V11369, BLDG109, 3/28/2006, 127, V11370, BLDG109, 3/29/2006, 17, V11371, BLDG109, 3/29/2006, 323, V11372, BLDG109, 3/29/2006, 358, V11373, BLDG109, 3/29/2006, 334, V11374, BLDG109, 3/29/2006, 318, V11375, BLDG109, 3/29/2006, 144, V11376, BLDG109, 3/29/2006, 142, V11377, BLDG109, 3/29/2006, 129, V11378, BLDG109, 3/29/2006, 127, V11379, BLDG109, 3/29/2006, 115, V11380, BLDG109, 3/30/2006, 17, V11381, BLDG109, 3/30/2006, 129, V11382, BLDG109, 3/30/2006, 358, V11383, BLDG109, 3/30/2006, 334, V11384, BLDG109, 3/30/2006, 323, V11385, BLDG109, 3/30/2006, 318, V11386, BLDG109, 3/30/2006, 144, V11387, BLDG109, 3/30/2006, 142, V11388, BLDG109, 3/30/2006, 127, V11389, BLDG109, 3/30/2006, 115, V11390, BLDG109, 3/31/2006, 17, V11391, BLDG109, 3/31/2006, 318, V11392, BLDG109, 3/31/2006, 358, V11393, BLDG109, 3/31/2006, 144, V11394, BLDG109, 3/31/2006, 142, V11395, BLDG109, 3/31/2006, 129, V11396, BLDG109, 3/31/2006, 127, V11397, BLDG109, 3/31/2006, 115, V11398, BLDG110, 11/7/2005, 320, V22399, BLDG110, 11/7/2005, 320, V27400, BLDG111, 10/12/2005, 146, V8401, BLDG111, 10/12/2005, 146, V22402, BLDG112, 2/14/2006, 141, V8403, BLDG112, 2/14/2006, 141, V22404, BLDG113, 2/15/2006, 145, V9405, BLDG113, 2/15/2006, 233, V9406, BLDG113, 2/16/2006, 145, V8407, BLDG113, 2/16/2006, 233, V9408, BLDG114, 1/26/2006, 310, V6409, BLDG114, 1/26/2006, 310, V8410, BLDG115, 5/5/2006, 36, V9411, BLDG115, 5/5/2006, 376, V9412, BLDG115, 7/5/2006, 36, V9413, BLDG115, 7/5/2006, 376, V9414, BLDG115, 7/6/2006, 36, V9415, BLDG115, 7/6/2006, 376, V9416, BLDG116, 4/12/2006, 34, V4417, BLDG116, 4/12/2006, 34, V13418, BLDG117, 12/27/2005, 323, V13419, BLDG117, 12/27/2005, 323, V27420, BLDG117, 2/17/2006, 14, V3421, BLDG117, 2/17/2006, 323, V3422, BLDG118, 1/31/2006, 308, V17423, BLDG118, 1/31/2006, 308, V22424, BLDG119, 5/9/2006, 308, V8425, BLDG119, 5/9/2006, 391, V2426, BLDG120, 11/8/2005, 233, V28427, BLDG120, 11/8/2005, 233, V29428, BLDG121, 12/15/2005, 141, V8429, BLDG121, 12/15/2005, 141, V17430, BLDG122, 5/23/2006, 25, V2431, BLDG122, 5/23/2006, 391, V22432, BLDG123, 4/20/2006, 25, V14433, BLDG123, 4/20/2006, 391, V2434, BLDG124, 4/17/2006, 25, V22435, BLDG124, 4/17/2006, 391, V2436, BLDG125, 4/18/2006, 25, V22437, BLDG125, 4/18/2006, 391, V2438, BLDG126, 10/18/2005, 13, V19439, BLDG126, 10/18/2005, 13, V22440, BLDG127, 5/10/2006, 308, V8441, BLDG127, 5/10/2006, 391, V2442, BLDG128, 1/10/2006, 78, V11443, BLDG128, 1/10/2006, 233, V23444, BLDG129, 11/8/2005, 78, V9445, BLDG129, 11/8/2005, 325, V9446, BLDG129, 11/9/2005, 78, V9447, BLDG129, 11/9/2005, 325, V9448, BLDG130, 10/18/2005, 325, V28449, BLDG130, 10/18/2005, 334, V9450, BLDG130, 11/9/2005, 78, V9451, BLDG130, 11/9/2005, 334, V9452, BLDG130, 11/9/2005, 325, V9453, BLDG130, 11/10/2005, 78, V9454, BLDG130, 11/10/2005, 325, V9455, BLDG130, 11/10/2005, 334, V6456, BLDG131, 5/30/2006, 50, V3457, BLDG131, 5/30/2006, 50, V8458, BLDG132, 5/24/2006, 31, V6459, BLDG132, 5/24/2006, 31, V22460, BLDG133, 2/7/2006, 114, V17461, BLDG133, 2/7/2006, 114, V22462, BLDG134, 6/21/2006, 62, V9463, BLDG134, 6/21/2006, 309, V28464, BLDG135, 5/10/2006, 78, V9465, BLDG135, 5/10/2006, 325, V9466, BLDG135, 5/11/2006, 78, V9467, BLDG135, 5/11/2006, 325, V9468, BLDG136, 12/15/2005, 129, V23469, BLDG136, 12/15/2005, 233, V23470, BLDG137, 5/17/2006, 129, V6471, BLDG137, 5/17/2006, 129, V8472, BLDG138, 3/8/2006, 336, V8473, BLDG138, 3/8/2006, 336, V22474, BLDG139, 3/20/2006, 129, V14475, BLDG139, 3/20/2006, 129, V19476, BLDG140, 3/22/2006, 52, V29477, BLDG140, 3/22/2006, 334, V29478, BLDG140, 3/23/2006, 52, V29479, BLDG140, 3/23/2006, 334, V29480, BLDG141, 1/4/2006, 78, V22481, BLDG141, 1/4/2006, 334, V22482, BLDG142, 9/7/2005, 52, V8483, BLDG142, 9/7/2005, 52, V10484, BLDG143, 5/4/2006, 78, V28485, BLDG143, 5/4/2006, 334, V29486, BLDG144, 5/11/2006, 50, V3487, BLDG144, 5/11/2006, 50, V8488, BLDG144, 5/12/2006, 50, V7489, BLDG144, 5/12/2006, 50, V8490, BLDG145, 10/4/2005, 119, V16491, BLDG145, 10/4/2005, 119, V19492, BLDG146, 6/6/2006, 34, V7493, BLDG146, 6/6/2006, 34, V8494, BLDG147, 9/14/2005, 36, V28495, BLDG147, 9/14/2005, 324, V5496, BLDG148, 9/14/2005, 322, V14497, BLDG148, 9/14/2005, 322, V15498, BLDG149, 6/20/2006, 123, V8499, BLDG149, 6/20/2006, 123, V22500, BLDG150, 5/16/2006, 12, V18501, BLDG150, 5/16/2006, 318, V18502, BLDG151, 4/17/2006, 62, V23503, BLDG151, 4/17/2006, 142, V23504, BLDG151, 4/17/2006, 318, V23505, BLDG151, 4/17/2006, 154, V2506, BLDG151, 4/18/2006, 62, V23507, BLDG151, 4/18/2006, 318, V23508, BLDG151, 4/18/2006, 142, V23509, BLDG151, 4/18/2006, 154, V2510, BLDG151, 4/19/2006, 62, V22511, BLDG151, 4/19/2006, 318, V23512, BLDG151, 4/19/2006, 154, V2513, BLDG151, 4/19/2006, 142, V23514, BLDG152, 10/20/2005, 320, V14515, BLDG152, 10/20/2005, 320, V15516, BLDG153, 7/11/2006, 334, V8517, BLDG153, 7/11/2006, 334, V9518, BLDG154, 2/1/2006, 53, V23519, BLDG154, 2/1/2006, 323, V23520, BLDG154, 2/2/2006, 53, V23521, BLDG154, 2/2/2006, 323, V23522, BLDG154, 2/3/2006, 53, V23523, BLDG154, 2/3/2006, 323, V27524, BLDG154, 2/3/2006, 323, V23525, BLDG154, 7/12/2006, 53, V24526, BLDG154, 7/12/2006, 139, V26527, BLDG154, 7/12/2006, 141, V25528, BLDG154, 7/13/2006, 53, V24529, BLDG154, 7/13/2006, 139, V26530, BLDG154, 7/13/2006, 141, V25531, BLDG154, 7/14/2006, 53, V24532, BLDG154, 7/14/2006, 141, V25533, BLDG154, 7/14/2006, 139, V26534, BLDG155, 9/14/2005, 323, V7535, BLDG155, 9/14/2005, 323, V27536, BLDG156, 9/12/2005, 165, V6537, BLDG156, 9/12/2005, 165, V8538, BLDG157, 10/27/2005, 32, V13539, BLDG157, 10/27/2005, 310, V13540, BLDG158, 11/2/2005, 320, V14541, BLDG158, 11/2/2005, 320, V15542, BLDG158, 2/20/2006, 115, V9543, BLDG158, 2/20/2006, 320, V9544, BLDG158, 2/21/2006, 115, V9545, BLDG158, 2/21/2006, 320, V9546, BLDG158, 4/26/2006, 320, V3547, BLDG158, 4/26/2006, 320, V17548, BLDG159, 3/14/2006, 336, V14549, BLDG159, 3/14/2006, 336, V22550, BLDG160, 3/15/2006, 336, V8551, BLDG160, 3/15/2006, 336, V14552, BLDG160, 8/24/2006, 336, V8553, BLDG160, 8/24/2006, 336, V27554, BLDG161, 1/5/2006, 253, V6555, BLDG161, 1/5/2006, 253, V17556, BLDG162, 3/13/2006, 141, V9557, BLDG162, 3/13/2006, 141, V22558, BLDG163, 6/29/2006, 78, V6559, BLDG163, 6/29/2006, 78, V9560, BLDG164, 12/12/2005, 76, V9561, BLDG164, 12/12/2005, 322, V9562, BLDG164, 12/13/2005, 76, V9563, BLDG164, 12/13/2005, 322, V22564, BLDG164, 12/13/2005, 322, V9565, BLDG165, 1/11/2006, 166, V8566, BLDG165, 1/11/2006, 166, V22567, BLDG166, 5/24/2006, 141, V17568, BLDG166, 5/24/2006, 141, V22569, BLDG167, 1/11/2006, 165, V8570, BLDG167, 1/11/2006, 165, V22571, BLDG168, 10/18/2005, 244, V14572, BLDG168, 10/18/2005, 309, V14573, BLDG169, 1/31/2006, 144, V8574, BLDG169, 1/31/2006, 144, V22575, BLDG170, 6/6/2006, 123, V18576, BLDG170, 6/6/2006, 383, V18577, BLDG171, 1/17/2006, 263, V6578, BLDG171, 1/17/2006, 263, V9579, BLDG172, 8/10/2006, 233, V9580, BLDG172, 8/10/2006, 376, V9581, BLDG172, 8/11/2006, 233, V9582, BLDG172, 8/11/2006, 376, V9583, BLDG173, 4/4/2006, 131, V13584, BLDG173, 4/4/2006, 144, V13585, BLDG174, 7/4/2006, 383, V4586, BLDG174, 7/4/2006, 383, V8=====================================Thanks

View 9 Replies View Related

Tough Problem, Need Help

Jul 20, 2005

I have a very strange database with a very strange problem.Consider 4 tables:Table1:----------------Table1ID INT PKTable2ID INT FKTable3ID INT FKOrderNo VARCHAR(50)Table2----------------Table2ID INT PKTable4ID INT FKTable3----------------Table3ID INT PKTable2ID INT FKTable4----------------Table4ID INT PKOrderTotal VARCHAR(50)With Data:Table1:------------1 1 NULL 900012 2 NULL 900023 NULL 1 900034 NULL 2 90004Table2:------------1 12 1Table3:------------1 12 2Table4:------------1 5002 1000Table1 can have either a Table2ID OR a Table3ID but not both.This is the query I'm attempting:---------------------------SELECT dbo.Table1.OrderNo, dbo.Table4.OrderTotalFROM dbo.Table1 LEFT OUTER JOINdbo.Table4 INNER JOINdbo.Table2 ON dbo.Table4.Table4ID =dbo.Table2.Table4ID INNER JOINdbo.Table3 ON dbo.Table2.Table2ID =dbo.Table3.Table3ID ON dbo.Table1.Table2ID = dbo.Table2.Table2ID ANDdbo.Table1.Table3ID = dbo.Table3.Table3IDWhich gives me:---------------------------90001 NULL90002 NULL90003 NULL90004 NULLWhen I really want:----------------------------90001 50090002 50090003 50090003 1000 (NOT 500)I don't know how to do this. Are any of you sql guru's up to thechallenge?Thanks in advance-Mattp.s. sql to recreate tables includedCREATE TABLE [dbo].[Table1] ([Table1ID] [int] IDENTITY (1, 1) NOT NULL ,[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Table2ID] [int] NULL ,[Table3ID] [int] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table2] ([Table2ID] [int] IDENTITY (1, 1) NOT NULL ,[Table4ID] [int] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table3] ([Table3ID] [int] IDENTITY (1, 1) NOT NULL ,[Table2ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table4] ([Table4ID] [int] IDENTITY (1, 1) NOT NULL ,[OrderTotal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOALTER TABLE [dbo].[Table1] WITH NOCHECK ADDCONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED([Table1ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table2] WITH NOCHECK ADDCONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED([Table2ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table3] WITH NOCHECK ADDCONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED([Table3ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table4] WITH NOCHECK ADDCONSTRAINT [PK_Table4] PRIMARY KEY CLUSTERED([Table4ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table1] ADDCONSTRAINT [FK_Table1_Table2] FOREIGN KEY([Table2ID]) REFERENCES [dbo].[Table2] ([Table2ID]),CONSTRAINT [FK_Table1_Table3] FOREIGN KEY([Table3ID]) REFERENCES [dbo].[Table3] ([Table3ID])GOALTER TABLE [dbo].[Table3] ADDCONSTRAINT [FK_Table3_Table2] FOREIGN KEY([Table3ID]) REFERENCES [dbo].[Table2] ([Table2ID])GO

View 6 Replies View Related

Please Help With This Tough SQL Query

Jul 20, 2005

I've been trying this one for 2-3 hours and can't figure it out. I'deappreciate any help or pointers in the right direction. Thanks.QueryI need the query to return me all the lottery names and results thathave the latest date in the database for that particular game and forthe state [AZ]. So the return data from the data below data would be:Result:--------------------------AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18AZ Atlantic PayDay 2004-08-05 15-51-59-75AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6Example Table "Lottery":----------------------------------------------------State|Game | Date | ResultsAZ Atlantic 6/49 2004-08-04 5-16-17-26-38-44 46AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18AZ Atlantic PayDay 2004-07-29 2-23-62-77AZ Atlantic PayDay 2004-08-05 15-51-59-75AZ Atlantic Tag 2004-08-04 5-8-9-1-2-3AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

View 3 Replies View Related

A Tough Situation To Get ID....

Apr 17, 2008

Hello All

I have a table called Tax Act

In that I have Tax Act ID. Which is supposed to be NOT NULL.

I have to create IDs by my self using some MAX and MAX+1 incrementing function.

Please guide me on how to

View 3 Replies View Related

SQL Server Timeouts

Mar 29, 2007

I am getting Timeout Errors quite often and cannot figure out why. I am using Enterprise Library 2.0 when accessing the database. It is not from any particular function or page either and when I check the database there may only be 2 or 3 connection from my app. Any ideas of what could be causing this? Below is my error and stack.

Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Stack:
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection() at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) at HM.Security.SecurityData.GetAllUsers(Int32 filter) at HM.Admin.Security.SecuritySearch.SetUpAutoFill() in f:InetpubWwwroothomemiSectionsactSearch.aspx.cs:line 74 at HM.Admin.Security.SecuritySearch.Page_Load(Object sender, EventArgs e) in f:InetpubWwwroothomemiSectionsactSearch.aspx.cs:line 26 at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

View 1 Replies View Related

ODBC TimeOuts

Apr 27, 1999

Hello all,
I am managaing an NT infrastructure that supports a web application. The web server is configured to use an ODBC System DSN to access data on an SQL server over a 100MB private segment. When users run a particular query through the web page, they get an ODBC "Timeout Expired" message.
Does anyone know a fix for this. I could not find anything that explicitly identified this problem in MS Support. The query runs fine from an Eneteprise Manager T-SQL window both on the server and remotely.

Thanks in advance,
Ed Molinari
Technical Architect
Eamerald Solutions

View 2 Replies View Related

Timeouts In SQL Server

Sep 5, 2001

A timeout issue is occurring resulting in following error.
This issue is sporadic and may be related to network.
This error occurs on the client: Microsoft OLE DB Provider for SQL Server error `80040e14`
OLE/DB provider returned message: Timeout expired
"conn_info".asp, line 444

View 1 Replies View Related

SQL Server Job Timeouts?

Jul 23, 2005

Subject: SQL server job timeouts?We have a job that uses WinHTTP inside a stored procedure. We haveanother SP wrapper that runs this for a couple hundred records.When we add this as a job using SQLAgent it times out half way. Runningit from SQL Query analyser and it completes to the end.Anyway to set the timeouts for jobs?

View 5 Replies View Related

Problem With Timeouts

Jul 20, 2005

I've had my SQL server database running for two years now without aproblem.However, just today one of the main tables started returning an error.The table is contained within a database called engineering. I backit up once a week and the file size is up to about 40 MB.The error returned when trying to return data from one table(DbLucent) is:"[Microsoft][ODBC SQL Server Driver]Timeout expired"I can open/query any of the other tables in the database. I can opendesign table for this table. But it won't return any query.I'm debating whether to restore the database from the last backup.Any suggestions would be appreciated. Being located reomotely, Irather not fly back to the city where the server is and work on itthere either.-David

View 6 Replies View Related

Tough SQL Server/ASP Problem

Jun 28, 2000

This one has me stumped!!!

I have a stored procedure that moves data from three tables to three other tables that are exactly like the first three. When I call the SP from the Query Analyzer, it works great and moves all the records over. When I call it from ASP, the SP inserts two identical records in each table. Has anyone seen this kind of behavior? The ASP code is not in a loop my the way. I placed a response.write after I executed the SP and it only printed to the screen once. SO I know that the SP is only being called once from ASP.
I don't have any loops within the SP either.

Thanks in advance

View 3 Replies View Related

Tough Query-Please Help!!! URGENT!!!

Nov 21, 2001

Hello,

I have a table with 3 cols--- id1,id2 and id3
Total no.of rows-- 18

id1id2id3
11
25
35
45
51
6100
7100
845
944
1045
11100
126
136
141
152
163
17111
18123

id1 is an identity column.
I have to populate third column which is id3 based on the data in id2.

I have to group similar values in id2 together
and insert 1,2,3 accordingly in id3.
For eg: we have three 100's in id2, so we should have values 1,2,3 for id3.
we have two 45's in id2, we should have values 1,2 for id3.

so final output should be:

id1id2id3
111
251
352
453
512
61001
71002
8451
9441
10452
111003
1261
1362
1413
1521
1631
171111
181231

Is it possible?
Please help!!!!!!!

Thanks,
cheryl

View 3 Replies View Related

This May Be A Tough One: Contains With Inflectional And NOT Logic...Can It Be Done?

Aug 10, 2007

Thank you in advance!

I know from other posts that this works:
CONTAINS(column, 'formsof(inflectional,sport) AND formsof(inflectional,award)'

But I have an advanced googlesque search that includes "Without the Words:". I want to include inflectionals in the list of words to be omitted. Can this be done?

So far I have not had any success with:
'NOT(formsof(inflectional,sport)) AND NOT(formsof(inflectional, award))'

Any help would be greatly appreciated!

View 1 Replies View Related

Some Tough SQL Server Queries

Oct 4, 2006

1) Given the name of an index how to find the columns associated with the particular index in SQL Server?

2) Given the name of a constraint, how to find whether it is enabled or disabled?

3) Is there any equivalent of the Oracle 'valid' state in SQL Server? e.g. In Oracle 'valid' state is defined for views, indexes. Are there equivalent concepts in SQL Server and if there are, how to find if a particular index/view is 'valid' or not?

View 2 Replies View Related

Linked Servers - Timeouts

Jun 24, 2004

I have a basic UPDATE that's something along the lines of:

UPDATE LocalTable SET LocalColumn = RemoteColumn
FROM LocalTable INNER JOIN server.catalog.dbo.RemoteTable ON (...)

After running in Query Analyzer for ten minutes, I get:

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.].

I checked the linked server settings in EnterpriseManager and both timeout settings are at 0 (no timeout). Any idea how I can remove whatever timeout is causing this?

Anyone know how remote join performance compares to moving remote data over via DTS, running an UPDATE, and dropping the data?

View 1 Replies View Related

Database Mail Timeouts

Feb 29, 2008

I'm having a problem with SQL Server 2005 not being able to send all of my messages. Occasionally when we batch a large number of messages together, we run into problems because the SMTP server can't process them fast enough, and the message doesn't send due to a timeout. I know I have increase the number of retries, but I'd rather just increase the timeout setting. Does anyone know where I might be able to do this at? The mail admins are also looking at modifying the rules in SMTP so that messages from these servers don't go through the vigor of SPAM and phishing detection. Hopefully that will speed it up, but I'd like to give it a little extra leniency on the SQL side as well. Any suggestions?

View 3 Replies View Related

Tables Seem Too Big - Timeouts Happening

Jul 23, 2005

I have two problems I need some help with.First, I've just inherited a system and am delving into a few timeoutproblems that are causing problems for the users.Now, if I do a simple select * from the table (which looks to be thecause of the problem at this stage) in QA, I get the results back inless than a second. If I open the table in EM it takes about 10. Isthere a difference in viewing the data this way ? I'm used to EM beingvirtually the same speed. There is only one row. Minor questionreally, just something I'd like to understand if there is adifference.CREATE TABLE [QUERY] ([QUERY_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[CAT_ID] [numeric](18, 0) NOT NULL ,[QUERY_DESCR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_ID] [int] NOT NULL ,[IND_EURO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULLCONSTRAINT [DF_QUERY_IND_EURO] DEFAULT ('N'),[IND_DGCOLUMNS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL CONSTRAINT [DF_QUERY_IND_DGCOLUMNS] DEFAULT ('N'),[NO_GROUPS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_GROUPS] DEFAULT(0),[NO_FIELDS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_FIELDS] DEFAULT(0),[NO_LINES] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_LINES] DEFAULT (0),CONSTRAINT [PK_QUERY] PRIMARY KEY CLUSTERED([QUERY_ID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_QUERY_QUERY_CATEGORY] FOREIGN KEY([CAT_ID]) REFERENCES [QUERY_CATEGORY] ([CAT_ID]) ON DELETE CASCADE ON UPDATE CASCADE) ON [PRIMARY]GOI don't think any re-indexing has been done on this (or the othertables in the db). I was wondering if constant adding/deleting rowscould cause the index to be massive and in need of a good clear out.Any pointers would be appreciated. From what I can tell, there wassome problems trying to get replication to work. I need to dig deeperto see if this is now correct.-------------------------Secondly, there is a another table in the same database.CREATE TABLE [FIELD_DATA] ([ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[DATA_ID] [numeric](18, 0) NOT NULL ,[FIELD_ID] [numeric](18, 0) NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,CONSTRAINT [PK_FIELDDATA] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GOIt holds approx 4 million rows. The rest of the tables have minimaldata and about the same amount (consider them the same if you will).Now, another 'copy' of this database is held elsewhere (differentclient data) and this holds 40 million rows. The difference is thatthe first DB is 4.5GB and the second 6.5GB (approx). Does this provemy theory that re-indexing would be a good idea ?ThanksRyan

View 3 Replies View Related

Timeouts - Disk Queues

Jul 20, 2005

HiGot a strange problem.For some reason our web client box times out occasionally. Maybe afew times a day.Nothing appears in the logs.What I do know is that the disk queue ramps up to <high>(think top ofthe graph), processor queue jumps up and the tps drops to 0 (naturallyenough!) along with reduced pagefile usage. Usually at this time thetps is between 20 and 300. Running a pair of mirrored 18gb scsi disksfor the whole server (yeah yeah) and a whopping 640mb memory. Oh anda single piii 1 gig. Sql2k standard vanilla, server2k.What server trace events is it worth my catching to try and get abetter Idea of what may be causing this? No major jobs (backups etcseem to be occuring at these times).Cheers

View 1 Replies View Related

Fill Stops /timeouts

Jul 20, 2005

Dear Group,I am tring to use a command that calls the server to fill an adapter, itnever seems to get to the adapter, command and the server either times outor does not respond. The timeout is set at 10 hours. I am using VisualStudio to acces MS SQL - Server.I think I have all the rights and permissions set correctly. Also, I haveused this command to fill other adapters and tables.Does anyone have a suggestion.Jeff Magouirk

View 1 Replies View Related