SQLActiveScriptHost.Print “Hello World”

Dim conn, rs
Dim sql, sProvider, sCString

”sProvider = “Microsoft.JET.OLEDB.4.0”
sProvider = “Microsoft.ACE.OLEDB.12.0”
sCString = “Data Source= c:temptryme.mdb”

sql = “SELECT * FROM tryme;”

Set conn = SQLActiveScriptHost.CreateObject(“ADODB.Connection”)
With conn
.Provider = sProvider
.Mode = adModeRead
.ConnectionString = sCString
.Open
End With

SQLActiveScriptHost.Print “Connected…”

Set rs = conn.Execute(sql)

While Not rs.EOF
rs.MoveNext
WEnd

SQLActiveScriptHost.Print “Executed SQL…”

conn.Close
Set conn = Nothing

SQLActiveScriptHost.Print “Connection Closed.”

Note: SQLActiveScriptHost is being deprecated in a future version of SQL Server (Post 2008 R2)

$adOpenStatic = 3

$adLockOptimistic = 3

$adStatusOpen = 1

$sql = "Select * from dispatch;"

$cstr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c:tempmydata.mdb; Jet OLEDB:Database Password=mypass;"

$conn = New-Object -comobject ADODB.Connection

$rs = New-Object -comobject ADODB.Recordset
$conn.Open($cstr)
if ($conn.State -eq $adStatusOpen)

{

   $rs.Open($sql, $conn, $adOpenStatic, $adLockOptimistic)
   if ($rs.State -eq $adStatusOpen)

   {

      $rs.MoveFirst()
      while (!$rs.EOF)

      {

         $rs.Fields.Item(1).Value;

         $rs.MoveNext()

      }

      $rs.Close()

   }
   $conn.Close()

}

30 January 2012
by Rob Garrison
www.simple-talk.com

* Where appropriate, use VARCHAR(n) over VARCHAR(MAX) for reasons of good design if not performance benefits, and because VARCHAR(MAX) data does not compress

* Storing large strings takes longer than storing small strings.

* Updating an in-row VARCHAR(MAX) value from below 8,000 to over 8,000 will be relatively slow, but the difference for a single transaction will likely not be measurable.

* Updating an in-row VARCHAR(MAX) value from over 8,000 to below 8,000 will be faster than if the table is set to store data out-of-row.

* Using the out-of-row option for VARCHAR(MAX) will cause slower writes until the strings are very long.

Full article
http://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar%28n%29-anymore/

Ran into an interesting problem with a SQL2005 database being restored to a SQL2008R2 instance.

Turned out the database owner was correctly represented as the login that restored the database, however the login mapped to the “dbo” user was still mapped to the old login on the source server.

To fix this, ran the following:

alter authorization on database::[database_name] to [login_name]
go

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.

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

     .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;

     }
















  
Column-A
  
Column-B
  
Column-C


  
Row 1 Col A
  
Row 1 Col B
  
Row 1 Col C


  
Row 2 Col A
  
Row 2 Col B
  
Row 2 Col C

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.

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/

DECLARE @tabs TABLE (  id int identity(1,1) not null

                     , tabname sysname not null );

DECLARE @i int

      , @tabname sysname;
   INSERT INTO @tabs ( tabname )

        SELECT name

          FROM sysobjects

         WHERE type = ‘U’;
SELECT @i = max(id) FROM @tabs;
WHILE @i > 0

 BEGIN

   SELECT @tabname = tabname FROM @tabs WHERE id = @i

   PRINT ‘UPDATE STATISTICS ‘ + @tabname + ‘ WITH ALL’

   SET @i = @i – 1

 END;
GO