March 11, 2009 at 9:26 am
Hi all, I got this procedure, which executes a procedure to select the data to write to a file. I came across a problem when the underlying procedure returns 0 rows (Which is what we want in that situation). When returning 0 rows, the bcp still executes the procedure, and writes the file to the disk.
We have another process which waits for the file creation and then moves it to the desired locations for further processing. Since the file is empty, it ruins the rest of the process.
Is there a way to not write the file when there are no rows to write to it? Other then just launching the procedure before the action, to see if it returns rows.. because that sounds like a really bad idea to me. I am trying to see if there would be a solution using @@RowCount, or I don't know what kind of bypass I could use.
Thanks in advance,
-- =============================================
-- Author: Jean-François Bergeron
-- Create date: 2009-02-20
-- Description: This procedure is used to generate the old COBOL currency file, it has a fixed format
--and needs to be generated on the C:\SEND\Currency of the server, the fileWatcher will
--then move the file to the desired location for further processing
-- =============================================
CREATE PROCEDURE [dbo].GICCurrencyGenerateFileForCEDNet
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36)
AS
BEGIN
DECLARE @sql VARCHAR(4000)
SET @sql = 'bcp "Exec Enterprise..GICCurrencySelectFileForCEDNet '''
+ @CompanyID
+ ''','''
+ @DivisionID
+ ''','''
+ @DepartmentID
+ '''" queryout c:\Send\Currency\IVDCURS.XMT -c -t"\t" -U%%% -P%%%% -S'
+ @@SERVERNAME
PRINT @sql
EXEC MASTER..xp_cmdshell
END
Edit : Changed the code, there was a call for the procedure which should not have been there.
Cheers,
J-F
March 11, 2009 at 12:19 pm
Is there a way to get the row count from the procedure, in the bcp, and not create the file when there is no row returned?
Cheers,
J-F
March 11, 2009 at 1:45 pm
Hello J-F
Maybe use sp_executesql to get information dynamically if there are any values within the table:
[font="Courier New"]
-- =============================================
-- Author: Jean-François Bergeron
-- Create date: 2009-02-20
-- Description: This procedure is used to generate the old COBOL currency file, it has a fixed format
-- and needs to be generated on the C:\SEND\Currency of the server, the fileWatcher will
-- then move the file to the desired location for further processing
-- =============================================
CREATE PROCEDURE [dbo].GICCurrencyGenerateFileForCEDNet
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36)
AS
BEGIN
DECLARE @SQl VARCHAR(4000)
DECLARE @i BIT
EXECUTE sp_executesql N'
IF EXISTS (SELECT TOP 1 * FROM Enterprise..GICCurrencySelectFileForCEDNet)
SET @i_IN = 1
ELSE
SET @i_IN = 0', N'@i_IN BIT OUTPUT', @i_IN = @i OUTPUT
IF (@i = 1)
BEGIN
SET @sql = 'sqlcmd -S bcp "Exec Enterprise..GICCurrencySelectFileForCEDNet '''
+ @CompanyID
+ ''','''
+ @DivisionID
+ ''','''
+ @DepartmentID
+ '''" queryout c:\Send\Currency\IVDCURS.XMT -c -t"\t" -U%%% -P%%%% -S'
+ @@SERVERNAME
PRINT @SQL
EXEC MASTER..xp_cmdshell
@sql
END
END
[/font]
Another way may be to script a "sqlcmd" execution which checks if there are values available and only if exports them.
Greets
Flo
March 11, 2009 at 2:04 pm
Thanks for your reply Florian,
For the first idea, which implies a bit variable, it would be a good idea, but I can't count the rows from a procedure if I do not execute it, it is not a table.
I'm trying to not execute the procedure a second time, just to validate that it will return rows.
For the SQLCMD I've never used it, and I will check on it, but I doubt I can tell it not to create the file when there are no rows returned? Anywayz, I will check, but I appreciate if you have other ideas,
Thanks,
Cheers,
J-F
March 11, 2009 at 2:17 pm
Hello J-F
If there is no possibility to request the data existence since procedure execution you may have to use the SQLCMD. It's just a little command line SQL tool which is supplied by MS with SQL Server.
Here a little example to ECHO a "Hello World" only if the AdventureWorks.HumanResources.Employee table has any values:
DECLARE @cmd NVARCHAR(1000)
SET @cmd = 'sqlcmd -S garfield159\sql2k8 -E -Q"IF EXISTS (SELECT TOP 1 * FROM AdventureWorks.HumanResources.Employee) EXECUTE xp_cmdshell N''ECHO Hello World''"'
EXECUTE xp_cmdshell @cmd
You have to enclose your BCP command within a second SQL script which will be executed by SQLCMD the BCP will only be executed if any data are available.
Hope this helps!
Greets
Flo
March 11, 2009 at 2:23 pm
Nice, I did not know you could do that, but that does not help in this scenario. Can you do it using a procedure?
You are still using a count from a table, which would work, but my procedure returns the select and I need to rely on the returned data to see if there is any.
I cannot do :
"if exists (Select top 1 * from (exec proc)) bcp c:\Test.txt", since I need to know the result from the query.
Maybe I just don't understand what you're trying to show me,
Thanks for your time,
Cheers,
J-F
March 11, 2009 at 2:33 pm
[font="Verdana"]I think the "if exists" needs to go in the procedure that calls BCP. So you would change your logic as follows.
Your current procedure will be something like this:
...
exec ProcedureThatRunsBCP
...
...and you need to change it so that it has some additional logic:
...
if exists (select top 1 1 from ResultSetBeingBCPd)
exec ProcedureThatRunsBCP
...
[/font]
March 11, 2009 at 2:38 pm
Okay, now I understand. Sorry, it's a little late here 😉
Proposal if the data to be exported are not too much:
* Insert the result of your procedure into a global temp table
* Use the BCP to export the global temp table
* Enclose this with the SQLCMD example
If you need example, let me know.
Greets
Flo
March 11, 2009 at 2:56 pm
Another way (which seems more simple in my oppinion):
* Use the BCP, no matter if any data are availale but to another directory
* After that use xp_cmdshell again to execute a java-script or vb-script which checks if the file size is zero. If yes, delete the file otherwise move it to the destination directory.
Edited:
Sorry to the Admins: Sure the script also may be perl, php or power-shell :hehe:
Greets
Flo
March 12, 2009 at 7:19 am
Nice... I didnt think of the global temp table to do that. I think it's a great idea, since I don't think SQL Server should be too much in the file handling business... Everytime I got a file to create, I just select the data for the VB application, and they do the thing, I prefer it that way. But in this scenario, we cannot do it in VB, so I had to do it.
Thanks for the info, I will test these options,
Cheers,
J-F
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply