Strange Results Using IN
Mar 2, 2004
I received some strange results after typing the wrong column in a subselect using IN. I've been puzzling over it a while now and can't quite figure out why it is doing what it is.
Here's an example of what is happening.
use northwind
go
select * from Customers where CustomerID in
( select CustomerID from Employees where 1 = 0 )
Now, there is no customerid on the Employees table, so running the subquery alone will produce an error. However when the full query above is run, all rows in the customer table are returned.
It was my understanding that the inner querry is evaluated first, and the results used by the outer query. I'm having a hard time seeing how this query is going anywhere.
Any ideas?
View 13 Replies
ADVERTISEMENT
Mar 14, 2008
Hi there,I'm a little bit confused here. I the TOP 1 function with ORDER BY DESC to get the last id in my table but it doesn't seems to work.Here's an example:SELECT TOP 1 ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID DESCSELECT ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID ASCSo basically the first SELECT should return the last id value of the second query but it doesn't.The first query gives me that: 60721And the second one gives me that:60680606816068360684606856068660718607196072060721610506112261124So as you can see my TOP 1 ID ORDER BY ID DESC should gave me this result: 61124Am I missing something or... ?Please help me this is aleready in function so I have to fix it ASAP.Thank you, Regards,OR-THO
View 5 Replies
View Related
Jul 10, 2006
Hi all,Using SQL Server 2000, SP4.I have a table of street names (Rua) whose ids (cod_rua) are foreignkeys into a consumer table (Consumidor). It turns out that the "Rua"table has many unused records which I'd like to wipe out. For instance,there are some 2800 unused records in the "Rua" table, and only some200 records actually being used by the "Consumidor" table (which,itself, has some 5000 records).Attempting to find the unused records, I issued the following query:a)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM CONSUMIDOR)To my surprise, the query came out empty. But the following queryshowed the 200 or so records which *are* being used:b)SELECT COD_RUA FROM RUAWHERE COD_RUA IN (SELECT COD_RUA FROM CONSUMIDOR)I've found two solutions for the query to list the records *notexisting* in the Consumidor table:c)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM CONSUMIDORWHERE COD_RUA IS NOT NULL)d)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM RUAWHERE COD_RUA IN (SELECT COD_RUA FROM CONSUMIDOR))I know that there are many other possible solutions to the query(including left joins), but what I don't understand is why the query a)failed.Can some of you, oh mighty gurus, enlighten me?For the record, here's how both tables are (partially) declared:CREATE TABLE Rua (Cod_Rua int NOT NULL ,Rua varchar (35) NULL ,-- ...-- other unrelated fields-- ...CONSTRAINT Pk_CodRuaPRIMARY KEY (Cod_Rua))CREATE TABLE Consumidor (Cod_Consumidor int NOT NULL ,Cod_Rua int NULL ,-- ...-- other unrelated fields-- ...CONSTRAINT Pk_CodConsumidorPRIMARY KEY(Cod_Consumidor) ,CONSTRAINT Fk_CodRua_ConsumidorFOREIGN KEY (Cod_Rua)REFERENCES Rua (Cod_Rua))Regards,Branco Medeiros
View 3 Replies
View Related
Jul 20, 2005
Bit of an obscure one here, so please bear with me. I have two copiesof a database which should be identical. Both have a complex viewwhich is identical. I can open the views and the data is as expectedand match. I can query it in several ways as detailed below. The 5thversion of the simple query below based on the second copy of the viewfails, but works under the first copy./*1 Statement below works*/SELECT *FROM AgentHierarchyWHERE AdviserId = 6069819/*2 Statement below works*/SELECT *, AH.AdviserLastName, AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819/*3 Statement below works*/SELECT *, AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819/*4 Statement below works*/SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AH/*5 Statement below fails*/SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819The error I get is to do with conversion of data within the view. It'sa little complex, but the view works fine. It looks to me like when Irun the 5th statement above, it re-runs the view and then finds anerror.So, I took the complex view and ran that with the data output into atemporary table with the queries above run against that, and it worksfine. The problem is that the statement I need is based around the 5thone above (part of an update statement).I'm struggling to understand why some of the queries above work andone doesn't. If you look at 3 and 5 I'd expect them both to fail. Ifit failed consistently I could get further into it.The problem is that it's a little difficult to get the view itselfchanged as it was supplied by a third party, but if it hasn't changedand the data hasn't changed then it's got to be something else causingthe problem.Anyway, as I said, it's a bit obscure, but if this sounds familiar I'dbe interested in your opinion.Thanks in advance.
View 4 Replies
View Related
Mar 6, 2008
Hi
I'm getting strange results when converting a number to hours and minutes.
The following section of code....
AbsentMinutes,
CONVERT(varchar(5), DATEADD([minute], [AbsentMinutes], 0), 108) AS AbsentHours,
[Term 1 Minutes],
CONVERT(varchar(5), DATEADD([minute], [Term 1 Minutes], 0), 108) AS Term1Hours,
......is yielding the following results.
AbsentMins 6480 AbsentHours 12:00
Term 1 Mins 21540 Term 1 Hours 23:00
Obviously thats not right but I cant see where.
Can anyone advise please? This has worked for me previously on other data.
View 5 Replies
View Related
Nov 18, 2006
Hi,I have written a stored procedure which includes a DATEPART command, i.e.DATEPART(weekday, <date>)The result when ran from SQL Query Analyser is as expected . i.e. Sundayreturns 1, Monday 2, etcWhen the same proc is called from within the Access 2000 project Sundayis returned as 7, Saturday as 6 instead of 1 and 7 respectively.Basically the same stored proc returns different data depending on fromwhere it has been called.This is causing some issues obviously as the resulting tables andreports are showing incorrect data when presented in Access 2000.Has anyone else experienced this before or have any idea what may cause it.Regards,PB
View 1 Replies
View Related
Jul 19, 2006
Hi everyone,
I€™m suffering a queer behaviour when I use BIDS. Concretely, when I open a dtsx from my project (it has 10 packages) many times Sequence Container and Data Flow tasks are invisible. I mean, its lines are not visible at all whereas its titles are. I mean, what you see is just a white box€¦
Then, I€™m gonna Data Flow layer and I have to do double-clik over the tasks and are visible but on Control Flow I don€™t see how to solve.
Curiously in our development and production server such behaviour doesn€™t happen (we are accessing by mean Terminal Server from our workstations)
How odd!. Everything is fine except this.
I want to remark you that such project has been copied from the server, this is, these packages are been built on the server
Thanks for your thougts or ideas,
View 5 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Mar 25, 2015
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
View 4 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Jun 18, 2008
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
View 2 Replies
View Related
Oct 19, 2007
Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
View 3 Replies
View Related
Jan 30, 2008
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies
View Related
Mar 6, 2007
I have a datasource where i assign the control Parameter value depending on whether theres a value in the querystring or a gridview selectedvalue is indeed selected.When i do this using a querystring it works fine, however, when i do it using gridView_selectedIndexChanged it doesn't work.Here's the code <asp:SqlDataSource ID="jobDetailsDS" runat="server" ConnectionString="<%$ ConnectionStrings:promanConn %>" SelectCommand="SELECT tblJobs.intJobId, tblJobs.intJobStaffId, tblJobs.intJobUserId, tblJobs.intJobCategoryId, tblJobs.strJobTitle, tblJobs.strJobDesc, tblJobs.strJobNotes, tblJobs.dateJobLogged, tblJobs.dateJobDeadline, tblJobs.dateJobCompleted, tblJobs.intJobPriorityId, tblJobs.strJobSolution, tblJobs.boolJobCompleted, tblPriority.priorityName, tblPriority.id, tblPriority.priorityOrder, tblStaff.staffName, tblStaff.id AS staffID, tblProblemCategoriesLookup.strProblemCatName, tblProblemCategoriesLookup.intProblemCatId, tblStaff_1.id AS Expr1, tblStaff_1.staffName AS UserName FROM tblJobs INNER JOIN tblPriority ON tblJobs.intJobPriorityId = tblPriority.id INNER JOIN tblStaff ON tblJobs.intJobStaffId = tblStaff.id INNER JOIN tblProblemCategoriesLookup ON tblJobs.intJobCategoryId = tblProblemCategoriesLookup.intProblemCatId INNER JOIN tblStaff AS tblStaff_1 ON tblJobs.intJobUserId = tblStaff_1.id WHERE (tblJobs.intJobId = @intJobId)" InsertCommand="INSERT INTO tblJobs(intJobStaffId, intJobUserId, intJobCategoryId, strJobTitle, strJobDesc, strJobNotes, dateJobLogged, dateJobDeadline, intJobPriorityId, boolJobCompleted) VALUES (@intJobStaffId, 56 , 39, @strJobTitle, @strJobDesc, @strJobNotes,{ fn NOW() }, { fn NOW() }, @intJobPriorityId, 0); "> <SelectParameters> <asp:Parameter Name="intJobId" Type="int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="intJobStaffId" Type="Int32" /> <asp:Parameter Name="strJobTitle" Type="String" /> <asp:Parameter Name="strJobDesc" Type="String" /> <asp:Parameter Name="strJobNotes" Type="String" /> <asp:Parameter Name="intJobPriorityId" Type="Int32" /> <asp:Parameter Name="intJobUserId" Type="Int32" /> </InsertParameters> </asp:SqlDataSource>Code Behind ElseIf dest > 0 Then jobDetailsDS.SelectParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") notesDS.SelectParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") notesDS.InsertParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") gvActiveJobs.Visible = "False" End If End Sub Protected Sub gvActiveJobs_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvActiveJobs.SelectedIndexChanged gvActiveJobs.Visible = "False" jobDetailsDS.SelectParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue notesDS.SelectParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue notesDS.InsertParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue End Sub I also debugged and checked the value of gvActiveJobs.SelectedValue and it is what i was expecting but its still no good.Can anyone help?ThanksMatt
View 2 Replies
View Related
Nov 19, 2001
I moved a table from one file group to another file group. After moving the table the row count show 0 but when I open the table it’s returning all the rows. Is something wrong with the table? Do I have created a cluster index and drop on the table?
View 1 Replies
View Related
Aug 2, 2006
Hi,
I have table in my db thats used as a temporary table. At the end of the day it contains thousands of records which get summarized then all are deleted. I recently moved the application over to a new server with pretty much the same hardware config and noticed a big performance hit when running queries on this temp table (455 milliseconds as opposed to 1) In short, the fix was dropping the table and re-creating it.
Anybody know how this could be? I know that even if you delete records from a table, the table still seems to retain its physical size on the hard drive, could this have something to do with it?
Thanks
View 3 Replies
View Related
May 4, 2004
CREATE TABLE #users(users varchar(50),pasw varchar(50),title varchar(50))
insert into #users select 'test','abcdef','ceo'
SELECT * FROM #users
alter table #users alter column pasw nvarchar(50)
update #users set pasw=pwdencrypt(pasw)
SELECT * FROM #users
What happens to the TITLE column?
I had a table like the above with users and passwords in the Production DB. The password column had VARCHAR type. I changed it to NVARCHAR and encrypted the passwords. When i executed the SELECT *, the title column appeared like this. :eek:
If i query the table by column names instead of * i see the correct values. I couldn't understand what behaviour is it.
Howdy!
View 7 Replies
View Related
May 22, 2008
Hi Experts,
Am able to restart my local sql server using a login which dont have a sysadmin privileage.This login only have access to a particular database .Its only having datareader and datawriter as DB roles but still am able to restart the server.Please help
TIA
RKNAIR
View 5 Replies
View Related
Aug 3, 2006
I am using c# and ASP.NET 2 and I am getting a very strange error. I
have a field called CompanyID in SQL Server 2005, and it allows null
values in it. When this CompanyID is not NULL, ie for example it is
202, then when i press the update button it allows me to update
perfectly in the database. However if the CompanyID is NULL,
and I try to update the companyID, to lets say 202, the application
just crashes with the following message:- System.InvalidCastException: Object cannot be cast from DBNull to other types. I tried to set some breakpoints in the application to debug, however it does not even pass through these breakpoints! Any ideas on what the problem can be or how I can debug? Thanks for your help and time Johann
View 6 Replies
View Related
Oct 3, 2006
This is the actual statement displayed from Response.Write in classic ASP. INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '06-012497',TaskNum,TaskDesc,TaskMemo,Account,'2006-Oct-3',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum = '000002' There are 4 records returned from the SELECT part of the statement. In some situations, 4 records are inserted to WOTasks table, in others, only 1 record is inserted. I can't find out why 1 record, instead of 4, record is inserted. A form page submits the form to the save page using post method. The above statement is contained in the save page. When one of the form textbox is filled, 1 record is inserted. When the textbox is not filled, 4 records are inserted. You may think the textbox has something to do with the behaviour. I also think so but the content of the textbox does not affect the sql statement. In both cases, the insert statement is the same. In the actual codes, only strings in quotes are variables and the rest are hardcoded. When I run the statement in SQL Server, 4 records are affected. No such problem when connected with Access.The actual code belowSub AddTask(ByVal proc, ByVal wonum) Dim sSQL sSQL = "INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '" & wonum & _ "',TaskNum,TaskDesc,TaskMemo,Account,'" & curDate & "',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum='" & proc & "'" 'Response.Write sSQL:Response.End conn.Execute sSQL, , 128 End Sub
View 2 Replies
View Related
Jan 20, 2008
Hi there I have this statement I have written as follows:strCommand = "SELECT * FROM tblstock WHERE Type='"&Statement &"' AND Description like'"&criteria &"%' OR Tag like'"&criteria &"%'OR Location like'"&criteria &"%' OR LAN like'"&criteria &"%' OR RAM like'"&criteria &"%' OR CD like'"&criteria &"%' OR OS like '" &criteria &"%' OR SN like'" &criteria &"%' OR DeviceStatus like'" &criteria &"%' ORDER BY " &sSortStr The variable "Statement" is passed into the sub as "PC" which means only records of type "PC" should be displayed along with any other criteria. The issue I'm having is that when I specify criteria I'm also recieving other types eg "Cameras" if they contain any of my criteria.. I can't understand how because in the statement I tell it only to display records of type "PC".. Anybody know what I'm doing wrong? Thanks
View 3 Replies
View Related
Feb 1, 2008
I've done a new tabel that insert the UserId that in a uniqueidentifier get from Membership.GetUser().ProviderUserKeySo if I want to make a select statement threw storedprocedure in codebehind it runs as it shouldCode behindDim GetCustomersCars As CustomerCarByUserId = New CustomerCarByUserId MyCars.DataSource = GetCustomersCars.CarByUserId(Membership.GetUser().ProviderUserKey)MyCars.DataBind() But in when I use ObjectDataSource it fails<asp:ObjectDataSource id="ObjectDataSource1" runat="server" selectmethod="CarByUserId" typename="CustomerCarByUserId"> <SelectParameters> <asp:Parameter defaultvalue="Membership.GetUser().ProviderUserKey" name="UserId" type="Object" /> </SelectParameters> </asp:ObjectDataSource>I've tried with Membership.GetUser().ProviderUserKey.ToString(), but that doesnt work. Error message:InvalidCastExceptionI connect to the same source in both cases.Any one with an Idee ?
View 1 Replies
View Related
May 13, 2008
So we currently are running a SQL 7 Server, which hopefully we will be updating this year but untill then we're stuck. I've got a web app using ASP.NET 2.0 and on occasion I get this error:
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) 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()
View 2 Replies
View Related
Mar 25, 2004
I Some sql that it keeps throwing the following error
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Here is the sql
UPDATE dbo.temptable
SET code = 'MM'
FROM dbo.temptable T
JOIN dbo.Item I WITH (NOLOCK)
ON T.ProductID = P.ProductID
JOIN dbo.cars C WITH (NOLOCK) ON I.ModelID = C.ModelID
JOIN dbo.carmakes CM WITH (NOLOCK) ON CM.MakeID = C.MakeID
JOIN dbo.CarDealerMakes CDM WITH (NOLOCK)
ON CM.MakeID = CDM.MakeID
WHERE T.ProductID NOT IN (
SELECT distinct A.ProductID
FROM dbo.Action A WITH (NOLOCK)
JOIN dbo.ActionPurchases AP WITH (NOLOCK)
ON A.ActionID = AP.ActionID
WHERE A.CustomerID = 2
UNION
SELECT distinct A.ProductID
FROM dbo.Action A WITH (NOLOCK)
JOIN dbo.ActionServices S WITH (NOLOCK)
ON A.ActionID = S.ActionID
WHERE 2 = A.ProductID)
That sql will run on windows 2000 but it will not run on windows 2003.
I have the latest service pack.
Has anyone ever come across this before or have and suggestions ?
View 2 Replies
View Related
Jul 9, 2005
I have a query as follow:
SELECT @resRate = (SELECT resRate FROM ProjectAssign WHERE proNo = (SELECT projNo FROM Timer_Cust WHERE refNum = @actProjNo AND username= (SELECT username FROM Timer_Emp WHERE refNum = @actEmployee) ) )
the definition of table Timer_cust contains refNum(int), projNo(varchar) and projName(varchar).Timer_cust table doesn't contain column username. So this query shoud generate a runtime error. however it works fine without error.But if I run SELECT projNo FROM Timer_Cust WHERE refNum = @actProjNo AND username= (SELECT username FROM Timer_Emp WHERE refNum = @actEmployee)A runtime error message appears.Why?
View 3 Replies
View Related
Aug 13, 2001
Can any one tell me why sp_helpindex shows several indexes though there is only one index on the table. When i use Enterprise manager it shows only one index..Any idea..is it a bug ..do I need to apply latest service pack ?
Here is the output shows
IX_tbl_ncf_bo
_WA_Sys_Deal_No_2902ECC1
_WA_Sys_fwddate_2902ECC1
_WA_Sys_deal_type_2902ECC1
_WA_Sys_RegionID_2902ECC1
_WA_Sys_CompID_2902ECC1
_WA_Sys_To_Include_2902ECC1
_WA_Sys_SvcChoice_2902ECC1
_WA_Sys_UtilityID_2902ECC1
First one is actual index that I created, remaining
View 1 Replies
View Related
Jul 31, 2000
i have four separate jobs which run at scheduled time daily..out of which one job has run properly and the other three have not run at all(there is no details of it in "View job history")for only one day in between..again from the next day all the four jobs are running properly.what could be the reason for the three jobs not running on a particular day?(The jobs were scheduled between 3 and 4am in the morning..so nobody would be using the system at that time and the system was definitely up at that time as that one job has run)
i could afford to have such strange behaviour in future as the jobs are important and should run daily.any ideas by which i could make sure all four jobs run daily?
View 6 Replies
View Related
Feb 6, 2001
We've been finding some strange indexes in our SQL 7 database. They are not indexes we put in, but seem to be system generated. They always have the format "hind_" and then a series of numbers and underscores. Most of the time they replicate indexes we already had in place. The strangest part is that in some cases they seem to be generating multiple clustered indexes on the same table. A previous question recieved an answer saying that they were statistical indexes. Could someone shed a little more light on what these are, where they came from, and how I can prevent them from coming back? Our queries usually take much longer on tables with these "rogue" indexes on them. Thanks.
View 1 Replies
View Related
Mar 13, 2003
Why is it so?
select len("Test")
--returns 4
select len("Test ")
--returns 4
select len(" Test ")
--returns 7
why not all 4 ?
why not 4,11,14?
View 6 Replies
View Related
Nov 29, 2005
I have a SP that usually works fine (0-16 CPU time, 40 ms Duration), but from time to time the server hangs with apparently no reason. The SP has a lock timeout set to 500, so it should abort if a lock timeout error (1222) occurs but it doesn't. The Profiler reports very long execution time (over 30 sec), and because of that all other SP calls are blocked, 'cause the transaction opened by the first sp execution is not finished yet.
Any other attempts to identify other blocking queries did not show me anything suspect (sp_lock, dbcc opentran) other then the usual blocked chain. I'm starting to think about an IO bottleneck, or IO failure, that could block the disk access and cause the delay. The status of RAID 5 is healthy.
The server is used as storage system for a website (approx. 2000 concurrent users), and occasionally I noticed an ASP queue, but this strange behavior occurs even during the peak-off hours.
Any thoughts ?
-----
HP Server - 2 CPU @ 3,4 ; 4 GB RAM; SCSI - RAID 5
Windows 2000 Advanced Server - SQL Server 2000 SP4
View 1 Replies
View Related
Apr 24, 2006
I have a table. It shows "Rows: 2" when I open it in the table properties dialog. But there is no record in the table when I open it from Open Table -> Return All Rows.
I don't understand how this can happen. Any help is appreciated.
Thanks.
View 6 Replies
View Related
Nov 19, 2006
Hello
I have one PC with SQL server 2000 and I am working on one database called TaxReg but today I found it gray and (suspect) beside it
could you please tell what is the problem and how to fix it ?
View 1 Replies
View Related
