Problems instancing Reporting Services

I set up Reporting Services as a Web Reference and when I try to instance it, I get a "type expected" error message. Let's say the web reference is named SQLRS. When I do a DIM rs as NEW SQLRS, a blue squiggly appears under SQLRS and it displays the type expected error message.

What am I missing? Anyone know or have a link to an example I could look at? Thanks for any help.Additional information:

When I bring up the Reporting Services as a web reference, I don't get a drop down list of the methods and properties that are listed in the Add a Web Reference dialogue box and from what I've seen in examples online. For instance, there should be a ListChildren method, but I don't see it when intellisense kicks in.

Example: Let's say I name the web service ReportingService. If I try to instance it by going DIM rs as NEW ReportingService, I get the blue squiggly line complaining that "Type expected". If I type ReportingService. intellisense kicks in, but what appears in the drop down box is not what I expect. There are no ListChildren, ListEvents, ListJobs, etc.

What I suspect is that I need to DIM the ReportingService with one of the selections from the intellisense dropdown box, but I have no idea which one as all the examples I've seen online show differently. Has there been changes to this with service pack 2 that I'm not aware of? Any help would be appreciated.|||Ok, I just love answering my own questions.

It turns out that all the examples I've been trying to follow are a bit off. To create an instance of the Reporting Services as a web service, first add the web service as a web reference to your project. (i.e., right click on references in the solution explorer and type in the web address of the reporting services: http://myserver/ReportServer/ReportService.asmx)

Then, to create an instance, use something like:

DIM rs as NEW ReportingService.ReportingService

Now you will have an object called rs that will have the properties and methods of the ReportingService web service such as ListChildren, ListJobs, Render, etc.

Problems creating a View

I have a query on one of my pages that is just too large to keep in the page, so I need to reference a stored view in sql. I'm kind of new to this, so I'm having trouble getting the syntax right. The query is just a simple select statement using the results of several textboxes as parameters. I know how to do the query inside an asp.net page, but when I move it to sql, I don't know how to reference the textbox value i.e. @.textbox. Here's what I have so far:
USE [Maindb]
GO
CREATE VIEW [tblMain_view] (@.textbox nvarchar(30)) ??
AS SELECT dbo.tblMain.Field1, ...
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @.textbox and ...

First of all, I know that where I declare @.textbox is wrong, so where is the right place to declare it? Also, how do I reference the view from the webpage and do I still use:
cmd.SelectCommand.Parameters.Add . . .
in the page to establish the value. Anyone know a good tutorial on this. All the ones I've found were either in C# or didn't really apply. I need to know how to do this in VB. ThanksUSE [Maindb]
GO
CREATE VIEW [tblMain_view] (@.textbox nvarchar(30)) ??
AS SELECT dbo.tblMain.Field1, ...
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @.textbox and ...

USE Your DB
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'Yourtable')
GO
CREATE VIEW Name
AS
SELECT col1,col2,
FROM your table
WHERE

Try the above statement with your info, your code is missing the WHERE clause before the create view. Run a search for create View in the BOL or use my email address in my profile and I will send you the VIEW tutorial I wrote a while back. Sorry cannot help you with VB I write C#. Hope this helps.

Kind regards,
Gift Peddiesql

Problem with View

Hi,
Today I encountered with a view that references other views and each of them
reference some other view. The problem began when I started querying this
view.
When I used condition(WHERE), the view produced about 67,000 rows. When I
excluded the condition, I expected to have more rows but the result was
about 50,000 rows. I tried different ways but the condition produced more
rows!
Whereas the views were nested, it was very difficult to trace the problem.
It may help if I say some views used UNION ALL to concatenate some result.
I must find the source of problem. Does anybody have any idea/experience
like that?
Any help would be greatly appreciated.
LeilaI've had problems with nested views where the server has taken hours to get a
query plan (but that developer nested to quite a few levels) and heard of the
sort of thing you are seeing.
have you rebooted the server and updated statistics?
Apart from that I would suggest getting rid of the views and putting the
logic in stored procedures. You'll probably find it faster and certainly
easier to maintain and troubleshoot.
"Leila" wrote:
> Hi,
> Today I encountered with a view that references other views and each of them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>
>|||In addition to Nigel's commends: Are you using any old-style outer joins in any of the view
definitions? (*=)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <leilas@.hotpop.com> wrote in message news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of them reference some other
> view. The problem began when I started querying this view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I excluded the condition,
> I expected to have more rows but the result was about 50,000 rows. I tried different ways but the
> condition produced more rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience like that?
> Any help would be greatly appreciated.
> Leila
>|||Thanks Tibor,
I'm not sure if I have undertood your meaning.
Do you mean old style joins like:
select t1.*,t2.* from t1,t2 where t1.id=t2.id
?
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> In addition to Nigel's commends: Are you using any old-style outer joins
in any of the view
> definitions? (*=)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <leilas@.hotpop.com> wrote in message
news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Today I encountered with a view that references other views and each of
them reference some other
> > view. The problem began when I started querying this view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I excluded the condition,
> > I expected to have more rows but the result was about 50,000 rows. I
tried different ways but the
> > condition produced more rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
like that?
> > Any help would be greatly appreciated.
> > Leila
> >
>|||Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||Leila,
I'm referring to old style *outer* joins, such as:
SELECT t1.col1, t2.colZ
FROM t1, t2
WHERE t1.c1 *= t2.c1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Leila" <Leilas@.hotpop.com> wrote in message news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor,
> I'm not sure if I have undertood your meaning.
> Do you mean old style joins like:
> select t1.*,t2.* from t1,t2 where t1.id=t2.id
> ?
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> In addition to Nigel's commends: Are you using any old-style outer joins
> in any of the view
>> definitions? (*=)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> > Today I encountered with a view that references other views and each of
> them reference some other
>> > view. The problem began when I started querying this view.
>> > When I used condition(WHERE), the view produced about 67,000 rows. When
> I excluded the condition,
>> > I expected to have more rows but the result was about 50,000 rows. I
> tried different ways but the
>> > condition produced more rows!
>> > Whereas the views were nested, it was very difficult to trace the
> problem.
>> > It may help if I say some views used UNION ALL to concatenate some
> result.
>> > I must find the source of problem. Does anybody have any idea/experience
> like that?
>> > Any help would be greatly appreciated.
>> > Leila
>> >
>>
>|||No, but there're ansi style outer joins.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> Leila,
> I'm referring to old style *outer* joins, such as:
> SELECT t1.col1, t2.colZ
> FROM t1, t2
> WHERE t1.c1 *= t2.c1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Leila" <Leilas@.hotpop.com> wrote in message
news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > Thanks Tibor,
> > I'm not sure if I have undertood your meaning.
> > Do you mean old style joins like:
> > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > ?
> >
> > Leila
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> >> In addition to Nigel's commends: Are you using any old-style outer
joins
> > in any of the view
> >> definitions? (*=)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> > Today I encountered with a view that references other views and each
of
> > them reference some other
> >> > view. The problem began when I started querying this view.
> >> > When I used condition(WHERE), the view produced about 67,000 rows.
When
> > I excluded the condition,
> >> > I expected to have more rows but the result was about 50,000 rows. I
> > tried different ways but the
> >> > condition produced more rows!
> >> > Whereas the views were nested, it was very difficult to trace the
> > problem.
> >> > It may help if I say some views used UNION ALL to concatenate some
> > result.
> >> > I must find the source of problem. Does anybody have any
idea/experience
> > like that?
> >> > Any help would be greatly appreciated.
> >> > Leila
> >> >
> >>
> >>
> >
> >
>|||Leila,
can you post a concise DDL statements to replicate the issue, if possible..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Leila" <Leilas@.hotpop.com> wrote in message
news:#u0J0dP8EHA.2572@.tk2msftngp13.phx.gbl...
> No, but there're ansi style outer joins.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:uqA6jYP8EHA.3124@.TK2MSFTNGP11.phx.gbl...
> > Leila,
> >
> > I'm referring to old style *outer* joins, such as:
> >
> > SELECT t1.col1, t2.colZ
> > FROM t1, t2
> > WHERE t1.c1 *= t2.c1
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "Leila" <Leilas@.hotpop.com> wrote in message
> news:eib86hO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > > Thanks Tibor,
> > > I'm not sure if I have undertood your meaning.
> > > Do you mean old style joins like:
> > > select t1.*,t2.* from t1,t2 where t1.id=t2.id
> > > ?
> > >
> > > Leila
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> in
> > > message news:#HRwpeM8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> > >> In addition to Nigel's commends: Are you using any old-style outer
> joins
> > > in any of the view
> > >> definitions? (*=)
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> http://www.sqlug.se/
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > > news:%23w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> > >> > Hi,
> > >> > Today I encountered with a view that references other views and
each
> of
> > > them reference some other
> > >> > view. The problem began when I started querying this view.
> > >> > When I used condition(WHERE), the view produced about 67,000 rows.
> When
> > > I excluded the condition,
> > >> > I expected to have more rows but the result was about 50,000 rows.
I
> > > tried different ways but the
> > >> > condition produced more rows!
> > >> > Whereas the views were nested, it was very difficult to trace the
> > > problem.
> > >> > It may help if I say some views used UNION ALL to concatenate some
> > > result.
> > >> > I must find the source of problem. Does anybody have any
> idea/experience
> > > like that?
> > >> > Any help would be greatly appreciated.
> > >> > Leila
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Sounds like someone coded that ID to be VARCHAR instead of INT. Chances are
WHERE CustomerID = 100 is NULL. Try it with WHERE CustomerID LIKE '100%'
Even though I'd like to, I will refrain from beating up on you for using
nested VIEWS; hwoever, if you must nest, then you might consider
materializing the lower level ones. SQL Server calls these INDEXED VIEWS
but the effect is the same.
Sincerely,
Anthony Thomas
"Leila" <Leilas@.hotpop.com> wrote in message
news:ORQCBiO8EHA.3756@.TK2MSFTNGP14.phx.gbl...
Thanks Nigel,
Actually I cannot blame that programmer because of the view! Their logic is
really complicated and if I try to use SP, I must write a query with lots of
sub queries and derived tables.
As far as I know, old statistics may cause SQL Server to choose
inappropriate index. Therefore it hinders the performance but logically must
not produce wrong result.
Consider this query:
SELECT * FROM ThatView
This produces the list of customers with their IDs. I saw the ID 100, but I
tried the query below and did not get any result:
SELECT * FROM ThatView WHERE CustomerID=100
I don't know why the first query showed the CustomerID with ID of 100 but
the second one did not!
What is the affect of reboot on this problem?
Leila
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:F289F3DB-E431-493E-9AD4-61A730A9A2CF@.microsoft.com...
> I've had problems with nested views where the server has taken hours to
get a
> query plan (but that developer nested to quite a few levels) and heard of
the
> sort of thing you are seeing.
> have you rebooted the server and updated statistics?
> Apart from that I would suggest getting rid of the views and putting the
> logic in stored procedures. You'll probably find it faster and certainly
> easier to maintain and troubleshoot.
> "Leila" wrote:
> > Hi,
> > Today I encountered with a view that references other views and each of
them
> > reference some other view. The problem began when I started querying
this
> > view.
> > When I used condition(WHERE), the view produced about 67,000 rows. When
I
> > excluded the condition, I expected to have more rows but the result was
> > about 50,000 rows. I tried different ways but the condition produced
more
> > rows!
> > Whereas the views were nested, it was very difficult to trace the
problem.
> > It may help if I say some views used UNION ALL to concatenate some
result.
> > I must find the source of problem. Does anybody have any idea/experience
> > like that?
> > Any help would be greatly appreciated.
> > Leila
> >
> >
> >|||I did see couple of issues on my server, where in I do a select * with and
without a where clause. The result was different. But, I did this on a
table. The problem was found to be index corruption. You might want to
check into that.
-Nags
"Leila" <leilas@.hotpop.com> wrote in message
news:#w01QMK8EHA.3012@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Today I encountered with a view that references other views and each of
them
> reference some other view. The problem began when I started querying this
> view.
> When I used condition(WHERE), the view produced about 67,000 rows. When I
> excluded the condition, I expected to have more rows but the result was
> about 50,000 rows. I tried different ways but the condition produced more
> rows!
> Whereas the views were nested, it was very difficult to trace the problem.
> It may help if I say some views used UNION ALL to concatenate some result.
> I must find the source of problem. Does anybody have any idea/experience
> like that?
> Any help would be greatly appreciated.
> Leila
>