SQL Statement in Stored Proc for ADF Pipeline not Updating...

Question

question

ewinkiser asked Apr 1, '21 MartinJaffer-MSFT commented Apr 8, '21

SQL Statement in Stored Proc for ADF Pipeline not Updating...

I am having issues trying to get this to work for some reason: @nasreen-akter @MartinJaffer-MSFT

 Update MercerReportTable
     SET [CountType10] = (SELECT COUNT(PersonRec10)
     FROM [MercerStagingDev].[MILKY-WAYTEST\AppSQLVST4DotNetDev-R].[MercerReportTableMod]
     WHERE PersonRec10 = 10)

The statement seems successful when executed, but nothing is updated in the MercerReportTable. There are 3000+ items that should be the result count. The column is the following type, but that should not matter, correct? When I just run

(SELECT COUNT(PersonRec10)
FROM [MercerStagingDev].[MILKY-WAYTEST\AppSQLVST4DotNetDev-R].[MercerReportTableMod]
WHERE PersonRec10 = 10) I get 3624 which is the correct number of rows..but I can't get other table to update....

[CountType10] [nchar](10) NULL,

Any ideas? Thanks Mike

azure-data-factory sql-server-transact-sql

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MartinJaffer-MSFT · Apr 06, 2021 at 04:50 PM

@ewinkiser did @EchoLiu-msft 's answer solve your issue? If so please mark as accepted answer, otherwise please provide feedback.

0 ·

MartinJaffer-MSFT · Apr 08, 2021 at 04:32 PM

@ewinkiser if you found your own solution, could you please share it with the community?

0 ·

Answer 1 · 2021-04-02T06:39:10.297Z

EchoLiu-msft answered Apr 2, '21

Hi @ewinkiser，

I did a simple test in sql server. According to my test results, your code seems to be correct:

 create  table test1(num int,projectname char(15))
 insert into test1 values(2,'project1'),(2,'project2'),(3,'project2'),
                         (4,'project3'),(1,'project2'),(2,'project1')
    
 create  table test2(id int,projectname char(15))
 insert into test2 values(1,'project1'),(2,'project1'),(3,'project2'),
                         (4,'project2'),(5,'project1'),(6,'project3')
    
 select * from test1
 select * from test2
    
 update test2
 set id=(select count(num) from test1 where num=2)
 from test2
    
 drop table test1
 drop table test2

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question