Support for structure.sql seems to be broken

activerecord-sqlserver-adapter (6.1.0.0) tiny_tds (2.1.5)

Compile-time settings (established with the "configure" script) Version: freetds v1.2.18 freetds.conf directory: /etc MS db-lib source compatibility: yes Sybase binary compatibility: no Thread safety: yes iconv library: yes TDS version: auto iODBC: no unixodbc: yes SSPI "trusted" logins: no Kerberos: no OpenSSL: yes GnuTLS: no MARS: yes

The structure.sql (config.active_record.schema_format = :sql) generated can't be imported (rails db:setup) again. Bugs noticed so far:

• Column names are not quoted. For example the column "key" of "ar_internal_metadata" causes: ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near the keyword 'key'.
• Views are not supported. Import fails with: ActiveRecord::StatementInvalid: TinyTds::Error: 'CREATE VIEW' must be the first statement in a query batch.

The "Column names are not quoted" issue is caused by defncopy from FreeTDS and is already opened as https://github.com/FreeTDS/freetds/pull/276

To demonstrate the issue do the following. Create a table with column name key (which Rails does in the ar_internal_metadata table).

CREATE TABLE dbo.table_with_column_named_key
( 
  [key]        nvarchar(4000)  NOT NULL
)

When you run "rails db:migrate" TinyTDS calls defncopy with a command similar to:

defncopy -S localhost -D [DATABASE NAME] -U [USERNAME] -P [PASSWORD] -o structure.sql table_with_column_named_key

If you open structure.sql it will contain:

CREATE TABLE dbo.table_with_column_named_key
	( key nvarchar(8000)  NOT NULL
	)
GO

If you run this manually in SQL Server you get the "Incorrect syntax near the keyword 'key'." error message. This issue would be fixed if "key" was wrapped in square brackets like "[key]" as "key" is a keyword in SQL Server.

I'm unable to recreate the "Views are not supported" issue. Could you provide steps on how to recreate the issue?

Still having this issue in 2024. https://github.com/FreeTDS/freetds/pull/276 was never merged.

Is there any workaround for using structure.sql?

Still having this issue in 2024. FreeTDS/freetds#276 was never merged.

Is there any workaround for using structure.sql?

@Michoels my comment here is where I last left my investigation-- unfortunately I won't have anytime soon to continue looking into this

Fixed in FreeTDS 1.4.12 and master branch

@aidanharan will a new release of activerecord-sqlserver-adapter be required to support FreeTDS 1.4.12? Or do I just need to upgrade my local FreeTDS and the current version will work OK?

And thanks so much to @freddy77, I'm looking forward to testing this!

I don't think a new release of the adapter is required, however I do not have the time to confirm this. Could you perform some testing and let us know your results?

We're now using FreeTDS 1.4.12 in production with the current release of activerecord-sqlserver-adapter.

It's working well with no issues 🥳

Need to confirm if the OP issue is now fixed.