Recent content by pengwyn

I get the error message: Server: Msg 5105, Level 16, State 2, Line 1 Device activation error. The physical file name 'x:\x\x.mdf' may be incorrect. The correct path is there on the test server and the files from the test db are there to be overwritten. I have also tried to create a new...

Thanks! I have made the change to CAST(c.startdate as SMALLDATETIME) "StartDate" And that seems to have worked. The job runs successfully now. All help has been much appreciated!

periodid is an INT. I added the CAST( )AS INT function hoping that would convert the varchar substring into an INT. Should I use the CONVERT function instead like below? a.periodid >= (SELECT DISTINCT CONVERT(INT,(SUBSTRING(CONVERT(VARCHAR,DATEADD(mm,-4,orderdate),112),1,6))) FROM tblorder...

I am a bit confused myself. The field startdate in vwcontracts is nvarchar(20) and the data is stored in the table in dmy format. (i.e. 14/1/2004, 21/1/2004) The field orderdate in tblorder is smalldatetime and the data is stored in the table in ymd format. (i.e. 2005-01-21 09:18:00...

Are you saying where I have the statements substring(convert(varchar,dateadd(mm,-4,orderdate),112)1,6) and substring(convert(varchar,orderdate,112),1,6) I should replace those with a value like 30112004 in mdy format?

I ran the script 1 as set dateformat mdy 2 as set dateformat dmy select * from vwcontracts where isdate(startdate) = 0 and startdate is not null and startdate <> '' With the dateformat set as mdy I get 35 rows returned when the nulls and blanks are filtered out and 1299 rows without. With...

Yes, I added the statement set dateformat dmy before I ran the query.

The date format is dmy 30/11/2003 14/11/2003 The query did not return any records.

The format for start date is: 1/4/2003 1/11/2003

It's OUCH!! vwcontracts.startdate is nvarchar(20).

Here is the table structure for BO_BACSVAL: AvgTotQty, decimal, 9(10,2) AvgTotBonus, decimal, 9(10,2) CurTotQty, decimal, 9(10,2) CurTotBonus, decimal, 9(10,2) ProductID, varchar, 50 ContractID, int, 4 StartDate, smalldatetime,4

I execute this procedure in Query Analyzer and it works. I execute it with a job and I get the following error in the job history. Executed as user: UKKCPHPUK\SQLAgent. Arithmetic overflow error converting expression to data type smalldatetime. [SQLSTATE 22003] (Error 8115) The statement has...

I execute this procedure in Query Analyzer and it works. I execute it with a job and I get the foollowing error The only difference is thtt if you look at the query in query Analyzer and the proceudu starts out with 'Alter Procedure' and in the Enterprise manager procedure list it starts out...