Passing NULL-value into package variables

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

I don't think you can pass nulls through the commandline dtexec or dtexecui simply because a DBNull is an object. One option you can pursue is to pass it using a console app written in VB or C#.

This link shows one of the ways of doing that.|||

Dennis_v_E wrote:

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

Its not possible.

One way around it may be to have a boolean variable called IsDatetimeNull which you set to TRUE or FALSE from the command-line.

Then, you put an expression on your datetime variable which sets it to NULL(DT_DBTIMESTAMP) if IsDatetimeNull==TRUE.

Something like that anyway. You get the idea.

-Jamie

|||

Thanx,

To bad it cannot be done simple. But i get the idea.
Maybe i put a feature request in Connect.

Dennis

sql

Passing DML to From .NET SQLClient?

Hi All,
I am trying to figure out why the following fails and how to fix it:
I have a .NET Client application that passes parameters to stored procedures
and uses dynamic SQL that is passed to the Server. Everything is great. My
problem is if I want to send over to the server several DML in one string.
e.g.
----
USE Master
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_XXXXX' AND type = 'P')
DROP PROC sp_XXXXX'
GO
Create Procedure sp_XXXXX
..
..
..
etc...
GO
----
Passing this type of string to SQL fails. Get error regarding the "GO" key
words, "Create Procedure Must be the first statement, etc...
Is it not possible to pass Multiple DML statements to SQL Server in one
string? If I send each DML statement alone, without the "GO" key word it
does work; but not all together.
Thanks for any insight,
John.GO is not a Transact-SQL statement. It's a batch delimiter used by tools
like OSQL and Query Analyzer. One method to execute scripts containing GOs
in your client app is to parse the script and execute batches individually
when a GO is encountered. See the link below for an example. Another
method is to use SQL-DMO to execute scripts.
[url]http://groups.google.com/group/comp.databases.ms-sqlserver/msg/3e7809e7eeb4cc95[/u
rl]
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <jrugo@.patmedia.net> wrote in message
news:ujWlCQZzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I am trying to figure out why the following fails and how to fix it:
> I have a .NET Client application that passes parameters to stored
> procedures and uses dynamic SQL that is passed to the Server. Everything
> is great. My problem is if I want to send over to the server several DML
> in one string.
> e.g.
> ----
> USE Master
> GO
> IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_XXXXX' AND type = 'P')
> DROP PROC sp_XXXXX'
> GO
> Create Procedure sp_XXXXX
> ..
> ..
> ..
> etc...
> GO
> ----
> Passing this type of string to SQL fails. Get error regarding the "GO"
> key words, "Create Procedure Must be the first statement, etc...
> Is it not possible to pass Multiple DML statements to SQL Server in one
> string? If I send each DML statement alone, without the "GO" key word it
> does work; but not all together.
> Thanks for any insight,
> John.
>sql