build sql script: working

Showing posts with label working. Show all posts

Tuesday, March 27, 2012

Bulk Load Identity Propagation

Hello,
I'm working with the SQLXML3.0 Bulk Loader SP3 & I am having a problem
propagating identity columns. I can get the identity to propagate to one
child, but the grandchild ends up getting 0s in the column that I expect the
identity value to be in.
Has anyone else had this problem, or can any of you see what I'm doing
wrong?
TIA,
Aelk
Here is a snippet from my xsd:
<xs:annotation>
<xs:appinfo>
<sql:relationship name="Batch-Test"
parent="XMLDM_Batch"
parent-key="BatchID"
child="XMLDM_Test"
child-key="BatchID" />
</xs:appinfo>
</xs:annotation>
<xs:annotation>
<xs:appinfo>
<sql:relationship name="Test-Child2"
parent="XMLDM_Test"
parent-key="BatchID ID"
child="XMLDM_TestChild2"
child-key="BatchID Child2ID" />
</xs:appinfo>
</xs:annotation>
<xs:annotation>
<xs:appinfo>
<sql:relationship name="Child2-GrandChild"
parent="XMLDM_TestChild2"
parent-key="BatchID Child2ID"
child="XMLDM_TestGrandChild"
child-key="BatchID Child2ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="TEST" sql:relation="XMLDM_Test"
sql:relationship="Batch-Test" >
<xs:complexType>
<xs:sequence>
<xs:element name="CHILD2" minOccurs="0" maxOccurs="unbounded"
sql:relation="XMLDM_TestChild2" sql:relationship="Test-Child2">
<xs:complexType>
<xs:sequence>
<xs:element name="GRANDCHILD" minOccurs="0"
maxOccurs="unbounded" sql:relation="XMLDM_TestGrandChild"
sql:relationship="Child2-GrandChild" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
And here is a sample of my XML that I am trying to load:
<TEST>
<CHILD2>
<GRANDCHILD>my grandchild</GRANDCHILD>
</CHILD2>
</TEST>
I'm getting the following in my tables:
XMLDM_Test:
BatchID ID
30 6
XMLDM_TestChild2:
BatchID Child2ID
30 6
XMLDM_TestGrandChild:
BatchID Child2ID Grandchild
30 0 my grandchild
This looks like a limitation in SqlXml3 Sp3.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"aelk" <aelk@.discussions.microsoft.com> wrote in message
news:D9B0719F-89DF-437A-A06F-43D6257817F1@.microsoft.com...
> Hello,
> I'm working with the SQLXML3.0 Bulk Loader SP3 & I am having a problem
> propagating identity columns. I can get the identity to propagate to one
> child, but the grandchild ends up getting 0s in the column that I expect
> the
> identity value to be in.
> Has anyone else had this problem, or can any of you see what I'm doing
> wrong?
> TIA,
> Aelk
> Here is a snippet from my xsd:
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="Batch-Test"
> parent="XMLDM_Batch"
> parent-key="BatchID"
> child="XMLDM_Test"
> child-key="BatchID" />
> </xs:appinfo>
> </xs:annotation>
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="Test-Child2"
> parent="XMLDM_Test"
> parent-key="BatchID ID"
> child="XMLDM_TestChild2"
> child-key="BatchID Child2ID" />
> </xs:appinfo>
> </xs:annotation>
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="Child2-GrandChild"
> parent="XMLDM_TestChild2"
> parent-key="BatchID Child2ID"
> child="XMLDM_TestGrandChild"
> child-key="BatchID Child2ID" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="TEST" sql:relation="XMLDM_Test"
> sql:relationship="Batch-Test" >
> <xs:complexType>
> <xs:sequence>
> <xs:element name="CHILD2" minOccurs="0" maxOccurs="unbounded"
> sql:relation="XMLDM_TestChild2" sql:relationship="Test-Child2">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="GRANDCHILD" minOccurs="0"
> maxOccurs="unbounded" sql:relation="XMLDM_TestGrandChild"
> sql:relationship="Child2-GrandChild" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
>
> And here is a sample of my XML that I am trying to load:
> <TEST>
> <CHILD2>
> <GRANDCHILD>my grandchild</GRANDCHILD>
> </CHILD2>
> </TEST>
> I'm getting the following in my tables:
> XMLDM_Test:
> BatchID ID
> 30 6
> XMLDM_TestChild2:
> BatchID Child2ID
> 30 6
> XMLDM_TestGrandChild:
> BatchID Child2ID Grandchild
> 30 0 my grandchild

