ADF Lookup query create schema and select

Question

I am trying to run a create Shema/table query in the ADF lookup activity, with a dummy select in the end.

CREATE SCHEMA [schemax] AUTHORIZATION [auth1]; SELECT 0 AS dummyValue

but I got the below error

A database operation failed with the following error: 'Parse error at line: 2, column: 1: Incorrect syntax near 'SELECT'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Parse error at line: 2, column: 1: Incorrect syntax near 'SELECT'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=103010,Class=16,ErrorCode=-2146232060,State=1

I was able to run a similar query without SELECT query in the end but got another error mandating lookup must return a value.

Answer 1

Hi @Kiran Rredy Addelle ,

Thank you for posting query in Microsoft Q&A Platform.

I tried executing similar query my end its working fine. Please check below screenshot.

Kindly try to have line break between your main query and dummy select query as I did in below screenshot.

Hope this will help. Please let us know if any further queries. Thank you.

---------------------

Please consider hitting Accept Answer button. Accepted answers helps community as well.

Answer 2

Hi @Kiran Rredy Addelle ,

Yes you are right. In case of schema for direct query its not working. But below is the work around.

Use dynamic sql query to do the same task.

Below is the implementation which i tried and it worked.

Hope this will help. Please let me know if any further queries.

------------------

Please consider hitting Accept Answer button. Accepted answers helps community as well.