Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Showing posts with label implement. Show all posts
Showing posts with label implement. Show all posts
Friday, March 30, 2012
predicate locking
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Depends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Depends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
predicate locking
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETsql
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETsql
predicate locking
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does is
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"
> Now I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?
But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.
Here is one repro that I tried:
In the first query window I ran:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION
SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
This query returns two rows: FISSA and PARIS
In the second I ran:
insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')
This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"
> Now I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?
But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.
Here is one repro that I tried:
In the first query window I ran:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION
SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
This query returns two rows: FISSA and PARIS
In the second I ran:
insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')
This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.
Tom had me worried for a second.
Regards,
Daniel Roth
MCSD.NET
Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "To guarantee true mathematical serializability, it is
> > necessary for a database system to enforce predicate locking, which
> > means that a transaction cannot insert or modify a row that would have
> > matched the WHERE condition of a query in another concurrent
> > transaction"
> > Now I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.
Tom had me worried for a second.
Regards,
Daniel Roth
MCSD.NET
Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "To guarantee true mathematical serializability, it is
> > necessary for a database system to enforce predicate locking, which
> > means that a transaction cannot insert or modify a row that would have
> > matched the WHERE condition of a query in another concurrent
> > transaction"
> > Now I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||As a basic transaction question, why did the second one block if the
first hadn't changed any rows and the query was done executing?|||pb648174 (google@.webpaul.net) writes:
> As a basic transaction question, why did the second one block if the
> first hadn't changed any rows and the query was done executing?
Because that transaction had isolation level serializable. This means
that if you resubmit a query with in the transaction, you should
essentially get the same results. Therefore writes must be blocked
until the reader transaction commits.
In the default isolation level "Read Committed", the same query in
the same transaction can give different results, and thus locks can
be released once data has been read.
There is also "Repeatable Read" which is between Serializable and Read
Committed. With Repeatable Read, reading the same row twice should yield
the same result. However, a for a range query that is resubmitted newly
entered rows may appear, so called "phantom inserts".
Finally there is "read uncommitted" in which no locks are taken for
reading at all, and thus you can get to read uncommitted data.
And, oh, in SQL 2005, there is a new level called snapshot. The result
is essentially the same as in serializable, but instead of locking
rows, old versions of changed rows are added to a snapshot. In this
way readers do not block writers. Also, the reader gets a wholly
consistent view of the database at a certain point in time, which you
in fact cannot achieve with Serializable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It seems that the serializable should just not "get" the updated rows
when it does queries, not lock the table for everyone else... It seems
like that is what the snapshot is I guess.|||pb648174 (google@.webpaul.net) writes:
> It seems that the serializable should just not "get" the updated rows
> when it does queries, not lock the table for everyone else... It seems
> like that is what the snapshot is I guess.
Without a snapshot like there is an Oracle and Rdb, it's difficult to
do serializable without blocking writers. A snapshot effectively means
that there is more than once instance of the data, and in SQL Server
there is only one single.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Monday, March 26, 2012
Prblem with MSDN Article on forms authentication for rs2005
Hi All
i wanted to implement foms authentication to RS2005.i am
using the MSDN article to implement the forms authentication.when build
the project i am getting this error.
'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
not implement interface member
'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
System.IntPtr, byte[],
Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
can any one help me with this problem.
ThanksTry opening your reference and see any problems otherwise remove the
reference and search for the reference and add it again..
Amarnath
"vamsi" wrote:
> Hi All
> i wanted to implement foms authentication to RS2005.i am
> using the MSDN article to implement the forms authentication.when build
> the project i am getting this error.
>
> 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> not implement interface member
> 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> System.IntPtr, byte[],
> Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
>
> can any one help me with this problem.
> Thanks
>|||Ok Thanks.i will try it.
Thanks
Vamsi
Amarnath wrote:
> Try opening your reference and see any problems otherwise remove the
> reference and search for the reference and add it again..
> Amarnath
> "vamsi" wrote:
> > Hi All
> > i wanted to implement foms authentication to RS2005.i am
> > using the MSDN article to implement the forms authentication.when build
> > the project i am getting this error.
> >
> >
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > not implement interface member
> > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > System.IntPtr, byte[],
> > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> >
> >
> >
> > can any one help me with this problem.
> >
> > Thanks
> >
> >|||everything looks good.i added microsoft.reporting.interfaces and web
services and named as my machine name.still getting the error at the
authorization .
Thanks
vamsi wrote:
> Ok Thanks.i will try it.
> Thanks
> Vamsi
> Amarnath wrote:
> > Try opening your reference and see any problems otherwise remove the
> > reference and search for the reference and add it again..
> >
> > Amarnath
> >
> > "vamsi" wrote:
> >
> > > Hi All
> > > i wanted to implement foms authentication to RS2005.i am
> > > using the MSDN article to implement the forms authentication.when build
> > > the project i am getting this error.
> > >
> > >
> > > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > > not implement interface member
> > > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > > System.IntPtr, byte[],
> > > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > > Files\Microsoft SQL Server\MSSQL\Reporting
> > > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> > >
> > >
> > >
> > > can any one help me with this problem.
> > >
> > > Thanks
> > >
> > >|||Hi,
apparently the samplel code is not complete for the current version, I faced
the same problem.
The issue is that the code supplied does not include 2 overloads of the
CheckAccess method.
To resolve include the following in Authorisation.cs:
// Overload for a Model operation
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
ModelOperation modelOperation)
{
// If the user is the administrator, allow unrestricted access.
if (0 == String.Compare(userName, m_adminUserName, true,
CultureInfo.CurrentCulture))
return true;
if (!CheckAccess(userName, userToken, secDesc, modelOperation))
return false;
else
return true;
}
// Overload for a ModelItem operation
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
ModelItemOperation modelItemOperation)
{
// If the user is the administrator, allow unrestricted access.
if (0 == String.Compare(userName, m_adminUserName, true,
CultureInfo.CurrentCulture))
return true;
if (!CheckAccess(userName, userToken, secDesc, modelItemOperation))
return false;
else
return true;
}
This will allow the code to compile.
Now if only it didnt give me an 'Invalid Namespace' Error when I try to run
it... :(
Please lt me know if it works for you!
"vamsi" wrote:
> everything looks good.i added microsoft.reporting.interfaces and web
> services and named as my machine name.still getting the error at the
> authorization .
> Thanks
> vamsi wrote:
> > Ok Thanks.i will try it.
> >
> > Thanks
> > Vamsi
> > Amarnath wrote:
> > > Try opening your reference and see any problems otherwise remove the
> > > reference and search for the reference and add it again..
> > >
> > > Amarnath
> > >
> > > "vamsi" wrote:
> > >
> > > > Hi All
> > > > i wanted to implement foms authentication to RS2005.i am
> > > > using the MSDN article to implement the forms authentication.when build
> > > > the project i am getting this error.
> > > >
> > > >
> > > > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > > > not implement interface member
> > > > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > > > System.IntPtr, byte[],
> > > > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > > > Files\Microsoft SQL Server\MSSQL\Reporting
> > > > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> > > >
> > > >
> > > >
> > > > can any one help me with this problem.
> > > >
> > > > Thanks
> > > >
> > > >
>|||Update:
The problem is that you are using the Report Services 2000 sample on a 2005
server. You should download the 2005 sample from here:
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
hope this helps :-)
"vamsi" wrote:
> Hi All
> i wanted to implement foms authentication to RS2005.i am
> using the MSDN article to implement the forms authentication.when build
> the project i am getting this error.
>
> 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> not implement interface member
> 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> System.IntPtr, byte[],
> Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
>
> can any one help me with this problem.
> Thanks
>|||Hi i downloaded new article from the same page just a week back and it
worked.it is able to authenticate.But i am having little bit doubt on
the member ship role authorization.
where we have to design all the tables for the roles that each
registered user supposed to get.Like i want some users to get the
subscription screated and i dont want for some of the users to do this
and they can just view the reports.
Thanks
Vamsi
> Update:
> The problem is that you are using the Report Services 2000 sample on a 2005
> server. You should download the 2005 sample from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
> hope this helps :-)
> "vamsi" wrote:
> > Hi All
> > i wanted to implement foms authentication to RS2005.i am
> > using the MSDN article to implement the forms authentication.when build
> > the project i am getting this error.
> >
> >
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > not implement interface member
> > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > System.IntPtr, byte[],
> > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> >
> >
> >
> > can any one help me with this problem.
> >
> > Thanks
> >
> >sql
i wanted to implement foms authentication to RS2005.i am
using the MSDN article to implement the forms authentication.when build
the project i am getting this error.
'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
not implement interface member
'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
System.IntPtr, byte[],
Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
can any one help me with this problem.
ThanksTry opening your reference and see any problems otherwise remove the
reference and search for the reference and add it again..
Amarnath
"vamsi" wrote:
> Hi All
> i wanted to implement foms authentication to RS2005.i am
> using the MSDN article to implement the forms authentication.when build
> the project i am getting this error.
>
> 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> not implement interface member
> 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> System.IntPtr, byte[],
> Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
>
> can any one help me with this problem.
> Thanks
>|||Ok Thanks.i will try it.
Thanks
Vamsi
Amarnath wrote:
> Try opening your reference and see any problems otherwise remove the
> reference and search for the reference and add it again..
> Amarnath
> "vamsi" wrote:
> > Hi All
> > i wanted to implement foms authentication to RS2005.i am
> > using the MSDN article to implement the forms authentication.when build
> > the project i am getting this error.
> >
> >
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > not implement interface member
> > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > System.IntPtr, byte[],
> > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> >
> >
> >
> > can any one help me with this problem.
> >
> > Thanks
> >
> >|||everything looks good.i added microsoft.reporting.interfaces and web
services and named as my machine name.still getting the error at the
authorization .
Thanks
vamsi wrote:
> Ok Thanks.i will try it.
> Thanks
> Vamsi
> Amarnath wrote:
> > Try opening your reference and see any problems otherwise remove the
> > reference and search for the reference and add it again..
> >
> > Amarnath
> >
> > "vamsi" wrote:
> >
> > > Hi All
> > > i wanted to implement foms authentication to RS2005.i am
> > > using the MSDN article to implement the forms authentication.when build
> > > the project i am getting this error.
> > >
> > >
> > > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > > not implement interface member
> > > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > > System.IntPtr, byte[],
> > > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > > Files\Microsoft SQL Server\MSSQL\Reporting
> > > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> > >
> > >
> > >
> > > can any one help me with this problem.
> > >
> > > Thanks
> > >
> > >|||Hi,
apparently the samplel code is not complete for the current version, I faced
the same problem.
The issue is that the code supplied does not include 2 overloads of the
CheckAccess method.
To resolve include the following in Authorisation.cs:
// Overload for a Model operation
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
ModelOperation modelOperation)
{
// If the user is the administrator, allow unrestricted access.
if (0 == String.Compare(userName, m_adminUserName, true,
CultureInfo.CurrentCulture))
return true;
if (!CheckAccess(userName, userToken, secDesc, modelOperation))
return false;
else
return true;
}
// Overload for a ModelItem operation
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
ModelItemOperation modelItemOperation)
{
// If the user is the administrator, allow unrestricted access.
if (0 == String.Compare(userName, m_adminUserName, true,
CultureInfo.CurrentCulture))
return true;
if (!CheckAccess(userName, userToken, secDesc, modelItemOperation))
return false;
else
return true;
}
This will allow the code to compile.
Now if only it didnt give me an 'Invalid Namespace' Error when I try to run
it... :(
Please lt me know if it works for you!
"vamsi" wrote:
> everything looks good.i added microsoft.reporting.interfaces and web
> services and named as my machine name.still getting the error at the
> authorization .
> Thanks
> vamsi wrote:
> > Ok Thanks.i will try it.
> >
> > Thanks
> > Vamsi
> > Amarnath wrote:
> > > Try opening your reference and see any problems otherwise remove the
> > > reference and search for the reference and add it again..
> > >
> > > Amarnath
> > >
> > > "vamsi" wrote:
> > >
> > > > Hi All
> > > > i wanted to implement foms authentication to RS2005.i am
> > > > using the MSDN article to implement the forms authentication.when build
> > > > the project i am getting this error.
> > > >
> > > >
> > > > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > > > not implement interface member
> > > > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > > > System.IntPtr, byte[],
> > > > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > > > Files\Microsoft SQL Server\MSSQL\Reporting
> > > > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> > > >
> > > >
> > > >
> > > > can any one help me with this problem.
> > > >
> > > > Thanks
> > > >
> > > >
>|||Update:
The problem is that you are using the Report Services 2000 sample on a 2005
server. You should download the 2005 sample from here:
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
hope this helps :-)
"vamsi" wrote:
> Hi All
> i wanted to implement foms authentication to RS2005.i am
> using the MSDN article to implement the forms authentication.when build
> the project i am getting this error.
>
> 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> not implement interface member
> 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> System.IntPtr, byte[],
> Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
>
> can any one help me with this problem.
> Thanks
>|||Hi i downloaded new article from the same page just a week back and it
worked.it is able to authenticate.But i am having little bit doubt on
the member ship role authorization.
where we have to design all the tables for the roles that each
registered user supposed to get.Like i want some users to get the
subscription screated and i dont want for some of the users to do this
and they can just view the reports.
Thanks
Vamsi
> Update:
> The problem is that you are using the Report Services 2000 sample on a 2005
> server. You should download the 2005 sample from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
> hope this helps :-)
> "vamsi" wrote:
> > Hi All
> > i wanted to implement foms authentication to RS2005.i am
> > using the MSDN article to implement the forms authentication.when build
> > the project i am getting this error.
> >
> >
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > not implement interface member
> > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > System.IntPtr, byte[],
> > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> >
> >
> >
> > can any one help me with this problem.
> >
> > Thanks
> >
> >sql
Wednesday, March 21, 2012
Postcode radius searching
Hi - I know this isn't really a specific SQL Server question but I
can't think of a better forum.
can't think of a better forum.
I want to implement a more sophisticated UK postcode search on a site
- "LIKE 'NW%'" etc, is not bringing back good enough results.
A feature that would allow me to ask: "give me all outward postcodes
in a 30 mile radius of NW10" would be ideal.
Has anyone had to do this before? - any advice on how much
appreciated. Some of the postcode products available don't really
seem to offer this feature.
NHThanks for the links - I'll follow them up.
NH|||Nazir wrote:
> Hi - I know this isn't really a specific SQL Server question but I
> can't think of a better forum.
> I want to implement a more sophisticated UK postcode search on a site
> - "LIKE 'NW%'" etc, is not bringing back good enough results.
> A feature that would allow me to ask: "give me all outward postcodes
> in a 30 mile radius of NW10" would be ideal.
> Has anyone had to do this before? - any advice on how much
> appreciated. Some of the postcode products available don't really
> seem to offer this feature.
There is a system called Address Point which associates a OS grid ref with
each Delivery point (full post code + street number). This is, I suppose,
how MultiMap works. Whether you could adapt this system just to deal with
the outward portion of the postcode I am not sure.
Kennedy
Postal Codes 10001-10005 as a parameter
Dear all
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community 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.
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community 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.
Subscribe to:
Posts (Atom)