Guidelines For Readiness Testing For Moving SQL2000 DBs To SQL2005?
Oct 17, 2006
Hi,
We have a number of databases running on our SQL2000 Servers. The databases use Stored Procedures which we call from our VB.NET apps, developed in-house. We now wish to upgrade our servers to SQL2005.
In the past, when upgrading from VS2002 to 2003 and then 2005, we have developed and executed Test Plans to ensure that the code upgrades properly. Microsoft even supplies FXCop and other tools to help. I now wish to develop and execute a similar Test Plan to ensure that our databases will work as required.
Does Microsoft supply any similar tools or guidelines for testing that databases written for SQL2000 will work as required on SQL2005? I see the Upgrade Wizard to examine the Server but is there a similar tool for testing databases? Are there any guidelines on the tests to include in such a Test Plan?
Thanks
View 3 Replies
ADVERTISEMENT
Mar 28, 2008
I am planning to move the sql server 2000 database to the sql server 2005 database. I have to restore that by using the back up copy. Do I need to follow any special instructions or just like moving from one server to another. Could some body help with this. Thanks!
View 5 Replies
View Related
Oct 22, 2007
Hi
I have a (possibly) common position where half of our IT department is SQL 2005 and the rest is SQL2000. For myself, having to work in a SQL2000 environment and needing data from a SQL2005 Cluster I came up with this solution.
Also, to alert me when the process starts and completes since it is part of a scheduled process, I have it do a RAISERROR with logging to record entry and exit times. In addition, this runs out a series of PRINT statements that lets the operator know what table is currently being worked on.
Of course, any suggestions for speeding this up would be helpful!
What I have found that works for getting data (albeit slow) from SQL 2005 down to SQL 2000 is to script a fairly simple copy process -
-- it is actually pretty easy to follow if you just read the code......
-- first, we find all the views which are present, so they can be ignored when copying the raw data
CREATE TABLE #VIEWS (TABLE_NAME NVARCHAR (255) )
INSERT #VIEWS
SELECT TABLE_NAME FROM
OPENDATASOURCE(
'SQLOLEDB',
Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' )
.SQL_SIS_SYSTEM.INFORMATION_SCHEMA.VIEWS
-- now, we get the actual tables with data
CREATE TABLE #TEMP (TBL_SCHEMA VARCHAR (12), TBL_NAME VARCHAR (255), RECCNT INT )
INSERT #TEMP
SELECT TABLE_SCHEMA , TABLE_NAME FROM
OPENDATASOURCE( 'SQLOLEDB', 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' )
.SQL_SIS_SYSTEM.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM #VIEWS)
-- then, we start copying tables over - now we tag the ones which are populated with at least 1 row.
-- the first cursor loop gets all table names, the second will find row counts of source tables.
-- this segment uses (ugh) cursor to loop through and gather all of the data. This cursor is at the table name level - not
-- processing anything, and is used only to find tables which have a rowcount > 0
-- believe it or not, the cursors run pretty darn quickly since they arnet doing any calculations - just finding
-- tables with row counts > 0
SET @QUOT = CHAR(39)
SET @LBRAKT = '['
SET @RBRAKT = ']'
SET @IUT = 'IsUserTable'
SET @ODBC_CMD ='(' + @QUOT + 'SQLOLEDB' + @QUOT + ',' + @QUOT + 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT +
').SQL_SIS_SYSTEM.dbo.'
PRINT 'BEGIN TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) )
DECLARE GETEM CURSOR FOR
SELECT TBL_SCHEMA, TBL_NAME FROM #TEMP
OPEN GETEM
FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'UPDATE #TEMP SET RECCNT = ' +
'(SELECT COUNT(*) FROM OPENDATASOURCE(' +
@QUOT + 'SQLOLEDB'+ @QUOT + ',' + @QUOT +''Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT +
').SQL_SIS_SYSTEM.' + @TBL_SCHEMA + '.' + @TBL_NAME +')' +
' WHERE TBL_NAME = ' + @QUOT + @TBL_NAME + @QUOT
EXEC (@SQL)
FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
END
CLOSE GETEM
DEALLOCATE GETEM
PRINT 'FINIS TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) )
DECLARE @PLIN VARCHAR (80),@LFT_PLIN VARCHAR (20),
@RT_PLIN VARCHAR (20), @TLIN INT
PRINT 'BEGIN TABLE CHECK AND LOAD CURSOR.'
DECLARE FETCHIT CURSOR FOR
SELECT TBL_NAME, RECCNT FROM #TEMP WHERE RECCNT > 0
OPEN FETCHIT
FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'CHECKING ' + @LBRAKT+ @TBL_NAME + @RBRAKT
IF NOT EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id
(@LBRAKT+ @TBL_NAME + @RBRAKT )
AND OBJECTPROPERTY(id,''+@IUT) = 1)
BEGIN
SET @LFT_PLIN = 'OBJECT' + SPACE(5)
SET @RT_PLIN = SPACE(5) + 'NOT FOUND. BUILD ' +
CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.'
SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT
SET @TLIN = LEN(@PLIN)
SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN
PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) )
PRINT @PLIN
PRINT 'COPYING DATABASE TABLE.'
SET @SQL = ' SELECT * INTO ' + @TBL_NAME +
' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME
EXEC (@SQL)
PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) )
END
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id
(@LBRAKT+ @TBL_NAME + @RBRAKT )
AND OBJECTPROPERTY(id,''+@IUT) = 1)
-- #TEMP (RECCNT) CONTAINS ROW COUNT OF SOURCE SYSTEM.
-- COMPARE TO ROW COUNT OF LOCAL TABLE
BEGIN
SELECT @DEST_RECCNT = RECCNT FROM #TEMP
WHERE TBL_NAME = @TBL_NAME
IF @DEST_RECCNT = @RECCNT
BEGIN
PRINT 'TABLE row COUNT MATCHES. BYPASSING REBUILD.'
END
IF @DEST_RECCNT <> @RECCNT
BEGIN
SET @SQL = 'DROP TABLE ' + @TBL_NAME
EXEC (@SQL)
SET @LFT_PLIN = 'OBJECT' + SPACE(5)
SET @RT_PLIN = SPACE(5) + 'REBUILD.' +
CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.'
SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT
SET @TLIN = LEN(@PLIN)
SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN
PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) )
PRINT @PLIN
PRINT 'COPYING DATABASE TABLE.'
SET @SQL = ' SELECT * INTO ' + @TBL_NAME +
' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME
EXEC (@SQL)
PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) )
END
END
FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT
END
CLOSE FETCHIT
DEALLOCATE FETCHIT
PRINT 'FINIS TABLE CHECK AND LOAD CURSOR.'
XIT:
RAISERROR (50002,010,1) WITH LOG -- FLAG COMPLETION TIME IN SQL LOG
SET QUOTED_IDENTIFIER OFF
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 7 Replies
View Related
