Free Microsoft Office Documents Recovery

PowerShell to upload a picture to AD and Exchange

2012-02-03T16:36:00.003-05:00

Friday! Something for fun :)

Import-RecipientDataProperty -Identity User -Picture -FileData ([Byte[]]$(Get-Content -Path "c:\users\me\desktop\mypics.jpg" -Encoding Byte -ReadCount 0))

Users and Groups management on Windows 7 Home Premium

2012-02-02T12:17:00.000-05:00

I had to uninstall Sophos from my home computer and was denied access with
Error 3005 "You are not a member of the Sophos Administrator Group"
There is no GUI to update local users or groups on Windows 7 Home Premium. To grant myself access I had to use a dos command:

net localgroup SophosAdministrator Michael /add
http://www.sophos.com/support/knowledgebase/article/110534.html

Convert InfoPath form to a WebPage

2012-01-31T11:41:00.000-05:00

1. Rename "form.XSN" to "form.ZIP"
2. Extract template.XML and view1.XSL
3. Run the code to convert template.XML to template.HTML
4. Verify that template.HTML looks ok
4. Loop thru other xml docs in your sharepoint library created from that infopath template and convert them all to htmls.

using System;

using System.Collections.Generic;

using System.IO;

using System.Text;

using System.Xml;

using System.Xml.Xsl;

using System.Web ;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

string path;

path = @"R:\\XSN\\EmployeeOrientationForm\\";

　

XslCompiledTransform xslt = new XslCompiledTransform();

xslt.Load(path+"view1.xsl");

// Execute the transform and output the results to a file.

xslt.Transform(path+"template.xml", path+"template.html");

}

};

}

http://msdn.microsoft.com/en-us/library/system.xml.xsl.xslcompiledtransform.aspx
http://code.cmsstores.com/infopath-form-to-html-using-c/
http://vamsheewithyou.blogspot.com/2010/12/infopath-form-to-html-using-csharp.html

Sending Emails with RAISEERROR

2012-01-19T14:29:00.002-05:00

I was going over sql server store procedures and decided to improve error handling with
try-catch code. Everything worked fine except that the RAISEERROR didn't send any emails to an operator.
The solution was simple all I had to do is to add "with log" option.

begin try
...
end try
begin catchraiseerror ('mydb.dbo.myproc failed!',16,1) WITH log

end catch

Email warning when mailbox size is over limit on Exchange 2010 with PowerShell Script

2012-01-06T11:56:00.000-05:00

I had to write a PowerShell script for Exchange 2010 that sends an email to a user when his mailbox size exceeded 1Gb. Here is the script:

If ((Get-PSSnapin | where {$_.Name -match "Exchange.Management"}) -eq $null)
{
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
}
$toArray=Get-MailboxServer | Get-Mailbox | foreach-object {$email = $_.primarysmtpaddress; $_ | Get-MailboxStatistics | Where {$_.TotalItemSize.Value.ToMB() -gt 1024 } |   select @{ expression={$email}}}
$from="from@from.com"
Foreach ($to in $toArray)
{
     $s=[string]$to
   [int]$Start = $s.IndexOf("=")
   [int]$End = $s.IndexOf("}")
   [string]$ss=$s.Substring($Start+1,$End-$start-1)

$messageParameters = @{
smtpServer = “smtp server”
From = [string]$from
To = $ss
Subject = "Warning!"
Body = “Your mailbox is over 1Gb in size. Please delete old email messages to reduce size of your mailbox”
}
   Send-MailMessage @messageParameters -BodyAsHtml
}

Creating Scheduled Tasks for Exchange 2010 PowerShell Scripts:
http://www.mikepfeiffer.net/2010/02/creating-scheduled-tasks-for-exchange-2010-powershell-scripts/
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command ". 'C:\Program Files\Microsoft\Exchange Server\V14\bin\RemoteExchange.ps1'; Connect-ExchangeServer -auto; c:\Scripts\MoveMailboxes.ps1"

