can not work with xp_cmdshell - 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

can not work with xp_cmdshell

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-28 : 02:18:10

have tried this procedure but its not working can you guide me.

Create procedure 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(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
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

Exec usp_ImportMultipleFiles 'C:\files\','a.txt', 'Account'

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-28 : 02:21:48

getting error

(2 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
BULK INSERT "Account" FROM "C:\files\The system cannot find the file specified."
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = ":\n")
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 02:37:03

is the file on your local machine or server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-28 : 02:41:56

local machine

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 02:46:49

why are you putting "" across table name?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-28 : 03:01:16

(2 row(s) affected)

(1 row(s) affected)
After removing also its giving the same error

(1 row(s) affected)
BULK INSERT Account FROM "C:\files\The system cannot find the file specified."
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = ":\n")
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

(1 row(s) affected)

(1 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 03:04:07

is this your filename?

The system cannot find the file specified

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-28 : 03:17:27

a.txt,b.txt,c.txt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 03:38:17

but where are you specifying it in above statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnasz
Posting Yak Master

101 Posts

Posted - 2010-11-28 : 05:06:17

while executing i am specifying the file name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 07:51:45

in posted statement i cant find a.txt,b.txt etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-28 : 08:25:23

Remove the quotes and replace with the single quote, and I am pretty sure you don't want the quotes around the table name...


	set @Query ='BULK INSERT '+ @Tablename + ' FROM '''+ @Filepath+@Filename+'''
	WITH (FIELDTERMINATOR = '','',ROWTERMINATOR ='':\n'')'

I created the directories, and aside from executing the inserts, this works for me to produce a valid Bulk Insert statement like this


BULK INSERT Account FROM 'C:\files\a.txt'
	WITH (FIELDTERMINATOR = ',',ROWTERMINATOR =':\n')

Except, I think when you are passing 'a.txt', I think you mean to pass '*.txt' to get the full list, otherwise, it will only find a.txt

If i change to pass '*.txt' I get both bulk inserts which parse correctly.


BULK INSERT Account FROM 'C:\files\a.txt'
	WITH (FIELDTERMINATOR = ',',ROWTERMINATOR =':\n')

BULK INSERT Account FROM 'C:\files\b.txt'
	WITH (FIELDTERMINATOR = ',',ROWTERMINATOR =':\n')

with two files in the directory of a.txt and b.txt

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources