Link to DB2

Hi all,

I'd like to know what the best means available is to enable distributed
queries from a SQL Server 2000, on tables in BOTH SQL Server 2000 and a
remote DB2 server (the latter is not yet available for testing).

So, I would be wanting to write queries or SP's like:

select t1.field1, t1.field5, t1.field6, t2.field55, t2.field22
from MySQLServer.mydb.dbowner.tableXXX as t1
inner join ThatDB2Server.thatdb.thatowner.tableYYY as t2 on t2.field11
= t1.field99
where t1.field44 = @.foobar

I'm aware of at least two ways to go here:

1) in SQL Server 2000, create a linked server to the remote DB2
server, either using the wizard or sp_addlinkedserver, and using either
an OLEDB or ODBC connection;

2) using MS Host Integration Server (HIS).

Since I've only just today learnt about HIS, I don't know very much
about it.

Questions:
(a) are options (1) and (2) mutually exclusive, or does one depend on
the other?
(b) can I do (1) without having to bother with (2)? If this, where
would I get hold of the required OLEDB/ODBC Provider?
(c) Is there another way(s) to go about this task?

HYCH,
Robrobertino wrote:

> 1) in SQL Server 2000, create a linked server to the remote DB2
> server, either using the wizard or sp_addlinkedserver, and using either
> an OLEDB or ODBC connection;
> 2) using MS Host Integration Server (HIS).
> Since I've only just today learnt about HIS, I don't know very much
> about it.
> Questions:
> (a) are options (1) and (2) mutually exclusive, or does one depend on
> the other?
> (b) can I do (1) without having to bother with (2)? If this, where
> would I get hold of the required OLEDB/ODBC Provider?
It depends :) If you would like to use Microsoft OLEDB Provider for DB2
you have to use HIS but there are third party OLEDB drivers like
http://www.hitsw.com/products_servi...db2_dsheet.html
or IBM OLEDB provider. I worked with IBM and HIS provider for linked
servers and everything went OK but i was using only openquery to access
data not joins with tables from DB2.

Lukasz

Line Number ?

Hi guys,
SQL RS supplies the global variable "PageNumber" but is there a kind of
"LineNumber" variable or function available ? Or another way to get the
number of current line being printed ?
Thanks.
Jean-MarcLook up "RowNumber" and "RunningValue" functions in the RS Books
Online. In a simple table, RowNumber(scope) gives the current row
number. In some more complex scenarios (ex: numbering rows within a
matrix group), the RunningValue(Expression, Function, Scope) function
is the best option.|||Thanks I'm gonna try that !
Jean-Marc

Limiting Export Rendering Options

I have a request to limit the number of rendering options available for
exporting. I know I can limit the options for all reports by modifying the
RSReportServer.config file. But is there a way programmatically to set the
available rendering options for a single report?The config file setting affects all reports. In order to limit the export
for certain reports, you would need to implement your own front end
application for the report server (similar to report manager) and manage the
restrictions on the report level yourself.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:A440907D-0EAB-465E-BA5A-73ECA1EFDD72@.microsoft.com...
>I have a request to limit the number of rendering options available for
> exporting. I know I can limit the options for all reports by modifying
> the
> RSReportServer.config file. But is there a way programmatically to set
> the
> available rendering options for a single report?|||where might I find out some information in how to build my own front end?
"Robert Bruckner [MSFT]" wrote:
> The config file setting affects all reports. In order to limit the export
> for certain reports, you would need to implement your own front end
> application for the report server (similar to report manager) and manage the
> restrictions on the report level yourself.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
> news:A440907D-0EAB-465E-BA5A-73ECA1EFDD72@.microsoft.com...
> >I have a request to limit the number of rendering options available for
> > exporting. I know I can limit the options for all reports by modifying
> > the
> > RSReportServer.config file. But is there a way programmatically to set
> > the
> > available rendering options for a single report?
>
>

Limited number of assemblies available in Visual Studio SQL Server Projects

Hi,

When starting a SQL Server Project in Visual Studio, there are a very limited number of assemblies available to reference. Does anyone know why?

Specifically I would like to use "Microsoft.AnalysisServices.AdomdClient". Does anyone know how I could do that?

Best Regards,

Johan ?hln,
IFS

Hi,

if you want more than these one, you have to register them in the GAC to see them in VS. The ones listed were intensivly tested by Microsoft not to produce any memory leaks being threadsafe etc. Additional (custom) resources have to be listed first for the computer and marked with the security policy level they need. "Doing the stack walk" while registering them at deployment time, SQL Server makes sure that called ressources reflect the at most the security settings of the registered assembly.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for your answer.

Microsoft.AnalysisServices.AdomdClient is already registered in the GAC, but I don't see it in Visual Studio. What else do I have to do to be able to reference it?

From what I can see it has unrestricted permissions in .NET Runtime Security Policy.

Best Regards,

Johan ?hln

|||

Hi Johan,

As the previous person mentions, the assemblies that are available to references in a sql server project are restricted. I think what this person meant to say is that these system assemblies are loaded from the GAC by sql server, yet this has nothing to do with when you wish to use an unsupported system assembly.

Note here about my terminology, system assembly = .Net BCL, user assembly = custom assembly.

You are wishing to reference an unsupported system assembly in sql server, this can be attempted (I say this because depending upon what the individual assembly tries to do, even in unsafe it will not be allowed to run when called upon and it's corresponding app. domain will be unloaded).

To do this, first run a create assembly statement referencing the adomd client assembly. Second, create a new regular class library project and add your reference to the adomd client libary. Then build the class in VS. Next, run the create assembly statement for your newly created class.

*You may have to set lower permission sets on the adomd client depending upon its attributes etc. There is a concept known as Host Protection Attributes (HPAs) which help sql server in determing which managed libaries should be allowed to execute within the hosted clr context. Basically, you may have to set the adomd client to unsafe it is "threatening" enough. But again, even with unsafe, sql server will only allow so much to occur aka you cannot kill sqlservr.exe from sqlclr code.

Hope this helps,

Derek

|||

Two additional points:

1. Actually, VS has a nice feature where if the assembly you're looking for is already registered in your SQL Server database, then you can reference it directly from your VS Database Project, even if it is a custom assembly or one that is not on the supported system assemblies list. It should show up normally under "Add Reference".

2. There are extremely few things that do not work in unsafe permission set, and the restrictions that are in place weren't done with reliability in mind. There are a number of ways to effectively kill your SQL Server process when running under unsafe, if you wanted to.

Steven

|||Noted on the unsafe being able to kill sqlservr. I did find that HPAs are only used in safe and unsafe perm buckets.|||i meant safe and external access :)|||

Thank you for your answers.

It seems impossible to reference AdomdClient from a SQL Server assembly:

It cannot be added using CreateAssembly|||

Johan ?hln wrote:

It seems impossible to reference AdomdClient from a SQL Server assembly:

It cannot be added using CreateAssembly

|||

TITLE: Microsoft SQL Server Management Studio Express

Create failed for SqlAssembly 'Microsoft.AnalysisServices.AdomdClient'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE ASSEMBLY failed because type 'ShilohSchemas' in safe assembly 'Microsoft.AnalysisServices.AdomdClient' has a static field 'shilohSchemas'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.
Warning: The SQL Server client assembly 'microsoft.analysisservices.adomdclient, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. (Microsoft SQL Server, Error: 6211)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=6211&LinkId=20476

BUTTONS:

OK

|||

I have solved this. It works if you create it as unsafe.

Thanks for your answers,

Johan

Limitations in SQL Server 2005 Express

