Am I imagining it or did I read somewhere once about an tool/command/method that allows the administrator/dba to access the server even if no-one else can - probably only in certain scenarios.
I think it had a use for a situation where all the connections are used up and no further connection pooling can kick in for a while. This tool enabled the dba to still get to the server and I presume still be able to run stuff like sp_who and kill.
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?
Hello, I am trying to configure a Script Component as a data source. Although this should be a simple exercise, I am running into a problem.
My control flow contains a Foreach Loop with a file iterator. The Directory Expression of the Foreach Loop Editor is supplied by an expression mapped to a package level variable called inputdirectory. The FileNameRetrieval Expression is mapped to a package scoped variable called filename.
My data flow is encapsulated by the Foreach Loop, and contains a Script Component as Source for the Data Flow Source, and a Flat File for the Data Flow Destination. The contents of the Script Designer are as follows:
Code Block Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.IO Public Class ScriptMain
Inherits UserComponent Dim thisFileDate As Date Dim thisFileName As String Dim thisFilePath As String Public Overrides Sub CreateNewOutputRows()
thisFileName = ReadOnlyVariables("filename").Value.ToString() thisFilePath = ReadOnlyVariables("inputdirectory").Value.ToString() thisFileDate = File.GetCreationTime(thisFilePath & "" & thisFileName) FileSpecBuffer.AddRow() FileSpecBuffer.FileName = thisFileName FileSpecBuffer.FullPath = thisFilePath & "" & thisFileName FileSpecBuffer.CreateDate = thisFileDate End Sub End Class
When I debug the package, I get the following error:
The collection of variables locked for read access is not available at this point.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadOnlyVariables()
at ScriptComponent_67311120e6eb4162a3ea1f70847f04de.ScriptMain.CreateNewOutputRows()
at ScriptComponent_67311120e6eb4162a3ea1f70847f04de.UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
My googlefu fails me at reconciling this. I have read various posts about changing the line ReadOnlyVariables("filename").Value to ReadWriteVariables("filename").Value, and handling the buffer assignment in PostExecute. The problem is that all examples shown are either for Script Component as Destination or Script Component as Transformation. I have tried playing with the Custom Properties in the Script Commponent set the ReadOnlyVariables and ReadWriteVariables, using a PreExecute method, a PostExecute method, all with different errors returning. I'm at a loss here. Could anybody provide me with a simple working example so that I can correctly populate my output buffer in the context of Script Component as Source?
I fully understand that I could just run a Script Task Using System.IO.Directory, and System.IO.File, but I really want to make this package work in the manner I've described. Any help would be appreciated.
Dear Friends, please suggest me the best book to gain sound knowledge in DBA concepts as well as writing in sub queries, complex queries as well as inner queries.
My sql server just dissalowed every connection attempt made. All the allowed users were denied permission, including the computer's admins. I changed the permissions and tryed everything I could think of but it wont let me back in. Can someone help ASAP?
Hi everyone, I hope someone can help me here! A friend of mine set up a Windows 2003 Server for me in my home with Sql Server 2005. I'm developing a website for prospective employers to see the kinds of things I can program since I have no field-related work experience. I have a cool site up, but I can't showcase the any features that use sql server (which obviously, are the kinds of things employers want to see) because the login and password my friend remembers produces this error:Cannot connect to server. Additional information: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 doesn't allow remote connections,(provider: Named Pipes Provider, error: 40-could not open a connection to sql server)(Microsoft sql server, Error:2)My friend doesn't know how to fix this. Is there a way to create another login with admin rights in the command prompt so that I can login & get my database functionality going? Thanks in advance,Monster
We have a project developed on asp.net 3.5 with SQL Server 2008 R2 as back end database. One of our client is facing issue of system performance in the afternoon time only. Mostly the tables are getting locked for unlimited time and system gets very slow. Need a professional SQL Server DBA to identify the root cause.
Hi-I've been trying to install SS2K Hot Fix # 818095 for one of my clientsand it keeps terminating - the Log for the install contains an errormessage that reads "...dbmslpcn.dll is WRITE LOCKED".I stopped all but a few of the services on the box, but still can'tseem to get it installed.Any suggesions??Thanks in advance for any help on this issue, it's been a royal PITA!Pete
We are running a shopping mall in Korea and got a database including a table of 4 million product prices, which is to be updated hourly basis. Updating 4million records requires at least 10 minutes to complete. During the update, our shopping mall exposed to customers does not respond quickly in fact very very slowly and we investigated and found out that many tables of SQL database during the update were being locked. As you know, site speed is top priority. We studied and found out that there are two ways to avoid having locked tables during update, those are "read uncommitted" and "snapshot" using the following lines.
set transaction Isolation level read uncommitted set transaction Isolation level snapshot
We tried numerous times the above two lines and still find our tables being locked during update and our customers are being disappointed.
My questions:
1. Is it possible at all in view of "the state of the art" to avoid having locked tables during update of 4 million records ?
2. if it is possible, would you please teach me like I am the beginner of database studies?
For your information, we are using 2005sql (64bit) in Windows 2003 (64bit).
I receive an error message in event log when i try to connect to the Database Server using ODBC on a client machine. The database server is running on Windows 2003 Server Standard Edition and the client machine is Windows XP Professional. Following is the error message from the event log:
2147467259 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.
What causes the error to occur and how to resolve it?Appreciate for your assistence.
I am using the desktop sqlexpress 2005 and I was experimenting with the account setting and I accidentally disabled my access. I am using windows authentication and there is no password, just the windows login name.
How can I reactivate the login for this connection. Any help is greatly appreciated.
I am writing in regard to this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52798 Which I found while searching for a best-practice way to implement record locking in my application. In the above referenced post the user asks “How can I tell if a row is locked?�, and the reason for asking is that the user wants to implement logic similar to this:
Select a row for editing. If the row has been locked by a different user, display the information as read only. Otherwise, lock the row and allow the user to edit (allow others to read).
My questions are exactly the same as the original poster of the above thread. I was surprised to see that the general response to the post was the user was taking a wrong approach and that sql server is unable to work as the user requested. I think the approach is both correct and desirable (I used it extensively as a foxpro programmer) and I would like further confirmation that sql server does not support it.
One of the things I don’t understand is the interaction between transactions and locks. Is it not possible to lock a row without initiating a transaction? Bottom line, my goal is this:
Select a row for editing. If the row has been locked by a different user, display the information as read only. Otherwise, lock the row and allow the user to edit (allow others to read).
Hi to all, I need help about access to mdf file. I don't know when, but sometime my application, build with vbnet, return an error on mdf database access, that is access denied, also winxp return on the mdf file the message "process cannot access the file because it is being used by another process" if I try to delete the mdf file. I can delete the file only if I stop the procss sqlservr.exe from taskmanager. I don't use instruction to open and close database, I use dataset, datatable and sql build in datatable. Someone can say me why sqlservr.exe don't unlock the mdf file when I close my application? Thanks..
When I am deleting records from a table, I am not able to select or insert records from the table. This particular table needs to be used by more users and very frequently used.
I am having an interesting problem that has got me completely baffled. I restored a database from the .bak file of another database on my server to use as a test DB for an App. upgrade before upgrading the live side. The problem I have run into is that the application will not allow login into the DB unless the Trusted Connection user has dbo privs. The DB is not set for dbo use only acoording to sp_dboption. It is not this way on the live side and tech support for the app. and I both think it has to strictly do with something in the SQL permissions. Any Ideas on what could have happened? Is the restored DB hanging onto something from the original DB that could be causing this? The connection works great on the live side on the older version, and I have seen it work in the newer version as well. Any takers?--Dallas
I have a problem that i can't log on sql server as the account is lock out. I don't know why it is locked out as this is the first time i log on. Anybody has this experience? Thank you very much!
I just brought SQL Server 2005 up on Windows Server 2003 a few days ago. Everything was going great up until a few minutes ago. I tried to connect using the Management Console and get the message
Login failed for user 'sa' because the account is currently locked out. Your administrator can unlock it.
My 'sa' account is locked out and the BUILTINAdministrators account does not have the sysadmin role. I removed the sysadmin role from BUILTINAdministrators for security reasons and before I could create a new account with the sysadmin role I fat fingered the sa password and locked it out. How can I get myself out of this mess.
HelloI have a stand alone Java application that uses SQL Server 2000 on thesame machine. Sometimes, when we run the applications and follow acertain steps, the SQL server locks some of the table. At that point oftime, if we try to query Select * on those table, in Query Analyzer, itjust keep spinning and doesn't return anything. That tells me that thetables are locked. If I close the application then it frees up theconnection and those tables are accessible again.My question is how do I find out how and what query/update causes thetables to be locked. Is there a tool (some kind of a profiler) that cantell me what was the last insert/update/delete quesry was run before itgot locked?Please help me.
Hi Champs, Is there a way to recover from a account lockout in SQL2005? I cannot connect to "Database Engine" in "SQL Server Management Studio", error: login failed...18456. Access denied.
And I also cannot start the "SQL Server agent service"
I have been using SQL2000 for a number of years and the company I work for needs a new system so I decided to install SQL2005.
I installed SQL2005 without any problems and set a complex password for SA. I tried to log on and had to make several atempts as I remmebered the password after the SA account got locked out with the following message:
Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486).
The problem I have is I cannot log onto SQL at all as it is not logging on with windows authentication either!
How do I unlock the SA account or do I have to reinstall SQL?
I am a beginner working on a webclass project using VBLive as a model. Today I have lost any ability to enter Enterprise Manager for SQL7 although my SQL7 Server is still running and operational with the webclass application.
When I try to start the enterprise manager, i get a mmc.exe-application error Have I locked myself out somehow? How to get back in control?
Hi, I've started 2 dbcc processes using SQL Scheduler overnight. and I've got one is flagged rollback and the other is still running, they are blocked together. I've tried to kill them using kill <spid> without success. How can I delete this processes ? Shall I shutdown the server ?
I have had a search of the forum and couldn't find a solution to this one.
On a clean install of SQL Server 2000 on a W2003 server we setup our database as normal, selected appropriate priveldges for everyone and all was well.
Until an adminsitraor accidently turned builtinadministors account to DENY access. We are now locked out of EM and I can't think of a way back in.
We can't access the database via the sa account as it is set to Windows Autentication, and we can't re-register the database for the same reason.
No other user has sufficent priveldges to update the master db or change authenticaion, or even log into EM for that matter.
I think we are snookered, but any thoughts are welcomed.