Can I Force SQL to Accept INSERT List of Values Less Than Number of Columns?

I have a canned application that does INSERTs with lists of values without
column lists. The table has one additional (uniqueidentifier) column for merge
replication, so the application is inserting 7 values, but there are 8
columns. Is there a way to tell SQL to accept it anyway and just fill the
columns from left to right until it runs out of data? I hope so, because I
have no access to the source code.
--EricHello Eric. you could try with renaming that table (the one you insert in)
and creating a view with old name of the table you just renamed. In the view
definition specify all fields from the renamed table except the one that you
added for replication(uniqueidentifier) .
Hope this works,
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr
"Eric Robinson" <eric@._nospam_nvipa.com> wrote in message
news:CFN379450577312037@.news.microsoft.com...
> I have a canned application that does INSERTs with lists of values without
> column lists. The table has one additional (uniqueidentifier) column for
merge
> replication, so the application is inserting 7 values, but there are 8
> columns. Is there a way to tell SQL to accept it anyway and just fill the
> columns from left to right until it runs out of data? I hope so, because I
> have no access to the source code.
> --Eric
>|||If there isn't a column list specified in the INSERT then the number of
columns in the table must match the number of columns in the INSERT
statement (less the IDENTITY column, if any).
You could set a default for the uniqueidentifier column, rename the table
and then create a view under the original name containing all except the
extra column:
CREATE TABLE newname (a INTEGER PRIMARY KEY, b INTEGER NOT NULL, c
UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ...)
CREATE VIEW oldname
AS
SELECT a,b
FROM newname
Then find the programmer and make him fix his code.
--
David Portas
--
Please reply only to the newsgroup
--|||"Tomislav Kralj" <tomislav.kralj1@.zg.tel.hr> wrote in message
news:bpi4j6$vq0$1@.sunce.iskon.hr...
> Hello Eric. you could try with renaming that table (the one you insert in)
> and creating a view with old name of the table you just renamed. In the
view
> definition specify all fields from the renamed table except the one that
you
> added for replication(uniqueidentifier) .
Oh, and i forgot. create view with VIEW_METADATA option !!!
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hrsql

Can columns be added to an error output?

When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?

I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.

IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;

// Add extra column here, e.g. ErrorMessage

Answering my own question. The answer is 'YES', you can enhance the error output.

You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.

===============================================================================

// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);

===============================================================================

// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;

// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);

===============================================================================

// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion

buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}

===============================================================================

|||

Can you give me more details on this. I will wait to see if you reply beofre I elaborate..

It sounds like this is something I am looking to do based on my post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1

|||

Rob,

The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.

-Jamie

|||

Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

|||

ronemac wrote:

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.

-Jamie

Can columns be added to an error output?

When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?

I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.

IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;

// Add extra column here, e.g. ErrorMessage

Answering my own question. The answer is 'YES', you can enhance the error output.

You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.

===============================================================================

// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);

===============================================================================

// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;

// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);

===============================================================================

// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion

buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}

===============================================================================

|||

Can you give me more details on this. I will wait to see if you reply beofre I elaborate..

It sounds like this is something I am looking to do based on my post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1

|||

Rob,

The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.

-Jamie

|||

Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

|||

ronemac wrote:

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.

-Jamie

Can a table, matrix or list produce this?

I am converting a report from a Clipper application and the existing reports
shows States with some additional information in a format I am finding hard
to reproduce. On first glance the report looks like a simple tabular
format. There are something like 10 rows in the table with 3 columns. The
first item of data is placed in row 1 column 1. The second item is in row 1
column 2 and the third item is row 1 column three. The fourth item is in
row 2 and column 1. I can use a matrix and produce the rows going
horizontally but I am not sure how to limit the columns to 3 and start new
rows below that. Below is an example.
AL 12345 AR 12367 CA 19494
CO 38823 CT 43984 GA 39393
IA 39390 ID 39390 IL 39300
Any idea would be greatly appreciated.
Thanks.You may be interested in this blog article:
http://blogs.msdn.com/chrishays/archive/2004/07/23/193292.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lance" <lely@.nospam.com> wrote in message
news:e1xduBf4EHA.1452@.TK2MSFTNGP11.phx.gbl...
> I am converting a report from a Clipper application and the existing
reports
> shows States with some additional information in a format I am finding
hard
> to reproduce. On first glance the report looks like a simple tabular
> format. There are something like 10 rows in the table with 3 columns.
The
> first item of data is placed in row 1 column 1. The second item is in row
1
> column 2 and the third item is row 1 column three. The fourth item is in
> row 2 and column 1. I can use a matrix and produce the rows going
> horizontally but I am not sure how to limit the columns to 3 and start new
> rows below that. Below is an example.
> AL 12345 AR 12367 CA 19494
> CO 38823 CT 43984 GA 39393
> IA 39390 ID 39390 IL 39300
> Any idea would be greatly appreciated.
> Thanks.
>