Bulk insertion

Hi,

I am working on an application that is to read a large number of XML files, take out specific values from each file, and store these in a SQL server so that reports can be generated from these values. There are some 15-20,000 files for each month of the year. I am OK with parsing the files and getting the fields that I need but I don't want to insert one record at a time as I parse the files. I was told that I can create a .exe file that parses the xml files and stores the required values in a csv file and use these csv files to initiate a bulk insert, using Business Intelligence Studio. I have not been able to find any info or article on how to do this. Any help on how I can accomplish this, or alternate solutions is greatly appreciated.

You can make use of SQL BulkCopy feature of ADO.NET. Load your XML into a DataSet/DataTable and run SQL Bulk copy into your table. Very few lines of code.

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
foreach (string tableName in tableNames)
{
SqlBulkCopy(dataSet.Tables[tableName], tableName, bulkCopy);
}
}

private static void SqlBulkCopy(DataTable dataTable, string tableName, SqlBulkCopy bulkCopy)
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn myCol in dataTable.Columns)
bulkCopy.ColumnMappings.Add(myCol.ColumnName, myCol.ColumnName);
bulkCopy.WriteToServer(dataTable);
}

|||

Thanks for the reply Raghu. I don't need all the data elements in the XML files, only a selected few. This is an example XML file:

I only need CIF/Input and RTS/INPUT from this particular file. There will be one of this file for each day of the month and I will have to process a month's worth of xml files. There are tens of different xml files but they all follow the same schema.

Thanks.

Sunday, March 25, 2012

Bulk Insert: One Row not importing in...

Ok so I got this working:

BULK INSERT

dbo.tbCheckPointTest

FROM 'c:\inetpub\wwwroot\upload\maxie_task_aging.csv'

WITH

(DATAFILETYPE = 'char', FIELDTERMINATOR = ',',

FIRSTROW = 2, ROWS_PER_BATCH = 6, ROWTERMINATOR = '\n')

My data file looks like this:

REPORT_EXECUTED_ON:20070403_170001 Research 306 263 2470 State 1031 7 0 Program 13 1 0 Program Tech 85 69 139

Total of 6 rows, so I indicate to start on Row 2, yet only the last three lines are getting into my table.

I have changed and manipulated the query varies counts, but still just the last three lines get imported.

Thoughts?

Thanks!

Hi,

From the looks of your data file, it is difficult to tell how many blank lines are associated with your data file.

It appears that "Research" may begin on line 4, in which case you would have only three rows of data: "Research", "State", and "Program".

I am confused by the fact that you get the last three rows: "State", "Program", and "Program Tech".

Is it possible that some rows are terminated by "0A0D" and others only by "0A" or "0D", and "\n" is being interpreted as strictly "0A0D" (hexadecimal)?

I also am confused by your use of "FIELDTERMINATOR = ',' ", since I don't see any commas in the data you have displayed.

Dan

|||

Sorry. I had the .csv open with excel when I copied and pasted it:

REPORT_EXECUTED_ON:20070404_110001

Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160

I am going to see if anything you suggested will help.

|||

The problem seems to be that the BULK INSERT function seems to expect all the rows in the file to have the correct number of fields for the target table (even those being skipped). So it is ignoring the newlines until it has found enough commas (and then counts any other commas until it finds the row terminator as part of that field).

