2
Vote

Assorted Feature Requests

description

  • Ability to return ado.net (not strongly typed) datasets
  • Ability to capture InfoMessages (i.e. not for display only)
  • Shortcuts for executing a stored procedure (command type of stored procedure)

comments

StephenMills wrote Aug 15, 2010 at 9:42 PM

1 - Datasets are sort of a pain to work with in PowerShell, I prefer the way it works now - it would also make it harder to do some things in the future.
2 - I agree - Please add the ability to capture Info Messages to Store in a variable or a custom object
3 - Not sure what you mean. Do you mean a -StoredProcedure parameter? So it knows that it is executing a stored procedure?

Just my 2 cents worth.

zespri wrote Aug 15, 2010 at 10:24 PM

1 - this is why I request this as an option. Surely, that this is option is inconvenient for you. But it can be convenient for someone else.
3 - I mean in terms of ADO.NET CommandType. I want to be able to run Commands with CommandType StoredProcedure. This way I don't need to specify stored procedure parameters in the query itself, only in the -parameters collection

wrote Aug 16, 2010 at 8:41 PM

TTRider wrote Aug 16, 2010 at 9:04 PM

  1. No Problem - if you need it, I'll add this to the next release
  2. Let me think about how to add it the most usable way. I may need to give you several options to test ....
  3. No Problem - if you specify a parameter, you can just pass sproc name and i'll flip SqlCommand switch accordingly.

wrote Aug 16, 2010 at 9:08 PM

TTRider wrote Aug 17, 2010 at 12:40 AM

1 and #3 has been implemented and released as 1.4 Alpha01 release.

StephenMills wrote Aug 17, 2010 at 3:40 AM

I'm not sure that -Parameters should mean a stored proc. You can use parameters without it being a proc. For Example:
Invoke-SqlQuery -Server 'Server1' -Query "select * from sysprocesses where spid = @spid" -Parameters @{'@spid'=1}
or you could use it for inserts.
Invoke-SqlQuery -Server 'mills4\sqlexpress' -Query "insert OnlineGame (Name, Description) values(@Name, @Description)" -Parameters @{'Name'='Eve-Online', 'MMORPG set in space with lots of PVP'}
If you assume it is a stored proc, then the insert/select will fail. Having it use parameters for these also gives you the advantages of being able to have your query separate from your other logic and you don't have to worry about weird characters that you should escape in SQL Server.
I would suggest a switch parameter -IsStoredProcedure. That way you can tell it that it's a stored proc and have it use the CommandType of StoredProcedure when needed.

zespri wrote Aug 17, 2010 at 6:26 AM

@StephenMills: I agree.

wrote Feb 13, 2013 at 2:21 AM