2000 To 2005 Query Processng Difference?
Mar 8, 2007
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.
Thanks for any replies.
View 2 Replies
ADVERTISEMENT
Aug 1, 2007
Hi,
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.
Cheers,
Chris
View 4 Replies
View Related
Mar 3, 2006
What are the difference between SQL Server 2000 and SQL Server 2005 Express Edition.?
Or where i can find the list of differences. ?
Can SQL Server 2000 and SQL Server 2005 Express Edition can be used interchangeably for basic database operations?
Hope to get some response soon.
Regards
Sanjeev
View 2 Replies
View Related
Aug 2, 2006
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.
Thanks
View 2 Replies
View Related
Apr 19, 2005
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
View 2 Replies
View Related
Jul 20, 2005
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
View 5 Replies
View Related
Sep 10, 2007
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...
Reply me asap.
Thanx in Advance.
View 1 Replies
View Related
Feb 1, 2008
Hi,
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.
Thanks for your time on this one, in advance.
Regards
Pankaj
View 3 Replies
View Related
Dec 21, 2005
Hi,
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.
Thanks in advance
Chris
View 3 Replies
View Related
Mar 15, 2006
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.
PLEASE HELP....
=====================================================
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 ))
View 3 Replies
View Related
Jul 18, 2007
Hi there,
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?
Any guidance is much appreciated.
Thanks!
View 1 Replies
View Related
Jan 12, 2006
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).
Thanks for any guidance!!
Cheryl Marland
cheryl.marland@va.gov
View 3 Replies
View Related
Aug 15, 2007
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.
any one else run into this problem?
View 1 Replies
View Related
Apr 25, 2008
Hello to all,
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?
Thanx
Alex
View 7 Replies
View Related
Jan 9, 2006
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!
View 5 Replies
View Related
May 24, 2007
Hello,
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).
Best regards,
Ike Casteleyn
View 6 Replies
View Related
Nov 18, 2005
I'm trying to set up Service Broker Services on SQL 2005 x86. I've got two services set up, and a stored procedure associated with one of them.
View 5 Replies
View Related
Mar 28, 2008
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.
Stacy
View 5 Replies
View Related
Jun 7, 2007
I am sending out an SOS.
Here is the situation:
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.
Thank you. Scott
View 4 Replies
View Related
Aug 24, 2006
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
View 5 Replies
View Related
May 9, 2001
Hi all,
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.
Thanks!
View 1 Replies
View Related
Sep 19, 2007
Can anyone please help me to find the diffence between two fields.
Field1 - 16:12:27:123
Field2 - 16:12:36:750
I need to find the difference between field2 and field1??
View 5 Replies
View Related
Oct 8, 2007
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
Thanks,
Rahul Jha
View 14 Replies
View Related
May 28, 2008
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);
Passenger_ID Passenger_Name Ticket_Price
---------------------------------------
132812298 Nice,Richard 1750.00
234890032 Franco,Sylvia 1750.00
339209841 Kim,Jongouk 1750.00
385894857 Uribe,Gloria 1750.00
(4 row(s) affected)
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.
Thanks for any help you can offer.
View 4 Replies
View Related
Dec 12, 2007
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.
Anyone offer any advice?
View 6 Replies
View Related
Aug 18, 2006
Hi
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?
Please help us.
Regards,
Sankar N
View 1 Replies
View Related
Apr 11, 2006
What is the difference between binary and image dataType.
When and how should I use them?
View 1 Replies
View Related
Oct 15, 2007
HI guys
I installed the SQL server 2005 express edition, but it shows in my add/remove programs as "Microsoft SQL server 2005".
Howcan I tell which version I have installed ?
View 4 Replies
View Related
Jun 18, 2015
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?
View 6 Replies
View Related
Dec 5, 2007
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?
Thanks.
View 1 Replies
View Related
Feb 25, 2008
Hi,
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.
Thanks and looking forward.
View 1 Replies
View Related
Oct 24, 2007
Question is in the subject.
Thanks in advance
-Jamie
View 7 Replies
View Related
Aug 1, 2014
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?
View 3 Replies
View Related