久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

<i id='VheZL'><tr id='VheZL'><dt id='VheZL'><q id='VheZL'><span id='VheZL'><b id='VheZL'><form id='VheZL'><ins id='VheZL'></ins><ul id='VheZL'></ul><sub id='VheZL'></sub></form><legend id='VheZL'></legend><bdo id='VheZL'><pre id='VheZL'><center id='VheZL'></center></pre></bdo></b><th id='VheZL'></th></span></q></dt></tr></i><div class="qwawimqqmiuu" id='VheZL'><tfoot id='VheZL'></tfoot><dl id='VheZL'><fieldset id='VheZL'></fieldset></dl></div>

<legend id='VheZL'><style id='VheZL'><dir id='VheZL'><q id='VheZL'></q></dir></style></legend>
<tfoot id='VheZL'></tfoot>

    <small id='VheZL'></small><noframes id='VheZL'>

    • <bdo id='VheZL'></bdo><ul id='VheZL'></ul>

        當 DataTypeCompatility 打開時,如何將 12/30/1899 參數

        How to parameterize 12/30/1899 to SQL Server native client when DataTypeCompatility is on?(當 DataTypeCompatility 打開時,如何將 12/30/1899 參數化為 SQL Server 本機客戶端?)

        1. <i id='zwf4w'><tr id='zwf4w'><dt id='zwf4w'><q id='zwf4w'><span id='zwf4w'><b id='zwf4w'><form id='zwf4w'><ins id='zwf4w'></ins><ul id='zwf4w'></ul><sub id='zwf4w'></sub></form><legend id='zwf4w'></legend><bdo id='zwf4w'><pre id='zwf4w'><center id='zwf4w'></center></pre></bdo></b><th id='zwf4w'></th></span></q></dt></tr></i><div class="qwawimqqmiuu" id='zwf4w'><tfoot id='zwf4w'></tfoot><dl id='zwf4w'><fieldset id='zwf4w'></fieldset></dl></div>
            • <bdo id='zwf4w'></bdo><ul id='zwf4w'></ul>
                <legend id='zwf4w'><style id='zwf4w'><dir id='zwf4w'><q id='zwf4w'></q></dir></style></legend>
                  <tbody id='zwf4w'></tbody>
                • <tfoot id='zwf4w'></tfoot>

                  <small id='zwf4w'></small><noframes id='zwf4w'>

                  本文介紹了當 DataTypeCompatility 打開時,如何將 12/30/1899 參數化為 SQL Server 本機客戶端?的處理方法,對大家解決問題具有一定的參考價值,需要的朋友們下面隨著小編來一起學習吧!

                  問題描述

                  限時送ChatGPT賬號..

                  短版

                  嘗試將 datetime12/30/1899 傳遞給 SQL Server,失敗,日期格式無效 - 但僅適用于本機客戶端驅動程序,并且僅在 DataTypeCompatiblity 模式下.

                  長版

                  嘗試在 ADO 中使用參數化查詢時,針對 SQL Server:

                  SELECT ?

                  我將 datetime 值參數化為 adDBTimeStamp:

                  //語言不可知,隱約似C#的偽代碼無效的測試它(){DateTime dt = new DateTime(3/15/2020");變體 v = DateTimeToVariant(dt);命令 cmd = 新命令();cmd.CommandText = "選擇?AS SomeDate";cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);連接 cn = GetConnection();cmd.Set_ActiveConnection(cn);cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);}

                  當日期為 3/15/2020 時效果很好.

                  您創建了一個 VARIANT,帶有 VType,共 7 個(VT_DATE),以及一個 8 字節浮點值:

                  VARIANTInt32 vt = 7;//VT_DATE雙日期 = 0;

                  但它在 12/30/1899 失敗

                  如果我用一個特定的日期時間做同樣的測試代碼,它就會失敗:

                  void TestIt(){DateTime dt = new DateTime(12/30/1899");變體 v = DateTimeToVariant(dt);命令 cmd = 新命令();cmd.CommandText = "選擇?AS SomeDate";cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);連接 cn = GetConnection();cmd.Set_ActiveConnection(cn);cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);}

                  ADO OLEDB 提供程序拋出異常(即在它甚至到達 SQL Server 之前):

                  無效的日期格式

                  但并非所有 SQL Server OLEDB 提供程序都會發生這種情況

                  在調試此問題時,我意識到并非所有 SQL Server OLEDB 提供程序都會發生這種情況.Microsoft 通常有 4 個 OLE DB Providers for SQL Server:

                  • SQLOLEDB:Microsoft OLE DB Provider for SQL Server(自 Windows 2000 起隨 Windows 一起提供)
                  • SQLNCLI:SQL Server Native Client(SQL Server 2005 附帶)
                  • SQLNCLI10:SQL Server Native Client 11.0(SQL Server 2008 附帶)
                  • SQLNCLI11:SQL Server Native Client 12.0(SQL Server 2012 附帶)
                  • MSOLEDBSQL:用于 SQL Server 的 Microsoft OLE DB 驅動程序(隨 SQL Server 2016 提供)

                  當與一些不同的提供商一起嘗試時,它確實對某些人來說效果很好:

                  • SQLOLEDB:有效
                  • SQLNCLI11(無數據類型兼容性):有效
                  • SQLNCLI11(啟用數據類型兼容性):失敗

                  數據類型兼容性?

                  是的.ActiveX 數據對象 (ADO),一個圍繞不友好 COM OLEDB API 的友好 COM 包裝器,不理解新的 datetimexmldatetime2datetimeoffset 數據類型.創建了新的 OLEDB 數據類型常量來表示這些新類型.因此,任何現有的 OLEDB 應用程序都不會理解新的常量.

                  為此,一個新的關鍵字由本機" OLE DB 驅動程序支持:

                  • DataTypeCompatibility=80

                  您可以將其添加到連接字符串中:

                  <塊引用>

                  "Provider=SQLNCLI11;數據源=螺絲刀;用戶 ID=hatguy;Password=hunter2;DataTypeCompatibility=80;"

                  這會指示 OLEDB 驅動程序僅返回首次發明 OLEDB 時存在的 OLEDB 數據類型:

                  <頭>
                  SQL Server 數據類型SQLOLEDBSQLNCLISQLNCLI
                  (w/DataTypeCompatibility=80)
                  XMLadLongVarWChar141 (DBTYPE_XML)adLongVarChar
                  日期時間adDBTimeStampadDBTimeStampadDBTimeStamp
                  datetime2adVarWCharadDBTimeStampadVarWChar
                  日期時間偏移adVarWChar146 (DBTYPE_DBTIMESTAMPOFFSET)adVarWChar
                  日期adVarWCharadDBDateadVarWChar
                  時間adVarWChar145 (DBTYPE_DBTIME2)adVarWChar
                  UDT132 (DBTYPE_UDT)adVarBinary(已記錄,未經測試)
                  varchar(max)adLongVarCharadLongVarCharadLongVarChar
                  nvarchar(max)adLongVarWCharadLongVarWCharadLongVarWChar
                  varbinary(max)adLongVarBinaryadLongVarBinaryadLongVarBinary
                  時間戳adBinaryadBinaryadBinary

                  失敗了

                  什么時候:

                  • 嘗試參數化 datetime
                  • 值為 12/30/1899
                  • 使用本地客戶端"驅動程序時
                  • DataTypeCompatilibty 已開啟
                  • 驅動程序本身因值而窒息
                  • 當它的價值實際上非常好時.

                  嘗試使用12/30/1899"這個日期并沒有本質上的錯誤:

                  • SELECT CAST('18991230' AS datetime) 工作正常
                  • 它在原始 OLE DB 驅動程序中運行良好
                  • 它在本機" OLE DB 驅動程序中運行良好
                  • 它只是在帶有 DataTypeCompatibility 的本機驅動程序中失敗

                  顯然這是 Microsoft OLE DB 驅動程序中的錯誤.但微軟永遠不會、永遠永遠永遠修復這個錯誤,這是一個絕對的事實.>

                  那么如何解決這個問題?

                  我可以檢測到這個特殊的日期時間,我可以嘗試解決我們數據訪問層中的這個錯誤.

                  • 但我需要一個可以放入VARIANT結構的值,
                  • 表示12/30/1899 12:00:00 AM
                  • SQOLEDB
                  • 下工作
                  • SQLNCLIxx 驅動程序
                  • MSOLEDBSQL驅動程序
                  • DataTypeCompatibilityMode
                  • (這到底是怎么回事,即使關閉了模式 - 雖然在沒有打開它的情況下使用 ADO 是無效的)

                  驅動生成的T-SQL

                  當 OLE DB 驅動程序確實真正按照我說的去做時,我們可以分析生成的 RPC:

                  SQOLEDB

                  <塊引用>

                  exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime','1899-12-30 00:00:00'

                  SQLNCLI11

                  <塊引用>

                  exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime2(0)','1899-12-30 00:00:00'

                  CMRE(德爾福)

                  program Project1;{$APPTYPE 控制臺}{$R *.res}用途System.SysUtils,對象,ActiveX,數據庫,注意,變體;函數 GetConnection(Provider: string; DataTypeCompatibility: Boolean): _Connection;無功連接字符串:字符串;開始{SQLOLEDB - Windows 的默認提供程序SQLNCLI11 - SQL Server 2008 本機客戶端}connectionString := 'Provider='+Provider+';數據源=螺絲刀;用戶ID=氫;密碼=hunter2;';如果 DataTypeCompatibility 那么connectionString := connectionString+'DataTypeCompatibility=80';結果:= CoConnection.Create;Result.Open(connectionString, '', '', adConnectUnspecified);結尾;過程測試(提供者名稱:字符串;數據類型兼容性:布爾值);無功dt: 日期時間;v:OleVariant;cmd:_命令;cn:_連接;受影響的記錄:OleVariant;s:字符串;開始dt := EncodeDate(1899, 12, 30);//12/30/1899 12:00:00 AM(在 Delphi 中也稱為零)v := dt;//該變體的類型為 VT_DATE (7)cmd := CoCommand.Create;cmd.CommandText := '選擇 ?AS SomeDate';cmd.Parameters.Append(cmd.CreateParameter('', adDBTimeStamp, adParamInput, 0, v));嘗試cn := GetConnection(ProviderName, DataTypeCompatibility);除了在 E: 例外做開始WriteLn('Provider '+ProviderName+' 未安裝: '+E.message);出口;結尾;結尾;如果 SameText(ProviderName, 'SQLOLEDB') 那么s := ''否則如果 DataTypeCompatibility thens := '(具有數據類型兼容性)'別的s := '(沒有數據類型兼容性)';cmd.Set_ActiveConnection(cn);嘗試cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);WriteLn('Provider '+ProviderName+s+': 成功.');除了在 E:Exception 上做開始WriteLn('Provider '+ProviderName+s+' 失敗:'+E.Message);結尾;結尾;結尾;程序主要;開始CoInitialize(nil);測試('SQLOLEDB',假);//自 2000 年以來隨 Windows 提供的 SQL Server 客戶端測試('SQLNCLI',假);//SQL Server 2005 原生客戶端測試('SQLNCLI',真);//SQL Server 2005 本機客戶端,具有數據類型兼容性測試('SQLNCLI10',假);//SQL Server 2008 原生客戶端測試('SQLNCLI10',真);//SQL Server 2008 本機客戶端,具有數據類型兼容性測試('SQLNCLI11',假);//SQL Server 2012 原生客戶端測試('SQLNCLI11',真);//SQL Server 2012 本機客戶端,具有數據類型兼容性測試('MSOLEDBSQL',假);//SQL Server 2016 原生客戶端測試('MSOLEDBSQL',真);//SQL Server 2016 本機客戶端,具有數據類型兼容性結尾;開始嘗試主要的;除了在 E: 例外做Writeln(E.ClassName, ': ', E.Message);結尾;WriteLn('按回車鍵關閉');讀Ln;結尾.

                  雖然這不是特定于 Delphi 的問題;我正在使用德爾福.所以它被標記為Delphi.如果你抱怨我會噎死你的舌頭.

                  <塊引用>

                  注意:這不是 ADO.net,而是 ADO.它不是托管的 .NET Framework 類庫,而是本機 Win32 COM OLE DB API.

                  解決方案

                  BrakNicku 給出了答案.

                  <塊引用>

                  將參數的 NumericScale 屬性設置為 1-7 范圍內的任何值.

                  更改代碼:

                  參數 p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);

                  參數 p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);p.NumericScale = 1;

                  有效.

                  它甚至適用于 SQL Server 2000 的 SQLOLEDB 驅動程序.

                  不同數據類型的精度和規模

                  從包含不同數據類型的SQL Server返回行集,我可以問OLEDB各種T-SQL數據類型的PrecisionNumericScale是什么:

                  SQL Server 類型 ADO 類型 Precision NumericScale DefinedSize---------------- --------------------- --------- ----————————————int adInteger (3) 10 255 4真實廣告單 (4) 7 255 4貨幣 adCurrency (6) 19 255 8位 adBoolean (11) 255 255 2tinyint adUnsignedTinyInt (17) 3 255 1bigint adBigInt (20) 19 255 8唯一標識符 adGUID (72) 255 255 16char(35) adChar (129) 255 255 35nchar(35) adWChar (130) 255 255 35十進制(15,5)和數字(131)15 5 19日期時間 adDBTimeStamp (135) 23 3 16varchar(35) adVarChar (200) 255 255 35文本 adLongVarChar (201) 255 255 2147483647varchar(max) adLongVarChar (201) 255 255 2147483647nvarchar(35) adVarWChar (202) 255 255 35nvarchar(max) adLongVarWChar (203) 255 255 1073741823xml adLongVarWChar (203) 255 255 1073741823圖像 adLongVarBinary (205) 255 255 2147483647varbinary(max) adLongVarBinary (205) 255 255 2147483647

                  因為 SQL Server 返回一個 datetime 字段,NumericScale3可能改變是一種美德:

                  參數 p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);p.NumericScale = 1;

                  參數 p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);p.NumericScale = 3;

                  獎勵閱讀

                  永遠不要、永遠永遠嘗試將datetime參數化為adDBTimestamp.Microsoft 的 SQL Server OLEDB 驅動程序中存在數據丟失錯誤(所有這些錯誤):

                  • SQLOLEDB (1999) - 失敗
                  • SQLNCLI (2005) - 失敗
                  • SQLNCLI10 (2008) - 失敗
                  • SQLNCLI11 (2010) - 失敗
                  • MSOLEDBSQL (2012) - 失敗

                  正確答案是將所有 datetime 值參數化為字符串(例如 adVarChar) 使用 ODBC 24 小時格式":

                  • yyyy-mm-dd hh:mm:ss.zzz
                  • 2021-03-21 18:16:22.619

                  Short Version

                  Trying to pass the datetime value 12/30/1899 to SQL Server, fails with Invalid date format - but only for the native client drivers, and only in DataTypeCompatiblity mode.

                  Long Version

                  When trying to use parameterized queries in ADO, against SQL Server:

                  SELECT ?
                  

                  I parameterize the datetime value as an adDBTimeStamp:

                  //Language agnostic, vaguely C#-like pseudo-code
                  void TestIt()
                  {
                     DateTime dt = new DateTime("3/15/2020");
                     VARIANT v = DateTimeToVariant(dt);
                  
                     Command cmd = new Command();
                     cmd.CommandText = "SELECT ? AS SomeDate";
                     cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
                     
                     Connection cn = GetConnection();
                     cmd.Set_ActiveConnection(cn);
                     cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);
                  }
                  

                  And that works fine when the date is 3/15/2020.

                  You create a VARIANT, with a VType of 7 (VT_DATE), and a value that is an 8-byte floating point value:

                  VARIANT
                     Int32  vt = 7; //VT_DATE
                     Double date = 0;
                  

                  But it fails on 12/30/1899

                  If I do the same test code with one particular datetime, it fails:

                  void TestIt()
                  {
                     DateTime dt = new DateTime("12/30/1899");
                     VARIANT v = DateTimeToVariant(dt);
                  
                     Command cmd = new Command();
                     cmd.CommandText = "SELECT ? AS SomeDate";
                     cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
                     
                     Connection cn = GetConnection();
                     cmd.Set_ActiveConnection(cn);
                     cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);
                  }
                  

                  The ADO OLEDB provider throws an exception (i.e. before it even reaches SQL Server):

                  Invalid date format
                  

                  But it doesn't happen with all SQL Server OLEDB providers

                  When debugging this issue, I realized it doesn't happen with all of the SQL Server OLEDB providers. Microsoft generally has 4 OLE DB Providers for SQL Server:

                  • SQLOLEDB: Microsoft OLE DB Provider for SQL Server (has shipped with Windows since Windows 2000)
                  • SQLNCLI: SQL Server Native Client (shipped with SQL Server 2005)
                  • SQLNCLI10: SQL Server Native Client 11.0 (shipped with SQL Server 2008)
                  • SQLNCLI11: SQL Server Native Client 12.0 (shipped with SQL Server 2012)
                  • MSOLEDBSQL: Microsoft OLE DB Driver for SQL Server (shipped with SQL Server 2016)

                  When trying it with some different providers, it does work fine for some:

                  • SQLOLEDB: Works
                  • SQLNCLI11 (without DataTypeCompatibility): Works
                  • SQLNCLI11 (with DataTypeCompatiility on): Fails

                  DataTypeCompatibility?

                  Yes. ActiveX Data Objects (ADO), a friendly COM wrapper around the unfriendly COM OLEDB API, doesn't understand the new date, time, xml, datetime2, datetimeoffset data types. New OLEDB data type constants were created to represents these new types. So any existing OLEDB applications wouldn't understand the new constants.

                  To that end, a new keyword is supported by the "native" OLE DB drivers:

                  • DataTypeCompatibility=80

                  which you can add to your connection string:

                  "Provider=SQLNCLI11; Data Source=screwdriver; User ID=hatguy; Password=hunter2;DataTypeCompatibility=80;"

                  This instructs the OLEDB driver to only return OLEDB data types that were in existance when OLEDB was first invented:

                  SQL Server data type SQLOLEDB SQLNCLI SQLNCLI
                  (w/DataTypeCompatibility=80)
                  Xml adLongVarWChar 141 (DBTYPE_XML) adLongVarChar
                  datetime adDBTimeStamp adDBTimeStamp adDBTimeStamp
                  datetime2 adVarWChar adDBTimeStamp adVarWChar
                  datetimeoffset adVarWChar 146 (DBTYPE_DBTIMESTAMPOFFSET) adVarWChar
                  date adVarWChar adDBDate adVarWChar
                  time adVarWChar 145 (DBTYPE_DBTIME2) adVarWChar
                  UDT 132 (DBTYPE_UDT) adVarBinary (documented,untested)
                  varchar(max) adLongVarChar adLongVarChar adLongVarChar
                  nvarchar(max) adLongVarWChar adLongVarWChar adLongVarWChar
                  varbinary(max) adLongVarBinary adLongVarBinary adLongVarBinary
                  timestamp adBinary adBinary adBinary

                  And there's the failure

                  When:

                  • trying to parameterize a datetime value
                  • with a value of 12/30/1899
                  • when using a "native client" driver
                  • and DataTypeCompatilibty is on
                  • the driver itself chokes on the value
                  • when its value is, in fact perfectly fine.

                  There's nothing inherently wrong with trying to use a date of '12/30/1899`:

                  • SELECT CAST('18991230' AS datetime) works fine
                  • it works fine in the original OLE DB driver
                  • it works fine in the "native" OLE DB drivers
                  • it just fails in the native driver with DataTypeCompatibility on

                  Obviously this is a bug in Microsoft OLE DB drivers. But it's an absolute truth that Microsoft will never, ever, ever, EVER, fix the bug.

                  So how to work-around it?

                  I can detect this special datetime, and I can try to work around this bug in our data access layers.

                  • But I need a value I can place into a VARIANT structure,
                  • that represents 12/30/1899 12:00:00 AM
                  • that works under SQOLEDB
                  • and under SQLNCLIxx drivers
                  • and under MSOLEDBSQL driver
                  • in DataTypeCompatibilityMode
                  • (and what the hell, even with the mode off - although it's invalid to use ADO without it on)

                  T-SQL generated by the driver

                  When the OLE DB driver does bother to actually do what i say, we can profile the RPC generated:

                  SQOLEDB

                  exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime','1899-12-30 00:00:00'

                  SQLNCLI11

                  exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime2(0)','1899-12-30 00:00:00'

                  CMRE (Delphi)

                  program Project1;
                  
                  {$APPTYPE CONSOLE}
                  
                  {$R *.res}
                  
                  uses
                    System.SysUtils,
                    ComObj,
                    ActiveX,
                    ADOdb,
                    ADOint,
                    Variants;
                  
                  function GetConnection(Provider: string; DataTypeCompatibility: Boolean): _Connection;
                  var
                      connectionString: string;
                  begin
                  {
                      SQLOLEDB - Default provider with Windows
                      SQLNCLI11 - SQL Server 2008 native client
                  }
                      connectionString := 'Provider='+Provider+'; Data Source=screwdriver;User ID=hydrogen;Password=hunter2;';
                      if DataTypeCompatibility then
                          connectionString := connectionString+'DataTypeCompatibility=80';
                  
                      Result := CoConnection.Create;
                      Result.Open(connectionString, '', '', adConnectUnspecified);
                  end;
                  
                  procedure Test(ProviderName: string; DataTypeCompatibility: Boolean);
                  var
                      dt: TDateTime;
                      v: OleVariant;
                      cmd: _Command;
                      cn: _Connection;
                      recordsAffected: OleVariant;
                      s: string;
                  begin
                      dt := EncodeDate(1899, 12, 30);// 12/30/1899 12:00:00 AM (also known in Delphi as zero)
                      v := dt; //the variant is of type VT_DATE (7)
                  
                      cmd := CoCommand.Create;
                      cmd.CommandText := 'SELECT ? AS SomeDate';
                      cmd.Parameters.Append(cmd.CreateParameter('', adDBTimeStamp, adParamInput, 0, v));
                  
                      try
                          cn := GetConnection(ProviderName, DataTypeCompatibility);
                      except
                          on E: Exception do
                              begin
                                  WriteLn('Provider '+ProviderName+' not installed: '+E.message);
                                  Exit;
                              end;
                      end;
                  
                      if SameText(ProviderName, 'SQLOLEDB') then
                          s := ''
                      else if DataTypeCompatibility then
                          s := ' (with DataTypeCompatibility)'
                      else
                          s := ' (without DataTypeCompatibility)';
                  
                      cmd.Set_ActiveConnection(cn);
                      try
                          cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);
                          WriteLn('Provider '+ProviderName+s+': success.');
                      except
                          on E:Exception do
                              begin
                                  WriteLn('Provider '+ProviderName+s+' failed: '+E.Message);
                              end;
                      end;
                  
                  end;
                  
                  procedure Main;
                  begin
                      CoInitialize(nil);
                  
                      Test('SQLOLEDB', False);        //SQL Server client that ships with Windows since 2000
                  
                      Test('SQLNCLI', False);     //SQL Server 2005 native client
                      Test('SQLNCLI', True);      //SQL Server 2005 native client, w/ DataTypeCompatibilty
                  
                      Test('SQLNCLI10', False);   //SQL Server 2008 native client
                      Test('SQLNCLI10', True);    //SQL Server 2008 native client, w/ DataTypeCompatibilty
                  
                      Test('SQLNCLI11', False);   //SQL Server 2012 native client
                      Test('SQLNCLI11', True);    //SQL Server 2012 native client, w/ DataTypeCompatibilty
                  
                      Test('MSOLEDBSQL', False);  //SQL Server 2016 native client
                      Test('MSOLEDBSQL', True);   //SQL Server 2016 native client, w/ DataTypeCompatibilty
                  end;
                  
                  
                  begin
                    try
                      Main;
                    except
                      on E: Exception do
                        Writeln(E.ClassName, ': ', E.Message);
                    end;
                      WriteLn('Press enter to close');
                      ReadLn;
                  end.
                  

                  And while this is not a Delphi-specific question; I am using Delphi. So it's tagged as Delphi. If you complain I'm going to choke your tongue out.

                  Note: This is not ADO.net, it is ADO. It is not managed .NET Framework Class Library, it is the native Win32 COM OLE DB API.

                  解決方案

                  BrakNicku had the answer.

                  Set NumericScale property of your parameter to anything in 1-7 range.

                  Changing the code from:

                  Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
                  

                  to

                  Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
                  p.NumericScale = 1;
                  

                  works.

                  It even works with SQLOLEDB driver against SQL Server 2000.

                  Precision and Scale of different data types

                  Returning rowsets from SQL Server containing different data types, i can ask OLEDB what the Precision, and NumericScale of various T-SQL data types are:

                  SQL Server type   ADO type               Precision  NumericScale  DefinedSize
                  ----------------  ---------------------  ---------  ------------  -----------
                  int               adInteger (3)          10         255           4
                  real              adSingle (4)           7          255           4
                  money             adCurrency (6)         19         255           8
                  bit               adBoolean (11)         255        255           2
                  tinyint           adUnsignedTinyInt (17) 3          255           1
                  bigint            adBigInt (20)          19         255           8
                  uniqueidentifier  adGUID (72)            255        255           16
                  char(35)          adChar (129)           255        255           35
                  nchar(35)         adWChar (130)          255        255           35
                  decimal(15,5)     adNumeric (131)        15         5             19
                  datetime          adDBTimeStamp (135)    23         3             16
                  varchar(35)       adVarChar (200)        255        255           35
                  text              adLongVarChar (201)    255        255           2147483647
                  varchar(max)      adLongVarChar (201)    255        255           2147483647
                  nvarchar(35)      adVarWChar (202)       255        255           35
                  nvarchar(max)     adLongVarWChar (203)   255        255           1073741823
                  xml               adLongVarWChar (203)   255        255           1073741823
                  image             adLongVarBinary (205)  255        255           2147483647
                  varbinary(max)    adLongVarBinary (205)  255        255           2147483647
                  

                  Since SQL Server returns a datetime field with a NumericScale of 3; there may be a virtue in changing:

                  Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
                  p.NumericScale = 1;
                  

                  to

                  Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
                  p.NumericScale = 3;
                  

                  Bonus Reading

                  Don't ever, ever, EVER try to parameterize a datetime as adDBTimestamp. There are data-loss bugs in Microsoft's SQL Server OLEDB drivers (all of them):

                  • SQLOLEDB (1999) - Fails
                  • SQLNCLI (2005) - Fails
                  • SQLNCLI10 (2008) - Fails
                  • SQLNCLI11 (2010) - Fails
                  • MSOLEDBSQL (2012) - Fails

                  The correct answer is to parameterize all datetime values as a string (e.g. adVarChar) using the "ODBC 24-hour format":

                  • yyyy-mm-dd hh:mm:ss.zzz
                  • 2021-03-21 18:16:22.619

                  這篇關于當 DataTypeCompatility 打開時,如何將 12/30/1899 參數化為 SQL Server 本機客戶端?的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!

                  【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題,如果有圖片或者內容侵犯了您的權益,請聯系我們刪除處理,感謝您的支持!

                  相關文檔推薦

                  Can I figure out a list of databases and the space used by SQL Server instances without writing SQL queries?(我可以在不編寫 SQL 查詢的情況下找出數據庫列表和 SQL Server 實例使用的空間嗎?) - IT屋-程序員軟件開發
                  How to create a login to a SQL Server instance?(如何創建對 SQL Server 實例的登錄?)
                  How to know the version and edition of SQL Server through registry search(如何通過注冊表搜索知道SQL Server的版本和版本)
                  Why do I get a quot;data type conversion errorquot; with ExecuteNonQuery()?(為什么會出現“數據類型轉換錯誤?使用 ExecuteNonQuery()?)
                  How to show an image from a DataGridView to a PictureBox?(如何將 DataGridView 中的圖像顯示到 PictureBox?)
                  WinForms application design - moving documents from SQL Server to file storage(WinForms 應用程序設計——將文檔從 SQL Server 移動到文件存儲)

                  <small id='pORg3'></small><noframes id='pORg3'>

                  1. <legend id='pORg3'><style id='pORg3'><dir id='pORg3'><q id='pORg3'></q></dir></style></legend>
                      <i id='pORg3'><tr id='pORg3'><dt id='pORg3'><q id='pORg3'><span id='pORg3'><b id='pORg3'><form id='pORg3'><ins id='pORg3'></ins><ul id='pORg3'></ul><sub id='pORg3'></sub></form><legend id='pORg3'></legend><bdo id='pORg3'><pre id='pORg3'><center id='pORg3'></center></pre></bdo></b><th id='pORg3'></th></span></q></dt></tr></i><div class="qwawimqqmiuu" id='pORg3'><tfoot id='pORg3'></tfoot><dl id='pORg3'><fieldset id='pORg3'></fieldset></dl></div>

                        <tbody id='pORg3'></tbody>

                        • <bdo id='pORg3'></bdo><ul id='pORg3'></ul>

                            <tfoot id='pORg3'></tfoot>

                          1. 主站蜘蛛池模板: 欧美色偷拍 | 国产激情视频网站 | 午夜男人免费视频 | 亚洲成人av一区二区 | a在线视频观看 | 伊人亚洲 | 国产精品视频免费看 | 99久久久国产精品 | 成年男女免费视频网站 | 亚洲 欧美 另类 综合 偷拍 | 日韩一区二区三区视频 | 午夜免费电影 | 国产99久久精品一区二区永久免费 | 亚洲精品1区 | 亚洲一本| 精品久久香蕉国产线看观看亚洲 | 欧美 日韩 国产 在线 | 国产精品精品视频一区二区三区 | 91伦理片 | 国产福利资源在线 | 99热都是精品 | 91久久精品国产免费一区 | 国产伦精品一区二区三区照片91 | 亚洲精品欧美一区二区三区 | 日韩高清中文字幕 | 亚洲精品美女在线观看 | 国产一区二 | 精品一区二区三区四区 | 国产精品免费av | 伊人免费视频二 | 国产一区二区三区免费观看在线 | 午夜在线电影网 | 日韩中文字幕在线播放 | 一本一道久久a久久精品综合蜜臀 | 欧美在线观看一区二区 | 亚洲午夜精品一区二区三区他趣 | 欧美一级久久 | 免费人成激情视频在线观看冫 | 日韩欧美电影在线 | 91在线看 | 毛片一区二区三区 |