0

I'm trying to export data from SQL Server to Excel with OpenRowSet.

The data does get exported to the Excel sheet datosOtraManera, BUT formulas are not automatically recalculated because Excel doesn't recognize the new data. This problem appears when we have to update from ...OLEDB.12 to OLEDB.15

I tried the following statement:

SET @SQL ='
    INSERT INTO 
    OPENROWSET (''Microsoft.ACE.OLEDB.15.0'', 
                ''Excel 12.0;Database='+ @TmpXLSX +''', 
                        ''SELECT * FROM [datosOtraManera$]'')
            SELECT getdate() '

EXEC sp_executesql @SQL
Improve this question
8
  • 1
    Honestly, don't go down this route. SQL Server isn't the right tool for exporting data. Use an ETL tool, use Python, use C#, use PowerShell, use, well anything other than SQL Server for the export process. Commented Sep 12 at 14:26
  • 1
    The ACE provider is not supported by Microsoft to be loaded into SQL Server, it's only intended for front-end apps. Why not just use PowerQuery inside Excel and have Excel pull the data in, rather than the other way round? Commented Sep 12 at 16:56
  • mssqltips.com/sqlservertip/6178/… Commented Sep 13 at 8:54
  • 1
    @AndreyS Note that: the ACE provider is not supported for non-user apps (it works, sort of, but MS won't give you any help). And OP wants to write not read so this is just a complete XY problem, there are much easier ways to achieve this. Commented Sep 13 at 23:58
  • How do you know the formulas aren't recalculated? Do you actually open the file in excel and it doesn't show correct data? Does the file even get the new "date"? Commented Sep 15 at 12:36
Related questions
4342
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
785
What is the best way to auto-generate INSERT statements for a SQL Server table?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.