sql login permissions: stored

Showing posts with label stored. Show all posts

Wednesday, March 28, 2012

long running report can not be rendered in Report Manager or through Subscription

I am using a stored procedure which will run about one hour and ten minutes to return a dataset to reporting services to generate a report. The report generation is OK if I run it inside Visual Studio 2005. Once it is deployed, it can not finish. Both cases I set "Do not timeout report execution". I even try to create a subscription to let it run throught. But it still can not finish. The traces information in ReportServer*.Log is like this:

<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.1399.00</Product>
<Locale>en-US</Locale>
<TimeZone>Central Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles\ReportServer__06_29_2006_09_58_21.log</Path>
<SystemName>JSONG-XP</SystemName>
<OSName>Microsoft Windows NT 5.1.2600 Service Pack 2</OSName>
<OSVersion>5.1.2600.131072</OSVersion>
</Header>
aspnet_wp!webserver!6!6/29/2006-09:58:21:: i INFO: Reporting Web Server started
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
aspnet_wp!configmanager!6!6/29/2006-09:58:21:: w WARN: WebServiceAccount is not specified in the config file. Using default: JSONG-XP\ASPNET
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
aspnet_wp!library!6!6/29/2006-09:58:21:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
aspnet_wp!resourceutilities!6!6/29/2006-09:58:21:: i INFO: Reporting Services starting SKU: Developer
aspnet_wp!resourceutilities!6!6/29/2006-09:58:21:: i INFO: Evaluation copy: 0 days left
aspnet_wp!runningjobs!6!6/29/2006-09:58:21:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds
aspnet_wp!runningjobs!6!6/29/2006-09:58:21:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
aspnet_wp!runningjobs!6!6/29/2006-09:58:21:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
aspnet_wp!runningjobs!6!6/29/2006-09:58:21:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
aspnet_wp!library!6!06/29/2006-09:58:23:: i INFO: Call to GetPermissions:/
aspnet_wp!library!6!06/29/2006-09:58:24:: i INFO: Catalog SQL Server Edition = Developer
aspnet_wp!library!6!06/29/2006-09:58:24:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-09:58:40:: i INFO: Call to GetPermissions:/CreditReports
aspnet_wp!library!6!06/29/2006-09:58:40:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!1!06/29/2006-09:58:41:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!1!06/29/2006-09:58:41:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!1!06/29/2006-09:58:44:: i INFO: Call to RenderFirst( '/CreditReports/ExperienceByCoverageSEDEV' )
aspnet_wp!runningjobs!5!6/29/2006-09:59:27:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:03:36:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:06:25:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:06:25:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:13:12:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:16:12:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:16:12:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:23:13:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:26:13:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:26:13:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:33:11:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:36:09:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:36:09:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:43:06:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:46:05:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:46:05:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!5!6/29/2006-10:53:07:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!library!5!6/29/2006-10:56:07:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 1 running jobs, 0 persisted streams
aspnet_wp!runningjobs!5!6/29/2006-10:56:07:: i INFO: Adding: 1 running jobs to the database
aspnet_wp!runningjobs!a!6/29/2006-10:57:07:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
aspnet_wp!runningjobs!c!6/29/2006-10:58:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-10:58:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-10:59:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-10:59:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:00:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:00:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:01:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:01:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:02:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:02:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:03:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:03:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:04:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:04:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:05:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:05:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!library!c!6/29/2006-11:06:07:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams
aspnet_wp!runningjobs!c!6/29/2006-11:06:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!c!6/29/2006-11:06:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!session!9!06/29/2006-11:06:52:: e ERROR: Sql Error in GetSnapshotData: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
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.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader()
at Microsoft.ReportingServices.Library.DatabaseSessionStorage.GetSessionData(String sessionId, String userName, ConnectionManager connMgr)
aspnet_wp!library!9!06/29/2006-11:06:52:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. > System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
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.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader()
at Microsoft.ReportingServices.Library.DatabaseSessionStorage.GetSessionData(String sessionId, String userName, ConnectionManager connMgr)
at Microsoft.ReportingServices.Library.DatabaseSessionStorage.GetSessionData(String sessionId, String userName)
at Microsoft.ReportingServices.Library.SessionReportItem.Load(DatabaseSessionStorage sessionStorage, LoadAction loadFlags, String sessionId, String reportPath, DateTime snapshotDate, String userName, String userParams, String imageName, DatasourceCredentialsCollection dataSourceCred)
at Microsoft.ReportingServices.WebServer.HttpClientRequest.LoadFromDB(LoadAction loadFlags, CatalogItemContext context)
at Microsoft.ReportingServices.WebServer.SessionStarterAction.CreateExisting()
End of inner exception stack trace
aspnet_wp!library!6!06/29/2006-11:06:54:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!6!06/29/2006-11:06:54:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-11:06:55:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!6!06/29/2006-11:06:55:: i INFO: Call to GetSystemPermissions
aspnet_wp!runningjobs!5!6/29/2006-11:07:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!5!6/29/2006-11:07:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!library!8!06/29/2006-11:07:51:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:07:51:: i INFO: Call to GetSystemPermissions
aspnet_wp!runningjobs!5!6/29/2006-11:08:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!5!6/29/2006-11:08:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...
aspnet_wp!library!8!06/29/2006-11:08:16:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:08:16:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!8!06/29/2006-11:08:16:: i INFO: Call to GetSnapshotLimit( '/CreditReports/ExperienceByCoverageSEDEV' )
aspnet_wp!library!6!06/29/2006-11:08:19:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!6!06/29/2006-11:08:19:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-11:08:19:: i INFO: Call to GetPolicies:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:08:21:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:08:22:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-11:08:28:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!6!06/29/2006-11:08:28:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!8!06/29/2006-11:08:37:: i INFO: Call to GetPermissions:/CreditReports/ExperienceByCoverageSEDEV
aspnet_wp!library!8!06/29/2006-11:08:37:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!6!06/29/2006-11:08:51:: i INFO: Call to GetPermissions:/CreditReports
aspnet_wp!library!6!06/29/2006-11:08:51:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!8!06/29/2006-11:08:55:: i INFO: Call to GetPermissions:/CreditReports/AS400SEDEV
aspnet_wp!library!8!06/29/2006-11:08:55:: i INFO: Call to GetSystemPermissions
aspnet_wp!runningjobs!5!6/29/2006-11:09:07:: w WARN: Previous request for Running Requests Scavengerstill executing, skipping...
aspnet_wp!runningjobs!5!6/29/2006-11:09:07:: w WARN: Previous request for Running Requests DBstill executing, skipping...

