Can I execute a SP in MSSQL

Matillion ETL
1

I thought the Database Query component can run a query like this:

exec schm.st_proc param1 param2

 

However, when I did the sample run, I get an error message:

Incorrect syntax near the keyword 'EXEC'.

 

Is it really impossible to do this?

 

I would appreciate it very much if someone can give me a hint.

 

2

I found an example:

 

SELECT * FROM OPENROWSET('SQLOLEDB', 'Trusted_Connection=Yes; Server=(local); Database=MyDatabase', 'EXEC abc.st_proc param1 param2')

 

"Ad Hoc Distributed Queries" must be allowed by the database. I will need to work with SQL DBA to get the permissions before I can actually try it.

 

3

Hello

 

You can try below steps-

 

Ensure that the query is correctly formatted for your MSSQL environment.

Sometimes, using CALL instead of EXEC can work. You might try:

CALL schm.st_proc param1, param2;

Also, make sure your parameters are correctly defined and enclosed in the right format, depending on your procedure's requirements.

Hope it helps !

 

Thank you😊