using ADO and SQL queries longer than 1024 char.

jjanauskas

Hello,

Has anyone got an idea how to execute sql queries longer than 1024 characters using ADO in Navision.

The query I want to executed is generated dynamically during run-time, so stored procedures do not help here.

What I did: I output the generated sql to text file and then used special my written automation server (SQLFileExecuterX) which executes text files as sql queries...

But I thought, maybe somehow it is possible to do that without using SQLFileExecuterX, but using such features like BIGTEXT or Stream, or at least some standard software which goes together with navision/ado/windows...?

huh?

jjanauskas

I found another possible solution to the problem. You can use BSTRConverter automation server from NATHash.dll which comes together with Navision installation.

BSTRConverter is used to handle long text and has a method which returns BSTR type result. You can pass that result to ADO Recordset...

ADORecset.Open(BSTRConverter.BSTR, ....

kriki

[Topic moved from Navision forum to SQL General forum]

ara3n

None of the above solution work

xp_execresultset is removed from sql 2005

and ADORecordset.Open(NavHash.BSTR); errors with

The length of the text string exceeds the size of the string buffer.

Any other solution?

ara3n

Still no answer to a way to execute a sql statement that is longer than 1024?

ara3n

I think I've found a solution. I will post an update.

ara3n

Not really a solution. I had to change the sql statement to be shorter.

DECLARE @D Date Set @D = '10/07/10'
(Select [Item No_],[Location Code],[Quantity], QtyToAdd =
 Case   when  ILE.[Positive] = 1 then
  (Select isnull(sum(IAE.[Quantity]),0)
  from [KRONUS$Item Application Entry] as IAE ,[KRONUS$Item Ledger Entry] as ILE2
  where IAE.[Posting Date] >= @D and ILE2.[Posting Date] <= @D and
  IAE.[Outbound Item Entry No_] <> 0 and IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
  and ILE.[Entry No_] = IAE.[Inbound Item Entry No_])
 else (Select isnull(sum(IAE.[Quantity]),0)
  from [KRONUS$Item Application Entry] as IAE,[KRONUS$Item Ledger Entry] as ILE2
  where IAE.[Posting Date] >= @D and ILE2.[Posting Date] <= @D and
  IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
  and ILE.[Entry No_] = IAE.[Outbound Item Entry No_])
 end,
(Select isnull(sum(VEntry.[Cost Amount (Expected)]+ VEntry.[Cost Amount (Actual)]),0) 
from [KRONUS$Value Entry] as VEntry where VEntry.[Posting Date] <= @D and
VEntry.[Item Ledger Entry No_] = ILE.[Entry No_]) as [Inventory Value] 

from [KRONUS$Item Ledger Entry] AS ILE
where ILE.[Posting Date] <= @D)

Changed to

DECLARE @D Date Set @D = '10/07/10'
(Select [Item No_],[Location Code],[Quantity], QtyToAdd =
  (Select isnull(sum(IAE.[Quantity]),0)
  from [KRONUS$Item Application Entry] as IAE ,[KRONUS$Item Ledger Entry] as ILE2
  where IAE.[Posting Date] >= @D and ILE2.[Posting Date] <= @D and
  IAE.[Outbound Item Entry No_] <> 0 and IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
  and  ((ILE.[Positive] = 1 and ILE.[Entry No_] = IAE.[Inbound Item Entry No_]) OR 
      (ILE.[Positive] = 0 and ILE.[Entry No_] = IAE.[Outbound Item Entry No_]))),
(Select isnull(sum(VEntry.[Cost Amount (Expected)]+ VEntry.[Cost Amount (Actual)]),0) 
from [KRONUS$Value Entry] as VEntry where VEntry.[Posting Date] <= @D and
VEntry.[Item Ledger Entry No_] = ILE.[Entry No_]) as [Inventory Value] 
from [KRONUS$Item Ledger Entry] AS ILE
where ILE.[Posting Date] <= @D)

rdebath

It's not that difficult to solve this problem, just use the VBScript OCX or DLL.
I'm using this technique to automatically generate some rather large trigger code, about 128K in 3000 lines for example.

  VBScript Automation  'Microsoft Script Control 1.0'.ScriptControl
or ..
  VBScript OCX         ScriptControl Object

VBScript.Language('VBScript');

Session.SETRANGE("My Session", TRUE);
Session.FIND('-');

VBScript.AddCode('set SQL = CreateObject("ADODB.Connection")');
VBScript.AddCode('SQL.open("Provider=sqloledb;Data Source=(local);Initial Catalog=' +
                  Session."Database Name" + ';Integrated Security=SSPI")');

VBScript.AddCode('StrData = ""');
VBScript.AddCode('Sub AddLine(str) : StrData = StrData & str & vbCRLF : End Sub');
VBScript.AddCode('Sub RunSQL : SQL.Execute(strData) : StrData = "" : End Sub');

VBScript.ExecuteStatement('AddLine("' +
          'drop table zxyzzy' +
          '")');

VBScript.ExecuteStatement('RunSQL');

You probably want to use BSTRConverter to build the string to avoid the horrible quoting issues.

VBBS     Automation  'Navision Attain Hash 1.0'.BSTRConverter
VBScript.AddObject('BS', VBBS, FALSE);

VBBS.BSTR := LineData;
VBBS.AppendNextStringPortion(LineData2);
VBScript.ExecuteStatement('StrData = StrData & BS.BSTR & vbCRLF');

BTW: for the record, VBScript does seem to be the answer to any little problems you may have with Navision ...

rmv_RU

You can use xmlTextNode objects to pass long SQL queries into ado objects.
xmlTextNode.appendData(query1);
xmlTextNode.appendData(query2);
xmlTextNode.appendData(query3);
xmlTextNode.appendData(query4);
xmlTextNode.appendData(query5);
xmlTextNode.appendData(query6);
xmlTextNode.appendData(query7);
adoRs.Open(xmlTextNode.nodeValue, adoConn);