Corrupted InfoPath forms get published to SharePoint

2011-11-14T18:00:00.001-05:00

The issue with corrupted forms are only come up when a user has an older version that was previously installed.

The system is requesting to save the form first and then updates it.

The solution to this is to click on the yellow bar with prompt to “Save and Update Form”, cancel the save dialog box and close the form and then reopen the XSN link again.

We can also run "Infopath /cache clearall " command to achieve the same effect:

It is all not very user friendly.

To fix this for good we changed logon script and deleted previously installed forms from cache during user’s logon with:

Erase %userprofile%\APPDATA\Local\Microsoft\InfoPath\FormCache3\*.* /q /s

MS Access 2010 /pwd command line switch is missing

2011-11-14T17:47:00.001-05:00

To automate a report from encrypted database I used a free uility AutoHotkey.

Here is the source script, that I compiled to mydb.exe and then deleted
-----------------------------------------------------------------------------------------------------------------
run "C:\Program Files\Microsoft Office\OFFICE14\MSACCESS.EXE" c:\projects\mydb.mdb /cmd "auto"

WinWait, Password Required, , 3

sendinput {p}
sendinput {a}
sendinput {s}
sendinput {s}
sendinput {w}
sendinput {r}
sendinput {d}
sendinput {Enter}
return

-----------------------------------------------------------------------------------------------------------------

Then I used mydb.exe to run as a scheduled job.

Microsoft Windows Updates Report for SOX Audit

2011-10-31T17:20:00.004-04:00

Our system center server failed to provide a report that would satisfy the auditor.
He wanted to see the latest updates for every single computer : servers and desktops.
To solve this problem I used a PowerShell script to retrieve system event log and filter the output for event code 19 which is:” Installation Successful: Windows successfully installed the following update:...”

get-content f:\WindowsUpdates\pc.txt | f:\WindowsUpdates\reportevent.ps1 –debug

where

Report-Events.ps1 was taken from here http://jdhitsolutions.com/resources/scripts/Report-Events.txt
and modified query string to filter out all other events:

$query="Select ComputerName,Message,TimeWritten,Type,SourceName,EventCode,Logfile from win32_NTLogEvent WHERE (EventCode=19) and Logfile='System' "

And list computers thet were down or failed to obtain the updates:

$err+=" *** No matching events found for $computername ****
"

To populate pc.txt I copied the script from

http://blogs.technet.com/b/heyscriptingguy/archive/2006/11/09/how-can-i-use-windows-powershell-to-get-a-list-of-all-my-computers.aspx

$strCategory = "computer"
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")
$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}
$colResults = $objSearcher.FindAll()
foreach ($objResult in $colResults)
{$objComputer = $objResult.Properties; $objComputer.name}

Html5 links

2011-09-21T17:26:00.002-04:00

Html5 Links: Dive into Html5 - a great overview of what Html5 has to offer.

http://diveintohtml5.org/IE9

Test Drive - cool applications made by the IE team.

http://ie.microsoft.com/testdrive/

Chrome Experiments - like IE9 Test drive but from the chrome team

http://www.chromeexperiments.com/ Apple Html5 - for Safari

http://www.apple.com/html5

/ Dynamic Pages via Media Queries - a list apart has good articles on web in general

http://www.alistapart.com/articles/responsive-web-design/ Html5 demos - Demos for individual features http://html5demos.com/

Internet Explorer links:• beautyoftheweb: http://www.beautyoftheweb.com/•

Internet Explorer Test Drive: http://ie.microsoft.com/testdrive/•

Pin Site Radio:
http://ie.microsoft.com/testdrive/Browser/Radio/Default.html

• Internet Explorer Developer Center:
http://msdn.microsoft.com/en-us/ie
• IEBlog: http://blogs.msdn.com/b/ie/

Hidding fields on DispForm.aspx in Sharepoint

2011-09-21T17:10:00.002-04:00

http://msdn.microsoft.com/en-us/library/aa544142.aspx http://www.sharepoint911.com/blogs/laura/Lists/Posts/Post.aspx?ID=3 http://www.cleverworkarounds.com/2008/02/28/more-sharepoint-branding-customisation-using-javascript-part-2/

Uninstall/Remove/Deactivate Sandboxed solution

2011-08-12T16:23:00.000-04:00

PS C:\Users\spAdmin2010> uninstall-spusersolution

cmdlet Uninstall-SPUserSolution at command pipeline position 1
Supply values for the following parameters:
Identity: csharepointproject.wsp
Site: http://******

Confirm
Are you sure you want to perform this action?
Performing operation "Uninstall-SPUserSolution" on Target
"CSharePointProject.wsp".
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help
(default is "Y"):
PS C:\Users\spAdmin2010> remove-spusersolution

cmdlet Remove-SPUserSolution at command pipeline position 1
Supply values for the following parameters:
Identity: csharepointproject.wsp
Site: http://****
Confirm
Are you sure you want to perform this action?
Performing operation "Remove-SPUserSolution" on Target
"csharepointproject.wsp".
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help
(default is "Y"):
PS C:\Users\spAdmin2010>

Sharepoint 2010 farm admin admin password change

2011-08-12T15:54:00.000-04:00

cd c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\
stsadm –o updatefarmcredentials –userlogin DOMAIN\username –password password
stsadm -o execadmsvcjobs

PowerShell:
Repair-SPManagedAccountDeployment

http://technet.microsoft.com/en-us/library/ff724280.aspx

Sharepoint 2010. The service is unavailable, error code 503

2011-07-08T12:45:00.000-04:00

App log says:
The Module DLL 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\isapi\owssvr.dll' could not be loaded due to a configuration problem. The current configuration only supports loading images built for a x86 processor architecture.

For some reason creating a site collection from the Central Administration sets "Enable 32-bit Applications" to true in the corresponding Application Pool. For 64-bit machines it must be re-set to false.

ASP.NET and Exchange 2010 powershell remote execution from webserver

2011-06-17T12:31:00.002-04:00

How to create a Distribution group maintenece in ASP.NET for Exchange 2010 with help of PowerShell cmd-lets.

http://social.technet.microsoft.com/Forums/en-US/exchangesvrdevelopment/thread/38d223da-2e6d-4a31-aedd-cd6971912bf4/

1. "Winrm quickconfig" must be ran on exchange
2. SystemManagementAutomation.dll v....17514
3. Virtual directory app. pool must have 32-bit enabled



Imports System.Collections.Generic

Imports System.Linq

Imports System.Text

Imports System.Management.Automation 

Imports System.Management.Automation.Runspaces 

Imports System.Security

' For the secure password

Imports System.Collections.ObjectModel

Imports System.Net.Security

Namespace PS2010



    Public Class PS

        Shared Sub AddContact(ByVal strEmail As String, ByVal strName As String, ByVal strDL As String, ByVal Op As String)

            Dim secureString As System.Security.SecureString = New System.Security.SecureString()

            Dim myPassword As String = "adminpass"

            For Each c As Char In myPassword

                secureString.AppendChar(c)

            Next

            Dim credential As PSCredential = New PSCredential("DOMAIN\ADMIN", secureString)

        

            Dim connectionInfo As WSManConnectionInfo = New WSManConnectionInfo(New Uri("http://EXCH/Powershell?serializationLevel=Full"), "http://schemas.microsoft.com/powershell/Microsoft.Exchange", credential:=credential)

            connectionInfo.AuthenticationMechanism = AuthenticationMechanism.Kerberos

            connectionInfo.SkipCACheck = True

            connectionInfo.SkipCNCheck = True

            Dim runspace As Runspace = System.Management.Automation.Runspaces.RunspaceFactory.CreateRunspace(connectionInfo)

            runspace.Open()

        

            Dim command As Command

            Dim cmd As Pipeline

            If InStr(strEmail, "DOMAIN.com") = 0 Then

                strName = "zzz-" & strName

                If Op = "AddAndDL" Then

                    command = New Command("New-MailContact")

                    command.Parameters.Add("Name", strName)

                    command.Parameters.Add("Alias", strName)

                    command.Parameters.Add("ExternalEmailAddress", strEmail)

                    command.Parameters.Add("OrganizationalUnit", "DOMAIN.local/Client Teams/External Contacts")

                    cmd = runspace.CreatePipeline()

                    cmd.Commands.Add(command)

                    cmd.Invoke()

                    Op = "AddToDL"

                End If

            End If

            If Op = "AddToDL" Then

                command = New Command("Add-DistributionGroupMember")

                command.Parameters.Add("Identity", strDL)

                command.Parameters.Add("Member", strName)

                cmd = runspace.CreatePipeline()

                cmd.Commands.Add(command)

                cmd.Invoke()

            End If

          

            If Op = "Delete" Then

                command = New Command("Remove-DistributionGroupMember")

                command.Parameters.Add("Identity", "DOMAIN.LOCAL/Client Teams/" & Trim(strDL))

                If InStr(strEmail, "DOMAIN.com") = 0 Then

                    command.Parameters.Add("Member", "DOMAIN.LOCAL/Client Teams/External Contacts/" & Trim(strName))

                Else

                    command.Parameters.Add("Member", Trim(strName))

                End If

                command.Parameters.Add("Confirm", False)

                cmd = runspace.CreatePipeline()

                cmd.Commands.Add(command)

                cmd.Invoke()

            End If

              

                If Op = "NewDL" Then

                    command = New Command("New-DistributionGroup")

                    command.Parameters.Add("Name", strName)

                    command.Parameters.Add("SamAccountName", strName)

                    command.Parameters.Add("Alias", strName)

                    command.Parameters.Add("Type", "Distribution")

                    command.Parameters.Add("OrganizationalUnit", "DOMAIN.local/Client Teams")

                    cmd = runspace.CreatePipeline()

                    cmd.Commands.Add(command)

                    cmd.Invoke()

                End If

                credential = Nothing

                connectionInfo = Nothing

                runspace.Close()

        End Sub

        

    End Class

End Namespace

Outlook macro to send out email templates from any folder

2011-05-16T16:29:00.000-04:00

The macro below will send out all selected emails from any folder

Sub Send_Bulk()

Dim objNS As NameSpace

Set objNS = Application.Session

Dim Item As Outlook.MailItem

For Each Item In ActiveExplorer.Selection

If Item.Class = olMail Then

Item.Send

End If

End Sub

Auto-reply between specific hours and on weekends from outook.

2011-05-06T15:57:00.000-04:00

------------------------------------------------------------------------------------------------
'--------------------------------------
' outlook VBA
      Dim i As Integer, flag As Boolean

        If ReplyBack("08:00 AM", "07:00 PM", Now()) Then
          If Item.Class = olMail Then
                     If Item.Recipients.Count > 0 And Item.Recipients.Count <= 2 Then

                            For i = 1 To Item.Recipients.Count
                                If InStr(1, Item.Recipients(i), "Desk") > 0 Then
                                    flag = True
                                End If
                            Next
       if flag then
                        Set fwdItem = Item.Reply
                                             fwdItem.Subject = fwdItem.Subject & " *** Auto Reply ***"
                         s = " Call After-Hours Support Line "
                         fwdItem.Body = s
                         fwdItem.Send
    end if
                     End If
                End If
            End If
-----------------------------------------------------------------------------------------------
Function ReplyBack(fromTime As String, toTime As String, curdate As Date) As Boolean
Dim flag As Boolean
flag = False ' dont replay
flag = Not (Val(Replace(Format(curdate, "short time"), ":", "")) < Val(Replace(Format(toTime, "short time"), ":", "")) And Val(Replace(Format(curdate, "short time"), ":", "")) > Val(Replace(Format(fromTime, "short time"), ":", "")))
If flag = False Then ' check dates
flag = (Format(curdate, "w") = 7 Or Format(curdate, "w") = 1)
End If
ReplyBack = flag
End Function

Dynamic GridView in ASP.NET

2011-04-13T17:51:00.000-04:00

I don't do ASP.NET projects very often, but when I need to quickly develop a web application I usually use a GridView(former DataGrid) control and I prefer to build it dynamically in the code.

I am posting my code here so it's easy to find in the future.

DropDown:

conn = new SqlConnection(this.dsAbsTime.ConnectionString);

conn.Open();

SqlCommand cmd = new SqlCommand("mvAbsTimeDropDown", conn);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@Login", UserLogin));

cmbUser.DataSource = cmd.ExecuteReader();

cmbUser.DataTextField = "fullName";

cmbUser.DataValueField = "id";

cmbUser.AutoPostBack = true;

cmbUser.DataBind();

GridView

SqlConnection conn = null;

dgVac.Columns.Clear();

try

{

SqlDataAdapter adapter = new SqlDataAdapter();

conn = new SqlConnection(this.dsAbsTime.ConnectionString);

conn.Open();

SqlCommand cmd = new SqlCommand( "mvAbsTime", conn);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmdDet.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.Add( new SqlParameter("@Login",signon));

adapter.SelectCommand = cmd;

DataSet dataSet = new DataSet( );

adapter.Fill(dataSet);

DataView view = new DataView(dataSet.Tables[0]);

view.RowFilter = "Sort<=200";

view.Sort = "Sort asc";

DataTable dt = new DataTable();

dt=view.ToTable();

this.lblHeader.Text = view[0]["FullName"].ToString() + " Personal Statement For Year " + DateTime.Today.Year;

dt.Columns.Remove("Sort");

foreach (DataColumn col in dt.Columns)

{

BoundField bfield = new BoundField(); bfield.DataField = col.ColumnName;

bfield.HeaderText = col.ColumnName;

bfield.HeaderStyle.Font.Bold = false;

bfield.HeaderStyle.BackColor = System.Drawing.Color.FromArgb(204,204,204);

bfield.ItemStyle.HorizontalAlign = HorizontalAlign.Center;

dgVac.Columns.Add(bfield);

}

dgVac.AutoGenerateColumns = false;

dgVac.DataSource = dt;

dgVac.Columns[0].HeaderText = "Description";

dgVac.DataBind();

catch (Exception e)

{

Response.Write(e.Message.ToString());

}

finally

{

if (conn != null)

{

conn.Close();

}

MS Access Query Timeout

2011-03-09T12:14:00.000-05:00

All of sudden my users start complaining about certain report timeouts when they pick a long date range on a table with 5 million records joined to itself.

I love to play with SQL server performance tools and tune long running queries, but in this case nothing seems to work.

At the end I just had a hunch and replaced all DoCmd.OpenQuery statements with DoCmd.RunSql and miraculously everything started to move fast.

ListBox control with Hyperlinks on MS Access form

2011-03-04T16:35:00.000-05:00

I've tried google and msdn but I couldn't find any workable samples .

By trial and error I came up with these 2 lines of code that worked :)

to add a hyperlink I used this line:

lstPdf.AddItem path & rst!fn & "\Report.pdf"

and for the list's click event this:

Private Sub lstPdf_Click()
Application.FollowHyperlink Me.lstPdf.ItemData([lstPdf].[ListIndex])
End Sub

xp_sendmail: failed with mail error 0x80040115

2011-02-09T17:51:00.000-05:00

After upgrading our Exchange Server from version 2003 to Exchange 2010 I start getting this unpleasant message from SQL Server.

Msg 17925, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040115

I rebuilt Outlook profile, reconfigured SMTP, tested all and everything worked except for xp_sendmail.
MS offered a hot fix, but it was for older version of SQL Server, so I decided to replace xp_sendmail with sp_db_sendmail instead. Firstly I ran these two statements to locate procedures and triggers, and then by quick replace changed xp_sendmail to sp_db_sendmail and @message to @body and the problem was solved.

Declare @StringToSearch varchar(25)
SET @StringToSearch = '%xp_sendmail%'

SELECT OBJECT_NAME(id)
FROM syscomments

WHERE [text] LIKE @StringToSearch AND OBJECTPROPERTY(id, 'IsTrigger') = 1

GROUP BY OBJECT_NAME(id)

SELECT Distinct SO.Name

FROM

sysobjects SO (NOLOCK)

INNER JOIN

syscomments SC (NOLOCK)

on SO.Id = SC.ID

AND SO.Type = 'P'

AND SC.Text LIKE @stringtosearch

ORDER BY SO.Name

Memory leaks in Sharepoint 2010

2011-02-07T12:06:00.000-05:00

I found that SPDisposeCheck.exe is must have tool to troubleshoot various Sharepoint issues.
WinDbg.exe is good too, but too complicated to quickly pinpoint a real issue.

Bulk Import to SharePoint Library from file share and category field update

2010-12-30T10:52:00.002-05:00

For one of my SharePoint projects I had to bulk import bunch of pdf documents to a Microsoft BPOS SharePoint library and assign a category field from respective folder name.
The first mistake I made is making that field a mandatory. All my documents got "check-out" status and I couldn’t find a way to check everything in in one step. So I had to delete everything and remove that category field, then by using windows explorer I copied all documents from a file share to the library again.
For the second task I used my beloved MS Access. I linked the new library and category list to a database and by trial and error wrote a little procedure to populate the category field.

It worked really slow. I would be probably better off writing some sql query, but it was just one time gig and it worked.

Here is the code:
Sub UpdateSharepointCategory()
Dim rs As DAO.Recordset

Dim i As Integer

Set rs = CurrentDb.OpenRecordset("select * from library", dbOpenDynaset)

While Not rs.EOF

rs.Edit

Debug.Print rs.Fields("Name").Value

If rs.Fields(8).Value <> "Folder" Then

s = rs.Fields(18).Value

rs.Fields("Category").Value = DLookup("id", "Categories", "category='" & Trim(Right(s, Len(s) - InStr(s, "/"))) & "'")

End If

'For i = 1 To rs.Fields.Count - 1

' Debug.Print i, rs.Fields(i).Value

'Next

On Error Resume Next

rs.Update

On Error GoTo 0

rs.MoveNext

Wend

rs.Close

End Sub

Sharepoint Datasheet View Problem

2010-12-22T11:32:00.000-05:00

Just completed another SharePoint project and was stumbled with a strange problem, some of client computers couldn't display lists in datasheet view. No matter what I did datasheet view wasn't coming up.
Then I noticed that the client had two versions of IE, 32 and 64-bit, then I checked and found that MS Office 2010 was 64-bit too. After digging around I got across of this article:
http://office.microsoft.com/en-us/sharepoint-server-help/use-datasheet-view-in-64-bit-office-2010-HA101882420.aspx
We now have a dilemma what to use 32-bit office or 64-bit with data connectivity pack.
It seems that 64-bit office has other issues like not being able to email directly from Acrobat Reader 9

Retrieving Author property from MS Office document files

2010-11-19T15:15:00.000-05:00

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Shell32;

using EnvDTE;

namespace getAuthors

{

class Program

{

static void Main(string[] args)

{

var shl = (Shell)Activator.CreateInstance(Type.GetTypeFromProgID("Shell.Application"));

string [] prop;

Shell32.Folder dir = shl.NameSpace(@"c:\temp");

Shell32.FolderItem itm = dir.Items().Item("test.doc");

Shell32.ShellFolderItem itm2 = (Shell32.ShellFolderItem)itm;

prop= itm2.ExtendedProperty("{F29F85E0-4FF9-1068-AB91-08002B27B3D9}4");

Console.WriteLine(prop[0]);

Console.ReadLine();

}

}

}

Network Share Files to Sql Server Table and to OLAP Cube

2010-11-19T11:03:00.001-05:00

Well I am not the first facing this problem http://www.codeproject.com/KB/cs/DiskReport.aspx , but every company has different requirements. In our case root folder name contains client code, that I had to extract and to use for joining MS Dynamics tables.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Net;
using System.Data.SqlClient;
namespace SDriveInsert
{
class Program
{
SqlConnection conn = new SqlConnection("Data Source=server;Initial Catalog=ClientFolders;Integrated Security=True");
StringBuilder sql;
string path = "//fileserver/CLIENTS";
static void Main(string[] args)
{
Program p = new Program();
p.process();
}

public void process()
{

sql = new StringBuilder();
sql.Append(" INSERT INTO [ClientFolders].[dbo].[Folders] ");
sql.Append(" ([FolderName]");
sql.Append(" ,[FileName]");
sql.Append(" ,[JobCode]");
sql.Append(" ,[LastModified]");
sql.Append(" ,[Created]");
sql.Append(" ,[Status]");
sql.Append(" ,[Size]");
sql.Append(" ,[Path]) Values");
conn.Open();
mvDir(path );
}

public void InsFiles(DirectoryInfo d)
{
string name = d.Name;
Int32 JobCode = 0;
string Job = "";
try
{

foreach (FileInfo f in d.GetFiles())
{
string fname = f.Name;

Int32 flength = (Int32)f.Length / 1024;
DateTime fdmodified = f.LastWriteTime;
DateTime fdcreated = f.CreationTime;
path = "//fileserver/CLIENTS";
if (f.FullName.IndexOf("Special") > 0)
{
if ((path + "/Special").Replace("/", "") == f.FullName.Substring(1, (path + "/Special").ToString().Length).Replace("\\", ""))
{
path = "//fileserver/CLIENTS/Special";

}
}
int lastPos = f.FullName.IndexOf("\\", path.Length + 1);

if (lastPos - path.Length - 1 > 1)
{
name = f.FullName.Substring(path.Length + 1, lastPos - path.Length - 1);
}

int start = f.FullName.IndexOf("-")+1;
if (start > 3)
{
int len = f.FullName.IndexOf("\\", start) - start;
if (len > 3)
{
bool result = Int32.TryParse(f.FullName.Substring(start, len).Trim(), out JobCode);
if (result) Job = f.FullName.Substring(start, len);
}
}
StringBuilder sql2 = new StringBuilder();
sql2.Append("('" + name + "','" + fname + "','" + Job + "','" + fdmodified + "','" + fdcreated + "',''," + flength + ",'" + f.FullName + "')");
// Insert this batch
SqlCommand cmd = new SqlCommand(sql.ToString() + sql2.ToString(), conn);
cmd.ExecuteNonQuery();
}
}
catch { }
}
public void mvDir(string strDir)
{
// Make sure we catch exceptions
// in looking at this directory.
Console.WriteLine(strDir);
DirectoryInfo d = new DirectoryInfo(strDir);

InsFiles(d);

try
{
// Loop through the list of directories.
foreach (string strDirName
in Directory.GetDirectories(strDir))
{
mvDir(strDirName);
}
}
catch { }
}

CREATE VIEW [dbo].[vwFolders]

AS

SELECT FolderName, FileName, JobCode, LastModified, Created, Size, Path, CONVERT(CHAR(10), LastModified, 101) AS Modified, YEAR(LastModified) AS YY,

CONVERT(char(4), YEAR(LastModified)) + RIGHT('0' + RTRIM(CONVERT(char(2), MONTH(LastModified))), 2) AS YYMM, CONVERT(CHAR(10), Created, 101)

AS DateCreated

FROM dbo.Folders

GO