I've just installed sql server 2005 enterprise on my Windows XP Professional
SP2 machine.
I can't find a way to connect with it.
I'm totaly new to SQL Server and have installed it to learn about it.
I can' t even find out if it is running or not.
When I do a
c:>netstat -a
I can't find the port number 1433 in the list.
I've checked the services but can't find the name there
Thanks in advance,
WillyThis may help. It's a new tutorial I wrote for the coming web release of
Books Online.
Getting Started with the Database Engine
Welcome to the Getting Started with the Database Engine tutorial. This
tutorial is intended for users who are new to SQL Server 2005 and who have
installed Developer, Express, or Evaluation Edition on a computer running
Windows XP Professional Service Pack 1 (SP1).This brief tutorial helps you
get started using the Database Engine.
What You Will Learn
This tutorial shows you how to connect to the Database Engine using SQL
Server Management Studio or Management Studio Express, on both the local
computer and from another computer.
This tutorial is divided into two lessons:
Lesson 1: Connecting to the Database Engine
In this lesson, you will learn how to connect to the Database Engine and
enable additional people to connect.
Lesson 2: Connecting from Another Computer
In this lesson, you will learn how to connect to the Database Engine from a
second computer, including enabling protocols, configuring ports, and
configuring firewall settings.
Requirements
This lesson has no knowledge prerequisites.
Your system must have the following installed to use this tutorial:
a.. Internet Explorer 6.0 or later.
b.. Either SQL Server Management Studio or Management Studio Express.
Lesson 1: Connecting to the Database Engine
When you install the SQL Server 2005 Database Engine, the tools that are
installed depend upon the edition and your setup choices. This lesson
reviews the principal tools, and shows you how to connect and perform a
basic function (authorizing more users).
This lesson contains the following topics:
a.. Tools for Getting Started
b.. Connecting with Management Studio
c.. Authorizing Additional Connections
Tools for Getting Started
The SQL Server 2005 Database Engine ships with a variety of tools. This
topic describes the first tools you will need, and helps you select the
right tool for the job. All tools can be accessed from the Start menu.
Generally, tools such as SQL Server Management Studio are not installed by
default. You must select the tools as part of the client components during
setup. For complete description of the tools described below, search for
them in Books Online. SQL Server 2005 Express Edition contains only a subset
of the tools.
Basic Tools
a.. SQL Server Management Studio is the principal tool for administering
the Database Engine and writing Transact-SQL code. It is hosted in the
Visual Studio shell. It is not included in SQL Server 2005 Express Edition
or SQL Server 2005 Mobile Edition.
b.. SQL Server Management Studio Express is a free, limited version of SQL
Server Management Studio, intended for use with SQL Server 2005 Express
Edition and for users who want the basic features of Management Studio in a
lightweight tool.
c.. SQL Server Surface Area Configuration installs with SQL Server and
lets you enable the server protocols, configure services to start
automatically, and enable some optional features of the Database Engine.
d.. SQL Server Configuration Manager installs with both SQL Server and the
client tools. It lets you enable server protocols, configure protocol
options such as TCP ports, configure server services to start automatically,
and configure client computers to connect in your preferred manner.
Sample Database
SQL Server 2005 includes a sample database called AdventureWorks. This
database is not installed by default. If you wish to experiment with the
AdventureWorks database, you must select it using the Setup program. Most
examples described in Books Online use the AdventureWorks database. For more
information, see Running Setup to Install AdventureWorks Sample Databases
and Samples.
To start SQL Server Management Studio
1. On the Start menu, point to All Programs, point to Microsoft SQL
Server 2005, and then click SQL Server Management Studio.
To start SQL Server Surface Area Configuration or SQL Server Configuration
Manager
1. On the Start menu, point to All Programs, point to Microsoft SQL
Server 2005, point to Configuration Tools, and then click either SQL Server
Surface Area Configuration or SQL Server Configuration Manager.
Connecting with Management Studio
It is easy to connect to the Database Engine from tools running on the same
computer if you know the name of the instance, and if you are connecting as
a member of the Administrators group on the computer. The following
procedures must be performed on the same computer that hosts SQL Server.
To determine the name of the Database Engine instance
1. Log into Windows as a member of the Administrators group, and open
either Management Studio or Management Studio Express Edition.
The Connect to Server dialog box opens.
2. Click Cancel.
3. If Registered Servers is not displayed, on the View menu, click
Registered Servers.
4. With Database Engine selected on the Registered Servers toolbar,
right-click Database Engine, and then click Update Local Server
Registration. All instances of the Database Engine installed on the computer
are displayed. This includes instances of SQL Server 2000. Management Studio
can connect and manage instances of SQL Server 2000 as well as SQL Server
2005. The default instance is unnamed and is shown as the computer name. A
named instance displays as the computer name followed by a backward slash
(\) and then the name of the instance. For SQL Server 2005 Express Edition,
the instance is named <computer_name>\sqlexpress unless the name was changed
during setup.
To verify that the Database Engine is running
1. In Registered Servers, if the name of your instance of SQL Server
has a green dot with a white arrow next to the name, the Database Engine is
running and no further action is necessary.
2. If the name of your instance of SQL Server has a red dot with a
white square next to the name, the Database Engine is stopped. Right-click
the name of the Database Engine, and then click Start. After a confirmation
dialog box, the Database Engine should start and the circle should turn
green.
To connect to the Database Engine
1. On the File menu, click Connect Object Explorer.
The Connect to Server dialog box opens. The Server type box displays the
type that was last used.
2. Select Database Engine.
3. In the Server name box, type the name of the Database Engine
instance.
4. Click Connect.
Authorizing Additional Connections
Now that you have connected to SQL Server as an administrator, one of your
first tasks is to authorize other users to connect. You do this by creating
a login and authorizing that login to access a database as a user. Logins
can be either Windows Authentication logins, which use your Windows
credentials, or SQL Server Authentication logins, which store the
authentication information in SQL Server and are independent of your network
credentials. Use Windows Authentication whenever possible.
Create a Windows Authentication login
1. In the previous task, you connected to the Database Engine using
Management Studio. In Object Explorer, expand your server instance, expand
Security, right-click Logins, and then click New Login.
The Login - New dialog box appears.
2. On the General page, in the Login name box, type a Windows login in
the format <domain>\<login>.
3. In the Default database box, select AdventureWorks if available.
Otherwise select master.
4. On the Server Roles page, if the new login is to be an
administrator, click sysadmin, otherwise leave this blank.
5. On the User Mapping page, select Map for the AdventureWorks
database if it is available. Otherwise select master. Note that the User box
is populated with the login. When closed, the dialog box will create this
user in the database.
6. In the Default Schema box, type dbo to map the login to the
database administrator schema.
7. Accept the default settings for the Securables and Status boxes and
click OK to create the login.
Note:
This is basic information to get you started. SQL Server provides a
rich security environment, and security is obviously an important aspect of
database operations. For more information about security, read the security
sections of Books Online, starting with Security Considerations for
Databases and Database Applications.
Lesson 2: Connecting from Another Computer
To enhance security, the Database Engine of SQL Server 2005 Developer,
Express, and Evaluation Editions cannot be accessed from another computer
when initially installed. This lesson shows you how to enable the protocols,
configure the ports, and configure the Windows Firewall for connecting from
other computers.
This lesson contains the following topics:
a.. Enabling Protocols
b.. Configuring a Fixed Port
c.. Opening Ports in the Firewall
d.. Connecting to the Database Engine from Another Computer
e.. Connecting Using the SQL Server Browser Service
Enabling Protocols
To enhance security, SQL Server 2005 Express Edition Developer Edition and
Evaluation Edition install with only limited network connectivity.
Connections to the Database Engine can be made from tools running the same
computer, but not from other computers. If you are planning to do your
development work on the same computer as the Database Engine, you're done.
Management Studio or Management Studio Express Edition will connect to the
Database Engine using the shared memory protocol, which is already enabled.
If you plan to connect to the Database Engine from another computer, you
must enable a protocol, such as TCP/IP.
How to enable TCP/IP connections from another computer
1. Start SQL Server Surface Area Configuration, and click Surface Area
Configuration for Services.
2. In the Surface Area Configuration for Services and Connections box,
the View by Instance box lists the instances of the Database Engine
installed on the computer. The default instance (an unnamed instance) is
listed as MSSQLSERVER. If you installed a named instance, the name you
provided is listed. SQL Server 2005 Express Edition installs as SQLEXPRESS
unless you changed the name during setup. In the View by Instance box,
expand the instance you wish to configure, expand Database Engine, and then
click Remote Connections.
3. Click Local and remote connections, click Using TCP/IP only, then
click OK and close the tool.
Configuring a Fixed Port
To enhance security, Windows XP Professional Service Pack 2 (SP2) turns on
the Windows Firewall. When SQL Server 2005 Developer, Express, or Evaluation
Edition is running on Windows XP Professional, and you wish to connect to it
from another computer, you must open a communication port in the firewall.
The default instance of the Database Engine listens on port 1433, so you do
not need to configure a fixed port, but named instances including SQL Server
2005 Express Edition listen on dynamic ports. Before you can open a port in
the firewall, you must first configure the Database Engine to listen on a
specific port; otherwise the Database Engine may listen on a different port
each time it is started.
Configure SQL Server to listen on a specific port
1. In SQL Server Configuration Manager, expand SQL Server 2005 Network
Configuration, and then click on the server instance you wish to configure.
2. In the right pane, double-click TCP/IP.
3. In the TCP/IP Properties dialog box, click the IP Addresses tab.
4. In the TCP Port box of the IPAll section, type an available port
number. For this tutorial, we will use 1500.
5. Click OK to close the dialog box, and click OK to the warning that
the service must be restarted.
6. In the left pane, click SQL Server 2005 Services.
7. In the right pane, right-click the instance of SQL Server, and then
click Restart. When the Database Engine restarts, it will listen on port
1500.
Opening Ports in the Firewall
Firewall systems prevent unauthorized access to computer resources. To
enhance security, Windows XP Professional Service Pack 2 (SP2) turns on
Windows Firewall. To connect to SQL Server from another computer, you must
open a port in the firewall.
Important:
Opening ports in your firewall can leave your server exposed to
malicious attacks. Be sure to understand firewall systems before opening
ports. For more information, see Security Considerations for a SQL Server
Installation.
After configuring the Database Engine to use a fixed port, follow the
following instructions to open that port in your Windows Firewall. (You do
not need to configure a fixed port for the default instance, because it is
already fixed on port 1433.)
To open a port in Windows Firewall
1. On the Start menu, click Control Panel.
2. In Control Panel, click Network and Internet Connections, and then
open Windows Firewall.
3. In Windows Firewall, click the Exceptions tab, and then click Add
Port.
4. In the Add a Port dialog box, in the Name box, type SQL Server
<instanceName>.
5. In the Port number box, type the port number of the Database Engine
instance. Use 1433 for the default instance. Type 1500 if you are
configuring a named instance and configured a fixed port in the previous
task. Verify that TCP is selected, and then click OK.
Connecting to the Database Engine from Another Computer
Now that you have configured the Database Engine to listen on a fixed port,
and have opened that port in the firewall, you can connect to SQL Server
from another computer.
When the SQL Server Browser service is running on the server computer, and
when the firewall has opened UDP port 1434, the connection can be made using
the computer name and instance name. To enhance security, our example does
not use the SQL Server Browser service.
To connect to the Database Engine from another computer
1. On a second computer containing the SQL Server 2005 client tools,
log in with an account authorized to connect to SQL Server, and open
Management Studio.
2. In the Connect to Server dialog box, confirm Database Engine in the
Server type box.
3. In the Server name box, type tcp: to specify the protocol, followed
by the computer name, a comma, and the port number. To connect to the
default instance, the port 1433 is implied and can be omitted, so type
tcp:<computer_name>. In our example for a named instance, type
tcp:<computer_name>,1500.
4. In the Authentication box, confirm Window Authentication, and then
click Connect.
Connecting Using the SQL Server Browser Service
The SQL Server Browser service listens for incoming requests for SQL Server
resources and provides information about SQL Server instances installed on
the computer. When the SQL Server Browser service is running, users can
connect to named instances by providing the computer name and instance name,
instead of the computer name and port number. Because SQL Server Browser
receives unauthenticated UDP requests, it is not always turned on during
setup. For a description of the service and an explanation of when it is
turned on, see SQL Server Browser Service.
To use the SQL Server Browser, you must follow the same steps as the
previous task in this lesson, and open UDP port 1434.
This concludes this brief tutorial on basic connectivity. For more
information about configuring server and client connectivity, see Database
Engine Connectivity How-to Topics.
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Willy Verbiest" <willy.verbiest@.telenetnospam.be> wrote in message
news:Oo4ff.51028$Uj3.2801801@.phobos.telenet-ops.be...
> I've just installed sql server 2005 enterprise on my Windows XP
> Professional SP2 machine.
> I can't find a way to connect with it.
> I'm totaly new to SQL Server and have installed it to learn about it.
> I can' t even find out if it is running or not.
> When I do a
> c:>netstat -a
> I can't find the port number 1433 in the list.
> I've checked the services but can't find the name there
> Thanks in advance,
> Willy
>
Showing posts with label version. Show all posts
Showing posts with label version. Show all posts
Wednesday, March 28, 2012
How to Connect to SQL Server 2005 enterprise version
I've just installed sql server 2005 enterprise on my Windows XP Professional
SP2 machine.
I can't find a way to connect with it.
I'm totaly new to SQL Server and have installed it to learn about it.
I can' t even find out if it is running or not.
When I do a
c:>netstat -a
I can't find the port number 1433 in the list.
I've checked the services but can't find the name there
Thanks in advance,
Willy
This may help. It's a new tutorial I wrote for the coming web release of
Books Online.
Getting Started with the Database Engine
Welcome to the Getting Started with the Database Engine tutorial. This
tutorial is intended for users who are new to SQL Server 2005 and who have
installed Developer, Express, or Evaluation Edition on a computer running
Windows XP Professional Service Pack 1 (SP1).This brief tutorial helps you
get started using the Database Engine.
What You Will Learn
This tutorial shows you how to connect to the Database Engine using SQL
Server Management Studio or Management Studio Express, on both the local
computer and from another computer.
This tutorial is divided into two lessons:
Lesson 1: Connecting to the Database Engine
In this lesson, you will learn how to connect to the Database Engine and
enable additional people to connect.
Lesson 2: Connecting from Another Computer
In this lesson, you will learn how to connect to the Database Engine from a
second computer, including enabling protocols, configuring ports, and
configuring firewall settings.
Requirements
This lesson has no knowledge prerequisites.
Your system must have the following installed to use this tutorial:
a.. Internet Explorer 6.0 or later.
b.. Either SQL Server Management Studio or Management Studio Express.
Lesson 1: Connecting to the Database Engine
When you install the SQL Server 2005 Database Engine, the tools that are
installed depend upon the edition and your setup choices. This lesson
reviews the principal tools, and shows you how to connect and perform a
basic function (authorizing more users).
This lesson contains the following topics:
a.. Tools for Getting Started
b.. Connecting with Management Studio
c.. Authorizing Additional Connections
Tools for Getting Started
The SQL Server 2005 Database Engine ships with a variety of tools. This
topic describes the first tools you will need, and helps you select the
right tool for the job. All tools can be accessed from the Start menu.
Generally, tools such as SQL Server Management Studio are not installed by
default. You must select the tools as part of the client components during
setup. For complete description of the tools described below, search for
them in Books Online. SQL Server 2005 Express Edition contains only a subset
of the tools.
Basic Tools
a.. SQL Server Management Studio is the principal tool for administering
the Database Engine and writing Transact-SQL code. It is hosted in the
Visual Studio shell. It is not included in SQL Server 2005 Express Edition
or SQL Server 2005 Mobile Edition.
b.. SQL Server Management Studio Express is a free, limited version of SQL
Server Management Studio, intended for use with SQL Server 2005 Express
Edition and for users who want the basic features of Management Studio in a
lightweight tool.
c.. SQL Server Surface Area Configuration installs with SQL Server and
lets you enable the server protocols, configure services to start
automatically, and enable some optional features of the Database Engine.
d.. SQL Server Configuration Manager installs with both SQL Server and the
client tools. It lets you enable server protocols, configure protocol
options such as TCP ports, configure server services to start automatically,
and configure client computers to connect in your preferred manner.
Sample Database
SQL Server 2005 includes a sample database called AdventureWorks. This
database is not installed by default. If you wish to experiment with the
AdventureWorks database, you must select it using the Setup program. Most
examples described in Books Online use the AdventureWorks database. For more
information, see Running Setup to Install AdventureWorks Sample Databases
and Samples.
To start SQL Server Management Studio
1. On the Start menu, point to All Programs, point to Microsoft SQL
Server 2005, and then click SQL Server Management Studio.
To start SQL Server Surface Area Configuration or SQL Server Configuration
Manager
1. On the Start menu, point to All Programs, point to Microsoft SQL
Server 2005, point to Configuration Tools, and then click either SQL Server
Surface Area Configuration or SQL Server Configuration Manager.
Connecting with Management Studio
It is easy to connect to the Database Engine from tools running on the same
computer if you know the name of the instance, and if you are connecting as
a member of the Administrators group on the computer. The following
procedures must be performed on the same computer that hosts SQL Server.
To determine the name of the Database Engine instance
1. Log into Windows as a member of the Administrators group, and open
either Management Studio or Management Studio Express Edition.
The Connect to Server dialog box opens.
2. Click Cancel.
3. If Registered Servers is not displayed, on the View menu, click
Registered Servers.
4. With Database Engine selected on the Registered Servers toolbar,
right-click Database Engine, and then click Update Local Server
Registration. All instances of the Database Engine installed on the computer
are displayed. This includes instances of SQL Server 2000. Management Studio
can connect and manage instances of SQL Server 2000 as well as SQL Server
2005. The default instance is unnamed and is shown as the computer name. A
named instance displays as the computer name followed by a backward slash
(\) and then the name of the instance. For SQL Server 2005 Express Edition,
the instance is named <computer_name>\sqlexpress unless the name was changed
during setup.
To verify that the Database Engine is running
1. In Registered Servers, if the name of your instance of SQL Server
has a green dot with a white arrow next to the name, the Database Engine is
running and no further action is necessary.
2. If the name of your instance of SQL Server has a red dot with a
white square next to the name, the Database Engine is stopped. Right-click
the name of the Database Engine, and then click Start. After a confirmation
dialog box, the Database Engine should start and the circle should turn
green.
To connect to the Database Engine
1. On the File menu, click Connect Object Explorer.
The Connect to Server dialog box opens. The Server type box displays the
type that was last used.
2. Select Database Engine.
3. In the Server name box, type the name of the Database Engine
instance.
4. Click Connect.
Authorizing Additional Connections
Now that you have connected to SQL Server as an administrator, one of your
first tasks is to authorize other users to connect. You do this by creating
a login and authorizing that login to access a database as a user. Logins
can be either Windows Authentication logins, which use your Windows
credentials, or SQL Server Authentication logins, which store the
authentication information in SQL Server and are independent of your network
credentials. Use Windows Authentication whenever possible.
Create a Windows Authentication login
1. In the previous task, you connected to the Database Engine using
Management Studio. In Object Explorer, expand your server instance, expand
Security, right-click Logins, and then click New Login.
The Login - New dialog box appears.
2. On the General page, in the Login name box, type a Windows login in
the format <domain>\<login>.
3. In the Default database box, select AdventureWorks if available.
Otherwise select master.
4. On the Server Roles page, if the new login is to be an
administrator, click sysadmin, otherwise leave this blank.
5. On the User Mapping page, select Map for the AdventureWorks
database if it is available. Otherwise select master. Note that the User box
is populated with the login. When closed, the dialog box will create this
user in the database.
6. In the Default Schema box, type dbo to map the login to the
database administrator schema.
7. Accept the default settings for the Securables and Status boxes and
click OK to create the login.
Note:
This is basic information to get you started. SQL Server provides a
rich security environment, and security is obviously an important aspect of
database operations. For more information about security, read the security
sections of Books Online, starting with Security Considerations for
Databases and Database Applications.
Lesson 2: Connecting from Another Computer
To enhance security, the Database Engine of SQL Server 2005 Developer,
Express, and Evaluation Editions cannot be accessed from another computer
when initially installed. This lesson shows you how to enable the protocols,
configure the ports, and configure the Windows Firewall for connecting from
other computers.
This lesson contains the following topics:
a.. Enabling Protocols
b.. Configuring a Fixed Port
c.. Opening Ports in the Firewall
d.. Connecting to the Database Engine from Another Computer
e.. Connecting Using the SQL Server Browser Service
Enabling Protocols
To enhance security, SQL Server 2005 Express Edition Developer Edition and
Evaluation Edition install with only limited network connectivity.
Connections to the Database Engine can be made from tools running the same
computer, but not from other computers. If you are planning to do your
development work on the same computer as the Database Engine, you're done.
Management Studio or Management Studio Express Edition will connect to the
Database Engine using the shared memory protocol, which is already enabled.
If you plan to connect to the Database Engine from another computer, you
must enable a protocol, such as TCP/IP.
How to enable TCP/IP connections from another computer
1. Start SQL Server Surface Area Configuration, and click Surface Area
Configuration for Services.
2. In the Surface Area Configuration for Services and Connections box,
the View by Instance box lists the instances of the Database Engine
installed on the computer. The default instance (an unnamed instance) is
listed as MSSQLSERVER. If you installed a named instance, the name you
provided is listed. SQL Server 2005 Express Edition installs as SQLEXPRESS
unless you changed the name during setup. In the View by Instance box,
expand the instance you wish to configure, expand Database Engine, and then
click Remote Connections.
3. Click Local and remote connections, click Using TCP/IP only, then
click OK and close the tool.
Configuring a Fixed Port
To enhance security, Windows XP Professional Service Pack 2 (SP2) turns on
the Windows Firewall. When SQL Server 2005 Developer, Express, or Evaluation
Edition is running on Windows XP Professional, and you wish to connect to it
from another computer, you must open a communication port in the firewall.
The default instance of the Database Engine listens on port 1433, so you do
not need to configure a fixed port, but named instances including SQL Server
2005 Express Edition listen on dynamic ports. Before you can open a port in
the firewall, you must first configure the Database Engine to listen on a
specific port; otherwise the Database Engine may listen on a different port
each time it is started.
Configure SQL Server to listen on a specific port
1. In SQL Server Configuration Manager, expand SQL Server 2005 Network
Configuration, and then click on the server instance you wish to configure.
2. In the right pane, double-click TCP/IP.
3. In the TCP/IP Properties dialog box, click the IP Addresses tab.
4. In the TCP Port box of the IPAll section, type an available port
number. For this tutorial, we will use 1500.
5. Click OK to close the dialog box, and click OK to the warning that
the service must be restarted.
6. In the left pane, click SQL Server 2005 Services.
7. In the right pane, right-click the instance of SQL Server, and then
click Restart. When the Database Engine restarts, it will listen on port
1500.
Opening Ports in the Firewall
Firewall systems prevent unauthorized access to computer resources. To
enhance security, Windows XP Professional Service Pack 2 (SP2) turns on
Windows Firewall. To connect to SQL Server from another computer, you must
open a port in the firewall.
Important:
Opening ports in your firewall can leave your server exposed to
malicious attacks. Be sure to understand firewall systems before opening
ports. For more information, see Security Considerations for a SQL Server
Installation.
After configuring the Database Engine to use a fixed port, follow the
following instructions to open that port in your Windows Firewall. (You do
not need to configure a fixed port for the default instance, because it is
already fixed on port 1433.)
To open a port in Windows Firewall
1. On the Start menu, click Control Panel.
2. In Control Panel, click Network and Internet Connections, and then
open Windows Firewall.
3. In Windows Firewall, click the Exceptions tab, and then click Add
Port.
4. In the Add a Port dialog box, in the Name box, type SQL Server
<instanceName>.
5. In the Port number box, type the port number of the Database Engine
instance. Use 1433 for the default instance. Type 1500 if you are
configuring a named instance and configured a fixed port in the previous
task. Verify that TCP is selected, and then click OK.
Connecting to the Database Engine from Another Computer
Now that you have configured the Database Engine to listen on a fixed port,
and have opened that port in the firewall, you can connect to SQL Server
from another computer.
When the SQL Server Browser service is running on the server computer, and
when the firewall has opened UDP port 1434, the connection can be made using
the computer name and instance name. To enhance security, our example does
not use the SQL Server Browser service.
To connect to the Database Engine from another computer
1. On a second computer containing the SQL Server 2005 client tools,
log in with an account authorized to connect to SQL Server, and open
Management Studio.
2. In the Connect to Server dialog box, confirm Database Engine in the
Server type box.
3. In the Server name box, type tcp: to specify the protocol, followed
by the computer name, a comma, and the port number. To connect to the
default instance, the port 1433 is implied and can be omitted, so type
tcp:<computer_name>. In our example for a named instance, type
tcp:<computer_name>,1500.
4. In the Authentication box, confirm Window Authentication, and then
click Connect.
Connecting Using the SQL Server Browser Service
The SQL Server Browser service listens for incoming requests for SQL Server
resources and provides information about SQL Server instances installed on
the computer. When the SQL Server Browser service is running, users can
connect to named instances by providing the computer name and instance name,
instead of the computer name and port number. Because SQL Server Browser
receives unauthenticated UDP requests, it is not always turned on during
setup. For a description of the service and an explanation of when it is
turned on, see SQL Server Browser Service.
To use the SQL Server Browser, you must follow the same steps as the
previous task in this lesson, and open UDP port 1434.
This concludes this brief tutorial on basic connectivity. For more
information about configuring server and client connectivity, see Database
Engine Connectivity How-to Topics.
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Willy Verbiest" <willy.verbiest@.telenetnospam.be> wrote in message
news:Oo4ff.51028$Uj3.2801801@.phobos.telenet-ops.be...
> I've just installed sql server 2005 enterprise on my Windows XP
> Professional SP2 machine.
> I can't find a way to connect with it.
> I'm totaly new to SQL Server and have installed it to learn about it.
> I can' t even find out if it is running or not.
> When I do a
> c:>netstat -a
> I can't find the port number 1433 in the list.
> I've checked the services but can't find the name there
> Thanks in advance,
> Willy
>
SP2 machine.
I can't find a way to connect with it.
I'm totaly new to SQL Server and have installed it to learn about it.
I can' t even find out if it is running or not.
When I do a
c:>netstat -a
I can't find the port number 1433 in the list.
I've checked the services but can't find the name there
Thanks in advance,
Willy
This may help. It's a new tutorial I wrote for the coming web release of
Books Online.
Getting Started with the Database Engine
Welcome to the Getting Started with the Database Engine tutorial. This
tutorial is intended for users who are new to SQL Server 2005 and who have
installed Developer, Express, or Evaluation Edition on a computer running
Windows XP Professional Service Pack 1 (SP1).This brief tutorial helps you
get started using the Database Engine.
What You Will Learn
This tutorial shows you how to connect to the Database Engine using SQL
Server Management Studio or Management Studio Express, on both the local
computer and from another computer.
This tutorial is divided into two lessons:
Lesson 1: Connecting to the Database Engine
In this lesson, you will learn how to connect to the Database Engine and
enable additional people to connect.
Lesson 2: Connecting from Another Computer
In this lesson, you will learn how to connect to the Database Engine from a
second computer, including enabling protocols, configuring ports, and
configuring firewall settings.
Requirements
This lesson has no knowledge prerequisites.
Your system must have the following installed to use this tutorial:
a.. Internet Explorer 6.0 or later.
b.. Either SQL Server Management Studio or Management Studio Express.
Lesson 1: Connecting to the Database Engine
When you install the SQL Server 2005 Database Engine, the tools that are
installed depend upon the edition and your setup choices. This lesson
reviews the principal tools, and shows you how to connect and perform a
basic function (authorizing more users).
This lesson contains the following topics:
a.. Tools for Getting Started
b.. Connecting with Management Studio
c.. Authorizing Additional Connections
Tools for Getting Started
The SQL Server 2005 Database Engine ships with a variety of tools. This
topic describes the first tools you will need, and helps you select the
right tool for the job. All tools can be accessed from the Start menu.
Generally, tools such as SQL Server Management Studio are not installed by
default. You must select the tools as part of the client components during
setup. For complete description of the tools described below, search for
them in Books Online. SQL Server 2005 Express Edition contains only a subset
of the tools.
Basic Tools
a.. SQL Server Management Studio is the principal tool for administering
the Database Engine and writing Transact-SQL code. It is hosted in the
Visual Studio shell. It is not included in SQL Server 2005 Express Edition
or SQL Server 2005 Mobile Edition.
b.. SQL Server Management Studio Express is a free, limited version of SQL
Server Management Studio, intended for use with SQL Server 2005 Express
Edition and for users who want the basic features of Management Studio in a
lightweight tool.
c.. SQL Server Surface Area Configuration installs with SQL Server and
lets you enable the server protocols, configure services to start
automatically, and enable some optional features of the Database Engine.
d.. SQL Server Configuration Manager installs with both SQL Server and the
client tools. It lets you enable server protocols, configure protocol
options such as TCP ports, configure server services to start automatically,
and configure client computers to connect in your preferred manner.
Sample Database
SQL Server 2005 includes a sample database called AdventureWorks. This
database is not installed by default. If you wish to experiment with the
AdventureWorks database, you must select it using the Setup program. Most
examples described in Books Online use the AdventureWorks database. For more
information, see Running Setup to Install AdventureWorks Sample Databases
and Samples.
To start SQL Server Management Studio
1. On the Start menu, point to All Programs, point to Microsoft SQL
Server 2005, and then click SQL Server Management Studio.
To start SQL Server Surface Area Configuration or SQL Server Configuration
Manager
1. On the Start menu, point to All Programs, point to Microsoft SQL
Server 2005, point to Configuration Tools, and then click either SQL Server
Surface Area Configuration or SQL Server Configuration Manager.
Connecting with Management Studio
It is easy to connect to the Database Engine from tools running on the same
computer if you know the name of the instance, and if you are connecting as
a member of the Administrators group on the computer. The following
procedures must be performed on the same computer that hosts SQL Server.
To determine the name of the Database Engine instance
1. Log into Windows as a member of the Administrators group, and open
either Management Studio or Management Studio Express Edition.
The Connect to Server dialog box opens.
2. Click Cancel.
3. If Registered Servers is not displayed, on the View menu, click
Registered Servers.
4. With Database Engine selected on the Registered Servers toolbar,
right-click Database Engine, and then click Update Local Server
Registration. All instances of the Database Engine installed on the computer
are displayed. This includes instances of SQL Server 2000. Management Studio
can connect and manage instances of SQL Server 2000 as well as SQL Server
2005. The default instance is unnamed and is shown as the computer name. A
named instance displays as the computer name followed by a backward slash
(\) and then the name of the instance. For SQL Server 2005 Express Edition,
the instance is named <computer_name>\sqlexpress unless the name was changed
during setup.
To verify that the Database Engine is running
1. In Registered Servers, if the name of your instance of SQL Server
has a green dot with a white arrow next to the name, the Database Engine is
running and no further action is necessary.
2. If the name of your instance of SQL Server has a red dot with a
white square next to the name, the Database Engine is stopped. Right-click
the name of the Database Engine, and then click Start. After a confirmation
dialog box, the Database Engine should start and the circle should turn
green.
To connect to the Database Engine
1. On the File menu, click Connect Object Explorer.
The Connect to Server dialog box opens. The Server type box displays the
type that was last used.
2. Select Database Engine.
3. In the Server name box, type the name of the Database Engine
instance.
4. Click Connect.
Authorizing Additional Connections
Now that you have connected to SQL Server as an administrator, one of your
first tasks is to authorize other users to connect. You do this by creating
a login and authorizing that login to access a database as a user. Logins
can be either Windows Authentication logins, which use your Windows
credentials, or SQL Server Authentication logins, which store the
authentication information in SQL Server and are independent of your network
credentials. Use Windows Authentication whenever possible.
Create a Windows Authentication login
1. In the previous task, you connected to the Database Engine using
Management Studio. In Object Explorer, expand your server instance, expand
Security, right-click Logins, and then click New Login.
The Login - New dialog box appears.
2. On the General page, in the Login name box, type a Windows login in
the format <domain>\<login>.
3. In the Default database box, select AdventureWorks if available.
Otherwise select master.
4. On the Server Roles page, if the new login is to be an
administrator, click sysadmin, otherwise leave this blank.
5. On the User Mapping page, select Map for the AdventureWorks
database if it is available. Otherwise select master. Note that the User box
is populated with the login. When closed, the dialog box will create this
user in the database.
6. In the Default Schema box, type dbo to map the login to the
database administrator schema.
7. Accept the default settings for the Securables and Status boxes and
click OK to create the login.
Note:
This is basic information to get you started. SQL Server provides a
rich security environment, and security is obviously an important aspect of
database operations. For more information about security, read the security
sections of Books Online, starting with Security Considerations for
Databases and Database Applications.
Lesson 2: Connecting from Another Computer
To enhance security, the Database Engine of SQL Server 2005 Developer,
Express, and Evaluation Editions cannot be accessed from another computer
when initially installed. This lesson shows you how to enable the protocols,
configure the ports, and configure the Windows Firewall for connecting from
other computers.
This lesson contains the following topics:
a.. Enabling Protocols
b.. Configuring a Fixed Port
c.. Opening Ports in the Firewall
d.. Connecting to the Database Engine from Another Computer
e.. Connecting Using the SQL Server Browser Service
Enabling Protocols
To enhance security, SQL Server 2005 Express Edition Developer Edition and
Evaluation Edition install with only limited network connectivity.
Connections to the Database Engine can be made from tools running the same
computer, but not from other computers. If you are planning to do your
development work on the same computer as the Database Engine, you're done.
Management Studio or Management Studio Express Edition will connect to the
Database Engine using the shared memory protocol, which is already enabled.
If you plan to connect to the Database Engine from another computer, you
must enable a protocol, such as TCP/IP.
How to enable TCP/IP connections from another computer
1. Start SQL Server Surface Area Configuration, and click Surface Area
Configuration for Services.
2. In the Surface Area Configuration for Services and Connections box,
the View by Instance box lists the instances of the Database Engine
installed on the computer. The default instance (an unnamed instance) is
listed as MSSQLSERVER. If you installed a named instance, the name you
provided is listed. SQL Server 2005 Express Edition installs as SQLEXPRESS
unless you changed the name during setup. In the View by Instance box,
expand the instance you wish to configure, expand Database Engine, and then
click Remote Connections.
3. Click Local and remote connections, click Using TCP/IP only, then
click OK and close the tool.
Configuring a Fixed Port
To enhance security, Windows XP Professional Service Pack 2 (SP2) turns on
the Windows Firewall. When SQL Server 2005 Developer, Express, or Evaluation
Edition is running on Windows XP Professional, and you wish to connect to it
from another computer, you must open a communication port in the firewall.
The default instance of the Database Engine listens on port 1433, so you do
not need to configure a fixed port, but named instances including SQL Server
2005 Express Edition listen on dynamic ports. Before you can open a port in
the firewall, you must first configure the Database Engine to listen on a
specific port; otherwise the Database Engine may listen on a different port
each time it is started.
Configure SQL Server to listen on a specific port
1. In SQL Server Configuration Manager, expand SQL Server 2005 Network
Configuration, and then click on the server instance you wish to configure.
2. In the right pane, double-click TCP/IP.
3. In the TCP/IP Properties dialog box, click the IP Addresses tab.
4. In the TCP Port box of the IPAll section, type an available port
number. For this tutorial, we will use 1500.
5. Click OK to close the dialog box, and click OK to the warning that
the service must be restarted.
6. In the left pane, click SQL Server 2005 Services.
7. In the right pane, right-click the instance of SQL Server, and then
click Restart. When the Database Engine restarts, it will listen on port
1500.
Opening Ports in the Firewall
Firewall systems prevent unauthorized access to computer resources. To
enhance security, Windows XP Professional Service Pack 2 (SP2) turns on
Windows Firewall. To connect to SQL Server from another computer, you must
open a port in the firewall.
Important:
Opening ports in your firewall can leave your server exposed to
malicious attacks. Be sure to understand firewall systems before opening
ports. For more information, see Security Considerations for a SQL Server
Installation.
After configuring the Database Engine to use a fixed port, follow the
following instructions to open that port in your Windows Firewall. (You do
not need to configure a fixed port for the default instance, because it is
already fixed on port 1433.)
To open a port in Windows Firewall
1. On the Start menu, click Control Panel.
2. In Control Panel, click Network and Internet Connections, and then
open Windows Firewall.
3. In Windows Firewall, click the Exceptions tab, and then click Add
Port.
4. In the Add a Port dialog box, in the Name box, type SQL Server
<instanceName>.
5. In the Port number box, type the port number of the Database Engine
instance. Use 1433 for the default instance. Type 1500 if you are
configuring a named instance and configured a fixed port in the previous
task. Verify that TCP is selected, and then click OK.
Connecting to the Database Engine from Another Computer
Now that you have configured the Database Engine to listen on a fixed port,
and have opened that port in the firewall, you can connect to SQL Server
from another computer.
When the SQL Server Browser service is running on the server computer, and
when the firewall has opened UDP port 1434, the connection can be made using
the computer name and instance name. To enhance security, our example does
not use the SQL Server Browser service.
To connect to the Database Engine from another computer
1. On a second computer containing the SQL Server 2005 client tools,
log in with an account authorized to connect to SQL Server, and open
Management Studio.
2. In the Connect to Server dialog box, confirm Database Engine in the
Server type box.
3. In the Server name box, type tcp: to specify the protocol, followed
by the computer name, a comma, and the port number. To connect to the
default instance, the port 1433 is implied and can be omitted, so type
tcp:<computer_name>. In our example for a named instance, type
tcp:<computer_name>,1500.
4. In the Authentication box, confirm Window Authentication, and then
click Connect.
Connecting Using the SQL Server Browser Service
The SQL Server Browser service listens for incoming requests for SQL Server
resources and provides information about SQL Server instances installed on
the computer. When the SQL Server Browser service is running, users can
connect to named instances by providing the computer name and instance name,
instead of the computer name and port number. Because SQL Server Browser
receives unauthenticated UDP requests, it is not always turned on during
setup. For a description of the service and an explanation of when it is
turned on, see SQL Server Browser Service.
To use the SQL Server Browser, you must follow the same steps as the
previous task in this lesson, and open UDP port 1434.
This concludes this brief tutorial on basic connectivity. For more
information about configuring server and client connectivity, see Database
Engine Connectivity How-to Topics.
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Willy Verbiest" <willy.verbiest@.telenetnospam.be> wrote in message
news:Oo4ff.51028$Uj3.2801801@.phobos.telenet-ops.be...
> I've just installed sql server 2005 enterprise on my Windows XP
> Professional SP2 machine.
> I can't find a way to connect with it.
> I'm totaly new to SQL Server and have installed it to learn about it.
> I can' t even find out if it is running or not.
> When I do a
> c:>netstat -a
> I can't find the port number 1433 in the list.
> I've checked the services but can't find the name there
> Thanks in advance,
> Willy
>
Monday, March 26, 2012
How to connect to Analysis Services with Excel 2007 Data Mining Add In?
I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?
|||
|||
|||
|||
the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.
In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)
|||The localhost server name was rejected.|||
The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.
Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx
|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.
|||
Can you check to see if the server's started?
Go to the start menu, right-click on "My Computer" and select "Manage"
Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.
|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.|||
Yes,
However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)
|||I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?
After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?
Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.
|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."
|||
There are a few possible reasons:
- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")
- is AS running as the default instance or as a named instance?
In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"
A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"
If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"
|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?|||
You can download freely an evaluation version from
http://www.microsoft.com/sql/downloads/trial-software.mspx
sqlHow to connect to Analysis Services with Excel 2007 Data Mining Add In?
I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?
the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.
In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)
|||The localhost server name was rejected.|||The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.
Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx
|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.|||
Can you check to see if the server's started?
Go to the start menu, right-click on "My Computer" and select "Manage"
Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.
|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.|||Yes,
However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)
|||I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?
After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?
Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.
|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."|||
There are a few possible reasons:
- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")
- is AS running as the default instance or as a named instance?
In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"
A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"
If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"
|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?|||
You can download freely an evaluation version from
http://www.microsoft.com/sql/downloads/trial-software.mspx
How to connect to Analysis Services with Excel 2007 Data Mining Add In?
I downloaded a trial version of office 2007 and the data mining addin. I do not have an automatic connection to Analysis Services, so most functionality does not work. Is it possible to configure a connection with a trial version? If so, does anyone know what the server name is and how to configure it?
the server name is typically the name of the computer that is running Analysis Services 2005 (the machine where SQL Server 2005 is installed). If this is installed on the same computer as Office 2007, then you can use "localhost" instead of the computer name.
In case you need a trial version of Analysis Services 2005, you can download it from here http://www.microsoft.com/sql/downloads/trial-software.mspx (download the SQL Setup, run it and make sure to include Analysis Services in the setup selection)
|||The localhost server name was rejected.|||The localhost server name was rejected after you installed the trial version of Analysis Services? You may also connect to any server in your organization.
Instructions on how to get the evaluation edition and upgrade are here: http://blogs.msdn.com/jamiemac/archive/2006/12/29/try-out-the-data-mining-addins-for-office-2007-free-for-60-days.aspx
|||Yes, it was after I installed the trial of analysis services sql 2005. I followed the directions of the link you provided without any success.|||
Can you check to see if the server's started?
Go to the start menu, right-click on "My Computer" and select "Manage"
Open up "Services and Application" and select "Services" and look for Analysis Services (likely called "SQL Server Analysis Services") to check if it's started.
|||Is it even possible with a standalone home laptop? That's what I'm trying to do this on.|||Yes,
However, you need either to install SQL Server 2005 (Analysis Services) on the laptop ( or on anothe machine in your home network)
|||I have all of the software installed on my laptop and it all works OK - by "home laptop" you mean a laptop that is not joined to a domain, correct?
After installing Analysis Services on your local machine, did you try to run the server configuration tool? If that failed, can you try to run SQL Server Management Studio and connect?
Please let us know. If you did install and it seems everything is running (i.e. the server is started), or the server will not start, you may have to call customer support.
|||In Excel, when you try to create a new connection to Analysis Service, you might want to share what is the error message when you click on Test connection (assuming you type localhost for the Server name).|||The error message when trying to test a connection to "localhost" is:"Connect to Analysis Services:
Test Connection Failed. A connection cannot be made. Ensure that the server is running. No connection could be made because the target machine actively refused it."|||
There are a few possible reasons:
- is SQL Server Analysis Services 2005 running on the local machine? (run "services.msc" from a command prompt and look for a services named "SQL Server Analysis Services")
- is AS running as the default instance or as a named instance?
In services.msc, a default instance would appear as "SQL Server Analysis Services(MSSQLSERVER)"
A named instance would appear as "SQL Server Analysis Services(INSTANCENAME)"
If running as a named instance, then please set the connection for the Excel add-ins to "localhost\INSTANCENAME"
|||It looks like I have SQL Server Express running, but that I may not have SQL Server Analysis Services...how do I get this cheaply?|||
You can download freely an evaluation version from
http://www.microsoft.com/sql/downloads/trial-software.mspx
Friday, March 23, 2012
How to connect SQL Server 2005 in WAS 5.1
Hi All,
One of our database server has upgraded from SQL Server 2000 to 2005 version some days ago, but I found the application which could run before cannot work fine now. I have followed some intructions got from other guys, such as replace the old driver with a new version, but it still did not work. Now I have two questions
1. What is the driver implementation class name? I found there were several answers. (I don't use XA connections)
2. What is the data source helper class name?
I suspect that the above questions are the causes. However, please give me some other hints if any.
Thank you very much!
The driver implementation class name is com.microsoft.sqlserver.jdbc.SQLServerDriver.
The datasource name of the com.microsoft.sqlserver.jdbc.SQLServerDataSource. You can use com.ibm.websphere.rsadapter.DerbyDataStoreHelper to setup our datasource.
Here is some info that may be of use:
Setting up our driver as the JDBC Provider
You should first set up our driver as the JDBC provider. Expand Resources menu on the left of the Administrative Console and choose JDBC Providers. Select New to create a new provider.
1) General Properties: Select the following.
Step 1: User-defined
Step 2: User-defined JDBC Provider
Step 3: User-defined
2) Next Screen
A) General Properties
You can leave scope and name as they are. You can choose your own description. My configuration is as follows
Name: SQLJDBC2005
Class path: path to the sqljdbc.jar file
Implementation class name: com.microsoft.sqlserver.jdbc.SQLServerDataSource
You have to click on save at the top of the screen so that your changes will be saved. You will also be prompted to save incase you forget.
3) Choosing a Datasource
Once you’ve saved and the provider is in place click on the provider name from the providers list. Click on the data sources below Additional Properties. Click on new. You can leave the scope as it is. My configuration is as follows. Unlisted options are left the way they are.
JNDI Name: SQLJDBC2005
Data store helper class name: Generic data store helper
(com.ibm.websphere.rsadapter.GenericDataStoreHelper)
4) Setting up authentication properties for the data source
There are different ways to set up authentication properties for the data source. The following is for the configuration options listed above.
A) Click on the saved data source and go to Custom Properties under Additional Properties
B) Enter serverName, password and user as additional properties. You only need to enter the connection string keyword as the name and the corresponding value.
Once you save all your changes go back to data sources under this provider and click on test connection. You should see the message that your connection succeeded at the top of the screen. You will be given an error message if the connection does not succeed.
|||
Your Step 1 didn't make any sense.
I'm using WAS 5.1.1.3, I'm having a problem trying to get to SQL Server 2005 data and I get errors when I Test Connection. error says:
Test Connection failed for datasource SQL2005 on server server1 at node localhost with the following exception: java.lang.Exception: DSRA8101E: DataSource class cannot be used as one-phase: ClassCastException: com.microsoft.sqlserver.jdbc.SQLServerDataSource
Aside from the vague step 1 instructions which don't follow the provider prompts, aren't there some custom properties that need to be created?sql
How to connect SQL Server 2005 in WAS 5.1
Hi All,
One of our database server has upgraded from SQL Server 2000 to 2005 version some days ago, but I found the application which could run before cannot work fine now. I have followed some intructions got from other guys, such as replace the old driver with a new version, but it still did not work. Now I have two questions
1. What is the driver implementation class name? I found there were several answers. (I don't use XA connections)
2. What is the data source helper class name?
I suspect that the above questions are the causes. However, please give me some other hints if any.
Thank you very much!
The driver implementation class name is com.microsoft.sqlserver.jdbc.SQLServerDriver.
The datasource name of the com.microsoft.sqlserver.jdbc.SQLServerDataSource. You can use com.ibm.websphere.rsadapter.DerbyDataStoreHelper to setup our datasource.
Here is some info that may be of use:
Setting up our driver as the JDBC Provider
You should first set up our driver as the JDBC provider. Expand Resources menu on the left of the Administrative Console and choose JDBC Providers. Select New to create a new provider.
1) General Properties: Select the following.
Step 1: User-defined
Step 2: User-defined JDBC Provider
Step 3: User-defined
2) Next Screen
A) General Properties
You can leave scope and name as they are. You can choose your own description. My configuration is as follows
Name: SQLJDBC2005
Class path: path to the sqljdbc.jar file
Implementation class name: com.microsoft.sqlserver.jdbc.SQLServerDataSource
You have to click on save at the top of the screen so that your changes will be saved. You will also be prompted to save incase you forget.
3) Choosing a Datasource
Once you’ve saved and the provider is in place click on the provider name from the providers list. Click on the data sources below Additional Properties. Click on new. You can leave the scope as it is. My configuration is as follows. Unlisted options are left the way they are.
JNDI Name: SQLJDBC2005
Data store helper class name: Generic data store helper
(com.ibm.websphere.rsadapter.GenericDataStoreHelper)
4) Setting up authentication properties for the data source
There are different ways to set up authentication properties for the data source. The following is for the configuration options listed above.
A) Click on the saved data source and go to Custom Properties under Additional Properties
B) Enter serverName, password and user as additional properties. You only need to enter the connection string keyword as the name and the corresponding value.
Once you save all your changes go back to data sources under this provider and click on test connection. You should see the message that your connection succeeded at the top of the screen. You will be given an error message if the connection does not succeed.
|||
Your Step 1 didn't make any sense.
I'm using WAS 5.1.1.3, I'm having a problem trying to get to SQL Server 2005 data and I get errors when I Test Connection. error says:
Test Connection failed for datasource SQL2005 on server server1 at node localhost with the following exception: java.lang.Exception: DSRA8101E: DataSource class cannot be used as one-phase: ClassCastException: com.microsoft.sqlserver.jdbc.SQLServerDataSource
Aside from the vague step 1 instructions which don't follow the provider prompts, aren't there some custom properties that need to be created?
Wednesday, March 21, 2012
How to configure the server notification in SQL 2005.
I couldn't find anything in the book online or tutorial addressing
this question.
Does anyone know how and what version of Outlook should be used?
Thank you!Why do you need Outlook? If you use the new Database Mail feature, all you
need is an SMTP server willing to relay...
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1134163048.351008.299170@.f14g2000cwb.googlegroups.com...
> I couldn't find anything in the book online or tutorial addressing
> this question.
> Does anyone know how and what version of Outlook should be used?
> Thank you!
>|||hey thanks. that's nice to know and it worked.
how about 'operators'? in sql 2000 EM you can test the operators under
the property page, but that option is no long available in sql 2005.
any idea?
thank you|||> how about 'operators'? in sql 2000 EM you can test the operators under
> the property page, but that option is no long available in sql 2005.
> any idea?
Never used them, probably never will. What do you plan on using them for?|||depends on the type of the scheduled jobs, the notification can go to
different users or emailing groups, or a pager. it's nice to be able to
test the validity of the operators before they go into production.|||> depends on the type of the scheduled jobs, the notification can go to
> different users or emailing groups, or a pager.
With database mail, you can go to an e-mail address, or a distribution list,
or the e-mail address of the pager. Still not sure why you need an
"operator"...
A|||I don't know how to explain it more clear than I alredy did. pls take a
look of
How to: Notify an Operator of Job Status for details in BOL. hope that
helps.
I do appreciate your response; however, I believe my question was about
how to 'test' a operator like in sql 2000 EM, not about why one should
use it, or what the 'operator' is for. If you've never used it and
never will (as you said), pls don't let my question burnden you for an
answer. Thank you.|||How about having a test job which just sends an email that you run on demand
?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1134169235.025163.56700@.o13g2000cwo.googlegroups.com...
> depends on the type of the scheduled jobs, the notification can go to
> different users or emailing groups, or a pager. it's nice to be able to
> test the validity of the operators before they go into production.
>
this question.
Does anyone know how and what version of Outlook should be used?
Thank you!Why do you need Outlook? If you use the new Database Mail feature, all you
need is an SMTP server willing to relay...
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1134163048.351008.299170@.f14g2000cwb.googlegroups.com...
> I couldn't find anything in the book online or tutorial addressing
> this question.
> Does anyone know how and what version of Outlook should be used?
> Thank you!
>|||hey thanks. that's nice to know and it worked.
how about 'operators'? in sql 2000 EM you can test the operators under
the property page, but that option is no long available in sql 2005.
any idea?
thank you|||> how about 'operators'? in sql 2000 EM you can test the operators under
> the property page, but that option is no long available in sql 2005.
> any idea?
Never used them, probably never will. What do you plan on using them for?|||depends on the type of the scheduled jobs, the notification can go to
different users or emailing groups, or a pager. it's nice to be able to
test the validity of the operators before they go into production.|||> depends on the type of the scheduled jobs, the notification can go to
> different users or emailing groups, or a pager.
With database mail, you can go to an e-mail address, or a distribution list,
or the e-mail address of the pager. Still not sure why you need an
"operator"...
A|||I don't know how to explain it more clear than I alredy did. pls take a
look of
How to: Notify an Operator of Job Status for details in BOL. hope that
helps.
I do appreciate your response; however, I believe my question was about
how to 'test' a operator like in sql 2000 EM, not about why one should
use it, or what the 'operator' is for. If you've never used it and
never will (as you said), pls don't let my question burnden you for an
answer. Thank you.|||How about having a test job which just sends an email that you run on demand
?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1134169235.025163.56700@.o13g2000cwo.googlegroups.com...
> depends on the type of the scheduled jobs, the notification can go to
> different users or emailing groups, or a pager. it's nice to be able to
> test the validity of the operators before they go into production.
>
Wednesday, March 7, 2012
How to compare two versions of sqlserver?
HI,
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celia
msnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. In
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate action
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celia
msnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. In
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate action
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen
How to compare two versions of sqlserver?
HI,
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celiamsnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. I
n
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate acti
on
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celiamsnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. I
n
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate acti
on
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen
How to compare two versions of sqlserver?
HI,
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celiamsnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. In
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate action
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen
I need to do an installer utility in which I have to install sql tables. In
there, I would need to check the version of sql server and add some
constraints if the sql server version is more than 8 and do alternate action
if its not.
Is there any function like version_compare()?
Thanks & Regards,
Celiamsnews wrote:
> HI,
> I need to do an installer utility in which I have to install sql tables. In
> there, I would need to check the version of sql server and add some
> constraints if the sql server version is more than 8 and do alternate action
> if its not.
> Is there any function like version_compare()?
> Thanks & Regards,
> Celia
>
>
There isn't a "out of the box" version compare function as such. I think
you'll have to use @.@.Version or SERVERPROPERTY and then use the result
you get from here to do your evaluation.
You can look up both options in Books On Line for further info and syntax.
Regards
Steen
Sunday, February 19, 2012
How to check which service pack is installed?
Hi,
I have the subject question both related to MS-SQL Server (desktop version) AND MS-Analysis services.
When I do do Help | About in the Analysis Manager, I get
Version: 8.00.382
And doing the same in Enterprise Manager, I get:
Version: 8.0
Is this indicating that SP3 (which I think is the latest) has been installed or??
Thanks.Originally posted by Joozh
Hi,
I have the subject question both related to MS-SQL Server (desktop version) AND MS-Analysis services.
When I do do Help | About in the Analysis Manager, I get
Version: 8.00.382
And doing the same in Enterprise Manager, I get:
Version: 8.0
Is this indicating that SP3 (which I think is the latest) has been installed or??
Thanks.
to determine the version and sp for ms-sql server use
select @.@.version in QA
check this link :
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp|||I'd use:SELECT ServerProperty('ProductLevel')-PatP|||Thanks :)|||to determine the version and sp for ms-sql server use
select @.@.version in QA
Hi,
When I run the @.@.version command in teh Query Analyzer, I get the following error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@.@.Version'.
Any clues?|||Originally posted by Joozh
Hi,
When I run the @.@.version command in teh Query Analyzer, I get the following error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@.@.Version'.
Any clues?
SELECT @.@.VERSION|||Thanks harshal. Select @.@.Version returned the results :)
On a side note, while trying to install SP3 on my Desktop, I've just discovered that this itself is like a mini project... when I try to run the setup file (for SP3) I get a message shown in the attached file... and while trying to solve this I was referred to 2 different KB articles... I tell you; learning MS SQL is turning out to be quite an ordeal :mad:
Hopefully I'll feel differently after couple of months and once I get a bit more comfortable. Any clues/shortcuts here :confused:|||Originally posted by Joozh
Thanks harshal. Select @.@.Version returned the results :)
On a side note, while trying to install SP3 on my Desktop, I've just discovered that this itself is like a mini project... when I try to run the setup file (for SP3) I get a message shown in the attached file... and while trying to solve this I was referred to 2 different KB articles... I tell you; learning MS SQL is turning out to be quite an ordeal :mad:
Hopefully I'll feel differently after couple of months and once I get a bit more comfortable. Any clues/shortcuts here :confused:
hi Joozh,
By default, the MSDE 2000 SP3a setup will not install a new instance of MSDE 2000 unless you use the SAPWD parameter to specify a strong sa password. By default, the MSDE 2000 SP3a setup will not upgrade an existing instance of MSDE 2000 unless you have assigned a strong password to the sa login. You should assign a strong password to the sa login, even when upgrading an existing instance, unless the application using your instance of MSDE depends in some way on a null sa password. Even if the instance of MSDE 2000 is running in Windows Authentication mode, the sa login becomes immediately active if the instance is ever switched to Mixed Mode. A null, blank, simple, or well-known sa password could be used for unauthorized access. If you need to assign a strong sa password before upgrading your instance of MSDE 2000 to SP3a.
check out the readme file at:
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp
Harshal.
I have the subject question both related to MS-SQL Server (desktop version) AND MS-Analysis services.
When I do do Help | About in the Analysis Manager, I get
Version: 8.00.382
And doing the same in Enterprise Manager, I get:
Version: 8.0
Is this indicating that SP3 (which I think is the latest) has been installed or??
Thanks.Originally posted by Joozh
Hi,
I have the subject question both related to MS-SQL Server (desktop version) AND MS-Analysis services.
When I do do Help | About in the Analysis Manager, I get
Version: 8.00.382
And doing the same in Enterprise Manager, I get:
Version: 8.0
Is this indicating that SP3 (which I think is the latest) has been installed or??
Thanks.
to determine the version and sp for ms-sql server use
select @.@.version in QA
check this link :
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp|||I'd use:SELECT ServerProperty('ProductLevel')-PatP|||Thanks :)|||to determine the version and sp for ms-sql server use
select @.@.version in QA
Hi,
When I run the @.@.version command in teh Query Analyzer, I get the following error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@.@.Version'.
Any clues?|||Originally posted by Joozh
Hi,
When I run the @.@.version command in teh Query Analyzer, I get the following error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@.@.Version'.
Any clues?
SELECT @.@.VERSION|||Thanks harshal. Select @.@.Version returned the results :)
On a side note, while trying to install SP3 on my Desktop, I've just discovered that this itself is like a mini project... when I try to run the setup file (for SP3) I get a message shown in the attached file... and while trying to solve this I was referred to 2 different KB articles... I tell you; learning MS SQL is turning out to be quite an ordeal :mad:
Hopefully I'll feel differently after couple of months and once I get a bit more comfortable. Any clues/shortcuts here :confused:|||Originally posted by Joozh
Thanks harshal. Select @.@.Version returned the results :)
On a side note, while trying to install SP3 on my Desktop, I've just discovered that this itself is like a mini project... when I try to run the setup file (for SP3) I get a message shown in the attached file... and while trying to solve this I was referred to 2 different KB articles... I tell you; learning MS SQL is turning out to be quite an ordeal :mad:
Hopefully I'll feel differently after couple of months and once I get a bit more comfortable. Any clues/shortcuts here :confused:
hi Joozh,
By default, the MSDE 2000 SP3a setup will not install a new instance of MSDE 2000 unless you use the SAPWD parameter to specify a strong sa password. By default, the MSDE 2000 SP3a setup will not upgrade an existing instance of MSDE 2000 unless you have assigned a strong password to the sa login. You should assign a strong password to the sa login, even when upgrading an existing instance, unless the application using your instance of MSDE depends in some way on a null sa password. Even if the instance of MSDE 2000 is running in Windows Authentication mode, the sa login becomes immediately active if the instance is ever switched to Mixed Mode. A null, blank, simple, or well-known sa password could be used for unauthorized access. If you need to assign a strong sa password before upgrading your instance of MSDE 2000 to SP3a.
check out the readme file at:
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp
Harshal.
Subscribe to:
Posts (Atom)