I am running a DTS to collect the summarized info from Oracle database
into SQL server. I then have a update job which updates my
transactional table from the summarized table.
The update takes a very long time (~ 3 minutes)even though it has
around 1500 rows which causes the application to timeout. I want this
job to be done in less than a minute.
Thoughts on improving performance. Is stored procedure a way to go?
(I have used Isolation,row hints etc etc..nothing seems to be working)
Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
Hi... I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc... ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE @Count int, @Sproc varchar(50), @Status varchar(200), @TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.' UPDATE Statements..Plan SET PlanName = PlanName1, Description = PlanName2 FROM Statements..Plan cp JOIN ( SELECT DISTINCT PlanId, PlanName1, PlanName2 FROM Census ) c ON cp.CPlanId = c.PlanId WHERE cp.ClientId = @ClientId AND ( IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'') OR IsNull(cp.Description,'') <> IsNull(c.PlanName2,'') )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.' END ELSE BEGIN SET @Status = 'No records were updated in Plan.' END
SET @Status = 'Adding plan information to Plan table.' INSERT INTO Statements..Plan ( ClientId, ClientPlanId, UserId, PlanName, Description ) SELECT DISTINCT @ClientId, CPlanId, @UserId, PlanName1, PlanName2 FROM Census WHERE PlanId NOT IN ( SELECT DISTINCT CPlanId FROM Statements..Plan WHERE ClientId = @ClientId AND ClientPlanId IS NOT NULL )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.' END ELSE BEGIN SET @Status = 'No information was added Plan.' END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.) Replication past two months working fine, Now 1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber). Iam attaching thw file. Thanks Sandeep
I have set up an sql server which has a column called times. This column is to hold race times however when i enter times such as 22:20:89 with the 89 being milliseconds i am not bale to input this it will only allow me to put times in where milliseconds are below 60. This is a problem especially for the shorter races where half a second is a big difference. Thanks Mike
When trying to connect to a server on a slow connection (satelite), Enterprise Manager times out. I am able to connect with Query Analyzer. Tried to change 'remote login timeout', but that did'nt help, EM seems to timeout long before this value is reached. Any ideas ?
I am trying to add a list of times together retrieved from my database.
They are from seperate records in the same table.
They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?
Below is the SQL statement of getting times from my database if its any help.
SQL StatementSELECT LENGTH_OF_VISIT FROM NOV.MAIN;
Dear All, I need to select records between two datetimes. My Database Structure and sample values are as below Fromdate ToDate 2007-02-20 09:00:00.000 2007-02-20 12:00:00.0002007-02-20 08:00:00.000 2007-02-20 12:00:00.0002007-02-20 06:00:00.000 2007-02-20 13:00:00.000 Query i used select * from tablename where ((fromdate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103)) or (todate between Convert(datetime,'2007-19-2 10:00',103) and Convert(datetime,'2007-19-2 11:00',103))) My query is not returning values when i am querying between '2007-19-2 10:00' and '2007-19-2 11:00' Please help Thanks
Hello everyone, I have an odd issue and I can't seem to figure it out. I am using SQL Server 2005 and I am using Transactions in combination with Try/Catch blocks. My query (executing the stored procedure) never seems to finish the query in query analyzer. I have removed all statements that exectute or select, etc upon the database except for a simple Select 'test' as test so I would have something in my Begin/End blocks. Below is my stored proc: ALTER PROCEDURE [dbo].[ar_mainCheckRecon] @tripID int, @payment money, @adjustment money, @serviceType tinyint, @checkTpId int, @checkTlId int, @checkNbr varchar(20) AS BEGINSET NOCOUNT ON; BEGIN TRY BEGIN TRAN select 'test' as test
END TRY BEGIN CATCH RAISERROR('Error commiting transaction', 16,1) ROLLBACK TRAN SELECT '0' as success END CATCH
COMMIT TRANSELECT '0' as errorCode
END I call upon the sproc by running this line in query analyzer: exec ar_mainCheckRecon 433636, 0, 500, 0, 500, 0, 23 Is there something I am overlooking as to why the query keeps running and never stops? My current execution time is at 3 mins accoring to query analyzer! Thanks,John
When using a SQL Server DateTime column to store just times, e.g. '6:00 PM' I've noticed some weird inconsistency. If I insert a time in a SQL statement, such as... INSERT INTO myTable (StartTime) VALUES ('6:00 PM') It winds up going in as '1/1/1900 6:00 PM'. This is okay. This is in fact what I want (unless of course the base day for sql server is in fact 12/30/1899, which is my next question). If, on the other hand, I insert a time value by hand, using SQL Server Management Studio, then after I insert '6:00 PM' it winds up as '<Today's Date> 6:00 PM', i.e. '1/20/2008 6:00 PM'. Also, if I insert it using a SQL Parameter, such as... da.InsertCommand.Parameters.Add("@StartTime", SqlDbType.DateTime, 8, "StartTime") ...Then, if StartTime is 6:00 PM again, it also winds up going in as '<Today's Date> 6:00 PM'. Is there a way to control this behavior (other than to programmatically append a base date to the time I'm inserting)? Finally, what is the base day for SQL Server 2005? Is it 1/1/1900 or 12/30/1899? I think I've seen both. Thanks! Aaron
I have two tables: (results) one with numeric results and one with names (importance_scale)
SELECT * FROM results
name i1 i2 i3 i4 i5 ---- ---- ---- ---- ---- john 2 1 2 2 2 anna 0 2 2 2 2 phil 2 2 2 2 2 dave 1 0 2 2 2
SELECT * FROM importance_scale
imp_value imp_name --------- ------------ 2 very important 1 important 0 not important
i1,i2 etc. value it is a imp_value from table 'importance_scale'
I would like to make report which will show all results in this format (in example only first record): name i1 i2 i3 i4 i5 etc. ----- --- --- --- --- --- john very important important very important very important very important etc.
I was thinking about using CASE I don't want use cursor. What is the best way?
I want to enter times for running event performances (like 10:26.35 for a person's 2-mile time, for instance). I am using sql server 2000. This is an ongoing concern for me since I do a lot of work with running events. Can someone give me some direction as to how I can best deal with this?
The datetime data types do a lot of converting to 'time-of-day' and that is not what I want. For instance, if I enter a 2-mile time of 10:26.35 (a pretty typical 2-mile time) my db converts it to 10:26:35 AM.
If I use varchar data type there is a ton of code to write when I want to sort the data.
Jeff writes "I am trying to write a query to retrieve information that was processed between two times of the day (2:00 pm to 6:00 pm) from a date range... I am perplexed; I am new to SQL and am trying not to become frustrated, but this seems to be beyond my skill level. Any guidence would be greatly appreciated!"
Hi I'm new to SQl..I have some monthly data which is being encoded as Varchar...Say forthe month of January 2005 it wud look like '200501'. For each of mymonth I have a coloums which gives me datetime and volume of the job.This is what I'm doingSelectMonth,Time ,Sum(Volume)[color=blue]>From Table A[/color]Where (( Month like '200501%' ) and (Time between '2005-01-0102:00:00.000' and '2005-01-31 06:00:00.000))group by month, timeorder by monthPRoblem: What I want is all the volume between 2am and 6am on every dayof the month?The above query naturaly will give me everything between first and lastof the month...Any help is appreciated...Balaji
I am using an MS Access front end to input data into a SQL serverdatabase. From Access I am generating an update query utilising aninline function. My problem is that when I update a time value,instead of updating the field with something like 08:00:00 it updatesit to 1/1/1900 08:00:00. Is there a way to force the update to onlyuse the hours minutes and seconds and ignore the date part?Any help would be greatly appreciated.RegardsTrevor
Hi guys,I have a field in my DB called EventDate as a DateTime field,therefore it holds both the date and time together like this:'2004-10-14 08:42:57.000'.I need to add together all the times in this column for a particulardate range (BETWEEN).Any suggestions will be great.ThanksSunny:)
I have a web form that collects details on books (as an example), and in that form is a checkboxlist that displays an entry for each potential author in the database (as an example).
The user can obviously tick as many authors as they want to represent Authors of the book. The ticked entries form the entries in the BooksToAuthors table which only has BookID and AuthorID columns.
I have a number of questions:
How do I take what is in the CheckBoxList to the database and how does this relate to Stored Procedures?
Do I fill the checkbox selections into an Array? How do I get these 'many items' to a Stored Procedure that runs a transaction to put the book in and then the many rows in AuthorsToBooks.
What is being passed? Can you pass an array or something to a stored procedure?
As far as performance goes should I avoid using "IN" with update statements?Example:update table_nameset x = 5where y IN (select z from table_name1 where a = b and c = d)If this is terribly inefficient what are the alternatives?Thanks...
I have 3 queries and I need to see if there is a way to combine them since they do the same thing, or if there is a more efficient way I am missing. I run the query below on Table A to find the product first for TableA.Model=1, then for Model=2 and then Model=3. The reason I split it into three queries is I need Model=1 Customers only, then Model=2 only if there isn't a Model=1 Customer, etc.
UPDATE Table1 INNER JOIN TableA ON Table1.Product = TableA.Product SET Table1.Customer = [TableA].[Customer] WHERE (((TableA.Model)="1") AND ((Table1.Customer) Is Null));
okay, so i have about 37 different updates i need to do to a table that is rather large (71million) and has no indexes. i know it's gunna table scan, and honestly, i'm not really worried about that. my question is, is there a way up squeeze all of these updates into one?
here is what i was going to do, but each one will take about an hour to run... (here are 5 of the 37 updates, but they are all basically the same concept)
update t1 set books_music='' from mailorder t1 where books_music is null update t1 set Car_Buff='' from mailorder t1 where Car_Buff is null update t1 set Childrens_Items_Buyers='' from mailorder t1 where Childrens_Items_Buyers is null update t1 set Computer='' from mailorder t1 where Computer is null update t1 set Crafts_Sewing='' from mailorder t1 where Crafts_Sewing is null
in FoxSlow (foxpro) i could just do something like this:
do while !eof() replace books_music with '' for books_music=null replace car_buff with '' for car_buff=null skip enddo
I have an SQL 2005 STD server, full install, that we use to run SQL and reporting services. When I run Microsoft Update, it shows updates for Visual Studio 2005 SP1 and MS Office 2003 SP3. The server does have MS Office 2003 web components installed, installed as part of the initial SQL server install, but not the MS Office suite software. It also has loaded the reporting services version of Visual studio 2005 that installs with reporting services, but not the entire version of Visual Studio 2005. Should I install these service pack updates? Is there any benifit?
Hey, don't know if it's the right place for this question but i hope you help me. I made an application with VS 2005 that connecting to sql server file db.mdf Now i want this application work on another user computer, and of course i don't want to install vs 2005 there. I did install .net framework, but what i need to do to make the database work? do i have to install sql server on his computer? or is there something more simple? I know that if i was using access file than i need to install nothing else. please help! thanx. max
Hi, I am developing an app that manages race results and I need some advice on the best way to store the race times in sql server. As far as I know, the best datatype would be datetime. With the data stored, I will need to be able to do things such as:
Hello, I have been working on this stored procedure, it pulls the results that i want, but it takes a very long time to execute and about half the time it times out, i have tried changing the timeout settings on the sql server to 0 (no timeout) but it seems to have made no difference. Could anyone help with making the query a bit more effiecient? SELECT DISTINCT webStats.id, webStats.ip, webStats.useragent, browsers.browsername, os.osname, webStats.datestamp, webStats.hourstamp, webStats.hostname, webStats.refurl, webStats.refhost, webStats.entrypage, webStats.visitcount, webStats.country, searchengines.enginename, IpToCountry.COUNTRY AS CountryName FROM webStats LEFT OUTER JOIN IpToCountry ON webStats.country = IpToCountry.CTRY LEFT OUTER JOIN searchengines ON webStats.useragent LIKE '%' + searchengines.agentstring + '%' LEFT OUTER JOIN browsers AS browsers ON webStats.useragent LIKE '%' + browsers.agentstring + '%' LEFT OUTER JOIN os ON webStats.useragent LIKE '%' + os.agentstring + '%'
WHERE (webStats.datestamp BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) AND GETDATE()) ORDER BY webStats.datestamp DESCThanks for your help. Bart
I have some code that opens a SqlConnection to a MSSQL 2000 database. The connection opens great everywhere except inside my implementation of IPasswordProvider.GetPassword(). It just hangs there, and then throws a timeout. I have tried using both trusted connections, and userid/password connection strings. I notice that within GetPassword, the authenticated user is COMPUTERNAMEASPNET, whereas in the web method that actually is getting called, the authenticated user is my personal user account. The SqlConnection opens fine in the actual web method.
Can anyone tell me what could stop a SqlConnection from opening while inside GetPassword()? I kinda need to look up passwords in the database.
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
I scheduled a Bulk copy operation, and have been getting some inconsistent failures.
Last night the job was set to run every 3 hours, I expect it to take about 1 hour to finish.
The second itteration of the job failed, the first and third ran fine. Here is the error from DTS
Any suggestions? Is there a better/stable Data Provider for SQL?
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSBulkInsertTask_1 DTSRun OnError: DTSStep_DTSBulkInsertTask_1, Error = -2147217900 (80040E14) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 3621 (E25) Error string: The statement has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040... Process Exit Code 1. The step failed.
I have an VB.NET web app which performs a fairly complicated SQL query. It seems in the morning, the 1st time the page is loaded (and query executed) it takes up to 10-15 seconds to complete loading. Sometimes it even times out. However anytime after that, the page loads up (even from another computer) in about 4-5 seconds. Can someone explain the reason for this and how I might fix the load times in the morning?
