Credentials for Reports and Subscriptions

I have run into a seemingly impossible wall with RS. I need to have the
â'domain\userâ' value to pass to my dataset parameter. From there, I need to
create data-driven subscription with the same report.
* If I use User!UserID to pass to the data source parameter, RS will not
allow subscriptions to be created
* If I use any kind of data source that does not have the user and pass
stored, RS will not allow you to create a data driven subscription. Why
wonâ't the data driven subscription simply allow me to choose another data
source that does have stored credentials?
Is there any way around these limitations?
Best regards,
Joel Blackthorne
Hewlett-Packard CompanyJoel,
The RS Windows Service (ReportServerService.exe) runs subscriptions in an
unattended mode so there is no interactive user. If RS would have allowed
you to be able to use User!Userid you would get the Windows identity of the
account the RS Windows Service runs under (not very useful as you would
probably agree).
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:FEFD3781-0374-42EA-B7FD-D83D207A308C@.microsoft.com...
> I have run into a seemingly impossible wall with RS. I need to have the
> 'domain\user' value to pass to my dataset parameter. From there, I need
to
> create data-driven subscription with the same report.
> * If I use User!UserID to pass to the data source parameter, RS will not
> allow subscriptions to be created
> * If I use any kind of data source that does not have the user and pass
> stored, RS will not allow you to create a data driven subscription. Why
> won't the data driven subscription simply allow me to choose another data
> source that does have stored credentials?
> Is there any way around these limitations?
> Best regards,
> Joel Blackthorne
> Hewlett-Packard Company|||the enterprise edition allow you to schedule a report for a bulk usage.
You have to create a query on a database to retreive the username, email,
paramaters values of your report parameter... (query NOT in the report, but
prepared by the administrator in the schedule definition)
when the schedule is reached, RS execute the query to retreive the user
names and send this name through a CUSTOM parameter (not the userid
parameter) and then RS render the report and email it.
another way is to create your own tool which produce the report in behalf of
a user, but you must store the user password anywhere!
then when you call the Report server service, you'll use this login name as
a credential instead-of the system user (or any other static account)
"Joel" <Joel@.discussions.microsoft.com> a écrit dans le message de news:
FEFD3781-0374-42EA-B7FD-D83D207A308C@.microsoft.com...
>I have run into a seemingly impossible wall with RS. I need to have the
> 'domain\user' value to pass to my dataset parameter. From there, I need
> to
> create data-driven subscription with the same report.
> * If I use User!UserID to pass to the data source parameter, RS will not
> allow subscriptions to be created
> * If I use any kind of data source that does not have the user and pass
> stored, RS will not allow you to create a data driven subscription. Why
> won't the data driven subscription simply allow me to choose another data
> source that does have stored credentials?
> Is there any way around these limitations?
> Best regards,
> Joel Blackthorne
> Hewlett-Packard Company|||Thanks for the feedback. However, in my opinion, User!UserID should return
the user id of the credentials that created the subscription then. At the
very least, Data-Driven subscriptions should let you handle this invalid
User!UserID condition by replacing it. As it stands, I donâ't see any way to
implement data-level security with RS if subscriptions are used. Can any one
suggest a way to achieve the goal outlined in the original post?|||Don't forget that there is always a back door leading to the .NET world.
Drop a pinch of custom code in your report, stir with some external .net
assemblies and escape the boundaries of the Report Server forever and ever.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:4607E1F7-EA33-4E0A-9A2B-9B48B4E5CE8C@.microsoft.com...
> Thanks for the feedback. However, in my opinion, User!UserID should
return
> the user id of the credentials that created the subscription then. At the
> very least, Data-Driven subscriptions should let you handle this invalid
> User!UserID condition by replacing it. As it stands, I don't see any way
to
> implement data-level security with RS if subscriptions are used. Can any
one
> suggest a way to achieve the goal outlined in the original post?

Creating tables with variables value?

Hi I would like to know how can I create a table with a veriable from my code.

This is my code:

DECLARE @.MTH INT
DECLARE @.MTH2 INT
DECLARE @.DAY INT
DECLARE @.BGN DATETIME
DECLARE @.END DATETIME
DECLARE @.ENDI INT
DECLARE @.DTCTRL DATETIME
DECLARE @.TBLNAME TABLE (NOME VARCHAR(30))
--||_@.MTH_ ===>_RANGE_MONTH/DAYS||
SET @.MTH = 11 + 1
_RANGE_MONTH">--||_@.MTH2_===>_RANGE_MONTH__||
SET @.MTH2 = 5
--||_||

SET @.BGN = CONVERT(VARCHAR(8),DATEADD(MM,-@.MTH,GETDATE()),112)
SET @.END = CONVERT(VARCHAR(8),GETDATE(),112)
SET @.DAY = 0

WHILE @.BGN <> @.END
BEGIN
WHILE CONVERT(VARCHAR(8),DATEADD(DD,-@.DAY,GETDATE()),112) = @.END
BEGIN
INSERT INTO @.TBLNAME
SELECT 'TB_CHEQUE_' + CONVERT(VARCHAR,DATEADD(DD,-@.DAY,GETDATE()),112)
SET @.DAY = @.DAY + 1
END
SET @.DTCTRL = CONVERT(VARCHAR,DATEADD(DD,-@.DAY,GETDATE()),112)
SET @.END = DATEADD(DD,-1,@.END)
END

SET @.ENDI = 0
WHILE @.MTH2 <> @.ENDI
BEGIN
INSERT INTO @.TBLNAME
SELECT 'TB_CHEQUE_' + LEFT(CONVERT(VARCHAR,DATEADD(MM,-@.ENDI,@.DTCTRL),112),6)
SET @.ENDI = @.ENDI + 1
END
SET @.DTCTRL = DATEADD(MM,-@.ENDI,@.DTCTRL)
SET @.ENDI = 0

WHILE @.ENDI <> 6
BEGIN
INSERT INTO @.TBLNAME
SELECT 'TB_CHEQUE_' + CAST(DATEPART(YY,DATEADD(YY,-@.ENDI, GETDATE())) AS VARCHAR)
SET @.ENDI = @.ENDI + 1
END

SELECT * FROM @.TBLNAME

The name of the table is in each line of the table @.TBLNAME !

Any help ?

Can you please rephrase your question and perhaps give a repro? I'm not sure what it is that you are asking.

Thanks|||For each row of the table @.TBLNAME I need to create a table with that Name and i don't know how to do that|||

Like this -
usage:
exec InsertIntoTableName 'temptablename'

create proc InsertIntoTableName(@.TBLNAME sysname)
as
DECLARE @.MTH INT
DECLARE @.MTH2 INT
DECLARE @.DAY INT
DECLARE @.BGN DATETIME
DECLARE @.END DATETIME
DECLARE @.ENDI INT
DECLARE @.DTCTRL DATETIME
DECLARE @.sql VARCHAR(8000)
SET @.SQL = 'create table ##'+@.TBLNAME +'(NOME VARCHAR(112))'
print @.SQL
exec (@.SQL)
--||_@.MTH_ ===>_RANGE_MONTH/DAYS||
SET @.MTH = 11 + 1
_RANGE_MONTH">--||_@.MTH2_===>_RANGE_MONTH__||
SET @.MTH2 = 5
--||_||

SET @.BGN = CONVERT(VARCHAR(8),DATEADD(MM,-@.MTH,GETDATE()),112)
SET @.END = CONVERT(VARCHAR(8),GETDATE(),112)
SET @.DAY = 0

WHILE @.BGN <> @.END
BEGIN
WHILE CONVERT(VARCHAR(8),DATEADD(DD,-@.DAY,GETDATE()),112) = @.END
BEGIN
SET @.SQL = 'INSERT INTO ##'+ @.TBLNAME + ' SELECT ''TB_CHEQUE_' + CONVERT(VARCHAR,DATEADD(DD,-@.DAY,GETDATE()),112) +''''
EXEC (@.SQL)
SET @.DAY = @.DAY + 1
END
SET @.DTCTRL = CONVERT(VARCHAR,DATEADD(DD,-@.DAY,GETDATE()),112)
SET @.END = DATEADD(DD,-1,@.END)
END

SET @.ENDI = 0
WHILE @.MTH2 <> @.ENDI
BEGIN
SET @.SQL = ' INSERT INTO ##'+ @.TBLNAME + ' SELECT ''TB_CHEQUE_' + LEFT(CONVERT(VARCHAR,DATEADD(MM,-@.ENDI,@.DTCTRL),112),6) +''''
EXEC (@.sql)
SET @.ENDI = @.ENDI + 1
END
SET @.DTCTRL = DATEADD(MM,-@.ENDI,@.DTCTRL)
SET @.ENDI = 0

WHILE @.ENDI <> 6
BEGIN
SET @.SQL='INSERT INTO ##'+@.TBLNAME + ' SELECT ''TB_CHEQUE_' + CAST(DATEPART(YY,DATEADD(YY,-@.ENDI, GETDATE())) AS VARCHAR)+''''
EXEC (@.sql)
SET @.ENDI = @.ENDI + 1
END

exec ('SELECT * FROM ##'+@.TBLNAME)

sql

Creating select statement with seperate columns for different valu

I've been trying to figure out a select statement that would list a seperate
column with the count of a particular value for each value. Basically if I
have a table like this:
number value
-- --
10 good
10 bad
10 bad
12 good
14 bad
16 better
and I want to return all numbers with good or bad values and the totals for
those values, like this:
number good bad
-- -- --
10 1 2
12 1 0
14 0 1
How would I create the query?The best way to query this would be to use:
select num, count(value), value from checkcount
group by num, value
your result would be:
10 2 bad
14 1 bad
16 1 better
10 1 good
12 1 good
then you would want to create a user interface to format your result shown
in your example.
However, if you wanted SQL to bring your result back formatted as your
example, then you would want to inner join your table. hopefully, it is not
a large table. The following query would bring back your desired formatting:
select checkcount.num, isnull( thegood.good ,0) good, isnull( thebad.bad ,0)
bad from checkcount
left join (select num, count(value)as good from checkcount
where value = 'good'
group by num) theGood
on checkcount.num = thegood.num
left join (select num, count(value)as bad from checkcount
where value = 'bad'
group by num) thebad
on checkcount.num = thebad.num
where value in ('good','bad')
group by checkcount.num,thegood.good,thebad.bad
Thanks Kllyj64
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||If you're lucky enough to have SQL Server 2005, try the new PIVOT operator:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( number INT, xvalue VARCHAR(10) )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 10, 'good' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 12, 'good' )
INSERT INTO #tmp VALUES ( 14, 'bad' )
INSERT INTO #tmp VALUES ( 16, 'better' )
SET NOCOUNT OFF
SELECT *
FROM #tmp AS t
PIVOT
(
COUNT(xvalue) FOR xvalue In ( [good], [bad], [better] )
) AS x
That is my first PIVOT query! That's going to be useful!
Let me know how you get on.
Damien
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||Try,
select
number,
sum(case when value = 'good' then 1 else 0 end) as good,
sum(case when value = 'bad' then 1 else 0 end) as bad
from
t1
group by
number
go
How to rotate a table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
For SQL Server 2005, see PIVOT operator.
AMB
"David Tilman" wrote:

> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?

Creating one specific value as unique

Hello, I have a question about SQL Serer.
I don't know how to make a "constrain" whitch protect one column in table
for specific value.
In example:
I would to have unique value '33' in column a
'id' , 'a'
1, 33
2, 45
3, 45
4, 45
6, 33 - I want to have exception i this momentUNIQUE
Create Table Test
(
ID INT,
Nombres INT UNIQUE
)
Insert into Test Values (1,1)
Insert into Test Values (1,1)
Look in BOL --Section CREATE TABLE
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
Newsbeitrag news:3669E088-9E38-4CEF-94E1-A6A5715B4134@.microsoft.com...
> Hello, I have a question about SQL Serer.
> I don't know how to make a "constrain" whitch protect one column in table
> for specific value.
> In example:
> I would to have unique value '33' in column a
> 'id' , 'a'
> 1, 33
> 2, 45
> 3, 45
> 4, 45
> 6, 33 - I want to have exception i this moment|||Jens
I think it is not exactly what he wanted
>I would to have unique value '33' in column a
'>id' , 'a'
>1, 33
>2, 45
>3, 45
>4, 45
> 6, 33 - I want to have exception i this moment
It will be thrown an error when you try to insert a value=45
Well ,the following solution in terms of performance amy be not ideal , so
you can re-write it as
a trigger to do the same thing.
CREATE TABLE TableA
(
col INT
)
INSERT INTO TableA VALUES (20)--WORKS
GO
CREATE FUNCTION dbo.fn_check_TableA()
RETURNS bit
AS
BEGIN
IF EXISTS(SELECT *
FROM TableA
WHERE col = 20
GROUP BY col
HAVING COUNT(*)>1)
RETURN 0
RETURN 1
END
GO
ALTER TABLE TableA
ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA()=1)
GO
INSERT INTO TableA VALUES (30)--works
INSERT INTO TableA VALUES (30)--works
INSERT INTO TableA VALUES (20)--failed
DROP TABLE TableA
DROP FUNCTION dbo.fn_check_TableA
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O6qk2DOQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> UNIQUE
>
> Create Table Test
> (
> ID INT,
> Nombres INT UNIQUE
> )
> Insert into Test Values (1,1)
> Insert into Test Values (1,1)
> Look in BOL --Section CREATE TABLE
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:3669E088-9E38-4CEF-94E1-A6A5715B4134@.microsoft.com...
table
>|||Didnt he want a exception ?
"I want to have exception i this moment"
Jens.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:OZgR0NOQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Jens
> I think it is not exactly what he wanted
> '>id' , 'a'
> It will be thrown an error when you try to insert a value=45
> Well ,the following solution in terms of performance amy be not ideal , so
> you can re-write it as
> a trigger to do the same thing.
> CREATE TABLE TableA
> (
> col INT
> )
> INSERT INTO TableA VALUES (20)--WORKS
> GO
> CREATE FUNCTION dbo.fn_check_TableA()
> RETURNS bit
> AS
> BEGIN
> IF EXISTS(SELECT *
> FROM TableA
> WHERE col = 20
> GROUP BY col
> HAVING COUNT(*)>1)
> RETURN 0
> RETURN 1
> END
> GO
> ALTER TABLE TableA
> ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA()=1)
> GO
> INSERT INTO TableA VALUES (30)--works
> INSERT INTO TableA VALUES (30)--works
> INSERT INTO TableA VALUES (20)--failed
> DROP TABLE TableA
> DROP FUNCTION dbo.fn_check_TableA
>
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:O6qk2DOQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> table
>|||Try this ...
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [IX_test] UNIQUE NONCLUSTERED
(
id
)|||Yep, but it still should be allowed to insert another value,45? Am I right?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u1VpVQOQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> Didnt he want a exception ?
> "I want to have exception i this moment"
> Jens.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:OZgR0NOQFHA.3544@.TK2MSFTNGP12.phx.gbl...
so
>|||Dead Jans thank you for yor reply, but you did not understand my question .
If i use yours script i will get all values as unique. But I would like to
get ONLY ONE - SPECIFIC VALUE (in my example 33) as unique , other values
can duplicate.
"Jens Sü?meyer" wrote:

> UNIQUE
>
> Create Table Test
> (
> ID INT,
> Nombres INT UNIQUE
> )
> Insert into Test Values (1,1)
> Insert into Test Values (1,1)
> Look in BOL --Section CREATE TABLE
> HTH, Jens Sü?meyer.|||Thank Uri Dimanti, this answer solve part of my problem :)
But i have another question - how to solve this problem on sql server ce (on
sql server ce i can't create functions) ?
Mariusz Wilk|||Yeah, bit i think that ok, because in his example on exception is thown in
the fifth line.
So the exception or the Contraint should be just palin unique.
Jens.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uFQoxSOQFHA.2580@.TK2MSFTNGP10.phx.gbl...
> Yep, but it still should be allowed to insert another value,45? Am I
> right?
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:u1VpVQOQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> so
>|||OK, understand.
Thanks for clearing up.
"Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C233C0DD-E834-4744-BFC6-FB2C5422F1E9@.microsoft.com...
> Dead Jans thank you for yor reply, but you did not understand my question
> .
> If i use yours script i will get all values as unique. But I would like to
> get ONLY ONE - SPECIFIC VALUE (in my example 33) as unique , other
> values
> can duplicate.
>
> "Jens Smeyer" wrote:
>
>
>

Creating Global Variables

I wish I could set a global value on my project so this one value could be
used by each of my reports. Is it possible?Eric,
Global variables defined in your project isn't possible. But you can
consider to use the underlying datasource for this purpose. Just retrieve in
all your report a datasource with the 'global' variables. Be sure that you
put all your variables in one row and name the columns as your variablenames.
Jan Pieter Posthuma
"Eric" wrote:
> I wish I could set a global value on my project so this one value could be
> used by each of my reports. Is it possible?
>

Creating Custom code with DBNull and Report Parameters

How can I force report parameter to accept a null value. I tried using the check box for null values to no avail. I am creating a custom code to get around this problem. I used the simple IIf statement below:

=IIF (Parameters!MarketID.Value = "None", IsDBNull.Value, Parameters!MarketID.Value)

...I was getting an error message like this:

The value expression for the report parameter ‘MarketID’ contains an error: [BC30455] Argument not specified for parameter 'value' of 'Public Shared Function IsDBNull(value As Object) As Boolean'.

How can I frame the IIf statement?

Thx in advance

You could try replacing IsDBNull.Value with System.DBNull.Value