Apply code to parameter before query execution

Hello, is there any way to perform custom calcuations on a parameter before
retrieving the data set? I have a table with an encrypted account number
field. The encryption is done at the application level using the .NET
encryption libraries. I need to prepare a report retrieving the transactions
for a particular account. Obviously, I cannot use the plain text account
number as the query parameter. I need to take the user input, encrypt it, and
then use the encrypted value as the parameter. I could perform the encryption
on one of the asp.net pages and pass the encrypted value as a url parameter,
but I would prefer to allow the users to type in the account number on the
report page itself. Is there a way to do this? (I am using SQL Server 2000
Reporting Services, not 2005) Thanks!No problem. When you tie a report parameter to a query parameter you can
first manipulate it in an expression. The fact that they are two different
things (query parameter versus report parameter) is very important.
In the dataset tab click on the ..., parameters tab. This is where the
mapping occurs. On the right instead of selecting the parameter select
expression.
Your expression would look like this:
= Code.Mycode(Parameters!MyParam.value)
You can do code behind report or you can use custom assemblies. Search books
online using the work Code.
There are hoops to go through with custom assemblies, mostly dealing with
permissions. I haven't done this myself but plenty of people have.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Daniels" <JoelDaniels@.discussions.microsoft.com> wrote in message
news:DE91CBA6-67D0-4EBD-A07B-9F6B770DEFF9@.microsoft.com...
> Hello, is there any way to perform custom calcuations on a parameter
> before
> retrieving the data set? I have a table with an encrypted account number
> field. The encryption is done at the application level using the .NET
> encryption libraries. I need to prepare a report retrieving the
> transactions
> for a particular account. Obviously, I cannot use the plain text account
> number as the query parameter. I need to take the user input, encrypt it,
> and
> then use the encrypted value as the parameter. I could perform the
> encryption
> on one of the asp.net pages and pass the encrypted value as a url
> parameter,
> but I would prefer to allow the users to type in the account number on the
> report page itself. Is there a way to do this? (I am using SQL Server 2000
> Reporting Services, not 2005) Thanks!

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application-controlled transactions, isolation level and commit/rollbacks

If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce

Are you talking about application controlled transactions as in the application only works through stored procedures? Or do you mean it use DTC Transactions? Either way, you should be able to catch these with SQL Profiler.

|||Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce|||I was also hoping to see what Isolation Level my stored procedures are being executed at via the profiler. I just don't see how I can do that, or anything like it.

I would be grateful of any further information or help.

Regards
Darren
|||

Bruce dBA wrote:

Yes, Peter, the application uses stored procs in all cases.. there is no Transaction handling inside the procs... no begin trans, rollback/commits... that is all on the app side, and I'm not sure how it's being sent to SQL Server, via DTC or what... I'll try tracing again and seeing if they can force a rollback, and see what comes thru in SQL Server... Thanks, Bruce

In .NET 2.0 and above there are two other kinds of transactions that uses isolation levels and rollbacks one is atomic like the SQL Server transactions while the other is not but SQL Server 2005 is equiped to handle the none atomic transaction while 2000 is not so DTC is needed to run the none atomic in SQL Server 2000. The transactions from ADO.NET System.Data is atomic while the transaction from System.Transaction which uses TransactionScope it not. Try the links below for details.

http://msdn2.microsoft.com/en-us/library/system.transactions.aspx

http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx

Application Role with VB6

In VB6 I've got the following code when opening a connection to sql server:
With cn
.ConnectionString = "MyConnectionString"
.ConnectionTimeout = 10
.Properties("OLE DB SERVICES") = -2
.Open
'Had to allow execute for sps against the windows group until we can
suss app role
.Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'},'ODBC'"
End With
In sql server I've added a windows group to the database to allow the users
of the application to open the connection. I gave the windows group no
permissions of any kind. I added an application role and gave it select,
insert etc. permissions on the tables and execute permissions on all stored
procedures
When the application runs under a windows user (who is in the windows group
I added to the databse), the application can select data from the database
but gets 'execute permission denied' on any stored procedure that it tries t
o
run.
If I examine the permissions of one of these stored procedures, there is a
grant on it for the application role (there are no denies on it at all). If
I
then explicitly grant permission on one of these stored procedures to the
windows group containing the windows user, they are able to access it.
Ok, I think, it looks like it is ignoring the application role, so I deleted
the application role from the database and amended the VB code, the user is
then unable to access any data.
My conclusion then becomes the application role was giving the user access
to table data, but not to stored procedures, and when I granted permission t
o
the windows group this somehow overrode the application role. This is clearl
y
rubbish, because as I understand it, once an application role takes over, no
other permissions matter.
Anybody see what I'm doing wrong?
Thanks
PaulPlease ignore - accidentally posted twice
"Paul Whittaker" wrote:

> In VB6 I've got the following code when opening a connection to sql server
:
> With cn
> .ConnectionString = "MyConnectionString"
> .ConnectionTimeout = 10
> .Properties("OLE DB SERVICES") = -2
> .Open
> 'Had to allow execute for sps against the windows group until we c
an
> suss app role
> .Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'}
,'ODBC'"
> End With
> In sql server I've added a windows group to the database to allow the user
s
> of the application to open the connection. I gave the windows group no
> permissions of any kind. I added an application role and gave it select,
> insert etc. permissions on the tables and execute permissions on all store
d
> procedures
> When the application runs under a windows user (who is in the windows grou
p
> I added to the databse), the application can select data from the database
> but gets 'execute permission denied' on any stored procedure that it tries
to
> run.
> If I examine the permissions of one of these stored procedures, there is a
> grant on it for the application role (there are no denies on it at all). I
f I
> then explicitly grant permission on one of these stored procedures to the
> windows group containing the windows user, they are able to access it.
> Ok, I think, it looks like it is ignoring the application role, so I delet
ed
> the application role from the database and amended the VB code, the user i
s
> then unable to access any data.
> My conclusion then becomes the application role was giving the user access
> to table data, but not to stored procedures, and when I granted permission
to
> the windows group this somehow overrode the application role. This is clea
rly
> rubbish, because as I understand it, once an application role takes over,
no
> other permissions matter.
> Anybody see what I'm doing wrong?
> Thanks
> Paul

Application Role VB6

In VB6 I have the following code to use an application role
With cn
.ConnectionString = "MyConnectionString"
.ConnectionTimeout = 10
.Properties("OLE DB SERVICES") = -2
.Open
'Had to allow execute for sps against the windows group until we can
suss app role
.Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'},'ODBC'"
End With
I added a windows group to my sql server database, containing windows users
who will use this app. I gave the group no permissions of any kind. This
group as I understand it will allow the users to open the initial connection
to the database before the application role is then applied to the connectio
n.
Next I added an application role to the database and gave it select, insert
etc. on any tables the app needs and execute permission on any stored
procedures the app uses.
When a user (from the windows group) runs the app, data can be selected from
the tables but you get 'Execute permission denied' on any stored procedure
that runs.
If I explicitly grant permission to the windows group for one of the stored
procedures, they are able to access the stored procedure from within the app
.
Ok, I think it looks like the app isn't using the application role.
If I delete the application role from the database and amend the VB code so
as not to use an application role, the user is unable to perform the select
operation on any table, this confuses me as it looks like it was using the
application role to gain access to the data.
Anybody got any ideas.
Regards
PaulYou might try running a Profiler trace to see what's going on behind the
scenes. I suspect ADO is opening a separate connection for your stored proc
execution and this fails because the app role wasn't activated on that
connection.
Note that ADO will implicitly open another connection when the specified
connection is busy, such as in the case of an open recordset on that
connection.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Whittaker" <PaulWhittaker@.discussions.microsoft.com> wrote in message
news:DEBB1148-23EF-425B-92D6-55DE6CA4DD77@.microsoft.com...
> In VB6 I have the following code to use an application role
> With cn
> .ConnectionString = "MyConnectionString"
> .ConnectionTimeout = 10
> .Properties("OLE DB SERVICES") = -2
> .Open
> 'Had to allow execute for sps against the windows group until we
> can
> suss app role
> .Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N
> 'MyPassword'},'ODBC'"
> End With
> I added a windows group to my sql server database, containing windows
> users
> who will use this app. I gave the group no permissions of any kind. This
> group as I understand it will allow the users to open the initial
> connection
> to the database before the application role is then applied to the
> connection.
> Next I added an application role to the database and gave it select,
> insert
> etc. on any tables the app needs and execute permission on any stored
> procedures the app uses.
> When a user (from the windows group) runs the app, data can be selected
> from
> the tables but you get 'Execute permission denied' on any stored procedure
> that runs.
> If I explicitly grant permission to the windows group for one of the
> stored
> procedures, they are able to access the stored procedure from within the
> app.
> Ok, I think it looks like the app isn't using the application role.
> If I delete the application role from the database and amend the VB code
> so
> as not to use an application role, the user is unable to perform the
> select
> operation on any table, this confuses me as it looks like it was using the
> application role to gain access to the data.
> Anybody got any ideas.
> Regards
> Paul|||Thanks Dan
That was spot on, it was opening another connection (a recordset hadn't been
closed before it tried to execute the stored procedure).
Paul
"Dan Guzman" wrote:

> You might try running a Profiler trace to see what's going on behind the
> scenes. I suspect ADO is opening a separate connection for your stored pr
oc
> execution and this fails because the app role wasn't activated on that
> connection.
> Note that ADO will implicitly open another connection when the specified
> connection is busy, such as in the case of an open recordset on that
> connection.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Whittaker" <PaulWhittaker@.discussions.microsoft.com> wrote in messag
e
> news:DEBB1148-23EF-425B-92D6-55DE6CA4DD77@.microsoft.com...
>
>

Append string to field value in select list

How can I append a string to the field value in the select list
SELECT Code + '-20' FROM tb....
I want to the above to return 2000-20 for example.
How can I do this?
Mike BJust like you did if Code is a character-based datatype. If not, - then CAST(Code as varchar(10)) + '-20'