Notes From Class: SQL Server, SharePoint, .NET, etc.

Q: I have a SQL Server Reporting Services report that uses a stored proc with three output parameters. How do I use the values of the parameters in the report?

2011-11-03T09:40:00.000-07:00

The problem that you're having is that SSRS doesn't actually support SP output parameters. You have to wrap your stored procedure with another stored procedure that puts the output parameter value into a rowset. Here's a simple example. If you want, you can just copy & paste into a query in SQL Server Management Studio and it will work:

/*** Begin Example ***/
-- Setup
CREATE TABLE customers
  (
customerid INT IDENTITY,
firstname  VARCHAR(50),
lastname   VARCHAR(50),
custstatus VARCHAR(50)
  )
GO

INSERT customers
VALUES ('John',
  'Smith',
  'Active')
GO

-- First Stored Procedure, which uses output parameters.
-- This cannot be used directly in SSRS
CREATE PROCEDURE dbo.Usp_proc1 @CustID INT = 0,
@p1     VARCHAR(50) OUTPUT,
@p2     VARCHAR(50) OUTPUT,
@p3     VARCHAR(50) OUTPUT
AS
  SELECT @p1 = firstname,
@p2 = lastname,
@p3 = custstatus
  FROM   customers
  WHERE  customerid = @CustID
GO

-- Second Stored Procedure, which wraps the first one, converting
-- the output parameters into fields in a single-row rowset.
CREATE PROCEDURE dbo.Usp_proc2 @CustomerID INT = 0
AS
  BEGIN
  DECLARE @p1_output VARCHAR(50),
  @p2_output VARCHAR(50),
  @p3_output VARCHAR(50)

  EXECUTE dbo.Usp_proc1
  @CustomerID,
  @p1_output OUTPUT,
  @p2_output OUTPUT,
  @p3_output OUTPUT

      SELECT @p1_output AS cfname,
@p2_output AS clname,
@p3_output AS cstatus
  END
GO

-- Example invocation of the wrapper
EXECUTE dbo.Usp_proc2 1
GO

-- Returns a rowset like this:
-- cFNAME                 cLNAME            cSTATUS
------------------------- ----------------- ----------------------
-- John                   Smith             Active
-- Cleanup

DROP PROCEDURE dbo.usp_proc2
DROP PROCEDURE dbo.usp_proc1
DROP TABLE dbo.customers
/*** End Example ***/

Once you've made your wrapper, you can execute it in SSRS as a separate dataset, and use the fields directly. Depending on where you use it, it may be necessary to use it with, e.g. the "First" function, which is an aggregate function that simply means to use the first (in this case only) row returned from the stored procedure call.

Q: How can I examine the contents of a SOAP envelope generated by my own proxy class?

2011-05-27T12:51:00.000-07:00

A: Take a look at what this clever guy figured out how to do:

How to capture SOAP envelopes when consuming a web service in ASP.NET.

