May 14, 2003 at 9:02 am
I am using the bcp out function to extract a flat file from a SQL table and put it onto hd. I am executing a query first to meet some conditions but if not met it creates me a flat file anyhow but empty.
Is there a parameter which inhibits creating an empty file?
Thanks mipo
May 14, 2003 at 9:18 am
If I where you I would run the bcp command from a T-SQL script with the xp_cmdshell extended procedure.
With t-SQL you can manage the result of the query and then you can pass that query to the string that executes the bcp.
For example:
use master
go
DECLARE @bcp AS VARCHAR(300)
/* Here you test that your query returns values */
IF (SELECT COUNT(*) FROM sysdatabases) > 0
BEGIN
SELECT @bcp = 'BCP '+''''+'SELECT * FROM sysdatabases'+''''+' QUERYOUT etc....
EXEC xp_cmdshell @bcp
END
May 15, 2003 at 5:36 am
Thanks but If you have no return values the sysdatabases still have the last value stored and therefore it will be executed.
Is there another work around in this construct:
SELECT @Year = CONVERT(varchar(4), @trxYearMonthStart, 120)
SELECT @Month = RIGHT(CONVERT(varchar(7), @trxYearMonthStart, 120),2)
SELECT @cmd = 'BCP "SELECT * FROM ' + @TableToBeCleaned + ''
SELECT @cmd = @cmd + ' WHERE '+ @SelectedColumn + ' BETWEEN '
print @cmd
SELECT @cmd = @cmd + '''' + CONVERT(varchar(10),@trxYearMonthStart,120) + ''' and ''' + CONVERT(varchar(10),@trxYearMonthEnd,120) + ''''
print @cmd
SELECT @cmd = @cmd + 'AND NOT EXISTS (Select * from DBCleanerHist Where TableName = ''' + @TableToBeCleaned + ''' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')'
print @cmd
SELECT @cmd = @cmd + ' " QUERYOUT ' + @DBCleanerBackUpPath+'\' +@TableToBeCleaned +'_'+ @Year + '_' + @Month + '.txt '
SELECT @cmd = @cmd + ' -c -C1250 -S -Uopms -Psmpo'
EXEC master.dbo.xp_cmdshell @cmd
The subquery checks first in DBCleanerHist if it already has been extracted and if so do not create an empty file and overwrite an existing file.
mipo
May 15, 2003 at 7:06 am
Yes I know. It an example to show you that you can use xp_cmdshell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply