Get all sites in a SharePoint web application using PowerShell

I realize the title of this entry is ambiguous. Specifically, you might be wondering if I mean site or site collection. The answer is: Yes. To make it less ambiguous, I will be referring to how things are in code, not the UI. The difference is that in code SPSite is actually a Site Collection (in the UI) and in code SPWeb is actually a Site (in the UI). The hierarchy of objects goes: SPWebApplication | SPSite | SPWeb where SPWebApplication and SPSite have Collection of the other objects below them. Below are example of how to get both SPSites and SPWebs.

Get number of SPWebs in your web application

$allwebs = Get-SPWebApplication http://www.sharepoint.com | Get-SPSite -Limit All | Get-SPWeb -Limit All
$allwebs.Count

Get urls of all the SPWebs in your web application

Get-SPWebApplication http://www.sharepoint.com | Get-SPSite -Limit All | Get-SPWeb -Limit All | select Url

Get number of SPSites in your web application

$allwebs = Get-SPWebApplication http://www.sharepoint.com | Get-SPSite -Limit All | Get-SPWeb -Limit All
$allwebs.Count

Get urls of all the SPSites in your web application

Get-SPWebApplication http://www.sharepoint.com | Get-SPSite -Limit All | Get-SPWeb -Limit All | select Url

MySql error message: Parameter '@A' must be defined using .NET connector

I upgraded my MySql .NET connector (MySql.Data.dll) from 1.0.7.30072 to a much newer 6.5.4.0 version. I was expecting complete backward compatibility, but I was completely wrong in my assumption. Instead I started getting the following error.

Fatal error encountered during command execution.  

  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

The problem is that “Fatal error encountered during command execution” was not particularly useful. As it turned out we had accidentally deployed the new MySql.Data.dll to QA and production without noticing that some queries failed with the above message. Then I tried to reproduce the problem on my laptop and could not. My project in Visual Studio 2010 had a reference to v1 of the MySql.Data.dll. Once I figured out there was a different version that snuck into our development environment and made it to production I figured that was the issue. So, I changed the reference in VS2010, but it seemed to be using the older version still. I’m not sure exactly how and I didn’t take the time to figure out why, and only made everything more confusing. In the end, I created a command line app, and added the new version of the MySql.Data.dll to the project and added just the snippet of code that was breaking. Thank goodness I could now reproduce the error on my laptop. Now I looked at the inner exception, and I see what the real error message is:

Parameter '@A' must be defined.

Okay, now we are getting somewhere. After a bit of searching I figured out that I had to add “Allow User Variables=True” to my connection string (not the SQL, but the connection string in the config file).

The solution: Just add

Allow User Variables=True

to your config file and you can now use user defined variables in your sql statements that you pass to the MySql .NET connector.

Finding Currently Running Query using T-SQL

To find out what queries are currently running on your SQL Server try the following query.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Keep in mind that the results of this query will include this query itself, so the result will always be at least one row returned.

If you decide you want to kill one of the queries you can use the kill sql statement and the session id (see the second column).

The system is simple:

KILL <session id here>

I owe the basis for this post to the following post: http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

Get a list of all user defined stored procedures, functions, etc

If you are using SQL Server 2005 and newer you can use the queries below to get a list of any user defined object (including, but not limited to stored procedures, scalar-valued functions, table-valued functions, aggregate functions, and views) for a given database on a SQL Server 2005 installation. For a complete list of objects that you can list click here. The queries below will give you the same results you get when you look in Object Explorer in SSMS (Microsoft SQL Server Managements Studio).

All User Defined Objects

This will give you all the objects listed here.

-- all user defined objects
SELECT  sys.schemas.name + '.' + sys.objects.name, type, type_desc
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
order by 3,1

Common User Defined Objects

This will give you a list of the following user defined objects: stored procedures, views, table-values functions, scalar-valued functions, aggregate functions.

-- common user defined objects
SELECT  sys.schemas.name + '.' + sys.objects.name, type, type_desc
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in
(
'p', 'pc', -- stored procs
'v', -- views
'tf', 'if', 'ft', -- table-valued functions
'fn', 'fs', -- scalar-valued functions
'af' -- aggregate functions
)
order by 3,1

User Defined Stored Procedures

-- user defined stored procs
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('p', 'pc')
order by 1

User Defined Views

-- user defined views
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type='v'
order by 1

User Defined Table-Valued Functions

-- user defined table-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('tf', 'if', 'ft')
order by 1

User Defined Scalar-Valued Functions

-- user defined scalar-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('fn', 'fs')
order by 1

User Defined Aggregate-Valued Functions

-- user defined aggregate-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type = 'af'
order by 1