Search code examples
sql-serversql-server-2019

SQLCMD Mode - Getting OBJECT EXISTS when used in a multi statement file

Version Info :

  • SSMS V15.0
  • SQL Server 2019 (v15.0)

enter image description here

I have a SQL process that involves 26 stored procedures. Some of them depend on each other. The master file calls about 6 of them, each of those calls a couple etc.

I have a file that uses SQLCMD and the :r command to run all these. I run them in reverse dependency order (files w/ no dependencies first)

All of the files have a DROP PROCEDURE IF EXISTS statement, then a GO, then the CREATE PROCEDURE.

My issue is that for a good handful of the files report that the object always exists IF and ONLY IF I run the whole file at once. I can highlight each file and it runs fine. I can open the file and it runs fine.

I have no other explanation other than this is somehow a quirk of SQLCMD.

Why am I getting an already exists error message on some object where there is a drop prior to the create?

here is an example of the simplest file that reliably causes this issue

Again... it works if I open the file.

It works if I highlight the single :r command,

DROP PROCEDURE IF EXISTS spdwh_PopulateOrderStatistics
GO

CREATE PROCEDURE spdwh_PopulateOrderStatistics
AS
    --   spdwh_populate 

    --=====================================
    --===
    --===  CustomerTaxExempt
    --===
    --=====================================

    DROP TABLE IF EXISTS #taxexemptOrders

    SELECT o.Source orderSource, o.SourceId OrderSourceId
    INTO #taxexemptOrders
    FROM dwhOrder o
    JOIN dwhCustomer c ON c.Source = o.CustomerSource
                       AND c.SourceId = o.CustomerSourceId
    WHERE c.NonTaxable = 1 

    --update dwo
    UPDATE dwhOrder SET customertaxexempt = 0           
    UPDATE dwhOrderItem SET customertaxexempt = 0           
    UPDATE dwhOrderTransaction SET customertaxexempt = 0
 
    UPDATE dwhOrder 
    SET customertaxexempt = 1           
    FROM dwhOrder o
    JOIN #taxexemptOrders teo ON teo.orderSource = o.Source 
                              AND teo.OrderSourceId = o.SourceId 

    -- update dwoi
    UPDATE dwhOrderItem 
    SET customertaxexempt = 1
    FROM dwhOrderItem oi
    JOIN #taxexemptOrders teo ON teo.orderSource = oi.Source 
                              AND teo.OrderSourceId = oi.SourceId

    -- update dwot 
    UPDATE dwhOrderTransaction 
    SET customertaxexempt = 1
    FROM dwhOrderTransaction ot
    JOIN #taxexemptOrders teo ON teo.orderSource = ot.Source 
                              AND teo.OrderSourceId = ot.SourceOrderId

I have played with moving the files around in the block.

I can say with some certainty that SQLCMD just does not handle this well.

The file that complains changes based on the order. There is no interdependence.

Here is an example of one of the blocks in the SQLCMD file...

StatFields and CleanFields were consistently complaining that they already exist. I moved them to the top and _tdc started complaining.

:r G:\doby\mc\circuPool\Database\dwhLoad\spDWH_popCustomer_tdc.sql
:r G:\doby\mc\circuPool\Database\dwhLoad\spDWH_popCustomer_man.sql
:r G:\doby\mc\circuPool\Database\dwhLoad\spDWH_set_Customer_CleanFields.sql
:r G:\doby\mc\circuPool\Database\dwhLoad\spDWH_set_Customer_StatFields.sql
:r G:\doby\mc\circuPool\Database\dwhLoad\spDWH_popCustomer_tdc_guest.sql
:r G:\doby\mc\circuPool\Database\dwhLoad\spDWH_PopulateCustomer.sql

If anyone has run into this issue and has a solution, I would greatly appreciate any ideas!

Solution

  • @danGuzman figured it out.
    If there is a file without a GO at the bottom, then SQLCMD struggles with it. I believe the file following the file without a trailing go has the issue.

    I opened all files , ensured they all had a trailing GO and suddenly the SQLCMD file processes without error.

    @danGuzman also points out that ALTER may be a better choice than DROP/CREATE. I was going to try that before the GO solution was suggested. I imagine it would also be an answer.

    If @danGuzman posts an answer, Ill recognize it and delete this one.