Skip to content


MS SQL TSQL PadLeft

Ever wanted a PadLeft for SQL server
Here's the next best thing, a scalar function we wrote and implemented into all our databases
Enjoy

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Andre Pageot | Michael Grabsky
-- Create date: 06/02/2014
-- Description:	Pads a string out
-- USAGE: SELECT dbo.fnPadLeft('123456',9,'0')
--	returns: 000123456
-- =============================================
CREATE FUNCTION [dbo].[fnPadLeft]
(
	-- Parameters taken from the .NET implementation of PadLeft
	@StringToPad varchar(250)
	,@totalWidth int
	,@paddingChar varchar(1)
 
)
RETURNS varchar(250)
AS
BEGIN
	/*	DECLARE	@StringToPad varchar(250)
			,@totalWidth int
			,@paddingChar varchar(1)
			SELECT @StringToPad = '123456',@totalWidth = 9,@paddingChar='0'
	*/
 
	DECLARE @returnString varchar(5)
	DECLARE @PadString varchar(max) SET @PadString =''
 
	WHILE LEN(@Padstring) < @totalWidth
		BEGIN
			SELECT @PadString = @PadString + @paddingChar
		END		
 
	--SELECT RIGHT(@PadString + RTRIM(@StringToPad), @totalWidth)
 
	RETURN RIGHT(@PadString + RTRIM(@StringToPad), @totalWidth)
END
 

Posted in Annoyances, SQL, TSQL.


Windows 8 to Windows 8.1 with redirected user profile folders

You're here probably because you received this error

Sorry, it looks like this PC can't run Windows 8.1. This might be because the Users or Program Files folder is being redirected to another partition

The Solution in a nutshell

  • Edit registry keys to re point to system drive
  • Add New Admin account to system
  • Login as new Admin account
  • edit user registry keys to re point to local system users folder
  • Run batch file to create junctions between local system user folders and redirected user folder
  • Restart, login as admin and run windows update to 8.1
  • Once complete edit registry to re point chosen user profile folders back across to redirected drive
  • The user folders that are redirected again must have the AppData hidden folder folder copied across to the redirected user folder fro the system drive

The solution in more detail
Basically we need to trick the updater that the profiles are local. We do this in a few steps

  1. Edit some registry keys
  2. Create a new admin account, restart and login to it
  3. Edit some more registry keys
  4. Create some junctions to point from local profiles to actual profiles
  5. Restart login again as the admin account and run updates

So let’s get started
You might want to save this page as a “.mht” file to the root of your C: so you can refer back to this as you progress
Edit registry
Run regedit as admin and goto
HKLM\SOFTWARE\Microsot\Windows NT\CurrentVersion\ProfileList
Mine looks like this
ProfileList HKLM
Edit the ProfilesDirectory Key back to “%SystemDrive%\Users”
ProfilesDirectory HKLM
Create a new account and mark is as administrator
Restart and login with the new account
Go back into an admin regedit and adjust the remaining keys so they look like this
RESET ProfileList keys HKLM

As you can see I have lots of local logins (I develop apps and as such lots of services login to my machine) so I built a batch file to create the junctions from local to actual user profile folders
For users with no gaps in their name
mklink /J C:\Users\Andre E:\Users\Andre
for users with gaps
mklink /J "C:\Users\.NET v4.5" "E:\Users\.NET v4.5"
My Batch file ended up here c:\MKLinks.bat and looked like this
mklink /J C:\Users\Mcx1-HOME-PC E:\Users\Mcx1-HOME-PC
mklink /J C:\Users\UpdatusUser E:\Users\UpdatusUser
mklink /J C:\Users\Andre E:\Users\Andre
mklink /J "C:\Users\Classic .NET AppPool" "E:\Users\Classic .NET AppPool"
mklink /J "C:\Users\.NET v4.5" "E:\Users\.NET v4.5"
mklink /J "C:\Users\.NET v2.0" "E:\Users\.NET v2.0"
mklink /J "C:\Users\.NET v4.5 Classic" "E:\Users\.NET v4.5 Classic"
mklink /J "C:\Users\.NET v2.0 Classic" "E:\Users\.NET v2.0 Classic"
mklink /J C:\Users\Default E:\Users\Default
mklink /J C:\Users\Public E:\Users\Public

Now we are ready to execute the batch file, so open an elevated command prompt, navigate to the location you saved the batch file and run it.

Next we must adjust the “ProfileImagePath” string value in each user profile sub key of ProfileList (the keys typically start with S-1-5-xx-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-xxxx) so they point back to the local system drive (C:) like this one

Return Profiles back to system drive locations

Once you are sure you have completed these instructions restart once more, login as the admin account and run windows update.

Once completed, every profile will be put back onto the C:\Users and your folder junctions will have been replaced by these user folders.
There is a simple solution to bring things back across to your redirected drive, editing the users registry key and copying the hidden AppData folder from the C:\Users\Username folder to the target user profile folder on the redirected drive you are trying to use.

I ended up leaving all of my profiles on C and only redirected my profile, which incidentally was far too large to reside on the C:

Posted in Annoyances, Infrastructure.


asp.net ReportViewerControl The size of the request headers is too long

Bad Request - Request Too Long

HTTP Error 400. The size of the request headers is too long.

When you use the "Microsoft.ReportViewer.WebForms"control in asp.net

You've eaten too many cookies Reserved.ReportViewerWebControl.axd

If like me you use the report viewer control to display reports from SSRS to the world wide web you may have come across an ever increasing number of cookies from the control.
I don't really have time to look into this in any detail as im working to a deadline at the moment and as such need to resolve this asap.
I think this is perhaps something to do with the fact I am using masterpages with iframes to both separate the CSS and pull out the parameters into an intermediate step.

Anyway my solution was to wipe the cookie from the request cookies collection so they dont get a chance to stack up.
Create class in you App_Code and past the following code into the file

you could adapt this to only delete if the number of cookies exceeded a certain level

 
Imports Microsoft.VisualBasic
 
Public Class httpModuleClearReportViewerControlCookies
    Implements IHttpModule, SessionState.IRequiresSessionState
 
    Public Sub Dispose() Implements IHttpModule.Dispose
 
    End Sub
 
    Public Sub Init(context As HttpApplication) Implements IHttpModule.Init
        Dim DeleteList As New List(Of String)
        For Each oCookie In context.Request.Cookies
            If oCookie.ToLower.Contains("reserved.reportviewerwebcontrol") Then DeleteList.Add(oCookie)
        Next
        For Each Cookie As String In DeleteList
            context.Request.Cookies.Remove(Cookie)
        Next
    End Sub
End Class
 

Posted in .NET, Annoyances, VB.


EWS & .NET how to get started

Getting started with Exchange Web Service (EWS)

Firstly you might want to configure your security groups as per my article titled "Exchange Web Service (EWS) configuration" then return and continue.

EWS is a powerful way to access Microsoft Exchange over https. Doing this via .NET is a doddle.

I am going to assume you know how to add a web reference to your .net project.

your exchange server will be publishing EWS on the following URL

https://<DomainName|IPAddress|Servaername>/EWS/Services.wsdl

All you need to do is add a webreference to your .NET project by pointing to the above url. The important thing to remember is that you will actually be using a different URL when it comes to binding in code. The url shape will be like this

https://<DomainaName|IPAddress|Servaername>/EWS/exchange.asmx

Once you have your web reference in place, and in this example we will be calling the reference EWS, the first thing you are going to need to do is declare a new instance like so

Dim binding As EWS.ExchangeServiceBinding = GetNewEWSBinding()

The GetNewEWSBinding() returns a binding. The binding is on one of the mailboxes within the saEWSImpersonatable security group mentioned in the article previously mentioned at the begining of this one.
The following version of the function has a default binding mailbox defined in a variable at the package level of the SSIS package this script task resides in; it also checks for an InTest switch to swap between two mailboxes when in test or production. Binding credentials can be configured manually for a specific user, this user must be a member of the security group sgEWSImpersonate as described in the article mentioned at the beginning of this one. Finally there is an override optional parameter to provide if you wish to specify a specific mailbox to bind to instead of the default.
You can amend this to your requirements

 
Function GetNewEWSBinding(Optional ByVal ImpersonateMailAddress As String = "") As ExchangeServiceBinding
        Dim binding As local.swisscantouk.exch01.ExchangeServiceBinding = _
                New local.swisscantouk.exch01.ExchangeServiceBinding()
 
        With binding
            .Url = Dts.Variables("ExchangeEWS").Value
            'specify binding credentials like so
            'binding.Credentials = New Net.NetworkCredential("username", "password", "domain")
            'or use the default credentials the code is executing under
            .UseDefaultCredentials = True
            .PreAuthenticate = True
 
            'set the version on the request
            .RequestServerVersionValue = New RequestServerVersion With {.Version = ExchangeVersionType.Exchange2010_SP1}
            'setup the default mailbox for this binding
            .ExchangeImpersonation = New ExchangeImpersonationType With {.ConnectingSID = New ConnectingSIDType With {.ItemElementName = ItemChoiceType.PrimarySmtpAddress}}
 
            'connect to the package level mailbox defined in the variables of this package
            If Dts.Variables("InTest").Value Then
                .ExchangeImpersonation.ConnectingSID.Item = emailImpersonationTEST '"main SMTP email address of account someone@contoso.co.uk"
            Else
                .ExchangeImpersonation.ConnectingSID.Item = emailImpersonationLIVE '"main SMTP email address of account someone@contoso.co.uk"
            End If
 
            'connect to a specified mailbox defined specifically in the program sequence 'overriding the default
            If Not String.IsNullOrEmpty(ImpersonateMailAddress) Then .ExchangeImpersonation.ConnectingSID.Item = ImpersonateMailAddress
        End With
 
        Return binding
    End Function
 

As soon as you have your binding you are ready to start working with the mailbox.

In my next article on EWS, I will discuss how to declare your EWS binding objects using object initialisers, you will be able to declare and run requests against EWS using less code.
ie Dim x as object With {.parameter = value, .parameter = value}
Example

 
 'construct the request
Dim reqFindFolder As FindFolderType = New FindFolderType
'setup the properties of the FindFolder request object
With reqFindFolder
    .Traversal = FolderQueryTraversalType.Deep 'change to deep to include subfolders
    .ParentFolderIds = New DistinguishedFolderIdType() {New DistinguishedFolderIdType With {.Id = DistinguishedFolderIdNameType.msgfolderroot}}
    .FolderShape = New FolderResponseShapeType With {.BaseShape = DefaultShapeNamesType.IdOnly}
    .Restriction = New RestrictionType
    .Restriction.Item = New ContainsExpressionType With { _
	.ContainmentMode = ContainmentModeType.FullString _
	, .ContainmentModeSpecified = True _
	, .ContainmentComparison = ContainmentComparisonType.Exact _
	, .ContainmentComparisonSpecified = True _
	, .Constant = New ConstantValueType With {.Value = FolderName} _
	, .Item = New PathToUnindexedFieldType With {.FieldURI = UnindexedFieldURIType.folderDisplayName} _
	}
End With
 

Posted in .NET, Development, Exchange, Linq, VB.


Exchange Web Service (EWS) configuration

Exchange Web Service (EWS) configuration

The idea here is to create two groups within the Active Directory. The first group will contain the Mailbox accounts you wish to allow access and manipulation of objects within the mailbox (sgEWSImpersonatable). The second will contain the accounts you wish to allow access to the accounts within the first group (sgEWSImpersonate).

What we want to do is

  • AD - Create a security group (sgEWSImpersonateAble), this group will hold the accounts we want to be able to impersonate (eg testAccounts, devsystems etc etc)
  • AD - Create a security group (sgEWSImpersonate), this group will hold the accounts we want to allow impersonation of the accounts in the group sgEWSImpersonateAble
  • EX - Create a Scope (scopeEWSImpersonate), this scope we use to link the ApplicationImpersonation Exchange role to the security group created in the previous step. . Ie we assign the scope to the security group sgEWSImpersonateAble
  • EX - Create a RoleAssignment (mraEWSImpersonation) this Management Role Assignment will be used to tie the ApplicationImpersonation role to the scope. this then compeltes the loop between AD and Exchange

