Sql-server – Can SQL Server 2014 schema be defined using ODBC

odbcschemasql serversql server 2014

I am trying to use the ODBC driver for SQL Server 2014 to export data from an Autodesk Revit 2015 model to a database.

However, I would like to export to a default database (e.g. db1), creating a new schema for each data export.

Is it possible?

Best Answer

No, ODBC cannot set the schema.

ODBC is (in Wikipedia-speak) "a standard programming language middleware API for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems."

If you want an array of schemas, they need to be created in the database before they are used.

If you manage your import closely, then is should be possible to change the schema of objects after the import. E.g. from the SQL Server documentation: https://msdn.microsoft.com/en-us/library/ms173423.aspx

USE AdventureWorks2012;
GO
ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

This transfers the Address table from the Person schema to the HumanResources schema.

Additionally the CREATE SCHEMA command can be used to create objects within the schema. (Again, this is at the database level.)
https://msdn.microsoft.com/en-us/library/ms189462(v=sql.105).aspx

CREATE SCHEMA Sprockets AUTHORIZATION Annik
  CREATE TABLE NineProngs (source int, cost int, partnumber int)
  GRANT SELECT ON SCHEMA::Sprockets TO Mandar
  DENY SELECT ON SCHEMA::Sprockets TO Prasanna;