Error with dynamic SQL

Question

Error with dynamic SQL

Vicky-854895

Mr or Mrs. 500

Points: 597
More actions
March 12, 2010 at 6:25 am

#221723

HI All,
I'm trying to import MS Excel data into table using stored procedure. In the stored procedure, I'm reading data using select * FROM OPENDATASOURCE... statement. I want this data to be store in cursor. I'm doing this using dynamic SQL. But I'm getting errors with creating cursor with dynamic SQL.
Following is the stored procedure code for reference...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Pro_Import_T_Registered]
(
@filePath1 varchar(255)
)
as
DECLARE @temp_registration_num varchar(255)
DECLARE @temp_price float
DECLARE @temp_date datetime
DECLARE @temp_sro_code int
DECLARE @drivers varchar(100)
DECLARE @props varchar(100)
DECLARE @filepath varchar(100)
DECLARE @path varchar(100)
DECLARE @sheet varchar(100)
DECLARE @query nvarchar(500)
DECLARE @importRegisteredData CURSOR
set @drivers = 'Microsoft.Jet.OLEDB.4.0'
set @path='Data Source='
set @filepath = 'F:\Reg\Registered.xls'
set @props =';Extended Properties=Excel 8.0'
set @sheet=')...[sheet1$];'
set @query =N'SET @importRegisteredData= CURSOR FOR select * FROM OPENDATASOURCE('+''''+@drivers+''''+', '+''''+@path+@filepath+@props+''''+@sheet;
EXECUTE sp_executesql @query
OPEN @importRegisteredData
FETCH NEXT FROM @importRegisteredData INTO
@temp_registration_num,
@temp_price,
@temp_date,
@temp_sro_code
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO T_Registered
([registration_num],
[price],
[date],
[sro_code]
)
VALUES(
@temp_registration_num,
@temp_price,
@temp_date,
@temp_sro_code
)
FETCH NEXT FROM @importRegisteredData INTO
@temp_registration_num,
@temp_price,
@temp_date,
@temp_sro_code
END
CLOSE @importRegisteredData
DEALLOCATE @importRegisteredData
The procedure compiles successfully, when try to execute, getting following errors
Msg 137, Level 15, State 3, Line 1
Must declare the scalar variable "@importRegisteredData".
Msg 16950, Level 16, State 2, Procedure Pro_Import_T_Registered, Line 29
The variable '@importRegisteredData' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Procedure Pro_Import_T_Registered, Line 34
The variable '@importRegisteredData' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Procedure Pro_Import_T_Registered, Line 62
The variable '@importRegisteredData' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Procedure Pro_Import_T_Registered, Line 63
The variable '@importRegisteredData' does not currently have a cursor allocated to it.
Please guide me to figure out the problem.
-Vicky

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

davidandrews13 SSCertifiable Points: 6808 More actions · Answer 1

i would try putting the whole code, including the code between the 'fetch new from' lines and the variables your declaring, within the @query and execute it as one.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Vicky-854895 (3/12/2010)
set @query =N'SET @importRegisteredData= CURSOR FOR select * FROM OPENDATASOURCE('+''''+@drivers+''''+', '+''''+@path+@filepath+@props+''''+@sheet;

Above lines causing the issue as you can use temp variable under a dynamic Sql.

@importRegisteredData doesnt have scope under @query

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 3

If may I ask, why do you want to run this code in a cursor? It doesn't look like you need to.

Regards

Piotr

...and your only reply is slàinte mhath

LutzM SSC Guru Points: 107049 More actions · Answer 4

Please see my answer posted on your parallel thread.

...

http://www.sqlservercentral.com/Forums/FindPost881675.aspx

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Vicky-854895 Mr or Mrs. 500 Points: 597 More actions · Answer 5

Its working now. I bundled all declarations and cursor code together and it runs now.. 🙂

Thanks to all of you for help....

-Vicky