Showing posts with label access. Show all posts
Showing posts with label access. Show all posts

Monday, March 26, 2012

Dynamic Query

Hi!

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.

When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."

Below is my access code:

Dim varItem As Variant
Dim strSQL As String
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strMyDate As String, dtMyDate As Date

dtMyDate = CDate([Forms]![ySalesHistory]![Start Date])
strMyDate = Format(dtMyDate, "yyyymmdd")

strSQL = "procCustomerSalesandPayments '" & strMyDate & "', '" &
[Forms]![ySalesHistory]![Customer Number] & "'"

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection

'= = >NOTE: THIS IS WHERE THE ERROR POPS OUT!
Set cmd = cat.Procedures("Ben_CustomerSalesandPayments").Command

cmd.CommandText = strSQL
Set cat.Procedures("Ben_CustomerSalesandPayments").Command = cmd

DoCmd.OpenReport stDocName, acViewPreview

Set cat = Nothing
Set cmd = Nothing

Can anyone help me out?

Thanks.Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

I am trying to dynamically modify my pass-through query containing a
procedure call with 2 parameters.
>
When I run my access app, I get this error: "Object or provider is not
capable of performing reuqested operation."


ADOX is nothing I have experience of, but I found in MSDN under the Command
property in ADOX that it says:

An error will occur when getting and setting this property if the
provider does not support persisting commands.

Which provider are you using? How does your connection string look like?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Below is the connection string:

ODBC;DSN=YES2;DATABASE=YES100SQLC;

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99621E8AFE47Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I am trying to dynamically modify my pass-through query containing a
>procedure call with 2 parameters.
>>
>When I run my access app, I get this error: "Object or provider is not
>capable of performing reuqested operation."


>
ADOX is nothing I have experience of, but I found in MSDN under the
Command
property in ADOX that it says:
>
An error will occur when getting and setting this property if the
provider does not support persisting commands.
>
Which provider are you using? How does your connection string look like?
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

Below is the connection string:
>
ODBC;DSN=YES2;DATABASE=YES100SQLC;


And what is in that DSN?

Particular which OLE DB provider do you use? I had a look in a book on
ADO, and it said that the only two providers to support ADOX are the
Jet provider and SQLOLEDB. The book is a bit old, but if ODBC means that
you are using MSDASQL, then we have the answer to your problem. Change
to use SQLOLEDB instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sunday, March 11, 2012

Dynamic Flat File Destination Name!

Hi,

I am trying to access from OLE DB source. And based on one of the columns, I need to write the data to a Flat File Destination.

For Example,

CustID, ProductID, Product Name, Product Description

Say I am going to write to a different Flat File for every product. So if there are 10 products in the data. There should be 10 Flat Files. Also the file name should include the Product Name And Product ID.

It is being done in a single Data Flow Task.

Right now the Property Expression for the File Name isSad which is not working)

Code Snippet

@.DestFolder + [Data Conversion].ProductID + @.TodaysDate + ".txt"

The ProductIDs are in the ascending order. Any help or guidance?

Thanks

-Leo

You can't do that in one data flow unless you want to split it out to a separate destination (and connection manager) for each product. That might be okay for three or four products, but not if you have a bunch.

What you should do instead is set up a ForEach loop based on a the results of a SELECT DISTINCT ProductId query. Then execute your data flow once for each ProductId, customizing both the Source query and the destination connection manager with your ProductId.
|||

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

|||You might be able to use the Export Column transform. That transform writes out a file per row based on one column containing the filename and another column containing the data.|||

Correct me if I am wrong, Ted, but the Export Column transform is used when you have a binary field in your data flow that you want to persist to a file. That doesn't seem to be the case for Leo's data.

Leo, you can partition the file by rows, but what Jay is suggesting would be simpler and perform better. If you use a For Each Loop, you would get a list of all product IDs, then execute a data flow inside the loop for each product ID. Using expressions, you can alter both the OLE DB Source query and the destination file connection string for each iteration of the loop. You wouldn't ever read a destination file - you would only be writing rows to them. And you would only process each row once.

Here's a few examples of using ForEach loops:

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

http://www.sqlis.com/55.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx

and a search that might have some more:

http://search.live.com/results.aspx?q=foreach+&form=QBRE&q1=macro%3Ajamiet.ssis

|||The Export Column should work with text and ntext in addition to binary -- the column containing the file data just needs to be either a character or binary lob column. This is just another option for Leo with a different approach, requiring the data and file columns to be derived, which he might be able to do since he has already derived the filename column in his original post.|||

New Leo wrote:

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

Leo,

Yes you can do this. I've covered this exact problem here:

Splitting a file into multiple files

(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)

You won't be able to do it in a single data-flow tho.

-Jamie

Friday, March 9, 2012

Dynamic Dimension security options

Hi

I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.

Questions

1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)

2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.

3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.

Thanks

Milind

Bryan

Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.

Thanks

|||

What are you using to define your allowed set?

B.

|||

A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.

In the cube dimension you want to restrict, you defined an allowed set something like this:

NONEMPTYCROSSJOIN(

[My Dimension].[My Attribute].Members,

STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),

