[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Communication Link Failure

Simon Woods

3/19/2007 12:03:00 PM

Hi

I populating a report in a VB app by running numerous queries and,
potentially, creating/deleting numerous temporary tables. I pass the
following SQL to ADO and onto the sql server db

SELECT d1.Field1,d1.Field2,d1.Field3,d1.Field4
INTO #MyDataTable3
FROM (MyDataTable d1
INNER JOIN MyDataTable2 d2 ON (d2.RecordNumber=d1.RecordNumber))
WHERE (d2.Field1 = '1')

and get

[Microsoft][ODBC SQL Server Driver]Communication link failure

If I fire the query directly at SQL Server through Query Analyzer, it runs
fine.

I have run quite a few queries before hand and it always errors on this
query. Could I be reaching some kind of capacity and this query is the one
that pushes things over the top.

Thanks

Simon
8 Answers

Henning

3/19/2007 12:36:00 PM

0

I guess you have to check the "Ignore # in Table name" in the ODBC
connector.

/Henning

"Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
news:%23v5WP6haHHA.3612@TK2MSFTNGP04.phx.gbl...
> Hi
>
> I populating a report in a VB app by running numerous queries and,
> potentially, creating/deleting numerous temporary tables. I pass the
> following SQL to ADO and onto the sql server db
>
> SELECT d1.Field1,d1.Field2,d1.Field3,d1.Field4
> INTO #MyDataTable3
> FROM (MyDataTable d1
> INNER JOIN MyDataTable2 d2 ON (d2.RecordNumber=d1.RecordNumber))
> WHERE (d2.Field1 = '1')
>
> and get
>
> [Microsoft][ODBC SQL Server Driver]Communication link failure
>
> If I fire the query directly at SQL Server through Query Analyzer, it runs
> fine.
>
> I have run quite a few queries before hand and it always errors on this
> query. Could I be reaching some kind of capacity and this query is the one
> that pushes things over the top.
>
> Thanks
>
> Simon
>
>
>

Aaron [SQL Server MVP]

3/19/2007 1:19:00 PM

0

> I populating a report in a VB app by running numerous queries and,
> potentially, creating/deleting numerous temporary tables.

Instead of running ad hoc SQL through ADO, have you considered generating
the data for this report within a stored procedure? This will definitely
reduce the complexities of having to write SQL that will pass the provider's
logic in addition to SQL Server's...

A


Simon Woods

3/19/2007 1:23:00 PM

0


> "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
> news:%23v5WP6haHHA.3612@TK2MSFTNGP04.phx.gbl...
>> Hi
>>
>> I populating a report in a VB app by running numerous queries and,
>> potentially, creating/deleting numerous temporary tables. I pass the
>> following SQL to ADO and onto the sql server db
>>
>> SELECT d1.Field1,d1.Field2,d1.Field3,d1.Field4
>> INTO #MyDataTable3
>> FROM (MyDataTable d1
>> INNER JOIN MyDataTable2 d2 ON (d2.RecordNumber=d1.RecordNumber))
>> WHERE (d2.Field1 = '1')
>>
>> and get
>>
>> [Microsoft][ODBC SQL Server Driver]Communication link failure
>>
>> If I fire the query directly at SQL Server through Query Analyzer,
>> it runs fine.
>>
>> I have run quite a few queries before hand and it always errors on
>> this query. Could I be reaching some kind of capacity and this query
>> is the one that pushes things over the top.

> I guess you have to check the "Ignore # in Table name" in the ODBC
> connector.
> /Henning

Thanks for responding.

It works fine when it is used in the queries executed prior to this one.

Simon Woods

3/19/2007 1:27:00 PM

0

Aaron Bertrand [SQL Server MVP] wrote:
>> I populating a report in a VB app by running numerous queries and,
>> potentially, creating/deleting numerous temporary tables.
>
> Instead of running ad hoc SQL through ADO, have you considered
> generating the data for this report within a stored procedure? This
> will definitely reduce the complexities of having to write SQL that
> will pass the provider's logic in addition to SQL Server's...

Thanks for your thoughts

Unfortunately, this is dynamic SQL and it needs to be so. We have a query
creation tool which permits users to create their own SQL. As part of
executing the users created SQL, we need to create/delete temporary tables
behind the scene. We may review this mechanism in the longer term, but at
the moment, I really need to understand what would cause the Comms Link
failure.


Henning

3/19/2007 2:07:00 PM

0


"Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
news:%23qQX4miaHHA.588@TK2MSFTNGP06.phx.gbl...
>
> > "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
> > news:%23v5WP6haHHA.3612@TK2MSFTNGP04.phx.gbl...
> >> Hi
> >>
> >> I populating a report in a VB app by running numerous queries and,
> >> potentially, creating/deleting numerous temporary tables. I pass the
> >> following SQL to ADO and onto the sql server db
> >>
> >> SELECT d1.Field1,d1.Field2,d1.Field3,d1.Field4
> >> INTO #MyDataTable3
> >> FROM (MyDataTable d1
> >> INNER JOIN MyDataTable2 d2 ON (d2.RecordNumber=d1.RecordNumber))
> >> WHERE (d2.Field1 = '1')
> >>
> >> and get
> >>
> >> [Microsoft][ODBC SQL Server Driver]Communication link failure
> >>
> >> If I fire the query directly at SQL Server through Query Analyzer,
> >> it runs fine.
> >>
> >> I have run quite a few queries before hand and it always errors on
> >> this query. Could I be reaching some kind of capacity and this query
> >> is the one that pushes things over the top.
>
> > I guess you have to check the "Ignore # in Table name" in the ODBC
> > connector.
> > /Henning
>
> Thanks for responding.
>
> It works fine when it is used in the queries executed prior to this one.
>
>
Don't know if this link about connection pooling is to any help, or if you
already checked it.
There are a lot of reading when google for [Microsoft][ODBC SQL Server
Driver]Communication link failure
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/3/77.asp&NoWe...

/Henning

Simon Woods

3/19/2007 2:42:00 PM

0

Henning wrote:
> "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
> news:%23qQX4miaHHA.588@TK2MSFTNGP06.phx.gbl...
>>
>>> "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
>>> news:%23v5WP6haHHA.3612@TK2MSFTNGP04.phx.gbl...
>>>> Hi
>>>>
>>>> I populating a report in a VB app by running numerous queries and,
>>>> potentially, creating/deleting numerous temporary tables. I pass
>>>> the following SQL to ADO and onto the sql server db
>>>>
>>>> SELECT d1.Field1,d1.Field2,d1.Field3,d1.Field4
>>>> INTO #MyDataTable3
>>>> FROM (MyDataTable d1
>>>> INNER JOIN MyDataTable2 d2 ON
>>>> (d2.RecordNumber=d1.RecordNumber)) WHERE (d2.Field1 = '1')
>>>>
>>>> and get
>>>>
>>>> [Microsoft][ODBC SQL Server Driver]Communication link failure
>>>>
>>>> If I fire the query directly at SQL Server through Query Analyzer,
>>>> it runs fine.
>>>>
>>>> I have run quite a few queries before hand and it always errors on
>>>> this query. Could I be reaching some kind of capacity and this
>>>> query is the one that pushes things over the top.
>>
>>> I guess you have to check the "Ignore # in Table name" in the ODBC
>>> connector.
>>> /Henning
>>
>> Thanks for responding.
>>
>> It works fine when it is used in the queries executed prior to this
>> one.
>>
>>
> Don't know if this link about connection pooling is to any help, or
> if you already checked it.
> There are a lot of reading when google for [Microsoft][ODBC SQL Server
> Driver]Communication link failure
> http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/3/77.asp&NoWe...
>
> /Henning

Thanks again.

Must admit I've tried testing with and without connection pooling. Same
effect.

Henning

3/19/2007 8:28:00 PM

0


"Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
news:u37HLTjaHHA.4508@TK2MSFTNGP03.phx.gbl...
> Henning wrote:
> > "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
> > news:%23qQX4miaHHA.588@TK2MSFTNGP06.phx.gbl...
> >>
> >>> "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
> >>> news:%23v5WP6haHHA.3612@TK2MSFTNGP04.phx.gbl...
> >>>> Hi
> >>>>
> >>>> I populating a report in a VB app by running numerous queries and,
> >>>> potentially, creating/deleting numerous temporary tables. I pass
> >>>> the following SQL to ADO and onto the sql server db
> >>>>
> >>>> SELECT d1.Field1,d1.Field2,d1.Field3,d1.Field4
> >>>> INTO #MyDataTable3
> >>>> FROM (MyDataTable d1
> >>>> INNER JOIN MyDataTable2 d2 ON
> >>>> (d2.RecordNumber=d1.RecordNumber)) WHERE (d2.Field1 = '1')
> >>>>
> >>>> and get
> >>>>
> >>>> [Microsoft][ODBC SQL Server Driver]Communication link failure
> >>>>
> >>>> If I fire the query directly at SQL Server through Query Analyzer,
> >>>> it runs fine.
> >>>>
> >>>> I have run quite a few queries before hand and it always errors on
> >>>> this query. Could I be reaching some kind of capacity and this
> >>>> query is the one that pushes things over the top.
> >>
> >>> I guess you have to check the "Ignore # in Table name" in the ODBC
> >>> connector.
> >>> /Henning
> >>
> >> Thanks for responding.
> >>
> >> It works fine when it is used in the queries executed prior to this
> >> one.
> >>
> >>
> > Don't know if this link about connection pooling is to any help, or
> > if you already checked it.
> > There are a lot of reading when google for [Microsoft][ODBC SQL Server
> > Driver]Communication link failure
> >
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/3/77.asp&NoWe...
> >
> > /Henning
>
> Thanks again.
>
> Must admit I've tried testing with and without connection pooling. Same
> effect.
>
>
INNER JOIN MyDataTable2 d2 ON
Is this line supposed to read as
INNER JOIN MyDataTable2 AS d2 ON

/Henning

Simon Woods

3/20/2007 8:39:00 AM

0

Henning wrote:
> "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
> news:u37HLTjaHHA.4508@TK2MSFTNGP03.phx.gbl...
>> Henning wrote:
>>> "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
>>> news:%23qQX4miaHHA.588@TK2MSFTNGP06.phx.gbl...
>>>>
>>>>> "Simon Woods" <simon.woodsUseless@virgin.net> skrev i meddelandet
>>>>> news:%23v5WP6haHHA.3612@TK2MSFTNGP04.phx.gbl...
>>>>>> Hi
>>>>>>
>>>>>> I populating a report in a VB app by running numerous queries
>>>>>> and, potentially, creating/deleting numerous temporary tables. I
>>>>>> pass the following SQL to ADO and onto the sql server db
>>>>>>
>>>>>> SELECT d1.Field1,d1.Field2,d1.Field3,d1.Field4
>>>>>> INTO #MyDataTable3
>>>>>> FROM (MyDataTable d1
>>>>>> INNER JOIN MyDataTable2 d2 ON
>>>>>> (d2.RecordNumber=d1.RecordNumber)) WHERE (d2.Field1 = '1')
>>>>>>
>>>>>> and get
>>>>>>
>>>>>> [Microsoft][ODBC SQL Server Driver]Communication link failure
>>>>>>
>>>>>> If I fire the query directly at SQL Server through Query
>>>>>> Analyzer, it runs fine.
>>>>>>
>>>>>> I have run quite a few queries before hand and it always errors
>>>>>> on this query. Could I be reaching some kind of capacity and this
>>>>>> query is the one that pushes things over the top.
>>>>
>>>>> I guess you have to check the "Ignore # in Table name" in the ODBC
>>>>> connector.
>>>>> /Henning
>>>>
>>>> Thanks for responding.
>>>>
>>>> It works fine when it is used in the queries executed prior to this
>>>> one.
>>>>
>>>>
>>> Don't know if this link about connection pooling is to any help, or
>>> if you already checked it.
>>> There are a lot of reading when google for [Microsoft][ODBC SQL
>>> Server Driver]Communication link failure
>>>
> http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/3/77.asp&NoWe...
>>>
>>> /Henning
>>
>> Thanks again.
>>
>> Must admit I've tried testing with and without connection pooling.
>> Same effect.
>>
>>
> INNER JOIN MyDataTable2 d2 ON
> Is this line supposed to read as
> INNER JOIN MyDataTable2 AS d2 ON

Yes - though my understanding from BOL is that it is optional.