Follow these steps

  1. Create the Security Group in AD (it can be mail enable or not, it makes no difference)
    Group Name: sgEWSImpersonateAble
    Group Description: Exchange Web Service Impersonation, accounts in this group will grant members of the group sgEWSImpersonate impersonation ability via Exchange Web Service calls
    Group Members: TestAccounts, testsqlmailuser, etc,etc,etc
  2. Create the Security Group in AD (it can be mail enable or not, it makes no difference)
    Group Name: sgEWSImpersonate
    Group Description: Exchange Web Service Impersonation, accounts in this group be able to impersonate members of the group sgEWSImpersonateAble via Exchange Web Service (EWS) calls
    Group Members: Developer1,Developer2, Sysadmin1, svcAccount, etc,etc
  3. Create the Scope (This is a one time only requirement to run) In Exchange Management Powershell console run the following, this will link the scope to the groupGet the location of the security group we created for the accounts to impersonate

    >$sgEWSImpersonateAble = $(Get-DistributionGroup sgEWSImperonateAble).Identity.DistinguishedName

    verify we have it by looking at the vaariable
    >$sgEWSImpersonateAble
    CN=sgEWSImpersonateAble,OU=OrganisationalUnitContainingTheGroup,DC=DomainName,DC=local

    Now Create the Scope linking it to the group
    >New-ManagementScope -Name:scopeEWSImpersonate -RecipientRestrictionFilter:"MemberOfGroup -eq '$sgEWSImpersonateAble'"

  4. Create the Role Assignment (to link the scope to the group containing the accounts we want to allow impersonation to)>New-ManagementRoleAssignment –Name:mraEWSImpersonation –Role:ApplicationImpersonation –SecurityGroup "sgEWSImpersonate" –CustomRecipientWriteScope: scopeEWSImpersonate

For a long story short execute the following in the Exchange Management Powershell console. Replace the names to those you would prefer.

>$sgEWSImpersonateAble = $(Get-DistributionGroup sgEWSImperonateAble).Identity.DistinguishedName
>$sgEWSImpersonateAble
CN=sgEWSImpersonateAble,OU=OrganisationUnitContainingTheGroup,DC=Domain,DC=local
>New-ManagementScope -Name:scopeEWSImpersonate -RecipientRestrictionFilter:"MemberOfGroup -eq '$sgEWSImpersonateAble'"
>New-ManagementRoleAssignment –Name:mraEWSImpersonation –Role:ApplicationImpersonation –SecurityGroup "sgEWSImpersonate" –CustomRecipientWriteScope: scopeEWSImpersonate

Now you can add and remove people and mailboxes to and from the two groups to allow impersonation of mailboxes from accounts

References

http://msdn.microsoft.com/en-us/library/exchange/bb204095(v=exchg.140).aspx

Set-ManagementRoleAssignment
http://technet.microsoft.com/en-us/library/dd335173(v=exchg.141).aspx
New-ManagementRoleAssignment
http://technet.microsoft.com/en-us/library/dd335193(v=exchg.141).aspx
New-ManagementScope
http://technet.microsoft.com/en-us/library/dd335137(v=exchg.141).aspx
Set-ManagementScope
http://technet.microsoft.com/en-us/library/dd297996.aspx

Posted in Configuration, Exchange, Infrastructure.


SQL Server Monitor Changes to SCHEMA

If you need to prevent changes to your SQL Server Schema, or at least monitor them, then this post is worth reading

I needed to monitor/control changes made to SQL server databases at server instance level. There are plenty of ways of doing this but there is nothing as simple as a DDL level trigger. It's clean, can be disabled quickly for rapid change implementation and its a central location which means deploying to sever SQL Servers or a server farm is that much easier.

So the basic requirements
1. We must be able to monitor CREATE,DROP and ALTER on Tables, Views, Stored Procedures, Triggers and Functions
2. We must be able to filter based on user, applying the rule or not
3. We must have a log of each schema change regardless of success or failure
4. We need to send an email when a change has been attempted and was disallowed

So how are we going to do this. Well we can use the ROLLBACK inside our trigger to prevent the changes from occurring, and we can use the RAISERROR to display the appropriate message we want to user/program to receive when the attempt fails. We can also take advantage of the code SQL Server query execution sequence such that after a rollback has been called in a trigger, the parent transaction has been dumped, but any further transactions will continue to be executed and committed. See here http://msdn.microsoft.com/en-us/library/ms187844(v=sql.105).aspx

Some Key fundamentals we are going to be using
First off the CREATE TRIGGER [TriggerName] ON ALL SERVER, this will allow us to create the trigger at server level, so it affects all databases. It will hence be located in the Server Objects >> Triggers within SQL Server Management Studio
Next comes EventData() which contains all the details we want about what is changing and who is trying to change it
We will be using "SYSTEM_USER" to identify the current logged in connection that is behind the event and also filter on this to make a decision to reject or approve the change.
Finally we use the ROLLBACK and RAISERROR commands to initiate a rejection of the event, followed with our logging insert and in my case an email send method, I won't get into the details of the mail sending in this article, however its worth mentioning that I have a fully fledged SSIS package driven mail sending engine integrated with MS Exchange, its possible to send from any mailbox to any mail group or address in the active directory or individual mail address book, you can attach files from the network or from binary within the database, you can send with importance in text format or html as this example demonstrates, Its a very powerful and very handy engine to have in the systems topology, took 3 days to write and about a month to fine tune, suddenly sending mail is very easy from anywhere in your infrastructure. Anyway I digress

Enough background, here's the Code. You will obviously need to adjust bits to suite your needs before you use this
This should be executed against the master

 
/*
IF  EXISTS (SELECT * FROM master.sys.server_triggers
WHERE parent_class_desc = 'SERVER' AND name = N'ddl_SQL_Server_Schema_Lock')
DROP TRIGGER [ddl_SQL_Server_Schema_Lock] ON ALL SERVER;
*/
 
CREATE TRIGGER [ddl_SQL_Server_Schema_Lock]
    ON ALL SERVER
    FOR ALTER_PROCEDURE, DROP_PROCEDURE,
        ALTER_TABLE, DROP_TABLE,
        ALTER_FUNCTION, DROP_FUNCTION,
        ALTER_INDEX, DROP_INDEX,
        ALTER_VIEW, DROP_VIEW,
        ALTER_TRIGGER, DROP_TRIGGER
    AS
    BEGIN
 
		/*Record the change and who made it, we must do this before our ROLLBACK  otherwise we lose access to the EventData*/
		DECLARE @EventData XML,
				@DatabaseName varchar(800),
				@Schema SYSNAME,
				@Object SYSNAME,
				@EventType SYSNAME,
				@SQL VARCHAR(max)
 
		SET @EventData = EventData()
		SET @DatabaseName = '' + @EventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(50)')
		SET @Schema = @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(50)')
		SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')
		SET @EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)')
		SET @SQL = @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)')
 
		/*Check user before allowing changes*/
		DECLARE @Msg varchar(MAX),@Blocked bit=0;
		IF (SELECT count(*) WHERE SYSTEM_USER IN ('NT AUTHORITY\SYSTEM','Domain\AllowUserName')) = 0
			BEGIN
				SET @Msg = 'Direct changes to LIVE are prohibited, Changes made to LIVE need to go through the roll out process';
				SET @Blocked = 1
				ROLLBACK;
				RAISERROR (@Msg,16,1);
				--print @Msg;
				--print'blocked'
			END			
 
		--Continue to log this event	to our logging table
		INSERT INTO [SFCLWareHouse].dbo.SQL_Server_SchemaAudit
			(AuditDate, UserName, DataBaseName, [Event], [Schema], Object, TSQL, [XMLEventData],Blocked)
		SELECT
			GetDate(),
			SYSTEM_USER,--@EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME')
			@DatabaseName,
			@EventType, @Schema, @Object,
			@SQL,
			@EventData,
			@Blocked
 
		/*Notify of blocked changes if the event was not allowed*/
		IF @Blocked = 1
			BEGIN
				DECLARE @MailID int, @HTMLBody varchar(max)
				SET @HTMLBody ='An attempt has been made at changing the SQL Schema for sqlprod.swisscantouk.local <br><br>' +
								'<TABLE style="border:none"><TD>Time:</TD><TD>' + CAST(GETDATE() AS varchar(MAX)) + '</TD></TR>' +
								'<TD>User:</TD><TD>' + SYSTEM_USER + '</TD></TR>' +
								'<TD>Database:</TD><TD>' + @DatabaseName + '</TD></TR>' +
								'<TD>Event:</TD><TD>' + @EventType + '</TD></TR>' +
								'<TD>ObjectName:</TD><TD>' + @Object + '</TD></TR>' +
								'<TD>TSQL:</TD><TD>' + @SQL + '</TD></TR>' 
 
				EXECUTE [DataWareHouse].[dbo].[Mail_INSERT] @MailID = @MailID OUTPUT
							,@TO = 'monitoredmailbox@domain.com'
							,@Subject = 'SQL Prod Schema change attmept'
							,@BodyHTML = @HTMLBody
							,@Importance = 1
			END
 
    END
 

Posted in Infrastructure, SQL, TSQL, Tech.


SSMS SQL Server Management Studio Refresh Schema

Refreshing the intelli-sense (Intellisense) for the current connection to a given database can be a bug bare

There is nothing more frustrating than making additions, or changes in design to tables/views and having the management studio intellisense not pick-up the changes once you have made them. This is because the SSMS schema refresh doesn't occur real-time not does it update local schema changes to those successfully committed to the db while you work, it occurs on close and open of the IDE.

The easiest way to refresh the schema on any connection (also note each new window is a separate connection and as such you need to refresh each window)

You can either

1) Go to Edit -> IntelliSense -> Refresh Local Cache and
OR
2) Hit Ctrl+Shift+R

Hope this helps those with the same issue

Posted in Annoyances, SQL.


SQL 2008 SSRS Web Service access from .NET

When you try to access a report server web service to execute code you get an error similar to, where the scheme or header varies a tiny bit

The HTTP request is unauthorized with client authentication scheme 'Basic'. The authentication header received from the server was 'Negotiate,NTLM

Basically my situation is that we have a MS 2008 Server running SSRS outside of our domain in the DMZ. However we need to execute code on a domain machine that will connect and run over 100 reports on the SSRS Server, then dump them on a share in our domain in excel format.

To get around the negotiation problem you need to make sure the SSRS server is allowing connections configured using basic authentication

Find the file

rsreportserver.config

This is usually buried in the install folder

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

Then change the authentication to support your desired connection authentication type

 
	<Authentication>
		<AuthenticationTypes>
			<RSWindowsBasic/>
			<RSWindowsNegotiate/>
			<RSWindowsNTLM/>
		</AuthenticationTypes>

More info at MSDN
Once you have done that you should be good to connect.
Here is some sample code to get you started with connecting to your web service and pulling back a list of items

 
Imports System
Imports TestExecuteSSRS.SSRS1510
Imports System.IO
Imports System.Text
Imports System.Web.Services.Protocols
Imports System.Xml
Imports System.Xml.Serialization
 
Imports System.ServiceModel
Imports rs = TestExecuteSSRS.SSRS1510
 
Module module1
    Sub Main(ByVal args As String())
        'Dim service = New rs.ReportingService2010SoapClient()
        Dim binding = New BasicHttpBinding(BasicHttpSecurityMode.TransportCredentialOnly)
        Dim endpointUri = "http://<ipaddress>/reportserver/ReportService2010.asmx"
 
        'binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows
        'OR
        binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic
 
        Dim service = New rs.ReportingService2010SoapClient(binding, New EndpointAddress(endpointUri))
 
        'service.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation
        'OR
        service.ClientCredentials.UserName.UserName = "<machinename>\<username>"
        service.ClientCredentials.UserName.Password = "
<password>"
 
        ' Retrieve a list of reports.
        Dim reports As rs.CatalogItem() = Nothing
        service.ListChildren(New rs.TrustedUserHeader(), "/<Target Folder>", True, reports)
 
        For Each report As CatalogItem In reports
            Console.WriteLine("{0} ({1}) : {2}", report.Name, report.Path, report.TypeName)
 
            If report.TypeName = "Report" Then
                'run the report and save it as excel 
 
            End If
        Next
    End Sub
End Module
 

Posted in .NET, Annoyances, Configuration, SQL.


SQL Server Auto Backup Database Script

This Script Is what I use on all my database servers, its never failed and is extremely useful.

Just change the @PATH and the @BaseLine days as you see fit, paste the code into a job and run it everyday at midnight or what ever time your backups tends to run.

 
/*
Written by	: Andre Pageot
Date		: 06/06/2012
Description	:
		This procedure will backup all databases in a SQL server with the exception of reportserver
		Each database will have its checkpoint marked and transactio log truncated before the backup occurs
		Each Database will be on a 5 day rolling backup. Baseline on @BaseLineDay (Monday) and differential throughout the week
		Monday's baseline will delete all previous backups in the rolling set,
		so be sure to have them in another location if you need to revert
		The rolling set will backup for two weeks rolling, two baselines and each baseline will contain a weeks worth of differentials
			Mon Tues Wed Thurs	Frid
			B1	D1	 D2	 D3	D4--Base line 1 with differentials throughout the week
			B2	D1	 D2	 D3	D4--Base line 2 with differentials throughout the week
			B1	D1	 D2	 D3	D4--Overwrite baseline one
			B2	D1	 D2	 D3	D4--Overwrite baseline 2
		--This stragtey allows a 2 week rolling backup
*/
 
DECLARE @name VARCHAR(50) -- database name
DECLARE @PATH VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDay VARCHAR(20) -- used for file name
DECLARE @BaseLineDay VARCHAR(10)
DECLARE @SQL VARCHAR(MAX) --the backup script to execute
DECLARE @BaseLineNumber VARCHAR(1) --Which baseline we are working with
 
