How to Create a Quick Table Backup using T-SQL

–OPTIONALLY CHECK FOR AND DROP THE BACKUP TABLE IF ALREADY EXISTS:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MY_TABLE_BACKUP_20130710]’) AND type in (N’U’))
DROP TABLE [MY_TABLE_BACKUP_20130710]
GO

–ONE LINE TABLE BACKUP:
SELECT * INTO [MY_TABLE_BACKUP_20130710] FROM [MY_ORG_TABLE]
GO
–NOTE THIS COPIES JUST DATA, NOT CONSTRAINTS OR RELATIONS

Advertisements

Umbraco SQl to Find Nodes using a Certain Document Type

SELECT * FROM cmsContent C
INNER JOIN cmsContentType CT ON C.contentType = CT.nodeId
INNER JOIN umbracoNode N ON C.nodeId = N.id
WHERE CT.alias = ‘yourDocumentTypeAliasHere’

–use the node path to locate nodes in Umbraco

Umbraco SQL to find documents using specific dataType


DECLARE @dataTypeSearch NVARCHAR(50)
SET @dataTypeSearch = '%Media Picker Adv%'

-- 1. Find thge data type you're after
SELECT * FROM cmsDataType
INNER JOIN umbracoNode N on N.id = cmsDataType.nodeId
WHERE N.[text] LIKE @dataTypeSearch

--1.b Pick your nodeId from above query
DECLARE @dataTypeId INT
SET @dataTypeId = 6877

-- 2. Find the properties using the dataType:
SELECT * FROM cmsPropertyType WHERE dataTypeId = @dataTypeId -- NodeId from above

--3. Find nodes with a proerty of that type(s):
SELECT DISTINCT N.* FROM UmbracoNode N INNER JOIN
cmsPropertyData PD ON PD.contentNodeId = N.id
WHERE
propertytypeid IN (SELECT DISTINCT id FROM cmsPropertyType WHERE dataTypeId = @dataTypeId) --Id's returned from second query above
AND dataInt IS NOT NULL --NULL CHECK Corresponding dbType from initial query above

--3. To locate in Umbraco select all nodes in the path:
--SELECT * FROm umbracoNode WHERE id in (-1,5205,6454,1086,4573,4454,4455,28104) ORDER BY LEN(path) -- To find a node paste in path

--4. Find Document Types using the propertyType (note the subquery in where clause is copied from item 3 above):
SELECT DISTINCT T.alias FROM cmsContent C
INNER JOIN cmsContentType T ON C.contentType = T.nodeId
WHERE C.nodeId IN (
SELECT DISTINCT N.Id FROM UmbracoNode N INNER JOIN
cmsPropertyData PD ON PD.contentNodeId = N.id
WHERE
propertytypeid IN (SELECT DISTINCT id FROM cmsPropertyType WHERE dataTypeId = @dataTypeId) --Id's returned from second query above
AND dataInt IS NOT NULL --NULL CHECK Corresponding dbType from initial query above
)

Umbraco Export to .NET – Skips properties in Inherited Tabs

Place a property of any type in the Generic tab and it always exports, move it to a tab inherited from the parent docType and it’s excluded from the export.

