Showing posts with label users. Show all posts
Showing posts with label users. Show all posts

Friday, March 30, 2012

Help on this

Hello:

I am currently running a query and it is taking like 6 hours to run.

what I do is:

I loop through user's table (20000 users) using a cursor, for each user I call a stored procedure "EXEC ...", then in that called stored procedure, I have an a set of 6 IF statements, and in each if statement I have a call for a simple stored procedure,.

Can anyone tell me, how can i optimize that query?

thanks a lot

Don't use a cursor? We'd need to know more specifics about what it's actually doing to determine if you can do this as a set operation instead of row-by-row (cursor).

Marcie

|||

Hello Marcie:

Thank you for your reply.

I sent you an email from your Blog, it has some codes.

thanks a lot,

|||

Hi SNT2,

I don't have access to that email account during the day, sorry. Also, if you'll post the code here (or at least an overview of what it does), other people will be able to help you with your problem too.

Take care,

Marcie

|||

Hello, I will list the Stored Procedures here: As I said, there is a main procedure, which loops using a cursor through all the records, and upon getting each account I call a method, the first method is the general one:

CREATE PROCEDURE ActiveUserAnalysis_AnalyzeUsers( @.AnalysisPeriod int = 30, -- the number of days over which the refills are checked @.CustGrpId int = 101, -- the group that we want to analyze @.AnalysisId int out)ASBEGIN SET NOCOUNT ON --create the analysis record INSERT INTO ActiveUserAnalysis ( AnalysisTime, AnalysisPeriod, CustGrpId) VALUES ( getdate(), @.AnalysisPeriod, 101) SET @.AnalysisId = @.@.identityDECLARE @.UserCount int SET @.UserCount = 0 --loop over all active users and analyze them DECLARE cActiveCustomers CURSORREAD_ONLYFOR select customerid, username from activecustomersview where custgrpid=@.CustGrpIdDECLARE @.customerid intDECLARE @.username varchar(30)OPEN cActiveCustomersFETCH NEXT FROM cActiveCustomers INTO @.customerid, @.usernameWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN SET @.UserCount = @.UserCount + 1 EXEC ActiveUserAnalysis_AnalyzeActiveUser @.AnalysisId, @.AnalysisPeriod, @.CustomerId, @.UserName   ENDFETCH NEXT FROM cActiveCustomers INTO @.customerid, @.usernameENDCLOSE cActiveCustomersDEALLOCATE cActiveCustomers UPDATE ActiveUserAnalysis SET ActiveUserCount = @.UserCount WHERE AnalysisId = @.AnalysisId SELECT Classification, count(*) FROM ActiveUserAnalysis_Results WHERE AnalysisId = @.AnalysisId GROUP BY ClassificationENDGO


Now the method we are calling above for each user is:

CREATE procedure ActiveUserAnalysis_AnalyzeActiveUser( @.AnalysisId int, @.AnalysisPeriod int, @.customerid int, @.username varchar(30))asbegin DECLARE @.TokenType varchar(4) DECLARE @.Expires datetime DECLARE @.Value int DECLARE @.LastActivity datetime DECLARE @.RefillType varchar(30) DECLARE @.LastRefillDate datetime DECLARE @.RefillCount int DECLARE @.TBSUserName varchar(30) --First collect info EXEC ActiveUserAnalysis_GetUsageToken @.customerid, @.TokenType out, @.Value out, @.Expires out SELECT TOP 1 @.LastActivity = LastActivity FROM PPPAccount WHERE CustomerId = @.CustomerId --Classify user -- 1- Refill EXEC ActiveUserAnalysis_CheckUserLastRefills @.CustomerId, @.AnalysisPeriod, @.RefillType out, @.LastRefillDate out if @.@.rowcount > 0 BEGIN INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'Refill', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires) RETURN END -- 2- NoLogin: He hasn't logged in at all during period If datediff (d, @.LastActivity, getdate()) > @.AnalysisPeriod BEGIN INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'NoLogin', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires) RETURN END --3 Other. If we reached here, then the user does not fit with any of the above categories INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'Other', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires)ENDGO

I didn't include all cases, but i listed only 3 of them.

Then in every case I am calling a procedure too.

Can that be optimzed?

Thanks Marcie

|||

Yes, but it will take some significant work. You haven't included the stored procedures/views that this depends on, so I can't really give you code that will work 100%.

Start off by creating a view for many of the stored procedures that this thing depends on... Can you post the code for say...

ActiveUserAnalysis_CheckUserLastRefills? Hopefully that doesn't call any more stored procedures/views.

|||

Hello, thank you for helping me.

The SP is as follows:

CREATE procedure ActiveUserAnalysis_CheckUserLastRefills(@.CustomerId int,@.AnalysisPeriod int, -- in days,@.Refilltype char(5) out,@.LastRefillDate datetime out)asselect top 1 @.RefillType=c.ISKCatCode, @.LastRefillDate = datepurchasedfrom isk i inner join iskcategory c on i.iskcatid = c.iskcatidwhere customerid = @.customerid and datediff(d, datepurchased, getdate()) <= @.AnalysisPeriod and i.CreatedNewCustomer = 0order by datepurchased descGO

