Commonly, to upgrade a database numerous scripts need to be applied. These scripts make both schema and data changes. Often, the data changes need to be interleaved with schema changes (for example, it is not possible to create a unique index until the column has been populated with unique values). The scripts will contain numerous GO commands as required for the definition of stored procedures, etc.
I am wondering what is the best way to do error handling with such scripts. I use SET XACT_ABORT on at the beginning, but when one batch fails, it just skips on to the next GO command. And of course, it rolls back my transaction having the side effect of running the subsequent batch without a transaction. Is there any way to avoid this? I would like a solution that would let me splice together scripts that I get from various different developers (and in some cases automated tools) with minimal difficulty even though the coding styles may be a little different.
The possible solution that comes to mind is RAISERROR with a severity greater than or equal to 20. I'm not thrilled that I have to use WITH LOG on this command. I tried the KILL command, but that won't let me terminate my own SPID.
Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
The following version and editions have been verified.
1. .NET 2.0 installed
2. Windows XP SP2
3. MSDE 8.00.2039(SP4)
4. all MSDE databases are owned by sa
5. Instance and SQLAgent running under user that is member of Administrators
What are the possible reasons this error is occurring?
Our application installation setup needs to be able to detect:
Is SQL Server 2005 Express Installed?
If false, run the SP2 Installation Is SQL Server 2005 Express or SQL Express SP1 Installed?
If false, run the SP2 Installation with Update Switch Is SQL Server 2005 (not Express) Installed
If true, abort installation with a mesage.
Can you direct me to some documentation about testing for these environments or just if someone has some scripts that will run in InstallShield, that would be great.
ALSO, can you direct me to information about using the command line update switch for the Express SP2 Installer?
We've restored a database from SQL2000 SP3a and into SQL2005 SP2.
When it restored through a TSQL restore, the message is saying below. Can someone explain this numbering between 539 and 611? Is this some sort internal versioning or something? That numbering doesn't corresponding the SQL version. ?!?!
Thanks
5 percent processed. 10 percent processed. 15 percent processed. 20 percent processed. 25 percent processed. 30 percent processed. 35 percent processed. 40 percent processed. 45 percent processed. 50 percent processed. 55 percent processed. 60 percent processed. 65 percent processed. 70 percent processed. 75 percent processed. 80 percent processed. 85 percent processed. 90 percent processed. 95 percent processed. 100 percent processed. Processed 12042832 pages for database 'XXXXXX55', file 'XXXXXX55' on file 1. Processed 37 pages for database 'XXXXXX55', file 'XXXXXX55_Log.ldf' on file 1. Converting database 'XXXXXX55' from version 539 to the current version 611. Database 'XXXXXX55' running the upgrade step from version 539 to version 551. Database 'XXXXXX55' running the upgrade step from version 551 to version 552. Database 'XXXXXX55' running the upgrade step from version 552 to version 553. Database 'XXXXXX55' running the upgrade step from version 553 to version 554. Database 'XXXXXX55' running the upgrade step from version 554 to version 589. Database 'XXXXXX55' running the upgrade step from version 589 to version 590. Database 'XXXXXX55' running the upgrade step from version 590 to version 593. Database 'XXXXXX55' running the upgrade step from version 593 to version 597. Database 'XXXXXX55' running the upgrade step from version 597 to version 604. Database 'XXXXXX55' running the upgrade step from version 604 to version 605. Database 'XXXXXX55' running the upgrade step from version 605 to version 606. Database 'XXXXXX55' running the upgrade step from version 606 to version 607. Database 'XXXXXX55' running the upgrade step from version 607 to version 608. Database 'XXXXXX55' running the upgrade step from version 608 to version 609. Database 'XXXXXX55' running the upgrade step from version 609 to version 610. Database 'XXXXXX55' running the upgrade step from version 610 to version 611. RESTORE DATABASE successfully processed 12042869 pages in 999.897 seconds (98.665 MB/sec).
We have Sqlsvr 6.5 under NT4. We want to upgrade the database toSqlsvr 2000 under windows 2K. Is there any way we can do this bycopying the database to the win2K computer?? At any rate wouldsomeone explain how to go about updating this. I know nothing aboutsqlsvr anyversion but do need to know.TIA
I tried the Upgrade method on SqlCeEngine, but that throws an exception if the database has already been upgraded. And because the exception type is generic, I have no reliable way to know if it was caused by corruption or because it is too stupid to ignore requests to upgrade from 3.5 to 3.5.
If someone can show me a way to get the database version, this would be easy to solve.
I am the IT vendor for a law firm that is using a product by HummingBird called DocsOpen, now called DM OpenText 5.2
We are upgrading the office and the SQL server that currently runs the database for the DM program is an old Compaq box running NT 4, SQL 7. No way to upgrade that native, for sure.
We have a brand new monster Dell running SQL 2005 and we need to move the database from the old server to the new server.
Can this be done? I don't have SQL 2000 as an interim. I have read the detach db document but am afraid the database is not compatible with 2005.
I am trying to develop a way to get this done in the most effective manner possible without losing data.
Can I offer anymore information or specifics? Soliciting a Microsoft SQl Engineer for a paying gig is not a bad option either, can't seem to find one in Hawaii.
If you have any hard info, I would certainly appreciate it.
Ok is this possible?? I tried to import data thinking it should just work - ha ha! Threw lots of errors about table and column mapping instead. Hodgehegs
We have an application with database with Sortid 50 (binary) in SQL 6.5 We're planning to upgrade to SQL2000 and use the default Sortid 52.
Can we change it when using the SQL2000 upgrade wizard ? I tried and got a warning msg "code page / sort order configuration between import and export SQl server do not match or not supported. A successful converison cannot be guarenteed"
Or we should bcp the database out from the old SQL 6.5(soritd 50) and then bcp in another new SQL 6.5(with sortid 52),then rebuild all schema, then migrate this new SQL 6.5 to 2000 with migration wizard as normal ?
Or we do the migration first, then use copy database wizard to copy it into another SQL2000 database with different sortid, i.e 52 ? (As I recall SQL2000 support databases with different character set and sorting order ) Anyone has any thoughts ?any pros and cons for various ways of converting it, Thanks. Anthony
I have written some software using .NET 2.0, a WinForms app, which uses SQL Server Express as a database. Although I've done my best to set up the database to take into account all of the features, etc., after releasing the first version, I need to make some changes to the database schema, but want to do so without wiping out the entire database for existing users. How do I go about upgrading the database schema for users who are already using the software? I'm thinking I should start by writing a helper app to do so whose sole purpose is to upgrade the schema. The app would be run at install-time, but I don't have any idea how to actually upgrade the schema. I am using C++/CLR .NET, but examples in other .NET languages are more than welcome. Any help is greatly appreciated. Thanks!
I have a few databases running a sql2000 and the time as come to move them onto sql2005. Which would be the best route to take.
If I can think back to older version it was just a simply job of doing a backup in it's current version then restore in the new version? and upgrade was taken while restoring.. I could be wrong but any help !!
I have installed Reporting Services 2005 on SQL Server 2005 SP2. Then when I try to connect to the Reporting Services i get the error "Invalid Database Version". Any suggest?
I've just upgraded my app from .Net 2.0 in VS 2005 to 3.5 in VS 2008, but now I can't open my SQL CE database any more. It falls over on the connection.Open() method, saying I need to run Compact /Repair if this is 3.0 or 3.1 version database (which it is, or was). So I run the compact/repair (full compaction) and it doesn't help at all. I also tried running the upgrade.exe tool that comes with SQL Server CE, but when I try to run it from the command line, it tells me it can't run in Win32 mode! I didn't think it was! Does that mean I can only run it from a DOS prompt before loading the OS or something? There's something on MSDN that says you can upgrade by opening a connection to the CE database in Server explorer and then you'll get a dialog that will upgrade the database for you. But that doesn't happen - it just happily connects. Anyone know what the hell is going on here?
Update: I just noticed in the excpetion message that the "requested version" is 3004180, i.e., it seems to be asking for a 3.0 version database and getting a 3.5 version one, but I can't work out why. Here's the exception, with stack trace etc snipped:
System.Data.SqlServerCe.SqlCeException was unhandled Message="You are trying to access an older version of a SQL Server Compact Edition database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact Edition 3.0 or later database, run Compact / Repair. [ Db version = 3505053,Requested version = 3004180,File name = F:\dotNET projects\Word Learner\bin\Debug\Words.sdf ]"
What does the shrink command exactly? I start the shrink command in the Enterprise Manager of SQL7/2000. Does it include re-indexing of all tables? And is it the same command as auto-shrink can be selected in the db properties?
Im using MS SQL7 as database software ,but now I plan to upgrate SQL7 to SQL2000 . I would like to know that are there any affect on the old database that already use with sql7 after I upgrade to MSSQL2000 ?
Hi, I am preparing to upgrade my database to 7.0. While doing DBCC on my existing database (in version 6.5), the process completed with status fail. Below is the message given:
Processed 64 entries in the Sysindexes for dbid 7. Found 7 bad entries in the Sysindexes. DBCC execution completed. If DBCC printed error messages, see your System Administrator.
Should I just ignore this message and proceed? If I were to clean up the bad entries, how do I proceed?
Hello All: I'm new to this forum and new to the upgrade process for SQL2K5. I would like to get this upgrade moving. However, I running into a few problems. Right now I am attempting to use the Copy Database Wizard. SQL2K5 and SQL2K are installed on different servers. I made a copy of the tables for the database I am converting and this is the one I am trying to copy over. The process runs fine until it gets to the SQL Server Agent Job. It fails on this step and prompts to check the event log. Does anyone know what might cause this?
Right now I'm looking for the easiest solution to test the data. Is the copy database wizard the best approach? Can anyone recommend another one? Maybe backup/restore.
I used export from SQL2K to SQL2K5 and that worked, but it brought over the dbo prefixes.
Any help is greatly appreciated. Or at least a shove in the right direction.
I'm not sure this is the exact forum in which to ask this question, so if there is a more appropriate forum, please point me in that direction.
Basically I'm looking for advice and best practices for dealing with an upgrade plan for a hosted database. Here is the situation: - Hosted software application (.Net) - Uses SQL Server 2005 - When we upgrade the software application to a new version, there will be schema and data changes that need to be applied to the database - For the software application, we have considered upgrading an offline machine and then switching old one instantaneously. - For the database, I'm trying to figure out if we can keep everything live for upgrades, or if we should have a small amount of scheduled downtime to upgrade.
So we have identified 2 basic directions: 1) Keep the database live and apply the new script changes just after the application software is upgraded. This means zero downtime, but we'd have to design and test our application to be backward compatible, so that the new version of the application code could handle both the new schema design, and the old schema design. It seems that this option would add a huge amount of complexity because we would essentially be testing a 3rd app/schema combination. 2) Take a small amount of scheduled downtime and upgrade the application code and database all at once.
Obviously option #2 is the most desirable from a development process point of view, and #1 is the most desirable option from a business point of view (zero downtime).
So, I'm looking for some advice on this. Do most people take the whole system down? Are there 100% live systems that deal with this? Are there other approaches I'm not considering?
Any information would be greatly appreciated. Thanks.
1. launch the DacIESvcCli.exe at specified time, which in-turn calls the service (southeast asia). 2. the bacpac is created and uploaded to the specified storage
However, after upgrading the Server to V12, it failed to create bacpac file in the storage, there are no errors/no logs/no information.
We utilise SQL scripts, executed via sqlcmd.exe, to upgrade the schema and common data of our database(s) when we deploy new versions of our software to a production site. At the moment we simply wait until after hours to do the upgrade and ask nicely for all users to not use the system for a while.
Obviously, asking nicely doesn't always work, and there is also the issue of scheduled server tasks and web services / web sites that operate against the database 24/7.
What are our options for putting the entire server (or preferrably just one database) into a semi-offline state so that users and services cannot connect to it while our script connects and performs the upgrade? I imagine there may be several approaches each with their own pros and cons.
If you could point me in the right direction or perhaps mention what strategy has worked for you, it would be greatly appreciated. We perform these upgrades from hundreds of kilometers away via VPNs and Remote Desktop so we can't just unplug the network cable :).
There is a great book on database refactoring that contains a comprehensive set or recipies on how to revise databases that are supposed to be always online and may have various clients that can't be upgraded at the same time. I guess this is a typical case with large databases and I would be surpised if Amazon stops their servers just to move a column from one table to another. The book describes necessary steps for such changes. Basically it's all about creating intermediate database schemas that would be used during transition period.
For example, if we need to move a column from one table to another:
Version 1. Table A columns: Name, Price Table B columns: Quantity, Date
Let's say we move Price to table B:
Version 2. Table A columns: Name Table B columns: Quantity, Date, Price
The book suggests an intermediate version:
Version 1_2. Table A columns: Name, Price Table B columns: Quantity, Date, Price Additional trigger that will synchronize "Price" columns between A and B.
Version 1_2 can be used by both clients written for version 1 and 2. Software developers don't need to rush their upgrades, transition can last months and include several changes.
This technique requires accuracy in version control management, but looks very good to implement non-interruptible database schema upgrade. I wonder if this is the only option available for data schema upgrade with no downtime. I can't think about anything else - it this how large data warehouses updata their databases?
Is there a way by which we can execute the backing up of database from the command prompt......
Suppose i need to do the below task. I can write a stored procedure and schedule it. But the situation demands to run from the command prompt. Is there a way out.
-- Create the backup device for the full MyNwind backup.
I am in the process of creating a windows installer. I am using SQL Server 2005 for my application. I have generated the script for my database. I want to run that script and create my database on the client machine (client has installed SQL 2005). Do you have any solution for this kind of issue?
When run an installer I can run a bat file. Can I create a bat file and create my database? Does SQL Server2005 provide any tool for this?
I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.
The same package works fine when the target is on Oracle database or a SQL Server database.
For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.
Any suggestion regarding this would be really helpful.
Following upgrade to SSRS2005, Reporting Services worked EXCEPT from within applications or from scheduled jobs. Running reports from application-generated URL€™s produced the following error: €˜An internal error occurred on the report server. See the error log for more details€™. These same reports, however, ran perfectly from within SSRS. After running them once from Reporting Services, they subsequently run without problem when called by applications or jobs.
Examples of these errors include the following (stack traces available if needed):
ReportingServicesService!runningjobs!13!5/27/2007-01:57:23:: i INFO: Adding: 1 running jobs to the database ReportingServicesService!chunks!f!05/27/2007-01:58:34:: e ERROR: LockSnapshotForUpgrade: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
ReportingServicesService!chunks!1a!05/27/2007-01:58:34:: e ERROR: GetChunkPointerAndLength: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
ReportingServicesService!chunks!11!05/27/2007-01:58:34:: e ERROR: ### SnapshotConverter(00d68151-85e5-4669-a0de-28ed81bd091c, True), System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
Log entries below correspond to the following attempts to run a specific report.
ENF_Comprehensive_Report 5/31/2007 9:43:37 AM rsInternalError (one of many user attempts to run from app after upgrade) ENF_Comprehensive_Report 5/31/2007 10:50:57 AM rsSuccess (first successful run after upgrade; run from within SSRS) ENF_Comprehensive_Report 5/31/2007 11:09:59 AM rsSuccess (first successful run by user from application)
It appears that after the upgrade Reporting Services was attempting to use non-existent or invalid chunks and snapshots to satisfy application or job originated requests. When first called from within SSRS, the old chunk was accessed but then appears to have been ignored, with subsequent calls running without a chunk. Our workaround for the problem was to manually run each of our 200+ reports from within SSRS to €˜initialize€™ them for applications and jobs.
Typical Application Request Failure for report: w3wp!library!a!5/31/2007-09:43:22:: i INFO: Cleaned 0 batch records, 0 policies, 4 sessions, 0 cache entries, 5 snapshots, 46 chunks, 0 running jobs, 0 persisted streams w3wp!library!a!05/31/2007-09:43:37:: i INFO: Call to RenderFirst( '/Enforcement/Historical/ENF_Comprehensive_Report' ) w3wp!chunks!a!05/31/2007-09:43:37:: i INFO: Returning old chunk for: (24788648-41c5-43d8-ba6b-409662211a37, 'CompiledDefinition', 0) w3wp!runningjobs!6!5/31/2007-09:44:29:: i INFO: Adding: 1 running jobs to the database w3wp!chunks!a!05/31/2007-09:45:37:: e ERROR: ### SnapshotConverter(24788648-41c5-43d8-ba6b-409662211a37, True), System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ßstack trace entries here€”> w3wp!library!a!05/31/2007-09:45:37:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ßstack trace entries here€”> --- End of inner exception stack trace --- w3wp!webserver!a!05/31/2007-09:45:37:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
First run of report from within Reporting Services following upgrade: w3wp!library!19!05/31/2007-10:50:30:: Call to GetPermissionsAction(/Enforcement/Historical/ENF_Comprehensive_Report). w3wp!library!19!05/31/2007-10:50:30:: Call to GetSystemPropertiesAction(). w3wp!library!19!05/31/2007-10:50:30:: Call to GetPropertiesAction(/Enforcement/Historical/ENF_Comprehensive_Report, PathBased). w3wp!chunks!19!05/31/2007-10:50:30:: i INFO: Returning old chunk for: (24788648-41c5-43d8-ba6b-409662211a37, 'CompiledDefinition', 0) w3wp!library!1!05/31/2007-10:50:31:: Call to GetSystemPermissionsAction(). w3wp!library!1!05/31/2007-10:50:31:: Call to GetPropertiesAction(/Enforcement/Historical/ENF_Comprehensive_Report, PathBased). w3wp!library!e!05/31/2007-10:50:31:: Call to GetSystemPropertiesAction(). w3wp!library!1!05/31/2007-10:50:56:: Call to GetPermissionsAction(/Enforcement/Historical/ENF_Comprehensive_Report). w3wp!library!6!05/31/2007-10:50:56:: Call to GetSystemPropertiesAction(). w3wp!library!6!05/31/2007-10:50:56:: Call to GetPropertiesAction(/Enforcement/Historical/ENF_Comprehensive_Report, PathBased). w3wp!library!6!05/31/2007-10:50:56:: Call to GetSystemPermissionsAction(). w3wp!library!6!05/31/2007-10:50:56:: Call to GetPropertiesAction(/Enforcement/Historical/ENF_Comprehensive_Report, PathBased). w3wp!library!6!05/31/2007-10:50:56:: Call to GetSystemPropertiesAction(). w3wp!library!1!05/31/2007-10:50:57:: i INFO: Call to RenderFirst( '/Enforcement/Historical/ENF_Comprehensive_Report' ) w3wp!webserver!1!05/31/2007-10:50:58:: i INFO: Processed report.
Subsequent application request for same report: w3wp!library!11!05/31/2007-11:09:59:: i INFO: Call to RenderFirst( '/Enforcement/Historical/ENF_Comprehensive_Report' ) w3wp!webserver!11!05/31/2007-11:10:00:: i INFO: Processed report. Report='/Enforcement/Historical/ENF_Comprehensive_Report', Stream=''
Environment: 32-bit WK3 Enterprise R2 SP2 with single instance of SQL2000 SP3 hosting SSRS2005 SP2 metadata; IIS running in 6.0 isolation mode; separate dedicated IIS server; dedicated domain user accounts for service accounts and application pools. Over 200 reports deployed, half of which are run by users from within applications.
SSRS upgrade process: backup up existing databases, settings, keys; uninstall SSRS2000; perform €˜files only€™ install of SSRS2005 to existing SQL2000 instance; manually configure SSRS2005 and IIS; upgrade reporting services databases.
This is a followup of a previous posting to this forum 30-May 6:28 pm UTC: Problems following SSRS database upgrade.
I start to get panic now after 10 hours of different ways of trying to get my old database from my [Server1]* to my [Server2]*. When Im trying with the "SQL Server Management Studio" that included in the MSSQL 2005 but that just tells me it dosn't support any version before MSSQL2000.. I've also tryed with some third part softwares like EMS but no joy!... Its 5 databases from the old server that I MUST get into the new one.. Anyone know howto do this?
[Server1] Windows 2000 MSSQL7
[Server2] Windows Small Business Server 2003 R2. Includes MSSQL 2005 Premium edition.
