Cant get list of servers

I can't get my server to show with the DMO function ListAvailableSQLServers or with the osql -L flag. The OS is windows 2000, but the drive is fat32 and doesn't have Directory Service.
Do you need NTFS to have Directory Service, and does SQL Server need Directory Service to broadcast its available servers?
I've got Visual Studio .Net and had no luck when I searched for an answer.I meant Active Directory.|||no neither ADS nor NTFS is essential for server listing. r u able to connect to other servers using EM or QA? is EM showing the complete list? of not there could be issues with the broadcasting server. check if 1433 port is not blocked by any firewall programa at server end. also check that the server is not hidden by server network utility >> TCPIP >> hide server.|||It is actually MSDE, so I don't have EM or QA. But I have Visual Studio .Net and it has Server Explorer which functions much like EM and QA. The databases and programs I use to connect are on the same machine. I use this to learn/practice SQL Server. I don't have a machine with a server OS, so I can't check "proper" server operation.
But yes, Server Explorer connects and shows my SS instance. I can use osql, programs I create in VB.net, or even web pages scripted with ADO or DMO objects to connect and modify the SS databases, as long as I specify the server. The only thing I can't seem to do is get a list of the servers (I only have one server).
I have a software firewall, but only have it running while my dial-up is online.
Not sure what you mean by "server network utility >> TCPIP >> hide server". I have the server/client tools that were included (in the tools\binn folder); the program cnfgsvr.exe sounds like a good place to start. I'll check it out.
Thanks|||the server network utility i am referring to uses ...\bin\svrnetcn.exe (non-msde version). the exe u have identified may work as well, not sure. for same machine sql server uses shared-memory net lib. if TCPIP or Named Pipe is not enabled, i have seen that OSQL -L fails to identify local server...|||Yeah, that's the one,SVRNETCN.EXE, the Sql Server Network Utility. Both Tcp/Ip and Named Pipes protocols are enabled, the default port 1433 is set, and the Hide Server option is unchecked.
I forgot to mention that my MSDE was included free with my VS.Net. When I installed it, I chose all the defaults which gave my sql server an instance name of VSDOTNET. I don't know why it didn't just install with the default computername. Maybe that has something to do with it?
So I used the Sql Server Client Network Utility, CLICONFG.EXE, and created aliases, one for tcp/ip and one for named pipes. The aliases were listed properly with OSQL -L and using the DMO App.ListAvailableSQLServers() function. I don't know why computername\vsdotnet won't list without the aliases.

Can't get linked servers to work

I'm having trouble getting a new linked server to work.
Server Mfr is 2005 Dev (sp1) and is the 'local' server.
Server Krypton is 2000 EE sp3 and is the target server I need to link to and
query.
I have a valid Windows login on both systems and I'm in the sysadmin group
on both servers.
After linking the servers using Mgt Studio, I ran this:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'KRYPTON', @.useself = 'true'
When I try running this query:
SELECT * FROM OPENQUERY(KRYPTON, 'SELECT * FROM CRICUST WHERE CustName =
''Acme''')
I get these errors:
OLE DB provider "SQLNCLI" for linked server "KRYPTON" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Can you see what I'm missing?
Thanks!
djNo login credentials are being passed to the remote server.
Did you set everything up needed for delegation?
Have you tried with a SQL Login to see if the basics parts
(without the delegation issues) are working? For the linked
server, Just map all users to a valid SQL login on the
remote server to see if that works.
-Sue
On Thu, 10 Aug 2006 06:40:02 -0700, dj
<dj@.discussions.microsoft.com> wrote:

>I'm having trouble getting a new linked server to work.
>Server Mfr is 2005 Dev (sp1) and is the 'local' server.
>Server Krypton is 2000 EE sp3 and is the target server I need to link to an
d
>query.
>I have a valid Windows login on both systems and I'm in the sysadmin group
>on both servers.
>After linking the servers using Mgt Studio, I ran this:
>EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'KRYPTON', @.useself = 'true'
>When I try running this query:
>SELECT * FROM OPENQUERY(KRYPTON, 'SELECT * FROM CRICUST WHERE CustName =
>''Acme''')
>I get these errors:
>OLE DB provider "SQLNCLI" for linked server "KRYPTON" returned message
>"Communication link failure".
>Msg 10054, Level 16, State 1, Line 0
>TCP Provider: An existing connection was forcibly closed by the remote host
.
>Msg 18452, Level 14, State 1, Line 0
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Can you see what I'm missing?
>Thanks!
>dj|||Hi Sue -
We're not on AD, so I believe delegation isn't an option.
If I'm using Windows authentication and my account exits on both machines,
do I still need to pass credentials? I thought the 'useself' parameter took
care of that.
Or am I just very confused (quite possibly)?
Thanks.
"Sue Hoegemeier" wrote:

> No login credentials are being passed to the remote server.
> Did you set everything up needed for delegation?
> Have you tried with a SQL Login to see if the basics parts
> (without the delegation issues) are working? For the linked
> server, Just map all users to a valid SQL login on the
> remote server to see if that works.
> -Sue
> On Thu, 10 Aug 2006 06:40:02 -0700, dj
> <dj@.discussions.microsoft.com> wrote:
>
>|||You are correct that if you aren't using AD, you can't use
delegation. Delegation will pass your authentication
information from one server to another. useself just says to
use the current login for authentication against the remote
server but doesn't really manage any of the passing of
credentials or how that will work (or not work).
Try using a SQL login for the security mappings to the
remote linked server.
-Sue
On Thu, 10 Aug 2006 11:07:01 -0700, dj
<dj@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Sue -
>We're not on AD, so I believe delegation isn't an option.
>If I'm using Windows authentication and my account exits on both machines,
>do I still need to pass credentials? I thought the 'useself' parameter too
k
>care of that.
>Or am I just very confused (quite possibly)?
>Thanks.
>"Sue Hoegemeier" wrote:
>

can't execute tasks after moving .dtsx to new server

Hello.

I have two servers, A for dev and B for production.

On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.

All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.

On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.

The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.

Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.

Can anyone help with this as my deployment to live isn't going very well to say the least!

Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.

Thanks.

bobbins wrote:

Hello.

I have two servers, A for dev and B for production.

On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.

All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.

On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.

The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.

Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.

Can anyone help with this as my deployment to live isn't going very well to say the least!

Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.

Thanks.

The easiest way to redeploy SSIS Packages to a different server is to use the Deployment Utility: http://msdn2.microsoft.com/en-us/library/ms141190.aspx|||

Hi bobbins,

Thoughts / suggestions:

The 'document is opened by another project' error is interesting. First, make sure Business Intelligence Development Studio(BIDS) is shut down on your first server. Instead of creating a new empty project and importing the package, try copying the entire project folder to your shared drive and opening it from the BIDS on your second server. This sounds more like a file locking issue to me.

Hope this helps,

Andy

|||

Thanks for the replies.

I copied the entire project folder to the new server and it is ok. The only issue that arises is that some of the connection managers lose their login information, these are ADO.NET ODBC connections to an Ingres database, so I suspect this is just normal behaviour anyway.

Thanks again.

Can't Drop table in Replication

Dear friends

I restore one database in two database servers which is running on SQL server 2000.I replicated these two through snapshot relication.Snapshot agent is creating snapshot.But when I am starting to synchronize it's telling can't drop table because that table is in replication. Backup I taken from a replicated database.so it's having rowguid both the servers.Another thing the table which it is telling not able to drop it's having primary key.please tell me what may be the problem.In replication why it is going to drop table it's only what to transport data na

Filson

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.|||

Greg Y wrote:

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.

I done like that.But I got a problem .I am using backup from replicated database .This backup only i restored on my publisher & subscriber.When I am subscribing three system stored procedure should be generate na for Del,Ins.Upd operations .This is not happening.I tried to delete this system generated procedures in the restored database .But it's not allowing.Then I applied replication Pubs database which is coming along with SQL Server .Then what I seen I published all Tables(10).But Stored procedure only for 5 tables is generated .in these tables whatever changes i am making it's affecting through Replication,Example publisher table in pubs database is not getting any stored procedure after subscription.So it's not getting any change through replication.Then I am getting error message'procedure Sp_MsIns_Publisher not found'.So please tell me what to do remove all old Stored procedures for replication in restored database and how to create the stored procedures for all the tables published

Thanks in Advance

Filson

|||

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

|||

Greg Y wrote:

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

Greg thanks for suggestion.Previously itself I tried these ways to remove replication components.But still System stored procedures for table Ins,Del,Upd is not dropping.I want a specific way to drop it out.I think i mentioned this in earlier post.So kindly suggest me a better way for that

Filson

|||Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.|||

Greg Y wrote:

Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.

I am describing my problem below .I taken a backup of replicated database.That I restored on another server.I made that one as a publisher.Another instance I made as a subscriber.I restored same database on this subscriber.Then I tried for transactional replication.While synchronizing I got error on my subscriber 'Sp_MsIns_AgentCode is not found'.This I got while Inserting records to AgentCode Table in publisher.Then I come to know for each table article published in Transactional replication will have three system generated stored procedures 1)insertion 2)updation 3)Deletion.This is not generating when i subscribing to my publisher..Here when I am pulling the subscription I am specifying 'No Shema & data transfer'.So I am not able to transport this System generated Procedures.If I am selecting 'Schema & data Tansfer'.It won't able to initialize in subscriber due to Foreign Key criterias. after completing Subscription through Wizard,Can I trnsfer UPD,INS,DEL Procedure Schema through any Sp_procedure call? Give me some better way

Filson

|||

I mentioned above to run stored procedure sp_scriptpublicationcustomprocs at the subscriber database after applying the snapshot, did you do that? That proc is supposed to generate the missing sp_MSins/upd/del procs that the distribution agent is trying to execute.

Can''t Delete Maintenance Report Files (txt) in SQL/2005

I have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?

Thanks in Advance,

DeWayne

did you create a maintenance clean up task and specify the .txt file extension to delete old text files ? but xp_delete_file is corresponding T_SQL of the cleanup task............if you hevent created the cleanup task just try it out and get back

|||

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

|||

Same problem here. Is there any hotfix to address this issue?

|||Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas?
|||Same for me, anyone from MS like to comment on this?|||

There is a support article on the subject, http://support.microsoft.com/kb/938085. The problem appears to be the first line of text in the report files. If it says "NEW COMPONENT OUTPUT", then xp_delete_file will not delete it. The stated workaround is to remove this text. That does work, but it's probably simpler to just manually delete the file. Hope a fix comes out soon.

Can''t Delete Maintenance Report Files (txt) in SQL/2005

I have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?

Thanks in Advance,

DeWayne

did you create a maintenance clean up task and specify the .txt file extension to delete old text files ? but xp_delete_file is corresponding T_SQL of the cleanup task............if you hevent created the cleanup task just try it out and get back

|||

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

|||

Same problem here. Is there any hotfix to address this issue?

|||Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas?
|||Same for me, anyone from MS like to comment on this?|||

There is a support article on the subject, http://support.microsoft.com/kb/938085. The problem appears to be the first line of text in the report files. If it says "NEW COMPONENT OUTPUT", then xp_delete_file will not delete it. The stated workaround is to remove this text. That does work, but it's probably simpler to just manually delete the file. Hope a fix comes out soon.

Can''t Delete Maintenance Report Files (txt) in SQL/2005

I have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?

Thanks in Advance,

DeWayne

did you create a maintenance clean up task and specify the .txt file extension to delete old text files ? but xp_delete_file is corresponding T_SQL of the cleanup task............if you hevent created the cleanup task just try it out and get back

|||

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

|||

Same problem here. Is there any hotfix to address this issue?

