Question regarding Pubs and Northwind

Hello all,
I have been asked this question and need to answer it. would the removal of
pubs and northwind DBs from SQL2000 have an adverse effect on upgrade to SQL
2005? I would appreciate an answer.
Regards,
Joresm
No issues at all -- these are just example data!
Cheers
Ryan
"Joresm" <joresm@.hotmail.com> wrote in message
news:eIHZTA5FGHA.2912@.tk2msftngp13.phx.gbl...
> Hello all,
> I have been asked this question and need to answer it. would the removal
> of
> pubs and northwind DBs from SQL2000 have an adverse effect on upgrade to
> SQL
> 2005? I would appreciate an answer.
> Regards,
> Joresm
>

Question regarding Pubs and Northwind

Hello all,
I have been asked this question and need to answer it. would the removal of
pubs and northwind DBs from SQL2000 have an adverse effect on upgrade to SQL
2005? I would appreciate an answer.
Regards,
JoresmNo issues at all -- these are just example data!
Cheers
Ryan
"Joresm" <joresm@.hotmail.com> wrote in message
news:eIHZTA5FGHA.2912@.tk2msftngp13.phx.gbl...
> Hello all,
> I have been asked this question and need to answer it. would the removal
> of
> pubs and northwind DBs from SQL2000 have an adverse effect on upgrade to
> SQL
> 2005? I would appreciate an answer.
> Regards,
> Joresm
>sql

Question regarding Pubs and Northwind

Hello all,
I have been asked this question and need to answer it. would the removal of
pubs and northwind DBs from SQL2000 have an adverse effect on upgrade to SQL
2005? I would appreciate an answer.
Regards,
JoresmNo issues at all -- these are just example data!
Cheers
Ryan
"Joresm" <joresm@.hotmail.com> wrote in message
news:eIHZTA5FGHA.2912@.tk2msftngp13.phx.gbl...
> Hello all,
> I have been asked this question and need to answer it. would the removal
> of
> pubs and northwind DBs from SQL2000 have an adverse effect on upgrade to
> SQL
> 2005? I would appreciate an answer.
> Regards,
> Joresm
>

Question on Settings in Connection

I've programmed a user defined function (SQL2000), which in a specific query
references a linked server (another SQL instance, BTW contained in same
physical server). The sintaxis is ok, but i couldn't apply the definition
because of following error:
"Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistente
query semantics. Enable these options and then reissue your query."
I set the corresponding settings in both servers, section Connections of
Server's properties, but to no avail.
Which is the trick here? How is resolved the 'connection' issue referred in
the error message?
Thanks in advanceMiguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> I've programmed a user defined function (SQL2000), which in a specific
> query references a linked server (another SQL instance, BTW contained in
> same physical server). The sintaxis is ok, but i couldn't apply the
> definition because of following error:
> "Error 7405: Heteogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
> options to be set for the connection. This ensures consistente
> query semantics. Enable these options and then reissue your query."
> I set the corresponding settings in both servers, section Connections of
> Server's properties, but to no avail.
> Which is the trick here? How is resolved the 'connection' issue referred
> in the error message?
The trick is to stop using Enterprise Manager for editing functions and
stored procedures. Use Query Analyzer instead, this is a far better tool
for the task.
The particular problem here, is that Enterprise Manager creates functions
and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
settings are saved with the procedure/function. Thus you need to recreate
the function with ANSI_NULLS ON. (In Query Analyzer all needed options
are ON by default.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thanks, it resolves the problem.
In ahead I will take this great tip in account.
"Erland Sommarskog" wrote:

> Miguel Castanuela (MiguelCastanuela@.discussions.microsoft.com) writes:
> The trick is to stop using Enterprise Manager for editing functions and
> stored procedures. Use Query Analyzer instead, this is a far better tool
> for the task.
> The particular problem here, is that Enterprise Manager creates functions
> and procedures with ANSI_NULLS and QUOTED_IDENTIFIER OFF, and these
> settings are saved with the procedure/function. Thus you need to recreate
> the function with ANSI_NULLS ON. (In Query Analyzer all needed options
> are ON by default.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Question on Report Builder/Designer

I have Visual Studio 2005 Team Developer. I don't need SQL2005 since I have SQL2000. How do I get Report Designer/Builder for VS2005? I would like to use the Report Designer/Builder to build reports for Windows applications. Do I HAVE to buy SQL 2005?

Thanks.

Reporting services is an add-on for SQL 2000, but it is available. You can download a 120-day evaluation version here:

http://www.microsoft.com/downloads/details.aspx?familyid=BA517C01-2E2F-4BC7-84AF-149B7637F807&displaylang=en

|||

I don't want SQL2000 I already have SQL2000. What I want to find is the Report Builder/Designer to use with VB programs within Windows. It is suppose to plug into Visual Studio 2005 which I have but I don't want to spend the money for SQL2005 just to get the plug in. I don't need Report Server just the Report Designer/Builder.

Jim

|||Report Builder is included with Reporting Services. For example, when I load my SQL Server 2005 Reporting Services site and click Build Reports, it launches the Report Builder.|||

It is also an snap-in to Visual Studio. Everything I have read is that you use it to develop reports for Windows Apps, which is what I want to do. I don't use SQL as a database generally. I normally use Access because they are small apps. But I would like to use the Report Builder/Designer to develop these app reports. That is where I'm trying to get to.

If I can get Report Builder/Designer into Visual Studio2005 then I can write reports for Windows Apps which they say you can do and is a replacement for Crystal. Also that you don't need SQL or SSRS to do this.

I just don't know where to get the snap/plug-in.

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
Paul G
Software engineer.
Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.
|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
Paul G
Software engineer.
"Chris Wood" wrote:

> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL process
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to SQL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>
>
|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...[vbcol=seagreen]
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
--
Paul G
Software engineer.Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
--
Paul G
Software engineer.
"Chris Wood" wrote:

> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL proces
s
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to S
QL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>
>|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...[vbcol=seagreen]
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:
>

question on proxy with sql 2005

I am a recent user of sql2005 but have worked with sql2000 for a few years.
I noticed under the sql server agent there is a listing for proxy. I have
used the sql server agent to set up jobs to run packages developed with
Integration services. I was wondering if anyone had a simple explination of
proxys and how/why to set them up.
Thanks.
--
Paul G
Software engineer.Paul,
My take on the proxy is if you need to run something from inside SQL that
needs to go outside of SQL you need the proxy account. So if an SQL process
needs to run xp_cmdshell then you need the proxy account. We have cases
where we start inside SQL and go outside to another server that runs a
process inside another SQL server. Here the proxy is needed to go outside
and needs to be defined on the other server and possibly given access to SQL
resources inside the other SQL Server otherwise it will not work.
Clear as mud!
To use xm\p_cmdshell you need to make it available SAC and give permission
to run it and then either create the proxy Under
Server/Properties/Security/Server proxy Account or by using the system sp
sp_xp_cmdshell_proxy_account. They both do the same thing.
HTH
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>I am a recent user of sql2005 but have worked with sql2000 for a few years.
> I noticed under the sql server agent there is a listing for proxy. I have
> used the sql server agent to set up jobs to run packages developed with
> Integration services. I was wondering if anyone had a simple explination
> of
> proxys and how/why to set them up.
> Thanks.
> --
> Paul G
> Software engineer.|||Hi Chris, thanks for the information. I think it makes sense, will probably
try to set up an example when I have some time. I guess for example if you
wanted to run an Integration Services package on server two and some how
initiate it with a job on server one, the proxy acount might be used? Also
not sure if you have run into this but I have a 2003 web app and when I open
it from vs 2003I get the error message unable to get the project file from
the web server, the two need to map to the same server? thanks.
--
Paul G
Software engineer.
"Chris Wood" wrote:
> Paul,
> My take on the proxy is if you need to run something from inside SQL that
> needs to go outside of SQL you need the proxy account. So if an SQL process
> needs to run xp_cmdshell then you need the proxy account. We have cases
> where we start inside SQL and go outside to another server that runs a
> process inside another SQL server. Here the proxy is needed to go outside
> and needs to be defined on the other server and possibly given access to SQL
> resources inside the other SQL Server otherwise it will not work.
> Clear as mud!
> To use xm\p_cmdshell you need to make it available SAC and give permission
> to run it and then either create the proxy Under
> Server/Properties/Security/Server proxy Account or by using the system sp
> sp_xp_cmdshell_proxy_account. They both do the same thing.
> HTH
> Chris
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
> >I am a recent user of sql2005 but have worked with sql2000 for a few years.
> > I noticed under the sql server agent there is a listing for proxy. I have
> > used the sql server agent to set up jobs to run packages developed with
> > Integration services. I was wondering if anyone had a simple explination
> > of
> > proxys and how/why to set them up.
> > Thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Paul,
THE proxy Account certainly does xp_cmdshell but you can have others and
assign them to do different tasks. Looks at SQL Server/Agent/Proxies. You
can create these using Security/Credentials. This is the way to define a
proxy and what it can use. As I mentioned the xp_cmdshell is defined using
the Server/Properties/Security/Server proxy Account.
Chris
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4325CA59-D1D9-4DA9-855B-FC8BF59C0338@.microsoft.com...
> Hi Chris, thanks for the information. I think it makes sense, will
> probably
> try to set up an example when I have some time. I guess for example if
> you
> wanted to run an Integration Services package on server two and some how
> initiate it with a job on server one, the proxy acount might be used?
> Also
> not sure if you have run into this but I have a 2003 web app and when I
> open
> it from vs 2003I get the error message unable to get the project file from
> the web server, the two need to map to the same server? thanks.
> --
> Paul G
> Software engineer.
>
> "Chris Wood" wrote:
>> Paul,
>> My take on the proxy is if you need to run something from inside SQL that
>> needs to go outside of SQL you need the proxy account. So if an SQL
>> process
>> needs to run xp_cmdshell then you need the proxy account. We have cases
>> where we start inside SQL and go outside to another server that runs a
>> process inside another SQL server. Here the proxy is needed to go outside
>> and needs to be defined on the other server and possibly given access to
>> SQL
>> resources inside the other SQL Server otherwise it will not work.
>> Clear as mud!
>> To use xm\p_cmdshell you need to make it available SAC and give
>> permission
>> to run it and then either create the proxy Under
>> Server/Properties/Security/Server proxy Account or by using the system sp
>> sp_xp_cmdshell_proxy_account. They both do the same thing.
>> HTH
>> Chris
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:242E6C8A-60E1-40CF-B17D-6DD9763F5695@.microsoft.com...
>> >I am a recent user of sql2005 but have worked with sql2000 for a few
>> >years.
>> > I noticed under the sql server agent there is a listing for proxy. I
>> > have
>> > used the sql server agent to set up jobs to run packages developed with
>> > Integration services. I was wondering if anyone had a simple
>> > explination
>> > of
>> > proxys and how/why to set them up.
>> > Thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>

Question on named instance

Hi,
I have sql2000 and 2 instances running, none have the default port. From the
BOL:
"A TCP port is chosen dynamically the first time the MSSQL$instancename
service is started."
"SQL Server 2000 clients do not have to be configured to connect to an
instance of SQL Server 2000. The SQL Server 2000 client components query a
computer running instances of SQL Server 2000 to determine the Net-Libraries
and network addresses for each instance. The client components then
transparently choose a supported Net-Library and address for the connection
without having to be configured on the client. The only information the
application must supply is the computer name and instance name."
Questions:
1, If I restart the MSSQL$instancename service the port is going to change
or fixed there?
2, what is "client components" refers to?
2, I need to connect to these instances from jdbc which only needs server
name and port number which is different from the above statement, how can I
connect?
Thanks
> 1, If I restart the MSSQL$instancename service the port is going to
change
> or fixed there?
I have no idea about this. If I had to guess, I would say that it
doesn't change. Suppose that a client obtains the port number for an
instance, then the instance gets restarted; now the client has an
incorrect port number (if the port has changed) and it has to get all
that information again. But this is just a guess.

> 2, what is "client components" refers to?
MDAC, JDBC drivers, any client libraries.

> 2, I need to connect to these instances from jdbc which only needs
server
> name and port number which is different from the above statement, how
can I
> connect?
You can look into the registry and obtain the port number from there
(not sure exactly where, but you could google for it) or you could use
a JDBC driver that "knows" how to determine ports for named instances,
such as the open source jTDS or the commercial drivers.
Alin.
Disclaimer: I am a jTDS developer.
|||Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:

> change
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
>
> MDAC, JDBC drivers, any client libraries.
> server
> can I
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the open source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>
|||The automatic assignment of ports on named instances that WERE NOT
configured manually do not change. However, upon startup, the first time,
one is acquired and cached. Upon secondary startups, if that same port is
in use by another process, then SQL Server will attempt binding to a new
port number.
What is meant by port assignment on the client end is that SQL Server
exposes UDP 1434, called Dynamic Discovery. Anyone who queries this will
receive a list of instances and port assignments which will automatically
configure clients to connect to those ports.
Sincerely,
Anthony Thomas

"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:932AC1DF-7195-416B-8DD6-6110BFEB4086@.microsoft.com...
Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:

> change
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
>
> MDAC, JDBC drivers, any client libraries.
> server
> can I
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the open source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>

Question on named instance

Hi,
I have sql2000 and 2 instances running, none have the default port. From the
BOL:
"A TCP port is chosen dynamically the first time the MSSQL$instancename
service is started."
"SQL Server 2000 clients do not have to be configured to connect to an
instance of SQL Server 2000. The SQL Server 2000 client components query a
computer running instances of SQL Server 2000 to determine the Net-Libraries
and network addresses for each instance. The client components then
transparently choose a supported Net-Library and address for the connection
without having to be configured on the client. The only information the
application must supply is the computer name and instance name."
Questions:
1, If I restart the MSSQL$instancename service the port is going to change
or fixed there?
2, what is "client components" refers to?
2, I need to connect to these instances from jdbc which only needs server
name and port number which is different from the above statement, how can I
connect?
Thanks> 1, If I restart the MSSQL$instancename service the port is going to
change
> or fixed there?
I have no idea about this. If I had to guess, I would say that it
doesn't change. Suppose that a client obtains the port number for an
instance, then the instance gets restarted; now the client has an
incorrect port number (if the port has changed) and it has to get all
that information again. But this is just a guess.

> 2, what is "client components" refers to?
MDAC, JDBC drivers, any client libraries.

> 2, I need to connect to these instances from jdbc which only needs
server
> name and port number which is different from the above statement, how
can I
> connect?
You can look into the registry and obtain the port number from there
(not sure exactly where, but you could google for it) or you could use
a JDBC driver that "knows" how to determine ports for named instances,
such as the Open Source jTDS or the commercial drivers.
Alin.
Disclaimer: I am a jTDS developer.|||Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:

> change
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
>
> MDAC, JDBC drivers, any client libraries.
>
> server
> can I
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the Open Source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>|||The automatic assignment of ports on named instances that WERE NOT
configured manually do not change. However, upon startup, the first time,
one is acquired and cached. Upon secondary startups, if that same port is
in use by another process, then SQL Server will attempt binding to a new
port number.
What is meant by port assignment on the client end is that SQL Server
exposes UDP 1434, called Dynamic Discovery. Anyone who queries this will
receive a list of instances and port assignments which will automatically
configure clients to connect to those ports.
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:932AC1DF-7195-416B-8DD6-6110BFEB4086@.microsoft.com...
Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:

> change
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
>
> MDAC, JDBC drivers, any client libraries.
>
> server
> can I
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the Open Source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>

Question on named instance

Hi,
I have sql2000 and 2 instances running, none have the default port. From the
BOL:
"A TCP port is chosen dynamically the first time the MSSQL$instancename
service is started."
"SQL Server 2000 clients do not have to be configured to connect to an
instance of SQL Server 2000. The SQL Server 2000 client components query a
computer running instances of SQL Server 2000 to determine the Net-Libraries
and network addresses for each instance. The client components then
transparently choose a supported Net-Library and address for the connection
without having to be configured on the client. The only information the
application must supply is the computer name and instance name."
Questions:
1, If I restart the MSSQL$instancename service the port is going to change
or fixed there?
2, what is "client components" refers to?
2, I need to connect to these instances from jdbc which only needs server
name and port number which is different from the above statement, how can I
connect?
Thanks> 1, If I restart the MSSQL$instancename service the port is going to
change
> or fixed there?
I have no idea about this. If I had to guess, I would say that it
doesn't change. Suppose that a client obtains the port number for an
instance, then the instance gets restarted; now the client has an
incorrect port number (if the port has changed) and it has to get all
that information again. But this is just a guess.
> 2, what is "client components" refers to?
MDAC, JDBC drivers, any client libraries.
> 2, I need to connect to these instances from jdbc which only needs
server
> name and port number which is different from the above statement, how
can I
> connect?
You can look into the registry and obtain the port number from there
(not sure exactly where, but you could google for it) or you could use
a JDBC driver that "knows" how to determine ports for named instances,
such as the open source jTDS or the commercial drivers.
Alin.
Disclaimer: I am a jTDS developer.|||Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:
> > 1, If I restart the MSSQL$instancename service the port is going to
> change
> > or fixed there?
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
> > 2, what is "client components" refers to?
> MDAC, JDBC drivers, any client libraries.
> > 2, I need to connect to these instances from jdbc which only needs
> server
> > name and port number which is different from the above statement, how
> can I
> > connect?
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the open source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>|||The automatic assignment of ports on named instances that WERE NOT
configured manually do not change. However, upon startup, the first time,
one is acquired and cached. Upon secondary startups, if that same port is
in use by another process, then SQL Server will attempt binding to a new
port number.
What is meant by port assignment on the client end is that SQL Server
exposes UDP 1434, called Dynamic Discovery. Anyone who queries this will
receive a list of instances and port assignments which will automatically
configure clients to connect to those ports.
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:932AC1DF-7195-416B-8DD6-6110BFEB4086@.microsoft.com...
Jen
The port the instance uses is fixed. You should find the default instance
will be running on port 1433. The named instance will be assigned by SQL
Server when you created the instance. If you want to see what ports you are
using, from the SQL Server programs group choose Server Network Utility (You
need to do this on the server running SQL Server). On there click on TCP/IP
and then properties, this will show you the port the instance is using.
Regards
John
"Alin Sinpalean" wrote:
> > 1, If I restart the MSSQL$instancename service the port is going to
> change
> > or fixed there?
> I have no idea about this. If I had to guess, I would say that it
> doesn't change. Suppose that a client obtains the port number for an
> instance, then the instance gets restarted; now the client has an
> incorrect port number (if the port has changed) and it has to get all
> that information again. But this is just a guess.
> > 2, what is "client components" refers to?
> MDAC, JDBC drivers, any client libraries.
> > 2, I need to connect to these instances from jdbc which only needs
> server
> > name and port number which is different from the above statement, how
> can I
> > connect?
> You can look into the registry and obtain the port number from there
> (not sure exactly where, but you could google for it) or you could use
> a JDBC driver that "knows" how to determine ports for named instances,
> such as the open source jTDS or the commercial drivers.
> Alin.
> Disclaimer: I am a jTDS developer.
>

Question on Idenity Columns

Is there a way to query any system tables, like sysobjects, for User tables
with Identity columns?
We have a SQL2000 server that we want to replicate. One of my tasks is to
identify all tables with Identity columns that are missing the 'not for
replication' clause.

Thanks,

JoeyDJoeyD (joeydba@.yahoo.com) writes:
> Is there a way to query any system tables, like sysobjects, for User
> tables with Identity columns? We have a SQL2000 server that we want to
> replicate. One of my tasks is to identify all tables with Identity
> columns that are missing the 'not for replication' clause.

SELECT object_name(id), name
FROM syscolumns
WHERE columnproperty(id, name, 'IsIdentity') = 1
AND columnproperty(id, name, 'IsIdNotForRepl') = 1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland - Thank you for your reply.

JoeyD

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns961D3C9067A9Yazorman@.127.0.0.1...
> JoeyD (joeydba@.yahoo.com) writes:
> > Is there a way to query any system tables, like sysobjects, for User
> > tables with Identity columns? We have a SQL2000 server that we want to
> > replicate. One of my tasks is to identify all tables with Identity
> > columns that are missing the 'not for replication' clause.
> SELECT object_name(id), name
> FROM syscolumns
> WHERE columnproperty(id, name, 'IsIdentity') = 1
> AND columnproperty(id, name, 'IsIdNotForRepl') = 1
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Question on Enterprise Mgr & Muyltiple Web SQL2000 DB

I have 5 DB that I want to access from EM on a semishare server with same IP.

Once I add the IP & also the Logon/password then a new one can not be added as a message shows that IP already exists.

Is the only solution to not add the Logon/password and enter them every time?

Any link to other threads that explain would be helpful.I'm a bit confused. Are all 5 databasese on the same server? Once you register that server in EM all of the databases on that server will be accessible. So you should be able to drill down and find your 5 databases.

Terri|||each database has a different Logon and password. I can see all of them and the others on the semi dedicated svr but if I check the box to save ID/pw then I am restricted to only 1 db.