community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

SQL Insert into (stored procedures) not working ("No target OLEDB object available")

Meteor

Hi All

 

Exploring the functionality of SQL server stored procedures in Alteryx. I have read the forums around using the Native SQL option which I am doing and I am able to get stored procedures working - but only when selecting. Whenever I am deleting, inserting, etc. I get a message:

 

Error: Input Data (21): Error: No target OLEDB object available.
in Query: EXEC MA_TEST2

 

In order to troubleshoot, I've created the most basic stored procedure I can. See below.

 

This is executed via an input data node and I've added a select statement at the end to return some data which I understand is the way it needs to work. 

 

The SQL code in the stored proc is as follows:

INSERT INTO [dbname].[dbo].[table]
(field1]
,[field2]
,[field3]
,[field4]
,[field5]
,[field6]
,[field7])
select '1','1','1','1','1','1','1'

 

select 'hello'

 

Any advice greatly received!

 

Cheers,
Matt

 

Inactive User
Not applicable

Going on an assumption but I do not see the purpose to do an insert SQL statement when you are in theory performing the Insert statement with the records you are loading to the DB? As for Deletes, if you have a record you need to delete input the DB into the flow, join it to that record, and then take the left/right of the join to a union and re-load the table (truncate and load); thus effectively deleting that existing record from the table. If the table is indexed or has keys set, use a SQL statement in that output tool to re-set those (in this scenario).

Highlighted
Meteor

@Inactive User wrote:

Going on an assumption but I do not see the purpose to do an insert SQL statement when you are in theory performing the Insert statement with the records you are loading to the DB? As for Deletes, if you have a record you need to delete input the DB into the flow, join it to that record, and then take the left/right of the join to a union and re-load the table (truncate and load); thus effectively deleting that existing record from the table. If the table is indexed or has keys set, use a SQL statement in that output tool to re-set those (in this scenario).


 

Hi

 

I am providing a simple example of the functionality I am trying to use. Simple answer as to why it is needed is that I am trying to leverage SQLserver to perform the heavy lifting for my analytics in some cases. Without going into the detail, the actual stored procedure could be inserting up to 100s of millions of lines into the database and based on querying data already in another table in the database. I realise I could probably rewrite the entire work in Alteryx but this is around understanding if this can be performed in Alteryx leveraging a SQL stored procedure. From what I can see, I can run the procedure as a Pre select SQL statement as a work around but  I then lose the ability to program the parameters as an input to a dynamic input node.

Labels