I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
>
>
>
>|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>
Showing posts with label project. Show all posts
Showing posts with label project. Show all posts
Monday, March 26, 2012
Generating XML Schema from db tables - including field lengths
I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?
Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>
|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
>
>
|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?
Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>
|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
>
>
|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>
Monday, March 19, 2012
generating crystal report using VB
hi
i am doing a project i VB and SQL Server.....
i want to display a report made in Crystal report when i click the print button of my application......
please send me an example code which i can use to do it...
reply soon
shantanuTake a look at this:
http://support.businessobjects.com/communityCS/TechnicalPapers/scr8_ttxado.pdf.asp
i am doing a project i VB and SQL Server.....
i want to display a report made in Crystal report when i click the print button of my application......
please send me an example code which i can use to do it...
reply soon
shantanuTake a look at this:
http://support.businessobjects.com/communityCS/TechnicalPapers/scr8_ttxado.pdf.asp
Monday, March 12, 2012
Generated Report Model
I'm working on a software project that requires a reporting solution.
Of all the solutions we have looked at RS2005 seems to fit best with
our product, however, I had a few queries.
1. Our data is stored in a non-relation format but for reporting the
end user can select what data the wish to report on and a relational
table model is created. Because the table structure is not known until
this point, is it possible to create a Report Data Model in code so
that we can use the end user report designer.
2. Because of the JIT generation of the relational tables, the table
names are not known until the report is requested, is it possible to
pass the table names into the report as parameters? Is it possible to
use these parameters in th Report Data Model?
3. Is it possible to get column totals and group sub-totals in reports
designed in the Report Builder end user designer?
4. Is it (going to be) possible to design free flow reports in the
Report Builder rather than just table, matrix or chart reports?
Many thanks,
Adrian RitchieHi Adrian,
not knowing anything at all about your project...have you considered
using a separate data warehouse for the reporting solution and
propagating changes from the operational system to the BI System so
that you have access to a relational database for reporting...also,
your reporting and analysis needs are very likely to grow past simple
reporting, and I would think you are building in rather sever
limitations trying to write a JIT style application to generate tables
for reporting....I do not see how it could possibly scale as you must
JIT push data into tables when a report is requested rather than just
reading a table that is already there...'
Peter
www.peternolan.com|||Hi Peter,
I realise that my explaination of our system is rather vague but that
was as much as I could really write without breaching my NDA :(
Having a separate data warehouse sounds like a good idea and I will run
it past some of the other guys here but I fear may not be practical.
The reason the relational model is not generated until reports are
requested is that:
1. The data storage structure is flexible. We ship out a standard
structure but clients can modify and add to it as they see fit (with
appropriate security rights).
2. The data in the system is temporal. The end user can request a
report for any point in time for which there is data in the system.
The point in time that the user selects will determine what database
structure they get (as the structure may change over time).
The second problem may not be a problem at all, it's just that I
haven't thought of another solution for it, so any suggestions will be
greatfully received.
Adrian Ritchie|||Hi Adrian,
I understand the world of NDAs...no problems...
1. Changes to source data structures for applications rolled out is
always a problem but not a hard one to solve...
2. All data structures are 'temporal' it's just that most people don't
know it and think they don't change. They do and the analysis system
must take care of that...
I've done systems easily handling both 1 and 2......so I still think
that unless you have some really good reasons for building the
limitations into your reporting system that you very likely will do
taking a look at something separate would be a good idea...Like I said,
I know nothing about what you are doing...so these are just my
thoughts...
Peter
www.peternolan.com
Of all the solutions we have looked at RS2005 seems to fit best with
our product, however, I had a few queries.
1. Our data is stored in a non-relation format but for reporting the
end user can select what data the wish to report on and a relational
table model is created. Because the table structure is not known until
this point, is it possible to create a Report Data Model in code so
that we can use the end user report designer.
2. Because of the JIT generation of the relational tables, the table
names are not known until the report is requested, is it possible to
pass the table names into the report as parameters? Is it possible to
use these parameters in th Report Data Model?
3. Is it possible to get column totals and group sub-totals in reports
designed in the Report Builder end user designer?
4. Is it (going to be) possible to design free flow reports in the
Report Builder rather than just table, matrix or chart reports?
Many thanks,
Adrian RitchieHi Adrian,
not knowing anything at all about your project...have you considered
using a separate data warehouse for the reporting solution and
propagating changes from the operational system to the BI System so
that you have access to a relational database for reporting...also,
your reporting and analysis needs are very likely to grow past simple
reporting, and I would think you are building in rather sever
limitations trying to write a JIT style application to generate tables
for reporting....I do not see how it could possibly scale as you must
JIT push data into tables when a report is requested rather than just
reading a table that is already there...'
Peter
www.peternolan.com|||Hi Peter,
I realise that my explaination of our system is rather vague but that
was as much as I could really write without breaching my NDA :(
Having a separate data warehouse sounds like a good idea and I will run
it past some of the other guys here but I fear may not be practical.
The reason the relational model is not generated until reports are
requested is that:
1. The data storage structure is flexible. We ship out a standard
structure but clients can modify and add to it as they see fit (with
appropriate security rights).
2. The data in the system is temporal. The end user can request a
report for any point in time for which there is data in the system.
The point in time that the user selects will determine what database
structure they get (as the structure may change over time).
The second problem may not be a problem at all, it's just that I
haven't thought of another solution for it, so any suggestions will be
greatfully received.
Adrian Ritchie|||Hi Adrian,
I understand the world of NDAs...no problems...
1. Changes to source data structures for applications rolled out is
always a problem but not a hard one to solve...
2. All data structures are 'temporal' it's just that most people don't
know it and think they don't change. They do and the analysis system
must take care of that...
I've done systems easily handling both 1 and 2......so I still think
that unless you have some really good reasons for building the
limitations into your reporting system that you very likely will do
taking a look at something separate would be a good idea...Like I said,
I know nothing about what you are doing...so these are just my
thoughts...
Peter
www.peternolan.com
Friday, February 24, 2012
generate attributelist from tables
hi there!
I've been working on a project in ASP.NET and SQLserver 2000.
Since this is actually something for school, I must give a report on my work done here.
What I want to do is make an attributelist of all the tables that are in a database, or one at the time would already be a great help!
So for each table I would need in text format all its column names, types en 'allow null' property, but I just can't find out how or with what to do this.
thanks for your help!
Tombaselect * from INFORMATION_SCHEMA.COLUMNS
I've been working on a project in ASP.NET and SQLserver 2000.
Since this is actually something for school, I must give a report on my work done here.
What I want to do is make an attributelist of all the tables that are in a database, or one at the time would already be a great help!
So for each table I would need in text format all its column names, types en 'allow null' property, but I just can't find out how or with what to do this.
thanks for your help!
Tombaselect * from INFORMATION_SCHEMA.COLUMNS
Subscribe to:
Posts (Atom)