I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the main
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
John
In general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John
|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting to
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example above
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John
|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John
sql
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Friday, March 30, 2012
Newbie question on stored procedures and linked servers
I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the mai
n
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting t
o
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example abov
e
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the mai
n
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting t
o
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example abov
e
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John
Newbie question on stored procedures and linked servers
I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the main
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting to
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example above
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
>> Perhaps if you provided a bit more details about what you are attempting
>> to
>> accomplish, someone here will have a better solution.
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the main
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting to
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example above
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
>> Perhaps if you provided a bit more details about what you are attempting
>> to
>> accomplish, someone here will have a better solution.
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John
Monday, March 26, 2012
newbie question -- error when connecting via System DSN on any network but the SQL Servers
I'm very new to using SQL Server. All I'm trying to do at the moment, is
create a System DSN on a Windows 2000 Professional desktop machine to
connect to a SQL Server 2000 server which is running on a different
network. When I try the DSN while connected to the SAME network that the
SQL Server machine is on, it works fine. But when I connect from any
other network, I receive the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
create a System DSN on a Windows 2000 Professional desktop machine to
connect to a SQL Server 2000 server which is running on a different
network. When I try the DSN while connected to the SAME network that the
SQL Server machine is on, it works fine. But when I connect from any
other network, I receive the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
Here are the settings that I'm using for my System DSN:
Name: test
Which SQL Server do you want to connect to? 129.22.180.203
* With SQL Server Authentication using a login ID and password entered by
the user
* Connect to SQL Server to obtain default settings for the additional
configuration options
Login ID: SA
Password: <correct password entered
And in the Client Configuration:
Server Alias: 129.22.180.203
Network libraries: TCP/IP
Server Name: 129.22.180.203
* Dynamically determine port
Also, the driver is:
Microsoft SQL Server ODBC Driver version 03.85.1025
Please, if anyone has any ideas on what I am doing wrong, I can use all
the help I can get!!
--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/sheree <sah18@.case.edu> wrote in message news:<opsb5642gy6l2js5@.uh007637>...
> I'm very new to using SQL Server. All I'm trying to do at the moment, is
> create a System DSN on a Windows 2000 Professional desktop machine to
> connect to a SQL Server 2000 server which is running on a different
> network. When I try the DSN while connected to the SAME network that the
> SQL Server machine is on, it works fine. But when I connect from any
> other network, I receive the following error:
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 10061
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
> (Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> exist or access denied.
> Here are the settings that I'm using for my System DSN:
> Name: test
> Which SQL Server do you want to connect to? 129.22.180.203
> * With SQL Server Authentication using a login ID and password entered by
> the user
> * Connect to SQL Server to obtain default settings for the additional
> configuration options
> Login ID: SA
> Password: <correct password entered>
> And in the Client Configuration:
> Server Alias: 129.22.180.203
> Network libraries: TCP/IP
> Server Name: 129.22.180.203
> * Dynamically determine port
> Also, the driver is:
> Microsoft SQL Server ODBC Driver version 03.85.1025
> Please, if anyone has any ideas on what I am doing wrong, I can use all
> the help I can get!!
I'm not sure what you mean by "a different network". If you mean that
the networks are physically separated by a firewall, or if you're
connecting over a VPN, then you might need to open the ports for
MSSQL:
http://support.microsoft.com/defaul...2&Product=sql2k
Simon|||Simon,
By "different network", I mean the following:
The SQL Server is running on a Windows 2000 Server machine that is located
on a University network. If I am on any machine that is also connected to
the University network, OR if I VPN into the University network, I can
successfully connect to the SQL Server in my DSN connection with the
parameters I gave in my original posting. However, if I try from my
office computer which is on a hospital network (and IS behind a firewall)
or if I try from any computer where I'm dialed up to my ISP (no firewall
would block anything here) I still can't connect to the SQL Server in my
DSN connection.
So I really don't think it's the port that is being blocked.
Are there any connection settings in SQL Server itself that I may have
configured incorrectly? Since I know so little about the software, I'm
not sure where even to begin to look.
Thanks for helping out!
-sheree
On 4 Aug 2004 01:16:19 -0700, Simon Hayes <sql@.hayes.ch> wrote:
> sheree <sah18@.case.edu> wrote in message
> news:<opsb5642gy6l2js5@.uh007637>...
>> I'm very new to using SQL Server. All I'm trying to do at the moment,
>> is
>> create a System DSN on a Windows 2000 Professional desktop machine to
>> connect to a SQL Server 2000 server which is running on a different
>> network. When I try the DSN while connected to the SAME network that
>> the
>> SQL Server machine is on, it works fine. But when I connect from any
>> other network, I receive the following error:
>> Connection failed:
>> SQLState: '01000'
>> SQL Server Error: 10061
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
>> (Connect()).
>> Connection failed:
>> SQLState: '08001'
>> SQL Server Error: 17
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
>> exist or access denied.
>>
>> Here are the settings that I'm using for my System DSN:
>> Name: test
>> Which SQL Server do you want to connect to? 129.22.180.203
>> * With SQL Server Authentication using a login ID and password entered
>> by
>> the user
>> * Connect to SQL Server to obtain default settings for the additional
>> configuration options
>> Login ID: SA
>> Password: <correct password entered>
>>
>> And in the Client Configuration:
>> Server Alias: 129.22.180.203
>> Network libraries: TCP/IP
>> Server Name: 129.22.180.203
>> * Dynamically determine port
>>
>> Also, the driver is:
>> Microsoft SQL Server ODBC Driver version 03.85.1025
>>
>> Please, if anyone has any ideas on what I am doing wrong, I can use all
>> the help I can get!!
> I'm not sure what you mean by "a different network". If you mean that
> the networks are physically separated by a firewall, or if you're
> connecting over a VPN, then you might need to open the ports for
> MSSQL:
> http://support.microsoft.com/defaul...2&Product=sql2k
> Simon
--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||> However, if I try from my
> office computer which is on a hospital network (and IS behind a firewall)
> or if I try from any computer where I'm dialed up to my ISP (no firewall
> would block anything here) I still can't connect to the SQL Server in my
> DSN connection.
> So I really don't think it's the port that is being blocked.
From your description, it seems you are trying to connect over the public
internet to the SQL Server at your university. Port 1433 needs to be open
to allow this and it is likely that the firewall at the university has the
port closed for security reasons. It works when you are connected to the
university LAN or VPN because the firewall doesn't block 1433 in those
cases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sheree" <sah18@.case.edu> wrote in message
news:opsb7ky5r56l2js5@.v129-22-124-109.vclient.cwru.edu...
> Simon,
> By "different network", I mean the following:
> The SQL Server is running on a Windows 2000 Server machine that is located
> on a University network. If I am on any machine that is also connected to
> the University network, OR if I VPN into the University network, I can
> successfully connect to the SQL Server in my DSN connection with the
> parameters I gave in my original posting. However, if I try from my
> office computer which is on a hospital network (and IS behind a firewall)
> or if I try from any computer where I'm dialed up to my ISP (no firewall
> would block anything here) I still can't connect to the SQL Server in my
> DSN connection.
> So I really don't think it's the port that is being blocked.
> Are there any connection settings in SQL Server itself that I may have
> configured incorrectly? Since I know so little about the software, I'm
> not sure where even to begin to look.
> Thanks for helping out!
> -sheree
>
> On 4 Aug 2004 01:16:19 -0700, Simon Hayes <sql@.hayes.ch> wrote:
> > sheree <sah18@.case.edu> wrote in message
> > news:<opsb5642gy6l2js5@.uh007637>...
> >> I'm very new to using SQL Server. All I'm trying to do at the moment,
> >> is
> >> create a System DSN on a Windows 2000 Professional desktop machine to
> >> connect to a SQL Server 2000 server which is running on a different
> >> network. When I try the DSN while connected to the SAME network that
> >> the
> >> SQL Server machine is on, it works fine. But when I connect from any
> >> other network, I receive the following error:
> >> Connection failed:
> >> SQLState: '01000'
> >> SQL Server Error: 10061
> >> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
> >> (Connect()).
> >> Connection failed:
> >> SQLState: '08001'
> >> SQL Server Error: 17
> >> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> >> exist or access denied.
> >>
> >> Here are the settings that I'm using for my System DSN:
> >> Name: test
> >> Which SQL Server do you want to connect to? 129.22.180.203
> >> * With SQL Server Authentication using a login ID and password entered
> >> by
> >> the user
> >> * Connect to SQL Server to obtain default settings for the additional
> >> configuration options
> >> Login ID: SA
> >> Password: <correct password entered>
> >>
> >> And in the Client Configuration:
> >> Server Alias: 129.22.180.203
> >> Network libraries: TCP/IP
> >> Server Name: 129.22.180.203
> >> * Dynamically determine port
> >>
> >> Also, the driver is:
> >> Microsoft SQL Server ODBC Driver version 03.85.1025
> >>
> >> Please, if anyone has any ideas on what I am doing wrong, I can use all
> >> the help I can get!!
> > I'm not sure what you mean by "a different network". If you mean that
> > the networks are physically separated by a firewall, or if you're
> > connecting over a VPN, then you might need to open the ports for
> > MSSQL:
http://support.microsoft.com/defaul...2&Product=sql2k
> > Simon
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Dan,
Can another port be used for SQL Server to run on (rather than using the
default port 1433, which is what it is currently running on)? It might be
easier for me to use a different port that is already open at the
university, rather than attempt to request that they open port 1433
(unlikely they will do this).
Thanks so much for your help!
-sheree
On Wed, 04 Aug 2004 13:13:12 GMT, Dan Guzman
<danguzman@.nospam-earthlink.net> wrote:
> From your description, it seems you are trying to connect over the public
> internet to the SQL Server at your university. Port 1433 needs to be
> open
> to allow this and it is likely that the firewall at the university has
> the
> port closed for security reasons. It works when you are connected to the
> university LAN or VPN because the firewall doesn't block 1433 in those
> cases.|||sheree (sah18@.case.edu) writes:
> Can another port be used for SQL Server to run on (rather than using the
> default port 1433, which is what it is currently running on)? It might be
> easier for me to use a different port that is already open at the
> university, rather than attempt to request that they open port 1433
Yes, it is possible to use another port. (Which would affect everyone
using that server, as SQL Server uses only one port at a time.) No,
I don't think your university is likely to make SQL Server available on the
Internet on any port, least of all if you insist on using SQL Server
authentication.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Although it is certainly possible to configure SQL Server to listen on an
alternate port, it appears the SQL Server is configured to use 1433 because
your working local/VPN connections use the default port.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sheree" <sah18@.case.edu> wrote in message
news:opsb7mufwi6l2js5@.v129-22-124-109.vclient.cwru.edu...
> Dan,
> Can another port be used for SQL Server to run on (rather than using the
> default port 1433, which is what it is currently running on)? It might be
> easier for me to use a different port that is already open at the
> university, rather than attempt to request that they open port 1433
> (unlikely they will do this).
> Thanks so much for your help!
> -sheree
>
> On Wed, 04 Aug 2004 13:13:12 GMT, Dan Guzman
> <danguzman@.nospam-earthlink.net> wrote:
> > From your description, it seems you are trying to connect over the
public
> > internet to the SQL Server at your university. Port 1433 needs to be
> > open
> > to allow this and it is likely that the firewall at the university has
> > the
> > port closed for security reasons. It works when you are connected to
the
> > university LAN or VPN because the firewall doesn't block 1433 in those
> > cases.
Friday, March 23, 2012
Newbie question
I am looking to go into a DBA role and I keep hearing
about clustering servers.
Is there anywhere I can go to learn about it? Or can
someone please tell me what it is and how to set it up?
Thanks,
GP
I like
http://www.microsoft.com/technet/pro.../failclus.mspx
as a resource.
You may also like:
http://support.microsoft.com/default.aspx?kbid=325106
or
http://support.microsoft.com/?id=325485
Cheers,
Rod
"GeorgeP" <anonymous@.discussions.microsoft.com> wrote in message
news:1d2ff01c453dd$e6e12890$a301280a@.phx.gbl...
> I am looking to go into a DBA role and I keep hearing
> about clustering servers.
> Is there anywhere I can go to learn about it? Or can
> someone please tell me what it is and how to set it up?
> Thanks,
> GP
|||http://www.sql-server-performance.co...ing_intro1.asp
haven't read it yet, but I need the same info to improve my chances at a
better DBA job than the one I have...Clustering is definitely an advanced
concept.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"GeorgeP" <anonymous@.discussions.microsoft.com> wrote in message
news:1d2ff01c453dd$e6e12890$a301280a@.phx.gbl...
> I am looking to go into a DBA role and I keep hearing
> about clustering servers.
> Is there anywhere I can go to learn about it? Or can
> someone please tell me what it is and how to set it up?
> Thanks,
> GP
|||Thanks I am sure this will help!
GP
|||Thanks for the info I am sure this will help.
GP
|||The following book is a good one for SQL Server 2000 Failover Clustering
Microsoft SQL Server 2000 High Availability (MS Press)
ISBN 0-7356-1920-4
Here are some more links to good Microsoft Whitepapers, Webcasts and KB articles related to SQL Server Failover Clustering.
Microsoft Whitepaper - SQL Server 2000 Failover Clustering
http://www.microsoft.com/SQL/techinf...vercluster.asp
Microsoft Webcasts
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack Installation
http://support.microsoft.com/default...lurb061002.asp
Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
http://support.microsoft.com/default...lurb101802.asp
Troubleshooting SQL 2000 Virtual Server and Service Pack Setups for Failover Clustering
http://support.microsoft.com/default...lurb020703.asp
Microsoft Knowledge Base Articles
Q243218 INF: Installation Order for SQL 2000 Enterprise Edition
http://support.microsoft.com/?kbid=243218
Q260758 - INF: Frequently Asked Questions - SQL Server 2000 - Failover Clustering
http://support.microsoft.com/?kbid=260758
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
sql
about clustering servers.
Is there anywhere I can go to learn about it? Or can
someone please tell me what it is and how to set it up?
Thanks,
GP
I like
http://www.microsoft.com/technet/pro.../failclus.mspx
as a resource.
You may also like:
http://support.microsoft.com/default.aspx?kbid=325106
or
http://support.microsoft.com/?id=325485
Cheers,
Rod
"GeorgeP" <anonymous@.discussions.microsoft.com> wrote in message
news:1d2ff01c453dd$e6e12890$a301280a@.phx.gbl...
> I am looking to go into a DBA role and I keep hearing
> about clustering servers.
> Is there anywhere I can go to learn about it? Or can
> someone please tell me what it is and how to set it up?
> Thanks,
> GP
|||http://www.sql-server-performance.co...ing_intro1.asp
haven't read it yet, but I need the same info to improve my chances at a
better DBA job than the one I have...Clustering is definitely an advanced
concept.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"GeorgeP" <anonymous@.discussions.microsoft.com> wrote in message
news:1d2ff01c453dd$e6e12890$a301280a@.phx.gbl...
> I am looking to go into a DBA role and I keep hearing
> about clustering servers.
> Is there anywhere I can go to learn about it? Or can
> someone please tell me what it is and how to set it up?
> Thanks,
> GP
|||Thanks I am sure this will help!
GP
|||Thanks for the info I am sure this will help.
GP
|||The following book is a good one for SQL Server 2000 Failover Clustering
Microsoft SQL Server 2000 High Availability (MS Press)
ISBN 0-7356-1920-4
Here are some more links to good Microsoft Whitepapers, Webcasts and KB articles related to SQL Server Failover Clustering.
Microsoft Whitepaper - SQL Server 2000 Failover Clustering
http://www.microsoft.com/SQL/techinf...vercluster.asp
Microsoft Webcasts
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack Installation
http://support.microsoft.com/default...lurb061002.asp
Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
http://support.microsoft.com/default...lurb101802.asp
Troubleshooting SQL 2000 Virtual Server and Service Pack Setups for Failover Clustering
http://support.microsoft.com/default...lurb020703.asp
Microsoft Knowledge Base Articles
Q243218 INF: Installation Order for SQL 2000 Enterprise Edition
http://support.microsoft.com/?kbid=243218
Q260758 - INF: Frequently Asked Questions - SQL Server 2000 - Failover Clustering
http://support.microsoft.com/?kbid=260758
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
sql
Monday, March 19, 2012
Newbie needs to be told where to go! (Cross-Post)
Greetings:
I've been developing web applications with asp/sql and access for some
time now on existing servers. I am now in a situation that requires me to
install SQL Server (SBS 2K sp3) as the backend for an intranet served from
IIS. I've installed the SQL services and have created the database and I
have some of the web based application written, no problem, but I've yet to
be able to access the database that I created on the newly installed SQL
server from the web based applications. Even after setting up the virtual
directory with the IIS SQL snap-in, I only get a DNS Server not found error
when I try to browse the site. If I remove the IIS/SQL virtual directory, I
can browse pages that don't error out due to data references.
Where can I find a walk through for setting up SQL Server and IIS to
function together in this very basic manner?
Thanks in advance.
--
Jerry M. Gartner
www.gartnerwebdev.comHi,
Have a look at these...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;176378
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;176380
http://support.microsoft.com/default.aspx?scid=kb;zh-
cn;chs264921
http://support.microsoft.com/default.aspx?scid=kb;PL;264921
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;245243
>--Original Message--
>Greetings:
> I've been developing web applications with asp/sql
and access for some
>time now on existing servers. I am now in a situation
that requires me to
>install SQL Server (SBS 2K sp3) as the backend for an
intranet served from
>IIS. I've installed the SQL services and have created
the database and I
>have some of the web based application written, no
problem, but I've yet to
>be able to access the database that I created on the
newly installed SQL
>server from the web based applications. Even after
setting up the virtual
>directory with the IIS SQL snap-in, I only get a DNS
Server not found error
>when I try to browse the site. If I remove the IIS/SQL
virtual directory, I
>can browse pages that don't error out due to data
references.
> Where can I find a walk through for setting up SQL
Server and IIS to
>function together in this very basic manner?
>Thanks in advance.
>
>--
>Jerry M. Gartner
>www.gartnerwebdev.com
>
>.
>|||Peter:
Preliminarily, this appears to be what I'm looking for. I didn't think
to use ODBC and a DSN. What are the major differences between ODBC and
"direct" connection? There will be under 30 clients using the DB - this is
an on demand intranet application and will be hit lightly and sporadically
throughout the day. The SQL server is also the PDC and fileserver/DFS root
and gateway. (2K SP4)
--
Jerry M. Gartner
www.gartnerwebdev.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:03de01c3a83a$14ef9140$a001280a@.phx.gbl...
> Hi,
> Have a look at these...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;176378
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;176380
> http://support.microsoft.com/default.aspx?scid=kb;zh-
> cn;chs264921
> http://support.microsoft.com/default.aspx?scid=kb;PL;264921
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;245243
I've been developing web applications with asp/sql and access for some
time now on existing servers. I am now in a situation that requires me to
install SQL Server (SBS 2K sp3) as the backend for an intranet served from
IIS. I've installed the SQL services and have created the database and I
have some of the web based application written, no problem, but I've yet to
be able to access the database that I created on the newly installed SQL
server from the web based applications. Even after setting up the virtual
directory with the IIS SQL snap-in, I only get a DNS Server not found error
when I try to browse the site. If I remove the IIS/SQL virtual directory, I
can browse pages that don't error out due to data references.
Where can I find a walk through for setting up SQL Server and IIS to
function together in this very basic manner?
Thanks in advance.
--
Jerry M. Gartner
www.gartnerwebdev.comHi,
Have a look at these...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;176378
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;176380
http://support.microsoft.com/default.aspx?scid=kb;zh-
cn;chs264921
http://support.microsoft.com/default.aspx?scid=kb;PL;264921
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;245243
>--Original Message--
>Greetings:
> I've been developing web applications with asp/sql
and access for some
>time now on existing servers. I am now in a situation
that requires me to
>install SQL Server (SBS 2K sp3) as the backend for an
intranet served from
>IIS. I've installed the SQL services and have created
the database and I
>have some of the web based application written, no
problem, but I've yet to
>be able to access the database that I created on the
newly installed SQL
>server from the web based applications. Even after
setting up the virtual
>directory with the IIS SQL snap-in, I only get a DNS
Server not found error
>when I try to browse the site. If I remove the IIS/SQL
virtual directory, I
>can browse pages that don't error out due to data
references.
> Where can I find a walk through for setting up SQL
Server and IIS to
>function together in this very basic manner?
>Thanks in advance.
>
>--
>Jerry M. Gartner
>www.gartnerwebdev.com
>
>.
>|||Peter:
Preliminarily, this appears to be what I'm looking for. I didn't think
to use ODBC and a DSN. What are the major differences between ODBC and
"direct" connection? There will be under 30 clients using the DB - this is
an on demand intranet application and will be hit lightly and sporadically
throughout the day. The SQL server is also the PDC and fileserver/DFS root
and gateway. (2K SP4)
--
Jerry M. Gartner
www.gartnerwebdev.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:03de01c3a83a$14ef9140$a001280a@.phx.gbl...
> Hi,
> Have a look at these...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;176378
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;176380
> http://support.microsoft.com/default.aspx?scid=kb;zh-
> cn;chs264921
> http://support.microsoft.com/default.aspx?scid=kb;PL;264921
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;245243
Monday, March 12, 2012
Newbie Installation Problem
I'm using Visual Studio.Net and have been unable to access SQL Server
Databases from the Server Explorer. Under SQL Servers I did see one from the
Office Business Contact Manager (BCM). So I down loaded the MSDE Sp3a file
and installed it from a command prompt, setup SAPWD="AStrongPassword"
(substituting an actual password within the quotes). The setup seemed to
proceed smoothly and then just ended. Now in VS.Net Server Exporer I see a
second SQL Server with simply the computer name, and when I click on the "+"
to expand it, a Login Box pops up with my username under Login. If have
tried typing the password I entered after the SAPWD parameter with my user
name and the following login names: sa, SA, Admin, Administrator, all to no
effect. I've entered the password with and without quotes. Each time I get
the server does not exist or access denied message. The only way I have been
able to tell if the MSDE is actually running is by looking under processes in
the task manager. There I do see an SQL2000 process. I've looked at the
knowledge base describing connection problems, and most of those seemed to be
related to access from other computers, and it really isn't clear to me which
of the problems they describe might apply to a local machine by itself. Is
there a different login name I should be using? Many of the wizards in
Visual Studio only work with MS SQL, so its a real hassle not having it
installed. Any suggestions would be greatly appreciated.
hi,
JerryKogan wrote:
> I'm using Visual Studio.Net and have been unable to access SQL Server
> Databases from the Server Explorer. Under SQL Servers I did see one
> from the Office Business Contact Manager (BCM). So I down loaded the
> MSDE Sp3a file and installed it from a command prompt, setup
> SAPWD="AStrongPassword" (substituting an actual password within the
> quotes). The setup seemed to proceed smoothly and then just ended.
> Now in VS.Net Server Exporer I see a second SQL Server with simply
> the computer name, and when I click on the "+" to expand it, a Login
> Box pops up with my username under Login. If have tried typing the
> password I entered after the SAPWD parameter with my user name and
> the following login names: sa, SA, Admin, Administrator, all to no
> effect. I've entered the password with and without quotes. Each
> time I get the server does not exist or access denied message. The
> only way I have been able to tell if the MSDE is actually running is
> by looking under processes in the task manager. There I do see an
> SQL2000 process. I've looked at the knowledge base describing
> connection problems, and most of those seemed to be related to access
> from other computers, and it really isn't clear to me which of the
> problems they describe might apply to a local machine by itself. Is
> there a different login name I should be using? Many of the wizards
> in Visual Studio only work with MS SQL, so its a real hassle not
> having it installed. Any suggestions would be greatly appreciated.
MSDE installs by default allowing only tusted WinNT authenticated
connections.. you have to provide the additional
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer to allow SQL Server (standard)
authenticated connections or, after install, to modify the Windows registry
as foillowing:
(named instance)
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
LoginMode=2
(default instance, your case)
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
LoginMode=2
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Databases from the Server Explorer. Under SQL Servers I did see one from the
Office Business Contact Manager (BCM). So I down loaded the MSDE Sp3a file
and installed it from a command prompt, setup SAPWD="AStrongPassword"
(substituting an actual password within the quotes). The setup seemed to
proceed smoothly and then just ended. Now in VS.Net Server Exporer I see a
second SQL Server with simply the computer name, and when I click on the "+"
to expand it, a Login Box pops up with my username under Login. If have
tried typing the password I entered after the SAPWD parameter with my user
name and the following login names: sa, SA, Admin, Administrator, all to no
effect. I've entered the password with and without quotes. Each time I get
the server does not exist or access denied message. The only way I have been
able to tell if the MSDE is actually running is by looking under processes in
the task manager. There I do see an SQL2000 process. I've looked at the
knowledge base describing connection problems, and most of those seemed to be
related to access from other computers, and it really isn't clear to me which
of the problems they describe might apply to a local machine by itself. Is
there a different login name I should be using? Many of the wizards in
Visual Studio only work with MS SQL, so its a real hassle not having it
installed. Any suggestions would be greatly appreciated.
hi,
JerryKogan wrote:
> I'm using Visual Studio.Net and have been unable to access SQL Server
> Databases from the Server Explorer. Under SQL Servers I did see one
> from the Office Business Contact Manager (BCM). So I down loaded the
> MSDE Sp3a file and installed it from a command prompt, setup
> SAPWD="AStrongPassword" (substituting an actual password within the
> quotes). The setup seemed to proceed smoothly and then just ended.
> Now in VS.Net Server Exporer I see a second SQL Server with simply
> the computer name, and when I click on the "+" to expand it, a Login
> Box pops up with my username under Login. If have tried typing the
> password I entered after the SAPWD parameter with my user name and
> the following login names: sa, SA, Admin, Administrator, all to no
> effect. I've entered the password with and without quotes. Each
> time I get the server does not exist or access denied message. The
> only way I have been able to tell if the MSDE is actually running is
> by looking under processes in the task manager. There I do see an
> SQL2000 process. I've looked at the knowledge base describing
> connection problems, and most of those seemed to be related to access
> from other computers, and it really isn't clear to me which of the
> problems they describe might apply to a local machine by itself. Is
> there a different login name I should be using? Many of the wizards
> in Visual Studio only work with MS SQL, so its a real hassle not
> having it installed. Any suggestions would be greatly appreciated.
MSDE installs by default allowing only tusted WinNT authenticated
connections.. you have to provide the additional
SECURITYMODE=SQL
parameter to the setup.exe boostrap installer to allow SQL Server (standard)
authenticated connections or, after install, to modify the Windows registry
as foillowing:
(named instance)
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer
LoginMode=2
(default instance, your case)
HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
LoginMode=2
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Subscribe to:
Posts (Atom)