Version Info :

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

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!

You can see what the issue is if you create two files

file1.sql

CREATE PROC Foo
AS
BEGIN
SELECT 'File1';
END

file2.sql

SELECT 'File2';

And then run the following script

:r "C:\SomeFolder\file1.sql"
:r "C:\SomeFolder\file2.sql"

GO

EXEC sp_helptext 'Foo'

It returns

Text
CREATE PROC Foo
AS
BEGIN
SELECT 'File1'
END
SELECT 'File2'

File1 and File2 effectively get concatenated together and the overall script looks like this with contents inlined.

CREATE PROC Foo
AS
BEGIN
SELECT 'File1';
END
SELECT 'File2';

GO

EXEC sp_helptext 'Foo'

There is no batch delimiter between the CREATE PROCEDURE in File1 and the text in File2 so the commands until a batch delimiter is reached just become part of the preceding procedure definition rather than actually being executed.

In your case this will be the DROP PROCEDURE IF EXISTS commands that aren't executed and just become part of the preceding procedure definition.

2条答案

按热度按时间

34gzjxbg1#

@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.

赞(0）回复(0）举报 2023-08-02

tpgth1q72#

Text
CREATE PROC Foo
AS
BEGIN
SELECT 'File1'
END
SELECT 'File2'

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

2条答案

相关问题

热门标签

最新问答