He used the SoapExtension class (http://msdn2.microsoft.com/en-us/library/system.web.services.protocols.soapextension(VS.80).aspx)

to add logging to the proxy class, and viola! Cool, eh?

Q: In SQL Server 2008, can you apply a service pack to the passive node in a cluster?

2011-04-08T10:35:00.000-07:00

As it turns out, yes:

Apply a service pack or hotfix SQL Server 2008 to a failover cluster instance - Microsoft SQL Server - Site Home - TechNet Blogs

In SQL Server 2005, service packs had to be installed on the active node in a cluster. SQL 2008 removes that constraint, thus reducing total downtime.

Q: How does Microsoft Dynamics NAV integrate with SharePoint 2010?

2011-02-10T11:58:00.000-08:00

A: I'll be the first to say that I know virtually nothing about MS Dynamics, but the question was asked, and as it happens the MS Dynamics NAV team have a blog post entitled "Microsoft Dynamics NAV Compatibility with Microsoft Office 2010 and Microsoft SharePoint 2010 - Microsoft Dynamics NAV Team Blog - Site Home - MSDN Blogs", which outlines some of the compatibility features, e.g. similar user experiences, ability to query Dynamics ERP & CRM databases via SharePoint BCS, which in turn allows synchronization of MS Dynamics information to Outlook & SharePoint Workspace, etc., etc.

Q: How do I control who is allowed to create My Sites?

2011-02-10T08:43:00.000-08:00

A: By using "Manage User Permissions" in the User Profile Service Application administration tool. The default is that all users get the "Create Personal Site" permission. To change this, remove "Authenticated Users" and "All Authenticated Users" from this permission and add the AD global groups to which you want to grant the permission. This blog post from SharePoint expert Dave Coleman gives the details: Locking Down My Site SharePoint 2010 - SharePointEduTech

Q: How many application pools does SharePoint set up by default?

2011-02-10T08:32:00.000-08:00

A: If you run the SharePoint Farm Configuration Wizard, the answer is 6. Most of the service applications share one application pool (though of course you can safely change that after the fact). SharePoint MVP Spencer Harbar has listed them all in detail here: More on SharePoint 2010 Application Pools

Q: Is there a PowerShell equivalent to the C# "using" statement (for auto-disposing disposable objects)?

2011-02-08T08:36:00.000-08:00

A: No. However, the PowerShell team very kindly came up with a simple implementation, detailed here:

Reserving keywords - Windows PowerShell Blog - Site Home - MSDN Blogs

It looks like this:


function using             
{             
param($obj, [scriptblock]$sb)             

try {             
& $sb             
} finally {             
if ($obj -is [IDisposable]) {             
$obj.Dispose()             
}             
}             
}

Here's the example:


using ($stream = new-object System.IO.StreamReader $PSHOME\types.ps1xml) {             
foreach ($_ in 1..5) { $stream.ReadLine() }             
}

Q. How do I upgrade from SharePoint 2003 to SharePoint 2010?

2010-11-19T07:23:00.000-08:00

A. As it turns out, by way of MOSS 2007. Which seems kind of silly, but actually works out great, since you can use an evaluation version of MOSS to do it:

Planning for Upgrade from SharePoint Portal Server 2003 to SharePoint Server 2010 - Microsoft SharePoint Team Blog - Site Home - MSDN Blogs

Q: How do I determine the storage size of the various sites in my SharePoint 2007 site collection?

2010-09-01T12:16:00.000-07:00

Believe it or not, there’s no easy way to determine how much space is being used, with any degree of detail, from within SharePoint. So, most folks use some kind of third party tool, like this one:

http://www.thesug.org/blogs/lsuslinky/SSM/Pages/default.aspx

…or this one:

http://spusedspaceinfo.codeplex.com/

There are other options as well.

SharePoint Designer: Its reports show the size of objects within a site. However, you'll have to break out your calculator to tally it all up.
STSADM: The "enumsites" operation produces xml output that includes storage size for each site, including MySites.
Custom development: I like the solution this guy came up with: http://blog.sharepointdevelopment.nl/category/Web-Parts.aspx
He developed a custom web part that lists all of the sites in the current site collection and the storage they use.

Actually, I like that last one so much that I made a finished solution for it, which you are welcome to take and use. (Disclaimer: I bear no responsibility for any negative consequences that arise from use of this software in your environment.)

The solution file is here: http://sites.google.com/site/mctclassnotes/utility/SiteStorageWebPart.wsp (Install: "stsadm -o addsolution -filename SiteStorageWebPart.wsp", deploy: "stsadm -o deploysolution -name SiteStorageWebPart.wsp -url [YourSiteCollection] -immediate -allowGacDeployment" )

The source code, originally developed by Nick Boumans, is here: http://sites.google.com/site/mctclassnotes/utility/SP_Utilities.zip

Q: Can a SQL 2008 Central Management Server manage SQL 2005 instances? What about SQL 2000?

2009-07-21T12:11:00.000-07:00

A: Yes, it can absolutely manage both SQL 2005 and SQL 2000 instances, and can in fact send multi-server queries out to all of them, assuming the queries are valid for all instances, e.g. SQL 2000 servers will obviously not understand a query against sys.databases... Similarly, policies can also be applied across versions, assuming the properties being set are valid for all instances.

For your reference, Kimberly Tripp has a good post highlighting some of the cooler features: http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx

Q. In SQL server, why is there a difference between the number of reads reported by STATISTICS IO and the number reported in a SQL Profiler trace?

2007-09-14T07:59:00.000-07:00

A. The short answer is, the profiler trace shows *all* IO associated with a particular statement, including reads of system tables for schema information, permission checks, etc. STATISTICS IO only shows the reads of data pages that contain the actual data for each table named in the query. So, in general the two numbers will not match, and the Profiler number will usually be larger than the STATISTICS IO number.

This Q article has the details: Q314648: INF: Differences in STATISTICS IO, SQL Profiler and Sysprocesses IO Counters
http://support.microsoft.com/default.aspx/kb/314648

Q. How do I debug client-side javascripts in my ASP.NET application?

2007-05-18T05:20:00.000-07:00

A. By enabling debugging in Internet Explorer and adding the "debugger" keyword to your javascript, like Walt Ritscher says to do in this blog entry:

http://waltritscher.com/blog/ramblings/archive/2004/09/19/211.aspx

Briefly:

In Internet Explorer, go to Tools-->Internet Options-->Advanced, and uncheck the option marked "Disable Script Debugging"
Add the word "Debugger" into your script where you want it to break into debug mode.
Run the app. When the script hits that point, you'll be asked what you want to use to debug. Choose "New Instance of Visual Studio".
Step through your code as normal.

That's it!

Updated to add:

This MSDN article has even more interesting things to say:

http://msdn2.microsoft.com/en-us/library/k2h50zzs(VS.80).aspx

Q. How do I prevent pages in my ASP.NET application from being cached on the browser?

2007-05-18T05:09:00.000-07:00

(More specifically, how do I make it so that a sensitive report is not available simply by clicking the browser's "back" button?)

A. Easy. Simply read Peter Bromberg's excellent article on ASP.NET caching:

http://www.eggheadcafe.com/articles/20060407.asp

This is actually an in-depth piece about both server-side and client-side caching, but it includes the answer to this question, which is to include the following line of code in the page_load event of the page you want to affect:

Response.Cache.SetCacheability(HttpCacheability.NoCache)

This will add a header to the http response that instructs both the browser and any intermediary proxy servers not to cache this page locally.

Q. In VB.NET, what's the difference between a reference (referencing a class library) and the "imports" statement?

2007-05-17T13:07:00.000-07:00

A. This one comes up a lot. A reference allows your application to use logic that has been implemented in some external class library (a dll). If you want to use the classes in a particular library, you need to add a reference to that library so your application knows where to find those classes. In contrast, the "imports" statement is essentially a convenience feature. It tells your application which parts of all of the libraries you've referenced will actually be used. It does this by specifying the namespace in which those parts reside. Namespaces are a logical construct that exist solely to organize classes into hierarchical relationships, basically to make the 12,000+ classes in the .NET framework easier to get along with. It's important to note that you don't actually need to use the "imports" statement -- as long as a class is available within the project or via a reference, you can work with it via its fully-qualified name. For example:

' If I want to work with the SQLConnection object, I can import the namespace it lives in, like this...

imports system.data.sqlclient

' ...and then I've got the class available by name, and I can instantiate it:

dim myConn as new SQLConnection

' Alternately, I can skip the "imports" statement, and just refer to the class directly

dim myConn as new system.data.sqlclient.SQLConnection

' Both "dim" statements are semantically identical, and compile to the same MSIL code

Another reason for the use of namespaces is to avoid name collisions -- say, for example, that you implement your own SQLConnection class, inside of a namespace called "myNamespace". This is legal, because the fully-qualified names of the two classes are different, i.e.

' Microsoft class

dim msConn as new system.data.sqlclient.SQLConnection

' Your class

dim myConn as new myNamespace.SQLConnection

You can even import both namespaces and alias them for later use in distinguishing between class names:

imports TheMicrosoftOne = system.data.sqlclient
imports TheOtherOne = myNamespace

' This is the Microsoft one

dim msConn as new TheMicrosoftOne.SQLConnection

' This is the other one

dim myConn as new TheOtherOne.SQLConnection

So, in summary:- References tell your application where to find external code (i.e. in external dll's) that you want to use.- The "Imports" statement provides easy access to class names inside hierarchical namespaces.- You need references if you want to use classes from external libraries.- You do not need the "Imports" statement to use any given class, it is there to facilitate the organization of classes into namespaces. The "Imports" statement simply saves you some typing.

References: http://msdn2.microsoft.com/en-us/library/7f38zh8x.aspx