Access97 To SQL7 DTS For Time Values
Mar 17, 2000
When Access date/time data is transformed to SQL7, it seems to work fine as long as the data contains date information. But, if the data is time only, there is the 2-day diff. coming out of Access.
For date/time data in Access, day 0 is 12/30/1899--for SQL7 it is 1/1/1900.
So, it appears that DTS first converts Access date/time data to a date formatted string and then sends to SQL. This will only be a problem for times....8:00 AM is stored to SQL as 8:00 AM on 12/30/1899, not 1/1/1900. If I try to calculate hours or minutes from midnight (date/time 0) I get a negative number.
Has anyone else encountered this? I guess my choice is to modify DTS to correct for the dates or to run an update once the data is moved.
View 1 Replies
ADVERTISEMENT
Mar 12, 1999
When I try to link tables from Access97 to SQL7, all the columns have #deleted# in them. Does any one know why this is?
Thanks
Phil
View 3 Replies
View Related
Jan 11, 2000
I would like to create a MSAccess97 front end for some SQL7 data.
I created an odbc source system dsn for the server holding the SQL7 data.
When I try to add a table to my Access97 database by linking it to a SQL7 table, the only choices presented me are tables in the default database for SQL7 on my server. How do I link to tables in another database on that server? Thanks.
View 1 Replies
View Related
Sep 28, 1998
I`m trying out SQLServer7, have installed it on my workstation, have been able to open access97 and link to sql7 tables I created. Want another user to use access97 to set up her own database and link to my sql7 tables. Do I need to install anything from the sql7 beta disk on her desktop? Right now she can`t link a sql7 table to her access97 db. Thanks.
View 1 Replies
View Related
Dec 14, 1999
Hi!
Has anyone experienced this problem?
Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values.
For example, the following query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,2,3,11,19)
go
returns a different result than this query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,3,11,2,19)
go
The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest:
AND columnA IN (19,11,3,2,1)
Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments...
Thanks!
View 2 Replies
View Related
Apr 23, 2007
[RS 2005]
Given the starting and stopping points (time values), how do I generate values between these points.
For example, if I have 08 (representing Hour) as a starting point and 12 as a stopping point.
From this I would like to generate a data sequence like 08, 09, 10, 11, and 12.
So how do I accomplish this? In SQL or in the RS?
The only thing I can think of is using a WHILE loop and a temporary table in SQL (not to keen on doing this).
//HÃ¥kan
View 7 Replies
View Related
May 30, 2001
I have migrated a database from Access to SQL. Promlem is that I cannot edit the records in the now
linked tables. We are still using the Access front end. Why can't we edit the records?
Thanks in advance.
View 7 Replies
View Related
Dec 18, 2000
Hi. Silly question time please.
I'm a newbie when it comes to SQL7.
I'm trying to connect via Access 97 VBA to a SQL server table. I can't seem to get it working at a basic level.
Has anyone got the basic syntax or sample piece of VBA code to connect to a SQL server. I've been searching everywhere to no avail so far.
Dave.
View 4 Replies
View Related
Oct 13, 2000
If i create ODBC links for a SQL server db in Access and make the file read-only, i find that 2 people cannot open teh file simultaneously...
any ideas why and any suggestions how to over come thi problem...any solutions will be highly appreciated
regards,
resh
View 2 Replies
View Related
Feb 14, 2005
I have an Access 97 database that I would like to "mirror" in my SQL server 2000 database.
Basically I would like SQL server to keep a current copy of an Access 97 database table which exists on remote machine on our network. Any time there is a change made to the Access table, I would like the SQL server to be updated automatically.
Is this something that is possible to do with replication/synchronization, or do I have to push the data up with another app?
Thanks
View 5 Replies
View Related
Mar 9, 2000
The website i'm building is using an access97 database. Actually the database isn't very big but i hope it will become. So i would like to convert it to SQL 7.0 which seems to be more powerfull. Is there a software which can do it automatically? What are the modifications needed by ODBC ?
Thanks !
View 1 Replies
View Related
May 11, 1999
I am trying to link a table from a MS SQL database into access97 thru ODBC. When I do this, I get the message "Can't define field more than once." When I look at the table in SQL, there are no duplicate field names, however, when I bring the table in to do a Crystal Report, I see there are 2 fields that have duplicate field names 6 times. (Evidently Crystal doesn't care about this as Access does.) Any clues on what is happening?
View 2 Replies
View Related
Mar 8, 2006
We have an Access97 database with 122 tables. I try to use import and export wizard to import the data from access into SQL 2005 database. I find an interesting problem. When select all tables, the wizard give following errors:
Pre-execute (Error)
Messages
Error 0xc0202009: {DBD1EAB5-7865-4B89-A7BB-DDC8507D8119}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - PlanClasses" (5206) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)
But if I divide importing as 2 steps:
1. import all tables before PlanClasses, it works.
2. import all tables after PlanClasses including PlanClasses, it works.
If I just import PlanClasses itself, it works, but I can not import all tables at once. Interesting thing is if I just unselect PlanClasses, it will have same error at the class right after PlanClasses. Is there size limitation when doing import at once?
View 5 Replies
View Related
Oct 3, 2002
Does Access2000 upsizing wizard support migration of tables to Sql 2000?
View 1 Replies
View Related
Jan 8, 2004
Greetings!
I recently rewrote an old ACCESS 97 application to work with MSSQL 7.
The program works fine until I use a form whiche opens several linked tables at the same time. When closing the Form I get (At unpredictable moments) an ODBC SQL SERVER DRIVER update failure error message.
All rights are set on the tables for Public and even then i keep getting this error at very odd moments. I also had a look in Access at the ODBC refresh rate which is set at 10sec , OLEDB refreshrate 10 sec, and refresh is set at 5sec.
I tried different combinations of timeout settings and even renewing the MDAC to version 2.7.
Nothing helps.
Can anyone give me a tip as to look for the possible source of the error and how to fix it?
Thanks!
Vincent JS
View 2 Replies
View Related
Jul 20, 2005
I have an application written in Access 97 that connects to a SQL2000backend. One field is a description field that is a data type NTEXT in theSQL database. In my access form, I can not enter more than 255 characters.Before I converted the backend to SQL, the description field was a memofield in Access.What do I need to do to make it so I can enter more text into this field?
View 1 Replies
View Related
Nov 16, 2007
Hi
I'm new to SQL and have a problem with the following script:-
INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)
VALUES 2311, 19219, 'BRAN', '01/12/2007', 'Jon')
The above script works OK on a one-off basis but....
Basically, organisation_number_2 (i.e. 19219) always stays the same. However, I need to update organisation_number_1 several times (i.e. 2311 will then change to 2312, 2313, 2314 etc.).
Rather than pasting the script several hundred times and changing the organisation_number_1 value each time, is there a quick way to encompass all the organisation_number_1 values in one go?
I've tried e.g. VALUES (2311,2312), 19219, 'BRAN' etc. but this doesn't seem to work.
Thanks for your help.
Jon
View 6 Replies
View Related
Aug 25, 2005
I have a table called WorkItem. It models a chunk of work done duringa working day.It has two columns that I'm interested in:Start (smalldatetime) - the TIME the work block is begunDuration (int) - the duration in minutes of the work block.In another table called OvertimeRates I have information about ratemultipliers and a column that tells me the TIME that the ratemultiplier kicks in.e.g.OTRateBegins (smalldatetime)In terms of calculating whether a particular work block starts afterthe OTRateBegins, I could (I presume) do something like:If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,OTRateBegins, 108)However, would I be better off using DATEPART functions to get the hourand minute parts of both the Start and OTRateBegins, and using theminstead? For some reason, (probably paranoia!), I am suspicious of theCONVERT function.Apologies for not posting DDL, but I felt that the situation didn'treally warrant it.ThanksEdward
View 6 Replies
View Related
Apr 30, 2007
I was attempting to create a linked server from SQL-Server2000 to an Access97 mdb file using the following scripts
EXEC sp_addlinkedserver
@server='REMOTE_OFFICE',
@srvproduct='Jet 4.0',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='F:RealEstate_Office1.mdb'
and
EXEC sp_addlinkedsrvlogin
@rmtsrvname='REMOTE_OFFICE',
@useself='false',
@locallogin='sa',
@rmtuser='Admin',
@rmtpassword=NULL
And while querying the linked server from the query analyzer using the following select command
SELECT *
FROM REMOTE_OFFICE.RealEstate_Office1.dbo.E_GOV_RE_OK
I got the following error
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].
could you explain why this happen
View 4 Replies
View Related
Aug 17, 2007
Hello, I have an issue where I need to format the way my times are stored in my table to the way I can compare those times with the current time given by:
select right(convert(varchar,getdate(),100),7)
this provides a result like: 2:10PM
But my times are stored like: 2:10 P.M.
so, Im thinking i need to format the saved time in order to compare it to the current time so I can do the following select:
select * where startTime > (
select right(convert(varchar,getdate(),100),7)
)
Thanks a lot for your help!
View 4 Replies
View Related
May 14, 2004
HI,
I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".
NOW,
HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.
HERE IS HOW THE PROC WRITTENED FOR IT GOES :-
DROP PROCEDURE DE_NAT
CREATE PROCEDURE DE_NAT
AS
BEGIN
DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
SET @MON = MONTH(GETDATE())
SET @YEAR = YEAR(GETDATE())
SET @MONYEAR = @MON + @YEAR
EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
INTO TSD_' + @MONYEAR
+ ' from TSD_20 A
LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
ON A.CUSTOMERS = B.CUSTOMER')
END
THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.
BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'
FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-
CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
----------- ---------- ---------- ---------- ----------- ----------
101023 <NULL> <NULL> <NULL> <NULL> 1.0
102022 1.0 1.62.3 3.4 4.5
NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"
CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?
AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
AGAIN CAUSE A PROBLEM .
PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.
View 6 Replies
View Related
Aug 27, 2006
Here is the sample data
nType dCDServerTime
02006-05-12 03:50:46.770
12006-05-12 04:00:59.153
962006-05-12 04:01:23.827
962006-05-12 04:16:01.297
142006-05-15 05:02:46.857
202006-06-23 03:23:36.970
I am trying to find the time diff between dCDServerTime of the "first" ntype = 1 and the "first" ntype = 96. There can be any number of rows with 1 and 96 but rows with 1 always come before those with 96.
I tried by putting a subset of the above result into a table variable but don't seem to get ahead without declaring more variables.
declare @t table
(Type int, ServerTime datetime)
insert into @t
select top 1 nType,dCDServerTime from tblEvent
where nGlobalCaseID = 11901643 and nType = 1
UNION
select top 1 nType,dCDServerTime from tblEvent
where nGlobalCaseID = 11901643 and nType = 96
select * from @t
Is it possible to do it with a single select?
View 8 Replies
View Related
Oct 12, 2004
Hi - I am a bit of an amatuer, but I am trying to use the EMS data Pump to get all of the data out of an Interbase 6 DB into an MSDE DB. I have managed to instal an Interbase ODBC driver, and I ca connect to it and then automatically create the tables in my MSDE DB, but I get an error stating that there cannot be multiple Timestamp columns in the MSDE databse. Is this true ?
View 3 Replies
View Related
Jun 10, 2015
I have a table Mytable which has two fields (Date, Value) and record is based on every hour like this:
Date Value
1999-01-01 00:00:00 10
1999-01-01 01:00:00Â 8
1999-01-01 02:00:00 6
...
1999-01-01 23:00:00Â 12
1999-01-02 00:00:00 9
1999-01-02 01:00:00 5
...
1999-01-02 23:00:00 2
How can I use a query to get accumulate value for the day? the result should look like this:
Date Value Accumulated_Value
1999-01-01 00:00:00 10 10
1999-01-01 01:00:00 8 18
1999-01-01 02:00:00 6Â 24...
1999-01-01 23:00:00Â 12 36
1999-01-02 00:00:00 9 9
1999-01-02 01:00:00 5 14...
1999-01-02 23:00:00Â 2 16
The accumulated value should be based on every hour for the day and then repeat for the second day and so on.
What is the best way to do this? I am using SQL 2008
View 2 Replies
View Related
Dec 5, 1998
How can I copy a SQL 7 database from one server to another? Please tell me all the ways because I've tried the obvious and it doesn't work. (backup and restore - won't go from one server to another, DTS transfer object Wizard - does a whole pile of errors and doesn't do stored proceedures and trigures). Thanks for help in advance. Also, what is the email address to post to the listserv and where is the FAQ for SQL7? Most of my listserv's tell you where to post right at the bottom of the digest I get - any chance you guys would do that with this list? Thanks again.
View 6 Replies
View Related
Nov 26, 2007
hi all,
In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.
table 1:
tid--> int,identity,primary key
tname-->varchar(200)
table 2:
sid-->int,identity,primary key
tid-->fk (this tid is set as foreign key for the tid in table1)
now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..
pls help me..
thanks
swapna
View 3 Replies
View Related
Jul 15, 2014
As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.
I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.
Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.
It leads to massive implicit conversions and I am trying to change this.
To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.
Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.
View 4 Replies
View Related
May 20, 2008
I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?
I have used the following lines before the function to obtain the 2nd parameter:
SELECT GETUTCDATE()
SELECT GETDATE()
SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')
DECLARE @UTC DATETIME
EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4
-- =============================================
-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.
-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value
-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>
-- =============================================
CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)
RETURNS DATETIME AS
BEGIN
IF ( DATEPART(hh, @UTCDate) <> 0 )
BEGIN
DECLARE @LocalDate DATETIME
DECLARE @UTCDelta INT
DECLARE @thisYear INT
DECLARE @DSTDay INT
DECLARE @NormalDay INT
DECLARE @DSTDate DATETIME
DECLARE @NormalDate DATETIME
SET @thisYear = YEAR(@UTCDate)
IF (@thisYear < 2007 )
BEGIN
SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1
SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)
SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
END
ELSE
BEGIN
SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )
SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )
SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'
END
IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))
BEGIN
SET @UTCDelta = @TZ + 1
END
ELSE
BEGIN
SET @UTCDelta = @TZ
END
-- now convert utc date to local date
SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)
END
ELSE
BEGIN
SET @LocalDate = @UTCDate
END
RETURN(@LocalDate)
END
GO
View 6 Replies
View Related
Nov 8, 2006
As the Microsoft Time Series algorithm implementation is based upon the Autoregressive Tree approach described in:
C. Meek, D. M. Chickering, D. Heckerman. Autoregressive Tree Models for Time-Series Analysis. In Proc. 2nd Intl. SIAM Conf. on Data Mining, 2002 (SDM-02). SIAM, pp. 229 €“ 244. http://www.siam.org/meetings/sdm02/proceedings/sdm02-14.pdf.
The model estimated is refererred to as an instance of "... autoregressive tree models of length p, denoted ART(p). An ART(p) model is an ART model in which each leaf node of the decision tree contains an AR(p) model, and the split variables for the decision tree are chosen from among the previous p variables in the time series..." (see the last paragraph of p. 2 of the paper).
What is the value of "p" used in the Microsoft Time Series implementation -- specifically, how many previous time series variables are used in estimating the model? It doesn't appear that this value can be specified in the algorithm parameters -- is that correct?
Thanks,
- Paul
View 1 Replies
View Related
Jun 6, 2015
I have the table as
|start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL
I want the output as : -
|start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 3:00
1/06/2015 3:00 || 1/06/2015 3:20
1/06/2015 3:20 || 1/06/2015 4:00
1/06/2015 4:00 || NULL
I am trying the below mentioned code but it is not giving me the desired output..
with cte as
(select
start
,end1
,ROW_NUMBER() over (order by (select 1)) as rn
[Code] .....
I am getting wrong output as -
| start || end1 |
1/06/2015 1:00 || 1/06/2015 1:30
1/06/2015 1:30 || 1/06/2015 2:00
1/06/2015 2:00 || 1/06/2015 4:00
1/06/2015 4:00 || 1/06/2015 4:00
View 1 Replies
View Related
Jan 11, 2007
hello,
I am trying to insert date and time into my table.
insert into <table_name> values('12/12/2006','12:23:04');
but it displays error at " ; "
can anyone help me to figure out the problem
Thanks a lot in advance.
Regards,
Sweety
View 3 Replies
View Related
Mar 30, 2008
Hi All,
I have developed few charts - line graphs - and have checked the Numeric or time-scale values on the X-Axis. Now, when the graphs come out, they appear to have two datasets. For example, if the X axis has values from 1st march to 14th March, then there will be a line from 1st March to 14th March, but additionally there will be another one, pointing to different datapoint, and again starts from 1st March and ends at 14th March. Whereas I think it is because of the Numeric or time-scale values selection, but I am not sure. Also, is there a particular way to interpret such graphs??
Thanks a lot, I would have loved to give a picture, but I think I cannot give it.. I did not find an option..
Let me know if the question is not clear enough..
Manoj Deshpande.
View 4 Replies
View Related
Sep 9, 2015
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)Â
Note: Both col1 columns are of type nvarcharÂ
View 6 Replies
View Related
