Activated stores procedure and Batch Processing

Hi There

2 Questions :

1. Almost in every SB example you will see this sql :

BEGIN TRANSACTION

WAITFOR (

RECEIVE TOP (1)

@.MessageType = message_type_name,

@.Message = message_body

FROM [Queue1]

WHERE conversation_handle = @.ConversationHandle

), timeout 5000;

If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?

2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?

Thanx

Hi Dietz,

1. The purpose of the WAITFOR is to have the procedure linger a few seconds when it empties the queue, in hope a new message comes in and gets processed. W/o this technique, the procedure might be deactivated just to be activated immedeatly by a new message.

2. To process a batch of messages, the best approach is indeed to RECEIVE into a @.table variable and process a cursor over the @.table variable. A cursor over a @.table variable is quite efficient, specially if the cursor is kept open between RECEIVEs.
One thing to keep in mind is the particular message pattern expected by the service. Remeber that RECEIVE only returns messages for one particular conversation_group at a time, which in most cases equates one conversation. If the typical message pattern is request-reply, then the RECEIVE will mostly return only 1 message at a time anyway, so using the TOP(1) might be simpler and more efficient.
My recommendation is to actually test and measure the performance, because it depends a lot with the message flow pattern.

HTH,
~ Remus

|||

Hi Remus

Initially it seems our app will only send individual messages and the target simpy receive and end conversation, and not have long conversatons or conversation groups spanning many dailogs, so in that case would you agree a top(1) should work fine ? So processing batches is only more efficient if you have large conversation groups , which is not true in our case where it is just a single conversation.

Thanx

Actiuve/Active

Hi,
I have to setup a Active/Active cluster and I need to know if there has to
be made any change in the applications that the user has? For example, direct
the app to the other virtual server, etc...
Thanks
What do you mean by "Active-Active"? It sounds like you may be
misunderstanding what the terms mean. "Active-Active" describes an onder
clustering technology that went away after SQL 7.0. The current correct
term is "multi-instance" and acts just like having multiple instances of SQL
Server on a single, non-clustered host.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:A2B00583-DDC2-41A1-94D9-394F2E985C6B@.microsoft.com...
> Hi,
> I have to setup a Active/Active cluster and I need to know if there has to
> be made any change in the applications that the user has? For example,
> direct
> the app to the other virtual server, etc...
>
> Thanks
|||Sorry,
what I meant was, we're going in a process of installing a cluster using
something similar to what then was called "Active/Active" in a sense that one
server serves a set of databases a acts as a failover through a instance what
will be activated in case of a failure in the other server. Something like:
Server1: Actives databases: A, B, C
Inactives databases: D, E, F
Server2: Actives databases: D, E, F
Inactives databases: A, B, C
In case of a failure in Server1, the Server2, serves not only databases D,
E, F, but also A, B, C. I think that's called in the old terminology
"Active/Active". My major point is, do I have to make some sort of change in
my applications to "redirect" them to the "new server" or the cluster can
that care of that automatically?
Is there any source that really clarifies clustering technology in terms of
applications made by our programmers?
Thanks for your observations, point taken.
"Geoff N. Hiten" wrote:

