How to verify the version of SQL Server

Use this query to verify the details on the SQL Server:

SELECT
SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,
SERVERPROPERTY(‘Edition’) AS Edition;

Of course, there are other ways, but the above is the most ‘coherent’.

Other convenient methods:

• SELECT @@VERSION

• Check the file version of sqlservr.exe

How to enable OPENROWSET and OPENDATASOURCE support in SQL Server 2008

SQL Server Surface area configuration is deprecated in SQL Server 2008, so this is how you should enable adhoc queries by setting OPENROWSET and OPENDATASOURCE options.

Run the following commands, one at a time:

sp_configure “show advanced options”,1

Output: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

reconfigure

Output: Command(s) completed successfully.

sp_configure “Ad Hoc Distributed Queries”,1

Output: Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

reconfigure

Output: Command(s) completed successfully.

And that is it.

Open a report created in Report Builder using Report Designer

Lets say you have a report created using Report Builder 2.0, in my case and want to open the report in Business Intelligence Development Studio (BIDS) using Report Designer.

 There are umpteen number of places where they talk about the fact that you can open a report created in Report Builder using Report Designer, but exactly how to go about doing that is not provided.

 So, I had to go hunt for myself and am providing the steps to be able to do that.

1. Go to the report using Report Manager, found at http://ReportServerURL/Reports
2. Click the Properties tab across the top
3. Click the Edit button under the Report Definition section
4. Now you can save the report definition as a .rdl file
5. Open the saved file in BIDS Project using Add Item functionality