Enabling SQL Server 2008 database to accept the changes made in a table

The SQL Server 2008 Management Studio will throw the following warning message when we tried to save the modification done in an existing table. It won’t allow us to save the changes in the table.

Warning Message:
“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created”.

We can fix this issue in Management Studio by un-checking a property. To fix this issue in Management Studio, Go To Tools > Options > Designer > Tables and Database Designs > uncheck the property “Prevent saving changes that require table re-creation”.

Changing the default value of “Edit Top 200 Rows” – SQL Server 2008

The SQL Server 2005 Management Studio allows us to “Edit” and “View” the whole set of records in a table. But in SQL Server 2008 Management Studio, this flexibility was restricted. SQL Server 2008 by default allows us to select top 1000 records and allows us to edit top 200 records. If our records in a table are more than the default numbers, SQL Server 2008 won’t display those records or allows us to edit those records. We can increase those default value.

Here are the steps…

1. Open SQL Server 2008 Management studio.

2. Right Click on any of the table. It will display the default value.

3. Go to “Tools” > “Option” > “SQL Server Object Explorer” > “Command”.

4. Change the default value to your desired value. Here i am entering “10000″.

5. Now go and check again. The value gets changed.

We successfully changed the default value to our desired value.

Excel 2007 as Data Source in Integration Services 2005

Most of us know how to use MS-Excel 2003 file format in an SQL Server 2005 Integration Services. But we could not use MS-Excel 2007 file format in an SQL Server 2005 Integration Services. The Connection manager for Excel files option that we would usually choose would not work.

This was sorted out with SQL Server 2005 SP2.

Here I have explained the procedure, how to make MS-Excel 2007 file format as a data source in SQL Server 2005 Integration Services.

Procedure

1. Open the Business Intelligence Developer Studio and start a new Integration Services Project.
2. Drag a Data Flow task on to the designer surface and double click on it.
3. Right click in the “Connection Manager” pane and select “New OLE DB Connection“.

4. Click ‘New‘ to create a new data connection.

5. After created the connection, click ‘Next‘ to select the appropriate provider.

6. Under the Provider drop-down, select “Microsoft Office 12.0 Access Database Engine OLE DB Provider“.

7. Click on the ‘All‘ tab of the tab strip located on the left, and type the following against the Extended Properties property “Excel 12.0“.

8. Go back to the “Connections” tab and type in the full file path of the MS-Excel 2007 file.

9. That’s it, click “Test Connection“. That’s it. We successfully created MS-Excel 2007 as data source in SQL Server 2005 Integration Services.