Seeing this issue in v.4.7, issue occurs with our without using the autoexport2dotnet (http://our.umbraco.org/projects/developer-tools/autoexport2dotnet) – because it’s an issue in the core export logic.

This is caused by what seems like a bug in the umbraco.presentation project, in \umbraco\presentation\umbraco\dialogs\ExportCode.aspx.cs in the GenerateProperties method.

		private object GenerateProperties(DocumentType dt)
		{
			var sb = new StringBuilder();
			
			foreach (var pt in 
                dt.getVirtualTabs.Where(x => x.ContentType == dt.Id).SelectMany(x => x.GetPropertyTypes(dt.Id, true))
                .Concat(dt.PropertyTypes.Where(x => x.ContentTypeId == dt.Id /* don't limit this to generic tab! && x.TabId == 0*/)).Distinct()
                )
			{
				//...loops and writes properties here

In the above the bit to remove is the “x.TabId == 0” that I’ve commented out – this limits the properties rendered to the first tab (the generic properties).

I have only tested this quickly, but seems to work perfectly – I will update here if I find any issues – cheers!

PS. Please note that this method has changed between version 4.7.0 and 4.7.1 because of a separate issue – however for both version removing the “x.TabId == 0” solves this issue.

If you include abstractions (interfaces) in your export, you need to make the same amend to the GenerateAbstractProperties method, so remove the check for TabId == 0 and make the selection distinct.

I wrote this solution here as well: http://our.umbraco.org/forum/templating/templates-and-document-types/18730-%27Export-to-net%27-not-including-properties-on-inherited-tabs

.NET (C#) – format decimal as price – with no culture specific information and two decimal points

This is one of those where there’s so many ways of doing something simple – you end up going “will someone please just tell me how the…” etc etc :0)
The below is a simple basic way, which ignores all culture specific information (you add the currency symbol yourself), and gives you two decimal points.

		decimal decimalValue = 12.99M;
		string test = String.Format("Item costs £{0:N} only", decimalValue);
		//"Item costs £12.99 only"
		//This will also round correctly - e.g. 12.995 to "13.00", and 12.994 to "12.99".

And if you need to inject the currency symbol based on some logic:

		string currencySymbol = "£";
		string test = String.Format("Item costs {0}{1:N} only", currencySymbol, decimalValue);
		//"Item costs £12.99 only"

And then of course finally we stick this into an extension method:

	public static class ExtDecimal
	{
		public static string ToMoney(this Decimal d, string currencySymbol)
		{
			return String.Format("{0}{1:N}", currencySymbol, d);
		}
	}

	...

	string test = decimalValue.ToMoney("£");
	//"£12.99" - and above example:
	test = String.Format("Item costs {0} only", decimalValue.ToMoney("£"));
	//"Item costs £12.99 only"

Multiple SSL sites using host headers in IIS 6

Use 443 for all, but use host file configured headers to distinguish (your host file lives at c:\windows\system32\drivers\etc\host)

To setup run this from your command prompt (if fails try the Visual Studio command prompt).

cscript.exe adsutil.vbs set /w3svc/1/SecureBindings ":443:siteonedomain.localhost.com"
cscript.exe adsutil.vbs set /w3svc/726940114/SecureBindings ":443:anotherdomain.localhost.com"

In the above the id listed between “w3svc” and “SecureBindings” (i.e. 1 and 726940114) is the ID of the website in IIS. You can see this in the IIS admin console when you view all sites – or you can use the below script to print all in the command prompt:

To list all bindings (save as .vbs file):

strComputer = "."
 
Set objWMIService = GetObject _
    ("winmgmts:{authenticationLevel=pktPrivacy}\\" _
        & strComputer & "\root\microsoftiisv2")
 
Set colItems = objWMIService.ExecQuery _
    ("Select * from IIsWebServerSetting")
 
For Each objItem in colItems
    For i = 0 to Ubound(objItem.SecureBindings)
        Wscript.Echo "Port: " & _
            objItem.SecureBindings(i).Port
    Next
Next

See http://www.sslshopper.com/article-how-to-configure-ssl-host-headers-in-iis-6.html for details and explanation.

ASP.NET Session State IsNew – or is it?

A new session ID is generated for each request in applications that do not store data in the session dictionary.

Yes it’s amazing, but true. Logically you’d expect a new ASP.NET session object to be created when the user arrives at your site, and then expire 20 mins (or whatever you configure) after they leave, but in fact a new instance is created for each request, until (if ever) your code sticks some data into session state.

The reason for this is performance – at the end of the request life cycle any data placed in Session state is serialised and persisted using your chosen provider. If there’s no data, then ASP.NET skips the taks of maintaining the session ID and loading this again next time.

If you’re using the default InProc mode then the performance gain might be negligable, but if you’re for instance using SQL server then the job of adding an empty record to the session state tables, and reading this again (for no purpose) is very sensibly skipped.

What this does mean however is that the Session IsNew property will return true until you use Session State – so don’t use this flag to check if a user has just arrived at your site. If you need to check this you could place your own cookie to expire with the session (i.e. don’t set the cookie’s Expire property).

Key thing is – Session State might be new, but that does not mean your user’s session on the webserver is.