Steven Wright Blog

I had a few reports for an application that needed to be deployed to number of different report servers.   The report servers were Express edition of Reporting Services 2005 with the application database on the same server which helped with the connection limitation of Reporting Services 2005 Express. As the report servers were joined to a domain their computer names have to be unique, the only way application was going to get around this problem was to use host file redirect.   To help with deploying the report I was to going to try to use the host idea, so I added the server name to the localhost entry and tried to run the reports.   Unfortunately this would not work because it looks like the reporting services 2005 express checks the name of the computer, which it gets from the following path HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\ComputerName\ActiveComputerName, against the datasource within the connection if they don’t match then raises an error.

We had a requirement to allow a front page, which was another local report, to be printed before the report within the report viewer was printed.  As both reports were local reports it was easy to create a print button which allowed us to meet the requirements, here is the code that we used: 1: if ( this .printFrontingSheetCheckbox.Checked) 2: { 3: LocalReport frontsheet = front.reportViewer1.LocalReport; 4: Export(frontsheet, 8.27f, 11.69f, 0f, 0f, 0f, 0f); 5: m_currentPageIndex = 0; 6: Print( false ); 7: 8: foreach (Stream s in m_streams) 9: { 10: s.Close(); 11: } 12: front = null ; 13: } 14:   15: LocalReport report = reportViewer.LocalReport; 16: Export(report, 11.69f, 8.27f, 0f, 0f, 0f, 0f); 17: m_currentPageIndex = 0; 18:   19: Print( true ); foreach (Stream s in m_streams) 20: { 21: s.Close(); 22: } 23:   24: private void Print( bool landscape) 25: { 26:...

For my current project I have a stored procedure that needs to sort data by 1 of 3 columns depending upon the option chosen by the user from the application.  This is only the dynamic part of the stored procedure, which also happens to be very complicated, so I didn’t wish to use dynamic SQL Server.  I know that within SQL server 2000 you can use a case against a variable to select which column to order the data by but the only caveat is that all the columns have to be the same data type.  My problem was that not all my columns are the same data type, I managed to over come this by using the new function within SQL server 2005 call Row_Number(), which gives a row number to each row, based upon the order by used within the over function, as this always returns the same data type it can be used within the case statement for the ordering. Below is an example of code: 1: DECLARE @vOrderby AS INT 2: SET @vOrderby = 4 3:   4: SELECT TABLE_CATALOG , TABL...

I have found that the reporting services report viewer control might not use the correct paper size to render the report.  The reason for this can be found on the following link: click here . Quick summary of the solution is that if you wish the report viewer control to render A4 page size instead of Letter then within the report define the pages size of A4 in inches.

I have just managed to solve the problem of how to give an application the ability to print Avery labels with data from our database. I only had to support two label formats (L7161 and L7163) and the data requirement was static as well. So I looked at RDL / Reporting Services and the Report Viewer control. The way I wrote the report is as follows: Set the page size to A4 and all the margins to 0cm. Made sure that the report was not using a Header or Footer. Placed one list control per column of labels on a sheet Changed the size of the list control to be the same as a single label Then set an expression on the list visibility based on the result of the rownumber modulus by the number of columns (making sure each column looked for a unique result) Then added the textboxes for the data making sure there was a gap between the top and bottom of the list control. Also made sure that the text boxes didn’t push the list box size out. I did have trouble trying to line the labels up 10...

Within my current project I have been using SQL Server 2005 merge replication.  While I have been testing some issues have been raised by our testers and I would like to write about another one of these in particular. Data, which was entered at the subscriber, was being removed while synchronizing with the publisher.  This can happen when the data entered isn’t part of the partition for a given subscriber.  However, on this occasion we expected the data to remain in place.  After some investigation by me and my colleague, James Rowland-Jones , we found the issue. We noted that the affected tables were organised in parent child relationships at a table / schema level but they were joined child parent in the replication topology.  They were joined via a join filter with a partition filter on the child record.  So Customers 1 -> N Orders but partitioned On Orders with a join filter back to Customers.  The topology was created in this way to help pa...

I have managed to set-up a SQL Server 2005 Express instance, on a windows-XP machine, with remote connections enable. The actual installation of the SQL Server 2005 express instance was straightforward, but enabling the remote connections was problematic. I found that I was not the only person. In the end I found these steps worked for me: Open up the SQL Server Surface Area Configuration Manager Click on Surface Area configurations for services and connections. Click the remote Connections entry on the tree, select local and remote connections (TCP/IP) . Click OK. Open the SQL Server Configuration Manager Select SQL Server 2005 Network Configuration/Protocols for the instance. Right click TCP/IP, select properties. Click the IP Addresses tab, change the Enable flag on the IP addresses to YES. In the IPAll section, copy the port value of the TCP Dynamic Ports to the TCP Port. Clear the TCP Dynamic Port value. Click Okay. Open up Control Panel/Firewall. Click Exception...

Do you need to write a custom component to transform input data, and disregard this input data, instead, replacing it? That's was what I needed to accomplish with my component. It takes data from the input buffer and performs a lookup via an external API. If there is a match, send the result data down a "match" output otherwise send the input data down a "unmatch" output. The key here is that the "shape" of the output was changing. In other words, the metadata of the output was very different from that of the input.I also want my component to be synchronous (meaning synchronous, I mean that the component takes a row, processes it and then immediately sends it down an output) and not asynchronous (meaning that it reads all the data and then processes the data in an internal buffer). Now from what I was reading and seeing this was what I thought SSIS meant by synchronous and asynchronous components, but I was wrong! An asynchronous component can either...

I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Update: I put this on the Microsoft forums and got a intresting answer back See Here ; There seems to be a memory leak with foreach loop which should be fixed in sp1.

I have been writing a custom source adapter that uses a file connection within the connection manager. If I hard-code a specific file then the component works. However if I use a file connection that has an expression defined which updates the connection, for example when you have a for-each loop looping over a set files. The file connection doesn’t seem to re-evaluate expression each time you access the file connection via the code.