j2ee -jdbc driver error

sir,
kindly solve the error.When I do a database updation in the
ejbcreate function the driver is working properly but when
I tried to use a user defined function I am thrown an the
exception listed below.This means that teh classpath
settings are fine .Probably the access writes are not
defined properly so I am posting you server.policy file
also just in case there is some problem.
=20
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
for JDBC]Can't start a
cloned connection while in manual transaction mode.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
Source
)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
Source)
at
com.microsoft.jdbc.base.BaseConnection.getImplConn ection(Unknown
Sour
ce)
at
com.microsoft.jdbc.base.BaseStatement.setupImplCon nection(Unknown
Sou
rce)
at com.microsoft.jdbc.base.BaseStatement.<init>(Unkno wn Source)
at
com.microsoft.jdbc.base.BasePreparedStatement.<ini t>(Unknown
Source)
at
com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
Sourc
e)
at
com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
Sourc
e)
at
com.sun.enterprise.resource.JdbcXAConnection$JdbcC onnection.prepareSt
atement(JdbcXAConnection.java:263)
at Loginsessionbean.checklogin(Loginsessionbean.java: 36)
at
Loginsessionbean_EJBObjectImpl.checklogin(Loginses sionbean_EJBObjectI
mpl.java:19)
at _Loginsessionbean_EJBObjectImpl_Tie._invoke(Unknow n Source)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatchToServant(Ge
nericPOAServerSC.java:423)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.i nternalDispatch(Gen
ericPOAServerSC.java:137)
at
com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatch(GenericPOAS
erverSC.java:98)
at com.sun.corba.ee.internal.iiop.ORB.process(ORB.jav a:227)
at
com.sun.corba.ee.internal.iiop.LocalClientRequestI mpl.invoke(LocalCli
entRequestImpl.java:90)
at
com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
entSC.java:142)
at
com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
entSC.java:183)
at
org.omg.CORBA.portable.ObjectImpl._invoke(ObjectIm pl.java:297)
at _Loginsessionremote_Stub.checklogin(Unknown Source)
at Loginbean.getLogin(Loginbean.java:34)
at
javaproject.login._0005cjavaproject_0005clogin_000 5clogin_0002ejsplog
in_jsp_1._jspService(_0005cjavaproject_0005clogin_ 0005clogin_0002ejsplogi=
n_jsp_1
..java:140)
at
org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:126)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
at
org.apache.jasper.runtime.JspServlet$JspServletWra pper.service(JspSer
vlet.java:161)
at
org.apache.jasper.runtime.JspServlet.serviceJspFil e(JspServlet.java:2
47)
at
org.apache.jasper.runtime.JspServlet.service(JspSe rvlet.java:352)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
at
org.apache.tomcat.core.ServiceInvocationHandler.me thod(ServletWrapper
..java:626)
at
org.apache.tomcat.core.ServletWrapper.handleInvoca tion(ServletWrapper
..java:534)
at
org.apache.tomcat.core.ServletWrapper.handleReques t(ServletWrapper.ja
va:378)
at
org.apache.tomcat.core.Context.handleRequest(Conte xt.java:644)
at
org.apache.tomcat.core.ContextManager.service(Cont extManager.java:440
)
at
org.apache.tomcat.service.http.HttpConnectionHandl er.processConnectio
n(HttpConnectionHandler.java:144)
at
org.apache.tomcat.service.TcpConnectionThread.run( TcpEndpoint.java:31
0)
at java.lang.Thread.run(Thread.java:484)
Loginsessionbean.java
import javax.ejb.*;
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import javax.transaction.*;
import java.rmi.RemoteException;
public class Loginsessionbean implements SessionBean
{
private Connection con;
public void ejbCreate() throws CreateException ,
RemoteException ,SQLException , NamingException
{
try
{
InitialContext ic =3D new InitialContext();
DataSource ds =3D
(DataSource)ic.lookup("java:comp/env/jdbc/master");
con =3D ds.getConnection();
}
catch(Exception ex)
{
throw new CreateException(ex.getMessage());
}
}
public String checklogin(String login,String password)
throws RemoteException , SQLException , NamingException ,
CreateException
{
String selectstatement=3D"select * from table1 where login =3D?
and password =3D ?";
ResultSet result;
try
{
PreparedStatement prep =3D con.prepareStatement(selectstatement);
prep.setString(1,login);
prep.setString(2,password);
result =3D prep.executeQuery();
if(result.next()=3D=3D true)
{
prep.close();
return(null);
}
}
catch(SQLException ex)
{
ex.printStackTrace();
}
return "login is correct";
}
public void ejbRemove(){}
public void ejbActivate(){}
public void ejbPassivate(){}
public void setSessionContext(SessionContext session){}
}
server.policy
// Standard extensions get all permissions by default
grant codeBase "file:${java.home}/lib/ext/-" {
permission java.security.AllPermission;
};
grant codeBase "file:${java.home}/../lib/tools.jar" {
permission java.security.AllPermission;
};
grant codeBase "file:${com.sun.enterprise.home}/lib/classes/" {
permission java.security.AllPermission;
};
// Drivers and other system classes should be stored in this
// code base.
grant codeBase "file:${com.sun.enterprise.home}/lib/system/-" {
permission java.security.AllPermission;
};
grant codeBase
"file:${com.sun.enterprise.home}/public_html/-" {
permission java.lang.RuntimePermission "loadLibrary.*";
permission java.lang.RuntimePermission
"accessClassInPackage.*";
permission java.lang.RuntimePermission "queuePrintJob";
permission java.lang.RuntimePermission "modifyThreadGroup";
permission java.io.FilePermission "<<ALL FILES>>",
"read,write";
permission java.net.SocketPermission "*", "connect";
// "standard" properies that can be read by anyone
permission java.util.PropertyPermission "*", "read";
// set the JSSE provider for lazy authentication of app.
clients.
permission java.security.SecurityPermission
"putProviderProperty.JSSE";
permission java.security.SecurityPermission
"insertProvider.JSSE";
};
grant codeBase "file:${com.sun.enterprise.home}/lib/j2ee.jar" {
permission java.security.AllPermission;
};
// default permissions granted to all domains
grant {
permission java.lang.RuntimePermission "queuePrintJob";
// Additional properties needed RI...
permission java.io.FilePermission "*", "read";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}-", "read";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}reposi tory${file.separator}-"=
,
"read,write,delete";
permission java.io.FilePermission
"${com.sun.enterprise.home}${file.separator}logs${ file.separator}-",
"read,write,delete";
permission java.io.FilePermission
"${java.io.tmpdir}${file.separator}-", "read,write,delete";
permission java.io.FilePermission
"${user.home}${file.separator}-", "read,write,delete";
// allows anyone to listen on un-privileged ports
permission java.net.SocketPermission "*:0-65535", "connect";
// "standard" properies that can be read by anyone
permission java.util.PropertyPermission "*", "read";
// A version of Merant driver needs this permission.
// permission java.io.FilePermission "<<ALL FILES>>", "read";
// permission java.lang.RuntimePermission "modifyThreadGroup";
};
// permissions granted to all domains
grant {
permission java.lang.RuntimePermission "modifyThread";
permission java.lang.RuntimePermission "modifyThreadGroup";
// DataSource access
permission java.io.FilePermission "<<ALL FILES>>","read,write";
permission java.util.PropertyPermission "java.naming.*",
"read,write";
// Adjust the server host specification for your environment
permission java.net.SocketPermission
"*.microsoft.com:0-65535", "connect";
};
rajat agrawal wrote:

> sir,
> kindly solve the error.When I do a database updation in the
> ejbcreate function the driver is working properly but when
> I tried to use a user defined function I am thrown an the
> exception listed below.This means that teh classpath
> settings are fine .Probably the access writes are not
> defined properly so I am posting you server.policy file
> also just in case there is some problem.
>
Hi. Add a property to the jdbc connection request: selectMethod=cursor
Joe Weinstein at BEA

>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
> for JDBC]Can't start a
> cloned connection while in manual transaction mode.
> at
> com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source
> )
> at
> com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseConnection.getImplConn ection(Unknown
> Sour
> ce)
> at
> com.microsoft.jdbc.base.BaseStatement.setupImplCon nection(Unknown
> Sou
> rce)
> at com.microsoft.jdbc.base.BaseStatement.<init>(Unkno wn Source)
> at
> com.microsoft.jdbc.base.BasePreparedStatement.<ini t>(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
> Sourc
> e)
> at
> com.microsoft.jdbc.base.BaseConnection.prepareStat ement(Unknown
> Sourc
> e)
> at
> com.sun.enterprise.resource.JdbcXAConnection$JdbcC onnection.prepareSt
> atement(JdbcXAConnection.java:263)
> at Loginsessionbean.checklogin(Loginsessionbean.java: 36)
> at
> Loginsessionbean_EJBObjectImpl.checklogin(Loginses sionbean_EJBObjectI
> mpl.java:19)
> at _Loginsessionbean_EJBObjectImpl_Tie._invoke(Unknow n Source)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatchToServant(Ge
> nericPOAServerSC.java:423)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.i nternalDispatch(Gen
> ericPOAServerSC.java:137)
> at
> com.sun.corba.ee.internal.POA.GenericPOAServerSC.d ispatch(GenericPOAS
> erverSC.java:98)
> at com.sun.corba.ee.internal.iiop.ORB.process(ORB.jav a:227)
> at
> com.sun.corba.ee.internal.iiop.LocalClientRequestI mpl.invoke(LocalCli
> entRequestImpl.java:90)
> at
> com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
> entSC.java:142)
> at
> com.sun.corba.ee.internal.POA.GenericPOAClientSC.i nvoke(GenericPOACli
> entSC.java:183)
> at
> org.omg.CORBA.portable.ObjectImpl._invoke(ObjectIm pl.java:297)
> at _Loginsessionremote_Stub.checklogin(Unknown Source)
> at Loginbean.getLogin(Loginbean.java:34)
> at
> javaproject.login._0005cjavaproject_0005clogin_000 5clogin_0002ejsplog
> in_jsp_1._jspService(_0005cjavaproject_0005clogin_ 0005clogin_0002ejsplogin_jsp_1
> .java:140)
> at
> org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:126)
> at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
> at
> org.apache.jasper.runtime.JspServlet$JspServletWra pper.service(JspSer
> vlet.java:161)
> at
> org.apache.jasper.runtime.JspServlet.serviceJspFil e(JspServlet.java:2
> 47)
> at
> org.apache.jasper.runtime.JspServlet.service(JspSe rvlet.java:352)
> at javax.servlet.http.HttpServlet.service(HttpServlet .java:865)
> at
> org.apache.tomcat.core.ServiceInvocationHandler.me thod(ServletWrapper
> .java:626)
> at
> org.apache.tomcat.core.ServletWrapper.handleInvoca tion(ServletWrapper
> .java:534)
> at
> org.apache.tomcat.core.ServletWrapper.handleReques t(ServletWrapper.ja
> va:378)
> at
> org.apache.tomcat.core.Context.handleRequest(Conte xt.java:644)
> at
> org.apache.tomcat.core.ContextManager.service(Cont extManager.java:440
> )
> at
> org.apache.tomcat.service.http.HttpConnectionHandl er.processConnectio
> n(HttpConnectionHandler.java:144)
> at
> org.apache.tomcat.service.TcpConnectionThread.run( TcpEndpoint.java:31
> 0)
> at java.lang.Thread.run(Thread.java:484)
>
> Loginsessionbean.java
> import javax.ejb.*;
> import java.util.*;
> import java.sql.*;
> import javax.sql.*;
> import javax.naming.*;
> import javax.transaction.*;
> import java.rmi.RemoteException;
>
> public class Loginsessionbean implements SessionBean
> {
> private Connection con;
> public void ejbCreate() throws CreateException ,
> RemoteException ,SQLException , NamingException
> {
> try
> {
> InitialContext ic = new InitialContext();
> DataSource ds =
> (DataSource)ic.lookup("java:comp/env/jdbc/master");
> con = ds.getConnection();
> }
> catch(Exception ex)
> {
> throw new CreateException(ex.getMessage());
> }
> }
> public String checklogin(String login,String password)
> throws RemoteException , SQLException , NamingException ,
> CreateException
> {
> String selectstatement="select * from table1 where login =?
> and password = ?";
> ResultSet result;
> try
> {
> PreparedStatement prep = con.prepareStatement(selectstatement);
> prep.setString(1,login);
> prep.setString(2,password);
> result = prep.executeQuery();
> if(result.next()== true)
> {
> prep.close();
> return(null);
> }
> }
> catch(SQLException ex)
> {
> ex.printStackTrace();
> }
> return "login is correct";
> }
> public void ejbRemove(){}
> public void ejbActivate(){}
> public void ejbPassivate(){}
> public void setSessionContext(SessionContext session){}
> }
>
> server.policy
>
> // Standard extensions get all permissions by default
> grant codeBase "file:${java.home}/lib/ext/-" {
> permission java.security.AllPermission;
> };
> grant codeBase "file:${java.home}/../lib/tools.jar" {
> permission java.security.AllPermission;
> };
> grant codeBase "file:${com.sun.enterprise.home}/lib/classes/" {
> permission java.security.AllPermission;
> };
> // Drivers and other system classes should be stored in this
> // code base.
> grant codeBase "file:${com.sun.enterprise.home}/lib/system/-" {
> permission java.security.AllPermission;
> };
> grant codeBase
> "file:${com.sun.enterprise.home}/public_html/-" {
> permission java.lang.RuntimePermission "loadLibrary.*";
> permission java.lang.RuntimePermission
> "accessClassInPackage.*";
> permission java.lang.RuntimePermission "queuePrintJob";
> permission java.lang.RuntimePermission "modifyThreadGroup";
> permission java.io.FilePermission "<<ALL FILES>>",
> "read,write";
> permission java.net.SocketPermission "*", "connect";
> // "standard" properies that can be read by anyone
> permission java.util.PropertyPermission "*", "read";
> // set the JSSE provider for lazy authentication of app.
> clients.
> permission java.security.SecurityPermission
> "putProviderProperty.JSSE";
> permission java.security.SecurityPermission
> "insertProvider.JSSE";
> };
> grant codeBase "file:${com.sun.enterprise.home}/lib/j2ee.jar" {
> permission java.security.AllPermission;
> };
> // default permissions granted to all domains
> grant {
> permission java.lang.RuntimePermission "queuePrintJob";
> // Additional properties needed RI...
> permission java.io.FilePermission "*", "read";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}-", "read";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}reposi tory${file.separator}-",
> "read,write,delete";
> permission java.io.FilePermission
> "${com.sun.enterprise.home}${file.separator}logs${ file.separator}-",
> "read,write,delete";
> permission java.io.FilePermission
> "${java.io.tmpdir}${file.separator}-", "read,write,delete";
> permission java.io.FilePermission
> "${user.home}${file.separator}-", "read,write,delete";
> // allows anyone to listen on un-privileged ports
> permission java.net.SocketPermission "*:0-65535", "connect";
> // "standard" properies that can be read by anyone
> permission java.util.PropertyPermission "*", "read";
> // A version of Merant driver needs this permission.
> // permission java.io.FilePermission "<<ALL FILES>>", "read";
> // permission java.lang.RuntimePermission "modifyThreadGroup";
> };
> // permissions granted to all domains
> grant {
> permission java.lang.RuntimePermission "modifyThread";
> permission java.lang.RuntimePermission "modifyThreadGroup";
> // DataSource access
> permission java.io.FilePermission "<<ALL FILES>>","read,write";
> permission java.util.PropertyPermission "java.naming.*",
> "read,write";
> // Adjust the server host specification for your environment
> permission java.net.SocketPermission
> "*.microsoft.com:0-65535", "connect";
> };
|||Please see the below KB for detailed info:
313181 PRB: Cannot Start a Cloned Connection While in Manual Transaction
Mode
http://support.microsoft.com/?id=313181

Iterating through a recordset

I am a newly indoctrinated SQL Server 2005 user. So, forgive me if this
question is amatuer.
I am trying to create a stored proc that will read in a list of values
from one table (Possibly using a cte?) then iterate through each record
running another sp to populate a temp table and return all matching
records. The logic would be like the following:
///////
create procedure spSample1
@.column2 int
As
create table #tmptbl
(tmptblcol1 int not null)
with samplecte (var1 int)
As
(select column1 from table where column2 = @.column2)
begin
while not end-of-file
set localvar1 = current value of samplecte var1
insert into #tmptbl exec spSample2 localvar1
end
////////
This is a very rough outline. Hopefully it helps with clarification of
what I am trying to acheive. Any help would be greatly appreciated.
Thank you in advance!
rbrryankbrown@.gmail.com wrote:
> I am a newly indoctrinated SQL Server 2005 user. So, forgive me if this
> question is amatuer.
> I am trying to create a stored proc that will read in a list of values
> from one table (Possibly using a cte?) then iterate through each record
> running another sp to populate a temp table and return all matching
> records. The logic would be like the following:
> ///////
> create procedure spSample1
> @.column2 int
> As
> create table #tmptbl
> (tmptblcol1 int not null)
> with samplecte (var1 int)
> As
> (select column1 from table where column2 = @.column2)
> begin
> while not end-of-file
> set localvar1 = current value of samplecte var1
> insert into #tmptbl exec spSample2 localvar1
> end
> ////////
> This is a very rough outline. Hopefully it helps with clarification of
> what I am trying to acheive. Any help would be greatly appreciated.
> Thank you in advance!
> rbr
>
Something like the code below will do what you need, but I'd encourage
you to think "outside the box" and come up with a way to do this without
a temp table. Temp tables are usually unnecessary results of trying to
do "procedural" processes in a set-based environment like SQL.
DECLARE @.LoopVar
SELECT @.LoopVar = 0
SELECT TOP 1 @.LoopVar = <someincrementalID>
FROM table
WHERE <someincrementalID> > @.LoopVar
WHILE @.@.ROWCOUNT > 0
BEGIN
EXEC storedproc @.LoopVar
SELECT TOP 1 @.LoopVar = <someincrementalID>
FROM table
WHERE <someincrementalID> > @.LoopVar
END|||>> I am trying to create a stored proc that will read in a list of values from one tab
le (possibly using a CTE?) then iterate through each record [sic] running another sp t
o populate a temp table and return all matching records [sic]. The logic would be
l
ike the following: <<
You are still thinking of a file system - a magnetic tape file
system, to be more precise, doing a merge from a scratch tape.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
SQL is a declarative set-oriented language. We tend to write one
statement to do a job; if I want to insert data into a table, I write
something like:
INSERT INTO Foobar (a, b, c, ..)
SELECT x, y, z, ..
FROM Floob, Snarf, ..
WHERE ..;
One statement, no loops, no if-then control flow. You tell SQL
**what** you want, not **how** to do it. You want to write code as if
you were still in a 3GL procedural language. And I'll bet that your
schema is full of redundancies, too.
Nope, it was too vague to be usable for any concrete suggestions. But
it did demonstrate that you missed the very foundations of RDBMS. Get
a few books (insert shameless plug for my stuff here), clear out
everything you already know and start over. "To drink new tea, you
must first empty the old tea from your cup" - Zen proverb.|||Trust the CELKO grasshopper... He is a curmudgeon and will not hesitate to
tell you what he thinks about a subject but search on his previous posts and
you will learn. Hang around here and lurk. Search is your friend... Good
luck.
"ryankbrown@.gmail.com" wrote:

