 | |
Scheduled Task Fails after amending stored Procedure
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Scheduled Task Fails after amending stored Procedure -
01-12-2004
, 05:26 AM
#2
| |||
| |||
|
|
I have a DTS package that is called by a scheduled job. The dts package calls a stored proc. I've noticed that when i amend the stored proc, the next time the job is due to run it fails. If i recreate the package then schedule all is well. Obviously a pain in the bum while i'm testing ... Is there a way around this??? Regards Paul.. |
#3
| |||
| |||
|
|
-----Original Message----- Can you give me an example. This does seem odd DTS shouldn't care what you define internally in the proc. Is there anything that the proc exposes which changes i.e. Output variables Differing recordset? What fails? With what error? -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:062a01c3d8fe$e87dfed0$a601280a (AT) phx (DOT) gbl... I have a DTS package that is called by a scheduled job. The dts package calls a stored proc. I've noticed that when i amend the stored proc, the next time the job is due to run it fails. If i recreate the package then schedule all is well. Obviously a pain in the bum while i'm testing ... Is there a way around this??? Regards Paul.. . |
#4
| |||
| |||
|
|
Thanks for your response Allan, I've been changing the number of fields returned in my recordset, and playing about with certain formats. The message i've found is basically telling me that a column that i have removed was not found. Presummably the package is storing the details of the procedure rather than just calling the proc from the database!?!?!?! Do these comments help? Paul.. -----Original Message----- Can you give me an example. This does seem odd DTS shouldn't care what you define internally in the proc. Is there anything that the proc exposes which changes i.e. Output variables Differing recordset? What fails? With what error? -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:062a01c3d8fe$e87dfed0$a601280a (AT) phx (DOT) gbl... I have a DTS package that is called by a scheduled job. The dts package calls a stored proc. I've noticed that when i amend the stored proc, the next time the job is due to run it fails. If i recreate the package then schedule all is well. Obviously a pain in the bum while i'm testing ... Is there a way around this??? Regards Paul.. . |
#5
| |||
| |||
|
|
-----Original Message----- Are you using this as the source to a Data Pump? If yes then the rowset returned is used to define the SourceColumns property of the Transformation object. If you remove a column from the expected output the SQL Server DTS does not automatically remap the transformations -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:065e01c3d900$ea009540$a601280a (AT) phx (DOT) gbl... Thanks for your response Allan, I've been changing the number of fields returned in my recordset, and playing about with certain formats. The message i've found is basically telling me that a column that i have removed was not found. Presummably the package is storing the details of the procedure rather than just calling the proc from the database!?!?!?! Do these comments help? Paul.. -----Original Message----- Can you give me an example. This does seem odd DTS shouldn't care what you define internally in the proc. Is there anything that the proc exposes which changes i.e. Output variables Differing recordset? What fails? With what error? -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:062a01c3d8fe$e87dfed0$a601280a (AT) phx (DOT) gbl... I have a DTS package that is called by a scheduled job. The dts package calls a stored proc. I've noticed that when i amend the stored proc, the next time the job is due to run it fails. If i recreate the package then schedule all is well. Obviously a pain in the bum while i'm testing ... Is there a way around this??? Regards Paul.. . . |
#6
| |||
| |||
|
|
Ok, that sounds like my problem... Will have to get my structure correct :-) Is there an easier way of doing this? Thanks paul.. -----Original Message----- Are you using this as the source to a Data Pump? If yes then the rowset returned is used to define the SourceColumns property of the Transformation object. If you remove a column from the expected output the SQL Server DTS does not automatically remap the transformations -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:065e01c3d900$ea009540$a601280a (AT) phx (DOT) gbl... Thanks for your response Allan, I've been changing the number of fields returned in my recordset, and playing about with certain formats. The message i've found is basically telling me that a column that i have removed was not found. Presummably the package is storing the details of the procedure rather than just calling the proc from the database!?!?!?! Do these comments help? Paul.. -----Original Message----- Can you give me an example. This does seem odd DTS shouldn't care what you define internally in the proc. Is there anything that the proc exposes which changes i.e. Output variables Differing recordset? What fails? With what error? -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:062a01c3d8fe$e87dfed0$a601280a (AT) phx (DOT) gbl... I have a DTS package that is called by a scheduled job. The dts package calls a stored proc. I've noticed that when i amend the stored proc, the next time the job is due to run it fails. If i recreate the package then schedule all is well. Obviously a pain in the bum while i'm testing ... Is there a way around this??? Regards Paul.. . . |
#7
| |||
| |||
|
|
-----Original Message----- Not really. You have identified the best solution. If you do not know a structure up front then it is difficult for everything else to adapt as well. -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:07e001c3d90a$aba20bd0$a301280a (AT) phx (DOT) gbl... Ok, that sounds like my problem... Will have to get my structure correct :-) Is there an easier way of doing this? Thanks paul.. -----Original Message----- Are you using this as the source to a Data Pump? If yes then the rowset returned is used to define the SourceColumns property of the Transformation object. If you remove a column from the expected output the SQL Server DTS does not automatically remap the transformations -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:065e01c3d900$ea009540$a601280a (AT) phx (DOT) gbl... Thanks for your response Allan, I've been changing the number of fields returned in my recordset, and playing about with certain formats. The message i've found is basically telling me that a column that i have removed was not found. Presummably the package is storing the details of the procedure rather than just calling the proc from the database!?!?!?! Do these comments help? Paul.. -----Original Message----- Can you give me an example. This does seem odd DTS shouldn't care what you define internally in the proc. Is there anything that the proc exposes which changes i.e. Output variables Differing recordset? What fails? With what error? -- ---------------------------- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.allisonmitchell.com - Expert SQL Server Consultancy. www.SQLDTS.com - The site for all your DTS needs. I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Diggers" <paul.digby (AT) sca (DOT) com> wrote in message news:062a01c3d8fe$e87dfed0$a601280a (AT) phx (DOT) gbl... I have a DTS package that is called by a scheduled job. The dts package calls a stored proc. I've noticed that when i amend the stored proc, the next time the job is due to run it fails. If i recreate the package then schedule all is well. Obviously a pain in the bum while i'm testing ... Is there a way around this??? Regards Paul.. . . . |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.