different schema formats? (SQLXMLBulkLoad and VS2k3)

Howdy,
What is different between the schema requirements for SQLXMLBulkLoad
and the schema generated from XML with Visual Studio?
Specifically I'm trying to bulk load XML output from the gotdotnet
SharePoint Reports utility
(http://www.gotdotnet.com/workspaces...r />
cb29970eb)
with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
Visual Studio 2003.
I've got the BOL examples working, including using SchemaGen to create
the required tables. However running the bulk load with my target XML
doesn't seem to do anything--no tables created, no data loaded, no
error at the command prompt, and no error log generated.
Any tips?
TIA,
Sean G.Hi,
If you send me the schema and the xml file I will take a look of your proble
m.
--
Thanks,
Monica Frintu
"SeanGerman@.gmail.com" wrote:

> Howdy,
> What is different between the schema requirements for SQLXMLBulkLoad
> and the schema generated from XML with Visual Studio?
> Specifically I'm trying to bulk load XML output from the gotdotnet
> SharePoint Reports utility
> (http://www.gotdotnet.com/workspaces.../>
accb29970eb)
> with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
> Visual Studio 2003.
> I've got the BOL examples working, including using SchemaGen to create
> the required tables. However running the bulk load with my target XML
> doesn't seem to do anything--no tables created, no data loaded, no
> error at the command prompt, and no error log generated.
> Any tips?
>
> TIA,
>
> Sean G.
>|||Thanks for the offer Monica. But I've decided to build a schema by
hand rather than fix the one created by Visual Studio.
I don't know enough about XML and XSD to describe all the relevant
differences between the automatically created schema that doesn't work
and the manually created one that is working, but let me know if you're
interested in a comparison between what VS creates and what BulkLoad
wants.
Thanks again,
Sean G.|||Hi Sean,
Yes, it may be useful for us to look at the differences...
Thanks
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136478947.978472.29760@.g47g2000cwa.googlegroups.com...
> Thanks for the offer Monica. But I've decided to build a schema by
> hand rather than fix the one created by Visual Studio.
> I don't know enough about XML and XSD to describe all the relevant
> differences between the automatically created schema that doesn't work
> and the manually created one that is working, but let me know if you're
> interested in a comparison between what VS creates and what BulkLoad
> wants.
> Thanks again,
>
> Sean G.
>|||Michael,
I'll be able to post the schema files this evening, but I can summarize
the differences. To reiterate, the xml is output from the gotdotnet
SharePoint Reports utility.
For the working file I used xsd:schema while the one generated by
Visual Studio 2k3 is xs:schema.
For the working file, elements have the minimum attributes--name,
type(, sql:relation, sql:relationship as needed).
The generated file has minOccurs, maxOccurs.
The working file has the xsd:annotation element and sql:relation and
sql:relationship attributes for SchemaGen=True.
The generated schema does not have this information, but still doesn't
work even after I add it.
There are some elements in the xml I have commented out in the
schema--primarily for dashes in element names--but those kick up an
"invalid value for 'column'" error during BulkLoad.
The thing that puzzles me is BulkLoad with the VS-generated schema
produces no (visible) output--no tables created, no data loaded, and no
error messages.
Other times I've seen this behavior from BulkLoad indicated a logical
error in the schema data. E.g. a well-formatted schema where the
element names don't match the names in the xml; or something labeled an
element in the schema is an attribute in the xml. But I can't find any
such issue in this schema. *shrug*
But like I said, I was able to create a (mostly, except for invalid
column names) working schema, though I wouldn't mind pinning down this
issue for the sake of edukashun. ;)
Sean G.

different schema formats? (SQLXMLBulkLoad and VS2k3)

Howdy,
What is different between the schema requirements for SQLXMLBulkLoad
and the schema generated from XML with Visual Studio?
Specifically I'm trying to bulk load XML output from the gotdotnet
SharePoint Reports utility
(http://www.gotdotnet.com/workspaces/...2-5accb29970eb)
with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
Visual Studio 2003.
I've got the BOL examples working, including using SchemaGen to create
the required tables. However running the bulk load with my target XML
doesn't seem to do anything--no tables created, no data loaded, no
error at the command prompt, and no error log generated.
Any tips?
TIA,
Sean G.
Hi,
If you send me the schema and the xml file I will take a look of your problem.
Thanks,
Monica Frintu
"SeanGerman@.gmail.com" wrote:

> Howdy,
> What is different between the schema requirements for SQLXMLBulkLoad
> and the schema generated from XML with Visual Studio?
> Specifically I'm trying to bulk load XML output from the gotdotnet
> SharePoint Reports utility
> (http://www.gotdotnet.com/workspaces/...2-5accb29970eb)
> with SQLXML 3 sp3 and MS SQL 2000 sp4. The schema was created using
> Visual Studio 2003.
> I've got the BOL examples working, including using SchemaGen to create
> the required tables. However running the bulk load with my target XML
> doesn't seem to do anything--no tables created, no data loaded, no
> error at the command prompt, and no error log generated.
> Any tips?
>
> TIA,
>
> Sean G.
>
|||Thanks for the offer Monica. But I've decided to build a schema by
hand rather than fix the one created by Visual Studio.
I don't know enough about XML and XSD to describe all the relevant
differences between the automatically created schema that doesn't work
and the manually created one that is working, but let me know if you're
interested in a comparison between what VS creates and what BulkLoad
wants.
Thanks again,
Sean G.
|||Hi Sean,
Yes, it may be useful for us to look at the differences...
Thanks
Michael
<SeanGerman@.gmail.com> wrote in message
news:1136478947.978472.29760@.g47g2000cwa.googlegro ups.com...
> Thanks for the offer Monica. But I've decided to build a schema by
> hand rather than fix the one created by Visual Studio.
> I don't know enough about XML and XSD to describe all the relevant
> differences between the automatically created schema that doesn't work
> and the manually created one that is working, but let me know if you're
> interested in a comparison between what VS creates and what BulkLoad
> wants.
> Thanks again,
>
> Sean G.
>
|||Michael,
I'll be able to post the schema files this evening, but I can summarize
the differences. To reiterate, the xml is output from the gotdotnet
SharePoint Reports utility.
For the working file I used xsd:schema while the one generated by
Visual Studio 2k3 is xs:schema.
For the working file, elements have the minimum attributes--name,
type(, sql:relation, sql:relationship as needed).
The generated file has minOccurs, maxOccurs.
The working file has the xsd:annotation element and sql:relation and
sql:relationship attributes for SchemaGen=True.
The generated schema does not have this information, but still doesn't
work even after I add it.
There are some elements in the xml I have commented out in the
schema--primarily for dashes in element names--but those kick up an
"invalid value for 'column'" error during BulkLoad.
The thing that puzzles me is BulkLoad with the VS-generated schema
produces no (visible) output--no tables created, no data loaded, and no
error messages.
Other times I've seen this behavior from BulkLoad indicated a logical
error in the schema data. E.g. a well-formatted schema where the
element names don't match the names in the xml; or something labeled an
element in the schema is an attribute in the xml. But I can't find any
such issue in this schema. *shrug*
But like I said, I was able to create a (mostly, except for invalid
column names) working schema, though I wouldn't mind pinning down this
issue for the sake of edukashun. ;)
Sean G.

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned in result #1?
--
Message posted via http://www.sqlmonster.comPRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via SQLMonster.com" wrote:
> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returned in result #1?
> --
> Message posted via http://www.sqlmonster.com
>|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
--
David Portas
SQL Server MVP
--

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned
in result #1?
Message posted via http://www.droptable.comPRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via droptable.com" wrote:

> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returne
d in result #1?
> --
> Message posted via http://www.droptable.com
>|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
David Portas
SQL Server MVP
--

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned in result #1?
Message posted via http://www.sqlmonster.com
PRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via SQLMonster.com" wrote:

> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returned in result #1?
> --
> Message posted via http://www.sqlmonster.com
>
|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
David Portas
SQL Server MVP

different formats of datetime?

Can somebody explain me why or due to what I have different formats of the same datetime data (SS2005) while opening a table (SS Mangmt Studio) and quering it?

For ex., AdventureWorks database, HumanResources.Department table, ModifiedDate column
1) RighClicking on table in ObjectExplorer (MS SQL Server Management Studio) --> Open Table shows the datetime data like
01.06.1998 0:00:00
2) executing
select ModifiedDate from HumanResources.Department
shows the same as
1998-06-01 00:00:00.000

Guennadi Vanine
PS.
I am trying to resolve the problem described in
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=180740&SiteID=1

Because datetime representation completely defined by client application, not by server storage. In a database this type stored in 8 byte container and its internal format has nothing in common with what you see when you retrieve the data.

About these two different formats... the first one is a typical German or Russian national date representation. In this place SSMS takes into consideration your computer' locale settings. The second one is a default format used in a query results.|||

Hmm, I see. The first one coincides with date in my Windows XP (Russian) though I am surprised that it is typical Russian (I have never seen the dates here used with dots, then Russia is multinational and multicultural federation having various republics)

Anyway, it is useful to remeber since this format from OS is the same (and independent on default language of installed db instance) for db instances with different different languages installed. And the default language/collation cannot be reset but only fresh (re)installed

I am still bewildered what is default language for? This does not have much sense to me especially due to the problem it is causing in SSIS [1], [2]

That one I could overcome only by installing additional db instance with English(US) as default language

[1]
collation or local sensitive settings or other configuration properties
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1566456&SiteID=1&mode=1
[2]
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

Different format in flat file for header and data

Hello all,

Is it possible to have two different formats for the header and the data in a flat file connection?

An example text file would look like this:

Col1,Col2,Col3

abcdefghi12345testtesttesttest

abcdeeeee12333setsetsetsetsets

where the header is delimited and the data is ragged right.

It looks like you should be able to accomplish this from the Flat File Connection Manager Editor interface, but perhaps having different delimiter dropdown boxes for the header and columns can only be used if you are using the Delimited format?

Thanks for any info you can provide!

Jessica

Do you even need the header?|||

No, but it would save a lot of time if we didn't have to type in hundreds of column names

I was more interested in knowing if this functionality is supposed to work and I'm missing some check box... or if it isn't supposed to work.

|||I don't think this is supported currently.

Different Date Formats when using xp_cmdshell

Hi all,
If I use the TSQL command:-
EXEC master.dbo.xp_cmdshell 'dir /a/od c:\*.*'
What determines the date format from of the output? I have 2 PC's (XP and
W2K) both with the same regional settings (DMY), both giving the same date
format output (DMY) for the dir command in a command window, but giving
different date formats with the above command.
I've tried changing the regional settings in the CP and also using the
DATEFORMAT command but the nothing changes the date format of the output.
I want to make sure the command returns the same format regardless of the
database, country or anyother SQL option/setting.
Thanks in advance.
Greg
greghines@.bigfoot.com.NOSPAM
Remove NOSPAM when replyingWho is logged into the machine in each case? What account is SQL Server
running under? What windows user is issuing the command?
The only way you can expect the output to be consistent is to be sure that
regional settings on all machines are identical, as are the regional
settings of all system accounts (e.g. the one SQL Server runs under) and all
accounts that may be logged in at the time the command is run.
A
"Greg Hines" <greghines@.bigfoot.com.NOSPAM> wrote in message
news:_PP9f.162$_j6.6553@.nnrp1.ozemail.com.au...
> Hi all,
> If I use the TSQL command:-
> EXEC master.dbo.xp_cmdshell 'dir /a/od c:\*.*'
> What determines the date format from of the output? I have 2 PC's (XP and
> W2K) both with the same regional settings (DMY), both giving the same date
> format output (DMY) for the dir command in a command window, but giving
> different date formats with the above command.
> I've tried changing the regional settings in the CP and also using the
> DATEFORMAT command but the nothing changes the date format of the output.
> I want to make sure the command returns the same format regardless of the
> database, country or anyother SQL option/setting.
> Thanks in advance.
> Greg
>
> --
> greghines@.bigfoot.com.NOSPAM
> Remove NOSPAM when replying
>|||The only logged in user in both cases is sa.
Account for both is dbo
It's not a windows user it's sa. Both use only SQL Authentication.
As I said the regional settings are the same. Proven by the fact that the
dir command in a command window gives the same date format on both PCs.
Greg
--
greghines@.bigfoot.com.NOSPAM
Remove NOSPAM when replying
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23Zf2Ay3FHA.696@.TK2MSFTNGP09.phx.gbl...
> Who is logged into the machine in each case? What account is SQL Server
> running under? What windows user is issuing the command?
> The only way you can expect the output to be consistent is to be sure that
> regional settings on all machines are identical, as are the regional
> settings of all system accounts (e.g. the one SQL Server runs under) and
all
> accounts that may be logged in at the time the command is run.
> A
>
> "Greg Hines" <greghines@.bigfoot.com.NOSPAM> wrote in message
> news:_PP9f.162$_j6.6553@.nnrp1.ozemail.com.au...
and
date
output.
the
>|||> The only logged in user in both cases is sa.
You can't be logged into Windows as sa. Believe it or not, this user may
have a bearing on how dir returns results.
Also, SQL Server is either running as a specific Windows user, or the "local
system" account. The service itself does not run as dbo or sa -- Windows
has absolutely no idea what those mean.
A