We're about to upgrade/re-build our SQL server box.
We have 2 apps which use the server (both fairly small), one uses NT security - the other uses SQL security.
My question is, apart from our 2 databases, what else should we backup (and restore onto the new clean machine) so as to preserve the user logins for the app that uses SQL security (the other one's dead simple)? The app itself maintains the user details and creates the SQL logins (with passwords etc.) but I can't find a way to 're-create' them.
Does anyone know how to upgrade a sql driver. Or where to get the driver dll from. The client machine is running 3.70.06.23 and needs 3.70.08.20 to connect to the sql server!
All the documentation I have read talks about upgrading from SQL Server 6.x. I presume I can use the upgrade wizard to upgrade my 6.0 system directly to 7.0 without having to upgrade to 6.5 first.
Any thoughts, comments or help would be much appreciated.
at the moment I have the following problem: We have installed a version of the MSDE 2000 Sp3 with our software since 2005. Now we install SQL Server Express 2005 in order to use the latest version and to be compatible with Vista. We have never had any problems with the MSDE, so it was no problem that we installed the german version (by mistake) because there never was any output at installation time. When we now upgrade the existing MSDE installing the new version of our software, there are often problems and there are error messages, unfortunately in german, which cannot be read by most of our international users.
The problem is, that there seems to be no possibility to upgrade the existing (german) MSDE with the english version of SQL Server Express 2005. My question is, if it is possible to upgrade the german MSDE to the english SQL Server 2005 to enable our international users to read the error messages at installation time in english?
We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.
However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors
1)
Error messages:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403) Get help: http://help/MSSQL_REPL-2147199403
Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207) Get help: http://help/207
Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207) Get help: http://help/207
The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368) Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036) Get help: http://help/21036
looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1
this is the stored procedure executed by sp_MSensure_single_instance and @retcode = -1 and thus cannot obtain exclusive lock on resource
so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources
i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work
Hi, I'm planning an upgrade of SQL 7.0 on NT 4.0 to SQL 2000 and of course I will attempt to upgrade the OS to Windows 2000 first. Can anyone tell me of any issues I may run across during this upgrade? Can I assume that SQL 7.0 will run ok on Windows 2000?
I'm upgrading a pair of active/passive cluster nodes running Windows 2003 Enterprise and SQL 2000. I'm going from 4 CPUs to 8 CPUs per server. What, if anything should I expect from SQL as far as licensing issues when performing this upgrade? We are using the per CPU license method and have the addtional licenses.
I'm running Windows XP SP2 and I had SQL Server 2005 running on my box. I needed to run the SP1 upgrade but it doesn't seem to work. I ran Windows Update and the list showed I need to install SP1 and it looked like it ran fine but when I checked the version after the upgrade it hadn't upgraded to .2047. I went back to Windows Update and it said I still needed SP1. So I tried downloading SP1 and running it myself still no luck. So then I decided to uninstall SQL Server 2005 completely, reinstall it and try the SP1 again (using Windows Update) and nothing in fact I'm now getting the error...
"Error Code: 0x2BC4"
...which I haven't been able to find anything on. Does ANYONE have any ideas on what might be happening?
i am trying to set up an automatic setup routine that will upgrade msde to server express and install our program or to leave a previous server express installed and just install our program.
i have made a template.ini file and everthing seems to go well, but the upgrade does not happen.
A lot of vs.net 2005 documentation talks about how asp.net 2.0 developers can easily build web apps using SQLX and then easily upgrade to SQL Server 2005 when we go to production.Can someone point me to the details? How exactly do I take an .MDF file from my development environment and ??import/attach/restore?? it too a full blown SQL Server 2005.TIA,g
I installed a SQL SERVER 6.5 on machine A. And restored the database db1 from a 6.5 backup to machine A. Now, I installed SQL SERVER 7.0 DEsktop version on machine A. Thereafter, when I try to upgrade the 6.5 server to 7.0 through upgrade wizard, I find no reference to DB1 database in the SQL SERVER 7.0 Enterprise Manager after the upgrade. Though, its there in 6.5 Enterprise Manager. Also, through upgrade wizard, I do not find any way of specifying that what databases I want to upgrade.
I am about to upgrade my main database server (5 db's - largest 16Gb) from NT 4 SP 6a / SQL Server 7 SP3 to Windows 2000 SP 2 / SQL Server 2000 SP 2
I am planning to detach the db's, backup to tape a few times and then totally trash the server, rebuilding it with the new software, restore the db's from tape and the reattach the db's.
Any reason I should not use this method and can folk advise the best practice way of achieving this?
Currently our system is .NET workframe 1.1 / Visual.NET 2003 plus SQL Server 2000. The developer will upgrade to .NET workframe 2.2. Should I have to update database to SQL Server 2005? And some reasons please.
Has anybody had any experience of this??We have over 2000 stored procs to convert from Watcom SQL to SQLServer 2000. Any automated tools would be much appreciated!!Also any known issues.
I have upgraded my server from SQL Server 2000 to SQL Server 2005. After upgrading, I noticed that an important option in a specific application isn't working, giving a generic error message ("Cannot update the database").
Using SQL Profiler, I found that the query that fails includes the expression:
Code Snippet DateAdd(DateInteval.Day, ..., ...)
And the error message is: "'DateInterval.Day' is not a recognized dateadd option".
This query:
Code Snippet SELECT DateAdd(DateInterval.Day, 2, getdate()) Works on SQL Server 2000 servers, while failing on SQL Server 2005 servers, with the same error message as above.
However, this query:
Code Snippet SELECT DateAdd(Day, 2, getdate()) Works on both SQL Server 2000 servers and SQL Server 2005 servers.
I searched for documentation of this problem, and couldn't find any. The code for the original application is lost, so changing it is impossible.
We have 2 SQL servers in a failover cluster environment. SQL1 and SQL2. Currently SQL1 is the primary in the cluster, and we need to upgrade from Service Pack 3 to Service Pack 4.
Our setup:
Both servers are Windows 2003 Server Enterprise Edition. Both servers have SQL Server 2000 Service Pack 3.
My question is:
Which upgrade plan do I take? Do I:
1. Upgrade the backup (offline) server in the cluster (SQL2) first, reboot, then failover from SQL1 to SQL2? Then do the other server?
2. Upgrade the primary (online) server in the cluster (SQL1) first and see if it replicates to the backup?
I have a SQL 2005 cluster using 2 HP DL 380 32bit servers connected to an HP MSA500 disk pack via SCSI. I need to upgrade the servers to 64 bit but I am still going to use 32 bit windows server.
My question is, has any tried this before? If so, will it work and what is the best way to go about it. Should I take down the secondary SQL server, then replace it, then add it to the cluster, then fail over to it, then replace the other?
I restored a 2000 database back up in to my 2005 database and it works fine. THe only problem is that I cannot create diagrams.
I get the following message:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
I have checked the db_owner and that is a valid owner.
I changed a few times to different owners and still could not install the diagrams..
I have to upgrade and move SQL Server 2000 databases from a platform using Server 2000 to one running SQL Server 2005. The server nameinstances will need to be the same so that a number of third party applications and front end systems that connect, and are outside of my control, will not need any data source changes. The servers will be on the same network and can't co-exist with the same name so I guess that I have to go "big bang".
Does anyone have any suggestions for a strategy that I can use? Does anyone know of any issues that I should be aware of?
I have run the SQL Server 2005 Upgrade Advisor and it tells me the databases should upgrade OK with a few minor issues that can be fixed after.
I had a SQL2000 DB Server with a simple database. I did an "in place" upgrade to SQL2005 expecting the DB Server and databases to upgrade. However, now when I run SQL Server Management Studio and enter the query:-
I had two Window NT 4.0 Server, say A and B... We recently upgraded from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for Computer B, I installed SQL Server 2000 from scratch. These two have a same RAM(512MB) and CPU speed...so the setup for hardware is almost identical...
For server A, there are around 10 connection to this SQL Server... For server B, there are also around 10 connection to this SQL Server...
The database for SQL Server B is a copy from SQL Server A.... I restored the database on SQL Server B from the backup dump of Server A...
We have a store procedure called usp_GetMemo(ID Interger) ... we feed ID number to this store procedure and return its return Description... this table has ID column and it's Non-Cluster index, allow the duplicate row... there are 1.7 million records for this table...
Here is problem... whenever i execute more than 100 times for this store procedure(usp_GetMemo(ID)) continuously, the CPU usage of Computer A is 3 times bigger than computer B....
I did run the command "DBCC DBReindex" , "Update Statistics" for this table on computer A, but did not make any difference...
Keep in mind that Computer A has been upgraded to SQL server 2000 while Computer B installed from scratch... would that make difference somehow?? Before the upgrading, the CPU usage of computer A is the same as Computer B...
Any help will be really appreciated, Thanks alot, Kim,
I have attached the SAN, configured the Windows Clustering and I'm ready for SQL. SQL Server 2005 was already on this machine as a stand alone. I removed all of the software and I'm reinstalling SQL. The cluster option is not enabled. Any ideas why?
I don't have any idea about upgrading databases.I am been given this task. It would be great if you let me know what are all the things to be considered upgrading the sql server 2000 databases to sql server 2005.
Hi I currently have this edition of SQL server on my laptop:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
It is running on SQL Server Management Studio 2005.
I wish to upgrdade to Developer Edition (as my laptop won't support Enterprise as it's running on Windows XP).
I have some questions, which I would be grateful if somebody could help answering:
1- will all my database remain as they are? 2- Will I loose any functionality? 3- Will it upgrade or intstall a separate edition? 4- Can anybody recommend where to purchase Developer 2005 edition? 5- I currently have 2gb capped database limit on my version, will I get a no capped limit? 6- Will the 2gb Capped limit increase to uncapped once I install (i.e. can i use my previous 2gb database and add more data to above 2 gb)
The main issues are that I need an uncapped limit for my DB's and that I will keep my existing db's.
What are the pro's and con's of these Sql Server 2005 Editions: WorkGroupStandard I am having to choose between the two for upgrade, and would be interested to know what your experience would tell me. Though it would be entertaining to go with the victor of a google fight(http://googlefight.com/index.php?lang=en_GB&word1=sql+server+2005+workgroup&word2=sql+server+2005+standard), I should probably be responsible about this.
I'm trying to upgrade a Microsoft SQL 2000 (8.00.760 SP3) server to SQL 2005.
It€™s a standard install, std version, not very big databases, default instance, plenty of memory etc. OS is a 2003 server recently upgraded from 2000. All patches etc are installed.
Upgrade was going fine and had got approx 90% through, had started the 2005 db service and converted the databases before it got the following error:
SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Invalid connection string attribute Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
The 'For help' link returned the helpful... €˜We are sorry but we can€™t help€™...
Worked through http://msdn2.microsoft.com/en-us/library/ms143698.aspx but not 100% applicable and didn€™t resolve.
Partially rolled back, rebooted and resumed install but failed in the same place. Ended going back to a backup snapshot so is now back running as SQL 2000.
Excerpt from install log is below...
Thanks in advance for any assistance.
<Finished SQL statement: Wed Oct 10 21:25:21 2007> <Started SQL statement: Wed Oct 10 21:25:21 2007> BEGIN DECLARE @GroupName AS nvarchar(4000) SET @GroupName = QUOTENAME(N'PROPMAN-DATASQLServer2005MSFTEUser$PROPMAN-DATA$MSSQLSERVER') EXECUTE(N'grant execute on sp_fulltext_getdata to ' + @GroupName) END <Finished SQL statement: Wed Oct 10 21:25:22 2007> Service MSSQLSERVER is being stopped at Wed Oct 10 21:25:22 2007 Service MSSQLSERVER with parameters '' is being started at Wed Oct 10 21:25:34 2007 Attempt to start service when it is already running SQL service MSSQLSERVER started successfully waiting for SQL service to accept client connections Service MSSQLSERVER started at Wed Oct 10 21:25:40 2007 <EndServerDowntime Wed Oct 10 21:25:40 2007> Loaded DLL: C:WINNTsystem32Odbc32.dll Version: 3.526.3959.0
sqls:equenceInstaller::launchAction Staring action SqlScript SqlUpgradeScriptHlpr Connecting to SQL Server ExecuteUpgradeSqlCommands sqlConnect Originial error was 8007ea62 (60002)
Error Code: 60002 MSI (s) (5C!20) [21:26:20:164]: PROPERTY CHANGE: Adding SqlUpgradeMessage property. Its value is 'SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Invalid connection string attribute Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.'. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Invalid connection string attribute Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online. Loaded DLL: C:WINNTsystem32Odbc32.dll Version: 3.526.3959.0
Help please... We have developed a system for a medium size company in NYC using SQL Server 2000. We are about to go live and thought it may be a good idea to upgrade first from SQL Server 2000 to 2005.
I believe standard edition would be sufficient but I am not 100% sure... There isn't a whole lot of data but high availability is crucial. I was thinking of a 2-node fail over cluster setup and/or mirroring. We need only one instance of SQL Server with about 30 client machines accessing the same databases.
Any thoughts/comments as far as: Standard or Enterprise? What type of licensing? Support agreement? What about the best setup for highest availability? (fail over cluster vs. mirroring vs. replication)
We shall be taking a bunch of 7.0 instances and moving/upgrading to a SQL 2000 cluster server. I was thinking of creating new named instances on the 2000 cluster and upgrading each 7.0 server to it's respective named instance. Also thought of using the 2000 copy database wizard; I was told this didn't always work. Anyone hear of problems with this? Thanks
I recently upgraded from SQL Server 2000 to SQL Server 2005. I have Oracle databases I need to share data with. I was using the Heterogeneous Services (hsodbc) from Oracle and connected to SS 2000 fine. Since I've upgraded, I can't use hsodbc to connect to my SS 2005 database. I get the following error:
ORACLE GENERIC GATEWAY Log File Started at 2006-09-06T09:40:07
My upgrade of SQL Server 2000 to SQL Server 2005 is being blocked as follows: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding FTCATALOGPATH DEPRECATEDSP FTCATALOGNAMERESTRICTION FTNONPERSISTEDCOMPCOL
Since Upgrading from SQL Standard Edition to SQL Server EE we have been unable to re-attach the database files that we detached before the upgrade, to SQL Server Management studio. We get an error message that reads:
TITLE: Microsoft SQL Server Management Studio ------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
