Is SQL Server Express A Replacement To MS Access Databases?
Feb 27, 2008
I have a small client installable web application which used to work great on 32 bit machines. Well now clients are starting to use x64 Windows 2003 servers and things have begun to unravel. W2K3 x64 does not have any of the JET database drivers needed to communicate to the Access databases and it doesn't look like they're coming anytime soon.
Is SQL Server Express the new replacement for Access databases on x64 machines. Does Microsoft intend on moving some of the JET drivers over from 32 bit servers. I know that you can run IIS in 32 bit mode and everything is fine, but I can't ask everyone to do that.
Is SQL Server Express something that I can expect people to have installed, it sure doesn't sounds like it. After reading some posts, it sounds like it's a bit of a hastle to install.
Any advice that can be offered on this subject would be much appreciated.
We are debating whether to backup/restore the replicated databases or simply copy the mdf and ldf files accross and Attach them. The question is whether the replication information is retained for the publshed objects when the files are copied across? Alernatively, must we use backup/restore with KEEP_REPLICATION set for the restore?
We also plan to back up and restore the distribution, publication, master, msdb etc databases as well as rename the SQL Server to have the same name as the original.
Do these things need to happen in any particular order?
Does the timing of the renaming of the SQL Server matter?
At least some of the these operations (restoring the master database and renaming the SQL Server) need to be done in single user mode. Is is best to restore all the system databases in single user mode?
Lastly, is it necsssary (from a SQL Server point of viewpoint) to rename the computer to have the same name as the SQL Server?
Just wondering if anyone has any suggestions for a replacement for Access Forms once I move the tables etc to SQL 2005? Does SQL 2005 have any form building functionality like Access?
Hi, How can I provide a user read only access to all the databases on the server. I have 15 databases on the server. I know I can give db_datareader access in each database individually but that is time consuming and I have 10 servers for this to be done.
Hi there,I have a situation where an application needs to import data fromnumber of access mdb files on a daily bases. The file names changeevery day. The data import is very straight forward:insert into sql_table select * from acess_tableThere are up to 8 tables in each access file and some access files willhave less. So the process needs to figure out which tables exist inAccess mdb file and import them whole into sql staging tables.Any recommendations are appreciated.Thanks
I have a test server where I was experiencing security/rights anomalies. I tried disabling the BUILTINAdministrators account, closed SEM, and when I reopened it, I could not access my Server (all databases,etc.are on local machine). I thought I could just use the SQL accounts (sa, etc.) to connect, but they all fail?? I tried creating a new registration, and that didn't work either. Since I deleted the old registration, and I cannot reconnect, what are my options(short of restoring image)? If I re-install SQL will all my databases still be there? Is there another way to get access back?? Any help would be appreciated.
I have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create that user and give datareader role to him. or is thr any shorter way to do so????
I wrote the below script to add a SQL server login account that is the db_datareader, db_datawriter, and granted permission on all stored procs, functions, and views on all databases within a server.
Code Snippet USE master GO SET NOCOUNT ON DECLARE @database_name sysname DECLARE @object_name sysname DECLARE @object_type char(2) CREATE TABLE #databases (DATABASE_NAME sysname, DATABASE_SIZE int, REMARKS varchar(254)) INSERT #databases EXEC sp_databases -- ++++++++++++++++++ add SQL Server Login ++++++++++++++++++ IF EXISTS ( SELECT 1 FROM master.dbo.syslogins WHERE [name] = 'WEB_USER2' ) BEGIN DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR SELECT DATABASE_NAME FROM #databases OPEN db_cur WHILE 1 = 1 BEGIN FETCH db_cur INTO @database_name IF (@@FETCH_STATUS <> 0) BREAK EXEC ('USE ' + @database_name +'; IF EXISTS ( SELECT 1 FROM sysusers WHERE [name] = ''WEB_USER2'' ) BEGIN EXEC sp_revokedbaccess ''WEB_USER2'' END ') END CLOSE db_cur DEALLOCATE db_cur EXEC sp_droplogin 'WEB_USER2' END EXEC sp_addlogin @loginame = 'WEB_USER2', @passwd = 'password' -- ++++++++++++++++++ loop thro' all User-Databases ++++++++++++++++++ DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR SELECT DATABASE_NAME FROM #databases WHERE DATABASE_NAME NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution', 'ASPState') OPEN db_cur WHILE 1 = 1 BEGIN FETCH db_cur INTO @database_name IF (@@FETCH_STATUS <> 0) BREAK PRINT '' PRINT 'Current database=' + @database_name -- add user to databases EXEC ('USE ' + @database_name +'; IF EXISTS ( SELECT 1 FROM sysusers WHERE [name] = ''WEB_USER2'' ) BEGIN EXEC sp_revokedbaccess ''WEB_USER2'' END ') EXEC ('USE ' + @database_name +'; EXEC sp_grantdbaccess ''WEB_USER2''; ') -- add user to db_datareader EXEC ('USE ' + @database_name +'; EXEC sp_addrolemember ''db_datareader'', ''WEB_USER2''; ') -- add user to db_datawriter EXEC ('USE ' + @database_name +'; EXEC sp_addrolemember ''db_datawriter'', ''WEB_USER2''; ') -- grant permission on Stored proc, Scalar function, Inlined table-function, Table function, View -- !! coz EXEC is a self-contained batch, so must use GLOBAL EXEC('USE ' + @database_name +'; DECLARE obj_cur CURSOR GLOBAL FAST_FORWARD FOR SELECT [name], [type] FROM sysobjects WHERE [type] IN (''P'', ''FN'', ''IF'', ''TF'', ''V'') ') OPEN obj_cur WHILE 1 = 1 BEGIN FETCH obj_cur INTO @object_name, @object_type IF (@@FETCH_STATUS <> 0) BREAK -- PRINT 'object=' + @object_name + '; type=' + @object_type IF LTRIM(RTRIM(@object_type)) = 'P' OR @object_type = 'FN' BEGIN -- EXEC on Stored proc, Scalar function EXEC('USE ' + @database_name +'; GRANT EXEC ON dbo.' + @object_name + ' TO WEB_USER2 ') END ELSE BEGIN -- SELECT ON table function, View EXEC('USE ' + @database_name +'; GRANT SELECT ON dbo.' + @object_name + ' TO WEB_USER2 ') END END CLOSE obj_cur DEALLOCATE obj_cur END CLOSE db_cur DEALLOCATE db_cur
The Classifieds starter kit supplied by microsoft has 2 databases - aspnetdb.mdf & classifieddb.mdf. How do I combine the two into one. I understand that there is now need to have two databases. I also understand that most hosting co's do not support Sql Server Express Any help offered is appreciated. Please offer help in a Dummies format - I might understand that
I've got a SQL Server hardware Replacement to do. This server has a merge replication setup on it which was setup by a contractor.
Do i have to do the configurations manually when moving the replication bits across two servers or is there a different process involved ? I am just not sure how to approach this.
I did a full BACKUP for two SQL databases using SQL Server Managament Studio. When trying to RESTORE the DBs, I get the following error:
"System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)"
There are NO users using the application. These are DBs for the Portfolio Server 2007 application.
After exploring a little bit, I found that there are several "SLEEPING" processes with an "AWATING COMMAND" flag. The processes come from the Portfolio Server Application, maybe from previous sessions.
Can they be deleted? if so, would there be any serious consequences?
Which tool can I use for structure editing of SQL Server Compact 3.5 databases? I'm installed SQL Server Compact 3.5. I have SQL Server Management Studio Express which was installed with SQL Server 2005 Express. Unfortunately this SSMS can create and open only 3.1 databases.
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it. Any help is appreciated and thanks in advance,
- Noble Bell
I'd be interested in knowing how VS doesn't allow you to create a database. What error are you getting? There are two ways to create databases, depending on your goal:
To just create a database on your server, do the following:
Open the Server Explorer Right-click on Data Connections Click Create New SQL Server Database Specify Server Name and Database Name
Your database will be created and you can start working with it.
Embed a database in your project:
On the Project menu, click Add New Item. Select SQL Database from the list and give it a name. Click OK
This will run you thorugh a wizard to create the database. If your having problems doing either of these, you may not have SQL Server installed on your computer or VS might be pointing to the wrong Instance Name. Check out the Option dialog under Database Tools:ata Connection and verify that the correct instance is specified.
I am trying to create a similar project and I also recieve an error while trying to create a database...
"An error has occured while establishing a connection to 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: Named Pipes Provider, error: 40 - Could Not Open A Connection to SQL Server."
Now let me explain what i am trying to do ... I am trying to create a web interface for the data that I want to store in the SQL database. I have installed VS2005 & SQL Server Express 2005 on my local machine. I want to develope this project on my local machine and then transfer it to a server once i have finished the project.
If i am doing this all wrong, please let me know !
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
Event Type: Warning Event Source: SQLBrowser Event Category: None Event ID: 3 Date: 9/15/2006 Time: 8:16:36 AM User: N/A Computer: COPLEYNEWS Description: The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error Event Source: Service Control Manager Event Category: None Event ID: 7024 Date: 9/15/2006 Time: 8:16:36 AM User: N/A Computer: COPLEYNEWS Description: The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411. C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql" HResult 0x2, Level 16, State 1 Named Pipes Provider: Could not open a connection to SQL Server [2]. Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi shing a connection to the server. When connecting to SQL Server 2005, re may be caused by the fact that under the default settings SQL Serve allow remote connections.. Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
I am programming in VB6 using ADO 2.8. This connection and query works in MS access, SQL server 2000 and Sql Server 2005. does not work in Sql server 2005 express. Any Suggestions?
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it.
Hi, In SQL Server 2005 Express I currently have 2 databases which I€™m working on, my problem is I want to transfer a table from one of the databases to the other but don't know how to go about achieving this? I want the table structure and all the info held within the table transferred across. Any ideas are welcome.
My company works with SQL Server 2005 express locally with Visual Studio to develop websites. Everything works very well.
We use SQL Server 2005 express on our production server as well. We change the database over to a non-User Instance when the site is ready to go live. Everything works fine here as well.
We run into issues when databases get near 100 MB. This is well below the stated database size limit for Express of 4GB.
At that point, about once a day, a site with a "large" database will stop responding. The error that we'll get is "Cannot open database 'DBNAME' requested by the login. The login failed. Login failed for user 'DBUSER'
The only way we've found to fix this is to restart the SQL Express service. Obviously, that isn't a very useful alternative.
Has anyone run into anything like this? Could we have some setting wrong?
Would moving to the full version of SQL Server 2005 fix this?
Hi everyone, I have some code that we need to migrate to SQL Server2005 from 2000, and I have a webpage that upon viewing, fires a queryto the SQL server using ISS and ISAPI. The result set is formatted fordisplay using XSLT. But since ISAPI is deprecated in SQL 2005, I waswondering how to migrate this.Thanks,
I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.
Here is the breakdown of the servers:
1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit 2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard 3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)
**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?
Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server? I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit.
INSERT INTO #Tab (Data) Select 'Apple=5,Orange=10,Banana=11' UNION ALL Select 'Apple=10,Orange=1033,Banana=0' UNION ALL Select 'Apple = 120,Orange = 1,Banana = 112' Select * from #Tab
How do I replace every value before the '=' but leave the comma.
Here is what the final output should look like
CREATE TABLE #TabFinal (Data Varchar(100))
INSERT INTO #TabFinal (Data) Select 'Apple,Orange,Banana' UNION ALL Select 'Apple,Orange,Banana' UNION ALL Select 'Apple,Orange,Banana'
Subject says it all, really. I want to start using Token Replacement,but do I break anything by enabling it? Do jobs that don't use tokensrequire any changes? I saw somewhere that it can't be turned off, soI'm paranoid about enabling it. Anything that I should be aware of?Many thanks.
I have a windows 2003 server which has SQL 2005 Express with advanced services installed on it. Then a few weeks back the company purchased SQL server Standard Edition which comes with SQL Server Management Studio (which has more features than SQL Server Management Studio Express currently installed on the server where sql express is running)I have been trying to schedule a maintanance plan on the SQLExpress Instance database from the SQL Server Management Studio that came with the standard version of sql but i have not been able to have all SQL Server Management Studio functionality available when connected to the SQLExpress instance. So is there a way i can connect to the Express Instance from management studio(that comes with the standard edition of sql) and have all its functions available when working with a SQL express database. Or i must upgrade the express database?
I installed the managerment stuid on my machine and now none of my database connections seem to work any more. I keep getting the attached error. Before I installed the studio the system worked fine, I could connect to the database and everything was fine. Does the studio change any permission stuff? I cannot seem to get it to work again at all now, I added in aspnet as a login inside the studio and it still won't let me connect to the database. I cannot find *any* logs anywhere that might be helpful in what it is doing or see any way to debug the connection to find out what the problem is. When I look at the permissions inside of the studio it shows me that the connections should all be fine.
I checked and remote and local connections are setup, although it is only doing local connections anyway since this is a asp.net web server page.
Any idea what the problem is here? This is very frustrating and annoying :( Since I didn't change anything and now nothing I will do gets it to work. I even tried to uninstall the management tool and it still doesn't work. Cannot open user default database. Login failed. Login failed for user 'GREENANDFLUFFYASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed. Login failed for user 'GREENANDFLUFFYASPNET'.
Hi,When I attempt to add the membership functionality to a database created within VWD Express, by using the aspnet_regsql.exe application (connecting to ./SQLEXPRESS using Windows authentication), the database I've created within VWD Express doesn't appear within the list of accessible databases. I have the same problem when attempting to access the same database within Management Studio Express.Does this mean that databases created from within VWD Express don't use the same storage mechanism or paradigm as those already available within Management Studio Express? Or perhaps I need to somehow register the database using Management Studio Express? As a side note, I'm not sure if the "File/Open File" menu option within Management Studio Express holds the answer to my problems, but rather bizarrely, it crashes Management Studio Express when I select it.Apologies for the rambling - in essence my question is "How can I make databases created within VWD Express appear within aspnet_regsql.exe and/or Management Studio Express?"Many thanks,Jon
Hi, i am very new to databases. I use VWD 2005, Sql Server Express 2005 and have a remote database through a host rather than my own server. i have managed to create sql databases fine. Its with Access Databases Im really geting confused: Can i use an access database I have created using VWD and sql server Express 2005? Or would it run just through VWD and the remote ? If i use VWD I dont usually need to use IIS but if the latter of the two options is what I must do then i would manually need to create a virtual folder? Nick
I just downloaded SQL Server Express from online and I'm unable to access it now. I cant make a connection nor can I create a database. Could anyone help me out with this and let me know what I've done incorrectly? Thank you!
I've installed Visual Studio Express C# and Visual Studio Express Web Designer along with SQL Server. I've downloaded the example databases and have installed them - I can access the databases using the SQL Server tools.
When I try to access any SQL Server databases (specifically the Northwind example DB) via the Add Database Connection dialog in either C# or Web Designer, I get an error box with message "Exception has been thrown by the target of an invocation".
I get this when I manually enter Northwind in the Database Name field; if I try to browse, the whole dialog exits, or, sometimes Visual C# Express crashes. Since the same problem occurs both within C# and Web Designer, I'm assuming the problem is in SQL Server, which is why I'm posting in this forum.
I'm sure I have some configuration problem, and a complete uninstall/reinstall is certainly a possibility, but I'm hoping someone has an idea about how I can around this problem without having to reinstall.
I have been developing a database application in MS Access that is intended for distribution. Before I go any further I would like to make sure that I should be using MS Access and not SQL Server Express. Can someone point me to a good source of information on this topic?
