Friday, November 12, 2010

How to see object definition in sql server

Found this neat function today - it shows you the internals of what tables a view is hitting, or the definition of a stored proc (ie sp_who2).  For example running the following will return you the definition of the service broker view:


SELECT object_definition(OBJECT_ID('sys.service_queues'))

Yields:
CREATE VIEW sys.service_queues AS  

SELECT o.name
       ,o.object_id
       ,o.principal_id
       ,o.schema_id
       ,o.parent_object_id
       ,o.type
       ,o.type_desc
       ,o.create_date
       ,o.modify_date
       ,o.is_ms_shipped
       ,o.is_published
       ,o.is_schema_published
       ,convert(smallint, o.property) AS max_readers
       ,convert(nvarchar(776), v.value) COLLATE database_default AS activation_procedure
       ,e.indepid AS execute_as_principal_id
       ,o.is_activation_enabled
       ,o.is_receive_enabled
       ,o.is_enqueue_enabled
       ,o.is_retention_enabled  
FROM sys.objects$ o  
  LEFT JOIN sys.sysobjvalues v
    ON v.valclass = 12 AND v.objid = o.object_id AND v.subobjid = 0 AND v.valnum = 0 -- SVC_QUEUEACTIVPROC  
  LEFT JOIN sys.syssingleobjrefs e
    ON e.depid = o.object_id AND e.class = 22 AND e.depsubid = 0 -- SRC_OBJEXECASOWNER  
WHERE o.type = 'SQ'
 
 Very cool stuff.


Thursday, November 4, 2010

Find out last backup time for all databases

This is merely a re-post of a script done on SQL Authority that I placed on my site so I can easily find it in the future.


If you have not read any of Pinal Dave's blogs (SQL Authority), you should - he posts some very good information and knows a vast amount about SQL and it's inner workings.


 This script will display by database the last time it was backed up.


SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
Order by 2

Tuesday, November 2, 2010

Multi Column Report in Reporting Services

Reporting Services in all of its awesomeness is lacking in one area- multi-column (or newspaper style) reports.  It can do it, but only certain rendering formats support it.  One way around it in RS 2008+ is to use the oh so handy tablix control and the TSQL row_number() function.  Here’s how it works:
   
      1.  Create a query that returns the information you want.  Include 2 additional columns: Row Number and Column Number.  The purpose of these is to show the x/y coordinates in the tablix group.  The example query I use will pull a list of tables and columns from the information_schema view:
declare @numberOfColumns int = 3

select table_name
      ,column_name
      ,[row_number] = ( ROW_NUMBER() over (partition by table_name order by table_name) - 1 ) / @numberOfColumns
      ,[column_number] = ( ROW_NUMBER() over (partition by table_name order by table_name) - 1) % @numberOfColumns
from INFORMATION_SCHEMA.COLUMNS
     
     2. Create a new report, place a matrix (tablix) on the report body, and create the groups as follows:

3.  For the column title I set the value of the expression to show the column number returned by the query: ="Column " & Fields!column_number.Value


4.  Add some alternating background coloring to make the report more readable : =iif(RunningValue(Fields!table_name.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")



And presto!  When you view your report it will show you a nicely formatted multiple column report.  For fun, you can make the @numberOfColumns a parameter for the report and allow dynamic column counts.




Monday, October 25, 2010

Forms Authentication against Active Directory

While trying to authenticate against AD I ran into some problems and was wondering if it was my ldap connection string.  Found a great way to verify it with the Group Policy Result tool (http://www.microsoft.com/windowsxp/using/setup/expert/gpresults.mspx)
gpresult.exe /r
It lists all sorts of items about the currently logged in user - look for the "User Settings" section, and the first line shows you the exact path to your entry.  In my case:

CN=John Hennesey,CN=Users,DC=domain,DC=com

and there is an entry "group policy was applied from" - there's your server.domain.com.

and the web.config entries (no magic here - this is on thousands of web pages - this is for easy reference in the future)


<connectionStrings>
      <add name="ADConnectionString" connectionString="LDAP://server.domain.com/CN=Users,DC=domain,DC=com"/>
</connectionStrings>

<authentication mode="Forms">
      <forms name=".ADAuthCookie" timeout="10"/>
      </authentication>
<authorization>
      <deny users="?"/>
<allow users="*"/>
</authorization>
<membership defaultProvider="MyADMembershipProvider">
      <providers>
      <add name="MyADMembershipProvider" type="System.Web.Security.ActiveDirectoryMembershipProvider, System.Web, Version=2.0.0.0,         Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="ADConnectionString" attributeMapUsername="sAMAccountName"/>
      </providers>
</membership>


Couple this with the asp Login control, and all set.  Easy!

Friday, October 22, 2010

Nolocks on a view - does it nolock all the tables in the view?

I received this question from a colleague this week:

"If there is a view that is not using nolock [in the from clause] but you have a stored procedure that uses nolock when it calls the view then do all the FROMs in the view run with nolock?"



The simple answer : Yes, the nolock hint on the view should propagate down to the tables queried in the view*.


A brief visit to our favorite resource, Books Online, reveals the following:
In SQL Server 2005/2008, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

 And the asterisk:
*If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

And my (rather simple) test proved it out:

use master
go

create database lockTest
go


use lockTest
go

create table t1 (f1 int, f2 int)
go

insert into t1 values (1, 1)
insert into t1 values (2, 2)

go


create view v1 as
select *
from t1
go

set transaction isolation level serializable
go

begin tran
select * from v1

insert into v1 values (3,3)

-- go into a new window and run the following:
/*
-- Works
select *
from v1 with (nolock)

-- Hangs
select *
from v1
*/



/*
-- cleanup
use master
go

drop database lockTest
go
*/



Sql - search for column comments

Technical reference - an easy way to query the system tables to search for column comments.

select *
from sys.extended_properties
where class =
  and major_id = object_id(N'<<schema>>.<<table>>') 
  and name = 'MS_Description'

Reporting Services 2008 over SSL - Certificates

(If you are trying to run RS over SSL and want to cut to the chase, skip this paragraph of frustration)

Disclaimer – I am not that savvy with administration – better at the nuts & bolts of programming, so most of this was learning.

Today I needed to experiment with the LogonUser method of the web service – the only way to call it is over SSL.  OK, easy enough, just tell Reporting Services to use SSL – just a check box, right?  Nope.  Do a little reading – all signs point to HttpCfg.exe.  Unless using Vista.  Then it’s netsh.exe.  Need a certificate – must generate one.  Use MakeCert.  OK, got all my parameters in check – generated.  What’s the hash?  Use certutil to get the hash (thumbprint), guid (app id).  Register it with netsh http add …..   ok, everything all lined up.

Now let’s go back into Reporting Services Configuration Manager and instruct it to use the certificate.  Piece of cake, select it from the drop down and fire it up.  Error?  Certificate is not trusted?  Oh good gracious.  Keep on researching, find out just how easy it is to do in XP.  Nothing about how easy it is to do in Vista, just lots of people complaining about how incredibly hard it is.


Oh, what’s this?  IIS 7 has an easy way to do it?  Oh YEAH!  Here are the steps to quickly create a test certificate you can use seamlessly:

1. Open IIS 7, click on the computer you wish to create the certificate for.  In the Content View double click on Server Certificates.


2.    2. The top right has an “Actions” menu.  Click on “Create Certificate Request” – could it really be this easy?



3. Type in a name for your certificate (yes, it really is this easy)


4.  Launch Reporting Services Configuration Manager, go to “Web Service URL”, select your newly created SSL certificate from the list and click apply.  It is just that easy!


And now you can do all of your development testing over SSL locally.