ODBC Connection Mystery

ASKER

Maintain, as innnnnnnn??????????

Can you do the configure option in ODBC Configuration?

Where you can test the configuration, and also let us know what options are checked in that configuration.

Thanks.

could you take alook at this article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_err_dblib_64s9.asp

let us know if it makes sense,
Cheers

That makes sense, unusual for microsoft to do that.

In any case what they're saying is that you are copying data that has no matching record in the destination.

Severity Level 11
Message Text
Attempt to bulk copy an oversized row to SQL Server.

Explanation
This error occurs during a bulk copy operation when a row or rows in the source data file do not match the row as defined for the destination database table. It can occur due to a missing end-of-line marker. It can also occur if there are more column delimiters in the source data file than currently exist in the destination database table, or if the source data file is corrupt.

Good call miron

ASKER

Crosenblum: When I go to test connection in ODBC it presents this error as indicated above, so I can't even get to the point of testing. Unless I change connectiong type to multiprotocol, then I can test, and receive a "seccessful connection test". But in order to reconnect using TCP/IP, i have to restart server, just restart SQL service does not clear it up., this happens intermittenly up to 3 times a day, sometimes one time some times none. On the average I see this issue once a day and I have to restart server during production.

Miron: that is not the same as my scenario, but thanks!

In all connections there are two points to configure, maybe more if there is a firewall.

First let's verify that the SQL Server is setup for communication via TCP/IP Only.

I don't know SQL Server well enough to know how to do that, to check how it is communication. All I know is that you could create an ODBC to itself on the same machine, and test that via TCP/IP connection.

It's always about being on the same page from all points that connect to each other.

ASKER

Thanks for the quick replies guys!

crosenblum: I have the SQL server communication, local (.) to tcp/ip only. And I test it from the server and it is successful. That would place the connections on the same page.

I agree that Miron had a good call, but that does not explain why any future TCP/IP connections are no longer allowed, needing to restart server.

As for a firewall, there is no LAN firewall, only on connecting to the internet which is on a different route.

ASKER

Let me suggest an extra round of troubleshooting next time similar situation happens

1 logon with the machine administrator account on the sql server box
2 go to the Start menu on the sql server
3 click on Run
4 type "cmd" with no quotes and click "OK" button
5 type command "netstat -na" with no quotes
6 send me the result at the email address: blueopera@cmpmail.com

Cheers,
Miron

Anthony Perkins

>> Maintain, as innnnnnnn?????????? <<
As in close out appropriately. If you are not sure please re-read the EE Guidelines at https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/cmtyQuestAnswer.jsp and I quote:
<quote>
Please be responsible for the questions you post. If you don't grade answered questions, the time an Expert took to help you goes unrewarded.
</quote>
If you have any further questions, feel free to contact Community Support at https://www.experts-exchange.com/Community_Support/

Here is a summary of your questions and grades:
Questions Asked 7
Last 10 Grades Given C C B C A A
Question Grading Record 6 Answers Graded / 6 Answers Received

Thanks,
Anthony

ASKER

acperkins:

Thanks for your concern, and I will.

It would be greatly appreciated if there is an issue that I get addressed and not the entire industry. If I had been addressed about a misunderstanding I would would gladly take care of it. But it seems like your insinuating that I was repeatedly advised of this and never took care of it.

My summary is available when anyone wants to see it, does not have to be displayed as you did.

Anthony Perkins

My apologies. From your comment and I quote: "Maintain, as innnnnnnn??????????" it appeared you were not familiar with the the way this site worked.

Thanks,
Anthony

Anthony,

you are barking on a wrong tree. GlobalFax, have you had a chance to run the diagnostics.

Cheers.

I find this conversation fascinating, I am gaining more knowledge on SQL Server by bounds and leaps.

At my company, I was the one doing the setup, and I have had no experience other than what I learned on my own, and reading 3-4 different hyper-thick books. We had to bring in a consultant, to learn how to do indexing, maintenance plans and relationships.

It's amazing what you can do with a solid Enterprise Database...

ASKER

Miron, thanks and yes. I am at home and will post the results of the diagnostics first thing tomorrow morning.

Look forward to your knowledge on the results.

crosenblum,

your talent and achievents are greatly appreciated on this board. Feel free to mingle as you pleased, there is a very conveinent place for it, the lounge, it is located at the URL

https://www.experts-exchange.com/Miscellaneous/Lounge/

let me know if you have trouble locating it.

ASKER

Miron,

Proto Local Address Foreign Address State
TCP 0.0.0.0:135 0.0.0.0:0 Listening
TCP 0.0.0.0:135 0.0.0.0:0 Listening
TCP 0.0.0.0:1030 0.0.0.0:0 Listening
TCP 0.0.0.0:1031 0.0.0.0:0 Listening
TCP 0.0.0.0:1033 0.0.0.0:0 Listening
TCP 0.0.0.0:1433 0.0.0.0:0 Listening
TCP 0.0.0.0:2148 0.0.0.0:0 Listening
TCP 0.0.0.0:6103 0.0.0.0:0 Listening
TCP 10.37.50.232:137 0.0.0.0:0 Listening
TCP 10.37.50.232:138 0.0.0.0:0 Listening
TCP 10.37.50.232:139 0.0.0.0:0 Listening
TCP 10.37.50.232:139 10.1050.90:2871 Established
TCP 10.37.50.232:139 10.37.50.248:4104 Established
TCP 10.37.50.232:139 10.147.13.180:1060 Established
TCP 10.37.50.232:139 10.147.13.181:1047 Established
TCP 10.37.50.232:1105 0.0.0.0:0 Listening
TCP 10.37.50.232:1105 10.1.50.240.139 Established
TCP 27.0.0.1:1026 0.0.0.0:0 Listening
TCP 27.0.0.1:1026 127.0.0.1:1031 Established
TCP 27.0.0.1:1029 0.0.0.0:0 Listening
TCP 27.0.0.1:1031 127.0.0.1:1026 Established
TCP 27.0.0.1:1032 0.0.0.0:0 Listening
UDP 0.0.0.0:135 *.*
UDP 0.0.0.0:2148 *.*
UDP 10.37.50.232:137 *.*
UDP 10.37.50.232:138 *.*

Above is the result from the netstat -an

Thanks in advance!

Gary

Gary,

was sql server TCP/IP connections unavailable at this moment. If yes, one more request

1 output of
select * from sysprocesses

2 DBCC OPENTRAN for each database, will post script in a second

Thanks,
Miron

WesLennon

Listening...

Wes Lennon
Director of Community Services
Experts Exchange

SET NOCOUNT ON
create table #t ( dbid int )

insert into #t select dbid from master.dbo.sysdatabases
declare @sql_line varchar(500)
select @sql_line = ''
while exists( select dbid from #t )
BEGIN
select '++++++++++++++++++++++++++++++++++++'
select [name] from master.dbo.sysdatabases where dbid = ( select max( dbid ) from #t )
select @sql_line = 'DBCC OPENTRAN( ' + RTRIM(LTRIM(STR( max( dbid ) ))) + ' )' from #t
execute( @sql_line )
select '===================================='
delete from #t where dbid = ( select max( dbid ) from #t )
END

--drop table #t

ASKER

Miron,

At the point of taking this diagostic view, what you see there is with the TCP/IP problem still present and the clients connecting to the SQL are using multiprotocol with was the only method that allowed them to continue working w/o restarting server.

1. What is the anomalies should we see from this query, since this has quite a bit of columns.

2. DBCC OPENTRAN: results in "No active open transactions"

Thanks,

Gary

Ok,

let me be boringly pedantic, but we need to narrow the time of the "netstat -na" query to a point in time. Let me write it as a list with changes of state marked with numbers.

1 .. for a while
TCP/IP works fine, no other protocol is allowed to connect to sql server

2 .. at this error 10054 is reported

3 .. clients may not connect to sql server via TCP/IP anymore
4 .. server netowrking libraries are altered using SVRNETCN.exe utility, TCP/IP removed from the list of available protocols and multiprotocol is the only protocol that can be used to connect to the sql server
5 .. clients are connecting to sql server via muti protocol
6 .. sql server machine goes through a warm reboot
7 .. server netowrking libraries are altered using SVRNETCN.exe utility, TCP/IP adde to the list of available protocols and TCP/IP is the only protocol that can be used to connect to the sql server
8 .. clients are connecting to sql server via TCP/IP

At what point in time did you run the diagnostics. This information will help me a lot to interpret the result of the "netststat -na" command

Cheers,
Mrion.

ASKER

1. for a while all methods work
2. then intermittently we receive 10054, resoltution is either restart server or change connection method to multiprotocol
3. correct, no more tcp/ip connections allowed
4. Nothing is changed on the server, only clients are reconfigured to multiprotocol.
5. Yes. clients are connection via miltiprotocol
6. yes, I can do that to allow TCP/IP connection to resestablish or reconfigure clients to MP w/o rebooting server.
7. no since sql server networking libraries are never altered.
8. yes, but only after a warm reboot. (a restart sql server service does no correct issue)

I ran the diagnostics with the tcp/ip problem still active and no warm reboot of server.

many thanks again,

Gary

Gary,

studying output of "netstat -na". Talk to you soon.

Thanks,
Miron

Ok,

some things I would check, new info points attention away from the sql server and towards the network.

1 how many protocols are bound to the network card(s) on the machine you are using
2 could you check both cards ( you have 2 cards, please correct me if I am wrong ) and verify that both only bound to TCP/IP protocol ( no IPX ... )
3 check if both cards have File And Print Sharing for Microsoft Networks attached to the list of protocols they support
4 what is the use for the private network.
5 is it possible that traffic from the private network can flood external NIC for a short period of time
6 is external NIC on DHCP or static IP.
7 do you have "ODBC pooling" check box checked on client side, I have vague memory using this feature on NT 4.0, and devastating effect it had on one heavily multi - user application. Not that I imply this is the cause of trouble, just one more thing to test.
7

I think I have some grasp on the cause of the trouble, the question I am working on is can we fix it with our resources. The worst thing to happen is if the switch/router connecting external network NIC to the internet server is faulty.

Intuitively I tend to think this is a component version / network configuration related issue. The simplest thing is to

1 try to find a network bits on the sql server that misbehave and check their version / lookup info on Microsoft KB
2 make sure that all windows 2000 machine have sp2 applied.
3 if this is a cluster MDAC 2.5 should be used to connect. XP uses higher version by default, you will need special direction on how to install and use MDAC 2.5 on the XP machine.

Let me know if any of it makes sense.

ASKER

I have two phsycial NICS built in to Dell server. But I only have one adapter in network properties configured.

Only have TCP/IP, static IP

On client side all check boxes unchecked.

private network?

Thanks

ASKER

Forgot to mention, that machines connecting range from Win98, 2000 to XP pro. As for the clients they do have both IPX and TCP/IP. We are running NDS for NT authentication.

is it running on the subnet 27

ASKER

Sorry about the delay, I'm back in the office.

the sql db server is on subnet 255.255.0.0, if I understand your question correctly

sorry for being cryptic in my wording, I need to find out how the usual address of a client ( not sql server ) on the NDS would look like, does it look like 27.x.x.x or 10.x.x.x. subnet mask is what you listed ( 255.255.0.0, ) the subnet is the leading number of the four numbers separated by a dot in the network interface address. Directions that I am thinking off

1 isolating sql server from IPX protocol network traffic.
2 using multi - protocol network protocol 100% of time on sql server.
3 upgrading sql server box to windows 2000 Service Pack 2.

I believe that presence of IPX traffic is the cause and either one of these three ways will at least remove presence of IPX from the list.

ASKER

At this point I will accept you last comment as the valid answer. The Multiprotocol scenario is working and at this point, I agree that as long as IPX traffic is in the loop it could be a contributing factor. Would you see any drawbacks in using Multiprotocol as opposed to TCP/IP only.

Upgading is the future for this server, as once we migrate to W2K AD, with NDS for NT in the current picture it is not the best scenario for a clean network, I guess there will always be extraordinary anomalies.

So if there are any drawbacks that I should think twice in using multiprotocol, then that is the path I will take.

awaiting your feedback to conclude, thanks again for you help.

Gary

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial