MS SQL Server Questions (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to FAQ (FireDAC)

This topic contains a list of questions and answers related to MS SQL Server.

Q1: SQL Server 2005: is it possible to use the Shared Memory transport with SQL Native Client?

A: In general:

  • Using the Client Network Utility, check that the Shared Memory protocol is enabled;
  • Use Server=(local) in your connection definition.

Q2: How can I get OriginTabName with SQL Server 2005?

A: Add ExtendedMetadata=True connection definition parameter.

Q3: When calling a stored procedure, I get "[FireDAC][Phys][ODBC]-345. Data too large for variable [#3]. Max len = [2], actual len = [14]". What is wrong?

A: In most cases, that happens when a stored procedure has a VARCHAR parameter defined without specifying a size. For example:

PROCEDURE ANALYZETABLE @T_OWNER VARCHAR, @T_TABLE VARCHAR AS
DECLARE @FOOBAR INTEGER;
BEGIN
  /* DUMMY PROCEDURE JUST FOR COMPATIBILITY PURPOSE */
  SET @FOOBAR = 1;
END;

Notes:

  • Exclude fiMeta from FetchOptions.Items, so FireDAC will not fetch the stored procedure parameters definition. This is required because ODBC driver describes @T_OWNER VARCHAR as VARCHAR(1).
  • Specify all parameters properties, including Size before Prepare or the first ExecProc call.

Q4: When calling a stored procedure, I get "[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0][SQL Server]Line 1: Incorrect syntax near '{'". What is wrong?

A: This is a known issue. It happens when:

  • The connection uses SQL Server Native Client 2008 ODBC driver;
  • The DBMS is SQL Server 2000;
  • And a stored procedure has a BLOB input parameter.

At the moment, the only workaround is to use SQL Server 2000 ODBC Driver or SQL Native Client 2005 when you connect to Microsoft SQL Server 2000.

Q5: When calling Array DML command, I get "[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 10.0]String data, length mismatch". What is wrong?

A: This is a known issue. It seems it is a bug in Microsoft SQL Native Client ODBC driver. We have not found a proper solution for that. The issue happens when one of the parameters has a BLOB data type (ftBlob, ftMemo, etc). As a workaround, set ResourceOptions.ArrayDMLSize to 1.

Q6: Why does the application raise the "Invalid object name '#<my temp table name>'" exception?

A: FireDAC application may raise the exception above when it works with Microsoft SQL Server local temporary tables. To demonstrate this, the following code reproduces the issue:

FDQuery1.ExecSQL('select * into #TmpOrd from [Orders]');
FDQuery1.Open('select * from #TmpOrd');

To resolve the issue, set TFDQuery.ResourceOptions.DirectExecute to True. This is also required when the application is extensively using:

  • Local temporary tables in the client SQL;
  • And/or the dynamic client SQL.

As an option, consider the use of global temporary tables.

Q7: I am getting a strange SQL error (8155) "No column was specified for column 1 of 'A'". What is wrong?

A: It seems that an application sets FetchOptions.RecsMax to a value greater than zero and executes a query with expressions in SELECT list. For example:

SELECT MIN(MyField) FROM MyTable WHERE MyIdField > 0

In that case, FireDAC modifies a query to:

SELECT TOP 10 * FROM (
  SELECT MIN(MyField) FROM MyTable WHERE MyIdField > 0
) A

In SQL Server, this syntax fails with the error above. To resolve this issue, specify the aliases for expressions in SELECT list.

Q8: I fail to get Chinese characters (Big5 encoding) from a database. How can I fix that?

A: Try adding the connection definition parameter:

ODBCAdvanced=AutoTranslate=no

And add the mapping rule:

  object FDConnection1: TFDConnection
.....................
    FormatOptions.AssignedValues = [fvMapRules]
    FormatOptions.OwnMapRules = True
    FormatOptions.MapRules = <
      item
        SourceDataType = dtAnsiString
        TargetDataType = dtWideString
      end>
  end

Also check that you have set the correct Chinese database character set and not Latin1.

Q9: When I insert a '2011-11-13 00:00' as a datetime value, I encounter the error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value". What am I doing wrong?

A: According to MSDN the used datetime format does not conform to the international standard. There are several solutions to avoid the error:

  • Use the SET DATEFORMAT option. For example, execute this to set the order of the year, month and day:
FDConnection1.ExecSQL('set dateformat ymd');
FDConnection1.ExecSQL('insert into Test (date_val) values (convert(datetime, ''2011-11-13 00:00'', 120)');
  • Change the format of your data to international standard:
FDConnection1.ExecSQL('insert into Test (date_val) values(''2011-11-13T00:00:00'');

Q10: When posting updates to SQL Server table, I get the error "Update command updated [N] instead of [1] record". What is the reason for that?

A: In most cases with SQL Server, this error happens when the table has a trigger which modifies the database explicitly or implicitly by calling a stored procedure that modifies the database. Then FireDAC receives the number of records modified by the trigger, instead of the number of records updated by the UPDATE command.

To avoid that, insert SET NOCOUNT ON at trigger beginning. Alternatively, set UpdateOptions.CountUpdatedRecords to False.

Q11: I declared some table columns as DATETIME2/DATE/TIME and FireDAC returns them as WideString. Or I get Type mismatch, expecting Date, actual WideString. What is wrong?

The DATETIME2/DATE/TIME were introduced in SQL Server 2008. SQL Server (SQL Server 2000) ODBC driver does not recognize these types and maps them to WideStrings. SQL Server Native Client v10 recognizes and represents them correctly. To resolve the issue, you should install SQL Server Native Client v10. It is not installed by default, but SQL Server ODBC driver is installed by default.