locked
generate a SQL query output as an excel file using task scheduler RRS feed

  • Question

  • Hello,

    I have written this post before but doesn't get a satisfied answer.

    I have one query that changes every semester and I want its output as excel file. Year code and term code changes every semester.So I have to pass parameters for both fields. I was trying to execute this query using bcp command but bcp doesn't work for this query. Then I tried import /export wizard but it doesn't work for me either. Do you have any idea to apply another way and get an output in excel file every month by executing this query through task scheduler.

    I have tried through following ways:

     (1) 

    SET QUOTED_IDENTIFIER ON

    GO

    declare @sql nvarchar(MAX)
    declare @server varchar(100)
    declare @email varchar(50)
    declare @query varchar(MAX)
    declare @cnt int
    DECLARE @cmd NVARCHAR(400)
    DECLARE @filepath AS NVARCHAR(500)='c:\backup\vihar.txt'
    declare @filename as nvarchar(500)='vihar.txt' 
    DECLARE  @return int
    set @cnt=0
    set @server = 'WIN-KQ6RVLJCTHP\TESTMSSQLSERVER'
    set @query='SELECT     
             distinct [TmsEPrd].[dbo].[NAME_MASTER].[EMAIL_ADDRESS]
        FROM [TmsEPrd].[dbo].[name_master],   
             [TmsEPrd].[dbo].[STUDENT_CRS_HIST]  
       WHERE ( [TmsEPrd].[dbo].[name_master].[id_num] = [TmsEPrd].[dbo].[STUDENT_CRS_HIST].[id_num] ) and  
             ( ( [TmsEPrd].[dbo].[STUDENT_CRS_HIST].[yr_cde] = ''2016'') AND  
             ( [TmsEPrd].[dbo].[STUDENT_CRS_HIST].[trm_cde] = ''20'') ) AND
    ([TmsEPrd].[dbo].[STUDENT_CRS_HIST].[STUD_DIV] = ''UG'') AND [TmsEPrd].[dbo].[name_master].[EMAIL_ADDRESS] is not null
    order by [TmsEPrd].[dbo].[name_master].[EMAIL_ADDRESS]'
    exec (@query)
    while (@cnt>=0)
    begin
       SET @cmd =   @query + ' >>' + @FilePath

                EXEC @return = MASTER..xp_cmdshell
                  @cmd,
                  no_output

     set @cnt = @cnt+1
    end;
    IF (@return = 0)
       PRINT 'Success'+ @cmd +@filepath 
    ELSE
       PRINT 'Failure' + @cmd +@filepath

    (2)bcp

    SET QUOTED_IDENTIFIER ON
    GO

    declare @sql nvarchar(MAX)
    declare @server varchar(100)
    declare @email varchar(50)
    declare @query varchar(MAX)
    declare @cnt int
    DECLARE @cmd NVARCHAR(400)
    DECLARE @filepath AS NVARCHAR(500)='c:\backup\vihar.txt'
    declare @filename as nvarchar(500)='vihar.txt' 
    DECLARE  @return int
    set @cnt=0
    set @server = 'WIN-KQ6RVLJCTHP\TESTMSSQLSERVER'
    set @query='SELECT     
             distinct [TmsEPrd].[dbo].[NAME_MASTER].[EMAIL_ADDRESS]
        FROM [TmsEPrd].[dbo].[name_master],   
             [TmsEPrd].[dbo].[STUDENT_CRS_HIST]  
       WHERE ( [TmsEPrd].[dbo].[name_master].[id_num] = [TmsEPrd].[dbo].[STUDENT_CRS_HIST].[id_num] ) and  
             ( ( [TmsEPrd].[dbo].[STUDENT_CRS_HIST].[yr_cde] = ''2016'') AND  
             ( [TmsEPrd].[dbo].[STUDENT_CRS_HIST].[trm_cde] = ''20'') ) AND
    ([TmsEPrd].[dbo].[STUDENT_CRS_HIST].[STUD_DIV] = ''UG'') AND [TmsEPrd].[dbo].[name_master].[EMAIL_ADDRESS] is not null
    order by [TmsEPrd].[dbo].[name_master].[EMAIL_ADDRESS]'
    exec (@query)

    --set @sql='bcp '+@query+' queryout
    -- c:\backup\roipa.txt -c -t, -T -r\r\n -S WIN-KQ6RVLJCTHP\TESTMSSQLSERVER'

    Any new ideas or any suggestions would be appreciated.

    Thanks


    Virtual Reality


    • Edited by VR16 Friday, September 16, 2016 6:13 PM
    Friday, September 16, 2016 6:12 PM

Answers

  • Good day,

    In first glance, I don't see any issue with the code as it is, it should not get more then 3 nesting level.

    * How do exactly execute the the bcpClassFile SP? Are you execute it from another procedure or trigger for example?

    There a simple way to monitor the issue using the SSMS debug tool, assuming that you are using SSMS to execute the procedure.

    1. select with your mouse the command that you want to execute in the SSMS. For example the query:

    EXECUTE dbo.bcpClassFile 2016,'20'
    GO

    2. instead of execute it using the button "Execute" or using F5, click on the button "debug". This will start execute using debug mode which will give you the option to execute line by line and examine if the is any loop.

    3. In order to find nesting level simply use F11 to continue the debugger to the next line (the use of F11 will take you to the next line and if the next line is in nesting element then it will take you inside the nesting level element. Follow the execution and simply debug your case.

    * I don't see why you change the default and use using "SET QUOTED_IDENTIFIER ON". Maybe it's related to how you execute the procedure...


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, September 17, 2016 3:19 AM

All replies

  • Here is a full sample for you:

    (you need to change the database name at the beginning and inside bcp command).

    Use mydb1 --the DB name
    go
    
    If object_id('dbo.Classtests','U') is not null
    drop table dbo.Classtests;
    go--
    
    create table Classtests (ClassID  int,  DepartID int, year_code int, trm_code int)
    Insert into Classtests (ClassID,DepartID,year_code ,trm_code )
    values(1,1,2015,20),(2,1,2017,20),(3,1,2016,20),(4,1,2016,20),(5,1,2016,20),(6,1,2016,20)
    insert into Classtests   
    Values (7,1,2017,20),(8,1,2017,20),(9,1,2017,20),(10,1,2017,20)
    
    
    If object_id('dbo.usp_Classtest','P') is not null
    drop procedure dbo.usp_Classtest;
    go
    create procedure usp_Classtest 
    @year_code int
    ,@trm_code varchar(100)
    As
    Begin
    Select * from Classtests
    WHERE year_code=@year_code and trm_code= @trm_code;
    End
    Go
    If object_id('dbo.bcpClassFile','P') is not null
    drop procedure dbo.bcpClassFile;
    go
    
    Create PROCEDURE bcpClassFile 
     @year_code int
    ,@trm_code varchar(100)
    AS
      BEGIN
    	DECLARE  @sql VARCHAR(8000)=''	
    	SET @sql = 'bcp "EXEC mydb1..usp_Classtest ''' + CONVERT(VARCHAR(4), @year_code) + ''',''' + @trm_code +  '''" Queryout "c:\temp\test\TheFile'
    				 + @trm_code + CONVERT(VARCHAR(4), @year_code)
    				 + '.txt " -c -t"\t" -T -S'
    				 + @@SERVERNAME
    	EXEC master..xp_cmdshell  @sql
      END
    
    
      --Test 
      exec bcpClassFile 2016,'20'
    
      --Check file in  c:\temp\test\TheFile202016.txt

    Friday, September 16, 2016 7:08 PM
  • So what exactly changes every semester? Or is it month - you refer to both. Can you post examples of dates on which you want this logic to run and what values in your query change based on those dates.

    And I suggest you help yourself - by removing any extra code that isn't used or useful. Start with all the extra variables that aren't used. Since you refer to the task scheduler, does that mean you are using sql server express?  The version and edition of sql server is something that you should ALWAYS identify when posting since it usually is relevant.

    Friday, September 16, 2016 7:08 PM
  • And here is your query cleaned up.  I omitted the use of distinct - because I doubt it is needed in this version (though it might have been in yours). You might need to add it back.  Notice that it does not rely on joining since you don't really need anything in the history table (and which is probably why you needed distinct). Also notice the use of aliases (to simplify& shorten the code) and the removal of the database name from your tables. Why do that? Because this lets you run the query against ANY database that has the correct schema - just set the executing connection to use the correct server instance and database. I also made some assumptions about datatypes - specifically that your "code" columns are numbers and not strings. And do you REALLY save anything by using the name yr_cde rather than year_code </Rant>?

    select master.EMAIL_ADDRESS
    from dbo.name_master as master
    where exists 
       (select * from dbo.STUDENT_CRS_HIST as hist 
       where master.id_num = hist.id_num 
       and hist.yr_cde = 2016
       and hist.trm_cde = 20
       and hist.STUD_DIV = 'UG')
    where master.EMAIL_ADDRESS is not null
    order by master.EMAIL_ADDRESS;
    
    

    Friday, September 16, 2016 7:32 PM
  • Thanks for quick reply and efficient reply. I just run this query and it is working for me.

    Yes, Every semester we have year and term change.

    For example for fall semester term code is:20

    Spring semester:30

    summer:10

    And year code is changed according to every year.

    It depends on student get enrolled.

    We have one google groups that sending emails to every student. So we want a list of students which are a new to that semester.

    No I am not using SQL Server Express, I just want to make it simple. So anyone can use this who doesn't have knowledge of SQL Server.So I am thinking about task scheduler.


    Virtual Reality

    Friday, September 16, 2016 7:59 PM
  • I could run on my test server but I am trying to run on production it gets following error

    Msg 217, Level 16, State 1, Procedure bcpClassFile, Line 11
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
    output
    NULL

    I couldn't generate an excel file on my production environment

    I get output as a null

    Here is code.

    My code is failing on my production server

    USE [TmsEPrd]
    GO
    /****** Object:  StoredProcedure [dbo].[bcpClassFile]    Script Date: 9/16/2016 3:29:07 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    Alter PROCEDURE [dbo].[bcpClassFile] 
     @year_code varchar(100)
    ,@trm_code varchar(100)
    AS
     BEGIN
    DECLARE  @sql VARCHAR(8000)=''
    declare @result int
    SET @sql = 'bcp "EXEC TmsEPrd..usp_Classtest ''' + @year_code + ''',''' + @trm_code +  '''" Queryout " D:\TheFile'
    + @trm_code + @year_code
    + '.txt " -c -t"\t" -T -S'
    + @@SERVERNAME
    --EXEC master..xp_cmdshell  @sql
    EXEC @result = master..xp_cmdshell @sql
    IF (@result = 0)
       PRINT 'Success' 
    ELSE
       PRINT 'Failure' 

      END
      Go

      --Test 
      exec bcpClassFile '2016','20'

      --Check file in  c:\temp\test\TheFile202016.txt

    If you have any idea, what's causing this error , please let me know.

    Thanks


    Virtual Reality


    • Edited by VR16 Friday, September 16, 2016 10:25 PM
    Friday, September 16, 2016 9:18 PM
  • Thanks.

    Doesn't matter with yr_cde name.


    Virtual Reality

    Friday, September 16, 2016 9:59 PM
  • Good day,

    In first glance, I don't see any issue with the code as it is, it should not get more then 3 nesting level.

    * How do exactly execute the the bcpClassFile SP? Are you execute it from another procedure or trigger for example?

    There a simple way to monitor the issue using the SSMS debug tool, assuming that you are using SSMS to execute the procedure.

    1. select with your mouse the command that you want to execute in the SSMS. For example the query:

    EXECUTE dbo.bcpClassFile 2016,'20'
    GO

    2. instead of execute it using the button "Execute" or using F5, click on the button "debug". This will start execute using debug mode which will give you the option to execute line by line and examine if the is any loop.

    3. In order to find nesting level simply use F11 to continue the debugger to the next line (the use of F11 will take you to the next line and if the next line is in nesting element then it will take you inside the nesting level element. Follow the execution and simply debug your case.

    * I don't see why you change the default and use using "SET QUOTED_IDENTIFIER ON". Maybe it's related to how you execute the procedure...


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, September 17, 2016 3:19 AM