Bulk Load and SQL function defaults

Is it possible to include a field default in the schema file that represent
a database function?
For example, all of our SQL tables includes the following fields...
CreateDate datetime NOT NULL DEFAULT (getdate())
CreateUser char (255) NOT NULL DEFAULT (suser_sname())
When creating our schema we tried the following:
<xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
/>
<xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
/>
The "CreateDate" element fails with an "Invalid character value for cast
specification." error, while the second element will insert the string value
'suser_sname()' into the "CreateUser" field.
Thanks in advance
No, this is not possible. The default is an XML schema default clause and
cannot contain an T-SQL expression.
Instead, define a default on the relational table column to which you map
the element and make sure that there is no value added.
Best regards
Michael
"Cipher" <c@.c.com> wrote in message
news:OU8QUY0TEHA.3988@.tk2msftngp13.phx.gbl...
> Is it possible to include a field default in the schema file that
> represent
> a database function?
> For example, all of our SQL tables includes the following fields...
> CreateDate datetime NOT NULL DEFAULT (getdate())
> CreateUser char (255) NOT NULL DEFAULT (suser_sname())
> When creating our schema we tried the following:
> <xsd:element name="CreateDate" sql:datatype="DateTime" default="getdate()"
> />
> <xsd:element name="CreateUser" sql:datatype="Char" default="suser_sname()"
> />
> The "CreateDate" element fails with an "Invalid character value for cast
> specification." error, while the second element will insert the string
> value
> 'suser_sname()' into the "CreateUser" field.
>
> Thanks in advance
>
sql

Bulk Insert with mid-string newlines

Hi.
I have a text file with several fields. One of these sometimes contains
newline characters. Any fields that do include newline characters are
enclosed with double quotes. What Bulk Insert command line do I need to use
to impot the file without it treating the enclosed newline characters as end
of row delimiters?
Example:
a,b,"c/nld",e
becomes
a b c
d e
where it should be
a b c/nd e
Thanks in advance,
Neil JonesTo clarify, I am looking for a commandline paramter that allows me to
designate a string delimiter, so that everything within the delimiters is
treated as a single string.
"NeilDJones" wrote:

> Hi.
> I have a text file with several fields. One of these sometimes contains
> newline characters. Any fields that do include newline characters are
> enclosed with double quotes. What Bulk Insert command line do I need to us
e
> to impot the file without it treating the enclosed newline characters as e
nd
> of row delimiters?
> Example:
> a,b,"c/nld",e
> becomes
> a b c
> d e
> where it should be
> a b c/nd e
> Thanks in advance,
> Neil Jones|||If you have custom formating, you'd want to use format file.
http://msdn.microsoft.com/library/e...pt_bcp_9yat.asp
-oj
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:8EBC7227-CD12-4450-93E1-F94E4D5621C6@.microsoft.com...
> Hi.
> I have a text file with several fields. One of these sometimes contains
> newline characters. Any fields that do include newline characters are
> enclosed with double quotes. What Bulk Insert command line do I need to
> use
> to impot the file without it treating the enclosed newline characters as
> end
> of row delimiters?
> Example:
> a,b,"c/nld",e
> becomes
> a b c
> d e
> where it should be
> a b c/nd e
> Thanks in advance,
> Neil Jones|||Thanks for the reply.
Looking at the information for the format file, I still cannot see how I
specify string delimiters, as I am able to do in Excel or Access. Is this
just something that BULK INSERT doesn't handle properly?
Cheers,
Neil
"oj" wrote:

> If you have custom formating, you'd want to use format file.
> http://msdn.microsoft.com/library/e...pt_bcp_9yat.asp
>
>
> --
> -oj
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:8EBC7227-CD12-4450-93E1-F94E4D5621C6@.microsoft.com...
>
>|||Sorry for the late reply.
With bulk load (bcp/bulk insert), you can define the column and row
delimiter. However, each must be different from each other (i.e. not
possible when column and row delimiter is defined as \r\n).
If excel/access can format it correctly for you, you might want to send the
data through it first before importing into sqlserver.
-oj
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:DE2EC0CB-E11F-4DC5-9496-D03482518603@.microsoft.com...
> Thanks for the reply.
> Looking at the information for the format file, I still cannot see how I
> specify string delimiters, as I am able to do in Excel or Access. Is this
> just something that BULK INSERT doesn't handle properly?
> Cheers,
> Neil
> "oj" wrote:
>|||Thanks.
I think I am puttnig this badly.
There is no problem with row or column delimiters. The column delimiter is a
comma, and the row delimiter is newline.
Some of the columns are strings, and these may or may not contain double
quotes ("). If there are double quotes, then everything inside the quotes is
a single string.
The problem arises when there is a newline character within the double
quote-delimited string, eg "Hello\nWorld", which is the string representing
Hello
World
Ideally, this would import as a single string. Excel and Access both do this
fine, but bulk insert doesn't recognise the string delimiter ", and breaks
the string over two lines.
I am hoping someone out there knows how to specify a string delimiter, but
am thinking that there ain't one.
Cheers,
Neil
"oj" wrote:

> Sorry for the late reply.
> With bulk load (bcp/bulk insert), you can define the column and row
> delimiter. However, each must be different from each other (i.e. not
> possible when column and row delimiter is defined as \r\n).
> If excel/access can format it correctly for you, you might want to send th
e
> data through it first before importing into sqlserver.
> --
> -oj
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:DE2EC0CB-E11F-4DC5-9496-D03482518603@.microsoft.com...
>
>