SQL Server Get Collection

[LoganRogers1]

I am attempting to use the Get Collection action through the SQL Server object to run a procedure and return values to a collection but have not had success.

This action is proceeded by a Set Connection action in which I ensured the server and database are correctly entered. I have left the user and password fields blank, but I have been able to run the procedure just fine in the SQL Management Studio with my own Windows authentication, so I don't think it is a permission issue. 

By stepping through the Get Collection action, I observed the following message appear after running the Get Data Table code stage -> Fill: SelectCommand.Connection property has not been initialized. This leads me to believe there is a connection issue, but the Set Connection action appears to be working.

Lastly, I am questioning the format of the expression for the SQL input. I've attached an image which shows the exact expression window only with values changed to be generic. In my research, I've seen other formats used for this input variable. What I've done is copied the query directly from SQL. Let me know if this is the potential cause of the issue. 

Thank you!

[simone.tomaCNH]

Hi,

Have you tried running a simple select and not a stored procedure using the Get Collection action? One of the problems I have seen is that even if you leave the username and password fields empty, an error is generated, whereas if you pass double quotes in the two input fields of user and password, the connection works correctly. Maybe it is the same problem.

Simone

[Steve-Pete]

Thanks for that!  I was having inconsistent connections with a stored procedure.  I was about to (regrettably) revert to a standard query but this fixed it.

[sms]

Hi Steve,

Good that it resolved your issue, in case If you still have requirement to use Stored Procedure then try 
1. Execute action in SQL Server VBO - when you're not expecting to handle output
2. If you want to handle output then use Get Collection action - in order to avoid connection issues, as a workaround pass double quotes in username and password fields. 

Let me know how it goes.

[naveed_raza · Accepted solution]

i used same steps and its working fine for me.

- write your store procedure query in data item , refer to the screenshot

- Set Connection (here you can keep the user id and password blank if its windows authentication)

- Get Collection (here you can pass the store procedure query which starts with Exec

please test and let us know

[LoganRogers1]

I was able to get this resolved, but I want to leave a reply to help out anyone who may have the same issue.

For the set connection action, even though I have authentication tied to my windows environment, I ended up storing SQL creds in the credential manager and now call those credentials anytime I want to make an SQL connection. Doing it this way has fixed all issues related to the set connection. This also has the benefit of ensuring SQL work is functional regardless of which machine the process is run on.

For the query itself, I believe there were just syntax issues. Copying straight out of SQL manager seems to not have the correct format the majority of the time. If I were to redo the query from my initial post, the correct format would have to look like this:

Notable differences include removing the '--INT' and removing the comma from the last argument.