Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

SQL Server Get Collection

LoganRogers1
Level 2

‎13-05-25 08:02 PM

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. Example.PNG

Thank you!

0 Likes
3 REPLIES 3

simone.tomaCNH
Level 4

3 weeks ago

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
Level 2

2 weeks ago

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
Level 2
In response to Steve-Pete

2 weeks ago

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.

0 Likes