Determine the SQL Server version of remote servers

Technorati Tags: ,

I ran into this situation where the SQL returned expected data against SQL 2000 but not against SQL 2005. This was one of those breaking changes in the SQL 2005 database engine.  The following piece of code determines the SQL version of the remote server from within a stored procedure. As always I believe there are multiple ways of getting to the solution but here is one. Please note the remote SQL server needs to be a linked server on the SQL server executing the SQL script (Can I add any more SQL/servers in this sentence!).

 

DECLARE @RemoteSQLserver VARCHAR(20), @sql VARCHAR(8000), @SQLVersion VARCHAR(10)

SELECT @RemoteSQLserver = 'sql_1'

/* -------------------------- Determine the version of the target server ---------------------*/
-- Check and drop if temporary table exists
IF (OBJECT_ID(N'tempdb..#Results') IS NOT NULL)
BEGIN
    DROP TABLE #Results
END
-- Create the temp table to hold results
CREATE TABLE [#Results] ([SQLVersion] [varchar] (50))

-- Execute the SERVERPROPERTY on the target server
SELECT @sql = 'SELECT CAST(SQLVersion AS Varchar(20)) FROM OPENQUERY(' + @RemoteSQLserver + ', ''SELECT SERVERPROPERTY(''''ProductVersion'''') AS SQLVersion'')'
INSERT INTO #Results EXEC (@sql)
SELECT @SQLVersion = SQLVersion FROM #Results 

-- Extract the major build of the version number
SELECT @SQLVersion = SUBSTRING(@SQLVersion, 1, CHARINDEX('.', @SQLVersion) - 1)
-- Display the major build
SELECT @SQLVersion 

Advertisements

ASP.NET GridView with fixed column headers

Technorati Tags: ,

 

 Requirement: GridView with fixed column headers that allow for scrolling horizontally and vertically. On page refresh or postback retain focus on the selected Gridrow.

Environment: Visual Studio 2005 and C# on Windows XP Professional SP2.

Solution: Add a web form to the web project. Then drop a panel onto the design view of the form. Adjust the height and width so as to force scrolling with the data displayed. Drop a GridView control onto the panel. I selected a style from the AutoFormat option. In the Column collection, add the Select command field and keep the "Auto-generate fields" checked.

Add a SQLDataSource control to the form and configure it to point a table/query with data rows. Set the GridView’s DataSourceID to the SQLDataSource control.

If you viewed the page in the browser, you should see something like this.

Update the panel properties, and set the scrollbars property to "Auto". The browser view should then look like this. But scrolling causes the column headers to disappear.

Add the following <style> block to the .aspx page.

    <style type="text/css">
        #grdvAuthors th
        {
            text-align: center;
            position: relative;
            cursor: default;
        }
        tbody th
        {
            top: expression(document.getElementById("pnlAuthors").scrollTop-2); /* IE5+ only */
            z-index: 20;
        }
  </style>

And remove the following line from the .aspx designer source code.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

This will now cause the column headers to stay fixed with horizontal and vertical scrolling.