I have an aplication (VS2005 and Sql2005 Express), where I need to lock a table when opening one form, and then close it when completing another form.
Is there a way to do this, maybe using a Stored Procedure.? I'd prefer to be still able to access the table in read only mode if possible.
I've looked at pessimistic concurrency, but the particular part of my application is not using a dataset, being based upon SqlCommand.ExecuteScalar(), so I'm fairly sure this is not an option.
Is there a possibility to import a table from sql 2000 into sql 2005 express? I have a table exportet from sql2000 to csv, adjusted "FALSE" and "TRUE" into 0 and 1 and saved it to csv. Now I want to replace a table in the sql2005 DB by the csv. (The first row contains the header, each row contains a record.
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
I recognize that this question borders on blasphemy but I am curious as to whether there is a version of sql2005 express for the mac platform. Would like to keep a compatible local instance of 2005 on my mac notebook.
Hello, can I run the express edition on my desktop and test web site with it, but then deploy the same app to my webhost and have it use SQL2005 standard? (They don't support the express edition.) I'm new to SQL2005 and am curious. In fact, I still don't totally understand if express is installed or just a file like access. :) Thanks! Stephen Rylander
Hello, I hope I am posting this question in the right forum. I have been working on some ASP applications and using them to connect to a MySQL database. I would like to move all of the data from MySQL over to SQL2005 Express. The problem is that the express version doesn't offer an import/export tool for CSV files. I am able to export my table data from MySQL to a CSV file, how can I import this data into my SQL2005 Ex database? Thanks.
I am currently using MSDE as the Db for a website and using (cheating?) enterprise manager i have created and scheduled a backup job that the agent runs.
Now I am wanting to upgrade the Database to SQL2005 Express but I want to have a similar scheduled backup job. From the reading of the OLBs it appears I have to manually create a script but I don't know how to schedule the running of the script.
So my questions are: 1. How do you schedule the running of a back up script in SQL Express? and,
2. Is there a tool that automates the creation of a script similar to how you do it in the old Enterprise manager?
We are coming out of the dark ages with our app using SQL 7 and, following the excellent advice of the folks here on SQLTeam, installing SQL 2005 Express on our new webserver.
Not being terribly fluent in all things SQL, I was wondering if anybody could provide input on the best practices for getting SQL 2005 Express going on the new server.
So far I've:
- Installed SQL 2005 Express - Downloaded and "installed" the SSEUtil for CMD line instructions - Downloaded and installed the graphical management interface (very nice, makes me feel more comfortable - like SQL 7 console!) - Copied backup files (made using SQL backup maintenance) to the new server
Should I simply create an empty db of the same name on the 2005 server and then restore the 7 data? Or ????
I searched briefly for previous posts of this nature and didn't find too much info so I hope I'm not duplicating effort here...
Thanks in advance for any advice!
Mmmmmkay. Yeah, did you get the memo about the TPS reports?
Ive got a SQl2005 Express server which doesnt appear on the network as an sql box. Im able to ping the server without any issues but when I try to attach to the server by using Connect option on the SMSE installed on my machine, I see my other sql 2005 express boxes and my sql2000 box but I cannot see my new SQL2005 Express box.
I'm trying to install sql express sp2 2005 in my laptop but i can't, give me this error
Machine : W7J Product : Microsoft SQL Server 2005 Express Edition Product Version : 9.2.3042.00 Install : Failed Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0015_W7J_SQL.log Last Action : InstallFinalize Error String : SQL Server Setup could not connect to the database service for server configuration. The error was: {Microsoft}{SQL Native Client}{SQL Server}Login failed for user 'sa'. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online. Error Number : 29515
I'm choosing Windows autentication mode, so i don't know it says "sa" user.
Excuse the cross post (to VBexpress fourum) but maybe someone here will have a different take?
have searched all around can't seem to see how to configure a SQL2005 developer ed so I can use vb express to develop a local front end. keep getting "the user instance login flag is not supported on this version of sql server" I have configured the .net framework via aspnet_regsql.exe. Also I configured the "Machine.config" file. but so far no luck.
Selecting sql2005 as the server thru the add new database connection in advanced area yields the error message
surely there is away.
So far still no joy,
While Scotty indicates that the connection to SQL2005 is intentionally hampered other posters indicate that the user instance problem can be solved by running the .net config tool to add the asp database to sql server, which I have done.
However this does not yet work.
I cannot get the code to spark yet.
Again my configuration cannot be any simpler. sql express sp1 and sqldeveloper on the same machine using windows security (all installed with default cofigurations)
In the absence of other ideas I may try to get sql express downloaded and going, I'm not sure what issues that will present re: what I'm trying to develop.
I'm trying to develop the skills to replace some fairly involved reporting and some user table interface that I had going in access2003, I hate to pruchace a big piece of sw when I don't know if it will remove the current roadblocks...
Appreciate the help here, but doesn't someone have a "simpler" complete answer? I.e. one that would get the developer database to accept the connection wizard? (user instance issue)
Error: The Script returned a failure result. Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out. Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
Hi. I need to move database schema (tables definiton with relations without data) form SQL Express to SQL 2005. Is there any easy way to do it? I'll be grateful for your help. Pawel
Hi,all: This problem almost drives me crazy, hope I can get some hints from you guyz!!! Ok, here is the situation: I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating. It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!! Thanx in advance!
hi, i have an application that updates some records in sql tables, and i want to do a web application that updates records in the some database-table(sql) so, my question is how can i lock the row or table so i dont have concurrency problems.tnx in advance.
Hello Friends, I am having a VB application running for the SQL SERVER DB. The VB application is installed on the multiple of PCs in the network. Now when I am trying to fetch the same from all the different PCs simultaneously, its amazingly fast. But the issue comes when I am trying to update the same table (but different rows) from the different PCs simultaneously. The time taken is directly proportaional to the number of users. I am not getting what could be the problem? Can any one suggest me the approch? Is it some related to table / row / page locking? As all the connections are trying to update on the same table. I checked the isolation level. Its default, "READ COMMITTED". Kindly suggest...
Hi,If I run an insert statement from the query analyzer and then try toopen the table from enterprise manager then it takes long time to openthe table. But this problem dissapears when i put the statement insideBegin/End Transaction statement.Any idea why this is happening?Thank in advance.Taw.
Which lock type or isolation level should I use to be sure that no onewill read or write or do anything with the table I'm using?Code block should look something like this:lock tableread value from tablechange value to new_valueupdate table set value = new_valuerelease lockWhile I'm changing the value absolutly no one should be able to readfrom the table.
I recently come to problem. I want to deploy my web site, which uses SQL2005EE extensively, so I began searching for host, SHARED HOSTING solution. I haven't find one offering SQL2005EE on .NET2.0 final frameworkI have been told that there isn't ONE host which would offer SQL2005EE on Final .NET2.0 shared hosting, that it is illegal! Only if the shared host provided is free, which is insufficient in my needs (free hosts have low trafic/disk space etc).Is it true? If I pay for hosting, I can't have SQL2005EE as an option in their hosting plan? Just SQL2005 full as an option? This way I need to pick up some expensive shared hosting plan?How you ppl solved this problem?
I have a problem to connect to SQL2005 Express Database with VC++ as regular user, not window system administrator.(If i login as window system admin, everything's working fine.)
A. I tried to use SQL2005 Express Server authentication, the error msg is: Code= 80004005 €œunspecified error€? Cannot open database ***, login failed.
1. I am using Server authentication with username and password. 2. Gave all the permission of Program FilesMicrosoft SQL ServerMSSQL.1 and Program FilesMicrosoft SQL ServerMSSQL.2 to this regular window user. 3. Connection String is: Provider=SQLNCLI;Initial Catalog=***; User ID=***;Password=***;Data Source=***;Persist Security Info=False;Integrated Security=SSPI;
B. I tried to use Window authentication by adding User Instance, but the error is: Code= 80004005 €œunspecified error€? MS Native Client Description = Invalid connection string attribute.
Connection String is: Provider=SQLNCLI;Initial Catalog=***; Data Source=***;Persist Security Info=False;User Instance=True;Integrated Security=SSPI;AttachDBFileName=directory***.mdf;
I'm trying to migrate a MSDE based application into a SQL2005 Express SP2 DB Engine on Windows Vista Business but I'm facing several errors that are blocking me. Since the installation of SQL2005 Expr. must be done in a silent mode (the end user shouldn't set anything), I'm installing it using this syntax:
The installation proceeds without problems but when my program tries to create the database, a message error appears:
CREATE DATABASE denied on database 'master' (I translated directly from italian). What I would like is to install SQL 2005 with "Windows Authentication" access instead of "SQL Authentication".
If I install SQL2005 manually by setting a named instance (CSERV), Windows Authentication mode, activating the option "set the local account as SQL administrative account" (translated from italian and suggested with Vista) I get the following error (read from the LOG):
CREATE FILE encountered operating system error 5 (access denied) while attempting to open or create the physical file 'C:Program FilesMicrosoft SQL ServerCSERVDataCGIDBDat.mdf'
CSERVData is the folder that the application creates and where the "mdf" and "ldf" files are stored.
So in the first case it seems that SQL2005 don't recognize the local account as administrator even if the user has administrative privileges (at least for Vista), in the second case is Vista that is blocking the action.
How can I see which table is locked up by some particular process? I know that I can view paricular spid from 'current activity'. But is there any way I can see which table is the center of problem? I really appreciate your help..
Hi, I am working on a project which need to produce a sequential certificate number, Everytime I need a new certificate number, I need to find out what is the max number in the database and then the new certificate number just max+1. But how can I block another transaction to check what is max certi. number while this transaction is in the middle of writing the new certificate number(max+1) into database . Does ADLockOptimistic work in this case? Here is the code: My database sql 2000.
cmdTemp.CommandText="Select max(certificateNumber) from product_table where certificateNumber<> 8888888" set cert_info=Server.CreateObject("ADODB.RecordSet") cert_info.Open cmdTemp, , AdOpenKeySet, adLockOptimistic If Not cert_info.EOF then
How can i give a table wise exclusive lock in MSSQL Server ?
I got the description but, How can I apply this ? The sql : LOCK TABLE <tablename> IN EXCLUSIVE MODE is not working.
Is there any query/method to do this ? Please help ...
thanks
About Exclusive locks -------------------- Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.
Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock. --------------------
I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?
