Failed to script out Dropping Default Constraint script for table columns

I used the following code to script out Creating default constraints. It works fine. But if I change so.ScriptDrops = true to script Dropping default constraints, it always fails with

Exception: {"Script failed for DefaultConstraint 'DF_Employee_VacationHours'. "}

InnerException: {"Object reference not set to an instance of an object."}

Can anyone shred some light on it? Thanks in advance.

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Text;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

namespace Test2

{

class Program

{

static void Main(string[] args)

{

Server s = new Server(".");

StringCollection sc;

ScriptingOptions so=new ScriptingOptions();

so.ScriptDrops = false;

s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table t in s.Databases["Adventureworks"].Tables)

{

foreach (Column c in t.Columns)

{

if (c.DefaultConstraint!=null)

{

Console.WriteLine(string.Format("{0},{1}", t.Name, c.Name));

sc=c.DefaultConstraint.Script(so);

foreach (string st in sc)

{

Console.WriteLine(string.Format("{0}", st));

}

sc = null;

}

}

}

return;

}

}

}

Hi,

Thank you for reporting this issue. This is a defect in SMO, and we're filing a bug report. We will try to fix it for the next release of the product.

Factless Fact Table

I have a table that stores all instances of when a company restates their finances. There are a number of other columns that have boolean data types in which I can use to filter. Since the records in this table are based on an event, there really isn't "fact" or numerical data.

When I build my cube in AS2005 using VS2005 and select this table as the fact table, I can't seem to get the other attributes (boolean fields) to appear as filters. Do I have to create a dimension using this table too?

For example, one report would be to display all restatements that had a negative impact (as opposed to a positive impact).

Do I have to create a dimension using this table too?

Yes (well, I guess so)
In my case I have to use it too, and I use it in a "WHERE" clause.
Kinda like:
WHERE [Blah].[True]
or something like that :-)

Extracting XML data to columns during query

I'm working with an inherited SQL 2000 database (now moved to 2005)
that stores strings of XML in a text column. I'd like to avoid
changing the schema right now. Each row's XML data looks something
like:
<root><element id='5' name='bob' message='hello' /></root>
The exact attributes in the inner element are unknown, but what I
would like to be able to do is return them as columns in a query such
that I would get:
id name message
-- -- --
5 bob hello
Any thoughts?I was just now experimenting with something similar. Most of this is right
out of the MSDN help. This is reading from an external XML file.
-- create tables for later population using OPENXML.
create table Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
go
create table Orders( CustomerID varchar(20), OrderDate datetime)
go
declare @.xmlDocument xml
select @.xmlDocument = cast (x as xml)
from OpenRowset (bulk 'P:\SQL scripts\Examples\XML\SourceOfXml.xml',
single_blob) R (x)
select @.xmlDocument
-- Contents of the source XML file.
--
-- <?xml version="1.0" encoding="windows-1252" ?>
-- <ROOT>
-- <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
-- <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
-- <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
-- </Customers>
-- <Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
-- </Customers>
-- </ROOT>
declare @.docHandle int
exec sp_xml_preparedocument @.docHandle output, @.xmlDocument
-- Use OpenXML to provide rowset consisting of customer data.
insert Customers
select *
from OpenXML (@.docHandle, N'/ROOT/Customers')
with Customers
-- Use OpenXML to provide rowset consisting of order data.
insert Orders
select *
from OpenXML (@.docHandle, N'//Orders')
with Orders
-- Using OpenXML in a SELECT statement.
select *
from OpenXML (@.docHandle, N'/ROOT/Customers/Orders')
with (CustomerID nchar (5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
exec sp_xml_removedocument @.docHandle
/*
drop table Customers
drop table Orders
*/
"Brian Vallelunga" wrote:

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> <root><element id='5' name='bob' message='hello' /></root>
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
> id name message
> -- -- --
> 5 bob hello
>
> Any thoughts?
>|||Hello Brian,

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
Theres lots of ways of doing this is the number of attributes are know, but
when they aren't, your going to have a hard time shaping them to a meaningfu
l
table. So whate exactly do you mean by "attributes are unknown?"
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||On Apr 3, 11:17 pm, Kent Tegels <kteg...@.develop.com> wrote:
> Hello Brian,
>
> Theres lots of ways of doing this is the number of attributes are know, bu
t
> when they aren't, your going to have a hard time shaping them to a meaning
ful
> table. So whate exactly do you mean by "attributes are unknown?"
> Thanks!
> Kent Tegels
> DevelopMentorhttp://staff.develop.com/ktegels/
Well, the xml data holds responses to online forms. Each form may have
different fields and each form field results in a single attribute key/
value pair. If a form has a first name and last name, the attributes
for these two would show up in the XML.
Obviously this makes it a bit more difficult than if the values were
known. However, for this, we can assume that any given set of data
pulled will be for one particular form, and will thus have the same
attributes in the XML data. I may just have to do this on the client
end, but thought I'd see if a SQL method was available.

Extracting XML data to columns during query

I'm working with an inherited SQL 2000 database (now moved to 2005)
that stores strings of XML in a text column. I'd like to avoid
changing the schema right now. Each row's XML data looks something
like:
<root><element id='5' name='bob' message='hello' /></root>
The exact attributes in the inner element are unknown, but what I
would like to be able to do is return them as columns in a query such
that I would get:
id name message
-- -- --
5 bob hello
Any thoughts?
I was just now experimenting with something similar. Most of this is right
out of the MSDN help. This is reading from an external XML file.
-- create tables for later population using OPENXML.
create table Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
go
create table Orders( CustomerID varchar(20), OrderDate datetime)
go
declare @.xmlDocument xml
select @.xmlDocument = cast (x as xml)
from OpenRowset (bulk 'P:\SQL scripts\Examples\XML\SourceOfXml.xml',
single_blob) R (x)
select @.xmlDocument
-- Contents of the source XML file.
-- <?xml version="1.0" encoding="windows-1252" ?>
-- <ROOT>
-- <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
-- <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
-- <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
-- </Customers>
-- <Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
-- </Customers>
-- </ROOT>
declare @.docHandle int
exec sp_xml_preparedocument @.docHandle output, @.xmlDocument
-- Use OpenXML to provide rowset consisting of customer data.
insert Customers
select *
from OpenXML (@.docHandle, N'/ROOT/Customers')
with Customers
-- Use OpenXML to provide rowset consisting of order data.
insert Orders
select *
from OpenXML (@.docHandle, N'//Orders')
with Orders
-- Using OpenXML in a SELECT statement.
select *
from OpenXML (@.docHandle, N'/ROOT/Customers/Orders')
with (CustomerID nchar (5) '../@.CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
exec sp_xml_removedocument @.docHandle
/*
drop table Customers
drop table Orders
*/
"Brian Vallelunga" wrote:

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> <root><element id='5' name='bob' message='hello' /></root>
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
> id name message
> -- -- --
> 5 bob hello
>
> Any thoughts?
>
|||Hello Brian,

> I'm working with an inherited SQL 2000 database (now moved to 2005)
> that stores strings of XML in a text column. I'd like to avoid
> changing the schema right now. Each row's XML data looks something
> like:
> The exact attributes in the inner element are unknown, but what I
> would like to be able to do is return them as columns in a query such
> that I would get:
Theres lots of ways of doing this is the number of attributes are know, but
when they aren't, your going to have a hard time shaping them to a meaningful
table. So whate exactly do you mean by "attributes are unknown?"
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||On Apr 3, 11:17 pm, Kent Tegels <kteg...@.develop.com> wrote:
> Hello Brian,
>
> Theres lots of ways of doing this is the number of attributes are know, but
> when they aren't, your going to have a hard time shaping them to a meaningful
> table. So whate exactly do you mean by "attributes are unknown?"
> Thanks!
> Kent Tegels
> DevelopMentorhttp://staff.develop.com/ktegels/
Well, the xml data holds responses to online forms. Each form may have
different fields and each form field results in a single attribute key/
value pair. If a form has a first name and last name, the attributes
for these two would show up in the XML.
Obviously this makes it a bit more difficult than if the values were
known. However, for this, we can assume that any given set of data
pulled will be for one particular form, and will thus have the same
attributes in the XML data. I may just have to do this on the client
end, but thought I'd see if a SQL method was available.

Extracting XML data from SQL 2000 to SQL 2005 database table.

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

VDeevi wrote:

Hi,

We need to extract data from XML that is contained in TEXT columns in Sql Server 2000 database table. Can this be done easily with SSIS (NB we cannot use schemas easily with our data)? Please kindly provide us the solution in order to resolve this problem.

Thanks

What methods have you tried so far and why do they fail?

-Jamie|||Hi Jamie,

Thanks for your reply, We tried with OPENXML, sp_xml_prepareddocument etc. but they are all round about things we want to know is there any simple technique in SSIS in order to retrieve the data from XML stored in SQL Server 2000 TEXT field.

Thanks,|||I've just had a go at this...

When you select the data a TEXT field appears in the SSIS pipeline as a DT_TEXT. To insert it into a XML field use a derived column transform to convert it to DT_NTEXT.
If you don't like DT_NTEXTs in your pipeline then convert it to DT_WSTR and then to DT_STR which can also be inserted into an XML field.

So yes, SSIS can do this very very well.

-Jamie