[TCP/IP Sockets] Specified SQL Server not found

Connections, using ODBC (version 3.520) and from a couple web apps, to one of
my SQL Server 2000 systems (with SP3) had been fine until a couple days ago.
Suddenly some of my systems (but not all) are unable to connect to that one
server. The message when ODBC attempts to login into the SQL Server is:
Connection failed:
SQLState: '01000', SQL Server Error: 11001, [TCP/IP Sockets]ConnectionOpen
(Connect())
SQLState: '08001', SQL Server Error: 6, [TCP/IP Sockets]Specified SQL Server
not found
My web apps (including a web service) display a similar (though abbreviated)
message when the user is trying to connect to the SQL server to
login/authenticate.
From that same system that can't get to the first SQL Server, I can use ODBC
to connect to a different SQL Server with no trouble. I've tried Named Pipes
with no success. Enterprise Manager connects fine from other systems. I am
certain that MDAC 2.8 has been installed on all systems but, regardless, I
hadn't done any Windows updates on any systems for at least a week and no
MDAC/ODBC updates for a couple months.
What can I check or fix?
Thanks.
Jon
jlimmer@.gmbeverage.com
On the surface it sounds like a name resoultion problem. What happens if
you ping the SQL Server name from the web server. Does it return? Als try
ping -a <ip address of the SQL Server machine>. Does it return the correct
server name?
Can you connect from a DSN using the ip address of the SQL Server as the
server name?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Very interesting, Rand.
I can ping the SQL server both using the server name and using the -a switch
(which does resolve the server name).
I also can sucessfully use the IP address in the SQL Server instance name in
the DSN to connect.
So, what does that indicate is the problem, if DNS is working?
Jon

[TCP/IP Sockets] Specified SQL Server not found

Connections, using ODBC (version 3.520) and from a couple web apps, to one o
f
my SQL Server 2000 systems (with SP3) had been fine until a couple days ago.
Suddenly some of my systems (but not all) are unable to connect to that one
server. The message when ODBC attempts to login into the SQL Server is:
Connection failed:
SQLState: '01000', SQL Server Error: 11001, [TCP/IP Sockets]ConnectionOp
en
(Connect())
SQLState: '08001', SQL Server Error: 6, [TCP/IP Sockets]Specified SQL Se
rver
not found
My web apps (including a web service) display a similar (though abbreviated)
message when the user is trying to connect to the SQL server to
login/authenticate.
From that same system that can't get to the first SQL Server, I can use ODBC
to connect to a different SQL Server with no trouble. I've tried Named Pipe
s
with no success. Enterprise Manager connects fine from other systems. I am
certain that MDAC 2.8 has been installed on all systems but, regardless, I
hadn't done any Windows updates on any systems for at least a week and no
MDAC/ODBC updates for a couple months.
What can I check or fix?
Thanks.
Jon
jlimmer@.gmbeverage.comOn the surface it sounds like a name resoultion problem. What happens if
you ping the SQL Server name from the web server. Does it return? Als try
ping -a <ip address of the SQL Server machine>. Does it return the correct
server name?
Can you connect from a DSN using the ip address of the SQL Server as the
server name?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Very interesting, Rand.
I can ping the SQL server both using the server name and using the -a switch
(which does resolve the server name).
I also can sucessfully use the IP address in the SQL Server instance name in
the DSN to connect.
So, what does that indicate is the problem, if DNS is working?
Jon

[SQL2k5] Dynamic SQL Query Select on all user tables

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.

Forgot, for every table it selects from, the table name must be one of the returned columns.|||

Hi, try the following:

sp_msforeachtable 'SELECT *,''?'' AS TableName FROM (SELECT TOP (3) , COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
FROM ?
GROUP BY [Target IP]
ORDER BY EventNum DESC) SubQuery'

But keep in mind that this should be just used for administrative task and adhoc queries not for business logic as the procedure is not documented, therefore not supported and could be deprecated in the next version of SQL Server.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

trying to install sql server2005 on a windows 2003 server box.

getting msg below at the sql server . i looked at other posts on trying to uninstall SQL Native Access Client and norton antivirus. i could never find the snac on the add - remove programs and this server does not have a virus protection program yet.

here's the history of the installs on the server:

wanted to test a 2005 upgrade so:

1) installed sql server 2000 then sp4 then restored some databases to it - all OK

2) tried to upgrade to sql 2005 but ran into problems and left it at that.

had a disk drive crash on the d drive so lost the installs but not the operating system

when the drive was replaced, left alone for a while

then wanted to test a straight 2005 install

1) removed the broken 2005 attempt

2) removed the 2000

3) installed 2005 and got the error on the subject line:

TITLE: Microsoft SQL Server 2005 Setup

SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

i've gone through as many of the forums that i can and have tried several things - like uninstalling 2005 and installing pieces and parts but but nothing seems to work.

Thanks!

Dan <><

It sounds like the SNAC version you have might be corrupt or incompatible.

Go to Add or Remove Programs and find the listing for "Microsoft SQL Native Client". Remove this component.

Then try the install again.

Michelle

|||

Thank you so much for replying. last attempt was to install just the sql server commonent - no others like analysis services or integration services.

i looked at the add / remove programs both before, during and after the sql server database services failed attempt and the add/remove has never shown a program called MS sql native access client (SNAC).

when i start the install, the installing prerequisites window shows the two components that will be installed: snac and the sql server 2005 setup support files. the snac goes to a green arrow almost immediately while the 2005 setup files takes a few seconds. i dont know if that helps or not.

the add remove programs just has:

microsoft .net framwork 2.0|||

It definatel looks like the problems is the "Microsoft SQL Server Native Client" layer. At this point your machine thinks that it is already installed but the install is broken.

Follow the directions in the following article to clean up the machine and then try again.

http://support.microsoft.com/kb/909967

Michelle

|||

uninstall manually - did the following (shows issues with some of the directions)

it said to delete the local security groups. but i never could figure out how to do. the article it mensions didnt (at least to me) show how to delete. so i just went on|||

i went out to the website and downloaded the .msi file for snac. it was slightly different from the .msi that came on the DVD (3511k vs 3516k).

the dvd version when i clicked on the msi file just returned me back to windows explorer.

the snac from microsoft's website executed and finally installed the snac.

the install worked perfectly after that.

thanks for your help

|||

Actually that was a good work around as downloading a newer version would have forced the install to run again where as in the previous case it was failing quickly becuase it thought it was laready installed.

Glad you got you machine back.

Michelle

|||

Hi,

I've been handling SQL Server upgrades for many years. The most dangerous path for an upgrade is to directly upgrade 2000 to 2005 (or 7.0 to 2000). That's because you create the risk of destroying the databases on the existing server and causing data loss.

The best approach embraces the fact that we typically upgrade a windows server when the mfg warranty on the hardware has expired. So, you have a new box and windows server. If you don't, the store the backups on another resource (disk, tape, flash drive, etc...) and copy from or restore from that resource. Rebuild the Windows server or uninstall SQL Server 2000. (I would have fresh windows to insure the best chance of success.)

Install SQL 2005 & configure on the new W03 SP1 server. migrate the users. (this worked easily with 2K but MS has hosed that functionality in 2005 from what I can see; you must use a script they provided that is flaky. You will need to manually create the users it fails to migrate.) Backup the dbs on 2K, shut down 2K on the old machine and restore the dbs to 2005. associate the users with the logins.

Now you have a fullly upgraded database server and your databases are unhosed. This may not be elegant but it guarantees the MS upgrade process won't destroy your databases or bring down your business for days.

If you have a completely hosed system, then rebuild the system (and the drives) and follow the steps above to a successful conclusion.

z.

[help] SQL Error - I/O Error 2 (The system cannot find the file specified)

Windows 2000 Server SP4 + SQL Server 2000 Enterprise Editon SP3,RAID5.

the windows event log give the following error information:

I/O error 2(The system cannot find the file specified) detected during write at offset 0x0000010c6c4000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL\data\GJSZBANK_Data.MDF'.

I have searched microsoft knowledge base and got this article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;828339

but don't understand some contents in the topic:

For example, if you encounter the following error message in the SQL Server Errorlog file, SQL Server encountered operating system error 2 when it uses a Windows API call to write to the tempdb primary database file:

Error: 823, Severity: 24, State: 4
I/O error 2(The system cannot find the file specified.) detected during write at offset 0x00000000284000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf'

Because SQL Server has already successfully opened the file and did not receive an Invalid Handle error, the error is likely being raised in a lower-level kernel software component, such as the file system or a device driver. This problem does not indicate a problem in SQL Server, and it must be investigated as an issue with the file system or a device driver that is associated with the file.

Does that mean this is not a SQL Server error?
Does that mean something wrong with my operating system? or something wrong with my hard disk?Are there any errors in the SQL errorlog? Or is it only in the NT Event log?

@schema_change_script usage

When does the script specified in the @.schema_change_script parameter for
the sp_repladdcolumn and sp_repldropcolumn get fired ? Is it after the
adding and dropping of the column on all ends or before ?
Thanks
after.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ObWssO%23VFHA.2796@.TK2MSFTNGP09.phx.gbl...
> When does the script specified in the @.schema_change_script parameter for
> the sp_repladdcolumn and sp_repldropcolumn get fired ? Is it after the
> adding and dropping of the column on all ends or before ?
> Thanks
>