This page describes several considerations specific to SQL Server.
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.
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.
SQL Server is case-insensitive, but remembers case. CA Live API Creator Logic treats all SQL Server tables and columns as case-sensitive.
SQL Server does not have a boolean type.
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):
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.