Thank you.|||

Hello Marcie:

Can you help in that please?

thanks

|||

SomeNewTricks2 wrote:

I am currently running a query and it is taking like 6 hours to run.

what I do is:

I loop through user's table (20000 users) using a cursor, foreach user I call a stored procedure "EXEC ...", then in that calledstored procedure, I have an a set of 6 IF statements, and in each ifstatement I have a call for a simple stored procedure,.

Can anyone tell me, how can i optimize that query?


That's an awful big haystack. Have you pinpointed thebottleneck? I'd suggest running the query from Query Analyzerwith Show Execution Plan turned on. This should give you an ideaof the slow parts.

Also, this will not likely make an appreciable difference in yourexecution time, but instead of a cursor I'd use a #temp table (see anexample of this approach in this post:http://forums.asp.net/511669/ShowPost.aspx).|||

SomeNewTricks2 wrote:

Hello Marcie:

Can you help in that please?

thanks

My gut feel is still that you could probably do all of this with one (large) Update statement, rather than looping through thousands of items and doing them one at a time. If that gets too complex (or is not possible), using a #temp table as Terri suggests will make this job easier, and much faster than the cursor approach.

Marcie

|||

Thank you all.

Do you think using a loop over a temp table, is better than cursor?

What about the "if" statements, is using them ok?

thanks

|||

SomeNewTricks2 wrote:

Do you think using a loop over a temp table, is better than cursor?


Yes I do, that's why I suggested it. Here's a little background information:SQL Server Temp Table Performance.

SomeNewTricks2 wrote:

What about the "if" statements, is using them ok?


I think using them is OK, but as far as I understand it, when SQLServer compiles the stored procedure it will use the execution planbased on whichever parameters were passed to it the first timethrough. So the execution plan may not be optimized for otherparameters. But I have also read that SQL Server often recompilesstored procedures, so I don't know how much of an issue that truly is.

Again, I urge you to identifiy your bottleneck(s). You havepresented a huge haystack to look through, and it could be that onesmall part needing optimization is causing all of the performanceissues. We don't really have enough information to be able tohelp you.

|||

Sorry, been away for a few days... I didn't see that you were stuffing some of values from ActiveUserAnalysis_GetUsageToken in your inserts, could you post that code? As long as it's pretty simple, I'll have some working code for ya that should be magnitudes faster than what you are currently doing by using set-based rather than cursor-if based logic.

Cursoring over a temp table won't be faster than cursoring over a query, however, you will have less transaction collisions (possibly). The article the above poster referenced, is saying temp tables are faster if by using one you can avoid a cursor and use set-based logic, which is what you'll probably have to do, but possibly not, but it doesn't eliminate the need to move away from the cursor-if logic.

|||

First of all, thank you Terri for your help.

Here is the code:

create procedure ActiveUserAnalysis_GetUsageToken
(
@.customerid int,
@.TokenType varchar(4) out,
@.Value int out,
@.Expires datetime out
)
as
SELECT TOP 1 /* get only first (highest priority) token */
@.TokenType=t.TokenTypeId, @.Value=u.Value, @.Expires=u.Expires
FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.customerid = @.customerid
AND u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)
ORDER BY y.Priority, u.Created


Thanks a lot.

|||

Ok, let's start here. First step is to unroll this stored procedure into a view so that we can reuse it later. Heh, it would figure that you would have this buried somewhere, this is definately the hardest part of unravelling your cursor logic, so here goes. I am going to make the assumption that a single user can not have two (or more) tokens created with the same priority created at the same "Created". If this assumption is not correct, please let me know what combination of fields would be required to ALWAYS return no more than a single record.

Coding by hand here, so might be a syntax error somewhere (sorry).

CREATE VIEW vw_CustomerUsageToken

AS

SELECT t1.customerid,t1.TokenTypeId,t1.Value,t1.Expires
FROM (SELECT u.customerid,t.TokenTypeId,u.Value,u.Expires,y.priority,u.created FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)) t1

LEFT JOIN (SELECT u.customerid,t.TokenTypeId,u.Value,u.Expires,y.priority,u.created FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)) t2 on t1.customerid=t2.customerid AND (t2.priority<t1.priority OR (t2.priority=t1.priority AND t2.created<t1.created))

WHERE t2.customerid IS NULLsql

Wednesday, March 28, 2012

Help on sql sync

Hi, all
We have prod. database and test db(new version of prod, table fields
changed).
Production db has users connected and updating records and I need to have
user transactions in production db updated in test db as well.
I can't use log shipping to sync them because of the table structures a
little diff or some datatype changed in new version(test db).
I can't do the DTS neither. It may have too many tables effected.
What is the best way to do the sync.
thanks in advanceThere is table replication in sql 2k?
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>|||Hi,
You can not use Logshiiping or Replication because based on my understanding
both environments are in read/write mode. Due to this we can not apply the
transaction log file. The best way for you is to write a batch file to do
frequent updates.
Thanks
Hari
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23GpcOWD2GHA.1568@.TK2MSFTNGP03.phx.gbl...
> There is table replication in sql 2k?
> Thanks
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
>|||You can use transactional replication to replicate to tables of different
schemas. See this for more info.
http://www.dbazine.com/sql/sql-articles/cotter1
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>

Help on sql sync

Hi, all
We have prod. database and test db(new version of prod, table fields
changed).
Production db has users connected and updating records and I need to have
user transactions in production db updated in test db as well.
I can't use log shipping to sync them because of the table structures a
little diff or some datatype changed in new version(test db).
I can't do the DTS neither. It may have too many tables effected.
What is the best way to do the sync.
thanks in advanceThere is table replication in sql 2k?
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>|||Hi,
You can not use Logshiiping or Replication because based on my understanding
both environments are in read/write mode. Due to this we can not apply the
transaction log file. The best way for you is to write a batch file to do
frequent updates.
Thanks
Hari
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23GpcOWD2GHA.1568@.TK2MSFTNGP03.phx.gbl...
> There is table replication in sql 2k?
> Thanks
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Hi, all
>> We have prod. database and test db(new version of prod, table fields
>> changed).
>> Production db has users connected and updating records and I need to have
>> user transactions in production db updated in test db as well.
>> I can't use log shipping to sync them because of the table structures a
>> little diff or some datatype changed in new version(test db).
>> I can't do the DTS neither. It may have too many tables effected.
>> What is the best way to do the sync.
>> thanks in advance
>>
>>
>>
>>
>>
>|||You can use transactional replication to replicate to tables of different
schemas. See this for more info.
http://www.dbazine.com/sql/sql-articles/cotter1
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>sql

Help on sql sync

Hi, all
We have prod. database and test db(new version of prod, table fields
changed).
Production db has users connected and updating records and I need to have
user transactions in production db updated in test db as well.
I can't use log shipping to sync them because of the table structures a
little diff or some datatype changed in new version(test db).
I can't do the DTS neither. It may have too many tables effected.
What is the best way to do the sync.
thanks in advance
There is table replication in sql 2k?
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>
|||Hi,
You can not use Logshiiping or Replication because based on my understanding
both environments are in read/write mode. Due to this we can not apply the
transaction log file. The best way for you is to write a batch file to do
frequent updates.
Thanks
Hari
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23GpcOWD2GHA.1568@.TK2MSFTNGP03.phx.gbl...
> There is table replication in sql 2k?
> Thanks
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
>
|||You can use transactional replication to replicate to tables of different
schemas. See this for more info.
http://www.dbazine.com/sql/sql-articles/cotter1
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>

Monday, March 26, 2012

help on query

I need to select all messages for user X from all users, except those who user X has blocked in tblBlockList.
I have the following query, but it returns a row for each time UserCodeBlocked is not equal to UserCodeSender...

What I need is just to select all messages for a user EXCEPT when they are from a user in the blocklist...
I have tried the keyworddistinct but that ofcourse doesnt work since Row_number makes every row unique...
tblMessages contains the actual message
tblUsersandmessages relates users and messages

select ROW_NUMBER() OVER (ORDER BY a.SentDateTime DESC) as RowNum,a.MessageID,a.UserCodeSender,a.MessageTitle,a.SentDateTime
FROM tblMessages a
INNER JOIN tblUsersAndMessages b ON (a.MessageID=b.MessageID)
INNER JOIN tblUserData c ON (a.UserCodeSender=c.UserCode)
RIGHT JOIN tblBlockList bl ON bl.UserCodeBlocked<>a.UserCodeSender
WHERE b.UserCode=5

did you try a left join instead of a right join? You want the data for the message, not for the block list.

select ROW_NUMBER()OVER (ORDER BY a.SentDateTimeDESC)as RowNum,a.MessageID,a.UserCodeSender,a.MessageTitle,a.SentDateTimeFROM tblMessages aINNERJOIN tblUsersAndMessages bON (a.MessageID=b.MessageID)INNERJOIN tblUserData cON (a.UserCodeSender=c.UserCode)LEFTOUTER JOIN tblBlockList blON bl.UserCodeBlocked<>a.UserCodeSenderWHERE b.UserCode=5

If this doesn't help, please post the table structures and/or relationship diagram

|||tblBlockList
UserCodeBlocker int 'person who has blocked someone
UserCodeBlocked int 'person being blocked

tblMessages
UserCodeSender int
MessageID int
MessageTitle
...

tblUsersAndMessages
UserCode int
MessageID int
...sql

Friday, March 23, 2012

Help on DB design

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

It depends,

You have 3 (or more) options,

Store as Individual Table(rows

Each file will be stored as single row with key field.

The master table can hold the key field & number of files

Store as Delimited Value

If you use SQL server 2000, you are not allowed to store more than 8000 chars, otherwise you have to change the data type to LOB(text). In SQL Server 2005 you can use the Varchar(max).

Manipulations need to be done on the UI.

Single row modification can’t be done on the UI, you have to stick with your UI.

Store as XML value

If you use SQL Server 2005, you can use new data type XML.

You can store at any number of chars

Easy to manipulate on Database itself.

Structured & Well Managed

My Rank is – 1.XML, 2.Table(rows), 3.Delimited value

|||^Thank you Manivannan.D.Sekaran . I will follow Table(rows) as i am using sql server 2000.

Help on DB Design

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj

Quote:

Originally Posted by bootzwiz

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj


Generate FileId in the database and GroupId and FilePath

FileId FileGroupID FilePath
--------------------------------

Like this|||

Quote:

Originally Posted by bootzwiz

Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.

Raj


i offer to have the following entity/table for your application :

1-an entity named "Session " with these fields (SessionID,SessionName,SessionDate ) with SessionID for primary key.

2-an entity named SessionFiles with these fields(SessionID,FilenameWithPath,uploadStatus) with "SessionID,FilenameWithPath" for primary key.

3-make relation for these two table with SessionID field.

4-each form shows one session in your application for uploading the related SessionFiles.

5-Enjoy it.sql

Wednesday, March 21, 2012

Help on changing the PW on SQL2000

Hi, all,
I got a problem to change the users' password from the VB
side. It is almost same as one from SQL7, and it works on
SQL7 but I have problem on SQL2000. It is ok when I run it
from QA, what's wrong? Do I need set ANSI NULLS?
Any one can help? Thanks you very much and have a good one
Abchawk
Here is the Stored procedure i wrote if it is sysadmin
to change others PW, the login will be the person who PW
will be changed, otherwise, the person changes his own PW,
in this case, login will be ' ')
CREATE PROCEDURE I_USER_CHANGE_PASSWORD_SP
(
@.LOGIN NVARCHAR(10)=' ',
@.OLDPASSWD NVARCHAR(10),
@.NEWPASSWD NVARCHAR(10)
) AS
DECLARE @.OUT INT
IF @.LOGIN <>' ' BEGIN
EXEC @.OUT = sp_password NULL ,
@.NEWPASSWD,@.LOGIN
END
ELSE BEGIN
EXEC @.OUT = sp_password
@.OLDPASSWD,@.NEWPASSWD
END
RETURN @.OUT
GO
****************************************
**************
The VB side code like this:
Set adoCommand = CreateObject("ADODB.Command")
mySQL = "I_USER_CHANGE_PASSWORD_SP "
With adoCommand
.ActiveConnection = strCnn
.CommandText = mySQL
.CommandTimeout = 100
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("RETURN",
adInteger, adParamReturnValue)
If SecurityLevel < 2 Or Trim(Me.txtLogin) =
userName Then
.Parameters.Append .CreateParameter
("Login", adWChar, adParamInput, 10, " ")
.Parameters.Append .CreateParameter
("OldPasswd", adWChar, adParamInput, 10, Trim
(Me.txtOldPassword))
Else
.Parameters.Append .CreateParameter
("Login", adWChar, adParamInput, 10, Trim(Me.txtLogin))
.Parameters.Append .CreateParameter
("OldPasswd", adWChar, adParamInput, 10, Null)
End If
.Parameters.Append .CreateParameter
("NewPasswd", adWChar, adParamInput, 10, Trim
(Me.txtNewPassword))
End With
adoCommand.Execute
OUT = adoCommand(0)
If OUT <> 1 Then
MsgBox "Change password successfully",
vbInformation
Else
MsgBox "Sorry,Changing password failured",
vbInformation
End If
Set adoCommand = Nothing
****************************************
******************Below is a VBScript code snippet that shows how to execute a proc with
parameters. This way, you don't have to build the SQL with properly quoted
strings.
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = connection
cmd.CommandType = 'adCmdStoredProcedure
cmd.CommandText = "sp_addlogin"
Set pReturnCode = cmd.CreateParameter("@.RETURN_CODE")
pReturnCode.Type = adInteger
pReturnCode.Direction = 4 'adParamReturnValue
cmd.Parameters.Append pReturnCode
Set pLoginName = cmd.CreateParameter("@.loginame")
pLoginName.Type = adVarWChar
pLoginName.Direction = adParamInput
pLoginName.Size = 128
pLoginName.Value = "MyLogin"
cmd.Parameters.Append pLoginName
Set pLoginName = cmd.CreateParameter("@.passwd")
pLoginName.Type = adVarWChar
pLoginName.Direction = adParamInput
pLoginName.Size = 128
pLoginName.Value = "MyLoginPassword"
cmd.Parameters.Append pLoginName
cmd.Execute
'display return code
MsgBox cmd.Parameters(0).Name & "=" & cmd.Parameters(0).Value
connection.Close
Hope this helps.
Dan Guzman
SQL Server MVP
"abchawk" <anonymous@.discussions.microsoft.com> wrote in message
news:005d01c4d72f$1c866120$a601280a@.phx.gbl...
> Hi, All,
> I think I found the reason that cause the problem. It is
> the single quota ('). If I run SQL statement in VB
> like "sp_password '" & strOldPW & "','" & strNewPW & "'"
> it is not problem at all, but how can I do samething in VB
> using ado command? Does anyone know it?
> Samething for sp_addlogin, I create the login but can not
> access the DB because of Password. Using SQL statement
> like above, it is fine. just becasue the data type is
> sysname? For SQL 7 , double quota works fine, but not in
> SQL 2000
> Thanks and have a great day.
> hawk

Help Needed: DataSet

Should I create a new dataset in order to populate a multi select parameter
drop down box for users?
I am trying to capture all Companies stored in our backend database as a
drop down selection to users.
Thanks!Hi Terry,
You are correct. Thats how the guy explained it on the Reporting Services
webcasts.
The webcasts are quite informative. I would advise everyone to watch them.
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Should I create a new dataset in order to populate a multi select parameter
> drop down box for users?
> I am trying to capture all Companies stored in our backend database as a
> drop down selection to users.
> Thanks!|||Which webcast are you referring to?
Provide URL or link, if available?
Again, thanks!
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||I am getting an error when I create a new dataset and use it as a parameter.
Error:
[rsMissingDataSetName] The data set name is missing in the data region
â'DataSetNameâ'
CompanyLookup Dataset:
select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
from addresses
where len(rtrim(ADDR_Org_Name_1)) > 0
order by ADDR_Org_Name_1
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||The best thing to do is to go through the webcasts and see what you like.
But the ones I'm reffering to is a 5 day series. They touch on everything.
Follow the URL and scroll down till you see the webcast that starts on 07
March part 1
Very long URL :-)
http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
Hope this helps man...
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Which webcast are you referring to?
> Provide URL or link, if available?
> Again, thanks!
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!|||Thank you for your assistance.
"Stan" wrote:
> The best thing to do is to go through the webcasts and see what you like.
> But the ones I'm reffering to is a 5 day series. They touch on everything.
> Follow the URL and scroll down till you see the webcast that starts on 07
> March part 1
> Very long URL :-)
> http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
> Hope this helps man...
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Which webcast are you referring to?
> >
> > Provide URL or link, if available?
> >
> > Again, thanks!
> >
> > "Stan" wrote:
> >
> > > Hi Terry,
> > > You are correct. Thats how the guy explained it on the Reporting Services
> > > webcasts.
> > > The webcasts are quite informative. I would advise everyone to watch them.
> > >
> > > R
> > > S
> > > --
> > > http://dotnet.org.za/stanley
> > >
> > >
> > > "Terry" wrote:
> > >
> > > > Should I create a new dataset in order to populate a multi select parameter
> > > > drop down box for users?
> > > >
> > > > I am trying to capture all Companies stored in our backend database as a
> > > > drop down selection to users.
> > > >
> > > > Thanks!|||I hope I understand you correctly.
Just to clear things out.
You have a report that takes parameters, but you want the user to select the
parameter from a drop down box. Is this correct?
If that is the case, go to you Data Tab and click on new dataset. Write
your query that will bring back the company names. Now go to your layout
view and click on the report menu. Go to report parameters. Now select on
your company parameter and then click the radio button that says from query.
Select your new dataset in there. When you preview your report the dropdown
box should be populated.
Hope this helps man...
--
http://dotnet.org.za/stanley
"Terry" wrote:
> I am getting an error when I create a new dataset and use it as a parameter.
> Error:
> [rsMissingDataSetName] The data set name is missing in the data region
> â'DataSetNameâ'
> CompanyLookup Dataset:
> select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
> from addresses
> where len(rtrim(ADDR_Org_Name_1)) > 0
> order by ADDR_Org_Name_1
>
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!

Monday, March 12, 2012

help needed to validate 2 users at once

hi all, i want to ask about validating 2 users (2 userids and 2 pws) with one button. my database table called login and it has loginId and password.

have tried

"SELECT * FROM login Where loginId = '" + hoName + "', '" + toName + "' AND password = '" + hoPassword + "', '" + toPassword + "'"

please help me!!

i'm using 03 to build, theres no errors. but when i view in browser and try to validate. a wole lot of errors come out.

|||

hi all, can anyone help me please?

|||

I didnt get u r question. Can u explain in detail. You want to check the two users and their passwords at a time or what. Clarity is required.If u give the reply i can try out and i will try to give the solution

Thank u

Baba

|||

SELECT COUNT(*)

FROM Users

WHERE (UserID=@.UserID1 ANDPWD=@.PWD1) OR (UserID=@.UserID2 ANDPWD=@.PWD2)

|||

thanks all.

Friday, March 9, 2012

Help needed in restoring the document library

Hi,
I am working in the SQL Server 2000 and Share Point Portal Server; we are
maintaining the SQL Backup for all the sites,
one of the users has deleted accidentally a document library from a
particular site. Can you please help me in restoring the particular document
library from the SQL Backup File?
Here are details of how to do this through the dedicated backup/restore
tool:
http://office.microsoft.com/en-us/sharepointportaladmin/HA011603461033.aspx
As this is not really a replication issue I'd recommend posting this in the
Sharepoint newsgroup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help needed for SSRS 2005(Regarding Individual folder Security)

Hi Experts,
I am working on SSRS 2005.
I want to implement security for each folder for different users on my
report server.
I have 6 different folders on my report server and I want that any
person can open them after entering a valid username and password. I
want set different username and password for each folder not a single
password for all folders.
I am not finding anyway to do this. So if anybody have any idea about
this please tell me.
Any help will be appreciated.
Regards
DineshOn May 3, 4:06 am, Dinesh <dinesh...@.gmail.com> wrote:
> Hi Experts,
> I am working on SSRS 2005.
> I want to implement security for each folder for different users on my
> report server.
> I have 6 different folders on my report server and I want that any
> person can open them after entering a valid username and password. I
> want set different username and password for each folder not a single
> password for all folders.
> I am not finding anyway to do this. So if anybody have any idea about
> this please tell me.
> Any help will be appreciated.
> Regards
> Dinesh
Normally, you would tie the folder-level security into Windows
authentication, based on user and group access (via select a folder ->
select the Properties tab -> select Security on the left-hand side ->
Edit item security on the top). You might make a new role w/only
certain members and that role only has access to certain folders;
however, again this is windows authentication based. The only other
way to go about it would be to create a parameter in each report that
accepts text input and use this as a password. Sorry that I could not
be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||As Enrique mentioned you can't do this.
This is not how Windows works. Consider this the same as if you had a file
server and were trying to do this. You could not do it.
Same result is to do the following. Create 6 local groups. To the local
groups add the users (you can add either individual domain users or domain
groups to the local groups). Then use these groups when setting up roles and
access to the folders.
You should read up on roles and security in RS.
One other possibility is to implement forms security. This is where you
integrate your own authentication mechanism.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1178183193.254090.33670@.c35g2000hsg.googlegroups.com...
> Hi Experts,
> I am working on SSRS 2005.
> I want to implement security for each folder for different users on my
> report server.
> I have 6 different folders on my report server and I want that any
> person can open them after entering a valid username and password. I
> want set different username and password for each folder not a single
> password for all folders.
> I am not finding anyway to do this. So if anybody have any idea about
> this please tell me.
> Any help will be appreciated.
> Regards
> Dinesh
>

Wednesday, March 7, 2012

Help moving users to from old instance

Hello,
I know there is a kb article on how to move users from an old instance
to a new one, but I cannot find it. Can someone point me in the right
direction please? We are moving our instances to new hardware. Thanks.
INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default...;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default...;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default...;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default...;en-us;Q274188
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake Smythe" <rondican@.hotmail.com> wrote in message
news:uWBV%238nJFHA.2628@.tk2msftngp13.phx.gbl...
> Hello,
> I know there is a kb article on how to move users from an old instance
> to a new one, but I cannot find it. Can someone point me in the right
> direction please? We are moving our instances to new hardware. Thanks.
>

Help moving users to from old instance

Hello,
I know there is a kb article on how to move users from an old instance
to a new one, but I cannot find it. Can someone point me in the right
direction please? We are moving our instances to new hardware. Thanks.INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274188
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake Smythe" <rondican@.hotmail.com> wrote in message
news:uWBV%238nJFHA.2628@.tk2msftngp13.phx.gbl...
> Hello,
> I know there is a kb article on how to move users from an old instance
> to a new one, but I cannot find it. Can someone point me in the right
> direction please? We are moving our instances to new hardware. Thanks.
>

Help moving users to from old instance

Hello,
I know there is a kb article on how to move users from an old instance
to a new one, but I cannot find it. Can someone point me in the right
direction please? We are moving our instances to new hardware. Thanks.INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/defaul...b;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/defaul...b;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/defaul...b;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/defaul...b;en-us;Q274188
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake Smythe" <rondican@.hotmail.com> wrote in message
news:uWBV%238nJFHA.2628@.tk2msftngp13.phx.gbl...
> Hello,
> I know there is a kb article on how to move users from an old instance
> to a new one, but I cannot find it. Can someone point me in the right
> direction please? We are moving our instances to new hardware. Thanks.
>

Friday, February 24, 2012

HELP ME PLEASE! GODADDY - ASPNETDB Security DB

Hi,

I have two sql db's. One for company record info, other for users (aspnetdb) I created the users one using the login control and web administration wizard. This all worked well until I had to publish to godaddy. I had to rebuild my company db, but eventually I was able to pull info and all that is working. The problem is, how the heck do I add users? I noticed that it put users table in my db to start. So I manually went into the db on godaddy's servers, and added an application. Then when I tried to add a user manually, it kept giving me errors. Is there an easier way to add website users through godaddy? Something like the web admin tool that comes with visual studio? Man it is driving me crazy!!!

For the poor man's approach, you can upload a file from the learnvisualstudio.net tutorials (begennirs lesson 09),

Signup.aspx

<%@. Page Language="C#" %
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"
<script runat="server"
</script
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:CreateUserWizard ID="CreateUserWizard1" runat="server" BackColor="#F7F7DE" BorderColor="#CCCC99"
BorderStyle="Solid" BorderWidth="1px" ContinueDestinationPageUrl="~/Default.aspx"
Font-Names="Verdana" Font-Size="10pt" Style="z-index: 100; left: 241px; position: absolute;
top: 152px">
<WizardSteps>
<asp:CreateUserWizardStep runat="server">
</asp:CreateUserWizardStep>
<asp:CompleteWizardStep runat="server">
</asp:CompleteWizardStep>
</WizardSteps>
<SideBarStyle BackColor="#7C6F57" BorderWidth="0px" Font-Size="0.9em" VerticalAlign="Top" />
<TitleTextStyle BackColor="#6B696B" Font-Bold="True" ForeColor="#FFFFFF" />
<SideBarButtonStyle BorderWidth="0px" Font-Names="Verdana" ForeColor="#FFFFFF" />
<NavigationButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="#FFFFFF" HorizontalAlign="Center" />
<CreateUserButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<ContinueButtonStyle BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid"
BorderWidth="1px" Font-Names="Verdana" ForeColor="#284775" />
<StepStyle BorderWidth="0px" />
</asp:CreateUserWizard>

</div>
</form>
</body>
</html
And here's my Web.config file:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<system.web>
<!--
<trace enabled="true" requestLimit="50" localOnly="false"/>
-->
<compilation debug="true"/>
<roleManager enabled="true"/>
<customErrors mode="Off"/>
<authentication mode="Forms"/
<membership defaultProvider="MySqlMembershipProvider" >
<providers>
<clear/>
<add name="MySqlMembershipProvider"
connectionStringName="MyLocalSQLServer"
applicationName="MyAppName"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership>
</system.web
<connectionStrings>
<add name="MyLocalSQLServer"
connectionString="Data Source=whsql-v01.prod.mesa1.secureserver.net;Initial Catalog=DB_nnnnn;User ID=user;Password=password;" />
</connectionStrings
</configuration
|||I'd like to know if there is decent user management app for this.

Also when I work with Roles I run into errors. Since I don't have access to the ASP.NET configuration tool I'm notsure if Role Management Enabled is checked on godaddy servers or ifthis would even matter for this app.

Here's my error screen.

Server Error in '/' Application.

TheSSE Provider did not find the database file specified in the connectionstring. At the configured trust level (below High trust level), the SSEprovider can not automatically create the database file.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Configuration.Provider.ProviderException:The SSE Provider did not find the database file specified in theconnection string. At the configured trust level (below High trustlevel), the SSE provider can not automatically create the database file.

Source Error:

Line 11: }
Line 12:
Line 13: if (Roles.IsUserInRole("Basic User") == false)
Line 14: Server.Transfer("unauthorized.aspx");
Line 15: }


Source File: d:\Hosting\javagrinder\SecurePage.aspx Line: 13

Stack Trace:

[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.]
System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2466581
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87
System.Web.Security.SqlRoleProvider.GetRolesForUser(String username) +771
System.Web.Security.RolePrincipal.IsInRole(String role) +272
System.Web.Security.Roles.IsUserInRole(String username, String roleName) +533
System.Web.Security.Roles.IsUserInRole(String roleName) +16
ASP.securepage_aspx.form1_Load(Object sender, EventArgs e) in d:\Hosting\javagrinder\SecurePage.aspx:13
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Control.LoadRecursive() +131
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061



Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42|||Hi JavaGrinder, have you tried setting the trust level higher (e.g. "Full")?|||

Lori,

Can you explain how to do that? That may solve another problem I am running into...

Thanks!

|||Ah, that's probably the problem. I get the following error, and am contacting godaddy to see if I can have them unlock this and allow overrides for sites upon request.

I suspect there is something more to it since I'm having troubles adding roles to the tables from their SQL server manager. I can add users and applications from that panel, but not Roles.

Server Error in '/' Application.

Configuration Error

Description:Anerror occurred during the processing of a configuration file requiredto service this request. Please review the specific error details belowand modify your configuration file appropriately.

Parser Error Message:Thisconfiguration section cannot be used at this path. This happens whenthe site administrator has locked access to this section using<location allowOverride="false"> from an inherited configurationfile.

Source Error:

Line 22: </providers>
Line 23: </membership>
Line 24: <trust level="Full"/>
Line 25: </system.web>
Line 26: </location>


Source File: d:\hosting\javagrinder\web.config Line: 24


Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42|||

Java,

Let me know how this goes... I am trying to run the reportviewer control from their servers and it gives me permssion errors. I have heard that the reportviewer control requires full trust. Please let me know if they allow you to use full trust.

Thanks!

|||

Oh and by the way Java, I had asked Godaddy before about the trust level and all they kept saying was that I could create folders myself. I don't think they really had a clue what I was talking about... Good luck and let me know how it goes!! I might have to end up swiching host since I need this reportviewer control to work..

Thanks!

|||They say on shared hosting the trust level is set to medium. You have to have a dedicated hosting account to increase it to full.|||Ok, so after talking to godaddy support (useless) I started searching around on google again. I don't know where I got this web.config, but it works for me. Maybe it will work for report viewer.

-Jon

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"
<location allowOverride="true"
<system.web>
<!--
<trace enabled="true" requestLimit="50" localOnly="false"/>
-->
<compilation debug="true"/>
<!-- roleManager enabled="true"/ -->
<customErrors mode="Off"/>
<authentication mode="Forms"/>
<membership
<providers
<remove name="AspNetSqlMembershipProvider" /
<add name="AspNetSqlMembershipProvider"

type="System.Web.Security.SqlMembershipProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a"

connectionStringName="MyLocalSQLServer"

enablePasswordRetrieval="false"

enablePasswordReset="true"

requiresQuestionAndAnswer="true"

applicationName="/"

requiresUniqueEmail="false"

passwordFormat="Hashed"

maxInvalidPasswordAttempts="5"

minRequiredPasswordLength="7"

minRequiredNonalphanumericCharacters="1"

passwordAttemptWindow="10"

passwordStrengthRegularExpression="" /
</providers
</membership
<profile
<providers
<remove name="AspNetSqlProfileProvider" /
<add name="AspNetSqlProfileProvider"

connectionStringName="MyLocalSQLServer"

applicationName="/"

type="System.Web.Profile.SqlProfileProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" /
</providers>

</profile
<roleManager enabled="true">

<providers
<remove name="AspNetSqlRoleProvider" /
<add name="AspNetSqlRoleProvider"

connectionStringName="MyLocalSQLServer"

applicationName="/"

type="System.Web.Security.SqlRoleProvider,

System.Web, Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" /
</providers
</roleManager>
<!--
<membership defaultProvider="MySqlMembershipProvider" >
<providers>
<clear/>
<add name="MySqlMembershipProvider"
connectionStringName="MyLocalSQLServer"
applicationName="MyAppName"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership>
-->
</system.web>
</location>
<connectionStrings>
<add name="MyLocalSQLServer"
connectionString="Data Source=whsql-v01.prod.mesa1.secureserver.net;Initial Catalog=DB_nnnnnn;User ID=userid;Password=password;" />
</connectionStrings
</configuration>|||

Didn't work for me :( I still get this error...

Required permissions cannot be acquired.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Security.Policy.PolicyException: Required permissions cannot be acquired.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


Stack Trace:

[PolicyException: Required permissions cannot be acquired.] System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Boolean checkExecutionPermission) +2737861 System.Security.SecurityManager.ResolvePolicy(Evidence evidence, PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset, PermissionSet& denied, Int32& securitySpecialFlags, Boolean checkExecutionPermission) +57[FileLoadException: Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417)] System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) +0 System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +211 System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +141 System.Reflection.Assembly.Load(String assemblyString) +25 System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +32[ConfigurationErrorsException: Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417)] System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +596 System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +3479081 System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +46 System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +177 System.Web.Compilation.WebDirectoryBatchCompiler..ctor(VirtualDirectory vdir) +267 System.Web.Compilation.BuildManager.BatchCompileWebDirectoryInternal(VirtualDirectory vdir, Boolean ignoreErrors) +36 System.Web.Compilation.BuildManager.BatchCompileWebDirectory(VirtualDirectory vdir, VirtualPath virtualDir, Boolean ignoreErrors) +429 System.Web.Compilation.BuildManager.CompileWebFile(VirtualPath virtualPath) +73 System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile) +580 System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile) +93 System.Web.Compilation.BuildManager.GetVirtualPathObjectFactory(VirtualPath virtualPath, HttpContext context, Boolean allowCrossApp, Boolean noAssert) +111 System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(VirtualPath virtualPath, Type requiredBaseType, HttpContext context, Boolean allowCrossApp, Boolean noAssert) +54 System.Web.UI.PageHandlerFactory.GetHandlerHelper(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath) +31 System.Web.UI.PageHandlerFactory.System.Web.IHttpHandlerFactory2.GetHandler(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath) +40 System.Web.HttpApplication.MapHttpHandler(HttpContext context, String requestType, VirtualPath path, String pathTranslated, Boolean useAppConfig) +139 System.Web.MapHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +120 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155



Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42