SQL2005 Outperforms SQL2000? Its Over 4 Times Slower...

Aug 1, 2006

I've found a very interesting case where SQL 2005 is more than 4 times slower than its predecessor, despite superior hardware.

To compare, I have some logs from the ISA Server has stored into the database, which is then filtered against my blocklists. The result are the most popular sites I have not blocked or trusted.
I have done some tuning on the query to utilize the data patterns that are in the database. This allows much better scaling towards large datasets. The engine is now capable to use hash-matches instead of nested loops over the entire datasets.

It currently runs in 1 to 1.5 minutes on SQL 2000, which is powered by a VIA C3 Nehemiah at 1 GHz, with only 350 MB RAM too spare in optimistic conditions (The remainder of the 1 GB is used by many other applications). THe server is limited to 384 MB memory usage.
The other box running SQL 2005 is a Athlon 800 MHz, which has superior processing power, larger cache and more memory bandwidth and has 1.25 GB RAM, where SQL server can use the needed 629 MB without any problem.
Despite these facts, my query takes more than 4 times longer on the SQL 2005 box. 1.5 minutes compared to 7 to 8 minutes.

SQL 2000 Database Schema:

CREATE TABLE [dbo].[WebProxyLog](
[ClientIP] [bigint] NOT NULL,
[ClientUserName] [nvarchar](514) NOT NULL,
[ClientAgent] [varchar](128) NOT NULL,
[ClientAuthenticate] [smallint] NOT NULL,
[logTime] [datetime] NOT NULL,
[service] [smallint] NOT NULL,
[servername] [nvarchar](32) NOT NULL,
[referredserver] [varchar](32) NOT NULL,
[DestHost] [varchar](255) NOT NULL,
[DestHostIP] [bigint] NOT NULL,
[DestHostPort] [int] NOT NULL,
[processingtime] [int] NOT NULL,
[bytesrecvd] [bigint] NOT NULL,
[bytessent] [bigint] NOT NULL,
[protocol] [varchar](12) NOT NULL,
[transport] [varchar](8) NOT NULL,
[operation] [varchar](24) NOT NULL,
[uri] [varchar](2048) NOT NULL,
[mimetype] [varchar](32) NOT NULL,
[objectsource] [smallint] NOT NULL,
[resultcode] [int] NOT NULL,
[CacheInfo] [int] NOT NULL,
[rule] [nvarchar](128) NOT NULL,
[FilterInfo] [nvarchar](128) NOT NULL,
[SrcNetwork] [nvarchar](128) NOT NULL,
[DstNetwork] [nvarchar](128) NOT NULL,
[ErrorInfo] [int] NOT NULL,
[Action] [varchar](32) NOT NULL,
[GmtLogTime] [datetime] NOT NULL
)

CREATE TABLE [dbo].[TrustedHosts](
[Hostname] [varchar](60) NOT NULL,
[Comment] [varchar](500) NULL,
CONSTRAINT [PK_TrustedHosts] PRIMARY KEY CLUSTERED
(
[Hostname] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DeniedHosts](
[ReasonId] [smallint] NOT NULL,
[Hostname] [varchar](80) NOT NULL,
[Path] [varchar](50) NOT NULL CONSTRAINT [DF_DeniedHosts_Path] DEFAULT ('%'),
[Comment] [varchar](500) NULL,
CONSTRAINT [PK_DeniedHosts] PRIMARY KEY CLUSTERED
(
[Hostname] ASC,
[ReasonId] ASC,
[Path] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [FK_DeniedHosts_DenyReason] FOREIGN KEY([ReasonId]) REFERENCES [dbo].[DenyReason] ([ReasonId])
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [FK_DeniedHosts_DenyReason]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_HostName_NoTwoDots] CHECK ((((not([Hostname] like '%..%')))))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_HostName_NoTwoDots]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_NoPercentDot] CHECK (([Hostname] <> '%.'))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_NoPercentDot]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_NoWildcardMiddle] CHECK ((((not([Hostname] like '_%[%]%')))))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_NoWildcardMiddle]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_ValidPath] CHECK (([Path] is null or [Path] = '%' or [Path] like '/%' and [Path] <> '' and [Path] <> '/%'))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_ValidPath]
GO
ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_WildcardStart] CHECK ((((not([Hostname] like '[%]%'))) or ([Hostname] like '[%].%' or [Hostname] = '%')))
GO
ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_WildcardStart]


