URGENT!!!!VB -6 application talking to MTS

[Shuchi]

Hi
thanx alot to ChipH and Sunrise for their help.
Question to the group.
I have a vb-6 program which is talking to MTS
Now when i create an object of MTS component to get the result of simple select query
it gives me a "Switch To - Retry" dialog box saying, the other application is busy..chosse switch to to activate the application.
if the reqult is less than 100 rows, it comes after 5-10 times clicking Switch to, but say for rows in thousands, i justkeep on clicking Switch to, and it never ends.
Same thing i try from ASP , it works.
Does someone have an idea, why it is happening like this and i can i avoid this

Pl. respond as quickily as possible

Thanx and Regards,
Shuchi

 

[chiph]

What happens when you run the query in Query Analyzer? Also, once it's in Query Analyzer, hit Ctrl+L to show the execution plan. Are there any table scans? Is it possible that your query is doing a cross product (typically the result of a poorly-planned Join)?

Chip H.

 

[Shuchi]

Hi ChipH
Thanx for responding Again
No, my query is not very complex .
And even in case of simple select query from single table , it gives me "switch to" dialog box"
I think, there is some some problem while connecting it to MTS.
When i run a query, with say result upto 50-60 rows. It runs perfectly.Whne i try to run more heavy query - ( 200 rows). It gives me "Switch to" box. But when i run the second query(200 rows) after the first one , both the query run fine. But again for heavier queries with rows in thousands, this same problem occurs, even if i run this after the first one ( my query id single table select query in all the three cases.

( hoping for a quick response)
Thanx and Regards
Shuchi

 

[chiph]

Could you post a sample of your query?

Also, please post a (short!) sample of your ADO code that you're using to run the query.

Chip H.

 

[Shuchi]

I am extremely thankful to u that u r taking so much pains to solve my problem but at the same time i am really sorry that i am giving u a long piece of code. I tried to cut it short as much as possible but couldn't do much as i thought u must go thru atleast this much of code to understand it.

Thanx alot agian
Hoping for a quick response..

Here it is
My vb code is creating an instance of GenMastMaint(MasterMaintenence MTS object).
PrintTable is the method of GenMastMaint and it is creating an instance of another MTS component ADOClass(database component)
My client machine has all the MTS components registered on it thru the exe which gets created on MTS server when u export the package

VB Code:
Dim obj as GenMastMaintInt 'interface of the component'
set obj = new GenMastMaintInt 'GIVES ME A SWTICH TO DIALOG BOX HERE BUT GOES OFF AFTER CLICKING 2-3 times
result = obj.PrintTable("tablename",arrayofcolumnstobedisplayed,resultarray) 'GIVES ME A "PERMANENT SWITCH TO" Dialog Box
'display of values using loop

GenMastMaint.PrintTable Code (VC++)
//Decalrations
Creating an object of ADOClass
IADOClassInt *p = NULL;
hr = m_spObjectContext->CreateInstance(CLSID_ADOClassInt,IID_IADOClassInt,(void**)&p);

\\code to get the values of passed columns and store in a char. array
try
{
OutputDebugString("GenMastMaintIntrintTable:Entering Method PrintTable"

if(vColumns->vt & VT_BYREF)
{
psaCol = *(vColumns->pparray);
}
else
{
psaCol = vColumns->parray;
}

//check the dimension of array if invalid then return the error
if(SafeArrayGetDim(psaCol) !=1)
{
TESTHR(E_INVALIDARG);
}

//get the lower and upper bounds
SafeArrayGetLBound(psaCol,1,&lLbound);
SafeArrayGetUBound(psaCol,1,&lUbound);

//compute the number of elements
cElements = lUbound - lLbound + 1;
OutputDebugString("outside for loop"
//access the elements of the array and store in char array
for(cCnt = lLbound;cCnt <= lUbound; cCnt++)
{
OutputDebugString(&quot;inside for loop&quot;
VARIANT vVal;
SafeArrayGetElement(psaCol,&cCnt,&vVal);
OutputDebugString(&quot;after get element&quot;
bval=vVal.bstrVal;
//Code for initializing the key value
OutputDebugString(&quot;before widechar&quot;
******in this method the control stops here***refer to note at the end

iretval = WideCharToMultiByte(CP_ACP,0,bval,-1,sColumns[cCnt],sizeof(sColumns[cCnt]),NULL,NULL);
OutputDebugString(&quot;value of columns array-- &quot;
OutputDebugString(sColumns[cCnt]);

::SysFreeString(bval);
}//loop end

lretval = fnGetdbinfo(szdsnname,szdbname,szusername,spwd);


//opening the connection
TESTHR(p->Open(sdsnname, susername,spwd));
nOpen = 1;

iretval = WideCharToMultiByte(CP_ACP,0,bTablename,-1,cTablename,sizeof(cTablename),NULL,NULL);
OutputDebugString(&quot;value of variable cTablename&quot;
OutputDebugString(cTablename);

wsprintf(sQuery,&quot;select rows from sysindexes where id = OBJECT_ID('%s') and indid <2&quot;,cTablename);

OutputDebugString(&quot;value of query to get rows&quot;
OutputDebugString(sQuery);
******************in another method code stops here********
p->OpenRecordset(sQuery);

***Note***
i created another method having exactly the same code but eliminated the 2nd parameter( columns to display). this method simply takes the table name and return all the rows.

But in this method the control stops just before OpenRecordset of ADOClass( mentioned above)

ADOClass Code:
ADOClass.Open
STDMETHODIMP CADOClassInt::Open(BSTR source, BSTR user, BSTR pwd)
{
// TODO: Add your implementation code here

/*****************************/
HRESULT hr;
void *pMsgBuf;
// char cBuf[100];
char csource[20],cuser[20],cpwd[20];
int iretval;

memset(csource,'\0',20);
memset(cuser,'\0',20);
memset(cpwd,'\0',20);

//IITPEvtLogObj *p1 = NULL;

try
{

hr = S_OK;
OutputDebugString(&quot;ADOTierCls::Open:entered the open function&quot;
//conversions
iretval = WideCharToMultiByte(CP_ACP,0,source,-1,csource,sizeof(csource),NULL,NULL);
iretval = WideCharToMultiByte(CP_ACP,0,user,-1,cuser,sizeof(cuser),NULL,NULL);
iretval = WideCharToMultiByte(CP_ACP,0,pwd,-1,cpwd,sizeof(cpwd),NULL,NULL);

OutputDebugString(&quot;ADOTierCls::Open:after the conversions to multibyte&quot;

OutputDebugString(&quot;ADOTierCls::Open:before creating the connection object&quot;

//HRESULT hr = m_connection.CreateInstance(&quot;ADODB.Connection.2.1&quot;
hr = m_connection.CreateInstance(__uuidof(Connection));
// m_spObjectContext->CreateInstance(__uuidof(Connection), __uuidof(_Connection),
// (void**)&m_connection);
OutputDebugString(&quot;ADOTierCls::Open:after connection of main connection&quot;
if (SUCCEEDED(hr))
{
//correct
//_bstr_t ssource(&quot;driver={SQL Server};server=ndl_frc_ntd03;uid=itpuser;pwd=itpuser;database=itpdb&quot;

char query[255];
BSTR ssource;

OutputDebugString(&quot;ADOClass :Open: initializing the query &quot;

//working 1
wsprintf(query,&quot;DSN=%s;UID=%s;PWD=%s;&quot;,csource,cuser,cpwd);

ssource=(BSTR) (_bstr_t) query;

OutputDebugString(&quot;ADOClass :Open: Before opening the Data connection with DSN--&quot;

m_connection->Open(ssource,NULL,NULL,adCmdUnspecified);
//m_connection->Open(ssource,NULL,NULL);

OutputDebugString(&quot;ADOClass :Open: After opening the Data connection with DSN&quot;


OutputDebugString(&quot;ADOTierCls::OpenBConnection successfully opend&quot;

//deallocating
::SysFreeString(ssource);

mExcStatus = 0;

}
if (SUCCEEDED(hr))
{
hr = m_command.CreateInstance(__uuidof(Command));
// hr = m_command.CreateInstance(&quot;ADODB.Command.2.1&quot;
// m_spObjectContext->CreateInstance(__uuidof(Command), __uuidof(_Command),
// (void**)&m_command);
OutputDebugString(&quot;ADOTierCls::Open:Command object successfully created&quot;
}
else
{
::FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM,NULL,
hr,MAKELANGID(LANG_NEUTRAL,SUBLANG_DEFAULT),(LPTSTR)&pMsgBuf,0,NULL);
OutputDebugString((LPTSTR)pMsgBuf);
LocalFree(pMsgBuf);

}
if (SUCCEEDED(hr))
{
hr = m_command->putref_ActiveConnection(m_connection);
OutputDebugString(&quot;ADOTierCls::Open:Active connection successfully opend&quot;
}

if (SUCCEEDED(hr))
{
hr = m_recordset.CreateInstance(__uuidof(Recordset));
//m_spObjectContext->CreateInstance(__uuidof(Recordset), __uuidof(_Recordset),
// (void**)&m_recordset);
OutputDebugString(&quot;ADOTierCls::Open:REcordset successfully created&quot;
}
else
{
::FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM,NULL,
hr,MAKELANGID(LANG_NEUTRAL,SUBLANG_DEFAULT),(LPTSTR)&pMsgBuf,0,NULL);
OutputDebugString((LPCTSTR)pMsgBuf);
LocalFree(pMsgBuf);
}
}//try

ADOClass.OpenRecordset:
STDMETHODIMP CADOClassInt::OpenRecordset(VARIANT query)
{

VARIANT v;
V_VT(&v) = VT_DISPATCH;
V_DISPATCH(&v) = (IDispatch*) m_connection;

// Need the AddRef() as VariantClear() calls Release(), unless fAddRef
// false indicates we're taking ownership
//
V_DISPATCH(&v)->AddRef();

//return m_recordset->Open(query, v, adOpenDynamic, adLockOptimistic, adCmdText);
return m_recordset->Open(query, v, adOpenStatic, adLockReadOnly, adCmdText);
}

 

[mrgondipalle]

Hello Shuchi,
I am also getting similar error with MTS. If you had found a solution, can pls tell that.

Thanks in advance,
Murali