One insert box per individual name?

Hi,

My Crystal Report has a box around each section (detail sections a&b). So, for example, John Doe might have 3 different sections and appear within 3 boxes. But now my client would like this box to group for each individual name, meaning John Doe's data should only be in one box. How would I do this? I'm new to Crystal Reports...help!

Please help as soon as possible.

Thanks so much!
LillyGroup your records on that field (click on it-> Insert->Group) place the fields you want to shown in you report into GF then suppress Details section.

On to Bulk Insert issues

OK, I tried this:

USE Alert_db;

BULK INSERT funds FROM 'C:\\data\\myData.dat'
WITH (FIELDTERMINATOR='\t',
KEEPNULLS,
ROWTERMINATOR='\r\n');

And I got the following errors.

Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for
the specified codepage) for row 1, column 4 (f_asset_classes_id).
Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row
1, column 6. Verify that the field terminator and row terminator are
specified correctly.
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "BULK" for linked server "(null)" reported an
error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "BULK" for linked server
"(null)".

One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.

I do not know what "State 1" vs "State 8" is supposed to mean.

The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?

In other cases, where I have to use something like bulk insert,
involves several columns containing dates. I know MS SQL supports the
format used in the file (by reading the documentation for cast
operations), but is there an easy way to tell MS SQL which of the
supported date formats to use when reading this data. I've read bcp
should be useful for this, but I have yet to figure that out.

In about half of the cases where I load data from a file, the data is
loaded once when the database is first created, and in the rest, there
is new data to be loaded every business day; so I need to be able to
submit the required command from the command line, and thus invoke it
using a perl script.

BTW: I have ordered a couple books on T-SQL, but they have yet to
arrive.

Thanks

TedTed (r.ted.byers@.rogers.com) writes:

Quote:

Originally Posted by

One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.

That's correct, if optionally means just optionally, so that you
could have:

9;Some unquoted data;12;9.234;2004-12-12
19;"Some quoted data";-12;31.4;2003-02-23

But if a text column is consistently quoted, you can handle this with a
format file where you specify each field. A format file that fits the
second row in the example above could look like:

8.0
5
1 SQLCHAR 0 0 ";\"" 1 col1 ""
2 SQLCHAR 0 0 "\";" 2 col2 ""
3 SQLCHAR 0 0 ";" 3 col3 ""
4 SQLCHAR 0 0 ";" 4 col3 ""
5 SQLCHAR 0 0 "\r\n" 5 col3 ""

The first row is the version of the file format. Next is the number of
fields in the file. Following lines describe one field each.

First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR
for a Unicode file. Other data types are only used with binary formats.

The third column is prefix-length, used only for binary files. Fourth
column is the length, and is used for fixed-length fields. Fifth field
is the terminator, and it is here you specify the quotes.

Six column is the database column, with 1 denoting the first column. 0
means that this field is not to be imported. Seventh column is the
column name, but it's informational. BCP/BULK INSERT does not use it.
Last colunm is the collation for the data in the file.

Overall, keep in mind that BCP/BULK INSERT reads a binary file and a
row terminator is really only the terminator for the last field.

Quote:

Originally Posted by

I do not know what "State 1" vs "State 8" is supposed to mean.

You can consider it as white noise. The state number may tell the
SQL Server developers something, but they are not documented.

Quote:

Originally Posted by

The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?

That should work fine. However, if fields are missing, so that you
have six fields on one line, and eight on the next, you lose.

--
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|||hi all again...
it is a chance for me that such a topic has been opened :))
so i can ask something that i need to know...

well i am using BCP command to get a text file into SQL Server 2000
but i am getting an error message like this one :
"string data, right truncation"

and i have no idea how i am going to get over this problem!!
in what situations does the sql server 2000 return with such an error?

what should i do to get over this problem?

thanks a lot

Tunc Ovacik

************************************************** *****************

Erland Sommarskog wrote:

Quote:

Originally Posted by

Ted (r.ted.byers@.rogers.com) writes:

Quote:

Originally Posted by

One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.

>
That's correct, if optionally means just optionally, so that you
could have:
>
9;Some unquoted data;12;9.234;2004-12-12
19;"Some quoted data";-12;31.4;2003-02-23
>
But if a text column is consistently quoted, you can handle this with a
format file where you specify each field. A format file that fits the
second row in the example above could look like:
>
8.0
5
1 SQLCHAR 0 0 ";\"" 1 col1 ""
2 SQLCHAR 0 0 "\";" 2 col2 ""
3 SQLCHAR 0 0 ";" 3 col3 ""
4 SQLCHAR 0 0 ";" 4 col3 ""
5 SQLCHAR 0 0 "\r\n" 5 col3 ""
>
The first row is the version of the file format. Next is the number of
fields in the file. Following lines describe one field each.
>
First column is record number. Second column is data type of the field
in the file. For a text file this is always SQLCHAR or always SQLNCHAR
for a Unicode file. Other data types are only used with binary formats.
>
The third column is prefix-length, used only for binary files. Fourth
column is the length, and is used for fixed-length fields. Fifth field
is the terminator, and it is here you specify the quotes.
>
Six column is the database column, with 1 denoting the first column. 0
means that this field is not to be imported. Seventh column is the
column name, but it's informational. BCP/BULK INSERT does not use it.
Last colunm is the collation for the data in the file.
>
Overall, keep in mind that BCP/BULK INSERT reads a binary file and a
row terminator is really only the terminator for the last field.
>

Quote:

Originally Posted by

I do not know what "State 1" vs "State 8" is supposed to mean.

>
You can consider it as white noise. The state number may tell the
SQL Server developers something, but they are not documented.
>

Quote:

Originally Posted by

The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?

>
That should work fine. However, if fields are missing, so that you
have six fields on one line, and eight on the next, you lose.
>
>
--
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

|||panic attack (tunc.ovacik@.gmail.com) writes:

Quote:

Originally Posted by

it is a chance for me that such a topic has been opened :))
so i can ask something that i need to know...
>
well i am using BCP command to get a text file into SQL Server 2000
but i am getting an error message like this one :
"string data, right truncation"
>
and i have no idea how i am going to get over this problem!!
in what situations does the sql server 2000 return with such an error?
>
what should i do to get over this problem?

First start a new thread, so we can keep different problems apart.

You can try the -e option to get errors to a file, you will then see which
records in the file that provokes this error. I need however add the caveat
that not all errors get listed in the error file, and I don't remember if
this error gets lists.

The two most plausible reasons for the error is
1) the file has data that does not fit the table columns.
2) there is an error with your delimiters, so that BCP gets out of sync.

If that does not help, post the CREATE TABLE command for the table and the
exact command line for BCP you are using. If you use a format file, please
also include a format file. Finally, include a sample of the input file.
Best is if the sample produces the error message. If the input file
exceeds 80 characters in length include it as an attachment, so it does
not get wrecked in news transport.

--
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|||Thanks Erland,

To deal with the quotes (because your example doesn't show what happens
if the first column in the file is quoted text), I tried the data
import wizard from within SQL Server Management Studio. For most of
the data files/table combinations, it worked well. However, there are
issues, especially related to nulls.

1) With one file, some of which goes into one tabe and some goes into
another, the last column contains null values, and consequently the
load fails. The key error seems to be:

Error 0xc020901c: Data Flow Task: There was an error with input column
"Dist_Unit" (67) on input "Destination Input" (51). The column status
returned was: "The value could not be converted because of a potential
loss of data.".
(SQL Server Import and Export Wizard)

Dist_Unit is the last column and more often than not contains null
values.

This is especially puzzling since the wizard, when asked to show a
preview, properly displays the data without complaint.

2) With a different data file/table, there are two fields containing
integers, both of which could contain nulls, and while the wizard will
import the data without complaint, it silently converts the nulls to
zero. Is there a way to tell the wizard to keep the nulls as nulls?
If so, might this fix the problem in item #1?

3) I am trying to populate a lookup table from data used in item #1.
Of course, in that file, there will be multiple occurances of most
supplier code/supplier name pairs (one for each product supplied by the
supplier). This leads to the wizard complaining about violating the
primary key. Is there a way to tell the wizard to ignore duplicate
records?

4) Each time I tried the wizard, I told it to store a package on the
server. However, I can't seem to find these packages. Where should I
be looking for them, and can I tell SQL Server Management Studio to
export the packages as scripts I can invokve from the commandline?

Thanks

Ted|||BTW: I examined the problematic data files using Open Office's Writer,
configured to show non-printable characters, and invariably the number
of fields is correct, with the right number of tabs.

Ted|||I solved the problem with errors (item #1 in my previous post) by more
carefully specifying the data type of the input file columns. But this
leads to an equally serious problem. All the nulls in that column are
silently converted into zero. This represents a major distortion of
the meaning of the column. For this column, zero carries a very
different meaning from null. Worse, since the column had many records
in which the value was zero, it is not possible after the insert to
recover the nulls!

How can I tell the data import wizard to preserve my nulls?

Ted|||Ted (r.ted.byers@.rogers.com) writes:

Quote:

Originally Posted by

To deal with the quotes (because your example doesn't show what happens
if the first column in the file is quoted text), I tried the data
import wizard from within SQL Server Management Studio. For most of
the data files/table combinations, it worked well. However, there are
issues, especially related to nulls.

Wait a minute, last night you were using BULK INSERT, now you are using
the Import Wizard which uses SQL Integration Services that I know next
to nothing about. So I cannot assist with that part.

Quote:

Originally Posted by

4) Each time I tried the wizard, I told it to store a package on the
server. However, I can't seem to find these packages. Where should I
be looking for them, and can I tell SQL Server Management Studio to
export the packages as scripts I can invokve from the commandline?

As I understand it, Mgmt Studio does not offer any interface to
Integration Services. To this end you should use Business Intelligence
Development Studio.

Quote:

Originally Posted by

I solved the problem with errors (item #1 in my previous post) by more
carefully specifying the data type of the input file columns. But this
leads to an equally serious problem. All the nulls in that column are
silently converted into zero.

Just a stupid check: you don't happen to have a default of 0 on those
columns.

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Ted (r.ted.byers@.rogers.com) writes:

Quote:

Originally Posted by

To deal with the quotes (because your example doesn't show what happens
if the first column in the file is quoted text), I tried the data
import wizard from within SQL Server Management Studio. For most of
the data files/table combinations, it worked well. However, there are
issues, especially related to nulls.

>
Wait a minute, last night you were using BULK INSERT, now you are using
the Import Wizard which uses SQL Integration Services that I know next
to nothing about. So I cannot assist with that part.
>

Actually, I have been experimenting with BULK INSERT, bcp, and the
import wizard simultaneously. I find some inconsistency in how they
work. With bulk insert, it seems I can tell it to preserve my nulls,
and I haven't found out how to do that with the wizard (which is
accessable in SQL Server Management Studio by selecting the database
and then, from the popup menu selecting either import data or export
data).

OTOH, the wizard lets me specify in the second dialog that the text
fields are enclosed by quotes while it seems I may be only able to do
that by creating a format file for use by bcp or bulk insert.

Quote:

Originally Posted by

Quote:

Originally Posted by

4) Each time I tried the wizard, I told it to store a package on the
server. However, I can't seem to find these packages. Where should I
be looking for them, and can I tell SQL Server Management Studio to
export the packages as scripts I can invokve from the commandline?

>
As I understand it, Mgmt Studio does not offer any interface to
Integration Services. To this end you should use Business Intelligence
Development Studio.
>

It is accessable in Mgmt Studio through the popup menu accessible on
each database on the server.

Quote:

Originally Posted by

Quote:

Originally Posted by

I solved the problem with errors (item #1 in my previous post) by more
carefully specifying the data type of the input file columns. But this
leads to an equally serious problem. All the nulls in that column are
silently converted into zero.

>
Just a stupid check: you don't happen to have a default of 0 on those
columns.
>

No. On that particular table, there are no default values.

Thanks

Ted|||many thanks...
my problem has been solved.

tunc

Erland Sommarskog wrote:

Quote:

Originally Posted by

panic attack (tunc.ovacik@.gmail.com) writes:

Quote:

Originally Posted by

it is a chance for me that such a topic has been opened :))
so i can ask something that i need to know...

well i am using BCP command to get a text file into SQL Server 2000
but i am getting an error message like this one :
"string data, right truncation"

and i have no idea how i am going to get over this problem!!
in what situations does the sql server 2000 return with such an error?

what should i do to get over this problem?

>
First start a new thread, so we can keep different problems apart.
>
You can try the -e option to get errors to a file, you will then see which
records in the file that provokes this error. I need however add the caveat
that not all errors get listed in the error file, and I don't remember if
this error gets lists.
>
The two most plausible reasons for the error is
1) the file has data that does not fit the table columns.
2) there is an error with your delimiters, so that BCP gets out of sync.
>
If that does not help, post the CREATE TABLE command for the table and the
exact command line for BCP you are using. If you use a format file, please
also include a format file. Finally, include a sample of the input file.
Best is if the sample produces the error message. If the input file
exceeds 80 characters in length include it as an attachment, so it does
not get wrecked in news transport.
>
>
>
--
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

|||Ted (r.ted.byers@.rogers.com) writes:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

>As I understand it, Mgmt Studio does not offer any interface to
>Integration Services. To this end you should use Business Intelligence
>Development Studio.
>>

It is accessable in Mgmt Studio through the popup menu accessible on
each database on the server.

Yeah, I know about those. What I meant to say is that if you want to
look inside the packages, you will have enter Business Intelligence
Development Studio. (Which I have never visited myself. I'm completely
unintelligent when it comes to business.)

--
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|||Ted (r.ted.byers@.rogers.com) writes:

Quote:

Originally Posted by

Actually, I have been experimenting with BULK INSERT, bcp, and the
import wizard simultaneously. I find some inconsistency in how they
work. With bulk insert, it seems I can tell it to preserve my nulls,
and I haven't found out how to do that with the wizard (which is
accessable in SQL Server Management Studio by selecting the database
and then, from the popup menu selecting either import data or export
data).

I tried to use the wizard and import a file which did not have all values
for an int column, but I got the same error as you. Overall, the wizard
strained my patience, so I gave up after a while. It's certainly more
efficient with format files.

--
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.mspxsql

On inserting a rec, auto insert into related table

Hi there,
I'm new...
Could someone tell me how to automatically (via a trigger perhaps ?) insert
records into a related (child) table, based on the insert into the parent ?
I have a situation in my app where I want to add a record into a tabe called
'OrderedService'. On insert into this table, I want to insert automatically,
exactly 5 records into (foreign key) related table 'OrderedServiceResult'.
Note that the only fields that need populating on creation of the child are
those necessary for the key...
Thank you !Bazza Formez (bazza.formez@.paradise.net.nz) writes:
> Could someone tell me how to automatically (via a trigger perhaps ?)
> insert records into a related (child) table, based on the insert into
> the parent ?
> I have a situation in my app where I want to add a record into a tabe
> called 'OrderedService'. On insert into this table, I want to insert
> automatically, exactly 5 records into (foreign key) related table
> 'OrderedServiceResult'.
> Note that the only fields that need populating on creation of the child
> are those necessary for the key...
Yes, you could use a trigger for this, at least judging from the information
you have provided. Here is a simple example:
CREATE TRIGGER tbl_tri ON tbl FOR INSERT AS
INSERT othertbl(col1, col2, ...)
SELECT col1, col2, ...
FROM inserted
"inserted" that appears here is a virtual table that holds the rows
that were inserted. This table is only visible within a trigger. There
is a sister table "deleted" which holds deleted rows in a DELETE trigger.
In an UPDATE trigger both tables are populated, "inserted" holding the
new rows, and "deleted" the old rows.
Very important to understand is that a trigger on SQL Server is fired
once per statement. Thus "inserted" can hold many rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for this excellent reply... Your effort is appreciated.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CF4D245B5FYazorman@.127.0.0.1...
> Bazza Formez (bazza.formez@.paradise.net.nz) writes:
> Yes, you could use a trigger for this, at least judging from the
> information
> you have provided. Here is a simple example:
> CREATE TRIGGER tbl_tri ON tbl FOR INSERT AS
> INSERT othertbl(col1, col2, ...)
> SELECT col1, col2, ...
> FROM inserted
> "inserted" that appears here is a virtual table that holds the rows
> that were inserted. This table is only visible within a trigger. There
> is a sister table "deleted" which holds deleted rows in a DELETE trigger.
> In an UPDATE trigger both tables are populated, "inserted" holding the
> new rows, and "deleted" the old rows.
> Very important to understand is that a trigger on SQL Server is fired
> once per statement. Thus "inserted" can hold many rows.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

on Insert, then Update Tigger Help

when a record is inserted into tNTS, I need the columnseq_numupdated with the value of the columnid(it's the PK)

can someone help me with my tigger:

CREATE TRIGGER updateNewSeq
ON dbo.tNTS
FOR INSERT
AS

update tNTS
(
set seq_num =tNTS
)
SELECT
NTS_id
FROM INSERTED

cheersYour update statement looks wierd.

Use as an example:

 update titleauthor
 set title_id = inserted.title_id
 from titleauthor, deleted, inserted
 where deleted.title_id = titleauthor.title_id

|||I tried:

CREATE TRIGGER [updateSeqNum] ON [dbo].[tNTS]
FOR INSERT
AS
update tNTS
set seq_num = NTS_id
from inserted

got error: error 209 ambigious column name: NTS_id

NTS_id is the pk i'm trying to copy to column seq_num after the record is inserted|||You need a where statement for starters so it knows where to actually update.|||

update tNTS

set seq_num=i.NTS_id

from tNTS

JOIN inserted i ON (tNTS.id=i.id)

|||thanks motley, it worked

on insert trigger and a linked server?

Hi all
Is it possiable to use an on insert trigger to insert the same record into
another server (linked)?
Any hints or issues with this would be appreciated.
thanksdarwin
Yes, but why would you want to use a trigger?
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:AD6E0624-1BB6-49A1-B187-DD6424FFFD1F@.microsoft.com...
> Hi all
> Is it possiable to use an on insert trigger to insert the same record into
> another server (linked)?
> Any hints or issues with this would be appreciated.
> thanks|||Hi
When a new record is inserted into the first table we need a copy of that
record sent to a second table on a different server (linked), in near real
time.
What is the solution you are thinking of?
thks
"Uri Dimant" wrote:

> darwin
> Yes, but why would you want to use a trigger?
>
> "darwin" <darwin@.discussions.microsoft.com> wrote in message
> news:AD6E0624-1BB6-49A1-B187-DD6424FFFD1F@.microsoft.com...
>
>|||Transactional replication is an option:
http://msdn.microsoft.com/library/d...r />
_7syn.asp
ML
http://milambda.blogspot.com/

OleDbCommand with Parameters

Hi,

I have application connected to MS Access DB using OleDB. When creating commands (Insert/Update/Select) I use OleDbParamater class to insert data into command. Examples :

Select ::

OleDbCommand select_cmd = new OleDbCommand("SELECT * FROM " + ObjectTable.TableName + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Update ::

OleDbCommand update_cmd = new OleDbCommand("Update " + ObjectTable.TableName + " SET " +
ObjectTable.idParent + "=@." + ObjectTable.idParent + " , " +
ObjectTable.idParentUnder + "=@." + ObjectTable.idParentUnder + " , " +
ObjectTable.License + "=@." + ObjectTable.License + " , " +
ObjectTable.Type + "=@." + ObjectTable.Type + " ," +
ObjectTable.Language + "=@." + ObjectTable.Language + " , " +
ObjectTable.Name + "=@." + ObjectTable.Name + " , " +
ObjectTable.Checksum + "=@." + ObjectTable.Checksum + " , " +
ObjectTable.VText + "=@." + ObjectTable.VText + " , " +
ObjectTable.VInt + "=@." + ObjectTable.VInt + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Parametes:: (Adding in separate method -> AddParameters(OleDbCommand command); )

command.Parameters.Add("@." + ObjectTable.idObject, OleDbType.BigInt).Value = this.IDUpper;
command.Parameters.Add("@." + ObjectTable.idObjectUnder, OleDbType.BigInt).Value = this.IDUnder;
command.Parameters.Add("@." + ObjectTable.Name, OleDbType.VarChar).Value = this.Name;
command.Parameters.Add("@." + ObjectTable.idParent, OleDbType.BigInt).Value = GetUpper(this.IDParent);
command.Parameters.Add("@." + ObjectTable.idParentUnder, OleDbType.BigInt).Value = GetUnder(this.IDParent);
command.Parameters.Add("@." + ObjectTable.License, OleDbType.BigInt).Value = this.License;
command.Parameters.Add("@." + ObjectTable.Language, OleDbType.BigInt).Value = this.Language;
command.Parameters.Add("@." + ObjectTable.Type, OleDbType.BigInt).Value = (int)this.Type;

command.Parameters.Add("@." + ObjectTable.VText, OleDbType.VarChar).Value = String.IsNullOrEmpty(this.VText) ? null : this.VText;
command.Parameters.Add("@." + ObjectTable.VInt, OleDbType.BigInt).Value = this.VInt;
command.Parameters.Add("@." + ObjectTable.Checksum, OleDbType.BigInt).Value = this.Checksum;

Question: Does the order of adding parameters to command matter? Because allways when the order of parameters added is diffrent from order in command text, I get weird Exceptions . I thought that the name matters, not the order, but it seems that system doesn't care about the parameter's name, it just picks next parameter in command.Parameters when putting values. How is it?Do you mean that if it could matter during the addition of the parameters ? It does not, as the .add method only puts the parameter in the collection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||You know what? It does. And it is the only thing that matters (talking about OleDb) -> ORDER. Despite of using names (I could questionmarks instead of parameter's names). I've done this using Oracle DB and (of cource) namespace and everything worked fine, but OleDb looks to handle thing in it's own weird way. Example :

OleDbCommand selectCmd = new OleDbCommand( "Select * from Customers where CustomerName=@.name AND CustomerAge=@.age);

selectCmd.Parameters.Add("@.age", OleDbType.Integer).Value = 20;

selectCmd.Parameters.Add("@.name", OleDbType.VarChar).Value = "Michael";

this is NOT going to work !!! If the order of parameters added to command's parameters collection is diffrent from order of parameters used in command itself, it won't work.

oledb command

I am transfering large data.

I use oledb command to insert and update as i need to make some modifications to incoming data.I do my modifications in the procedure.

But the command does not insert as the data is huge at one shot.

if i try to send small data it works fine

Its shows warning(yellow color)

How can i achive inserting huge data effeciently please help.

Yellow just indicates that the task is still working, not that there is a problem. Red indicates a problem.

The OLEDB command is not the best for moving large amounts of data. When you need to perform an update, you might try writing the update data to a temporary table, then using an Execute SQL task to perform a batch update, as that usually has better performance.

|||

Even if i had to transfer to temporary table i need to using OLEDB connection or oledb command etc to store my update data and then use execute sql task in the control flow.

As i said earlier i am using oledb command as i try making some modifications in my procedure (updating several table at one time)

How can this be done?

|||you need to insert data with an OLE DB Destination, not the command transformation. Or the SQL Server destination. The OLE DB Command is not really designed for INSERTS as it executes for every row of data going through it.

OLE DB Timeout Expired

I am getting a Timeout Expired message when executing a query against a
linked server. The query is basically "INSERT INTO local_table(...)
SELECT ... FROM table". When executed from Query Analyzer the query
takes about 34 seconds. When I run the query from my VB6SP5
application, I get the timeout. I have tried the setting the
CommandTimeout properties of the connection and command objects used:
(VB Code - MDAC 2.8)
[ADODB.Connection].CommandTimeout = 999999
[ADODB.Command].CommandTimeout = 999999
Am I missing some other place to configure the timeout? All of these
settings are in effect when the timeout occurs (always after 30 seconds).If you're using SQL Server 2000 you can specify a timeout value for
connections to a linked server
by using the sp_serveroption stored procedure in conjunction with the
'connect timeout' option.
Michael Otey
"Gregg Savage" <gsavage@.charter.net> wrote in message
news:10fogtlf2mbjac1@.corp.supernews.com...
> I am getting a Timeout Expired message when executing a query against a
> linked server. The query is basically "INSERT INTO local_table(...)
> SELECT ... FROM table". When executed from Query Analyzer the query
> takes about 34 seconds. When I run the query from my VB6SP5
> application, I get the timeout. I have tried the setting the
> CommandTimeout properties of the connection and command objects used:
> (VB Code - MDAC 2.8)
> [ADODB.Connection].CommandTimeout = 999999
> [ADODB.Command].CommandTimeout = 999999
> Am I missing some other place to configure the timeout? All of these
> settings are in effect when the timeout occurs (always after 30 seconds).
>

OLE DB Timeout Expired

I am getting a Timeout Expired message when executing a query against a
linked server. The query is basically "INSERT INTO local_table(...)
SELECT ... FROM table". When executed from Query Analyzer the query
takes about 34 seconds. When I run the query from my VB6SP5
application, I get the timeout. I have tried the setting the
CommandTimeout properties of the connection and command objects used:
(VB Code - MDAC 2.8)
[ADODB.Connection].CommandTimeout = 999999
[ADODB.Command].CommandTimeout = 999999
Am I missing some other place to configure the timeout? All of these
settings are in effect when the timeout occurs (always after 30 seconds).
If you're using SQL Server 2000 you can specify a timeout value for
connections to a linked server
by using the sp_serveroption stored procedure in conjunction with the
'connect timeout' option.
Michael Otey
"Gregg Savage" <gsavage@.charter.net> wrote in message
news:10fogtlf2mbjac1@.corp.supernews.com...
> I am getting a Timeout Expired message when executing a query against a
> linked server. The query is basically "INSERT INTO local_table(...)
> SELECT ... FROM table". When executed from Query Analyzer the query
> takes about 34 seconds. When I run the query from my VB6SP5
> application, I get the timeout. I have tried the setting the
> CommandTimeout properties of the connection and command objects used:
> (VB Code - MDAC 2.8)
> [ADODB.Connection].CommandTimeout = 999999
> [ADODB.Command].CommandTimeout = 999999
> Am I missing some other place to configure the timeout? All of these
> settings are in effect when the timeout occurs (always after 30 seconds).
>

OLE DB error

I have a stored procedure that consists 4 set of statements (delete rows and
then insert rows from excel files with openrowset). It returns error when
execute the 4th insert statement. The error message is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file ''. It is already opened exclusively by another user, or you
need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Its strange, I think, that there is no file in the error message... BTW,
the stored procedure can be executed successfully if any one insert
statement is commented. Does openrowset or MS Jet database engine has any
limitation? Could anyone please tell me how can I solve this issue?
Any help would be appreciated.
P.S. I'm running SQL Server 2000
SChi Squirrel,
Could you please be so kind to post the aforementioned stored procedure here
?
"Squirrel" wrote:

> I have a stored procedure that consists 4 set of statements (delete rows a
nd
> then insert rows from excel files with openrowset). It returns error when
> execute the 4th insert statement. The error message is:
>
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine canno
t
> open the file ''. It is already opened exclusively by another user, or yo
u
> need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
>
> It?|s strange, I think, that there is no file in the error message... B
TW,
> the stored procedure can be executed successfully if any one insert
> statement is commented. Does openrowset or MS Jet database engine has any
> limitation? Could anyone please tell me how can I solve this issue?
>
> Any help would be appreciated.
>
> P.S. I'm running SQL Server 2000
>
> SC
>
>|||here. thanks.
CREATE PROCEDURE convert_data
@.userid varchar(8)
as
BEGIN TRANSACTION UpdateAll
DELETE table1
INSERT INTO table1
SELECT id, name, cat, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table1.xls', 'select * from [sheet1$]')
DELETE table2
INSERT INTO table2
SELECT id, serial, add_1, add_2, add_3, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table2.xls', 'select * from [sheet1$]')
DELETE table3
INSERT INTO table3
SELECT table1_id, table2_id, serial, type, amount, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table3.xls', 'select * from [sheet1$]')
DELETE table4
INSERT INTO table4
SELECT code, num, description, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table4.xls', 'select * from [sheet1$]')
COMMIT TRANSACTION UpdateAll
GO
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:EC55EB71-31E0-4A15-8BAA-5A04C45E0640@.microsoft.com...
> hi Squirrel,
> Could you please be so kind to post the aforementioned stored procedure
> here?
> "Squirrel" wrote:
>

ole db destination not inserting data

my package has a data flow task that is attempting to insert data into a sql server table using the ole db destination. the package validates and runs without reporting any errors, but the data was not inserted into the table. my data mappings look fine. how can i see what's happening when the component attempts to insert the data into the table?

Can you post all log messages that are produced from your data-flow?

Regards

Jamie

|||

Jamie Thomson wrote:

Can you post all log messages that are produced from your data-flow?

Regards

Jamie

here's the info from the output windows:

SSIS package "CaseStudy_Load.dtsx" starting.
Information: 0x4004300A at Data Flow Lockbox Detail Data Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x80047076 at Data Flow Lockbox Detail Data Task, DTS.Pipeline: The output column "PaymentAmount" (294) on output "Merge Join Output" (268) and component "Merge Join Checks and Invoices" (265) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x4004300A at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has started.
Information: 0x400490F4 at Data Flow Lockbox Validate File and Header Info, Lookup BankBatchID [373]: component "Lookup BankBatchID" (373) has cached 0 rows.
Information: 0x4004300C at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The total number of data rows processed for file "c:\casestudy\lockbox\samplelockbox.txt" is 11.
Information: 0x402090DF at Data Flow Lockbox Validate File and Header Info, OLE DB Destination Error Log [326]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Lockbox Validate File and Header Info, OLE DB Destination Error Log [326]: The final commit for the data insertion has ended.
Information: 0x40043008 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Lockbox Validate File and Header Info, Flat File Lockbox [1]: The processing of file "c:\casestudy\lockbox\samplelockbox.txt" has ended.
Information: 0x40043009 at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "OLE DB Destination Error Log" (326)" wrote 0 rows.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "Script Component to Capture BatchItems" (643)" wrote 0 rows.
Information: 0x4004300B at Data Flow Lockbox Validate File and Header Info, DTS.Pipeline: "component "Script Component to store variables" (437)" wrote 0 rows.
SSIS package "CaseStudy_Load.dtsx" finished: Success.
The program '[3448] CaseStudy_Load.dtsx: DTS' has exited with code 0 (0x0).

below is the information written to log.txt:

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message|||When debugging in BIDS - what is the number of rows passed to destination (it is displayed on the path leading from a previous transform to the destination while running package in BIDS)?

If the number is 0, no data has reached destination - something might be wrong with tranformations. Try adding data viewers to understand what data flows between transforms.|||ok. thanks. i discovered that the data isn't flowing from the flat file source. i'll make a new thread with a more appropriate subject line so that more people will notice my issue.

OLE DB Destination Component

when loading the transformed data into OLE DB destination, there is no options to truncate destination table first. Have to insert a middle step to run script to truncate the destination table first.

I'm very confused. We even has the options of keeping or deleting the data in destination table in SQL2000 DTS package. Why we don't have this option in SQL2005?

That's correct. You should use an Execute SQL task in the control flow before the data flow containing the OLE DB Destination.|||

Phil,

is there any component in SSIS package that can let us run flexible SQL Script again the input dataset just like the input dataset is a table?

|||

Jeff_LIU wrote:

Phil,

is there any component in SSIS package that can let us run flexible SQL Script again the input dataset just like the input dataset is a table?

Well, in the control flow, you have the Execute SQL task. In the data flow, you have the OLE DB Command transformation, but beware with that one as it will execute the contained SQL for every row in the input data source.|||

those two components can only use the input dataset as parameters, but can't update the data in input data source.

What I want to know is if we can run SQLScript to directly update the columns in input dataset

|||

Jeff_LIU wrote:

those two components can only use the input dataset as parameters, but can't update the data in input data source.

What I want to know is if we can run SQLScript to directly update the columns in input dataset

No. As I said in your other thread, you can update the columns in the data flow via a lookup transformation and a derived column transformation.