Bulk insert syntax problem - 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 2000 Forums

SQL Server Development (2000)

Bulk insert syntax problem

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-18 : 14:10:40

Can anyone please advise why this bulk insert is not working correctly :

select @path = '\\jyr2003scse\d$\Data\Iris6\Data\HOSTIN\000' + cast(@storeno as char(3)) + '\Dayadjst\dayadjst.001' 
select @task = 'BULK INSERT [#sta] from' + @path
Exec(@task)

it is always coming up with an error :

Could not find stored procedure 'B'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-18 : 14:30:27

Run PRINT @task and post the results here.

Tara Kizer

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-19 : 04:22:58

Thanks Tara I don't understand though as both these variables are declared as varchar .

Here are the results:

print @task
gives me:
B
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'B'.

print @path
gives me what I need just without the Bulk insert:
\\jyr2003scse\d$\Data\Iris6\Data\HOSTIN\000114\Dayadjst\dayadjst.001

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 05:05:02

No! DECLARE @Task AS VARCHAR
Yes! DECLARE @Task AS VARCHAR(1000)

In the future when requesting more assistance, please post ALL relevant code.

Peter Larsson
Helsingborg, Sweden

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-19 : 05:43:49

Apologies Peso but this is giving me the same problem as per the below code:

DECLARE @pstore as varchar(1000)
DECLARE @path as varchar(1000)
DECLARe @task as varchar(1000)
DECLARE @storeno as int
DECLARE storecursor CURSOR FOR 
	Select storenum From		-- store number count is 43 required
	retailchoisestore WHERE storenum <> 300





OPEN storecursor
FETCH NEXT FROM storecursor INTO  @storeno
WHILE @@fetch_status = 0
BEGIN
create table #sta (adj [char] (127))
select @path = '\\jy2003012\d$\Data\Iris6\Data\HOSTIN\000' + cast(@storeno as char(3)) + '\Dayadjst\dayadjst.001' 
select @task = 'BULK INSERT [#sta] from' + @path
print @task
print @path
Exec(@task)

delete #sta from #sta t, Stocktest s
where 	s.store = @storeno
	and s.type collate database_default = substring(t.adj,6,3)
	and s.ean collate database_default = substring(t.adj,10,13)
	and s.tdate = cast(dateadd(dd,(cast(substring(t.adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime)
	and s.ttime = substring(t.adj collate database_default,100,6)


select * from #sta
if @@rowcount <> 0
begin 
insert into Stocktest
	Select
	'103' as Store,
	substring(adj,6,3) Type,
	substring(adj,10,13) EAN,
	Qty = 		case -- Check if value is minus
				when substring(adj,35,1) = '-' 
				then cast(substring(adj,24,6) as int) * -1
				else cast(substring(adj,24,6) as int)
			end,
	Retail = 	case -- Check if value is minus
				when substring(adj,46,1) = '-' 
				then cast(substring(adj,37,9) as money) * -1
				else cast(substring(adj,37,9) as money)
			end,
	Cost = 		case -- Check if value is minus
				when substring(adj,57,1) = '-' 
				then cast(substring(adj,48,9) as money) * -1
				else cast(substring(adj,48,9) as money)
			end,
	substring(adj,59,8) A,
	substring(adj,68,3) B,
	substring(adj,72,20) C,
	cast(dateadd(dd,(cast(substring(adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime) tDate,
	substring(adj,100,6) tTime, null

from #sta
end
ELSE 
begin
select @pstore = 'We have not received yesterdays daily adjustment files for store: ' + cast(@storeno as varchar) 
EXEC master..xp_sendmail @recipients = 'JimTho@Lddd.co.uk ', 
			
			--@copy_recipients = 'Matthew@dde.co.uk ; Tim@ddd.co.uk ; Sara@dde.co.uk ',
			@subject = 'Daily Adjustment Problem',
			@message = @pstore   
														,

			@separator = '            ',
			@attach_results = 'false', @width = 80
end

DROP TABLE #sta
FETCH NEXT FROM storecursor INTO @storeno
END
CLOSE storecursor
DEALLOCATE storecursor

Go

I am getting the following error messages when I run the above one for each store from the retailchoisestore table :

B
\\jy2003012\d$\Data\Iris6\Data\HOSTIN\000114\Dayadjst\dayadjst.001
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'B'.

(0 row(s) affected)

(0 row(s) affected)

Mail sent.

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-19 : 05:50:34

Instead of Exec u must use sp_executesql

Ref : http://www.sommarskog.se/dyn-search.html

select @task = 'BULK INSERT [#sta] from' + @path
Exec(@task) ---Problem is here u must replace this exec with sp_executesql

Read about dynamic sql in above mentioned site

Madhu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 05:53:00

What are you doing?
I told you to change the declaration
~~declare @task varchar~~
to
declare @task varchar(1000)

You change all other declarations, but not the one I asked you to change.

What will you do now?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 05:56:43

quote:
Originally posted by madhuotp

Instead of Exec u must use sp_executesql

No, you must not.
sp_executesql has some advantages over EXEC, but that is not relevant in this case.

Peter Larsson
Helsingborg, Sweden

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-19 : 05:59:34

hi peso,

with due respect, what i would say is... at least let him try once.. why i said this is because see the error

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'B'.

Madhu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 06:02:24

Yes, let him try and waste more time, when the solution is so easy.
Look at the declaration of the @task variable and you will understand me.

Peter Larsson
Helsingborg, Sweden

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-19 : 06:04:03

ya.. i got the point... i saw that... sorry...
even in the print statement it is clear... my appology again..

Madhu

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-19 : 06:16:01

sorry to waist your time Peso, I have changed the above code to look like it is now, it is getting better but I am now getting the following error message:

BULK INSERT [#sta] from\\jy2003012\d$\Data\Iris6\Data\HOSTIN\000114\Dayadjst\dayadjst.001
\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000114\Dayadjst\dayadjst.001
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '\'.

(0 row(s) affected)

(0 row(s) affected)

Mail sent.

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-19 : 06:19:23

after from gap is not there..

from \\jy2003012

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 06:20:27

What does Books Online (the excellent help file for T-SQL) tell you about the syntax for the command?
You need

1) single quotes around the path
2) space after the FROM keyword.

BULK INSERT [#sta] from '\\jy2003012\d$\Data\Iris6\Data\HOSTIN\000114\Dayadjst\dayadjst.001
\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000114\Dayadjst\dayadjst.001'

That would mean for you;
~~select @task = 'BULK INSERT [#sta] from' + @path~~
select @task = 'BULK INSERT [#sta] from ''' + @path + ''''

Peter Larsson
Helsingborg, Sweden

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-19 : 09:25:43

Thanks for your help Peso sorry to upset you ,will endeavour to consult books online before asking stupid questions,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 09:36:15

Many questions can be easily solved by consulting Books Online. This helpfile has all the syntax and even some examples to illustrate the usage of any command.

The only time a question is stupid is when it's obvious that poster has not bother to take the time to look in the Books Online first.

Many questions here is not about syntax.
They are about algorithms, which in most cases can't be found in any books because of each and every companies business rules.

I'm happy we were able to assist you. Does the query work for you now?

Peter Larsson
Helsingborg, Sweden

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-19 : 10:19:09

Yes it does work thanks again Peso, apologies for being lazy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources