SQL 2000 Scheduled Jobs Max Character

Question

I'm trying to schedule a long T-SQL script. However, the script gets cut off when pasted into the text box. I googled the problem and there is a 3200 character limit.

What do you recommend I do to solve this? Create a stored procedure and run that as a scheduled job? Is there a better option? If it helps, here is the script.

INSERT INTO CheltonCustomizations..SOBacklogAudits (SoNo,
                                                       SoRev,
                                                       SysItemNo,
                                                       UserItemNo,
                                                       Release,
                                                       OrderDate,
                                                       DueDate,
                                                       PartNo,
                                                       PartRev,
                                                       OrderQty,
                                                       ShippedQty,
                                                       ShippedNotInvoicedQty,
                                                       InvoicedQty,
                                                       ProdCl,
                                                       [Group],
                                                       NetAmount,
                                                       SorelsIdentityColumn,
                                                       BacklogDate)
                 SELECT SOR.fsono,
                        SOM.fsorev,
                        SOR.fenumber,
                        SOR.finumber,
                        SOR.frelease,
                        SOM.[forderdate],
                        SOR.fduedate,
                        SOR.fpartno,
                        SOR.fpartrev,
                        SOR.forderqty,
                        SOR.fshipbook + SOR.fshipbuy + SOR.fshipmake,
                        COALESCE (
                           DBO.GETSHIPPEDNOTINVOICEDQTY (
                              SOR.fsono + SOR.finumber + SOR.frelease),
                           0),
                        SOR.finvqty,
                        SOI.fprodcl,
                        SOI.fgroup,
                        (SOR.forderqty - SOR.finvqty
                         - COALESCE (
                              DBO.GETSHIPPEDNOTINVOICEDQTY (
                                 SOR.fsono + SOR.finumber + SOR.frelease),
                              0))
                        * SOR.funetprice,
                        SOR.identity_column,
                        getdate ()
                        --CONVERT (DATETIME, '02/09/2009')
                   FROM SORELS SOR
                        INNER JOIN SOITEM SOI
                        ON SOR.finvqty <
                              SOR.forderqty
                              - COALESCE (
                                   DBO.GETSHIPPEDNOTINVOICEDQTY (
                                      SOI.FSONO + SOR.finumber + SOR.frelease),
                                   0)
                              - 0
                           AND SOR.finvqty <
                                 SOR.forderqty
                                 - COALESCE (
                                      DBO.GETSHIPPEDNOTINVOICEDQTY (
                                         SOR.fsono + SOI.FINUMBER + SOR.frelease),
                                      0)
                                 - 0
                           AND SOR.FSONO = COALESCE (SOI.FSONO, SOI.FSONO)
                           AND SOR.FINUMBER = SOI.FINUMBER
                        INNER JOIN SOMAST SOM
                        ON SOR.finvqty <
                              SOR.forderqty
                              - COALESCE (
                                   DBO.GETSHIPPEDNOTINVOICEDQTY (
                                      SOM.FSONO + SOR.finumber + SOR.frelease),
                                   0)
                              - 0
                           AND SOR.FSONO = COALESCE (SOM.FSONO, SOM.FSONO)
                           AND SOI.FSONO = SOM.FSONO
                  WHERE SOM.FSTATUS = 'OPEN' AND FMASTERREL = COALESCE (0, 0)
                        AND SOM.forderdate >=
                              CONVERT (DATETIME, '01/01/2002')
                        AND SOR.forderqty >
                              0 + SOR.finvqty
                              + COALESCE (
                                   DBO.GETSHIPPEDNOTINVOICEDQTY(SOR.fsono
                                                                + SOR.finumber
                                                                + SOR.frelease),
                                   0)
                        AND SOR.finvqty <
                              SOR.forderqty
                              - COALESCE (
                                   DBO.GETSHIPPEDNOTINVOICEDQTY(SOR.fsono
                                                                + SOR.finumber
                                                                + SOR.frelease),
                                   0)

Answer 1

As you mentioned, I'd use a stored procedure, call that from the job.

You also have the advantage that you can update the code but leave the job untouched

Answer 2

make it a stored procedure. I'd never have any job run anything other than a stored procedure. you can keep adding logic etc. to the procedure and not mess with the job at all...

Answer 3

Agree with above - definitely put it in a stored proc and call it from the job.

It's generally good practice to put all code in stored procedures. This allows you to easily call a stored procedure by name later on (for use in either a job or DTS package, for example).

If you ever had to create multiple jobs or DTS packages that call the same stored procedure, and you were required to make changes to that stored procedure, you would only need to make the change once.

If you simply copied the code into the job/DTS package, you would need to go into each and every job to update your code if any changes were ever required.