an error with the solution script

[pericles1976]

hi,

i use this solution for an import file but give me an error. why?

"Server: Msg 208, Level 16, State 1, Procedure SP_IMPORTARARCHIVOS_BCP, Line 31
Invalid object name 'logtable'."

that is the solution

Create procedure

set quoted_identifier off
go
Create procedure usp_ImportMultipleFilesBCP @servername varchar(128),
@DatabaseName varchar(128), @filepath varchar(500), @pattern varchar(100),
@TableName varchar(128)
as
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
--select * from #y
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='bcp "'+ @databasename+'.dbo.'+@Tablename + '"
in "'+ @Filepath+@Filename+'" -S' + @servername + ' -T -c -r\n -t,'
set @Query = 'MASTER.DBO.xp_cmdshell '+ "'"+ @query +"'"
--print @query
EXEC ( @query)
insert into logtable (query) select @query ?????
end

thanks!!!!
Christian from Argentina..Sud America!!!
drop table #y

Execute the Procedure

Execute the above procedure by passing the parameters shown below.

Example 1: To import all .csv files from folder c:\myimport to a table Account

Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:\Myimport\','*.csv','Account'
Example 2: To import all files from the folder c:\myimport to a table Account

Exec usp_ImportMultipleFilesBCP 'SQL','Bank','c:\Myimport\','*.*','Accoun

[emilg]

The messsage seems to be preety clear:

Invalid object name 'logtable'

probably generated by

insert into logtable (query)...

Did you chech if logtable exists on the database that the procedure is running?

[nosepicker]

And if the table does exist in the database, also check to see if the owner of the table is someone other than 'dbo'. If yes, you will have to reference the table like: owner.logtable

[rmiao]

And ensure you run the sp in correct db.

[chozhanvijay]

hi,


I try to bulk import set of files using the BCP....
The following is the procedure used

set quoted_identifier off
go
ALTER procedure usp_ImportMultipleFilesBCP @servername varchar(128),
@DatabaseName varchar(128), @filepath varchar(500), @pattern varchar(100),
@TableName varchar(128)
as
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
--select * from #y
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='bcp "'+ @databasename+'.dbo.'+@Tablename + '"
in "'+ @Filepath+@Filename+'" -S' + @servername + ' -T -c -r\n -t,'
set @Query = 'MASTER.DBO.xp_cmdshell '+ "'"+ @query +"'"
--print @query
EXEC ( @query)
insert into logtable (query) select @query
end

drop table #y



-----------

I had executed the above script by using the following statement


Exec usp_ImportMultipleFilesBCP 'SYSTEM20','Bank','C:\Myimport\','*.csv*','Account '

------------

The values does not inserted into the Account table but the log table has entries.....

The output i have is as follows


usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL


Kindly help me out of this issue...........

[rmiao]

What did you get with 'print @query'?

[chozhanvijay]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER procedure [dbo].[usp_ImportMultipleFiles] @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(max))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
print @query
exec (@query)
insert into logtable (query) select @query
end

drop table #y

on executing the above procedure by

usp_ImportMultipleFiles 'C:\MyImport\','*.txt','f'

i get the following o/p,


(3 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

(1 row(s) affected)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

(1 row(s) affected)

(1 row(s) affected)


I dont know whats wrong with the syntax.....

[rmiao]

What's @puery?