path as variable in openrowset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

path as variable in openrowset

Author

Topic

moramoga
Starting Member

34 Posts

Posted - 2010-05-20 : 17:18:18

Hi,

How can I send the path in a variable for this store procedure, I use it like this but is not working.

Create PROCEDURE dbo.StoredProcedure1

AS
	declare @ruta varchar (200)
	set @ruta = 'C:\testing.xls'

	
	SELECT     Columna1, Columna2, Columna3, Columna4, Columna5, Columna6, Columna7
FROM         OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database= @ruta', 'SELECT * FROM [Sheet1$]') AS derivedtbl_1
	
	RETURN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 17:37:17

Try this: ...Excel 8.0;Database= ' + @ruta + ',...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-20 : 17:37:31

Ok, try this:

Create PROCEDURE dbo.StoredProcedure1

AS
declare @ruta varchar (200)
set @ruta = 'C:\testing.xls'

SELECT Columna1, Columna2, Columna3, Columna4, Columna5, Columna6, Columna7
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database='+ @ruta, 'SELECT * FROM [Sheet1$]') AS derivedtbl_1

RETURN

moramoga
Starting Member

34 Posts

Posted - 2010-05-21 : 10:39:20

Hi Thanks for the query, I try that before but is giving the syntax error : incorrect syntax near '+'. I also try with a + after the @ruta but still not working even if the syntax colors seems to match as if they were correct.

moramoga
Starting Member

34 Posts

Posted - 2010-05-21 : 10:49:10

Hi, I also try this as a test but still not working:


Create PROCEDURE dbo.test2

AS

declare @ruta1 varchar (200)
declare @ruta2 varchar(299)


set @ruta1= 'Microsoft.Jet.OLEDB.4.0'
set @ruta2 = 'Excel 8.0;Database= C:\testing.xls' 


SELECT Columna1, Columna2, Columna3, Columna4, Columna5, Columna6, Columna7
FROM OPENROWSET(@ruta1,@ruta2 , 'SELECT * FROM [Sheet1$]') AS derivedtbl_1

RETURN

moramoga
Starting Member

34 Posts

Posted - 2010-05-21 : 10:56:27

ok I am starting to give up lol, I found this in another forum asking the same question:

quote:
Openrowset() (or any ad-hoc function) does not accept variable parameter.
They only take literal string.
Afterall, these are pass-through functions.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-21 : 13:32:52

If it needs to be dynamic, you may be able to build the SQL string in a variable and then use:

EXEC (@SQL);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources