I have stored procedure where i am passing two variable
1. @sMonth char(2),
2. @sYear char(4))

I have created a DTS package where i am passing the name
of my stored procedure in execute sql task properties as

exec test_dts ?,?

in parameter i have define two parameter
Name Type Value
1. sMonth string Month(getdate())
2. sYear string Year(getdate())


i am getting the following error
Syntax Error converting datetime from charcter string

Where do you get the error ?
I would guess it is inside the proc and has little to do with DTS but cannot
be sure as I cannot see the proc text.

It suggests you are passing a string value to a datetime field and the
formatting is incorrect.

Does it work in QA with say

Exec test_dts '10','2003'



--

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


"mk" <anonymous (AT) discussions (DOT) microsoft.com> wrote

yes the stored procedure work in QA with
exec test_dts '10','2003'

only the problem is when you execute it through the DTS
gives the folloing error
Syntax Error converting datetime from charcter string

And what is the text of the proc. Can you break it down so you only send
the minimum required to cause a failure ?

i.e

CREATE PROCEDURE A @Month CHAR(2), @Year CHAR(4)
AS
DECLARE @val DATETIME
SET @val = CAST(..................... AS DATETIME)


etc etc

I can then repro the problem and see exactly where things are going wrong.



--

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


"mk" <mustafakh (AT) yahoo (DOT) com> wrote

My stored procedure is as follow

CREATE PROCEDURE test_dts(@sMonth char(2),@sYear char(4))
AS
BEGIN
-- Variable Declaration --
DECLARE @sqlstr varchar(5000)
DECLARE @CurrDate char (20)

SET NOCOUNT ON

SET @CurrDate = rtrim(convert(char(2),@sMonth)) + '/01/'+
convert(char(4),@sYear) + ' 00:00:00'

SELECT * from TravelScheme_user where tdate = ''+ rtrim
(@CurrDate) +''

END
SET NOCOUNT OFF
GO

exec test_dts '06','2003'

it works well when u execute in QA. the table structure is
as follows.

CREATE TABLE [dbo].[TravelScheme_user] (
[Type_Scheme] [char] (25) NULL ,
[Qty] [int] NULL ,
[amount] [float] NULL ,
[TDate] [smalldatetime] NULL
) ON [PRIMARY]
GO


DTS is giving me the error
Syntax error converting Character String to smaldatetime
data type

OK

Tell me that the values you are setting for your GVs in the Designer is not
as typed i.e. MONTH(Getdate())

If it is then you stored Proc is trying to do this

SET @CurrDate = rtrim(convert(char(2),'Month(Getdate())')) + '/01/'+
convert(char(4),'Year(Getdate())') + ' 00:00:00'

SELECT * from TravelScheme_user where tdate = ''+ rtrim
(@CurrDate) +''

Run Profiler at the same time and see what parameters you are passing in

My guess is that it looks like this

exec sp_executesql N'exec test_dts @P1,@P2', N'@P1 varchar(2),@P2
varchar(4)', 'Mo', 'Year'



--

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


"mk" <anonymous (AT) discussions (DOT) microsoft.com> wrote