SET @PATH = 'E:\SQL\BAK\'
SET @BaseLineDay = 'Monday'
if(DATEPART(wk, GETDATE())  % 2) = 0
	BEGIN
		--print 'even'
		SET @BaseLineNumber = 2
	END
ELSE
	BEGIN
		--print 'odd'
		SET @BaseLineNumber = 1
	END
 
SELECT @fileDay = (SELECT DATENAME (DW,GETDATE()) as Day)
 
DECLARE db_cursor CURSOR FOR
	SELECT  name = D.Name--QUOTENAME(D.name)
	FROM    sys.databases D
	WHERE   (
			   NOT D.database_id BETWEEN 1 AND 4       -- master, tempdb, model, and msdb
			AND NOT  D.name LIKE 'ReportServer%'        -- Report Server
			AND NOT  D.is_distributor = 1                -- Replication
			)
	AND     D.source_database_id IS NULL        -- not a snapshot
	AND     D.state_desc = N'ONLINE'            -- is online
	AND     D.user_access_desc = N'MULTI_USER'  -- open for all users
	AND     D.is_read_only = 0;
 
	OPEN db_cursor
		FETCH NEXT FROM db_cursor INTO @name  
 
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @fileName = @path + @name + '_SET_' + @BaseLineNumber + '.BAK'
 
				SET @SQL = 'USE ' + @name 
 
				SET @SQL = @SQL + '
				CHECKPOINT	--forces the dirty pages in memory to be commited to the transaction log file'
 
				SET @SQL = @SQL + '
				DBCC SHRINKDATABASE (' + @name + '); --new way to truncate the log file (commit all transactions to the mdf)
 
				--perform our backup depending on what day it is (Fridays have a new baseline)
				IF (''' + @fileDay + ''' = ''' + @BaseLineDay + ''')
					BEGIN
						BACKUP DATABASE ' + @name + ' TO DISK = ''' + @fileName + ''' WITH INIT
					END
				ELSE
					BEGIN
						BACKUP DATABASE ' + @name + ' TO DISK = ''' + @fileName + ''' WITH DIFFERENTIAL
					END
					'
					PRINT @SQL
					EXEC (@SQL)
				   --SET @fileName = @path + @name + '_' + @fileDay + '.BAK'
				   --DBCC SHRINKDATABASE (@name, TRUNCATEONLY);
				   --BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
 
				FETCH NEXT FROM db_cursor INTO @name
			END  
 
	CLOSE db_cursor
	DEALLOCATE db_cursor
 

Posted in Uncategorized.


TFS 2010 Windows sharepoint Services Search

You type a search in the text box and hit search, you are then greated with

"Your search cannot be completed because this site is not assigned to an indexer. Contact your administrator for more information."

You have TFS 2010 installed and everything is working, apart from the Search Services in the sharepoint portals

You try to manually start the service in the windows services console but it fails

You have followed microsoft instructions to enable and start the service but you cannot see the search service in central administration > Operations > services on server

Easily Fixed

You just need to do a repair of your WSS 3 install through programs and features. there is a catch though. When you try to perform a repair you recieve another error

"Microsoft Windows SharePoint Services 3.0 1033 Lang Pack - Error 1706th An installation package for the product Microsoft Windows SharePoint Services 3.0 1033 Lang Pack can not be found. Try the installation again using a valid copy of the installation package 'wssmui.msi'."

From Microsoft Download the Service Pack for Windows SharePoint Services Language Pack from here 64bit or here 32bit and install.

Now perform your repair.

Now browse to Central administration > operations > services on server and you will see as if by magic the search service is now available to configure. Click the start.

Configure Windows SharePoint Services Search Service Settings on server

Service Account: can be a local account \ or can be a domain account \
Content Access Account: can also be a local or domain account
Search Database: this is created automatically

NOTE- I suggest Using the account that you configured for the TFS to use when you were installing TFS 2010, this is because it needs full access to the SQL server and should NOT need to be an admin on the machine or domain. If you use the same account it also keep maintenance easy

Final Step - Add Search to an application database

  • goto central administration > application management > manage > content databases
  • Select the application database (on a fresh TFS instal it should be "WSS_Content")
  • Search Server - select the only item in the drop down list (should be the server name)

Posted in Uncategorized.