Storing Race Times
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:
rank the results based on the race time
View Complete Forum Thread with Replies
Related Forum Messages:
How Do I: Prevent Race Condition?
I want to be able to read and update a value in the database without entering a race condition. For example: User #1 reads a row from the database, changes a value then writes the value back. User #2 reads the same row AFTER user #1 has read it, but BEFORE user #1 writes it back. User #2 then changes the value and writes it back, overwriting the value that user #1 wrote. I thought I could do this with transactions, but it just makes user #2 wait until user #1 is done writing before user #2 can write. It doesn't stop user #2 from reading while user #1 has it out. Does that make sense?
View Replies !
Which Is Better? Storing Data In The Database OR Storing It In The File System
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
View Replies !
Times
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
View Replies !
EM Times Out
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 ? Brgds, Odd Oren
View Replies !
Add Times
SQL Statement. Adding times together. 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; Returned Results 30-Apr-2007 02:00:00 AM 30-Apr-2007 01:00:00 AM 01-Jan-2006 02:30:00 PM 01-Jan-2006 03:10:00 PM 30-Apr-2007 01:00:00 AM
View Replies !
Inserting Just Times
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
View Replies !
SQL Transaction Times Out
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
View Replies !
Selecting Between Two Times
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
View Replies !
Search Between Times
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
View Replies !
Times In SQL Server
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
View Replies !
MySQL Between Times
Hello,I want to COUNT all records between TODAY 12:00:00am and Now().Select *where TIMESTAMP BETWEEN TODAY 12:00:00am AND Now()How do I do this? Not sure how to specify TODAY, 12:00am.Thanks,Gary.
View Replies !
Adding Times Together
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:)
View Replies !
Times In Sql Server
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. Thanks!
View Replies !
Dates Without Times
Anyone know of an easy way to work with datetime columns ignoring the time part. I want to "group by" the date so it is vital that the time part goes. But I still want to work with the resulting date as a date (i.e. use date functions) so I would prefer to keep the datetime data type.
View Replies !
Name Lookup 5 Times
Hi I'm new in SQL. I have a big problem Database: MSSQL 2000 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? Thanks for your help
View Replies !
Need Help With Dates/times
Hi, I have a flightmaster MS SQL database table with a smalldatetime column called departuretime. Users search for flights on specific dates. The date values are passed to the database from a form in mm/dd/yyyy format. I have two stored procedures used to retrieve records from the database: SP1 retrieves flights occurring after the current time on the specified date; if no flights are found by SP1, SP2 retrieves flights occurring within the next 14 days. I have tried numberous CAST/CONVERT scenarios and cannot get the database to return the information I want. Here's SP1: Code: @ddate varchar(25), @origin varchar(5), @destination varchar(5), @totalpass int AS SELECT * FROM flightschedules WHERE (departuretime < DATEADD(dd,1,CONVERT(smalldatetime,@ddate,120)) and departuretime > DATEADD(hh,3,GETDATE())) AND origincode = @origin AND destinationcode = @destination AND availableseats > @totalpass Here's SP2: Code: @ddate varchar(25), @origin varchar(5), @destination varchar(5), @totalpass int AS SELECT * FROM flightschedules WHERE (departuretime < DATEADD(dd,15,CONVERT(SMALLDATETIME,@ddate,120)) and departuretime > CONVERT(SMALLDATETIME,@ddate,120)) AND origincode = @origin AND destinationcode = @destination AND availableseats > @totalpass When I execute SP2 for flights on '08/05/2006', SP2 seems to work fine, I get: 1052006-08-07 09:00:00 1052006-08-09 09:00:00 1052006-08-11 09:00:00 1052006-08-16 09:00:00 1052006-08-18 09:00:00 When I use '08/05/2006' in SP1, I should get nothing; it shouldn't return any records. Instead, I get: 1052006-07-17 09:00:00 1052006-07-19 09:00:00 1052006-07-21 09:00:00 1052006-07-24 09:00:00 1052006-07-26 09:00:00 1052006-07-28 09:00:00 1052006-07-31 09:00:00 1052006-08-02 09:00:00 1052006-08-04 09:00:00 Here's what's in the database: 1052006-07-17 09:00:00 1052006-07-19 09:00:00 1052006-07-21 09:00:00 1052006-07-24 09:00:00 1052006-07-26 09:00:00 1052006-07-28 09:00:00 1052006-07-31 09:00:00 1052006-08-02 09:00:00 1052006-08-04 09:00:00 1052006-08-07 09:00:00 1052006-08-09 09:00:00 1052006-08-11 09:00:00 1052006-08-16 09:00:00 1052006-08-18 09:00:00 I've been looking at this for days and I'm getting wonky. Why am I getting dates returned that are out of the range I'm specifying?
View Replies !
Subtracting Times?
I'd like to take two DateTimes, subtract them and insert the result as HH:MM:SS, I think you use datediff but I'm not clear on the datediff formatting to get back HH:MM:SS... Thanks for your help! --PhB
View Replies !
Various Times From Various Dates
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!"
View Replies !
Stored Procedure Times Out
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
View Replies !
Exec SP Many Times, From Select?
Hi All... if i had the following sp... ******************************************************* create procedure my_insert (param1 int, param2 int, paramx int) as ... complicated insert routine ... return ******************************************************* 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 Many Thanks Gary T
View Replies !
SqlConnection Times Out In GetPassword
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.
View Replies !
Select Records Between Two Times
This seems so simple yet I can't figure it out.I have a table that has two important columns for this query.Column A has varcharColumn B is datetimeAll I want to do is select any record that is between 5:00 am and10:00 am regardless of what date it falls under.In my brain I seeSelect *From <table>Where B Between Like ‘%5%am' and Like ‘%10%am'This chokes and I suppose its because Between is literal and Like isvariable.I cant convert column B to just times because I still need to see thedate in the results of the query.Thanks in advance for help
View Replies !
Application Times Out While Doing The Updates
here's the scenario..I am running a DTS to collect the summarized info from Oracle databaseinto SQL server. I then have a update job which updates mytransactional table from the summarized table.The update takes a very long time (~ 3 minutes)even though it hasaround 1500 rows which causes the application to timeout. I want thisjob 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)AJ
View Replies !
Strange Processing Times
I have written a simple sql statement that I am running through theviews in sql manager that retrieves data from a couple of tables by daterange and a street name. I am getting the following run times forvarious date ranges.I did the above as a test because of a sudden reduction in retrievalresponse times.September data < 1 secondOctober data < 1 secondSeptember + October > 30 seconds (what could cause the jump)September 1 > October 5 < 1 secondSeptember 1 > October 6 > 30 secondsTotal records per month is less than 50Indexes just rebuiltSame results when running fron another PCResults are consistant from run to runOnly three fields returnedFast server, 4 processors, very little activity, 4 gig ramAny ideas where to look? Reboot server?Thanks.Charles*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Scheduled Job Fails 1 Out Of 3 Times
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.
View Replies !
SQL Query Load Times
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? Thanks
View Replies !
Same SQL; Different Exec Plans; Different Run Times
I have a problem that is starting out in Reporting Services but I believe also involves basic SQL issues. In SSRS, I have a report created that will execute in 30 seconds when the default parameters are entered. If I change the parameters, the report will take 9 minutes to execute. If I reverse the values of the parameters, I'll get the same results. The default will run in 30 seconds and the entered values will run in 9 minutes. So to recap the scenarios. First test: Default set to US - run time 25 seconds Change country to UK - runs in 9 minutes Then change the programming of the defaults and: Default set to UK - run time 25 seconds Change default to US - runs in 9 minutes Upon doing some further investigation, I've discovered that the SQL is being executed with two different execution plans. WIth the default paramters (regardless of what they are) execution Plan A is used. When the parameters are changed, execution Plan B is used. I do no know enough about execution plans to read through them and understand the differences, however, the plans are not really the issue. The issue is why does SQL Server create two different execution plans? In Plan A, default set to US, the execution plan does not see the passed parameters. The values are directly entered into the SQL. In this example, the query will run in about 30 seconds. WHERE (REGION IN (N'US',N'Canada')) AND (SUB_REGION IN (N'Canada',N'US')) AND (COUNTRY IN (N'Canada',N'United States of America')) In Plan B, changed to UK, the parameters are passed and are seen in the SQL. In this example, the query will take about 9 minutes to run. WHERE (REGION IN (@Region_Selected)) AND (SUB_REGION IN (@Sub_Region_Selected)) AND (COUNTRY IN (N'Great Britain',N'Ireland')) For some reason, the parameter @Country_Selected isn't passed but the values are entered. In this example, Great Britain and Ireland. Why does SSRS substitute the parameters for the values in Plan A but not in Plan B? Why does the SQL engine care and create two different execution plans? Rob
View Replies !
SSIS To Run A Webservice Times Out
We have a package that runs a webservice. The webservice connects to exchange for multiple mail accounts so it sometimes takes awhile to run. If run from SQL Server Management Studio, it usually completes, but may occassionally time out. If run from a job (i.e. we want this scheduled) then it NEVER completes successfully - althought there is very little information about what the error was. It seems to fail too quickly to be a time out error... Any idea why this is happening? Or how to prevent it from timing out? Is there a way to initiate the webservice asynchronously from SSIS?
View Replies !
Run A Task Multiple Times
I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script. Basically, it is an audit log, which I set variables and then insert into a SQL table the variables. I would like to do this: Task1 ------Success-----> Set Vars -----Success--> Log | Task2 ------Success-----> Set Vars -----Success-| (do the Log task again) | Task3 -------Success-----> Set Vars -----Success-| (do the Log task again) | etc This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once. Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again. Any ideas?
View Replies !
SLow Access Times
Hii, A bit new to SQL 2k5 but here goes, I recently installed SQL 2k5 on a preety decent box with about 4GB Ram and created a database called PointOfSale. As probably obvious by now, the application that accesses the Database is a PointOfSale application. The store sells ladies clothing items, of which each is barcoded. When an item is scanned via the barcode reader into the application, it takes like about 10-15 seconds for the item information to appear. Of course the item information resides on the database. There are three registers and the same thing happens on all. I am running sp 2 for sql2k5 already. Any thing I can do that would reduce the 15 sec delay significantly? Any help at all would be appreciated immensley. Regards, Burt.
View Replies !
Function Is 10 Times Slower Than SP
Hi all, In order to return a table for a specific input parameter, I am using Function, but the performance is just awful! After I have tried same code as SP, the whole thing is running under 1 sec (like 0.5 sec), while the function is about 10 times slow (4-6 sec). I know in SQL 2000 function is slower than SP, but that cannot be as bad as 10 times slower. Now, in order to use that table from SP, I have to create a temp table, then insert result into that temp table, before I can direct use any "select" sentence. Any explanation here? Or how to "select" from a SP directly? Thanks, Ning
View Replies !
Release Times And Process
With regard to the rest of the development shop the BI department(myself) tends to be more lone ranger. As I can often develop test and deploy my reports in a relatively short amount of time. However there are some who believe that the rapid deployment of report (ad-hoc and others) on a release schedule shorter then 2 weeks is incongruent to the rest of the build release times hence must be bad. So the question is ... Fast and furious, long and laborious. I find that ad-hoc reporting is very difficult to relate to the rest of the software development process however there is SQL to be maintained and rdls to be stored. Does anyone have a nice ad-hoc reporting request to deployment process that encompasses these concerns? I personally like being the cowboy but it does cause hell with the PM.
View Replies !
Execution Of Report Times Out
I am having major problems with some of the reports that i run. Some of the larger reports are running for 20-30 mins and then timing out, allthough the actual sql stored procedure behind the report only takes 2-3mins to run. I sometimes get an rsexecutionnotfound error which looks like the chunk data has been removed by ssrs before it is sent to the screen i am getting to the end of my teather with this as i have gone through everything that i can find to try and stop this happeneing and after 2 months of investigation still nothing. There is one thing that seems to speed up the reports and that seems to be if i pump the data for the report into a new table and then ref that table in the report which only takes a couple of seconds it works fine, but surely ssrs should be able to cope with a 2-3 min query. Thanks for anyones help on this.
View Replies !
Export To Excel Times Out
Hello. I have a report that runs to over 500 pgs of data. the number of rows returned is well under the maximum row limit for excel and teh data displays correctly within the report. When trying to export to excel it fails to open an excel file. I believ this is because of the volume of data as with a samller set it works fine. Is there any way to achive what I am trying to do so. I need the data feed in excel. Thanks, Kiran
View Replies !
Set Based Way To Add The Last Row (a Few Times Or Not) Into A Dataset
I have a view that returns a dataset similar to this... IssueID FacilityID CalendarYear ExpenseYear Incurred DOS LRDate 747 2572 1994 2 123456 11-5-1994 12-18-1996 747 2572 1994 3 123456 11-5-1994 12-30-1997 747 2572 1994 4 123456 11-5-1994 12-30-1998 747 2572 1994 5 223456 11-5-1994 12-30-1999 747 2572 1994 6 4564 11-5-1994 12-18-2000 747 2572 1994 7 100000 11-5-1994 11-12-2001 747 2572 1994 8 121111 11-5-1994 10-14-2002 What I need to be able to do is attach additional records for ExpenseYear 9, 10, 11 and 12 using most of the data from the record that has ExpenseYear 8. IE, I need to append additional records that look like this.... 747 2572 1994 9 121111 11-5-1994 12-30-2003 747 2572 1994 10 121111 11-5-1994 12-30-2004 747 2572 1994 11 121111 11-5-1994 12-30-2005 747 2572 1994 12 121111 11-5-1994 12-30-2006 Only thing that needs to change in these records is the ExpenseYear and incrimenting the LRDate (I can manipulate the date to do what I need to). I also need to be able to control the ExpenseYear maximum (I can do that with a formula with other data that I am pulling back in the recordset). I haev tried to use Recursive queries to do this and even tried to use full joins (which I've never used before) and still can't get this thing to work. I woudl like to learn from this and not just use a while loop to push in the extra data. Something more elegant and set based (preferably) would be in order, I believe. If it can't be done, then I'm open to that, too. Thought I'd try for a set based solution first. If someone could help me out with this I would really appreciate it. Many Thanks Scott PS - One more thing I forgot. In some parts of my dataset I already have data to the ExpenseYear that I need (I"m not missing the data to haev to carry it forward) so the query would have to take that into account as well. Again, Thanks you SQL gurus!!
View Replies !
Rda.push Multiple Times?
Hello, After doing some research it seems like you can only push the same table once using rda.push -- is this correct? If yes, are there any other alternatives for saving changes to the table back to SQL Server aside from merge replication? One idea I am toying with is to pull the tracked table with 0 records, save changes to the tracked table, push, drop table and pull, repeating this process everytime I push the data. Wondering is somebody has any advice? Thanks!
View Replies !
Importing Times Into SQL Server
I am trying to import an access database into SQL Server 2000. I am using the DTS Import wizard to to this. Most of the tables import fine, but a few are not importing. Here is the error message that occurs on most of them: Code Snippet Table Name Here Error at destination for Row number 1. Error encountered so far in this task: 1. Insert error, column 7 (€˜CallTime€™, DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification. Here is an example of an entry in one of the 'Calltime' rows: 7:45:30PM. This is a date/time field in Access. I looked at this SQL table after the import finished and there was no data. Also, it tried to import as a smalldatetime data type. Is there something I can change in Accesss to fix this problem?
View Replies !
How To Find The Difference Between 2 Times In SQL
Hi, I am trying to find the difference between two times (timeIN, timeOut)and then populate a new field called "duration of stay" with the difference. The two times are in a table called attendance and the new duration of stay field is to be populated in a table called factAttend2 Is there an easy way to do this? Thanks
View Replies !
Don't Want To Insert Same Values 2 Times.
create proc dbo.usp_CatchAddressException @tableName varchar(500) as -- alter proc dbo.usp_CatchAddressException @tableName varchar(500) as begin set nocount on declare @sql varchar(700) -- truncate table tempException set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp) select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate() from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in (select nm_street from HAVA_DCDE.dbo.state_streets ss)' -- and a.id_town = + @tableName -- print(@sql) exec(@sql) set nocount off end --------------------------------------------------- when i run this SP 2 times, it will insert the values 2 times. what should i do if i don't want to insert the same values 2 times? I'm confussed, Should i use update? any input will be appreciated.
View Replies !
Compare Two Times In SQL Express
Hey, everyone. I'm new to database programming in SQL Server, and I currently have the 2005 express edition. I was wondering whether it was possible to check whether one time (h:mm AMPM) is less or greater than another (the other in this case being the GETDATE). I already made a user-defined function in order to extract the time from a datetime, since SQL Server doesn't support the time data type. The thing is that the value is returned as a char(8) in the 12-hour style HH:MM AMPM format and I'm sure that's what's screwing up the comparison function. What would I need to do to compare two times (24-hour or 12-hour, whichever is easier)?
View Replies !
Query - Between To Dates And Between To Times
Hi there, I'm a little bit stumped on this one. I have a column in a table that records when the date and time of an event took place. Table Name: Chronicle Column Name: Created (of type DateTime) I would like to select the Chronicle records that are between two dates. (e.g. 1 May 2001 and 20 May 2001) And I would like to select those records that are between two times. (e.g. 6:00am and 1:00pm) Does anyone know how to do this or have any pointers for me? I can see it would be easier if I had the date in one column and the time in the other. Can it be done without doing that? cheers, Hamish Hamish Norton hamish@liftrider.com
View Replies !
Function Running Many Times
Hello, I see the following problem regarding a SQL function that returns a value. We have the following query SELECT c.Id_Customer, c.Name FROM t_Customers c WHERE c.Id_Status = fn_GetParameter('ID_Status.Active') The idea is not hardcoding the status and other values on each query, and, since SQL Server does not support the definition of constants, we have a table with many parameters and we search them. We defined the function "WITH SCHEMABINDING" and the SQL Server recognizes it as DETERMINISTIC, so I do not understand why it executes the function as many times as records in the t_Customers table, since every time it is executed it returns the same value. We could define a variable, assign the value returned by this function to this variable and then use it on the SELECT, but this approach is useless if we use SQL instead of stored procedures (for example, in reports from reporting / BI tools). Any explanation about why SQL chooses to execute the function many times, and any hint regarding how to make SQL Server execute only once the function will be very appreciated. Thanks in advance, Lisandro.
View Replies !
|
TRACKER
