Persisting Code (Repost)

I have created a SRS report with 6 columns. I would like every 3rd Row of
the report to have a silver background and white on the rest. When I run
the report the first time I get the desired result. Then, depending on the
number of rows the report returns, successive runs of the report will have
the silver row starting on 1, 2 or 3. My guess is that SRS is remembering
where the code left off and picks up from there on the successive runs.
I added the following code to my report:
Private Shared count As Integer = 0
Private Shared colors As String() = {"White","White","White","White","White","White","White","White","White","White","White","White","Silver","Silver","Silver","Silver","Silver","Silver"}
Public Function GetColor() As String
Dim c as string = colors(count Mod colors.Length)
count = count + 1
Return c
End Function
Then, on the background Color I have:
=Code.GetColor()
George F Grund IVWould removing the shared keyword achieve your desired results?
You could also try and reset the count by creating a function like
below and then calling from your page header.
public function ResetVariable() as string
count = 0
return ""
end function|||Well, I'll asumme that you are using a table with no groups. If so, what you
are trying to do is very simple and you don't that code. Select the detail
row, then in the background color porperty type something like this:
=IIF(RowCount("YourDataSet") mod 3 = 0, "#D3D3D3", "#FFFFFF")
D3D3D3 is the RBG code for Light Gray, and FFFFFF is for White
I hope this helpssql

Permissions problem (I think)

O.k. Here's the background on my situation. We've got an Access program
running against a SQL 2000 DB. The program was created using Access 2003.
The user that runs this program had Access 2000 on their PC, which was
upgraded to Access 2003. One of the first steps the program does is copy
data from 2 external files into 2 seperate tables(1 table for each file).
This is done with the TransferText command. When the user runs the program,
a new table with the same name as the original is created with the user id
shown as the owner. The data is then copied into this new table instead of
the one already in the DB. Thus, when the program tries to read the origina
l
table it fails b/c it is empty. This happens ONLY for this user. If anyone
else logs in on the users PC, the program works fine. I've tried changing
every permissions option available, including giving the user FULL rights an
d
even making them an enterprise admin. Nothing seems to work. All security
is the same as it was before we upgraded the user to Access 2003, and it was
all working before. Any ideas'If the following is the issue and only happens for that
user:
"When the user runs the program, a new table with the same
name as the original is created with the user id shown as
the owner"
then it sounds like the user is in different database roles
than the other user. You didn't say what authentication
method is used or how the access program is connecting, if
it's a project or an mdb, etc. You may want to try running
profiler, capture the Login, user info and compare it when
others run the same routine. Then look at the differences in
database role, server role membership.
-Sue
On Thu, 25 Aug 2005 14:26:09 -0700, "CD"
<CD@.discussions.microsoft.com> wrote:

>O.k. Here's the background on my situation. We've got an Access program
>running against a SQL 2000 DB. The program was created using Access 2003.
>The user that runs this program had Access 2000 on their PC, which was
>upgraded to Access 2003. One of the first steps the program does is copy
>data from 2 external files into 2 seperate tables(1 table for each file).
>This is done with the TransferText command. When the user runs the program
,
>a new table with the same name as the original is created with the user id
>shown as the owner. The data is then copied into this new table instead of
>the one already in the DB. Thus, when the program tries to read the origin
al
>table it fails b/c it is empty. This happens ONLY for this user. If anyon
e
>else logs in on the users PC, the program works fine. I've tried changing
>every permissions option available, including giving the user FULL rights a
nd
>even making them an enterprise admin. Nothing seems to work. All security
>is the same as it was before we upgraded the user to Access 2003, and it wa
s
>all working before. Any ideas'

Permissions in SQLExpress

Hello. I have a problem with assigning permissions in SQLExpress.
First a little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry Dyck

It looks like you connect to the database as a member of PWNDAdmin and try to grant the role permissions you don't have. The error you were getting is caused by running as a low privileged principal with no access to the procedures. You don't need to grant db_owner status to grant execute permission, you just need to be able to grant permissions on the object and one way to achieve this would be to connect to the database as the dbo before granting the permissions.

Thanks
Laurentiu

|||Actually, I have my own user in the database for my login and am assigned db_owner. I ran a query that showed that I indeed had db_owner status. Would it perhaps help if I wasn't in the PWDNAdmin group on the network?
Thanks,
Gerry
|||

But didn't you get db_owner status because you're a PWDNAdmin member and you made that a db_owner? Or were you a db_owner before that?

As a db_owner, you should be able to grant any permission on objects in the database.

Can you post the T-SQL grant statement that fails as well as the text and number of the error that you are receiving?

Thanks
Laurentiu

|||I was a db_owner before giving that to PWDNAdmin. You have arrived at my delima!!! I should, as a db_owner grant permissions on objects in the database.
The T-SQL is:
GRANT EXECUTE ON mystoredprocedure TO PWDNAdmin
To get the error I will have to get back to the office and run the program. I will do that tomorrow.
Thanks for your help in deciphering my problem. Sure as tomorrow is the shortest day, my problem will be some simple thing, but until I find it....uuuuggghh.
Gerry
|||

Maybe the mystoredprocedure resides in a special schema and you should prefix its name with the schema name. The statement may fail because it cannot find the procedure within the schemas it searches by default.

Thanks
Laurentiu

|||The statement itself works. What doesn't work is the program when the procedure is called.
|||I am not sure about the schema that the query belongs to.
Maybe I need to review, for myself as well as for you to help me better. The database I am using was upgraded from MSDE so it brought along all the permissions for PWDNAdmin and the stored procedures. One of my programs that has been using the database for a few months works without any problems after the upgrade to SQLExpress.
Now I have created stored procedures in a separate database located on my notebook (so I can develope at my home office) that I have then created in the database in question.
Here is the T-SQL that I used to create the procedures (well only one 'cause there are lots)
STart:
USE [ProcWareDOTNET]
GO
/****** Object: StoredProcedure [dbo].[AcctInitialLogSelect] Script Date: 12/21/2005 10:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[AcctInitialLogSelect]
AS
SET NOCOUNT ON;
SELECT InvoiceNumber, PONumber, DateReceived, VendorName, Value, Initials, ErrorDescription FROM ATInitialLog
Grant Execute on dbo.AcctInitialLogSelect to PWDNAdmin
END
Note the Start and End are so you know where the sql ends. Anywho. I have now discovered the problem. If I remove the Grant Excecute line then my problem goes away!!! Now why would that be? Very interesting.
|||

The grant ends up being part of the procedure definition, so when you will call the procedure, you will actually perform a grant on it. You should always use BEGIN and END to delimit the body of your stored procedure. Also, simply adding a GO before the grant would make the script run as intended, but I recommend always using BEGIN and END.

Thanks
Laurentiu

|||Thank you Laurentiu for your help is solving my problem. I am not trained in SQL and in fact all my programming knowledge is self taught. There are times when the scope of work requires venturing into new areas previously unknown. This is one of those cases. I have worked for years using Access databases and in the last year the scope of our work finally made us realize that it was time to lay Access to rest and get with the program and true SQL databases. As such, there are times when learning while doing means getting it done sooner and perhaps bulkier rather than getting it done with precision that only years of knowledge can bring.
Thanks again have great holiday season and terrific new year,
Gerry
|||

I am glad I could be of help. Happy holidays to you as well.

Thanks
Laurentiu

Permissions

Hello. I have a problem with assigning permissions in SQLExpress. First a
little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry DyckHi,
add that users to db_datareader or db_ddladmin(it will grant permission to
Data Defination Languages)
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:

> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>|||Hi,
add them to db_ddladmin or grant db_datareader
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:

> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>