}
if (ReplicationComplete != null) ReplicationComplete(this, true);
}
catch(Exception ex)
{
if (ReplicationComplete != null) ReplicationComplete(ex, false);
}
}
Then later, if I try to update, insert or delete to the database, the application will hang. I can read from it, but I cannot write. If I close the application down and open it back up without replicating, I will not get any lockups. It also will not hang up after any replications prior to the create replication. I think I am doing something wrong in the initial replication that is holding on to some connection to the DB causing it to lock up. Has anyone seen anything like this before?
I would like to ask if there will be a write and concurrency issue if i would create an online application with just one user connecting to the database, just like most open source php/mysql that can be downloaded
i.e Setup 1. I will grant a single user that will connect to a database and will be set to a config.inc.php file and then create a table users (userid, username, password) and this table will be used for the application authentication and access control.
Setup 2. or is it much better to grant users that will connect to the database and have the table users for access control of the application?
to further illustrate my query:
Setup 1 would be: ###################Table: Users user a@ipadd --> db1 --> userid mary --> myOnlineApp user a@ipadd --> db1 --> userid john --> myOnlineApp user a@ipadd --> db1 --> userid paul --> myOnlineApp
and Setup 2 would be: #####################Table: Users user mary@ipadd --> db1 --> userid mary --> myOnlineApp user john@ipadd --> db1 --> userid john --> myOnlineApp user paul@ipadd --> db1 --> userid paul --> myOnlineApp
will Setup 1 be enough if I will use SQL Server as database?
or Setup 2 is better user/database architecture?
This application will be online enrollment for a school with 16,000 students
very much appreciated for the feedbacks and suggestions
Hi,I would like to use database locking mechanism to control access to anexternal resource (like file system).What I need is1. an exclusive (write) lock conflicting with any access to theresource (both for read and write)2. non-exlusive (read) lock conflicting with writes onlyHow this could be done?I'd appreciate any reply.Vadim
Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.
I saw a video on how its possible to create a local database so that when the application is being deployed, the mdf file will be deployed with it and will contain the enter data. The instructor(Beth Messi) showed that all I need to do is to add the .mdf file using the "Rightclick Project name in solution explorer > select add > add new item > then in the dialog that opens, select SQL database and click ok. I did this but the Visual Studio kept saying: "An error occored while extablishing a connection with the server. When connecting SQL Server 2005, this failure may have been caused by the fact that under the default settings of SQL Server does not allow remote connections. (Provider: Shared memory provider, error: 40 - could not open a connection to SQL Server)"
Honestly, I don't know how to go futher with this. The SQL server am using is the professional edition and I have been able to use it through Visual Studio to create Databases. It connects alright in that senario. Please I really need help here.
Hi,I am trying to use Enterprise Manager Wizard to create a newPublication database but I got the following message.Error 2812: Could not find stored procedureMyDB.dbo.sp_MSadd_logreader_agentThere seems that some replication stored procedures.Any help?Thanks in Advance
It is possible to write an application which monitors the Microsoft SQL Server 2005 database's transaction log and captures data modified (values after modification) by transactions? Can you point me to some tutorial and API specification?
It is possible to write an application which monitors the Microsoft SQL Server 2005 database's transaction log and captures data modified (values after modification) by transactions? Can you point me to some tutorial and API specification?
I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:
SELECT request_session_id, COUNT (*) num_locks -- select * FROM sys.dm_tran_locks --where request_session_id = 77 GROUP BY request_session_id ORDER BY count (*) DESC
The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.
A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.
We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system. The query also included the Process ID of the process we needed to kill in order to get rid of the lock.
We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.
Our enterprise consists of a mix of mission-critical transaction-oriented applications. The ERP applications are accessed by 70 branch offices around the country. The network also carries file transfers, e-mail, and Web traffic. Each branch is linked to headquarters via a Frame Relay network with port speeds of 64Kbps.
We are facing problems when ERP database locks up and cause time-outs and frustrating messages such as: "User disconnected - unknown error."
I've a stored procedure that grabs from a table all of the records that have a status = 2. Once these records are grabbed the same stored procedure needs to update the status of the records to 3. Something like this:
SELECT * FROM [MyTable] WHERE Status = 2
UPDATE [MyTable] SET Status = 3 WHERE Status = 2
What I'm afraid will happen is that between the running of the select statement and the running of the update statement that a record that originally had a status of 1 will be set to 2 - which means the record was never selected by the first half of the stored procedure but had its status updated to 3 in the second half of the stored procedure.
I thought at first wrapping all of this in a transaction would fix the problem but I've done some more reading and that doesn't seem to be the case. I need the Update to run on only those records the Select query grabbed no matter what else happens in the table.
Is there a lock to address this in a way that will allow other records (those not selected) to be updated to a value of 2 without them being updated to 3 when the Update query runs?
The database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
I had application that access the database 'test'. Now i created a ETL package, in this Souce database is 'test' and destination database is 'mytest'. I have scheduled the job for every hour.
Now, i want to know that whether any locks will create on source database 'test' while ETL package is running.
Hi, I am joining a table with a view in my query to get the desired data. But when I run this query it does not produce any result, instead the execution goes on never ending finally locking the database. Surprisingly if the selected data from this view is put in a temporary table and that table is joined with the table to get the result, it works fine.
Could anybody please help me with this as creating a table every time would be slow procedure. Is there any restrictions related to views which may be I have ignored.
I want to make SQLTransaction as global and use it checking the State. But then where there are Only Select queries are going to fire, it will open transaction.
So, Does it locks database/tables when Only Select query comes in SQLTransaction.
If you have another successful way of doing this, Please suggest.
You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar.
And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile.
My below script shows error. I have following records in my TempWeekFile
Week_No WeekCnt 10 1 11 2 12 3 13 4
How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you.
We've all seen this, where it uses an individual .write statement for each column.
Code Snippet
Public Overrides Sub AWCCogent_ProcessInputRow(ByVal Row As AWCCogentBuffer)
With textWriter
Dim item As Object
If Not Row.AddressID_IsNull Then .Write(Row.AddressID) End If .Write(columnDelimiter) If Not Row.City_IsNull Then .Write(Row.City) End If
.WriteLine()
End With
End Sub
But hard coding this seems not the smartest way. Especially since in my text file, there needs to be close to 100 columns. This could be a nightmare to update down the road. But I can't seem to find the object collection to loop through, like row.items which would seem to be logical.
i am creating a model in which the control panel i am building for a web application, i can write sql statements in a textbox and the query will be done in my application. But i am have a problem creating a table. this error of "Create table permission not granted in the sql server" pops up whenever i want to execute the create table sql statements from my web page. How can i permit my sql server to allow creation of tables from my web application. Thanks
Trying to create an audit that goes to the application log. With a certain criteria, select, update, delete etc.. where like 'example'-- some criteria..is there any reference out there that could give me some examples?
I have a VB application using an SQL server database. I've created two groups in SQL Server for the database, one called APPS Users and the other called APPS Admin. So far, so good. However, when I place users into the APPS Admin group, it seems that besides getting to my application via a connection string, they also get complete access to my SQL Server. I don't want them to have total access to my SQL Server, only to the application's database.
In short, I want to limit them to nothing more than my application and its related database. In my application, these APPS Admin users will see a different group of menus than the regular users.
Since some of these APPS Admin users are also DBO's, how can I set up my group to limit them?
Hope I made my plight clear.... any help offered is most appreciated! Thank you!
I am always not sure whether to consider create another DB for Logging usage.
As my habit, i love to create 2 DBs for one apllication, for example the applaication name is HHH, i would love to create two DBs named HHH and HHHLog. For HHH, recoording any transaction , for HHHLog, it's only for log alll knids message and archive some transaction tables monthly.
After I watch a video for how to create MS SQL Replication, I configure distribution and create the publication. The problem comes from subscription. If I create a "PUSH" subscription, it works fine. However, when I create a "PULL" subscription, I got the following error. Where I should look for solution for this error.
Hi all, I have a problem is that I cannot perform the transacional replication from one server (ms-sql2k) to other server (mssqlserver).
The error show below : Log Reader Agent - Is not a valid win32 application.
I try to setup another server (mssql2k) to do the same thing, there is no error appeared. Therefore, I have doubt that the name of the computer using "-" will have problem. Or the registry of (ms-sql2k) may have problem because it had wrongly installed personal edition and re-installed the standard verions afterwards.
Hi GuysOff late, I've grown with programming that requires more than a number of tables that has foreign keys with other tables' primary keys. It takes a really cumbersome coding to retrieve the code from another table with the other table having foreign keys. My question is, how do we program VS 2005 such that it does all the retrieval of the data from the database instead of us writing the code all by ourself?Is it really good database technique to bend the normalcy rules and have one to two columns having redundant data?Can anyone tell me how to write code that retrieves the foreign key data when the data from the other table is called?Thanks
Hi all, I have a problem is that I cannot perform the transacional replication from one server (ms-sql2k) to other server (mssqlserver).
The error show below : Log Reader Agent - Is not a valid win32 application.
I try to setup another server (mssql2k) to do the same thing, there is no error appeared. Therefore, I have doubt that the name of the computer using "-" will have problem. Or the registry of (ms-sql2k) may have problem because it had wrongly installed personal edition and re-installed the standard verions afterwards.
I'm developing a scientific appliation - to forecast the spread of disease. The algorithm has been designed such that different subpopulations reside on separate computers.
The model uses the same set of data repeatedly, and only returns a small signature that reflects the appropriateness of a particular set of parameters.
The model does however change, and I'm looking for a way to roll out this model (which only contains T-SQL, CLR assemblies and service broker code).
I have considered setting up a database on each computer which has the 'data' files which are static, and a separate database which contains the algorithm which can be amended.
Could one then detach the algorithm database making the new version that can then be rolled out (by attaching it to each computer used)?
Is this the best practice? Does anyone have a suggestion as to how to do this?
