The list of questions and answers related to MS SQL Server.
A: In general:
A: Add ExtendedMetadata=True connection definition parameter.
A: That happens in most cases, 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;
The following code will work:
with ADStoredProc1 do begin FetchOptions.Items := FetchOptions.Items - [fiMeta]; StoredProcName := 'ANALYZETABLE'; Params.Clear; Params.CreateParam(ftString, 'T_OWNER', ptInput).Size := 20; Params.CreateParam(ftString, 'T_TABLE', ptInput).Size := 20; Prepare; ParamByName('T_OWNER').AsString := ''; ParamByName('T_TABLE').AsString := TableName; ExecProc; end;
A: It is a known issue. It happens when:
At moment only workaround is to use SQL Server 2000 ODBC Driver or SQL Native Client 2005, when you are connecting to the Microsoft SQL Server 2000.
A: It is a known for me issue. It seems it is a bug in Microsoft SQL Native Client ODBC driver. For a while, 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.
A: The AnyDAC application may raise the exception above, when it is working with the Microsoft SQL Server local temporary tables. To demonstrate this, the following code reproduces the issue:
ADQuery1.ExecSQL('select * into #TmpOrd from [Orders]'); ADQuery1.Open('select * from #TmpOrd');
As an option, consider to use global temporary tables.
A: It seems 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 AnyDAC modifies a query to:
SELECT TOP 10 * FROM ( SELECT MIN(MyField) FROM MyTable WHERE MyIdField > 0 ) A
On SQL Server this syntax fails with the above error. To resolve this issue, specify the aliases for expressions in SELECT list.
A: Try to add connection definition parameter:
And add the mapping rule:
object ADConnection1: TADConnection ..................... FormatOptions.AssignedValues = [fvMapRules] FormatOptions.OwnMapRules = True FormatOptions.MapRules = < item SourceDataType = dtAnsiString TargetDataType = dtWideString end> end
Also check that you have set correct Chinese database character set and not Latin1.
A: According to MSDN the used datetime format does not conform to the international standard. There are several solutions to avoid the error:
ADConnection1.ExecSQL('set dateformat ymd');
ADConnection1.ExecSQL('insert into Test (date_val) values (convert(datetime, ''2011-11-13 00:00'', 120)');
ADConnection1.ExecSQL('insert into Test (date_val) values(''2011-11-13T00:00:00'');
A: In most cases with SQL Server this error happens when the table has a trigger which modifies the DB explicitly, or implicitly by calling a stored procedure which modified the DB. Then AnyDAC receives not the number of records updated by UPDATE command, but the number of records modified by the trigger.
To avoid that insert SET NOCOUNT ON at trigger beginning. Or set UpdateOptions.CountUpdatedRecords to False.
The DATETIME2 / DATE / TIME were introduced in SQL Server 2008. "SQL Server" (SQL Server 2000) ODBC driver does not know these types and maps them to WideStrings. The SQL Server Native Client v 10 knows them and represents correctly. To resolve the issue you should install SQL Server Native Client v 10. It is not installed by default, but "SQL Server" ODBC driver is installed by default.
What do you think about this topic? Send feedback!