Docs‎ > ‎Integrate‎ > ‎Data Sources‎ > ‎

Microsoft SQL Server

This page describes several considerations specific to SQL Server.

Identity Columns

CA Live API Creator Logic supports identity columns.  For REST requests that perform insertion and provide a non-null value, we will correctly enable identity_insert before performing the insertion.

Sequences

SQL Server has added support for Sequences in its latest release. Live API Creator supports these sequences. In API Creator, under Database/Sequences, you can specify that column A in table FOO should use the sequence MySequence. Once that is specified, the sequence will be used to generate a value for the specified column for all inserts. Note that the same sequence can be used for more than one table.

Case Sensitivity

SQL Server is case-insensitive, but remembers case.  CA Live API Creator Logic treats all SQL Server tables and columns as case-sensitive. 

Boolean

SQL Server does not have a boolean type. 

Pagination

SQL Server has not traditionally provided simple support for pagination.  CA Live API Creator currently emulates pagination at the database level.   For most users, this will provide reasonable performance.


Connecting to SQL Server

Microsoft SQL Server can present some unique challenges. This page shows two common issues that may prevent you from obtaining connectivity to your database server.

SQL Server and Windows Authentication

Make sure that Server Authentication is set to SQL Server and Windows Authentication mode (in SQL Server Management Studio, right-click on server, select Properties):







Set the port number

Make sure that TCP/IP is enabled in SQL Server Configuration Manager, and that IP6 and IPAll (ignore the other ports, e.g. IP1, IP2, etc...) have an assigned TCP Port (note: this is TCP Port, not TCP Dynamic Ports).







Important - restart the service

If you had to make any changes, you will have to restart the SQL Server service.

The port you specified in step 2 (normally 1433) will be the one to use when connecting to the database.