Dear All,

in our currwent software project we use lots of DTSRun commands to start
several DTS packages that have been created by several developers.

All packages follow some guidelines, including: Never hard-code any
server-names or stored-proc parameters, use global variables instead.

Therefore each package has a global variable name glvDate, which is of type
(date).

Now, doing some code review and integration of jobs into an overnight ETL
workflow, we've discovered a strange issue and I wonder if anybody has had
the same or (better ;-)) could explain it:

Although all DTS packages use the same data type (date) for the variable and
although all jobs (SQL agent jobs) use the same function (user defined) that
returns a date as an integer in format yyyymmdd, we had to use all of the
following procedures to get the DTS packages running by DTSRun commands,
varying on a DTS package by package basis:

a) Use implicit conversion by setting a nvarchar(20) variable to the
functions return value; use type constant 8 (string) in the DTSRun /A
parameter;

b) Use implicit conversion by setting a nvarchar(20) variable to the
functions return value; use type constant 7 (date) in the DTSRun /A parameter;

c) Use explicit conversion to set a nvarchar(20) variable to the functions
return value; use type constant 8 (string) in the DTSRun /A parameter,
passing this variable;

d) Use explicit conversion to set a nvarchar(20) variable to the functions
return value; use type constant 7 (date) in the DTSRun /A parameter, passing
this variable.

In short terms: All four combinations of using either CONVERT or not and
type constants 7 and 8 are used now and each package showed to work with only
a single combination.

The only thing I could guess is that the whole thing might be due to
different language settings on the different developers' machines, but I
couldn't find anything about it: neither in this group nor by web-searching
wih different short descriptions of the problem...

Any comments appreciated.

Thank you

Christian
--
--
Press any key to continue;
press any other key to quit

Did you get any error?

If it is a language thing then you should see something "value out of range
for datetime".

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Christian Kuntz" <ChristianKuntz (AT) discussions (DOT) microsoft.com> wrote in
message news:6CEB12A3-7B3F-441F-8ADB-38D105B8925E (AT) microsoft (DOT) com...

Hello Allan,

first thank you for replying.

In between, while trying to reply to your post, I learned that some code has
changed during my stay in Amsterdam last week:

As of current the problem is difficult to describe, because I've been
surprised by our developers didn't streamline the packages by using a single
calling convention yet, although they pretended they did :-(.

Anyway, if you can still offer assistance or experience, let's start with
one particular problem: We have two T-SQL steps inside a SQLAgent job, each
calling xp_cmdshell with a DTSRun command like the following (Asterisks used
as replacement for real names to protect my innocence):

------------------ CODE SNIPPET START
------------------------------------------

-- Variables:
DECLARE @CmdStr nvarchar(1000)
DECLARE @CobDate nvarchar(20)
DECLARE @Server nvarchar(100)
DECLARE @Database nvarchar(100)
DECLARE @UID nvarchar(100)
DECLARE @PWD nvarchar(100)
DECLARE @DTS nvarchar(100)

-- Initialize Variables
SET @CobDate = dbo.fn_get_cob_date() -- this function returns a datetime
SET @Server = '*********'
SET @Database = '*****'
SET @UID = '*****'
SET @PWD = '*****'
SET @DTS = '*****'

SET @CmdStr = 'DTSRun /S "' + @Server + '" ' +
'/U "' + @UID + '" ' +
'/P "' + @PWD + '" ' +
'/N "' + @DTS + '" ' +
'/A "glvDate":8="' + @CobDate + '' +
'/A "glvServer":8="' + @Server + '" ' +
'/A "glvDatabase":8="' + @Database + '" ' +
'/A "glvUser":8="' + @UID + '" ' +
'/A "glvPwd":8="' + @PWD + '" ' +
'/W "0"'


DECLARE @CmdFailure int
EXEC @CmdFailure = master.dbo.xp_cmdshell @CmdStr
if @CmdFailure <> 0
begin
DECLARE @MsgStr nvarchar(400)
SET @MsgStr = 'xp_cmdshell command [' + @CmdStr + '] had errors'
RAISERROR(@MsgStr, 11, 1)
end

------------------ CODE SNIPPET END ------------------------------------------

The package called inside this step and the package called from the similar
(only DTS Package name is different) subsequent step each have a global
variable glvDate of type STRING (different from what I wrote originally, due
to one developer having other weird issues only with packages containing a GV
of type date: any package he changed could not be opened by anybody else
until he removed all date type GVs ?!?)

Now the problem is:

When you change the line { '/A "glvDate":8="' + @CobDate + '' + } to use a
7 instead of the 8, the step fails. Of course you might claim I shouldn't do
so, but this is only a simulation of other jobs' behaviour and if I change
the call for the (remember: similar) second step to use 7, it runs without
any errors.

The error message is not very meaningful (at least to me), but we included
output of the statement we generated to the error message as yozu can see in
the above script. So here goes the result from step history:

--------------------------------------------------------------------------
Executed as user: AD\_sql-febris-dev_fft. xp_cmdshell command [DTSRun /S
"*****" /U "*****" /P "*****" /N "*****" /A "glvDate":7="Mar 30 2005
12:00AM/A "glvServer":8="*****" /A "glvDatabase":8="*****" /A
"glvUser":8="*****" /A "glvPwd":8="*****" /W "0"] had errors [SQLSTATE 42000]
(Error 50000). The step failed.
--------------------------------------------------------------------------

Again: This is a somehow "constructed" example of part of our troubles, so
although any comments are still appreciated an answer saying "Don't use :7="
wouldn't help.

Cheers

Christian
--
Press any key to continue;
press any other key to quit


"Allan Mitchell" wrote:

You rely on an implicit conversion from date to string -

'/A "glvDate":8="' + @CobDate + '' +

That looks like trouble as the T-SQL date time settings may not be the same
as the xp_cmdshell context, and either way the conversion may be ambiguous
when interpreted bh DTRUN or inside the package.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Christian Kuntz" <ChristianKuntz (AT) community (DOT) nospam> wrote

Hello Darren,

"Darren Green" wrote:

First I agree, that these implicit conversions are somehow weird and that we
should get rid of them. But I just decided not to "overload" the group with
all problems we discovered. Otherwise I could provide other examples where we
use CONVERT - sometimes with format 112 (ISO) and sometimes with 103 and
produce errors again when changing either call...

Additionally I should obviously clarify that
a) all packages and jobs are executed on the same server. I am unhappy about
that, but we have no local SQL installations for developers

b) The project customer uses an automated software deployment solution,
therefore all developers' machines have the same version of SQL Client Tools
and the same (english) language settings; the server is english Win 2003 with
english SQL 2000, SP3a

Patches (e.g. SQL SP4) come late because of testing/packaging requirements,
but are also rolled out synchronously.

So what I get from your answer is that implicit conversion is the cause for
that particular problem and that we shouldn't use it. Although I totally
agree with you, I cannot stop wondering, why this happens to be different
behaviour in two very similar steps of the same job, which are calling two
very similar DTS packages.
Because of that, I would believe that both calls are using exactly the same
context.

So, do I now have to assume, DTSRun's behaviour on implicit conversions is
non-deterministic? ;-)

Again: Your answer perfectly points out the way how we should improve code
quality, but additionally - although I don't get paid for that ;-) - I would
still love to understand what's going on here...

Cheers

Christian
--
Press any key to continue;
press any other key to quit