Is there any information on any limits in the facilities available in SQL
Server 2005 Express (compared with Standard edition)?
For example: database size, maximum number of users etc.
What features are not available in the Express edition?
Thanks,
Steve.There is a 4GB limit for database size in SQL Server Express. As far as
number of users I do not think there is any limitation. Here is the complete
feature comparison chart for the different editions:
http://www.microsoft.com/sql/prodin...e-features.mspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hello,
Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
OF COST. SQL Express have below limitations:-
1. Maximum 4 GB database size
2. Allow only 1 GB RAM
3.Allow only 1 CPU
4. No Profiler
5. No SQL Agent to schedule jobs
The list goes on and on...
Thanks
Hari
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
> Is there any information on any limits in the facilities available in SQL
> Server 2005 Express (compared with Standard edition)?
> For example: database size, maximum number of users etc.
> What features are not available in the Express edition?
> Thanks,
> Steve.
>|||Many thanks for your comments and the link to Ms.
Steve.
"Hari Prasad" wrote:

> Hello,
> Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
> OF COST. SQL Express have below limitations:-
> 1. Maximum 4 GB database size
> 2. Allow only 1 GB RAM
> 3.Allow only 1 CPU
> 4. No Profiler
> 5. No SQL Agent to schedule jobs
> The list goes on and on...
> Thanks
> Hari
>
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
>
>

Limitations in SQL Server 2005 Express

Is there any information on any limits in the facilities available in SQL
Server 2005 Express (compared with Standard edition)?
For example: database size, maximum number of users etc.
What features are not available in the Express edition?
Thanks,
Steve.
There is a 4GB limit for database size in SQL Server Express. As far as
number of users I do not think there is any limitation. Here is the complete
feature comparison chart for the different editions:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Hello,
Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
OF COST. SQL Express have below limitations:-
1. Maximum 4 GB database size
2. Allow only 1 GB RAM
3.Allow only 1 CPU
4. No Profiler
5. No SQL Agent to schedule jobs
The list goes on and on...
Thanks
Hari
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
> Is there any information on any limits in the facilities available in SQL
> Server 2005 Express (compared with Standard edition)?
> For example: database size, maximum number of users etc.
> What features are not available in the Express edition?
> Thanks,
> Steve.
>
|||Many thanks for your comments and the link to Ms.
Steve.
"Hari Prasad" wrote:

> Hello,
> Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
> OF COST. SQL Express have below limitations:-
> 1. Maximum 4 GB database size
> 2. Allow only 1 GB RAM
> 3.Allow only 1 CPU
> 4. No Profiler
> 5. No SQL Agent to schedule jobs
> The list goes on and on...
> Thanks
> Hari
>
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
>
>

Limitations in SQL Server 2005 Express

Is there any information on any limits in the facilities available in SQL
Server 2005 Express (compared with Standard edition)?
For example: database size, maximum number of users etc.
What features are not available in the Express edition?
Thanks,
Steve.There is a 4GB limit for database size in SQL Server Express. As far as
number of users I do not think there is any limitation. Here is the complete
feature comparison chart for the different editions:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hello,
Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
OF COST. SQL Express have below limitations:-
1. Maximum 4 GB database size
2. Allow only 1 GB RAM
3.Allow only 1 CPU
4. No Profiler
5. No SQL Agent to schedule jobs
The list goes on and on...
Thanks
Hari
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
> Is there any information on any limits in the facilities available in SQL
> Server 2005 Express (compared with Standard edition)?
> For example: database size, maximum number of users etc.
> What features are not available in the Express edition?
> Thanks,
> Steve.
>|||Many thanks for your comments and the link to Ms.
Steve.
--
"Hari Prasad" wrote:
> Hello,
> Only advantage in SQL Express over SQL 2005 Standard edition is it is FREE
> OF COST. SQL Express have below limitations:-
> 1. Maximum 4 GB database size
> 2. Allow only 1 GB RAM
> 3.Allow only 1 CPU
> 4. No Profiler
> 5. No SQL Agent to schedule jobs
> The list goes on and on...
> Thanks
> Hari
>
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:948DDF45-3E76-4146-8610-3471873770C1@.microsoft.com...
> > Is there any information on any limits in the facilities available in SQL
> > Server 2005 Express (compared with Standard edition)?
> >
> > For example: database size, maximum number of users etc.
> > What features are not available in the Express edition?
> >
> > Thanks,
> > Steve.
> >
>
>

