OLE Automation Procedures (i.e. sp_OA*) is a feature of Microsoft SQL Server that allows for calling OLE Automation objects and COM-visible code from within T-SQL batches.
For more information, please see: OLE Automation Objects in Transact-SQL
Questions tagged [ole-automation]
10 questions
6
votes
2 answers
Can I call SMO from pure T-SQL?
I know how to script a stored procedure using PowerShell and SMO:
[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") |…
bernd_k
- 12,369
- 24
- 79
- 111
6
votes
1 answer
When did sp_OACreate et. al. become deprecated?
I had an issue on an instance of SQL Server I'm supporting and many of the Stack sites have information on the sp_OA* stored procedures,
but most of them basically say "Don't use those; they are deprecated/buggy/leaky".
I'm going to try to implement…
akousmata
- 163
- 1
- 4
5
votes
3 answers
What's an example of an exploitation of the security from enabling Ole Automation Procedures Server Configuration option?
I have a need to enable to the Ole Automation Procedures Server Configuration Option on my SQL Server, but was wondering what specific kind of security risks do I incur by doing so?
What is an example of how the security can be exploited with this…
J.D.
- 40,776
- 12
- 62
- 141
2
votes
0 answers
sp_OACreate script returns Null value from API fetch? The API works well in a html file
I am trying to create a script in Microsoft SQL Server Management Studio to fetch the current exchange rate from an API and update a database. I am told it is not posisble and common to do this.
-- Change the database context
USE lndb;
-- Declare…
Luis Avalos
- 25
- 3
2
votes
1 answer
What is the best way to export/extract varbinary images in a SQL Server database to flat file via t sql, anything better than sp_OA methods?
I've been tasked to extract images saved in SQL Server (as varbinary) to flat files via t-sql. I haven't done ETL work for over a decade and I can't recall any other method of doing this via t-sql other than using sp_OACreate, sp_OAMethod etc.
Is…
Eric Swiggum
- 694
- 1
- 12
- 31
2
votes
2 answers
Export a table and zip with password
I would like, from TSQL, to use a view to extract data from a table, output it with BCP, then use non 3rd party tools to ZIP it with a password and AES encryption. Optionnally, I could use a 3rd party tool that doesn't require a local installation…
Philippe
- 517
- 6
- 15
1
vote
1 answer
Why do I see sp_OACreate, sp_OASetProperty in sp_WhoIsActive?
I've seen this happen a few times: sp_WhoIsActive shows various sp_OA% sprocs, with my user name, but I'm certainly not running them directly. Does this reflect some kind of query rewrite process? It seems to be correlated with queries across…
Jon of All Trades
- 5,987
- 7
- 48
- 63
0
votes
2 answers
Looking for recommendation
I am a non IT background, my recent projects uses PowerShell for a lot of SQL Server administration. I would like to start from absolute zero, and learn to use and code the Windows PowerShell as it was meant to be used. I am looking for a good book…
Goshu
- 13
- 4
0
votes
1 answer
Calling a web service from T-SQL (Stored Procedure) using MSXML
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF @hResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
SELECT hResult = convert(varbinary(4), @hResult),
…
Alfaiz Ahmed
- 136
- 1
- 4
-2
votes
1 answer
Huge PREEMPTIVE_OS_GETPROCADDRESS Waits From sp_OAMethod. How to debug?
Some queries are hitting my CPU extremely hard. sp_WhoIsActive reports that sp_OAMethod is the cause (the sql_text column points to it) and that it has of huge waits of the PREEMPTIVE_OS_GETPROCADDRESS wait type. Given that sp_OAMethod is a built-in…
J. Mini
- 1,161
- 8
- 32