I'm not going to give you any data as:
WebProxyLog contains 1 223 878 rows; 524 MB (few more on SQL 2000 database).
DeniedHosts contains 52 338 rows; 3 MB
TrustedHosts contains 2 183 rows; <1 MB

The relevant query is:

CREATE PROCEDURE [dbo].[GetTrustedHosts]
AS
SELECT Hosts, Requests --, DistinctRequests
FROM(SELECT AA.Hosts, COUNT(*) AS Requests, COUNT(DISTINCT Path) AS DistinctRequests
FROM(SELECTCASE
WHEN CHARINDEX('':'', SUBSTRING(URI, 8, CHARINDEX(''/'', URI, 8)-8), 8) <> 0 THEN SUBSTRING(URI, 8, CHARINDEX('':'', URI, 8)-8)
ELSE SUBSTRING(URI, 8, CHARINDEX(''/'', URI, 8)-8)
END AS Hosts,
SUBSTRING(URI, CHARINDEX(''/'', URI, 8), 50) AS Path
FROM dbo.WebProxyLog wpl
WHERE URI LIKE 'http://%/%'
AND ResultCode BETWEEN 200 AND 399
AND (Service = 1) -- filter for only forward proxy
) AA
WHERE NOT EXISTS
(SELECT *
FROM dbo.DeniedHosts dhp
WHERE Path <> ''%''
AND LEFT(AA.Path,3) = LEFT(dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS, 3)
AND AA.Hosts LIKE (dhp.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS)
AND ( AA.Path LIKE (dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS) OR AA.Path LIKE ((dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS) + ''[?]%'') )
)
AND NOT Hosts IS NULL-- this seems to give a speed advantage
GROUP BY AA.Hosts
HAVING COUNT(*) >= 25
) A
WHERE NOT Hosts IN
(SELECT Hostname COLLATE SQL_Latin1_General_CP1_CI_AS
FROM dbo.TrustedHosts thc
--WHERE NOT thc.Hostname LIKE ''%[%]%''
)
AND NOT Hosts IN
(SELECT Hostname COLLATE SQL_Latin1_General_CP1_CI_AS
FROM dbo.DeniedHosts dhc
WHERE dhc.Path = ''%''
)
AND NOT EXISTS
(SELECT *
FROM dbo.TrustedHosts thh
WHERE A.Hosts LIKE (thh.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS)
AND thh.Hostname LIKE ''[%]%''
-- generates a hash join instead of a of nested loop
AND RIGHT(A.Hosts,6) = (RIGHT(thh.Hostname,6) COLLATE SQL_Latin1_General_CP1_CI_AS)
)
AND NOT EXISTS
(SELECT *
FROM dbo.DeniedHosts dhh
WHERE dhh.Path = ''%''
-- this reduces the cost of the most expensive query
AND dhh.Hostname LIKE ''[%]%''
AND A.Hosts LIKE (dhh.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS)
-- generates a hash join instead of a of nested loop
-- performance difference is significant due to volume
AND RIGHT(A.Hosts,6) = (RIGHT(dhh.Hostname,6) COLLATE SQL_Latin1_General_CP1_CI_AS)
)
ORDER BY Requests DESC


There is some mess with collations, but these don't seem to hurt performance.

The query plans provided differ only slightly.
SQL 2000 provides a Clustered Index Scan over WebProxyLog with predicate on resultcode. After this a filter for the LIKE operator. Cost is 78% for the scan and 10% for the filter.
SQL 2005 combines both. With the scan costing 93%.

However, the key does not seem to be in this data, as the plans are nearly equavent, with the SQL2005 executing plan looking slightly better (table scan is a larger part of the execution).

How can these differences, espcially of this magnitude, be explained?
And further, how can the query be optimized for decent performance on SQL2005? What am I doing wrong?

View 5 Replies


ADVERTISEMENT

Function Is 10 Times Slower Than SP

Mar 24, 2007

Hi all,

In order to return a table for a specific input parameter, I am using Function, but the performance is just awful! After I have tried same code as SP, the whole thing is running under 1 sec (like 0.5 sec), while the function is about 10 times slow (4-6 sec). I know in SQL 2000 function is slower than SP, but that cannot be as bad as 10 times slower.

Now, in order to use that table from SP, I have to create a temp table, then insert result into that temp table, before I can direct use any "select" sentence. Any explanation here? Or how to "select" from a SP directly?

Thanks,

Ning



View 2 Replies View Related

OpenWithServiceComponents 5 Times Slower For Oracle Than For SQL Server?

Apr 28, 2004

Hi,

I am experiencing some problems accessing an Oracle database through
OLE DB from an MTS application using OpenWithServiceComponents (which
is supposed to give me connection pooling).
When I connect to a SQL Server database it only takes 3.1 ms to open a
connection, while with Oracle it takes 15.5 ms (both DB's running on
the same machine, I made 1000 calls and took the average).
Am I doing something wrong, am I missing something here?
I have tried to use the plain Open method, but in this case it takes
5.4 ms with SQL Server and 31.4 ms with Oracle.
Is Oracle really that much slower when accessing it through OLE DB?

Thanks for any advice/hints!
Florin

View 1 Replies View Related

SQL 2012 :: Index Reorganizing Is 10 Times Slower Than Rebuild?

Oct 19, 2014

why index reorganizing is 10 times slower then rebuild with "ONLINE=ON" clause?

View 9 Replies View Related

Upgraded SQL2000 To SQL2005, SQL2005 VERY Slow

Dec 27, 2005

I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?

View 3 Replies View Related

Sql2000 && Sql2005, Want Localhost To Use Sql2000

Sep 17, 2006

 i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?

View 1 Replies View Related

Sql2000 & Sql2005

Jul 22, 2007

Hi all,
I just wonder can we install both sql2000 & sql2005
into one server, any problems?

thanks for advice.

View 1 Replies View Related

Possible Between SQL2005 And SQL2000?

Oct 17, 2007

I have two SQL servers setup; A - SQL 2005, and B - SQL 2000.


I would like to create an account on Server A which has access to the results from one view via a linked server on Server B. I don't want the user on Server A to be able to access any databases, tables or even columns on Server B with the exception of those contained within this view.

Is this possible, and how would I go about doing it? (Permission-wise)

View 9 Replies View Related

SQL2005 SP2 And SQL2000

Nov 14, 2007

I have just loaded SQL2005(server only)on my production box.
This box also is running SQL2000.
When I install the SQL2005 SP2 it says that if services are locked they will cause a reboot. I stop all the SQL 2005 services but it also want to stop SQL2000 Server for "backward compatiblilty".
I thought installing a separate instance of SQL2005 would not have any effect on my SQL2000 instance.
Thanks for your help
R/P

View 1 Replies View Related

