SSRS Split method to get the last element of the split array

One of the report columns had the entire folder path, but all I wanted to show was the innermost folder name and exclude all the parent folders.
For example, from the path C:\Dropboxes\DeviceType\LogFiles\DeviceName\Tuesday\ the report should just show "Tuesday".

Split function is the first thing that comes to mind and that's what I saw in SSRS list of expression methods and used.

=Split(Fields!FolderPath.Value.ToString(),"\",-1)(Split(Fields!FolderPath.Value.ToString(),"\",-1).GetUpperBound(0)-1)

The second split is to get the count of total elements as I need to find the last element of the split array, and the folder path depth is not static.

ASP.Net authorization and Custom Role Provider

In the application I was developing, I had custom role provider implemented for laying out the menu items depending on the privilege each web user has. It had been a while since this part of the application was done. Towards the end of development a requirement came up to redirect users to a "Request Access" page if they do not have access to the application.

I thought of implementing this the easiest way by setting values for in web.config.

< authorization>
< allow roles="AppAccessGroup"/>
< deny users="*">

"AppAccessGroup" was a group I created on my local machine with my NT-id as a member of the group.
But it didn't work right off the bat, and that was confusing. I also had Anonymous access turned off in IIS, so the browser was prompting me for NT credentials. While trying to figure this out, I had a surprising find here: http://blogs.msdn.com/b/dougste/archive/2006/07/27/680031.aspx

But that fix didn't work too.

After a while it stuck me that it may be the custom role provider and I commented that out, to test. And voila!, it worked.

I hadn't seen anybody mention this anywhere, but sure somebody must have had this issue and figured it before.

Server Application Unavailable

This was my first ASP.Net 4.0 application deployment on IIS 6.0. The website was published under "Default Web Site" that hosts other Virtual Directories too, targeting ASP.Net 1.1 & 2.0.

When I tried to access the site, I got the error:

Server Application Unavailable

The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.

Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.

And the event viewer said:

It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process.

Looking for resolution, I came across this blog: http://weblogs.asp.net/owscott/archive/2006/01/26/436607.aspx

I rechecked and my application was using the application pool created for .Net 4.0 apps; so the issue was not with application pool. After a bit of more wandering I started checking all websites that were using the .Net 4.0 application pool. And it turned out somebody had their website use the .Net 4.0 application pool but target .Net framework 2.0. Argh!!

The package failed to load due to error 0xC0010014

We recently got SQL Server 2008 and deployed an instance on one of our QA servers. Nothing out of the ordinary, till I tried to deploy a 2005 SSIS package on the QA server.

As usual I took SSISDeploymentManifest file, right click and selected Deploy. I was doing a file system deployment, so the first thing out of ordinary that I noticed was, the installer gave me SQL 2008 deployment path; which I assumed was okay as the 2008 installation may have overwritten some registry default value. So I selected the path where I wanted it to be and rest of the process went without a problem.

Now when I go to SQL 2005 to create a job process for the package, it gives me the error

TITLE: SSIS Execution Properties
------------------------------

The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.


------------------------------
ADDITIONAL INFORMATION:

The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.

It turned out the default SSIS installer launched was for SQL 2008 and it didn't read anything from the manifest to identify which installer it should invoke (like Visual studio does).

The way to deploy SSIS packages in such scenarios is to right click and go to "Open With". The interesting thing here was that both options listed here are : "SQL Server 2005 Integration Services Package Installation Utility". So prima-facie we won't know which one to select. I tried both and it turned out that the second option was the one that is for SQL 2005.

On a bit further investigation, I found that the "Description" on the "Version" tab of "Properties" of dtsinstall.exe is still "SQL Server 2005 Integration Services Package".

Looks like somebody at Microsoft overlooked this step of testing. :)

Login failed for user "username"

I ran into the login failed for user "username" again. This time it was different from what I had faced the first time around.

My package uses config file to get the sql credentials. When I have the package open in my IDE and add another parameter to the file it wipes out the previously saved password. So now when I try to execute the package from my IDE to test the changes, I get this dB login failure message.

What threw me off was I was looking at the connection defined in the package designer and all seemed well.

So if this happens again, you need to take a look again in the config file to check if the password value is still there.

DateTime String patterns

Once in a while I find myself confused with which string pattern to apply to a .Net DateTime object.

