I found an unusual problem between 2000 and 2005 I haven't been able
to decipher from any documentation.
The query structure is as follows:
select *
from
tableA a
join
tableB b ON a.somekey = b.somekey
where
a.type = 'A'
and datediff(yyyy, b.someDateField, getdate()) between
a.lowboundary and a.highboundary
Some basic facts about the elements and data. The low and high-
boundary fields are varchar datatypes. In 2005 (regardless of
compatibility type I run the database under), the query evaluates the
BETWEEN and errors out due to the fact that it is evaluating the
DATEDIFF as an integer and finds a non-integer entry in either
lowboundary or highboundary. I understand and expect this behavior.
Obviously, changing the result of the DATEDIFF function to varchar
allows the operation to go forth.
The odd thing is that there is no "a.type = 'A' " entry, thus the
query wouldn't return anything. In 2000, it seems as though the
engine is evaluating the type = 'A' and short-circuiting and in 2005,
it is trying to evaluate the entire query OR is there an implicit
conversion occuring in 2000 and not in 2005?
As I mentioned, the compatibility mode doesn't change how this reacts,
but running this on a native 2000 server allows this to happen. This
particular code isn't the problem, it's what we might have to contend
with when we migrate this through. Sure, we're going to perform
regression testing, but I'm concerned about what we would miss.
I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.
The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).
I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.
Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.
I am creating an install program and I'm wondering if there is a difference between SQL Server 2000 and MSDE 2000? Do they have different entries in the Registry?
From the documentation that I've read it seems as if they are one and the same.
However, if someone knows how to differentiate between them in the Registry it would be greatly appreciated.
Hi, I am developing an web-database application using ASP.net,c# and MSDE. and after the completion of the project i want to deploy the database over the server whichs got sql server professional edition. Are there any changes to be made while deploying it over the server and also will the connection string for MSDE and SQL client the same ? Please do clear me the confusions i have got .. thanks inadvance
During testing of an application, i noticed a difference betweenSQL 2000 and SQL 7, both with identical config.In a nutshell:A table has a trigger for UPDATE and DELETE.When a column in the table is UPDATED the following happens:In autocommit mode, when entering a trigger the trancount equals1 for both SQL 7 and 2000.When the same update is performed in an explicit transactionin SQL 7 @@TRANCOUNT equal 2, and in SQL 2000 @@TRANCOUNT equals 1.Configuration is the same and there are no implicit transactions.I don't need a work around as this will invalidate the migrationprocess as both products should behave identically.What would influence the difference or why is there a difference???Is there something which has been overlooked?================================================== =======The following code replicates the problemEnsure implicit transactions are off in both versions at the serverlevel, thus defaulting to autocommitted mode.Ensure sp_configure settings are identical.Step 1: Create a DB called test:Step 2: Execute the following under the context of test DB.if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, Outrigger') = 1)drop trigger [dbo].[trigtest]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[test]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[trancount]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[trancount]GOCREATE TABLE [dbo].[test] ([id] [int] IDENTITY (1, 1) NOT NULL ,[text] [char] (10) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[trancount] ([id] [int] IDENTITY (1, 1) NOT NULL ,[trancount] [int] NOT NULL) ON [PRIMARY]GOCREATE TRIGGER trigtest ON [dbo].[test]FOR UPDATE, DELETEASdeclare @trancount intselect @trancount = @@TRANCOUNTinsert into trancount ( trancount ) values ( @trancount )Step 3: Run the following against the DB, then check trancount table.-- Add a record to the test table (trigger will not fire)insert into test (text) values ( 'xxxx' );go-- Update the value (autocommit mode) to fire trigger-- Under SQL 7 and 2000, trancount table will only indicate 1tranaction open.-- This is being performed in autocommit mode.update test set text = 'test1'go-- Update value using an explicit transaction-- Under SQL 7, trancount will equal 2 in trigger, in SQL 2000trancount equals 1begin transactionupdate test set text = 'test2'commit workgo
Hello everyone,i want to know abt the "Difference between MSDE and SQL Server 2000". For example the features they support, capacity and requirements etc...
We are into a phase of retiring SQL 2000 database and replacing with SQL 2005. Could you please guide me to get a list of all the MUST DO changes on syntax, statements/query those have been changed in 2005 when compared to 2000.
I know there is a list of new features list on site, but it doesn't tells me precisely what all syntaxes will result differently then expected in 2000. For Example - if we open help for SET ANSI_NULLS ON, we can read that MS is suggesting to avoid its use as it will be absolete in later version. So do we have a list of all such things in one place that we can read and analyse our code for changes to be done now instead of finding it later.
We are currently trying to get to grips with SQL Server 2005, and have a basic question. Is it possible to user Query Analyser from SQL 2000 with 2005? All our support staff use Query Analyser on a day to ay basis and all our clients are currently on SQL Server 2000. It would be usefull if when new customers come on line with 2005 our staff can use one tool to perform their tasks
We've tried to run Query Anayser and it's comming up with a connection error, this may be down to us not setting some access up or not connecting correctly.
The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
=======================
SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL AND VWCONTACT.EMAIL LIKE '%@%' AND NOT EXISTS (SELECT VWCONTACT.ID FROM (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0 ON VWCONTACT.ID = TMP0.CONTACTID JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1 ON VWCONTACT.ID = TMP1.CONTACTID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1 WHERE VWCONTACT.ID = NESTEDQUERY1.ID) AND NOT EXISTS (SELECT VWCONTACT.ID FROM (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77336) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77338) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77345) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77365) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77396) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77489) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77504) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR ON ATTR.CONTACTID = VWCONTACT.ID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1 WHERE VWCONTACT.ID = NESTEDQUERY1.ID) AND NOT EXISTS (SELECT VWCONTACT.ID FROM (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 23102 AND ATTRIBUTEVALUE = 'Houston') TMP0 ON VWCONTACT.ID = TMP0.CONTACTID JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22944 AND ATTRIBUTEVALUE = 'TX') TMP1 ON VWCONTACT.ID = TMP1.CONTACTID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1 WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.
SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL AND VWCONTACT.EMAIL LIKE '%@%'
EXCEPT
((SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0 ON VWCONTACT.ID = TMP0.CONTACTID JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1 ON VWCONTACT.ID = TMP1.CONTACTID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) UNION (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77336) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77338) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77345) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77365) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77396) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77489) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77504) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR ON ATTR.CONTACTID = VWCONTACT.ID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) UNION (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 23102 AND ATTRIBUTEVALUE = 'Houston') TMP0 ON VWCONTACT.ID = TMP0.CONTACTID JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22944 AND ATTRIBUTEVALUE = 'TX') TMP1 ON VWCONTACT.ID = TMP1.CONTACTID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))
We are in the process of upgrading from SQL Server 2000 to 2005. During testing we came across the following situation.
To reproduce the issue you can do the following
Create this structure in a 2000 and 2005 server instances
CREATE TABLE test (a int, b varchar(30))
INSERT INTO test (a, b) VALUES (1, '2')
INSERT INTO test (a, b) VALUES (3, '3a')
INSERT INTO test (a, b) VALUES (4, '4')
Then run the following statement in both servers:
UPDATE test SET a = ltrim(rtrim(b)) WHERE b NOT LIKE '%a%' AND ltrim(rtrim(b)) <> a
In 2000 this last statement will execute with no problem and it will update one row, whereas in 2005 the following error message is given:
Msg 245, Level 16, State 1, Line 20 Conversion failed when converting the varchar value '3a' to data type int.
By looking at the execution plan it seems that 2005 first tries to evaluate ltrim(rtrim(b)) <> a and then excludes those rows containing a whereas 2000 first excludes those rows containing a and then evaluates the different than condition.
I know fixing this instance itself is easy but I€™m more concerned about having to rewrite many more stored procedures where we find this same scenario; is there any setting that can be changed to avoid this?
I have kind of an urgent need here. I need to know if there is a dll file or registry key or something of that nature that I can query on to find out if my SQL 2000 installations are Enterprise or Standard.
I understand I can do this using query analyser, but that will not work with my reporting tool (this is for reporting numbers of SQL installations in our very large enterprise for Microsoft license compliance).
We have an interesting problem. We are attempting to migrate from sql 2000 to sql 2005. the schema we have is exactly the same. the new 2005 box is more powerful than our 2000 box.
here is our schema:
tbl_Items ItemID int pk ReferenceID int sessionid varchar(255) StatusID int
tbl_ItemsStatus statusid int pk isinternalstatus bit
there is an index on (ReferenceID, SessionID, StatusID) and (SessionID, StatusID)
this is the query:
DECLARE @referenceid INTEGER SET @referenceid = 1019
SELECT MAX(i2.itemid) FROM tbl_Items i2 (NOLOCK) JOIN tbl_ItemsStatus s (NOLOCK) ON i2.StatusID = s.StatusID WHERE s.IsInternalStatus = 0 AND i2.referenceid = @referenceid AND i2.sessionid IN ( SELECT i3.sessionid FROM tbl_Items i3 (NOLOCK) WHERE i3.referenceid = @referenceid AND i3.status <> 7 AND i3.status <> 8 AND i3.status <> 10 AND i3.itemid IN ( SELECT max(i4.itemid) FROM tbl_Items i4 (NOLOCK) WHERE i4.referenceid = @referenceid GROUP BY i4.sessionid ) AND i3.itemid NOT IN ( SELECT MAX(i7.itemid ) FROM tbl_Items i7 (NOLOCK) WHERE i7.referenceid = @referenceid AND i7.SessionID IN ( SELECT i5.SessionID FROM tbl_Items i5 (NOLOCK) WHERE i5.status <> 11 AND i5.referenceid = @referenceid AND i5.itemid IN ( SELECT MAX(i6.itemid) FROM tbl_Items i6 (NOLOCK) WHERE i6.referenceid = @referenceid AND i6.status IN (7,11,8) GROUP BY i6.sessionid ) ) GROUP BY i7.SessionID ) )
GROUP BY i2.sessionid
we know this query is pretty bad and can be optimized. however, if we run this query as is on 2005 it takes about 2 hours to run...if we run the exact same query on 2000 it takes 9 seconds.
so this query on 2005 if run takes 2 hours..however, if we omit the s.IsInternalStatus = 0 or the i2.referenceid = @referenceid line it takes about 9 seconds.
why would this be? it makes no sense why omitting one of those where clauses would increase the performance of the query by 2 hours? we know its a bad query...but this doesnt make sense.
if have a problem with a SELECT query that works very fine on SQL Server 2000 but not on 2005. I've transfered my db by creating a full backup and restoring the db on 2005. The db is working except this problem.
When i start executing it doesn't finish. I waited a couple of minutes. On 2000 it only takes about 6 seconds to run.
Here it is:
Code Snippet
SELECT * FROM PPS_TERMbesttmpwhere PPS_TermBestTmp.BestNr + CONVERT(varchar(30),CAST(REPLACE(PPS_TermBestTmp.Pos1, ',', '.') AS float),2)
NOT IN (SELECT PPS_TermBest.BestNr + CONVERT(varchar(30), PPS_TermBest.Pos1,2) FROM PPS_TermBest) Any ideas?
Hi All: I am new to Sql 2000 database,Now I'm planing to create a table in my databse,my table included below fields like this : PoNo(the length is 15 characters) ,Supplier Name(the length is 50 characters).etc but I don't how to select the datatype for them. should I select Char or VarChar ? which one is the best slection ? thans in advanced!
Could someone give me the difference between "Sql Mobile 2005" and "Sql Compact 2005". At first I thought they were the same, that this was just something of the change in naming the server has had the last months. My first guess was that Mobile would be the new one, but I have 'accidently' downloaded the Compact and what seems the System.data.SqlServerCE.dll is newer.
So is there a difference? If yes, what are they. If not, is the "compact" then the latest version and the name to be used (since on the site only "mobile" is mentioned).
I have a simple update statement that is running forever in SQL 2005 but works fine in SQL 2000. We have a new server we put SQL 2005, restored db. The table in question WEEKLYSALESHISTORY I even re-indexed all the indexes and rebuilt the stats as well. But still no luck, still running extremely long. 1 hour 20 minutes.
I'll try to give you some background on these table. Weeklysalehistory has approx 30 fields. I have 11 indesxes set up weekending date being one of them. And replication control has index on lasttrandatetime as well. So I think my indexes are fine.
/* Update WeekEnding Date for current weeks WeeklySales Records */ Update WeeklySalesHistory set weekendingdate = (SELECT LastTransDateTime from ReplicationControl where TableName = 'WEEKHST') where weekendingdate is null
Weekly sales has approx 100,000,000 rows Replication control has 631,000 (Ithink I can delete some from here to bring it down to 100 or 200 records) Although I don't think this is issue since on 2000 has same thing and works fine.
I was trying to do this within SSIS and thought that was issue. I am new so SSIS but it runs long even if I just run it as a job with this simple Update statement so I think its something with tables, etc that is wrong.
One thing on noticed if I look at the statistics in SQL Server Management studio there is a ton of stats. some being statistics on indexes which makes sense then I have a ton of hind_113_9_6 and simiiar one like this. I must have 90 or so named like this. Not sure how to check on SQL 2000 all the stats to see if they moved over from there or what. I checked a few other tables and don't have all these extra stats. Could this be causing the issue do I need to delete all these extras? Any help would be greatly appreciated.
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong. Thank you
I've been running a long query which takes almost 39 seconds in Query Analyzer. After creating a Stored Procedure (with the same query) I expected to run it faster bcoz I heared that SP has a cache, and its a faster technique. But I didnt gain any performance improvments.
Can somebody clear my confusion, what I'm doing wrong.
Guys can you tell me whether there is any difference between the following queries.......... Both of them are resulting same number of records. Not sure whether the output is same or not. Just wanted to know if result output wise if there is no difference than performance wise there might be some. Kinldy educate me on the same.
SELECT * FROM FRProposalOutline FPO INNER JOIN SC_Collection SCC ON FPO.Items= SCC.CollectionGID UNION SELECT * FROM FRProposalOutline FPO INNER JOIN SC_Collection SCC ON FPO.PageBreakPositions= SCC.CollectionGID
SELECT * FROM FRProposalOutline FPO INNER JOIN SC_Collection SCC ON FPO.Items= SCC.CollectionGID OR FPO.PageBreakPositions= SCC.CollectionGID
Hi, I have created a query (using SQL 2005) that will pull the people who have spent the most on tickets purchased:
Select P.Passenger_ID, Passenger_Name, Ticket_Price From Passenger P, Ticket_Purchase T Where P.Passenger_ID = T.Passenger_ID Group By P.Passenger_ID, Passenger_Name, Ticket_Price Having Ticket_Price >= All (Select Max(Ticket_Price) From Ticket_Purchase Group By Ticket_Price);
I now want to be able to only choose the Passenger_ID's from above who are not listed in another table called Frequent_Flier, which should leave me with only 2 records not 4.
I am wondering if I add the below to the first query to eliminate those passengers in the Frequent_Flier table: NOT IN (Select Passenger_ID From Frequent_Flier);
When I add it to the Where clause I get an error. Should I be sub-querying that differently or is there a better way to do this.
Hi, I have a BETWEEN query (at least I think that's what it will need), but with a difference.
Normally you would specific a field which was BETWEEN two set variables
ie. {fieldname} BETWEEN 1 AND 3
However I need mine the other way round.
I have a series of records which have a startdate and enddate held against them.
When a user submits a new record to the db, I need it to check that the starting and ending date range doesn't overlap any of the existing start-end date ranges that exist.
In order to do that I'm trying to build a query which takes in the incoming startdate variable and see if that is within any of the existing start-date-enddate dates ranges of the existing records, and then same for the incoming endate. I actually want the ones that are going to cause a problem to appear...
I;m sure there is a pretty easy way of coding this, but I'm struggling to get my head round it.
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
I have a table that will be loaded over night everyday and I need to write a query on running value difference ?
List of Columns (ID, Branch ,Group, Date, Value)
ID   Branch  Group  Date                 Value 1       A          C    2015-06-01           10 2       A          C      2015-06-02           15 3       A          C      2015-06-03           25 4        A          C      2015-06-04           20 5        B          D       2015-06-01           20 6        B          D       2015-06-02           25 7        B          D       2015-06-03           10 8        B          D       2015-06-04           20
I want the Output like below with a Running value difference in comparison to previous day.
ID   Branch  Group  Date         Value   Running Value 1       A          C    2015-06-01           10        10 2       A          C      2015-06-02           15         05 3       A          C      2015-06-03           25        10 4        A          C      2015-06-04           20        -5 5        B          D       2015-06-01           20        20 6        B          D       2015-06-02           25        05 7        B          D       2015-06-03           10        -15 8        B          D       2015-06-04           20        10
Basically I need to compare the previous day and show the difference. How can I do this in SQL 2008 r2?
hi, I just realized that this must be a difference between sql server 2000 and 2005. For example, the query is like this: declare @n int set @n = -1 select @n = employeeid from employees where companyID=1 and idnumber='1-1' --select @n if(@n is null) ...... If there is no such employee, in 20002, @n will be null, but in 2005, @n is -1, not null. Am I right? So, for this piece, I have to do twice on the same where, like if(exists(select * from employees where companyID=1 and idnumber='1-1')) select @n=employeeid from employees where companyID=1 and idnumber='1-1' else set @n = null Any better way?
do any body know the article that can give me berif idea about the difference between sql server 2005 and 2008, acutally i want to move over to 2008. but if the difference is not that much then i m might think about that.
We are trying to troubleshoot some website performance issues and found some queries taking 2 to 3 seconds when the request comes from the web, and captured by the Profiler.
The same queries, when run in the Query Analyzer take 0 seconds.
What could be the reasons for this difference, I mean why it takes 2 - 3 seconds shown by the Profiler, when it's 0 second in Analyzer?
