Troubleshooting : SQL Compilation Error

A problem encountered by a developer here.

The OLE DB provider "SQLNCLI" for linked server "LINKED_SRVNAME" reported a change in schema version between compile time ("40575232373724308") and run time ("40575232373723940") for table ""DB"."dbo"."Tbl_Test"".

1) locate the code calling this script (i already know it is a view) >

select table_name 
  from information_schema.views 
 where view_definition like '%Tbl_Test%' 

view name returned > vw_tblTest

2) attempt to return data from the view >

select * from vw_tblTest

error returned >

The OLE DB provider "SQLNCLI" for linked server "LINKED_SRVNAME" reported a change in schema version between compile time ("40575232373724308") and run time ("40575232373723940") for table ""DB"."dbo"."Tbl_Test"".

3) check what the view is doing >

create view [dbo].[vw_tblTest] as
select 
    column1,
    column2,
    column3
from
    dbo.SYN_tblTest

so the view is calling a SYNONYM !

4) check what the synonym is doing >

create synonym [dbo].[SYN_tblTest] for [LINKED_SRVNAME].[DB].[dbo].[Tbl_Test]

5) attempt to run the query from the view >

select 
    column1,
    column2,
    column3
from
    dbo.SYN_tblTest

Success! data is returned!

So the query itself works, but the view does not. The plan being used for the view is no longer valid.

Basicly, Tbl_Test on the remote server had changed.
Even though all the columns being requested in the view were still present (the table had only had 2 columns added), the change to the table occured since the view was compiled.

The solution? Recompile the view.

Either recreate the view (alter view [dbo].[vw_tblTest] .... ) (This is what I did, and it worked)

or

DBCC FREESESSIONCACHE (This clears the cache used by distributed queries i.e. the linked server)

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

Dynamic Crosstab transforming TEXT data from columns to rows

/*
Dynamic Crosstab transforming TEXT data from columns to rows

based on sql server central example, but modified to
1) remove error on table index hints
2) drop temporary objects first
3) perform transformation on text data (the original uses MONEY data type)
*/

Use AdventureWorks

if object_id('tempdb..#Aggregates') is not null
 begin
 drop table #Aggregates
 end
if object_id('tempdb..#Columns') is not null
 begin
 drop table #Columns
 end
if object_id('tempdb..#Rows') is not null
 begin
 drop table #Rows
 end

CREATE TABLE #Aggregates
             (
              RowText VARCHAR(50),
              ColumnText VARCHAR(50),
              CellData VARCHAR(50)
             )

INSERT INTO #Aggregates
            (
             RowText,
             ColumnText,
             CellData
            )


------------------------------------
-- source query to be transformed --
------------------------------------
/*
i have picked an adventureworks table
as an example.
*/
select 
  GroupName,
  row_number() over (partition by GroupName order by Name),
  Name
from 
  HumanResources.Department

------------------------------------


CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)
 
CREATE TABLE #Columns
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(50)
             )
 
INSERT INTO     #Columns
                (
                 ColumnText
                )
SELECT DISTINCT ColumnText
FROM           #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
ORDER BY       ColumnText
 
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
 
CREATE TABLE #Rows
             (
              RowText VARCHAR(50)
             )
 
INSERT INTO    #Rows
                (
                 RowText
                )

SELECT distinct RowText
FROM            #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
 
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)
 
DECLARE     @ColumnIndex INT,
            @MaxColumnIndex INT,
            @ColumnText VARCHAR(50),
            @SQL VARCHAR(1000)
 
SELECT      @ColumnIndex = 0,
            @MaxColumnIndex = MAX(ColumnIndex)
FROM        #Columns


