question

NSH2120 avatar image
0 Votes"
NSH2120 asked HimanshuSinha-MSFT commented

ADF Copy Activity with USP as source and Azure SQL server as sink

Hello All,

I have a weird situation. I am trying to run a USP (which writes into the local temporary table and drops the temp table at end of stored procedure), therefore there is no output table the stored procedure writes to. When we run the Stored procedure EXEC dbo.test @Param1 =1 , it gives data in SSMS window. Now I am trying to load this data ( by passing the same parameters above and trying to run the stored procedure) from one SQL on prem server to another Azure SQL server.

I do not have permissions to modify or change the USP. The number of rows output from the stored procedure is about 8k.

I tried couple of solutions and nothing worked.
When I just try using copy activity and use Stored procedure as input and pass parameters, I get errors saying it cannot find the local temporary table while running

"
Failure happened on 'Source' side. 'Type=System.Data.SqlClient.SqlException,Message=Invalid object name '#outputtable'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=0,Errors=[{Class=16,Number=208,State=0,Message=Invalid object name '#outputtable'.,},],'"


When I try using lookup activity and pass it to an array with set variable, it is not able to handle 8k rows and says the number of rows exceeds the limitation of Lookup activity.

If anyone has a better solution pls help or if I should use a different activity also, pls let me know. Can I use data flow to run USP as source and write it to a SQL table? Would that work?
Thanks for all your help

azure-data-factory
5 |1600 characters needed characters left characters exceeded

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

NSH2120 avatar image
0 Votes"
NSH2120 answered HimanshuSinha-MSFT commented

I was able to resolve this issue by using ODBC Linked Service Instead of SQL Server and using Query option in Copy Activity while moving data from On Prem server to Azure .
Thanks for your help.

· 1
5 |1600 characters needed characters left characters exceeded

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

Hello @NSH2120 ,
It was great to know that you were able to get to a resolution . We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries .
Thanks
Himanshu

0 Votes 0 ·
HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered HimanshuSinha-MSFT converted comment to answer

Hello @NSH2120 ,
Thanks for the ask and using the Microsoft Q&A platform .

I wanted to repro the issue , but then thinks just worked fine for me , I am sharing what I did , may be you tell me how your scenario is different .


My proc looks like this .

CREATE proc testproc @id int
as

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
BEGIN
CREATE TABLE #Results ( id int , name varchar(100) )
END
INSERT INTO #Results select PersonID,Name from [dbo].[customer_table]
Where PersonID = @id
SELECT * from #Results
drop table #Results

exec testproc 3

95722-image.png


I did pointed the ADF with source as the Proc as shown below and it worked just fine .


95696-image.png

95676-image.png


Hope this helps .

Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members



image.png (5.7 KiB)
image.png (26.9 KiB)
image.png (67.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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

Thank you for your answer,@HimanshuSinha-MSFT Can you pls try using on prem db to Azure SQL and try inserting multiple rows of data into multiple columns. I get the following error while doing so.!
95678-error1.jpg

InvalidObjectName Showing #(local temporary table)


[1]: /answers/storage/attachments/95680-image.png


0 Votes 0 ·
error1.jpg (19.8 KiB)
image.png (20.2 KiB)