Board index » delphi » MSDE to SQL Server 2005 in Vista - How to do it

2008-04-17 06:50:05 PM
delphi129
Hi,
I need to move my D6/MSDE application to Vista.
Vista will not run MSDE and suggests moving to SQL Server 2005 Express.
I installed SQL Server 2005 Express and now I have difficulties:
* Connecting to the new SQL Server
* Figuring out tjer server name
* Reading my database made by MSDE
It seems the SQL server is not as compatible as I had hoped.
Anya ideas on what I may be doing wrong?
Thanks,
AJ
 

Hi AJ,
database, however in order to use the new features offered in SQL 2005 you
need to use the newer driver, SQLCLNI. If you check
www.connectionstrings.com you can view many different strings used to create
your connection.
servername\instancename. 2005 installs in a default manner that no network
connections are allowed. You must use the Surface Area Configuration tool to
open up the network access, did you do that?
simply did a backup of the 2K db and restored in the 2K5 environment and
voila.
The process of moving from one to the other should be relatively seamless.
Hth,
Brian
 

Brian, thanks for your response,
For now I need no extra features.
I'm happy if I can access my DB as before.
Assuming "servername" = computer name,
I used servername\SQLEXPRESS and got a bit further than before.
At least the error messages is now different.
It now complains about not being able to open the database.
I did not do a backup and restore.
I just copied *.ldf and *.mdf to the new Vista machine, with SQL Server 2005
Express.
Is that a mistake?
The MSDE had a "Enterprise Manager" and some more tools.
With EM I could browse the database and build tables, indexes etc.
I find no such tools with SQL Server 2005 Express.
Have I missed something here?
Thanks for your assistance and patience,
Anders J
"Brian" <bbop1322=AT=hotmail=DOT=com>skrev i meddelandet

Anders Johansson formulated on ÐÝìðôç :
using the existing .mdb, .ldf files or do a backup & restore.
The problem is that the main db on any MS SQL server keeps track of what
databases are istalled on this server and a number of details for every
database (number of files, location etc). With out any record of the
database in the main database it does not exists for this server.
from a full MS SQL 2000 server and use them but you had to own an MS SQL
server license.
think) that you need to download as well.
Regards
Yannis.
--
When you do the common things in life in an uncommon way, you will
command the attention of the world.
- George Washington Carver (1864-1943)
 

Anders Johansson writes:
database
engine to allow "remote connections" using tcp/ip and named pipes.
If you don't, it only allows local connections through its shared memory
driver (which you surely don't use in your legacy applications)
2) The default server name is "(local)\SQLExpress" , if you access the
database from
another PC it is "computername\SQLExpress"
--
Arthur Hoornweg
(In order to reply per e-mail, please just remove the ".net"
from my e-mail address. Leave the rest of the address intact
including the "antispam" part. I had to take this measure to
counteract unsollicited mail.)
 

Yannis, I am getting closer now.
When I use SQL Server Management Studio,
I get error 916, since the model database cannot be accessed.
When I try to open it by clicking on it in "Databases/System Databases"
I get an error message: "The database model is not accessible"
I do have that access in my MSDE environment on another PC.
Should it not be accessible in the same way in SQL 2005?
Thanks for your help,
Anders J
"yannis" <XXXX@XXXXX.COM>skrev i meddelandet

Anders,
To attach your .mdf and .ldf files to the server, you can use
AlfaAlfa's SQL Server Comparison Tool (SCT)
www.sql-server-tool.com
It has built-in "one-click" functionality of attaching database to SQL
Server directly from within the tool.
In order to add your SQL Server Express to the list of servers
available for SCT please click menu item Tools ->Add Server (if you
don't have at least one server added to SCT, the program will display
"Add Server" dialog automatically). As a server name please enter
\SQLEXPRESS (with the leading backsplash).
Then you need to specify what kind of authentication - Windows or SQL
Server - you use (you was prompted for authentication selection at the
time SQL Express was installed). Please select appropriate option,
enter user name and password if necessary, and click button "OK".
After short time SCT should display message confirming successful
adding of the server to SCT.
If this happens, you are almost done.
If SCT displays error message, please exit the application, restart it
and check if the server was added to the list; if it was, please try
to connect to it - the chances are that the connection will be
established properly.
Once you are connected to the server, please click menu item Tools ->
Attach Database, specify database name and the location of files, and
click button "Attach". SCT should attach the database and display
confirmation message.
Dariusz Dziewialtowski
 

Anders Johansson expressed precisely :
If so please explain what you have done? have you attached the database
or you have backup and restore it?
It is known that a backup and restore is the recomended method of
trasfearing a DB between different version of MS SQL server. Please try
it.
example is the description "(single user)" expanded in the database
name on management studio?
Take a look on the database properties to see if any restrictions are
enabled.
Regards
Yannis.
--
It's kind of fun to do the impossible.
- Walt Disney (1901-1966)
 

No, model is preinstalled.
I assumed that it is included in SQL Server.
Perhaps it is not, since you are not familiar with it.
I get the error message inside the SQL Server Management studio
when I attempt to to attach my my own database (*.mdf *.ld)
I do the following:
* Right click "databases"
* Select "Attach"
* Click "Add" to add my own database.
This is when I get the error message below.
Added my own transaction at ====>>>
I cannot answer your questions below since I cannot attach.
Thanks for your assistance/AJ
========= E R R OR M E S S A G E B E L O W ===============
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SmoEnum)
For help, click:
go.microsoft.com/fwlink
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
=====>>>"cannto access database model under current securitu context"
Serverobjektet RS4000d\RS4000 kan inte f?tillgång till databasen model
under den aktuella säkerhetskontexten. (Microsoft SQL Server, Error: 916)
For help, click:
go.microsoft.com/fwlink
------------------------------
BUTTONS:
OK
------------------------------
"yannis" <XXXX@XXXXX.COM>skrev i meddelandet

Anders Johansson expressed precisely :
about that.
Two questions
1) when you installed the SQLExpress did you choose the mixed mode
authentication?
2) How do you connect to the database from management studio which user
do you use?
In short this seems to be a rights problem you are connecting with a
user which is very restricted on has not been declared in the database
at all. Most of the time I install the SQL server in mixed mode
authentication and use the SA user to work with.
Regards
Yannis.
--
A little inaccuracy sometimes saves a ton of explanation.
- H. H. Munro (Saki) (1870-1916)
 

"yannis" <XXXX@XXXXX.COM>skrev i meddelandet
 

Anders Johansson brought next idea :
properties. From the dialog window select security on the page list in
the top right in there select Mixed mode authentication and press OK.
Go to Security\logins tree on the right and locate the user SA right
click properties on the general page specify a passowrd for the user,
on the status page make sure that login is enabled and the option grand
is selected in the "permission to connect to database engine" option.
Shutdow the SQL service and restart it. Try to connect to the enginee
from the management studio using the SA user. If everything was done
correctly and your windows user had the appropriate rights then you
shoold be able to connect.
Regards
Yannis.
--
Don't stay in bed, unless you can make money in bed.
- George Burns (1896-1996)
 

This got me a bit further.
Now I can press the ADD button and select my *.mdf file.
Previously the error popped up when ADD was pressed.
Then when I press OK to save this selection I get
another error message:
"Cannot open file....*.mdf access denied."
as shown below.
Anders J
================ERROR MESSAGE ======================
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Attach database failed for Server 'RS4000D\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)
For help, click:
go.microsoft.com/fwlink
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Det går inte att öppna filen C:\Robur\RS4000\Database\RS4000.mdf.
Operativsystemfel 5: "5(Åtkomst nekad.)". (Microsoft SQL Server, Error:
5120)
For help, click:
go.microsoft.com/fwlink
================ END OF ERROR MESSAGE ======================

that or backup in MSDE and restore in SQL Server.
Oliver
 

Good thinking, but no.
This Vista machine is new and does not have MSDE.
I am attempting to install the application under SQL 2005 Express
instead of MSDE which will not install in Vista.
*.mdf has been copied from another installation.
I did a similar SQL 2005 installation on a WIN2K machine which works fine.
It must be something that differs in the Vista environment.
Is it importatn who "owns" the ndf-file.
Regular user or administrator?
Anders
"Oliver Townshend" <oliveratcodelegaldotcomdotau>skrev i meddelandet