sqlforge: January 2012

Friday, January 27, 2012

Invalid Column Error When One Stored Proc Calls Another

I've run into a problem where one stored procedure calls another and I get an "Invalid Column" error. The child stored proc works fine when run separately.

Some similar errors could easily be cleaned up by using sp_recompile or sp_refreshsqlmodule.

But, turns out there is a little bug in the column name validation when there are temp tables in a child stored proc that have the same name as a temp table in the parent proc.

Solution: rename the temp table in one proc or another. Or, could use variable tables or some other structure in one or the other.

Friday, January 13, 2012

Enable CLR

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

http://msdn.microsoft.com/en-us/library/ms131048.aspx

Thursday, January 12, 2012

Vertical Text



<HTML>
<HEAD>
  <STYLE>
     .verticaltext 
     {
        writing-mode:tb-rl;
        filter: flipv fliph;
        -webkit-transform:rotate(270deg);
        -moz-transform:rotate(270deg);
        -o-transform: rotate(270deg);
        white-space:nowrap;
     }


     .heading
     {
        background: #000000;
        color: #FFFFFF;
        font-weight: bold;
        height: 90px;
     }
  </STYLE>
</HEAD>


<BODY>

<TABLE BORDER="1"><TR class="heading">
  <TH class="verticaltext">Column-A</TH>
  <TH class="verticaltext">Column-B</TH>
  <TH class="verticaltext">Column-C</TH>
</TR>
<TR>
  <TD>Row 1 Col A</TD>
  <TD>Row 1 Col B</TD>
  <TD>Row 1 Col C</TD>
</TR>
<TR>
  <TD>Row 2 Col A</TD>
  <TD>Row 2 Col B</TD>
  <TD>Row 2 Col C</TD>
</TR>
</TABLE>
</BODY>

</HTML>

Wednesday, January 4, 2012

DROP all VIEWS, FUNCTIONS, PROCEDURES

DECLARE @obj TABLE ( id int identity, objname sysname, objtype sysname )
DECLARE @i int
      , @sql nvarchar(max)


INSERT INTO @obj ( objname, objtype )
          SELECT A.name
               , (case when A.type = 'V' then 'VIEW'
                       when A.type in ('FN','IF','TF') then 'FUNCTION'
                       when A.type = 'P' then 'PROCEDURE'
                       else '' end ) 
            FROM sysobjects A (NOLOCK)
           WHERE A.type IN ( 'V', 'FN', 'IF', 'TF', 'P' )
        ORDER BY (case A.type when 'V' then 3
                              when 'P' then 1
                              else 2 end )
               , A.name DESC

SELECT @i = max(id) FROM @obj

WHILE @i > 0
 BEGIN
   SELECT @sql = 'DROP ' + A.objtype + ' ' + A.objname
     FROM @obj A
    WHERE A.id = @i
   PRINT @sql
   SET @i = @i - 1
 END

This is one example where using a table variable and while loop on a relatively small set of data is preferred, simply because the "sysobjects" table will be mutating through the process of the loop. Could still get away with it in a cursor if locks and hints are properly applied, but I still prefer to avoid it when possible.

Using Cursors Optimally

sqlwithmanoj demonstrated cases where using optimization hints such as FAST_FORWARD, FORWARD_ONLY, READ_ONLY can result in cursors that are more performant than while loops on table variables.

http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/