Multi-User Acces Using SQL Server Express
Mar 27, 2008
I am new to SQL Server but have been using Access to provide Multi-User acces to a central data store. We are evaluating the replacement of Access with SQL Server. With Access we don't need any special installation; I believe that the Jet Database Engine installed locally on each user's PC allows them to work with this in a disconnected manner; we use ADO.net 2.0 with VB.net 2005.
If each user has a locally installed SQL Server Express can they all attach to a database that resides on a shared network location?
In another post on the Forum I see the following regarding multi-user access:
Our application You will need to pick a central computer that everyone will be using the access the data and attach the file to the parent instance of SQL Express on that comuter, giving it a name that you can use to access it. Then you will need to modify your connection string to connect to the named database on the central server...
Can multiple locally installed SQL Server Express instances attach to a single data store that resides on a shared network location?
If not, what permissions do I need to create a parent instance of SQL Server Express?
Thank you for helping me understand how to implement our solution.
JudiR
View 13 Replies
ADVERTISEMENT
Jul 10, 2007
Okay, I've read massive topics on ClickOnce, and embedded database applications, etc, etc.
I need a handful of good suggestions how I can create a deployment package, to get my sql express database onto a SQL Express server.
The database will need to be multi-user, because I am also developing a WinForms application to connect to the SQL Express database.
I'm thinking I need to use some automated scripting features, to generate scripts for the database once I am done (unless you have a better suggestion).
After that, I need to know what to do with those scripts, and how can I create a setup / deployment packages to run those scripts against a SQL Express server.
The other alterntative obviously is to copy my sql express database, and run an attach command. This will work the first time, only because this will have been the first deployment of this database.
Please keep in mind when answering this question, that I will not be embedding the database, and as far as I understand ClickOnce is a feature for use with embedded databases or user instance databases (not sure).
Daniel Crane
View 6 Replies
View Related
Oct 25, 2006
I have developed two programs that operates against the same database. One of the program is just a display program that displayes the data put into the other administation program where you put in the data to be displayed (an administration program).
Every time I access the db from the administration program, the display program stops and throws connection pool errors and other database errors. For me, it looks like it doesīt do multi-access to a database.
I have tried putting user instance to off in both programs, but this didnīt help.
Connection string also points to same file database.
View 6 Replies
View Related
Jun 21, 2004
I have written a VB/SQL Server based application. The application uses stored procs and the SQL Server security model to handle logins and permissions. My partner and I want to sell the applicaiton to specific businesses and offer a rental program for smaller companies that would like to use the application to connect to an internet based database. The application uses TCP/IP with a specific port number to connect to the database so it can be used in or out of LAN.
If we rent the application, maintenance is simple. I just create a user in the database with the login/password the user would like and viola they have access to the data.
When a company wants to purchase the program to use in-house, we would like to offer two alternatives: #1 a single user or #2 a multiple user SQL Server database.
My question is if a company purchases the single-user version, how can we keep them from adding users to their database. In other words, since we have no control over the database at the client's location, how can we ensure that it remains a single-user database?
Thanks for any help!
Randall Medcalf
View 1 Replies
View Related
Feb 19, 2008
Hi!!
I need some help to importing Acces file to SQL 2005.
Thank's
View 1 Replies
View Related
Jan 20, 2007
I was wondering if you guys might give me some advice on how best to handle a particular scenario i'm struggling with.
I have a client that basically wants web-based-update access to their sql server database. Specifically, for a group of users to be able to access a page where they select a record for editing. the caveat is that no two users should be able to pull up the same record at the same time. Originally I would have thought there was some easier record-locking-mechanism I could exploit within sql server or ado.net itself, but I haven't been able to come up with anything.. so this is my current approach:
The page they use starts-out with basically a blank form. there are custom-built paging controls at the bottom of the screen. they click page-forward to begin and a stored procedure is ran to select a record and update a field on that record to indicate "in-process". when they finish editing the record - or page on to the next record without updating - another stored procedure is ran - updating/resetting the status field on the record appropriately.
The entire page is encapsulated within an ajax.net updatepanel.
The entire page has caching disabled. This works well in conjunction with the first page being blank. if they get out of the app and try to get back in by clicking the back button - all they can do is get to the first (blank) page.
A piece of javascript window.onunload clicks a button on the page that releases the record they currently have selected in the event of a re-direct, clicking back, etc.. it appears to work with everything except a window close. in that case, i have a stored procedure running periodically on the server that checks how long a record has been selected - and if it exceeds the time indicated - resets the record as to allow it to be re-selected later.
In the event of session timeout, they are redirected to another page that tells them their session has timed-out (and since the window.onunload fires - it takes care of releasing the record if they have one on the screen).
The concept seemed to be working well until I started multi-user testing. Now it seems as if two users time it perfectly - they are actually able to both select the same record. it happens pretty rarely, but it does seem to happen. I'm guessing this has to do with how my stored procedure is structured - possibly allowing a tiny-enough window between the record being selected for editing - and the update running to actually status the record as in-process (2 separate sql statements within the one stored procedure).
I believe I also have a found a second quirk in my approach where something is causing the window.onunload event to fire twice in some strange situations.. but that's more annoying/confusing from a logging standpoint than anything..
I've read where people say to ensure you dont update a record that's already been updated - that you should compare the fields before you actually perform the update and ensure they haven't changed since you selected it.. but to me that doesn't solve anything.. if two people select the same record and both spend time working on it - the person that tries to update last has just wasted their time.
I've also toyed with the idea of maintaining a separate table in the database to hold the keys to the currently selected records and use that to keep multiple people from selecting the same record - but honestly i dont know if that approach is any better than what i'm doing now.
anyway, I was just curious if you guys had any advice in regards to how you'd handle a request like this.. or if you see any obvious problems/fixes with my current approach..
I would greatly appreciate any info you could provide-
thanks-
View 3 Replies
View Related
Mar 4, 2015
I have tried everything I found on Google, but nothing seems to be working. Already Ran alter command set multi_user, but getting an error.
"Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1..ALTER DATABASE statement failed."
View 8 Replies
View Related
Apr 22, 2006
Good Day,
I have an application that is being developed on Visual Studio 2005 (VB) and SQL Express. The application is set up to use the SQL Express on the development computer, and if an Internet or CD install is built, it installs SQL Express as expected. The question is, when it is time to deploy and put the SQL Express database on the 2003 server, how should the install on the server be done and how should we set the database path to the server?
Thanks,
Dave
View 1 Replies
View Related
Feb 27, 2008
I am using SharePoint Services 3.0 (SP1) with default configuration options, which installs the Microsoft##SSEE instance of SQL to my local C: drive.
While attempting to relocate the files to another drive, I set one of the databases (as recommended) to Single User by using the SQL Server Management Express tool.
I cannot now reset that database to Multi User, even by executing the query
exec sp_dboption 'database_name', 'single user', ''FALSE'
again by using the Management Express Tool.
Can someone please help, in plain english???? Thanks
View 5 Replies
View Related
Mar 5, 2008
Hi all,
For the first time, I want to set up the configuration of my SQL Server Management Studio Express (SSMSE) to allow me in doing the non-User-Instance/ADO.NET 2.0 programming from my VB 2005 Express. The SSMSE and VB 2005 Express are in my Windows XP Pro PC that is part of our NT 4 LAN System in our office. I read the article "How to configure SQL Server 2005 to allow remotre connections" in http://support.microsoft.com/kb/914277/ about (i) "Enable remote connections for SQL Server 2005 Express", (ii) Enable the SQL Server Browser service", (iii) Create exception in Windows Firewall, and (iv) Create an exception for the SQL Server Browser service in Windows Firewall. I entered the SQL Server Surface Area Configuration and I could not decide what options I should take for doing the non-User-Instance/ADO.NET 2.0 programming from my VB 2005 Express. I have the following questions on the page of "Minimize SQL Server 2005 Surface Area":
(1) I saw "Configure Surface Area for localhost [change computer]". I clicked on [change computer] and I saw the
following: Select Computer
The Surface Area Configuration of this surface area of this computer or a remote computer.
Specify a computer to configure: O Local computer
O Remote computer
Should I choose the "Local computer" or the "Remote computer" option?
(2) Below the "Configure Surface Area for localhost [change computer]",
I clicked on "Surface Area Configuration for Service and Connections", Select a component and then configure its services and connections: |-| SQLEXPRESS
|-| Database Engine
Service
I picked => Remote Connection
On the right-hand side, there are: O Local connections only
O Local and remorte connections
O Using TCP/IP
O Using named pipes only
O Using both TCP/IP and named pipes
Should I choose O Local and remorte connections and O Using named pipes only?
Please help and tell me what options I should choose in (1) and (2).
Thanks in advance,
Scott Chang
View 10 Replies
View Related
Jan 6, 2007
Hi all,
I have read/studied (i) Working with Databases in Visual Web Developer 2005 Express in http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/vwd.aspx, (ii) Xcopy Deployment (SQL Server Express) in http://msdn2.microsoft.com/en-us/library/ms165716.aspx, (iii) User Instances for Non-Administrators in http://msdn2.microsoft.com/en-us/library/ms143684.aspx, and (iv) Embedding SQL Server Server Express in Applications in http://msdn2.microsoft.com/en-us/library/ms165660.aspx. I do not understand the concepts and procedures to do Xcopy and User Instances for non-administrators completely-I do not know how to connect to databases and create database diagrams or schemas using the Database Explorer. I have a stand-alone Windows XP Pro PC. I have created a ChemDatabase with 3 dbo tables in the SQL Server Management Studio of my SQL Server Express and a website of my VWD Express application with an App_Data folder. I am not able to proceed to use Xcopy and user instance to bring the 3 dbo tables of ChemDatabase to my App_Data folder. Please help and give me some detailed procedures/instructions to bring the 3 dbo tables of ChemDatabase (or ChemDatabase itself) from the SQL Server Management Studio Express to the App_Data folder of the website of my VWD Express project?
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Feb 27, 2008
I've got a website with authentication mode "forms". Now I need access to the database to authenticate the user. The SQL Server is located in another domain than IIS.I don't know how to establish a trusted connection with the SQL Server. How can I establish this?
View 2 Replies
View Related
Dec 14, 2001
Hi
I would like to acces a SQL Server that is on a web server.
I want to to this to be able to control the sql server and database to create table, trigger...
For "MySql" i use DBTools to acces my database via the web, is there something i can use like BdTools to acces the Sql Server 2000 database
I'm new to SQL so can somebody can tell me what to do!
Thanks
View 4 Replies
View Related
Dec 5, 2007
Dear all,
to restore one perticular backup set, i've made the database to single user mode, now it is not accepting me to connect to that again...
how can i bring the database to multiuser mode?
thanks in advance
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 9 Replies
View Related
Jul 22, 2005
I try to access an OLAP Server from a remote compute, using ADO MD Library with following string conection :
"Datasource=SERVNAME; Provider=msolap; Initial Catalog=FoodMart 2000;"
I recive the following error code: -2147467259 (0x80004005), "Unspecified error"
Can sombody help me
View 4 Replies
View Related
Apr 30, 2007
Hi, I'm using Sql 2000 and I have a linked server called SRVSRV.
The link works because I can list all tables.
How can I access the data i'm trying:
SELECT * FROM [SRVSRV].Database.Table
And it don't works.
What am i doing bad?
Thanks.
View 4 Replies
View Related
Feb 19, 2008
Hi there.
How can i convert a Ms Access Database file (.mdb) to an SQL Server 2005 Database ????
is there any tools ?? or what ever can help me .
Thank you.
View 2 Replies
View Related
Oct 9, 2007
After an internet search I have some questions regarding SQL Server access from a mobile device via LAN / WLAN.
I will write a application in C that write data to an SQL server. First if I correct that's not possible to acces an SQL server via ODBC on Windows CE? What alternatives are there?
Stef
View 1 Replies
View Related
Mar 28, 2008
Hi All,
I have MS Access 97 database and SQL Server 2005 Express Edition (or SQL Server 2005 Developer Edition).
I want online get data from MS Access and upload to SQL Server.
Help me, which way I select ?
Regards
View 3 Replies
View Related
Dec 10, 2006
What is the fastet connection between access 2003 to sql server 2005?
View 2 Replies
View Related
Oct 30, 2007
Hi guys,
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
Thanks,
CP
View 3 Replies
View Related
Apr 28, 2008
I am developing an asp.net app using visual web developer. I am using a sql server 2005 db for the back end. I have learned that I have to restart my machine if I want to work with the db in SQL Server 2005 Express after working with it in Visual Web Developer.
I am wondering why this is. I am assuming that the db is locked when it is used with VWD. Is this correct? Am I refering to it's status of "locked" correctly? Should I call this something else?
-thanks to anyone that can help
View 3 Replies
View Related
Nov 5, 2007
Can multiple users connect to SQL Server 2005 concurrently?
View 4 Replies
View Related
May 30, 2006
Does anyone have links to various strategies for handling multi-user concurrency issues when updating data in SQL Server (2000 or 2005), either in the database (stored procedures) or via code.
I've seen a couple such as:
Check individual user updated columns to see if the database columns have changed and if not apply the updated columns (either individual updates or constructing a dynamic SQL statement).
Check all the fields for any change and if no change, update all the fields.
Check a version field for change and if no change, update all the fields.
What I haven't seen is complete solutions such as for the first one, ensuring the record is locked and can't be changed while checking for concurrency and ultimately updating the data. For the second one, how to raise an error if there was a problem, or for the third one how to ensure the record is locked between the version check and the update.
Even pieces of solutions are welcome (locking records, testing for change, etc). Just want to compare and contrast various methods as I create an infrastructure for 2005.
Thanks,
LarryC
View 2 Replies
View Related
Feb 7, 2008
Dear All,
i've altered my database to single user mode. and now trying to change to multi user mode with the query
alter database mydb set multi_user
but it is saying that it is in single user mode. query analyser closed.
how can i change that to multi user mode? idont know who opend that
to run sp_who it is not showing the database in dropdown list in enterprise manager.
thanks in advance
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 2 Replies
View Related
Apr 16, 2008
I've taken over development of a Vs2003(vB)/MsSql2000 application which usually runs in on a Windows 2000 server with multiple workstations running the application simultaneously in Remote Desktop Sessions.
The central user interface element of the application is an un-databound grid (actually a farpoint spreadsheet) which displays sql table rows that are accessable from every workstation. The grid refreshes its content whenever a specific subroutine is called. That subroutine performs a query which loads its results into a collection which is then loaded into the grid. The subroutine is called during startup and at other times using the rather elaborate mechanism described below. Each workstation also has the ability to modify any row in the grid via a dialog-based editor. The grid itself is not setup to allow inplace editing.
The grid usually contains about 200 records and 20-50% of these records get modified (some repeatedly) every hour.
My question involves the classic multi-user issues of keeping all of the seperate workstation's grids current as individual workstations make modifications to the data.
What is Microsoft's recommended method for handling multi-user data-refresh requirements in custom applications written in VB.NET2003 with MsSQL2000?
Now that you know what I'm looking for, let me add a little more detail. The existing code handles this multi-user refresh issue in the following way...
A SQL Trigger is attached to the SQL Table's Insert/Modify and Delete events. That Trigger calls the xp_logevent built-in stored procedure to make an entry in the NT Event Log.
The application contains a subroutine to handle EventLog("Application").EntryWritten messages. That subroutine calls the subroutine (mentioned earlier) which refreshes the grid contents through a new SQL query.
In this way, whenever any workstation changes the table data, a trigger fires which makes a log entry and broadcasts an EntryWritten message into the global windows environment. The individual applications (each running in their own RDP session) hear the message and respond by refreshing their grid.
This all seems a little "rube goldberg" to me, but it has worked for many years.
That is, until we moved the SQL2000 server to a seperate machine to improve program responsiveness.
Now when the trigger is fired and the log entry is made, the EntryWritten message is broadcast into the windows environment of the dedicated SQL server where there are no applications listening for it. Meanwhile, the applications continue to run with unrefreshed data on the (now) dedicated RDP server from where the EntryWritten messages cannot be "heard".
So my question really comes down to this...
In an environment where the SQL server and the RDP server are two seperate machines, what is the best method for coordinating multi-user screen refreshes across seperate applications running in distinct RDP Client sessions?
Thanks in advance for any assistance.
Peace,
Colt Taylor
Computer Golf Sofware
View 1 Replies
View Related
Nov 21, 2005
Ok, I need to add aspnet user to my database in SQL Server express. I have the SQL Server Mangament Tool Express CTP that i am using to do it.Anyway when i go to add the permissions for my user to the table I type it in and it returns back that there is no user that exists with that name. I then went to the top level for SQL Server Express and it has the ASPNET user. I don't understand why the engine itself can see it and use it but not my individual database.
View 6 Replies
View Related
Sep 11, 2007
Please help....
I have designed an application that uses sql server 2005 express database. This application was intended to be installed and used on a local desktop machine. However, now this application needs to be also installed on several computers and still needs to use only 1 database. Part of the database saves local user settings, so what I have done thus far is use two connection strings: one for local database (because the application may be used as origionally intended) and one for remote database that everyone should be able to connect to.
When I remotely connect to the database, it works fine. However, it seems to be locked so when I chose the same database for another user I get the error message "This File Is In Use." I need to know how to unlock it and make it usable for several users at the same time.
Thank you for your help! Please let me know if you need more information or if something is unclear in my description.
View 6 Replies
View Related
Sep 1, 2005
I am unable to add a new user like 'xxxASPNET' to the Users under Security from AdventureWorks Database. I edited the SQL query which is "sp_grantlogin 'XXXXASPNET' " . It was completed successfully but was not able to add it to the Users.
View 1 Replies
View Related
Sep 22, 2006
Hello,
I am a new to SQL server express, although i have been using SQL server 2003 for development purpose. I just recently installed express and would like to connect to it but can't. I've downloaded and installed SQL Server 2005, SQL Server Management Studio Express, SQL Server Native Client, MSXML 6.0 Parser and .Net Framework 2.0.
When i setup my ODBC driver and test the connection i get the "test failed" message:
Attempting connection
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]SSL Security error
I also get the following message from the SQL Server Management Studio Express.
Any help on slving this problem will be highly appreciated.
Thank you.
===================================
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2146893019&LinkId=20476
------------------------------
Server Name: S2003SQLEXPRESS
Error Number: -2146893019
Severity: 20
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ConnectToServer(Object connectionInfo, IDbConnection liveConnection, Boolean validateConnection)
View 3 Replies
View Related
Jan 18, 2007
What rights do users require to be able to use SQL Server Express?? I receive errors when the user doesn't have full admin rights. Our users cannot have admin rights all the time on their PCs. Errors below:
"Create failed for Database 'TestDatabase'. (microsoft.SqlServer.Express.Smo) Additional information: An exception occurred while executing a Tranact-SQL statement or batch. (Microsoft.SqlServer.Exress.ConnectionInfor) Create Database permission denied in database 'master'. (Microsoft SQL Server, Error: 262)"
View 1 Replies
View Related
Feb 6, 2004
Hi,
I am developing reporting application (access project) which will be used in multi user environment.
Here is what I have:
1 SQLServer database for many users
Each report will be based on:
stored procedure which creates a table filtered for specific dates predefined views will use the newly generated table to show results to the client. However, if more than 1 person runs reports results will not be accurate if each person specified different dates because they will look at the same table and results will match only for a user who called the stored procedure last.
what can you recommend - how to report in multi user environment?
Many thanks
View 2 Replies
View Related
Oct 4, 2007
Hi all,
I just want to know if SSCE 3.0/3.5 can cater for Multi-user instances? I have a desktop application that I want to allow multiple Windows-user accounts to access. basically I need a datastore that can be lightweight (which SSCE is) and support "User Instances" (like in SQL Server Express)
thanx!
View 5 Replies
View Related