Possible In SQL2000 - Not In SQL2005 :(

Jan 4, 2008



Hello all

I have an odd problem with sql 2005. I'm a long time sql 2000 user trying to migrate to 2005. My company uses CSVs to import data from our clients db into our SQL db. In 2000 I just create a txt source and pick whichever CSV and transform to a temp sql table. This has always worked just fine with no problems.

So we have a new server with sql 2005 installed. I go through the "migrate dts 2000" wizard and pull in all my DTSs from sql 2000. The first thing I see that needs to be fixed is "connection 1" which is my CSV connection manager along with the flat file source.

I went through both the connection and the flat file source and tried to copy exactly what I do in sql 2000. When I preview the data or even execute this package I get bad data.

Here is what my CSV looks like:
"ACCT ","ACCOUNT DESCRIPTION ","ACCT TYPE","SCH NO","SC"
"9999Z","Balance By Source Offset",9
"87B","UTILITIES(NOT PHONE)U-C",7
72,"ADM-LEGAL/AUDIT/COLL EXP",7
315,"SALES TAX - VEHICLES LOCAL 1",3
"90A","INTEREST(NON FPLAN/MORT)/N-C",7
73,"MEALS & ENTERTAINMENT-ALL",7
"210A","REBATES RECEIVABLE",2,17,4
"5A@","# OF EXT WARR SALES-UC",10
"51N","N/C-VEHICLE INV MAINTENANCE",7
"87D","UTILITIES(NOT PHONE)SVC",7
"90B","INTEREST(NON FPLAN/MORT)U-C",7
317,"ACCRUED OTHER",3,75,4


This is what it looks like when I preview it inside the flat file connection manager:
ACCT ACCT DESC ACCT TYPE SCH NO SCTYPE
9999z blance by source... 9– – "87B" UTILITIES(NO... 7


So it is basically putting two rows of csv data into one row of SQL data. Now I realize my CSV isn't in the correct format because it doesn't have the extra commas at the end of a row IF the columns are null. It seems that 2005 doesn't recognize the carriage return/line feed. It puts the actual ascii characters in the preview. Once I execute the package it changes the ascii characters into blank spaces. My big issue is, why did this work just fine in 2000 but I can't get clean data in 2005? I'm completely clueless now, I have no idea where to go with this. Its impossible to change the output of the CSVs I get. There is literally no other options. I kinda get what I get with them. Why is this happening?

View 8 Replies View Related

Migrating SQL2000 DB To SQL2005

Nov 25, 2005

Any one knows if I can restore a SQL2000 database backup file on SQL2005? and the DB structure will be upgraded?
Many thanks.

View 2 Replies View Related

Sever With Sql2000 && Sql2005

Jul 23, 2007

Hi,
Can a sever with OS win2003 install both sql2000 & sql2005? is there any problems with 2 differenct versions in one sever.

Thank you. :)

View 3 Replies View Related

Sql2000 And Sql2005 On The Same Machine

Apr 16, 2008

I have installed MSDE2000 (name: mypcsql2000, port 1433) and sql2005 Express (mypcsql2005, port 1434) on my WinXP.
I'm developing some application and I need to test both server.

It has worked fine about 1 year, but last month i got error message.

When i'm trying to connect to SQL2005 from my application or import data from Excel, i get error message:
[DBNETLIB][ConnectionOpen (Invalid Connection()).]Invalid connection.

I can access SQL2005 with Management Studio or cmd line (sqlcmd). But not with Excel (or with my application).

MSDE2000 works fine, i haven't any problems.


Last weekend I have reinstalled my WinXP and also MSDE2000, SQL2005 Express.
Worked fine, but when turn on automatic updates ON and afterwards installed latest updates, the problem is here again...


Any idea to solve this problem?

(sorry for my english...)

View 2 Replies View Related

Upgrading From Sql2000 32 Bit To Sql2005 64 Bit

Mar 26, 2007

Hi,

Is there any difficulties I should be aware of? I'm not running DTS and I think my DB is relatively simple. I've googled and not found anything that sticks out as a problem.

We are getting a new server because we want more RAM, and better upgrade options. The hosting company recommended 64bit SQL, so I am thinking of following their recommendations.

I am upgrading from a 2 x p4 2.8ghz xeons to 1 x Intel 5130 dual core 64 bit chip. I also will be going from 2 gigs to 4 gigs of RAM.


Any thoughts I would love to hear.

Thanks!

View 8 Replies View Related

Why SQL2005 Is NOT Faster Than SQL2000?

Apr 19, 2006

Hello!!

we use SQL2000(64bit) and we are considering our system to apply SQL2005

so i tested two SQL-Server on One Machine by installing each instance of them.

The RESULT..

i can't find difference of performance between two instance.

is there no impoving in Database engine?

 

* i'm sorry, for my poor sentence...

 

View 3 Replies View Related

SQL2000 TO AND FROM SQL2005 TRANSFERING

Jan 11, 2007

I am a IT Manager.I have question:

There is a database was created on SQL Server.

I want to learn if I restore the database to SQL 2005 then I want to get backup then I want to restore to SQL 2000.

shortly: SQL 2000 sever-> SQL 2005->SQL 2000 SERVER

Can I do that?

If I do that is there someting missed?

http://www.narcv.com/

View 5 Replies View Related

Replication Between SQL2000 And SQL2005

Jul 26, 2007

I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:


Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802

Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.

The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment. This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005. I have been diligently comparing our production environment to my other environment and have yet to find differences.

Has anyone else seen an error similiar to this? Any insight would be appreciated.

Thanks in advance,

Max

View 2 Replies View Related

Install SQL2000 AFTER SQL2005

Jul 9, 2007

Hi,



I need to install SQL2000 onto a machine (W2K3 R2, SP2) that already has SQL2005, SP1 running. I can't disturb the SQL2005 installation.

Are there any problems or pitfalls to avoid? I've googled and looked on Technet but not yet found an answer.

Has anyone done this?

I know that if I install a separate instance of SQL2000 alongside a default installation of SQL2000 then I'll get a totally separate installation which won't affect the other one at all, so I reckon it could be done. But I have to be sure before I start!



Thanks in advance,

Steve

View 8 Replies View Related

SQL2000 Job Calling SQL2005 Job

Feb 14, 2007

Hi,

We have two systems, one working on SQL2000 and other working on SQL2005. In SQL2000, there is JOB which needs to execute a JOB in SQL2005, get feedback based (success or failure) and proceed accordingly.

I am not sure the best practice to call SQL2005 job from SQL2000 job. Both the server's run undersame domain account.

please help, thanks
atul

View 1 Replies View Related

Restore Sql2005 Db In Sql2000

Oct 23, 2007



Hello,

Restoring a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message:

TITLE: Microsoft SQL Server Enterprise Edition
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

View 1 Replies View Related

Upgrade From SQL2000 To SQL2005

Mar 10, 2008

Hi,

I'm running a VB6 app with MDAC 2.8 on Windows Server 2003 SP1.
I create an ADO recordset with a SELECT statement which returns an empty recordset.
I then add 2 new records to it with AddNew and later perform an UpdateBatch.
Before the SQL upgrade it worked fine but now errors with:
'Cannot convert a char value to money. The char value has incorrect syntax.'
I did a SQL Trace to find out what the Updatebatch was actualling sending to SQL (listed below).
This works fine when I run it on Query Analyser using SQL 2000 but fails on SQL 2005.
I worked out the problem is linked to the columns NewValue and OldValue both are varchar 70.
The first insert has them defined varchar(7) and varchar(1). This insert succeeds, but the second insert has the columns defined as varchar(18) and varchar(12). This one fails (unless they only contains numbers!).
I'm confused.
I've read that sp_executesql has changed for SQL2005.
Any ideas anyone?

Thanks

Howard





Code Snippet
exec sp_executesql N'INSERT INTO "pit1_lamda".."Planalt"
("ElNo","Item","MvmtDate","NewValue","OldValue","PlanAltId","PlanNo","Timestamp","User_Id","RBNo","CreatorId","CreatorTypeCode","CreatedDate","ExitNo","PBTxnTriggerId","PremHistNo","RegContractPremium","WaiverPrem","SinglePremTot","PlanMvtId","SingMvtID","PLFAID","TrancheId")
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23); INSERT INTO
"pit1_lamda".."Planalt"
("ElNo","Item","MvmtDate","NewValue","OldValue","PlanAltId","PlanNo","Timestamp","User_Id","RBNo","CreatorId","CreatorTypeCode","CreatedDate","ExitNo","PBTxnTriggerId","PremHistNo","RegContractPremium","WaiverPrem","SinglePremTot","PlanMvtId","SingMvtID","PLFAID","TrancheId")
VALUES (@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46)',N'
@P1 varchar(6),@P2 int,@P3 datetime,@P4 varchar(7),@P5 varchar(1),@P6 int,@P7 varchar(12),@P8 int,@P9 varchar(8),@P10 varchar(4),@P11
varchar(12),@P12 tinyint,@P13 datetime,@P14 int,@P15 int,@P16 int,@P17 money,@P18 money,@P19 money,@P20 int,@P21 int,@P22 int,@P23 int,
@P24 varchar(6),@P25 int,@P26 datetime,@P27 varchar(18),@P28 varchar(12),@P29 int,@P30 varchar(12),@P31 int,@P32 varchar(8),@P33 varchar(4),@P34
varchar(12),@P35 tinyint,@P36 datetime,@P37 int,@P38 int,@P39 int,@P40 money,@P41 money,@P42 money,@P43 int,@P44 int,@P45 int,@P46 int',
'000000',80,'01-01-2008','1463286','0',27425002,'043902305X2 ',57153,'mtstdat4','0000',
'mtstdat4 ',1,'03-05-2008',0,0,0,$0.0000,$0.0000,$0.0000,4250207,0,0,3001752,
'000000',11,'03-02-2008','10 In Force (V011)','3 AwaitIssue',27425003,'043902305X2 ',57155,'mtstdat4','0000',
'mtstdat4 ',1,'03-05-2008',0,0,0,$0.0000,$0.0000,$0.0000,4250207,0,0,3001752







