How to use a "SELECT TOP 1..." T-SQL query in a calc macro with MSSQL Server

Hi, I have a "SELECT TOP 1 * FROM … " query I would like to execute in a macro:

sub DoStuff()	
	oDBRange = thisComponent.DataBaseRanges.getByName("something")
	oDesc() = oDBRange.getImportDescriptor()
	For i = 0 to ubound(oDesc())
		oPrp = oDesc(i)
		If oPrp.Name = "DatabaseName" then
			oPrp.Value = "my_db"
		elseIf oPrp.Name = "SourceType" then
			oPrp.Value =
		elseIf oPrp.Name = "SourceObject" then
			oPrp.Value = "SELECT TOP 1 * FROM myTable ORDER BY val DESC"
		elseif oPrp.Name = "IsNative" then
			oPrp.Value = True
		oDesc(i) = oPrp
end sub

Calc returns invalid SQL error for this. It is ok with the same statement withtout the "TOP 1 ".

I looked at this answer this seems to be specific to data using the UI view with forms.

Is there some way to bypass this syntax check and pass it to MSSQL Server anyway?
Or some alternate syntax?
I could add a RowNumber to the table and add a “WHERE Row = 1” to the query but I’d prefer not to touch the DB schema.



Use Limit x

oPrp.Value = "SELECT * FROM myTable ORDER BY val DESC Limit 1"

Reference → MySQL LIMIT

An error is an error. Bypass is already stated with:

oPrp.Name = "IsNative" then
			oPrp.Value = True

This doesn’t work with Microsoft sql server equivalent. Is there an equivalent for T-SQL?

Sorry, did not look closely at The DB - Thought it was MySQL.

Don’t you have T-SQL documentation? Should be there.

How about this post → T-SQL SELECT TOP 1

It doesn’t look to be a SQL syntax error. I can run the same query with/without the TOP directly in SQL Server and it’s fine. But from the macro it’s failing…

I ran a profiler to see what is being sent to SQL Server too.
Without the top - it gets there with some modification added:

SELECT * FROM "odbc_source_namename"."dbo"."myTable"

With the top - it’s not reaching sql server at all. The Invalid Sql Exception happens somewhere before.

A workaround is to use the syntax for paging


But I’m still not sure why it’s failiing…

Appears you need to surround names (table/field) with quotes. Maybe:

"SELECT TOP 1 * FROM ""myTable"" ORDER BY ""val"" DESC"

Do not have MSSQL so cannot help any further.

No worries, I should be good with the paging workaround. Thanks for you help

Could be it has something to do with EscapeProcessing. In “normal” connections through macros you could set

oSQL_Statement = oConnection.createStatement()
oSQL_Statement.EscapeProcessing = False

You can easily import record sets into Calc without writing any macros.


elseif oPrp.Name = "IsNative" then
			oPrp.Value = True

IsNative is the opposite of direct SQL mode. IsNative means that Base parses the query before passing it to the driver. You want False for “IsNative”.

1 Like