I have found this web page (http://www.geekzilla.co.uk/View00FF7904-B510-468C-A2C8-F859AA20581F.htm) to be very useful and handy.

Thought of putting it here -- for my ready reference and in case somebody stumbles across this post.

And the MSDN article: http://msdn.microsoft.com/en-us/library/az4se3k1.aspx

ASMX Web Service as Service Reference in VS 2010

This was an interesting thing I observed.

I started a new project on VS 2010 & .Net 4.0. For some data I had to connect to the Web Service that was build with early version of .Net framework. So I added the web service as a "Service Reference" in my project.

The web service in question has 4 overloaded search methods with "MessageName" attributes set to "search1", "search2" etc. {Not very intuitive, I know, but that's the way it is}. The one I wanted to call has the MessageName "search4".

Now when I go to my ASPX page to call the web method, I was confused not to find "search4" method listed in the intellisense. It would give me an error if I type down the method name. All the while I could see the "search4" method through my web browser.

After a bit of digging into the associated files created for each service reference, I found that in "Reference.cs" under "Reference.svcmap" this line of code:

// CODEGEN: Generating message contract since the wrapper name (search4) of message search4 does not match the default value (search3)
[System.ServiceModel.OperationContractAttribute(Action="http://tempuri.org/search4", ReplyAction="*")]
[System.ServiceModel.XmlSerializerFormatAttribute(SupportFaults=true)]
NS.MyProject.WS_Test.search41 search3(NS.MyProject.WS_Test.search4 request);

The auto generated code explanation sounded a bit weird to me. Expose the method as it was intended by the Web service developers. Why rename them?

An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available.Hresult:Hresult: 0x80040E4D Description: "Login failed for user '

Error:
"DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user ''."

This error is interesting in that the user name and credentials are entered correct in the SSIS Connection but you still get this error.

There are whole lot of reasons you may find on Internet. But no one sequence worked for me. What made this go away in my package was:
a.) Set "DelayValidation" to true, as I was seeking connection at run time from database/xml file.
b.) Set "Protection Level" property to "EncryptSensitiveWithPassword" and provide a package password.

I found a good explanation for various causes, though unrelated to my issue, here:
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/09/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx

Could not find default endpoint

This was a weird error I got while trying to consume a WCF service in my web application.

Could not find default endpoint element that references contract 'WS_User.IUser' in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.

My project setup was so that the service reference was made in a class library whose method, user authentication, was invoked by the web application. So the solution to this problem was that I had to have the binding information at two places:
a.) The class library that was making the call to WCF service, and
b.) The web application that was making a call to the method in class library.

A bit weird behavior in my view, but that did away with the issue.

Could not find stored procedure 'sp_stop_job'

Deployed my SSIS package to QA and the job threw an error

Could not find stored procedure 'sp_stop_job'.

Turns out I had made the call to sp_stop_job in the current connection that was pointing to the logging database.

Just had to modify the call to look like "msdb.dbo.sp_stop_job"

DataFlow Script Component Variables

In SSIS the dataflow script component is pretty strict about how to pass on the variables to the component -- ReadOnlyVariables or ReadWriteVariables. The ReadWriteVariables variables can be used only if you are overriding PostExecute event

Public Overrides Sub PostExecute()
Variables.ErrorMessage = errorMessage
Variables.ErrorDescription = innerException
MyBase.PostExecute()
End Sub

I found the hard way that even if you are not writing to a variable but have passed the variable as a ReadWriteVariable to the component and accessing it in CreateNewOutputRows() method; it will throw an error.

And the error message is not very clear about what could be wrong. The error I kept getting was:

[Create output tables [1]] Error: System.NullReferenceException:
Object reference not set to an instance of an object.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

CopyToDataTable: LINQ to DataSet

Linq has a built in CopyToDataTable() method to convert resultset of a LINQ query to DataTable. This works fine if the query returns DataRow type. But surprisingly they don't work if a LINQ query implements a JOIN.
For example, the resultset of following query can not be converted into a DataTable using CopyToDataTable() method.

var final = from s in sourceTable.AsEnumerable()
join d in destinationTable.AsEnumerable() on s.Field("Id1") equals d.Field("Id2")
select new { Name = d.Field("Name"), Zip = s.Field("Zip"), Error = d.Field("Error") };

After a bit of toiling I found that to achieve this it will require implementation of an extension method. It was described here:
http://blogs.msdn.com/b/aconrad/archive/2007/09/07/science-project.aspx

And also on MSDN

I was using the CopyToDataTable() method over other queries too and implementation of the extension methods as suggested above resulted in exceptions where the method was called earlier.

I figured it will be safe if I rename the extension methods to avoid ambiguity. And that also helped the earlier exception to go away.

Using themed css files requires a header control on the page. (e.g. head runat="server" ).

A search with the title text yields thousands of results each with almost similar solutions:
a.) Put the

tag on the page.
b.) Set EnableTheming = "False" on page. But this in itself doesn't work. Another workaround I saw was set StylesheetTheme="" Theme="" on the page. Even this didn't do the trick.

What worked for me was the comment on MS Connect page: http://connect.microsoft.com/VisualStudio/feedback/details/102039/using-themed-css-files-requires-a-header-control-on-the-page-e-g-head-runat-server">

So I added a web.config in the folder where I had the page that was throwing an exception and insert

< pages theme="">

My page was using MS Virtual Earth and this error would block my custom code to be called from the Mapping.js file to display the push pins on the map.