Locking Occurs When Trying To Insert - HELP
Nov 11, 2001
I am trying to insert data using the select * command from one table into another, but query analyzer just seems to run and run (over one hour before I stopped it). The current activity shows a huge number of extent locks. The table itself has approx 90,000 rows. What is causing this insert not to finish, and how do I sort it?
I am a little confused over LE thresholds etc. At present the server has the following settings:
LE threshold max - 10000
LE threshold min - 20
LE threshold % - 0
locks - 60000
Will changing these values help??
Derek
View 2 Replies
ADVERTISEMENT
Apr 21, 2015
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
View 2 Replies
View Related
Sep 17, 1998
SQL Server 6.5 on-line help states that IRL is only effective if the table has a unique clustered index defined on the table. IF this true and if so does anyone know why.
thanks,
Stu.
View 1 Replies
View Related
Jun 11, 1999
Does anyone know how to stop SQL server from applying exclusive table locks on mass Insert statements. I need to allow concurrent inserts into the same table using INSERT ... SELECT * FROM ...
SQL Server applies exclusive table locks that prevent this. I've tried setting LE as high as it goes but with no success. Microsoft suggest using single row inserts but this will be far too slow!
Any help would be much appreciated.
Jim Plant
View 1 Replies
View Related
Jun 11, 1999
Does anyone know how to stop SQL server from applying exclusive table locks on mass Insert statements. I need to allow concurrent inserts into the same table using INSERT ... SELECT * FROM ...
SQL Server applies exclusive table locks that prevent this. I've tried setting LE as high as it goes but with no success. Microsoft suggest using single row inserts but this will be far too slow!
Any help would be much appreciated.
Jim Plant
View 1 Replies
View Related
Apr 4, 2007
Hi,
I want to insert a record in a table having an identity column as primary key. I want to lock the table while inserting. so that no one should be able to insert, select, update, delete from the table. and once my insert is over, then will release the lock.
Can I have the code for the same. M using SQL SERVER 2005.
Thanks,
Rahul Jha
View 8 Replies
View Related
Sep 10, 2014
There are 2 tables which need to have data inserted into them for auditing purposes. The number of inserts per minute seems be at least 50-100. How to reduce locks during inserts.
There are 2 tables
Table1
ID - Surrogate Key/identity Column
SomeColumn1
SomeColumn2
SomeColumn3
SomeColumn4_timestamp
clustered index on ID column
Table2
ID Column ..... there's a call to get id from SCOPE_IDENTITY()
SomeColumn1
SomeColumn2
clustered index on ID column
NC idx on SomeColum1
NC idx on SomeColum2
A Sproc has the following code:
I changed the names to protect the innocent
CREATE PROCEDURE [dbo].[logging_sp]
@Audit BIGINT,
@varT1_1 NVARCHAR (50),
@varT1_2 NVARCHAR (64),
@varT1_3 INT,
@VarTime DATETIME,
@varT2_1 NVARCHAR (50),
@varT2_2 NVARCHAR (1024),
[code]....
View 5 Replies
View Related
Sep 18, 2015
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
View 5 Replies
View Related
Jun 19, 2015
I have an SSIS package doing a bulk insert from a file. Then later on I'm trying to delete that file (in a file delete task), but I'm getting an error:[File System Task] Error: An error occurred with the following error message: "The process cannot access the file 'xyz' because it is being used by another process.".I'm wondering if there isn't some way to 'tweak' the bulk insert syntax so that it doesn't lock the file?
View 5 Replies
View Related
Feb 8, 2008
Hi,
I have implemented Remus Resanu's implementation from the Recycling Conversations article and I am experiencing locking issue when I try to insert new conversation handles to the SessionConversations table. I have copied the code in the article exactly including the activation procedure. Any ideas why I may be locking. I am thinking it is related to the HOLDLOCK hint on the table.
The sepcific line where I see locking is directly from the article:
INSERT INTO [SessionConversations] (SPID, FromService, ToService, OnContract, Handle) VALUES (...etc)
Thanks
View 6 Replies
View Related
Jun 12, 2005
Code:
ID User BirthYear Where
1 Tom 1975 USA
2 Alex 1975 UK
3 Ben 1980 UK
4 Tom 1976 UK
5 Tom 1977 Unknown
6 Cindy 1985 USA
7 Tom 1975 UK
I want to print it like below
Tom-USA-1975
Alex-UK-1975
Ben-UK-1980
Tom-UK-1976
Tom-Unknown-1977
Cindy-USA-1985
I dont want
Code:
7 Tom 1975 UK
becasuse there is another record that User field is Tom and BirthDate is 1975
I tried "group by User,year" , it didn't work.
View 8 Replies
View Related
Mar 28, 2008
Hi all:
We are using a simple clr function in sql server, which splits input strings by a given delimiter. The code is in €śclr_sql.cs€?. And I wrote a simple stored proc in SQL Server 2005 which calls this function(storedproc.slq). Then I used a test application, which creates 80 threads to call this stored proc continuously. The client code is in client.cs. The TPS can reach to 2000+ in my test. Unfortunately, exception occurs sometimes (about 0.01%). Most of the exceptions are:
System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
And there are a few others:
System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
I wrote a dummy T-Sql stored proc, which returns all the rows from an existing table (60 rows, the same dataset as the test above) directly. It€™s much faster than the clr one, and no exception occurs. And I also wrote a T-Sql stored proc which doing the same task as the clr one and is slower. It doesn€™t fail, neither.
To summarize my problem: exception occurs when using clr sql, but it vanish when using T-SQL. But we need CLR SQL because of the performance is better.
I€™m looking forward to hear from you. Thanks in advanceJ
/* clr sql.cs */
public class KspSplitString
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable InitMethod(String input, char delimiter)
{
if (null == input)
return new string[] { };
return input.Split(new char[] { delimiter });
}
public static void FillRow(Object obj, out SqlString Term)
{
Term = (obj as string).Trim();
}
}
/* storedproc.sql */
ALTER proc [dbo].[spSplitString2] (@keywords nvarchar(max), @delimiter nchar)
as
set nocount on
select * from dbo.fnKspSplitString(@keywords, @delimiter)
/* client.cs */
SqlConnection conn;
public override void Setup()
{
conn = new SqlConnection(connectionString);
conn.Open();
}
public override void Cleanup()
{
conn.Close();
}
public override void Run()
{
try
{
using (SqlCommand command = new SqlCommand(storedproc, conn))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = sqlCommandTimeout;
SqlParameter sqlParameter = command.Parameters.Add("@Keywords", SqlDbType.NVarChar);
sqlParameter.Value = keywords;
sqlParameter = command.Parameters.Add("@Delimiter", SqlDbType.NChar);
sqlParameter.Value = delimiter;
using (SqlDataReader reader = command.ExecuteReader())
{
tblResult = new DataTable("RESULT");
tblResult.Load(reader);
}
}
}
catch (Exception ex)
{
Cleanup();
Setup();
}
}
View 8 Replies
View Related
Nov 21, 2006
Hi,
How do find out how many times a character occurs in a field thru an expression? Like the occurs() function in FoxPro
Thanks
View 1 Replies
View Related
Oct 11, 2001
Hi,
I am trying to set up the SQL Server Agent so that when ever a error occurs
the DBA is notified.I created a operator name and when i tested to make sure
it was working it was giving me an error.The error is
Error 22022: SQLServerAgent mail session is not working; check the mail
profile and/or the SQLServerAgent service startup account in the SQLServerAgent properties dialog.
I am new to SQLServer and would like to know how to get this working.
Any help will be appreciated.
Thanks,
Rann
View 1 Replies
View Related
Jun 26, 2006
Hi,
I am facing this problem with SQL Server 2000 as well as with SQL Server 2005. I want to get notified when a deadlock occurs. This is what I have done.
I have setup an SQL Server Agent alert for the error number 1205. The alert is enabled and in response to the alert I am invoking a job and also tried email and net send etc.
Now I simulated deadlock situation by using two connections and received the 1205 error. However when I go back to the alert and see it does not invoke the job nor send message nor email. I find the number of occurrances of the alert as 0 which means that the alert never occurred. Why am I not able to trap the 1205 error message and get a notification?
I understand that I can enable trace flag 1204 and trace flag 3605 to get the deadlock event and the complete description logged to the sql server error log. But my question is why am I not able to use the 1205 event in the sql server agent alert. Even if I enable 1204 and 3605 I am only able to get the information of the deadlock in the error log but the alert is not getting triggered.
I am able to get all other alerts except the alert for 1205 and in general for any severity 13 alerts they are not getting triggered.
Any help is appreciated. I got this problem with sql server 2000 sp4 and sql server 2005 sp1.
Regards,
Ravi
View 2 Replies
View Related
Mar 8, 2007
Hi all,
What kind of notification / alert can I get when a failover occurs?
I need to refresh SqlDependency after a failover.
Thanks,
Avi
View 3 Replies
View Related
Jul 13, 2004
I need to know how to figure out the number of times a character occurs within a string, in SQL.
For example, "happy" concerning "p" would be evaluated to 2.
View 2 Replies
View Related
Jul 23, 2005
Hi,I have a peculiar problem (or maybe not). I have SQL clients installedon XP boxes. These XP boxes are a member of let us say "XYZ" domain.SQL Server is installed on one of the boxes having Windows 2000 Serverin the same network. The installation has been done on local domainaccount. Since SQl Server has been installed with Windowsauthentication, what happens if the XYZ domain fails. Will it bepossible for all clients to access the database present on SQL Serverin the Win 2K box. I think I have made myself clear upto some extent.Please throw light on how to accomplish the connectivity between SQLclients and server in case of domain failure.Regards,Vinodi
View 2 Replies
View Related
Feb 18, 2007
Ever since I installed Internet Explorer 7 I have recieved an error message when closing the browser tabs. It doesn't matter if I am closing one or all of the open tabs. Here is the message I recieve,
"Access violation at address 02050200 in module iesdsg.dll Read of address 25202E67"
This message didn't occur until I installed IE7. When I uninstall IE7 the message does not show up. Any insight would be appreciated.
View 4 Replies
View Related
Mar 12, 2008
I Have a data flow task inside ForEachLoop.
Inside that dataflow, I was transfering data from a flat file to SQL Server. Now, When error occurs, I want to move that particular file to another location and proceed with the next file. I can't use file system task inside data flow . How can i achieve this ? Is there any other way other than Script-Task (Here script task will be complex thing as of my understanding).
Any solutions ?
View 8 Replies
View Related
Mar 21, 2001
I'm running SQL Server 7.0 SP3 and having trouble with DTS.
I have an Execute SQL Task that runs several stored procedures. When one of the stored procedures fails, the Execute SQL Task just terminates without failing.
I found a knowledge base article (Q238523) dealing with this situation but it was supposedly fixed in SP2 - I have SP3! The other work around suggested, issuing a SET NOCOUNT ON, does not always work.
Has anyone else run into this or have any other suggestions.
I hate the thought of spending days doing another work around in order get basic DTS functionalty to work as it should!
Thanks!
Jerry Dunn
gdunnjr@yahoo.com
View 2 Replies
View Related
Apr 8, 1999
All,
Is there a way, in SQL Server 6.5, to continue processing within a stored procedure even though an error occurs? An example I am inserting records into a temp table within a stored procedure, and there may be duplicate UNIQUE keys, I simply want the procedure to continue inserting records ignoring the failure.
Thank you,
Scott Kolek
Development Manager
SKM Software
http://www.skm-software.com
View 3 Replies
View Related
Aug 10, 2006
Hi,
I have designed a report in sql server reporting services using SQL Server Bussiness Intelligence Studio tool. I have applied sorting feature for a column. Sorting facility is working fine when there is one or more records.
Its giving "object required" Java Script error When there is no record. Kindly help me how to fix this issue.
Regards
Sagayaraj R
View 7 Replies
View Related
Apr 21, 2008
I am getting an 'Access is denied' error:
Error 0x80070005 while loading package file "Package.dtsx". Access is denied. .
The package is executed from the main package via an 'Execute Package Task'. The strange thing is that the error comes after 'Package.dtsx' has run successfully. I am logging the PackageStart/PackageEnd and error events and I see that Package.dtsx ends successfully and then the "Access is denied" error occurs. The main package is launched by executing dtsexec via a SQL Server Agent Job. The packages have 'SaveCheckpoints' set to True. Any ideas are welcome. Thanks.
View 7 Replies
View Related
Sep 11, 2006
greets all,
i have a express set up and running a table filled with a few batches of records. Each batch has approx. 20k records. I have a program that inserts a new batch into the table. afterwards, it makes a call to a stored procedure. this SP compares the last batch with the latest batch and marks the records as additions, updated, or deleted records (which will be used by the program). the problem im experiencing is that the comparison algorithm is getting the "Timeout expired" error returning back to my program. the SP seems to take roughly 1-3 minutes to make the comparisons. i tried setting the execution timeout to 0 (no timeout) in Options->Query Execution->SQL Server->General but i still encounter the problem. am i changing the wrong option for this or is there something else i can try? im going to try to reproduce this database in SQL Server 2000 so i can use the profiler, but im running into problems exporting the database to an older version (im not familiar with bcp, which im looking into). any advice until then?
View 7 Replies
View Related
Jul 23, 2005
I have an identical SQL database on two machines (my machine and a webserver) that links to a database on a third server (S3). When I executea stored procedure on my machine that accesses a database on S3, italways runs without a problem. However, when I run the same storedprocedure on the webserver (via Query Analyzer on my machine, connectedto the webserver), the stored procedure runs without a problem forabout ten minutes, and then I suddenly receive the error "Login failedfor user '(null)'. Reason: Not associated with a trusted SQL Serverconnection.". If I then connect remotely to the webserver via RemoteDesktop Connection and run the stored procedure there it works, andthen I can run it again on the webserver via Query Analyzer on mymachine, but the same problem will occur again after about ten minutes.The only difference between my machine and the webserver is that thewebserver is running Windows Server 2003, whereas my machine runsWindows XP. All settings in SQL on the two machines are identical, so Iwould love to know if anyone has a possible solution to this seeminglyrandom problem.
View 23 Replies
View Related
Dec 26, 2007
We are using SSRS 2005.We designed one report.This report contains Table and chart controls .When Table is hidden,chart only is visible, this chart is going to show only on last page.There is a white space in top of the chart while hiding the table control.
Consider for an Example Report one tables and one chart . We want to show only chart,So we made Table's visible property is false.Total Number of pages is five and table has only four pages and chart has only one page that is in last page. Now the report showing Four pages are empty with header and footer and last page have a chart with header and footer.
Any suggestions how to avoid the empty pages?
I tried to Filter property to filter the table.Same empty page with header and footer is only visible
Thanks In Advance
Mugesh
View 8 Replies
View Related
Aug 15, 2007
Some more insight into the error I am getting.
It always occurs at the task (control flow) level and never at the component (data flow) level
It always occurs on the 'copy' action.
I have tried all the msxml/registry recommendations to no avail.
once again here are the error details
===================================
An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)
===================================
Could not copy object 'SQT Drop Create RAWMigDeal RAWBarrier table' to the clipboard.
(Microsoft.DataTransformationServices.Design)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
at Microsoft.DataTransformationServices.Design.ControlFlowClipboardCommandHelper.InternalMenuCopy(MenuCommand sender, CommandHandlingArgs args)
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.PersistImpl.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.SqlServer.Dts.Runtime.DtsContainer.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
View 7 Replies
View Related
Jun 4, 2007
I am getting an 'Access is denied' error:
Error 0x80070005 while loading package file "E:SSISPackagesPackage1.dtsx". Access is denied. .
The package is executed from the main package via an 'Execute Package Task' that is within a 'For Each Loop' container. The 'For each loop' goes through a single iteration as expected. The strange thing is that the error comes after 'Package1' has run successfully. I am logging the PackageStart/PackageEnd and error events and I see that Package1 ends successfully and then the "Access is denied" error occurs. The main package is launched by executing dtsexec via Process.Start() from a web page. The packages have 'SaveCheckpoints' set to True. Any ideas are welcome. Thanks.
View 7 Replies
View Related
Mar 3, 2008
Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.
Here is a simple example of what I have:
Code Snippet
begin try
if not exists (select * from sys.database_principals where [name] = 'flea')
raiserror('flea not found', 16, 1)
end try
begin catch
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select
@ErrorMessage = error_message(),
@ErrorSeverity = error_severity();
raiserror(@ErrorMessage, @ErrorSeverity, 1);
end catch
go
begin
print 'hello world'
end
At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!
Thanks,
Flea
View 5 Replies
View Related
Jan 14, 2008
Hello everyone,
I have a package that extracts data from a Flat File. If any errors or truncation occur during the extraction of the input data, the package should fail. All fields that have erroneous values should be reported in the log file.
My Solution:
- I have created a Data Flow Task that contains a Flat File Source Adapter and a dummy destination.
- I have left the default "Error Output" configuration of the Flat File Source adapter, namely if a truncation or an error occur for a certain column, then the reaction is "Fail Component".
Problem:
This configuration gives me only the first erroneous column in the row being processed.
Question:
Is it possible to make the Flat File Source adapter continue parsing the current row before it fails? This way, I would be able to get all the erroneous columns in the row in one shot.
Thanks in advance...
Samar
View 6 Replies
View Related
Jul 7, 2015
I Create a measure group and two dimensions using  [AdventureWorksDW2012], I try to change one dimension's storage mode with setting property proactive caching as Real-Time ROlap. There is no any warning message when deploying and processing, but error occurs when I query in sql server analysis services, see below for the error messages and the screen capture.
Error occurred retrieving child nodes: the current operation was cancelled because another operation in the transaction failed.
View 2 Replies
View Related
Jul 12, 2007
We just replaced an older Windows 2000 sp4 server with a new Window 2003 Standard Edition sp2 server to run our batch processing. We noticed that when the SQL command times out we now get the following error in the ADO command object error collection.
[DBNETLIB][ConnectionWrite (send()).] General network error. Check you network documentation.
Native Error: 11
SQL State: HY018
msado15.dll version 2.82.3959 is on this server.
On other servers running Windows 2003 Enterprise Edition sp1 the error on the ADO command object is normal with.
Timeout expired
Native Error: 0
SQL State: HYT00
msado15.dll version 2.82.1830 is on this server.
The SQL server they are talking to is on the other side of a firewall. It is SQL 2005 sp2 running on Windows 2000 sp4.
I have also tested this on a Windows 2003 sp2 server that doesn't have to cross a firewall and get the correct Timeout error. It also has the same version of msado15.dll as problem server.
The application is a collection of VB6 Components that are running in COM+ applications. I have isolated the test application to our one SQL interface component and have build a test vbs that can reproduce this on demand. The VB6 Components were not modified in any way for this change and have been working fine for many years prior to this.
All other aspects of our batch processing works as expected. Transactions are working fine. As long as the SQL doesn't timeout everything is normal. DTCPing was used to ensure both servers have correct ports open to allow DTC traffic. Checked the SynAttackProtect setting on SQL and this is not an issue since other clients have no problems.
One point I would like to add is that this new server was initially setup with a temporary server name and then was changed to use the name of the original server on move day. Don't know if this has any impact or not.
View 31 Replies
View Related
