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?
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
I have tables [CustomerData] ([CD]) and [MembershipData] ([MD]).
[CD] has
ID - Primary key
FirstName
LastName
Birthdate
Etc
[MD] has
ID - Primary key
Customer - The ID of the customer from [CustomerData]
StartDate - Membership start date
EndDate - Membership end date
Each [CD] can have multiple [MD].
Memberships can be looked at in one of four ways. They are also prioritized (see next paragraph) in this order
Current: Membership started on or before today and ends on or after today.
Future: Membership starts after today.
Past: Membership ended before today and start and end dates do not fall on 1/1/1900
None: Membership starts and ends on 1/1/1900
I have created four Views that return all memberships for each type. I am trying to create a query that selects all [CD] and includes the highest matching membership for each [CD]
I have a working query that will return the highest priority [MD] that a [CD] has when I specify a [CD] in my WHERE.
That query looks like this. (CurrentMembership, FutureMembership, PastMembership and NoMembership are my Views)
IF (EXISTS(SELECT * FROM CurrentMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM CurrentMembership WHERE Customer = 452)
ELSE
IF (EXISTS(SELECT * FROM FutureMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM FutureMembership WHERE Customer = 452)
ELSE
IF (EXISTS(SELECT * FROM PastMembership WHERE Customer = 452))
(SELECT TOP 1 * FROM PastMembership WHERE Customer = 452)
ELSE
(SELECT TOP 1 * FROM NoMembership WHERE Customer = 452)
I have it working for one specified individual. I'm at a loss as to how to extend it to work for all individuals.
Any ideas?
TIA
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 RelatedI 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?
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...
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?
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.
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 RelatedI 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 RelatedThe 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 RelatedI 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 RelatedI'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
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
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!
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
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!
I currently have the following query:
Quote:
select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName,
count(f.memberID) as numMembers
FROM Members a
INNER JOIN groupLeaders b
ON a.memberID = b.memberID
Inner JOIN Chapters c
ON c.chapterID = b.chapterID
LEFT JOIN divisions d
ON d.divisionID = c.divisionID
Inner Join groupsOfEight e
ON e.groupLeaderID = b.groupLeaderID
Inner Join groupOfEightMembers f
ON f.groupOfEightID = e.groupOfEightID
Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName
Order By divisionName, numMembers
This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.
Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.
[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.
Here is the query I started to build before I ran into serious trouble:
Quote:
select a.divisionName, count('c.memberID') as numMembers
From Divisions a
Inner Join Chapters b
On b.divisionID = a.divisionID
Inner Join chapterMembers c
ON c.chapterID = b.chapterID
Left Join Members d
ON d.memberID = c.memberID
LEFT Join groupLeaders e
On e.memberID = d.memberID
Group By a.divisionName
This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...
Have I done an okay job of explaining the problem?
The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.
Thanks for any advice!
Zoop
Greetings All, I was hoping that someone might be able to help me withthe following issue:table ddl:create table exchange(exchangefrom varchar(6),exchangeto varchar(6),exchangecode varchar(6),datemfrom datetime,dateto datetime,exchangerate decimal(28,10))The data in this table under normal conditons will look like:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 01/31/2004 .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My task is to calculate the days delimeted by the start and end date ofthe period which is simple enough:select exchangefrom, exchangeto, exchangecode, datemfrom, dateto,datediff(d, datemfrom, dateto)from exchangeHowever due to circumstances beyond my control the dateto field maycontain a null instead of a valid end date!! YIKES:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 NULL .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My solution to correct the data is to populate the missing end datewith the (start date -1 day) of the next period. However, I am notsure how to do this with SQL? E.g) from the example directly above therow: USD GBP GL 01/01/2004 NULL needs to be updated to:USD GBP GL 01/01/2004 01/31/2004 and this can be done bylooking for the next period (USD GBP GL 02/01/200402/29/2004) that follows and subtracting from its start date 1 day (02/01/2004 - 1 day = 01/31/2004) and that will give me the appropriateend date.If anyone has any insight into solving this problem I would be verythankful.Regards, TFD.
View 7 Replies View RelatedI am running 2 versions of a correlated subquery. The two versiondiffer slightly in design but differ tremendously in performance....ifanyone can answer this, you would be awesome.The "bad" query attempts to build a result set using a correlatedsubquery. The part causing the error is that the correlated subqueryis part of a derived table (joining 3 tables). Trying to run the querytakes a long time and the more records in the source table, theperformance is exponentially worse. When i change the derived table toa fixed table, the query runs fast.I look at the Execution Plan in Query Analyzer and the majority of timeis taken by the Clustered Index Seek and by the Nested Loops/InnerJoin.************************************************** ************************************************** ******here is the "bad" query:************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 mi.id FROM messages miINNER JOIN identities i ON i.id=mi.identityidINNER JOIN licenses l on i.licenseid=l.idWHERE l.licenseKey = t1.licenseKey AND category = 'usage'ORDER BY mi.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******here is the "good" query************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 t2.id FROM temptable T2WHERE t2.licenseKey = t1.licenseKeyORDER BY t2.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******Thank you in advance
View 5 Replies View RelatedI want to trace just sql statements, ie insert delete and update
On d drive the file should be l dropped
and have a fixed size say 1 mg
How do i set up
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.
I'm developing a 2 year comparison rolling 12 month Sales report but am having problems with the query I'm using. Because I need to include every month (whether no sales or not) I have a calendar table named metaDates and each table gets outer joined as a result. (Forgive the long query) Please see below.
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) )AS GM,
'Current 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
UNION
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) ) AS GM,
'Previous 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
ORDER BY MetaDates.[Year], MetaDates.[Month]
That said the second outer join (Customer.country IN (@country)) never gets executed. It's really a simple concept. "Give me last 2 year sales by month (every previous and last month even if no sales) but only for the country the user is in"
Can someone help me out with this sql statement. Any help would be much appreciated.
I have a procedure I need to get the values out of..I am using outputs...I have no idea why it wont work......I need all values listed in the select part of procedure....
CREATE procedure dbo.Appt_Login_NET
(
@LoginName nvarchar(15),
@Password NvarChar(15),
@UserName nvarchar(15)Output,
@UserPassword nvarchar(15)Output,
@UserClinic nvarchar(3)Output,
@UserTester bit Output
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @LoginName
and
UserPassword = @Password
GO
my vb.net code to retrive this info is
Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmd As New SqlCommand
Dim parmuser As SqlParameter
Dim parmus As SqlParameter
Dim parmpass As SqlParameter
Dim parmtest As SqlParameter
Dim struser As String
Dim strpass As String
Dim strclinic As String
Dim strnames As String
Dim tester As String
strpass = txtPass.Value
struser = txtUser.Value
cmd = New SqlCommand("Appt_Login_NET", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@LoginName", struser)
cmd.Parameters.Add("@Password", strpass)
parmus = cmd.Parameters.Add("@UserName", SqlDbType.NVarChar)
parmus.Size = 15
parmus.Direction = ParameterDirection.Output
parmuser = cmd.Parameters.Add("@UserClinic", SqlDbType.NVarChar)
parmuser.Size = 3
parmuser.Direction = ParameterDirection.Output
parmpass = cmd.Parameters.Add("@UserPassword", SqlDbType.NVarChar)
parmpass.Size = 15
parmpass.Direction = ParameterDirection.Output
parmtest = cmd.Parameters.Add("@UserTester", SqlDbType.Bit)
parmtest.Size = 1
parmtest.Direction = ParameterDirection.Output
con.Open()
cmd.ExecuteNonQuery()
If Not IsDBNull(cmd.Parameters("@UserName").Value) Then
Label1.Text = cmd.Parameters("@UserName").Value()
Else
Label1.Text = "No Results Found"
End If
con.Close()
End Sub
Why does this always show as "DBNUll" I get nothing when I debug any of my parm variables.I searched the SQl Server and in Query analyzer instead of the output variables in the procedure being just outputs they are input/outputs...................What does it take to get this working??? Do I need a conversion datatype I would prefer I gain the values and store them in variables......
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies View RelatedHi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
In MS-ACCESS we can write and store queries and then deploy them in our applications. How can we do the same in SQL-SERVER 2000
Queries written in MS-ACCESS were converted into tables while I migrated the database from ACCESS to SQL SERVER.
I am trying to get the same results in SQL server with a query as it works in access
There are two tables:
TITLES
Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel
Fields in Table:
[TitleID]
,[TitleNum]
,[TitleState]
,[VehYR]
,[VehMake]
,[VehModel]
,[NewORUsed]
[code]...
How this can be ONE SQL Query?In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.I have never used a query as a table before in a select .
I'm new to the world of SQL and queries. There was an existing SQL server when I took the job and have been learning what I can as it comes up. My problem is that installed a new SQL server and moved everything to it. Everything seems to be working with the exception of the existing queries. I have changed the server in the ODBC Administrator but I still get the following error:
Connection Failed:
SQLState: 01000
SQL SErer Error: 10060 & 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
Connection Failed:
SQLState: 08001
SQL SErer Error: 17
[Microsoft][ODBC SQL Server Driver][SQL Server does not exist or access denied.
I have also checked to make sure that the server is listening for incoming sockets connections. (Port 1433) It is.
I don't know if there are files (DSN) that need too be moved to the new server or if there is another place to change the server the queries are pointed to. I hope someone can help me.
Thanks,
Jo
Is there any log that catches all querys in SQL Server 2000???Full problem (in slanish): http://foro.hackhispano.com/showthread.php?t=28980Thanks
View 2 Replies View RelatedIn one of our requirement, I want all the query details for the SQL query batch that got executed for the day. I know, we can get sql query from dm_ exec_ query_stats. But I want all sql query along with their session details ie. ExecutedDateTime, SessionId, UserID etc. I have tried using sys.dm_ exec_ sessions. But it contains only last executed query details for all the sessions. how to obtain all the session details for all the query executed for the day in the server.
View 7 Replies View RelatedI have a remote server with SQL server 2014 instance on it. There is nothing else running on the SQL Server box(dedicated SQL box). There is only one instance of SQL 2014 on the server. No other versions of SQL server are on the server.
Issue:
1. When I execute a query connecting to the SQL server instance through my local SSMS, the query executes in 30 secs.
2. When i connect to remote server through windows RDP session and execute the same query in the SSMS(on server), then query executes in 1 minute.
Hi there,
I am trying to link one sql server to other sql server(version 7.0). I was able to link server1 to server2 by creating an odbc source and am able to see the tablenames when i click on the linked server tables.
My problem is..when i am trying to query on these tables its giving me error saying "OLE DB provider 'MSDASQL' does not contain table xxxxxx"
i am using select * from servername.tablename.dbo.tablename.
Any help on this will be appreciated.
Ravi