Access SMO objects in CLR proc

I would like to write some CLR procs that use SMO objects. In visual studio I am unable to add refrences to the SMO objects. How can I do this?

Thanks

Bert

Because CLR was designed to work within the SQL Server engine the references available for a CLR assembly are limited. The assemblies actually run within the SQL Server context, not as operating system processes.

What would you want to do using SMO that you can't do using Transact-SQL?

|||

You are unable to add them because SMO is dependent on Batchparser90.dll which is half managed/half unmanaged code, so SQL server will not load it. They don't show up in visual studio for this reason.

If you want to use assemblies that don't show up, try creating just a normal class library and then use the CREATE ASSEMBLY T-SQL command. You will have to load all of your external assemblies as well, so I would see if they load before you start writing it, as some will and some wont.

Access shared data source inside custom assembly

I have a database connection string hard-coded in a SQL Server 2005 Reporting Services custom assembly. Since the connection string is environment-specific (dev/prod), I would like to read the connections string from a settings file, like is typically done for web.config. Using the shared data source is also a good option.

How do I accomplish this?

I looked into trying to use DataSourceReference(), but that does not seem to be allowed inside of a custom assembly. Thank you.

Only private data sources can be expression-based. You can think of the Report Server as a web application since it runs under IIS. Therefore, you can put your config settings in the C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\web.config just like you would do with a regular ASP.NET app. Assuming you've deployed your report to the report catalog, you can read these settings at runtime . Additional considerations:

1. This technique is not supported.

2. Since there is no HttpContext in in the VS.NET Report Designer, you need to check for this condition and default your settings so you report works in design mode. I have a report in this download that demostrates how you can do this.

Access shared data source inside custom assembly

I have a database connection string hard-coded in a SQL Server 2005 Reporting Services custom assembly. Since the connection string is environment-specific (dev/prod), I would like to read the connections string from a settings file, like is typically done for web.config. Using the shared data source is also a good option.

How do I accomplish this?

I looked into trying to use DataSourceReference(), but that does not seem to be allowed inside of a custom assembly. Thank you.

Check Jason's blog here

http://weblogs.asp.net/jgaylord/archive/2005/05/12/406639.aspx

HTH
Regards

|||

Thank you. I read that blog, but it just seems to mention connection strings in general. The issue I am facing is that the Reporting Services assembly does not seem to allow me to access web.config, even though there is a web.config associated with RS. When I try to write "Imports System.Web.Configuration," VS2005 says it is not available and that the WebConfigurationManager is not available.

|||

Ok, simply add the System.Configurations.Dll as a reference in your project.

Let me know if you need further help.

Regards

Access second grid from SQL stored procedure

Hi:
I have a SQL stored procedure that returns 2 tables. How can I access the
fields from the second table. even though the column names are distinct in
the 2 tables, my reports do not get to the second table's fields. I cannot
join the 2 tables returned. And i cannot have them in 2 separate stored
procedures, because they use the data from the first table.
Please suggest something.
ThanksAre you using temporary tables? Not sure why you can't combine the 2 tables
into a 3rd table to return one result set. Can you explain further?
"NI" wrote:
> Hi:
> I have a SQL stored procedure that returns 2 tables. How can I access the
> fields from the second table. even though the column names are distinct in
> the 2 tables, my reports do not get to the second table's fields. I cannot
> join the 2 tables returned. And i cannot have them in 2 separate stored
> procedures, because they use the data from the first table.
> Please suggest something.
> Thanks|||well my first table is like a summary table, and the second table is like a
detailed table. One issue am having whihc is stopping me from combining both
tables is that the second table has duplicates (whihc I want to show in the
second table but not in the first table). I know SQL RS has the hide
duplicates option, ad if I do a grouping on that particular column it hides
duplicates, but the issue is with the count. If I have just one table (the
one with duplicates in it), I cannot get the right count (without duplicates)
in RS, as it always returns me the count with duplicates. so i had to
separate these two tables.
Also though there a few common columns in these tables, knowing the data I
know it wouldnt make much sense combining them, so I want to leave them as
separate tables.
Right now the workaround I have is have 2 different datasets doing the same
thing, but returning only one of the select statements. But its kinda wierd
to have 2 separate datasets doing the same thing.
Thanks
"phil" wrote:
> Are you using temporary tables? Not sure why you can't combine the 2 tables
> into a 3rd table to return one result set. Can you explain further?
> "NI" wrote:
> > Hi:
> >
> > I have a SQL stored procedure that returns 2 tables. How can I access the
> > fields from the second table. even though the column names are distinct in
> > the 2 tables, my reports do not get to the second table's fields. I cannot
> > join the 2 tables returned. And i cannot have them in 2 separate stored
> > procedures, because they use the data from the first table.
> >
> > Please suggest something.
> >
> > Thanks|||And you never will, except for using aggregation functions.
I think this is a bummer.
Look up SCOPE in the RS help and you will see what I mean.
Here's a sample assuming dataset1 and dataset2:
TextBox1.value = Fields!FirstName.Value
TextBox2.value = Sum(Fields!Income.Value, "dataset2")
BR//Jerry

