Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Go Back  Xtreme Visual Basic Talk > > > Distributing App. w/ SQL Server 2005 EE


Reply
 
Thread Tools Display Modes
  #1  
Old 12-06-2007, 05:23 PM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default Distributing App. w/ SQL Server 2005 EE


Hello,
I hope this is the proper section to post my questions. I'm a beginner. I've several questions regarding installing and setting up SQL Server 2005 Express Edition on the company's computers so that the application works properly.
-----------
In the company's building, there are 8 PCs, all having XP OS SP2, linked together with a "workgroup" named "OFFICE". The names of the computers are "PC151", "PC152", ..., "PC158". In every computer there is a "SharedDocs" folder, and all users have full access to it.
-----------
I recently moved from MS Access to SQL Server 2005 EE. I developed the same application using MS Access one month ago, and deployed it successfully. It's working fine.
I put the .mdb file in the "SharedDocs" folder of "PC151", and installed the application (along with .Net Framework 2.0 and Crystal Reports) on every computer. The ConnectionString indicates where the .mdb file is.
Q1: Is this way of using SharedDocs folder as a place for .mdb file correct, to set up a Windows application that works over a network of this type? What is the best way?
Q2: How fast is this network? I expect the .mdb file size to reach 50 MB soon. (I don't now the name of the cable that is connected to the Network Adapter but I think it's Cat 6).
-----------
By Now, I modified the VB.Net project so that it uses SQL Server 2005 EE instead of MS Access. I've not deployed or installed it yet because I don't now how to get best benefits from the SQL Server technology.

I seem to do the following:
1- Install the application, .Net Framework, Crystal Reports, SQL Server 2005 EE on all computers.
2- Put the .mdf file and .log file in the "SharedDocs" folder of "PC151".
3- Use this ConnectionString:
Code:
Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=" & DBPath
where DBPath is the path to the .mdf.

Q3: Will this work? Is it faster than Access?
Q4: Is this preferable? What is the best way, and hence, the best ConnectionString to set up this Windows Application over this network?
Q5: I read a lot at MSDN website, and I understood that this way is called "User Instance", and they describe it as the "Rare", "Exceptional", "Not Normal" way of using SQL Server. What is the normal way, then? I can't find any article about it. Is the normal way means installing SQL Server 2005 EE only on ONE computer, and do some configrations? I know that the normal way is to use "ServerName" in the ConnectionString, but where is the server in my case? and what is the server name?

I'm so sorry.
I really need your help.

Adel.
Reply With Quote
  #2  
Old 12-07-2007, 03:03 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

Hi
Quote:
Q1: Is this way of using SharedDocs folder as a place for .mdb file correct, to set up a Windows application that works over a network of this type? What is the best way?
it's ok to use your shared folder but make sure that the users won't delete your database file by mistake.

Quote:
Q2: How fast is this network? I expect the .mdb file size to reach 50 MB soon. (I don't now the name of the cable that is connected to the Network Adapter but I think it's Cat 6).
your network should be 100 Mbps or 1000Mbps either way it's more than enough for your data, just one point to note here is that you need to compact and repair your database through code or through MSAccess to make sure that all your indexes are in check and to keep your file as small as possible, once a day is okay.

Quote:
By Now, I modified the VB.Net project so that it uses SQL Server 2005 EE instead of MS Access. I've not deployed or installed it yet because I don't now how to get best benefits from the SQL Server technology.
you don't need the server right away you can use an mdf file the same way you're using mdb files and after you need all the functionality of SQL server you can attach your file to the server and just change the connection string and voila you're set.
If you opt for going the server way make sure that you choose the best computer on your network to host your server, by that i mean the one with the best hardware configuration (Processor, HDD, RAM) the bigger the better.

Quote:
I seem to do the following:
1- Install the application, .Net Framework, Crystal Reports, SQL Server 2005 EE on all computers.
no don't install sql on all computers, just the one you chose earlier.

Quote:
2- Put the .mdf file and .log file in the "SharedDocs" folder of "PC151".
Quote:
Q4: Is this preferable? What is the best way, and hence, the best ConnectionString to set up this Windows Application over this network?
your mdf file should be attached to your server.

regarding your connection string you can check this site for help
http://www.connectionstrings.com/?carrier=sqlserver2005

your connection string should look like this

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

meServerAddress is pc151/SQLExpress
myDataBase is the name of your database attached to your server.

if you want to build the connectionString and check on each machine do the following
create a text file
change the extension to udl
open that and choose your data provider, server name and database name
test the connection if it's successful change the extension back to txt and open the file you'll find your connection string there.
this is a good way to test the connection from other computers.

Quote:
Q3: Will this work? Is it faster than Access?
I don't know it's different for each system, the number of users accessing the database in the same time your system hosting the server and other factors I guess you should try it first and see for your self.

good luck with your project
Reply With Quote
  #3  
Old 12-07-2007, 06:00 PM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

Many thanks really I don't know how to thank you. I was looking for this help. Thank you, I will try to do what you suggested and will reply soon and let you know how things go.

Quote:
it's ok to use your shared folder but make sure that the users won't delete your database file by mistake.
OK and thanks for this comments. Fortunatly, the application already does a backup every day, and compacts and repairs the .mdf file every day. All this by code. Really it's a big security problem that all users have compelete access to the .mdb file.
------------
Quote:
your network should be 100 Mbps or 1000Mbps either way it's more than enough for your data
I will check the speed of the network to ensure this.
-----------
Quote:
you don't need the server right away you can use an mdf file the same way you're using mdb files
Excellent, but do I need to install SQL Server on all computers in this case (I mean with User Instance mode)? I read that "Users Instance" mode requires SQL Server to be installed on every computer.

Quote:
you can attach your file to the server
OK, Assume I select the best computer and install SQL Server only on this one to make it the serevr, "how" to attach the file to the server, so that the connectionstring you gave me works from other computers?

Quote:
no don't install sql on all computers, just the one you chose earlier.
Thank you, I know by now that I don't need to install SQL Server on all computers. I really needed this information.

Quote:
your connection string should look like this

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

meServerAddress is pc151/SQLExpress
myDataBase is the name of your database attached to your server.
Thank you. I finally got the servername parameter correctly. You make it very clear by this example. I really appreciate your help. I'll try this and let you know (I will make a LAN between two computers at home very soon. This will help me know how things go and learn SQL Server better).
Quote:
if you want to build the connectionString and check on each machine do the following
create a text file
change the extension to udl
open that and choose your data provider, server name and database name
test the connection if it's successful change the extension back to txt and open the file you'll find your connection string there.
this is a good way to test the connection from other computers.
Really thank you.

What else can I say? I'm speechless.
I'll never forget this, and will try everything you said and see how it goes.

YOURS
Adel
Reply With Quote
  #4  
Old 12-07-2007, 07:55 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

Quote:
OK, Assume I select the best computer and install SQL Server only on this one to make it the serevr, "how" to attach the file to the server, so that the connectionstring you gave me works from other computers?
copy the database file and the log file to the data directory in sqlserver your path should look like this
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
open the management window for your server, right click the database folder and choose attach in the window choose your databse file and select ok.
your file should show on the database list from there you can manage your database and do whatever you want with it.
if you are using SQL2005EE then you'll need SQL Server Management studio Express CTP.
if you don't have it you can download it from Microsoft web site. it will allow you to manage your server from outside visual studio much like the full SQL2005 server.
If you are going ahead with SQL2005EE take some time to learn it so you would be ready for any kind of problems "God forbid", and to be comfortable with the new platform.
good luck
Reply With Quote
  #5  
Old 12-08-2007, 01:21 PM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

Dear sgm, Thank you very much for your help.

Quote:
copy the database file and the log file to the data directory in sqlserver your path should look like this
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
open the management window for your server, right click the database folder and choose attach in the window choose your databse file and select ok.
Very clear. I'll try this soon. I have tried the following so far:
1- "PC151" was the server.
2- I tried to connect to the server from "PC151" which is the server itself.
3- I wanted to establish a connection to the "Northwind" database. I didn't now if it was attached to the server or not.
4- I used the following code:
Quote:
Public Conn As New SqlConnection
Public Comm As New SqlCommand
Public DA As New SqlDataAdapter
Public DS As New DataSet
Public CB As SqlCommandBuilder

Conn.ConnectionString = "server=PC151\SQLEXPRESS; Database=Northwind; Integrated Security=SSPI;"
Comm.Connection = Conn
DA.SelectCommand = Comm
Comm.CommandText = "select * from Orders"
DA.Fill(DS, "Orders")
5- I got the following error:
Cannot open database "Northwind" requested by the login. The login failed.
Login failed for user 'PC151\Administrator'

What is this error about? I carefully followed all the commands stated here and compeleted them successfully. But the error remains.

I will come back here.
Thank you.
YOURS

Last edited by Adel99; 12-08-2007 at 01:27 PM.
Reply With Quote
  #6  
Old 12-08-2007, 05:26 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

the text file method to write the correct connectionstring, you'll know when you get it right by the connection test success.
you can try the server explorer in VS2005 by creating a new data connection Enter the name of your server and from the drop down list browse for the attached dataBases in the server if Northwind is installed it will show up there.
keep us posted with your progress

Last edited by sgm; 12-08-2007 at 05:40 PM.
Reply With Quote
  #7  
Old 12-09-2007, 06:57 AM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

Dear sgm, Thanks very very much. Connection succeeded!
This time I did the follwoing:
1- Downloaded and installed "Microsoft SQL Server Management Studio Express"
2- Copied .mdf and the log files to the directory you gave me above:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
3- From SQL Server Management Studio, attached the database to the server.
4- Tried the "udl" method, connection succeeded!
5- Copied the connectionstring to VB.Net code. It worked.

Here is the ConnectionString:
Code:
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=_D;Data Source=PC151\sqlexpress
Still to try from a remote computer (e.g. PC152) because PC151 is the server itself. It seems that the connectionstring will not vary, will it?

You helped me a lot and stayed beside me all the time, I don't know how to thank you.
I didn't know that it had a steep learning curve, I read a lot at MSDN and MS websites. But I found nothing saying: "Do the following to get it done". Very likely the subject is famous, but my search skills are poor.

Thank you so much. I learned a lot here.
YOURS
Reply With Quote
  #8  
Old 12-09-2007, 01:33 PM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

Today I bought the cords to make a LAN between two computers (at home). I didn't buy the correct "cross" cable. So, tomorrow when the LAN is set up, I will try the same connectionstring.
INFO: Now, PC152 has a fresh installation of win XP, No office, no SQL Server, no .Net framework. Tomorrow, I will just install my app. and .net framework to test the connection from there.

Thank you.
YOURS
Reply With Quote
  #9  
Old 12-10-2007, 07:25 AM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

I agree with you that reading MSDN and trying to extract good practical solutions is very hard, probably for us who does not speak english as their first language, I often find that if I want to learn a new technology I have to do a project with it because there is nothing like a hands on experience to better understand all the tricks of the trade.
any way once you are familiar with database application development you'll develop a better understanding for MSDN and how to extract useful information.
keep at it and don't despair, you'll always find help here in this forum as long as you do your homework and ask the right question.
by the way are you using windows with Arabic interface because the string you posted looked like it was from a right to left notepad and the Name also is a big clue :-)??
Reply With Quote
  #10  
Old 12-10-2007, 08:19 AM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

Quote:
probably for us who does not speak english as their first language
Your English is almost perfect. I thought that you were a native speaker.

Quote:
are you using windows with Arabic interface because the string you posted looked like it was from a right to left notepad and the Name also is a big clue
I'm Arabic, but I'm not using an Arabic version of Windows. When I opened the .udl file with the Notepad, I found it like this.

Thank you.
I will come back soon.
Reply With Quote
  #11  
Old 12-10-2007, 11:55 AM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

Dear sgm, Please help me with this I could not figure it out.
1- I bought a cross cable and get the LAN working perfectly (between 2 PCs, 'PC151' is a sql server, 'PC152' is not).
2- The application is working perfectly on PC151 with this coonectionstring:
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=_D;Data Source=PC151\sqlexpress
3- I deployed the application and .Net framework and installed them on PC152.
4- I ran the application from PC152.
- At first it gave me the famouse error that SQL Server doesn't allow remote conncetions, but after I had compleleted all the instructions in the MSDN article, that error was gone.
- However, another error showed up:
Login Failed for user 'PC151\Guest'
I tried adding a user named 'PC151\Guest' from the MSSQL Management Studio; and tried playing with the schema; THE ERROR REMAINS.

PS: I didn't change the ConnectonString for PC152 , I just deployed the application.

Please help me.
YOURS
Reply With Quote
  #12  
Old 12-11-2007, 09:58 AM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

when you are usind OLE DB you should enable server browser services, or you can use SQL native client.
you can also use sql authentication by adding a new login and granting access to your databse.
i don't know what causes your problem exactly so I'll have to check it first on my network, in the mean time check this site it's great for SQL connection problems
http://blogs.msdn.com/sql_protocols/...23/558651.aspx
Reply With Quote
  #13  
Old 12-11-2007, 03:19 PM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

SQL Server browser was already enabled.
Code:
you can also use sql authentication by adding a new login and granting access to your databse
"How", and what is the user's name... the connectionstring...?
I'll try to solve this and search the web. I'll post my progress here.
Thank you very much. I hope I'm not causing problems to you.
Using your help, I've solved the hard part. Thank you.
YOURS
Reply With Quote
  #14  
Old 12-11-2007, 06:46 PM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

go for your sql instance in the management studio and right click it, choose options, Security and check SQL server and windows authentication mode.
now go for the security folder in the management studio it should be under the database folder go for the login folder create a new login and set the password.
right-click your database and on the files tab assign the user as an owner of your database.
in the udl file choose sql server user name and password instead of windows authentication and supply the login data you've just created.
try the connection.
when creating the new login try to go through the different categories and assign a database for the login and what it can do with it.
if you can open your last log file it's in LOG folder the same level as data and check the error numbers (state and severity) the state should tell us what is going wrong with your connection.
tell me what happens with you
Reply With Quote
  #15  
Old 12-12-2007, 07:44 AM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

Thank you very much.
The problem was completely solved with your help.
Now I'm using the server mode.
Thank you. I'm speechless...
You have been kind to me all the way with those problems, I appreciate this.
Thank you.
YOURS

Last edited by Adel99; 12-12-2007 at 07:54 AM.
Reply With Quote
  #16  
Old 12-12-2007, 08:16 AM
sgm sgm is offline
Junior Contributor
 
Join Date: Aug 2003
Location: AlBayda, Libya
Posts: 305
Default

you are welcome
I'm glad that everything is ok
good luck
Reply With Quote
  #17  
Old 12-12-2007, 01:40 PM
Adel99 Adel99 is offline
Centurion
 
Join Date: Aug 2005
Posts: 160
Default

Thank you and Goodbye.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
 
Distributing App. w/ SQL Server 2005 EE
Distributing App. w/ SQL Server 2005 EE
 
-->