Bug? Column Default Disappears when Reinitializing Replication

Hi Gurus,
I have a default on a column that disappears every time I reinitialize
replication. This is transactional replication to an updatable subscriber on
SQL Server 2005. Yes, replication of defaults is turned on, and *most* of my
column defaults do, in fact, replicate to the subscriber. The ones that fail
are distinctive in that they reference a user-defined function. And yes,
that UDF and all its internal dependencies are present on the subscriber.
(All the defaults that successfully replicate are either literal values or
else reference *intrinsic* functions, e.g. GETDATE.)
Any idea whether this is a bug? Or what? Any help would be *VERY*
appreciated!
Thanks!
Jason Fisher
Did you replicate the UDFs referenced by the defaults as well, would be
great if you can post the history of the snapshot agent as well. Thanks
much.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uaU5n8waHHA.4396@.TK2MSFTNGP06.phx.gbl...
> Hi Gurus,
> I have a default on a column that disappears every time I reinitialize
> replication. This is transactional replication to an updatable subscriber
> on SQL Server 2005. Yes, replication of defaults is turned on, and *most*
> of my column defaults do, in fact, replicate to the subscriber. The ones
> that fail are distinctive in that they reference a user-defined function.
> And yes, that UDF and all its internal dependencies are present on the
> subscriber. (All the defaults that successfully replicate are either
> literal values or else reference *intrinsic* functions, e.g. GETDATE.)
> Any idea whether this is a bug? Or what? Any help would be *VERY*
> appreciated!
> Thanks!
> Jason Fisher
>
|||Raymond,
Thanks for the reply. The UDF is not being *replicated* -- currently, we
aren't replicating any of the code objects (e.g., stored procedures,
functions, or triggers) -- but it was copied to the subscriber database
manually (ahead of time). It did exist there at the time replication was
initialized. But does it have to be explicitly *included* in the replication
articles? I wouldn't have thought so.
Jason
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
news:ew9MKFxaHHA.1400@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Did you replicate the UDFs referenced by the defaults as well, would be
> great if you can post the history of the snapshot agent as well. Thanks
> much.
> -Raymond
> "Jason Fisher" <j.fisher@.earthlink.net> wrote in message
> news:uaU5n8waHHA.4396@.TK2MSFTNGP06.phx.gbl...
|||The problem is that we can't just assume that you have the UDFs referenced
by the defaults\check constraints to be at the subscriber ahead of time so
you have two options:
1) Explicitly include the UDFs referenced by the defaults in your
publication
2) Put the defaults referencing the unpublished UDFs in a post-snapshot
script, this basically means you know better than we do what you have at
your subscriber (After all, you did manually create the UDFs at the
subscriber right?)
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:OsG3BaxaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Raymond,
> Thanks for the reply. The UDF is not being *replicated* -- currently, we
> aren't replicating any of the code objects (e.g., stored procedures,
> functions, or triggers) -- but it was copied to the subscriber database
> manually (ahead of time). It did exist there at the time replication was
> initialized. But does it have to be explicitly *included* in the
> replication articles? I wouldn't have thought so.
> Jason
> "Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message
> news:ew9MKFxaHHA.1400@.TK2MSFTNGP06.phx.gbl...
>
|||Raymond,

> The problem is that we can't just assume that you have the UDFs referenced
> by the defaults\check constraints to be at the subscriber ahead of time so
> you have two options:
Hmm. But wouldn't it make more sense to replicate the default and then throw
an error later if the referenced UDF is not there on the subscriber, rather
than to simply fail silently during replication? I would always rather have
a noisy, trappable error later on than a silent, untrappable "error" earlier
on. Oh well ...
Regarding your recommendations:

> 1) Explicitly include the UDFs referenced by the defaults in your
> publication
I'm trying this now, but it doesn't look like a good solution in our
particular case because we have tables already in the subscriber database
(not being replicated from the publisher) that also reference the same UDF.
This means that what I'm seeing is error after error in the replication
monitor as it tries and fails to drop the UDF.

> 2) Put the defaults referencing the unpublished UDFs in a post-snapshot
> script, this basically means you know better than we do what you have at
> your subscriber (After all, you did manually create the UDFs at the
> subscriber right?)
I will think about trying this next, although it's not ideal because a) it's
manual, b) the tables in the script have to be updated / maintained as / if
our business needs change. In other words: again, it's manual.
Any other ideas? And thanks very much for the assistance, too, by the way!

Jason
|||> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
I believe the snapshot agent did print out messages explaining that a
default\check constraint is not scripted because it references udf not in
the publication; and believe me when I say some folks will complain the
other way if the snapshot fails to apply because we "should know better" not
to script a default constraint that is referencing something that they
"explicitly" exclude from the publication. The perfect solution will be for
us to divine what the user really wants but we are not there yet and will
not likely be there in the near future.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uobv$TyaHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Raymond,
>
> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
> Regarding your recommendations:
>
> I'm trying this now, but it doesn't look like a good solution in our
> particular case because we have tables already in the subscriber database
> (not being replicated from the publisher) that also reference the same
> UDF. This means that what I'm seeing is error after error in the
> replication monitor as it tries and fails to drop the UDF.
>
> I will think about trying this next, although it's not ideal because a)
> it's manual, b) the tables in the script have to be updated / maintained
> as / if our business needs change. In other words: again, it's manual.
> Any other ideas? And thanks very much for the assistance, too, by the way!
>
> Jason
>
|||Now that I have thought about your case some more, you can publish the UDFs
referenced by the default constraint while making sure that the pre-creation
command for the UDF articles is 'none' instead of 'drop'. This will avoid
having the snapshot delivery process from trying to drop the UDFs at your
subscriber which is most likely the main source of problem for replicating
the UDFs to the subscriber.
-Raymond
"Jason Fisher" <j.fisher@.earthlink.net> wrote in message
news:uobv$TyaHHA.4716@.TK2MSFTNGP02.phx.gbl...
> Raymond,
>
> Hmm. But wouldn't it make more sense to replicate the default and then
> throw an error later if the referenced UDF is not there on the subscriber,
> rather than to simply fail silently during replication? I would always
> rather have a noisy, trappable error later on than a silent, untrappable
> "error" earlier on. Oh well ...
> Regarding your recommendations:
>
> I'm trying this now, but it doesn't look like a good solution in our
> particular case because we have tables already in the subscriber database
> (not being replicated from the publisher) that also reference the same
> UDF. This means that what I'm seeing is error after error in the
> replication monitor as it tries and fails to drop the UDF.
>
> I will think about trying this next, although it's not ideal because a)
> it's manual, b) the tables in the script have to be updated / maintained
> as / if our business needs change. In other words: again, it's manual.
> Any other ideas? And thanks very much for the assistance, too, by the way!
>
> Jason
>

BUG: SQLXML 3.0 attempts to update Timestamp column