I would like know the reason behind it. How to fix that?. I use Sql Server 2005 developer edition.

Thanx

There is a limit for how long a report will run from Report Manager. You can modify the setting at:

Report Manager --> Site Settings-->Report Execution Timeout-->Limit
report execution to the following number of seconds = 3600 (1 hr)

|||

Thank you for your reply.

Actually I set

Report Manager --> Site Settings-->Report Execution Timeout-->Do not timeout report execution. It still can not produce the report.

Another question is after I set localhost/reports and localhost/reportserver session timeout to 3 hrs and these site's asp.net state management session timout to 3hrs, the localhost/reportserver is not accessable. Maybe it is a reporting services bug. Steps to repeat the scenario: Go to IIS, right click localhost/reports-->Properties-->Click Configuration-->Application Configuration-->Options-->Change session timeout to 180 minutes. To set asp.net state management session timout to 3hrs. right click localhost/reports-->Properties-->Report Properties windows-->Click ASP.NET tab-->Edit Configuration-->State Management-->Set Session timout to 180 Minutes.

So far so good. Once repeating above operations to localhost/reportserver, both sites will become unaccessable.

Your response will be appreciated.

|||

The exception you are hitting is the session access timeout. The viewer control will try to ping back to the server every N-1 minutes (where N is basically the session expiration time). The default session access timeout is 10 minutes, so if the report takes over an hour to render, you will hit this timeout.

You can run into problems when the time it takes to render the report is longer than the session timeout, I would recommend altering the following system properties (you will have to do this via the SOAP API or an rs.exe script since they are not exposed in the management studio UI) to be more on the order of what you expect the report execution to be:

SessionAccessTimeout|||

Actually, rendering the long running report in the report manager is just for test purpose. I will create a subscription to deliver the report to a shared folder using SOAP API. Even for the subscription, the timeout issue still exists. Could you provide me some examples to set session timeout via SOAP API or a re.exe.

Thank you very much.

|||Any news? I have the same issue. Can you explain please which values should be used for these parameters in order to get the report running for more than 1 hour?|||There is a blog about it here

http://blogs.msdn.com/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx
So i did change this for a report that needs 20 minutes but it still fail after 5 minutes with this error in the computer application log
Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 10/31/2006 10:08:46 AM
Event time (UTC): 10/31/2006 5:08:46 PM
Event ID: c6233c9a2cff498a90cfa4140a3e932a
Event sequence: 1572
Event occurrence: 1
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/Reports-1-128067603886462532
Trust level: RosettaMgr
Application Virtual Path: /Reports
Application Path: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\
Machine name: ATLANTA
Process information:
Process ID: 6452
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: ReportServerException
Exception message: Execution 'fmnjra45tvj15545rxlxt5ec' cannot be found (rsExecutionNotFound)
Request information:
Request URL: http://reporting.onsemi.com/Reports/Reserved.ReportViewerWebControl.axd?ReportSession=fmnjra45tvj15545rxlxt5ec&ControlID=84b2a2bd-4f03-4c5d-9d73-c305e7883b6e&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl161TouchSession0&CacheSeed=Tue Oct 31 10:08:44 2006
Request path: /Reports/Reserved.ReportViewerWebControl.axd
User host address: 10.253.138.208
User: ONSEMI\R38851
Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 14
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: True
Stack trace: at Microsoft.Reporting.WebForms.ServerReport.GetExecutionInfo()
at Microsoft.Reporting.WebForms.ServerReport.SetExecutionId(String executionId, Boolean fullReportLoad)
at Microsoft.Reporting.WebForms.ServerReport.LoadFromUrlQuery(NameValueCollection requestParameters, Boolean fullReportLoad)
at Microsoft.Reporting.WebForms.ReportDataOperation..ctor()
at Microsoft.Reporting.WebForms.HttpHandler.GetHandler()
at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Any idea?
Thanks,
Philippe

long running report can not be rendered in Report Manager or through Subscription

I would like know the reason behind it. How to fix that?. I use Sql Server 2005 developer edition.

Thanx

There is a limit for how long a report will run from Report Manager. You can modify the setting at:

Report Manager --> Site Settings-->Report Execution Timeout-->Limit
report execution to the following number of seconds = 3600 (1 hr)

|||

Thank you for your reply.

Actually I set

Report Manager --> Site Settings-->Report Execution Timeout-->Do not timeout report execution. It still can not produce the report.

So far so good. Once repeating above operations to localhost/reportserver, both sites will become unaccessable.

Your response will be appreciated.

|||

SessionAccessTimeout|||

Thank you very much.

|||Any news? I have the same issue. Can you explain please which values should be used for these parameters in order to get the report running for more than 1 hour?|||There is a blog about it here

Any idea?
Thanks,
Philippe

long running report can not be rendered in Report Manager or through Subscription

I would like know the reason behind it. How to fix that?. I use Sql Server 2005 developer edition.

Thanx

There is a limit for how long a report will run from Report Manager. You can modify the setting at:

Report Manager --> Site Settings-->Report Execution Timeout-->Limit
report execution to the following number of seconds = 3600 (1 hr)

|||

Thank you for your reply.

Actually I set

Report Manager --> Site Settings-->Report Execution Timeout-->Do not timeout report execution. It still can not produce the report.

So far so good. Once repeating above operations to localhost/reportserver, both sites will become unaccessable.

Your response will be appreciated.

|||

SessionAccessTimeout|||

Thank you very much.

|||Any news? I have the same issue. Can you explain please which values should be used for these parameters in order to get the report running for more than 1 hour?|||There is a blog about it here

Any idea?
Thanks,
Philippesql

Monday, March 26, 2012

Long parameter list for stored procs

Hi All,

I've got a problem with increasingly long parameter lists for SProcs... Especially when one calls another SProc, and so on. Is there any way around this? Like can you dynamically construct a string and pass that? I'm just looking to see if more experienced players have found ways around this, or have just dealt with it by using well formed code.

Thanks

Chris

Have a look at the following article by Erland Sommarskog:
http://www.sommarskog.se/arrays-in-sql.html|||

thats a useful link carlop has given...

u can construct any synamic sql statement dynamically..using string and variables...and execute it..including calling an sp..

look for execute and sp_executesql in books online..

|||

Hi,

It is not bad idea to have more number of parameters. But i know it is more overhead when you call it.

If you want to reduce this you can go for XML string as Parameter & OPENXML to extract the XML data in your sp.

Basically XML Parameter is used to solve the following issue,

1. Multiple Row Insert on Single Call

2. Passing multiple data into single Parameter (Like Array of values)

Here I gave simple example with 2 Parameter A &B

XML String (Parameter):

</Parameters>

OPENXML on your SP

Declare @.SpParams Table (Name varchar(100), Value Varchar(100));

DECLARE @.hDoc int
Exec sp_xml_preparedocument @.hDoc OUTPUT,@.XmlString

INSERT INTO @.SpParams
SELECT *
FROM OPENXML (@.hDoc,'/Parameters/Param')
WITH (Name varchar(100), Value Varchar(100))

EXEC sp_xml_removedocument @.hDoc;

Now you can use the @.spparams table on nessasary place....

BUT,

If you ask me, it is best practice for Mulitple row insert and passing array of value.

For your concern it always better use as indivdual params. (see the complex/overhead code on sp to prepare the document, parse the XML, and read the content on every query)

|||To me it is a question of numbers. If you have a long list of parameters, but they are fixed in number, then I would certainly stick to just writing well formed queries, not putting stuff into arrays or any of that other stuff. That method should be considered mostly for unknown numbers of parameters, paticularly when you need to pass unknown numbers of values to the same parameters. The more clear to follow that your procedures are (regardless of size) the easier that "future you" will remember what the heck you had in mind when you wrote the procedure. If you are poking around in a string value to parse out different parameter values, your chances of making a "dumb" mistake later that will not be caught until the president of your company is trying to demo the program for a potential client.

Long Oracle report

Hi,
I'm having problems with a report that has a Oracle datasource.
The query source is a stored procedure that takes 3 hours to complete but
reporting services don't wait this time and shows an error page.
Reporting Services is configured to "Do not timeout report execution" and
the report uses "Use default setting"
This works ok with a SLQ Server Stored procedure.
Any ideas?
AndrésHello Andres,
I'd like to know the exact error message you encountered. Also, how long
did it take to display error message after you started to execute the
report? You mentioned, the SP works in SQL but I wonder if the report from
SQL server also takes 3 hours to render?
As I know, IE version 5.x and higher have 60 minute time-out limit. A quick
registry setting and a restart fixes the problem.
1. Add a ReceiveTimeout DWORD value with a data value of (<number
ofseconds>)*1000 in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
For example, if you want the timeout duration to be 8 minutes, set the
ReceiveTimeout data value to 480000 (<480>*1000).
2. Restart your computer.
You can find more info at:
http://support.microsoft.com/kb/q181050/
You could check the datasource of Oracle in RS designer by open the data
source->Edit->Advanced and set connection timeout to 0 (not timeout).
Also, the issue could occur if Oracle disconnect the connection from RS due
to some issues. You may want to include Oracle support on this specific
issue and look into the logs under
C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Andres" <andres.fontan@.alcuadrado.com>
>Subject: Long Oracle report
>Date: Tue, 27 Dec 2005 09:33:15 -0500
>Lines: 21
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65737
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hi,
>I'm having problems with a report that has a Oracle datasource.
>The query source is a stored procedure that takes 3 hours to complete but
>reporting services don't wait this time and shows an error page.
>Reporting Services is configured to "Do not timeout report execution" and
>the report uses "Use default setting"
>This works ok with a SLQ Server Stored procedure.
>Any ideas?
>Andrés
>
>
>
>|||Hello Peter
I tried with this with SQL Server (A report based in a stored procedure with
a waifor command before a select .... ) and it works fine!.
Now we are working with Oracle to find if it works also. The problem is that
each try is 4 hours!
Regards,
Andres
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl...
> Hello Andres,
> I'd like to know the exact error message you encountered. Also, how long
> did it take to display error message after you started to execute the
> report? You mentioned, the SP works in SQL but I wonder if the report from
> SQL server also takes 3 hours to render?
> As I know, IE version 5.x and higher have 60 minute time-out limit. A
> quick
> registry setting and a restart fixes the problem.
> 1. Add a ReceiveTimeout DWORD value with a data value of (<number
> ofseconds>)*1000 in the following registry key:
> HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
> For example, if you want the timeout duration to be 8 minutes, set the
> ReceiveTimeout data value to 480000 (<480>*1000).
> 2. Restart your computer.
> You can find more info at:
> http://support.microsoft.com/kb/q181050/
>
> You could check the datasource of Oracle in RS designer by open the data
> source->Edit->Advanced and set connection timeout to 0 (not timeout).
> Also, the issue could occur if Oracle disconnect the connection from RS
> due
> to some issues. You may want to include Oracle support on this specific
> issue and look into the logs under
> C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
>>From: "Andres" <andres.fontan@.alcuadrado.com>
>>Subject: Long Oracle report
>>Date: Tue, 27 Dec 2005 09:33:15 -0500
>>Lines: 21
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>>X-RFC2646: Format=Flowed; Original
>>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65737
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>Hi,
>>I'm having problems with a report that has a Oracle datasource.
>>The query source is a stored procedure that takes 3 hours to complete but
>>reporting services don't wait this time and shows an error page.
>>Reporting Services is configured to "Do not timeout report execution" and
>>the report uses "Use default setting"
>>This works ok with a SLQ Server Stored procedure.
>>Any ideas?
>>Andrés
>>
>>
>>
>|||Hello Andres,
Thank you for taking time to write in about the status of the issue. :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>From: "Andres" <andres.fontan@.alcuadrado.com>
>References: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
<FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl>
>Subject: Re: Long Oracle report
>Date: Tue, 3 Jan 2006 11:51:19 -0500
>Lines: 111
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>Message-ID: <OCz67XIEGHA.1424@.TK2MSFTNGP12.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:66018
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Hello Peter
>I tried with this with SQL Server (A report based in a stored procedure
with
>a waifor command before a select .... ) and it works fine!.
>Now we are working with Oracle to find if it works also. The problem is
that
>each try is 4 hours!
>Regards,
>Andres
>
>
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:FwMN0q1CGHA.3764@.TK2MSFTNGXA02.phx.gbl...
>> Hello Andres,
>> I'd like to know the exact error message you encountered. Also, how long
>> did it take to display error message after you started to execute the
>> report? You mentioned, the SP works in SQL but I wonder if the report
from
>> SQL server also takes 3 hours to render?
>> As I know, IE version 5.x and higher have 60 minute time-out limit. A
>> quick
>> registry setting and a restart fixes the problem.
>> 1. Add a ReceiveTimeout DWORD value with a data value of (<number
>> ofseconds>)*1000 in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings
>> For example, if you want the timeout duration to be 8 minutes, set the
>> ReceiveTimeout data value to 480000 (<480>*1000).
>> 2. Restart your computer.
>> You can find more info at:
>> http://support.microsoft.com/kb/q181050/
>>
>> You could check the datasource of Oracle in RS designer by open the data
>> source->Edit->Advanced and set connection timeout to 0 (not timeout).
>> Also, the issue could occur if Oracle disconnect the connection from RS
>> due
>> to some issues. You may want to include Oracle support on this specific
>> issue and look into the logs under
>> C:\Program Files\Microsoft SQL Server\mssql\reporting services\logfiles
>> Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> --
>>From: "Andres" <andres.fontan@.alcuadrado.com>
>>Subject: Long Oracle report
>>Date: Tue, 27 Dec 2005 09:33:15 -0500
>>Lines: 21
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
>>X-RFC2646: Format=Flowed; Original
>>Message-ID: <Oc3YNKvCGHA.4068@.TK2MSFTNGP14.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>>NNTP-Posting-Host: static-adsl200-75-93-227.epm.net.co 200.75.93.227
>>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:65737
>>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>>Hi,
>>I'm having problems with a report that has a Oracle datasource.
>>The query source is a stored procedure that takes 3 hours to complete but
>>reporting services don't wait this time and shows an error page.
>>Reporting Services is configured to "Do not timeout report execution" and
>>the report uses "Use default setting"
>>This works ok with a SLQ Server Stored procedure.
>>Any ideas?
>>Andrés
>>
>>
>>
>>
>
>

lokking for a specificy text in a procedure

How can i do a query to return the stored procedures with a specificy text?
regards
select object_name(id) from syscomments where text like '%YouSearchText%'
order by 1
"EdwinSlyfingster" wrote:

> How can i do a query to return the stored procedures with a specificy text?
> regards
sql

lokking for a specificy text in a procedure

How can i do a query to return the stored procedures with a specificy text?
regardsselect object_name(id) from syscomments where text like '%YouSearchText%'
order by 1
"EdwinSlyfingster" wrote:
> How can i do a query to return the stored procedures with a specificy text?
> regards

lokking for a specificy text in a procedure

How can i do a query to return the stored procedures with a specificy text?
regardsselect object_name(id) from syscomments where text like '%YouSearchText%'
order by 1
"EdwinSlyfingster" wrote:

> How can i do a query to return the stored procedures with a specificy text
?
> regards

Wednesday, March 21, 2012

logshipping

Hi,
I'll take FULL and transaction LOG backups via a custom written stored
procedure.
But its working only for single database file. Does not dynamically pick up
database files/filegroups from the backup files themselves.
Is it possible to take backup of database contain files/filegroups?
I used this code in stored procedure:
BACKUP DATABASE @.db_name TO @.vbackupdevice
WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
Any help will be appreciated.
Kind Regards,
Melih.Yea maan, i think its very impressive,
bu i am away this technical issues for 3 months,
sorry maan.
I hope you will find more usefull solution technics..
Cihan.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick up
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||Melih,
Your backup is probably working... Even when the database contains multiple
files, from one or more filegroups the backup file (given your command) will
contain all of this in a single backup file..
Do a small test , backup, then restore a multi-file database as a different
name. This should show you that things are working fine.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Melih" wrote:
> Hi,
> I'll take FULL and transaction LOG backups via a custom written stored
> procedure.
> But its working only for single database file. Does not dynamically pick up
> database files/filegroups from the backup files themselves.
> Is it possible to take backup of database contain files/filegroups?
> I used this code in stored procedure:
> BACKUP DATABASE @.db_name TO @.vbackupdevice
> WITH INIT, NAME=@.db_name, NOSKIP, STATS=10, DESCRIPTION=@.v_filename, NOFORMAT
>
> Any help will be appreciated.
> Kind Regards,
> Melih.
>|||I tested and get result:
Database 'Exchange' does not exist. Check sysdatabases.
ALTER DATABASE statement failed.
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data2' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data2_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Exchange_Data3' cannot be restored to 'C:\DATABASES
SQL2K\Exchange_Data3_Data.NDF'. Use WITH MOVE to identify a valid location
for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I used this code in stored procedure:
set @.found_full = 0
if @.@.fetch_status = 0 and @.found_full = 0
begin
set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER with
rollback immediate'
exec (@.sqlstring)
set @.sqlstring = 'alter database ' + @.dbname + ' set MULTI_USER with
rollback immediate'
exec (@.sqlstring)
-- check file isnt zipped, if so decompress & restore
if charindex('.gz', @.restore_filename) > 0 or charindex('.zip',
@.restore_filename) > 0
begin
SELECT @.sqlstring = @.zippath + '\gzip.exe -d ' + @.backuppath +
@.restore_filename
EXEC @.error = master..xp_cmdshell @.sqlstring, NO_OUTPUT
set @.restore_filename = replace(@.restore_filename, '.gz', '')
set @.restore_filename = replace(@.restore_filename, '.zip', '')
end
--set @.sqlstring = 'alter database ' + @.dbname + ' set RESTRICTED_USER
with rollback immediate'
--exec (@.sqlstring)
set @.sqlstring = 'RESTORE DATABASE ' + @.dbname + ' ' +
'FROM DISK= ''' + @.backuppath + @.restore_filename + ''' ' +
'WITH ' + @.fullbackupmovecommand + ' , STANDBY = ''' + @.standbyfile + ''''
exec (@.sqlstring)
etc......

Monday, March 12, 2012

Logins under Windows Group

Hi All,
I want to get all the Logins under Particular Windows Group by using Query
or System Stored Procedure.
For Eg: All Logins under BUILTIN\Administrators
Could anyone pls help me out?
Thanks in Advance...
Regards,
Herbert R.exec sp_helplogins
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:AC53F0BE-9938-4D4F-807B-833F00F5688E@.microsoft.com...
> Hi All,
> I want to get all the Logins under Particular Windows Group by using Query
> or System Stored Procedure.
> For Eg: All Logins under BUILTIN\Administrators
> Could anyone pls help me out?
> Thanks in Advance...
> Regards,
> Herbert R.

Logins to the website doesnt work after DB backing up and restoring

Hi all, plz help...

I have an asp.net login control on my website;

my users are stored in a SQL DB;

The problem is when I back-up my DB from my PC and restore it to my server; the logins doesn't work anymore. I mean the user I've created on my PC can't login on the website on the internet...

Thanks a lot...

I've had this same problem. If you delete the user from the database and re-add it, it will work. Does anyone know why this happens? Is there a numeric ID the server uses for the user name?|||

Thank god someone else had the same problem; it's been so long that I'm working on it that I allways thought it only happens to me..

Yes if I delete and then re-register same user it works but I can't say to my users please re-register:))

is there any way to programmaticaly do it? or any REAL solution??

How did you handle that? I mean does it work now?

|||Wait, when you say they are stored in a sql db, do you mean they are stored in a table or they are sql logins of the database? This happened to me when I had a user with a sql login.|||Backup/Restore does not transfer the logins. You need to generate the script to create the logins and run it to set the permissions after your restore.|||

They are in a table..

I'm not talking about SQL logins. Logins are aspnetdb logins for a web site. I mean users inside the aspnetdb_Membership and aspnetdb_Users tables...

|||The users may be in a table. You still need to run a script to SET the permissions for the users.|||

What script s that?

Users (I mean the user connecting by internet) already got permissions and re-mapped?

|||

The user exists in your table but he/she does not yet have the permissions to execute any scripts.

Check out BOL for sp_addlogin, sp_grantlogin, sp_defaultdb, etc.. If you are not sure of the scripts, try generating the script for existing logins.

|||

Hi,

I think the answer is hidden somewhere over there I didn't even realised that there could be that much stored procedure and functions... Anyways I found that sp's but I didn't understand anything:) how can I work em out from a query? Also I found "dt_validateloginparams" could that also be related?

Actually theres a parameter HasAccess like below:

EXEC

%%LocalLogin(Name= @.loginame).SetAccess(HasAccess= 1)

I think this is the key line but where does it store this HasAccess info? I couldn't find it in the tables?

Thanks a lot...

|||

In Enterprise Manager, right click on the DB -> All Tasks -> Generate Scripts -> Options tab -> select "Script SQL Server Logins" -> Back to General tab -> Click on Preview.

You can either paste the code into a stored proc and call it from your application or parameterize the scripts and use it for any login...as creative as you can get...

|||

Hi;

I'm sorry; I'm as lame as I couldn't figure it out. Maybe you already told me the answer but I don't know...

Could you please tell me what to do:)

Thanks a lot..

|||I just did. Thats the best I can do.|||Ok man; thanks.. I'll try..|||http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

Friday, March 9, 2012

Login with stored procedure

In my last post I was having doubt in Registration form and obviously I'm having doubt now in Login form. I can write stored procedure if Login Control is used, but I've created Login form manually and dunno how to do, but I've tried some. Can any one help me?

Login.aspx

 "LoginUserNameLabel" runat="server" Font-Bold="True" Style="z-index: 106; left: 625px; position: absolute; top: 179px" Text="Username:" Font-Size="Small"> "LoginPasswordLabel" runat="server" Font-Bold="True" Style="z-index: 107; left: 625px; position: absolute; top: 225px" Text="Password:" Font-Size="Small"> "LoginUserNameTextBox" runat="server" Style="z-index: 108; left: 696px; position: absolute; top: 175px" BorderStyle="Solid"> "LoginPasswordTextBox" runat="server" TextMode="password" Style="z-index: 109; left: 695px; position: absolute; top: 222px" BorderStyle="Solid"> "LoginRememberMeCheckBox" runat="server" Style="z-index: 110; left: 624px; position: absolute; top: 276px" Text="Remember me next time." Font-Size="Small" /> "LoginSubmitButton" runat="server" CausesValidation="true" Style="z-index: 111; left: 786px; position: absolute; top: 288px" Text="Login" Width="65px" BackColor="Transparent" BorderColor="Black" BorderStyle="Solid" ForeColor="Black" OnAuthenticate="Login_Authenticate" />

Login.aspx.cs:

protected void LoginSubmitButton_Click(object sender, EventArgs e) {bool authenticated;string uname, pwd; uname = LoginUserNameTextBox.ToString() ; pwd = LoginPasswordTextBox.ToString(); authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());//e.authenticated;if (authenticated ==true) { Response.Redirect("Safety.aspx"); } }private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox) {bool boolvalue =false; SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI"); SqlCommand cmd =new SqlCommand("usp_ulogin",conn); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader dr; conn.Open(); dr = cmd.ExecuteNonQuery();while(dr.Read()) {if((LoginUserNameTextBox==dr["uname"].ToString())&(LoginPasswordTextBox==dr["pwd"].ToString())); { boolvalue=true; } dr.Close();return boolvalue; conn.Close(); } }}

Stored Procedure:

select * from mytable

create procedure usp_ulogin

@.uname varchar(30),
@.pwd varchar(20)

select uname,pwd from mytable
where
uname=@.uname and
pwd=@.pwd

Is there any thing that I should correct in my code or my code is itself wrong. And can any one explain me what is happening in the code, because I'm little week in c# ;-)

private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox)
{
bool boolvalue =false;
SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI");
SqlCommand cmd =new SqlCommand("usp_ulogin",conn);

SqlParameter uname = new SqlParameter("@.uname", SqlDbType.Varchar,30);

uname.Value=LoginUserNameTextBox;

cmd.Parameters.Add(uname);

SqlParameter pwd = new SqlParameter("@.pwd", SqlDbType.Varchar,20);

pwd.Value=LoginPasswordTextBox;

cmd.Parameters.Add(pwd);

cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr;
conn.Open();

dr =cmd.ExecuteReader()
while(dr.Read())
{
if((LoginUserNameTextBox==dr["uname"].ToString())&(LoginPasswordTextBox==dr["pwd"].ToString()));
{
boolvalue=true;
}
dr.Close();
return boolvalue;
conn.Close();
}
}
}

my brother if ur using DataReader class use com.executereader() method rather than ExecuteNonquery

second thing u should provide value to the stored procedure like this

first u should create object of sqlparameter

here 2 objects are created bcz ur stored procedure have 2 parametere having same datatype and size of sqlparameter object which u have taken in stored procedure

SqlParameter uname = new SqlParameter("@.uname", SqlDbType.Varchar,30);

uname.Value=LoginUserNameTextBox;

cmd.Parameters.Add(uname);

SqlParameter pwd = new SqlParameter("@.pwd", SqlDbType.Varchar,20);

pwd.Value=LoginPasswordTextBox;

cmd.Parameters.Add(pwd);

i have chnged ur code appropriately above it may work for u

check out ur stored procedure once more

|||

Hai,

I did as you said, but getting 2 errors:

Error 1 'System.Data.SqlDbType' does not contain a definition for 'Varchar'

Error 2 'System.Data.SqlDbType' does not contain a definition for 'Varchar'

My Code is Below:

protected void LoginSubmitButton_Click(object sender, EventArgs e) {bool authenticated;string uname, pwd; uname = LoginUserNameTextBox.ToString(); pwd = LoginPasswordTextBox.ToString(); authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());//e.authenticated;if (authenticated ==true) { Response.Redirect("Safety.aspx"); } }private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox) {bool boolvalue =false; SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI"); SqlCommand cmd =new SqlCommand("usp_ulogin",conn);SqlParameter uname =new SqlParameter("@.uname", SqlDbType.Varchar,30);uname.Value=LoginUserNameTextBox;cmd.Parameters.Add(uname);SqlParameter pwd =new SqlParameter("@.pwd", SqlDbType.Varchar,20);pwd.Value=LoginPasswordTextBox;cmd.Parameters.Add(pwd); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader dr; conn.Open(); dr =cmd.ExecuteReader();while(dr.Read()) {if((LoginUserNameTextBox==dr["uname"].ToString())&(LoginPasswordTextBox==dr["pwd"].ToString())) { boolvalue=true; } dr.Close();return boolvalue; conn.Close(); } }

I'm really confused!

|||

If u r using VS2005 u have to write like this

SqlParameter pwd =new SqlParameter("@.pwd", Data.SqlDbType.Varchar,20);

and remainig thing is ok

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Yes I'm using VS 2005. The application is executing correctly, but it is not redirected to "Terms.aspx" page. I dunno whether the boolean is returning correct value.

protected void LoginSubmitButton_Click(object sender, EventArgs e)
 {
bool authenticated =false;
string uname, pwd;
 uname = LoginUserNameTextBox.ToString();
 pwd = LoginPasswordTextBox.ToString();
 authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());
//e.authenticated; -- //what is this used for. I've used this for Login Control. But here, is it necessary?if (authenticated ==true)
 {
 Response.Redirect("Terms.aspx");
 }
 }private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox)
 {
bool boolvalue =false;
 SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI");
 SqlCommand cmd =new SqlCommand("usp_ulogin", conn);
//SqlParameter uname = new SqlParameter("@.uname", System.Data.SqlDbType.Varchar,30); SqlParameter uname =new SqlParameter("@.uname", System.Data.SqlDbType.VarChar, 30);
 uname.Value = LoginUserNameTextBox;
 cmd.Parameters.Add(uname);
 SqlParameter pwd =new SqlParameter("@.pwd", System.Data.SqlDbType.VarChar, 20);
 pwd.Value = LoginPasswordTextBox;
 cmd.Parameters.Add(pwd);
 cmd.CommandType = CommandType.StoredProcedure;
 SqlDataReader dr;
 conn.Open();
 dr = cmd.ExecuteReader();
while (dr.Read())
 {
if ((LoginUserNameTextBox == dr["@.uname"].ToString()) & (LoginPasswordTextBox == dr["@.pwd"].ToString()))
 {
 boolvalue =true;
//return boolvalue; }else { LoginLoginFailureLabel.Text ="Login failed";
 }
 dr.Close();
//return boolvalue; conn.Close(); }return boolvalue;
//conn.Close(); }

|||

any idea?

|||

U applied executereader. Actually u have to use executenonquery.Plz check out.

u may use execute reader also. I dont no wy u applied while here.

U can write sql stmt like this

This is in stored procedure

Select user,pwd from users where user=@.user and pwd=@.pwd

In .net u have to add the parameters like this. i dont no c# i m giving u in vb.net

cmd.parameter.add("@.uid",data.sqldbtype.varchar,30).value=txtuser.text

cmd.parameter.add("@.pwd",data.sqldbtype.varchar,20).value=txtpwd.text

Plz gothrough it sure u will get it.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

I'm really getting confused with this. Every time when I change something, I get error.

When I change cmd.ExecuteReadyer() tocmd.ExecuteNonQuery(), I gett error as Cannot implicitly convert type 'int' to 'System.Data.SqlClient.SqlDataReader'

Here is my code again. I dunno whether the logic works correct or not. The application is executing. I'm entering the correct username and password in the text box, it shows the same Login.aspx page only. Its not redirecting to Terms.aspx.

protected void LoginSubmitButton_Click(object sender, EventArgs e) {bool authenticated =false;string uname, pwd; uname = LoginUserNameTextBox.ToString(); pwd = LoginPasswordTextBox.ToString(); authenticated = SiteLevelCustomAuthenticationMethod(LoginUserNameTextBox.ToString(), LoginPasswordTextBox.ToString());//e.authenticated;if (authenticated ==true) { Response.Redirect("Terms.aspx"); } }private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox) {bool boolvalue =false; SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI"); SqlCommand cmd =new SqlCommand("usp_ulogin", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter unamepar =new SqlParameter("@.uname", System.Data.SqlDbType.VarChar); SqlParameter pwdpar =new SqlParameter("@.pwd", System.Data.SqlDbType.VarChar); unamepar.Direction = ParameterDirection.Input; pwdpar.Direction = ParameterDirection.Input; unamepar.Value = LoginUserNameTextBox; pwdpar.Value = LoginPasswordTextBox; SqlDataReader dr; conn.Open(); dr = cmd.ExecuteNonQuery();while (dr.Read()) {if ((LoginUserNameTextBox == dr["uname"].ToString()) & (LoginPasswordTextBox == dr["pwd"].ToString())) { boolvalue =true; } dr.Close(); conn.Close(); }return boolvalue; }

|||

Finally I got it resolved. Actually I didn't add the parameter to the command. And I didn't pass the text value, that is the reason I was not able to redirect to
"terms.aspx" file. Anyhow thanks for all of them who helped me a lot. I really gained many things from you people.

Here is the corrected code in C#:

private bool SiteLevelCustomAuthenticationMethod(string LoginUserNameTextBox,string LoginPasswordTextBox)
 {
bool boolvalue =false;
 SqlConnection conn =new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds; Integrated Security=SSPI");
 SqlCommand cmd =new SqlCommand("usp_ulogin", conn);
 cmd.CommandType = CommandType.StoredProcedure;
 SqlParameter unamepar =new SqlParameter("@.uname", System.Data.SqlDbType.VarChar);
 SqlParameter pwdpar =new SqlParameter("@.pwd", System.Data.SqlDbType.VarChar);
 unamepar.Direction = ParameterDirection.Input;
 pwdpar.Direction = ParameterDirection.Input;
 unamepar.Value = LoginUserNameTextBox;
 pwdpar.Value = LoginPasswordTextBox;
 cmd.Parameters.Add(unamepar);<-- I didn't add the parameter earlier to the command
 cmd.Parameters.Add(pwdpar);<-- I didn't add the parameter earlier to the command
 SqlDataReader dr;
 conn.Open();
 dr = cmd.ExecuteReader();
while (dr.Read())
 {
if ((LoginUserNameTextBox == dr["uname"].ToString()) & (LoginPasswordTextBox == dr["pwd"].ToString()))
 {
 boolvalue =true;
 }
 }
return boolvalue;
 dr.Close();
 conn.Close(); }
protected void LoginSubmitButton_Click1(object sender, EventArgs e)
 {
bool authenticated =false;
string uname, pwd;
 uname = LoginUserNameTextBox.Text.ToString();<-- And this is another error which I did
 pwd = LoginPasswordTextBox.Text.ToString();<-- And this is another error which I did
 authenticated = SiteLevelCustomAuthenticationMethod(uname,pwd);
//e.authenticated;if (authenticated ==true)
 {
 Response.Redirect("Terms.aspx");
 }
else { LoginLoginFailureLabel.Text ="Username or Password incorrect";
 }
 }

|||

I think u didnt check my code properly. Clearly i mentioned the parameters coding. Plz check out

cmd.parameter.add("@.uid",data.sqldbtype.varchar,30).value=txtuser.text

cmd.parameter.add("@.pwd",data.sqldbtype.varchar,20).value=txtpwd.text

Thank u

Baba

|||

Yes I checked your answer, but I dunno vb.net that much. But guessed that I made some mistake by seeing your code. Any way, thanks a lot for the help. I'll try to work in vb.net also. Your reply was very worth. Thanks.

Wednesday, March 28, 2012

long running report can not be rendered in Report Manager or through Subscription

long running report can not be rendered in Report Manager or through Subscription

long running report can not be rendered in Report Manager or through Subscription

Monday, March 26, 2012

Long parameter list for stored procs

Long Oracle report

lokking for a specificy text in a procedure

lokking for a specificy text in a procedure

lokking for a specificy text in a procedure

Wednesday, March 21, 2012

logshipping

Monday, March 12, 2012

Logins under Windows Group

Logins to the website doesnt work after DB backing up and restoring

Friday, March 9, 2012

Login with stored procedure

sql login permissions

Blog Archive

About Me