Dynamic database problem!

I use the following query to enable a dynamic database in my reportviewer.
declare @.SQL varchar(255)
select @.SQL = 'select * from ' + @.DBName + '.dbo.V_RoofSection Where
(Section =' + @.Section + ') and (bassin =' + @.Bassin + ')'
exec (@.SQL)
This query causes a problem though.
Since the query is exacuted as a string, the resultset columns are not
recognized by the compiler and several errors appear when i try to preview my
report.
Is there a way to explicitely specify my columns on the above query so they
are recognized by the builder? Something like:
SELECT Section, Bassin FROM exec(@.SQL)
I hope i am clear enough.You should still be able to get the field list. Have you tried the refresh
fields button (to the right of the ...)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:8E1215FA-815A-4F99-9D29-B1D0F51C0415@.microsoft.com...
>I use the following query to enable a dynamic database in my reportviewer.
> declare @.SQL varchar(255)
> select @.SQL = 'select * from ' + @.DBName + '.dbo.V_RoofSection Where
> (Section =' + @.Section + ') and (bassin =' + @.Bassin + ')'
> exec (@.SQL)
> This query causes a problem though.
> Since the query is exacuted as a string, the resultset columns are not
> recognized by the compiler and several errors appear when i try to preview
> my
> report.
> Is there a way to explicitely specify my columns on the above query so
> they
> are recognized by the builder? Something like:
> SELECT Section, Bassin FROM exec(@.SQL)
>
> I hope i am clear enough.
>|||Yes I get them now ... I cant believe it was that simple. :)
"Bruce L-C [MVP]" wrote:
> You should still be able to get the field list. Have you tried the refresh
> fields button (to the right of the ...)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:8E1215FA-815A-4F99-9D29-B1D0F51C0415@.microsoft.com...
> >I use the following query to enable a dynamic database in my reportviewer.
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'select * from ' + @.DBName + '.dbo.V_RoofSection Where
> > (Section =' + @.Section + ') and (bassin =' + @.Bassin + ')'
> > exec (@.SQL)
> >
> > This query causes a problem though.
> >
> > Since the query is exacuted as a string, the resultset columns are not
> > recognized by the compiler and several errors appear when i try to preview
> > my
> > report.
> >
> > Is there a way to explicitely specify my columns on the above query so
> > they
> > are recognized by the builder? Something like:
> >
> > SELECT Section, Bassin FROM exec(@.SQL)
> >
> >
> > I hope i am clear enough.
> >
> >
>
>|||I still have a problem though, there is an error message saying that the
value I give to @.Section is an invalid column name.
declare @.SQL varchar(255)
select @.SQL = 'select distinct bassin from ' + @.DBName + '.dbo.V_RoofSection
Where (Section =' + @.Section + ') and bassin is not null'
exec (@.SQL)
However when I put quotes around my @.Section value the query works. How can
I modify my above query so I don't have to enter quotes around the values I
enter?|||Ok I found it, I just had to replace my single quotes with a triple quote!
god that is intuitive :p
"Eric" wrote:
> I still have a problem though, there is an error message saying that the
> value I give to @.Section is an invalid column name.
> declare @.SQL varchar(255)
> select @.SQL = 'select distinct bassin from ' + @.DBName + '.dbo.V_RoofSection
> Where (Section =' + @.Section + ') and bassin is not null'
> exec (@.SQL)
> However when I put quotes around my @.Section value the query works. How can
> I modify my above query so I don't have to enter quotes around the values I
> enter?