When submitting an update gram generated from a dataset containing a table
having a timestamp column, SQLXML 3.0 will generate an UPDATE statement
attempting to set the timestamp column even after the timestamp column is
marked in the XSD with the attribute sql:datatype="timestamp". This of
course results in an error from SQL Server.
If the /diffgr:diffgram/me:DataSet/me:TABLE_NAME/me:TimeStamp node is
present in the diffgram and the
/diffgr:diffgram/diffgr:before/me:TABLE_NAME/me:TimeStamp node is removed.
Then SQLXML attempts to set the value of TimeStamp to the value from the
/diffgr:diffgram/me:DataSet block.
If TimeStamp node is not present in either block, then SQLXML attempts to
set the value of the TimeStamp column to NULL.
I am attempting to remove all but the primary key columns from the
/diffgr:diffgram/diffgr:before block as suggested as a work around by
Microsoft to other bugs and this is clearly not going to work with tables
having TimeStamp columns.
This bug is a showstopper for me and I am going to have to switch to a
different update mechanism.
AND ANOTHER BUG:
SQLXML 3.0 also applies exact constraints to floating point numbers when
generating update statements. This does not work all the time due to the
vagaries of string to floating point representation conversions.Hello Kevin,
Thank you for your feedback, and this has been routed to the proper
channel. In the meantime, I also encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
To be able to choose the updating logic, you may choose to use updategrams
instead of diffgrams:
Updating Data by Using XML Updategrams
<http://msdn.microsoft.com/library/d...n-us/sqlxml3/ht
m/updategram_0i5v.asp>
This lets you choose the updating logic to be sent to SQL server:
Handling Database Concurrency Issues in Updategrams
<http://msdn.microsoft.com/library/d...n-us/sqlxml3/ht
m/updategram_7tir.asp>
You will see that there is a way to use Primary Key and Timestamp (Specify
the timestamp column (if available) in the <before> block.).
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am getting the DiffGram from a changed DataSet. UpdateGram is not an
option given by DataSet.GetChanges.WriteXml.
Does MSoft have a DiffGram to UpdateGram stylesheet laying around somewhere?
My DataSet has over 30 tables having over 1000 columns. At first glance
this DiffGram approach seemed like a miracle: 5 lines of code to perform
the updates using a stylesheet generate by 100 lines of code. Wiring up
data adapters and getting all of that correct is at least another order of
magnitude and possibly 3 orders of magnitude more code. (The generated
dataset code is 86,000 lines).
It looks like I am going to have to see how I can minimally tweak the
generated Adapters that I am not using at this moment to leverage the
generated update code. I hate to do this because all future DB changes are
going to result in a very error-prone code management process.
""privatenews"" <petery@.online.microsoft.com> wrote in message
news:gHQJ9w0bGHA.2240@.TK2MSFTNGXA01.phx.gbl...
> Hello Kevin,
> Thank you for your feedback, and this has been routed to the proper
> channel. In the meantime, I also encourage you submit via the link below
> http://lab.msdn.microsoft.com/produ...ck/default.aspx
> To be able to choose the updating logic, you may choose to use updategrams
> instead of diffgrams:
> Updating Data by Using XML Updategrams
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/ht[/ur
l]
> m/updategram_0i5v.asp>
> This lets you choose the updating logic to be sent to SQL server:
> Handling Database Concurrency Issues in Updategrams
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/ht[/ur
l]
> m/updategram_7tir.asp>
> You will see that there is a way to use Primary Key and Timestamp
> ("Specify
> the timestamp column (if available) in the <before> block.").
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Kevin,
Thank you for your comments on this feature. To the best of my knowledge,
there is currently no stylesheet to transform DiffGram to UpdateGram. You
may need to develop your own transform if it is necessary. I think it shall
be future product enhancement and this has been reported to the proper
channel.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Bug, Can not create varchar(max) columns with SMO

I just want to create a varchar(max) column with SMO. My code is as follows. Instead of varchar(max), a column with datatype varchar(1) is created!!!

Table t = new Table(db, "t1");
t.Columns.Add(new Column(t, "c1", new DataType(SqlDataType.NVarCharMax)));
t.Alter();

That seems to be a bug of release version. I don't face this problem in Beta 2

Is there a workaround for that problemThis seems like bug to me also. I will file a bug against the product. As a workaround, you can execute the T-SQL directly, such as

db.ExecuteNonQuery("create table tb (id nvarchar(max))");

Peter

|||Thanks for your help.|||Just ran across the same issue. Any other workaround on this?|||Hi

I just encountered the same issue with Sql Server 2005 SP1. However, it has only happened on one computer. The problem is not reproducible on any other machines in our environment.|||YOu should doublecheck that again, as in SP2 the following script is returned from the Scripter:

Code Snippet

USE [Northwind]

ALTER TABLE [dbo].[t1] ADD [c1] [nvarchar](max)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||The SMO library is invoked on a server that does not have Sql Server. The only Sql Server related libraries installed on the server are as follows.

XMO 9.00.1399.06 NOV 2005 Feature Pack
NATIVE CLIENT 9.00.1399.06 NOV 2005 Feature Pack
XML6.0 6.00.3890.0 NOV 2005 Feature Pack

Are there known issues with using these versions?

Thanks|||The SMO libraries are also updated within the service packs, so it could be that the actual stack which is generating the ALTER statement was bug fixed. I posted a bug before RTM time, which was also fixed with SP1, so there can be a good chance that the version you are using 1399 = RTM has the bug which is causing the problems.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Actually, you can set it - it's just a bit "hidden". Set the DataType to NVarChar and set an arbitrary length value (like 100). Then set the column's DataType.MaximumLength property to -1. It is now a NVarChar(max) column.

|||Thanks

I resolved the problem by upgrading the Sql Server Management Objects from the Nov 2005 release to the Feb 2007 release.

Bug, Can not create varchar(max) columns with SMO

I just want to create a varchar(max) column with SMO. My code is as follows. Instead of varchar(max), a column with datatype varchar(1) is created!!!

Table t = new Table(db, "t1");
t.Columns.Add(new Column(t, "c1", new DataType(SqlDataType.NVarCharMax)));
t.Alter();

That seems to be a bug of release version. I don't face this problem in Beta 2

Is there a workaround for that problem
This seems like bug to me also. I will file a bug against the product. As a workaround, you can execute the T-SQL directly, such as

db.ExecuteNonQuery("create table tb (id nvarchar(max))");

Peter

|||Thanks for your help.|||Just ran across the same issue. Any other workaround on this?|||Hi

I just encountered the same issue with Sql Server 2005 SP1. However, it has only happened on one computer. The problem is not reproducible on any other machines in our environment.

|||YOu should doublecheck that again, as in SP2 the following script is returned from the Scripter:

Code Snippet

USE [Northwind]

ALTER TABLE [dbo].[t1] ADD [c1] [nvarchar](max)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||The SMO library is invoked on a server that does not have Sql Server. The only Sql Server related libraries installed on the server are as follows.

XMO 9.00.1399.06 NOV 2005 Feature Pack
NATIVE CLIENT 9.00.1399.06 NOV 2005 Feature Pack
XML6.0 6.00.3890.0 NOV 2005 Feature Pack

Are there known issues with using these versions?

Thanks
|||The SMO libraries are also updated within the service packs, so it could be that the actual stack which is generating the ALTER statement was bug fixed. I posted a bug before RTM time, which was also fixed with SP1, so there can be a good chance that the version you are using 1399 = RTM has the bug which is causing the problems.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Actually, you can set it - it's just a bit "hidden". Set the DataType to NVarChar and set an arbitrary length value (like 100). Then set the column's DataType.MaximumLength property to -1. It is now a NVarChar(max) column.

|||Thanks

I resolved the problem by upgrading the Sql Server Management Objects from the Nov 2005 release to the Feb 2007 release.

Bug, Can not create varchar(max) columns with SMO

I just want to create a varchar(max) column with SMO. My code is as follows. Instead of varchar(max), a column with datatype varchar(1) is created!!!

Table t = new Table(db, "t1");
t.Columns.Add(new Column(t, "c1", new DataType(SqlDataType.NVarCharMax)));
t.Alter();

That seems to be a bug of release version. I don't face this problem in Beta 2

Is there a workaround for that problem
This seems like bug to me also. I will file a bug against the product. As a workaround, you can execute the T-SQL directly, such as

db.ExecuteNonQuery("create table tb (id nvarchar(max))");

Peter

|||Thanks for your help.|||Just ran across the same issue. Any other workaround on this?|||Hi

I just encountered the same issue with Sql Server 2005 SP1. However, it has only happened on one computer. The problem is not reproducible on any other machines in our environment.

|||YOu should doublecheck that again, as in SP2 the following script is returned from the Scripter:

Code Snippet

USE [Northwind]

ALTER TABLE [dbo].[t1] ADD [c1] [nvarchar](max)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||The SMO library is invoked on a server that does not have Sql Server. The only Sql Server related libraries installed on the server are as follows.

XMO 9.00.1399.06 NOV 2005 Feature Pack
NATIVE CLIENT 9.00.1399.06 NOV 2005 Feature Pack
XML6.0 6.00.3890.0 NOV 2005 Feature Pack

Are there known issues with using these versions?

Thanks
|||The SMO libraries are also updated within the service packs, so it could be that the actual stack which is generating the ALTER statement was bug fixed. I posted a bug before RTM time, which was also fixed with SP1, so there can be a good chance that the version you are using 1399 = RTM has the bug which is causing the problems.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Actually, you can set it - it's just a bit "hidden". Set the DataType to NVarChar and set an arbitrary length value (like 100). Then set the column's DataType.MaximumLength property to -1. It is now a NVarChar(max) column.

|||Thanks

I resolved the problem by upgrading the Sql Server Management Objects from the Nov 2005 release to the Feb 2007 release.

Bug using FOR XML AUTO with columns with type char(1)

Hello,
Having some problems with generating xml with "FOR XML AUTO". Rows with
a column with a value char(0) seems to terminate the row!
Try to run the following:
SELECT
*
FROM (
SELECT
1 AS orderItemId,
1 AS orderId,
CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
UNION
SELECT
2 AS orderItemId,
1 AS orderId,
CHAR(49) AS orderItemStatus
) AS orderItem
FOR XML AUTO
Generates:
<orderItem orderItemId="1" orderId="1" orderItemStatus="
Should be(?):
<orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
orderItemId="2" orderId="1" orderItemStatus="1"/>
Anyone seen this before?
Regards, Nils
CHAR(0) is not a valid XML character. In FOR XML, we do not detect invalid
characters for performance reasons, so you will pass the XML anyway.
Depending on what the client code uses to read the XML, it may see the
CHAR(0) and decide that it is the end of the string (for instance, if the
client code uses the standard C/C++ string types).
So the recommendation is: Do not generate XML containing invalid characters
such as CHAR(0) since they are not supported by compliant XML parsers and
may result in other unforeseen behaviour.
Best regards
Michael
<nilsflemstrom@.gmail.com> wrote in message
news:1125047051.277443.138730@.z14g2000cwz.googlegr oups.com...
> Hello,
> Having some problems with generating xml with "FOR XML AUTO". Rows with
> a column with a value char(0) seems to terminate the row!
> Try to run the following:
> SELECT
> *
> FROM (
> SELECT
> 1 AS orderItemId,
> 1 AS orderId,
> CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
> UNION
> SELECT
> 2 AS orderItemId,
> 1 AS orderId,
> CHAR(49) AS orderItemStatus
> ) AS orderItem
> FOR XML AUTO
> Generates:
> <orderItem orderItemId="1" orderId="1" orderItemStatus="
> Should be(?):
> <orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
> orderItemId="2" orderId="1" orderItemStatus="1"/>
> Anyone seen this before?
> Regards, Nils
>

Bug using FOR XML AUTO with columns with type char(1)

Hello,
Having some problems with generating xml with "FOR XML AUTO". Rows with
a column with a value char(0) seems to terminate the row!
Try to run the following:
SELECT
*
FROM (
SELECT
1 AS orderItemId,
1 AS orderId,
CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
UNION
SELECT
2 AS orderItemId,
1 AS orderId,
CHAR(49) AS orderItemStatus
) AS orderItem
FOR XML AUTO
Generates:
<orderItem orderItemId="1" orderId="1" orderItemStatus="
Should be(?):
<orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
orderItemId="2" orderId="1" orderItemStatus="1"/>
Anyone seen this before?
Regards, NilsCHAR(0) is not a valid XML character. In FOR XML, we do not detect invalid
characters for performance reasons, so you will pass the XML anyway.
Depending on what the client code uses to read the XML, it may see the
CHAR(0) and decide that it is the end of the string (for instance, if the
client code uses the standard C/C++ string types).
So the recommendation is: Do not generate XML containing invalid characters
such as CHAR(0) since they are not supported by compliant XML parsers and
may result in other unforeseen behaviour.
Best regards
Michael
<nilsflemstrom@.gmail.com> wrote in message
news:1125047051.277443.138730@.z14g2000cwz.googlegroups.com...
> Hello,
> Having some problems with generating xml with "FOR XML AUTO". Rows with
> a column with a value char(0) seems to terminate the row!
> Try to run the following:
> SELECT
> *
> FROM (
> SELECT
> 1 AS orderItemId,
> 1 AS orderId,
> CHAR(0) AS orderItemStatus --CHAR(0) terminates row!
> UNION
> SELECT
> 2 AS orderItemId,
> 1 AS orderId,
> CHAR(49) AS orderItemStatus
> ) AS orderItem
> FOR XML AUTO
> Generates:
> <orderItem orderItemId="1" orderId="1" orderItemStatus="
> Should be(?):
> <orderItem orderItemId="1" orderId="1" orderItemStatus=" "/><orderItem
> orderItemId="2" orderId="1" orderItemStatus="1"/>
> Anyone seen this before?
> Regards, Nils
>

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

buffer.SetString not writing values!

Hello all,

I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.

Here's the code:

Code Snippet

public override void ProcessInput(int inputID, PipelineBuffer buffer){

//TODO

setupTraceListener();

IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];

int outId = outp.ID;

int errorOut = -1;

IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);

