IN Clause With 32000 Values Shuts Down SQL Server Service
Sep 21, 2007
Hello
SQL Server 2000 Enterprise, 2 clustered nodes on Windows Server 2003
A web application ran a query based on the selection by the end user-> dynamically generated query with IN clause including + 32000 values
-> SQL Server Services shut down
Found this:
http://support.microsoft.com/kb/288095
Stack overflow occurs when you run a query that contains a large number of arguments inside an IN or a NOT IN clause in SQL Server
Is there a way to secure SQL Server from queries like that?
Many thanks!
Worf
View 3 Replies
ADVERTISEMENT
Oct 26, 2015
I'm running SQL 2012 on a Win 2012 VM and the SQL server shuts down automatically after starting the SQL Server & SQL Server Agent Jobs.
View 9 Replies
View Related
Feb 23, 2005
Has anyone ever not been able to stop a server side trace?
When I try, SQL Service shuts down. No errors are logged. I just get a general network error - connection broken.
sp_trace_setstatus 1,0
Thanks - Rick
View 2 Replies
View Related
Apr 26, 2006
I am experiencing an issue with the MSSQLSERVER shutting down automically at 6:00 every 3 days. I do have 2 jobs that run at this time, but they run everyday. We did not install anything additional on the server and we are running SP3a. This just started on April 15, 2006. The Windows Event log displays the following message:
4/21/20066:00:08 PMMSSQLSERVERError(2)17052N/A<server name>The MSSQLSERVER service terminated unexpectedly.
The service stops and does not restart. It required me to restart the service manually.
Any help would be appreciated.
View 4 Replies
View Related
Oct 25, 1999
Hello!
Twice our sql server has shut down by it self. The error log did not say
anything special the first time. This second time it had run out of locks and
after that it just shut down.
Any suggestions what it can depend on? Any suggestion except reinstall?
Ann-Sofie
View 1 Replies
View Related
Mar 27, 2000
Hello. I have a problem with regards to MS SQL Server 7.
I was creating an index on a 7 million row table and I executed the following TSQL command.
create index idx_accno on ccaccount(accno)
go
After 1 minute the following error was displayed and SQL Server was shut down automatically.
[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionCheckForData (PeekNamedPipe()).
[Microsoft][ODBC SQL Server Driver][Named Pipes]Connection broken.
Connection Broken
All the relevant settings on the server (server connections) seems to be ok. Any help will be greatly appreciated.
Winston
View 3 Replies
View Related
Aug 3, 2007
First of all I€™m a VB beginner, real beginner, like today is my first time hahaha
My problem is that I don€™t know how to put my question into VB code. Here goes.
I€™m working in Visual Studio and Reporting Services with a chart. I have a chart that shows the amount of trips an opportunity has in my CRM system. The opportunity goes from Open to Won €¦ or lost but I don€™t want to see those.
What I want to do in this chart is to separate all the Open Opportunities from the Won ones. I want to use a Stacked Column chart so Open Opportunities is on the upper part and Won is on the lower part of the column of course.
I can get the trips data from the CRM database, but I don€™t know how to put it in VB code €œSum the trips where Opportunity=Won€? for one series and for the other €œSum the trips where Opportunity=Open€?. In the database I have fields like statecodename='Won'. So far I have in the Data Values, were I write my expression:
Opportunity Open Trips €¦ =Sum(Fields!trips.Value)
And the same for Opportunities Won Trips €¦ but that isn€™t right of course.
So how do I say €œ=Sum(Fields!trips.Value) €¦ Where statecodename='Won'€??
View 6 Replies
View Related
Oct 14, 2007
Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number.
I have the following tables
tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing
bid_id Uci_id
123 1000
123 2000
123 1050
tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's.
Uci_ID Sub_ID
1000 456
1000 2345
2000 456
1050 2345
2000 2345
This is the statement I am using to return the Uci's from the Bid table with bid_id of 123. For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list.
SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause.
Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.
View 4 Replies
View Related
Jul 26, 2006
how can i use multiple values in IN CLAUSE in a SQL query, that too when the number of values are changing at runtime.
complete SQL code required...............
I have following picture in mind but the values(in IN clause) are changing at Runtime
DECLARE @groups TABLE (group_id int)
SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @groups)
View 4 Replies
View Related
Apr 18, 2007
Running SQL Server 2005 on Windows Server 2003 Box.
When I run a report using SQL Server Reporting Services from the \server
eports page, it previews fine. However on multiple pc's when you click the print button, the print dialog comes up allows you to select a printer and when you click ok a printing dialog flashes and it reboots the client pc. The report never prints.
Any Ideas?
Thanks,
Jeremy
View 4 Replies
View Related
May 1, 2007
I am having a weird trouble with BI Studio. Whenever I start a integration services project the BI Studio just closes automatically. any idea whats going on here? is this a bug? I am already on sp2.
View 1 Replies
View Related
Mar 2, 2006
Greetings,
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:
CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN
DECLARE TABLE @t1 (account INT)
INSERT INTO @t1 VALUES (100)
INSERT INTO @t1 VALUES (101)
INSERT INTO @t1 VALUES (102)
INSERT INTO @t1 VALUES (407)
RETURN @t1
END
Then I could call this function by doing something such as:
SELECT *
FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN
INSERT INTO @t1 VALUES (100)
RETURN
END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
Any help is very much appreciated.
- Jeff
View 3 Replies
View Related
Sep 28, 2006
Hello everyone. This is my first post here, so be gentle =)
I need to construct a query, that would return roughly 6000 rows of data.
There are some conditions, or joins, that I can't figure out. Maybe you could help me?
This is the first one.
Invoice.ID-DocParty.DocumentID -> DocParty.OtherID-Party.ID -> Party.IDNumber
This can be achieved with inner join, no problem. Pretty simple.
However, there's a catch =)
DocParty.Role can have four different values in the 'where' clause. Is there a
way to fetch all of these four values without returning four duplicates with
only one field differing?
There are multiple fields in the query that are to be fetched in similar ways. Therefore,
using a IN('value1','value2','value3','value4') would increase the number of selected rows
a lot.
In addition, there is another type of condition that needs to be fullfilled.
Invoice.Type1Account-Account.ID -> Account.Number
Invoice.Type2Account-Account.ID -> Account.Number
Basically, there two fields in the 'main' table that are joined to the same field in another table
with different conditions. Can this be fetched with the same row as all the other data without duplicates?
Should I use a view somehow? How can I construct a view with these complex conditions if I can't
construct an SQL query, that would return no duplicates (pseudo-du
View 13 Replies
View Related
Apr 7, 2008
hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D
View 4 Replies
View Related
Jun 25, 2004
how does one specify multiple values for a single column in a where clause?
example:
SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')
if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:
View 2 Replies
View Related
Jul 19, 2006
Hi all
My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed.
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A
What I did try:
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A OR TableA.A IS NULL
(but It generates redundant values from TableB)
I need to show all values even that value from Tablea is null
Thank a lot for any help
View 1 Replies
View Related
Jan 11, 2007
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<SelectParameters>
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
</SelectParameters>
</asp:SqlDataSource>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Else
Label1.Text = Item.Value + ","
End If
End If
Next
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here? Thanks!
View 4 Replies
View Related
Mar 17, 2008
I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus). Then I want to use these dates as the basis for a WHERE clause like:<some sql...> WHERE start_date BETWEEN 'FromDateTextBox.Text' AND 'ToDateTextBox.Text' (Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression). 1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?2. What's a decent way to convert the strings from the textboxes to the required format?3. How can I avoid an SQL injection attack?
View 4 Replies
View Related
Dec 14, 1999
Hi!
Has anyone experienced this problem?
Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values.
For example, the following query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,2,3,11,19)
go
returns a different result than this query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,3,11,2,19)
go
The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest:
AND columnA IN (19,11,3,2,1)
Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments...
Thanks!
View 2 Replies
View Related
Jun 11, 2004
I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:
CREATE PROC sp_test @in_company_code nvarchar(1024)
AS
select company_code, name, description
from member_company
where company_code in (@in_company_code)
However, I tried the following :
exec sp_test 'abc', 'rrd', 'bbc'
Procedure or function sp_test has too many arguments specified.
and SQLServer doesn't like it.
Did I specify this stored procedure correct?
If so, how can I can pass multiple values to the stored procedure then to the sql statement?
If not, is it possible to specify a stored procedure like this?
Thanks!
View 2 Replies
View Related
Jun 12, 2015
I am try to use a variable containing comma separated values in the IN clause.
View 10 Replies
View Related
Dec 28, 2006
Hi,
I am using a SQL back end to dynamically populate an asp.net report/page.
As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable.
My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query.
Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause?
Any help gratefully appreciated.
Thanks.
John
View 2 Replies
View Related
Nov 30, 2015
I have created a ssrs report which connects to vertica database through odbc connection. When I try to pass parameter value through parameter (e.g.: column name IN (@parameter) ) then getting error message in query designer prompting "Error in list of values in IN clause. Unable to parse query text. ". Using sql server 2012 , visual studio 2010 version and HP Vertica 7.1 .Â
View 6 Replies
View Related
Sep 10, 2015
Table : incident
----------------
incident_id usr_id item_id Inc_Date
10059926 191 61006 8-22-2015
10054444 222 3232 6-7-2015
Table: act_reg
--------------
act_reg_id act_type_id incident_id usr_id act_type_sc
454244 1 10059926 191 ASSIGN
471938 115 10059926 191 TRAVEL TIME
473379 40 10059926 191 FOLLOW UP
477652 115 10059926 191 TRAVEL TIME
489091 504 10059926 191 ADD_ATTCHMNTS
477653 504 10054444 222 ADD_ATTCHMNTSParameter: @attach (value=1, Label=Yes & Value=0, Label=No)
Result (While I am selecting 'Yes' in dropdown)
----------------------------------------------
incident_id usr_id item_id
10059926 191 61006
10054444 222 3232
SELECT incident.incident_id,incident.usr_id,incident.item_id
FROM incident
where exists (How i can write query here to check the act_type_sc=ADD_ATTCHMNTS is exists)
View 7 Replies
View Related
Apr 14, 2008
I understand that Multi-Select Parameters are converted behind the scenes to an In Clause when a report is executed. The problem that I have is that my multi-select string parameter is turned into an in claused filled with nvarchar/unicode expressions like:
Where columnName in (N'Value1', N'Value2', N'Value3'...)
This nvarchar / unicode expression takes what is already a fairly slow-performing construct and just drives it into the ground. When I capture my query with Profiler (so I can see the In Clause that is being built), I can run it in Management Studio and see the execution plan. Using N'Values' instead of just 'Value1', 'Value2','Value3' causes the query performance to drop from 40 seconds to two minutes and 40 seconds. It's horrible. How can I make it stop!!!?
Is there any way to force the query-rewriting process in Reporting Services to just use plain-old, varchar text values instead of forcing each value in the list to be converted on the fly to an Nvarchar value like this? The column from which I am pulling values for the parameter and the column that I am filtering are both just plain varchar.
Thanks,
TC
View 3 Replies
View Related
Jul 12, 2007
I have an almost virgin install of SQLExpres running on a WIN2K Pro system.
Have been able to create and connect db to Access 2000 without problem.
Now I wish to extend to remote connections. Using Surface Area Configuration tool, I changed Remote Connections to Local and Remote.
Whenever this setting contains TCP/IP and I try to restart the service I receive the following errors:
System Log:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 10013.
Application Log:
Server TCP provider failed to listen on [ 'any' <ipv4> 0]. Tcp port is already in use.
TDSSNIClient initialization failed with error 0x271d, status code 0xa.
TDSSNIClient initialization failed with error 0x271d, status code 0x1.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
If I change back to Local Connections Only or Local and Remote using named pipes only, the service starts up again without a problem.
After five days of investigating, researching, reinstalling and waiting I have to ask for help.
Any suggestions or leads?
Thank You in advance.
View 14 Replies
View Related
May 18, 2007
Hello! I have the following problem. I developed CLR Stored Procedure "StartNotification" and deploy it on db. This sp calls external web service. Furthermore, this sp is called according with SQL Server Agent Job's schedule. On my PC SQL Server works under Local System account and this web service is called correctly (Executed as user: NT AUTHORITYSYSTEM). But on ther other server the following exception is raised during job running:
Date 17.04.2007 16:42:10
Log Job History (FailureNotificationJob)
Step ID 1
Server MSK-CDBPO-01
Job Name FailureNotificationJob
Step Name MainStep
Duration 00:00:00
Sql Severity 16
Sql Message ID 6522
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: CORPmssqlserver.
A .NET Framework error occurred during execution
of user defined routine or aggregate 'StartNotification':
System.Security.SecurityException: Request for the permission of type
'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net. The step failed.
What is the reason of this behaviour? Unfortunately I do not have direct access to this server.
I have the following guesses:
1) CORPmssqlserver may have not enough permissions to call web service
2) Something wrong with SQL Server account's permissions
2) Something wrong with SQL Server Agent account's permissions
I will take the will for the deed. Thanks.
View 1 Replies
View Related
Aug 19, 2015
If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)â€
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.
View 4 Replies
View Related
Jul 30, 2007
Hi all,
I do understand that it is highly recomended to have aserprate user (perfered a domain user account) for each of the SQL Server service and SQL Agent service.
What is the reason behind that? (Someone told me to not run the service with an account that has a powerul privilegs! - I don't undrstanmd this point can you explain it please?)
What is the diffrent between: 1- Local System account 2 -Network Service account
Thanks in advanced!
CS4Ever
View 4 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
Nov 4, 2015
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
View 13 Replies
View Related
Jul 23, 2005
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
View 3 Replies
View Related
Oct 25, 2007
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.
When on SQL Server 2000 the following statement ran without issue:
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
GROUP BY TrackID
HAVING MAX(LegNum) = 1 AND
TrackID + 'x1' IN
(
SELECT
dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
WHERE TrackID + 'x1' IN
(
SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
GROUP BY TrackID
HAVING MAX(LegNum) = 1
)
View 3 Replies
View Related