WHILE @ColumnIndex <= @MaxColumnIndex
   BEGIN
      SELECT     @ColumnText = ColumnText
      FROM       #Columns
      WHERE      ColumnIndex = @ColumnIndex
 
      SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(50) NOT NULL DEFAULT '''''
      EXEC       (@SQL)
 
      SELECT     @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
                         FROM #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
            , #Columns WITH (INDEX(IX_Columns), NOLOCK)
         WHERE #Rows.RowText = #Aggregates.RowText
         AND #Columns.ColumnText = #Aggregates.ColumnText
         AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
      EXEC       (@SQL)
 
      SELECT  @ColumnIndex = @ColumnIndex + 1
   END

DROP TABLE #Columns
DROP TABLE #Aggregates

SELECT     #Rows.*
FROM        #Rows
ORDER BY   #Rows.RowText

DROP TABLE  #Rows

Dynamic Crosstab transforming MONEY data from columns to rows

/*
Dynamic Crosstab transforming MONEY data from columns to rows

based on sql server central example, but modified to
1) remove error on table index hints
2) drop temporary objects first
*/

Use AdventureWorks

if object_id('tempdb..#Aggregates') is not null
 begin
 drop table #Aggregates
 end
if object_id('tempdb..#Columns') is not null
 begin
 drop table #Columns
 end
if object_id('tempdb..#Rows') is not null
 begin
 drop table #Rows
 end

CREATE TABLE #Aggregates
             (
              RowText VARCHAR(50),
              ColumnText VARCHAR(50),
              CellData MONEY
             )

INSERT INTO #Aggregates
            (
             RowText,
             ColumnText,
             CellData
            )

------------------------------------
-- source query to be transformed --
------------------------------------
/*
i have picked an adventureworks table
as an example.
*/

select
  LastName + ' , ' + FirstName,
  row_number() over (partition by eph.EmployeeID order by LastName + ' , ' + FirstName),
  Rate
from
  HumanResources.EmployeePayHistory eph
inner join
  HumanResources.Employee e
  on e.EmployeeID = eph.EmployeeID
inner join
  Person.Contact c
  on e.ContactID = c.ContactID

------------------------------------

CREATE UNIQUE INDEX IX_Aggregates ON #Aggregates (RowText, ColumnText, CellData)
 
CREATE TABLE #Columns
            (
             ColumnIndex INT IDENTITY (0, 1),
             ColumnText VARCHAR(50)
             )
 
INSERT INTO     #Columns
                (
                 ColumnText
                )
SELECT DISTINCT ColumnText
FROM           #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
ORDER BY       ColumnText
 
CREATE UNIQUE INDEX IX_Columns ON #Columns (ColumnIndex, ColumnText)
 
CREATE TABLE #Rows
             (
              RowText VARCHAR(50)
             )
 
INSERT INTO    #Rows
                (
                 RowText
                )

SELECT DISTINCT RowText
FROM            #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
 
CREATE UNIQUE INDEX IX_Rows ON #Rows (RowText)
 
DECLARE     @ColumnIndex INT,
            @MaxColumnIndex INT,
            @ColumnText VARCHAR(50),
            @SQL VARCHAR(1000)
 
SELECT      @ColumnIndex = 0,
            @MaxColumnIndex = MAX(ColumnIndex)
FROM        #Columns
 

WHILE @ColumnIndex <= @MaxColumnIndex
   BEGIN
      SELECT     @ColumnText = ColumnText
      FROM       #Columns
      WHERE      ColumnIndex = @ColumnIndex
 
      SELECT     @SQL = 'ALTER TABLE #Rows ADD ' + QUOTENAME(@ColumnText) + ' INT NULL DEFAULT 0'
      EXEC       (@SQL)
 
      SELECT     @SQL = 'UPDATE #Rows SET ' + QUOTENAME(@ColumnText) + ' = #Aggregates.CellData
                         FROM #Aggregates WITH (INDEX(IX_Aggregates), NOLOCK)
            , #Columns WITH (INDEX(IX_Columns), NOLOCK)
         WHERE #Rows.RowText = #Aggregates.RowText
         AND #Columns.ColumnText = #Aggregates.ColumnText
         AND #Columns.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))
      EXEC       (@SQL)
 
      SELECT  @ColumnIndex = @ColumnIndex + 1
   END

DROP TABLE #Columns
DROP TABLE #Aggregates

SELECT     #Rows.*
FROM        #Rows
ORDER BY   #Rows.RowText

DROP TABLE  #Rows
 

DatabasePropertyEX()

Database Properties - SQL 2000 vs SQL 2005
Using 'Read Only' as an example >

-- SQL 2000 >

select name from sysdatabases 
where databaseproperty(name,'isreadonly') = 1

-- SQL 2005 >

select name from sys.databases 
where databasepropertyEX(name,'Updateability') = 'READ_ONLY'

Note:
System Table SYSDATABASES still works in sql 2005, but the documentation states it will be deprecated in favour of SYS.DATABASES in a future version.
Function DATABASEPROPERTY still works in sql 2005, but the documentation states it will be deprecated in favour of DATABASEPROPERTYEX in a future version.

the UPSERT

The concept of an UPSERT is to UPdate an existing data row (if present) or inSERT it (if not).

These are examples only, assume variable declaration and parameters have already been sorted.

Upsert example #1
1) check for row existance,
2) if exists - update row,
3) if doesnt - insert row.

if EXISTS (SELECT * FROM dbo.tblContact WITH (READUNCOMMITTED) Where ContactID = @contactID)
BEGIN
UPDATE dbo.tblContact
SET Surname = @Surname
 , Forename = @Forename
 , EmailAdd = @EmailAdd
WHERE ContactID = @contactID
END
ELSE
BEGIN
INSERT dbo.tblContact
 (ContactID
 ,Surname
 ,Forename
 ,EmailAdd)
 VALUES
 (@ContactID
 ,@Surname
 ,@Forename
 ,@EmailAdd)
END

Upsert example #2
1) attempt to update row
2) if no rows updated, insert row.

UPDATE dbo.tblContact
SET Surname = @Surname
, Forename = @Forename
, EmailAdd = @EmailAdd
WHERE ContactID = @contactID
IF @@rowcount = 0
BEGIN
INSERT dbo.tblContact
 (ContactID
 ,Surname
 ,Forename
 ,EmailAdd)
 VALUES
 (@ContactID
 ,@Surname
 ,@Forename
 ,@EmailAdd)
END

Example 1 always hits the database twice, with 2 I/O operations. The first for checking, the second for the correct DML command.

Example 2 gets away with 1 I/O operation for rows that are updates, and 2 for rows that are inserts. This is an improvement over example 1 therefore.

ROW_NUMBER() Example

Using AdventureWorks, demonstrates how ROW_NUMBER can be used to count within groups, in this case counting the number of people in each contact type.

Use AdventureWorks

select
 ct.Name
 ,row_number() over (partition by vc.ContactTypeID order by vc.ContactTypeID)
 ,LastName + ' , ' + FirstName as Person

from
 Purchasing.VendorContact vc
inner join
 Person.ContactType ct
on ct.ContactTypeID = vc.ContactTypeID
inner join
 Person.Contact c
on c.ContactID = vc.ContactID

TSQL : Show all available DATETIME formats

SET NOCOUNT ON
SET NOCOUNT ON
DECLARE @tblDateTime TABLE (id TINYINT,DateString VARCHAR(30))
DECLARE @CurrentCount INT
DECLARE @TotalCount INT
DECLARE @DateLength INT
DECLARE @DateString VARCHAR(30)

SET @TotalCount = 150
SET @CurrentCount = 1 
WHILE @CurrentCount <= @TotalCount    
BEGIN       
BEGIN TRY  
SELECT @DateString = CONVERT(VARCHAR(30),GETDATE(),@CurrentCount)  
INSERT INTO @tblDateTime (id,DateString) VALUES(@CurrentCount,@DateString)  
END TRY  
BEGIN CATCH  
END CATCH 
SET @CurrentCount = @CurrentCount + 1   
END   
SELECT id,datestring FROM @tblDateTime 

TSQL : Adding a linked server

Adding a linked server & it's associated login.

exec sp_addlinkedserver 'servername'
exec sp_addlinkedsrvlogin  'servername', 'false', null, 'username', 'password'

Performance tip :

The local and linked servers should share the same character set and sort order (collation). If this is the case, performance can be boosted by using this command >

EXEC sp_serveroption 'servername', 'collation compatible', 'true'

Sql then assumes local and remote character sets are compatible for all queries.