' etc. And, I cannot run 'sqlservr.exe -c -m -s MSSQLSERVER' either (MSSQLSERVER is the only instance), it also reports "Cannot recover the master database. Exiting."hi, as you can read from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_5w6d.asp , you need to rebuild the master database, but as rebuildm utility is not provided with MSDE we are deep in trouble what you can probably do, is to copy all your user databases remove the MSDE instance, re-install it, copy your databases to the MSDE instance data folder, reattach them or restore tehm, depending on your media a thing I never tried is to reinstall (as previously installed no param modified) and restore the older master backup this should preserve
SQL Server MVPThanks a lot! I have already found references on how to recover/rebuild the master db. However, what I haven't found yet is, what causes the corruption of the database. With this cause identified, we can then proceed to the discussion of how to fix the problem, whether thru code, or thru rebuilding master db, or something. Any ideas anyone? :)Hi, If your SQL Server is not starting with Master database is corrupted. Best option is to REBUILD Master and proceed. Hari SQL Server MVPhi Hari, actually we're here on MSDE ng rebuildm.exe is not provided with it.. :( :)Typically database corruption is hardware related. Check the application and system logs of Windows Event Viewer.Just what I need to hear. Thanks Lori. And thanks alot to all who replied
of MSDE This leads me to the following problem: When I update the application, I have to update database too (because app access database with stored procs) I move database to test server either generating a script generated by DBAMgr2k from Andrea Montanari (I don't have Enterprise manager, since I have only MSDE), or I detach database from a server and reattach it to another one However, with both of those approaches, I loose all data from database previously on test server, since it is replaced by new one. Is there any easy way to import all data from a database to another one? Thank you for any suggestion.Here are some options to generate DDL script, some including data: You can use SCPTXFER to generate DDL from code as described in: DTS Does Not Copy Identity, Indexes
How do I programmatically find the names of all data tables in a MSDE database? Is there an ADO.NET method that can help, or a SQL command? Thanks in advance.You can refer to information schema view called "Tables". ex: SELECT TABLE_NAME, table_schema FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
I am using MSDE in sql server 2000. When the database size exceed 2G limit, I tried to shrink the db and it reduced by about 100MB. 2 days later, I checked the db size and it was reduced to 550MB, which is 1/4 of the original db size! Please kindly explain the reason of this case, and how can it be monitored effectively? Thank you.hi Jackie, if lot of delete operations (and/or truncate table, drop table) have been performed this could be your issue anyway, when it has been shrinked to 550mb, that size could not be 1/4 of the original db, as the shrink operation never can shrink a file to a size that is less then the creation size..Dear Andrea, I've compare the two db using red-gate. No significant change of data were made. Is there any case that a db will change size
Hi, If i have some data on a table that's being deleted by an application and i did not do backup but the recovery mode is "Full Recovery" can i somehow get back the data ? i know there's a deleted/ (created or updated) table but that's wtithin a session isn't it ? appreciate any advise tks & rdgshi, unfortunately there's no native way but you can have a look at http://www.aspfaq.com/show.asp?id=2449 for some tools that enable that kind of stuff..Hi Andrea, tks for the link that you have provided rdgs
= N'C:\Programmi\Microsoft SQL Server\MSSQL\Data\a_log.LDF'" >c:\err.txt and it works as expected you say and this seems to me not related with the execution of sp_attach_db, but perhaps my english is to poor to understand your meaning Sorry for the confusion. What I mean is that when that batch file is initially executed, by the Wise utility, it does not appear to work, because when I run the app, it says if fails for login 'sa'. If I then go double-click on this batch file, and run the app, the app runs fine. Notice that the batch file does not set any passwords. It just attaches the database, so I don't think the problem is with the login, its with the attaching. The question is, why would it not work if the Wise
Hello all, My app uses MSDE to sotre data, but raw data in database are not supposed to exposed to end users per my view. I found that users can attach the data file installed to another database and then see everything in the database. That's what I don't wanna see. Per my thoughts, users are allowed to log on by userID and password for the application, but not the database level. Right now all information in DB can be seen without a password, is that wierd? Any thoughts? Thanks in advance, Vincenthi Vincent, this is the way SQL Server manage secuirty and you can not prevent that you can secure the server machine, not allowing files to be directly accessed via strong ACLs permissions but if the sysadmin wants to move the datafiles to another server, you can not
from MSDE to another computer with MSDE? DamianoDamiano, A good place to start is to have a look at the stored procedures called sp_detach and sp_attach_db. After you detach the database, just move the MDF and LDF files to the other computer and attach them to the other server. DanIf you've committed all pending transactions before detaching then there's no need to move the transaction log also. sp_attachdb will create a new transaction log for you. JimHi, Detach and Attach require some down time in actual server. If you do a Backup in source server , copy the backup file to destination and do a Restore - This require Zero down time. Steps:- OSQL -Usa -Ppassword -Sserver (enter) 1> Backup database <dbname> to disk='c:\dbname.bak&
with access, we have a frontend mdb and a backend mdb, which has all the data. There it is only copy the backend and link the front end tables to it and its done. What is the process with MSDE ? When you make a setup of your program, how can you make it install MSDE and then your database in it.Hi , The following articles may be useful in deploying MSDE with custom applications. 1) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html /usingmsde.asp - Using MSDE to Build Scalable Solutions That Migrate to SQL Server 2) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html /msderoadmap.asp - Choosing and Using MSDE 2000 as the Database Engine for Your Application 3) http://msdn.microsoft.com/library/default.asp?url=/library/en-us
TO DISK='C:\testbackup'" I get: "Cannot open backup device 'C:\testbackup'. Operating system error 5(error not found). BACKUP DATABASE is terminating abnormally." The same command works fine with the MSDE database. I have tried other backup destinations but with no success. I have full admin rights; backup fails in both debug and release environments. Any ideas what's wrong?hi, please verify the Windows account the SQLExpress instance is running on has been granted adeguate NTFS permissions on the destination path..Evidently permissions are not granted! I can back up to the folder where the .mdf file is stored, so that will be fine, thanks. However it would be useful to know how to grant SQL permission to an alternative folder. Thanks for your help
What can I do if I failed to attach a database by EM? If the database is corrupted, any method to recover the database?hi Tony, if you have a clean backup, this usually is the best solution to go but, if the database can be saved, perhaps the advices by Jasper as per http://tinyurl.com/3pxhv can be helpfullHi, What is the error you are getting while attaching? Best option after a corrruption will be restoring from a good backup. If you do not have the backup try below:- 1. Execute DBCC CHECKDB('db_name','REPAIR_REBUILD') , this will corrupt minor issues. If you do not have any options try with Jaspers suggestion http://www.google.it/groups?q=+%22sp_attach_db+%27PowerDVD301%27,%27h:%5Cdvd.mdf%27,%27h:%5CDVD.ldf%27%22&hl=
We currently have the SQL Desktop Engine running on a machine. How do i link a database from another machine. The database is called Customers.mdf and was taken off the other machine due to Windows XP developing a servere fault.hi, if you need to attach a database to the local instance, you have to use sp_attach_db.. please have a look at it's synopsis at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp
I am using MSDE. Currently the size of all the data and system database is 5 gb. I want to know since this is freeware from Microsoft - what is the size limit and how is it enforced. The databases are currently working fine in that they are tested on a remote machine - but I am trying to anticipate any unpleasant surprises regarding this issue. MichaelMSDE has a limit of 2GB per database. For more information about the features and any limitations of MSDE see: http://www.microsoft.com/sql/msde/ Jim
but a search in this newsgroup has not solved the problem at hand: I have installed MSDE on a windows xp pro machine with IIS already running. Later I set up the MS Web data administrator (WDA) However, it is impossble to log in via WDA. As username I have tried "sa", my initials, my windows user name and computername/windowsusername As pw I have tried the strong pw that I used when making the installation and leaving the pw field in blank. As server I have tried "MSSQLSERVER", "Localhost" and my computer name. Nothing works. The funny thing is the above value, I rebooted and now the SQL Server Service Manager is no longer visible in the system tray. But the the database is clearly running (as I can log in from WDA). Any suggestions? Thanks
This is a general question " xpsqlbot.dll" what is it doing running a SP? My environment is SQL 2000 sp 3a One problem I am having is a database is transaction log is being truncated during the nightly SQL backup. All other SQL functions appeared to be running normally TIA Jimhi Jim, xpsqlboot.dll is a container of other extended stored procedures and libraries internally used by SQL Server and is a wrapper around sqlboot.dll it contains, as example, xp_cv, an extended stored procedure that returns info about the SKU type, licensing info and the like.. but it hosts several other fueatures did you check the T-SQL task command been performed by that job? are you sure the right BACKUP statement is executed?
Osql command? how can I compact a msde database like I do with access databaseosql -E -Q "DBCC SHRINKDATABASE (db_name)"Hi, To add on to Jacco, do the steps below shrinking:- 1. Backup the transaction log if the database is not SIMPLE recovery 2. Use either DBCC SHRINKDATABASE or DBCC SHRINKFILE (See books online for usage) Hari SQL Server DBA
current SQLExpress version, code based on SQL Server 2005, you can download the free graphical management tool provided by Microsoft (SQL Server Management Studio Express) at http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en as regard adding "data" SQL Server is not like Access, which provides an "all in one" tool/interface to manage objects and interact with data this task is usually perfomed via "client tools", and actually via applications interacting with the Database Server Access an be one of them, as long as Web or Win applications why are you required to define a DSN? nowdays usually DSN-less connections are used to interact with databases..
I need to know for sure. NickSELECT @@VERSIONRunning SELECT @@VERSION tells me that I have Microsoft SQL Server 2000, which is correct, but it does not tell me whether a specific database listed in Enterprise Manager is SQL or MSDE. Any other ideas appreciated.And I get: ------------------------------------------------------------------------------------------------------------------------ Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)SELECT SERVERPROPERTY('edition') Result = 'Standard Edition'. I have 2 SQL Server Groups (or are they called 1: (LOCAL)(Windows NT) Properties: Version 8.00.760 (SP3) 2: AMPSRV\AMP (Windows NT) Properties
How does the MSDE and SQL Server 2005 Express calculate the database size for the 2GB and 4GB limit respectively? Are they based on database size or just the allocated part of the database size? Does it include log file size? I want to write a script to check whether a database is reaching 90% of the limit. I wonder which function or system stored procedure I should use. Thanks for any help.*Peter* z wysiłkiem wystukał(a) przypadkowy ciąg słów: IMHO they based on allocated database size. It doesn't include log file size.
2000 on an XP workstation. My program has already been reconfigured to point to a remote (from the XP viewpoint) SQL 2000 server. As such, it has created the 2 databases that it uses and is already populating those databases with data. The question is, how do I get the "old" data from MSDE on the XP box into the "new" databases on the remote SQL server? When I try to use DTS and choosing the default of OLE Provider for SQL for the source, I can't connect to the XP MSDE, which is using SQL Auth. I know I am using the correct "sa" password for the MSDE database. RobertYou have a few options: DTS, Detatch/Reattach, or Backup/Restore. I prefer to backup on the old server, copy the backup file to the new server, and restore it on the new server
with a modified one and starts again.hi, actually not modifications (both in the metadata and in the data as well) is a "natural" state of life for data and databases and the "case" of your scenario is even wors as SQL Server is not involved at all, shutting down the service ad overwriting the database's files is performed at NTFS level (so adeguate permissions in this area is required) but, again, SQL Server is not involved.. it is later involved when restarting the service and starting the replaced database.. if something goes wrong in the NTFS operation, SQL Server could mark the database as suspect, but this is another story.. on the other hand, SQL Server 2005 introduces DDL triggers, http://msdn.microsoft.com/en-us/library
I'm running into a problem restoring a database to MSDE. Actually, osql reports RESTORE DATABASE successfully worked but when I view the database from enterprise manager it tells me the database is still "loading " Any suggestions?Sounds like you are using NORECOVERY with the RESTORE command. This will leave the database expecting additional restores. Jim
Express it bombs out on me. Before the PC fully died I even tried using the Data Publishing Wizard to script it and that failed. I have even wrote a detach.sql and attach.sql scripts and the detach works fine but the attach bombs. Any insight would be greatly appreciated. Restore to blank database: TITLE: Microsoft SQL Server Management Studio Express ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo) ------------------------------ ADDITIONAL INFORMATION: Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205) For help, click: http://go.microsoft.com/fwlink
give it the admin password for the old databases. The tool the software company gave me to pull the password does not work, as it pulls the information from the registry (which is not the original as I have a new boot drive). Does anyone know how to pull MSDE SQL database passwords without booting from the original drive? There has to be a way to get all of the databases back since the data is intact.If you have the MDF files, just attach the database to a new instance of MSDE with sp_attachDB. If you mean a specially created user in the database or server AFAIK which the application will use for connection and which is not known to you, this password cannot be recovered. (If you have no access to the stored password in the source code) HTH, Jens Suessmeyer.All I have are the old
Job = SeBatchLogonRight Log on as a Service = SeServiceLogonRight Replace a Process Level Token = SeAssignPrimaryTokenPrivilege 4. Tried removing and re-adding domain\sql as the Startup Service Account. One interesting point is that if we just install MSDE version 8.00.2039 WITHOUT sharepoint, xp_cmdshell works fine. But if MSDE version 8.00.2039 is installed as part of sharepoint, xp_cmdshell does not work at all. I suspect Sharepoint is doing something to the SQL instance, but I don't know what. How do we get xp_cmdshell to work on our Sharepoint MSDE db?The problem is with the version of XP_CmdShell that is released with the version of MSDE that comes with Sharepoint. I deleted XP_CmdShell from the MSDE server, and replaced it with a copy from the working server, and it
not happen at all when testing with MS SQL Server 2000. Please, can somebody give me a hint about what probably goes on.hi Lazar, are you sure this is not depending on the provided values exceeding the maximum storage of the columns? DECLARE @t TABLE ( c char(5) ) INSERT INTO @t VALUES ( 'abcdefghi' )Hi, Actually, I don't do any checks for exceeding the storage space in the string columns. I thought about that, but the strange is that this error happens only sometimes. That is why I'm curious. And, as I wrote, it works all fine in MS SQL Server 2000. I thought that the server just trims the data and everything is OK. Now, I'll make sure that I pass strings with length less than the definition of the column, and I'll test again.
While the program is running I can add and select data. As soon as I exit and go back into the program the database is empty. No transactions, no deletes in the program. Any ideas?hi, are you perhaps using "User Instances" and this behavior is in the Visual Studio Ide (and you are referencing an empty database in another folder that will be copied in the application folder)? http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
Hi, I have a win98 Pc connecting to a winxp pc - trying to connect to the MSDE2000 database - the database is operating in mixed mode (allows SQL logins) rather than windows integrated. The database is working ok on winxp pc - i can use the SQL Web Data adminsitrator to verify that SQL Logins work ok - also the ODBC Adminstrator can make connections to the database. Now when i try using the ODBC Administrator to connect using the same details it fails - SQL server does not exist or access denied. Now i have disabled windows firewall to rule that out (although i had enabled port 1433- sql server listening port). Now how can i verify SQL server is listening on port 1433 for remote connections? How do i configre SQL server to listen for remote connections? Any other ideas for
from scratch, or updating existing ones, I do personally use another companion (still VB6, at the moment) application provided with our main apps.. this other application, via SQLDMO (but you can use whatever access method of your choice like ADO, Ado.Net, ..) will create/update the database(s) from a reserved defintion file described like: [Database] Name=xxx DataName=logical_name|physical_name DataNameN=logical_name|physical_name ; for additional .Ndf files LogTo=logical_name|physical_name LogToN=logical_name|physical_name ; for additional .Ldf files other key to specify additional settings each database is described in it's structure by another reserved file which list all actions, .sql DDL files, BCP to be executed, like <BOF> SQL=\Tables\MasterTables.sql
I am not so familiar with sql server. I would like to know how to purge database? How to truncate database. Do we need to do some database maintenance if the databse grows to 33gb? thanks!Ah, SQL Server manages the space allocated to a database based on the amount of data you're storing and the size of the log file(s). When you delete data, the space is reclaimed for use but not deleted unless you request "autoshrink" in which case the pages are discarded. The log must also be managed separately with periodic backups and truncations. I suggest you check out "BACKUP DATABASE" in books online for more information.
Is it possible to set DATADIR=\\\? When I try it, set-up seems to fail. Also, if I were to create a new database on a default instance of MSDE, I am not able to pick mapped network directories. Appreciate any insight. Sha.Sorry, it seems I clicked 'Reply' in stead of 'new post' by mistake.
Hello Is it posible to encrypt whole database in Sql 2005 Express. I need it for deployment process of my application and hiding database structure? Or if not is good solution to use third party software for this propose? MilanBThe easiest way to encrypt a whole database is to put it in an encrypted NTFS directory. That has some performance implications obviously and may or may not do what you want it to depending on who you want to hide the data from.
Hi peeps I'm running MSDE on a Win2k server, and have just installed SQL Server Web Data Administrator. Everything works, but I can't find a way to edit the actual data. I can edit columns, tables etc, but not the actual data, although I can query it. Is this right? Mucho useless if you can't edit data. So does anyone know of a free application (web or otherwise) for editing tables and data? Or how do i get SSWDA to allow me to edit my data? Danhi Dan, actually I do not think WDA has data editing features you can use Access if you like to "open" tables and edit data, or you can have a look at other tools, both free and commercial, at http://www.microsoft.com/sql/msde/partners/default.asp and/or http://www.aspfaq.com/show.asp?id=2442
an it will generate reports.for this client machine must have sql server.now my client need data and application in one cd .he want to see the reports using that cd.without sql server how can we access that database.is it possible ?hi, you can put a SQL Server/MSDE database on a CD (the like, as later explained), but SQL Server/MSDE must be present and installed on the target pc in order to acces that database and related data.. storing a database on CD support can be performed using sp_create_removable system stored procedure, but this will always produce a 3 files set database at least 1 file storing the actual database data (that can be stored on the CD support) 1 file for the transaction log (that can be stored on the CD support) 1 file for system tables that must stored
and successfully used it with an VB.Net program on my desktop. Works great. I have just begun learning ASP.Net. I created another MSDE database. But I can't get past the login problems. "Predict" is the name of the database. "GARY" is the Windows user account name. The error message is as follows: Cannot open database requested in login 'Predict'. Login fails. Login failed for user 'GARY\ASPNET'. I used the following 2 connection strings. Neither works. data source=(local)\VSdotNET;database=Predict;integrated security=true workstation id=GARY;packet size=4096;integrated security=SSPI;data source="GARY\VSDOTNET";persist security info=False;initial catalog=Predict Does anyone know what MSDE might be looking for here?hi