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 to SQL Server 2005 Express

Howdy,
My company is setting up a new dedicated hosting server and we're debating
using the new SQL 2005 verse the MSDE. I'm not familiar with the new
version, so I have one big question:: Outside of the connection string, will
any syntax in my existing ASP.NET script/stored procs have to change to
support SQL 2005? Is the migration process painless?
I've searched MSDN but really can't find what I'm looking for.
Thanks,
David Lozzi
Web Applications Developer
dlozzi@.(remove-this)delphi-ts.comHello David,

> Outside of the
> connection string, will any syntax in my existing ASP.NET
> script/stored procs have to change to support SQL 2005?
Without specifics, its hard to say precisely. Some things change some, a
few a lot, most hardly at all. There's an upgrade advisor tool on the distri
bution
media and here [0] that I'd recommend starting with. It should catch a major
ity
of the changes needed with a minimum of effort.

> Is the
> migration process painless?
Again, that depends on what you've done in the past. I suspect not, but the
upgrade advisor will help you know more for sure.
[0]: http://www.microsoft.com/downloads/...&displaylang=en
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

MSDE sql express on laptop, error 26

hi, I am running Visual Studio.net 2005 on my laptop, MSDE sql express was
installed during the installation, finally trying to get an sql database to
work, i am a newbie to both Visual studio and SQL, anyway i can see the
database inside server explorer and all its talbles, but when i try to run
the application i get an error has occured while establishing a connection
the the server, When connecting to SQL server 2005, this failure may be
caused by the fact that under the default settings SQL server does not allow
remote connections, (provider SQL network interfaces, error:26 error
locatiang server/instance Specified
i read similar posts in this forum and tried the following
enabled TCP/Ip on the server and client, do i need any of the other
protocols enabled: shared memory is enabled, named pipes is disabled, tcp/ip
is enablaed and VIA is disabled, this is for the server, for the client
shared memory, tcp/ip, named pipes are all enabled while via is disabled
i ran the SQL server surface area configuration and choose the Surface area
configuration for servcies and connections, under there, the radio buttion
for local and remote connection/Using TCP/IP only are selected. so i think
that is ok
i copi8ed a SQL database to a folder in the database and set the connection
using the wizard, i tested the connection and it is ok.
are there other things to do are other places to check,
Possibility 1:
Start the SQL Server Browser Service. SQL Server 2005 Express installs as a
named instance called sqlexpress. The SQL Server Browser Service resolves a
connection attempt of <computer_name>\sqlexpress to a port number. Without
SQL Server Browser, when connecting to a named instance you need to specify
the port number when you connect. And all named instances like SQL Server
2005 listen on dynamic ports, so the port may change when SQL Server is
started.
Possibility 2:
If you are running on a laptop, you may be running on Windows XP, service
pack 2 which automatically turns on the Windows firewall. If you are
connecting from the same server, you should be connecting using Shared
Memory so that won't matter. If you are connecting from another computer,
you may need to open a TCP port, or allow sqlservr.exe to communicate
through the firewall.
If this information is helpful, more information is available at
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Frank Bruhn" <FrankBruhn@.discussions.microsoft.com> wrote in message
news:E96EEA76-4277-4D80-8B75-79D945744E95@.microsoft.com...
> hi, I am running Visual Studio.net 2005 on my laptop, MSDE sql express was
> installed during the installation, finally trying to get an sql database
> to
> work, i am a newbie to both Visual studio and SQL, anyway i can see the
> database inside server explorer and all its talbles, but when i try to run
> the application i get an error has occured while establishing a connection
> the the server, When connecting to SQL server 2005, this failure may be
> caused by the fact that under the default settings SQL server does not
> allow
> remote connections, (provider SQL network interfaces, error:26 error
> locatiang server/instance Specified
> i read similar posts in this forum and tried the following
> enabled TCP/Ip on the server and client, do i need any of the other
> protocols enabled: shared memory is enabled, named pipes is disabled,
> tcp/ip
> is enablaed and VIA is disabled, this is for the server, for the client
> shared memory, tcp/ip, named pipes are all enabled while via is disabled
> i ran the SQL server surface area configuration and choose the Surface
> area
> configuration for servcies and connections, under there, the radio buttion
> for local and remote connection/Using TCP/IP only are selected. so i think
> that is ok
> i copi8ed a SQL database to a folder in the database and set the
> connection
> using the wizard, i tested the connection and it is ok.
> are there other things to do are other places to check,
>
>

MSDE sql express on laptop, error 26

hi, I am running Visual Studio.net 2005 on my laptop, MSDE sql express was
installed during the installation, finally trying to get an sql database to
work, i am a newbie to both Visual studio and SQL, anyway i can see the
database inside server explorer and all its talbles, but when i try to run
the application i get an error has occured while establishing a connection
the the server, When connecting to SQL server 2005, this failure may be
caused by the fact that under the default settings SQL server does not allow
remote connections, (provider SQL network interfaces, error:26 error
locatiang server/instance Specified
i read similar posts in this forum and tried the following
enabled TCP/Ip on the server and client, do i need any of the other
protocols enabled: shared memory is enabled, named pipes is disabled, tcp/i
p
is enablaed and VIA is disabled, this is for the server, for the client
shared memory, tcp/ip, named pipes are all enabled while via is disabled
i ran the SQL server surface area configuration and choose the Surface area
configuration for servcies and connections, under there, the radio buttion
for local and remote connection/Using TCP/IP only are selected. so i think
that is ok
i copi8ed a SQL database to a folder in the database and set the connection
using the wizard, i tested the connection and it is ok.
are there other things to do are other places to check,Possibility 1:
Start the SQL Server Browser Service. SQL Server 2005 Express installs as a
named instance called sqlexpress. The SQL Server Browser Service resolves a
connection attempt of <computer_name>\sqlexpress to a port number. Without
SQL Server Browser, when connecting to a named instance you need to specify
the port number when you connect. And all named instances like SQL Server
2005 listen on dynamic ports, so the port may change when SQL Server is
started.
Possibility 2:
If you are running on a laptop, you may be running on Windows XP, service
pack 2 which automatically turns on the Windows firewall. If you are
connecting from the same server, you should be connecting using Shared
Memory so that won't matter. If you are connecting from another computer,
you may need to open a TCP port, or allow sqlservr.exe to communicate
through the firewall.
If this information is helpful, more information is available at
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
--
Rick Byham
MCDBA, MCSE, MCSA
Lead Technical Writer,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Frank Bruhn" <FrankBruhn@.discussions.microsoft.com> wrote in message
news:E96EEA76-4277-4D80-8B75-79D945744E95@.microsoft.com...
> hi, I am running Visual Studio.net 2005 on my laptop, MSDE sql express was
> installed during the installation, finally trying to get an sql database
> to
> work, i am a newbie to both Visual studio and SQL, anyway i can see the
> database inside server explorer and all its talbles, but when i try to run
> the application i get an error has occured while establishing a connection
> the the server, When connecting to SQL server 2005, this failure may be
> caused by the fact that under the default settings SQL server does not
> allow
> remote connections, (provider SQL network interfaces, error:26 error
> locatiang server/instance Specified
> i read similar posts in this forum and tried the following
> enabled TCP/Ip on the server and client, do i need any of the other
> protocols enabled: shared memory is enabled, named pipes is disabled,
> tcp/ip
> is enablaed and VIA is disabled, this is for the server, for the client
> shared memory, tcp/ip, named pipes are all enabled while via is disabled
> i ran the SQL server surface area configuration and choose the Surface
> area
> configuration for servcies and connections, under there, the radio buttion
> for local and remote connection/Using TCP/IP only are selected. so i think
> that is ok
> i copi8ed a SQL database to a folder in the database and set the
> connection
> using the wizard, i tested the connection and it is ok.
> are there other things to do are other places to check,
>
>

MSDE Performance verses SQL 2005 Express

I have been testing one of our Apps under SQL 2005 Express and I am seeing a big downgrade in performance compared to MSDE on the same hardware. Has anyone else experienced this?

Cheers

Aussie Coder

Hi Aussie Coder,

Could you give me an idea what specific tasks your testing and what the change in performance has been in your tests?

Regards,

Mike Wachal
SQL Express team

|||The tests involved loading a list control with a page of records and then using the down arrow key to scroll through a dataset of 2000 records. There is only enough records stored on the client side to fill the list control. I believe the data access engine I am using implements a server side cursor to access each record on an as needed basis. It also provides some buffering of the records, so it could be buffering 2 or three pages of records ahead, but this is transparent to my use of the engine. In my testing, the SQL Server was running on the same computer as the client app.

Under MSDE 2000 Release A, the scrolling is quite smooth and responsive. Under MSSQL 2005 Express, there is a noticable lag in the retrieval of records as the display page is updated.

The update process for scrolling down involves removing the top record from the page, retrieving the next record in sequence and adding it to the bottom of the page and then redrawing the list.

I hope this adds some light to what might be happening

Cheers
Aussie Coder|||

Getting back to this after a while, sorry. There was not any significant performance testing done comparing SQL Express and MSDE, so I can't offer much. I'm working on correcting this oversight, so I'll keep your scenario in mind as I push the team to do some comparisons.

Mike

|||Any new info on this? Our app does very similar caching for list controls.

MSDE Performance verses SQL 2005 Express

I have been testing one of our Apps under SQL 2005 Express and I am seeing a big downgrade in performance compared to MSDE on the same hardware. Has anyone else experienced this?

Cheers

Aussie Coder

Hi Aussie Coder,

Could you give me an idea what specific tasks your testing and what the change in performance has been in your tests?

Regards,

Mike Wachal
SQL Express team

|||The tests involved loading a list control with a page of records and then using the down arrow key to scroll through a dataset of 2000 records. There is only enough records stored on the client side to fill the list control. I believe the data access engine I am using implements a server side cursor to access each record on an as needed basis. It also provides some buffering of the records, so it could be buffering 2 or three pages of records ahead, but this is transparent to my use of the engine. In my testing, the SQL Server was running on the same computer as the client app.

Under MSDE 2000 Release A, the scrolling is quite smooth and responsive. Under MSSQL 2005 Express, there is a noticable lag in the retrieval of records as the display page is updated.

The update process for scrolling down involves removing the top record from the page, retrieving the next record in sequence and adding it to the bottom of the page and then redrawing the list.

I hope this adds some light to what might be happening

Cheers
Aussie Coder|||

Getting back to this after a while, sorry. There was not any significant performance testing done comparing SQL Express and MSDE, so I can't offer much. I'm working on correcting this oversight, so I'll keep your scenario in mind as I push the team to do some comparisons.

Mike

|||Any new info on this? Our app does very similar caching for list controls.

MSDE Performance verses SQL 2005 Express

I have been testing one of our Apps under SQL 2005 Express and I am seeing a big downgrade in performance compared to MSDE on the same hardware. Has anyone else experienced this?

Cheers

Aussie Coder

Hi Aussie Coder,

Could you give me an idea what specific tasks your testing and what the change in performance has been in your tests?

Regards,

Mike Wachal
SQL Express team

|||The tests involved loading a list control with a page of records and then using the down arrow key to scroll through a dataset of 2000 records. There is only enough records stored on the client side to fill the list control. I believe the data access engine I am using implements a server side cursor to access each record on an as needed basis. It also provides some buffering of the records, so it could be buffering 2 or three pages of records ahead, but this is transparent to my use of the engine. In my testing, the SQL Server was running on the same computer as the client app.

Under MSDE 2000 Release A, the scrolling is quite smooth and responsive. Under MSSQL 2005 Express, there is a noticable lag in the retrieval of records as the display page is updated.

The update process for scrolling down involves removing the top record from the page, retrieving the next record in sequence and adding it to the bottom of the page and then redrawing the list.

I hope this adds some light to what might be happening

Cheers
Aussie Coder

|||

Getting back to this after a while, sorry. There was not any significant performance testing done comparing SQL Express and MSDE, so I can't offer much. I'm working on correcting this oversight, so I'll keep your scenario in mind as I push the team to do some comparisons.

Mike

|||Any new info on this? Our app does very similar caching for list controls.sql

MSDE or SQLServer Express

Hi
In our vb.net application we are using sqlserver2000 database. we are
planning to move to another free database engines.I have few queries.
1.in MSDE or SQLServer Express which one i have to select
2.Where can i get the documentation of how to dump my database in those
because those don't have a User interface.
3.can i redistribute those databases
4.can i access those stored in another system in a network.
5.what if 2 or 3 application directly accessing that at the same time.
hope i delivered my question properly.any help is appreciable
-regards
GRK
Hi Andrea ,
Thank you very much for your suggestion.I'll check the links you have
sent and come back to you with other queries.Thanks a lot
-GRK
"Andrea Montanari" wrote:

> hi,
> GRK wrote:
> SQLExpress is newer than MSDE, and MSDE will not be supported on Vista
> platform..
> I'd go for SQLExpress as it will be better supported anyway..
> for SQLExpress you can freely download the "official" management tool, SQL
> Server Management Studio Express at
> http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
> for MSDE there's no official graphical tool available, but you can have a
> look at a free prj of mine, DbaMgr2k, available at
> http://www.asql.biz/en/DbaMgr.aspx
> both engines are free to use.. you have to freely register for distribution
> if you plan to;
> SQLExpress: http://go.microsoft.com/fwlink/?LinkId=64062
> MSDE :
> http://www.microsoft.com/sql/prodinfo/previousversions/msde/redistregister.mspx
> yes... you have to enable remote connections as, by default, both products
> disable network protocols at installation time.. you can modify this
> behaviour both at install time and later, at "runtime"..
> SQL Server is full client/server database engine.. it will handle that
> appropriately :D
> but you have to deal with concurrency scenarios in your apps..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>
>
|||Small Doubt Mr Andrea,
I'm using visual studio.net 2003 is it compatible when i install
ServerExpress
-GRK
"Andrea Montanari" wrote:

> hi,
> GRK wrote:
> SQLExpress is newer than MSDE, and MSDE will not be supported on Vista
> platform..
> I'd go for SQLExpress as it will be better supported anyway..
> for SQLExpress you can freely download the "official" management tool, SQL
> Server Management Studio Express at
> http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
> for MSDE there's no official graphical tool available, but you can have a
> look at a free prj of mine, DbaMgr2k, available at
> http://www.asql.biz/en/DbaMgr.aspx
> both engines are free to use.. you have to freely register for distribution
> if you plan to;
> SQLExpress: http://go.microsoft.com/fwlink/?LinkId=64062
> MSDE :
> http://www.microsoft.com/sql/prodinfo/previousversions/msde/redistregister.mspx
> yes... you have to enable remote connections as, by default, both products
> disable network protocols at installation time.. you can modify this
> behaviour both at install time and later, at "runtime"..
> SQL Server is full client/server database engine.. it will handle that
> appropriately :D
> but you have to deal with concurrency scenarios in your apps..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>
>

MSDE or SQL Express 2005?

You'd probably be better off posting in this newsgroup:
http://communities.microsoft.com/new...previewFrame.a
sp?
ICP=sqlserver2005&sGroupURL=microsoft.private.sqls erver200
5.express&sLCID=us
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks, Paul.
"Paul Ibison" wrote:

> You'd probably be better off posting in this newsgroup:
> http://communities.microsoft.com/new...previewFrame.a
> sp?
> ICP=sqlserver2005&sGroupURL=microsoft.private.sqls erver200
> 5.express&sLCID=us
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql

MSDE or SQL 2005 Express?

Which one would you choose for a small application that needs to go in
production NOW? SQL 2005 Express is supposed to be the successor of MSDE
2000, but is it stable enough for production?
NOW - I would look at MSDE.
If you can wait until Nov 7th, you might consider SqlExpress.
SqlExpress seems quite stable, but I find it hard to justify going into
production with it when we are so close to the release date. Also, depending
on whether or not you are also running any v2.0 .NET apps, you could have
issues with similar but still different versions of the CLR. Lastly, do you
qualify for the Go Live License and does that license apply to SqlExpress or
only Visual Studo?
-Andrew
"JDG" <JDG@.discussions.microsoft.com> wrote in message
news:745B1A5C-F8B1-47C7-BD19-E1F254231D7B@.microsoft.com...
> Which one would you choose for a small application that needs to go in
> production NOW? SQL 2005 Express is supposed to be the successor of MSDE
> 2000, but is it stable enough for production?
>
|||Thanks Andrew. Three weeks sounds manageable... Is this a published date?
I've registered for the Go Live license, including SQLExpress.
"Andrew Robinson" wrote:

> NOW - I would look at MSDE.
> If you can wait until Nov 7th, you might consider SqlExpress.
> SqlExpress seems quite stable, but I find it hard to justify going into
> production with it when we are so close to the release date. Also, depending
> on whether or not you are also running any v2.0 .NET apps, you could have
> issues with similar but still different versions of the CLR. Lastly, do you
> qualify for the Go Live License and does that license apply to SqlExpress or
> only Visual Studo?
> -Andrew
>
> "JDG" <JDG@.discussions.microsoft.com> wrote in message
> news:745B1A5C-F8B1-47C7-BD19-E1F254231D7B@.microsoft.com...
>
>

MSDE limitations lifted in Express?

8 connection limit, 2GB file size limit in MSDE? What are the limits in the
capabilities of SQL Express?
-billb
billb -
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
No 8 connection limit, 4GB database size, and some other things are
different.
RLF
"William Bernat" <Wil86ianospammb86ernat@.cap86tanospamaris.com> wrote in
message news:urA1lqAaHHA.3268@.TK2MSFTNGP04.phx.gbl...
>8 connection limit, 2GB file size limit in MSDE? What are the limits in the
>capabilities of SQL Express?
> -billb
>
|||Although it was not really an '8 connection' limit. See Euan Garden's
Mythbuster BLOG. http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx
RLF
|||Thanks Russell!!!
-billb
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ekDT08AaHHA.5056@.TK2MSFTNGP03.phx.gbl...
> Although it was not really an '8 connection' limit. See Euan Garden's
> Mythbuster BLOG.
> http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx
>
> RLF
>
|||Indeed, there is not 8 concurrent queries limit, the 2GB database size
limit has been raised to 4GB, but the maximum RAM limit of 2GB has
been lowered to 1GB and SQL Server Agent is no longer included.
Razvan

MSDE limitations lifted in Express?

8 connection limit, 2GB file size limit in MSDE? What are the limits in the
capabilities of SQL Express?
-billbbillb -
http://www.microsoft.com/technet/pr...sqlexpress.mspx
No 8 connection limit, 4GB database size, and some other things are
different.
RLF
"William Bernat" < Wil86ianospammb86ernat@.cap86tanospamaris
.com> wrote in
message news:urA1lqAaHHA.3268@.TK2MSFTNGP04.phx.gbl...
>8 connection limit, 2GB file size limit in MSDE? What are the limits in the
>capabilities of SQL Express?
> -billb
>|||Although it was not really an '8 connection' limit. See Euan Garden's
Mythbuster BLOG. http://blogs.msdn.com/euanga/archiv.../09/545576.aspx
RLF|||Thanks Russell!!!
-billb
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ekDT08AaHHA.5056@.TK2MSFTNGP03.phx.gbl...
> Although it was not really an '8 connection' limit. See Euan Garden's
> Mythbuster BLOG.
> http://blogs.msdn.com/euanga/archiv.../09/545576.aspx
>
> RLF
>|||Indeed, there is not 8 concurrent queries limit, the 2GB database size
limit has been raised to 4GB, but the maximum RAM limit of 2GB has
been lowered to 1GB and SQL Server Agent is no longer included.
Razvan

MSDE limitations lifted in Express?

8 connection limit, 2GB file size limit in MSDE? What are the limits in the
capabilities of SQL Express?
-billbbillb -
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
No 8 connection limit, 4GB database size, and some other things are
different.
RLF
"William Bernat" <Wil86ianospammb86ernat@.cap86tanospamaris.com> wrote in
message news:urA1lqAaHHA.3268@.TK2MSFTNGP04.phx.gbl...
>8 connection limit, 2GB file size limit in MSDE? What are the limits in the
>capabilities of SQL Express?
> -billb
>|||Although it was not really an '8 connection' limit. See Euan Garden's
Mythbuster BLOG. http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx
RLF|||Thanks Russell!!!
-billb
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ekDT08AaHHA.5056@.TK2MSFTNGP03.phx.gbl...
> Although it was not really an '8 connection' limit. See Euan Garden's
> Mythbuster BLOG.
> http://blogs.msdn.com/euanga/archive/2006/03/09/545576.aspx
>
> RLF
>|||Indeed, there is not 8 concurrent queries limit, the 2GB database size
limit has been raised to 4GB, but the maximum RAM limit of 2GB has
been lowered to 1GB and SQL Server Agent is no longer included.
Razvan

MSDE end of life?

With SQL Server Express coming out -- is there an end of life for MSDE
announced or assumed? Should new development not be on MSDE?
Mainstream support usually extends two years after the next version is
released, and extended support goes 5 years. There are exceptions, of
course, but right now no end of life has been announced for any SQL Server
2000 product (partly because 2005's ship date is not yet known).
http://www.aspfaq.com/
(Reverse address to reply.)
"TryingToFigureItOut" <TryingToFigureItOut@.discussions.microsoft.com> wrote
in message news:650E0F46-8F14-4A07-AE07-D31BF68FFE0C@.microsoft.com...
> With SQL Server Express coming out -- is there an end of life for MSDE
> announced or assumed? Should new development not be on MSDE?