database error caused by if_sp

InstantASP Community Forums

Home

Members

Calendar

Who's On

Welcome Guest ( Login | Register )

database error caused by...

Rate Topic

Display Mode

Topic Options

Author

Message

kleesman

Posted 20/07/2005 08:39:27

Forum Newbie

Group: Customers
Last Login: 30/08/2005 07:44:45
Posts: 12, Visits: 24

Hi,

I've got 100+ roles defined in my forums. (Running 4.0 RC3).

I found today that when I try to browse into a forum (example: http://mydomain.com/fyforum/Forum103-1.aspx) I receive an error:

Exception Type:        System.Data.SqlClient.SqlException
Exception Message:     Line 6: Incorrect syntax near ')'.
Exception Source:      .Net SqlClient Data Provider
Exception Target Site: ExecuteReader
I did a SQL Server trace to see what SP's are being called. See the list below:
exec dbo.iasp_sp_SelectRoles
exec dbo.iasp_sp_SelectUserData @strUsername = N'randy.swing2@gmail.com', @strPassword = N'', @strEncyptedPassword = N'', @intLoginUsing = 1, @intUserID = 0
exec dbo.if_sp_SelectForumUserByUserID @intUserID = 16
exec dbo.if_sp_SelectForums @strRoles = N'155,156'
exec dbo.if_sp_SelectForumRoles @intForumID = 103
exec dbo.if_sp_SelectPermissionSetForRole @intRoleID = 156
exec dbo.if_sp_SelectPermissionSet @intPermissionID = 2
exec dbo.if_sp_InsertForumsRead @intForumID = 103, @intUserID = 16, @dtExpires = 'Aug 4 2005 9:18:14:117AM'
exec dbo.if_sp_SelectFolders @intUserID = 16

exec dbo.if_sp_SelectForums @strRoles = N'1,2,3,4,5,6,40,41,42,43,44,45,46,47,
48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,
73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,
98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,
116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,
134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,
152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,
170,171,172,173,174,175,176,177'

Everything seems to work fine until dbo.if_sp_SelectForums is executed the second time.

The first time it ran as I would expect passing in the user roles(155, 156) for the logged in user.

However, it was then run again and passed in the list of every user role. This broke because it exceeded the 500 nvarchar parameter.

Any thoughts on how to fix this, or why that SP would every be executed with all the possible user roles?

Thanks,
Kyle

Post #7713

Ryan Healey

Posted 21/07/2005 04:56:37

IF.NET 4.2 Coming Soon

Group: Administrators
Last Login: 24/11/2008 12:47:40
Posts: 1,956, Visits: 3,019

Hi Kyle,

Many thanks. I sent a resolution within your support ticket. This will be resolved within the next release candidate within the next couple of days.

Kindest Regards,

Ryan Healey
Director / Developer

Explore our products...
http://demos.instantasp.co.uk/

Post #7722

kleesman

Posted 29/07/2005 15:39:52

Forum Newbie

Group: Customers
Last Login: 30/08/2005 07:44:45
Posts: 12, Visits: 24

I've installed RC5 and I am still having this issue. I'm getting ready to roll out my production app on Tuesday next week. It is URGENT that I have this working.

I do not understand why the if_sp_SelectForums is being called and passed every role in the UserRoles table. I've got almost 500 forums and over 800 user roles.

exec dbo.if_sp_SelectForums @strRoles = N'812,818'

exec dbo.if_sp_SelectForums @strRoles = N'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,
39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,
84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,
121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,
154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,
187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,
220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,
253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,
286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,
319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,
352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,
385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,
418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,
451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,
484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,
517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,
550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,
583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,
616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,
649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,
682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,
715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,
748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,
781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,
814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,
847,848,849,850,851,852,853,854,855,856,857'

Post #7839

Ryan Healey

Posted 30/07/2005 04:25:38

IF.NET 4.2 Coming Soon

Group: Administrators
Last Login: 24/11/2008 12:47:40
Posts: 1,956, Visits: 3,019

kleesman (7/29/2005)

I've installed RC5 and I am still having this issue. I'm getting ready to roll out my production app on Tuesday next week. It is URGENT that I have this working.

I do not understand why the if_sp_SelectForums is being called and passed every role in the UserRoles table. I've got almost 500 forums and over 800 user roles.

exec dbo.if_sp_SelectForums @strRoles = N'812,818'

Hi Kyle,

The if_sp_SelectForums stored procedure requires the roles to determine which forums to return based on the current roles associated with your user account. I've sent you a PM with a view to resolving this issue.

Kindest Regards,

Ryan Healey
Director / Developer

Explore our products...
http://demos.instantasp.co.uk/

Post #7843

Mark Christianson

Posted 30/07/2005 13:17:57

Supreme Being

Group: Moderators
Last Login: 14/10/2008 15:36:31
Posts: 582, Visits: 876

kleesman (7/29/2005)

I've got almost 500 forums and over 800 user roles.

Ok, curiousity is killing me... what the heck kind of board has 500 forums and needs 800 user roles?

Mark 'Rigger82' Christianson
US Paratrooper & Webmaster of Paratrooper.net
http://www.paratrooper.net

Post #7846