if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;

}

string sourceValue;

if(!buffer.EndOfRowset){

while(buffer.NextRow()){

if(!buffer.IsNull(inputColumnBufferIndex)){

switch(inp.InputColumnCollection[0].DataType){

case DataType.DT_WSTR:

sourceValue = buffer.GetString(this.inputColumnBufferIndex);

break;

case DataType.DT_I4:

sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();

break;

default: throw new Exception("Invalid Data Type!");

}

sourceValue = sourceValue.Trim();

try{

object decodedValueObj = this.mappings[sourceValue];

if(decodedValueObj == null){

throw new Exception("No mapping!");

}

string decodedValue = decodedValueObj.ToString();

switch(outp.OutputColumnCollection[0].DataType){

case DataType.DT_WSTR:

buffer.SetString(this.outputColumnBufferIndex, decodedValue);

break;

case DataType.DT_I4:

buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));

break;

default:

throw new IOException("Invalid Data Type!");

}

buffer.DirectRow(outId);

}catch(IOException fake){

throw fake;

}catch(Exception e){

redirectOrFail(inp, errorOut, outId, buffer, e);

}

}else{

redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));

}

}

}

}

mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:

Code Snippet

IDTSInput90 input = ComponentMetaData.InputCollection[0];

IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];

IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];

IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];

this.inputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);

this.outputColumnBufferIndex =

BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);

I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.

I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.

Any help would be greatly appreciated!

Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?

Do you only have one input and one output column in your component?

Are you getting any errors from SetString?

Thanks,

Bob

|||

Thank you for your reply Bob,

Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.

It's working now by using:

Code Snippet

buffer[this.outputColumnBufferIndex] = decodedValue;

Don't ask me why.

I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.

Maybe my installation is corrupted somehow?

|||

Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.

Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?

Thanks,

Bob

|||

I don't know what to look for, this is my 3rd week with C# and SSIS.

Here's the basic idea:

Code Snippet

public override void ProvideComponentProperties(){

//FIXME

base.ProvideComponentProperties();

(set the name, dispositions, etc...)

base.RemoveAllInputsOutputsAndCustomProperties();

(add synchronous inputs and outputs)

(add custom properties)

}

I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?

Thank you very much again.

|||

Yeah, that is it.

You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.

Alternatively, you may move that line before base.ProvideComponentProperties().

Thanks,

Bob

|||Thank you very much for your help|||

Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

|||

Thank you very much for that link! I'll be changing that right away.

Breaking up a string column into multiple records

Hopefully someone can help me. I have a table of records with the following fields:

AnswerID (int)
MultipleChoiceMultipleAnswer (varchar)
QuestionID (int)

now the multipleChoiceMultipleAnswer field data is in the following format:

1234,5678,99867
2345,456,7891

I want to break that field up into multiple records using the same AnswerID and QuestionID for each new multiple choice answer, so it would look like this:

10001 1234 9999
10001 5678 9999
10001 99867 9999
10002 2345 9998
10002 456 9998
10002 7891 9998

Is there an optimized method of doing this without using a cursor to iterate through each record?

Any help would be greatly appreciated.

ThanksLook at the following link - if you need something else, let me know -

link (http://dbforums.com/showthread.php?threadid=586248)

Breaking column data

DBA's
How do I break a column data into 2 or more columns.
Example, I have a 7 character column that I want to break into 3 columns of 3,2 and 2 character respectively. Let me know how the query would look like.
ThanksYou could use left(), substring() functions:

select left(column,2), substring(column,3,2),...|||Don't forget RIGHT

USE Northwind
GO
CREATE TABLE myTable99 (Col1 char(7))
GO

INSERT INTO myTable99(Col1)
SELECT '1234567' UNION ALL
SELECT 'abcdefg' UNION ALL
SELECT 'qwertyu' UNION ALL
SELECT 'lkjhgfd' UNION ALL
SELECT '1212312'

SELECT LEFT(Col1,2) AS [Left]
, SUBSTRING(Col1,3,3) AS Center
, RIGHT(Col1,2) AS [Right]
FROM myTable99
GO

DROP TABLE myTable99
GO|||Don't forget about your bol - check out string functions.

Breaking apart Column into rows

I have a column that has text delimited by a percent sign that I wish
to turn into rows.
Example:
A column contains ROBERT%CAMARDA, I want to turn that into two rows,
one row with ROBERT and antoher row with CAMARDA.
I will have source rows that have zero, one, or many percent sign
delimiters that will correspond to that many rows (One percent sign
will create 2 rows, 2 percent signs will create 3 rows and so forth).

Any thoughts?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> I have a column that has text delimited by a percent sign that I wish
> to turn into rows.
> Example:
> A column contains ROBERT%CAMARDA, I want to turn that into two rows,
> one row with ROBERT and antoher row with CAMARDA.
> I will have source rows that have zero, one, or many percent sign
> delimiters that will correspond to that many rows (One percent sign
> will create 2 rows, 2 percent signs will create 3 rows and so forth).

Have a look at an article on my web site:
http://www.sommarskog.se/arrays-in-sql.html.

Specifically, look at "List-of-strings" and "Unpacking a table column".

You did not say which version of SQL Server you are using. The article
is written for SQL 2000. SQL 2005 provides a new operator CROSS APPLY
which is good for the column bit. In SQL 2000, you will have to run
it row by row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Brackets appear in column name

I am using Enterprise Manager to create database tables. In the table design view I am trying to create a column called, section. After typing, section, Enterprise Manager automatically puts brackets around the name, ex. [section]. When I view the table by returning all rows the brackets are gone. However, when I go back to desing view the brackets are there. Why is this happening and what affect does it have on my database?

Thanks,

Matt

That is 'normal' and expected behavior when your table or column names contain spaces or other 'invalid' characters.

SQL Server, unlike Access you may have been using, does not allow the use of spaces in object (tables, columns, etc.) names. You will always have to use those 'pesky' square brackets when you write any SQL code specifying the improperly named items.

Other than the extra work always having to use the square brackets when writing code, there is no discernable impact on the database. The database assigns an 'ObjectID' to all tables, columns, etc., and internally, it uses the ObjectID.

|||

Arnie,

The column name I am trying to use is, section, with no spaces but Enterprise Manager is automatically putting the brackets on. Any ideas?

Thanks,

Matt

|||

I should have included that square brackets are also included around 'reserved words' when used as object names.

You may wish to examine the 'reserved word' list in Books Online, and then avoid using any reserved words in your object names.

As I indicated before, if EM adds the square brackets, you will also be required to also use square brackets (or double quotes) when referring to those improperly named objects.

|||

Arnie,

I found the word, section, in the Books Online. It is an ODBC reserved word. Thanks for the help,

Matt

|||

Matt,

If that information helped you solve your issue, please mark the post as helpful.

Thanks

Brackets appear in column name

I am using Enterprise Manager to create database tables. In the table design view I am trying to create a column called, section. After typing, section, Enterprise Manager automatically puts brackets around the name, ex. [section]. When I view the table by returning all rows the brackets are gone. However, when I go back to desing view the brackets are there. Why is this happening and what affect does it have on my database?

Thanks,

Matt

That is 'normal' and expected behavior when your table or column names contain spaces or other 'invalid' characters.

SQL Server, unlike Access you may have been using, does not allow the use of spaces in object (tables, columns, etc.) names. You will always have to use those 'pesky' square brackets when you write any SQL code specifying the improperly named items.

Other than the extra work always having to use the square brackets when writing code, there is no discernable impact on the database. The database assigns an 'ObjectID' to all tables, columns, etc., and internally, it uses the ObjectID.

|||

Arnie,

The column name I am trying to use is, section, with no spaces but Enterprise Manager is automatically putting the brackets on. Any ideas?

Thanks,

Matt

|||

I should have included that square brackets are also included around 'reserved words' when used as object names.

You may wish to examine the 'reserved word' list in Books Online, and then avoid using any reserved words in your object names.

As I indicated before, if EM adds the square brackets, you will also be required to also use square brackets (or double quotes) when referring to those improperly named objects.

|||

Arnie,

I found the word, section, in the Books Online. It is an ODBC reserved word. Thanks for the help,

Matt

|||

Matt,

If that information helped you solve your issue, please mark the post as helpful.

Thanks

Borders & Styles

Hi,
I am making a Matrix report & having a tough time getting the borders to
work.
On the column header the side borders are appearing but the top borders
phew, I dont seem to be getting them.
Please help, ThanksThe Borders don't appear on the report when do a preview but when I save it
as Excel they are there in Excel.
Any thoughts ?
Thanks
"Vishal" <vrajput77@.hotmail.com> wrote in message
news:uwPwkIdCGHA.4040@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am making a Matrix report & having a tough time getting the borders to
> work.
> On the column header the side borders are appearing but the top borders
> phew, I dont seem to be getting them.
> Please help, Thanks
>