EXEC - INSERT

You can return the results of a dynamic sql statement into a temp table or table variable:

DECLARE @tab TABLE ( id int, val varchar(100) )
DECLARE @sql varchar(8000)

SET @sql = "SELECT id, name FROM dbo.mytable"

INSERT INTO @tab ( id, val )
EXEC( @sql )

Installing Reporting Services

You have to open your browser (on server console or during a remote session) using "Run as Administrator".

Then go to http://localhost/Reports (NOTE: do not put http:///Reports - it doesn't work).

Go to Folder Settings and assign role browser and content manager to your administrator account. It should start working now.

If you are running it on Windows server 2008 don't forget to create inbound rule in windows firewall to port 80.

SQL Server Limiting Data Access for a Server Admin

If a user is granted the following server roles they still cannot access the data of databases they are not a user or dbo.


serveradmin - can change server configuration parameters and shut down the server.

setupadmin - can add or remove linked servers, manage replication, create, alter or delete extended stored procedures, and execute some system stored procedures, such as sp_serveroption.

securityadmin - can create and manage server logins and auditing, and read the error logs.

processadmin - can manage the processes running in SQL Server.

dbcreator - can create, alter, and resize databases.

diskadmin - can manage disk files.



----------- example script -----------

use master
go



EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'securityadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'serveradmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'setupadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'processadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'diskadmin'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'dbcreator'
GO

EXEC sys.sp_addsrvrolemember @loginame = [testuser], @rolename = N'bulkadmin'
GO

----------- example script -----------
To allow backing up of a database they do have access to, then can use the following:


use [user_database]
go

grant backup database to [testuser]
go

Plan Cache Stats

Special thanks to Kimberly L. Tripp for this query

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go 

Full article: http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx#ixzz00P7SU8xp

SQL Server Browser Service

Quick Tip from a respected MS Instructor: Disable SQL Server Browser Service

This causes a LOT of wasted network traffic and opens up a vulnerability making it easier for hackers to find your data servers.

Turn this off and advise your users to explicity enter the server name and not use the "browse" feature.

SQLActiveScriptHost Example

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:\temp\tryme.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)

PowerShell Connect to MSAccess

$adOpenStatic = 3
$adLockOptimistic = 3
$adStatusOpen = 1
$sql = "Select * from dispatch;"
$cstr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c:\temp\mydata.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()
}