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