MSDE to SQL Server Express

Hi - I have an MSDE database running on my server - I'd like to port my asp.net 1.1 app to asp.net2, and make use of SQL 2005 Express.

How do I transfer my data from an MSDE database to SQL Server Express, keeping intact all of my unique ID fields, indexes, and SPs (I don't have any functions or views)

Thanks for any help,

Mark

Your best bet is either to "detach" it on the MSDE side, and "attach" it or "restore from backup" on the SQL 2005 side, or use DTS (data transformation services).

|||Hi - it's just SQL Server 2005 Express I have available, so no management tools are there to allow me to 'restore from backup' - I'm not sure it supports DTS either.

Has anyone else been able to do this?

Thanks, Mark|||

Even with SQL Express you should have the ability to detach the database. You need the Management studio installed to work with the Express server engine though. You can download it here.

http://www.microsoft.com/downloads/details.aspx?familyid=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&displaylang=en

|||You don't have to detach from SQL Express if you are using a file based database (You are if you use the AttachDB attribute in your connect string). Just stop whatever application that uses it, and copy the mdb/ldb files to SQL Sever 2005 and attach.

MSDE Setup and first database

I'm deploying MSDE with a desktop app (windows forms .net).
after installing MSDE, how do I create the database for the application?
I cannot provide a createdb.sql script to the user because there is no
queryanalyzer or similar tools.
How can I run the create query from the setup? or what is the recommended
method for doing this?
Thank You!
Hi,
Give a batch file to create the database. All you have to do is double click
the batch file. batch file should contain:-
OSQL -Usa -Ppassword -S servername -Q'Create database
dbname' -o'c:\createdb.log'
Save the command inside a .BAT file and send to client side to create the
database. Instad of giving the -Q parameter you
can also input a .SQL file which contans sequence of TSQL commands.
Thanks
Hari
SQL Server MVP
"Harley" <harleyobrienAThotmailDOTcom> wrote in message
news:E3273FC0-ADE4-47B1-9F3D-8DEC479E3341@.microsoft.com...
> I'm deploying MSDE with a desktop app (windows forms .net).
> after installing MSDE, how do I create the database for the application?
> I cannot provide a createdb.sql script to the user because there is no
> queryanalyzer or similar tools.
> How can I run the create query from the setup? or what is the recommended
> method for doing this?
> Thank You!

MSDE Productcode

Hello,
I am installing MSDE along with my app using the MS Toolkit. When I install MSDE and then my app, everything works fine. However if later I uninstall my app (not MSDE instance) and rerun the setup.exe, it disables my app setup saying that MSDE prerequis
ite has to be installed. But MSDE is already on my machine as I did not uninstall it.
Someone pointed out that the productcode should remain the same for the setup. But I never change it. It's a fixed GUID.
Any ideas anyone. Thanks
Dev,
This is a known issue with the RC build of the Toolkit. We'll be fixing it
for RTM.
Cheers,
Brian A. Randell
MCW Technologies
"dev" <anonymous@.discussions.microsoft.com> wrote in message
news:2A575001-D9A3-47EA-9142-C1E5546D3CD8@.microsoft.com...
> Hello,
> I am installing MSDE along with my app using the MS Toolkit. When I
install MSDE and then my app, everything works fine. However if later I
uninstall my app (not MSDE instance) and rerun the setup.exe, it disables my
app setup saying that MSDE prerequisite has to be installed. But MSDE is
already on my machine as I did not uninstall it.
> Someone pointed out that the productcode should remain the same for the
setup. But I never change it. It's a fixed GUID.
> Any ideas anyone. Thanks
|||Great, Thanks
|||On Tue, 27 Apr 2004 16:33:49 -0700, "Brian A. Randell"
<brianr@.nospam.mcwtech.com> wrote:

>Dev,
>This is a known issue with the RC build of the Toolkit. We'll be fixing it
>for RTM.
Any indication of when that might be?
|||Dates are a funny thing. They move. In time for Tech Ed US is our goal. But
as always, could be sooner or later.
Cheers,
Brian A. Randell
MCW Technologies
"Brady Kelly" <bkelly@.icom-it.co.za> wrote in message
news:e6iv80praetf3cp77legif9a1i4tloovre@.4ax.com... [vbcol=seagreen]
> On Tue, 27 Apr 2004 16:33:49 -0700, "Brian A. Randell"
> <brianr@.nospam.mcwtech.com> wrote:
it
> Any indication of when that might be?

MSDE on a local computer (not into a LAN)

I try to develop an installer that should install MSDE and an app that needs an MSDE database. But the problem is, that the ODBC (and my installer too) see no MSDE installation if the computer is no part of a network. If i plug the network cable, i can find the MSDE (SQL Server) without a problem. If i unplug it, my installer (and ODBC too) find no MSDE/SQL installation.

Can anyone help me? Maybe should i set another option when i install MSDE to make it work on a local computer that is no part of a network? Any advice would be a bid help.

Thank you,
IulianI don't know why i cannot work with MSDE when my computer is no more in network.. pls ..some help would be so needed|||So? Anyone any idea?|||I've got a .CAB and Setup.exe that installs for clients :

- MSDE
- Minimum SQL Server with the db Master, tempdb,... (free part)

Plus a little free Entreprise-Manager-Look-A-Like program

does this interast you ?|||and functions and when the client have a local computer (that isn't in a network)?

Many thanks for your time and help!!!|||the file .Cab is too big to be sent by here
email me your e-mail so that i can send you the package|||i have send u my email adress.

I wait your file.

Tnks again|||i didn't receive your email

MSDE not showing up in SQLDMO.ListAvailableServers

I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
server. I installed a copy of MSDE on the users' machine (this machine did
not have MSDE previously so it was a new copy including latest SP3), then
installed my app. When I start my app, the local computer name does not
show up in the list of available servers. This has happened twice now on
separate machines. Is there something I need to do after installing MSDE
for it to register as a server? Note: I know MSDE is installed and running
because my app creates and properly fills databases on the local machine, it
just doesnt show up in the list of available servers.
Thanks
Dave Taylor
I had the same problem. SQL-DMO does two things when it enumerates SQL
Servers:
1. It creates a list of SQL Servers using the Win32 API NetServerEnum
function
2. It creates a list of SQL Servers that respond to a broadcast on Port
1433
3. It combines these two lists.
If you're not connected to a network, NetServerEnum doesn't seem to return
any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
won't pick up the broadcast. If you're just trying to enumerate the LOCAL
MSDE instance(s), you can read the registry subkeys under
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
HKLM\Software\Microsoft\Microsoft SQL Server.
If anyone out there knows of a function that can be used to return named
instances of MSDE, I'd appreciate the info.
Thanks,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>
|||Michael,
Thanks for the reply and info. At least now I know it isn't something I was
doing wrong.
I'll use your registry workaround for the local info.
Thanks again
Dave Taylor
"Michael C" <michaelc@.nospam.org> wrote in message
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...[vbcol=seagreen]
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
> Thanks,
> Michael C.
>
> "Dave Taylor" <nospam_dave@.processeng.com> wrote in message
> news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
available[vbcol=seagreen]
> did
then[vbcol=seagreen]
on[vbcol=seagreen]
MSDE[vbcol=seagreen]
> running
machine,
> it
>
|||No problem. I just found a website that has a utility for retrieving all
SQL Server information from all computers on a specified subnet via a UDP
packet sent to Port 1434. I tested it on my home network (I don't have MSDE
installed right now though). It might be another way to go... but right
now I'm not sure if it's robust enough for my purposes. Anyway, here's the
link http://www.sqlsecurity.com/DesktopDe...aspx?tabid=26. (One version
includes C# source code). If you do try the SQLPing utility, please let me
know if it returns MSDE info. also.
Enjoy,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:%234E8NcadEHA.3392@.tk2msftngp13.phx.gbl...
> Michael,
> Thanks for the reply and info. At least now I know it isn't something I
was[vbcol=seagreen]
> doing wrong.
> I'll use your registry workaround for the local info.
> Thanks again
> Dave Taylor
> "Michael C" <michaelc@.nospam.org> wrote in message
> news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...
return[vbcol=seagreen]
LOCAL[vbcol=seagreen]
> available
machine[vbcol=seagreen]
> then
not
> on
> MSDE
> machine,
>
|||hi Michael,
"Michael C" <michaelc@.nospam.org> ha scritto nel messaggio
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
SQL-DMO ListAvailableServer uses ODBC function SQLBrowseConnect() provided
by ODBC libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
next problem will be WinXP sp 2 firewall, which will default closing all
ports so that single ports must be manually open by the user in order to
allow network connections... this will defeat dynamic TCP/IP allocation as
you should rely on static assignement for Named instances too...
further method to enlist SQL Server instances available on the LAN can be
found at http://www.sqldev.net/misc/EnumSQLSvr.htm ,
http://www.sqldev.net/misc/ListSQLSvr.htm and
http://www.sqldev.net/misc/OleDbEnum.htm
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast.
default instance always default on port 1433 TCP/IP, while UDP1434 is used
(if network protocols have been enabled) for dynamic named instances
listening in order to redirect to the appropriate TCP/IP dialog port
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||
> In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
> default protocol has been changed to be TCP/IP sockets and instead of a
> NetBIOS broadcast, they use a TCP UDP to detect the servers. The same
logic
> still applies roughly.
> - SQL Server that are running
> - SQL Server that listening on TCP/IP
> - Running on Windows NT or Windows 2000 or Windows 9x
> - If you use routers and these are configured not to pass UDP broadcasts,
> only machines within the same subnet show up.
> Upgrading to Service Pack 2 of SQL Server 2000 is required in order to
have
> .ListAvailableServer method to work properly, becouse precding release of
> Sql-DMO Components of Sql Server 2000 present a bug in this area.
Then again, you might want to quit using SQL-DMO for this,
as Yukon removes SQL-DMO again :-(
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
|||Yukon does not remove DMO, it replaces the 2000 version with DMO9. It does
add a new API called SMO which is the new management API going fwds.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Martijn Tonies" <m.tonies@.upscene-remove-.com> wrote in message
news:ucF3#QbdEHA.3864@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> logic
broadcasts,[vbcol=seagreen]
> have
of
> Then again, you might want to quit using SQL-DMO for this,
> as Yukon removes SQL-DMO again :-(
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server.
> Upscene Productions
> http://www.upscene.com
>
|||Dave,
Note that the current MSDE (2000RevA) install disables the network
connections by default. You will have to enable them using the svmetcn.exe
in the 80\Tools\Binn directory under the install directory. This was
supplied by the current install and I now have my MSDE responding to network
queries on port 1433.
You can also just put '(local)' in as the server name and you should get
a connection locally.
Ron Allen
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>
sql

MSDE not showing up in SQLDMO.ListAvailableServers

I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
server. I installed a copy of MSDE on the users' machine (this machine did
not have MSDE previously so it was a new copy including latest SP3), then
installed my app. When I start my app, the local computer name does not
show up in the list of available servers. This has happened twice now on
separate machines. Is there something I need to do after installing MSDE
for it to register as a server? Note: I know MSDE is installed and running
because my app creates and properly fills databases on the local machine, it
just doesnt show up in the list of available servers.
Thanks
Dave Taylor
I had the same problem. SQL-DMO does two things when it enumerates SQL
Servers:
1. It creates a list of SQL Servers using the Win32 API NetServerEnum
function
2. It creates a list of SQL Servers that respond to a broadcast on Port
1433
3. It combines these two lists.
If you're not connected to a network, NetServerEnum doesn't seem to return
any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
won't pick up the broadcast. If you're just trying to enumerate the LOCAL
MSDE instance(s), you can read the registry subkeys under
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
HKLM\Software\Microsoft\Microsoft SQL Server.
If anyone out there knows of a function that can be used to return named
instances of MSDE, I'd appreciate the info.
Thanks,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>
|||Michael,
Thanks for the reply and info. At least now I know it isn't something I was
doing wrong.
I'll use your registry workaround for the local info.
Thanks again
Dave Taylor
"Michael C" <michaelc@.nospam.org> wrote in message
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...[vbcol=seagreen]
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
> Thanks,
> Michael C.
>
> "Dave Taylor" <nospam_dave@.processeng.com> wrote in message
> news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
available[vbcol=seagreen]
> did
then[vbcol=seagreen]
on[vbcol=seagreen]
MSDE[vbcol=seagreen]
> running
machine,
> it
>
|||No problem. I just found a website that has a utility for retrieving all
SQL Server information from all computers on a specified subnet via a UDP
packet sent to Port 1434. I tested it on my home network (I don't have MSDE
installed right now though). It might be another way to go... but right
now I'm not sure if it's robust enough for my purposes. Anyway, here's the
link http://www.sqlsecurity.com/DesktopDe...aspx?tabid=26. (One version
includes C# source code). If you do try the SQLPing utility, please let me
know if it returns MSDE info. also.
Enjoy,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:%234E8NcadEHA.3392@.tk2msftngp13.phx.gbl...
> Michael,
> Thanks for the reply and info. At least now I know it isn't something I
was[vbcol=seagreen]
> doing wrong.
> I'll use your registry workaround for the local info.
> Thanks again
> Dave Taylor
> "Michael C" <michaelc@.nospam.org> wrote in message
> news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...
return[vbcol=seagreen]
LOCAL[vbcol=seagreen]
> available
machine[vbcol=seagreen]
> then
not
> on
> MSDE
> machine,
>
|||hi Michael,
"Michael C" <michaelc@.nospam.org> ha scritto nel messaggio
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
SQL-DMO ListAvailableServer uses ODBC function SQLBrowseConnect() provided
by ODBC libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
next problem will be WinXP sp 2 firewall, which will default closing all
ports so that single ports must be manually open by the user in order to
allow network connections... this will defeat dynamic TCP/IP allocation as
you should rely on static assignement for Named instances too...
further method to enlist SQL Server instances available on the LAN can be
found at http://www.sqldev.net/misc/EnumSQLSvr.htm ,
http://www.sqldev.net/misc/ListSQLSvr.htm and
http://www.sqldev.net/misc/OleDbEnum.htm
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast.
default instance always default on port 1433 TCP/IP, while UDP1434 is used
(if network protocols have been enabled) for dynamic named instances
listening in order to redirect to the appropriate TCP/IP dialog port
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||
> In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
> default protocol has been changed to be TCP/IP sockets and instead of a
> NetBIOS broadcast, they use a TCP UDP to detect the servers. The same
logic
> still applies roughly.
> - SQL Server that are running
> - SQL Server that listening on TCP/IP
> - Running on Windows NT or Windows 2000 or Windows 9x
> - If you use routers and these are configured not to pass UDP broadcasts,
> only machines within the same subnet show up.
> Upgrading to Service Pack 2 of SQL Server 2000 is required in order to
have
> .ListAvailableServer method to work properly, becouse precding release of
> Sql-DMO Components of Sql Server 2000 present a bug in this area.
Then again, you might want to quit using SQL-DMO for this,
as Yukon removes SQL-DMO again :-(
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
|||Yukon does not remove DMO, it replaces the 2000 version with DMO9. It does
add a new API called SMO which is the new management API going fwds.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Martijn Tonies" <m.tonies@.upscene-remove-.com> wrote in message
news:ucF3#QbdEHA.3864@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> logic
broadcasts,[vbcol=seagreen]
> have
of
> Then again, you might want to quit using SQL-DMO for this,
> as Yukon removes SQL-DMO again :-(
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server.
> Upscene Productions
> http://www.upscene.com
>
|||Dave,
Note that the current MSDE (2000RevA) install disables the network
connections by default. You will have to enable them using the svmetcn.exe
in the 80\Tools\Binn directory under the install directory. This was
supplied by the current install and I now have my MSDE responding to network
queries on port 1433.
You can also just put '(local)' in as the server name and you should get
a connection locally.
Ron Allen
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>

MSDE not showing up in SQLDMO.ListAvailableServers

I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
server. I installed a copy of MSDE on the users' machine (this machine did
not have MSDE previously so it was a new copy including latest SP3), then
installed my app. When I start my app, the local computer name does not
show up in the list of available servers. This has happened twice now on
separate machines. Is there something I need to do after installing MSDE
for it to register as a server? Note: I know MSDE is installed and running
because my app creates and properly fills databases on the local machine, it
just doesnt show up in the list of available servers.
Thanks
Dave Taylor
I had the same problem. SQL-DMO does two things when it enumerates SQL
Servers:
1. It creates a list of SQL Servers using the Win32 API NetServerEnum
function
2. It creates a list of SQL Servers that respond to a broadcast on Port
1433
3. It combines these two lists.
If you're not connected to a network, NetServerEnum doesn't seem to return
any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
won't pick up the broadcast. If you're just trying to enumerate the LOCAL
MSDE instance(s), you can read the registry subkeys under
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
HKLM\Software\Microsoft\Microsoft SQL Server.
If anyone out there knows of a function that can be used to return named
instances of MSDE, I'd appreciate the info.
Thanks,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>
|||Michael,
Thanks for the reply and info. At least now I know it isn't something I was
doing wrong.
I'll use your registry workaround for the local info.
Thanks again
Dave Taylor
"Michael C" <michaelc@.nospam.org> wrote in message
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...[vbcol=seagreen]
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
> Thanks,
> Michael C.
>
> "Dave Taylor" <nospam_dave@.processeng.com> wrote in message
> news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
available[vbcol=seagreen]
> did
then[vbcol=seagreen]
on[vbcol=seagreen]
MSDE[vbcol=seagreen]
> running
machine,
> it
>
|||No problem. I just found a website that has a utility for retrieving all
SQL Server information from all computers on a specified subnet via a UDP
packet sent to Port 1434. I tested it on my home network (I don't have MSDE
installed right now though). It might be another way to go... but right
now I'm not sure if it's robust enough for my purposes. Anyway, here's the
link http://www.sqlsecurity.com/DesktopDe...aspx?tabid=26. (One version
includes C# source code). If you do try the SQLPing utility, please let me
know if it returns MSDE info. also.
Enjoy,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:%234E8NcadEHA.3392@.tk2msftngp13.phx.gbl...
> Michael,
> Thanks for the reply and info. At least now I know it isn't something I
was[vbcol=seagreen]
> doing wrong.
> I'll use your registry workaround for the local info.
> Thanks again
> Dave Taylor
> "Michael C" <michaelc@.nospam.org> wrote in message
> news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...
return[vbcol=seagreen]
LOCAL[vbcol=seagreen]
> available
machine[vbcol=seagreen]
> then
not
> on
> MSDE
> machine,
>
|||hi Michael,
"Michael C" <michaelc@.nospam.org> ha scritto nel messaggio
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
SQL-DMO ListAvailableServer uses ODBC function SQLBrowseConnect() provided
by ODBC libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
next problem will be WinXP sp 2 firewall, which will default closing all
ports so that single ports must be manually open by the user in order to
allow network connections... this will defeat dynamic TCP/IP allocation as
you should rely on static assignement for Named instances too...
further method to enlist SQL Server instances available on the LAN can be
found at http://www.sqldev.net/misc/EnumSQLSvr.htm ,
http://www.sqldev.net/misc/ListSQLSvr.htm and
http://www.sqldev.net/misc/OleDbEnum.htm
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast.
default instance always default on port 1433 TCP/IP, while UDP1434 is used
(if network protocols have been enabled) for dynamic named instances
listening in order to redirect to the appropriate TCP/IP dialog port
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||
> In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
> default protocol has been changed to be TCP/IP sockets and instead of a
> NetBIOS broadcast, they use a TCP UDP to detect the servers. The same
logic
> still applies roughly.
> - SQL Server that are running
> - SQL Server that listening on TCP/IP
> - Running on Windows NT or Windows 2000 or Windows 9x
> - If you use routers and these are configured not to pass UDP broadcasts,
> only machines within the same subnet show up.
> Upgrading to Service Pack 2 of SQL Server 2000 is required in order to
have
> .ListAvailableServer method to work properly, becouse precding release of
> Sql-DMO Components of Sql Server 2000 present a bug in this area.
Then again, you might want to quit using SQL-DMO for this,
as Yukon removes SQL-DMO again :-(
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
|||Yukon does not remove DMO, it replaces the 2000 version with DMO9. It does
add a new API called SMO which is the new management API going fwds.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Martijn Tonies" <m.tonies@.upscene-remove-.com> wrote in message
news:ucF3#QbdEHA.3864@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> logic
broadcasts,[vbcol=seagreen]
> have
of
> Then again, you might want to quit using SQL-DMO for this,
> as Yukon removes SQL-DMO again :-(
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server.
> Upscene Productions
> http://www.upscene.com
>
|||Dave,
Note that the current MSDE (2000RevA) install disables the network
connections by default. You will have to enable them using the svmetcn.exe
in the 80\Tools\Binn directory under the install directory. This was
supplied by the current install and I now have my MSDE responding to network
queries on port 1433.
You can also just put '(local)' in as the server name and you should get
a connection locally.
Ron Allen
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>

MSDE not showing up in SQLDMO.ListAvailableServers

I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
server. I installed a copy of MSDE on the users' machine (this machine did
not have MSDE previously so it was a new copy including latest SP3), then
installed my app. When I start my app, the local computer name does not
show up in the list of available servers. This has happened twice now on
separate machines. Is there something I need to do after installing MSDE
for it to register as a server? Note: I know MSDE is installed and running
because my app creates and properly fills databases on the local machine, it
just doesnt show up in the list of available servers.
Thanks
Dave TaylorI had the same problem. SQL-DMO does two things when it enumerates SQL
Servers:
1. It creates a list of SQL Servers using the Win32 API NetServerEnum
function
2. It creates a list of SQL Servers that respond to a broadcast on Port
1433
3. It combines these two lists.
If you're not connected to a network, NetServerEnum doesn't seem to return
any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
won't pick up the broadcast. If you're just trying to enumerate the LOCAL
MSDE instance(s), you can read the registry subkeys under
HKLM\Software\Microsoft\MSSQLServer\MSSQ
LServer and
HKLM\Software\Microsoft\Microsoft SQL Server.
If anyone out there knows of a function that can be used to return named
instances of MSDE, I'd appreciate the info.
Thanks,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>|||Michael,
Thanks for the reply and info. At least now I know it isn't something I was
doing wrong.
I'll use your registry workaround for the local info.
Thanks again
Dave Taylor
"Michael C" <michaelc@.nospam.org> wrote in message
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.net...
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQ
LServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
> Thanks,
> Michael C.
>
> "Dave Taylor" <nospam_dave@.processeng.com> wrote in message
> news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
available[vbcol=seagreen]
> did
then[vbcol=seagreen]
on[vbcol=seagreen]
MSDE[vbcol=seagreen]
> running
machine,[vbcol=seagreen]
> it
>|||No problem. I just found a website that has a utility for retrieving all
SQL Server information from all computers on a specified subnet via a UDP
packet sent to Port 1434. I tested it on my home network (I don't have MSDE
installed right now though). It might be another way to go... but right
now I'm not sure if it's robust enough for my purposes. Anyway, here's the
link http://www.sqlsecurity.com/DesktopD...aspx?tabid=26. (One version
includes C# source code). If you do try the SQLPing utility, please let me
know if it returns MSDE info. also.
Enjoy,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:%234E8NcadEHA.3392@.tk2msftngp13.phx.gbl...
> Michael,
> Thanks for the reply and info. At least now I know it isn't something I
was
> doing wrong.
> I'll use your registry workaround for the local info.
> Thanks again
> Dave Taylor
> "Michael C" <michaelc@.nospam.org> wrote in message
> news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.net...
return[vbcol=seagreen]
LOCAL[vbcol=seagreen]
> available
machine[vbcol=seagreen]
> then
not[vbcol=seagreen]
> on
> MSDE
> machine,
>|||hi Michael,
"Michael C" <michaelc@.nospam.org> ha scritto nel messaggio
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.net...
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQ
LServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
SQL-DMO ListAvailableServer uses ODBC function SQLBrowseConnect() provided
by ODBC libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
.ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
next problem will be WinXP sp 2 firewall, which will default closing all
ports so that single ports must be manually open by the user in order to
allow network connections... this will defeat dynamic TCP/IP allocation as
you should rely on static assignement for Named instances too...
further method to enlist SQL Server instances available on the LAN can be
found at http://www.sqldev.net/misc/EnumSQLSvr.htm ,
http://www.sqldev.net/misc/ListSQLSvr.htm and
http://www.sqldev.net/misc/OleDbEnum.htm
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||>MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast.
default instance always default on port 1433 TCP/IP, while UDP1434 is used
(if network protocols have been enabled) for dynamic named instances
listening in order to redirect to the appropriate TCP/IP dialog port
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||
> In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
> default protocol has been changed to be TCP/IP sockets and instead of a
> NetBIOS broadcast, they use a TCP UDP to detect the servers. The same
logic
> still applies roughly.
> - SQL Server that are running
> - SQL Server that listening on TCP/IP
> - Running on Windows NT or Windows 2000 or Windows 9x
> - If you use routers and these are configured not to pass UDP broadcasts,
> only machines within the same subnet show up.
> Upgrading to Service Pack 2 of SQL Server 2000 is required in order to
have
> .ListAvailableServer method to work properly, becouse precding release of
> Sql-DMO Components of Sql Server 2000 present a bug in this area.
Then again, you might want to quit using SQL-DMO for this,
as Yukon removes SQL-DMO again :-(
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server.
Upscene Productions
http://www.upscene.com|||Yukon does not remove DMO, it replaces the 2000 version with DMO9. It does
add a new API called SMO which is the new management API going fwds.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Martijn Tonies" <m.tonies@.upscene-remove-.com> wrote in message
news:ucF3#QbdEHA.3864@.TK2MSFTNGP10.phx.gbl...
>
> logic
broadcasts,[vbcol=seagreen]
> have
of[vbcol=seagreen]
> Then again, you might want to quit using SQL-DMO for this,
> as Yukon removes SQL-DMO again :-(
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
SQL
> Server.
> Upscene Productions
> http://www.upscene.com
>|||Dave,
Note that the current MSDE (2000RevA) install disables the network
connections by default. You will have to enable them using the svmetcn.exe
in the 80\Tools\Binn directory under the install directory. This was
supplied by the current install and I now have my MSDE responding to network
queries on port 1433.
You can also just put '(local)' in as the server name and you should get
a connection locally.
Ron Allen
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>

msde memory setting

What memory settings should i configure to tune msde?
Sometimes it will be installed on a PC but at other times it will be an app
server. On the app server i want to take advantage of the increase in memory.
hi,
Keeper wrote:
> What memory settings should i configure to tune msde?
> Sometimes it will be installed on a PC but at other times it will be
> an app server. On the app server i want to take advantage of the
> increase in memory.
usually you should not manually "tune" SQL Server as it is tailored to self
configure it self...
on the other hand, SQL Server is very resources intensive, and is designed
to take advantage of all the installed available memory (in sys32 processes
limit )
and it's a very challenging taks to manual configure it for best
performance..
remember that memory usage is database and application dependent, but usage
and workloads dependent too...
Loosely speaking, SQL Server organize it's memory allocation in two distinct
regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
regions. (I'm excluding use of AWE in order to provide easy understanding
and because MSDE does not admit it)..
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, linked server drivers, NetLib dlls, in process COM objects space
and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
more, each connection, live or sleeping, will eat about 24kb of memory,
calculated as 12 KB + 3 times the Network Packet Size (default setting that
can be partially customized via sp_configure system stored procedure
modifying the 'user connections' setting), used to store the data structures
holding the connection 's context, as long as for buffer used to send and
receive the relative associated network streams (default to 4KB network
packet setting), that can be stolen from the buffer pool memory region
and/or the MemToLeave memory area...
not limiting the resources, SQL Server can reclaim that memory (up to 1 gb,
in our example) for it's uses, and it will perhaps release it only under
pressure by the OS claiming for additional memory, if it's the case, else it
will maintain that memory in order to cache pages and execution plans...
under OS pressure, SQL Server will try having the Lazy Writer releasing
resources, but you are not granted that this will occur, even if SQL Server
will always try to leave some memory for other OS needs, usually between 4
and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
memory usage climbing to the top and staying there for long time, becouse of
SQL Server tends to keep pages read from disk in memory to increase further
and successive access to those pages, as long as query plans and so on... on
a heavy loaded server, if you add web server duty, you will have resources
contentions for sure...
I strongly advice you for
http://www.windowsitpro.com/Article/...908/37908.html and
http://www.windowsitpro.com/Article/...890/37890.html
another interesting readings:
http://msdn.microsoft.com/library/de...v_03252004.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
Keeper wrote:
> What memory settings should i configure to tune msde?
> Sometimes it will be installed on a PC but at other times it will be
> an app server. On the app server i want to take advantage of the
> increase in memory.
usually you should not manually "tune" SQL Server as it is tailored to self
configure it self...
on the other hand, SQL Server is very resources intensive, and is designed
to take advantage of all the installed available memory (in sys32 processes
limit )
and it's a very challenging taks to manual configure it for best
performance..
remember that memory usage is database and application dependent, but usage
and workloads dependent too...
Loosely speaking, SQL Server organize it's memory allocation in two distinct
regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
regions. (I'm excluding use of AWE in order to provide easy understanding
and because MSDE does not admit it)..
so... the BPool is the primary region SQL Server uses for it's internal
matter, while MemToLeave consists of the virtual memory space within the 1gb
user mode address space and the memory not used by the BPool.
when SQL Server starts, it begins calculating the upper limit the BPool can
reach... if no MaxMemory is set, this value will be set to the amount of the
physical memory or the size of the user mode address space (1gb) , minus the
size of the MemToLeave, whichever is less..
by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
providers, linked server drivers, NetLib dlls, in process COM objects space
and memory requirements and so on..
when MaxMemory value is explicitally set, this upper limit will only address
BPool region needs...
so only the address space of BPool pages is limited by this configuration
value, while SQL Server memory requirememts outside BPool allocation are not
limited this way...
more, each connection, live or sleeping, will eat about 24kb of memory,
calculated as 12 KB + 3 times the Network Packet Size (default setting that
can be partially customized via sp_configure system stored procedure
modifying the 'user connections' setting), used to store the data structures
holding the connection 's context, as long as for buffer used to send and
receive the relative associated network streams (default to 4KB network
packet setting), that can be stolen from the buffer pool memory region
and/or the MemToLeave memory area...
not limiting the resources, SQL Server can reclaim that memory (up to 1 gb,
in our example) for it's uses, and it will perhaps release it only under
pressure by the OS claiming for additional memory, if it's the case, else it
will maintain that memory in order to cache pages and execution plans...
under OS pressure, SQL Server will try having the Lazy Writer releasing
resources, but you are not granted that this will occur, even if SQL Server
will always try to leave some memory for other OS needs, usually between 4
and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
memory usage climbing to the top and staying there for long time, becouse of
SQL Server tends to keep pages read from disk in memory to increase further
and successive access to those pages, as long as query plans and so on... on
a heavy loaded server, if you add web server duty, you will have resources
contentions for sure...
I strongly advice you for
http://www.windowsitpro.com/Article/...908/37908.html and
http://www.windowsitpro.com/Article/...890/37890.html
another interesting readings:
http://msdn.microsoft.com/library/de...v_03252004.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||i appreciate the information
thanx
"Andrea Montanari" wrote:

> hi,
> Keeper wrote:
> usually you should not manually "tune" SQL Server as it is tailored to self
> configure it self...
> on the other hand, SQL Server is very resources intensive, and is designed
> to take advantage of all the installed available memory (in sys32 processes
> limit )
> and it's a very challenging taks to manual configure it for best
> performance..
> remember that memory usage is database and application dependent, but usage
> and workloads dependent too...
> Loosely speaking, SQL Server organize it's memory allocation in two distinct
> regions, the "buffer pool" (BPool) and the "memory to leave" (MemToLeave)
> regions. (I'm excluding use of AWE in order to provide easy understanding
> and because MSDE does not admit it)..
> so... the BPool is the primary region SQL Server uses for it's internal
> matter, while MemToLeave consists of the virtual memory space within the 1gb
> user mode address space and the memory not used by the BPool.
> when SQL Server starts, it begins calculating the upper limit the BPool can
> reach... if no MaxMemory is set, this value will be set to the amount of the
> physical memory or the size of the user mode address space (1gb) , minus the
> size of the MemToLeave, whichever is less..
> by default, MemToLeave is set to 384mb, 128mb of them are for worker thread
> stacks and 256mb for allocation outside the BPool, such as memory for OLE-DB
> providers, linked server drivers, NetLib dlls, in process COM objects space
> and memory requirements and so on..
> when MaxMemory value is explicitally set, this upper limit will only address
> BPool region needs...
> so only the address space of BPool pages is limited by this configuration
> value, while SQL Server memory requirememts outside BPool allocation are not
> limited this way...
> more, each connection, live or sleeping, will eat about 24kb of memory,
> calculated as 12 KB + 3 times the Network Packet Size (default setting that
> can be partially customized via sp_configure system stored procedure
> modifying the 'user connections' setting), used to store the data structures
> holding the connection 's context, as long as for buffer used to send and
> receive the relative associated network streams (default to 4KB network
> packet setting), that can be stolen from the buffer pool memory region
> and/or the MemToLeave memory area...
> not limiting the resources, SQL Server can reclaim that memory (up to 1 gb,
> in our example) for it's uses, and it will perhaps release it only under
> pressure by the OS claiming for additional memory, if it's the case, else it
> will maintain that memory in order to cache pages and execution plans...
> under OS pressure, SQL Server will try having the Lazy Writer releasing
> resources, but you are not granted that this will occur, even if SQL Server
> will always try to leave some memory for other OS needs, usually between 4
> and 10mb of RAM... so, on SQL Server dedicated servers, you usually see
> memory usage climbing to the top and staying there for long time, becouse of
> SQL Server tends to keep pages read from disk in memory to increase further
> and successive access to those pages, as long as query plans and so on... on
> a heavy loaded server, if you add web server duty, you will have resources
> contentions for sure...
> I strongly advice you for
> http://www.windowsitpro.com/Article/...908/37908.html and
> http://www.windowsitpro.com/Article/...890/37890.html
> another interesting readings:
> http://msdn.microsoft.com/library/de...v_03252004.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>

MSDE Licensing In Web App?

I have an ASP.NET web application that will be accessed from the Internet.
Does the MSDE redistributable license cover this situation?
Thanks,
<WARNING: I'm not a lawyer>
Yes. It's commonly used that way. The biggest limitation is usually the
database size limit if you make good use of connection pooling.
</WARNING: I'm not a lawyer>
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Hani Atassi" <hani@.community.nospam> wrote in message
news:89E6E948-F597-41DF-9F8A-61F0DBC2177D@.microsoft.com...
>I have an ASP.NET web application that will be accessed from the Internet.
> Does the MSDE redistributable license cover this situation?
> Thanks,
|||you may also need a Windows Server External Connector License.
If you are running on Windows Server and the external connections are not
from employees, then you need the EC. If they are employees, then you need
CALs for those employees.
-Dino
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:eSlqRs9xEHA.3840@.tk2msftngp13.phx.gbl...
> <WARNING: I'm not a lawyer>
> Yes. It's commonly used that way. The biggest limitation is usually the
> database size limit if you make good use of connection pooling.
> </WARNING: I'm not a lawyer>
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Hani Atassi" <hani@.community.nospam> wrote in message
> news:89E6E948-F597-41DF-9F8A-61F0DBC2177D@.microsoft.com...
>

MSDE Licensing

The MSDE licence says that it can be used for up to 5 concurrent users. My app is an ASP.NET app, so won't I just be using one user - the ASP.NET account? I'm wondering whether we can get away with just buying a new server and sticking MSDE on it rather than shelling out for another £5000 SQL server licence...I do not believe what you say (5 concurrent users) is correct.

http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp.

From this page:

Q. Can I use MSDE as a database for Web applications?

A. Yes, MSDE is an ideal solution for basic Web applications with up to 25 concurrent users.

There is throttling of performance with 8 concurrent OPERATIONS (but three are normally used internally).

Fromthis page:

The Microsoft® SQL Server? 2000 workload governor is designed to limit the performance of an instance of the database engine any time more than eight operations are active at the same time. An instance of the SQL Server 2000 database engine is one copy of the database software that operates as an operating system service.

The operations counted by the workload governor are:

Processing a request to open an inbound connection and login.

Processing a batch of one or more Transact-SQL statements received over an inbound connection.

Processing a distributed transaction operation, such as a prepare-to-commit or rollback operation.

Processing a request to log off and close an inbound connection.

Periodic system-generated operations such as shrinking a database if the database has the AUTO_SHRINK option turned on, completing the deletion of rows from the base level of indexes, or populating the SQL Server performance counters in the System Monitor.

Instances of SQL Server 2000 Personal Edition will also periodically generate system operations to process any full-text indexes referenced by the databases managed by the instance. SQL Server 2000 Desktop Engine (MSDE 2000) does not support full-text indexes.|||Sorry - missed a "2" out there.... Thats answered the question great - thanks a lot!

MSDE License when used with non-Microsoft database.

MSDE would make a great local cache in my app that uses dialup networking.
However, the App comminicates with sybase running on Linux.
Am I allows to use MSDE this way? Or should I use an open source database
for a local cache?
Thanks
From http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp it looks like
you're ok using MSDE.
joe.
"Karl" <Karl@.discussions.microsoft.com> wrote in message
news:F055A34F-DE27-4B55-A0B7-3E1B14F33097@.microsoft.com...
> MSDE would make a great local cache in my app that uses dialup networking.
> However, the App comminicates with sybase running on Linux.
> Am I allows to use MSDE this way? Or should I use an open source database
> for a local cache?
> Thanks
>