SharePoint, .Net, SQL, MS BI, Collaboration and Gadgets

If you trying to extract data from a flat file and got the following error:

Error 1 Validation error. Extract Interface Message ID Data: Extract Interface Message ID Data [1]: The data type for “output column “InterfaceMessageBody” (54)” is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.

It looks like one or more column datatype in the FlatFile Source has become DT_NTEXT instead of DT_STR.
You can try the following steps to rectify the error:

1. Right Click the Flat File Source Component that is generating this error.
2. Choose “Show Advanced Editor” from the context menu and then the “input and output properties” tab on the dialog box that appears.
3. Click on the + sign beside “Flat File Source Output” node in the treeview on the left side of the dialog box.
4. Click on the + sign beside “output columns” node in the treeview just below “Flat File Source Output” node. You would see all the columns available in the text file.
5. Select each column one after another and check their dataType property on the Right side of the Dialog box.
6. If the datatype is DT_NTEXT then change it to DT_STR.
7. Repeat step 6 for the all the columns where if columns datatype is DT_NTEXT.

* Thanks to Ritesh Modi for the solution.

If you have two aspx pages (sharepoint views) and need to pass some value from the parent view (aspx page1) to the child view (aspx page2) based on the user selection and filter the second view based on that parameter. Please check it out here…

http://www.theserverside.net/tt/articles/showarticle.tss?id=WebParts2

We must use xp_cmdshell and an application which executes the command like sqlcmd or OSQL (and the –I switch). Here’s an example of how to do it:

osql -S%1 -U%2 -P%3 -d%4 -i”.\DataConversion.sql”

where –S,-U etc are the various options…

Here are some simple codes to do it:

1. Add the group (newMembersGroup)
2. Set the permissions of the group (role assignment) to contributor
3. Add the author to the group.
4. Associate the group to the site.
Dim groupOwner As SPUser = site.Author
site.SiteGroups.Add(newMembersGroup, groupOwner, Nothing, “”)

Dim membersGroup As SPGroup = site.SiteGroups(newMembersGroup)
membersGroup.OnlyAllowMembersViewMembership = False
membersGroup.AllowMembersEditMembership = False
membersGroup.AllowRequestToJoinLeave = True
membersGroup.AutoAcceptRequestToJoinLeave = True
membersGroup.Update()

Dim contributor As SPRoleDefinition = site.RoleDefinitions.GetByType(SPRoleType.Contributor)
Dim membersRoleAssignment As SPRoleAssignment = New SPRoleAssignment(CType(membersGroup, SPPrincipal))
membersRoleAssignment.RoleDefinitionBindings.Add(contributor)
site.RoleAssignments.Add(membersRoleAssignment)
‘Add the site author as a member
membersGroup.AddUser(site.Author)
membersGroup.Update()
site.AssociatedMemberGroup = membersGroup
site.Update()
More info in Role Assignments: http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.sproleassignment.aspx

More info to create your Group (now Cross Site Groups) : http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.spgroupcollection_methods.aspx

Just to share out some documentations here:

• http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
• http://www.microsoft.com/technet/interopmigration/sql.mspx
• http://www.convert-in.com/sql2mss.htm
• http://www.swissql.com/sql-server-migration.html  (This is a decent tool that helps in the migration.)
• http://www.bitpipe.com/detail/RES/1149540179_37.html (this is for SQL – MySQL but in reverse would work)

There are few ways we can backup individual tables. Here are some of them:

1. Use BCP or SSIS/DTS.
2. 3rd party vendors do it like litespeed (http://www.quest.com/litespeed_for_sql_server/ ).  Litespeed on the backend is just really doing a BCP for the most part. They’ve just made it easier by putting a GUI
3. Ditto for SQL BackTrack by BMC.

There are conditions where we have our development environment on a SQL express (vs 2005) machine and we are trying to get the same code to run on a SQL Server 2005 machine. But, we cannot connect to the ASP.NET provider db even after registering a sql db in asp_net_regsql or something like that. By default, it uses whatever settings are in the machine.config file. So, that file usually has the settings that point to using a LocalSqlServer connection string, which is also defined in the machine.config as a user instance DB in the DataDirectory.

There are 2 ways to make it works:

1. Removing the LocalSqlServer connection string and then adding one to your point to your DB in web.config. The connection strings section of your web.config file should look something like this:

   <connectionStrings>
   <remove name=”LocalSqlServer”/>
   <add name=”LocalSqlServer”
   connectionString=”Data Source=Server\Instance; Integrated Security=SSPI; Initial Catalog=database;”
   providerName=”System.Data.SqlClient”/>
   </connectionStrings>

2. Provide your own <membership> section and specify the specific connection & provider type to use for membership.

   <configuration>
   <connectionStrings>
   <add name=”MySqlConnection” connectionString=”Data
   Source=MySqlServer;Initial Catalog=aspnetdb;Integrated
   Security=SSPI;” />
   </connectionStrings>
   <system.web>
   <membership defaultProvider=”SqlProvider” userIsOnlineTimeWindow=”15″>
   <providers>
   <clear />
   <add
   name=”SqlProvider”
   type=”System.Web.Security.SqlMembershipProvider”
   connectionStringName=”MySqlConnection”
   applicationName=”MyApplication”
   enablePasswordRetrieval=”false”
   enablePasswordReset=”true”
   requiresQuestionAndAnswer=”true”
   requiresUniqueEmail=”true”
   passwordFormat=”Hashed” />
   </providers>
   </membership>
   </system.web>
   </configuration>

* Thanks to Shawn Cicoria and George McKee for the solution.

I got chance to work in different type of companies in my working life. Basically it can be categorized in to 2 types; either project base or product base. However, I found that most of the project or product development is not being managed well. You can see someone is doing nothing all the time and someone is busy like hell. Manager or top management never knows exactly what the progress is. We do not really have a system to manage, assign task to worker, and keep track the progress of each individual’s task. We always end up with sending, replying email in order to communicate, and sharing files among team members. It was really a terrible experience.

I had spent some of my free time to develop an online collaboration solution for teamwork and project management, ajaxworkspace.com. I know this cannot compare with other professional collaboration tools like groove or SharePoint since this only develop by me alone. However, I hope I can improve it to become better and better. Hopefully, it can be use by SOHO user, freelancer and small-mid size company. Here are some of the product features I get from ajaxworkspace.com

AJAX workspace offers a secure Web-based workplace consisting of an integrated suite of simple and easy to use collaboration application. You can efficiently organize yourself, manage projects and work with your entire enterprise, colleagues and team member from one central location. The ideal tool for any type of project management regardless if you are working in a company or if you are working as freelancer from home.

Read the rest of this entry »

I bought a book “Pro WF: Windows Workflow in .NET 3.0 (Expert’s Voice in .Net)” from amazon early April. I suppose to receive it somewhere around 18-25 april, but don’t know why I still do not get it yet It cost me RM 150++! Nothing I can do now. Just keep waiting… Hopefully will get it soon…

Business Scorecard Manager 2005 is not supported officially in MOSS 2007. Here are some work around to make it work.

To install BSM 2005:

1. Make sure all prerequisite software like ADOMD.NET and msxml were installed.
2. Run updateBSM.vbs update registry for SharePoint site. You need to run it again when creating a new site.
3. Proceed to run server.msi and builder.msi

To deploy BSM 2005 to MOSS:

1. In your MS Business Scorecard Builder IDE, proceed to deploy to SharePoint site as usal. The newly deployed page never work. We just want to deploy necessary web parts and it’s scorecard entry to SharePoint server.
2. To view the Scorecard, add “Office Scorecard View” and “Office Report View” web part to your page. If you encounter error web part cannot desirable, make sure you add in the
   <add key=”Bpm.ConnectionString” value=”Integrated Security=SSPI;Initial Catalog=ScorecardServer; Data Source=(local)”/>
   in web.config under appsetting and change the data source accordingly.
3. Once web part added, just modify the web part as usual to select your Scorecard and View.