Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access


‎Reporting Services provides some nice options to allow user’s to feel like they are interacting with the data, by expanding lower levels of detail when they click on the plus (+) sign in the report’s tablix. You can also set the initial Toggle state of report items as expanded i.e. (-) sign.  There is a catch though in that when you Show your initial state as expanded, the items may expand correctly but the toggle sign appears reversed (i.e. +  instead of -).

Nice solution to the problem demonstrated Here.

From this site! http://geekswithblogs.net/WesWeeks/archive/2009/01/21/128874.aspx

Running Windows Vista 64 bit, Sql Server 2008 on a remote server. Using ASP.Net and the Report Viewer control on an asp.net page for a local report. Was also using IIS to host the web site instead of using the web server built into Visual Studio 2008.

Searched and tried everything I could find on the net trying to fix this freaking error. On a whim (and because I didn't know what else to do and was trying everything I could think of) changed the IIS 7.0 app pool to the classic app pool. Angels came singing down from the sky and my report worked.

What a PITA! Hope this post helps someone else.

The Actual Error for me was

Msg 7347, Level 16, State 1, Line 2

OLE DB provider 'SQLNCLI10' for linked server 'Common_Pts' returned data that does not match expected data length for column '[Common_Pts].[Pts].[dbo].[veRegulatoryInstrument].prefix'. The (maximum) expected data length is 4, while the returned data length is 5. 

After creating a linked server in ms sql express. and running the view I got the following error. to solve the problem run sp_refreshview command from the server that is hosting the view.

sp_refreshview [ @viewname = ] 'viewname' 

This is how to fix your missing Right-Click Context Menu option. When you rebuild your profile this sometimes happens.

  • Click Start, type shell:sendto and press ENTER
  • Create a new text document (Right-click - New - Text document)
  • Rename the text file to Mail Recipient.MAPIMail
  • Click Yes when you see the following message:
  • If you change a file name extension, the file may become unusable.

    Are you sure you want to change it?

    Note that after renaming the file, the .MAPIMail extension hides automatically. The Mail Recipient entry should now be restored to the Send To menu.

    From this guys site: http://www.mysharepointadventures.com/2012/11/removing-references-to-old-unused-databases-in-sharepoint-2010/

    Over time, as you create / remove service applications, there is a tendency for SharePoint to retain a list of databases that are no longer in use but still referenced in the SharePoint Config database. If you try to remove the database on the SQL server without first removing it from SharePoint, you will get an event logged periodically on your SharePoint Web Front End about being unable to find / access the missing database.


    To remove the orphan databases / databases that are no longer in use.

    Run SharePoint Management Shell

    Run the command

    Get-SPDatabase | fl name,id

    Locate the database you wish to remove and copy its ID.
    Run the command

    $db = Get-SPDatabase (id)

    Now type Get-SPDatabase again, you should see that the database(s) have been removed.

    From this guys great Blog! http://blogs.technet.com/b/meamcs/archive/2012/10/23/renaming-sharepoint-2010-search-service-database-name.aspx

    In this post I’m planning to walkthrough the practice of changing the SharePoint 2010 Search Service Database names as you might need it in the following scenarios:

    • Replacing the Search Service Application Databases Names that Includes GUID’s to a friendlier name.
    • The Database Admin in the Organization is planning to change the naming convention of all the Databases names in the Data Center.
    • If you are planning to move from one Farm to another that has a different Naming Convention.

    You have many options to do this practice as shown in the below figure, but in this post I will walkthrough the preferred steps only to make it simple and clear:



    Renaming Property & Crawl DBs using Central Administration:

    Verify that the user account that is performing the procedure is a member of the Farm Administrators SharePoint group.

    1. In Central Administration, click Application Management.
    2. On the Application Management page, click Manage service applications.
    3. Click the name of the Search Service Application.
    4. On the Search Administration page, click Modify.
    5. On the Manage Search Topology page, the following three databases are listed: Administration, Crawl, and Property. You can point to renamed or moved Crawl or Property databases by using this procedure.
    6. Click the database that you want to change, and then click Edit Properties.
    7. In the Database Server text box, type the new server location if there is one; in the Database Name text box, type the new name for the database; and then click OK.
    8. On the Manage Search Topology page, click Apply Topology Changes.



    • You cannot point to a renamed or moved Search Administration database by using Central Administration.
    • It might take several minutes for the changes to take effect.


    Renaming Search Service Application Database using PowerShell:

    Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

    1. On the Start menu, click All Programs.
    2. Click Microsoft SharePoint 2010 Products.
    3. Click SharePoint 2010 Management Shell.
    4. At the Windows PowerShell command prompt, type the following commands:
        • Point the Search Administration database to its new name or location.
    $searchapp | Set-SPEnterpriseSearchServiceApplication -DatabaseName "<NewDbName>" -DatabaseServer "<NewServerName>" 



          • <NewDbName> is the name of the renamed database
          • <NewServerName> is the new database location
    2. Monitor whether the search instances have finished re-provisioning.
    Do {write-host -NoNewline .;Sleep 10; $searchInstance = Get-SPEnterpriseSearchServiceInstance -Local} while ($searchInstance.Status -ne "Online")


    Additional References:

    1. Rename FAST Search for SharePoint 2010 Databases
    2. Rename or  Move Service Application
    3. Change database (FAST Search Server 2010 for SharePoint)

    Sub reports are great for showing data from a related table. This works just like a sub form, by setting the master/child linking fields.

    What else can you use a sub report to print?

    Other data that is not related to a specfic reord in the parent report. The trick is to not set the master/child linking fields. This allows you to print information in the report header or footer that is not "link" to a record.

    Sub reports are great for sunmmizing the data in a report.

    By basing an sub report's recprd source on the same record source as the parent report, you can show all the totals for each grouping (hiding the detail). You can also create a  totaling query based on the parent report's record source (query)

    What if you want to place the report's total in the report header?

    Use a sub report that cauclates and displays just the total.   

    How do you print several different statistics that are caclulated with different queries?

    Use sub reports. It is possible to have a parent report that has no record source, only sub reports.

    How to print a header on each page when a sub report spans multiple pages?

    Becuse page headers do not print from a sub report you have to use group header. This is done by creating a grouping that will group all record into a single group. You then can set teh group header to repeat.

    1. Open the subreport in design view.
    2. Open the Sorting and Grouping box.
    3. In the Field column of the dialog, enter an expression that won't change, e.g.: =0 and set Group Header to Yes.
    4. Right-click the new group header (grey bar on the report), and choose Properties. Set Repeat Section to Yes.

    This gives you a group header that appears at the top of the subreport. It will repeat on each new page like the way you would excect the page header for the subreport.

    Working with Report Header and Footer sections.

    Print order for4 sections:

    • Pager Header - Prints at the top of each page.  This section can NOT be set to grow or shrink. The Page Header prints before the Report Header/Footer. It is possible to turn off the Page Header so it does not print with the Report Header/Footer
    • Report Header - prints once at the beginning. This section can be set to grow or shrink.
    • Group Headers - prints at the begining of each group This section can be set to grow or shrink. It can also be set to Repeat on each new page.
    • Group Footers - prints at the end of each group. This section can be set to grow or shrink.
    • Report Footer - prints once at the end. This section can be set to grow or shrink.
    • Page Footer - prints at the bottom of each page. This section can NOT be set to grow or shrink. The Page Feader prints after the Report Header/Footer. It is possible to turn off the Page Footer so it does not print with the Report Header/Footer

    When a report is used as a Sub Report, the header and footer sections work a little different.

    Print order for sections:

    • Pager Header - does NOT print. The parent report is responsible for the page header
    • Report Header - prints once at the beginning. This section can be set to grow or shrink.
    • Group Headers - prints at the begining of each group This section can grow or shrink. It can also be set to Repeat on each new page.
    • Group Footers - prints at the end of each group. This section can be set to grow or shrink.
    • Report Footer - pirnts once at the end. This section can be set to grow or shrink.
    • Page Footer - does NOT print. The Parent report is responsible for the page footer.

    How to create something lke a  a Page Header in a Sub Report

    You can create a dummy header .grouping by setting the group on to  1 (enter the digit 1). You can then set the section to repeat. Now you have a  group header that will print on every page.

    Header and Footer Tips

    Repeating Group Headers:

    In the Group Header's prperties, set the Repeat property to Yes to get the header to print on every page if the group spans multiple pages.

    Print Report Header without a Page Header/Footer:

    In the Report's properties, set the Page Header property to Not With Rpt Hdr or Not With Rpt Hdr/Ftr depending on your needs.

    Print Report Footer without a Page Footer/Footer:

    In the Report's properties, set the Page Footer property to Not With Rpt Ftr or Not With Rpt Hdr/Ftr depending on your needs.


    Run As Administrator

    by Dave Stuart | Tags:

    Everything you need to know about Run as Administrator: http://www.sevenforums.com/tutorials/11841-run-administrator.html


    I ran into a problem recently when looking up the Created By and Modified By fields for an List Item so I caould display it on an Application Page. When I opened the page it worked fine, however when a user with less privilages opened it they got an error laie this "Value does not fall within the expected range".

    This is what I found and I have no idea why this was the solution BUT it worked for me.


    It seems there can be several causes.
    1). Use of invalid field name. You need to use the internal field name! However, that was not the case in my situation.
    2). Change the List View Lookup Threshold value of the web application. Default value is 8, I changed it to 20 and big surprise: it worked! Thanks to this post.
    Note: My item has some lookup fields some of which some have more than 8 items.
    Little conclusion: So it seems that when using (one of) the system admin accounts, this list view lookup threshold is ignored in one way. By using non-system admin accounts, it can be a show stopper.
    Steps to edit this value:

    • Go to Central Administration > Application Management > Manage web applications
    • Select the appropriate web application (in my case the SharePoint - 80)
    • In the ribbon bar, select General Settings > Resourse Throttling
    • Search for the List View Lookup Threshold and change the value to 20.

    This is a very good article about Working with VBA in the 32-bit and 64-bit Versions of Office 2010 http://msdn.microsoft.com/en-us/library/ff700513(v=office.11).aspx

    I have re-posted it and referenced it here! Mainly for my own reference :-)

    Office 2010 32-bit and 64-bit Client Installations

    Microsoft offers a 32-bit client installation and 64-bit client installation of Microsoft Office 2010. If you use a 64-bit version of Microsoft Windows, you can choose which version of Microsoft Office to use. By using the 64-bit version, you can experience improved application performance by using more physical memory for storage and by moving more data in fewer operations.

    However, there are considerations when moving your 32-bit applications to or creating solutions for the 64-bit version of Microsoft Office. For example, applications written for the 64-bit version likely do not work in previous versions of Microsoft Office. Also, calls to Microsoft Windows Application Programming Interface (API) functions from your application code may not work.

    Calls to 64-bit Windows API functions from 32-bit solutions (and the opposite) may lead to erratic behavior or system crashes resulting from the truncation of data or overflows into protected memory spaces. To compensate for this, Microsoft Office provides a 32-bit version and 64-bit version of several of the Declare statements used in the Windows API. To ensure compatibility, you must change the Declare statements in solutions to differentiate between 32-bit calls and 64-bit calls. These differences and remedies are described in the article Compatibility Between the 32-bit and 64-bit Versions of Office 2010.

    Office 2010 introduces a new version of Microsoft Visual Basic for Applications (VBA) known as VBA 7.0 that is updated to work with 64-bit client installations. VBA 7.0 improves the performance of your Microsoft Office applications through 64-bit performance improvements. VBA 7.0 also enables you to create applications that are compatible with Office 2007 or continue to use VBA solutions created in previous versions. To improve compatibility of application code targeted to a particular version of Microsoft Office, there is a compilation constant (VBA7) that enables you to determine the version of VBA you are using and execute the correct code. Note that if your applications are only written for the 32-bit versions of Microsoft Office, no changes are necessary to your code.

    There is also a compilation constant (Win64) that enables your Office 2010 solutions to work with both the 32-bit version and 64-bit version. Both of these compilation constants are demonstrated in the following sections.

    As stated previously, VBA 7.0 works with 64-bit versions of Microsoft Office. To enable VBA solutions created for 32-bit versions to continue working in Office 2010, the compilation constant (VBA7) tests to determine the VBA version used in the solution.

    The following code example shows how to use the VBA7 constant. This example enables you to find the location and size of the main window in Microsoft Excel 2010. To do this, first, a user-defined type, RECT, is created. Next, the Windows API functions, FindWindow and GetWindowRect, are defined to return the dimensions of the window. Because there is a 32-bit version and 64-bit version of these functions, the VBA7 compilation constant directs the compiler to the correct section of code. Then, the DisplayExcelWindowSize subroutine is called. This subroutine calls the FindWindow and GetWindowRect functions. Finally, a message box appears with the dimensions.

    ' A user-defined type to store the window dimensions.
    Type RECT
        Left As Long
        Top As Long
        Right As Long
        Bottom As Long
    End Type
    ' Test which version of VBA you are using.
    #If VBA7 Then
       ' API function to locate a window.
       Declare PtrSafe Function FindWindow Lib "user32" _
          Alias "FindWindowA" ( _
          ByVal lpClassName As String, _
          ByVal lpWindowName As String) As LongPtr
       ' API function to retrieve a window's dimensions.
       Declare PtrSafe Function GetWindowRect Lib "user32" ( _
          ByVal hwnd As LongPtr, _
          lpRect As RECT) As Long
       ' API function to locate a window.
       Declare Function FindWindow Lib "user32" _
          Alias "FindWindowA" ( _
          ByVal lpClassName As String, _
          ByVal lpWindowName As String) As Long
       ' API function to retrieve a window's dimensions.
       Declare Function GetWindowRect Lib "user32" ( _
          ByVal hwnd As Long, _
          lpRect As RECT) As Long
    #End If
    Sub DisplayExcelWindowSize()
       Dim hwnd As Long, uRect As RECT
       ' Get the handle identifier of the main Excel window.
       hwnd = FindWindow("XLMAIN", Application.Caption)
       ' Get the window's dimensions into the RECT UDT.
       GetWindowRect hwnd, uRect
       ' Display the result.
       MsgBox "The Excel window has these dimensions:" & _
          vbCrLf & " Left: " & uRect.Left & _
          vbCrLf & " Right: " & uRect.Right & _
          vbCrLf & " Top: " & uRect.Top & _
          vbCrLf & " Bottom: " & uRect.Bottom & _
          vbCrLf & " Width: " & (uRect.Right - uRect.Left) & _
          vbCrLf & " Height: " & (uRect.Bottom - uRect.Top)
    End Sub

    To test this solution, add a module to Excel 2010, insert the previous code, and then run the macro. You should see a message displayed similar to Figure 1 with the dimensions, in pixels, of the window.

    Figure 1. Message with the dimensions of the main Excel window

    Message with the Excel window dimensions

    Resize the Excel window and rerun the macro. You should see that the values changed.

    In some solutions, you must provide code for the 32-bit version of Office 2010 and code for the 64-bit version of Office 2010. And as in the previous example, you must call different versions of the Windows API functions. To do this, the Win64 compilation constant is available as shown in the following example.

    ' Test whether you are using the 64-bit version of Office 2010.
    #If Win64 Then
       Declare PtrSafe Function GetTickCount64 Lib "kernel32" () As LongLong
       Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
    #End If

    In this example, if you code is running in the 64-bit version of , the GetTickCount64 function is called. Otherwise, the GetTickCount function is called.

    VBA 7.0 is the latest version of VBA that improves the performance of applications that are created in Office 2010. To enable you to continue using your legacy solutions in different versions of Microsoft Office, the VBA7 compilation constant is available. Likewise, because there is now a 32-bit version and 64-bit version of Office 2010, you use the Win64 compilation constant to direct the compiler to the run the correct section of code in your VBA applications.

    The Blogger

    Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca


    <<  October 2021  >>

    View posts in large calendar

    Sign in