Network Connects to Wrong SQL Instance

I have a server running Windows Server 2003 with two instances of SQL Server
2000 - a default instance and a named instance I shall call "INSTANCE_A".
Locally, SQL Server works correctly. Query Analyzer connects to both
instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
network, when I connect to default instance SERVER it actually connects to
SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is unchange
d
and works correctly. Port numbers are set correctly to 1433 and 1226
respectively. The server has been rebooted several times - no change. All
network clients are behaving the same - they all see SERVER\INSTANCE_A when
pointed to SERVER. Any ideas on what's gone wrong?"Jonno" <Jonno@.discussions.microsoft.com> wrote in message
news:57B7981D-0750-43F9-8BB5-F39B4A581570@.microsoft.com...
>I have a server running Windows Server 2003 with two instances of SQL
>Server
> 2000 - a default instance and a named instance I shall call "INSTANCE_A".
> Locally, SQL Server works correctly. Query Analyzer connects to both
> instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
> network, when I connect to default instance SERVER it actually connects to
> SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is
> unchanged
> and works correctly. Port numbers are set correctly to 1433 and 1226
> respectively. The server has been rebooted several times - no change. All
> network clients are behaving the same - they all see SERVER\INSTANCE_A
> when
> pointed to SERVER. Any ideas on what's gone wrong?
Validate that the remote clients are connecting over TCP/IP.
Validate the ports the instances are using by looking into the log file for
each intance.
David|||Thanks David
Both instances have TCP/IP only.
Log files show that both instances are listening on TCP, with default
instance listening on port 1433, and INSTANCE_A listening on port 1226.
Is it possible the publishing of the default instance has become corrupted?
I've tried re-registering from the client side, but the problem persists.
"David Browne" wrote:

> "Jonno" <Jonno@.discussions.microsoft.com> wrote in message
> news:57B7981D-0750-43F9-8BB5-F39B4A581570@.microsoft.com...
>
> Validate that the remote clients are connecting over TCP/IP.
> Validate the ports the instances are using by looking into the log file fo
r
> each intance.
>
> David
>
>

Network Connects to Wrong SQL Instance

I have a server running Windows Server 2003 with two instances of SQL Server
2000 - a default instance and a named instance I shall call "INSTANCE_A".
Locally, SQL Server works correctly. Query Analyzer connects to both
instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
network, when I connect to default instance SERVER it actually connects to
SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is unchanged
and works correctly. Port numbers are set correctly to 1433 and 1226
respectively. The server has been rebooted several times - no change. All
network clients are behaving the same - they all see SERVER\INSTANCE_A when
pointed to SERVER. Any ideas on what's gone wrong?"Jonno" <Jonno@.discussions.microsoft.com> wrote in message
news:57B7981D-0750-43F9-8BB5-F39B4A581570@.microsoft.com...
>I have a server running Windows Server 2003 with two instances of SQL
>Server
> 2000 - a default instance and a named instance I shall call "INSTANCE_A".
> Locally, SQL Server works correctly. Query Analyzer connects to both
> instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
> network, when I connect to default instance SERVER it actually connects to
> SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is
> unchanged
> and works correctly. Port numbers are set correctly to 1433 and 1226
> respectively. The server has been rebooted several times - no change. All
> network clients are behaving the same - they all see SERVER\INSTANCE_A
> when
> pointed to SERVER. Any ideas on what's gone wrong?
Validate that the remote clients are connecting over TCP/IP.
Validate the ports the instances are using by looking into the log file for
each intance.
David|||Thanks David
Both instances have TCP/IP only.
Log files show that both instances are listening on TCP, with default
instance listening on port 1433, and INSTANCE_A listening on port 1226.
Is it possible the publishing of the default instance has become corrupted?
I've tried re-registering from the client side, but the problem persists.
"David Browne" wrote:
> "Jonno" <Jonno@.discussions.microsoft.com> wrote in message
> news:57B7981D-0750-43F9-8BB5-F39B4A581570@.microsoft.com...
> >I have a server running Windows Server 2003 with two instances of SQL
> >Server
> > 2000 - a default instance and a named instance I shall call "INSTANCE_A".
> > Locally, SQL Server works correctly. Query Analyzer connects to both
> > instances (SERVER and SERVER\INSTANCE_A) correctly. However, over the
> > network, when I connect to default instance SERVER it actually connects to
> > SERVER\INSTANCE_A. Over the network, access to SERVER\INSTANCE_A is
> > unchanged
> > and works correctly. Port numbers are set correctly to 1433 and 1226
> > respectively. The server has been rebooted several times - no change. All
> > network clients are behaving the same - they all see SERVER\INSTANCE_A
> > when
> > pointed to SERVER. Any ideas on what's gone wrong?
>
> Validate that the remote clients are connecting over TCP/IP.
> Validate the ports the instances are using by looking into the log file for
> each intance.
>
> David
>
>

netbios and named pipes sql replication.

A client that we replicate to is receiving some
netbios request that are originating from our
distribution server. I looked in the protocol
section to see which protocol is on top. I
assume the one on top is the order of which
protocol it will try to use first. Both us and
the subscriber have tcp/ip listed first.
However, they are receiving netbios request from
our machine. We have netbios over tcp/ip
disabled. Therefore, i am guessing that the
distributor is trying to use named pipes to
communicate. Is this correct? Does named pipes
even use netbios for discovery?
tia..
-comb
-comb
yes Named Pipes uses netbios.
we had a situation after apply a MS patch to the SQL server which disabled
netbios connections (security hole).
Clients (ODBC) which had been setup to use TCP/IP would switch to named
pipes if the TCP/IP connection could not be made or was dropped sometine in
the life of the ODBC. Once the client switched to named pipes, it never
switches back to TC/IP. It remains at the last protocol it made a good
connection with.
When we took named pipes support off the SQL server with the MS patch, those
clients could not connect.
We had to manually walk thru the ODBC setup and configure the CLIENT
CONFIGURATION to use the TCP/IP library.
Even if the client was using a native or 3rd party connector the client uses
the last good connection library to make all subsequent connections to that
SQL server.
It is actually stored in the registry, but the easiest way to change it is
thru an ODBC configuration.
You may have a similar issue with the distributor server acting as the
client in this case.
DS
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1ba7a7e4990ef73c9896a7@.news.newsreader.co m...
> A client that we replicate to is receiving some
> netbios request that are originating from our
> distribution server. I looked in the protocol
> section to see which protocol is on top. I
> assume the one on top is the order of which
> protocol it will try to use first. Both us and
> the subscriber have tcp/ip listed first.
> However, they are receiving netbios request from
> our machine. We have netbios over tcp/ip
> disabled. Therefore, i am guessing that the
> distributor is trying to use named pipes to
> communicate. Is this correct? Does named pipes
> even use netbios for discovery?
> tia..
> -comb
|||In article <10jrt6rrko7fc4e@.news.supernews.com>,
donald.stewart2@.alcoa.com says...
> -comb
> yes Named Pipes uses netbios.
> we had a situation after apply a MS patch to the SQL server which disabled
> netbios connections (security hole).
> Clients (ODBC) which had been setup to use TCP/IP would switch to named
> pipes if the TCP/IP connection could not be made or was dropped sometine in
> the life of the ODBC. Once the client switched to named pipes, it never
> switches back to TC/IP. It remains at the last protocol it made a good
> connection with.
> When we took named pipes support off the SQL server with the MS patch, those
> clients could not connect.
> We had to manually walk thru the ODBC setup and configure the CLIENT
> CONFIGURATION to use the TCP/IP library.
> Even if the client was using a native or 3rd party connector the client uses
> the last good connection library to make all subsequent connections to that
> SQL server.
> It is actually stored in the registry, but the easiest way to change it is
> thru an ODBC configuration.
> You may have a similar issue with the distributor server acting as the
> client in this case.
> DS
>
Thanks DS..
I believe this is exactally what is happening.
It's interesting to know that it defaults to the
last known good protocol. I figured it would
always try to connect with the first protocol in
the list.
I appreciate the info.
-comb

Nesting stored procedure, how to ?

Hello,

I'm trying to use nested stored procedure but can't get it to work.

I've created a stored procedure named 'sousmenu1_parents' which does a simple select, no parameter.

here's what I'm trying to achieve now :

CREATE PROCEDURE SelectAllDroitSpe

-- Add the parameters for the stored procedure here

@.UserId int

AS

EXEC sousmenu1_parents;

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT sousmenu1_parents.menu_id1 AS sousmenu1_id

FROM authorisations_speciales LEFT JOIN sousmenu1_parents ON authorisations_speciales.page_id = sousmenu1_parents.menu_id1

WHERE authorisations_speciales.utilisateur_id=@.UserId

END

I get 'sousmenu1_parents isn't a valid object name'

Where am I going wrong ?

Thanks a lot.

Flip:

If your stored procedure does a simple select then transform the procdure into an function.

Dave

|||

Hi,

you can′t directly select from a strored procedure although the procedure return a table with the results, you will have to EXEC the procedure and redirect the output into a table (like a temp table)

-Create a Table first (can be also a temp table)
-

INSERT INTO YourTable
EXEC procedurename

-Use the table in your query.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This is a good alternative; the problem with the INSERT INTO ... EXEC PROC method is that you can only use this construct at a single level in a nested series; after that you get execution errors. Don't get me wrong, I use this construct frequently but it is definitely not my first choice when I am dealing with nesting a procedure that "is a simple select". You might be able to make a case for avoiding the function for some other reason but this post is titled "Nesting stored procedure, how to?"

|||

Thanks very much.

I'm now looking up on Functions.

I'm new to Sql Server and I'm migrating an Access database.

I used many nested queries in Access and it looks like converting them is going to be a nightmare.

One thing I don't understand : if I can't "directly select from a stored procedure" what is the point in nesting stored procedure ?

|||

Flip:

Stored procedures will nest; however, it sometims requires judicious use of your alternatives. There are several options to return data from a called stored procedure to a calling stored procedure:

Use of output parameter(s)|||

ignition, I was a big user of access in my past and used many nested access queries to produce result sets. Think of Access queries as Views in SQL.

If in Access you had two queries

select col1, col2 from tablea where some where clause.... (query name is Query1)

select count(*) from Query1 (query name is Query2)

In SQL you could create a View:

Create View vw_Query1 as

select col1, col2 from tableA where....some where clause

Select count(*) from vw_Query 1

Views do not allow you to pass parameters (this is where stored procedures or functions come into play)

Reason you might want to nest stored procedures is to utilize other stored procedures to get a resultset or parameter back to the calling procedure and/or to encapsulate standard code that you cannot execute using functions. Functions have specific limitations (can't do data manipulation (insert, create, drop, can only call extended stored procedures) that you can do in stored procedures.

Create Proc usp_Test1 @.tablename varchar(100)

as

exec usp_AddMissingColumns @.tablename (does an ALTER which you can't do in functions)

exec usp_SetBlanksToNull @.tablename

return

|||

Great, it works a treat using a View !

This all make sense now ...

So for all the simple SELECT queries I had in Access (no parameters) should I use views in Sql Server ? is it in any way faster than a stored procedure, or any other advantage of this ?

Thanks again everyone, and in case I don't come back here before next year : have a lot of fun to end 2006 !

|||

Ignition, I wouldn't automatically change every simple query you have in Access to a view. If you're migrating to SQL Server, it's a good time to analyze the MS Access queries you currently have to determine if there is a continued need for them. I know that I would create queries (in Access) to do some ad hoc things, and then leave them there (laziness on my part!). When I started looking at the stored queries (for migration), I realized that many of them could be combined into a handful of views (some did basically the same thing but with different columns).

Think of a view as a subset (either just certain columns, an aggregation, and/or with specific where clauses) of a table. If you are querying the same data with the same where clause continually, this is probably a good candidate for a view... If you are continually looking at only certain columns within a table, probably a candidate for a view...

Views, stored procedures, and functions all have their place to do certain types of activities. If you lookup these items in Books Online, they will give you some scenarios on when/where and how to use them...

Good luck and happy New Year...

|||Convert your SPs to table-valued functions or even views if there is no parameterization so that you can reuse them easily.

Need urgent MDX help on using Aggreate funtion in SSAS 2005

Hi,

I need a functionality similar to Named Member in ProClarity in SSAS 2005.

In Proclarity I can define a named memeber by selecting a few levels of a dimension. The underlying MDX uses the Aggregate function something like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once the named member is selected the calculated measures show the aggreated (sum) results based on the named member and the underlying selected levels in the aggreate function.

How can I do something like this in SSAS 2005. I dont want a named set. And I tired created a calculated member (which i knew will not work but still gave a shot)

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

I also tried specifying the measure in the second parameter

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }

, [Measures].[Orders Received Local])

Is it possible to use the aggreate function dynamically as its used in ProClarity?

Thanks in advance for help

The aggregate function should work fine, can you tell us exactly what error you are getting. One thing about the aggregate function is that you would need to create the calculated member on a dimension other than the measures dimension.

Something like the following should report on a dynamically created aggregate member:

Code Snippet

WITH

MEMBER [Channel].[Channel].[AggTest]

as Aggregate({

[Channel].[Channel].[CON - Contractor]

, [Channel].[Channel].[DIS - Distributor]

, [Channel].[Channel].[END - End-User]

, [Channel].[Channel].[GRP - Group]

, [Channel].[Channel].[OEM - OEM]

, [Channel].[Channel].[PLA - Private Label]

, [Channel].[Channel].[SER - Service Provider]

, [Channel].[Channel].[SYS - System builder]

})

SELECT

{[Channel].[Channel].[AggTest]} ON COLUMNS

FROM <Cube>

|||Thanks. yes it worked this way. i made the mistake of creating it on the measures dimension. after changing the dimension to Channel it works fine.

Need urgent help on Aggregate Function

Hi,

I need to do something similar to Proclarity in SSAS 2005. In Proclarity i can create a named member by selecting a few members from a dimension. The underlying mdx looks like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once i select the named member all the calculated measures reflect the result based on the members selected in the aggregate function.

How can I do something similar in SSAS 2005. I dont want a named set and calculated member does not work if i use the plain mdx as above. But i also tried using

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }, [Measures].Orders Received Local)

just to try to to see teh behaviour but to no avail. after processing the cube and selecting this meausre shows up empty cells.

How can I solve this problem.

thanks in adavance for helping

Could you explain why "calculated member does not work if i use the plain mdx as above" - what dimension/hiererchy did you create the member on, and what results did you get?|||

Thanks for asking this question. I think i made a mistake by leaving the parent hierarchy as default "Measure" and Parent Member as empty. I have modifed the Parent hirerachy to Channel.Channel and Parent Member to Channel.All Channels. I am processing the cube now and would get back to you soon with the results.

Thanks once again

|||

after i put the correct hierarchy and parent member the mdx works just fine.

Thanks a million for your help.

Need to sum columns

I need to create a view (I believe) that will take a column named
'accountbalance' for a group of records, and total (sum) up those entries...
can someone point me to a tutorial to learn how to do this? THanks!Never mind.. I found a reference. thanks tho

Need to sum columns

I need to create a view (I believe) that will take a column named
'accountbalance' for a group of records, and total (sum) up those entries...
can someone point me to a tutorial to learn how to do this? THanks!Never mind.. I found a reference. thanks tho