Page 1 of 1
SQL Server 2005 SP2
Posted: Tue 20 Feb 2007 16:32
by 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.
Posted: Fri 23 Feb 2007 13:30
by 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.
Posted: Fri 23 Feb 2007 14:14
by 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!
Posted: Fri 23 Feb 2007 14:23
by 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.
Posted: Mon 26 Feb 2007 11:34
by 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.
Posted: Mon 26 Feb 2007 17:10
by 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?
Doesn't work
Posted: Tue 27 Feb 2007 07:54
by 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
Posted: Tue 27 Feb 2007 14:44
by 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
got hint for solving
Posted: Fri 01 Jun 2007 09:48
by 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.
Posted: Fri 01 Jun 2007 14:52
by 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.
Posted: Mon 04 Jun 2007 09:55
by Ludek
It didn't help me. I'm afraid, it's because of the following line in procedure ConvertStreamToBlob in OLEDBAccess...
could you please make this also an assignable constant in the next version? thx.
Posted: Tue 05 Jun 2007 08:06
by 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.
Posted: Tue 05 Jun 2007 09:32
by Ludek
Great! Now i'm also getting correct data. Thanks!