HI Team,
I've called API in SQL Sever and it returning JSON. I was trying to store the result in variable VARCHAR(MAX). But my data is huge (around hundreads records with 10 to 20 columnns if we convert it as table in SQL) so the length is not supporting, so no data is storing in the variable, it is becoming NULL.
I'm using SQL server 2014.
Declare objectas Int;
Declare @ResponseTextas Varchar(MAX);
--Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', objectOUT;
Exec sp_OAMethod object, 'open', NULL, 'get',
APIurl,'false'
Exec sp_OAMethod object, 'setRequestHeader', null, 'Content-Type', 'application/xml'
Exec sp_OAMethod object, 'send'
Exec sp_OAMethod object, 'responseText', @ResponseTextOUTPUT
select @ResponseText
Select * from dbo.json_Parse(@ResponseText)
Exec sp_OADestroy object
Output : @ResponseText = NULL
and
I tried API consuming logic in .net and tried to create an assembly from SQL.(create ASSEMBLY jsontest from 'c:\dll\JsonParser.dll' WITH PERMISSION_SET =EXTERNAL_ACCESS)
But it is giving me the error as ‘Assembly 'JsonParser' references assembly 'system.net.http, version=4.0.0.0,’ as it was dependent on other built-in assembly System.Net.Http
so again tried to create the references assembly 'system.net.http’ But it is giving me the error as
‘CREATE ASSEMBLY failed because type 'System.Net.Http.HttpContent' in safe assembly 'System.Net.Http' has a static field 'EncodingsWithBom'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language’
Please help in this I did a lot of R&D but nothing worked for me. My version also in 2014, there is no planning to upgrade currently. I hope i'll get solution from experts here.
Thanks in advance
Hi lakshmiSundari,
HI Team,
I've called API in SQL Sever and it returning JSON. I was trying to store the result in variable VARCHAR(MAX). But my data is huge (around hundreads records with 10 to 20 columnns if we convert it as table in SQL) so the length is not supporting, so no data is storing in the variable, it is becoming NULL.
I'm using SQL server 2014.
Declare objectas Int;
Declare @ResponseTextas Varchar(MAX);
--Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', objectOUT;
Exec sp_OAMethod object, 'open', NULL, 'get',
APIurl,'false'
Exec sp_OAMethod object, 'setRequestHeader', null, 'Content-Type', 'application/xml'
Exec sp_OAMethod object, 'send'
Exec sp_OAMethod object, 'responseText', @ResponseTextOUTPUT
select @ResponseText
Select * from dbo.json_Parse(@ResponseText)
Exec sp_OADestroy object
Output : @ResponseText = NULL
and
I tried API consuming logic in .net and tried to create an assembly from SQL.(create ASSEMBLY jsontest from 'c:\dll\JsonParser.dll' WITH PERMISSION_SET =EXTERNAL_ACCESS)
But it is giving me the error as ‘Assembly 'JsonParser' references assembly 'system.net.http, version=4.0.0.0,’ as it was dependent on other built-in assembly System.Net.Http
so again tried to create the references assembly 'system.net.http’ But it is giving me the error as
‘CREATE ASSEMBLY failed because type 'System.Net.Http.HttpContent' in safe assembly 'System.Net.Http' has a static field 'EncodingsWithBom'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language’
Please help in this I did a lot of R&D but nothing worked for me. My version also in 2014, there is no planning to upgrade currently. I hope i'll get solution from experts here.
Thanks in advance