Limitation on Multiple parameters with Multi-Select and default values

Dear Anyone,

I created 6 parameters in my report. Each of these parameters are multi-select enabled. Each of their available values comes from a data set. Their default values also comes from a data set in order to have the select all check box selected by default.

My problem is only the first 2 parameters gets to have the select all selected. And the rest just have blank values. I was wondering if RS parameters have some sort of limitation that I cannot enable data set default values in all of my parameters.
Thanks,
JosephThere seems to be a bug in RS2005 in the parameters section. Filter the data set your using for the parameters to not include blanks. This is aparently whats causing your problem.|||

Hello,

I need to create reports with parameters that allow for multi-selection values.

How can I make them multi-select enabled?

Appreciate your reponse.

SK

Limitation on Multiple parameters with Multi-Select and default values

Dear Anyone,

I created 6 parameters in my report. Each of these parameters are multi-select enabled. Each of their available values comes from a data set. Their default values also comes from a data set in order to have the select all check box selected by default.

My problem is only the first 2 parameters gets to have the select all selected. And the rest just have blank values. I was wondering if RS parameters have some sort of limitation that I cannot enable data set default values in all of my parameters.
Thanks,
JosephThere seems to be a bug in RS2005 in the parameters section. Filter the data set your using for the parameters to not include blanks. This is aparently whats causing your problem.|||

Hello,

I need to create reports with parameters that allow for multi-selection values.

How can I make them multi-select enabled?

Appreciate your reponse.

SK

Limit Report Server Access

All,

I'm looking at the possibility of allowing only certian tasks and reports to be available to users. I want the user to only have so much control...but would like them to be able to run reports, subscribe to reports and be able to use the query builder. We don't want the user to see all of the reports in the organization, just the reports for there specific department. So I don't know that I want them to see the report server itself...but maybe be able to run certian features.

Can this be done?Sure, look up the security section of the Reporting Services books online. This describes RS 2000 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_security_v1_3hia.asp
There are some changes for Yukon, but only minor, they have to do with the introduction of Report builder.

Thanks
Tudor|||Tudor,

Thanks. I didn't mention that I am using RS 2005. But I don't know if this article adresses what I am looking for. If I can use the existing RS interface that is great. But I didn't want to show items that are not available. So for example, I will have a person log into a sales region and when they go to run reprots they will only see Sales reports. I don't want them to see executive reports in the list. I know I can limit the permissions on the reprot so they get an error, but I'm really trying to make it so they don't see what they can't run.

If not then I need to try and limit what they can do in my code. But them my question is how I can access report builder and subscription via code.

Thanks for the assistance!|||Is there a way to limit reports listed in the Report Viewer based on groups?
For example, Sales can only see Sales reports in the Report Manager. They can't see reports for other divisions, like IT, Directors, HR.|||If a certain user doesn't have READ permissions on a folder, report, datasource, they won't see it returned from the Report server SOAP APIs, not see it in report manager, SQL Management Studio, etc...

The permissions have changed slightly in RS 2005, but there are only additions to support the new features: report builder, models.

Thanks
Tudor|||Tudor,

Thanks for the answer. However, I'm not seeing the expected results.
I have 5 reports. I took away permissions on 1 report and the item still shows in the Report Manager. When I click on it, it only shows me a few properties instead of showing me the report. How can I prevent the report without adequete security from being displayed?

Thanks!|||

Local administrators on the report server machine will get read properties and read/set security on all items.
If you try to access using an account that's not in the local administrators group, you should get no access at all.

Thanks
Tudor