Problem With A Lot Of Inserts In Short Time - SQL Server 2000
Feb 27, 2008
Hi!
We have SQL Server 2000 on virtual machine (slowly). We run a pair of
stored procedures from ADO.NET 2.0 (WebService):
- first procedure: insert into table A a data row with correlationID
- next procedure: search for inserted record in tab A by
correlationID and insert value into tab B with constraint to tab A.
The problem is when we try to do this in short time (few minutes)
thousands times (5000/6000 pairs in 5/6 minutes) in 100 parallel
threads:
query in second procedure cannot find inserted record in first
procedure.
Hello all, this is my first post, and I think I've found something interesting between sql 2005 and 2000. Thanks in advance for any help you can provide.
I am attempting to migrate a system from SQL 2000(v 8.00.2039 - Enterprise Edition) to SQL 2005 (v9.0.2047 - Standard Edition ), and a few of my most complex queries are generating errors. I was able to isolate the problem to a fairly simple query that works in SQL 2000, but generates an error in SQL 2005.
I believe that the problem has something to do with how short-circuiting is handled in the WHERE clause. I'm not sure if short-circuiting is really going on when these statements are parsed, but it seems to describe the situation well.
To replicate the problem, first create the following table and fill it with values:
insert into orgview (orgname, qid, answer) values('org1', 120, '1') insert into orgview (orgname, qid, answer) values('org2', 94, '2006-06-06') insert into orgview (orgname, qid, answer) values('org3', 98, 'free kevin') ----------------------------------------------
The second value (org2) contains a date, but the rest do not. Under both systems, the following contrived query works fine:
SELECT orgname FROM OrgView WHERE (QID = 94 AND CONVERT(DATETIME, Answer) = '2006-06-06')
It would seem, that when QID = 94, the expression short circuits, and the CONVERT statement doesn't run. This is good, because the CONVERT statement would throw an error whenever 'Answer' doesn't contain a date. However, if modify the query by adding an OR to the end like this:
SELECT orgname FROM OrgView WHERE (QID = 94 AND CONVERT(DATETIME, Answer) = '2006-06-06') or QID=98
then the query fails on SQL 2005 with a 'Conversion failed when converting datetime from character string.' error. On SQL 2000, the short circuiting works fine, and the above query returns 'org2' and 'org3'. I know shortcircuiting may not be the best way to describe the set calculus going on in the background, but can anyone explain what is going on here? Is there any way to make SQL 2005 work like SQL 2000 in this specific case?
Hi, I have the following problem: Within a VBScript, I use a component (written in C++ I think with use of ADO) for sending "Insert", "Update" Statements to an SQL Server 2000 for inserting, updating data. If I insert 100 - 120 Records in a Loop, all works fine. If I insert 1000 records, approximately 150 records will be inserted very quick, then the program pause fo approx. 8 - 15 minutes and then it proceed for the next 150 recs, pause for 8 - 15 minutes and so on.
If I use a SQL Server 2005 for the database, all works fine. The same happens with another customer and another program written in Visual Basic 6.0 with ADO. The access to SQL 2000 pause and with SQL 2005 all works fine. It seems to me that this is a problem with some buffers, timeout, or so. Has anyone an idea on what screw I can turn?
I have SQL Server 2005 installed on my machine and I am firing following query to insert 1500 records into a simple table having just on column.
Declare @i int Set @i=0 While (@i<1500) Begin Insert into test2 values (@i) Set @i=@i+1 End
Here goes the table definition,
CREATE TABLE [dbo].[test2]( [int] NULL ) ON [PRIMARY]
Now the problem with this is that on one of my server this query is taking just 500ms to run while on my production and other test server this query is taking more than 25 seconds. Same is the problem with updates. I have checked the configurations of both the servers and found them to be the same. Also there are no indexes defined on either of the tables. I was wondering what can be the possible reason for this to happen. If any of u people has any pointers regarding this, will be really useful
I have a situation here. Please advice me on this.
I have a master table and a child table. They have a PK and FK relationship. The master table has an identity column with auto increment set to true. This map as a FK in the child table. My questions are:
Can I have a single form to insert a new record into master and child table at the same time?This has to be accomplished without stored procedures. Can it be done?Is it possible to do this with a single insert query? If yes, can it be done with sql data source or dataset or tableadapters’?Please point me towards appropriate link for doing so.
I am kind of baffeled. I have a table with a column of 8 varchar in 2000 and the same in 6.5. When I insert into 2000 with a data length of more than 8 chars via Cold Fusion into the table, it fails. The same Cold Fusion program inserts into the 6.5 table, but truncates the data but does not fail. Does anyone know why this happens. Thanks, Newbie.
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
Hi All, Iam experiencing lot of errors for users who are accessing from application to the database . ERROR: "timeout occurred while waiting for resources to execute the query"
I wpuld appreciate if you could tell me work around for this problem. Thanks, Reddy
DECLARE @PriorDayDate as varchar(10) DECLARE @CurrentDayDate as varchar(10) DECLARE @StartHourTime as varchar(10) DECLARE @EndHourTime as varchar(10) DECLARE @PriorDayDateANDStartHourTime as varchar(20) DECLARE @PriorDayDateANDEndHourTime as varchar(20) DECLARE @CurrentDayDateANDStartHourTime as varchar(20) DECLARE @CurrentDayDateANDEndHourTime as varchar(20)
--select @PriorDayDate --select @CurrentDayDate --select @StartHourTime --select @EndHourTime --select @PriorDayDateANDStartHourTime --select @PriorDayDateANDEndHourTime select cast(@PriorDayDateANDStartHourTime as datetime) as 'PD Start Time' select cast(@PriorDayDateANDEndHourTime as datetime) as 'PD End Time' select cast(@CurrentDayDateANDStartHourTime as datetime) as 'CD Start Time' select cast(@CurrentDayDateANDEndHourTime as datetime) as 'CD End Time'
HI there,I currently store the date using the getdate() functionbut how can I store just the time or seperate the time off from adatetime datatype?M3ckon*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the natureof a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highlyspecialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by thesavings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of yourapplication to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this: CREATE PROCEDURE ProcName @Param int /* ... other parameters */ WITH RECOMPILE AS /* ... procedure code follows */ If we omit "WITH RECOMPILE", what will be the consequence? Thanks
Hello all. I have a nvarchar field in the format dd:hh:mm:ss I need to get a sum of these fields in the same format. So for example if the values are 01:23:00:00 and 00:01:00:00 then the sum of these fields should be 02:00:00:00. Can someone help me? Thanks.
hi. i am managing a sql server 2000 database. for some reason, a table got dropped from the database--why i don't know; i'll worry about that later. is there a way to "rollback" the database to a point in time...say like 5 minutes ago? I have taken the database offline for the moment so no more changes can take place. i have never done this before and i would really appreciate some advice/online tutorials/help on this matter. i do have a full backup of the database because of nightly backups. any help will be greatly appreciated. thanks!
Dear All,Do anyone know where I can find some useful documentation or anydocumentationabout restoring database to the point in time (using logs).SQL Server 2000 on Win 2000Thanks for all,M&M
Im having a lot of trouble inserting a small time value into a table cell. I gave the cell column the data type 'DateTime', i found i couldnt manually insert a time only value such as '12:30 PM' into a column with 'SmallDateTime'. Something about a "SmallDateTime Overflow Error". However if i enter a similar time value into a table column with the data type 'DateTime' it will happily accept it and leave it as entered.
The real problem seems to be when i try to send a time value to that column with my ASP.NET application. Because it inserts the time value and todays date. So that if i send:
12:30 PM
It will be stored as:
15/11/2003 12:30:00 PM
I only want to store the short time, not the date especially not the date that row was created on because thats useless for the purposes of what my application is trying to achieve and just creates problems down the track when selecting rows.
Hello I have and tested in 2 way using variable and constant. Query 1: This query I am using vaiable "@last" for the date value. this takes to run for 2 minute .
Query 2: Here I am using value '11/05/2002' instead of varialbe and it is running within a second.
Why these much difference to run this same query?. Can anybody give correct reason or solution for this. Thanks, Ravi
query 1: DECLARE @last smalldatetime SET @last ='11/05/2002'
select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num and o.added_date > @last UNION select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num INNER JOIN Leap.dbo.ENTERPRISE_STATE es WITH(NOLOCK) ON (a.enterprise_num = es.ENTERPRISE_NUM AND o.last_updated_date > @last)
Query 2:
select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num and o.added_date > '11/05/2002' UNION select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num INNER JOIN Leap.dbo.ENTERPRISE_STATE es WITH(NOLOCK) ON (a.enterprise_num = es.ENTERPRISE_NUM AND o.last_updated_date > '11/05/2002' )
After DST change (US/Canada), when I run any of my reports, I get this error:
"An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help Specified argument was out of the range of valid values. Parameter name: date "
Hi guys, When I thought everything is okay with this script, I got a new problem... I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000. in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results. The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR: run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors. Thanks in advance, Aldo.
Below the code:
Code Snippet Function QuerySalesAging() '-------------------------------------------------------------- 'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library '--------------------------------------------------------------
Dim ConnString As New ADODB.Connection Dim RecordSet As New ADODB.RecordSet
'Report Criterias Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange ' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay. ' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table TemporaryTableName = "CTE" ' The table is a regular one CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..." CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName CmdLine03 = CmdLine03 & " FROM ..." CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")" CmdLine03 = CmdLine03 & " ORDER BY ..."
Maintenance Plans Broken Database Mail Broken Reporting Services Report Viewer web part displays US Format dates instead of report design English-Australian. . In Detail:
Microsoft SQL Server 2005 SP2 was downloaded from Microsoft.com. This download occurred after 5 March 2007 and the file version was 9.0.3042.1 (SQLServer2005SP2-KB921896-x86-ENU.exe) During the Installation of SP2 by a Domain Administrator an error occurred on the Database Engine. A Hotfix Exception was raised and the setup completed with errors. The Error Log reported a problem: ---------------------------------------------------------------------------------- Product : Database Services (MSSQLSERVER) Product Version (Previous): 2047 Product Version (Final) : Status : Failure Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log Error Number : 29534 Error Description : MSP Error: 29534 Service 'MSSQLSERVER' could not be started. Verify that you have sufficient privileges to start system services. The error code is (1067) The process terminated unexpectedly.
The SQL Server service could not be manually started. An error was reported in the event viewer: One or more files do not match the primary file 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 corrupted and should be restored from a backup.
It was noted that a new file mssqlsystemresource.ldf had been copied to the %data% folder. Note: As part of separating data and log files, both the master and mssqlsystemresource databases had been previously moved to another volume %data% whilst their respective log files had been moved to a separate volume also %log%. This had occurred after SP1 was initially installed and months prior to the SP2 install using Microsoft KB articles to detach and reattach the databases. It appears that the SP2 installer does not work correctly if mssqlsystemresource mdf and ldf files are not in the same location. Whilst the Engine could not be started €“ the new mssqlsystemresource.ldf file was manually moved to the %log% file, overwriting the previous version. The SQL Server Service was now able to start without any errors. Since the SQL Server Service has been started two major errors have become apparent: The following error is recorded when trying to create a new maintenance plan: TITLE: Microsoft SQL Server Management Studio ------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'from_msx'. Invalid column name 'has_targets'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
Also when using the maintenance plan wizard the following error is recorded when the Wizard attempts to save the plan at the end: TITLE: Maintenance Plan Wizard Progress ------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------
Database Mail is also broken on the server. The Database Mail Log reports an €śActivation Failure€? when a test email is sent. (the email does not arrive). The SQL Log and event viewer also report the following message: The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Another error affects Reporting Services reports that are accessed using the SharePoint V2.0 Web parts ( rsWebparts.cab) . The date picker in the Report Viewer web part now only recognises US format dates. It is important to note that the same reports run through the Report Manager web site work correctly using DD/MM/YYYY (English-Australian ) date format for the report picker. It is noted that the rsWebparts.cab file was not updated by SQL 2005 SP2 install. However, the behaviour of the Report Viewer web part has definitely been affected by the SP2 install. Are the first two issues related? Are they due to the fact that mssqlsystemresource mdf and ldf files had been moved to an unexpected location prior to SP2? How can this be fixed? Are the Reporting Services SharePoint (V2) web parts going to be updated? We are not in a position to upgrade our Portal 2003/SharePoint Servcies V2 installation at the present time.
Here is a fairly basic question in which I'd like assistance. In determining a standard for a schema, for simplicity, my thinking is that using the long data type would meet our purposes.
Are there any thoughts on mapping out where we would not need to take advantage of long vs short? I realize the space but for this particular table, space won't an issue.
Is there a way to short cut through the fields in a table when updating them or inserting? ie. I have a table with 78 fields and could grow with time. Is there a way to run the UPDATE or INSERT TO [table](blah....) without listing all the table fields?
Hello, i have database front end is Access, and backend is sql server. The front end, there are two dropbox, called 'CountyLongName', and 'CountyShortName'. I want to: when i select a countyLongName, there it will pop up the exact CountyShortName in anohter field.
For example, when i click Howard Couty in the 'CountyLongNmae', it will automatically display "HW" in the 'CountyShortName" Field. How can i do that? Thanks.
CREATE PROCEDURE MyBooks_Selling ( @MemberID SMALLINT ) AS
SELECT * FROM v_BookInfo_Sellers_Extended WHERE MemberID=@MemberID
GO GRANT EXEC ON MyBooks_Selling TO bto GO
CREATE PROCEDURE MyBooks_Buying ( @MemberID SMALLINT ) AS
SELECT * FROM v_BookInfo_Buyers_Extended WHERE MemberID=@MemberID
GO GRANT EXEC ON MyBooks_Buying TO bto GO
Is there a way to make it so I could combine those 2 prcedures and choose which table i would like to select from based on another input parameter? I tried it that way but it didnt work...so im asking here to make sure
Hi all, I have a view with a datetime column, when viewing the column data the format is:
" 2006-06-12T12:00:00.0000000+03:00 "
while i'm only want to see the date part
(that is, 2006-06-12, and if possible in the format: dd/mm/yyyy) now, when i'm using convert( varchar(10), [column_name],101) i'm getting the format i need but the problem is that i'm "losing" the date type, is it possible to view it as the format i stated and still as a datetime type ??
This is a question concerning query optimisation. Sorry if it's a bit long,but thanks to anyone who has the patience to help - This is my first posthere...If I have two tables: 'tblContact' and 'tblCategory' where categories arelike:Code Name010101 Short010102 Fat010103 StupidThe junction table 'tblConCat' has fields CctConID, CctCatCode to tell mewhich contacts have which category codes. These are nchar(6) fields, if itmakes any difference.If I need to find all people who are short, fat and stupid I can see twoways:Solution One:SELECT tblContact.* FROM tblContact WHEREConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010101') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010102') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010103')Solution Two:Build a helper table which contains the codes I'm looking forSELECT tblContact.* FROM tblContactWHERE ConID IN(SELECT CctConIDFROM tblConCat INNER JOIN tblHelperON tblConCat.CctCatCode = tblHelper.HlpCatCodeGROUP BY CctConID HAVING Count(*)=3)I have tried them both out and although I looked at the query analyzer itprovided more information than I knew what to do with. In practise theyboth provide similar working times (I wait about a second) but I thought thefirst looked rather inefficient and thought my helper table might help.There are about 30,000 contact records, 180 category records and 120,000junction table records.All I am looking for comments on any pros and cons of these two approaches -does one look that bad? The database was migrated from Access where thehelper table really did help, but using SQL Server I might not need it.Thanks again, if you got this far!