> I am a newly indoctrinated SQL Server 2005 user. So, forgive me if this
> question is amatuer.
> I am trying to create a stored proc that will read in a list of values
> from one table (Possibly using a cte?) then iterate through each record
> running another sp to populate a temp table and return all matching
> records. The logic would be like the following:
> ///////
> create procedure spSample1
> @.column2 int
> As
> create table #tmptbl
> (tmptblcol1 int not null)
> with samplecte (var1 int)
> As
> (select column1 from table where column2 = @.column2)
> begin
> while not end-of-file
> set localvar1 = current value of samplecte var1
> insert into #tmptbl exec spSample2 localvar1
> end
> ////////
> This is a very rough outline. Hopefully it helps with clarification of
> what I am trying to acheive. Any help would be greatly appreciated.
> Thank you in advance!
> rbr
>|||I trust that you are all correct. As I stated, I am new to this from a
much more procedural background. As most of us do when faced with a new
problem we fall back on what we know best.
I already wrote a simple sp that does the job just fine. I was asked by
a superior to find a way to do it reusing another pre-existing sp to
acheive the same result. This is what spawned my question.
Thanks for imparting your wisdom upon me!
rbr
StvJston wrote:
> Trust the CELKO grasshopper... He is a curmudgeon and will not hesitate to
> tell you what he thinks about a subject but search on his previous posts a
nd
> you will learn. Hang around here and lurk. Search is your friend... Goo
d
> luck.
> "ryankbrown@.gmail.com" wrote:
>|||Oh, and by the way...
All is fine with my db. No redundancies here. The only complaint i've
heard from my programmers is that it may be over-normalized.
But thanks for the unproductive insult anyway.
rbr
rbr wrote:
> I trust that you are all correct. As I stated, I am new to this from a
> much more procedural background. As most of us do when faced with a new
> problem we fall back on what we know best.
> I already wrote a simple sp that does the job just fine. I was asked by
> a superior to find a way to do it reusing another pre-existing sp to
> acheive the same result. This is what spawned my question.
> Thanks for imparting your wisdom upon me!
> rbr
> StvJston wrote:

Iterating

Hi,

I have a 6 different textboxes in my web application. I have 6 different tables in my database such as tbl1,tbl2,tbl3 etc.

When the user clicks the submit button I have to check whether the values in the textboxes match the value in the database. (if in txt1 the user enters 3 I need to go to tbl1 and check if there is such a value).

What is the most efficient way to perform such a check? Will I need to write 6 select statements or can I use a loop and if I can use a loop I would appreciate an example

Thanks

Why do you need to do this?

If you are loading the value into the textbox to begin with, and you simply want to check whether the text has changed, there is a text changed event raised by the textbox. If that doesn't work for you, you could always store the initial value of the textbox in a custom property when you populate the textbox:

myTextBox1.Attributes.Add('initValue',myValue)

Then simply check to see whether it has changed.

If you MUST check these against values from the database (i.e.; for data volatility reasons), you should retrieve all six from the db in a single SQL query, if possible. It would be more efficient...

|||

I need to do this coz the user enters a code in the textboxes and I need to check if such a code exists in the db and if there is no such a code then the user must select another code.

How can I retrieve all 6 values in a single query from 6 different tables?

Issues with privileges

I am having trouble with providing the minimum security to a user. After issuing the following:

GRANT EXECUTE ON SCHEMA :: DBO TO skillsnetuser;

I test the permissions with

exec as login = 'skillsnetuser'

exec prcElmtList 1, 1, 102268

revert;

and receive this message

Msg 229, Level 14, State 5, Line 2

SELECT permission denied on object 'Org', database 'SNAccess_Dev', schema 'dbo'.

The principal that owns the dbo schema is dbo and is the principle for all procedures and tables in that schema.

What can I do to shed some light on what is causing this access problem?

Are the objects 'Org' and the procedure prcElmList defined in the same database ?|||Yes, Org is a table that prcElmList accesses|||

My first guess is that ownership chaining is broken for some reason. Can you please include the portion of the SP body that access the table? We can take a look to it and try to determine why ownership chaining is broken in this case.

I would also like to suggest migrating to one of the other mechanisms we have available on SQL Server 2005 that allow controlled access to resources via a module (i.e. SP) execution:

* EXECUTE AS – For more information you can visit Context Switching in BOL (http://msdn2.microsoft.com/en-us/library/ms188268.aspx)

* module signing: Module signing in BOL (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

IF NOT EXISTS (SELECT

o.OrgID

FROM

Org o

WHERE

o.OrgID = @.piOrgID

AND o.Active = 1

AND o.BeginDate < GetDate()

AND (o.EndDate > GetDate() OR o.EndDate IS NULL) )

BEGIN

RAISERROR ('The passed OrgID is invalid or inactive.' ,17 ,1)

RETURN -2 -- OrgID not valid or active

END -- IF NOT EXISTS

|||

I tried a similar scenario, and it seems to work for me, and as your sample code doen’t involve any dynamic SQL I am not sure what I may be missing. Can you give us some more information?

I would like to ask you to run the following query:

SELECT name, principal_id, schema_id

FROM sys.objects WHERE

name LIKE 'prcElmtList'

OR name LIKE 'Org'

I would also like to ask you if you are using a database in backwards compatibility mode (and if so, what is the compatibility level you are using) and if you are using any options for the stored procedure declaration (such as NOT FOR REPLICATION, EXECUTE AS, etc.), and if there are more objects involved in the chain other than the SP and the table.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

SELECT name, principal_id, schema_id

FROM sys.objects WHERE

name LIKE 'prcElmtList'

OR name LIKE 'Org'

--Results

name principal_id schema_id

Org NULL 1

prcElmtList NULL 1

--For the procedure generation script

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

WITH RECOMPILE

--General Properties of the procedure showned by the GUI

Execute as caller

Schema dbo

System object False

ANSI NULLS True

Encrypted False

For replication False

Quoted identifier True

Recompile True

--Database Options showned by the GUI

Compatibility Level SQL Server 2005 (90)

All Miscellaneous Settings are False except Parameterization which is Simple

|||

At this junction I have added with execute as owner to the procedures,

this provides the functionality I was expecting to be present....

|||

Sorry for the late response, I have been trying to repro why ownership chaining didn’t worked for this particular scenario, I tried creating a small repro similar to the one you described, but so far I have no luck (my repro test is able to access the table as expected).

If you consider it is a bug in the product, I would recommend following the instructions described on the “Tips on using SQL Server Security forums” page to report it (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1). We will probably need more details than the ones we have right now to continue the investigation.

I am glad you were able to find a solution for the problem. Please, let us know if you have any further questions or feedback; we will greatly appreciate your feedback on the EXECUTE AS feature.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

ISSUE: After the SQL Server restart, Login failed for user 'XXX\Administrator'

Hello,
I've a problem, When I Execute the following COMMAND in a batch
command(.bat)
NET STOP MSSQLSERVER
NET START MSSQLSERVER
MY_DATABASE APPLICATION
I GOT Login failed for user 'XXX\Administrator' Exception, but when
after the SERVER started long time, MY_DATABASE APPLICATION will not
get the exception
My COnnection string is "Trusted_Connection=Yes;Integrated
security=SSPI;data source=(local);initial catalog=xxx"
Is there any help that can help me to get rid of it?
MSSQL SERVER 2005I've noticed that SQL Server 2005 takes longer to start. I suggest adding a
delay after starting SQL
Server and before starting your application. Or, modify your application cod
e so it has some
re-tries with some wait in between.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"zzppallas" <zzppallas@.gmail.com> wrote in message
news:1140253035.885476.318630@.g43g2000cwa.googlegroups.com...
> Hello,
> I've a problem, When I Execute the following COMMAND in a batch
> command(.bat)
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> MY_DATABASE APPLICATION
> I GOT Login failed for user 'XXX\Administrator' Exception, but when
> after the SERVER started long time, MY_DATABASE APPLICATION will not
> get the exception
> My COnnection string is "Trusted_Connection=Yes;Integrated
> security=SSPI;data source=(local);initial catalog=xxx"
> Is there any help that can help me to get rid of it?
> MSSQL SERVER 2005
>|||Actually this is a weird explanation. If you start the server, are you
getting at this point the exception or at connection time with your
client application. If you are getting the exception at service start,
it seems that your account is a) not prvililedged any more or b) the
password you enetered in the service manager has changed.
If you are getting the exception in the connection via a application
you have to make sure that the user you are connecting with has the
appropiate right on the database. per default administrator are
priviledged on the server, but they can be removed from the autorized
list.
HTH, jens Suessmeyer.

ISSUE: After the SQL Server restart, Login failed for user 'XXX\Administrator'

Hello,
I've a problem, When I Execute the following COMMAND in a batch
command(.bat)
NET STOP MSSQLSERVER
NET START MSSQLSERVER
MY_DATABASE APPLICATION
I GOT Login failed for user 'XXX\Administrator' Exception, but when
after the SERVER started long time, MY_DATABASE APPLICATION will not
get the exception
My COnnection string is "Trusted_Connection=Yes;Integrated
security=SSPI;data source=(local);initial catalog=xxx"
Is there any help that can help me to get rid of it?
MSSQL SERVER 2005I've noticed that SQL Server 2005 takes longer to start. I suggest adding a delay after starting SQL
Server and before starting your application. Or, modify your application code so it has some
re-tries with some wait in between.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"zzppallas" <zzppallas@.gmail.com> wrote in message
news:1140253035.885476.318630@.g43g2000cwa.googlegroups.com...
> Hello,
> I've a problem, When I Execute the following COMMAND in a batch
> command(.bat)
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> MY_DATABASE APPLICATION
> I GOT Login failed for user 'XXX\Administrator' Exception, but when
> after the SERVER started long time, MY_DATABASE APPLICATION will not
> get the exception
> My COnnection string is "Trusted_Connection=Yes;Integrated
> security=SSPI;data source=(local);initial catalog=xxx"
> Is there any help that can help me to get rid of it?
> MSSQL SERVER 2005
>|||Actually this is a weird explanation. If you start the server, are you
getting at this point the exception or at connection time with your
client application. If you are getting the exception at service start,
it seems that your account is a) not prvililedged any more or b) the
password you enetered in the service manager has changed.
If you are getting the exception in the connection via a application
you have to make sure that the user you are connecting with has the
appropiate right on the database. per default administrator are
priviledged on the server, but they can be removed from the autorized
list.
HTH, jens Suessmeyer.

Issue with SqlUserDefinedAggregate

I am using the code below but I am getting a "zero" result for
dbo.AggredIssue('Test') user defined aggregate everytime that the query
executes parallel processing and uses the "Merge" method. It seems that my
private variable "private List<string> myList" gets nullified everytime it
goes through the "Merge".
I saw other people reporting the same issue in other forums, but nobody was
able to provide a solution or explanation.
See below a simplified version of my code (posted just after the queries)
that replicates the issue.
The query below works because it does't process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund = 2
group by GroupID
The query below doesn't work because it process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund <= 20
group by GroupID
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
MaxByteSize = 1000)]
public class AggregIssue : IBinarySerialize {
private List<string> myList;
private int myResult;
public void Init() {
myList = new List<string>();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) { return; }
myList.Add(Value.ToString());
}
public void Merge(AggregIssue Other) {
if (Other.myList != null) {
if (myList == null) {
myList = Other.myList;
}
else {
myList.AddRange(Other.myList);
}
}
}
public SqlInt32 Terminate() {
return new SqlInt32(myResult);
}
public void Read(BinaryReader r) {
myResult = r.ReadInt32();
}
//The code below is simplified for posting in the forum.
//I do additional manipulation of the list and require
//the aggregation to be IBinarySerialize.
//But this code replicates the issue also
public void Write(BinaryWriter w) {
w.Write(myList.Count);
}
}"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:60C66368-EFD5-4C6B-A9EF-FC363A91C1DB@.microsoft.com...
>I am using the code below but I am getting a "zero" result for
> dbo.AggredIssue('Test') user defined aggregate everytime that the query
> executes parallel processing and uses the "Merge" method. It seems that my
> private variable "private List<string> myList" gets nullified everytime it
> goes through the "Merge".
> I saw other people reporting the same issue in other forums, but nobody
> was
> able to provide a solution or explanation.
> See below a simplified version of my code (posted just after the queries)
> that replicates the issue.
> The query below works because it does't process the query in parallel.
> SELECT GroupID, dbo.AggregIssue('Test')
> FROM MyTable
> where fund = 2
> group by GroupID
>
> The query below doesn't work because it process the query in parallel.
>
Yikes! How on earth do you test the merge method of a CLR Aggregate?
Perhaps you could cook up an appropriate plan guide?
David|||Hello Fernando,
F> I am using the code below but I am getting a "zero" result for
F> dbo.AggredIssue('Test') user defined aggregate everytime that the
F> query executes parallel processing and uses the "Merge" method. It
F> seems that my private variable "private List<string> myList" gets
F> nullified everytime it goes through the "Merge".
I don't believe BinaryRead and BinaryWrite method isn't preserving your List
<T>
as you expect it does. Here's example that serializes such a between calls
to merge.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSVBuilder : IBinarySerialize, INullable
{
List<string> _list = null;
public void Init()
{
_list = new List<string>();
}
public void Accumulate(SqlString Value)
{
_list.Add(Value.Value);
}
public void Merge(CSVBuilder Group)
{
_list.AddRange(Group._list);
}
public SqlString Terminate()
{
StringBuilder sb = new StringBuilder(8000);
foreach (string item in _list) {
sb.Append(", ");
sb.Append(item);
}
return new SqlString(sb.ToString().Substring(2));
}
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
int size = r.ReadInt32();
BinaryFormatter f = new BinaryFormatter();
_list = (List<string> )(f.Deserialize(new MemoryStream(r.ReadBytes(size))));
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
BinaryFormatter f = new BinaryFormatter();
MemoryStream ms = new MemoryStream();
f.Serialize(ms,_list);
Int32 size = (Int32)ms.Length;
w.Write(size);
w.Write(ms.ToArray(), 0, (int)size);
}
bool INullable.IsNull
{
get { return _list == null; }
}
}
It seems to work with this.
USE scratch
go
create table dbo.vs(v varchar(50));
insert into dbo.vs values ('Alpha')
insert into dbo.vs values ('Bravo')
insert into dbo.vs values ('Charlie')
insert into dbo.vs values ('Delta')
insert into dbo.vs values ('Echo')
insert into dbo.vs values ('Foxtrot')
insert into dbo.vs values ('Golf')
insert into dbo.vs values ('Hotel')
insert into dbo.vs values ('India')
insert into dbo.vs values ('Juliet')
insert into dbo.vs values ('Kilo')
insert into dbo.vs values ('Lima')
insert into dbo.vs values ('Mike')
insert into dbo.vs values ('November')
insert into dbo.vs values ('Oscar')
insert into dbo.vs values ('Papa')
insert into dbo.vs values ('Quebec')
insert into dbo.vs values ('Romeo')
insert into dbo.vs values ('Sierra')
insert into dbo.vs values ('Tango')
insert into dbo.vs values ('Uniform')
insert into dbo.vs values ('Victor')
insert into dbo.vs values ('Whiskey')
insert into dbo.vs values ('Yankee')
insert into dbo.vs values ('Zulu')
go
select dbo.csvbuilder(v) from dbo.vs
go
drop table dbo.vs
go
Returns:
Alpha, Bravo, Charlie, Delta, Echo, Foxtrot, Golf, Hotel, India, Juliet,
Kilo, Lima, Mike, November, Oscar, Papa, Quebec, Romeo, Sierra, Tango, Unifo
rm,
Victor, Whiskey, Yankee, Zulu
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thank you very much for your response. The reason I don't serialize the list
itself is because the list exceeds the limit of 8000 for maxbytesize.
I need the whole list to process the resolution of a non-linear equation, so
I cannot pre-aggregate values to save space before serializing. So what I am
trying to do is to solve the equation (using the required data from the list
)
just before serialization.
The weird thing is that it works if the code doesn't go through the merge
method.
If you can think of some other alternative...
Thank you very much,
Fernando|||"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
> Hi Kent,
> Thank you very much for your response. The reason I don't serialize the
> list
> itself is because the list exceeds the limit of 8000 for maxbytesize.
> I need the whole list to process the resolution of a non-linear equation,
> so
> I cannot pre-aggregate values to save space before serializing. So what I
> am
> trying to do is to solve the equation (using the required data from the
> list)
> just before serialization.
> The weird thing is that it works if the code doesn't go through the merge
> method.
> If you can think of some other alternative...
>
As a workaround you can always prevent a parallel plan with a MAXDOP hint.
David|||David,
Thanks for your response. The option "MAXDOP 1" worked. It would be nicer if
parallelism is enabled, but for now it a good workaround.
Thanks again,
Fernando
"David Browne" wrote:

> "Fernando" <Fernando@.discussions.microsoft.com> wrote in message
> news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
>
> As a workaround you can always prevent a parallel plan with a MAXDOP hint.
> David
>
>|||Hello Fernando,
F> The weird thing is that it works if the code doesn't go through the
F> merge method.
Right, I suspect the that the merge method if actually building instances
of the UDA on different CPUs and when has to marshal them together (merging
the threads if you will), that's when it calls the serialization stuff and
that's when you lose your values.
F> If you can think of some other alternative...
Don't use a UDA, use a procedure or function if possible.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent!

> Hello Fernando,
> F> The weird thing is that it works if the code doesn't go through the
> F> merge method.
> Right, I suspect the that the merge method if actually building instances
> of the UDA on different CPUs and when has to marshal them together (mergin
g
> the threads if you will), that's when it calls the serialization stuff and
> that's when you lose your values.
You are probably right, and that would be the reason why my code is failing.

> F> If you can think of some other alternative...
> Don't use a UDA, use a procedure or function if possible.
The solution is much simpler with UDA as the List used in the aggregation is
dynamically populated based on the items in the "Group By" of the query.
The workaround provided by David Browne (force non-parallelism) will work
for now.
Thank you very much for your help!
Fernando

>

Issue with SqlUserDefinedAggregate

I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge".

I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation.

See below a simplified version of my code (posted just after the queries) that replicates the issue.

The query below works because it does't process the query in parallel.

SELECT GroupID, dbo.AggregIssue('Test')

FROM MyTable

where fund = 2

group by GroupID

The query below doesn't work because it process the query in parallel.

SELECT GroupID, dbo.AggregIssue('Test')

FROM MyTable

where fund <= 20

group by GroupID

[Serializable]

[SqlUserDefinedAggregate(

Format.UserDefined,

IsInvariantToNulls = true,

IsInvariantToDuplicates = false,

IsInvariantToOrder = true,

MaxByteSize = 1000)]

public class AggregIssue : IBinarySerialize {

private List<string> myList;

private int myResult;

public void Init() {

myList = new List<string>();

}

public void Accumulate(SqlString Value) {

if (Value.IsNull) { return; }

myList.Add(Value.ToString());

}

public void Merge(AggregIssue Other) {

if (Other.myList != null) {

if (myList == null) {

myList = Other.myList;

}

else {

myList.AddRange(Other.myList);

}

}

}

public SqlInt32 Terminate() {

return new SqlInt32(myResult);

}

public void Read(BinaryReader r) {

myResult = r.ReadInt32();

}

//The code below is simplified for posting in the forum.

//I do additional manipulation of the list and require

//the aggregation to be IBinarySerialize.

//But this code replicates the issue also

public void Write(BinaryWriter w) {

w.Write(myList.Count);

}

}

? If you want to maintain the list properly you should serialize/deserialize the list itself -- and not its count -- in the Read and Write methods. They may be called more than one during the course of aggregation. Your current code is probably failing because it's making assumptions about how and when these will be called. You should instead do something like: public void Write(BinaryWriter w) { w.Write(myList.Count); foreach (string theString in myList) w.Write(theString); } public void Read(BinaryReader r) { this.myList = new List<string>(); int numStrings = r.ReadInt32(); for (int i = 0; i<numStrings; i++) myList.Add(r.ReadString()); } ... Then, in your Terminate method: public SqlInt32 Terminate() { return new SqlInt32(myList.Count); } -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <FernandoT@.discussions..microsoft.com> wrote in message news:e4178255-6dd9-4b48-b5ae-eb072c88e314_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge". I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation. See below a simplified version of my code (posted just after the queries) that replicates the issue. The query below works because it does't process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund = 2 group by GroupID The query below doesn't work because it process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund <= 20 group by GroupID [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = true, MaxByteSize = 1000)] public class AggregIssue : IBinarySerialize { private List<string> myList; private int myResult; public void Init() { myList = new List<string>(); } public void Accumulate(SqlString Value) { if (Value.IsNull) { return; } myList.Add(Value.ToString()); } public void Merge(AggregIssue Other) { if (Other.myList != null) { if (myList == null) { myList = Other.myList; } else { myList.AddRange(Other.myList); } } } public SqlInt32 Terminate() { return new SqlInt32(myResult); } public void Read(BinaryReader r) { myResult = r.ReadInt32(); } //The code below is simplified for posting in the forum. //I do additional manipulation of the list and require //the aggregation to be IBinarySerialize. //But this code replicates the issue also public void Write(BinaryWriter w) { w.Write(myList.Count); } }|||

Hi Adam,

Thank you very much for your response.

The reason that I don't serialize the list itself is because of the MaxByteSize limitation of 8096. My list will easily go beyond that limitation. I test your solution and it works for a small list, but not for long ones.

I noticed that the list is loosing the information in the merge. If the query doesn't go through parallel threads, it works fine.

My assumption is that the serialization will happen before terminate and final output of aggregate value for each row of the result set.

Any other ideas?

Thanks!!

Fernando

|||Hi, Fernando,

I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.

About Merge() stuff, to my limited understanding:

If single thread (no parallel op):

Init() -> Accumulate() -> Terminate()

If multi threads (parallel plan):

T1: Init() -> Accumulate()
T2: Init() -> Accumulate() -> Merge( with T1)
T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()

This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.

In Adam's book Chapter 6, p. 195, I quote:

"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"

I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?

Regards,

Dong Xie
|||? Actually, that quote from the book is not quite accurate anymore -- when I wrote it against an earlier CTP it appeared to be true, but now it does not. I believe that it has been changed in a later printing of the book, but I'll check today with Apress to make sure. Thanks for pointing it out! Anyway, the correct phrasing at this point should be "the intermediate result can be serialized and deserialized up to one time per row of aggregated data" In other words, the SQL Server engine may not do it for every row (or even nearly that often in most cases), but you need to code your aggregate as if it will. There is not necessarily a direct/stated relationship between Merge and Read/Write, but it appears that when Merge is called the engine also does a round of serialization/deserialization. I'm not sure why, though. Hopefully Steven Hemingray or one of the other dataworks guys will show up in this thread and clarify! -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Dong Xie@.discussions.microsoft.com> wrote in message news:c5fafeee-262b-43a4-9295-10a7cb07d92a@.discussions.microsoft.com...Hi, Fernando,I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.About Merge() stuff, to my limited understanding:If single thread (no parallel op):Init() -> Accumulate() -> Terminate()If multi threads (parallel plan):T1: Init() -> Accumulate()T2: Init() -> Accumulate() -> Merge( with T1)T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.In Adam's book Chapter 6, p. 195, I quote:"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?Regards,Dong Xie|||

It seems that you are right and serialization/deserialization happens when merge is called.

For now I have a workaround that was suggested in another forum to use "MAXDOP=1" and it works as merge is never executed.

It is not ideal, as parallelism cannot be leveraged, but it is a workaround.

Thanks to all for the responses!!

Fernando

|||

There seems to be a few issues within the thread:

1) Why is the private field myList nullified?

Whenever serialization takes place (Read/Write), a new instance is instantiated and it is up to your serialization code to fill the instance. Since your Write() only sets myResult, myList will be remain on the default value of null.

2) Why is MaxByteSize not always enforced?

MaxByteSize is enforced during serialization. You could cause instances to become much larger than 8k over Accumulate() calls and then not serialize out 8k.

With this said, there should not be a reason to build UDAggs that become larger than 8k but do not serialize out 8k. Read/write should serialize all necessary information for the UDAggs.

The UDAgg code within this thread is a good example of a contrived case for this. Since Terminate() only returns the count, there is not a need to accumulate the actual strings, but Accumulate() could increment a counter instead.

3) When is Read/Write called?

Within SQL Server 2005, serialization takes place during Merge() and before Terminate() is called. If the UDAgg provided accessed the myList within Terminate(), you would see a NullReferenceException from within Terminate() (since Write() does not reconstruct myList and null is its default reference).

As pointed out within this thread, Read/Write is not called for every row. However, please write your UDAgg with proper serialization semantics as if it were called every row.

Hope that helps!

-Jason

|||

Jason,

Thanks for all your explanation. It makes sense to me now how all this work.

But I still have one issue, which is the limitation of 8K. The list that I am building cannot be contrived until it is complete just before "Terminate". In the example I am passing the count and I could do that in the merge also. But in my real case, I need the complete list to be used in the resolution of a non-linear equation, so I cannot do anything with it until I pass it to a iterative algorithm in order to solve the equation. But this list can grow bigger than 8K.

The only way to avoid so far is to limit the degree of parallelism to 1, so the code doesn't go to merge.

Any thoughts on this?

Thanks,

Fernando

|||

Fernando-

Merge is also called in some more complex queries (such as GROUP BY WITH CUBE).

This is an interesting workaround to the 8k limitation for your scenario. I do worry that your workaround leaves your UDAgg prone to internal changes within SQL Server causes serialization to be more frequently performed or eliminated. For this reason, I'd recommend always using an approach where instances created from Read/Write are no different than the original.

I cannot recommend using this approach for these reasons, but limiting DOP to 1 should eliminate Merge() calls for the most part. If you absolutely must use this workaround, I'd recommend safeguarding against your assumptions (Merge never called, serialization takes place once after all Accumulate calls but before Terminate):

Always throw an exception within your Merge() call so that in the case that Merge() is invoked, you'll know what the issue is and you can try to simplify the query so that Merge is not called.|||

Jason,

Thanks for all your help and explanations.

Fernando

Issue with restoring master db

Hi all,

I am trying to restore my master database to a new server in sql 2000. I have started sql in single user mode from the command line and connected to enterprise manager and attempted to do the restore. I also tried query analyzer and used the restore database from disk ='file path of backup' used the move option with the correct file path and on both counts received the same error message...

Error: 9001, Severity: 21, State: 1.
Error: 3151, Severity: 21, State: 0.

Any ideas? The connection is then broken (which I would expect anyway as restoring master shuts down sql but, I checked and it has not restored the db)

Thanks!This article may be of some help:

http://www.dbarecovery.com/restoremasterdb.html|||lmao @. your sig ... rdjabarov ... nice one !!!|||That is mostly what I have done (did not need to do the rebuild part) When I try to do the restore it does not complete but generates the error messages I listed in my first posting. I have done this before and it worked but, for some reason it is not allowing me to do the restore but, is breaking the connection.

Any other suggestions?|||I did some more checking - could there be an issue with one server having a windows service pack 3 and the other server having windows service pack 4?

Issue with ReadNext, ReadPrev

I have an issue with the following sequence.

1) User select all recordsby executing a SqlCeResultSet ( using like % )

2) User selects "Last" by triggering the rset.readlast()

3) User now selects "Next" by triggering the rset.readnext()

4) User now select "Prev" by triggering rset.readprevious()

...

nothing happens...the textboxes do not update their values.

However if the "Prev" button is pressed again ( a second time ) now the textboxes populates the prev record's data and the dataset nav goes well again...

This behaviour is very annoying...any help here?

Thanks in advance!

I would say it’s a bug in SQL CE as exception should’ve been thrown on attempt to read past last record. Please file a bug report on http://connect.microsoft.com/

To make sure your application still works after that is fixed (and to eliminate that effect with existing versions) make sure not to read past last record, e.g. disable"Next" button if user hits "Last" or reaches last record by hitting "Next". That is easy to determine – if Read()/ReadLast() returns false then it’s the last record and “Next” should be disabled. Enable it as user moves back to valid records range.

|||

Ilya,

I've found a workaround in my project, without resoriting to enabled/disabled buttons.

I'll try to post the bug

Thanks for your coop.

Gus

|||

The bug 13334 in SQL Server CE has been filed to track this issue.

Hope the issue gets resolved soon.

Issue with ReadNext, ReadPrev

I have an issue with the following sequence.

1) User select all recordsby executing a SqlCeResultSet ( using like % )

2) User selects "Last" by triggering the rset.readlast()

3) User now selects "Next" by triggering the rset.readnext()

4) User now select "Prev" by triggering rset.readprevious()

...

nothing happens...the textboxes do not update their values.

However if the "Prev" button is pressed again ( a second time ) now the textboxes populates the prev record's data and the dataset nav goes well again...

This behaviour is very annoying...any help here?

Thanks in advance!

I would say it’s a bug in SQL CE as exception should’ve been thrown on attempt to read past last record. Please file a bug report on http://connect.microsoft.com/

To make sure your application still works after that is fixed (and to eliminate that effect with existing versions) make sure not to read past last record, e.g. disable"Next" button if user hits "Last" or reaches last record by hitting "Next". That is easy to determine – if Read()/ReadLast() returns false then it’s the last record and “Next” should be disabled. Enable it as user moves back to valid records range.

|||

Ilya,

I've found a workaround in my project, without resoriting to enabled/disabled buttons.

I'll try to post the bug

Thanks for your coop.

Gus

|||

The bug 13334 in SQL Server CE has been filed to track this issue.

Hope the issue gets resolved soon.

Issue with passing a parameter to Stored Procedure using IN keywor

Hello,
A program I've written creates a parameter to be passed to a stored
procedure based on a user's report selection.
A user can select one, two or three locations.
That parameter is used in an IN clause.
"ZMCC.WERKS IN (@.Locations) "
If a user requests a single location, it works just fine. However, when the
user makes multiple selection, the parameter fails.
I've built the parameter so it looks like " '0031', '0032', '0033' " and a
bunch of variations on this, but none of them work
How can I build the parameter and pass it to the stored procedure?
Thx.
Andy JacobsPlenty of suggestions here:
http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||http://www.aspfaq.com/2248
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||This one works for me
set @.StrType = ''''+ replace(@.StrType,',',''',''')+''''
Declare @.SQL varchar(5000)
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type],OrderNo AS [Order No],Fee from
#Confirm WHERE RptType IN(' + @.StrType + ')'
--print @.sql
Exec(@.SQL)
END
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||SQL is treating the passed variable as a single string, instead of
interpreting the string as a set.
What you will need to do in your stored procedure is parse the string and
insert the values into a temporary table. Then reference that temporary tabl
e
in your select statement with the IN clause.
See http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" wrote:

> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when th
e
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>

Issue in Domain User Accessing Report

Hello All,
I have created Reporting Services Report and I had given a user Browser
permissions to browse the report. But he is not able to get to the report.
The browser asks for userid and password and when typed it does not take it.
Only when I put the user in the local administrator group, the user is able
to see the report and all the reports.
I checked the app pool and it is running under network service and the
network service is in the local administrator group. Could anybody help?
Thanks!
--
SaravananOne thing you could try (and really is easier than adding user by user
browser permissions). Create a local group (I called mine Reports). Add all
users and groups of users to this report. Then add this group to the browser
role of Home. Everything inherits permissions from the Home directory.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
news:84A3066B-05BB-4360-8A8E-DCE0DFF6E50D@.microsoft.com...
> Hello All,
> I have created Reporting Services Report and I had given a user Browser
> permissions to browse the report. But he is not able to get to the report.
> The browser asks for userid and password and when typed it does not take
> it.
> Only when I put the user in the local administrator group, the user is
> able
> to see the report and all the reports.
> I checked the app pool and it is running under network service and the
> network service is in the local administrator group. Could anybody help?
> Thanks!
> --
> Saravanan|||Thanks Bruce for your response. I am starting to test with a user now and
that I would be putting the users in groups and assign permissions to the
group. My concern is that, whether the users needs to be on the local admin
group?. Am I missing anything?
--
Saravanan
"Bruce L-C [MVP]" wrote:
> One thing you could try (and really is easier than adding user by user
> browser permissions). Create a local group (I called mine Reports). Add all
> users and groups of users to this report. Then add this group to the browser
> role of Home. Everything inherits permissions from the Home directory.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> news:84A3066B-05BB-4360-8A8E-DCE0DFF6E50D@.microsoft.com...
> > Hello All,
> > I have created Reporting Services Report and I had given a user Browser
> > permissions to browse the report. But he is not able to get to the report.
> > The browser asks for userid and password and when typed it does not take
> > it.
> >
> > Only when I put the user in the local administrator group, the user is
> > able
> > to see the report and all the reports.
> >
> > I checked the app pool and it is running under network service and the
> > network service is in the local administrator group. Could anybody help?
> >
> > Thanks!
> > --
> > Saravanan
>
>|||No, definitely not. Do not put the user in the local admin group. I think
what you are missing is the process of adding to a role. Click on the home
directory, details, security (on the left panel). Then add a role and use
the new group that you have and give it browsing rights).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
news:5A61A957-3E8B-464D-8E0F-D1DD886ADA39@.microsoft.com...
> Thanks Bruce for your response. I am starting to test with a user now and
> that I would be putting the users in groups and assign permissions to the
> group. My concern is that, whether the users needs to be on the local
> admin
> group?. Am I missing anything?
> --
> Saravanan
>
> "Bruce L-C [MVP]" wrote:
>> One thing you could try (and really is easier than adding user by user
>> browser permissions). Create a local group (I called mine Reports). Add
>> all
>> users and groups of users to this report. Then add this group to the
>> browser
>> role of Home. Everything inherits permissions from the Home directory.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
>> news:84A3066B-05BB-4360-8A8E-DCE0DFF6E50D@.microsoft.com...
>> > Hello All,
>> > I have created Reporting Services Report and I had given a user Browser
>> > permissions to browse the report. But he is not able to get to the
>> > report.
>> > The browser asks for userid and password and when typed it does not
>> > take
>> > it.
>> >
>> > Only when I put the user in the local administrator group, the user is
>> > able
>> > to see the report and all the reports.
>> >
>> > I checked the app pool and it is running under network service and the
>> > network service is in the local administrator group. Could anybody
>> > help?
>> >
>> > Thanks!
>> > --
>> > Saravanan
>>|||Thanks for the patience to answer me Bruce.
Before adding into the local administrators group, I tried that option too.
I included the group onto the Browser Role on the Home of Reporting Services,
then created the folder and I selected assign specific rights to the folder
I uploaded the reports , went to the security tab of the folder and included
the group into the Browser Role. As it did not work, I included the group
into local admin.
Thanks!
--
Saravanan
"Bruce L-C [MVP]" wrote:
> No, definitely not. Do not put the user in the local admin group. I think
> what you are missing is the process of adding to a role. Click on the home
> directory, details, security (on the left panel). Then add a role and use
> the new group that you have and give it browsing rights).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> news:5A61A957-3E8B-464D-8E0F-D1DD886ADA39@.microsoft.com...
> > Thanks Bruce for your response. I am starting to test with a user now and
> > that I would be putting the users in groups and assign permissions to the
> > group. My concern is that, whether the users needs to be on the local
> > admin
> > group?. Am I missing anything?
> > --
> > Saravanan
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> One thing you could try (and really is easier than adding user by user
> >> browser permissions). Create a local group (I called mine Reports). Add
> >> all
> >> users and groups of users to this report. Then add this group to the
> >> browser
> >> role of Home. Everything inherits permissions from the Home directory.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> >> news:84A3066B-05BB-4360-8A8E-DCE0DFF6E50D@.microsoft.com...
> >> > Hello All,
> >> > I have created Reporting Services Report and I had given a user Browser
> >> > permissions to browse the report. But he is not able to get to the
> >> > report.
> >> > The browser asks for userid and password and when typed it does not
> >> > take
> >> > it.
> >> >
> >> > Only when I put the user in the local administrator group, the user is
> >> > able
> >> > to see the report and all the reports.
> >> >
> >> > I checked the app pool and it is running under network service and the
> >> > network service is in the local administrator group. Could anybody
> >> > help?
> >> >
> >> > Thanks!
> >> > --
> >> > Saravanan
> >>
> >>
> >>
>
>|||When you assigned specific rights to the folder you overwrote the inherited
rights to Home. All the folders inherit from Home. First, try and get this
working without doing anything to the new folder.
Do this:
1. Add a user to a local group
2. Can they see Home (even if you don't have any folders)
3. Add a folder
4. Can they see this folder?
5. Add a report to the folder.
Again, don't touch any of the rights on the folder you should have to. The
only time to do this is if you want them in some folders and not others. In
that case you can give browsing rights to the folder and not to home and
then a link that brings them directly to the folder in question.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
news:7FF767C5-2371-4CFB-BAD9-120049766893@.microsoft.com...
> Thanks for the patience to answer me Bruce.
> Before adding into the local administrators group, I tried that option
> too.
> I included the group onto the Browser Role on the Home of Reporting
> Services,
> then created the folder and I selected assign specific rights to the
> folder
> I uploaded the reports , went to the security tab of the folder and
> included
> the group into the Browser Role. As it did not work, I included the group
> into local admin.
> Thanks!
> --
> Saravanan
>
> "Bruce L-C [MVP]" wrote:
>> No, definitely not. Do not put the user in the local admin group. I think
>> what you are missing is the process of adding to a role. Click on the
>> home
>> directory, details, security (on the left panel). Then add a role and use
>> the new group that you have and give it browsing rights).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
>> news:5A61A957-3E8B-464D-8E0F-D1DD886ADA39@.microsoft.com...
>> > Thanks Bruce for your response. I am starting to test with a user now
>> > and
>> > that I would be putting the users in groups and assign permissions to
>> > the
>> > group. My concern is that, whether the users needs to be on the local
>> > admin
>> > group?. Am I missing anything?
>> > --
>> > Saravanan
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> One thing you could try (and really is easier than adding user by user
>> >> browser permissions). Create a local group (I called mine Reports).
>> >> Add
>> >> all
>> >> users and groups of users to this report. Then add this group to the
>> >> browser
>> >> role of Home. Everything inherits permissions from the Home directory.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
>> >> news:84A3066B-05BB-4360-8A8E-DCE0DFF6E50D@.microsoft.com...
>> >> > Hello All,
>> >> > I have created Reporting Services Report and I had given a user
>> >> > Browser
>> >> > permissions to browse the report. But he is not able to get to the
>> >> > report.
>> >> > The browser asks for userid and password and when typed it does not
>> >> > take
>> >> > it.
>> >> >
>> >> > Only when I put the user in the local administrator group, the user
>> >> > is
>> >> > able
>> >> > to see the report and all the reports.
>> >> >
>> >> > I checked the app pool and it is running under network service and
>> >> > the
>> >> > network service is in the local administrator group. Could anybody
>> >> > help?
>> >> >
>> >> > Thanks!
>> >> > --
>> >> > Saravanan
>> >>
>> >>
>> >>
>>|||Hi Bruce,
Thanks for the step by step instructions. This is what I did
1. Created a Usergroup named ReportUsers
2. Included a Domain User into it (who does not have any access on the
machine)
3. I went to the Reporting services home and selected Properties > Security
and provided this group a role of Browser
4. I asked the user to go into the home page as
http://machinename/reports/Pages/Folder.aspx
5. The user is prompted with the a userid and password dialog box with the
machinename.domain.com in the dialog box above the userid text box.
Please let me know, if this makes sense.
Thanks for your support
--
Saravanan
"Bruce L-C [MVP]" wrote:
> When you assigned specific rights to the folder you overwrote the inherited
> rights to Home. All the folders inherit from Home. First, try and get this
> working without doing anything to the new folder.
> Do this:
> 1. Add a user to a local group
> 2. Can they see Home (even if you don't have any folders)
> 3. Add a folder
> 4. Can they see this folder?
> 5. Add a report to the folder.
> Again, don't touch any of the rights on the folder you should have to. The
> only time to do this is if you want them in some folders and not others. In
> that case you can give browsing rights to the folder and not to home and
> then a link that brings them directly to the folder in question.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> news:7FF767C5-2371-4CFB-BAD9-120049766893@.microsoft.com...
> > Thanks for the patience to answer me Bruce.
> >
> > Before adding into the local administrators group, I tried that option
> > too.
> > I included the group onto the Browser Role on the Home of Reporting
> > Services,
> > then created the folder and I selected assign specific rights to the
> > folder
> >
> > I uploaded the reports , went to the security tab of the folder and
> > included
> > the group into the Browser Role. As it did not work, I included the group
> > into local admin.
> > Thanks!
> > --
> > Saravanan
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No, definitely not. Do not put the user in the local admin group. I think
> >> what you are missing is the process of adding to a role. Click on the
> >> home
> >> directory, details, security (on the left panel). Then add a role and use
> >> the new group that you have and give it browsing rights).
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> >> news:5A61A957-3E8B-464D-8E0F-D1DD886ADA39@.microsoft.com...
> >> > Thanks Bruce for your response. I am starting to test with a user now
> >> > and
> >> > that I would be putting the users in groups and assign permissions to
> >> > the
> >> > group. My concern is that, whether the users needs to be on the local
> >> > admin
> >> > group?. Am I missing anything?
> >> > --
> >> > Saravanan
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> One thing you could try (and really is easier than adding user by user
> >> >> browser permissions). Create a local group (I called mine Reports).
> >> >> Add
> >> >> all
> >> >> users and groups of users to this report. Then add this group to the
> >> >> browser
> >> >> role of Home. Everything inherits permissions from the Home directory.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> >> >> news:84A3066B-05BB-4360-8A8E-DCE0DFF6E50D@.microsoft.com...
> >> >> > Hello All,
> >> >> > I have created Reporting Services Report and I had given a user
> >> >> > Browser
> >> >> > permissions to browse the report. But he is not able to get to the
> >> >> > report.
> >> >> > The browser asks for userid and password and when typed it does not
> >> >> > take
> >> >> > it.
> >> >> >
> >> >> > Only when I put the user in the local administrator group, the user
> >> >> > is
> >> >> > able
> >> >> > to see the report and all the reports.
> >> >> >
> >> >> > I checked the app pool and it is running under network service and
> >> >> > the
> >> >> > network service is in the local administrator group. Could anybody
> >> >> > help?
> >> >> >
> >> >> > Thanks!
> >> >> > --
> >> >> > Saravanan
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||When prompted can they do this:
domainname\username put into the userid box and then their password?
I had this happen to me before and tore my hair out resolving it. For me the
issue was that the server did not have a fixed IP address, it was using
DHCP. Once I made it a fixed IP address then the problem went away.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
news:0C759E42-7D5E-4CD0-872E-E7F1619884DE@.microsoft.com...
> Hi Bruce,
> Thanks for the step by step instructions. This is what I did
> 1. Created a Usergroup named ReportUsers
> 2. Included a Domain User into it (who does not have any access on the
> machine)
> 3. I went to the Reporting services home and selected Properties >
> Security
> and provided this group a role of Browser
> 4. I asked the user to go into the home page as
> http://machinename/reports/Pages/Folder.aspx
> 5. The user is prompted with the a userid and password dialog box with the
> machinename.domain.com in the dialog box above the userid text box.
> Please let me know, if this makes sense.
> Thanks for your support
> --
> Saravanan
>
> "Bruce L-C [MVP]" wrote:
>> When you assigned specific rights to the folder you overwrote the
>> inherited
>> rights to Home. All the folders inherit from Home. First, try and get
>> this
>> working without doing anything to the new folder.
>> Do this:
>> 1. Add a user to a local group
>> 2. Can they see Home (even if you don't have any folders)
>> 3. Add a folder
>> 4. Can they see this folder?
>> 5. Add a report to the folder.
>> Again, don't touch any of the rights on the folder you should have to.
>> The
>> only time to do this is if you want them in some folders and not others.
>> In
>> that case you can give browsing rights to the folder and not to home and
>> then a link that brings them directly to the folder in question.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
>> news:7FF767C5-2371-4CFB-BAD9-120049766893@.microsoft.com...
>> > Thanks for the patience to answer me Bruce.
>> >
>> > Before adding into the local administrators group, I tried that option
>> > too.
>> > I included the group onto the Browser Role on the Home of Reporting
>> > Services,
>> > then created the folder and I selected assign specific rights to the
>> > folder
>> >
>> > I uploaded the reports , went to the security tab of the folder and
>> > included
>> > the group into the Browser Role. As it did not work, I included the
>> > group
>> > into local admin.
>> > Thanks!
>> > --
>> > Saravanan
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> No, definitely not. Do not put the user in the local admin group. I
>> >> think
>> >> what you are missing is the process of adding to a role. Click on the
>> >> home
>> >> directory, details, security (on the left panel). Then add a role and
>> >> use
>> >> the new group that you have and give it browsing rights).
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
>> >> news:5A61A957-3E8B-464D-8E0F-D1DD886ADA39@.microsoft.com...
>> >> > Thanks Bruce for your response. I am starting to test with a user
>> >> > now
>> >> > and
>> >> > that I would be putting the users in groups and assign permissions
>> >> > to
>> >> > the
>> >> > group. My concern is that, whether the users needs to be on the
>> >> > local
>> >> > admin
>> >> > group?. Am I missing anything?
>> >> > --
>> >> > Saravanan
>> >> >
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> One thing you could try (and really is easier than adding user by
>> >> >> user
>> >> >> browser permissions). Create a local group (I called mine Reports).
>> >> >> Add
>> >> >> all
>> >> >> users and groups of users to this report. Then add this group to
>> >> >> the
>> >> >> browser
>> >> >> role of Home. Everything inherits permissions from the Home
>> >> >> directory.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
>> >> >> news:84A3066B-05BB-4360-8A8E-DCE0DFF6E50D@.microsoft.com...
>> >> >> > Hello All,
>> >> >> > I have created Reporting Services Report and I had given a user
>> >> >> > Browser
>> >> >> > permissions to browse the report. But he is not able to get to
>> >> >> > the
>> >> >> > report.
>> >> >> > The browser asks for userid and password and when typed it does
>> >> >> > not
>> >> >> > take
>> >> >> > it.
>> >> >> >
>> >> >> > Only when I put the user in the local administrator group, the
>> >> >> > user
>> >> >> > is
>> >> >> > able
>> >> >> > to see the report and all the reports.
>> >> >> >
>> >> >> > I checked the app pool and it is running under network service
>> >> >> > and
>> >> >> > the
>> >> >> > network service is in the local administrator group. Could
>> >> >> > anybody
>> >> >> > help?
>> >> >> >
>> >> >> > Thanks!
>> >> >> > --
>> >> >> > Saravanan
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi Bruce,
Thanks for your ideas.
Even if they put their Domain userid and password it is not letting them in.
I checked the IP configuration and I can see that the IP has been assigned
static
--
Saravanan
"Bruce L-C [MVP]" wrote:
> When prompted can they do this:
> domainname\username put into the userid box and then their password?
> I had this happen to me before and tore my hair out resolving it. For me the
> issue was that the server did not have a fixed IP address, it was using
> DHCP. Once I made it a fixed IP address then the problem went away.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> news:0C759E42-7D5E-4CD0-872E-E7F1619884DE@.microsoft.com...
> > Hi Bruce,
> > Thanks for the step by step instructions. This is what I did
> > 1. Created a Usergroup named ReportUsers
> > 2. Included a Domain User into it (who does not have any access on the
> > machine)
> > 3. I went to the Reporting services home and selected Properties >
> > Security
> > and provided this group a role of Browser
> > 4. I asked the user to go into the home page as
> > http://machinename/reports/Pages/Folder.aspx
> > 5. The user is prompted with the a userid and password dialog box with the
> > machinename.domain.com in the dialog box above the userid text box.
> >
> > Please let me know, if this makes sense.
> > Thanks for your support
> > --
> > Saravanan
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> When you assigned specific rights to the folder you overwrote the
> >> inherited
> >> rights to Home. All the folders inherit from Home. First, try and get
> >> this
> >> working without doing anything to the new folder.
> >> Do this:
> >> 1. Add a user to a local group
> >> 2. Can they see Home (even if you don't have any folders)
> >> 3. Add a folder
> >> 4. Can they see this folder?
> >> 5. Add a report to the folder.
> >>
> >> Again, don't touch any of the rights on the folder you should have to.
> >> The
> >> only time to do this is if you want them in some folders and not others.
> >> In
> >> that case you can give browsing rights to the folder and not to home and
> >> then a link that brings them directly to the folder in question.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> >> news:7FF767C5-2371-4CFB-BAD9-120049766893@.microsoft.com...
> >> > Thanks for the patience to answer me Bruce.
> >> >
> >> > Before adding into the local administrators group, I tried that option
> >> > too.
> >> > I included the group onto the Browser Role on the Home of Reporting
> >> > Services,
> >> > then created the folder and I selected assign specific rights to the
> >> > folder
> >> >
> >> > I uploaded the reports , went to the security tab of the folder and
> >> > included
> >> > the group into the Browser Role. As it did not work, I included the
> >> > group
> >> > into local admin.
> >> > Thanks!
> >> > --
> >> > Saravanan
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> No, definitely not. Do not put the user in the local admin group. I
> >> >> think
> >> >> what you are missing is the process of adding to a role. Click on the
> >> >> home
> >> >> directory, details, security (on the left panel). Then add a role and
> >> >> use
> >> >> the new group that you have and give it browsing rights).
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >>
> >> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> >> >> news:5A61A957-3E8B-464D-8E0F-D1DD886ADA39@.microsoft.com...
> >> >> > Thanks Bruce for your response. I am starting to test with a user
> >> >> > now
> >> >> > and
> >> >> > that I would be putting the users in groups and assign permissions
> >> >> > to
> >> >> > the
> >> >> > group. My concern is that, whether the users needs to be on the
> >> >> > local
> >> >> > admin
> >> >> > group?. Am I missing anything?
> >> >> > --
> >> >> > Saravanan
> >> >> >
> >> >> >
> >> >> > "Bruce L-C [MVP]" wrote:
> >> >> >
> >> >> >> One thing you could try (and really is easier than adding user by
> >> >> >> user
> >> >> >> browser permissions). Create a local group (I called mine Reports).
> >> >> >> Add
> >> >> >> all
> >> >> >> users and groups of users to this report. Then add this group to
> >> >> >> the
> >> >> >> browser
> >> >> >> role of Home. Everything inherits permissions from the Home
> >> >> >> directory.
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Bruce Loehle-Conger
> >> >> >> MVP SQL Server Reporting Services
> >> >> >>
> >> >> >> "Saravanan" <Saravanan@.discussions.microsoft.com> wrote in message
> >> >> >> news:84A3066B-05BB-4360-8A8E-DCE0DFF6E50D@.microsoft.com...
> >> >> >> > Hello All,
> >> >> >> > I have created Reporting Services Report and I had given a user
> >> >> >> > Browser
> >> >> >> > permissions to browse the report. But he is not able to get to
> >> >> >> > the
> >> >> >> > report.
> >> >> >> > The browser asks for userid and password and when typed it does
> >> >> >> > not
> >> >> >> > take
> >> >> >> > it.
> >> >> >> >
> >> >> >> > Only when I put the user in the local administrator group, the
> >> >> >> > user
> >> >> >> > is
> >> >> >> > able
> >> >> >> > to see the report and all the reports.
> >> >> >> >
> >> >> >> > I checked the app pool and it is running under network service
> >> >> >> > and
> >> >> >> > the
> >> >> >> > network service is in the local administrator group. Could
> >> >> >> > anybody
> >> >> >> > help?
> >> >> >> >
> >> >> >> > Thanks!
> >> >> >> > --
> >> >> >> > Saravanan
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||> > > 5. The user is prompted with the a userid and password dialog box with the
> > > machinename.domain.com in the dialog box above the userid text box.
>
This sounds to me like it is related to permissions required to access
the report manager website (permissions set in IIS) rather than
permissions to access the reports via report manager. You may need to
give the user/group permission to read the actual folder where the
report server website is installed (should be similar to 'C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting Services\'). Check to
ensure the user has these privileges.
Rowen|||Unless IIS permissions have been changed from outside of RS there is never a
need to do anything with IIS permissions. However, if anyone has been
changing permissions with IIS then there could be a problem with a mismatch
between the two.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rowen" <rowenmcd@.gmail.com> wrote in message
news:1170200769.300637.219980@.h3g2000cwc.googlegroups.com...
>> > > 5. The user is prompted with the a userid and password dialog box
>> > > with the
>> > > machinename.domain.com in the dialog box above the userid text box.
> This sounds to me like it is related to permissions required to access
> the report manager website (permissions set in IIS) rather than
> permissions to access the reports via report manager. You may need to
> give the user/group permission to read the actual folder where the
> report server website is installed (should be similar to 'C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting Services\'). Check to
> ensure the user has these privileges.
> Rowen
>|||Thanks Rowen and Bruce. No body changed the permissions on the server, but
when I followed Rowen's idea it worked. Now the users are able see the report
without Admin priveliges on the report server machine.
Again! Thanks a lot
--
Saravanan
"Bruce L-C [MVP]" wrote:
> Unless IIS permissions have been changed from outside of RS there is never a
> need to do anything with IIS permissions. However, if anyone has been
> changing permissions with IIS then there could be a problem with a mismatch
> between the two.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Rowen" <rowenmcd@.gmail.com> wrote in message
> news:1170200769.300637.219980@.h3g2000cwc.googlegroups.com...
> >
> >> > > 5. The user is prompted with the a userid and password dialog box
> >> > > with the
> >> > > machinename.domain.com in the dialog box above the userid text box.
> >>
> >
> > This sounds to me like it is related to permissions required to access
> > the report manager website (permissions set in IIS) rather than
> > permissions to access the reports via report manager. You may need to
> > give the user/group permission to read the actual folder where the
> > report server website is installed (should be similar to 'C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting Services\'). Check to
> > ensure the user has these privileges.
> >
> > Rowen
> >
>
>|||On Feb 1, 5:32 am, Saravanan <Sarava...@.discussions.microsoft.com>
wrote:
> Thanks Rowen and Bruce. No body changed the permissions on the server, but
> when I followed Rowen's idea it worked. Now the users are able see the report
> without Admin priveliges on the report server machine.
> Again! Thanks a lot
> --
> Saravanan
> "Bruce L-C [MVP]" wrote:
> > Unless IIS permissions have been changed from outside of RS there is never a
> > need to do anything with IIS permissions. However, if anyone has been
> > changing permissions with IIS then there could be a problem with a mismatch
> > between the two.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > "Rowen" <rowen...@.gmail.com> wrote in message
> >news:1170200769.300637.219980@.h3g2000cwc.googlegroups.com...
> > >> > > 5. The user is prompted with the a userid and password dialog box
> > >> > > with the
> > >> > > machinename.domain.com in the dialog box above the userid text box.
> > > This sounds to me like it is related to permissions required to access
> > > the report manager website (permissions set in IIS) rather than
> > > permissions to access the reports via report manager. You may need to
> > > give the user/group permission to read the actual folder where the
> > > report server website is installed (should be similar to 'C:\Program
> > > Files\Microsoft SQL Server\MSSQL\Reporting Services\'). Check to
> > > ensure the user has these privileges.
> > > Rowen
Glad to hear this solved the problem. It may have been there since
reporting services was installed (particularly when using reporting
services 2003, which often requires manual configuration after
installation).
Rowen