Hi Group,
As part of our efforts at best practices, we recently changed the default
port of 1433 for the first instance of SQL on our SQL server another port say
8888. There is a firewall between the SQL server and the clients. The
firewall rulebase was changed to allow traffic on port 8888 between the SQL
server and the clients.
However following the change the clients could not connect to the SQL
instance. I am not a database administrator, but my understanding of SQL is
that the clients would dynamically determine the changed port number using
the SQL browser (reporting) service and attempt a connection on the new port
8888.
Any ideas on what went wrong or is my understanding of how SQL connects
fundamentally wrong......any feedback is much apreciated.
Thanks
CMSQL Server uses SQL Server Resolution Protocol (SSRP) to resolve instance
name. In SQL Server 2000 SSRP is operated by SQL Server service (and I think
that if there is no instance working on port 1433 there is no automatic name
resolution). In SQL Server 2005 there is another service called SQL Server
Browser dedicated to respond to SSRP requests. It listens on UDP1434 (so you
must open this port on your firewall).
What you can try is to connect to SQL Server not by instance name but by
machine name and TCP port number: tcp:MACHINE_NAME,port. Example:
tcp:LONDON,8888
--
Regards
Pawel Potasinski
U¿ytkownik "CP" <CP@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:5A85887A-FE89-4245-837C-E01FDEF43BAB@.microsoft.com...
> Hi Group,
> As part of our efforts at best practices, we recently changed the default
> port of 1433 for the first instance of SQL on our SQL server another port
> say
> 8888. There is a firewall between the SQL server and the clients. The
> firewall rulebase was changed to allow traffic on port 8888 between the
> SQL
> server and the clients.
> However following the change the clients could not connect to the SQL
> instance. I am not a database administrator, but my understanding of SQL
> is
> that the clients would dynamically determine the changed port number using
> the SQL browser (reporting) service and attempt a connection on the new
> port
> 8888.
> Any ideas on what went wrong or is my understanding of how SQL connects
> fundamentally wrong......any feedback is much apreciated.
> Thanks
> CM
>|||Thanks for your response, Pawel.
We have 1434 opened on the firewall. Prior to this change we had (and still
do have) other instances of SQL on the same server. My understanding is that
the SQL Server Browser, was determinining the port numbers of these other
instances. So when the port no of default instance was changed, the
reporting service would determine the new port no as well.
This was tested in QA successfully albeit without a firewall in between. Do
you still think it would be worthwhile to connect using a machine name? We
are back to QA now, without a firewall in between.
Thanks again.
CM
"Pawel Potasinski" wrote:
> SQL Server uses SQL Server Resolution Protocol (SSRP) to resolve instance
> name. In SQL Server 2000 SSRP is operated by SQL Server service (and I think
> that if there is no instance working on port 1433 there is no automatic name
> resolution). In SQL Server 2005 there is another service called SQL Server
> Browser dedicated to respond to SSRP requests. It listens on UDP1434 (so you
> must open this port on your firewall).
> What you can try is to connect to SQL Server not by instance name but by
> machine name and TCP port number: tcp:MACHINE_NAME,port. Example:
> tcp:LONDON,8888
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "CP" <CP@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:5A85887A-FE89-4245-837C-E01FDEF43BAB@.microsoft.com...
> > Hi Group,
> >
> > As part of our efforts at best practices, we recently changed the default
> > port of 1433 for the first instance of SQL on our SQL server another port
> > say
> > 8888. There is a firewall between the SQL server and the clients. The
> > firewall rulebase was changed to allow traffic on port 8888 between the
> > SQL
> > server and the clients.
> >
> > However following the change the clients could not connect to the SQL
> > instance. I am not a database administrator, but my understanding of SQL
> > is
> > that the clients would dynamically determine the changed port number using
> > the SQL browser (reporting) service and attempt a connection on the new
> > port
> > 8888.
> >
> > Any ideas on what went wrong or is my understanding of how SQL connects
> > fundamentally wrong......any feedback is much apreciated.
> >
> > Thanks
> >
> > CM
> >
> >
>
>
Showing posts with label default. Show all posts
Showing posts with label default. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
Connecting to remote Express instance using SSMS
I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
the default instance and two SQLExpress instances named 'sqlexpress' and
'officeservers', all withing SSMS, and can connect and manage them.
From my workstation, running SQL 2005 Dev, SP2, I can connect to the default
instance on that server, but cannot connect to the express instances. They
both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
I have done the following:
Made myself a member of the local Admin group
Tried using the sa account to connect
nothing seems to work. What am I missing?
TIA
Todd Chittenden
Is the SQLBrowser Service enabled and started?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
> the default instance and two SQLExpress instances named 'sqlexpress' and
> 'officeservers', all withing SSMS, and can connect and manage them.
> From my workstation, running SQL 2005 Dev, SP2, I can connect to the
> default
> instance on that server, but cannot connect to the express instances. They
> both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
> I have done the following:
> Made myself a member of the local Admin group
> Tried using the sa account to connect
> nothing seems to work. What am I missing?
> TIA
> --
> Todd Chittenden
>
|||I found it not long after posting the original question:
No, the Browser service was not running. Starting that up enabled me to
connect to it. So that was the answer.
But even before the Enterprise instance was installed, (which includes the
browser service) I was not able to connect. I know I have other machines in
my domain that are running SQL Express. Should I not be able to connect to
them using my SSMS? What do I need to do to make that happen?
I assume that I need membership in the group on the local machine that bears
the SQL Express instance name?
What else? I can't rely on the Browser service in those instances.
Thanks
Todd Chittenden
"Roger Wolter[MSFT]" wrote:
> Is the SQLBrowser Service enabled and started?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>
|||SQL Express by default installs as a named instance. Unless you specify the
TCP/IP port in the connection string, you need the sqlbrowser service to
connect to a named instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:F4B981A2-4B9A-443E-855B-D53B89FCE511@.microsoft.com...[vbcol=seagreen]
>I found it not long after posting the original question:
> No, the Browser service was not running. Starting that up enabled me to
> connect to it. So that was the answer.
> But even before the Enterprise instance was installed, (which includes the
> browser service) I was not able to connect. I know I have other machines
> in
> my domain that are running SQL Express. Should I not be able to connect to
> them using my SSMS? What do I need to do to make that happen?
> I assume that I need membership in the group on the local machine that
> bears
> the SQL Express instance name?
> What else? I can't rely on the Browser service in those instances.
> Thanks
> --
> Todd Chittenden
>
> "Roger Wolter[MSFT]" wrote:
the default instance and two SQLExpress instances named 'sqlexpress' and
'officeservers', all withing SSMS, and can connect and manage them.
From my workstation, running SQL 2005 Dev, SP2, I can connect to the default
instance on that server, but cannot connect to the express instances. They
both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
I have done the following:
Made myself a member of the local Admin group
Tried using the sa account to connect
nothing seems to work. What am I missing?
TIA
Todd Chittenden
Is the SQLBrowser Service enabled and started?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
> the default instance and two SQLExpress instances named 'sqlexpress' and
> 'officeservers', all withing SSMS, and can connect and manage them.
> From my workstation, running SQL 2005 Dev, SP2, I can connect to the
> default
> instance on that server, but cannot connect to the express instances. They
> both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
> I have done the following:
> Made myself a member of the local Admin group
> Tried using the sa account to connect
> nothing seems to work. What am I missing?
> TIA
> --
> Todd Chittenden
>
|||I found it not long after posting the original question:
No, the Browser service was not running. Starting that up enabled me to
connect to it. So that was the answer.
But even before the Enterprise instance was installed, (which includes the
browser service) I was not able to connect. I know I have other machines in
my domain that are running SQL Express. Should I not be able to connect to
them using my SSMS? What do I need to do to make that happen?
I assume that I need membership in the group on the local machine that bears
the SQL Express instance name?
What else? I can't rely on the Browser service in those instances.
Thanks
Todd Chittenden
"Roger Wolter[MSFT]" wrote:
> Is the SQLBrowser Service enabled and started?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>
|||SQL Express by default installs as a named instance. Unless you specify the
TCP/IP port in the connection string, you need the sqlbrowser service to
connect to a named instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:F4B981A2-4B9A-443E-855B-D53B89FCE511@.microsoft.com...[vbcol=seagreen]
>I found it not long after posting the original question:
> No, the Browser service was not running. Starting that up enabled me to
> connect to it. So that was the answer.
> But even before the Enterprise instance was installed, (which includes the
> browser service) I was not able to connect. I know I have other machines
> in
> my domain that are running SQL Express. Should I not be able to connect to
> them using my SSMS? What do I need to do to make that happen?
> I assume that I need membership in the group on the local machine that
> bears
> the SQL Express instance name?
> What else? I can't rely on the Browser service in those instances.
> Thanks
> --
> Todd Chittenden
>
> "Roger Wolter[MSFT]" wrote:
Connecting to remote Express instance using SSMS
I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
the default instance and two SQLExpress instances named 'sqlexpress' and
'officeservers', all withing SSMS, and can connect and manage them.
From my workstation, running SQL 2005 Dev, SP2, I can connect to the default
instance on that server, but cannot connect to the express instances. They
both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
I have done the following:
Made myself a member of the local Admin group
Tried using the sa account to connect
nothing seems to work. What am I missing?
TIA
--
Todd ChittendenIs the SQLBrowser Service enabled and started?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
> the default instance and two SQLExpress instances named 'sqlexpress' and
> 'officeservers', all withing SSMS, and can connect and manage them.
> From my workstation, running SQL 2005 Dev, SP2, I can connect to the
> default
> instance on that server, but cannot connect to the express instances. They
> both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
> I have done the following:
> Made myself a member of the local Admin group
> Tried using the sa account to connect
> nothing seems to work. What am I missing?
> TIA
> --
> Todd Chittenden
>|||I found it not long after posting the original question:
No, the Browser service was not running. Starting that up enabled me to
connect to it. So that was the answer.
But even before the Enterprise instance was installed, (which includes the
browser service) I was not able to connect. I know I have other machines in
my domain that are running SQL Express. Should I not be able to connect to
them using my SSMS? What do I need to do to make that happen?
I assume that I need membership in the group on the local machine that bears
the SQL Express instance name?
What else? I can't rely on the Browser service in those instances.
Thanks
--
Todd Chittenden
"Roger Wolter[MSFT]" wrote:
> Is the SQLBrowser Service enabled and started?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>|||SQL Express by default installs as a named instance. Unless you specify the
TCP/IP port in the connection string, you need the sqlbrowser service to
connect to a named instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:F4B981A2-4B9A-443E-855B-D53B89FCE511@.microsoft.com...[vbcol=seagreen]
>I found it not long after posting the original question:
> No, the Browser service was not running. Starting that up enabled me to
> connect to it. So that was the answer.
> But even before the Enterprise instance was installed, (which includes the
> browser service) I was not able to connect. I know I have other machines
> in
> my domain that are running SQL Express. Should I not be able to connect to
> them using my SSMS? What do I need to do to make that happen?
> I assume that I need membership in the group on the local machine that
> bears
> the SQL Express instance name?
> What else? I can't rely on the Browser service in those instances.
> Thanks
> --
> Todd Chittenden
>
> "Roger Wolter[MSFT]" wrote:
>
the default instance and two SQLExpress instances named 'sqlexpress' and
'officeservers', all withing SSMS, and can connect and manage them.
From my workstation, running SQL 2005 Dev, SP2, I can connect to the default
instance on that server, but cannot connect to the express instances. They
both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
I have done the following:
Made myself a member of the local Admin group
Tried using the sa account to connect
nothing seems to work. What am I missing?
TIA
--
Todd ChittendenIs the SQLBrowser Service enabled and started?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>I have a server running SQL Server 2005 Ent. Ed. SP2 and locally, I can see
> the default instance and two SQLExpress instances named 'sqlexpress' and
> 'officeservers', all withing SSMS, and can connect and manage them.
> From my workstation, running SQL 2005 Dev, SP2, I can connect to the
> default
> instance on that server, but cannot connect to the express instances. They
> both have TCP/IP protocol enabled, along with "Allow Remote COnnections".
> I have done the following:
> Made myself a member of the local Admin group
> Tried using the sa account to connect
> nothing seems to work. What am I missing?
> TIA
> --
> Todd Chittenden
>|||I found it not long after posting the original question:
No, the Browser service was not running. Starting that up enabled me to
connect to it. So that was the answer.
But even before the Enterprise instance was installed, (which includes the
browser service) I was not able to connect. I know I have other machines in
my domain that are running SQL Express. Should I not be able to connect to
them using my SSMS? What do I need to do to make that happen?
I assume that I need membership in the group on the local machine that bears
the SQL Express instance name?
What else? I can't rely on the Browser service in those instances.
Thanks
--
Todd Chittenden
"Roger Wolter[MSFT]" wrote:
> Is the SQLBrowser Service enabled and started?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:15C51BF6-FF25-435E-B26B-3863344FB81D@.microsoft.com...
>|||SQL Express by default installs as a named instance. Unless you specify the
TCP/IP port in the connection string, you need the sqlbrowser service to
connect to a named instance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:F4B981A2-4B9A-443E-855B-D53B89FCE511@.microsoft.com...[vbcol=seagreen]
>I found it not long after posting the original question:
> No, the Browser service was not running. Starting that up enabled me to
> connect to it. So that was the answer.
> But even before the Enterprise instance was installed, (which includes the
> browser service) I was not able to connect. I know I have other machines
> in
> my domain that are running SQL Express. Should I not be able to connect to
> them using my SSMS? What do I need to do to make that happen?
> I assume that I need membership in the group on the local machine that
> bears
> the SQL Express instance name?
> What else? I can't rely on the Browser service in those instances.
> Thanks
> --
> Todd Chittenden
>
> "Roger Wolter[MSFT]" wrote:
>
Monday, March 19, 2012
Connecting to named instance in Management Studio fails
I have a remote server with a default instance of Sql 2000 and a named
instance of Sql 2005. Whenever I try to connect to the named instance
in Management Studio, it ALWAYS connects to the default instance. I am
specifying the connection as ServerName\InstanceName. It seems like it
is ignoring the instance name in the connection request. I have tried
creating an alias, but it does not change the behavior. One special
note - due to our network structure we must use fully qualified domain
names when connecting to remote servers. The my local server and my
remote server are in different sub-domains:
local server = ServerA.HQ.domain.com
remote server = ServerX.Remote.domain.com
One interesting observation is that I am able to connect to the remote
server from a different db server in the same sub-domain without any
problems. i.e. ServerY.remote.domain.com can connect to
ServerX.remote.domain.com.
I know there was a bug in MDAC that was similar to this, but it was
supposedly fixed in MDAC 2.8 SP1. I wonder if the same bug crept into
the SQLNI code?check with your network admin. it seems there is firewall rule on udp 1434.
also, try to specify the tcp as part of your connection string.
e.g.
sqlcmd -S"ServerX.Remote.domain.com,tcp#"
-oj
"kmart" <kevmart@.gmail.com> wrote in message
news:1146849746.958318.194980@.v46g2000cwv.googlegroups.com...
>I have a remote server with a default instance of Sql 2000 and a named
> instance of Sql 2005. Whenever I try to connect to the named instance
> in Management Studio, it ALWAYS connects to the default instance. I am
> specifying the connection as ServerName\InstanceName. It seems like it
> is ignoring the instance name in the connection request. I have tried
> creating an alias, but it does not change the behavior. One special
> note - due to our network structure we must use fully qualified domain
> names when connecting to remote servers. The my local server and my
> remote server are in different sub-domains:
> local server = ServerA.HQ.domain.com
> remote server = ServerX.Remote.domain.com
> One interesting observation is that I am able to connect to the remote
> server from a different db server in the same sub-domain without any
> problems. i.e. ServerY.remote.domain.com can connect to
> ServerX.remote.domain.com.
> I know there was a bug in MDAC that was similar to this, but it was
> supposedly fixed in MDAC 2.8 SP1. I wonder if the same bug crept into
> the SQLNI code?
>
instance of Sql 2005. Whenever I try to connect to the named instance
in Management Studio, it ALWAYS connects to the default instance. I am
specifying the connection as ServerName\InstanceName. It seems like it
is ignoring the instance name in the connection request. I have tried
creating an alias, but it does not change the behavior. One special
note - due to our network structure we must use fully qualified domain
names when connecting to remote servers. The my local server and my
remote server are in different sub-domains:
local server = ServerA.HQ.domain.com
remote server = ServerX.Remote.domain.com
One interesting observation is that I am able to connect to the remote
server from a different db server in the same sub-domain without any
problems. i.e. ServerY.remote.domain.com can connect to
ServerX.remote.domain.com.
I know there was a bug in MDAC that was similar to this, but it was
supposedly fixed in MDAC 2.8 SP1. I wonder if the same bug crept into
the SQLNI code?check with your network admin. it seems there is firewall rule on udp 1434.
also, try to specify the tcp as part of your connection string.
e.g.
sqlcmd -S"ServerX.Remote.domain.com,tcp#"
-oj
"kmart" <kevmart@.gmail.com> wrote in message
news:1146849746.958318.194980@.v46g2000cwv.googlegroups.com...
>I have a remote server with a default instance of Sql 2000 and a named
> instance of Sql 2005. Whenever I try to connect to the named instance
> in Management Studio, it ALWAYS connects to the default instance. I am
> specifying the connection as ServerName\InstanceName. It seems like it
> is ignoring the instance name in the connection request. I have tried
> creating an alias, but it does not change the behavior. One special
> note - due to our network structure we must use fully qualified domain
> names when connecting to remote servers. The my local server and my
> remote server are in different sub-domains:
> local server = ServerA.HQ.domain.com
> remote server = ServerX.Remote.domain.com
> One interesting observation is that I am able to connect to the remote
> server from a different db server in the same sub-domain without any
> problems. i.e. ServerY.remote.domain.com can connect to
> ServerX.remote.domain.com.
> I know there was a bug in MDAC that was similar to this, but it was
> supposedly fixed in MDAC 2.8 SP1. I wonder if the same bug crept into
> the SQLNI code?
>
Connecting to named instance in Management Studio fails
I have a remote server with a default instance of Sql 2000 and a named
instance of Sql 2005. Whenever I try to connect to the named instance
in Management Studio, it ALWAYS connects to the default instance. I am
specifying the connection as ServerName\InstanceName. It seems like it
is ignoring the instance name in the connection request. I have tried
creating an alias, but it does not change the behavior. One special
note - due to our network structure we must use fully qualified domain
names when connecting to remote servers. The my local server and my
remote server are in different sub-domains:
local server = ServerA.HQ.domain.com
remote server = ServerX.Remote.domain.com
One interesting observation is that I am able to connect to the remote
server from a different db server in the same sub-domain without any
problems. i.e. ServerY.remote.domain.com can connect to
ServerX.remote.domain.com.
I know there was a bug in MDAC that was similar to this, but it was
supposedly fixed in MDAC 2.8 SP1. I wonder if the same bug crept into
the SQLNI code?check with your network admin. it seems there is firewall rule on udp 1434.
also, try to specify the tcp as part of your connection string.
e.g.
sqlcmd -S"ServerX.Remote.domain.com,tcp#"
-oj
"kmart" <kevmart@.gmail.com> wrote in message
news:1146849746.958318.194980@.v46g2000cwv.googlegroups.com...
>I have a remote server with a default instance of Sql 2000 and a named
> instance of Sql 2005. Whenever I try to connect to the named instance
> in Management Studio, it ALWAYS connects to the default instance. I am
> specifying the connection as ServerName\InstanceName. It seems like it
> is ignoring the instance name in the connection request. I have tried
> creating an alias, but it does not change the behavior. One special
> note - due to our network structure we must use fully qualified domain
> names when connecting to remote servers. The my local server and my
> remote server are in different sub-domains:
> local server = ServerA.HQ.domain.com
> remote server = ServerX.Remote.domain.com
> One interesting observation is that I am able to connect to the remote
> server from a different db server in the same sub-domain without any
> problems. i.e. ServerY.remote.domain.com can connect to
> ServerX.remote.domain.com.
> I know there was a bug in MDAC that was similar to this, but it was
> supposedly fixed in MDAC 2.8 SP1. I wonder if the same bug crept into
> the SQLNI code?
>
instance of Sql 2005. Whenever I try to connect to the named instance
in Management Studio, it ALWAYS connects to the default instance. I am
specifying the connection as ServerName\InstanceName. It seems like it
is ignoring the instance name in the connection request. I have tried
creating an alias, but it does not change the behavior. One special
note - due to our network structure we must use fully qualified domain
names when connecting to remote servers. The my local server and my
remote server are in different sub-domains:
local server = ServerA.HQ.domain.com
remote server = ServerX.Remote.domain.com
One interesting observation is that I am able to connect to the remote
server from a different db server in the same sub-domain without any
problems. i.e. ServerY.remote.domain.com can connect to
ServerX.remote.domain.com.
I know there was a bug in MDAC that was similar to this, but it was
supposedly fixed in MDAC 2.8 SP1. I wonder if the same bug crept into
the SQLNI code?check with your network admin. it seems there is firewall rule on udp 1434.
also, try to specify the tcp as part of your connection string.
e.g.
sqlcmd -S"ServerX.Remote.domain.com,tcp#"
-oj
"kmart" <kevmart@.gmail.com> wrote in message
news:1146849746.958318.194980@.v46g2000cwv.googlegroups.com...
>I have a remote server with a default instance of Sql 2000 and a named
> instance of Sql 2005. Whenever I try to connect to the named instance
> in Management Studio, it ALWAYS connects to the default instance. I am
> specifying the connection as ServerName\InstanceName. It seems like it
> is ignoring the instance name in the connection request. I have tried
> creating an alias, but it does not change the behavior. One special
> note - due to our network structure we must use fully qualified domain
> names when connecting to remote servers. The my local server and my
> remote server are in different sub-domains:
> local server = ServerA.HQ.domain.com
> remote server = ServerX.Remote.domain.com
> One interesting observation is that I am able to connect to the remote
> server from a different db server in the same sub-domain without any
> problems. i.e. ServerY.remote.domain.com can connect to
> ServerX.remote.domain.com.
> I know there was a bug in MDAC that was similar to this, but it was
> supposedly fixed in MDAC 2.8 SP1. I wonder if the same bug crept into
> the SQLNI code?
>
Connecting to MSSQL Server 2000 using JDBC
Hello,
I am developing an application which needs JDBC connection to a Named Instance of SQL Server 2000 (other than default instance). I am facing problem in this regard as my code gives an exception "Time Out" or "Connection Failed". However it works fine with the default named instance...The main driver class is "com.microsoft.sqlserver.jdbc.SQLServerDriver"
and Connection URL is
"jdbc: sqlserver://localhost;instanceName=Testing:1432;databaseName=testDB;"
It gives the exception that Unable to connect to named instance....
Please if any one have any knowledge in this regard....do let me know....I have read an article on MSDN according to which on MS SQL Server 2000 the named Instance other than default can be accessed only through named pipes.
http://msdn2.microsoft.com/en-us/library/aa224779(sql.80).aspx
if some body can tell me Connection string for JDBC driver which utilizes named pipes...the it will be very help full for me.........any help from MSDN Experts is really appreciated...
Thanks
Muhammad:
The JDBC driver does connect to named instances but doesn't use Named Pipes -- only TCP/IP. In your case it looks like you're overloading the instanceName property in the connection string by putting the instanceName and portNumber together. Try breaking them apart like this:
"jdbc: sqlserver://localhost;instanceName=Testing;portNumber=1432;databaseName=testDB;"
If that still doesn't work let me know.
-shelby
|||Thanks Shelby for your quick reply.....I have tried the string you have given and it is given the same error..
"com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:193)
at com.frt.test.DatabaseTest.main(DatabaseTest.java:21)"
One thing i want to discuss with you is that when I go to "Server Network utility" the value written in default port for the second instance in "TCP/IP" is 0..why it is so? I have chaged it to 1432 myself and have restarted the server again....My code is as follows (however it is very simple but I am sending it)
import java.sql.*;
public class DatabaseTest
{
public static void main(String[] args)
{
try
{
java.lang.Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
// Class.forName("com.inet.tds.TdsDriver");
Connection c = java.sql.DriverManager.getConnection( "jdbc
qlserver://localhost;instanceName=TESTING;portNumber=1432;databaseName=testDB;" );
// Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost/pipe/MSSQL$TESTING/sql/query?database=testDB&user=sa&password=abc");
// Connection c = DriverManager.getConnection("jdbc:inetdae7://localhost/pipe/MSSQL$TESTING/sql/query?database=CMS&user=sa&password=abc");
Statement s = c.createStatement();
ResultSet r =s.executeQuery("select * from table1");
while(r.next())
{
System.out.println(r.getString(2));
}
System.out.println( "Connected!" );
}
catch( Exception ex )
{
ex.printStackTrace();
}
}
}
as you can see that in connection statement I have commented out some lines....that is connection string of some other driver I have downloaded for testing...and when I use it with piped names it connects perfectly to named instance...but I have to use the first driver....so please help me...
Thanks for your coordination
bye
|||At last i got the solution.....All the problem is due to cause that Service Pack 3.0 for SQL Server 2000 was not installed on my machine...as it is a bug in SQL Server 2000, that it does not listen on TCP/IP port, as u can verify by using TELNET, however this bug has been removed in SP3
Regards
Muhammad Ummar
Connecting to MSSQL Server 2000 using JDBC
Hello,
I am developing an application which needs JDBC connection to a Named Instance of SQL Server 2000 (other than default instance). I am facing problem in this regard as my code gives an exception "Time Out" or "Connection Failed". However it works fine with the default named instance...The main driver class is "com.microsoft.sqlserver.jdbc.SQLServerDriver"
and Connection URL is
"jdbc: sqlserver://localhost;instanceName=Testing:1432;databaseName=testDB;"
It gives the exception that Unable to connect to named instance....
Please if any one have any knowledge in this regard....do let me know....I have read an article on MSDN according to which on MS SQL Server 2000 the named Instance other than default can be accessed only through named pipes.
http://msdn2.microsoft.com/en-us/library/aa224779(sql.80).aspx
if some body can tell me Connection string for JDBC driver which utilizes named pipes...the it will be very help full for me.........any help from MSDN Experts is really appreciated...
Thanks
Muhammad:
The JDBC driver does connect to named instances but doesn't use Named Pipes -- only TCP/IP. In your case it looks like you're overloading the instanceName property in the connection string by putting the instanceName and portNumber together. Try breaking them apart like this:
"jdbc: sqlserver://localhost;instanceName=Testing;portNumber=1432;databaseName=testDB;"
If that still doesn't work let me know.
-shelby
|||Thanks Shelby for your quick reply.....I have tried the string you have given and it is given the same error..
"com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:193)
at com.frt.test.DatabaseTest.main(DatabaseTest.java:21)"
One thing i want to discuss with you is that when I go to "Server Network utility" the value written in default port for the second instance in "TCP/IP" is 0..why it is so? I have chaged it to 1432 myself and have restarted the server again....My code is as follows (however it is very simple but I am sending it)
import java.sql.*;
public class DatabaseTest
{
public static void main(String[] args)
{
try
{
java.lang.Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
// Class.forName("com.inet.tds.TdsDriver");
Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost;instanceName=TESTING;portNumber=1432;databaseName=testDB;" );
// Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost/pipe/MSSQL$TESTING/sql/query?database=testDB&user=sa&password=abc");
// Connection c = DriverManager.getConnection("jdbc:inetdae7://localhost/pipe/MSSQL$TESTING/sql/query?database=CMS&user=sa&password=abc");
Statement s = c.createStatement();
ResultSet r =s.executeQuery("select * from table1");
while(r.next())
{
System.out.println(r.getString(2));
}
System.out.println( "Connected!" );
}
catch( Exception ex )
{
ex.printStackTrace();
}
}
}
as you can see that in connection statement I have commented out some lines....that is connection string of some other driver I have downloaded for testing...and when I use it with piped names it connects perfectly to named instance...but I have to use the first driver....so please help me...
Thanks for your coordination
bye
|||At last i got the solution.....All the problem is due to cause that Service Pack 3.0 for SQL Server 2000 was not installed on my machine...as it is a bug in SQL Server 2000, that it does not listen on TCP/IP port, as u can verify by using TELNET, however this bug has been removed in SP3
Regards
Muhammad Ummar
Thursday, March 8, 2012
Connecting to an instance of SQL
I have a SQL2000 instance that when I attempt to connect to it by
default\NamedInstance it connects me to the default instance on that server
instead. If I connect with (local)\NamedInstance it connects me to the named
instance.
In addition when I attempt to start the agent for the named instance I get
the below:
SQLServerAgent cannot start because the instance of the server (MSSQLSERVER)
is not the expected instance
thanks for your your helpI'd start by checking for aliases in Client Network Utility.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:6743587B-BC99-498B-8E1D-519B98C7380C@.microsoft.com...
>I have a SQL2000 instance that when I attempt to connect to it by
> default\NamedInstance it connects me to the default instance on that server
> instead. If I connect with (local)\NamedInstance it connects me to the named
> instance.
> In addition when I attempt to start the agent for the named instance I get
> the below:
> SQLServerAgent cannot start because the instance of the server (MSSQLSERVER)
> is not the expected instance
> thanks for your your help|||It was the correct alias but the port was wrong. I changed and it seems to
be working fine.
What do you think the cause was?
thanks for your help.
"Tibor Karaszi" wrote:
> I'd start by checking for aliases in Client Network Utility.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:6743587B-BC99-498B-8E1D-519B98C7380C@.microsoft.com...
> >I have a SQL2000 instance that when I attempt to connect to it by
> > default\NamedInstance it connects me to the default instance on that server
> > instead. If I connect with (local)\NamedInstance it connects me to the named
> > instance.
> >
> > In addition when I attempt to start the agent for the named instance I get
> > the below:
> > SQLServerAgent cannot start because the instance of the server (MSSQLSERVER)
> > is not the expected instance
> >
> > thanks for your your help
>
>
default\NamedInstance it connects me to the default instance on that server
instead. If I connect with (local)\NamedInstance it connects me to the named
instance.
In addition when I attempt to start the agent for the named instance I get
the below:
SQLServerAgent cannot start because the instance of the server (MSSQLSERVER)
is not the expected instance
thanks for your your helpI'd start by checking for aliases in Client Network Utility.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:6743587B-BC99-498B-8E1D-519B98C7380C@.microsoft.com...
>I have a SQL2000 instance that when I attempt to connect to it by
> default\NamedInstance it connects me to the default instance on that server
> instead. If I connect with (local)\NamedInstance it connects me to the named
> instance.
> In addition when I attempt to start the agent for the named instance I get
> the below:
> SQLServerAgent cannot start because the instance of the server (MSSQLSERVER)
> is not the expected instance
> thanks for your your help|||It was the correct alias but the port was wrong. I changed and it seems to
be working fine.
What do you think the cause was?
thanks for your help.
"Tibor Karaszi" wrote:
> I'd start by checking for aliases in Client Network Utility.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:6743587B-BC99-498B-8E1D-519B98C7380C@.microsoft.com...
> >I have a SQL2000 instance that when I attempt to connect to it by
> > default\NamedInstance it connects me to the default instance on that server
> > instead. If I connect with (local)\NamedInstance it connects me to the named
> > instance.
> >
> > In addition when I attempt to start the agent for the named instance I get
> > the below:
> > SQLServerAgent cannot start because the instance of the server (MSSQLSERVER)
> > is not the expected instance
> >
> > thanks for your your help
>
>
Connecting to an instance of SQL
I have a SQL2000 instance that when I attempt to connect to it by
default\NamedInstance it connects me to the default instance on that server
instead. If I connect with (local)\NamedInstance it connects me to the name
d
instance.
In addition when I attempt to start the agent for the named instance I get
the below:
SQLServerAgent cannot start because the instance of the server (MSSQLSERVER)
is not the expected instance
thanks for your your helpI'd start by checking for aliases in Client Network Utility.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:6743587B-BC99-498B-8E1D-519B98C7380C@.microsoft.com...
>I have a SQL2000 instance that when I attempt to connect to it by
> default\NamedInstance it connects me to the default instance on that serve
r
> instead. If I connect with (local)\NamedInstance it connects me to the na
med
> instance.
> In addition when I attempt to start the agent for the named instance I get
> the below:
> SQLServerAgent cannot start because the instance of the server (MSSQLSERVE
R)
> is not the expected instance
> thanks for your your help|||It was the correct alias but the port was wrong. I changed and it seems to
be working fine.
What do you think the cause was?
thanks for your help.
"Tibor Karaszi" wrote:
> I'd start by checking for aliases in Client Network Utility.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:6743587B-BC99-498B-8E1D-519B98C7380C@.microsoft.com...
>
>
default\NamedInstance it connects me to the default instance on that server
instead. If I connect with (local)\NamedInstance it connects me to the name
d
instance.
In addition when I attempt to start the agent for the named instance I get
the below:
SQLServerAgent cannot start because the instance of the server (MSSQLSERVER)
is not the expected instance
thanks for your your helpI'd start by checking for aliases in Client Network Utility.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RomM" <RomM@.discussions.microsoft.com> wrote in message
news:6743587B-BC99-498B-8E1D-519B98C7380C@.microsoft.com...
>I have a SQL2000 instance that when I attempt to connect to it by
> default\NamedInstance it connects me to the default instance on that serve
r
> instead. If I connect with (local)\NamedInstance it connects me to the na
med
> instance.
> In addition when I attempt to start the agent for the named instance I get
> the below:
> SQLServerAgent cannot start because the instance of the server (MSSQLSERVE
R)
> is not the expected instance
> thanks for your your help|||It was the correct alias but the port was wrong. I changed and it seems to
be working fine.
What do you think the cause was?
thanks for your help.
"Tibor Karaszi" wrote:
> I'd start by checking for aliases in Client Network Utility.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RomM" <RomM@.discussions.microsoft.com> wrote in message
> news:6743587B-BC99-498B-8E1D-519B98C7380C@.microsoft.com...
>
>
Connecting to a SQL Server 2005 Instance
I completed a default install that uses the server name: Server1
and completed an instance install on Server1 called Server1test with
the same users, etc.
I can connect remotely to Server1 using SQL Server 2005 Management
Studio, but when I try to connect to Server1\Server1test I get:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (Provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) (Microsoft SQL Server)
If I can connect to Server1 using the SA account why is it not allowing
the named instance (i.e., Server1\Server1test) to connect? While at
Server1 I can connect to Server1\Server1test using the SA account.
Thanks for any suggestions.
RBollinger
Let's see your ConnectString.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"robboll" <robboll@.hotmail.com> wrote in message
news:1169744212.563385.41730@.v33g2000cwv.googlegro ups.com...
>I completed a default install that uses the server name: Server1
> and completed an instance install on Server1 called Server1test with
> the same users, etc.
> I can connect remotely to Server1 using SQL Server 2005 Management
> Studio, but when I try to connect to Server1\Server1test I get:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (Provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) (Microsoft SQL Server)
> If I can connect to Server1 using the SA account why is it not allowing
> the named instance (i.e., Server1\Server1test) to connect? While at
> Server1 I can connect to Server1\Server1test using the SA account.
> Thanks for any suggestions.
> RBollinger
>
|||When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "robboll" <robb...@.hotmail.com> wrote in messagenews:1169744212.563385.41730@.v33g2000cwv.go oglegroups.com...
>
>
>
>
|||While these aren't ConnectString strings, they do tell me that you're using
SA to access the servers. This is not a good idea, but it's not why you
aren't getting connected. When you install SQL Server it does not support
SQL Server authentication unless it was specifically requested in the
interactive setup process. Each instance must be so configured. The SQL
Server Surface Area Configuration tools can help adjust these settings.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"robboll" <robboll@.hotmail.com> wrote in message
news:1169751617.893553.179310@.v33g2000cwv.googlegr oups.com...
When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "robboll" <robb...@.hotmail.com> wrote in
> messagenews:1169744212.563385.41730@.v33g2000cwv.go oglegroups.com...
>
>
>
>
and completed an instance install on Server1 called Server1test with
the same users, etc.
I can connect remotely to Server1 using SQL Server 2005 Management
Studio, but when I try to connect to Server1\Server1test I get:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (Provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) (Microsoft SQL Server)
If I can connect to Server1 using the SA account why is it not allowing
the named instance (i.e., Server1\Server1test) to connect? While at
Server1 I can connect to Server1\Server1test using the SA account.
Thanks for any suggestions.
RBollinger
Let's see your ConnectString.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"robboll" <robboll@.hotmail.com> wrote in message
news:1169744212.563385.41730@.v33g2000cwv.googlegro ups.com...
>I completed a default install that uses the server name: Server1
> and completed an instance install on Server1 called Server1test with
> the same users, etc.
> I can connect remotely to Server1 using SQL Server 2005 Management
> Studio, but when I try to connect to Server1\Server1test I get:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (Provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) (Microsoft SQL Server)
> If I can connect to Server1 using the SA account why is it not allowing
> the named instance (i.e., Server1\Server1test) to connect? While at
> Server1 I can connect to Server1\Server1test using the SA account.
> Thanks for any suggestions.
> RBollinger
>
|||When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "robboll" <robb...@.hotmail.com> wrote in messagenews:1169744212.563385.41730@.v33g2000cwv.go oglegroups.com...
>
>
>
>
|||While these aren't ConnectString strings, they do tell me that you're using
SA to access the servers. This is not a good idea, but it's not why you
aren't getting connected. When you install SQL Server it does not support
SQL Server authentication unless it was specifically requested in the
interactive setup process. Each instance must be so configured. The SQL
Server Surface Area Configuration tools can help adjust these settings.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"robboll" <robboll@.hotmail.com> wrote in message
news:1169751617.893553.179310@.v33g2000cwv.googlegr oups.com...
When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "robboll" <robb...@.hotmail.com> wrote in
> messagenews:1169744212.563385.41730@.v33g2000cwv.go oglegroups.com...
>
>
>
>
Connecting to a SQL Server 2005 Instance
I completed a default install that uses the server name: Server1
and completed an instance install on Server1 called Server1test with
the same users, etc.
I can connect remotely to Server1 using SQL Server 2005 Management
Studio, but when I try to connect to Server1\Server1test I get:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (Provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) (Microsoft SQL Server)
If I can connect to Server1 using the SA account why is it not allowing
the named instance (i.e., Server1\Server1test) to connect? While at
Server1 I can connect to Server1\Server1test using the SA account.
Thanks for any suggestions.
RBollingerLet's see your ConnectString.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"robboll" <robboll@.hotmail.com> wrote in message
news:1169744212.563385.41730@.v33g2000cwv.googlegroups.com...
>I completed a default install that uses the server name: Server1
> and completed an instance install on Server1 called Server1test with
> the same users, etc.
> I can connect remotely to Server1 using SQL Server 2005 Management
> Studio, but when I try to connect to Server1\Server1test I get:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (Provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) (Microsoft SQL Server)
> If I can connect to Server1 using the SA account why is it not allowing
> the named instance (i.e., Server1\Server1test) to connect? While at
> Server1 I can connect to Server1\Server1test using the SA account.
> Thanks for any suggestions.
> RBollinger
>|||When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no right=
s=2E
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----=
--=AD---
> "robboll" <robb...@.hotmail.com> wrote in messagenews:1169744212.563385.41=
730@.v33g2000cwv.googlegroups.com...[vbcol=seagreen]
>
>
>
>
>
>
>=20|||While these aren't ConnectString strings, they do tell me that you're using
SA to access the servers. This is not a good idea, but it's not why you
aren't getting connected. When you install SQL Server it does not support
SQL Server authentication unless it was specifically requested in the
interactive setup process. Each instance must be so configured. The SQL
Server Surface Area Configuration tools can help adjust these settings.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"robboll" <robboll@.hotmail.com> wrote in message
news:1169751617.893553.179310@.v33g2000cwv.googlegroups.com...
When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
-_---
> "robboll" <robb...@.hotmail.com> wrote in
> messagenews:1169744212.563385.41730@.v33g2000cwv.googlegroups.com...
>
>
>
>
>
>
>
and completed an instance install on Server1 called Server1test with
the same users, etc.
I can connect remotely to Server1 using SQL Server 2005 Management
Studio, but when I try to connect to Server1\Server1test I get:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (Provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) (Microsoft SQL Server)
If I can connect to Server1 using the SA account why is it not allowing
the named instance (i.e., Server1\Server1test) to connect? While at
Server1 I can connect to Server1\Server1test using the SA account.
Thanks for any suggestions.
RBollingerLet's see your ConnectString.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"robboll" <robboll@.hotmail.com> wrote in message
news:1169744212.563385.41730@.v33g2000cwv.googlegroups.com...
>I completed a default install that uses the server name: Server1
> and completed an instance install on Server1 called Server1test with
> the same users, etc.
> I can connect remotely to Server1 using SQL Server 2005 Management
> Studio, but when I try to connect to Server1\Server1test I get:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (Provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) (Microsoft SQL Server)
> If I can connect to Server1 using the SA account why is it not allowing
> the named instance (i.e., Server1\Server1test) to connect? While at
> Server1 I can connect to Server1\Server1test using the SA account.
> Thanks for any suggestions.
> RBollinger
>|||When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no right=
s=2E
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----=
--=AD---
> "robboll" <robb...@.hotmail.com> wrote in messagenews:1169744212.563385.41=
730@.v33g2000cwv.googlegroups.com...[vbcol=seagreen]
>
>
>
>
>
>
>=20|||While these aren't ConnectString strings, they do tell me that you're using
SA to access the servers. This is not a good idea, but it's not why you
aren't getting connected. When you install SQL Server it does not support
SQL Server authentication unless it was specifically requested in the
interactive setup process. Each instance must be so configured. The SQL
Server Surface Area Configuration tools can help adjust these settings.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"robboll" <robboll@.hotmail.com> wrote in message
news:1169751617.893553.179310@.v33g2000cwv.googlegroups.com...
When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
-_---
> "robboll" <robb...@.hotmail.com> wrote in
> messagenews:1169744212.563385.41730@.v33g2000cwv.googlegroups.com...
>
>
>
>
>
>
>
Wednesday, March 7, 2012
Connecting to a default installation of MSDE on XP
On PC that has a default installation of MSDE and XP SP2, what ports
need to be open to allow remote connection to the MSDE?
hi,
mark@.sherwood.org.uk wrote:
> On PC that has a default installation of MSDE and XP SP2, what ports
> need to be open to allow remote connection to the MSDE?
a default instance requires TCP/IP 1433 port, while for named instance (with
dynamic port assignement) you have to check the assigned port using the
Server Network Utiliy (svrnetcn.exe) or inspecting the registry, say
HKLM\SOFTWARE\Microsoft\Microsoft SQL
Server\InstanceName\MSSQLServer\SuperSocketNetLib\ Tcp
TcpPort
and of course UDP 1434...
you can close UDP 1434 assigning a static port to the named instance, but
that requires definig server alias on the Client Network Utility
(cliconfg.exe) on each remote client...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I have temporariliy stopped the XP Firewall on the remote PC. Created
an alias in the Client Network Utility on the pc trying to connect to
the MSDE but the registration still fails it shows the following error
message.
SQL Server does not exist or access is denied,
ConnectionOpen(Connect()).
I am a memebr of the sys admin role and I have also tried as the SA
account both display the same error message.
|||hi,
mark@.sherwood.org.uk wrote:
> I have temporariliy stopped the XP Firewall on the remote PC. Created
> an alias in the Client Network Utility on the pc trying to connect to
> the MSDE but the registration still fails it shows the following error
> message.
> SQL Server does not exist or access is denied,
> ConnectionOpen(Connect()).
> I am a memebr of the sys admin role and I have also tried as the SA
> account both display the same error message.
this is a general MDAC message indicatin connection problem(s)... please
have a look at
http://support.microsoft.com/default...06&Product=sql
http://support.microsoft.com/default.aspx?kbid=841249
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
need to be open to allow remote connection to the MSDE?
hi,
mark@.sherwood.org.uk wrote:
> On PC that has a default installation of MSDE and XP SP2, what ports
> need to be open to allow remote connection to the MSDE?
a default instance requires TCP/IP 1433 port, while for named instance (with
dynamic port assignement) you have to check the assigned port using the
Server Network Utiliy (svrnetcn.exe) or inspecting the registry, say
HKLM\SOFTWARE\Microsoft\Microsoft SQL
Server\InstanceName\MSSQLServer\SuperSocketNetLib\ Tcp
TcpPort
and of course UDP 1434...
you can close UDP 1434 assigning a static port to the named instance, but
that requires definig server alias on the Client Network Utility
(cliconfg.exe) on each remote client...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I have temporariliy stopped the XP Firewall on the remote PC. Created
an alias in the Client Network Utility on the pc trying to connect to
the MSDE but the registration still fails it shows the following error
message.
SQL Server does not exist or access is denied,
ConnectionOpen(Connect()).
I am a memebr of the sys admin role and I have also tried as the SA
account both display the same error message.
|||hi,
mark@.sherwood.org.uk wrote:
> I have temporariliy stopped the XP Firewall on the remote PC. Created
> an alias in the Client Network Utility on the pc trying to connect to
> the MSDE but the registration still fails it shows the following error
> message.
> SQL Server does not exist or access is denied,
> ConnectionOpen(Connect()).
> I am a memebr of the sys admin role and I have also tried as the SA
> account both display the same error message.
this is a general MDAC message indicatin connection problem(s)... please
have a look at
http://support.microsoft.com/default...06&Product=sql
http://support.microsoft.com/default.aspx?kbid=841249
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Sunday, February 19, 2012
Connecting from Powerbuilder through ole db, column defaults n
I am using PB 9.0.1 Build 7236. I am using a datawindow to do an Insert.
The column in question should default to zero. It is NOT included as an
updateable column in the datawindow. But PowerBuilder is including it in the
Insert syntax and erroring as the default of zero is not being recognized.
When I use the PB Native MS SQL Server interface, the column default is
recognized. In Enterprise Manager, the default of zero shows on the
in-question column.
I have asked in PB groups but have only got one reply that states that this
is the way the OLE DB interface has always worked. Is this a peculiarity to
OLE DB? Or do you think it is a PB problem?
"Scott Morris" wrote:
> You need to be much more specific. What does "not recognizing column
> defaults" mean? What problems? Are you using datawindows. If so, the only
> time columns are included in insert statements is when your application code
> sets the value of that column. Is that what you are doing? If your code
> (and user) do not provide a value, then it is left out of the inserted
> column "list". In this case, the default is used. You also failed to
> specify which version of Powerbuilder. At the very least, you should review
> the fix list for the most current build of the version you are using to
> determine if there is a bug that has already been fixed.
> Incidentally, you are more likely to get help posting in a powerbuilder NG.
> The more information you post, the more likely someone will help.
> "roz" <roz@.discussions.microsoft.com> wrote in message
> news:091BC8BE-02BE-41AE-8F5C-07621F96BFA5@.microsoft.com...
> problems,
>
>
Sounds to me like your code (or perhaps in an ancestor class) is doing
something unexpected. If the column is not included the the list of
updateable columns, then it will not be included as a column in the insert
list. I've never had a problem with PB in this respect. I suspect that
something else is occurring that is causing the behavior. You may want to
use the preview pane in the dw painter to insert a row (if possible); this
would at least identify PB or your code as the problem. The next step would
be to create a simple test case using the existing datawindow (and without
any of your ancestor/application code). If these succeed, then the problem
lies with the application code. Although I haven't moved beyond PB9 6533, I
suspect that application code or design is the problem. It also may be
related to some odd cirucumstances (sharedata, linkage, get/setfullstate,
etc.). You may also want to investigate whether some piece of code is
dynamically modifying the datawindow and (implicitly or explicitly) setting
the update properties.
Be aware that the default you define in the table and the default you define
within PB are different "things" entirely. PB will not automatically update
a default defined in the datawindow to be consistent with any table-level
default. This should not be an issue in this particular situation.
One last thing: The phrase "... syntax and erroring as the default of zero
is not being recognized" is not particularly meaningful. I will repeat.
Help is much likely to be more "helpful" and more forthcoming if you
accurately and completely describe the problem. By itself, a default (or
lack thereof) will not produce an error when a row is inserted into a table.
Perhaps there is a constraint or trigger that enforces the use of a specific
domain of values for a column, producing the error. It helps to know the
exact text of the error. I'm still not certain what "...the column default
is recognized." means but I'm not certain it matters at this point.
"roz" <roz@.discussions.microsoft.com> wrote in message
news:D6DE6DA2-C749-4546-B121-D097E2C3A25F@.microsoft.com...
> I am using PB 9.0.1 Build 7236. I am using a datawindow to do an Insert.
> The column in question should default to zero. It is NOT included as an
> updateable column in the datawindow. But PowerBuilder is including it in
the
> Insert syntax and erroring as the default of zero is not being recognized.
> When I use the PB Native MS SQL Server interface, the column default is
> recognized. In Enterprise Manager, the default of zero shows on the
> in-question column.
> I have asked in PB groups but have only got one reply that states that
this
> is the way the OLE DB interface has always worked. Is this a peculiarity
to[vbcol=seagreen]
> OLE DB? Or do you think it is a PB problem?
>
> "Scott Morris" wrote:
only[vbcol=seagreen]
code[vbcol=seagreen]
code[vbcol=seagreen]
review[vbcol=seagreen]
NG.[vbcol=seagreen]
2000.[vbcol=seagreen]
out?[vbcol=seagreen]
|||I am trying to do an insert on a table using a datawindow. The dw does not
use the
column 'dues_paid_lifetime' in the select (I searched the source!). Thus
there is no initial value to set to zero. It is not in the select so there
is no column to remove from the update list.. I set the dbparm to
DisableParm = 1. Still on the insert I get the error:
SQLSTATE = 23000
Microsoft OLE DB Provider for SQL Server
Cannot insert the value NULL into column "DUES_PAID_LIFETIME'; column does
not allow nulls. INSERT fails.
The insert syntax also displays in the error message. The column
"DUES_PAID_LIFETIME' is not in the sql syntax.
This does not happen with Oracle or with the Native MSSQL Server interface
sent with Powerbuilder.
Is this a SQLOLEDB bug?
"Scott Morris" wrote:
> Sounds to me like your code (or perhaps in an ancestor class) is doing
> something unexpected. If the column is not included the the list of
> updateable columns, then it will not be included as a column in the insert
> list. I've never had a problem with PB in this respect. I suspect that
> something else is occurring that is causing the behavior. You may want to
> use the preview pane in the dw painter to insert a row (if possible); this
> would at least identify PB or your code as the problem. The next step would
> be to create a simple test case using the existing datawindow (and without
> any of your ancestor/application code). If these succeed, then the problem
> lies with the application code. Although I haven't moved beyond PB9 6533, I
> suspect that application code or design is the problem. It also may be
> related to some odd cirucumstances (sharedata, linkage, get/setfullstate,
> etc.). You may also want to investigate whether some piece of code is
> dynamically modifying the datawindow and (implicitly or explicitly) setting
> the update properties.
> Be aware that the default you define in the table and the default you define
> within PB are different "things" entirely. PB will not automatically update
> a default defined in the datawindow to be consistent with any table-level
> default. This should not be an issue in this particular situation.
> One last thing: The phrase "... syntax and erroring as the default of zero
> is not being recognized" is not particularly meaningful. I will repeat.
> Help is much likely to be more "helpful" and more forthcoming if you
> accurately and completely describe the problem. By itself, a default (or
> lack thereof) will not produce an error when a row is inserted into a table.
> Perhaps there is a constraint or trigger that enforces the use of a specific
> domain of values for a column, producing the error. It helps to know the
> exact text of the error. I'm still not certain what "...the column default
> is recognized." means but I'm not certain it matters at this point.
> "roz" <roz@.discussions.microsoft.com> wrote in message
> news:D6DE6DA2-C749-4546-B121-D097E2C3A25F@.microsoft.com...
> the
> this
> to
> only
> code
> code
> review
> NG.
> 2000.
> out?
>
>
|||It looks to me like there is some other problem. The insert statement
generated by PB does not include the column in the insert list. If this is
true (and you can verify this using profiler - just to be absolutely
certain), then the default constraint you think is present isn't or the
default constraint is not enabled. Another possibility is a mal-functioning
trigger
Based on your information, you should be able to take the insert statement
from the error message, paste it into QA, and generate the error yourself by
executing the statement.
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:E5F27698-9BC8-4909-919E-63F11B9AF80E@.microsoft.com...
> I am trying to do an insert on a table using a datawindow. The dw does
not
> use the
> column 'dues_paid_lifetime' in the select (I searched the source!). Thus
> there is no initial value to set to zero. It is not in the select so
there[vbcol=seagreen]
> is no column to remove from the update list.. I set the dbparm to
> DisableParm = 1. Still on the insert I get the error:
> SQLSTATE = 23000
> Microsoft OLE DB Provider for SQL Server
> Cannot insert the value NULL into column "DUES_PAID_LIFETIME'; column does
> not allow nulls. INSERT fails.
> The insert syntax also displays in the error message. The column
> "DUES_PAID_LIFETIME' is not in the sql syntax.
> This does not happen with Oracle or with the Native MSSQL Server interface
> sent with Powerbuilder.
> Is this a SQLOLEDB bug?
>
> "Scott Morris" wrote:
insert[vbcol=seagreen]
that[vbcol=seagreen]
to[vbcol=seagreen]
this[vbcol=seagreen]
would[vbcol=seagreen]
without[vbcol=seagreen]
problem[vbcol=seagreen]
6533, I[vbcol=seagreen]
get/setfullstate,[vbcol=seagreen]
setting[vbcol=seagreen]
define[vbcol=seagreen]
update[vbcol=seagreen]
table-level[vbcol=seagreen]
zero[vbcol=seagreen]
table.[vbcol=seagreen]
specific[vbcol=seagreen]
default[vbcol=seagreen]
Insert.[vbcol=seagreen]
an[vbcol=seagreen]
in[vbcol=seagreen]
recognized.[vbcol=seagreen]
is[vbcol=seagreen]
peculiarity[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
application[vbcol=seagreen]
inserted[vbcol=seagreen]
to[vbcol=seagreen]
should[vbcol=seagreen]
to[vbcol=seagreen]
powerbuilder[vbcol=seagreen]
me[vbcol=seagreen]
The column in question should default to zero. It is NOT included as an
updateable column in the datawindow. But PowerBuilder is including it in the
Insert syntax and erroring as the default of zero is not being recognized.
When I use the PB Native MS SQL Server interface, the column default is
recognized. In Enterprise Manager, the default of zero shows on the
in-question column.
I have asked in PB groups but have only got one reply that states that this
is the way the OLE DB interface has always worked. Is this a peculiarity to
OLE DB? Or do you think it is a PB problem?
"Scott Morris" wrote:
> You need to be much more specific. What does "not recognizing column
> defaults" mean? What problems? Are you using datawindows. If so, the only
> time columns are included in insert statements is when your application code
> sets the value of that column. Is that what you are doing? If your code
> (and user) do not provide a value, then it is left out of the inserted
> column "list". In this case, the default is used. You also failed to
> specify which version of Powerbuilder. At the very least, you should review
> the fix list for the most current build of the version you are using to
> determine if there is a bug that has already been fixed.
> Incidentally, you are more likely to get help posting in a powerbuilder NG.
> The more information you post, the more likely someone will help.
> "roz" <roz@.discussions.microsoft.com> wrote in message
> news:091BC8BE-02BE-41AE-8F5C-07621F96BFA5@.microsoft.com...
> problems,
>
>
Sounds to me like your code (or perhaps in an ancestor class) is doing
something unexpected. If the column is not included the the list of
updateable columns, then it will not be included as a column in the insert
list. I've never had a problem with PB in this respect. I suspect that
something else is occurring that is causing the behavior. You may want to
use the preview pane in the dw painter to insert a row (if possible); this
would at least identify PB or your code as the problem. The next step would
be to create a simple test case using the existing datawindow (and without
any of your ancestor/application code). If these succeed, then the problem
lies with the application code. Although I haven't moved beyond PB9 6533, I
suspect that application code or design is the problem. It also may be
related to some odd cirucumstances (sharedata, linkage, get/setfullstate,
etc.). You may also want to investigate whether some piece of code is
dynamically modifying the datawindow and (implicitly or explicitly) setting
the update properties.
Be aware that the default you define in the table and the default you define
within PB are different "things" entirely. PB will not automatically update
a default defined in the datawindow to be consistent with any table-level
default. This should not be an issue in this particular situation.
One last thing: The phrase "... syntax and erroring as the default of zero
is not being recognized" is not particularly meaningful. I will repeat.
Help is much likely to be more "helpful" and more forthcoming if you
accurately and completely describe the problem. By itself, a default (or
lack thereof) will not produce an error when a row is inserted into a table.
Perhaps there is a constraint or trigger that enforces the use of a specific
domain of values for a column, producing the error. It helps to know the
exact text of the error. I'm still not certain what "...the column default
is recognized." means but I'm not certain it matters at this point.
"roz" <roz@.discussions.microsoft.com> wrote in message
news:D6DE6DA2-C749-4546-B121-D097E2C3A25F@.microsoft.com...
> I am using PB 9.0.1 Build 7236. I am using a datawindow to do an Insert.
> The column in question should default to zero. It is NOT included as an
> updateable column in the datawindow. But PowerBuilder is including it in
the
> Insert syntax and erroring as the default of zero is not being recognized.
> When I use the PB Native MS SQL Server interface, the column default is
> recognized. In Enterprise Manager, the default of zero shows on the
> in-question column.
> I have asked in PB groups but have only got one reply that states that
this
> is the way the OLE DB interface has always worked. Is this a peculiarity
to[vbcol=seagreen]
> OLE DB? Or do you think it is a PB problem?
>
> "Scott Morris" wrote:
only[vbcol=seagreen]
code[vbcol=seagreen]
code[vbcol=seagreen]
review[vbcol=seagreen]
NG.[vbcol=seagreen]
2000.[vbcol=seagreen]
out?[vbcol=seagreen]
|||I am trying to do an insert on a table using a datawindow. The dw does not
use the
column 'dues_paid_lifetime' in the select (I searched the source!). Thus
there is no initial value to set to zero. It is not in the select so there
is no column to remove from the update list.. I set the dbparm to
DisableParm = 1. Still on the insert I get the error:
SQLSTATE = 23000
Microsoft OLE DB Provider for SQL Server
Cannot insert the value NULL into column "DUES_PAID_LIFETIME'; column does
not allow nulls. INSERT fails.
The insert syntax also displays in the error message. The column
"DUES_PAID_LIFETIME' is not in the sql syntax.
This does not happen with Oracle or with the Native MSSQL Server interface
sent with Powerbuilder.
Is this a SQLOLEDB bug?
"Scott Morris" wrote:
> Sounds to me like your code (or perhaps in an ancestor class) is doing
> something unexpected. If the column is not included the the list of
> updateable columns, then it will not be included as a column in the insert
> list. I've never had a problem with PB in this respect. I suspect that
> something else is occurring that is causing the behavior. You may want to
> use the preview pane in the dw painter to insert a row (if possible); this
> would at least identify PB or your code as the problem. The next step would
> be to create a simple test case using the existing datawindow (and without
> any of your ancestor/application code). If these succeed, then the problem
> lies with the application code. Although I haven't moved beyond PB9 6533, I
> suspect that application code or design is the problem. It also may be
> related to some odd cirucumstances (sharedata, linkage, get/setfullstate,
> etc.). You may also want to investigate whether some piece of code is
> dynamically modifying the datawindow and (implicitly or explicitly) setting
> the update properties.
> Be aware that the default you define in the table and the default you define
> within PB are different "things" entirely. PB will not automatically update
> a default defined in the datawindow to be consistent with any table-level
> default. This should not be an issue in this particular situation.
> One last thing: The phrase "... syntax and erroring as the default of zero
> is not being recognized" is not particularly meaningful. I will repeat.
> Help is much likely to be more "helpful" and more forthcoming if you
> accurately and completely describe the problem. By itself, a default (or
> lack thereof) will not produce an error when a row is inserted into a table.
> Perhaps there is a constraint or trigger that enforces the use of a specific
> domain of values for a column, producing the error. It helps to know the
> exact text of the error. I'm still not certain what "...the column default
> is recognized." means but I'm not certain it matters at this point.
> "roz" <roz@.discussions.microsoft.com> wrote in message
> news:D6DE6DA2-C749-4546-B121-D097E2C3A25F@.microsoft.com...
> the
> this
> to
> only
> code
> code
> review
> NG.
> 2000.
> out?
>
>
|||It looks to me like there is some other problem. The insert statement
generated by PB does not include the column in the insert list. If this is
true (and you can verify this using profiler - just to be absolutely
certain), then the default constraint you think is present isn't or the
default constraint is not enabled. Another possibility is a mal-functioning
trigger
Based on your information, you should be able to take the insert statement
from the error message, paste it into QA, and generate the error yourself by
executing the statement.
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:E5F27698-9BC8-4909-919E-63F11B9AF80E@.microsoft.com...
> I am trying to do an insert on a table using a datawindow. The dw does
not
> use the
> column 'dues_paid_lifetime' in the select (I searched the source!). Thus
> there is no initial value to set to zero. It is not in the select so
there[vbcol=seagreen]
> is no column to remove from the update list.. I set the dbparm to
> DisableParm = 1. Still on the insert I get the error:
> SQLSTATE = 23000
> Microsoft OLE DB Provider for SQL Server
> Cannot insert the value NULL into column "DUES_PAID_LIFETIME'; column does
> not allow nulls. INSERT fails.
> The insert syntax also displays in the error message. The column
> "DUES_PAID_LIFETIME' is not in the sql syntax.
> This does not happen with Oracle or with the Native MSSQL Server interface
> sent with Powerbuilder.
> Is this a SQLOLEDB bug?
>
> "Scott Morris" wrote:
insert[vbcol=seagreen]
that[vbcol=seagreen]
to[vbcol=seagreen]
this[vbcol=seagreen]
would[vbcol=seagreen]
without[vbcol=seagreen]
problem[vbcol=seagreen]
6533, I[vbcol=seagreen]
get/setfullstate,[vbcol=seagreen]
setting[vbcol=seagreen]
define[vbcol=seagreen]
update[vbcol=seagreen]
table-level[vbcol=seagreen]
zero[vbcol=seagreen]
table.[vbcol=seagreen]
specific[vbcol=seagreen]
default[vbcol=seagreen]
Insert.[vbcol=seagreen]
an[vbcol=seagreen]
in[vbcol=seagreen]
recognized.[vbcol=seagreen]
is[vbcol=seagreen]
peculiarity[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
application[vbcol=seagreen]
inserted[vbcol=seagreen]
to[vbcol=seagreen]
should[vbcol=seagreen]
to[vbcol=seagreen]
powerbuilder[vbcol=seagreen]
me[vbcol=seagreen]
Friday, February 17, 2012
Connecting Access from MS SQL 7.0
Hi,
I have been trying to connect to access database from SQL Server 7.0.
This machine is having 7.0 as a default instance and 2000 as a named instance.
Also the machine doesn't have access installed and Microsoft.Jet.4.0 is of version SP8 for Windows 2000
The access database is password protected.
I have tried all, OPENROWSET, OPENDATASOURCE, linked server, and ODBC.
These are some of the commands which I have tried but gives following error.
*******************************************
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'F:\Geerimain\billing.mdb';'bhagath';'bhagath', Employee)
AS a
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
**********************************************
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'DataSource="F:\Geerimain\billing.mdb";
User ID=bhagath;Password=bhagath;Jet OLEDB:SystemDatabase="c:\WINNT\system32\System.mdw"')...Employee
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
**********************************************
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Access Driver
(*.mdb)};Dbq=F:\Geerimain\billing.mdb;Uid=bhagath; pwd=bhagath','SELECT *
FROM Employee')
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
**********************************************
I have tried all possible combinations but most of the time I come up with Error 7399 or 7303
In case if anyone has some other syntax and successful with that, please let me know
Regards
JayDaft answer but... do you have any other way of confirming that you can open an Access database on that machine? Could be that SQL is just showing a general problem. Is MDAC installed?|||I have copied the mdb file on my machine and have Access installed.
I can open the access application using the username and password specified here.
Yes MDAC is installed, but which version can't say.
Can you tell me how to find that ..
I have been trying to connect to access database from SQL Server 7.0.
This machine is having 7.0 as a default instance and 2000 as a named instance.
Also the machine doesn't have access installed and Microsoft.Jet.4.0 is of version SP8 for Windows 2000
The access database is password protected.
I have tried all, OPENROWSET, OPENDATASOURCE, linked server, and ODBC.
These are some of the commands which I have tried but gives following error.
*******************************************
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'F:\Geerimain\billing.mdb';'bhagath';'bhagath', Employee)
AS a
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
**********************************************
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'DataSource="F:\Geerimain\billing.mdb";
User ID=bhagath;Password=bhagath;Jet OLEDB:SystemDatabase="c:\WINNT\system32\System.mdw"')...Employee
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
**********************************************
SELECT *
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Access Driver
(*.mdb)};Dbq=F:\Geerimain\billing.mdb;Uid=bhagath; pwd=bhagath','SELECT *
FROM Employee')
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
**********************************************
I have tried all possible combinations but most of the time I come up with Error 7399 or 7303
In case if anyone has some other syntax and successful with that, please let me know
Regards
JayDaft answer but... do you have any other way of confirming that you can open an Access database on that machine? Could be that SQL is just showing a general problem. Is MDAC installed?|||I have copied the mdb file on my machine and have Access installed.
I can open the access application using the username and password specified here.
Yes MDAC is installed, but which version can't say.
Can you tell me how to find that ..
Sunday, February 12, 2012
Connect to Sql Sevrer on non default?
Hi,
- Can I use set multiple port for connecting to the Sql Server?
- Where/How can I set it?
- If so, i've set it, how can i connect it on a non-default port?
Thx & RegardsFrom the Microsoft KB article 307645PRB: Cannot Connect to SQL Server on Any Port Other Than 1433:
To resolve this problem, use TCP/IP protocol, and include "Server=ComputerName, PortNumber" in the connection string.
Terri|||how to user the tcp ip protocole? we must put sth on the connection string?
(ive got the same prob)
Connect to SQL Server dialog
Hi,
Is it possible to avoid the last used server being in Query Analyzer
'Connect to SQL Server' dialog by default(when I click on File/Connect
in Query Analyzer) - I don't want to apply 'truncate table' scripts to
a production server by mistake!
Thanks<polinaskulski@.aol.com> wrote in message
news:1120572207.464620.272820@.g49g2000cwa.googlegr oups.com...
> Hi,
> Is it possible to avoid the last used server being in Query Analyzer
> 'Connect to SQL Server' dialog by default(when I click on File/Connect
> in Query Analyzer) - I don't want to apply 'truncate table' scripts to
> a production server by mistake!
> Thanks
Is it possible to avoid the last used server being in Query Analyzer
'Connect to SQL Server' dialog by default(when I click on File/Connect
in Query Analyzer) - I don't want to apply 'truncate table' scripts to
a production server by mistake!
Thanks<polinaskulski@.aol.com> wrote in message
news:1120572207.464620.272820@.g49g2000cwa.googlegr oups.com...
> Hi,
> Is it possible to avoid the last used server being in Query Analyzer
> 'Connect to SQL Server' dialog by default(when I click on File/Connect
> in Query Analyzer) - I don't want to apply 'truncate table' scripts to
> a production server by mistake!
> Thanks
I don't think that's possible - if you're concerned about running scripts in
the wrong place, then you can reference @.@.SERVERNAME in your scripts, or
always begin a transaction and require a manual COMMIT or ROLLBACK. Another
option is to use a dedicated administrator account for deployments to
production servers.
But all those approaches have drawbacks as well, and in the end there's no
catch-all substitute for being careful about what you're doing.
Simon
Subscribe to:
Posts (Atom)