I am trying to connect to a remote MSDE SQL database. When I set up the odbc connection I get a connection denied error. The SQL server is on a windows 2003 server. Any ideas are greatly appreciated.
Thanks, LinsLoMSDE won't allow remote connections on Windows 2003 unless you apply MSDE sp3a.
-PatP|||This is currently on the server, but it is still not connecting...|||what are you using to connect? a programming language (c, c++, c#) and are they .net? If you are using .net then i would suggest trying to use the sql connection library instead of the odbc. are you on the same network as the db server? please provide more info on your situation. I can not give an accurate answer with out more information
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
MSDE Replication ?
I am designing a web site which will use MSDE at the back end (on a remote hosting provider). Uers will be able to edit small amounts of data (in limited no. of tables) in the web application, whilst I will need to 'upload' much larger amounts (and differ
ent tables) on a daily basis.
My question is how to design the replication ?
a) Merge replicate the whole Db ? or ...
a) Split the database into two databases (one being the stuff that users/browsers can edit) and one being the other stuff that will be 'uploaded' and use different replication techniques for each ?
c) Any other way ?
Also, are there any limitations on replication with MSDE ?
Many Thanks
Also, is it possible to use the Enterprise Manager in the Developer Edition to manage the replication or is this in breach of the licensing ?
|||Sarah,
this article explains MSDE limitations/restrictions for use in replication,
and the licensing implications:
http://support.microsoft.com/default...;en-us;Q324992
HTH,
Paul Ibison
|||Sarah,
I'm not sure about the topology. Presumably the website uses MSDE and you
need to replicate to a separate site (lets say B)? When you say 'upload' is
this a flow of data from the website to site B or vice versa? Is there any
posibility of data both at site B and the website being changed before
synchronization? When you 'upload' the data, is this the entire table(s) or
incremental changes?
Regards,
Paul Ibison
|||Hiya Paul,
Thanks for the link.
Re the topology ...
Website is running MSDE (say A) whilst I have a (home) server which can run MSDE or Developer Edition (say B).
Small amount of data/tables (say Tables-X) are updated by web users on A.
All data/tables are updated daily by me on B (this is the 'upload' TO the website).
It is possible that data in Tables-X will have been updated at both A and B and hence will need merging.
The other data changes (excepting Tables-X) will largely be updates to column values but also added rows (particularly early on in the sites lifetime).
I'm assuming I'll do a merge replication with B being the Publisher/Distributor and A being the Subscriber. (Or can I do a Merge Replication on Tables-X and Transactional replication on everything else ?)
|||Sarah,
I agree - merge for Tables-X. You'll need to consider conflict resolution,
or partition the data according to the subscriber eg use identity ranges, so
no conflicts will arise. If the other tables are only changed in one
direction then transactional would be definitely suitable there.
HTH,
Paul Ibison
|||Brilliant! Thanks Paul! That's reassuring
... but can I use the Development Edition of Enterprise Manager to handle or will this breach the licencing ?
|||This is one for MS licensing (a phone call). For what it's worth, my guess is that it is allowed, as I know of someone who uses the evaluation edition of SQL Server for the same purposes and insists it is legal.
HTH,
Paul Ibison
|||Many Thanks for your help Paul
sql
ent tables) on a daily basis.
My question is how to design the replication ?
a) Merge replicate the whole Db ? or ...
a) Split the database into two databases (one being the stuff that users/browsers can edit) and one being the other stuff that will be 'uploaded' and use different replication techniques for each ?
c) Any other way ?
Also, are there any limitations on replication with MSDE ?
Many Thanks
Also, is it possible to use the Enterprise Manager in the Developer Edition to manage the replication or is this in breach of the licensing ?
|||Sarah,
this article explains MSDE limitations/restrictions for use in replication,
and the licensing implications:
http://support.microsoft.com/default...;en-us;Q324992
HTH,
Paul Ibison
|||Sarah,
I'm not sure about the topology. Presumably the website uses MSDE and you
need to replicate to a separate site (lets say B)? When you say 'upload' is
this a flow of data from the website to site B or vice versa? Is there any
posibility of data both at site B and the website being changed before
synchronization? When you 'upload' the data, is this the entire table(s) or
incremental changes?
Regards,
Paul Ibison
|||Hiya Paul,
Thanks for the link.
Re the topology ...
Website is running MSDE (say A) whilst I have a (home) server which can run MSDE or Developer Edition (say B).
Small amount of data/tables (say Tables-X) are updated by web users on A.
All data/tables are updated daily by me on B (this is the 'upload' TO the website).
It is possible that data in Tables-X will have been updated at both A and B and hence will need merging.
The other data changes (excepting Tables-X) will largely be updates to column values but also added rows (particularly early on in the sites lifetime).
I'm assuming I'll do a merge replication with B being the Publisher/Distributor and A being the Subscriber. (Or can I do a Merge Replication on Tables-X and Transactional replication on everything else ?)
|||Sarah,
I agree - merge for Tables-X. You'll need to consider conflict resolution,
or partition the data according to the subscriber eg use identity ranges, so
no conflicts will arise. If the other tables are only changed in one
direction then transactional would be definitely suitable there.
HTH,
Paul Ibison
|||Brilliant! Thanks Paul! That's reassuring
... but can I use the Development Edition of Enterprise Manager to handle or will this breach the licencing ?
|||This is one for MS licensing (a phone call). For what it's worth, my guess is that it is allowed, as I know of someone who uses the evaluation edition of SQL Server for the same purposes and insists it is legal.
HTH,
Paul Ibison
|||Many Thanks for your help Paul
sql
MSDE remote connection on Windows Home - Windows Authentication
I installed an MSDE on a PC running Windows Home Edition. I would like
to connect to this MSDE installation from an oher PC using Windows
Authentication.
What do I have to do (add account on Home Edition)
hi,
xavierdaull@.gmail.com wrote:
> I installed an MSDE on a PC running Windows Home Edition. I would like
> to connect to this MSDE installation from an oher PC using Windows
> Authentication.
> What do I have to do (add account on Home Edition)
without a Domain Controller, thought in a Workgroup environment, usually you
should rely on standard SQL Server authenticated connections for remote
ones, as all the authentication features are resolved and managed by the
Domain Controller it self..
you can however setup a local account to map the remote account of the other
machine... that's to say, on your local PC you have to add another account
with the very same "credentials" of other account(s) present on the remote
PCs...
all this (still) works becouse of the underlying OS gets somehow confused
and a sort of "embezzlement" where 2 differents accounts, YourPC\YourAccount
and OtherPC\YourAccount "share" a kind of uniqueness becouse of netbios
intrusion... a preventive netbios connection, even on a share or using NET
USE can help...
but this is not granted to work in future releases or even OS service
packs...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
to connect to this MSDE installation from an oher PC using Windows
Authentication.
What do I have to do (add account on Home Edition)
hi,
xavierdaull@.gmail.com wrote:
> I installed an MSDE on a PC running Windows Home Edition. I would like
> to connect to this MSDE installation from an oher PC using Windows
> Authentication.
> What do I have to do (add account on Home Edition)
without a Domain Controller, thought in a Workgroup environment, usually you
should rely on standard SQL Server authenticated connections for remote
ones, as all the authentication features are resolved and managed by the
Domain Controller it self..
you can however setup a local account to map the remote account of the other
machine... that's to say, on your local PC you have to add another account
with the very same "credentials" of other account(s) present on the remote
PCs...
all this (still) works becouse of the underlying OS gets somehow confused
and a sort of "embezzlement" where 2 differents accounts, YourPC\YourAccount
and OtherPC\YourAccount "share" a kind of uniqueness becouse of netbios
intrusion... a preventive netbios connection, even on a share or using NET
USE can help...
but this is not granted to work in future releases or even OS service
packs...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
MSDE remote connection failure
Hi all,
I installed a MSDE on a Windows XP SP2 and configured it for accepting remote connections. But It shows the "SQL Server doesnot exist or connection denied" message whenever I try to connect to this server. For more information I am adding more facts
* It is an MSDE and not a WMSDE
* Firewall is turned off
* Server process is listening on 0.0.0.0:1433
* TCP/IP and Named Pipes are enabled using server network utility
* LoginMode=2 is set for mixed mode authentication in the registry
* Tried restaring the Process and even windows after the changes.
Please help me !!
Thanks in advanceHi friends,
I got the issue fixed. :angel:
I dont know how it works but it worked.
On the client side; using the client network utility, I added an alias to my server.
My server's instance name was SLIMSHADY\SSIT and it was listening on 8001(I changed it thru the registry) in mixed mode authentication.
It wasted my 4-5 hours of playing with the registry and googling. The final attempt succeeded in which I created an alias to the server Indicating its exact port number and Instance name.
Then I tried connecting using this alias .... IT WORKED !!
I DONT KNOW HOW IT WORKS BUT IT WORKED IN MY CASE
HAPPY COMPUTING
MSDE remote connection failure
Hi all,
I installed a MSDE on a Windows XP SP2 and configured it for accepting remote connections. But It shows the "SQL Server doesnot exist or connection denied" message whenever I try to connect to this server. For more information I am adding more facts
* It is an MSDE and not a WMSDE
* Firewall is turned off
* Server process is listening on 0.0.0.0:1433
* TCP/IP and Named Pipes are enabled using server network utility
* LoginMode=2 is set for mixed mode authentication in the registry
* Tried restaring the Process and even windows after the changes.
Please help me !!
Thanks in advanceHi friends,
I got the issue fixed. :angel:
I dont know how it works but it worked.
On the client side; using the client network utility, I added an alias to my server.
My server's instance name was SLIMSHADY\SSIT and it was listening on 8001(I changed it thru the registry) in mixed mode authentication.
It wasted my 4-5 hours of playing with the registry and googling. The final attempt succeeded in which I created an alias to the server Indicating its exact port number and Instance name.
Then I tried connecting using this alias .... IT WORKED !!
I DONT KNOW HOW IT WORKS BUT IT WORKED IN MY CASE
HAPPY COMPUTING
I installed a MSDE on a Windows XP SP2 and configured it for accepting remote connections. But It shows the "SQL Server doesnot exist or connection denied" message whenever I try to connect to this server. For more information I am adding more facts
* It is an MSDE and not a WMSDE
* Firewall is turned off
* Server process is listening on 0.0.0.0:1433
* TCP/IP and Named Pipes are enabled using server network utility
* LoginMode=2 is set for mixed mode authentication in the registry
* Tried restaring the Process and even windows after the changes.
Please help me !!
Thanks in advanceHi friends,
I got the issue fixed. :angel:
I dont know how it works but it worked.
On the client side; using the client network utility, I added an alias to my server.
My server's instance name was SLIMSHADY\SSIT and it was listening on 8001(I changed it thru the registry) in mixed mode authentication.
It wasted my 4-5 hours of playing with the registry and googling. The final attempt succeeded in which I created an alias to the server Indicating its exact port number and Instance name.
Then I tried connecting using this alias .... IT WORKED !!
I DONT KNOW HOW IT WORKS BUT IT WORKED IN MY CASE
HAPPY COMPUTING
MSDE Remote Connect strange problem
I'm trying to connect to the SharePoint MSDE database from Windows XP
SP2 machine over Ethernet LAN. I was getting server not found or access
denied, until I enabled TCP/IP via svrnetcn.exe. Now, I'm getting
OK
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
I just did an netstat -an and I don't see the server listening on 1433.
What am I not doing?
The svrnetcn tool has no effect. I make a change and check the logs,
but nothing happens. What would be causing this?
Did you ever get an answer to this problem? I'm having a similar error on Windows XP SP2 machines that have MDAC 2.8SP1. I have an MS Access front end that links to SQL Server database using ODBC connections. The report will run, but keeps asking for SQL Server login info, then finally I get the same Connection failed error you listed with the exception on the line: SQL Server Error: 10048. However, the report actually finishes with all the data requested.
I have this same Access front end running on other XP machines with SP1 and Windows 2000 machines with MDAC 2.7 SP1. When the report runs on those machines it only asks for SQL Server login info when the program is first connecting to the db.
Any ideas?
|||Thank you thank you for letting me know about the svrnetcn.exe tool. I have been trying for hours to get a Sharepoint portal going and whilst for the error I had MS KB said enable tcp/ip, it didn't say how to do it for msde. Where did you find out about svrnetcn.exe and are there other useful tools I should know about? I'm ok with osql.
I have seen the error you are getting. It will happen if I'm not using Windows integrated authentication. If I try to connect with osql -U sa -S servername\instancename it doesn't work and gives a server does not exist or access denied. I have to use osql -E -S servername\instancename and it goes in fine.
Hope this helps - even if its a bit late!
Nath
|||
Nathan,
here is the linke which might be useful to you guys
http://www.codeproject.com/database/ConfigureMSDE.asp
Best regards,
Web
MSDE Remote Connect strange problem
I'm trying to connect to the SharePoint MSDE database from Windows XP
SP2 machine over Ethernet LAN. I was getting server not found or access
denied, until I enabled TCP/IP via svrnetcn.exe. Now, I'm getting
OK
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
I just did an netstat -an and I don't see the server listening on 1433.
What am I not doing?
The svrnetcn tool has no effect. I make a change and check the logs,
but nothing happens. What would be causing this?
SP2 machine over Ethernet LAN. I was getting server not found or access
denied, until I enabled TCP/IP via svrnetcn.exe. Now, I'm getting
OK
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
I just did an netstat -an and I don't see the server listening on 1433.
What am I not doing?
The svrnetcn tool has no effect. I make a change and check the logs,
but nothing happens. What would be causing this?
Did you ever get an answer to this problem? I'm having a similar error on Windows XP SP2 machines that have MDAC 2.8SP1. I have an MS Access front end that links to SQL Server database using ODBC connections. The report will run, but keeps asking for SQL Server login info, then finally I get the same Connection failed error you listed with the exception on the line: SQL Server Error: 10048. However, the report actually finishes with all the data requested.
I have this same Access front end running on other XP machines with SP1 and Windows 2000 machines with MDAC 2.7 SP1. When the report runs on those machines it only asks for SQL Server login info when the program is first connecting to the db.
Any ideas?
|||Thank you thank you for letting me know about the svrnetcn.exe tool. I have been trying for hours to get a Sharepoint portal going and whilst for the error I had MS KB said enable tcp/ip, it didn't say how to do it for msde. Where did you find out about svrnetcn.exe and are there other useful tools I should know about? I'm ok with osql.
I have seen the error you are getting. It will happen if I'm not using Windows integrated authentication. If I try to connect with osql -U sa -S servername\instancename it doesn't work and gives a server does not exist or access denied. I have to use osql -E -S servername\instancename and it goes in fine.
Hope this helps - even if its a bit late!
Nath
|||
Nathan,
here is the linke which might be useful to you guys
http://www.codeproject.com/database/ConfigureMSDE.asp
Best regards,
Web
MSDE Remote Connect strange problem
I'm trying to connect to the SharePoint MSDE database from Windows XP
SP2 machine over Ethernet LAN. I was getting server not found or access
denied, until I enabled TCP/IP via svrnetcn.exe. Now, I'm getting
OK
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
I just did an netstat -an and I don't see the server listening on 1433.
What am I not doing?
The svrnetcn tool has no effect. I make a change and check the logs,
but nothing happens. What would be causing this?
SP2 machine over Ethernet LAN. I was getting server not found or access
denied, until I enabled TCP/IP via svrnetcn.exe. Now, I'm getting
OK
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
I just did an netstat -an and I don't see the server listening on 1433.
What am I not doing?
The svrnetcn tool has no effect. I make a change and check the logs,
but nothing happens. What would be causing this?
Did you ever get an answer to this problem? I'm having a similar error on Windows XP SP2 machines that have MDAC 2.8SP1. I have an MS Access front end that links to SQL Server database using ODBC connections. The report will run, but keeps asking for SQL Server login info, then finally I get the same Connection failed error you listed with the exception on the line: SQL Server Error: 10048. However, the report actually finishes with all the data requested.
I have this same Access front end running on other XP machines with SP1 and Windows 2000 machines with MDAC 2.7 SP1. When the report runs on those machines it only asks for SQL Server login info when the program is first connecting to the db.
Any ideas?
|||Thank you thank you for letting me know about the svrnetcn.exe tool. I have been trying for hours to get a Sharepoint portal going and whilst for the error I had MS KB said enable tcp/ip, it didn't say how to do it for msde. Where did you find out about svrnetcn.exe and are there other useful tools I should know about? I'm ok with osql.
I have seen the error you are getting. It will happen if I'm not using Windows integrated authentication. If I try to connect with osql -U sa -S servername\instancename it doesn't work and gives a server does not exist or access denied. I have to use osql -E -S servername\instancename and it goes in fine.
Hope this helps - even if its a bit late!
Nath
|||
Nathan,
here is the linke which might be useful to you guys
http://www.codeproject.com/database/ConfigureMSDE.asp
Best regards,
Web
Friday, March 9, 2012
MSDE instance registration in Enterprise Manager
Hi All,
I'm trying to register an instance of MSDE on a remote machine using EM on my desktop. The remote machine has 2
named instances of MSDE running. I am able to register 1 instance with no problems. I had to register it using
"RemoteServer1\InstanceName" rather than just the remote machines name.
For the life of me I can not get the other instance to register. I get the "SQL Server does not exist or access
denied" error. I've tried just registering the 2nd instance without the first with the same results. I can see
no difference in the way either of the 2 instances were set up or in their properties. I am a machine admin on
the remote machine and am connecting using Windows Auth.
What am I missing and how can I register that 2nd instance?
Thank you.
gdr
Gary D. Rezek
University Networking Systems and Services
South Dakota State University
hi Gary,
Gary D. Rezek wrote:
> Hi All,
> I'm trying to register an instance of MSDE on a remote machine using
> EM on my desktop. The remote machine has 2 named instances of MSDE
> running. I am able to register 1 instance with no problems. I had to
> register it using "RemoteServer1\InstanceName" rather than just the
> remote machines name.
this is normal as a named instance full qualified name is
ComputerName\InstanceName...
> For the life of me I can not get the other instance to register. I
> get the "SQL Server does not exist or access denied" error. I've
> tried just registering the 2nd instance without the first with the
> same results. I can see no difference in the way either of the 2
> instances were set up or in their properties. I am a machine admin on
> the remote machine and am connecting using Windows Auth. What am I
> missing and how can I register that 2nd instance?
at least 1 difference must be present... the name... MSDE/SQL Server 2000
can be installed as a default instance or a named instance... but only a
default instance can be present, wit up to other 15 named instances per
computer...
as you already saw, a default instance is available as ComputerName, where a
named instance will be as ComputerName\InstanceName...
you can inspect your (remote) services for MSSQLSERVER and
MSSQL$InstanceName to see what is going on...
MSDE installs by default disabling network protocols, thus not allowing
remote connections... please verify using the Server Network Utiliy
(svrnetcn.exe) that the "failing" instance has enabled network protocols...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Thanks for your answer. It eventually led me to http://tinyurl.com/8q95s this post. In that post I found this
quote...
"You need to look for a line in the error log which states:
"SQL server listening on TCP, Shared Memory, Named Pipes.
"If you only have Shared Memory on this list, then only local connections are allowed."
In the instance I have been trying to connect, Shared Memory is the only thing listed in the errorlog, so no
TCP/IP or Named Pipes. I asked the individual who is responsible for the application which created this instance
of MSDE (and also the one who asked me WHY can't he connect through EM), I learned that this is the way it
should be. :^/ I've worked with MSDE very, very little, but I still should have looked at the ErrorLog to
begin with.
Thank you very much for your help
gdr
Gary D. Rezek
University Networking Systems and Services
South Dakota State University
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message news:3jg2rjFpnmhnU1@.individual.net...
> hi Gary,
> Gary D. Rezek wrote:
> this is normal as a named instance full qualified name is ComputerName\InstanceName...
>
> at least 1 difference must be present... the name... MSDE/SQL Server 2000 can be installed as a default
> instance or a named instance... but only a default instance can be present, wit up to other 15 named instances
> per computer...
> as you already saw, a default instance is available as ComputerName, where a named instance will be as
> ComputerName\InstanceName...
> you can inspect your (remote) services for MSSQLSERVER and MSSQL$InstanceName to see what is going on...
> MSDE installs by default disabling network protocols, thus not allowing remote connections... please verify
> using the Server Network Utiliy (svrnetcn.exe) that the "failing" instance has enabled network protocols...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface)
> -- remove DMO to reply
>
|||h Gary,
Gary D. Rezek wrote:
> Hi Andrea,
> Thanks for your answer. It eventually led me to
> http://tinyurl.com/8q95s this post. In that post I found this quote...
> "You need to look for a line in the error log which states:
> "SQL server listening on TCP, Shared Memory, Named Pipes.
> "If you only have Shared Memory on this list, then only local
> connections are allowed."
you always have to verify (on the server using Server Network Utility
[svrnetcn.exe] and on the client using Client Network Utility
[cliconfg.exe]) that a common network protocol is available for the client
and the server to dialog...
> In the instance I have been trying to connect, Shared Memory is the
> only thing listed in the errorlog, so no TCP/IP or Named Pipes. I
> asked the individual who is responsible for the application which
> created this instance of MSDE (and also the one who asked me WHY
> can't he connect through EM), I learned that this is the way it
> should be. :^/
if Shared Memory is the only protocol that "must" be enabled, then no remote
connections will be available... but it can be ok in local scenarios..
MSDE will then actually be used as a local desktop database..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
I'm trying to register an instance of MSDE on a remote machine using EM on my desktop. The remote machine has 2
named instances of MSDE running. I am able to register 1 instance with no problems. I had to register it using
"RemoteServer1\InstanceName" rather than just the remote machines name.
For the life of me I can not get the other instance to register. I get the "SQL Server does not exist or access
denied" error. I've tried just registering the 2nd instance without the first with the same results. I can see
no difference in the way either of the 2 instances were set up or in their properties. I am a machine admin on
the remote machine and am connecting using Windows Auth.
What am I missing and how can I register that 2nd instance?
Thank you.
gdr
Gary D. Rezek
University Networking Systems and Services
South Dakota State University
hi Gary,
Gary D. Rezek wrote:
> Hi All,
> I'm trying to register an instance of MSDE on a remote machine using
> EM on my desktop. The remote machine has 2 named instances of MSDE
> running. I am able to register 1 instance with no problems. I had to
> register it using "RemoteServer1\InstanceName" rather than just the
> remote machines name.
this is normal as a named instance full qualified name is
ComputerName\InstanceName...
> For the life of me I can not get the other instance to register. I
> get the "SQL Server does not exist or access denied" error. I've
> tried just registering the 2nd instance without the first with the
> same results. I can see no difference in the way either of the 2
> instances were set up or in their properties. I am a machine admin on
> the remote machine and am connecting using Windows Auth. What am I
> missing and how can I register that 2nd instance?
at least 1 difference must be present... the name... MSDE/SQL Server 2000
can be installed as a default instance or a named instance... but only a
default instance can be present, wit up to other 15 named instances per
computer...
as you already saw, a default instance is available as ComputerName, where a
named instance will be as ComputerName\InstanceName...
you can inspect your (remote) services for MSSQLSERVER and
MSSQL$InstanceName to see what is going on...
MSDE installs by default disabling network protocols, thus not allowing
remote connections... please verify using the Server Network Utiliy
(svrnetcn.exe) that the "failing" instance has enabled network protocols...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Thanks for your answer. It eventually led me to http://tinyurl.com/8q95s this post. In that post I found this
quote...
"You need to look for a line in the error log which states:
"SQL server listening on TCP, Shared Memory, Named Pipes.
"If you only have Shared Memory on this list, then only local connections are allowed."
In the instance I have been trying to connect, Shared Memory is the only thing listed in the errorlog, so no
TCP/IP or Named Pipes. I asked the individual who is responsible for the application which created this instance
of MSDE (and also the one who asked me WHY can't he connect through EM), I learned that this is the way it
should be. :^/ I've worked with MSDE very, very little, but I still should have looked at the ErrorLog to
begin with.
Thank you very much for your help
gdr
Gary D. Rezek
University Networking Systems and Services
South Dakota State University
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message news:3jg2rjFpnmhnU1@.individual.net...
> hi Gary,
> Gary D. Rezek wrote:
> this is normal as a named instance full qualified name is ComputerName\InstanceName...
>
> at least 1 difference must be present... the name... MSDE/SQL Server 2000 can be installed as a default
> instance or a named instance... but only a default instance can be present, wit up to other 15 named instances
> per computer...
> as you already saw, a default instance is available as ComputerName, where a named instance will be as
> ComputerName\InstanceName...
> you can inspect your (remote) services for MSSQLSERVER and MSSQL$InstanceName to see what is going on...
> MSDE installs by default disabling network protocols, thus not allowing remote connections... please verify
> using the Server Network Utiliy (svrnetcn.exe) that the "failing" instance has enabled network protocols...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface)
> -- remove DMO to reply
>
|||h Gary,
Gary D. Rezek wrote:
> Hi Andrea,
> Thanks for your answer. It eventually led me to
> http://tinyurl.com/8q95s this post. In that post I found this quote...
> "You need to look for a line in the error log which states:
> "SQL server listening on TCP, Shared Memory, Named Pipes.
> "If you only have Shared Memory on this list, then only local
> connections are allowed."
you always have to verify (on the server using Server Network Utility
[svrnetcn.exe] and on the client using Client Network Utility
[cliconfg.exe]) that a common network protocol is available for the client
and the server to dialog...
> In the instance I have been trying to connect, Shared Memory is the
> only thing listed in the errorlog, so no TCP/IP or Named Pipes. I
> asked the individual who is responsible for the application which
> created this instance of MSDE (and also the one who asked me WHY
> can't he connect through EM), I learned that this is the way it
> should be. :^/
if Shared Memory is the only protocol that "must" be enabled, then no remote
connections will be available... but it can be ok in local scenarios
..MSDE will then actually be used as a local desktop database..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Monday, February 20, 2012
MSDE General Questions
I am trying to better understand my replication options with MSDE.
I am pretty sure that with merge replication the remote MSDE database will
track the changes in a set of state tables. However with transactional
replication I have read conflicting information as to whether it tracks
changes in the MSDE transaction log or in state tables. Does anyone know the
answer to this?
Also since MSDE has a limit to the size of the database does anyone know how
much additional data is stored in these state tables whenever a change is
made?
Thanks
Answers in line
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
news:DBE421DC-3E4B-4BC6-9275-0DB77F7133E0@.microsoft.com...
> I am trying to better understand my replication options with MSDE.
> I am pretty sure that with merge replication the remote MSDE database will
> track the changes in a set of state tables. However with transactional
> replication I have read conflicting information as to whether it tracks
> changes in the MSDE transaction log or in state tables.
In merge replication is uses the same state tabels for all versions of SQL
Server including MSDE. For transactional replication MSDE can only subscribe
to transactional replication publications.
Does anyone know the
> answer to this?
> Also since MSDE has a limit to the size of the database does anyone know
how
> much additional data is stored in these state tables whenever a change is
> made?
For an insert or update roughly 2329 bytes. For a delete about 748.
> Thanks
|||> For transactional replication MSDE can only subscribe
> to transactional replication publications.
So for transactional replication (MSDE), does it track changes in state
tables or in the transaction log?
Thanks for the other answers.
|||I don't like this question, because if I was to say yes, which strictly
speaking is true, you would probably get a incorrect idea of how it works.
The state of the subscription is tracked in a table in MSDE called
MSreplication_subscriptions. This table contains information about what
transaction was last applied on the subscription database. It contains no
information about changes which were made to the subscriber tables per se.
So if a user makes a change to a subscriber table, it won't appear in
MSreplication_subscriptions. If a replication process makes a change some
metadata will be logged in MSreplication_subscriptions.
Changes themselves are logged in two "state tables" in the distribution
database on the publisher. Prior to being logged in the distribution
database, they are logged in the transaction log in the publication
database.
No changes are logged in the subscription database on the MSDE database,
unless you are using queued updating.
The way to think about it is, with plain vanilla transactional replication,
any user activity originating on the subscriber (MSDE), in the subscription
database will not be logged in state tables, but replication activity
originating on the publisher will update the subscriber tables, and write
replication metadata to the "state" table MSreplication_subscription.
HTH
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
news:181BE87C-FAEC-467E-861C-0E19DED023F8@.microsoft.com...
> So for transactional replication (MSDE), does it track changes in state
> tables or in the transaction log?
> Thanks for the other answers.
|||Thanks!
"Hilary Cotter" wrote:
> I don't like this question, because if I was to say yes, which strictly
> speaking is true, you would probably get a incorrect idea of how it works.
> The state of the subscription is tracked in a table in MSDE called
> MSreplication_subscriptions. This table contains information about what
> transaction was last applied on the subscription database. It contains no
> information about changes which were made to the subscriber tables per se.
> So if a user makes a change to a subscriber table, it won't appear in
> MSreplication_subscriptions. If a replication process makes a change some
> metadata will be logged in MSreplication_subscriptions.
> Changes themselves are logged in two "state tables" in the distribution
> database on the publisher. Prior to being logged in the distribution
> database, they are logged in the transaction log in the publication
> database.
> No changes are logged in the subscription database on the MSDE database,
> unless you are using queued updating.
> The way to think about it is, with plain vanilla transactional replication,
> any user activity originating on the subscriber (MSDE), in the subscription
> database will not be logged in state tables, but replication activity
> originating on the publisher will update the subscriber tables, and write
> replication metadata to the "state" table MSreplication_subscription.
> HTH
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
> news:181BE87C-FAEC-467E-861C-0E19DED023F8@.microsoft.com...
>
>
I am pretty sure that with merge replication the remote MSDE database will
track the changes in a set of state tables. However with transactional
replication I have read conflicting information as to whether it tracks
changes in the MSDE transaction log or in state tables. Does anyone know the
answer to this?
Also since MSDE has a limit to the size of the database does anyone know how
much additional data is stored in these state tables whenever a change is
made?
Thanks
Answers in line
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
news:DBE421DC-3E4B-4BC6-9275-0DB77F7133E0@.microsoft.com...
> I am trying to better understand my replication options with MSDE.
> I am pretty sure that with merge replication the remote MSDE database will
> track the changes in a set of state tables. However with transactional
> replication I have read conflicting information as to whether it tracks
> changes in the MSDE transaction log or in state tables.
In merge replication is uses the same state tabels for all versions of SQL
Server including MSDE. For transactional replication MSDE can only subscribe
to transactional replication publications.
Does anyone know the
> answer to this?
> Also since MSDE has a limit to the size of the database does anyone know
how
> much additional data is stored in these state tables whenever a change is
> made?
For an insert or update roughly 2329 bytes. For a delete about 748.
> Thanks
|||> For transactional replication MSDE can only subscribe
> to transactional replication publications.
So for transactional replication (MSDE), does it track changes in state
tables or in the transaction log?
Thanks for the other answers.
|||I don't like this question, because if I was to say yes, which strictly
speaking is true, you would probably get a incorrect idea of how it works.
The state of the subscription is tracked in a table in MSDE called
MSreplication_subscriptions. This table contains information about what
transaction was last applied on the subscription database. It contains no
information about changes which were made to the subscriber tables per se.
So if a user makes a change to a subscriber table, it won't appear in
MSreplication_subscriptions. If a replication process makes a change some
metadata will be logged in MSreplication_subscriptions.
Changes themselves are logged in two "state tables" in the distribution
database on the publisher. Prior to being logged in the distribution
database, they are logged in the transaction log in the publication
database.
No changes are logged in the subscription database on the MSDE database,
unless you are using queued updating.
The way to think about it is, with plain vanilla transactional replication,
any user activity originating on the subscriber (MSDE), in the subscription
database will not be logged in state tables, but replication activity
originating on the publisher will update the subscriber tables, and write
replication metadata to the "state" table MSreplication_subscription.
HTH
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
news:181BE87C-FAEC-467E-861C-0E19DED023F8@.microsoft.com...
> So for transactional replication (MSDE), does it track changes in state
> tables or in the transaction log?
> Thanks for the other answers.
|||Thanks!
"Hilary Cotter" wrote:
> I don't like this question, because if I was to say yes, which strictly
> speaking is true, you would probably get a incorrect idea of how it works.
> The state of the subscription is tracked in a table in MSDE called
> MSreplication_subscriptions. This table contains information about what
> transaction was last applied on the subscription database. It contains no
> information about changes which were made to the subscriber tables per se.
> So if a user makes a change to a subscriber table, it won't appear in
> MSreplication_subscriptions. If a replication process makes a change some
> metadata will be logged in MSreplication_subscriptions.
> Changes themselves are logged in two "state tables" in the distribution
> database on the publisher. Prior to being logged in the distribution
> database, they are logged in the transaction log in the publication
> database.
> No changes are logged in the subscription database on the MSDE database,
> unless you are using queued updating.
> The way to think about it is, with plain vanilla transactional replication,
> any user activity originating on the subscriber (MSDE), in the subscription
> database will not be logged in state tables, but replication activity
> originating on the publisher will update the subscriber tables, and write
> replication metadata to the "state" table MSreplication_subscription.
> HTH
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Cavanagh" <Cavanagh@.discussions.microsoft.com> wrote in message
> news:181BE87C-FAEC-467E-861C-0E19DED023F8@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)