help with syntax please - SQL Server Forums

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)

help with syntax please

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-09-10 : 04:03:12

Hi,

Can someone please help me?
I am trying the code below and receive a syntax error and I can't see what is wrong.

Incorrect syntax near ',cast(convert(varchar , [division description] )as varchar (max)),1)-1) as itemcatalog_businessunit_description '.

declare @TableName varchar (200)
declare @HDR varchar (1)

set @TableName='#tmptable'
IF OBJECT_ID (@TableName,'U') IS NOT NULL
set @HDR=1
SET @SQL = 'SELECT left (cast(convert(varchar , [division description] )as varchar (max)) ,charindex(' ',cast(convert(varchar , [division description] )as varchar (max)),1)-1) as itemcatalog_businessunit_description '
SET @SQL = @SQL + 'INTO ' + @TableName + ' FROM OPENROWSET'

SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Database='

SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='

SET @SQL = @SQL + @HDR + ''''''')...['

SET @SQL = @SQL + @SheetName + ']'

EXEC(@sql)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-10 : 04:06:03

What is the result of this?

print @sql

Madhivanan

Failing to plan is Planning to fail

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-09-10 : 10:17:03

ok i solved this problem but now i am getting an error when executing the sp. How can i solve this?

Thanks

Msg 208, Level 16, State 1, Procedure usp_CPX_ImportCatalogExcel, Line 57
Invalid object name '##TmpTable'.

(1 row(s) affected)


ALTER PROCEDURE [dbo].[usp_CPX_ImportCatalogExcel]

	
AS
 
BEGIN  
	
DECLARE @sql NVARCHAR(500)
declare   @FilePath NVARCHAR(50),  @SheetName NVARCHAR(30)


SET @FilePath = N'c:\test\Catalog_big.xls'
SET @SheetName = N'catalog SU08$'

declare @TableName varchar (200)
declare @HDR varchar (1)


   set @TableName='##TmpTable'
  IF OBJECT_ID(@TableName) IS NOT NULL
	set @HDR=1
SET @SQL = 'SELECT left (cast( [division description] as varchar (max)) ,charindex('' '',cast( [division description] as varchar (max)),1)-1)   as  itemcatalog_businessunit_description '

 SET @SQL = @SQL + 'INTO ' + @TableName + ' FROM OPENROWSET' 

    SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Database=' 

    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 

    SET @SQL = @SQL + @HDR + ''''''')...[' 

    SET @SQL = @SQL + @SheetName + ']' 

    EXEC(@sql)
  select itemcatalog_businessunit_description from ##TmpTable

 
 end

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-11 : 02:58:27

[code]
ALTER PROCEDURE [dbo].[usp_CPX_ImportCatalogExcel]

AS

BEGIN

DECLARE @sql NVARCHAR(500)
declare @FilePath NVARCHAR(50), @SheetName NVARCHAR(30)

SET @FilePath = N'c:\test\Catalog_big.xls'
SET @SheetName = N'catalog SU08$'

declare @TableName varchar (200)
declare @HDR varchar (1)

set @TableName='##TmpTable'
IF (@TableName) IS NOT NULL
set @HDR=1
SET @SQL = 'SELECT left (cast( [division description] as varchar (max)) ,charindex('' '',cast( [division description] as varchar (max)),1)-1) as itemcatalog_businessunit_description '

SET @SQL = @SQL + 'INTO ' + @TableName + ' FROM OPENROWSET'

SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Database='

SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='

SET @SQL = @SQL + @HDR + ''''''')...['

SET @SQL = @SQL + @SheetName + ']'

EXEC(@sql)
select itemcatalog_businessunit_description from ##TmpTable
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources