Re: Passing Global Variables, type (date) via DTSRun

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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@xxxxxxxxxxxxxxxx> wrote in message
news:CA685CA2-BA5A-48FE-B708-84D94F677784@xxxxxxxxxxxxxxxx
> 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:
>
>> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> message news:6CEB12A3-7B3F-441F-8ADB-38D105B8925E@xxxxxxxxxxxxxxxx
>> > 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
>>
>>
>>


.

Relevant Pages

  • Re: Passing Global Variables, type (date) via DTSRun
    ... The package called inside this step and the package called from the similar ... >> several DTS packages that have been created by several developers. ... >> a) Use implicit conversion by setting a nvarcharvariable to the ...
    (microsoft.public.sqlserver.dts)
  • Re: Storing object references in hashes
    ... every package), for seemingly little benefit. ... > I've extended the code to list the names sorted on age.) ... It is much better to declare two lexicals, ...
    (comp.lang.perl.misc)
  • Re: file handle
    ... I dont think it should be defined as a signal. ... So you probably want to declare the ... Such procedures can go in a package if you wish. ... DOULOS - Developing Design Know-how ...
    (comp.lang.vhdl)
  • Re: Idiom for a class and an object in Ada
    ... you still have to declare them somewhere. ... declaration in the package body of the package that creates the class? ... > clear to me what you have against access types. ... with things having nothing to do with Ada and I don't see any need to do ...
    (comp.lang.ada)
  • Re: Instantiating private types with discriminants?
    ... I'm still a bit new to Ada terminology - does "making Put a primitive ... Have I got to declare two new packages - one against ... A "primitive operation" of a type T is a subprogram that: ... is declared in the same package specification as T (same declarative ...
    (comp.lang.ada)