Philippsen's Blog

Everyday findings in my world of .net and related stuff

Archive for the ‘Microsoft SQL Server’ Category

Connection troubleshooting

Posted by Torben M. Philippsen on November 22, 2011

One easy way to test your database connection is to use a udl file. Just create a new file and rename it to .udl. Open it and configure the connection using the connection properties:

Test_Database_connection

Test_Database_connection

Posted in Microsoft SQL Server | Tagged: , , | Comments Off on Connection troubleshooting

Find out which control triggered post back event

Posted by Torben M. Philippsen on January 3, 2011

In a recent Reporting Services web project I used the ReportViewer control to display reports.

In order to position some asp.net controls properly according to whether the parameters bar was present or not, I needed to determine if the “view report” button was clicked – causing the postback. Analyzing the html output of the reportviewer control, I needed to deterrmine if the control with id “ReportViewer1_ctl00_ctl00” caused the postback.

This is what I did:

    Control ctrl = GetPostBackControl(this);

    if (ctrl.ClientID.Equals(“ReportViewer1_ctl00_ctl00”))

    {

        ImageButton1.CssClass = “”;

        LinkButton1.CssClass = “LinkButton”;

    }

 

And the code for the GetPostBackControl method:

    public static Control GetPostBackControl(Page page)

    {

        Control control = null;

        string ctrlname = page.Request.Params.Get(“__EVENTTARGET”);

        if (ctrlname != null && ctrlname != string.Empty)

        {

            control = page.FindControl(ctrlname);

        }

        else

        {

            foreach (string ctl in page.Request.Form)

            {

                if ((ctl.LastIndexOf(“.x”) > 0) || (ctl.LastIndexOf(“.y”) > 0))

                {

                    control = page.FindControl(ctl.Substring(0, ctl.Length – 2));

                    break;

                }

                control = page.FindControl(ctl);

                if ((control is System.Web.UI.WebControls.Button))

                {

                    break;

                }

            }

        }

        return control;

    }

Posted in asp.net, Reporting Services | Tagged: , | 1 Comment »

SQL server sorting problem when upgrading Navision 4.0 SP3 to Navision 2009

Posted by Torben M. Philippsen on November 22, 2010

Last week I was contacted by one of my Navision collegues. He had performed an upgrade of MS Dynamics Nav 4.0 SP 3 to MS Dynamics Nav 2009.

The problem was a sorting issue in one of the account tables. Before the upgrade, sorting on the “No_” column would be like this:

10
100
1000
10000
10001

After the upgrade the same values were sorted like this:

10
100
10000
10001
1001

As you might notice, the sorting is very different causing a lot of troubles in some of the reports using data from the accounting table.

My first thought was that it had to have something todo with collation settings of the two database. Examing that however confirmed that the collation was identically configured in the two databases, tables and fields.

I noticed that in the Nav40SP3 version of the database the “No_” fields was defined as sql_variant datatype. In the new Nav2009 database the same field was defined as a varchar field – which would explain the sorting. The problem now was that I could understand the sorting after the upgrade, but I couldn’t understand why it actually worked (was sorting differently) before the upgrade. I tried to manually to change the datatype to a sql_variant in the Nav2009 version of the database – this had no effect at all. I tried scripting the two tables in order to maybe identify any differences in settings or whatever – nothing came to my attention. Having no ideas left to pursue I consulted Microsoft  for an explanation and fortunately they presented a solution and explanation very quickly – thank you Gerard Conroy.

Microsoft instructed my Navision collegue to go into the table editor of the accounting table and to view the properties of the field “No.”. In the SQL Data Type property he instructed us to change the value from “int” to “variant”:

Properties in navision
Properties in navision

First of all, let me state that this solved the sorting problem. However looking at the table directly on the sequal server it now turned out that the “No_” field was converted from a varchar to a sql_variant datatype – the exact same thing that I had tried out earlier. Why did it work when performed from within Navision and not when performed directly at the sequal server?

 
It turned out that when performing this task from within Navision, Navision actually does something more.
When performed directly at the sequal server, something like this is performed:
 

alter table [CRONUS International Ltd_$G_L Account] alter column [No_] sql_variant not null

 

In addition to this it turned out that Navision also does something like this (simplified version):

UPDATE [CRONUS International Ltd_$G_L Account]  SET “No_”=CAST(“No_” AS BIGINT)

 

As You might notice, Navision actually performs a CAST of the content of the column – which explains why the sorting turns out differently, since the content is now treated as Bigints rather than character data.

Problem solved – the only thing hauting me, is that I didn’t think of setting up a profiler session to monitor what actually happened when changing this from navision – I won’t forget that again – at least not until next time:-)

 



Posted in Microsoft Dynamics Nav, Microsoft SQL Server | Tagged: , , , | Comments Off on SQL server sorting problem when upgrading Navision 4.0 SP3 to Navision 2009

SQL server row versioning

Posted by Torben M. Philippsen on November 4, 2010

A client of mine experienced some performance issues with their Dynamics AX setup. In order to analyse the problem I had to determine whether row versioning was enabled or not. Below I will show how determine whether row versioning es enabled or not and how to enable it.

If You want to know more about row versioning, please visit this msdn article about “SQL Server 2005 Row Versioning-Based Transaction Isolation”.

Determining whether row versioning is enabled:

use tempdb
SELECT snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on
from sys.databases
where name = ‘my_database_name’

Enable row versioning:

use my_database_name
ALTER DATABASE my_database_name
    SET READ_COMMITTED_SNAPSHOT off;

ALTER DATABASE my_database_name
    SET ALLOW_SNAPSHOT_ISOLATION off;

Posted in Microsoft SQL Server | Tagged: | Comments Off on SQL server row versioning

Reporting Services – rsAccessDenied error

Posted by Torben M. Philippsen on October 13, 2010

Today I had to prepare a new SQL Server Reporting Services installation for a workshop I will be giving this friday. I’ve performed this task a lot of times but always on “real” server OS’s – never on a desktop OS, which this has to be.

The installation went pretty much by itself, but after runing the Reporting Services Configuration Manager and opening http://localhost/reports I got the following error:

The permissions granted to user ‘domain\username’ are insufficient for performing this operation. (rsAccessDenied)

After some googling I finally found a solution that I thought I would share:

  • Make sure you have access configured to the URL http://localhost/reports using the SQL Reporting Services Configuration. To do this:
  • Open Reporting Services Configuration Manager -> then connect to the report server instance  -> then click on Report Manager URL.
  • In the Report Manager URL page, click the Advanced button -> then in the Multiple Identities for Report Manager, click Add.
  • In the Add a Report Manager HTTP URL popup box, select Host Header and type in: localhost
  • Click OK to save your changes.
  • Now start/ run Internet Explorer using Run as Administator…  
  • NOTE: If you don’t see the ‘Site Settings’ link in the top left corner while at http://localhost/reports it is probably because you aren’t running IE as an Administator or you haven’t assigned your computers ‘domain\username’ to the reporting services roles, see how to do this in the next few steps.
  • Then go to: http://localhost/reports   (you may have to login with your Computer’s username and password)
  • You should now be directed to the Home page of SQL Server Reporting Services here: http://localhost/Reports/Pages/Folder.aspx
  • From the Home page, click the Folder Settings tab, then click New Role Assignment
  • In the Group or user name textbox, add the ‘domain\username’ which was in the error message (you can find the domain\username for your computer in the rsAccessDenied error message).
  • Now check all the checkboxes; Browser, Content Manager, My Reports, Publisher, Report Builder, and then click OK.
  • You’re domain\username should now be assigned to the Roles that will give you access to deploy your reports to the Report Server.  If you’re using Visual Studio or SQL Server Business Intelligence Development Studio to deploy your reports to your local reports server, you should now be able to.

Just to let You know, this solution applies to windows 7 running SQL Server 2008 Developer Edition

Posted in Reporting Services | Tagged: , | 1 Comment »

 
%d bloggers like this: