Scheduling Jobs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Scheduling Jobs

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-18 : 16:59:44

This is the create statement for the table to be "inserted into."

/****** Object: Table [dbo].[SOBacklogAudits]   Script Date: 9/18/2008 2:01:33 PM ******/
USE [Customizations];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[SOBacklogAudits] (
[fsono] char(6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fsorev] char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fenumber] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[frelease] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcompany] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fcustno] char(6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[forderdate] datetime NOT NULL,
[fcustpono] char(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[forderqty] numeric(15, 5) NOT NULL,
[AmountShipped] numeric(17, 5) NULL,
[fprodcl] char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fgroup] char(6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShippedNotInvoiced] numeric(20, 5) NULL,
[NetAmount] numeric(38, 9) NULL,
[SorelsIdentityColumn] int NOT NULL,
[identity_column] int IDENTITY(1, 1) NOT NULL)
ON [PRIMARY];
GO

The following code works in the query analyzer:

use [Customizations]
go

INSERT INTO [Customizations].[dbo].[SOBacklogAudits]
       ([fsono],
        [fsorev],
        [fenumber],
        [frelease],
        [fcompany],
        [fcustno],
        [forderdate],
        [fcustpono],
        [forderqty],
        [AmountShipped],
        [fprodcl],
        [fgroup],
        [ShippedNotInvoiced],
        [NetAmount],
        [sorelsIdentityColumn])
   SELECT m2mdata01.dbo.sorels.fsono,
          m2mdata01.dbo.somast.[fsorev],
          m2mdata01.dbo.sorels.fenumber,
          m2mdata01.dbo.sorels.frelease,
          m2mdata01.dbo.somast.[fcompany],
          m2mdata01.dbo.somast.[fcustno],
          m2mdata01.dbo.somast.[forderdate],
          m2mdata01.dbo.somast.[fcustpono],
          m2mdata01.dbo.sorels.[forderqty],
          m2mdata01.dbo.sorels.[fshipbook]
        + m2mdata01.dbo.sorels.[fshipbuy]
        + m2mdata01.dbo.sorels.[fshipmake]
             AS AmountShipped,
          m2mdata01.dbo.soitem.[fprodcl],
          m2mdata01.dbo.soitem.[fgroup],
          COALESCE (m2mdata01.DBO.GETSHIPPEDNOTINVOICEDQTY(m2mdata01.dbo.sorels.fsono
                                               + m2mdata01.dbo.sorels.finumber
                                               + m2mdata01.dbo.sorels.frelease),
                    0
          )
             AS ShippedNotInvoiced,
          (m2mdata01.dbo.sorels.forderqty - m2mdata01.dbo.sorels.finvqty
         - (CASE
               WHEN ( (m2mdata01.dbo.sorels.fshipbook + m2mdata01.dbo.sorels.fshipbuy + m2mdata01.dbo.sorels.fshipmake)
                   - m2mdata01.dbo.sorels.finvqty) <= 0
                 OR COALESCE (m2mdata01.DBO.GETSHIPPEDNOTINVOICEDQTY(m2mdata01.dbo.sorels.fsono
                                                         + m2mdata01.dbo.sorels.finumber
                                                         + m2mdata01.dbo.sorels.frelease),
                              0
                    ) <= 0
               THEN
                  0
               WHEN (m2mdata01.dbo.sorels.fshipbook + m2mdata01.dbo.sorels.fshipbuy + m2mdata01.dbo.sorels.fshipmake)
                  - m2mdata01.dbo.sorels.finvqty <
                       COALESCE (m2mdata01.DBO.GETSHIPPEDNOTINVOICEDQTY(m2mdata01.dbo.sorels.fsono
                                                            + m2mdata01.dbo.sorels.finumber
                                                            + m2mdata01.dbo.sorels.frelease),
                                 0
                       )
               THEN
                  (m2mdata01.dbo.sorels.fshipbook
                 + m2mdata01.dbo.sorels.fshipbuy
                 + m2mdata01.dbo.sorels.fshipmake
                 - m2mdata01.dbo.sorels.finvqty)
               ELSE
                  COALESCE (m2mdata01.DBO.GETSHIPPEDNOTINVOICEDQTY(m2mdata01.dbo.sorels.fsono
                                                       + m2mdata01.dbo.sorels.finumber
                                                       + m2mdata01.dbo.sorels.frelease),
                            0
                  )
            END))
        * m2mdata01.dbo.sorels.funetprice
             AS NetAmount,
          m2mdata01.dbo.sorels.identity_column AS sorelsIdentityColumn
     FROM       m2mdata01.dbo.sorels
             JOIN
                m2mdata01.dbo.somast
             ON m2mdata01.dbo.somast.FSONO = m2mdata01.dbo.sorels.FSONO
          JOIN
             m2mdata01.dbo.soitem
          ON m2mdata01.dbo.soitem.FSONO = m2mdata01.dbo.sorels.FSONO
         AND m2mdata01.dbo.soitem.FINUMBER = m2mdata01.dbo.sorels.FINUMBER
    WHERE m2mdata01.dbo.somast.FSTATUS = 'OPEN'
      AND m2mdata01.dbo.sorels.FMASTERREL = 0
      AND m2mdata01.dbo.somast.forderdate >= CONVERT (DATETIME, '01/01/2002')

However, when I try to schedule and run it as a regular job, I get the following error:

Executed as user: NT AUTHORITY\SYSTEM. The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. [SQLSTATE 07008] (Error 120).  The step failed.

I don't get it. I think I have 15 fields in both the insert and select statements. Can anyone figure out why this works in query analyzer but not as a SQL Server Agent Job?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-09-18 : 17:15:04

nope - there are 15 / 14

greetings
webfred

Planning replaces chance by mistake

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-18 : 17:21:43

I don't see it.

Also, doesn't that error indicate that the numbers should be reversed?

And even so, why does it work in the query analyzer?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-18 : 17:30:15

I count 15/15, but I could be wrong.

Try wrapping the INSERT/SELECT into a stored procedure and then calling the stored procedure in the job step. I've seen jobs have issues with
complex queries in that it thinks there's a syntax error when there isn't. I would definitely consider it a bug.

Let us know if that works.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-09-18 : 17:33:39

Yes - I absolutly agree to Tara's last Post.

And excuse me - it's 15/15 --> I was a little blind.

greetings
webfred

Planning replaces chance by mistake

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-09-18 : 17:38:09

PS:
It was my hasitation to post the solution "use stored procedure" without explaining what's the real problem because i could not...
But Tara is right - i have seen problems with large queries in DTS in SQL Server 2000 too...

greetings
webfred

Planning replaces chance by mistake

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-18 : 17:48:22

I'll give that a try Tara, but this actually works:


INSERT INTO[Customizations].[dbo].[SOBacklogAudits]
       ([fsono],
        [fsorev],
        [fenumber],
        [frelease],
        [fcompany],
        [fcustno],
        [forderdate],
        [fcustpono],
        [forderqty],
        [AmountShipped],
        [fprodcl],
        [fgroup],
        [ShippedNotInvoiced],
        [NetAmount],
        [sorelsIdentityColumn])
   SELECT sorels.fsono,
          somast.[fsorev],
          sorels.fenumber,
          sorels.frelease,
          somast.[fcompany],
          somast.[fcustno],
          somast.[forderdate],
          somast.[fcustpono],
          sorels.[forderqty],
          (sorels.[fshipbook]
         + sorels.[fshipbuy]
         + sorels.[fshipmake])
             AS AmountShipped,
          soitem.[fprodcl],
          soitem.[fgroup],
          COALESCE (dbo.GETSHIPPEDNOTINVOICEDQTY (sorels.fsono + sorels.finumber + sorels.frelease),
                    0
          )
             AS ShippedNotInvoiced,
          (sorels.forderqty - sorels.finvqty
         - (CASE
               WHEN ( (sorels.fshipbook
                     + sorels.fshipbuy
                     + sorels.fshipmake)
                   - sorels.finvqty) <= 0
                 OR COALESCE (dbo.GETSHIPPEDNOTINVOICEDQTY (sorels.fsono + sorels.finumber + sorels.frelease),
                              0
                    ) <= 0
               THEN
                  0
               WHEN (sorels.fshipbook
                   + sorels.fshipbuy
                   + sorels.fshipmake)
                  - sorels.finvqty <
                       COALESCE (dbo.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                                                      + sorels.finumber
                                                                      + sorels.frelease),
                                 0
                       )
               THEN
                  (sorels.fshipbook
                 + sorels.fshipbuy
                 + sorels.fshipmake
                 - sorels.finvqty)
               ELSE
                  COALESCE (dbo.GETSHIPPEDNOTINVOICEDQTY (sorels.fsono + sorels.finumber + sorels.frelease),
                            0
                  )
            END))
        * sorels.funetprice
             AS NetAmount,
          sorels.identity_column AS sorelsIdentityColumn
     FROM       sorels
             JOIN
                somast
             ON somast.FSONO = sorels.FSONO
          JOIN
             soitem
          ON soitem.FSONO = sorels.FSONO
         AND soitem.FINUMBER = sorels.FINUMBER
    WHERE somast.FSTATUS = 'OPEN'
      AND sorels.FMASTERREL = 0
      AND somast.forderdate >= CONVERT (DATETIME, '01/01/2002')

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-18 : 17:51:26

I just noticed your 4-part naming convention in the original post. I'm pretty sure you've got it wrong: m2mdata01.dbo.sorels.fsono...

Do you have a database named dbo? Me thinks, no.

The 4-part naming convention is: LinkedServerName.DatabaseName.ObjectOwner.ObjectName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

james_wells
Yak Posting Veteran

55 Posts

Posted - 2008-09-18 : 17:56:14

i suspect it not treating the fields you are concaternating
as one select field when it should

rather then

m2mdata01.dbo.sorels.[fshipbook]
+ m2mdata01.dbo.sorels.[fshipbuy]
+ m2mdata01.dbo.sorels.[fshipmake]
AS AmountShipped,

try doing this sort of thing on all fields being concaternated

(m2mdata01.dbo.sorels.[fshipbook]
+ m2mdata01.dbo.sorels.[fshipbuy]
+ m2mdata01.dbo.sorels.[fshipmake])
AS AmountShipped,

using the "(" and ")"

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-18 : 17:57:34

quote:
Originally posted by tkizer

I just noticed your 4-part naming convention in the original post. I'm pretty sure you've got it wrong: m2mdata01.dbo.sorels.fsono...

Do you have a database named dbo? Me thinks, no.

The 4-part naming convention is: LinkedServerName.DatabaseName.ObjectOwner.ObjectName

Well, in my case M2Mdata01 is the database, dbo is the owner of course, sorels is the table name, and fsono is the field. Am I not addressing this correctly?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-18 : 18:03:10

You are not addressing it correctly. You can't fully qualify like that.

Use TableName.ColumnName or AliasName.ColumnName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources