Problem registering sql server

I am attempting to connect to a SQL Server across the Internet, when I
attempt to register the server through the Enterprise Manager using the IP
provided by the web hosting provider, I get the following error :"SQL Server
does not exist or access denied" , I was told by them that I should change
the default port from 1433 to 4000. I don't know where in SQL Servre i could
make this modification. Anybody knows how?
I have 2 instances of SQL Server, the default one and another named
instance, I am using the named instance, the default one has problems & is
not working, so changing this port thing would be related to the instance I
am working on or to the SQL Server as a whole?
Thanks in advance.
You can use the Client Network Utility to specify ports -
select properties for the TCP IP protocol and define the
port. The named instance would normally go through the
resolution service to determine what port it is listening
on.
Either way, you can always just create an alias using the
Client Network Utility and specify the ports when setting up
the alias.
-Sue
On Wed, 26 Jan 2005 04:19:03 -0800, JessyEzzy
<JessyEzzy@.discussions.microsoft.com> wrote:

>I am attempting to connect to a SQL Server across the Internet, when I
>attempt to register the server through the Enterprise Manager using the IP
>provided by the web hosting provider, I get the following error :"SQL Server
>does not exist or access denied" , I was told by them that I should change
>the default port from 1433 to 4000. I don't know where in SQL Servre i could
>make this modification. Anybody knows how?
>I have 2 instances of SQL Server, the default one and another named
>instance, I am using the named instance, the default one has problems & is
>not working, so changing this port thing would be related to the instance I
>am working on or to the SQL Server as a whole?
>Thanks in advance.

Problem registering sql server

I am attempting to connect to a SQL Server across the Internet, when I
attempt to register the server through the Enterprise Manager using the IP
provided by the web hosting provider, I get the following error :"SQL Server
does not exist or access denied" , I was told by them that I should change
the default port from 1433 to 4000. I don't know where in SQL Servre i could
make this modification. Anybody knows how?
I have 2 instances of SQL Server, the default one and another named
instance, I am using the named instance, the default one has problems & is
not working, so changing this port thing would be related to the instance I
am working on or to the SQL Server as a whole?
Thanks in advance.You can use the Client Network Utility to specify ports -
select properties for the TCP IP protocol and define the
port. The named instance would normally go through the
resolution service to determine what port it is listening
on.
Either way, you can always just create an alias using the
Client Network Utility and specify the ports when setting up
the alias.
-Sue
On Wed, 26 Jan 2005 04:19:03 -0800, JessyEzzy
<JessyEzzy@.discussions.microsoft.com> wrote:

>I am attempting to connect to a SQL Server across the Internet, when I
>attempt to register the server through the Enterprise Manager using the IP
>provided by the web hosting provider, I get the following error :"SQL Serve
r
>does not exist or access denied" , I was told by them that I should change
>the default port from 1433 to 4000. I don't know where in SQL Servre i coul
d
>make this modification. Anybody knows how?
>I have 2 instances of SQL Server, the default one and another named
>instance, I am using the named instance, the default one has problems & is
>not working, so changing this port thing would be related to the instance I
>am working on or to the SQL Server as a whole?
>Thanks in advance.sql

Problem populating temp table from linked server.

Hello,

I have a 2000 sql server linked to a 2005 sql server an I am trying to return data across the link. If I just run the sp I get the data back fine but if I try to Insert the data into a temp table the process just hangs and has to be killed.

This works fine:

EXEC [MyLink].[MyDocs].[dbo].[spGetSearchWrapper] -- (returns 156k records in about 2 sec.)

However inserting the results into a local temp table never returns. In fact the process never really runs.

CREATE TABLE #tmpOrgResult
(
intObjectID INT NOT NULL,
intObjectTypeCodeID INT NOT NULL

)
GO

-- Insert org records that match the search.
INSERT INTO #tmpOrgResult
(
intObjectID,
intObjectTypeCodeID
)
EXEC [MyLink].[MyDocs].[dbo].[spGetSearchWrapper] -- (This statement just hangs)

Try with

SET REMOTE_PROC_TRANSACTIONS OFF

before the INSERT statement. What I suspect happens is the local transaction is promoted to a distributed one and something gets messed up.

Zlatko

|||

You are correct it is getting promoted to a distributed transaction. Turns out MSDTC was off by default on the server. The following article showed me how to enable it. Works like a champ now.

http://support.microsoft.com/?kbid=873160

|||You are welcome.