Passing ADO.Net dataset to RS 2005

Hi,
I would like to know how to pass ado.net dataset to reporting services 9.
I´ve found this article
http://msdn.microsoft.com/data/archive/default.aspx?pull=/library/en-us/dnsql2k/html/rsdsetex3.asp
but, acording to the sumary, it does apply to "SQL 2000 - Reporting
Services".
Could anyone help me'
Any help will be appreciated.
thanx a lotHi Ricardo,
the same article code works for RS 2005 also.
There will be only few changes like pointing to 2005 RS assemblie references..
just point the Microsoft.ReportingServices.Interfaces.dll to the 2005RS dll
and it will work..
-Bava
"Ricardo" wrote:
> Hi,
> I would like to know how to pass ado.net dataset to reporting services 9.
> I´ve found this article
> http://msdn.microsoft.com/data/archive/default.aspx?pull=/library/en-us/dnsql2k/html/rsdsetex3.asp
> but, acording to the sumary, it does apply to "SQL 2000 - Reporting
> Services".
> Could anyone help me'
> Any help will be appreciated.
> thanx a lot
>
>

Passing a variable to a Linked Query (OPENROWSET for Excel Syntax)

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)

but I have not gotten all the ''''' + @.variable syntax right.

Here is my raw openrowset with what I am aiming at.

Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game? Many thank-yous, in advance.

Kind Regards,

Claudia.

You can make use of the QUOTENAME function to help you out here.

I couldn't get the MSDASQL Excel driver to work on my desktop, but below is an example that uses the same principles but with the Jet Excel driver. Simply modify the values of the provider, connection string, filename and query variables as appropriate.

Chris

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.Provider NVARCHAR(100)

DECLARE @.FIL NVARCHAR(256)

DECLARE @.ConnectionString NVARCHAR(1000)

DECLARE @.Query NVARCHAR(1000)

SET @.Provider = N'Microsoft.Jet.OLEDB.4.0'

SET @.FIL = N'C:\Company Folders\Documentation\INVENTORY.xls'

SET @.ConnectionString = N'Excel 8.0;DATABASE=' + @.FIL

SET @.Query = N'SELECT * FROM [Inventory$]'

SET @.SQL = N'SELECT *

FROM OPENROWSET(' + QUOTENAME(@.Provider, N'''') + N', '

+ QUOTENAME(@.ConnectionString, N'''') + N', '

+ QUOTENAME(@.Query, N'''') + N')'

EXEC sp_executesql @.SQL