I'm working on an app in Visual Studio 2005 that needs to have installed locally SQL Express to handle its own data.
This app is in a strong upgrading process. I've been using clickonce to publish the app and let my users upgrade automatically (by the way this works amazing ...).
Yet many times changes to the DB are made. Is there a simple way to upgrade their local SQL Express DB like click once does with the app?
I've written a ClickOnce app that deploys a Sql Server Express data file (.mdf) and log file (.ldf) along with the other application files. SQL Express SP2 is a prerequisite for this app. After the applicaiton installs and starts up it copies these files to a specific folder and attaches them by calling the AttachDatabase() method of a Microsoft.SqlServer.Management.Smo.Server object.
This method works fine using Windows XP, however when I run it on Windows Vista I get the following exception:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Attach database failed for Server 'localhostSQLEXPRESS'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Unable to open the physical file "C:WashMasterDatabasewasshmaster.mdf". Operating system error 5: "5(Access is denied.)".
This exception occurs when I try to programmatically attach the database.
I've searched through the MSDN forums for a solution and I've only found two suggestions:
1.) Create all database connections using a User Instance. 2.) Create the database (using SQL) in the target folder, rather than copying it over from the deployment folder.
I don't like the idea of using User Instances because my entire data access layer is currently written using Ole and I would rather not rewrite it if I don't have to. I cannot find any way to use User Instances with Ole.
I also don't like the idea of creating the database using SQL because it is fairly large.
Here are my questions:
1.) Can User Instances be used with Ole?
2.) Can I somehow programmatically set the permissions of the copied data and log files so that SQL Express can access them without error?
3.) Is there a better method for doing what I'm trying to do or am I destined to have to either rewrite in data access layer so I can use User Instances or create my large database rather than copy it to the target directory?
SQL Express CTP 2 provides a checkbox that will enrol the user installing the software as a database administrator. If the user fails to check this box, they run as an ordinary user, and hence are not allowed to perform functions such as create database.
In the process of deploying an application via ClickOnce, if SQL Express is not present it will be installed automatically. Also, in the process of deploying a ClickOnce application, we should usually expect to need to create a new database. Currently, if the user takes the default install of SQL Express they will not be a database administrator and will not be able to create the database. The installation will thus fail.
Is anything being done with SQL Express for ClickOnce applications deploying to Vista that will allow us to automatically enrol the user installing SQL Express as a database administrator?
I'm posting this in the ClickOnce forums as well...
My application doesn't include the .mdf and .ldf files, rather it creates the database the first time the application is run using a script that is included in the build. I want to create the database in the ClickOnce data directory. The problem I'm having is that SQL Server complains when I execute the CREATE DATABASE command as follows:
CREATE DATABASE [C:Documents and SettingsxxxxxLocal SettingsApps2.0DataPQCK6EXN.5KGAW630RPT.VGOifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89DataiFieldMobile.mdf] ON PRIMARY ( NAME = N'myDatabase', FILENAME = N'C:Documents and SettingsxxxxxxLocal SettingsApps2.0DataPQCK6EXN.5KGAW630RPT.VGOifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89DatamyDatabase.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'myDatabase_log', FILENAME = N'C:Documents and SettingsxxxxLocal SettingsApps2.0DataPQCK6EXN.5KGAW630RPT.VGOifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89DatamyDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) END
Here's the error:
System.Data.SqlClient.SqlException "The identifier that starts with 'C:Documents and SettingsxxxxxxLocal SettingsApps2.0DataPQCK6EXN.5KGAW630RPT.VGOifie..tion_014028c05b1d6ec6_0001.00' is too long. Maximum length is 128."
I posted this in the SQL Server Express forum as well...
My app deployed via ClickOnce. It ships with a database creation script which creates the database in the ClickOnce data directory. Upon uninstall, the entire data folder is deleted successfully. But if you then reinstall the app, SQL Server Express still thinks the database exists. Right now, to get a re-install to work, I have to go into Management Studio Express, click on the db (get an pop-up error as the physical db file is no longer there) and then click delete.
So my question is... what system proc should I execute in the db creation script before I run the CREATE DATABASE command?
I'm trying to compact a SQL CE database programmatically. I've copied the code from MSDN, and it's fine as far as it goes, but there's a problem tying to compact a database in a ClickOnce application because you don't know the path to the database. You have to use the "|DataDirectory|" in your connection string to signify the path to the database. But when I try the file operations to replace the old database with the compacted version, I get an error message that the path "|DataDirectory|" is not valid. Here's the code:
Question about deploying a SQL compact database with Clickonce. Deploying the first time is straightforward and that works OK but I am not so sure about consequent releases, how do I deal with the that? If I mark the file as dont deploy that will, of course, work fine for those that have installed the first release but not for thoses that havent. What about if I make a change to the database, adding a table for example. How do I go about that? I obviously dont want to loose any of the existing data?
I have been using sql server everywhere for some time now in my desktop application. We use clickonce technology to deploy. I find that each time I update using clickonce my empty db file in the server gets updated with the application. Is it possible to do something such that the db file will not get updated with clickonce if it is present .
I'm working on a VB.Net 2005 application that uses SQLCE as a backend database.
Following the instructions on how to do a private installation (http://msdn2.microsoft.com/en-us/library/bb219482.aspx) so that admin rights were not needed, I successfully got it working as a ClickOnce deployment. But today during a demo for a client, they ran into this error while installing the latest version from our publishing URL:
"Unable to install or run the application. Application requires that assembly System.Data.SQLServerCe version 9.0.42 .... must be installed in the Global Assembly Cache."
Basically it's saying the user isn't an admin so they can't install. This is the problem I went to great lengths to try to avoid, and is the reason we are using SQL CE in the first place: admin rights are not required to install our application's database. To our knowledge, SQL CE is the only Microsoft product that fits this scenario.
Now, I haven't changed anything in the publishing of the application to my knowledge, and when I check the project prerequisites, the SQLCE engine still isn't in there (as summarized at the above URL). Again following the instructions at the URL above for private installation, the required assemblies are still part of the project's files.
It must be something I did to cause this, but I have no memory of changing anything in regards to this part of the application. Our deadline is in 4 days and I cannot continue development until I get past this issue.
Where do I look to fix this problem? A little help would be greatly appreciated.
I'm attempting to grant rights to Report Builder as deployed as part of my TFS install. My problem is that I have to add my users to the builtinadmin group in order for them to see the report builder button on the SQL Server Reporting homepage & then have rights to launch the ReportBuilder click once app. I obviously do not want make users admins on the box, but I've tried adding them to all of the other groups having to do with SQLServer to no avail. How can I grant users access to launch the Report Builder app?
I have a sql express .mdf that is located in c:appdirectorymydb.mdf whilst developing an application. During development my connection string uses the above path to connect to the db. e.g.
I have published the app using click once in visual studio.net which places the db in : C:Documents and Settingsuser.nameLocal SettingsApps2.0DataJN1WL1DJ.X7B ... 08ed2810aab2abData
When i am executing the application following a clickonce install, what should I use in my connection string for the path to the db ? or where can I find the above path (e.g. in the registry) so that I can provide it to the connection string ?
Alternatively, can I specify where the DB is going to be installed (via click once). e.g. in my app directory under program files ?
I have tried searching what seems like everywhere for a solution to this problem and as a last resort thought it worth posting the question.
When I downloaded/started using Visual Web Developer I was under the impression that I needed to install SQL Server Management Studio Express in order to create/manage databases, and to provide the engine to access the data. Since then I have found tutorials and have successfully created/used databases solely from within Visual Web Developer. I'm assuming that Visual Web Developer includes a database engine, much like the webserver that is included. (This is an awesome thing). When I tried to upload my web application with database to my production server, the database would not work, it started working after I installed SQL Server Management Studio Express on the server. Is it my understanding that you need SQL Server Management Studio Express if you do not have Visual Web Developer Express installed in order to provide the data access engine? Also, I am unable to "attach" my Visual Web Developer Express created database to SQL Server Management Studio Express. Are there any posts that provide more information about this topic?
The only reason I'm asking is that I have extra whitespace on the end of my text fields, and I thought ANSI_PADDING was turned on. I do not see the option in Visual Web Developer Express, but have found it in SQL Server Management Studio Express.
I have VC++ express and MSSQL SMS express and have an application working nicely locally. The Data explorer and data connections part works really easily.
Now, I want to make the application available to my home network.
I mapped the drive where the database is and called it Z: so I could put my "release" on my other network PC and assumed it would find Z: if I mapped the shared network drive on that machine and called it Z:
But: I can't even add the mapped connection on the local machine, I get:
The file "Z:databasescalorie.mdf" is on a network path not supported for database files. An attempt to attach.....etc"
It works fine on the original F drive.......
Am I approaching this the wrong way. How should I distribute to network PCs?
I am a newbie so i apologies beforehand for any mistakes i make on this forum. Anyway, i created an asp.net website using the MS Visual Web developer tool. This has a couple of SQL SERVER databases within it. I then latervinstalled the SQL SERVER management studio express in order to manage the SQL SERVER DBs that i had as part of my website but they do not appear within the management studio db list. As this is the express version, is there anyway of importing the existing SQL SERVER dbs that i have to the management studio so i can manage them from there?
I get an error dialog when I try to create a new SQL database, both via the Add New Item dialog and the property wizard of a new SqlDataSource control. The error is:
Local Database File:
User does not have permission to perform this action.
I've searched for help with this.
I ensured the App_Data folder exists and I added the local ASP.NET account to the group that have R/W access to it (although the RO flag is in an unchangeable tri-state on the folder). The SQL Server Express error log is clean and indicates full functionality. Everything is running locally. No VWD installation errors.
Here is the error message I get:http://img109.imageshack.us/my.php?image=error2ug.jpgI have just installed both packages and havent done much in the way of configuration... What do i need to do? Thanks!
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).
Hi, I'm completely new to SQL Server and ASP.NET, so please bear with me on my learning curve! I've installed Visual Web Developer Express and SQL Server Express (on the same PC). I then used Web Developer Express to successfully create a new database with a couple of tables in it. So far so good. I then installed SQL Server Management Studio Express on the same PC and tried to connect to my new database. The connection to the SQL Server instance appears to work OK (using Windows Authentication) but I can't see the database I previously created. All I can see are the System Databases. Does anyone know what the solution might be?
In the last one and half years, I used the Northwind Database in SQL Server Management Studio Express (SSMSE) to learn the programming of SqlConnections, Data sources, Database Exploere, ADO.NET 2.0, etc. via VB 2005 Express.
The Northwind Database in my SSMSE got lost very often, but I was not aware of it. How can I know where the Northwind Database is used or processed by my VB 2005 Express projects that were the examples of some tutorial books or my trial projects? How can I release the Northwind Database back to my SSMSE from the VB 2005 Express projects? Please help and advise.
I am new to this type of programming and and have read all articles on adding an image to the database and it seems they all use sql queries to add an image but I want to add an image at design time. I am using Visual Basic 2005. I am also using Visual Basic 2005 Express Edition to try the same thing. I am trying to build a Translator program for english to Brazilian Portuguese and the reason I want to add the images is so that when I translate the word cat from english to Portuguese, I can also show an image of a cat. Can anyone please help me
Can VB 2005 express able to connect remote SQL 2005 express database? I played with connection string many time and still could not success! Thanks all.
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
I'm trying to connect to the northwind database thats available online using c# and SQL 2005, both express editions. i used the following command
sqlcmd -S yourServerSQLExpress -E -iinstnwnd.sql
and it connects to the northwind database. but when it comes to the part where i have to select the data source, there are only 2 available - microsoft access database file, microsoft SQL server database file. but according to the microsoft books that i'm following, there should also be a "micosoft SQL server" in the data source list. because of this i cant connect to the database.
I'm trying to learn some VB programming with the VB 2005 Express Absolute Beginner Series video tutorials (which I think is great) and have come across a problem that I can't solve.
When I follow the instructions in Lesson 9 (Databinding Data to User Interface Controls) my application will display the data from the database correctly and I can edit it (and as long as the debugger is running the data remains changed). However, the changes won't propagate back to the database. I don't get any error messages but after I edit the data, save (with the save button on the BindingNavigator toolbar), and end debugging the data in my database remains unchanged. When I use a MessageBox to show how many rows where edited/updated in the
I get the correct number back. I'm sure the problem is not due to coding errors since I've also tried running the accompanying Lesson 9 project file that can be downloaded from MSDN and the problem persists.
I'm using Windows XP SP2, SQL Server 2005 Express Edition and VB 2005 Express Edition. I've tried installing SQL Server 2005 Express with a number of different settings, including default settings, but it doesn't make any difference.
Would greatly appreciate any feedback on this as I'm keen to resolve this problem so I can get on with the next tutorial lesson.
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
I have installed 2 SQL Server 2005 Express sample databases from 2 books, ASPnet 2.0 and ADOnet 2.0. The ASPNETDB.MDF was shown in App_Data and Database Explorer, but not in the SQL Server Management Studio Express. The AdoStepBy Step database created by a ConfigDB.exe was displayed in the Management Studio, but not in the App_Data, or Database Explorer.
Is this the way SQL Server 2005 runs the 2005 databases for SQL Server 2005 Express only? Or also in SQL Server 2005?
I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which I have heard works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.
Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get the following error:
Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.
Of course, some of the entries will be read only, since doctors have to sign off the charts and are not allowed to subsequently change them. But I should still be able to switch over to sql 2005?!?!?!?
Or... is there a way to attach the databases to SQLExpress manually?
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code: ///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure ....................................... etc. /////////////////////////////////////////////////////// If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString: ......................... ........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure ........................ etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Hi, What are the steps required to migrate or upgrade data or database from a sql server 2005 express database to main sql server 2005 database? Regards,Sandy
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()