EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'myDB', schema 'dbo'.

by Damiaan Peeters 7. April 2009 18:27

Problem

Today I launched the aspnet_regsql.exe.  Unfortunately I received the following error:

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'myDB', schema 'dbo'.

Solution

Take the properties of the “NT Autority\Network Service” user.  Select the myDB database and add all the necessary Roles like aspnet_Membership_FullAccess to the user. 

image

In the screenshot are all the databaseroles added.

Tags: ,

Asp.Net | SQL

Update Query versus Updatable cursor

by Damiaan Peeters 28. February 2009 02:02

Tonight someone gave me passed me an awfull SQL Query.  After a small few adaptions some the tablescans where eliminated.  I still believed it would be quicker using a cursor.  So, i started to create a SQL script with a cursor.

Create and populate test data

This was the Table to work against:

CREATE TABLE temptbl   
(    
  ProductId INT primary key identity(1,1),   
  Period INT,   
  Qty INT,   
  BFQty INT,   
  CFQty INT  
)   

 

To populate the table, you can write a million of bytes or create a small loop

-- Populate the table with some data
INSERT INTO temptbl VALUES(200801,1,0,0)   
INSERT INTO temptbl VALUES(200802,2,0,0)   
INSERT INTO temptbl VALUES(200803,3,0,0)   

DECLARE @RowCount INT, @Random INT, @Upper INT, @Lower INT, @periode INT

SET @Lower = 0
SET @Upper = 12
SET @RowCount = 0

WHILE @RowCount < 10000
BEGIN
    SET @RowString = CAST(@RowCount AS VARCHAR(10))
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    INSERT INTO temptbl VALUES(200801+@random,@random,0,0)   
    SET @RowCount = @RowCount + 1
END
Select count(*) from temptbl

 

The solution with one single query

This was the – not sooo awful solution to fill the brought forward and carry forward fields

DECLARE @Value INT
UPDATE temptbl SET  
     @Value = (SELECT ISNULL(SUM(Qty),0) FROM temptbl AS InnerT WHERE InnerT.ProductId=temptbl.ProductId AND InnerT.Period<temptbl.Period),
     BFQty = @Value,CFQty = @Value + Qty   

Solution with (updatable) SQL cursor

The trick to let it run fast, is – if i understood it well – using the updateble cursor.  Normally you should avoid using this type of cursor.  But now we need to run through the whole table anyway and update each field using the data of the previous record.  I highlighted the 2 important parts.

begin transaction
DECLARE MY_CURSOR Cursor DYNAMIC
FOR SELECT ProductId, qty FROM temptbl order by productId, period
FOR UPDATE  OF BFQty, CFQty

Open My_Cursor
DECLARE @Value int, @Qty int, @prodId int, @OldProdId int

Fetch NEXT FROM MY_Cursor INTO @ProdId, @Qty
SET @OldProdId = @ProdId
SET @Value = 0
While (@@FETCH_STATUS = 0)
BEGIN

    SET @OldProdId = @ProdId

   UPDATE temptbl
    set BFQty = @Value, CFQty = @Value + @Qty
    WHERE  CURRENT OF My_Cursor

    SET @Value = @Value + @Qty

    Fetch NEXT FROM MY_Cursor INTO @ProdId, @Qty
    IF @OldProdId <> @ProdId
    BEGIN
        set @VALUE =0
    END
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
commit transaction

Conclusion

Without the transaction it took 2 seconds for the cursor solution, and less than 1 second for the sql statement.  Adding The transaction dropped the cursor solution also below a second.

BUT, it took me 1 hour and a half to create (and learn about updateble) cursor solutions.  It’s 2:04 am, the other guy is in bed next to his girlfriend for some very reasonable time.  And I even haven’t fully tested if the cursor solution has the correct return (i even doubt it).

 

Normal

In transaction

Human work time

Total

Statement

< 1 sec

n.a.

10 minutes

winner

Cursor

2 seconds

< 1 sec

1,5 hour

loser

Next time, i might write a sql query right away.  The optimizer of MSSQL does it’s work anyway.

Hotsync not working on Palm (Tungsten E) when using InfraRed

by Damiaan Peeters 2. January 2009 12:27

Today I had an old "Palm Tungsten E" in my hands.  The user had a problem synchronizing it with Hotsync on the computer.  It always worked until I the day cleaned the computer from some junk a week ago. The synchronization worked flawless when using the cable, but didn't occurred when using Infra Red (IR).

The Error

Hotsync problem

Unable to initiate hotsync operation because the port is in use by another application.

The Solution

Just reboot the Palm.
(And select IR when opening hotsync on the Palm.)

Tags:

General

ASP.Net Membership API Cannot resolve collation conflict for equal to operation

by Damiaan Peeters 31. December 2008 09:36

Error

I was using the ASP.NET SQL Server Setup Wizard (aspnet_regsql.exe) to deploy te database to the production SQL Server 2005.

image

Creation or change of the SQL Server database failed.

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 468 and the SqlException message is: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
[cut]

SQL file loaded:
InstallRoles.sql

Commands failed:

[cut] …  the failed SQL code

SQL Exception:
System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
[cut
]…
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Cause

Apparently this error happend when creating the asp.net membership stored procedures: aspnet_UsersInRoles_AddUsersToRoles or aspnet_UsersInRoles_RemoveUsersFromRoles.

The reason is pretty straithforward.

The aspnet_Roles table uses collation “latin1_General_CI_AS”.

image

While my database has a SQL_Latin1_General_CP1_CI_AS collation by default (just like my SQL server collation).

image

Solution

Use the SQL Profiler to get the Code of the of the Stored procedure which need to be created.  The SQL scripts of the ASP.Net Membership can also be found under: C:\Windows\Microsoft.NET\Framework\v2.0.50727  (InstallRoles.sql)

Change the line(s) in the scripts:

look for this line: DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY) and add the collation like specified in the “aspnet_Roles.RoleName” column.  Your new line should look like this:

DECLARE @tbNames table(Name nvarchar(256) COLLATE Latin1_General_CI_AS NOT NULL PRIMARY KEY)

You need to do this for the 2 stored procedures.

Netblock Owner Search

by Damiaan Peeters 30. December 2008 11:33

Every tried to find out who is hosting a web site?  You can't be sure, but you might get some idea when you look at the organization who supplied the IP address for the web site.

If you want a web site, you need a server and an Internet connection.  To have an active Internet connection, you need of course a IP address.

The IANA delegates Internet resources to other regional organizations called a Regional Internet Registry. IANA manages also the DNS Root and information about top level domains (as country codes and .com, .org and .biz).  If you ever have 2 minutes: you should read the "IANA is attacking me" page on their web site. 

Like mentioned before, it is not IANA allocating the IP Addresses to your web hosting company.  There are 5 regional organizations called RIRs allocating the IP addresses to your web hosting company.  Currently there are 5 RIRs.

  • APNIC - Asia Pacific NIC
  • RIPE NNC - Europe NIC
  • ARIN - American NIC
  • AfriNIC African NIC
  • LACNIC Latin American and Caribbean Internet Addresses Registry

Instead of performing a reverse DNS lookup, you can look up more information on one of the NIC’s web sites above to determine the owner of the "netblock" that IP is related to.

Dependency Matrix & Application metrics

by Damiaan Peeters 29. December 2008 00:44

I got today back on an old controls assembly.  I am using DevExpress controls for more than a year.  But there are still some forms using my old control library.  It would have been a mess cleaning up this old directory, until i realized that this could be done very easily using NDepend.

I loaded the assemblies and used the dependency matrix to find out where the controls are used.

image

Especially the “Remove Empty… Rows and columns” image  gave me a quick overview of controls which still where used on some forms and should have been replaced by other ones.

Furtermore i looked – just for fun – at the “abstractness versus instability” graph created by NDepend.  I took a screenshot:

image 

As you can see, it ain’t looking good.  When i started developing this application, i never heard of abstractions and instability was my middlename. Well… None of my assemblies are in “zone of uselessness” (upper right corner), but i know the “zone of pain” very well.

The numbers aren’t lying:

Application Metrics

Number of IL instructions: 1006520
Number of lines of code: 121799
Number of lines of comment: 22251
Percentage comment: 15
Number of assemblies: 11
Number of classes: 849
Number of types: 896
Number of abstract classes: 2
Number of interfaces: 10
Number of value types: 4
Number of exception classes: 0
Number of attribute classes: 0
Number of delegate classes: 0
Number of enumerations classes: 33
Number of generic type definitions: 36
Number of generic method definitions: 5
Percentage of public types: 86,27%
Percentage of public methods: 80,31%
Percentage of classes with at least one public field: 2,23%

I know i can do better  :-)

Tags:

.Net

Asp.Net Debug (breakpoints) not working in VS2008

by Damiaan Peeters 14. December 2008 10:42

Problem

I set a breakpoint and my debugger is not stopping at the breakpoint.  Debug is configured in the Web.Config:

<compilation debug="true">
    <assemblies>

My assembly is compliled in Debug mode: debug - any cpu

Solution

The last thing to check is of cource the Project Properties. 

Go to the tab “Web”, just below Signing.
image

You will see at the bottom a section Debuggers.
image

Activate the ASP.NET checkbox and your breakpoints will halt your running code again.

Microsoft support can be found here for some common problems: INFO Common Error When you Debug ASP.Net Applications in Visual Studio .NET and especially here: PRB: Visual Studio .NET Debugger Does Not Stop on Breakpoints When You Debug ASP.NET Pages

Framework for Azur.be

by Damiaan Peeters 7. November 2008 16:10

Frameworks are THE way to develop web sites today.  In the PHP world there are several alternatives.

Today I received an error on the (new?) web site of Azur.be

image

Apparently they are using Symfony.  I didn't knew it so I looked it up.

Symphony provides an architecture, components and tools for developers to build complex web applications faster. Choosing symfony allows you to release your applications earlier, host and scale them without problem, and maintain them over time with no surprise.

Sounds a lot like a framework I am using a lot lately.  :-)

Tags:

General

And then the silence hit me

by Damiaan Peeters 15. September 2008 13:03

I know people die.  That is the way of live when you are old.
But life can be cruel. 

There are no words when thing like this happen. Therefore I dedicate this (short) blog post to Patrick Tisseghem (+ 3 september 2008)

I wish lots of strenght to his family, friend and co-workers.  

Big companies discovered .Net

by Damiaan Peeters 8. September 2008 17:27

I was surfing today and I noticed that some web sites of big companies where using .Net.
This doesn't mean that .Net is there main technology, but the extension is at least used in the URL...

McAfee

They use it in the FAQ's... here

image

 

Fabricom GTI (Suez)

image

At their main page: http://www.fabricom-gti.com

Dell

image

At their main site...  http://www.dell.com

Yahoo

image

Probably they are not really using .Net, but it is a .Net file on there domain anyway...
On this survey link: http://surveylink.yahoo.com/wix/p0473306.aspx

Tags:

.Net

Who.I.am

Certified Umbraco, .net and Azure developer, seo lover. Magician in my spare time.

Month List