Error in stored procdure - 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

General SQL Server Forums

New to SQL Server Programming

Error in stored procdure

Author

Topic

vimaldreams
Starting Member

17 Posts

Posted - 2010-12-07 : 08:45:57

when i execute this stored procedure in sqlserver query analyzer it says executed successfully.

but when i execute thz SP thro asp.NET it says
Incorrect Syntax near '.'.

can i know wat could be the problem

USE [CRM database]
GO
/****** Object: StoredProcedure [dbo].[sp_ImportFromExcel00] Script Date: 11/19/2010 13:49:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE spx_ImportFromExcel03

@SheetName varchar(20),

@FilePath varchar(100),

@HDR varchar(3),

@TableName varchar(50)

AS

BEGIN

DECLARE @SQL nvarchar(1000)

IF OBJECT_ID (@TableName,'U') IS NOT NULL

SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM Sheet1$'

ELSE

SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM Sheet1$'

SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Data Source=';

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

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

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

EXEC sp_executesql @SQL

END

GO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 08:53:54

Does the SP actually run when called from asp or is it a call error
Try using the profiler to trace the call.

If it does run
Comment out the execute to see if that is the issue
Try logging @sql to a table when run from query analyser and asp and see if there is any difference .

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 08:56:51

Actually - are you sure this works when the SP is called from query analyser?
It looks like it needs an openrowset or something like that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vimaldreams
Starting Member

17 Posts

Posted - 2010-12-07 : 09:06:22

Thanx for your suggestion. but can you explain eloborately about SQL profiler or how can i trace the error.
bcoz i haven't heard of those things.

but when i comment the execute of SP its working fine. so definitely the problem with SP.

I dnt know how to trace the error.

Any suggestions or articles would help me to learn ..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 09:13:18

In that case create a trace table
create table trace (id int identity, entity varchar(100), dte datetime default getdate(), data varchar(max))
and relace the execute by
insert trace (entity, data) select 'spx_ImportFromExcel03', @sql

then you can see what you are executing and run it independently.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vimaldreams
Starting Member

17 Posts

Posted - 2010-12-07 : 09:15:37

when i use the opendatasource the following error occurs

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "Sheet1$". The table either does not exist or the current user does not have permissions on that table.

USE [CRM database]
GO
/****** Object: StoredProcedure [dbo].[sp_ImportFromExcel00] Script Date: 11/19/2010 13:49:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE spx_ImportFromExcel03
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3),
@TableName varchar(50)
AS
BEGIN
DECLARE @SQL nvarchar(1000)

IF OBJECT_ID (@TableName,'U') IS NOT NULL
SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'
ELSE
SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'
SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Data Source='
SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 8.0;HDR='
SET @SQL = @SQL + @HDR + ''''''')...['
SET @SQL = @SQL + @SheetName + ']'
EXEC sp_executesql @SQL
END

GO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 09:24:33

Sounds like it can find the file but not Sheet1$.
Try changing the name of the file to one that doesn't exist to make sure the error changes.
I take it the previous version didn't work when called from query analyser.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources