Stored procedure for page results and sort by different columns

 

Here is a stored procedure for MS SQL Server that can be used to page the results and allow it to be sorted by different columns. It is fast and easy to use. Enjoy.

--GetPersonPage2005 5, 20, 'NAME DESC'
--GetPersonPage2005 null, 20, 'NAME DESC'
--GetPersonPage2005 5, 20, 'SEARCH_CODE ASC'

alter proc GetPersonPage2005
@StartRowIndex decimal(18,0),
@MaximumRows int,
@OrderBy varchar(50)
as

SELECT [NAME], SEARCH_CODE, CREATED
FROM
     (SELECT [NAME], SEARCH_CODE, CREATED,
               ROW_NUMBER() OVER
            (
            ORDER BY -- add columns to sort by here
                CASE @OrderBy WHEN 'NAME DESC' THEN  [NAME] END DESC,
                CASE @OrderBy WHEN 'NAME ASC' THEN  [NAME] END ASC,
                CASE @OrderBy WHEN 'SEARCH_CODE DESC' THEN  SEARCH_CODE END DESC,
                CASE @OrderBy WHEN 'SEARCH_CODE ASC' THEN  SEARCH_CODE END ASC,
                CASE @OrderBy WHEN 'CREATED DESC' THEN  CREATED END DESC,
                CASE @OrderBy WHEN 'CREATED ASC' THEN  CREATED END ASC
            ) as RowNum
      FROM ALL_PERSON e
     ) as Tbl
WHERE RowNum BETWEEN @StartRowIndex AND (@MaximumRows + @StartRowIndex - 1)   
Order by RowNum ASC
go

How to copy Oracle data to MS SQL Server

 

This tutorial gives you the bare minimum information to copy data from an Oracle database to a MS SQL Server database using SSIS (Integration Services).

Edit tnsnames.ora

Create an entry in tnsnames.ora file. This is a file that Oracle uses to abstract the server, port, etc and make it easy to reference. This file resides on the machine that SSIS is running from. The changes will also need to be done on the server were SSIS runs. If you don’t have Oracle installed on that server, you need to do that also.

Here is an example that we will use in this example:

TEMPTEST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = serverNameHere)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORACLETEST)

)

)

You can make sure you have this configured properly by opening up a command prompt and typing

sqlplus username/password@TEMPTEST

If you get a SQL> prompt then you have configured the tnsnames.org entry ok.

Create a project

In Business Intelligence Studio or Visual Studio if you have it, create a new Business Intelligence project.

Add an Oracle Connection

There are two types. One that is from Microsoft and the other that is from Oracle. Either one will technically work.

MS OLE DB Provider for Oracle

This requires an entry in the tnsnames.ora file that matches the name of the Server you enter in the configuration dialog for the OLE DB datasource.

Click the Test Connection to verify it works.

Oracle Provider for OLE DB

This requires an entry in tnsnames.ora file that matches the Server you enter in the configuration dialog for the OLE DB datasource.

Click the Test Connection to verify it works.

Add MS SQL Server Connection

There are like Oracle several options for connecting to a MS SQL Server database. In this example, I am going to use OLE DB. Right-Click the Connection Managers area and choose New ADO.NET connection. In this case we are connecting to a named instance called SQL2005. If you are using the default instance of SQL Server, then just remove the \SQL2005 from this example. The name of the database is called TestSSISDB. The user is also called TestSSISDB.

Add Data Flow Task

Drag the Data Flow Task onto your Control Flow tab / page. Rename the task something like “Copy Data”. Double-click the task to open the Data Flow tab.

Add OLE DB Source

Drag the OLE DB Source onto the Data Flow tab.

Rename it something like My Oracle Data Source.

Double-click the OLE DB Source to open the Editor.

Enter information like the following:

Use the Preview button to make sure your query works.

Create the Destination Table

Create a table in TestSSISDB using MS SQL Server Management Studio or like tool.

The table should have a ID (identity column), object_id, first_name, last_name.

Use the following to create it if you prefer:

BEGIN TRANSACTION

GO

CREATE TABLE dbo.LocalPerson

(

ID int NOT NULL IDENTITY (1, 1),

Object_ID varchar(100) NULL,

First_Name varchar(500) NULL,

Last_Name varchar(500) NULL

) ON [PRIMARY]

GO

ALTER TABLE dbo.Table_1 ADD CONSTRAINT

PK_Table_1 PRIMARY KEY CLUSTERED

(

ID

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

Add Copy Column Transformation

Drag the Copy Column Data Flow Transformation to the Data Flow tab.

Connect the arrows to and from this transformation to the data source and destination.

Double-Click transformation and click the columns you want to copy.

Add OLE DB Destination

Drag the OLE DB Destination onto the Data Flow tab.

Rename it something like TestSSISDB SQL Destination.

Double-click the OLE DB Source to open the Editor.

Enter information like the following:

Be sure to select the right connection manager.

Review the Mappings, and Advanced Options.

Configuring Encryption of Sensitive Data

It is important that you configure how encryption will be handled. To do so, make sure the Properties panel is visible. Click the page / background on one of the tabs (i.e. Control Flow or Data Flow). You will see a lot of properties in the panel. At the top of the panel it should say Package <package name>. Enter a password in the PackagePassword field, and change the Protection level to EncryptSensitiveWithPassword. Note that the password will not show in the connection strings or in the PackagePassword field. It should look something like this.

Build package

Testing Package

F5 to run.

Deploying Package

Locate the tnsnames.ora file and make the same change as we did to the development machine. Be sure to test the configuration as we did for the development machine also.

Copy Package.dtsx file to server.

Open up MS SQL Server Management Studio and connect to the Integration Services found on the server where it will run.

Under Stored Packages | MSDB right click MSDB and choose Import Package…

Import package as shown below:

Be sure to select “Encrypt sensitive data with password” from the Protection Level field. If you don’t, the package will run by itself, but it will NOT run as a job.

It should prompt you for the password you entered in the PackagePassword field in Visual Studio property panel for package. If it doesn’t prompt you for the password, the package in Visual Studio is not configured correctly. Be sure you built the package after configuring the password.

Test Deployment

To test your deployment, right click the package you imported and choose Run Package. If it has issues, go back to Visual Studio and add logging to a text file and consume appropriate events to help debug the package.

More information

Here is a good tutorial to get you start or give you more details than this tutorial does.

http://msdn2.microsoft.com/en-us/library/ms167031.aspx

Loading an Assembly dynamically at runtime

I recently needed to figure out if an assembly loaded at runtime. In particular, I was using this to determine what version of an assembly was being loaded and where it was loaded from when I was trying to troubleshoot a server deployment. The code below can be used to put together a web page that you can hit on the server you are deploying to. The information here can be very valuable when trying to figure out why something is not working properly.

string nameOfDLL = "System.Xml.Linq"; // just an example
bool partialNameGiven = true;

// flag if the load failed or succeeded
bool successfullyLoaded;

try
{
    Assembly asm;
    if (partialNameGiven)
    {
        asm = Assembly.LoadWithPartialName(nameOfDLL);
    }
    else
    {
        AssemblyName asmName = new AssemblyName(nameOfDLL);
        asm = Assembly.Load(asmName);
    }

    if (asm != null)
    {
        // loaded the assembly so get some basic info about it
        string loadedAssemblyName = asm.FullName;
        string location = asm.Location;
        successfullyLoaded = true;
    }
    else
    {
        // couldn't load the assembly
        successfullyLoaded = false;
    }
}
catch (Exception ex)
{
    // do error handling here
    successfullyLoaded = false;
}

Troubleshooting STSADM Command Line Error message

The stsadm.exe that is used to administer SharePoint has terrible error handling. The only error message it seems to ever give when there is something wrong with the syntax is Command Line Error. If you get the syntax then you can get better error messages.

I checked, there is nothing in the Event Log, or any other SharePoint logs that I am aware of. So, what can you do?

Here are the basic things I would check when you get the dreaded Command Line Error from stsadm.exe.

1. Make sure you spelled everything correctly
2. Make sure you didn’t forget any parameters
3. Make sure you didn’t forget the –o
4. Check for extra or missing spaces

If you copied the code from anywhere (especially a web page, or MS Word or Word Pad or Outlook) here are some additional things. Rich text editors such as MS Word and web pages often try to “help” you by using proper typographical characters. The problem is they are not what you want to use for command line or coding in general. I have listed the special characters that often fall into this category and should be checked

1. Use hyphens, not En Dash or Em Dash. For details on the differences, see here.
2. Use double quotes, not smart quotes, curved, slanted, etc. For details on the differences, see here.
3. Use single quotes, not smart quotes, curved, slanted, etc. For details on the differences, see here.

Don’t be fooled, sometimes you can’t visibly see the difference in Notepad or the like. They are valid characters so Notepad will still display them just fine. One way I know to fix the problem is to go and do a search and replace or manually retype all the suspected characters over to make sure they are right.

How to remove a user from the People Picker in SharePoint

The People Picker in SharePoint is quite clever and confusing. The reason is that it brings in results from more than one source.

It pulls information from:

• The Site Collection User Information List (UIL)
• The Profile Database (User Profile Service Application)
• Active Directory

So, the only way to remove a user from the People Picker is to make sure you have removed them from all 3 locations. Let’s start at the bottom of the list. Active Directory is usually where users are deleted from first. You can delete the users from the Profile Database using the Central Administration UI for the User Profiles Application.

Most people expect that they would not show up in SharePoint anymore, but that is not the case because once a user accesses a site collection in SharePoint they are added to the UIL (User Information List). This is basically a list of users that have accessed SharePoint that the People Picker also uses also. You will need to remove them from the UIL if you don’t want them to show in the People Picker. To do that you can check out an excellent post here that tells you how to do it using PowerShell or using the UIL interface. The UI is hidden, but can easily be located by going to the root of your SharePoint installation and appending _catalogs/users/simple.aspx. An example would be http://mysp/_catalogs/users/simple.aspx. The UI is ok if you don’t have a lot of users, but if you do, paging through results can be very time consuming. In that case, I would recommend the PowerShell route.

Introduction to Razor in MVC

Razor is a view engine that is used in MVC 3 and later. Files use the file extension .cshtml.

One of the nice things is that it is unit testable with standard Visual Studio unit tests.

A cleaner and more elegant view engine than the default view engine.

You can use multiple view engines in the same project, including ones that third-parties have created.

Syntax

It is simpler to use than the Web forms engine syntax used by the default MVC view engine.

Default way: <%: Html.ActionLink(“Register”, “Register”) %>

Razor way: @Html.ActionLink(“Register”, “Register”)

You’ll notice there is no closing code block needed and the opening code block is just the @ sign which is easier to type. It will still automatically encode the string as well.

Code Block

if you are using Html.BeginForm or other blocks of code you can do something like this. No need for the old <% %> tags

@Html.BeginForm() {
… <some html here>…
}

Comments

You can do a server side comment with

@”
Comment here
”@

Layouts

Razor uses Layouts instead of Master Pages. They are just like other Razor files except it uses @RenderBody() to designate where content should be. Each view has a Layout property that you can set to the layout file. Alternatively, you can define the default by creating a file that is has a special name_ViewStart.cshtml. That way you don’t need to specify the layout property on each view. You can also put the file in subfolders in the View folder to have only the views in that subfolder be affected.

Good tool for programmatically working with CSV files

If you are looking for a good CSV parser look no more. Check out http://filehelpers.sourceforge.net/

Besides reading and writing CSV file using .NET (C#), it allows you to work with a CSV file in a strong-typed manner.

You can even read/write to a database or Excel file from the CSV file.

It even works with fixed width and delimited files as well.

Create a Windows share programmatically

To create a share called mydir that points to c:\mydocs\mydir and gives everyone full permissions you would use the following command

net share mydir=C:\mydocs\mydir /GRANT:Everyone,FULL

NOTE: If you are using Windows XP, or Windows Server 2003, I recommend using Everyone with full permissions and then using the ACL (Windows permissions) to restrict access. Otherwise, you have two levels of permissions and they can be difficult to maintain if you try to make them match. Newer versions of the OS make this work better though.