Got a weird one for ya....

• lk4772

  Ten Centuries

  Points: 1030

  More actions

  June 25, 2017 at 11:52 pm

  #338401

  Have a sproc that is throwing an error when trying to do an Insert statement. Gives the "Invalid Object Error" message. However, the table does exist on the server and in the db that is in the path. The weird part is that 11 lines above, it is pulling from the same table and inserting into a temp table. Records are being saved - so I tried copying/pasting the path and table name from the first query into the Insert statement, but it still fails. I also double checked my login to make sure I had write access to the table - I'm good there. Also tried pulling the name of the table from the Objects list into the query window. Nothing is working. We're using 2008 R2 as the database where the sproc lives and 2014 where we're pulling the data from (the full path for the table). Sample of the code is below along with screen shot of what I'm seeing. If anyone has any suggestions on what to look for I'd appreciate any kind of tip! Thanks!

  
declare @x int
drop table testCustomer
select * into testCustomer from dbo.Customer
select @x = count(*) from testCustomer
print 'x = ' + convert(varchar(15), @x);
select @x = count(*) from dbo.Customer
print 'x = ' + convert(varchar(15), @x);

  -----------------INSERT INTO CUSTOMER

  INSERT dbo.Customer ( Ac

  

• Thom A

  SSC Guru

  Points: 99126

  More actions

  June 26, 2017 at 2:49 am

  #1948113

  The screenshot of your query has part blanked out, but I assume that that is a declaration of a database, so you are using 3 part naming convention, rather than 2. This means that the above references to dbo.Customer will be using the table in the database you are connected to, rather than the one in the other database. I would hazard a guess if you change the prior references to 3 part naming convention you will get the same error, as the table does not exist in that database. dbo.Customer exist in the connected database (hence no error), but doesn't in the other database.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• lk4772

  Ten Centuries

  Points: 1030

  More actions

  June 26, 2017 at 12:14 pm

  #1948223

  Hi Thom, Thanks for your response! However, the only reason I blacked out some of the table path is due to company policy as the server and database names have the company name in them. I will tell you that both line 17 and 28 have 4 part naming as it's using a linked server for the same table. So it would be like 

  <linked server name>.<database name>.dbo.Customer 

  for both lines - and I did copy and paste the path from line 17 to line 28 but still get the error?

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  June 26, 2017 at 12:43 pm

  #1948228

  The devil's in the details, unfortunately, and those are hidden from us.

  Instead of an incredibly obscured screenshot, just put up an anonymized script.

  Substitute something with no meaning for your server and database names (of course be consistent, so that the same server/database name is replaced by the same thing for each occurrence), something like Server1.Database1, etc.

  Cheers!

• lk4772

  Ten Centuries

  Points: 1030

  More actions

  June 26, 2017 at 1:25 pm

  #1948241

  Hi Jacob, sorry! Guess I could have done that at the beginning - here's the updated screen shot. I updated the names after running the code so that I could generate the error. Thanks for looking! 
  

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 26, 2017 at 2:22 pm

  #1948255

  I suspect in updating after running, you may have done something to hide the error.
  The first block that returned a count could not possibly have done so, because the table it selects from is dropped just before the select count and the temp table created is not used.

  Instead of manually changing names and taking screenshots, could you please run a single replace (use SSMS's find/replace to make sure it's just one thing replaced) and replace whatever the company name is with "DUMMY" and then paste the code itself (not a screenshot) here, and please include code past the insert that you have, the line numbers are often off for various reasons.

  Oh, and are there any triggers on the Customers table?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• lk4772

  Ten Centuries

  Points: 1030

  More actions

  June 26, 2017 at 2:47 pm

  #1948262

  You're right Gila! I missed a part of the table name when manually updating the code - here's the code with "Dummy" added 

  
declare @x int
drop table DummyCustomer
select * into DummyCustomer from DummySQL03.TESTdummyAPP.dbo.Customer
select @x = count(*) from DummyCustomer
print 'x = ' + convert(varchar(15), @x);

  select @x = count(*) from DummySQL03.TESTdummyAPP.dbo.Customer
print 'x = ' + convert(varchar(15), @x);

  -------------------------------------------------------------------------------------------------------INSERT INTO CUSTOMER

  INSERT DummySQL03.TESTdummyAPP.dbo.Customer (

  Line 3 is where I'm pulling from the table with the issues into the temp table 
  Line 5 is where I'm returning the count to make sure there are records in the table
  Line 6 is where I'm getting the count directly from the table
  Last line is where it's failing with message: 
  Msg 208, Level 16, State 1, Line 30
  Invalid object name 'Customer'.

  There are also 2 triggers on the table -> I've pasted them in as well.

  
CREATE PROCEDURE [dbo].[dummy_CustNameXRef_Delete]

      @CustIDParm varchar(15),
    @NameParm varchar(60)
AS
    
    DECLARE @Name varchar(60)
    DECLARE @NameSeg varchar(20)
    DECLARE @RecExists smallint
    DECLARE @DelimiterPos smallint
    DECLARE @LookupSpecChar varchar(30)

      DECLARE @CharPos smallint
    DECLARE @DelimiterCharPos smallint

      -- Convert to upper case and trim the spaces off
    -- the passed name.
    SELECT @Name = LTRIM(RTRIM(UPPER(@NameParm)))

      -- Skip all of this if no name was passed.
    IF @Name IS NULL
    BEGIN
        RETURN
    END

      SELECT @LookupSpecChar = DfltShpnotInvSub FROM INSetup
    SELECT @LookupSpecChar = LTRIM(RTRIM(@LookupSpecChar)) + ' '

  START:
    IF @Name IS NOT NULL
    BEGIN
        SELECT @CharPos = 1

          WHILE 1=1
        BEGIN
            IF @CharPos > DATALENGTH(@Name)
            BEGIN
                SELECT @CharPos = 0
                BREAK
            END

              SELECT @DelimiterCharPos = CHARINDEX(SUBSTRING(@Name, @CharPos, 1), @LookupSpecChar)
            IF @DelimiterCharPos > 0
                BREAK

              SELECT @CharPos = @CharPos + 1
        END
    END
    ELSE
    BEGIN
        SELECT @CharPos = 0
    END

      SELECT @DelimiterPos = @CharPos

      -- If there are not delimiters in the name.
    IF @DelimiterPos = 0
    BEGIN
        -- Assume whats there is a segment.
        SELECT @NameSeg = @Name

          -- If there is something in the name...
        IF @NameSeg IS NOT NULL
        BEGIN
            -- See if the record already exists.
            SELECT @RecExists = COUNT(*) from CustNameXref
            WHERE CustID = @CustIDParm AND NameSeg = @NameSeg

              -- If the record does exist...
            IF @RecExists <> 0
            BEGIN
                -- Delete the segment from the table.
                DELETE FROM CustNameXref
                WHERE CustID = @CustIDParm AND NameSeg = @NameSeg
            END
        END
        RETURN
    END
    ELSE
    BEGIN
        -- There are delimiters in the name --

          -- Extract the segment.
        SELECT @NameSeg = SUBSTRING(@Name, 1, @DelimiterPos - 1)

          -- If there is something in the name...
        IF @NameSeg IS NOT NULL
        BEGIN

              -- See if the record already exists.
            SELECT @RecExists = COUNT(*) from CustNameXref
            WHERE CustID = @CustIDParm AND NameSeg = @NameSeg

              -- If the record does exist...
            IF @RecExists <> 0
            BEGIN
                -- Delete the segment from the table.
                DELETE FROM CustNameXref
                WHERE CustID = @CustIDParm AND NameSeg = @NameSeg
            END
        END

          -- Extract the segment from the current name.
        SELECT @Name = LTRIM(SUBSTRING(@Name, @DelimiterPos + 1, 60))
    END

      GOTO START


  and

  
SET QUOTED_IDENTIFIER OFF
GO

  CREATE PROCEDURE [dbo].[ADG_CustNameXRef_Add]
    @CustIDParm varchar(15),
    @NameParm varchar(60)
AS
    SET NOCOUNT ON
    DECLARE @Name varchar(60)
    DECLARE @NameSeg varchar(20)
    DECLARE @RecExists smallint
    DECLARE @DelimiterPos smallint
    DECLARE @LookupSpecChar varchar(30)

      DECLARE @CharPos smallint
    DECLARE @DelimiterCharPos smallint

      -- Convert to upper case and trim the spaces off
    -- the passed name.
    SELECT @Name = LTRIM(RTRIM(UPPER(@NameParm)))

      -- Skip all of this if no name was passed.
    IF @Name IS NULL
    BEGIN
        RETURN
    END

      SELECT @LookupSpecChar = DfltShpnotInvSub FROM INSetup
    SELECT @LookupSpecChar = LTRIM(RTRIM(@LookupSpecChar)) + ' '

  START:
    IF @Name IS NOT NULL
    BEGIN
        SELECT @CharPos = 1

          WHILE 1=1
        BEGIN
            IF @CharPos > DATALENGTH(@Name)
            BEGIN
                SELECT @CharPos = 0
                BREAK
            END

              SELECT @DelimiterCharPos = CHARINDEX(SUBSTRING(@Name, @CharPos, 1), @LookupSpecChar)
            IF @DelimiterCharPos > 0
                BREAK

              SELECT @CharPos = @CharPos + 1
        END
    END
    ELSE
    BEGIN
        SELECT @CharPos = 0
    END

      SELECT @DelimiterPos = @CharPos

      -- If there are not delimiters in the description...
    IF @DelimiterPos = 0
    BEGIN
        -- Assume whats there is a segment.
        SELECT @NameSeg = @Name

          -- If there is something in the segment...
        IF @NameSeg IS NOT NULL
        BEGIN

              -- See if the record already exists.
            SELECT @RecExists = COUNT(*) from CustNameXref
            WHERE CustID = @CustIDParm AND NameSeg = @NameSeg

              -- If the record doesn't exist.
            IF @RecExists = 0
            BEGIN
                -- Add the new segment to the table.
                INSERT INTO CustNameXref(Crtd_DateTime, Crtd_Prog, Crtd_User, CustID, LUpd_DateTime, LUpd_Prog, LUpd_User, NameSeg)
                VALUES(getdate(), '', '',@CustIDParm, getdate(), '', '',@NameSeg)
            END
        END
        RETURN
    END
    ELSE
    BEGIN
        -- There are delimiters in the name --

          -- Extract the segment.
        SELECT @NameSeg = SUBSTRING(@Name, 1, @DelimiterPos - 1)

          -- If there is something in the segment...
        IF @NameSeg IS NOT NULL
        BEGIN
            -- See if the record already exists.
            SELECT @RecExists = COUNT(*) from CustNameXref
            WHERE CustID = @CustIDParm AND NameSeg = @NameSeg

              -- If the record doesn't exist...
            IF @RecExists = 0
            BEGIN
                -- Add the new segment to the table.
                INSERT INTO CustNameXref(Crtd_DateTime, Crtd_Prog, Crtd_User, CustID, LUpd_DateTime, LUpd_Prog, LUpd_User, NameSeg)
                VALUES(getdate(), '', '',@CustIDParm, getdate(), '', '',@NameSeg)
            END
        END

          -- Extract the segment from the current name.
        SELECT @Name = LTRIM(SUBSTRING(@Name, @DelimiterPos + 1, 60))
    END

      GOTO START


• Mr. Brian Gale

  SSC-Insane

  Points: 24670

  More actions

  June 26, 2017 at 3:16 pm

  #1948266

  You said you posted 2 triggers, but those are procedures.
  Do the triggers call those procedures?

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• lk4772

  Ten Centuries

  Points: 1030

  More actions

  June 26, 2017 at 3:18 pm

  #1948268

  sorry - yes the triggers call those procedures

• lk4772

  Ten Centuries

  Points: 1030

  More actions

  June 26, 2017 at 3:26 pm

  #1948269

  I'm so sorry - boy I'm messing this post up - so sorry! trying to do too many things at once LOL  below is the code for the triggers that call the sprocs above
  
  USE [TESTdummyAPP]
GO

  /****** Object: Trigger [dbo].[TR_CustNameXref_Add]  Script Date: 06/26/2017 16:36:52 ******/
SET ANSI_NULLS ON
GO

  SET QUOTED_IDENTIFIER OFF
GO

  CREATE TRIGGER [dbo].[TR_CustNameXref_Add] ON [dbo].[Customer]
FOR INSERT,UPDATE
AS
    DECLARE @CustID varchar(15)
    DECLARE @Name varchar(30)
    DECLARE @RowsAffected int

      SELECT @RowsAffected = @@ROWCOUNT

      IF @RowsAffected = 1
    BEGIN

          IF UPDATE(Name)
        BEGIN
            SELECT @CustID = CustID, @Name = Name from DELETED        
            EXECUTE ADG_CustNameXref_Delete @CustID, @Name

              SELECT @CustID = CustID, @Name = Name from INSERTED
            EXECUTE ADG_CustNameXref_Add @CustID, @Name
        END
    END

      IF @RowsAffected > 1
    BEGIN

          IF UPDATE(Name)
        BEGIN

              DECLARE DelCursor SCROLL CURSOR FOR SELECT CustID, Name FROM DELETED

              OPEN DelCursor

              FETCH FIRST FROM DelCursor INTO @CustID, @Name
            
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                EXECUTE ADG_CustNameXref_Delete @CustID, @Name

                  FETCH NEXT FROM DelCursor INTO @CustID, @Name

              END

              CLOSE DelCursor
            DEALLOCATE DelCursor

              DECLARE InsCursor SCROLL CURSOR FOR SELECT CustID, Name FROM INSERTED

              OPEN InsCursor

              FETCH FIRST FROM InsCursor INTO @CustID, @Name
            
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                EXECUTE ADG_CustNameXref_Add @CustID, @Name
    
                FETCH NEXT FROM InsCursor INTO @CustID, @Name

              END

              CLOSE InsCursor
            DEALLOCATE InsCursor
        END
    END
GO

  and

  USE [TESTdummyAPP]
GO

  /****** Object: Trigger [dbo].[ADG_TR_CustNameXref_Delete]  Script Date: 06/26/2017 17:24:55 ******/
SET ANSI_NULLS ON
GO

  SET QUOTED_IDENTIFIER OFF
GO

  CREATE TRIGGER [dbo].[TR_CustNameXref_Delete] ON [dbo].[Customer]
FOR DELETE
AS
    DECLARE @CustID varchar(15)
    DECLARE @Name varchar(30)
    DECLARE @RowsAffected int

      SELECT @RowsAffected = @@ROWCOUNT

      IF @RowsAffected = 1
    BEGIN

          SELECT @CustID = CustID, @Name = Name from DELETED
        EXECUTE ADG_CustNameXref_Delete @CustID, @Name
    END

      IF @RowsAffected > 1
    BEGIN
        DECLARE DelCursor SCROLL CURSOR FOR SELECT CustID, Name FROM DELETED

          OPEN DelCursor

          FETCH FIRST FROM DelCursor INTO @CustID, @Name
            
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXECUTE ADG_CustNameXref_Delete @CustID, @Name

              FETCH NEXT FROM DelCursor INTO @CustID, @Name

          END

          CLOSE DelCursor
        DEALLOCATE DelCursor
    END

  

• Mr. Brian Gale

  SSC-Insane

  Points: 24670

  More actions

  June 26, 2017 at 3:32 pm

  #1948270

  I am assuming based on the names that:
  dummy_CustNameXRef_Delete
  gets called on delete and:
  ADG_CustNameXRef_Add
  gets called on insert?

  Also, unless I am reading those SP's incorrectly (which is entirely possible), do they ever finish?  It looks like it is an infinite loop to me.  That "GOTO START" at the end of the SP's I think would put them into an infinite loop once they are called.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 26, 2017 at 3:32 pm

  #1948271

  lk4772 - Monday, June 26, 2017 2:47 PM

  You're right Gila! I missed a part of the table name when manually updating the code - here's the code with "Dummy" added 

  
declare @x int
drop table DummyCustomer
select * into DummyCustomer from DummySQL03.TESTdummyAPP.dbo.Customer
select @x = count(*) from DummyCustomer
print 'x = ' + convert(varchar(15), @x);

  select @x = count(*) from DummySQL03.TESTdummyAPP.dbo.Customer
print 'x = ' + convert(varchar(15), @x);

  -------------------------------------------------------------------------------------------------------INSERT INTO CUSTOMER

  INSERT DummySQL03.TESTdummyAPP.dbo.Customer (

  Line 3 is where I'm pulling from the table with the issues into the temp table 
  Line 5 is where I'm returning the count to make sure there are records in the table
  Line 6 is where I'm getting the count directly from the table
  Last line is where it's failing with message: 
  Msg 208, Level 16, State 1, Line 30
  Invalid object name 'Customer'.
  

  And the rest of that code please?
  I need to see the entire insert and any statements after it.

  Doesn't look like it's coming from the triggers or procedure (btw, both are very inefficient), so currently I suspect something further in the INSERT statement that we haven't seen yet, or after the INSERT.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 26, 2017 at 3:42 pm

  #1948273

  bmg002 - Monday, June 26, 2017 3:32 PM

  Also, unless I am reading those SP's incorrectly (which is entirely possible), do they ever finish?  It looks like it is an infinite loop to me.  That "GOTO START" at the end of the SP's I think would put them into an infinite loop once they are called.

  They do. There's a RETURN in the middle that, I imagine, gets hit everntually. Not particularly good coding practice, but...

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• lk4772

  Ten Centuries

  Points: 1030

  More actions

  June 26, 2017 at 3:43 pm

  #1948274

  Hi Gail, 

  Here's the code - please don't judge me on the syntax - I didn't write this! LOL I've been trying to clean up the mess but you know how that goes....

  USE [dummyNewPartial]
GO

  /****** Object: StoredProcedure [dbo].[sp_InsertNew]  Script Date: 06/26/2017 17:38:54 ******/
SET ANSI_NULLS ON
GO

  SET QUOTED_IDENTIFIER ON
GO

  CREATE PROCEDURE [dbo].[sp_InsertNew] AS

  declare @x int
drop table dummyCustomer
select * into dummyCustomer from dummySQL03.TESTdummyAPP.dbo.Customer
select @x = count(*) from dummyCustomer
print 'x = ' + convert(varchar(15), @x);
drop table dummysoaddress
select * into dummysoaddress from dummySQL03.TESTdummyAPP.dbo.soaddress
drop table dummycustomeredi
select * into dummycustomeredi from dummySQL03.TESTdummyAPP.dbo.customeredi
select @x = count(*) from dummySQL03.TESTdummyAPP.dbo.Customer
print 'x = ' + convert(varchar(15), @x);

  -------------------------------------------------------------------------------------------------------INSERT INTO CUSTOMER

  INSERT dummySQL03.TESTdummyAPP.dbo.customer ( AccrRevAcct, AccrRevSub, AcctNbr, Addr1, Addr2, AgentID, ApplFinChrg, ArAcct, ArSub, Attn, AutoApply, BankID, BillAddr1, BillAddr2, BillAttn, BillCity,
        BillCountry, BillFax, BillName, BillPhone, BillSalut, BillState, BillThruProject, BillZip, CardExpDate, CardHldrName, CardNbr, CardType, City, ClassId,
        ConsolInv, Country, CrLmt, Crtd_DateTime, Crtd_Prog, Crtd_User, CuryId, CuryPrcLvlRtTp, CuryRateType, CustFillPriority, CustId, DfltShipToId,
        DunMsg, EMailAddr, Fax, InvtSubst, LanguageID, LUpd_DateTime, LUpd_Prog, LUpd_User, Name, NoteId, OneDraft, PerNbr, Phone, PmtMethod,
        PrcLvlId, PrePayAcct, PrePaySub, PriceClassID, PrtMCStmt, PrtStmt, S4Future01, S4Future02, S4Future03, S4Future04, S4Future05, S4Future06,
        S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12, Salut, SetupDate, ShipCmplt, ShipPctAct, ShipPctMax, SICCode1, SICCode2,
        SingleInvoice, SlsAcct, SlsperId, SlsSub, State, Status, StmtCycleId, StmtType, TaxDflt, TaxExemptNbr, TaxID00, TaxID01, TaxID02, TaxID03,
        TaxLocId, TaxRegNbr, Terms, Territory, TradeDisc, User1, User2, User3, User4, User5, User6, User7, User8, Zip)

  SELECT  AccrRevAcct, AccrRevSub, AcctNbr, Addr1, Addr2, AgentID, ApplFinChrg, ArAcct, ArSub, Attn, AutoApply, BankID, BillAddr1, BillAddr2, BillAttn, BillCity,
        BillCountry, BillFax, left(newBillName + space(30),30) as Billname, BillPhone, BillSalut, BillState, BillThruProject, BillZip, CardExpDate, CardHldrName, CardNbr, CardType, City, newClassId as ClassID,
        ConsolInv, Country, CrLmt, getdate() as Crtd_DateTime, Crtd_Prog, Crtd_User, CuryId, CuryPrcLvlRtTp, CuryRateType, CustFillPriority, newCustId as CustID, DfltShipToId,
        DunMsg, EMailAddr, Fax, InvtSubst, LanguageID, getdate() as LUpd_DateTime, LUpd_Prog, LUpd_User, left(newName + space(30),30) as [Name], NoteId, OneDraft, PerNbr, Phone, PmtMethod,
        PrcLvlId, PrePayAcct, PrePaySub, PriceClassID, PrtMCStmt, PrtStmt, S4Future01, S4Future02, S4Future03, S4Future04, S4Future05, S4Future06,
        S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12, Salut, SetupDate, ShipCmplt, ShipPctAct, ShipPctMax, SICCode1, SICCode2,
        SingleInvoice, SlsAcct, SlsperId, SlsSub, State, Status, StmtCycleId, StmtType, TaxDflt, TaxExemptNbr, TaxID00, TaxID01, TaxID02, TaxID03,
        TaxLocId, TaxRegNbr, Terms, Territory, TradeDisc, User1, User2, newUser3 as User3, User4, User5, User6, User7, User8, Zip
FROM   dbo.sfaCUSTOMER cross join (SELECT  custid as newCustID, classid as newClassID, [name] as newName, billname as newBillname, team as newUser3
                        FROM   Customer where Customer.CustID not in (select CustID from dummyCustomer)) newCust

  -------------------------------------------------------------------------------------------------------INSERT INTO CUSTOMER

  -------------------------------------------------------------------------------------------------------INSERT INTO SOADDRESS

  INSERT dummySQL03.TESTdummyAPP.dbo.soaddress (Addr1, Addr2, Attn, City, COGSAcct, COGSSub, Country, Crtd_DateTime, Crtd_Prog, Crtd_User, CustId, Descr, DiscAcct, DiscSub, EMailAddr, Fax, FOB,
        FrghtCode, FrtAcct, FrtSub, FrtTermsID, GeoCode, LUpd_DateTime, LUpd_Prog, LUpd_User, MapLocation, MiscAcct, MiscSub, Name, NoteId, Phone,
        S4Future01, S4Future02, S4Future03, S4Future04, S4Future05, S4Future06, S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12,
        ShipToId, ShipViaID, SiteID, SlsAcct, SlsPerID, SlsSub, State, Status, TaxId00, TaxId01, TaxId02, TaxId03, TaxLocId, TaxRegNbr, User1, User2, User3,
        User4, User5, User6, User7, User8, Zip)

  SELECT  Addr1, Addr2, Attn, City, COGSAcct, COGSSub, Country, getdate() as Crtd_DateTime, Crtd_Prog, Crtd_User, newCustId as CustID, Descr, DiscAcct, DiscSub, EMailAddr, Fax, FOB,
        FrghtCode, FrtAcct, FrtSub, FrtTermsID, GeoCode, getdate() as LUpd_DateTime, LUpd_Prog, LUpd_User, MapLocation, MiscAcct, MiscSub, left(newName + space(30),30) as [Name], NoteId, Phone,
        S4Future01, S4Future02, S4Future03, S4Future04, S4Future05, S4Future06, S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12,
        ShipToId, ShipViaID, SiteID, SlsAcct, SlsPerID, SlsSub, State, Status, TaxId00, TaxId01, TaxId02, TaxId03, TaxLocId, TaxRegNbr, User1, User2, User3,
        User4, User5, User6, User7, User8, Zip
FROM   dbo.sfasoaddress cross join (SELECT  custid as newCustID, [name] as newName
                        FROM   Customer where Customer.CustID not in (select CustID from dummysoaddress)) newCust

  -------------------------------------------------------------------------------------------------------INSERT INTO SOADDRESS

  -------------------------------------------------------------------------------------------------------INSERT INTO CUSTOMEREDI

  INSERT dummySQL03.TESTdummyAPP.dbo.customeredi ( AgreeNbrFlg, ApptNbrFlg, ArrivalDateFlg, BatchNbrFlg, BidNbrFlg, BOLFlg, BOLNoteID, BOLRptFormat, BuyerReqd, CertID, CheckShipToID, COGSAcct,
        COGSSub, ContractNbrFlg, ContTrackLevel, CreditMgrID, CreditRule, CrossDockFlg, Crtd_DateTime, Crtd_Prog, Crtd_User, CustCommClassID, CustID,
        CustItemReqd, DeliveryDateFlg, DeptFlg, DfltBuyerID, DiscAcct, DiscSub, DivFlg, EDSOUser10Flg, EDSOUser1Flg, EDSOUser2Flg, EDSOUser3Flg,
        EDSOUser4Flg, EDSOUser5Flg, EDSOUser6Flg, EDSOUser7Flg, EDSOUser8Flg, EDSOUser9Flg, EquipNbrFlg, FOBFlg, FOBID, FOBLocQualFlg,
        FOBTranTypeFlg, FrtAcct, FrtAllowCd, FrtDiscCd, FrtSub, GeoCode, GLClassID, GracePer, GSA, HeightFlg, ImpConvMeth, InternalNoteID, IntVendorNbr,
        IntVendorNbrFlg, InvcNoteID, LabelReqd, LenFlg, LineItemEDIDiscCode, LUpd_DateTime, LUpd_Prog, LUpd_User, MajorAccount, ManNoteID,
        MinOrder, MinWt, MiscAcct, MiscSub, MultiDestMeth, NbrCartonsFlg, NoteID, OrigSourceID, OutBndTemplate, PlanDateFlg, POReqd, PROFlg,
        PromoNbrFlg, PSNoteID, PTNoteID, QuoteNbrFlg, RegionID, RequestDateFlg, S4Future01, S4Future02, S4Future03, S4Future04, S4Future05,
        S4Future06, S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12, SalespersonFlg, SalesRegionFlg, SCACFlg, ScheduledDateFlg,
        SDQMarkForFlg, SendZeroInvc, SepDestOrd, ShipDateFlg, ShipmentLabel, ShipMthPayFlg, ShipNBDateFlg, ShipNLDateFlg, ShipToRefNbrFlg,
        ShipViaFlg, ShipWeekOfFlg, SingleContainer, SiteID, SlsAcct, SlsSub, SOTypeID, SOUser10Flg, SOUser1Flg, SOUser2Flg, SOUser3Flg, SOUser4Flg,
        SOUser5Flg, SOUser6Flg, SOUser7Flg, SOUser8Flg, SOUser9Flg, SplitPartialLineDisc, SubNbrFlg, SubstOK, TerritoryID, TrackingNbrFlg, UseEDIPrice,
        User1, User10, User2, User3, User4, User5, User6, User7, User8, User9, UserNoteID1, UserNoteID2, UserNoteID3, VolumeFlg, WebSite, WeightFlg,
        WholeOrdEDIDiscCode, WidthFlg)

  SELECT  AgreeNbrFlg, ApptNbrFlg, ArrivalDateFlg, BatchNbrFlg, BidNbrFlg, BOLFlg, BOLNoteID, BOLRptFormat, BuyerReqd, CertID, CheckShipToID, COGSAcct,
        COGSSub, ContractNbrFlg, ContTrackLevel, CreditMgrID, CreditRule, CrossDockFlg, getdate() as Crtd_DateTime, Crtd_Prog, Crtd_User, CustCommClassID, newCustID as CustID,
        CustItemReqd, DeliveryDateFlg, DeptFlg, DfltBuyerID, DiscAcct, DiscSub, DivFlg, EDSOUser10Flg, EDSOUser1Flg, EDSOUser2Flg, EDSOUser3Flg,
        EDSOUser4Flg, EDSOUser5Flg, EDSOUser6Flg, EDSOUser7Flg, EDSOUser8Flg, EDSOUser9Flg, EquipNbrFlg, FOBFlg, FOBID, FOBLocQualFlg,
        FOBTranTypeFlg, FrtAcct, FrtAllowCd, FrtDiscCd, FrtSub, GeoCode, GLClassID, GracePer, GSA, HeightFlg, ImpConvMeth, InternalNoteID, IntVendorNbr,
        IntVendorNbrFlg, InvcNoteID, LabelReqd, LenFlg, LineItemEDIDiscCode, getdate() as LUpd_DateTime, LUpd_Prog, LUpd_User, MajorAccount, ManNoteID,
        MinOrder, MinWt, MiscAcct, MiscSub, MultiDestMeth, NbrCartonsFlg, NoteID, OrigSourceID, OutBndTemplate, PlanDateFlg, POReqd, PROFlg,
        PromoNbrFlg, PSNoteID, PTNoteID, QuoteNbrFlg, RegionID, RequestDateFlg, S4Future01, S4Future02, S4Future03, S4Future04, S4Future05,
        S4Future06, S4Future07, S4Future08, S4Future09, S4Future10, S4Future11, S4Future12, SalespersonFlg, SalesRegionFlg, SCACFlg, ScheduledDateFlg,
        SDQMarkForFlg, SendZeroInvc, SepDestOrd, ShipDateFlg, ShipmentLabel, ShipMthPayFlg, ShipNBDateFlg, ShipNLDateFlg, ShipToRefNbrFlg,
        ShipViaFlg, ShipWeekOfFlg, SingleContainer, SiteID, SlsAcct, SlsSub, SOTypeID, SOUser10Flg, SOUser1Flg, SOUser2Flg, SOUser3Flg, SOUser4Flg,
        SOUser5Flg, SOUser6Flg, SOUser7Flg, SOUser8Flg, SOUser9Flg, SplitPartialLineDisc, SubNbrFlg, SubstOK, TerritoryID, TrackingNbrFlg, UseEDIPrice,
        User1, User10, User2, User3, User4, User5, User6, User7, User8, User9, UserNoteID1, UserNoteID2, UserNoteID3, VolumeFlg, WebSite, WeightFlg,
        WholeOrdEDIDiscCode, WidthFlg
FROM   dbo.sfacustomeredi cross join (SELECT  custid as newCustID
                        FROM   Customer where Customer.CustID not in (select CustID from dummycustomeredi)) newCust

  ---------------------------------------------------------------------------------------------------------INSERT INTO CUSTOMEREDI

  ---------------------------------------------------------------------------------------------------------INSERT INTO COMPANY
--insert teamsPARTIAL.dbo.company ([Co #], TEAM, MemberCorp, UpdateDate)
--SELECT  [Co #], TEAM, MemberCorp, getdate() as UpdateDate
--FROM   vwsfaNewCOMPANY
---------------------------------------------------------------------------------------------------------INSERT INTO COMPANY

  ---------------------------------------------------------------------------------------------------------INSERT INTO PEOPLE

  --insert teamsPARTIAL.dbo.people ([Per #], Lname, Fname, Co#, addr1, addr2, city, st, zip, country, [Foreign zip], busunit, Email, Fax, Phone, DateChange)
--SELECT  [Per #], left(Lname + space(20),20), left(Fname + space(15), 15) as Fname, Co#, left(addr1+ space(50),50) as addr1, left(addr2+ space(50),50) as addr2, left(city + space(25),25) as city, left(st + space(2),2) as st, left(zip + space(5),5) as zip, left(country+ space(15), 15) as Country, Foreignzip as [Foreign zip], convert(char(40),busunit) as busunit, Email, left(Fax+space(20),20) as Fax, left(Phone+space(20),20) as Phone, getdate() as DateChange
--FROM   vwsfaNewPEOPLE

  ---------------------------------------------------------------------------------------------------------INSERT INTO PEOPLE

  --update teamsPARTIAL.dbo.company
--set team = t.team
--from dbo.CompaniesAndTeams t inner join teams.dbo.company c on t.[co #] = c.[co #]
--where c.team <> t.team

  --update dummySQL03.TESTdummyAPP.dbo.customer
--set user3 = t.team
--from dbo.CompaniesAndTeams t inner join dummySQL03.TESTdummyAPP.dbo.customer c on t.[co #] = c.[custid]
--where c.user3 <> t.team

  ---------------------------------------------------------------------------------------------------------UPDATE PERSON USER and PWD

  --update TEAMSPARTIAL.dbo.PEOPLE
--set Userid = c.User_ID__c,
--Psswd = c.Password__c
--from TEAMS.dbo.PEOPLE p
--inner join dummyNewPartial..Contact c on p.[Per #] = c.Person_ID__c
--where isnull(c.User_ID__c,'') <> isnull(p.Userid,'') or isnull(c.Password__c,'') <> isnull(p.Psswd ,'')

  GO

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  June 26, 2017 at 3:45 pm

  #1948276

  To pile on, this CustNameXRef object referenced in those procedures, is it a view?

  If so, could we see its definition as well?

  Cheers!

  EDIT: Your most recent post got posted while I was typing this, so I have another question.

  Does this DummyNewPartial DB actually have a Customer table in it?