> What do you mean by "Active-Active"? It sounds like you may be
> misunderstanding what the terms mean. "Active-Active" describes an onder
> clustering technology that went away after SQL 7.0. The current correct
> term is "multi-instance" and acts just like having multiple instances of SQL
> Server on a single, non-clustered host.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:A2B00583-DDC2-41A1-94D9-394F2E985C6B@.microsoft.com...
>
>
|||Ok, you do have an almost correct picture of SQL Clustering.
It works more like this:
Instance 1: Databases A, B, C
Instance 2: Databases D, E, F
Host machines: Node 1, Node 2
Normal operation has Instance 1 on Node 1 and Instance 2 on Node 2. If node
2 fails, Node 1 grabs the resources for Instance 2 and starts up Instance 2
on Node 1. The outside world still sees Instance 2 with the exact same
names, security, and IP address(es) as before. The two( or more depending
on your cluster configuration) instances look like completely different and
independent servers to the outside world. Reconnection is necessary since
the SQL service stopped and restarted, but no client redirection is required
since the clients connect to a virtual IP address.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:C20D7184-9C4B-4D63-B3F8-AA37951F3CC0@.microsoft.com...[vbcol=seagreen]
> Sorry,
> what I meant was, we're going in a process of installing a cluster using
> something similar to what then was called "Active/Active" in a sense that
> one
> server serves a set of databases a acts as a failover through a instance
> what
> will be activated in case of a failure in the other server. Something
> like:
> Server1: Actives databases: A, B, C
> Inactives databases: D, E, F
> Server2: Actives databases: D, E, F
> Inactives databases: A, B, C
> In case of a failure in Server1, the Server2, serves not only databases D,
> E, F, but also A, B, C. I think that's called in the old terminology
> "Active/Active". My major point is, do I have to make some sort of change
> in
> my applications to "redirect" them to the "new server" or the cluster can
> that care of that automatically?
> Is there any source that really clarifies clustering technology in terms
> of
> applications made by our programmers?
> Thanks for your observations, point taken.
>
> "Geoff N. Hiten" wrote:
|||Thanks, this post helped a lot to clarify these points to me.
"Geoff N. Hiten" wrote:

> Ok, you do have an almost correct picture of SQL Clustering.
> It works more like this:
> Instance 1: Databases A, B, C
> Instance 2: Databases D, E, F
> Host machines: Node 1, Node 2
> Normal operation has Instance 1 on Node 1 and Instance 2 on Node 2. If node
> 2 fails, Node 1 grabs the resources for Instance 2 and starts up Instance 2
> on Node 1. The outside world still sees Instance 2 with the exact same
> names, security, and IP address(es) as before. The two( or more depending
> on your cluster configuration) instances look like completely different and
> independent servers to the outside world. Reconnection is necessary since
> the SQL service stopped and restarted, but no client redirection is required
> since the clients connect to a virtual IP address.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:C20D7184-9C4B-4D63-B3F8-AA37951F3CC0@.microsoft.com...
>
>

acess I/O File

Is it possible to access a file (for example, to write in a text file)
with transact-sql?.
I've create a stored procedure and I'd like to write in a log file
some traces depends on the result of the statements executed in this
stored procedure.
ThanksHere's one method:

ALTER PROC WriteToLog
@.Message varchar(100)
AS
DECLARE @.Command varchar(255)
BEGIN TRAN
-- ensure only one process writes to the log at a time
EXEC sp_getapplock
@.Resource = 'MyLog.txt',
@.lockmode = 'Exclusive'
SET @.Command = 'ECHO ' +
@.Message +
' >>"C:\MyLog.txt"'
EXEC master..xp_cmdshell @.Command, NO_OUTPUT
EXEC sp_releaseapplock @.Resource = 'MyLog.txt'
COMMIT
GO

EXEC WriteToLog 'Test message'

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Bego?a" <bego_colomer@.yahoo.es> wrote in message
news:c04c2a85.0311180409.4631ec98@.posting.google.c om...
> Is it possible to access a file (for example, to write in a text file)
> with transact-sql?.
> I've create a stored procedure and I'd like to write in a log file
> some traces depends on the result of the statements executed in this
> stored procedure.
> Thanks

Accessing ReportManager with FormsAuthentication

