Hi, I have a best question practice about database design. My idea is to
store business rules information for my customers. Basically, I define a set
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.
John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com
|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=e...lt&qt_s=Search
http://groups.google.com/groups?hl=e...av&qt_s=Search
http://groups.google.com/groups?hl=e...rm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>
|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=e...lt&qt_s=Search
> http://groups.google.com/groups?hl=e...av&qt_s=Search
> http://groups.google.com/groups?hl=e...rm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>
|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter
Showing posts with label basically. Show all posts
Showing posts with label basically. Show all posts
Monday, March 26, 2012
Generic data types in SQL Server
Hi, I have a best question practice about database design. My idea is to
store business rules information for my customers. Basically, I define a se
t
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=...tlt&qt_s=Search
http://groups.google.com/groups?hl=...eav&qt_s=Search
http://groups.google.com/groups?hl=...orm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=...tlt&qt_s=Search
> http://groups.google.com/groups?hl=...eav&qt_s=Search
> http://groups.google.com/groups?hl=...orm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter
store business rules information for my customers. Basically, I define a se
t
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=...tlt&qt_s=Search
http://groups.google.com/groups?hl=...eav&qt_s=Search
http://groups.google.com/groups?hl=...orm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=...tlt&qt_s=Search
> http://groups.google.com/groups?hl=...eav&qt_s=Search
> http://groups.google.com/groups?hl=...orm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter
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.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
>
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
>
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
>
Wednesday, March 21, 2012
Generating report from Code
I need to generate and save a report to disk from a standard Windows
Application without user intervention.
So basically, a user clicks a button, and a report is saved to his c:\ in a
predefined format and with a predefined filename.
Anyone?
J. Jespersen> So basically, a user clicks a button, and a report is saved to his c:\ in
> a
> predefined format and with a predefined filename.
Correction: File should just be saved to the servers c:\|||This code will allow you to generate a report, and create a PDF file from it.
You can tweak the parameters to export the format and type you wish:
public static void deliverReport()
{
MyReportingService.ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Render arguments
byte[] rpt = null;
string reportPath = "/ReportProject1/Report1";
string format = "PDF";
string historyID = null;
string devInfo =@."<DeviceInfo><StartPage>0</StartPage><PageHeight>8.5in</PageHeight><PageWidth>14in</PageWidth></DeviceInfo>";
ParameterValue[] parameters = new ParameterValue[0];
DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string encoding;
string mimeType;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
SessionHeader sh = new SessionHeader();
rs.SessionHeaderValue = sh;
rpt = rs.Render(reportPath, format, historyID, devInfo,
parameters, credentials,
showHideToggle, out encoding, out mimeType, out
reportHistoryParameters, out warnings,
out streamIDs);
FileStream stream = File.Create("test.pdf", rpt.Length);
stream.Write(rpt, 0, rpt.Length);
stream.Close();
}
This URL may help you as well:
http://www.csharphelp.com/archives3/archive545.html
Cheers,
Rich
"Jeppe Dige Jespersen" wrote:
> > So basically, a user clicks a button, and a report is saved to his c:\ in
> > a
> > predefined format and with a predefined filename.
> Correction: File should just be saved to the servers c:\
>
>|||Thank you Rich. Works perfectly! :-)
Jeppe Jespersen
Denmark
> This code will allow you to generate a report, and create a PDF file from
> it.
> You can tweak the parameters to export the format and type you wish:
> public static void deliverReport()
> {
> MyReportingService.ReportingService rs = new
> ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> // Render arguments
> byte[] rpt = null;
> string reportPath = "/ReportProject1/Report1";
> string format = "PDF";
> string historyID = null;
> string devInfo => @."<DeviceInfo><StartPage>0</StartPage><PageHeight>8.5in</PageHeight><PageWidth>14in</PageWidth></DeviceInfo>";
> ParameterValue[] parameters = new ParameterValue[0];
> DataSourceCredentials[] credentials = null;
> string showHideToggle = null;
> string encoding;
> string mimeType;
> Warning[] warnings = null;
> ParameterValue[] reportHistoryParameters = null;
> string[] streamIDs = null;
> SessionHeader sh = new SessionHeader();
> rs.SessionHeaderValue = sh;
> rpt = rs.Render(reportPath, format, historyID, devInfo,
> parameters, credentials,
> showHideToggle, out encoding, out mimeType, out
> reportHistoryParameters, out warnings,
> out streamIDs);
> FileStream stream = File.Create("test.pdf", rpt.Length);
> stream.Write(rpt, 0, rpt.Length);
> stream.Close();
> }
> This URL may help you as well:
> http://www.csharphelp.com/archives3/archive545.html
> Cheers,
> Rich
> "Jeppe Dige Jespersen" wrote:
>> > So basically, a user clicks a button, and a report is saved to his c:\
>> > in
>> > a
>> > predefined format and with a predefined filename.
>> Correction: File should just be saved to the servers c:\
>>
Application without user intervention.
So basically, a user clicks a button, and a report is saved to his c:\ in a
predefined format and with a predefined filename.
Anyone?
J. Jespersen> So basically, a user clicks a button, and a report is saved to his c:\ in
> a
> predefined format and with a predefined filename.
Correction: File should just be saved to the servers c:\|||This code will allow you to generate a report, and create a PDF file from it.
You can tweak the parameters to export the format and type you wish:
public static void deliverReport()
{
MyReportingService.ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Render arguments
byte[] rpt = null;
string reportPath = "/ReportProject1/Report1";
string format = "PDF";
string historyID = null;
string devInfo =@."<DeviceInfo><StartPage>0</StartPage><PageHeight>8.5in</PageHeight><PageWidth>14in</PageWidth></DeviceInfo>";
ParameterValue[] parameters = new ParameterValue[0];
DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string encoding;
string mimeType;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
SessionHeader sh = new SessionHeader();
rs.SessionHeaderValue = sh;
rpt = rs.Render(reportPath, format, historyID, devInfo,
parameters, credentials,
showHideToggle, out encoding, out mimeType, out
reportHistoryParameters, out warnings,
out streamIDs);
FileStream stream = File.Create("test.pdf", rpt.Length);
stream.Write(rpt, 0, rpt.Length);
stream.Close();
}
This URL may help you as well:
http://www.csharphelp.com/archives3/archive545.html
Cheers,
Rich
"Jeppe Dige Jespersen" wrote:
> > So basically, a user clicks a button, and a report is saved to his c:\ in
> > a
> > predefined format and with a predefined filename.
> Correction: File should just be saved to the servers c:\
>
>|||Thank you Rich. Works perfectly! :-)
Jeppe Jespersen
Denmark
> This code will allow you to generate a report, and create a PDF file from
> it.
> You can tweak the parameters to export the format and type you wish:
> public static void deliverReport()
> {
> MyReportingService.ReportingService rs = new
> ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> // Render arguments
> byte[] rpt = null;
> string reportPath = "/ReportProject1/Report1";
> string format = "PDF";
> string historyID = null;
> string devInfo => @."<DeviceInfo><StartPage>0</StartPage><PageHeight>8.5in</PageHeight><PageWidth>14in</PageWidth></DeviceInfo>";
> ParameterValue[] parameters = new ParameterValue[0];
> DataSourceCredentials[] credentials = null;
> string showHideToggle = null;
> string encoding;
> string mimeType;
> Warning[] warnings = null;
> ParameterValue[] reportHistoryParameters = null;
> string[] streamIDs = null;
> SessionHeader sh = new SessionHeader();
> rs.SessionHeaderValue = sh;
> rpt = rs.Render(reportPath, format, historyID, devInfo,
> parameters, credentials,
> showHideToggle, out encoding, out mimeType, out
> reportHistoryParameters, out warnings,
> out streamIDs);
> FileStream stream = File.Create("test.pdf", rpt.Length);
> stream.Write(rpt, 0, rpt.Length);
> stream.Close();
> }
> This URL may help you as well:
> http://www.csharphelp.com/archives3/archive545.html
> Cheers,
> Rich
> "Jeppe Dige Jespersen" wrote:
>> > So basically, a user clicks a button, and a report is saved to his c:\
>> > in
>> > a
>> > predefined format and with a predefined filename.
>> Correction: File should just be saved to the servers c:\
>>
Monday, March 12, 2012
generating a database design from xml
Hi, I'm looking for a way to take an XML feed and basically do what this article describes: http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/(In short, take in XML, generate a database generation script, creating that database, then load the XML data into said database.)But, the above article involves user interaction and I would like this all automated, ie: in .NET 2.0/C#Does anyone know how to do this?Cheers,Matt.
Himatthewwebster, I read your previously post and give some advise there, please check whehter it works for your issue:)
http://forums.asp.net/1266225/ShowThread.aspx
Wednesday, March 7, 2012
generate scripts from management studio
Hello all,
Our database gets replaced every day with new tables and data. Basically
the routine job is to retore database from other source database, in which we
get fresh data and tables. However, it will also wipe out all views,
procedures and functions created by developer. So, every day, our developer
will need to generate the scripts before the database get replaced and rerun
the scripts for all views/procedures/functions. The way we generate the
script is from the SQL Server Manager Studio, the problem is, the developer
will need to click the necessary options for the scripts, for instance, she
needs to reset the dependency as true every time she generates the script,
and in some degree, it is riskey, because if she forgot reset certain options
then it will not generate the correct scripts. Is there a way to set the
options on Server Management Studio permanantly? or any other better ideas to
get the functions/views/procedures scripts?
Thanks,Why would you first drop objects if you then restore the database? In case I misunderstood you:
Write a small app for the script generation. All the script generation liven in the SMO programming
API, and is really easy to use. See http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:1BFD57F7-1C08-484F-9773-216419ECE254@.microsoft.com...
> Hello all,
> Our database gets replaced every day with new tables and data. Basically
> the routine job is to retore database from other source database, in which we
> get fresh data and tables. However, it will also wipe out all views,
> procedures and functions created by developer. So, every day, our developer
> will need to generate the scripts before the database get replaced and rerun
> the scripts for all views/procedures/functions. The way we generate the
> script is from the SQL Server Manager Studio, the problem is, the developer
> will need to click the necessary options for the scripts, for instance, she
> needs to reset the dependency as true every time she generates the script,
> and in some degree, it is riskey, because if she forgot reset certain options
> then it will not generate the correct scripts. Is there a way to set the
> options on Server Management Studio permanantly? or any other better ideas to
> get the functions/views/procedures scripts?
>
> Thanks,|||Hi Jenny
There are several ways you may want to get around this. If your stored
procedures, views and functions and are in a source code control system, they
could be extracted and run against the database. This would be better than
storing the source code on disc and just running each of the files using
SQLCMD. If you wish to execute all file in a give directory with osql with a
command like:
for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
An alternative approach would be to rename the existing database, restore
the backup, then use a SSIS package to copy them.
A third approach would be to use SMO instead of using the batch files or
wizards.
John
"Jenny" wrote:
> Hello all,
> Our database gets replaced every day with new tables and data. Basically
> the routine job is to retore database from other source database, in which we
> get fresh data and tables. However, it will also wipe out all views,
> procedures and functions created by developer. So, every day, our developer
> will need to generate the scripts before the database get replaced and rerun
> the scripts for all views/procedures/functions. The way we generate the
> script is from the SQL Server Manager Studio, the problem is, the developer
> will need to click the necessary options for the scripts, for instance, she
> needs to reset the dependency as true every time she generates the script,
> and in some degree, it is riskey, because if she forgot reset certain options
> then it will not generate the correct scripts. Is there a way to set the
> options on Server Management Studio permanantly? or any other better ideas to
> get the functions/views/procedures scripts?
>
> Thanks,|||Hi John,
I am not quite sure if our developer using source code control system. I
know she is using Visual Studio. So the first way might not work.
The second way you mentioned - rename the database and restore the backup
and then use SSIS package to copy it. It could work tempory, since I will
need to have 2 database capacity. Our database is around 15 G and it is
growing, I will need to watch the space very carefully.
The third one you mentioned maybe will work better, but I don't know how to
use SMO. Do you have a quick way to do the SMO?
Thanks,
Jenny
"John Bell" wrote:
> Hi Jenny
> There are several ways you may want to get around this. If your stored
> procedures, views and functions and are in a source code control system, they
> could be extracted and run against the database. This would be better than
> storing the source code on disc and just running each of the files using
> SQLCMD. If you wish to execute all file in a give directory with osql with a
> command like:
> for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
> An alternative approach would be to rename the existing database, restore
> the backup, then use a SSIS package to copy them.
> A third approach would be to use SMO instead of using the batch files or
> wizards.
> John
> "Jenny" wrote:
> > Hello all,
> >
> > Our database gets replaced every day with new tables and data. Basically
> > the routine job is to retore database from other source database, in which we
> > get fresh data and tables. However, it will also wipe out all views,
> > procedures and functions created by developer. So, every day, our developer
> > will need to generate the scripts before the database get replaced and rerun
> > the scripts for all views/procedures/functions. The way we generate the
> > script is from the SQL Server Manager Studio, the problem is, the developer
> > will need to click the necessary options for the scripts, for instance, she
> > needs to reset the dependency as true every time she generates the script,
> > and in some degree, it is riskey, because if she forgot reset certain options
> > then it will not generate the correct scripts. Is there a way to set the
> > options on Server Management Studio permanantly? or any other better ideas to
> > get the functions/views/procedures scripts?
> >
> >
> >
> > Thanks,|||The source data actually is from a DB2 database. All tables and data are map
to a sql server database (a transition database). Every day the transition
database get backup and then the bakcup is ftp to our server. And then the
retore process get started.
"Tibor Karaszi" wrote:
> Why would you first drop objects if you then restore the database? In case I misunderstood you:
> Write a small app for the script generation. All the script generation liven in the SMO programming
> API, and is really easy to use. See http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:1BFD57F7-1C08-484F-9773-216419ECE254@.microsoft.com...
> > Hello all,
> >
> > Our database gets replaced every day with new tables and data. Basically
> > the routine job is to retore database from other source database, in which we
> > get fresh data and tables. However, it will also wipe out all views,
> > procedures and functions created by developer. So, every day, our developer
> > will need to generate the scripts before the database get replaced and rerun
> > the scripts for all views/procedures/functions. The way we generate the
> > script is from the SQL Server Manager Studio, the problem is, the developer
> > will need to click the necessary options for the scripts, for instance, she
> > needs to reset the dependency as true every time she generates the script,
> > and in some degree, it is riskey, because if she forgot reset certain options
> > then it will not generate the correct scripts. Is there a way to set the
> > options on Server Management Studio permanantly? or any other better ideas to
> > get the functions/views/procedures scripts?
> >
> >
> >
> > Thanks,
>|||Hi Jenny
If you are not using version control, you will not have a process of rolling
back any changes to a known version. Visual Studio has close integration with
source code control systems, particularly Visual Source Safe, therefore it
would not require any effort for you developer to use it other than the
initial configuration.
If you want to limit the space used by the current database, backup the
current database before renaming it, then truncate all the tables and shrink
the data file.
John
John
"Jenny" wrote:
> Hi John,
> I am not quite sure if our developer using source code control system. I
> know she is using Visual Studio. So the first way might not work.
> The second way you mentioned - rename the database and restore the backup
> and then use SSIS package to copy it. It could work tempory, since I will
> need to have 2 database capacity. Our database is around 15 G and it is
> growing, I will need to watch the space very carefully.
> The third one you mentioned maybe will work better, but I don't know how to
> use SMO. Do you have a quick way to do the SMO?
>
> Thanks,
> Jenny
> "John Bell" wrote:
> > Hi Jenny
> >
> > There are several ways you may want to get around this. If your stored
> > procedures, views and functions and are in a source code control system, they
> > could be extracted and run against the database. This would be better than
> > storing the source code on disc and just running each of the files using
> > SQLCMD. If you wish to execute all file in a give directory with osql with a
> > command like:
> > for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
> >
> > An alternative approach would be to rename the existing database, restore
> > the backup, then use a SSIS package to copy them.
> >
> > A third approach would be to use SMO instead of using the batch files or
> > wizards.
> >
> > John
> >
> > "Jenny" wrote:
> >
> > > Hello all,
> > >
> > > Our database gets replaced every day with new tables and data. Basically
> > > the routine job is to retore database from other source database, in which we
> > > get fresh data and tables. However, it will also wipe out all views,
> > > procedures and functions created by developer. So, every day, our developer
> > > will need to generate the scripts before the database get replaced and rerun
> > > the scripts for all views/procedures/functions. The way we generate the
> > > script is from the SQL Server Manager Studio, the problem is, the developer
> > > will need to click the necessary options for the scripts, for instance, she
> > > needs to reset the dependency as true every time she generates the script,
> > > and in some degree, it is riskey, because if she forgot reset certain options
> > > then it will not generate the correct scripts. Is there a way to set the
> > > options on Server Management Studio permanantly? or any other better ideas to
> > > get the functions/views/procedures scripts?
> > >
> > >
> > >
> > > Thanks,
Our database gets replaced every day with new tables and data. Basically
the routine job is to retore database from other source database, in which we
get fresh data and tables. However, it will also wipe out all views,
procedures and functions created by developer. So, every day, our developer
will need to generate the scripts before the database get replaced and rerun
the scripts for all views/procedures/functions. The way we generate the
script is from the SQL Server Manager Studio, the problem is, the developer
will need to click the necessary options for the scripts, for instance, she
needs to reset the dependency as true every time she generates the script,
and in some degree, it is riskey, because if she forgot reset certain options
then it will not generate the correct scripts. Is there a way to set the
options on Server Management Studio permanantly? or any other better ideas to
get the functions/views/procedures scripts?
Thanks,Why would you first drop objects if you then restore the database? In case I misunderstood you:
Write a small app for the script generation. All the script generation liven in the SMO programming
API, and is really easy to use. See http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:1BFD57F7-1C08-484F-9773-216419ECE254@.microsoft.com...
> Hello all,
> Our database gets replaced every day with new tables and data. Basically
> the routine job is to retore database from other source database, in which we
> get fresh data and tables. However, it will also wipe out all views,
> procedures and functions created by developer. So, every day, our developer
> will need to generate the scripts before the database get replaced and rerun
> the scripts for all views/procedures/functions. The way we generate the
> script is from the SQL Server Manager Studio, the problem is, the developer
> will need to click the necessary options for the scripts, for instance, she
> needs to reset the dependency as true every time she generates the script,
> and in some degree, it is riskey, because if she forgot reset certain options
> then it will not generate the correct scripts. Is there a way to set the
> options on Server Management Studio permanantly? or any other better ideas to
> get the functions/views/procedures scripts?
>
> Thanks,|||Hi Jenny
There are several ways you may want to get around this. If your stored
procedures, views and functions and are in a source code control system, they
could be extracted and run against the database. This would be better than
storing the source code on disc and just running each of the files using
SQLCMD. If you wish to execute all file in a give directory with osql with a
command like:
for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
An alternative approach would be to rename the existing database, restore
the backup, then use a SSIS package to copy them.
A third approach would be to use SMO instead of using the batch files or
wizards.
John
"Jenny" wrote:
> Hello all,
> Our database gets replaced every day with new tables and data. Basically
> the routine job is to retore database from other source database, in which we
> get fresh data and tables. However, it will also wipe out all views,
> procedures and functions created by developer. So, every day, our developer
> will need to generate the scripts before the database get replaced and rerun
> the scripts for all views/procedures/functions. The way we generate the
> script is from the SQL Server Manager Studio, the problem is, the developer
> will need to click the necessary options for the scripts, for instance, she
> needs to reset the dependency as true every time she generates the script,
> and in some degree, it is riskey, because if she forgot reset certain options
> then it will not generate the correct scripts. Is there a way to set the
> options on Server Management Studio permanantly? or any other better ideas to
> get the functions/views/procedures scripts?
>
> Thanks,|||Hi John,
I am not quite sure if our developer using source code control system. I
know she is using Visual Studio. So the first way might not work.
The second way you mentioned - rename the database and restore the backup
and then use SSIS package to copy it. It could work tempory, since I will
need to have 2 database capacity. Our database is around 15 G and it is
growing, I will need to watch the space very carefully.
The third one you mentioned maybe will work better, but I don't know how to
use SMO. Do you have a quick way to do the SMO?
Thanks,
Jenny
"John Bell" wrote:
> Hi Jenny
> There are several ways you may want to get around this. If your stored
> procedures, views and functions and are in a source code control system, they
> could be extracted and run against the database. This would be better than
> storing the source code on disc and just running each of the files using
> SQLCMD. If you wish to execute all file in a give directory with osql with a
> command like:
> for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
> An alternative approach would be to rename the existing database, restore
> the backup, then use a SSIS package to copy them.
> A third approach would be to use SMO instead of using the batch files or
> wizards.
> John
> "Jenny" wrote:
> > Hello all,
> >
> > Our database gets replaced every day with new tables and data. Basically
> > the routine job is to retore database from other source database, in which we
> > get fresh data and tables. However, it will also wipe out all views,
> > procedures and functions created by developer. So, every day, our developer
> > will need to generate the scripts before the database get replaced and rerun
> > the scripts for all views/procedures/functions. The way we generate the
> > script is from the SQL Server Manager Studio, the problem is, the developer
> > will need to click the necessary options for the scripts, for instance, she
> > needs to reset the dependency as true every time she generates the script,
> > and in some degree, it is riskey, because if she forgot reset certain options
> > then it will not generate the correct scripts. Is there a way to set the
> > options on Server Management Studio permanantly? or any other better ideas to
> > get the functions/views/procedures scripts?
> >
> >
> >
> > Thanks,|||The source data actually is from a DB2 database. All tables and data are map
to a sql server database (a transition database). Every day the transition
database get backup and then the bakcup is ftp to our server. And then the
retore process get started.
"Tibor Karaszi" wrote:
> Why would you first drop objects if you then restore the database? In case I misunderstood you:
> Write a small app for the script generation. All the script generation liven in the SMO programming
> API, and is really easy to use. See http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:1BFD57F7-1C08-484F-9773-216419ECE254@.microsoft.com...
> > Hello all,
> >
> > Our database gets replaced every day with new tables and data. Basically
> > the routine job is to retore database from other source database, in which we
> > get fresh data and tables. However, it will also wipe out all views,
> > procedures and functions created by developer. So, every day, our developer
> > will need to generate the scripts before the database get replaced and rerun
> > the scripts for all views/procedures/functions. The way we generate the
> > script is from the SQL Server Manager Studio, the problem is, the developer
> > will need to click the necessary options for the scripts, for instance, she
> > needs to reset the dependency as true every time she generates the script,
> > and in some degree, it is riskey, because if she forgot reset certain options
> > then it will not generate the correct scripts. Is there a way to set the
> > options on Server Management Studio permanantly? or any other better ideas to
> > get the functions/views/procedures scripts?
> >
> >
> >
> > Thanks,
>|||Hi Jenny
If you are not using version control, you will not have a process of rolling
back any changes to a known version. Visual Studio has close integration with
source code control systems, particularly Visual Source Safe, therefore it
would not require any effort for you developer to use it other than the
initial configuration.
If you want to limit the space used by the current database, backup the
current database before renaming it, then truncate all the tables and shrink
the data file.
John
John
"Jenny" wrote:
> Hi John,
> I am not quite sure if our developer using source code control system. I
> know she is using Visual Studio. So the first way might not work.
> The second way you mentioned - rename the database and restore the backup
> and then use SSIS package to copy it. It could work tempory, since I will
> need to have 2 database capacity. Our database is around 15 G and it is
> growing, I will need to watch the space very carefully.
> The third one you mentioned maybe will work better, but I don't know how to
> use SMO. Do you have a quick way to do the SMO?
>
> Thanks,
> Jenny
> "John Bell" wrote:
> > Hi Jenny
> >
> > There are several ways you may want to get around this. If your stored
> > procedures, views and functions and are in a source code control system, they
> > could be extracted and run against the database. This would be better than
> > storing the source code on disc and just running each of the files using
> > SQLCMD. If you wish to execute all file in a give directory with osql with a
> > command like:
> > for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
> >
> > An alternative approach would be to rename the existing database, restore
> > the backup, then use a SSIS package to copy them.
> >
> > A third approach would be to use SMO instead of using the batch files or
> > wizards.
> >
> > John
> >
> > "Jenny" wrote:
> >
> > > Hello all,
> > >
> > > Our database gets replaced every day with new tables and data. Basically
> > > the routine job is to retore database from other source database, in which we
> > > get fresh data and tables. However, it will also wipe out all views,
> > > procedures and functions created by developer. So, every day, our developer
> > > will need to generate the scripts before the database get replaced and rerun
> > > the scripts for all views/procedures/functions. The way we generate the
> > > script is from the SQL Server Manager Studio, the problem is, the developer
> > > will need to click the necessary options for the scripts, for instance, she
> > > needs to reset the dependency as true every time she generates the script,
> > > and in some degree, it is riskey, because if she forgot reset certain options
> > > then it will not generate the correct scripts. Is there a way to set the
> > > options on Server Management Studio permanantly? or any other better ideas to
> > > get the functions/views/procedures scripts?
> > >
> > >
> > >
> > > Thanks,
Generate Script Wizard takes FOREVER (SQL 2k5)
I'm trying to use the Generate Script Wizard to generate scripts for a relatively small DB.
I basically follow the same steps I would have in Sql 2k to generate the scripts but in 2k5 this takes FOREVER.
In fact, it's taking so long that I've stopped it every time (never has completed)
I have sp2 installed.
anyone else having this issue or am I an isolated case ?
basically, from what I can tell, the tool is completely unusable.
Greg Jackson
Portland, OR
I have the same problem with a bigger database with about 1000 sp's and 1000 tables
in sql2000 it took about 15 minutes to script out, now it takes over and hour.
Plus if you watch the server it is hammering the server to 30 to 40 percent utilization
I have broken down the scripting to tables, then script views, then script sp'
It appears that if i change to sql2000 compatible scripts it runs some faster
and if you create drops, and rights it can add another 15 to 20 minutes
Generate Script Wizard takes FOREVER (SQL 2k5)
I'm trying to use the Generate Script Wizard to generate scripts for a relatively small DB.
I basically follow the same steps I would have in Sql 2k to generate the scripts but in 2k5 this takes FOREVER.
In fact, it's taking so long that I've stopped it every time (never has completed)
I have sp2 installed.
anyone else having this issue or am I an isolated case ?
basically, from what I can tell, the tool is completely unusable.
Greg Jackson
Portland, OR
I have the same problem with a bigger database with about 1000 sp's and 1000 tables
in sql2000 it took about 15 minutes to script out, now it takes over and hour.
Plus if you watch the server it is hammering the server to 30 to 40 percent utilization
I have broken down the scripting to tables, then script views, then script sp'
It appears that if i change to sql2000 compatible scripts it runs some faster
and if you create drops, and rights it can add another 15 to 20 minutes
Generate script of ALL database objects
I need to be able to programatically generate the CREATE scripts for all objects for a given database. Basically, I need the same results as if I generated the script using SSMS and choosing to script all objects in the selected database.
I'm hoping I could do this using the Scripter w/in the SMO Utility Classes. Can anyone help me out?
Thanks in advance!Hi,
use the transfer method of the scripter:
http://blogs.msdn.com/mwories/articles/smosample_transfer.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Thanks Jens. Just what I was looking for. :-)|||Take a look at this free tool -- it scripts out all database objects in a manner which mimics Visual Studio for Database Professionals [the "Data Dude"]. That is, it creates a separate file for each object in the database in an organized file tree.
http://sourceforge.net/projects/script-sql-db
Richard|||Would anyone happen to know how I could get the end of a batch of Transact-SQL statements; i.e "GO" statements to show up in generated script?
Thanks.|||As you get a stringCollection using the
System.Collections.Specialized.StringCollection s = t.ScriptTransfer(); // Or use ScriptTransfer() if you need to capture the script (without data)
You can simply modify the script on your own.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Well, yes, I see your point, but then wouldn't I have to write a decent amount of conditional code to to determine if I need a "GO" line or not? Assuming I would not want a "GO" statement on each line as I'd like to batch some of the operations.I noticed that the scripter object generates code with the embedded "GO" statements, does the transfer object not provide this capability?
Thanks.|||There is a property in the Transfer object named 'FileName'. When you supply this property with a file path the generated script is output to a text file and the 'GO' statements are in the text file.
Generate script of ALL database objects
I need to be able to programatically generate the CREATE scripts for all objects for a given database. Basically, I need the same results as if I generated the script using SSMS and choosing to script all objects in the selected database.
I'm hoping I could do this using the Scripter w/in the SMO Utility Classes. Can anyone help me out?
Thanks in advance!Hi,
use the transfer method of the scripter:
http://blogs.msdn.com/mwories/articles/smosample_transfer.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||Thanks Jens. Just what I was looking for. :-)|||Take a look at this free tool -- it scripts out all database objects in a manner which mimics Visual Studio for Database Professionals [the "Data Dude"]. That is, it creates a separate file for each object in the database in an organized file tree.
http://sourceforge.net/projects/script-sql-db
Richard|||Would anyone happen to know how I could get the end of a batch of Transact-SQL statements; i.e "GO" statements to show up in generated script?
Thanks.|||As you get a stringCollection using the
System.Collections.Specialized.StringCollection s = t.ScriptTransfer(); // Or use ScriptTransfer() if you need to capture the script (without data)
You can simply modify the script on your own.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Well, yes, I see your point, but then wouldn't I have to write a decent amount of conditional code to to determine if I need a "GO" line or not? Assuming I would not want a "GO" statement on each line as I'd like to batch some of the operations.I noticed that the scripter object generates code with the embedded "GO" statements, does the transfer object not provide this capability?
Thanks.|||There is a property in the Transfer object named 'FileName'. When you supply this property with a file path the generated script is output to a text file and the 'GO' statements are in the text file.
Friday, February 24, 2012
Generate Exchange Task from T-SQL
Our organization would like to add tasks to users' Exchange accounts from our SQL Server using a USP. Basically, we are looking for the same functionality as the xp_sendmail syntax provides, but instead of sending an email to a user, we would like to create a task in the user's Exchange Tasks folder based on the information passed from our database via the USP.
Here is an example:
A client must receive paperwork every 6 months based on a date stored in our SQL database.
2 weeks prior to the date the paperwork is due, a USP would detect that John Doe has upcoming paperwork needed.
Bob is John Doe's sales rep. The USP would create a new task in Bob's Exchange Tasks folder indicating that John Doe's paperwork is due on such-and-such a date, setting reminders, etc.
We are currently running SQL 2000 and Exchange 2003 in an Active Directory environment. Any help or pointers would be greatly appreciated!!
Thank you - Jeremy
Best is to use CDO (Colloborative Data Objects) or Outlook Object Data Model outside of the database. You could write code using sp_OA* SPs but it is not going to be a robust implementation. You can use a SQLAgent job with ActiveXScript task to do the task creation. See below links for more details on how to use CDO and Outlook object model.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/exchanchor/htms/msexchsvr_cdo_top.asp
http://msdn2.microsoft.com/en-us/library/ms268893.aspx
There are lots of KB articles that contains code for using CDO / Outlook Object Model to create messages, appointments, items, tasks etc.
Subscribe to:
Posts (Atom)