Archive for the ‘Troubleshooting’ Category

move sql server 2005 database to sql server 2000

I need to move a database that I created in sql server 2005 express to an old development environment which run sql server 2000. After searching on the web for half hour, I follow the instructions in one of the posts. It works. Below are the steps to do this.

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

Step 1 Generating Scripts for the Database Elements and Structures

1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3) Set the following Elements to the following Values

a. Script Collation , set to TRUE

b. Script Database Create, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to FALSE

e. Script Triggers, set to FALSE

Then Hit the Next button

4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5) Click Finish

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step2 Moving the data from 2005 to 2000

1) After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2) From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3) At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4) A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3 Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be produced. The procedure that should be followed is the one stated next:

1) Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2) Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3) Set all the Elements on the List to a False Value except the ones that follow:

a. Include IF NOT EXISTS , set to TRUE

b. Script Owner, set to TRUE

c. Script of SQL Version, set to SQL SERVER 2000

d. Script foreign keys, set to TRUE

e. Script Triggers, set to TRUE

Then Hit the Next button

4) After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5) At the screen that follows hit the Select All button and the Next.

6) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7) Click Finish Button.

After completing this procedure, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

Other Thoughts:
The keyword in the store procedures needs to be changed. For example, the transaction keywords is different in version 2005 from version 2000.


Source:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=722132&SiteID=1

Advertisements

See actual ASP Error database connection not defined

ASP Error:

Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.

To see the actual error, edit the "_fpclass\fdbdrgn1.inc" file and set the "fp_DEBUG = True".

ASP Error:

Database Results Wizard Error
The database connection named 'HRcontacts' is undefined.

Solution: I fixed this by adding the website to the app pool in IIS 6

PHP error No input file specified.

I received this error when changing the home virtual directory in IIS 6. The default location was at C:\inetpub\wwwroot and I changed it to E:\. This cause the No input file specified error. To fix it, I open the php.ini file and change the value of the doc_root variable. In my case, I set doc_root = E:\

Restart the server and all the php website work again.

ASP website not running in Windows Server 2003 IIS6

I just moved some ASP websites to a new installed Windows Server 2003 with IIS 6 installed. When I visit the website, it gave me a 404 error which mean files not found. This is the most common problem with a new site under IIS 6.0. By default, IIS 6.0 is only allowed to serve static content. No ASP, no ASP.NET… so calls to ASP pages will actually trigger a 404 error.

This fix this:
1.    Open IIS Manager, expand the master server node (that is, the Servername node), and then select the Web service extensions node.
2.    In the right pane of IIS Manager, right-click the extension that you want to enable. In this example, this is Active Server Pages.
3.    Click to select the Allow check box.

Active Server Pages, ASP 0131 Disallowed Parent Path

Internet Information Services 6.0

1. Open the Internet Services Manager in the Microsoft Management Console (MMC).
2. Right-click on your Default Web Site and select Properties.
3. Click the Home Directory tab.
4. Click the Configuration button.
5. Click the App Options tab.
6. Click to select the Enable Parent Paths checkbox.
7. Click the OK button until you return to the MMC.

Internet Information Services 7.0

1. Start Internet Services Manager.
2. Click Default Web Site, and then click Properties.
3. Double-click ASP in the Features pane.
4. Expand Behavior.
5. Click Enable Parent Paths.
6. Click True for Enable Parent Paths.
7. Click Apply.

CAUSE

loadTOCNode(1, ’cause’);

This is caused by disabling ASP’s “parent paths” for a Web site or application while using relative parent paths in an include statement.

RESOLUTION

loadTOCNode(1, ‘resolution’);

The best solution to the problem is to use absolute virtual paths from the root of the Web site instead of relative paths.

For example, if you use an include file named “mycode.inc” at the root of your server, the virtual path would be “/mycode.inc.” If you use the same include file in a virtual directory named “/includes” on your server, the virtual path would be “/includes/mycode.inc.”

Website display partially in IE 7

I am still using IE 6 at work and the website looks fine. When I get home, the website only display partial of the page.  It turns out that it only effect web pages with the Panel control on it. The problem is the default height for the Panel control is 50 px. IE 6 display the whole thing and IE 7 is more restrict and hence only display 50px of the panel. Solution is to get rid of the height.