|||Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas?
|||

Same for me, anyone from MS like to comment on this?|||

There is a support article on the subject, http://support.microsoft.com/kb/938085. The problem appears to be the first line of text in the report files. If it says "NEW COMPONENT OUTPUT", then xp_delete_file will not delete it. The stated workaround is to remove this text. That does work, but it's probably simpler to just manually delete the file. Hope a fix comes out soon.

Can''t Delete Maintenance Report Files (txt) in SQL/2005

I have several maintenance plans setup and working in SQL/2005 (sp2 9.0.3042) on several servers; however, the .txt maint plan output files will not delete on any of the servers. The backups run fine, the old database and transaction log backup files delete fine (.bak and .trn); but, the .txt files won't go away unless I delete them by hand. I tried copy/paste the scripted command (xp_delete_file) and it runs with no errors but does not delete the old .txt files. ( This server was upgraded from SQL/2000 -

EXECUTE master.dbo.xp_delete_file 1,N'E:\Program Files\Microsoft SQL Server\MSSQL\LOG\',N'txt',N'2007-07-09T10:59:38'

)

I do NOT specify a period (.) in the file extension dialog box. Any ideas before I setup a .cmd batch file and schedule it instead?

Thanks in Advance,

DeWayne

did you create a maintenance clean up task and specify the .txt file extension to delete old text files ? but xp_delete_file is corresponding T_SQL of the cleanup task............if you hevent created the cleanup task just try it out and get back

|||

Yes! That's where I got the xp_delete_file script that I quoted in my original post...as it does not delete any .txt files.

Thanks

|||

Same problem here. Is there any hotfix to address this issue?

|||Same here - I've tried on several SQL 2005 servers and none of them delete the .txt files. Any ideas?
|||Same for me, anyone from MS like to comment on this?|||

There is a support article on the subject, http://support.microsoft.com/kb/938085. The problem appears to be the first line of text in the report files. If it says "NEW COMPONENT OUTPUT", then xp_delete_file will not delete it. The stated workaround is to remove this text. That does work, but it's probably simpler to just manually delete the file. Hope a fix comes out soon.

Can't create a linked server when the servers are set up to replicate

I can't create a linked server when the same two servers are set up for repl
icatation. I get 'Error 15028: The server 'SERVERNAME' already exists.' whe
n I try to add a linked server. And it does exist as a Remote Server becaus
e as I mentioned, the two s
ervers are replicating data.
There has to be a way to add a linked server when two servers are already re
plicating. Or, there has to be a way to use the remote servers for data acc
ess.
Any suggestons?
Thanks,
SniperXYou may have to use sp_addlinkedsrvlogin . This was new for SQL 2000
remote servers. There's also a kb article documenting this issue.
274098 PRB: Adding a Linked Server Causes Error 15028
http://support.microsoft.com/?id=274098
From Books Online:
Setting Up the Local Server
In SQL Server 2000, create remote server connections for remote server
logins created by Windows Authentication by:
Setting up a local login mapping on a local server that defines what login
and password are used by an instance of SQL Server when it makes an RPC
connection to a remote server.
For logins created by Windows Authentication, you must create a mapping to
a login name and password. This login name and password must match the
incoming login and password expected by the remote server.
Using the sp_addlinkedsrvlogin stored procedure to create local login
mappings.
Note For logins created by SQL Server Authentication, it is not necessary
to create any local login mappings for executing a stored procedure against
a remote server.
Security Note When possible, use Windows Authentication.
274098 PRB: Adding a Linked Server Causes Error 15028
http://support.microsoft.com/?id=274098
When configuring new subscribers for replication, SQL Server adds the
subscriber as a remote server and stores its information in the
master..sysservers system table. Subsequent attempts to add a linked server
using the same name as an existing subscriber may cause error 15028 to
occur:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'SQL1' already exists.
The article also references the use of sp_addlinkedsrvlogin
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Can't connect via Enterprise Manager

I had a similar problem that was due to the server not
using the proper TCP/IP port. I am guessing that one of
your servers is using a different port, (or none at all),
so it can't be seen by clients looking for the default
port number 1433, at least that was the problem in my case.
Check your Windows registry settings for the following
entries, (this is the key for a default instance, but
there are similar key paths for multiple instances):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServ
er\SuperSocketNetLib\Tcp\TcpPort
This value is the port that SQL Server will listen on. In
some cases it does not get set up correctly on the
install. The default is a decimal value of "1433" (if it
was blank then the server was only using shared memory
protocol)
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\Su
perSocketNetLib\Tcp\DefaultPort
This value is the port that clients use to communicate
with the server. It normally will also be "1433" (decimal).
If you do change these values you should shut down the
server service first and check the registry again after
the server is running again... I had a heck of a time
getting my server to use port 1433, as it kept switching
to port 2433 after the server started up. (Not sure if
it's necessary, but I also put a "" in the TcpDynamicPorts
variable) to make sure dynamic port assignments is off.
Alternatively, if you do have different port settings and
need to keep them as they are, you can create alias
entries on your clients that will associate the proper
protocols and ports for each server.
HTH,
TK
>--Original Message--
>Hi All,
>We have a MSDE on a W2K server installed. It is running
fine and the
>application can access it without a problem. We have
another server on that
>same subnet that has an SQL 2000 server installed. When
trying to connect to
>the MSDE via the Enterprise Manager, only the local SQL
shows up and we
>cannot connect to the MSDE instance.
>When we go to the ODBC on the MSDE box we see both SQL -
the local and the
>remote one. On the ODBC on the other server we only see
the local one.
>Does anybody have an idea what is going on?
>Thanks,
>Claus
>
>.
>
Thanks for the reply. I finally was able to connect. I still don't have any
idea what the reason was. I rebooted both servers and it worked without
making any changes. No entries in the event logs.
MS never stops surprising me.
thanks again to all of you for your good advise,
Claus
"TK" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9f01c4a58c$61560b90$a301280a@.phx.gbl...[vbcol=seagreen]
> I had a similar problem that was due to the server not
> using the proper TCP/IP port. I am guessing that one of
> your servers is using a different port, (or none at all),
> so it can't be seen by clients looking for the default
> port number 1433, at least that was the problem in my case.
> Check your Windows registry settings for the following
> entries, (this is the key for a default instance, but
> there are similar key paths for multiple instances):
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServ
> er\SuperSocketNetLib\Tcp\TcpPort
> This value is the port that SQL Server will listen on. In
> some cases it does not get set up correctly on the
> install. The default is a decimal value of "1433" (if it
> was blank then the server was only using shared memory
> protocol)
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\Su
> perSocketNetLib\Tcp\DefaultPort
> This value is the port that clients use to communicate
> with the server. It normally will also be "1433" (decimal).
> If you do change these values you should shut down the
> server service first and check the registry again after
> the server is running again... I had a heck of a time
> getting my server to use port 1433, as it kept switching
> to port 2433 after the server started up. (Not sure if
> it's necessary, but I also put a "" in the TcpDynamicPorts
> variable) to make sure dynamic port assignments is off.
> Alternatively, if you do have different port settings and
> need to keep them as they are, you can create alias
> entries on your clients that will associate the proper
> protocols and ports for each server.
> HTH,
> TK
> fine and the
> another server on that
> trying to connect to
> shows up and we
> the local and the
> the local one.

Cant connect to remote server

I recently reinstalled SQL Server 2005 on a new computer, and now I can't connect to remote SQL servers for some reason.

When trying to access the remote server with SQL Management Studio or DTS I get the following error:

----
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
----

I'm certain the servers I'm trying to connect to allow remote connections so the fault must be on my side. Does anyone have any idea what the cause and solution might be?

Rydeman:

I'm certain the servers I'm trying to connect to allow remote connections so the fault must be on my side.

The default behaviour of a new sql install is to NOT allow remote connections until they are specifically enabled. Since you are dealing with a new install, i'd check the server settings to be sure:http://support.microsoft.com/kb/914277

|||

So for it to work both instances of sql server has to be configured to allow remote connections?

Because the reinstall is on the local client computer, the online servers are untouched.

|||yes may be it will work after re-install u have enable the TCP-IP connection in sql-server configuration manager

Can't connect to linked SQL server: Cannot initialize the data source object of OLE DB provider

I find this most perplexing.

I have two servers, DEV and PROD. Now my DEV server works just great, I can connect to the linked server, query, etc... all is well.

So I'm setting up my PROD server and when I go to add the linked server I get:

Cannot initialize the data source object of OLE DB provider "SQLNCLI".... and Unable to complete login process due to delay in opening server connection.

Now I am running SQL Server 2005 and connecting to an SQL 2000 server.

The odd part is that this works just fine on DEV.

When I go to create the linked server I set:

Linked Server: "LinkedServerName"

Server Type: "SQL Server"

and that's it.

I go to Security and enter my DOMAIN\USER.ACCOUNT and then enter the login creds for the linked server.

When I click "OK" I get the above mentioned error code.

Any thoughts?

Please see if http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990327&SiteID=1 could be useful for you.|||

Thanks, I'll check that out.

This whole thing is very odd as I even saved the working LinkedServer as TSQL script then executed it on the PROD server and with the EXACT same information for the linked server it fails to work.

I'll read up on the link you provided and keep my fingers crossed.

No in my case I'm not having an issue with impersonation. The credentials are all configured the same on both servers, yet my DEV server works while PROD fails...

Here is the error message I receive when I try to test the linked server connection:

"The test connection to the linked server failed."

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MaxersServer".
OLE DB provider "SQLNCLI" for linked server "MaxersServer" returned message "Unable to complete login process due to delay in opening server connection". (.Net SqlClient Data Provider)

Cant connect SQL server by Enterprise manager but no problem by Query analyzer

I cant connect SQL server by Enterprise manager, then i try to connect it by Query analyzer it works !
I have 2 SQL servers get the same problem, no matter how many time i try,
Even I set the timeout to900 seconds, I still got the message in Enterprise manager..

Help me please..!

A connection could not be established to xxx.xx.xxx.x

Reason: Timeout expired.

Please verify SQL Server is running and check your SQL Server registration properties
(by right-clicking on the xxx.xx.xxx.x node) and try again.I have been able to get this working by going to the Client Network Utility and switching the primary network library from TCP/IP to Named Pipes or vice-versa. No idea why some machines prefer one over the other, but life would be boring, if we did not have these little things to keep us up all night ;-).
~Matt

Can't connect from SQL Server 2000 Enterprise Manager

I have two servers, one have SQL Server 2000 and another has CTP2005. I am trying to register the newly installed CTP 2005 in Enterprise Manager of SQL Server 2000, but failed.
I have make sure that SQL Browser is started and TCP/IP protocol is enabled in the configuration manager. I stopped also all firewalls between the two servers. But I still cannot find the CTP. The error message seem to be some Japanese like "...SQL Server Management Studio...SQL Server Management Object(SMO)..." where ...are Japanese.
However, I have no problem registering SQL Server 2000 to SQL Server Management Studio.
Please help. Thanks.Enterprise Manager in SQL Server 2000 cannot manage SQL Server 2005 isntances. However, SQL Sever Management Studio in SQL Server 2005 can manage SQL Sever 2000 instances.|||Thats not entirely true. You will not be able to manage SQL server 2000 services from SQL server 2005 Management Console .

Microsoft documentation clearly specifies this:

"SQL Server 2000 services cannot be managed with SQL Server Management Studio or SQL Server Configuration Manager. Use SQL Server 2000 Enterprise Manager to change service accounts, Service Manager to start and stop services, Server Network Utility to manage server protocols, and Client Network Utility to manage client protocols and aliases"