SQL Server 2005 SP2

[Ludek]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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