Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 job running in Analyser but not in a DTS

Author  Topic 

Jim77
Constraint Violating Yak Guru

440 Posts
Posted - 2007-01-29 : 06:11:20
Hi there guys I have run the following code no problem in QA :

bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.001'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.002'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.003'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.004'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.005'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.006'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.007'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.008'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.009'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.010'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.011'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.012'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.013'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.014'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.015'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.016'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.017'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.018'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.019'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.020'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.021'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.022'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.023'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.024'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.025'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.026'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.027'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.028'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.029'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.030'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000103\Dayadjst\dayadjst.031'
Go

delete #sta from #sta t, StockAdjustments s
where 	s.store = '103'
	and s.type collate database_default = substring(t.adj,6,3)
	and s.ean collate database_default = substring(t.adj,10,13)
	and s.tdate = cast(dateadd(dd,(cast(substring(t.adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime)
	and s.ttime = substring(t.adj collate database_default,100,6)
Go

insert into StockAdjustments
	Select
	'103' as Store,
	substring(adj,6,3) Type,
	substring(adj,10,13) EAN,
	Qty = 		case -- Check if value is minus
				when substring(adj,35,1) = '-' 
				then cast(substring(adj,24,6) as int) * -1
				else cast(substring(adj,24,6) as int)
			end,
	Retail = 	case -- Check if value is minus
				when substring(adj,46,1) = '-' 
				then cast(substring(adj,37,9) as money) * -1
				else cast(substring(adj,37,9) as money)
			end,
	Cost = 		case -- Check if value is minus
				when substring(adj,57,1) = '-' 
				then cast(substring(adj,48,9) as money) * -1
				else cast(substring(adj,48,9) as money)
			end,
	substring(adj,59,8) A,
	substring(adj,68,3) B,
	substring(adj,72,20) C,
	cast(dateadd(dd,(cast(substring(adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime) tDate,
	substring(adj,100,6) tTime, null

from #sta
Go

--Stock Adjusments for store 104

drop table #sta
Go

create table #sta (adj [char] (127))
Go

bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.001'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.002'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.003'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.004'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.005'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.006'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.007'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.008'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.009'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.010'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.011'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.012'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.013'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.014'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.015'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.016'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.017'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.018'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.019'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.020'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.021'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.022'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.023'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.024'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.025'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.026'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.027'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.028'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.029'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.030'
bulk insert #sta from '\\jy2003smem012\d$\Data\Iris6\Data\HOSTIN\000104\Dayadjst\dayadjst.031'
Go

delete #sta from #sta t, StockAdjustments s
where 	s.store = '104'
	and s.type collate database_default = substring(t.adj,6,3)
	and s.ean collate database_default = substring(t.adj,10,13)
	and s.tdate = cast(dateadd(dd,(cast(substring(t.adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime)
	and s.ttime = substring(t.adj collate database_default,100,6)
Go

insert into StockAdjustments
	Select
	'104' as Store,
	substring(adj,6,3) Type,
	substring(adj,10,13) EAN,
	Qty = 		case -- Check if value is minus
				when substring(adj,35,1) = '-' 
				then cast(substring(adj,24,6) as int) * -1
				else cast(substring(adj,24,6) as int)
			end,
	Retail = 	case -- Check if value is minus
				when substring(adj,46,1) = '-' 
				then cast(substring(adj,37,9) as money) * -1
				else cast(substring(adj,37,9) as money)
			end,
	Cost = 		case -- Check if value is minus
				when substring(adj,57,1) = '-' 
				then cast(substring(adj,48,9) as money) * -1
				else cast(substring(adj,48,9) as money)
			end,
	substring(adj,59,8) A,
	substring(adj,68,3) B,
	substring(adj,72,20) C,
	cast(dateadd(dd,(cast(substring(adj,93,6) as int) - 146766), '31/10/2002') as smalldatetime) tDate,
	substring(adj,100,6) tTime, null
from #sta
Go



--Stock Adjusments for store 105

drop table #sta
Go



But as soon as I put it into a dts execute sql task with a functioning connection to the correct server it gives me a error description of :

quote:
Deferred prepare could not be completed
Statement(s) could not be prepared
Invalid object name '#sta'
Invalid object name '#sta'


Has anyone see anything like this before please cheers

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2007-01-29 : 08:04:09
maybe use ##sta....could the #sta be going out of scope??
Go to Top of Page

Jim77
Constraint Violating Yak Guru

440 Posts
Posted - 2007-01-29 : 08:19:32
yeah tried that cheers Andrew, get a similar error message :

Deferred prepare could not be completed
Statement(s) could not be prepared
Invalid object name '##sta'
Invalid object name '##sta'
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2007-01-29 : 11:15:54
could you make #sta or ##sta more permanent...ie define outside this step of the dts...and then clear down at the start + clear down afterwards...but leave the structure intact...in effect bypass the problem....
Go to Top of Page

Jim77
Constraint Violating Yak Guru

440 Posts
Posted - 2007-01-29 : 12:14:52
cheers Andrew this technique bypasses the sqntax error :

Deferred prepare could not be completed
Statement(s) could not be prepared
Invalid object name '##sta'
Invalid object name '##sta'

caused when parseing a execute sql task in dts but I am now getting a 'adding a value to a datetime column caused overflow' error and looking back at my logs this is why the dts failed historically because of the same error not the invalid object error which I thought was caused by temp table.

the thing is the dts worked fine up until a week agao and it still runs fine I put it directly into QA.
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2007-01-30 : 04:40:04
".........adding a value to a datetime column caused overflow'"....
Looks like you'll have to put a 'validation step into the DTS/SQL code to check that whatever calculation you are doing with a date field returns a valid date...ie put a CASE statement together on the lines of...

CASE ISDATE(@datecalc)
CASE TRUE
INSERT RECORD
CASE FALSE
RAISE ERROR + NOTIFY ADMIN
END


some lateral thinking....what date settings are you using??...any chance some regional settings have been changed...esp on the server? I normally force all date manipulation to be the same with a SET DATEFORMAT DMY command....but playing with dates in ccyymmdd format is also guaranteed to work 100%.
Go to Top of Page

Jim77
Constraint Violating Yak Guru

440 Posts
Posted - 2007-02-01 : 19:40:35
sorry for the late reply Andrew yeah I had some blank rows coming into my table which caused the overflow I excluded them with a check on a character string on the first line which sorted the problem out , thanks for the advice
Go to Top of Page

Jim77
Constraint Violating Yak Guru

440 Posts
Posted - 2007-02-01 : 19:40:51
cheers
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site