I have an application created with VS 2005 and VB 2005 using a Compact Edition Database. I created some tables that I no longer use or want. How can I permanently delete them from the database?
Connect to the database a run .ExecuteNonQuery:
Code Snippet
DROP TABLE tablename
You may have to run
Code Snippet
ALTER TABLE tablename DROP CONSTRAINT constraint_name
first, if you have foreign key constraints.
I installed my SQL 2005 Developer edition and applied the SP1. I didn’t have a time to wait the installation to complete (I needed to reboot) so I cancelled the SP1 which was downloaded from Windows Update.
Now, my development PC says that SQL 2005 SP1 is applied but I am not sure if it’s applied correctly. I tried to reinstall SP1 in order to be assured but I can’t get it reinstalled. I get error message saying that you already have SP1 2047 ENU applied.
I just don’t want (have a time) to troubleshoot some possible upcoming issues in development because of this. So, how can I be assured that I do have SP1 applied correctly?
At what stage you have to rebooted the box?
if this is used for a testing on production, then its better to resintall afresh to avoid any spurious errors in future.
Run SELECT @.@.VERSION and see the results.
|||Yes. 9.00.2047 is the SQL Server 2005 SP1 version. You can verify the following items to get more confidence.
1. Add/Remove Program in control panel to check the status of SQL Server 2005.
2. Go to registry keys HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup to check the version.
3. You can check the file version of %ProgramFiles%Microsoft SQL Server\MSSQL.<instance id>\MSSQL\Binn\sqlservr.exe.
|||I try to uninstall the SP1 but its not possible, so I guess in order to be safe I should uninstall the SQL 2005 developer edition completely and reinstall it and apply the batch again.
It looks all good in SQL site, but SQL reported the SP1 applied and Microsoft Update reported the SP1 cancelled. This is a bit wired, so I going to do the reinstallation in order avoid any spurious errors in future.
Thanks folks....You great....
Alexander
I developed an asp.net application in visual web developer 2005 express edition and SQL sever 2005 express with Advanced services. The application has been deployed and iam wondering what tools are availabel to for backing up my data. Are there any tools i can use to back-up my database. Iam not talking of third party tools but tools a vailable in sql sever 2005 express with advanced services or visual web developer express.
OR can write a vb.net Sub procedure that i run and have my database backed up. If so where can i start or what other options may i explorer.
Hi Dear,
you can backup your datbase by usingMicrosoft SQL Server Management Studio and also by ur VB.NET application
i will try to explain you both ways.
1:By using Microsoft SQL Server Management Studio
Open Management Studio and Right Click On Your Database and go to the Tasks->Backup menu item, Backup Database Dialogbox will be open.
Click Add button in Destination Panel, and choose the Destination File. and simply press OK ( This is the simplest way, but you can customize your backup by choosing different options available on Backup Database Dialog)
2. By VB.NET Application
execute following TSQL statement in your SQL Server
USE
masterEXEC
sp_addumpdevice'disk','MyDataBaseBackup','c:\YourDatabaseBackupDirectory\MyDBBackup.dat'' and then use following code in your application
' VB.NET Code
Dim conAsNew SqlConnection("ConnectionString")Dim comAsNew SqlCommand("BACKUP DATABASE YourDataBaseName TO MyDataBaseBackup")com.Connection = con
com.Connection.Open()
com.ExecuteNonQuery()
com.Connection.Close()
Thanks
Regards,
Muhammad Akhtar Shiekh
|||Oh thanks very much Akhtar,
I want to take on and understand thoroughly the second option(vb.net option) as its looks to be more flexible though a bit complicated. Looks like i have to start with stored procedures, isn't. Please would you point me to a tutorial from microsoft to get me started with the core basics of backingup a database using this approach.
Thanks very much.
|||Check out the BACKUP statement in Books On Line. You might want to schedule it as a job and include it in your weekly maintenance window (if you dont already have one, time to create one) rather than create a VB application for it.|||
The problem all starts with version of sql sever one is using. some of the documentation do not apply to the express version of sql sever so you find your self wasting valuable time reading an article that you later find out does not apply to the product version your running. Iam running sql sever 2005 express edition with advanced sevices in my case.
sql
Hello,
I just installed SQL Server Compact Edition, since I am considering using it instead of SQL Server Express for a local database in my application. The documentation mentioned that I could use SQL Server Management Studio Express to connect to the Compact Edition and create and manipulate databases.
To try to connect, I run Management Studio and bring up the "Connect to Server" dialog. Unfortunately, the pull-down list of "Server name"s does not include the SQL Server Compact Edition server. I do not know how to type in the server name manually, so I cannot connect.
To install Compact Edition, I downloaded it and ran "SQLServerCE31-EN.msi". This installed, and I assume registered, a number of DLLs in "C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\".
Is there perhaps an additional step that I left out to complete the installation?
Might I need an upgrade to some other components? My Management Studio Version is:
- Microsoft SQL Server Management Studio Express 9.00.2047.00
I would appreciate any help you can provide.
Thank you,
WTW
The support for SQL Server 2005 Compact Edition was added in Microsoft SQL Server Management Studio Express (SSMSE). SSMSE is available for download at:
http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
SSMSE shipped with SQL Server 2005 SP2. SQL Server 2005 SP2 information is at
http://technet.microsoft.com/en-us/sqlserver/bb426877.aspx#3
Regards
Ambrish
|||WTW,
I've written a blog post about this, you can see it at http://arcanecode.wordpress.com/2007/04/11/create-a-sql-server-compact-edition-database-using-sql-server-2005-management-studio/ . I've also written an article that describes all the components you'll want to install to make everything work right, http://arcanecode.wordpress.com/2007/04/24/sql-server-2005-compact-edition-important-component/ .
You might also want to check the Arcane Lessons page for more SSCE tutorials.
Arcane
|||Thanks for your reply. I uninstalled SSMSE and downloaded and installed it again. However, I still cannot access a SQL Server 2005 Compact Edition database.
When I bring up the "Connect to Server" dialog, the "Server Type" is fixed at "Database Engine", and I cannot determine how to change it. I've tried most of the menus and buttons, and I cannot find a setting for this.
I am missing some other component or configuration option?
Thanks for your help.
WTW
|||Thanks for your very helpful reply. Thanks to your blog, I now have all of the proper components installed to make Compact Edition work properly. Your blog was the only place where I have found a complete list of all of the necessary components.
I have even purchased the E-book that you recommended, and it is very helpful.
However, I still am not able to access a Compact Edition database through SQL Server Management Studio Express. When I bring up the "Connect to Server" dialog, the "Server Type" is fixed at "Database Engine", and I cannot determine how to change it to Compact Edition or something similar.
The E-book demonstrates how to access a Compact Edition database through Management Studio. However, it shows a "Connect" menu in Management Studio that does not exist on my version. According to the E-book screen shots, the "Connect" menu should allow you to select "SQL Server Mobile", which will then bring up a "Connect to Server" dialog that shows a "Server Type" of "SQL Server Mobile". The next line allows selection of a "Database file" instead of a "Server name".
My version of Management Studio does not have the "Connect" menu. I even tried reinstalling Management Studio from the Microsoft website. However, I discovered that I already had the latest version, 9.00.2047.00.
I would appreciate any additional help you can provide.
Regards,
WTW
|||In typical microsoft style, the documentation is horrible on this. I was only able to get it to work by installing SP2 of SSMSE (of which there is no mention on the regular SSMSE download page) at:http://www.microsoft.com/downloads/details.aspx?familyid=6053C6F8-82C8-479C-B25B-9ACA13141C9E&displaylang=en
and the SQL CE SDK found at:
http://www.microsoft.com/downloads/details.aspx?familyid=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en
You will know you have SP2 of SSMSE installed if you version is 9.00.3042.00
|||
SP2 did the trick. Thank you so much.
Regards,
WTW
hi:
I am a newbie to sql server 2005 (developer edition). Now I installed both sql server and VS 2005 professional edition on my machine.
I just what to know how and where should I create a mdf file on my server or .net window project because I try to combine to it from a window application. Should I use the sql server management studio or the Visual Studio 2005 ? What is the generate steps to create one?
I am completely confused. Please help me, I cannot find related on line source.
Thank you, thank you
bigheadjj
That depends wheter you want to use a user instance with a SQL Server database or a SQL Server hosted instance. The Visual Studio Database can support both, whether a user or a server instance. Did you have the concept of a user instance or a server instance ? If not, I would suggest you reading the principles of that in the web.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||Even if VS 2005 can connect to the server and create database files, I recommend that you use the SQL Server client tools (SSMS, sqlcmd, etc.) This gives you a good understanding of what is happening on the database side. Plus, using the SQL Server client tools, you can configure other database options which cannot be done using VS 2005|||
Jens
Thank you very much. I will start to read related principles as you suggested.
|||bass_player
thank you for helping me. Your recommendation is very helpful.
Randy,
I did run upgrade advisior to check the existed sql 2005 standard edition to upgrade to enterprise editon. I got the following error message:
SQL Server version: 09.00.1399 is not supported by this release of Upgrade Advisor
Is it means the upgrade advisor can only work on from 7.0, 2000 to 2005? If I need check from standard to enterjprise in 2005, what kind of tool I can use?
One of the SQL Server forums would be a better place to ask this question:
http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1
You'll have better luck finding an answer there.
-Tom