Hi,
I am trying to implement a custom security extension using the Forms Auth
example provided by Microsoft. When i type in http://localhost/ReportServer,
I can see the Logon.aspx page. But when i type in http://localhost/Reports/,
I get a parser error saying
----
Parser Error
Description: An error occurred during the parsing of a resource required to
service this request. Please review the following specific parse error
details and modify your source file appropriately.
Parser Error Message: Could not load type
'Microsoft.Samples.ReportingServices.CustomSecurity.UILogon'.
Source Error:
Line 1: <%@. Page language="c#" Codebehind="UILogon.aspx.cs"
AutoEventWireup="false"
Inherits="Microsoft.Samples.ReportingServices.CustomSecurity.UILogon" %>
Line 2: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
Line 3: <HTML>
Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager\Pages\UILogon.aspx Line: 1
-----
Seems like it might have something to do wht permissions on the dll...Did
anyone have this problem? Or any ideas why this is happening?I have the same problem. Any luck with it?
"Aparna" wrote:
> Hi,
> I am trying to implement a custom security extension using the Forms Auth
> example provided by Microsoft. When i type in http://localhost/ReportServer,
> I can see the Logon.aspx page. But when i type in http://localhost/Reports/,
> I get a parser error saying:
> ----
> Parser Error
> Description: An error occurred during the parsing of a resource required to
> service this request. Please review the following specific parse error
> details and modify your source file appropriately.
> Parser Error Message: Could not load type
> 'Microsoft.Samples.ReportingServices.CustomSecurity.UILogon'.
> Source Error:
> Line 1: <%@. Page language="c#" Codebehind="UILogon.aspx.cs"
> AutoEventWireup="false"
> Inherits="Microsoft.Samples.ReportingServices.CustomSecurity.UILogon" %>
> Line 2: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> Line 3: <HTML>
> Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager\Pages\UILogon.aspx Line: 1
> -----
> Seems like it might have something to do wht permissions on the dll...Did
> anyone have this problem? Or any ideas why this is happening?|||Yeah, i later realized that I accidentally changed the wrong config file i.e.
I gave the 'My Computer' code group 'Full Trust' in the rssrvpolicy.config
file, in stead of the 'rsmgrpolicy.config' file... Everything worked like a
charm after that.. Hope that helps you....
"Minas Papageorgiou" wrote:
> I have the same problem. Any luck with it?
> "Aparna" wrote:
> > Hi,
> > I am trying to implement a custom security extension using the Forms Auth
> > example provided by Microsoft. When i type in http://localhost/ReportServer,
> > I can see the Logon.aspx page. But when i type in http://localhost/Reports/,
> > I get a parser error saying:
> > ----
> > Parser Error
> > Description: An error occurred during the parsing of a resource required to
> > service this request. Please review the following specific parse error
> > details and modify your source file appropriately.
> > Parser Error Message: Could not load type
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.UILogon'.
> > Source Error:
> > Line 1: <%@. Page language="c#" Codebehind="UILogon.aspx.cs"
> > AutoEventWireup="false"
> > Inherits="Microsoft.Samples.ReportingServices.CustomSecurity.UILogon" %>
> > Line 2: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > Line 3: <HTML>
> >
> > Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\ReportManager\Pages\UILogon.aspx Line: 1
> > -----
> > Seems like it might have something to do wht permissions on the dll...Did
> > anyone have this problem? Or any ideas why this is happening?|||Are you using Enterprise Edition of SQL'
"Aparna" wrote:
> Yeah, i later realized that I accidentally changed the wrong config file i.e.
> I gave the 'My Computer' code group 'Full Trust' in the rssrvpolicy.config
> file, in stead of the 'rsmgrpolicy.config' file... Everything worked like a
> charm after that.. Hope that helps you....
> "Minas Papageorgiou" wrote:
> > I have the same problem. Any luck with it?
> >
> > "Aparna" wrote:
> >
> > > Hi,
> > > I am trying to implement a custom security extension using the Forms Auth
> > > example provided by Microsoft. When i type in http://localhost/ReportServer,
> > > I can see the Logon.aspx page. But when i type in http://localhost/Reports/,
> > > I get a parser error saying:
> > > ----
> > > Parser Error
> > > Description: An error occurred during the parsing of a resource required to
> > > service this request. Please review the following specific parse error
> > > details and modify your source file appropriately.
> > > Parser Error Message: Could not load type
> > > 'Microsoft.Samples.ReportingServices.CustomSecurity.UILogon'.
> > > Source Error:
> > > Line 1: <%@. Page language="c#" Codebehind="UILogon.aspx.cs"
> > > AutoEventWireup="false"
> > > Inherits="Microsoft.Samples.ReportingServices.CustomSecurity.UILogon" %>
> > > Line 2: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > Line 3: <HTML>
> > >
> > > Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > > Services\ReportManager\Pages\UILogon.aspx Line: 1
> > > -----
> > > Seems like it might have something to do wht permissions on the dll...Did
> > > anyone have this problem? Or any ideas why this is happening?|||Developer's Edition
"Minas Papageorgiou" wrote:
> Are you using Enterprise Edition of SQL'
>
> "Aparna" wrote:
> > Yeah, i later realized that I accidentally changed the wrong config file i.e.
> > I gave the 'My Computer' code group 'Full Trust' in the rssrvpolicy.config
> > file, in stead of the 'rsmgrpolicy.config' file... Everything worked like a
> > charm after that.. Hope that helps you....
> >
> > "Minas Papageorgiou" wrote:
> >
> > > I have the same problem. Any luck with it?
> > >
> > > "Aparna" wrote:
> > >
> > > > Hi,
> > > > I am trying to implement a custom security extension using the Forms Auth
> > > > example provided by Microsoft. When i type in http://localhost/ReportServer,
> > > > I can see the Logon.aspx page. But when i type in http://localhost/Reports/,
> > > > I get a parser error saying:
> > > > ----
> > > > Parser Error
> > > > Description: An error occurred during the parsing of a resource required to
> > > > service this request. Please review the following specific parse error
> > > > details and modify your source file appropriately.
> > > > Parser Error Message: Could not load type
> > > > 'Microsoft.Samples.ReportingServices.CustomSecurity.UILogon'.
> > > > Source Error:
> > > > Line 1: <%@. Page language="c#" Codebehind="UILogon.aspx.cs"
> > > > AutoEventWireup="false"
> > > > Inherits="Microsoft.Samples.ReportingServices.CustomSecurity.UILogon" %>
> > > > Line 2: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > Line 3: <HTML>
> > > >
> > > > Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > > > Services\ReportManager\Pages\UILogon.aspx Line: 1
> > > > -----
> > > > Seems like it might have something to do wht permissions on the dll...Did
> > > > anyone have this problem? Or any ideas why this is happening?|||Do not use localhost as url path. U can try giving your machine name and
check if that works.
Under C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer:
open the web.config file.
Under appSettings tag change url path like:
<add key="ReportServer"
value="</add>">http://ApplicationServerName.YOurCompanyName.com/ReportServer"></add>
<add key="ReportServerExternalUrl"
value="</add>">http://ApplicationServerName.YOurCompanyName.com/ReportServer"></add>
Under C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager:
Open RSWebApplication.config:
Under Configuration tag:
<UI>
<CustomAuthenticationUI>
<loginUrl>/Pages/UILogon.aspx</loginUrl>
<UseSSL>False</UseSSL>
</CustomAuthenticationUI>
<ReportServerUrl>http://ApplicationServerName.YOurCompanyName.com/ReportServer</ReportServerUrl>
</UI>
I had the same issue.
Im assuming that you are using Form Authentication Code as in:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
Good luck!
Rajesh
"Minas Papageorgiou" wrote:
> Are you using Enterprise Edition of SQL'
>
> "Aparna" wrote:
> > Yeah, i later realized that I accidentally changed the wrong config file i.e.
> > I gave the 'My Computer' code group 'Full Trust' in the rssrvpolicy.config
> > file, in stead of the 'rsmgrpolicy.config' file... Everything worked like a
> > charm after that.. Hope that helps you....
> >
> > "Minas Papageorgiou" wrote:
> >
> > > I have the same problem. Any luck with it?
> > >
> > > "Aparna" wrote:
> > >
> > > > Hi,
> > > > I am trying to implement a custom security extension using the Forms Auth
> > > > example provided by Microsoft. When i type in http://localhost/ReportServer,
> > > > I can see the Logon.aspx page. But when i type in http://localhost/Reports/,
> > > > I get a parser error saying:
> > > > ----
> > > > Parser Error
> > > > Description: An error occurred during the parsing of a resource required to
> > > > service this request. Please review the following specific parse error
> > > > details and modify your source file appropriately.
> > > > Parser Error Message: Could not load type
> > > > 'Microsoft.Samples.ReportingServices.CustomSecurity.UILogon'.
> > > > Source Error:
> > > > Line 1: <%@. Page language="c#" Codebehind="UILogon.aspx.cs"
> > > > AutoEventWireup="false"
> > > > Inherits="Microsoft.Samples.ReportingServices.CustomSecurity.UILogon" %>
> > > > Line 2: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> > > > Line 3: <HTML>
> > > >
> > > > Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > > > Services\ReportManager\Pages\UILogon.aspx Line: 1
> > > > -----
> > > > Seems like it might have something to do wht permissions on the dll...Did
> > > > anyone have this problem? Or any ideas why this is happening?|||I too am trying to use the security extenstions as explained here
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true
I am using SQL Server Standard edition but when I try to login to the sample
login form "UILogon.aspx" I get the error:
This operation is not supported in this edition of Reporting Services
(rsOperationNotSupported)
Are there different versions of Reporting Services or is the version taken
from the SQL Server edition?
Can't I use forms authentication with SQL Server Standard edition?
Please and thank you.
Greg
"Aparna" wrote:
> Hi,
> I am trying to implement a custom security extension using the Forms Auth
> example provided by Microsoft. When i type in http://localhost/ReportServer,
> I can see the Logon.aspx page. But when i type in http://localhost/Reports/,
> I get a parser error saying:
> ----
> Parser Error
> Description: An error occurred during the parsing of a resource required to
> service this request. Please review the following specific parse error
> details and modify your source file appropriately.
> Parser Error Message: Could not load type
> 'Microsoft.Samples.ReportingServices.CustomSecurity.UILogon'.
> Source Error:
> Line 1: <%@. Page language="c#" Codebehind="UILogon.aspx.cs"
> AutoEventWireup="false"
> Inherits="Microsoft.Samples.ReportingServices.CustomSecurity.UILogon" %>
> Line 2: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> Line 3: <HTML>
> Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager\Pages\UILogon.aspx Line: 1
> -----
> Seems like it might have something to do wht permissions on the dll...Did
> anyone have this problem? Or any ideas why this is happening?|||You need to use RS Enterprise edition for custom security extensions to
work.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsinstall/htm/gs_installingrs_v1_72jq.asp
"Greg Allan" <GregAllan@.discussions.microsoft.com> wrote in message
news:0C739298-5CD2-410D-8955-36A2B4B976DF@.microsoft.com...
>I too am trying to use the security extenstions as explained here:
> http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true
> I am using SQL Server Standard edition but when I try to login to the
> sample
> login form "UILogon.aspx" I get the error:
> This operation is not supported in this edition of Reporting Services
> (rsOperationNotSupported)
> Are there different versions of Reporting Services or is the version taken
> from the SQL Server edition?
> Can't I use forms authentication with SQL Server Standard edition?
> Please and thank you.
> Greg
>
> "Aparna" wrote:
>> Hi,
>> I am trying to implement a custom security extension using the Forms Auth
>> example provided by Microsoft. When i type in
>> http://localhost/ReportServer,
>> I can see the Logon.aspx page. But when i type in
>> http://localhost/Reports/,
>> I get a parser error saying:
>> ----
>> Parser Error
>> Description: An error occurred during the parsing of a resource required
>> to
>> service this request. Please review the following specific parse error
>> details and modify your source file appropriately.
>> Parser Error Message: Could not load type
>> 'Microsoft.Samples.ReportingServices.CustomSecurity.UILogon'.
>> Source Error:
>> Line 1: <%@. Page language="c#" Codebehind="UILogon.aspx.cs"
>> AutoEventWireup="false"
>> Inherits="Microsoft.Samples.ReportingServices.CustomSecurity.UILogon" %>
>> Line 2: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
>> Line 3: <HTML>
>> Source File: C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>> Services\ReportManager\Pages\UILogon.aspx Line: 1
>> -----
>> Seems like it might have something to do wht permissions on the dll...Did
>> anyone have this problem? Or any ideas why this is happening?