When I develop I sometimes forget and try to open the database file with the MS SQL Express Manager even though the web application I am devloping is still using it ... error lock occurrs and thats fine .... but what is the best way to unlock the files again? .... often I find myself having to restart the workstation because of this and thats sounds a bit like overdoing it .... there should be an easier way to unlock locked db files ...without having to restart the workstations.... is there ?
I have a situation where I want to copy a SQLExpress mdf file, after having read some metadata from it, but I find that an exclusive lock persists on the file for about 5 minutes after closing... the lock is held by "sqlservr.exe" This lock prevents me from copying the file. It seems that I may be able to fix this by calling sp_detach_db after closing the connection (does this seem reasonable?) but I am unsure what the database name (dbname) is that I need to pass to sp_detach_db.
To make this a little more concrete, here is the connection string I use... "Data Source=.SQLEXPRESS;AttachDbFilename=C: estdataDVDCollectionDatabase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True'" so in that case what would be the dbname?
Note that I need to do this through API (in a .NET application)
I have a database consisting of multiple files on a remote server.
Both servers have the same user (bob) which is in the administrator group.
The directory that the mdf/ndf files exist on is shared as "nas" with full rights given to the user bob.
They are both in the same workgroup.
SQL server is running as this user (not ideal I know but server and services will be locked down later)
The script I used to create the database uses the following line to enable remote database files:
-- Enable the use of network drive for DB files
DBCC TRACEON(1807)
GO
I used SSMS to detatch the database and then modified my create script by removing database sizes and adding "FOR ATTACH" at the end. (I have also tried using sp_attach_db but no use)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5120, Level 16, State 101, Line 5
Unable to open the physical file "\123.45.67.89asDBName_FG.mdf". Operating system error 5: "5(error not found)".
I know the permissions are ok as I can map a drive without being prompted.
SQL server is running as the user bob which is the same user I log onto the server as.
I am running Windows 2003 Server R2 Standard Edition on server with SQL Server 2005 SP2.
The NAS box is running SQL Server R2 Storage Edition.
One thing I noticed was that the mdf/ndf files on the NAS box are locked as if SQL is still using them, i.e. I cannot rename them, move them, alter any properties etc. I just get "Access Denied".
I've tried rebooting both servers but still no luck. I've also tried stopping SQL server then renaming the files, but again "Access Denied".
I know there has been a problem in the past detaching and attaching db files on a NAS, but the Hotfix is included in SP2, which I am using.
I hope I have included enough info but if I've missed anything, it can be provided.
(Event Log basically replicates what the error message says.)
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'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?
I have to write a program in VB to receive the read data from a RFID reader for my graduation project.The problem is I am not a computer science student so I have only general info on programming.
I created my DB in VB express but I couldn't find out how to send the read data (that will be either in XML or ASCII format) to my database...The read data will be transferred to my computer by the RFID reader's software but after that I don'T know how to transfer it to my DB.As I know I have to use commands like read.xml etc,but no idea how write the complete program.
I checked the forum and couldn't find the answer,sorry if someone already answered my question and I missed it.
1) What is the current version of SQL Server Express? 2) HOw much SQL Server Express costs (figure about 500 branch servers) 3)Are there any Tools from Microsoft to convert Access 97 directly to SQL Server express, and how much do they cost? 4)Server Hardware requirements to run SQL Server Express - disk size, memory size, security settings, pre-requisite, service needed. 5)Maximum capacity / capabilities of SQL Server Express-max # of simultaneous users,tables,rows,database size. 6)Any installation instruction for SQL Server Express.
Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database? I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors. But I have heard a lot of discussion that you can't just simply backup SQL Server data files? I'm wondering how sound the backup I've created is... Any suggestions?
Hi,I am new to SQL express and try to solve the 4GB size limitation.Is there a possibility to create a new database file every time I getto the limit?How can I do that with C#? how can I create new database file everytime it gets full?Can I be connected to two database files at the same time (the full dband the new db)?thanks in advance,oren
Hi all I have heared that there is way to use SQL SMO (SQL Management Objects), or other third party tools running on a management server to send out an email or some kind of notifications if a database is locked.
Anyone knows how to do that in SQL Managment 2005. Thanks -Sarah
What is they best way to get data from a locked Access database into SQL Server? Linked Server, DTS Package, or should I just directly access it from my App?
wierd thing - i am trying to write a program that use simple access database but 5 user can access it at the same time ,
i wont to know if the database is in use by other user before attemping to write to it ,
but i notice that DBNAME.ldb file even when exsist i can some times write to the database - so to chack if this file is exsist will not be the sulotion , some one know how to chack it?
Can multiple instances of SQL 2005 Express attach to the same database files on a network share? I have seen this done before with MSDE where the database files are stored on the server, but instead of having a SQL server running on the network and then connecting to it, only the database files exist on the network share and the users connect through MSDE running on the local machine. Is this possible with SQL2005Express? I do not have the ability to share an SQL instance from one workstation to another nor do I have the ability to install an instance on the corporate server. Is it as simple as creating the database and storing the files on the share then attaching the database to the SQL Instance on each workstation?
An error has occurred 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 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: An error has occurred 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error: here is my web.config file:<?xml version="1.0"?><!-- Note: As an alternative to hand editing this file you can use the web admin tool to configure settings for your application. Use the Website->Asp.Net Configuration option in Visual Studio. A full list of settings and comments can be found in machine.config.comments usually located in WindowsMicrosoft.NetFrameworkv2.xConfig --><configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <appSettings><add key="EmailFrom" value="webmaster@reaganpower.com"/> <add key="EmailSubject" value="File Ready for Download!"/><add key="SmtpServer" value=""/> <add key="MailUser" value=""/><add key="MailPassword" value=""/> <add key="MailPort" value="25"/><add key="EmailFormatSelected" value="Text"/> <add key="PageTitle" value="Send It Now!"/><add key="ShareLocalFolderPath" value="H:MIS DepartmentIntranetSendItNowFileStorage"/> <add key="httpDownloadPath" value="http://misfs/SendItNow/ContentFiles/"/> <!-- <add key="CurrentTheme" value="CleanBlue" />--> <add key="CurrentTheme" value="CleanOrange"/></appSettings> <connectionStrings> <add name="ConnectionString" connectionString="Data Source=(local)SqlExpress;AttachDbFilename=|DataDirectory|FileShareDB.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/> </connectionStrings><system.web><!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. Visual Basic options: Set strict="true" to disallow all data type conversions where data loss can occur. Set explicit="true" to force declaration of all variables.--> <identity impersonate="false"/> <roleManager enabled="true"/><compilation debug="true" strict="false" explicit="true"> <assemblies><add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies></compilation> <pages><namespaces> <clear/><add namespace="System"/> <add namespace="System.Collections"/><add namespace="System.Collections.Specialized"/> <add namespace="System.Configuration"/><add namespace="System.Text"/> <add namespace="System.Text.RegularExpressions"/><add namespace="System.Web"/> <add namespace="System.Web.Caching"/><add namespace="System.Web.SessionState"/> <add namespace="System.Web.Security"/><add namespace="System.Web.Profile"/> <add namespace="System.Web.UI"/><add namespace="System.Web.UI.WebControls"/> <add namespace="System.Web.UI.WebControls.WebParts"/><add namespace="System.Web.UI.HtmlControls"/> </namespaces></pages> <!-- The <authentication> section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. --> <authentication mode="Forms"/> <!-- The <customErrors> section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm"> <error statusCode="403" redirect="NoAccess.htm" /> <error statusCode="404" redirect="FileNotFound.htm" /> </customErrors>--> </system.web> </configuration>
Really confused ... I created 2 tables in a VB Express program (which went well). Problem is that I can't see/find those tables with MSME. I don't really understand how to connect the MSME to where the tables are. I created the tables under My DocumentsData and they are DBapp.mdf + DBapp_log.LDF and the tables are Items and Preferences. The VB application can "see" the tables OK.
Do I need to move the tables to C:Program FilesMicrosoft SQL ServerMSQL.1MSQLDATA (like the Adventureworks installation which I haven't done) or what?
All-- Please help. I have an issue at a shared hosting site. Basically, my LDF and MDF files are becomming locked. (1) How can one unlock an LDF or MDF file? (2) Is there a way to run a pseudo-iisreset that applies just to a particular site? (3) Is there a way to force a detach from an MDF file? (4) Is there a way to force a DotNet application to shut-down and release all resources? (5) Will setting the database as AutoClose=True help and what are the implications of doing so? Here is the situation. I have a site at a shared hosting company. I upload my site via FTP. To start, I just upload the MDF file one time. I do not upload the LDF file as that gets created automatically. If I have a schema change, then I need to replace the MDF, pushing up a new version from the local box to the server box. However, what happens is that on the server box the MDF and the LDF become locked. They cannot be renamed, replaced, deleted, or moved. After some kind of timeout (I don't know how long) the files are released and I can delete the server's LDF file and replace the server's MDF file and all is well. The problem is that I do not know a way to remove the lock and/or force a timeout so the files are released without having to wait. What do you think? Please advise. Thank you. --Mark Kamoski
I Everytimes I run a software I wrote on the server I get the following error:
Process ID %d attempting to unlock unowned resource %.*ls.
Does anybody experienced that type of problems before?
have a copy of the database and of the sfotwqre locally on my win 98 machine where it runs without problem. Once I try on the server it crashes miserably.
i have a SP that updates a table for about 2hours. My question is, is it possible to unlock this table so that other SPs or SSIS packages can update/innsert into the same table i am updating.
I know that if you want to unlock all records should issue a rollback or commit statement. But what about to unlock one records from a list of records and keep the other records locked. I used the following statement to make the lock for the records:-
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE begin transaction select item_no,itemdesc from ims.item [rowlock] with (xlock) where item_no = '0100100001'
begin transaction select item_no,itemdesc from ims.item [rowlock] with (xlock) where item_no = '0100100002'
I have read some post about it in the forum, but i still have 0xC001404D error....
In a custom transformation script, i get a column (just one row in this columns) whit an integer, and i need to assign this value to a variable. My code is as following:
Public Class ScriptMain Inherits UserComponent
Dim nfilas As Int32
Public Overrides Sub Entrada0_ProcessInputRow(ByVal Row As Entrada0Buffer)
As you can see i have been doing several king of solutions... In the next step i write this cariable to a text file... do i have to lock for read the variable also??
I had some process in which ... I need to do some transaction control within dynamic SQL like as follows
Begin tran
insert.......
if @@error <>0
rollback else
commit
Now, when i executed above dynamic SQL for some cases I think there is a some severe error at insert statement and thus resulted in abnormal termination of Dynamic SQL i.e. neither rollback nor commit is executed.
Now certain row level locks are still present on object involved in insert. Now my question is how should i Unlock those locked resources.
I have an Access97 front end using ODBC to communicate with anSQLServer 7.0 back end on a different machine. Most of the work I doin the front end uses forms bound to linked tables that reside on theback end. In one instance though I have to create some new recordsprogrammatically and I use code in a procedure in a general module inthe front end that looks like this:Set newrec = db.OpenRecordset("SELECT * FROM [workshop assignments]",dbOpenDynaset, dbAppendOnly + dbSeeChanges)newrec.AddNew ' assign the workshopnewrec![ComboWS] = WSKeynewrec![Participant] = IndividualIDnewrec![Assigned] = Truenewrec.Updatenewrec.CloseThe problem occurs later when I am in a form that views those recordsthat were just added. For some reason SQLServer still has thoserecords locked, and I am not allowed to make any changes to them. Ican't even just delete them. In fact, even when I exit out of all myforms, go straight into the table window, straight to the table itself(but still in the front end), I cannot delete or change those recordsdirectly. I've tried taking down the front end and bringing it backup. I've tried restarting the whole computer where the front endresides. I've also tried restarting the SQL Server. I still can'tchange those records. Oddly enough, I can change the records withinSQL Server itself. The Access97 front end will see the new values, butstill is unable to do anything with them. How can I fix this problem?Thank you for any help you can give me,Rebecca Jaxon
Hi... When i try to login(other than sa for e.g. "test1" user) in mssql server the account be locked. By using sa account i try to unlock the test1 user by using the following command
Alter login [test1] with PASSWORD = 'pass123' UNLOCK
when i execute above command test1 user unlocked successfully.
But our requirement is, with out setting the PASSWORD property i want to unlok the test1 user
Hi all, I am using the sqlExpress database, and now I am looking for a hosting plan. Problem is that most of the .NET average hosting plans are expensive and gives limited space of database. I have 2 questions: 1. Is there any way to reduce size of data base? 2. why there is 2 files for database? 1 is .MDF extension and the other is .LDF. LDF is a lof file? if yes, how acn I remove it from the project. Thanks.
I am developing using visual web dev and sql server express. When this project goes live its going on another server running sql server(full version) this is my first asp.net app and my question is...Do I just move the mdf file from the dev machine running sql server express to the new server running sql server full version to retain all the tables and data?I know its been asked before but I want to be sure if this is the proper procedure.Thanks in advance!
I am to take a web app tomorrow to work to install it on a a web server. I just wanted to be sure which files in the web app Sql Server Express needs to operate. Is it the *.mdf file only or also the *_log.ldf and the aspnetdb.mdf files also.Thanks.
Are there any specific Help files to download for SQL Server Management Studio Express. I thought I saw a link the other day, but I am not sure anymore... Or is it part of the Book online for SQL Server Management Express? I can't find reference to Studio in there...
I just downloaded SQL 2005 and SQL Express. I'm not seeing SQL Express in my Program files yet when I open SQL server configuration manager it AND SQL Server 2005 Services it says it's running. SQL Server Browser is NOT.
I have installation program which can Install MSDE 2000 base on the user input.
If user does not want to install it, I ill install SQL-DMO redistributable files, so during installation User can pick server location where database will be installed.
Now I want to upgrade to SQL 2005 Express and I want to perform the same steps. Where I can find SQL-DMO redistribution files for SQL 2005 Express ?
I am new to VC# and SQL Express. I am currently trying to find a way to store user selected files into SQL Express. I am using a column with the varbinary(MAX) data type. My current thought is to give the user a open file dialog box, and let them select the file they want to upload. On closing this dialog box, I convert the file selected into a file stream and insert into SQL Express.
Is this method a good one? If anyone else can give me pointers or hints to better methods, please do!
