2

I am trying to execute Stored Procedure on a regular basis using a SQL Job and store the results of this into a .txt file in a folder location.

I have used the BCP command which is like:

DECLARE @command VARCHAR(1000)
SET @command = 'BCP "Exec [DatabaseName].[dbo].[StoredProcedureName] " queryout "D:\In\ErrorDetails'+ '.txt" -c -T -t -k'
EXEC xp_cmdshell @command

I need this to execute only if results of SP are not null.

NickyvV
  • 1,720
  • 2
  • 16
  • 18
Vinay Nandargi
  • 43
  • 6
  • Use EXISTS to see if it returns NULL before calling the proc. https://msdn.microsoft.com/en-us/library/ms188336.aspx – smoore4 Aug 09 '16 at 08:20

1 Answers1

0

You can use temp table to store data generated by SP, if there are any rows - write it to file

USE tempdb

IF OBJECT_ID(N'#MyTempTable') IS NOT NULL
BEGIN
    DROP TABLE #MyTempTable
END

SELECT * INTO #MyTempTable 
FROM OPENROWSET('SQLNCLI', 'Server=HOME\SQLEXPRESS;Trusted_Connection=yes;',
     'EXEC Test.dbo.StoredProcedureName');

IF (SELECT COUNT(*) FROM #MyTempTable) > 0
BEGIN
    DECLARE @command VARCHAR(1000) 
    SET @command = 'BCP "USE tempdb SELECT * FROM #MyTempTable " queryout "D:\In\ErrorDetails'+ '.txt" -c -T -t -k' 
    EXEC xp_cmdshell @command
END
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • 1
    Thanks for the help, i get the below error: OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired". – Vinay Nandargi Aug 09 '16 at 10:04
  • I have no idea about the OLE DB provider. I guess i need to replace SQLNCLl11 with my OLE DB provider name. But where do i get that? – Vinay Nandargi Aug 09 '16 at 10:06
  • I've got `Server=HOME\SQLEXPRESS` what is yours? How do you re-write this part? This problem usually occurs when provider cannot find server. If it is on your local machine you can use `(local)\SQLEXPRESS`. Maybe you got some other instance name. – gofr1 Aug 09 '16 at 10:07
  • SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. This is the error. I have now given the correct Server name – Vinay Nandargi Aug 09 '16 at 10:58
  • Nope, "SQLNCLI11" is the default Linked Server Provider which is used, and i tried ".\" and "MyServerName\SQLEXPRESS". It was of no use. – Vinay Nandargi Aug 09 '16 at 11:23
  • Is there any alternative to SELECT into #MyTempTable apart from OPENROWSET functionality? – Vinay Nandargi Aug 09 '16 at 11:24
  • Another way is to create temp table manually with all columns definition. Here is a nice sample to avoid OPENROWSET http://stackoverflow.com/a/21582634/2893376 and create temp table automatically. – gofr1 Aug 09 '16 at 11:27
  • I tried the second option: Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name '#MyTempTable'. Inside the BCP, the select query does not work! – Vinay Nandargi Aug 09 '16 at 11:43
  • add `USE tempdb ` before `SELECT * FROM...` all `#table_name' tables are stored in `tempdb`. – gofr1 Aug 09 '16 at 11:54