Joseph Michael Pesch
VP Programming

SQL Date Manipulation

by 25. October 2011 09:35

Here is a query to get the previous quater:

select
  dateadd(qq, datediff(qq, 0, getdate())-1, 0) 
, dateadd(ms, -3, dateadd(qq, datediff(qq, 0, getdate()), 0))

Tags:

SQL Server

ASP.Net .ASMX WebService Invocation Test Page Remote Access

by 18. October 2011 07:32

The ASP.Net .ASMX webservice page comes with an automatic invocation test page for each of the available operations defined in the service.  By default the invocation is limited to the service running on a local machine (e.g. if you are running the service on your local machine and browse to it you can invoke it;).  However, if the service is running on a server and you browse from your local machine you will probably see this message "The test form is only available for requests from the local machine" in the spot where the invocation button would normally be.  To enable remote invocation, make sure to add the web config section shown below.

<configuration>
  <system.web>
    <webServices>
      <protocols>
        <add name="HttpGet"/>
        <add name="HttpPost"/>
      </protocols>
    </webServices>
  </system.web>
</configuration>

Tags:

ASP.Net | Web Service

MDX Query in C#

by 17. October 2011 08:46

 

Requires the AdomdClient, which is included in Microsoft SQL Server 2008 Feature Pack, October 2008, found here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

Download "Microsoft ADOMD.NET " from the link above and after running msi file you will get
Microsoft.AnalysisServices.AdomdClient.dll under folder
C:\Program Files\Microsoft.NET\ADOMD.NET\100.

Tags:

C# | SSAS

ASP.Net Security Issue FormsAuthentication.Authenticate Always Returns false

by 14. October 2011 09:42

I was trying to create a custom authentication screen without using any of the standard ASP.Net security controls (i.e. Login control).  So, I created my custom form and was calling the System.Web.Security.FormsAuthentication.Authenticate(UserName, Password) method.  However, it was always returning false and in fact did not appear to even be hitting my SQL database (which contained the security) as the failed login attempts, etc. were all unaltered.  Furthermore, I changed my connection string to be a bogus reference and the Authenticate still simple returned false (I was expecting it to error).  I then found out that the System.Web.Security.FormsAuthentication.Authenticate(UserName, Password) method is for user information stored in the web.config file.  Instead, I used the System.Web.Security.Membership.ValidateUser(UserName, Password) method which is for the user information stored in the SQL database.

NOTE: When doing this I also then set the authorization cookie by calling:

System.Web.Security.FormsAuthentication.SetAuthCookie(UserName, true);

Here is some sample code:

static public string Login(string UserName, string Password)
{
  if (System.Web.Security.Membership.ValidateUser(UserName, Password))
  {
    System.Web.Security.FormsAuthentication.SetAuthCookie(UserName, true);
    return "1|" + DateTime.Now.ToString();
  }
  else
    return "0|Login failed!";
}

Tags:

ASP.Net | C#

Excel Number Format for Millions

by 12. October 2011 15:24

Here are a couple of custom number formats that allow formatting Excel for Millions.

Sample Number: 1,598,999

Use this format string: $#,##0.0,, ;[Red](#,##0.0,,);- ;

To display this: $1.6

Use this format string: $#,##0.0,,\M ;[Red](#,##0.0,,\M);- ;

To display this: $1.6M

Tags:

Excel

Sharepoint 2010 Registry Entries

by 5. October 2011 12:38

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\LauncherSettings\LoadBalancerUrl
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\Gather\Portal_Content\ContentSources\0\StartPages\0\URL
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\Gather\Portal_Content\ContentSources\0\StartPages\1\URL
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\ResourceManager\ (several entries)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Applications\fd2e5092-25e3-46cf-8fc6-d62c85334b3d\Gather\Search\ContentSources\0\StartPages\0\URL
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Applications\fd2e5092-25e3-46cf-8fc6-d62c85334b3d\ResourceManager\ (several entries)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure\ConfigDB\dsn
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\WSS\CentralAdministrationURL

Tags:

Restart Machine via Remote Desktop Connection

by 5. October 2011 12:36

From Commandline: "shutdown /r /t 0"

Alternatively, CTRL+ALT+END on the keyboard to get the Windows screen where you have shutdown/restart options.

Tags:

Sharepoint 2010 Database Connection Strings in Registry

by 5. October 2011 08:28

I recently installed a new development environment on a virtual machine that was being used as the basis of a new virtual desktop pool for several developers.  My Sharepoint environment was broken once the virtual desktops were created because the installation put the machine name in the SQL connection strings and each of the virtual desktop instances has a unique machine name.  When I attempted to browse to the local sharepoint site I simply received the following error message: "Cannot connect to the configuration database.".  Researching the log file (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Logs\...) I saw the original machine name in the connection string of the failed connections.  Next I looked at the web.config of the root site as well as the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Config\web.config and was unable to find any connection information.  Finally, I found the following registry entries and changed them from the specific machine name to the "localhost" alias.  NOTE: The first entry is the database connection and the other entries are various URL's.

My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure\ConfigDB\

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\LauncherSettings\LoadBalancerUrl

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\Gather\Portal_Content\ContentSources\0\StartPages\0\URL

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\Gather\Portal_Content\ContentSources\0\StartPages\1\URL

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Applications\c0e071e1-533e-457a-8a61-8a86a6b78dcd\ResourceManager\ (several urls are located in this section)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Applications\fd2e5092-25e3-46cf-8fc6-d62c85334b3d\Gather\Search\ContentSources\0\StartPages\0\URL

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Search\Applications\fd2e5092-25e3-46cf-8fc6-d62c85334b3d\ResourceManager\ (several urls are located in this section)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\WSS\CentralAdministrationURL

 

Tags:

Sharepoint

SQL Out of Log Space

by 3. October 2011 15:39

To recover allocated but unused space run this command:

dbcc shrinkdatabase ('DBNAME_HERE', TRUNCATEONLY)

Tags:

SQL Server