Access Script Task

Hi,

How to access the Package inside the Script Taks. for example

IF intStatus = 1 Then

//Execute Package1

Else

//Execute Package2

End IF

in the above sample what is the property to get the Package1 object in script

Thanks & Regards

Jegan.T

You should use the Execute Package Task in order to execute other packages. Any reason why this doesn't work for you?

-Jamie

|||

If i use Execute Package i have to supply all the connection string details.we have the requirement is like this we have to excute the package based on a routine or function execution .its more like using batch file in Data Stage.

Jegan.T

|||

Sorry, I don't know anything about DataStage.

The connection string for the package tells you where it is located. Surely you need the same information in order to execute from the Script Task?

-Jamie

|||

Hi Jamie

Thanks for your reply . but the Script Task does not have any provison for connection string .

i have the design like this in the control flow

Script Task -> DataFlow1 --> DataFlow2

we have to write a routine in the script editor which will decied which DataFlow it has to execute

Script Editor

--

Public Sub Main()

'Code to Invoke DataFlow1 or DataFlow2
End Sub

How to achive this in the script editor ?

Thanks

Jegan.T

|||

I'm confused. At the start of this thread you said you were trying to execute a package.

NOW you say you are trying to decide which data-flow to execute.

The two are completely different so which is it? Perhaps you can explain exactly what it is you are trying to do.

If what you are trying to do is conditionally execute a data-flow then you are going about it the wrong way. You do not need to use a script task - use conditional precedence constraits which are described here: http://www.sqlis.com/default.aspx?306

-Jamie

access rs reports through .aspx

I'm not sure of what i should do to come up with a web site that allows the
users to access the reports that are in my reportserver, I would like to
know a step by step instructions on how to access my reports using asp.net
web application.
Is my problem has anything to do with configuring consoles or xml log files?
Will I have to make a coding that will look for server/ virtualroot/
pathinfo prefixes.. and where and how will i make one?
I am really confused on which or what should i do to access my reports
through aspx application ..
Please guide me..
--
~SiMPLe~You need to decide if you are going to integrate using URL integration
(easiest and fullest featured) or web services (harder but complete
control). Is this an intranet or internet. If internet where you don't want
the user to have access to your server then you would have to use web
services. If URL integration then the best thing is to first understand the
URL that you need to create. Make sure you can create the URL (even just
typing it in from IE) and that you can pass parameters. Once you can do that
then you can control the other aspects of it. I tend to create the URL for
Jump to URL which is a little different (RS automatically fills in the
server name so I don't have to deal with it).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alex" <alex@.interpac.com.ph(donotspam)> wrote in message
news:C9A32654-7806-402D-B709-9D7289DC9889@.microsoft.com...
> I'm not sure of what i should do to come up with a web site that allows
> the
> users to access the reports that are in my reportserver, I would like to
> know a step by step instructions on how to access my reports using asp.net
> web application.
> Is my problem has anything to do with configuring consoles or xml log
> files?
> Will I have to make a coding that will look for server/ virtualroot/
> pathinfo prefixes.. and where and how will i make one?
> I am really confused on which or what should i do to access my reports
> through aspx application ..
> Please guide me..
> --
> ~SiMPLe~|||so i need to know how to intergrate my reports using web services.. because i
don't want my clients to see my server.. Can you tell me in details how
should i start making one? and also in the URL integration, you mean the
prefixes right? (rs.. rc.. ) I guess i'll also use the URL integration
inside my web service?
--
~SiMPLe~
"Bruce L-C [MVP]" wrote:
> You need to decide if you are going to integrate using URL integration
> (easiest and fullest featured) or web services (harder but complete
> control). Is this an intranet or internet. If internet where you don't want
> the user to have access to your server then you would have to use web
> services. If URL integration then the best thing is to first understand the
> URL that you need to create. Make sure you can create the URL (even just
> typing it in from IE) and that you can pass parameters. Once you can do that
> then you can control the other aspects of it. I tend to create the URL for
> Jump to URL which is a little different (RS automatically fills in the
> server name so I don't have to deal with it).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alex" <alex@.interpac.com.ph(donotspam)> wrote in message
> news:C9A32654-7806-402D-B709-9D7289DC9889@.microsoft.com...
> > I'm not sure of what i should do to come up with a web site that allows
> > the
> > users to access the reports that are in my reportserver, I would like to
> > know a step by step instructions on how to access my reports using asp.net
> > web application.
> > Is my problem has anything to do with configuring consoles or xml log
> > files?
> > Will I have to make a coding that will look for server/ virtualroot/
> > pathinfo prefixes.. and where and how will i make one?
> > I am really confused on which or what should i do to access my reports
> > through aspx application ..
> > Please guide me..
> >
> > --
> > ~SiMPLe~
>
>

Access rights...

Hello,
I want to restrict the database not to be accessed from anywhere except my webservice...I mean, my client applications or anyone else can not be able to access the database...
How can I do this?
Thanks very much...

Are you looking for something similar to what was discussed in the following thread?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1

If not, can you explain what exactly you are trying to protect?

Thanks
Laurentiu