1

I am trying to run an OPENQUERY to get data from a table connected with in a API. I am trying to do this via SQL server and having no joy.

I have looked at SSIS to query the data, but i don't know how to connect to an API XML server as my knowledge of SSIS is very little - does anyone know of a simple steps tutorial as would love to try

First i tried 

SELECT * FROM OPENQUERY ([https://server.com], ' SELECT * FROM Booking A')a

But realized that i needed to add a username and password

I have tried below with no joy

SELECT a.*
FROM OPENROWSET('SQLOLEDB','Data Source=https://webserver.com;Initial Catalog=dbName;Persist Security Info=True;User ID=server.com;Password=pass',
'select top 1 * from Booking') AS a

Can someone point me in the correct direction please, as I have tried SQL Server 2012 and SSIS 2010

Hadi
  • 36,233
  • 13
  • 65
  • 124
Joeysonic
  • 255
  • 3
  • 11
  • The publisher of the API should provide you with instructions on how to access their API. – Tab Alleman Jul 29 '19 at 15:20
  • An API can be many things. It looks like maybe your API is a _web_ API. I've never heard of accessing a web API from `OPENQUERY` but there are many guides on using SSIS to do it. – Nick.Mc Jul 29 '19 at 23:53
  • the only instructions I have received is the username and password, like above (slightly changed for the post) – Joeysonic Jul 30 '19 at 08:04
  • web API - there are many guides on using SSIS - Nick.McDermaid can you please point me in the right direction, sorry to ask as am just basic level with ssrs , and would love not just to get this above to work, but to learn – Joeysonic Jul 30 '19 at 08:05
  • @Joeysonic if you want to notify the user about your comment you have to add `@` before its name – Yahfoufi Jul 30 '19 at 13:54
  • it wont let me edit a comment – Joeysonic Jul 30 '19 at 20:22
  • I googled "using ssis to access a web api". Please google and try something and ask a specific question. – Nick.Mc Aug 06 '19 at 11:14

0 Answers0