View 5 Replies View Related

Replication From SQL2000 To SQL2005

May 25, 2007

I want to replicate from SQL2000 to SQL2005. I had configured distributor, publisher in SQL2000 and also subscriber in SQL2005. But so far no replication was done on SQL2005. I wonder SQL2000 (SP2) and SQL2005 replication will it work? Is it true that I should install Service Pack 3 and above in order to replicate my database to SQL2005.

Thanks
Wint

View 1 Replies View Related

Linked Server From SQL2005 To SQL2000

Jan 7, 2007

Linked server from SQL2005 to SQL2000
Hi
I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.
 
Though the link server is created successfully, I am not able to se the tables under it so could not query anything.
 
----
 
USE [master]
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerNameInstanceName', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc',@optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerNameInstanceName', @locallogin = NULL , @useself = N'False', @rmtuser = N'remoteUser', @rmtpassword = N'remotePassword'

View 5 Replies View Related

Parameter Query Asp.net 2.0 With Sql2000 Vs Sql2005

Jan 8, 2007

Hello,
I am currently using vs2005 with sql2000 and have a parameter query getting records form my sql2000 db.
  
'Load existing data to textboxes for editingDim strSQL1 As String = "SELECT id, LogDate, LogTime, LogDD, StatDD, LogEvent FROM DEMO_DailyOccurrence WHERE completed = 0 AND RecordIdentify = @strOutdate ORDER BY id ASC "Dim scnnNW1 As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("SQLconnection"))Dim scmd1 As New SqlCommand(strSQL1, scnnNW1)With scmd1.Parameters.Add(New SqlParameter("@strOutdate", SqlDbType.DateTime)).Value = strOutdate1End WithDim sda1 As New SqlDataAdapter(scmd1)Dim ds1 As New Data.DataSetTrysda1.Fill(ds1)Catch ex As ExceptionEnd Try
 
 
strOutdate1 is a date and has been tested and proven to be OK.
When I run the same code against my sql2005 db, the query returns nothing.
Do I have to handle something differently for sql2005.
 
Thanks
Peter 
 
 
 

View 2 Replies View Related

Can A SQL2005 DB Be Hosted On A SQL2000 Server ?

Jan 2, 2007

Hi,
I'd like to host a small website created using VS2005 EE with a company that offers just SQL2000 DB support.
My question, can i use the DB created by VS site manager as a DB to the site, if not, is there any alternatives.
Thanks.

View 1 Replies View Related

SQL2000-SQL2005 Connection String

Jun 4, 2008

Hi all,
I'm new to SQL and I am trying to move from SQL 2000 to SQL 2005 on another server. When I change my connection string from the old IP to the new one(with SQL 2005) my web app crashes. Here is the command string I need to change:

<add key="ConnStr" value="data source=myoldIP;initial catalog=gt-test;uid=myuserid;pwd=mypassword;" />

I have copied the old database exactly the way it was to the new one. I need to change myoldIP to my new one but when I do it crashes... Any ideas?
Thanks in advance for your help
-Will

View 20 Replies View Related

Accessing SQL2005 As A Second Instance To SQL2000

Aug 2, 2006

I'm having trouble accessing SQL2005 Standard Edition as a secondinstance of SQL Server where the first instance is SQL 2000 EnterpriseEdition. I installed SQL 2005 as a named instance "SQL2005". Theserver is running Windows 2000 SP4 ON A 32-Bit machine. When I look inServices I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)services there. I went into SQL Server Configuration manager anddisabled the named pipes protocol and only have TCP/IP and by defaultShared Memory enabled to rule out that named pipes is causing theproblem. In SQL Server Configuration Manager I created an alias"computername$SQL2005" and was able to connect to database engine"computernameSQL2005" through SQL Server Management Studio WHILE ONTHAT BOX.But when trying to access the 2005 instance from another server(64-bit Windows 2003 SP1) I'm having trouble. On the 64-bit machine Idisabled the named pipes protocol for both "SQL Native ClientConfiguration (32-bit)" and "SQL Native Client Configuration". Under32-bit I created the alias "machinename$SQL2005" to point to the IPaddress of the 32-bit machine. In SQL Server Management Studio whentrying to connect to the database engine "machinename$SQL2005" itdefaults to the SQL 2000 instance. How do I access the SQL2005instance from a remote server?Any help would be appreciated.Mike

View 5 Replies View Related

Inner Join Queries Between Sql2005 And Sql2000

Feb 21, 2007

We have the follwoing environment

Sql2005 64 bit edition standard edition servers
Sql2000 Sp3 enterprise edition servers

when we try to access a table in sql2000 from sql2005 using linked server, the query also uses inner joins and max()

it gives the follwoing error

"Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'Col1017'."



The query looks something like this

select *FROM [X.X.X.X.].HRDE.dbo.PS_HX_LVE_FRM_SRCH A, [X.X.X.X.].HRDE.dbo.PS_NAMES B
WHERE A.EMPLID = B.EMPLID
AND A.HX_LEAVE_STATUS = 'PND'
AND B.EFFDT IN (SELECT MAX(EFFDT) FROM [X.X.X.X.].HRDE.dbo.PS_NAMES WHERE EMPLID = A.EMPLID)




View 4 Replies View Related

Returning A Databse From Sql2005 To Sql2000

Feb 20, 2008

Dear All,

I had a database file running under sql 2000 perfectly, because of a crash in the server we provided the customer with a temp server 2003 with sql 2005 until we fix his server, the procedure we did:
1. Detach the database from sql2000.
2. Copy the database to the new server the (filename.mdf & filename.ldf)
3. Attached the database to sql 2005.
4. The application is working fine.

Now we returned the original server with sql 2000 back to the customer, when we followed the same procedures above, meaning same steps but from sql2005 to sql2000 we got this error:
[[Error 602: Could not find row in sysindexes for database ID 5, object ID 1, Index ID 1. Run DBCC CHECKTABLE on sysindexes]]

We tried every thing export/import the database etc. nothing works.
Any advice please,
& thanks for the help.

View 4 Replies View Related

Cast - Differences Between SQL2000 And SQL2005

Sep 27, 2006

I came across a problem when migrating from SQL2000 to SQL2005.

Table1 - large table with multiple columns including columns (X21 varchar(50), Start datetime, Complete datetime)

View1 - Only returns integer values into the dwpId column

SELECT X21 AS dwpId, Start AS startDate, Complete AS endDate
FROM Table1
WHERE (X21 IS NOT NULL) AND (X21 LIKE '[0-9]%') AND
(ProjectID NOT LIKE '%_WI')

View2

SELECT cast(dwpId as int) as dwpId, startDate, endDate
FROM View1

In SQL2005, when selecting values from View2 an error is returned indicating that the statement failed when converting a varchar value to a data type int. In SQL2000, this same statement would return the appropriate rows from the view.

View 5 Replies View Related

Link Server From SQL2005 To SQL2000

Jul 2, 2007

I have the follwoing scenario, where the query returns an error, when we tried to upgrade oru production SQL2000 enviroment to SQL2005.

SQL2000 Env:
Microsoft SQL Server Developer Edition
Microsoft Windows NT 5.2 (3790)
8.00.2175 SP4

SQL2005 Env:
Microsoft SQL Server Developer Edition
Microsoft Windows NT 5.2 (3790)
9.00.3054.00

Notice the removal of the two RTRIMs.


Any help welcome
Gertus


Current Connection was on the SQL2005 machine:
Remote Link Server SQL2000 SP4

Working:
SELECT * FROM Entity -- on SQL2005
WHERE
(EntityRef) + Source NOT IN
(SELECT (ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY) --this is on sql2000
AND Source = 'EU'

Not-working:

SELECT * FROM Entity-- on SQL2005
WHERE
RTRIM(EntityRef) + Source NOT IN
(SELECT RTRIM(ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY)
AND Source = 'EU'



Error :: >>

OLE DB provider "SQLNCLI" for linked server "MRIEU" returned message "Cannot create new connection because in manual or distributed transaction mode.".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "SELECT TOP 1 1 FROM "MRIOB"."dbo"."ENTITY" "Tbl1005"" against OLE DB provider "SQLNCLI" for linked server "MRIEU".

View 2 Replies View Related

Import MDF And LDF Backup From SQL2000 To SQL2005

Oct 26, 2006

hi,

i have six files ( 3 MDF and 3 LDF) from a backup of a MS SQL 2000 server.

I installed MS SQL 2005 and wanted to Restore these Dataset. but they cant be accessed. doesnt SQL 2005 support these files ?

How can i use these files to access them with SQL 2005 ?





Thx,

View 1 Replies View Related

SQL2000 Distributer Move To SQL2005

Oct 26, 2006

Hi there,

Need to plan moviing a SQL2000 distributor (which is also the publisher) to a SQL2005 distributor (64bit).

Problem is this needs to be done without a snapshot as the databases are a couple of hundred Gb and there is about 15 subscribers (including remote sites on a slow link)

What would be a recommended process for this, according to some searches it seems published DB goes to readonly/dbo mode, once log reader/distro agents have delivered all they are stopped. Replication is reconfigured on the pubication to point to the new SQL2005 boxes, and replication/subscriptios are readded/started again without snapshots.

I read somewhere that there were problems with the SQL stored procs being called differently between 2000 and 2005 for the insert/del/update, something like the owner (dbo) becoming part of the procs being called in 2005 (ie sp_MSins_dbo etc instead of dbo.sp_MSins). Can someone confirm if this is the case?

This will ofcourse be labbed before being moved, so just wanted to get some idea of what one is in for.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved