SQL Server 2005 SP2

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

SQL Server 2005 SP2

Post by Ludek » Tue 20 Feb 2007 16:32

Hi,
I'm having trouble after installation of service pack 2 for sql server 2005.
If I place following query into the TMSQuery component

Code: Select all

select * from master.dbo.sysprocesses
I get Error in TDS-Stream.

In the ConnectionString I'm using the SQLOLEDB.1 provider, because you told me to do so some months/(year ?) ago:

http://crlab.com/forums/viewtopic.php?t=1569

My OS is Vista.

Have you an idea, what could I do to get rid of this error?
Thanks very much!
Ludek.

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Fri 23 Feb 2007 13:30

We couldn't reproduce the problem.
Please try the following things:
- reproduce it using ADO components
- execute your query in Enterprise Manager
- use SQL Native Client instead of OLE DB Provider for SQL Server
Please specify the exact error message that you get.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Fri 23 Feb 2007 14:14

It doesn't work well using ADO either, if I set the provider to SQLOLEDB.1.
When I insert my select and make the TAdoquery in delphi-designer active, it takes seconds-minutes and then - sometimes it gets active, sometimes it writes "connection failure", sometimes some TDS-Error, it's really various.
The SP2 is installed on server and also on the client, where I do the test.
I DON'T want to use this provider, but I have to, I STILL need to fetch my text fields correctly - or do you have a better solution than you wrote to me in
http://crlab.com/forums/viewtopic.php?t=1569
?
thanks for answer!

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Fri 23 Feb 2007 14:23

It works, If I use only "SQLOLEDB", but using "SQLOLEDB.1" it doesn't. I only want to know, if it is now safe to use SQLOLEDB without ".1" with SDAC, or if my old problem with long text fields will again apper If i revert the provider back to SQLOLEDB. Thx.

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Mon 26 Feb 2007 11:34

We have investigated the problem described in http://crlab.com/forums/viewtopic.php?t=1569 and found no bug using SQL Native Client Provider.
Possibly the problem was concerned usage of beta version.
Try to specify SQLNCLI.1 as a provider in the ConnectString property and test both problems.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Mon 26 Feb 2007 17:10

It's works well on my place, but on some test stations, I only get a message "MS SQL not found" if I try to connect to a database...
Do I have to set up my workstations after this change somehow differently?

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Doesn't work

Post by Ludek » Tue 27 Feb 2007 07:54

No, It doesn't work even on my place. I'm trying following

Code: Select all

program texttest;

{$APPTYPE CONSOLE}

uses
  SysUtils, MSAccess, ActiveX;

var
  c: TMSConnection;
  q: TMSQuery;
  txtconst: string;
begin
  txtconst := stringofchar('A', 1025) + stringofchar('B', 1025);
  CoInitialize(nil);
  c := TMSConnection.Create(nil);
  try
    c.ConnectString := 'provider=sqlncli.1';
    c.Server := ....
    c.Database := ....
    c.Username := .....
    c.Password := ....
    c.Open;
    try
      q := TMSQuery.Create(nil);
      try
        q.Connection := c;
        q.SQL.Text := 'delete from texttest';
        q.Execute;
        q.SQL.Text := 'insert into texttest (txt) values (:txt)';
        q.ParamByName('txt').AsString := txtconst;
        q.Execute;
        q.SQL.Text := 'select txt from texttest';
        q.Open;
        try
          if q.FIeldByName('txt').AsString = txtconst then
            writeln('OK')
          else begin
            writeln('FAILED!');
            writeln(q.FieldByName('txt').AsString);
          end;
        finally
          q.Close;
        end;
      finally
        q.Free;
      end;
    finally
      c.Close;
    end;
  finally
    c.Free;
  end;
  readln;
end.
It writes "FAILED". the texttest table is a table with one column of type "text". What could I try next? Please help, I really need working solution
:(

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Tue 27 Feb 2007 14:44

Please send us (evgeniym*crlab*com) a complete small test project to reproduce both problems; include definition of your own database objects.
Also supply us the following information:
- Exact version of Delphi or C++ Builder
- Exact version of SDAC. You can see it in About sheet of TMSConnection Editor
- Exact version of Microsoft SQL Server and OLE DB provider that you use. You can see it in Info sheet of TMSConnection Editor

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

got hint for solving

Post by Ludek » Fri 01 Jun 2007 09:48

I have to reopen this thread, because this text-reading-task is still failing (still reading bad data). I have now a workaround to this SQLNCLI.1 bug in reading long blob data: the memo-field data has to be read in smaller pieces, Read(...)

call? This would allow me to use the SQLNCLI.1 provider again.
Thanks.

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Fri 01 Jun 2007 14:52

There is a DefaultPieceSize constant declared in the MemData unit.
You can check "Project->Options->Compiler->Assignable types constants", and assign this constant to the value you need.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Mon 04 Jun 2007 09:55

It didn't help me. I'm afraid, it's because of the following line in procedure ConvertStreamToBlob in OLEDBAccess...

Code: Select all

        
  PieceSize := 10 * 1024;
could you please make this also an assignable constant in the next version? thx.

Jackson
Posts: 512
Joined: Thu 26 Jan 2006 10:06

Post by Jackson » Tue 05 Jun 2007 08:06

Please set the TMSConnection.Options.AutoTranslate property to False and try again.
In such case SQL Native Client does not use Unicode to convert data transferred between the client side and CHAR, VARCHAR, or TEXT columns, variables, or parameters in a SQL Server database. Please see MSDN and the SDAC help for more information about this property.

Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Post by Ludek » Tue 05 Jun 2007 09:32

Great! Now i'm also getting correct data. Thanks!

Post Reply