February 15, 2015 at 8:47 am
Hi all,
I have a Job that creates .CSV files at predetermined intervals (the delimiter is a tabstop) and every 5 minutes I have to retrieve These files one at a time and Import them into the relevant database.
My solution was to create a list of the files and insert this list into a temporary table where a Cursor would extract each filename one at a time and insert that Name into a BCP Statement. The BCP Statement is constructed using dynamic SQL.
Here the Cursor:
create table #filelist(filename nvarchar(100))
insert into #filelist exec xp_cmdshell 'dir C:\BCP_Test /A-D /B'
declare @filename nvarchar(100)
declare @path nvarchar(100)
declare @bcp_open nvarchar(100)
declare @params nvarchar(100)
declare @filepath nvarchar(100)
declare @bcpcmd nvarchar(max)
set @bcp_open = 'bcp HumanResources.Employee2 in '
set @path = 'C:\BCP_Test\'
set @params = ' -T -d AdventureWorks2014 -S .\SQLSERVER -F2 -n -k'
declare curBulkImport cursor
forward_only static read_only
for
select filename
from #filelist
where filename is not null
order by filename asc;
open curBulkImport;
while @@FETCH_STATUS = 0
begin
fetch next from curBulkImport into @filename
set @filepath = @path + @filename
set @bcpcmd = (@bcp_open + @filepath + @params)
exec xp_cmdshell @bcpcmd
end;
deallocate curBulkImport
drop table #filelist
The code seems to work but although there are around 50000 lines in the .csv file, None of the lines are inserted.
Can anyone help? I am losing more hair with each execution..... 🙂
Regards
Kev
February 20, 2015 at 10:13 am
Hi..
I think it's because you need to fetch from the cursor before you get the @@FETCH_STATUS. If you run the below you'll get some commands printed out:
declare curBulkImport cursor
forward_only static read_only
for
select [filename]
from #filelist
where [filename] is not null
order by [filename] asc;
open curBulkImport;
fetch next from curBulkImport into @filename
while @@FETCH_STATUS = 0
begin
set @filepath = @path + @filename
set @bcpcmd = (@bcp_open + @filepath + @params)
--exec xp_cmdshell @bcpcmd
print @bcpcmd
fetch next from curBulkImport into @filename
end;
deallocate curBulkImport
drop table #filelist
However I can't help but think this would be better handled in Powershell?
February 20, 2015 at 6:41 pm
Jim Mackenzie (2/20/2015)
However I can't help but think this would be better handled in Powershell?
Do you happen to have a working example of such a thing? I'd love to see one that works from womb to tomb that can be called from SQL Server (not SSIS) without the use of xp_CmdShell.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2015 at 6:44 pm
kevaburg (2/15/2015)
Can anyone help? I am losing more hair with each execution..... 🙂Regards
Kev
I think Jim hit the nail on the head above.
Shifting gears a bit, is there a reason why you need to use BCP instead of BULK INSERT? I ask because BULK INSERT doesn't use xp_CmdShell to do it's work. I'm certainly no xp_CmdShell-phobe (I use it for a wealth of things) but it does make some folks squirm.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2015 at 9:13 pm
Jeff Moden (2/20/2015)
Jim Mackenzie (2/20/2015)
However I can't help but think this would be better handled in Powershell?Do you happen to have a working example of such a thing? I'd love to see one that works from womb to tomb that can be called from SQL Server (not SSIS) without the use of xp_CmdShell.
Nope! haha. I'll have a play around when it's not 0410 in the morning and I'm awake due to being called out! Gotta love being the on call SQL Developer 🙂
February 24, 2015 at 4:51 am
Hi Folks!
Firstly, thanks for all the Input. I decided to try the same procedure as a Loop using BULK INSERT.
It isn't perfect, but it works!
create procedure sp_Ladung_OptionGroesseKollektionStatistik
as
--Create a holding table for the filelist
create table #filelist(pos int identity(1,1), name nvarchar(max))
insert into #filelist exec xp_cmdshell 'dir \\cbr-bi\Daten Exporte Schnittstellen\ERP Sub Applikationen\NOS\OptionGroesseKollektionStatistik\ /A-D /B'
--select * from #filelist;
--DECLARATIONS
declare @fname nvarchar(100)--Name of the CSV file
declare @path nvarchar(100)--Abolute path to the folder containing the files
declare @open nvarchar(100)--Starting statement for the Bulk Insert
declare @params nvarchar(100)--Bulk Insert parameters
declare @filepath nvarchar(100)--Complete abolute path including the filename
declare @sql nvarchar(max)--The SQL statement for the Bulk Insert
declare @fcount int--How any files are in the source folder?
declare @i int--Required for the loop
declare @move varchar(255)--Used for the Windows batch statement
--CONSTRUCT THE DYNAMIC SQL
set @open = 'bulk insert [Test_KBu].[v5].[IF_OptionGroesseKollektionStatistik] from '''
set @path = '\\cbr-bi\Daten Exporte Schnittstellen\ERP Sub Applikationen\NOS\OptionGroesseKollektionStatistik\'
set @params = ''' with (firstrow = 2, datafiletype = ''widechar'');'
set @fcount = (select count(*) from #filelist where name is not null)
set @i = 0
--BULK IMPORT ALL FILES AND MOVE TO THE STORE ON SUCCESSFULL COMPLETION
while (@i < @fcount)
begin try
begin
set @fname = (select name from #filelist where name is not null and pos=@i+1)
set @filepath = @path + @fname
set @sql = @open + @filepath + @params
set @i = @i +1
exec sp_executesql @sql
set @move = 'copy \\cbr-bi\Daten Exporte Schnittstellen\ERP Sub Applikationen\NOS\OptionGroesseKollektionStatistik\' + @fname + ' H:\NOS_Copy_Test\'
exec xp_cmdshell @move
end
end try
--SEND EMAIL ON FAILURE
begin catch
exec msdb..sp_send_dbmail
@profile_name = 'SendMail',
@recipients = 'kevin.burgess@company.de',
@subject = 'CBR-SQLNODE1\ERP_MODULE: NOS Ladelauf Fehlgeschlagen',
@body = 'The NOS Load has encountered a problem. Check the logs for more information.',
@importance = 'high';
end catch
drop table #filelist;
Does anyone have any ideas on how this might be improved upon? Maybe something I have forgotten?
Regards,
Kev
February 24, 2015 at 4:55 am
Jim Mackenzie (2/20/2015)
Jeff Moden (2/20/2015)
Jim Mackenzie (2/20/2015)
However I can't help but think this would be better handled in Powershell?Do you happen to have a working example of such a thing? I'd love to see one that works from womb to tomb that can be called from SQL Server (not SSIS) without the use of xp_CmdShell.
Nope! haha. I'll have a play around when it's not 0410 in the morning and I'm awake due to being called out! Gotta love being the on call SQL Developer 🙂
Hi!
The Speed with which this needed to be rolled out was the single biggest factor in my choice of using XP_CMDSHELL. I am also not a fan of it and prefer to have it turned off.
The solution from my side would be to activte it at the beginning of the execution and deactivate it once the procedure is complete. On the side I will try to convert the whole Job to run in SQLCMD but first I Need to convince the Server admins that I Need the ability to use it.....
Regards,
Kev
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply