How to exclude the collation property when scripting a table in SQL Server 2005

In Sequel Server 2000 when I used to script the creation of a table, it was
possible to go to the scripting options in SQL Query Analyser and switch off
the Include Collation statements.
However, I cannot seem to find this setting in SQL Server 2005 - can anyone
point me in the right direction as I do not want to include these statements
when I create a CREATE TABLE script.
Thanks in advance
AndyHi Andrew
If you use the scripting wizard there are configurable options that by
default do not include collations. Try right clicking on the management
branch rather than the table and choose the General Scripts option.
John
"Andrew Houghton" wrote:
> In Sequel Server 2000 when I used to script the creation of a table, it was
> possible to go to the scripting options in SQL Query Analyser and switch off
> the Include Collation statements.
> However, I cannot seem to find this setting in SQL Server 2005 - can anyone
> point me in the right direction as I do not want to include these statements
> when I create a CREATE TABLE script.
> Thanks in advance
> Andy
>
>

How to edit a custom property on an external metadata column

I am using external metadata columns in a destination data component to match sharepoint fields to input columns. I want to add a flag that the user can set to specify if a column is a lookup column (indicates that the component should look for a list item with the specified input column value, and do an update if the item already exists). I added a custom boolean property to the medata column, but the property editor does not allow me to edit the column. It appears to be enabled, but when I choose a value other than the default, the editor immediately replaces the value I chose with the default.

How do I set the property so that it is editable in the property editor? Here is the code that I am using to create the property:

private void AddLookupProperty(IDTSExternalMetadataColumn90 externalColumn)

{

IDTSCustomProperty90 lookupProperty = null;

try

{

//Check to see if the property exists. If it does, don't do anything. If it doesn't, an exception will be thrown

lookupProperty = externalColumn.CustomPropertyCollection[LOOKUP_PROPERTY_NAME];

}

catch (System.Runtime.InteropServices.COMException ex)

{

//This is the exception that is thrown if the property does not exist

if (ex.ErrorCode == -1073676279)

{

lookupProperty = externalColumn.CustomPropertyCollection.New();

lookupProperty.Description = "When saving data, look for a row with this field, and if found, do an update instead of an insert.";

lookupProperty.Value = false;

lookupProperty.Name = LOOKUP_PROPERTY_NAME;

}

//Something unexpected happened

else

{

throw;

}

}

}

External metadata columns are not supposed to have editable properties. I do not completely understand your scenario. Why would you allow users of your component to be able to change this property?

Could you possibly put this property on an associated input column?

Thanks.

|||

It doesn't make sense in the input column, unfortunately. It's basically a way to create sort of a foreign key relationship to the destination, without actually creating a foreign key. When you save data to the destination, I want to check any columns in the destination that are marked as lookup columns, then if there are rows in the destination that match the value in the input buffer, I perform an update on those rows, otherwise I do an insert.

As a last resort, I was thinking that it may be possible to create another input to the destination that contained a list of input columns. Using variables may be another way to do this.

It would be cool if I could create a multi-choice variable at design time from the destination, but I don't think that is possible, so I may must use a semi-colon delimited list.

Eventually, I may need to do some custom UI to make this all easier, I suppose

|||

I see now why you need to edit external columns. You may want to submit request for this feature on the product feedback site or in the Kirk's thread on top of this forum.

For now, I would rather have a single custom property on the single input with delimited list of input columns than play with two inputs. The second input might bring you some additional headache.

Thanks.

|||

This is exactly what i needed too... I need to map an external input and a inside input that comes from an inside especified connection and table...

It's really important for me to have this done... If anyone have any idea how to add a custom input based not on a outside path but from an inside connection that would be great... using that i would be able to virtually map the lookup columns...

Best Regards,

How to edit a custom property on an external metadata column

I am using external metadata columns in a destination data component to match sharepoint fields to input columns. I want to add a flag that the user can set to specify if a column is a lookup column (indicates that the component should look for a list item with the specified input column value, and do an update if the item already exists). I added a custom boolean property to the medata column, but the property editor does not allow me to edit the column. It appears to be enabled, but when I choose a value other than the default, the editor immediately replaces the value I chose with the default.

How do I set the property so that it is editable in the property editor? Here is the code that I am using to create the property:

private void AddLookupProperty(IDTSExternalMetadataColumn90 externalColumn)

{

IDTSCustomProperty90 lookupProperty = null;

try

{

//Check to see if the property exists. If it does, don't do anything. If it doesn't, an exception will be thrown

lookupProperty = externalColumn.CustomPropertyCollection[LOOKUP_PROPERTY_NAME];

}

catch (System.Runtime.InteropServices.COMException ex)

{

//This is the exception that is thrown if the property does not exist

if (ex.ErrorCode == -1073676279)

{

lookupProperty = externalColumn.CustomPropertyCollection.New();

lookupProperty.Description = "When saving data, look for a row with this field, and if found, do an update instead of an insert.";

lookupProperty.Value = false;

lookupProperty.Name = LOOKUP_PROPERTY_NAME;

}

//Something unexpected happened

else

{

throw;

}

}

}

External metadata columns are not supposed to have editable properties. I do not completely understand your scenario. Why would you allow users of your component to be able to change this property?

Could you possibly put this property on an associated input column?

Thanks.

|||

It doesn't make sense in the input column, unfortunately. It's basically a way to create sort of a foreign key relationship to the destination, without actually creating a foreign key. When you save data to the destination, I want to check any columns in the destination that are marked as lookup columns, then if there are rows in the destination that match the value in the input buffer, I perform an update on those rows, otherwise I do an insert.

As a last resort, I was thinking that it may be possible to create another input to the destination that contained a list of input columns. Using variables may be another way to do this.

It would be cool if I could create a multi-choice variable at design time from the destination, but I don't think that is possible, so I may must use a semi-colon delimited list.

Eventually, I may need to do some custom UI to make this all easier, I suppose

|||

I see now why you need to edit external columns. You may want to submit request for this feature on the product feedback site or in the Kirk's thread on top of this forum.

For now, I would rather have a single custom property on the single input with delimited list of input columns than play with two inputs. The second input might bring you some additional headache.

Thanks.

|||

This is exactly what i needed too... I need to map an external input and a inside input that comes from an inside especified connection and table...

It's really important for me to have this done... If anyone have any idea how to add a custom input based not on a outside path but from an inside connection that would be great... using that i would be able to virtually map the lookup columns...

Best Regards,

How to dynamically set the AllMemberName property?

Hello

Is it possible to dynamically set the AllMemberName resp. the AttributeAllMemberName property? Or isn't there a way to bind this property to a table column or define an expression for that property?

Kind regards

Any ideas anyone? Or is this functionality just not implemented?

Kind regards

how to drop the identity property ?

We have a table that has an identity property associated to a column ? How
can we drop it ?
Disclaimer: Get a good backup before you start mucking around with the
schema.
Probably have to create a new INT column, UPDATE it:
UPDATE Table1
SET NewCol = OldIdentityCol
Then use ALTER TABLE to drop OldIdentityCol. You'll also need to Drop and
Rebuild any constraints, etc.
If you use EM to change the Identity property of the table, it does
something similar - except that it creates the entire table again and should
preserve any constraints for you.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>
|||Hi,
There is no direct command in SQL Server to drop the Identity property in a
table. You may need to drop and recreate the table with no identity.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>

how to drop the identity property ?

We have a table that has an identity property associated to a column ? How
can we drop it ?Disclaimer: Get a good backup before you start mucking around with the
schema.
Probably have to create a new INT column, UPDATE it:
UPDATE Table1
SET NewCol = OldIdentityCol
Then use ALTER TABLE to drop OldIdentityCol. You'll also need to Drop and
Rebuild any constraints, etc.
If you use EM to change the Identity property of the table, it does
something similar - except that it creates the entire table again and should
preserve any constraints for you.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>|||Hi,
There is no direct command in SQL Server to drop the Identity property in a
table. You may need to drop and recreate the table with no identity.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>

how to drop the identity property ?

We have a table that has an identity property associated to a column ? How
can we drop it ?Disclaimer: Get a good backup before you start mucking around with the
schema.
Probably have to create a new INT column, UPDATE it:
UPDATE Table1
SET NewCol = OldIdentityCol
Then use ALTER TABLE to drop OldIdentityCol. You'll also need to Drop and
Rebuild any constraints, etc.
If you use EM to change the Identity property of the table, it does
something similar - except that it creates the entire table again and should
preserve any constraints for you.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>|||Hi,
There is no direct command in SQL Server to drop the Identity property in a
table. You may need to drop and recreate the table with no identity.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>

how to do Alternative color row?

Does anyone know how to do alternative color row dynamically on reports? I
can do that in ASP.NET DataGrid.. but can't seem to find a property on
reporting server..
Simon
Zean,
See:
http://www.sqlservercentral.com/colu...formatting.asp
HTH
Jerry
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:7p6dnQpaQ9zGz8TeRVn-hQ@.rogers.com...
> Does anyone know how to do alternative color row dynamically on reports?
> I can do that in ASP.NET DataGrid.. but can't seem to find a property on
> reporting server..
> Simon
>

how to do Alternative color row?

Does anyone know how to do alternative color row dynamically on reports? I
can do that in ASP.NET DataGrid.. but can't seem to find a property on
reporting server..
SimonZean,
See:
http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp
HTH
Jerry
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:7p6dnQpaQ9zGz8TeRVn-hQ@.rogers.com...
> Does anyone know how to do alternative color row dynamically on reports?
> I can do that in ASP.NET DataGrid.. but can't seem to find a property on
> reporting server..
> Simon
>

how to do Alternative color row?

Does anyone know how to do alternative color row dynamically on reports? I
can do that in ASP.NET DataGrid.. but can't seem to find a property on
reporting server..
SimonZean,
See:
http://www.sqlservercentral.com/col.../>
matting.asp
HTH
Jerry
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:7p6dnQpaQ9zGz8TeRVn-hQ@.rogers.com...
> Does anyone know how to do alternative color row dynamically on reports?
> I can do that in ASP.NET DataGrid.. but can't seem to find a property on
> reporting server..
> Simon
>