[Measure].[MySecurityMeasureGroupCount],

1)

It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.

Bryan

|||

Hi Bryan,

In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this

User Id, Dimension, Members

User123 Location [Location].[Some level member].(member].value

This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.

Can you please let me know how would you acheive this with performance taking into consideration.

Cheeeers

Milind

|||Hello!

I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.

Radim

|||

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

|||

Bryan C. Smith wrote:

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

Bryan,

1. Can you please post the example of acheiving this...i mean some link to some help or some code.

2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)

I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.

Thanks in advance.

Waiting for your response.

Milind

|||

Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:

Code Snippet

create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)

create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)


Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.

Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.

Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.

Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.

Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.

Now we're ready to set up security.

Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.

Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:

Code Snippet

{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}

This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)

Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.

Hope that helps get you started.

Bryan

|||

Hi Bryan,

Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.

But solution you have provided still doesn't statisfy the requirement I have.

As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.

1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.

2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.

3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)

I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.

Thanks,

Milind

|||

Sorry for the delay in the response. Long weekend here.

Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).

Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.

Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)

Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.

Good luck,
Bryan

Dynamic Dimension security options

Hi

I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.

Questions

1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)

2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.

3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.

Thanks

Milind

A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.

In the cube dimension you want to restrict, you defined an allowed set something like this:

NONEMPTYCROSSJOIN(

[My Dimension].[My Attribute].Members,

STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),

[Measure].[MySecurityMeasureGroupCount],

1)

It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.

Bryan

|||

Hi Bryan,

In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this

User Id, Dimension, Members

User123 Location [Location].[Some level member].(member].value

This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.

Can you please let me know how would you acheive this with performance taking into consideration.

Cheeeers

Milind

|||Hello!

I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.

Radim

|||

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

|||

Bryan C. Smith wrote:

I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.

You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.

Bryan

Bryan,

1. Can you please post the example of acheiving this...i mean some link to some help or some code.

2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)

I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.

Thanks in advance.

Waiting for your response.

Milind

|||

Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:

Code Snippet

create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)

create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)


Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.

Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.

Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.

Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.

Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.

Now we're ready to set up security.

Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.

Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:

Code Snippet

{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}

This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)

Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.

Hope that helps get you started.

Bryan

|||

Hi Bryan,

Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.

But solution you have provided still doesn't statisfy the requirement I have.

As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.

1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.

2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.

3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)

I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.

Thanks,

Milind

|||

Sorry for the delay in the response. Long weekend here.

Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).

Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.

Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)

Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.

Good luck,
Bryan

|||

Bryan

Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.

Thanks

|||

What are you using to define your allowed set?

B.

Wednesday, March 7, 2012

dynamic datasource

Our application is used by multiple companies. Each company has its own SQL
Server 2k database. Each company has access to the same list of reports in
SRS. We would like to deploy one physical copy of each report in the report
database for simplest maintenance of RDL files. Our application is deployed
in a web browser as an ASP application.
When a user in company A accesses Report 2 we need to render the report with
datasource parameters that will connect him to the correct db. At the same
time, users in companies B, C and D may be running the same report, each with
the correct datasource parameters for their companies.
Hence, as a report is requested by each user, we need to set up (or connect
to) the correct datasource parameters dynamically and then render the report
for the user.
Is there a way to do this with SRS? If multiple ways I would appreciate
knowing some of the options. If there are good books that discuss these
options I would appreciate references to them as well.
TIAHi joe,
We do something similar. We have multiple clients who use their own SQL
Database. We are using a custom data source (using ADO.net dataset). There
are numerous articles on the web which show you how to create one. Its pretty
simple and straightforward. The connection string is dynamically formed based
on the SQL Database name passed in as a parameter. This way we are able to
support multiple clients using a single copy of the rdl files.
Thanks,
George Tharakan
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Are you using the Report Manager or are you integrating with your own
application using either URL integration or Web services?
Assuming you are using Report Manager you can do the following:
1. have a report parameter that is based on the query. For the query use
pass the User!userid to a stored procedure (or use in a query) to query a
table and find out what database they should be using. Have this parameter
hidden so it can not be seen or modified by the user.
2. Using the generic query designer have the query use an expression like
this:
= "Select * from " & Parameters!DBName & ".dbo.mytable"
I usually test all this out with a report that has a single text box set to
this expression so I can see the query string and be sure it is correct
before I set a dataset source to it.
But, one issue you will have (with both this and the other solution on
creating your own extension) is that the UserID global parameter is not
usable from subscriptions. Really any solution this will be a problem
because you have to set the subcription to be for someone or it is run as
someone.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:28360A4A-BB83-4B5B-B55E-83A9F3B378E9@.microsoft.com...
> Our application is used by multiple companies. Each company has its own
> SQL
> Server 2k database. Each company has access to the same list of reports
> in
> SRS. We would like to deploy one physical copy of each report in the
> report
> database for simplest maintenance of RDL files. Our application is
> deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report
> with
> datasource parameters that will connect him to the correct db. At the
> same
> time, users in companies B, C and D may be running the same report, each
> with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or
> connect
> to) the correct datasource parameters dynamically and then render the
> report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Thanks very much for both of your responses.
I have been attempting to build an extension. It's hard to tell how far I
am from success because I don't know how to debug the code. Can you offer
advice on this?
I'm using the VS.NET Report Designer to build my reports. I can see the new
extension there, but cannot get a connection to work.
FYI - I'm using the ReportViewer control to embed the reports in my web app.|||Sorry, I have not done an extension. I suggest posting a new subject with
your questions for that so people that have done extensions know that is
what the question is about and can jump in and help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:3537E448-CE86-4E68-87C7-C8719A62112B@.microsoft.com...
> Thanks very much for both of your responses.
> I have been attempting to build an extension. It's hard to tell how far I
> am from success because I don't know how to debug the code. Can you offer
> advice on this?
> I'm using the VS.NET Report Designer to build my reports. I can see the
> new
> extension there, but cannot get a connection to work.
> FYI - I'm using the ReportViewer control to embed the reports in my web
> app.|||We have struggled with the same issue.
We were able to implement the @.server parameter and use that parameter to
dynamically choose the sql server at runtime.
Have fun trying to figure out the authentication nightmare you will encounter.
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>

Dynamic database with MS ACCESS

Hi,
I am using ASP.NET to display reports using the report viewer object.
I have multiple MS Access databases that can be used as the datasource to a
report.
I know how to make a connection to multiple different SQL databases passing
the database as a parameter in the URL of my query and then into my stored
procs, but the same technique cannot be applied to MS Access databases.
I figured out that the parameter to be passed must be the *.mdb file path on
the server... but where to use it?
ThxI have a huge query for as one of my datasets, something like this (this
query works well) :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN 'c:\db1.mdb'
ORDER BY tabl1.x
I tried the following :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN @.DBPath
ORDER BY tabl1.x
But apparently u can't use a named parameter with OLE DB ... so I tried this :
SELECT tabl1.*, ..., tablx.*
FROM tabl1, ... tablx
IN ?
ORDER BY tabl1.x
But when I execute the query and am asked what value should ? take, whatever
the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
[c:\db1.mdb]
Nothing works. I also don't know how to map the ? variable to a @.Parameter,
because the ? doesn't generate a parameter in the parameter tab ...
AAAaaaarggg!|||You are right about using unnamed parameters. First, are you in the generic
query designer (2 panes). You will want to be there (button is to the right
of the ...). Next, if a parameter is not created automatically for you then
go to the form design, menu report->report parameters and add a parameter.
Then go back to the data tab, click on the ..., go to the parameters tab and
then put in the ? on the left and select your parameter on the right.
My guess is that you cannot just have a parameter in your query where it is
not part of the where clause (which in this case is not). However, you can
still do this. Use an expression which would look like this:
= "SELECT tabl1.*, ..., tablx.* FROM tabl1, ... tablx IN '" &
Parameters!Paramname.Value & "' ORDER BY tabl1.x"
I sometimes create a report with no datasource, just my parameters and a
textbox. I set the textbox to the expression so I can see what I have
created so I know if it is correct before I set the dataset to this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6024F625-1721-4212-AB86-B8C131A12CE2@.microsoft.com...
>I have a huge query for as one of my datasets, something like this (this
> query works well) :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN 'c:\db1.mdb'
> ORDER BY tabl1.x
> I tried the following :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN @.DBPath
> ORDER BY tabl1.x
> But apparently u can't use a named parameter with OLE DB ... so I tried
> this :
> SELECT tabl1.*, ..., tablx.*
> FROM tabl1, ... tablx
> IN ?
> ORDER BY tabl1.x
> But when I execute the query and am asked what value should ? take,
> whatever
> the value i enter it doesnt work. I tried 'c:\db1.mdb', c:\db1.mdb, and
> [c:\db1.mdb]
> Nothing works. I also don't know how to map the ? variable to a
> @.Parameter,
> because the ? doesn't generate a parameter in the parameter tab ...
> AAAaaaarggg!
>

Friday, February 24, 2012

Dynamic connection to sql database via Access 2k7 project

I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.
Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:

>I am using SQL server 2005 with multiple client databases with the identical
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.

Dynamic connection to sql database via Access 2k7 project

I am using SQL server 2005 with multiple client databases with the identical
structure. Is it possible to disconnect from one database and connect to
another within the same ACCESS 2007 project? In Access 2007 connecting to
the database through an ODBC connection (with linked tables) is slower. I
would like to have the direct access for speed purposes. Any help on this
qould be greatly appreciated.Refer to the following article:
How to programmatically change the connection of a Microsoft
Access project
http://support.microsoft.com/kb/306881
-Sue
On Tue, 5 Jun 2007 17:11:00 -0700, Ed C <Ed
C@.discussions.microsoft.com> wrote:

>I am using SQL server 2005 with multiple client databases with the identica
l
>structure. Is it possible to disconnect from one database and connect to
>another within the same ACCESS 2007 project? In Access 2007 connecting to
>the database through an ODBC connection (with linked tables) is slower. I
>would like to have the direct access for speed purposes. Any help on this
>qould be greatly appreciated.