Turning Off TCP/IP Client Has No Effect ?

Hi Everyone,
I was doing some testing with client configuration and I'm seeing
something I don't understand. Let me explain the setup.
My setup involves a single machine running "SQL Server Express 2005".
As you probably know, SSE includes the "SQL Server Native Client". I
run the "SQL Server Configuration Manager" and under the "SQL Native
Client Configuration" | "Client Protocols" node, I disable TCP/IP. My
understanding is that this should prevent clients on this machine from
using TCP/IP to connect to any SQL Server instance. Does this sound
right so far?
Well, when I run SQLCMD (specifying "tcp:" as the protocol) I am still
able to connect to the instance on the local machine.
In other words, I have TCP/IP disabled as a client protocol, yet I am
still able to successfully connect to an instance using SQLCMD with a
command line like this:
SQLCMD -S tcp:.\TEST_01 -d AdventureWorks
Now, it's my understanding that SQLCMD relies upon the "SQL Server
Native Client". And since I've (supposedly) configured the "SQL Server
Native Client" *NOT* to allow TCP/IP connections, the above SQLCMD
connection should not work, yet it does.
What am I missing here?
AlanDid you stop and restart the service after disabling TCP/IP
protocol? The settings won't take effect until after you
restart the service.
-Sue
On Mon, 10 Apr 2006 01:38:29 -0500, "Alan Foxmore"
<AFoxmore@.yahoo.com> wrote:

>Hi Everyone,
>I was doing some testing with client configuration and I'm seeing
>something I don't understand. Let me explain the setup.
>My setup involves a single machine running "SQL Server Express 2005".
>As you probably know, SSE includes the "SQL Server Native Client". I
>run the "SQL Server Configuration Manager" and under the "SQL Native
>Client Configuration" | "Client Protocols" node, I disable TCP/IP. My
>understanding is that this should prevent clients on this machine from
>using TCP/IP to connect to any SQL Server instance. Does this sound
>right so far?
>Well, when I run SQLCMD (specifying "tcp:" as the protocol) I am still
>able to connect to the instance on the local machine.
>In other words, I have TCP/IP disabled as a client protocol, yet I am
>still able to successfully connect to an instance using SQLCMD with a
>command line like this:
>SQLCMD -S tcp:.\TEST_01 -d AdventureWorks
>Now, it's my understanding that SQLCMD relies upon the "SQL Server
>Native Client". And since I've (supposedly) configured the "SQL Server
>Native Client" *NOT* to allow TCP/IP connections, the above SQLCMD
>connection should not work, yet it does.
>What am I missing here?

.NET Database/General Performance

Can someone please explain what I'm missing?

Looks like, from my investigation on the net, that 1500/s is pretty much the upper limit of .NET's ability to insert records. Can anyone tell me why? I have tried using UpdateBatchSize properties of 300, 500, 1500, 4500 all of which resulted in a max of 4 seconds difference over 90,000 records.

Why can .NET only insert 1500/s when DTS can do roughly a million in a little over a minute, which is approximately 10 times the performance? Both were doing only simple inserts. Due to my processing needs and integration with other apps, I really need to do the loading from within the app.

I have also written a lot of automation in Visual Basic and C++ before, and when I rewrote it in .NET, the performance was abominable. I am concerned that .NET is not a great language for performance-oriented tasks outside the core application. It does not interface smoothly or quickly with outside technologies as far as I can tell.

Within the application, I don't seem to have a problem, as I was able to write a parser to evaluate 100,000 boolean expressions in less than half a second (simple expressions, mind you). This used a lot of Regex expressions and evaluation code, so it's not that the code is running slow.

Anyone out there seen similar performance with .NET? Anyone from Microsoft that can help explain this?

Programming languages are not the best solution for bulk inserts.

.NET is a lot faster than plain old VB when used properly. Unmanaged C++ still rocks of course.

There are some great articles on MSDN about Data Access (and general performance) with .NET. Maybe you could check these out.

*** Can anyone explain me this ***

Hi:
I'm having a very strange and problematic behavior of SQL Server 2000 in my
program.

Program execution:
--------------
1.BEGIN TRANSACTION
2.EXECute for the first time a SP called sp_GAupdARMAZ_MOV, which inserts into a table ARMAZ_MOV a new record.
3.Til this point everything is ok!
4.EXECute for the second time sp_GAupdARMAZ_MOV, which I don't why rollbacks my transaction, aborting obviously the previously inserted record. But still continues execution without any error and inserts another record in my table ARMAZ_MOV.
5.COMMIT TRANSACTION

I make a "select * from armaz_mov" and there's only one record !!!!!!! What the hell? ... Can anyone explain me this???

I attached the SQL profiler trace of this problem, and an image of the exact line where my transaction is rolled back.

I analysed very well my SP and the only reason I encountered to explain this is because of an string concatenation with an integer in a SP line of code that's not even executed !!! Maybe a compilation error is causing this...
If I include a "convert(varchar,id)" in my string concatenation everything
works fine !!!!!!!!

If anyone could help me with this, maybe already had a similar problem, or maybe this is a known issue of SQL Server...

I would appreciate a lot if someone could analise for some minutes the
attached SQL Profiler trace and give me some feedback, opinion ...

Thanks a lot.
Monica
------------------------Can you post your stored procedure ? You may want to repost the profiler in zip format rather than rar.|||Originally posted by rnealejr
Can you post your stored procedure ? You may want to repost the profiler in zip format rather than rar.

Ok I reposted the Profiler trace in zip format and included my SP.|||What is the schema for the table ARMAZ_MOV ? Also, where are you executing the transaction (in query analyzer) ? Have you tried it in query analyzer to see if any errors are reported ? What are the exact statements that you are using to execute this transaction (including the parameters) ? You have to cast your int value to varchar anyway - but I am curious as well.|||Also, which version of sql server are you using - including service pack ?|||Originally posted by rnealejr
Also, which version of sql server are you using - including service pack ?

I execute my program in Delphi 6, W2K, with SQL Server 2000 and SP2 installed.
I'm connecting to my database and executing the SP using ADO.

The schema for my ARMAZ_MOV table is:
--
create table DBO.ARMAZ_MOV
(
ID_ARMAZ_MOV integer not null,
ID_REG_MOV integer not null,
REF_TIPO_MOV char(3) not null,
COD_FABRICA char(2) not null,
REF_TP_ARMAZ char(2) not null,
COD_ARTIGO varchar(10) not null,
DATA_MOV datetime not null,
QTD_MOV decimal(18,3) not null,
QTD_EXIST decimal(18,3) null ,
UNIDADE_MOV varchar(10) not null,
OBS_MOV varchar(200) null ,
INS_BY varchar(10) not null,
INS_DATE datetime not null,
UPD_BY varchar(10) not null,
UPD_DATE datetime not null,
STATUS_EXPORT varchar(15) null
constraint CKC_STATUS_EXPORT_ARMAZ_MO check (STATUS_EXPORT in ('EXPORTADO','NAO_EXPORTADO')),
ERRO_EXPORT varchar(200) null ,
TIPO_REG varchar(15) not null
constraint CKC_TIPO_REG_ARMAZ_MO check (TIPO_REG in ('NORMAL','CORRECCAO')),
ID_ORIGINAL integer null ,
constraint PK_ARMAZ_MOV primary key (ID_ARMAZ_MOV)
)|||I executed the 2 iterations of my program in qry analyser and everything worked fine.

So my conclusion is that problem is not from the DBMS but from the client side (ADO/Delphi).

I have to see if this is a known problem of ADO or Delphi, or not.

Rui Ferreira