sql 2008 default port

yet if i test the linked server connection within management studio, it tests fine.
i'm not sure what i am missing.

1433 is the def port for sql, so i'm not sure at all how i got it on 1483/1484
surely this can be addressed w/out an uninstall/reinstall, but i'd rather re-do it right, than mess around with stuff like these unspecified errors that i get when selecting from the linked server

any ideas?

Hey guy,

Try run the script:

Get-TCPPort.ps1 hostname POWERPC instanceName MSSQLSERVER

getting closer....http://support.microsoft.com/kb/906954
the v2008 instance is x64, the v2000 linked server is not.  ok.  not really sure how to address that, but it's becoming a little more clear.

what of the default port?

Get-TCPPort.ps1 -hostname "YOURSERVERNAME"" -instanceName "YOURINSTANCENAME"

all over BOL right now, trying to find reference to the Get-  syntax failing:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'hostname'.

i've got a lot of reading/research to do

# ======================================================================================================================
#
# NAME: Get-TCPPort.ps1
#
# AUTHOR: Yan Pan
# DATE  : 7/19/2008
#
# COMMENT: This script queries the registry on a SQL Server host to find the TCP port for an instance on the host.  
# =======================================================================================================================
##############################################################################
# Initialize parameters
##############################################################################
param (
 [switch]$help,
 [string]$hostName = {},  # Name of the SQL Server host.
 [string]$instanceName = {} # Name of the SQL Server instance.
    )

function getTcpPort([String] $pHostName, [String] $pInstanceName)
{
 $strTcpPort=""
 $reg = [WMIClass]"\\$pHostName\root\default:stdRegProv"
 $HKEY_LOCAL_MACHINE = 2147483650
 #SQL Server 2000 or SQL Server 2005/2008 resides on the same host as SQL Server 2000
 # Default instance
 if ($pInstanceName -eq 'MSSQLSERVER') {
  $strKeyPath = "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp"
  $strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
  if ($strTcpPort) {
   return $strTcpPort
  }
 
 }
 # Named instance
 else {
  $strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp"
  $strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
  if ($strTcpPort) {
   return $strTcpPort
  }
 }
 #SQL Server 2005
 for ($i=1; $i -le 50; $i++) {
  $strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i"
  $strInstanceName=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"").svalue
   
  if ($strInstanceName -eq $pInstanceName) {
     $strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer\SuperSocketNetLib\tcp\IPAll"
     $strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
     return $strTcpPort
  }
 }
 #SQL Server 2008
 $strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"
 $strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
 if ($strTcpPort) {
  return $strTcpPort
 }
 
 return ""
}

##############################################################################
# Main Program
##############################################################################
if ( $help ) {
 "Usage: Get-TCPPort -hostName <string[]> -instanceName <string[]>"
 exit 0
}
if ( $hostName.Length -eq 0 ) {
 "Please enter a host name."
 exit 1
}
if ( $instanceName.Length -eq 0 ) {
 "Please enter a server name."
 exit 1
}

$tcpPort=(getTcpPort $hostName $instanceName)
# If TCP Port is not available, the server or the host doesn't exist.
if ($tcpPort -eq "") {
 "TCP port is not found. Please check the server name and the host name."
 "If the server is a default instance, please use the MSSQLSERVER as the instance name."
 exit 2
}
$tcpPort
############################################# End of Script ##############################################################

but what of the default port 1433?  possibly i did something in error during the install?  why would it not be listening on that port?

No, its not a problem during installation.
TCP/IP is not enabled by default after setup.  You have to enable it which would then enable the default instance to listen on 1433.

i have enabled it... or at least i thought i did.  and i restarted the svc afterward.  it is still not in the log as being listened on

However, you mut enable TCP/IP during setup using a specified parameter in command prompt.
See http://msdn.microsoft.com/en-us/library/ms144259.aspx
You can change the default port.
See http://msdn.microsoft.com/en-us/library/ms177440.aspx

oke doke.  i've got the port corrected.  i still can't get out or in to the box.

out (to linked server):
Msg 233
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error:  0 - No process is on the other side of the pipe.)

in (from linked server):
Msg 14, Level 16, State 1, Line 2
[DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.

retract that out error.  don't know what actually produced that, but right now, my error from trying to hit the linkedserver is the same as it was before --

OLE DB provider "SQLNCLI10" for linked server "linkedservername" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "linkedservername" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "linkedservername". The provider supports the interface, but returns a failure code when it is used.

this is just getting better and better... i created this in the linked server master db:

CREATE PROCEDURE [sp_tables_info_rowset_64]
@table_name sysname
, @table_schema sysname = NULL
, @table_type nvarchar(255) = null
AS
DECLARE @Result int
SELECT @Result = 0

EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO

and now, the simple selects from the v2008 box to the v2000 linked server work fine.

Yet any reference to the v2008 instance, from anywhere, fails w/this:   Invalid connection.

Any ideas, or even just some very good reference to point me to, outside of BOL ?    you know... the top ten things every DBA better know about v2008 before trying to use the darned thing