Try loading the file:

REPORT_EXECUTED_ON:20070404_110001,,,
,,,
Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160

Though you will need a FIRSTROW = 3

If you put the last field into a varchar rather than an int then if you add commas to the end (e.g. 2635,,,,) then you will find that these commas end up in that field (if not then it will complain about unexpected characters when it tries to convert it to an int or whatever).

So any rows to be skipped should have the correct number of field separators in them (3) or they will not be included in the skip count.

|||

Thank you, that solved the issue. Now I have to get w/the programmer who delivers the file to me.

|||

Nice catch, Dhericean!

Dan

Thursday, March 22, 2012

Bulk Insert Statement

I am trying to perform bulk insert but it is not working.

I created a table

INSERT INTO [demo].[dbo].[test]

([ID] ,[Plates] ,[Driver])

VALUES

(<ID, int,> ,<Plates, varchar(7),> ,<Driver, varchar(7),>)

I am tryin to insert a file.txt

100, 091-184, DOUG798
101, 406-846, DALL152
102, 384-080, TIZE489
103, 064-460, NAMO927
104, 101-366, CETI001
105, 109-366, JESS111

Any help, please

Juvan

hi Juvan,

the syntax you used is not correct for BULK INSERT statement,
have a look at http://msdn2.microsoft.com/en-us/library/ms188365.aspx for the statement's full synopsis and syntax..

[FILE d:\fmt.txt]

9.0

1 SQLCHAR 0 10 "," 1 FirstName "Latin1_General_CI_AS"

2 SQLCHAR 0 10 "," 2 LastName "Latin1_General_CI_AS"

3 SQLCHAR 0 8 "\r\n" 3 BDate "Latin1_General_CI_AS"

[/FILE d:\fmt.txt]

[FILE d:\studs.txt]

Juvan, Bonni, 19701015

Andrea, Montanari, 19651030

[/FILE d:\studs.txt]

SET NOCOUNT ON;

USE tempdb;

CREATE TABLE dbo.Students(

FirstName varchar(10) NOT NULL,

LastName varchar(10) NOT NULL,

BDate datetime NOT NULL

);

BULK INSERT dbo.Students

FROM 'd:\studs.txt'

WITH(FORMATFILE = 'd:\fmt.txt') ;

SELECT * FROM dbo.Students;

DROP TABLE dbo.Students;

--<--

FirstName LastName BDate

- - --

Juvan Bonni 1970-10-15 00:00:00.000

Andrea Montanari 1965-10-30 00:00:00.000

regards

Monday, March 19, 2012

Bulk Insert not working in ASP

---------------------------

I have the following ASP code:

'load the conversion_dump_daily table
Response.Write("<br>Load Conversion_Dump_Daily data for " & theDate & ".")
Response.Write("<br> File Name: " & inputFile & ".")
Response.flush
cmd.CommandText = "AddConversionDumpDaily"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("InFile", adVarChar, , 1000, "D:\DataSources\ConversionBuilder\test.txt")
On Error Resume Next
cmd.Execute
cmd.Parameters.Delete("InFile")

..... asp error checking follows

---------------------------

The stored procedure is as follows:

CREATE PROCEDURE AddConversionDumpDaily @.InFile varchar(1000)
AS
DECLARE @.ErrorSave int
SET @.ErrorSave = 0
-- Create a transaction so that a rollback can be performed in case of an error.
BEGIN TRAN
-- Bulk insert Conversion data from csv file.
EXEC('BULK INSERT Conversion_Dump_Daily FROM ''' + @.InFile + ''' WITH (FORMATFILE=''D:\FormatFiles\ConversionBuilder.fmt '') ')

-- Check to see if the insert was successful
if @.@.error <> 0
begin
SET @.ErrorSave = @.@.error -- Store the error code in a variable.
rollback tran
goto endOfBatch
end
COMMIT TRAN
endOfBatch:
RETURN @.ErrorSave -- Return the error code to determine success or failure of the process.
GO

---------------------------

The stored procedure works fine from the Query Analyzer, but nothing is inserted from the ASP page. I don't get any errors... it is just that nothing appears!

Any ideas anyone?I am not sure if you left out the code, but where are you creating the command object and adding the connection information. Also, you will not receive an error when you are using "On Error Resume Next" before your execute statement. By removing this, you will see the problem.

Good luck.

bulk insert not working from asp.netpage

Hi,

I am tryin to run the store procedure which has bulk insert command.when i run the code,it says you don't have permission to use bulk insert command. This query/storeprocedure is running succesfully in the backend(sqlserver2000).

solution is only user in Bulkadmin role has permission for bulk insert .But i don't know how to create one and use it in asp.net.

Can anyone help me in this. ..Here is the code.

string conn_string1 =@."Data Source=SENTHIL\TEST;Initial Catalog=EMPLOYEE;Integrated Security=SSPI";

SqlConnection objconn1=

new SqlConnection(conn_string1);

objconn1.Open();

SqlCommand command1=

new SqlCommand("bulkinsert",objconn1);

command1.CommandType=CommandType.StoredProcedure;

command1.ExecuteNonQuery();

Response.Write("Stored procedure excecuted");

objconn1.Close();

thanks,

kar

In http://msdn2.microsoft.com/en-us/library/ms188365.aspx

Requires INSERT and ADMINISTER BULKOPERATIONS permissions. Additionally, ALTER TABLE permission isrequired if one or more of the following is true:

Constraints exist and the CHECK_CONSTRAINTS option is not specified.

Note: Disabling constraints is the default behavior. To check constraints explicitly, use the CHECK_CONSTRAINTS option.

bulk insert not working

i MADE A USER AS bULK aDMIN BUT HE STILL CAN'T BULK iNSERT TO A TABLE . He has dd_writer and dd_reader roles assigned in database .

What should I do to fix this . Here is the error

The current user is not the database or object owner of table 'Temp_load'. Cannot perform SET operation.does the database bulk insert flag is on?|||Keep in mind only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.

Thursday, March 8, 2012

BULK Insert Errors

I'm having a little trouble with a Bulk Insert. This is my first time
working with this, so I'm not sure what I'm missing here. I've read
everything I can find about Bulk Insert in BOL and from querying the
archive, but I'm not finding the answer.
This is the error I'm getting when attempting to Bulk Insert the data into a
table in a local MSDE DB named GACT:
Server: Msg 4865, Level 16, State 1, Line 1
Could not bulk insert because the maximum number of errors (10) was
exceeded.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
This is the Table def:
CREATE TABLE [GACT2] (
[id] [uniqueidentifier] NOT NULL ,
[Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
These are a couple of lines from the Import file:
14|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
15|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
AVERAGE UNIT PRICE TRANSACTION
And this is my Bulk Insert Query:
Delete from GACT2
GO
BULK INSERT GACT.dbo.GACT2
FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
KEEPNULLS,
KEEPIDENTITY
)
TIA
MattYour problem is with the first field - you are trying to push what appears
to be an int value (14) into a unique identifier column. So your data is
incompatible with the table. You have to get valid data or change your
schema or pipe it to a holding table.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OlnRNS0MHHA.3268@.TK2MSFTNGP04.phx.gbl...
> I'm having a little trouble with a Bulk Insert. This is my first time
> working with this, so I'm not sure what I'm missing here. I've read
> everything I can find about Bulk Insert in BOL and from querying the
> archive, but I'm not finding the answer.
> This is the error I'm getting when attempting to Bulk Insert the data into
> a table in a local MSDE DB named GACT:
> Server: Msg 4865, Level 16, State 1, Line 1
> Could not bulk insert because the maximum number of errors (10) was
> exceeded.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
> This is the Table def:
> CREATE TABLE [GACT2] (
> [id] [uniqueidentifier] NOT NULL ,
> [Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [portcode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Buy_Sell] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SourceCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CUSIP] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProcDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TradeDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SettlementDate] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmt] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranAmtSign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Quantity] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QuantitySign] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> These are a couple of lines from the Import file:
> 14|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> 15|3J6|897000|117236710|B|||20070103|20070103|20070104|000000000080000000|+|000000000044800000|+|CITIGROUP
> INC COM SOLICITED ORDER OTC OR NASDAQ EXCH DETAILS ON REQUEST
> AVERAGE UNIT PRICE TRANSACTION
> And this is my Bulk Insert Query:
> Delete from GACT2
> GO
> BULK INSERT GACT.dbo.GACT2
> FROM 'E:\Import GACT\Files\Exports\NEW\GACT.DML'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> KEEPNULLS,
> KEEPIDENTITY
> )
> TIA
> Matt
>|||> Your problem is with the first field - you are trying to push what appears
> to be an int value (14) into a unique identifier column. So your data is
> incompatible with the table. You have to get valid data or change your
> schema or pipe it to a holding table.
That's not an issue. I'm creating the output file myself, so I can remove
that field if I need to. What would be the best way to have a unique ID
column? Do I need to leave it null in my import file and let SQL generate
it? Or should I just change the table def for that column to INT(14)
instead?
TIA
Matt|||>> Your problem is with the first field - you are trying to push what
>> appears to be an int value (14) into a unique identifier column. So your
>> data is incompatible with the table. You have to get valid data or change
>> your schema or pipe it to a holding table.
> That's not an issue. I'm creating the output file myself, so I can remove
> that field if I need to. What would be the best way to have a unique ID
> column? Do I need to leave it null in my import file and let SQL generate
> it? Or should I just change the table def for that column to INT(14)
> instead?
I did some testing and ended up changing that field in my table to INT and
now I get past that error. Now I'm getting a new error that says
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 15
(Description).
I have the description field defined as [Description] [varchar] (240)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
The description field in my file is always 240 chars. It CAN'T be any bigger
because that's the number of bytes I'm extracting for that field and writing
to the file. I don't get it. Changing it to [TEXT] helps, but it only
imports up to that field for one row and stops.|||I found it odd that you're using \n alone as a rowterminator. It *looks* to
me like your data may naturally contain carriage returns and/or line feeds,
unless that was a bad copy & paste, so I'd make sure that you use a proper
delimiter that can't appear in your data.
A
"Matt Williamson" <ih8spam@.spamsux.org> wrote in message
news:OY3ovA2MHHA.992@.TK2MSFTNGP04.phx.gbl...
>> Your problem is with the first field - you are trying to push what
>> appears to be an int value (14) into a unique identifier column. So your
>> data is incompatible with the table. You have to get valid data or
>> change your schema or pipe it to a holding table.
>> That's not an issue. I'm creating the output file myself, so I can remove
>> that field if I need to. What would be the best way to have a unique ID
>> column? Do I need to leave it null in my import file and let SQL generate
>> it? Or should I just change the table def for that column to INT(14)
>> instead?
> I did some testing and ended up changing that field in my table to INT and
> now I get past that error. Now I'm getting a new error that says
> Server: Msg 4863, Level 16, State 1, Line 1
> Bulk insert data conversion error (truncation) for row 1, column 15
> (Description).
> I have the description field defined as [Description] [varchar] (240)
> COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> The description field in my file is always 240 chars. It CAN'T be any
> bigger because that's the number of bytes I'm extracting for that field
> and writing to the file. I don't get it. Changing it to [TEXT] helps, but
> it only imports up to that field for one row and stops.
>|||>I found it odd that you're using \n alone as a rowterminator. It *looks*
>to me like your data may naturally contain carriage returns and/or line
>feeds, unless that was a bad copy & paste, so I'd make sure that you use a
>proper delimiter that can't appear in your data.
The lines are pretty long, so the copy of it wrapped. Each row of data is on
one line in my file and is terminated with 0D 0A Hex. Is that \n or \r\n?
I've tried both. I also tried adding just a ! at the end of the row and
using "|!" as the terminator, but it didn't change anything either.|||> >I found it odd that you're using \n alone as a rowterminator. It *looks*
> >to me like your data may naturally contain carriage returns and/or line
> >feeds, unless that was a bad copy & paste, so I'd make sure that you use
> >a proper delimiter that can't appear in your data.
> The lines are pretty long, so the copy of it wrapped. Each row of data is
> on one line in my file and is terminated with 0D 0A Hex. Is that \n or
> \r\n? I've tried both. I also tried adding just a ! at the end of the row
> and using "|!" as the terminator, but it didn't change anything either.
Nevermind. It was a standard 1d10t error. I changed the path to where I was
generating the output file in my code, but forgot to change the Bulk Insert
query in QA to reflect that. It works perfectly now.
Thanks for anyone that posted.

Saturday, February 25, 2012

BULK INSERT - A general question

Hi NG,
I am working with BULK INSERT`s have some general questions about BULK
INSERT:
- how does a BULK INSERT really works within the database? Why is it
faster?
- why can I only pass a file to a BULK INSERT?
- are there ways to tune the BULK INSERT performance?
Thank you very much
Rudi> how does a BULK INSERT really works within the database? Why is it faster?
BULK_INSERT is a firehose cursor. This means data is directly streamed from
its original location to SQL Server.

> - why can I only pass a file to a BULK INSERT?
Other types of locations, like a database, require special drivers.

> - are there ways to tune the BULK INSERT performance?
Through batch size primarily, either KB per batch or rows per batch.
Explicitly setting the code page, etc., can help, but only slightly.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
****************************************
*******
Think Outside the Box!
****************************************
*******
<rudolf.ball@.asfinag.at> wrote in message
news:1132145686.696338.65250@.g43g2000cwa.googlegroups.com...
> Hi NG,
> I am working with BULK INSERT`s have some general questions about BULK
> INSERT:
> - how does a BULK INSERT really works within the database? Why is it
> faster?
> - why can I only pass a file to a BULK INSERT?
> - are there ways to tune the BULK INSERT performance?
> Thank you very much
> Rudi
>

Friday, February 24, 2012

Bulk insert

I m a newbie in Stored Proc. Here I m working with some stuff for importing the csv files then write those inside to MSSQL Server. However I get confused what steps I shoud take.

Here's the Stored Proc I've written.

Instead of running execute proc_TestIT '11113333', 'V', 'Tony Jones , those records would be kept in a csv file instead with at least 100 records per each csv file. I am wondering if I should use bulk insert and what I should do with the SP I 've written. As only 10 out of all the 20 columns in the interface file would be required for the updates/inserts. I m wondering where I should start.

execute proc_TestIT '11113333', 'V', 'Tony Jones'
CREATE Procedure proc_TestIT

@.Locker_No Varchar(10),
@.Locker_Type Varchar(10),
@.Member_Full_Name Varchar(30)

AS
declare @.Location Varchar(06)
set @.Location=' '
Begin
BEGIN TRANSACTION
SET @.Location = (select Location from tblLockers where Locker_No=@.Locker_No)
IF (@.Locker_Type='P')
begin
Update tblLockerIssues SET Remarks='Damaged' where Locker_No=@.Locker_No
end
IF(@.Locker_Type='V')
begin
insert into tblLockerIssues_VIP values(@.Locker_No,@.Location,'')
end
COMMIT TRANSACTION
RETURN
End
GO

There are several basic building blocks that you can use:

Consider using SSIS (DTS if you are running SQL Server 2000) instead of a stored procedure|||

Hi Dave,

Thanks a lot. For the stuff I m working with, we have to enable users to import the csv files with the use of ASP.

As such imports would trigger inserts/updates in several tables e.g. by check each record's member_type in the inteface files, I m wondering if I should make use of ASP to extract data from those columns I need from the csv files then import into the DB.

Cheers,

Manfred

bulk import to sql 200

How can i implement this ussing bulk load or do i need to find other way to import it?
it was working until i recieved a new file with CDATA.
in advance thanks.
here is a sample xml
<data>
<row>
<MYDIV_CDE><![CDATA[ES ]]></MYDIV_CDE>
<MYDIV_DESC><![CDATA[Esc ]]></MYDIV_DESC>
</row>
</data>
here is my xsd
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="row" sql:relation="TblVMDDivisions" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="DivisionIdent" type="xsd:integer" />
<xsd:element name="MYDIV_CDE" type="xsd:string"
sql:use-cdata="1"/>
<xsd:element name="MYDIV_DESC" type="xsd:string"
sql:use-cdata="1" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema
this is error that i get
<?xml version="1.0"?>
<Result State="FAILED">
<Error><HResult>0xC00CEE2B</HResult>
<Description><![CDATA[Illegal xml character.]]></Description>
<Source>XML SAX Parser</Source>
<Type>FATAL</Type>
</Error>
</Result>
Felix
hi there,
by testing the code I found out that if xml file has only one 'row' it works just fine but if it has more than one it fails with the same error that i post in original message.
any ideas why i will work for one row at the time but not for more than one?
Felix
"Felix" wrote:

> How can i implement this ussing bulk load or do i need to find other way to import it?
> it was working until i recieved a new file with CDATA.
> in advance thanks.
> here is a sample xml
> <data>
> <row>
> <MYDIV_CDE><![CDATA[ES ]]></MYDIV_CDE>
> <MYDIV_DESC><![CDATA[Esc ]]></MYDIV_DESC>
> </row>
> </data>
>
> here is my xsd
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="row" sql:relation="TblVMDDivisions" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="DivisionIdent" type="xsd:integer" />
> <xsd:element name="MYDIV_CDE" type="xsd:string"
> sql:use-cdata="1"/>
> <xsd:element name="MYDIV_DESC" type="xsd:string"
> sql:use-cdata="1" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema
> this is error that i get
> <?xml version="1.0"?>
> <Result State="FAILED">
> <Error><HResult>0xC00CEE2B</HResult>
> <Description><![CDATA[Illegal xml character.]]></Description>
> <Source>XML SAX Parser</Source>
> <Type>FATAL</Type>
> </Error>
> </Result>
>
> Felix
|||What are the characters inside the CDATA section?
Note that XML does not allow certain binary code points (such as the ESC
code point) regardless of whether they are inside a CDATA section or not.
Best regards
Michael
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:24962467-0738-46CD-B286-96A80F53225D@.microsoft.com...[vbcol=seagreen]
> hi there,
> by testing the code I found out that if xml file has only one 'row' it
> works just fine but if it has more than one it fails with the same error
> that i post in original message.
> any ideas why i will work for one row at the time but not for more than
> one?
> Felix
> "Felix" wrote:

Friday, February 10, 2012

Building a database

I am working with SQL server 2000. The database is installed in my machine. Now I have got the ".ldf" and ".mdf" file pertaining to a database from someother server.

Is it possible to build the database present in the above said files in my server.

NOTE : I dont have a direct access to the remote server from where the above said files were obtained (otherwise I think the DTS utility would have come in handy).An example from The Holy book
This example attaches two files from pubs to the current server.

EXEC sp_attach_db @.dbname = N'pubs', @.filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', @.filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'|||Thanks for the instant help . That worked !|||BTW, the imported database seems to have a lot of procedures like dt_addtosourcecontrol, dt_adduserobject etc.. what are these for ?|||You might want to take a peek at Microsoft Knowledge Base Article 218172 when attaching/detaching databases on different servers.