Bulk load multiple rows

Hi
I'm a total newbie in this area and would appreciate some help regarding
sqlxml bulk load. I have a xml file like below:
<voyage>
<portcalls>
<portcall>
<port_code>bjasta</port_code>
</portcall>
<portcall>
<port_code>ovik</port_code>
</portcall>
</portcalls>
</voyage>
My xsd-schema for this file is like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:relation="portcode" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType> </xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
When executing SQLXML Bulk Load-object the following error occurs: "Data
mapping to column 'port_code' was already found in the data. Make sure that
no two schema definitions map to the same column."
I want the parser to bulk load new rows into my table "portcode" for every
port_code-element found in the xml-file, but this code only works when havin
g
only one port-code-element in the xml-file.
What is the issue here? Is there a way to get this scenario to work?
Thanks in advance for your help. My deadline is closing in on me :-(
Regards
Daniel Nhttp://msdn.microsoft.com/library/d... />
sqlxml.asp|||Hi,
You need to slightly modify the schema to look like this :
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="voyage" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcalls" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="portcall" minOccurs="0" maxOccurs="unbounded"
sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="0" sql:relation="portcode"
maxOccurs="unbounded" name="port_code"
type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this will solve your problem.
Best Regards,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm" .

Bulk Inserting Data into a new table with different ID's that need to be changed

Hi

I have a system which Profiles people. However a new profiler has been written which is better, and all the old profiled members have to be moved to the new profiler tables.

I have a Members table with member data in it, it contains the old profile data in the table too, or at least the ID's per member.

In the new profiler things work slightly different.

I'll give an example:

Lets say MemberID 3241 is a smoker, SmokerID = 3 (Moderate Smoker)

And he drinks occasionally, AlcoholID = 2 (Light)

These ID fields are kept in the Members table which link to a table of their own i.e. memSmoking Table, or memAlcohol Table

The new profiler table works different. In the sense that the Alcohol and Smoking are all in the same table, but with different OptionID's and ValuesID's

Here is some sample code that i wrote. Just copy and paste, it will give you a base to work from. I'm trying my best to supply as much information as possible, so if i left anything out then please let me know? And thanks for the help in advance

Code Snippet

--Sample Code:

--Members Table

DECLARE @.Members TABLE (MemberID INT IDENTITY(1,1),

ClientID INT,

Name VARCHAR(50),

Surname VARCHAR(50),

GenderID CHAR(1),

MaritalStatusID INT,

MemSmokingID INT,

MemAlcoholID INT)

INSERT INTO @.Members VALUES (211, 'Carel','Greaves', 'M', 2, 1, 3)

INSERT INTO @.Members VALUES (211, 'Jill', 'Jenkins', 'F', 1, 3, 4)

SELECT * FROM @.Members

--

--Profile Attrubutes

--

DECLARE @.memGender TABLE (GenderID CHAR(1), Description VARCHAR(50))

INSERT INTO @.memGender VALUES ('M', 'Male')

INSERT INTO @.memGender VALUES ('F', 'Female')

SELECT * FROM @.memGender

DECLARE @.memMaritalStatus TABLE (MaritalStatusID INT, Description VARCHAR(50))

INSERT INTO @.memMaritalStatus VALUES (1, 'Married')

INSERT INTO @.memMaritalStatus VALUES (2, 'Single')

INSERT INTO @.memMaritalStatus VALUES (3, 'Devorced')

INSERT INTO @.memMaritalStatus VALUES (4, 'Widowed')

SELECT * FROM @.memMaritalStatus

DECLARE @.memSmoke TABLE (MemSmokingID INT, Description VARCHAR(50))

INSERT INTO @.memSmoke VALUES (1, 'Nil')

INSERT INTO @.memSmoke VALUES (2, 'Light')

INSERT INTO @.memSmoke VALUES (3, 'Moderate')

INSERT INTO @.memSmoke VALUES (4, 'Heavy')

SELECT * FROM @.memSmoke

DECLARE @.memAlcohol TABLE (MemAlcoholID INT, Description VARCHAR(50))

INSERT INTO @.memAlcohol VALUES (1, 'Nil')

INSERT INTO @.memAlcohol VALUES (2, 'Light')

INSERT INTO @.memAlcohol VALUES (3, 'Moderate')

INSERT INTO @.memAlcohol VALUES (4, 'Heavy')

SELECT * FROM @.memAlcohol

--

--New Profile Attributes

--

DECLARE @.MemberOptionAtributes TABLE (ValuesID INT IDENTITY(1,1),

OptionID INT,

DisplayName VARCHAR(50))

INSERT INTO @.MemberOptionAtributes VALUES (2, 'Male')

INSERT INTO @.MemberOptionAtributes VALUES (2, 'Female')

INSERT INTO @.MemberOptionAtributes VALUES (3, 'Married')

INSERT INTO @.MemberOptionAtributes VALUES (3, 'Single')

INSERT INTO @.MemberOptionAtributes VALUES (3, 'Devorced')

INSERT INTO @.MemberOptionAtributes VALUES (3, 'Widowed')

INSERT INTO @.MemberOptionAtributes VALUES (4, 'Nil')

INSERT INTO @.MemberOptionAtributes VALUES (4, 'Light')

INSERT INTO @.MemberOptionAtributes VALUES (4, 'Moderate')

INSERT INTO @.MemberOptionAtributes VALUES (4, 'Heavy')

INSERT INTO @.MemberOptionAtributes VALUES (5, 'Nil')

INSERT INTO @.MemberOptionAtributes VALUES (5, 'Light')

INSERT INTO @.MemberOptionAtributes VALUES (5, 'Moderate')

INSERT INTO @.MemberOptionAtributes VALUES (5, 'Heavy')

SELECT * FROM @.MemberOptionAtributes

--MemberProfileLookupValues Table

DECLARE @.MemberProfileLookupValues TABLE (EntryID INT IDENTITY(1,1),

MemberID INT,

OptionID INT,

ValueID INT)

--This is how the data should be inserted, but i don't know how to do it in bulk for all the members in the members table (There are over 1000000)!

INSERT INTO @.MemberProfileLookupValues VALUES (1, 1, 1)

INSERT INTO @.MemberProfileLookupValues VALUES (1, 3, 5)

INSERT INTO @.MemberProfileLookupValues VALUES (1, 4, 7)

INSERT INTO @.MemberProfileLookupValues VALUES (1, 5, 13)

INSERT INTO @.MemberProfileLookupValues VALUES (2, 1, 2)

INSERT INTO @.MemberProfileLookupValues VALUES (2, 3, 3)

INSERT INTO @.MemberProfileLookupValues VALUES (2, 4, 9)

INSERT INTO @.MemberProfileLookupValues VALUES (2, 5, 14)

SELECT * FROM @.MemberProfileLookupValues

--Here is a piece of actual code that i tried that i thought would work but it inserted all the fields for all options for all members (WHOOPS!)

/*SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'

WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM Members M

INNER JOIN _MemberProfileLookupValues ML ON M.MemberID = ML.MemberID

WHERE M.Active = 1

AND ML.OptionID <> 6

GO

*/

Carel:

What is the objective here; I am afraid I missed it.

|||

Old Profiler uses these tables

@.Members

@.memSmoking

@.memAlcohol

@.memMaritalStatus

New Profiler uses these Tables

@.MemberOptionAtributes

@.MemberProfileLookupValues

So the memberID From the @.members table has to be inserted into the @.memberProfileLookupValues table, where the OptionID matched the type of attribute i.e. Smoking, Alcohol etc

Smoking = OptionID (4 in this case) i get the OptionID values from the @.MemberOptionAtributes table

Alcohol = OptionID (5 in this case) i also get the OptionID values from the @.MemberOptionAttributes table

When it comes to the memory tables i created, just look at the INSERT STATEMENT for the @.MemberProfileLookupValues table at the bottom and compare to the fields in the @.Members table.

OptionID's Come from the @.MemberOptionAtributes table. The valuesID's just have to be set to the right places.

for example:

If you look at the memSmoking Table

it has 4 ID's

1

2

3

4

In the @.MemberOptionAtributes table the OptionID for Smoking is 4

as far as the values are concerned for smoking in the MemberOptionAtributes table

@.memSmoking .memSmokingID(1) = @.MemberOptionAtributes .ValueID(7)

@.memSmoking .memSmokingID(2) = @.MemberOptionAtributes .ValueID(8)

@.memSmoking .memSmokingID(3) = @.MemberOptionAtributes .ValueID(9)

@.memSmoking .memSmokingID(4) = @.MemberOptionAtributes .ValueID(10)

I hope this helps, i'll keep trying to give as much info as possible.

Kind Regards

Carel Greaves

=== Edited by Carel Greaves @. 23 Jun 2007 9:32 PM UTC===

This was the porst that i posted yesterday, i sat thinking about the problem again today, and came up with something that might help me a little more, and maybe unconfuse the situation.

I want to insert all the generated memberID's in the members table into the @.MemberProfileLookupValues table with the AlcoholID's and MemSmokingID's

Hovever the New Profiler accepts the MemberID, OptionID, and ValuesID
Where the old way it was done was basically a pivotted way of doing it, i'm basically just un-pivvotting the table in the new table.

--Real Code that i actually used.
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT m.MemberID, '12', CASE MC.HealthInterestID
WHEN 1 THEN '71'
WHEN 2 THEN '72'
WHEN 3 THEN '73'
WHEN 4 THEN '74'
WHEN 5 THEN '75'
WHEN 6 THEN '76'
WHEN 7 THEN '77'
WHEN 8 THEN '78'
WHEN 9 THEN '79'
WHEN 10 THEN '80'
WHEN 11 THEN '81'
WHEN 12 THEN '82'
WHEN 13 THEN '83'
WHEN 14 THEN '84'
END
FROM Members m, _MemberProfileLookupValues ml, memHealthInterests mc
WHERE m.memberID = ml.MemberID
AND m.MemberID = mc.MemberID
AND m.Active = 1
AND ml.OptionID &lt;&gt; 12
GO

The problem that i had with this statement is that it Inserted the memberID with all of these field per memberID, instead of looking for where the values is = lets say 9 and insering the values 79 (According to the CASE)

|||

This was the post is started two days ago, wouold someone please be able to help me out.

Just look at my @.Members table and the @.MemberProfileLookupValues

This was the porst that i posted yesterday, i sat thinking about the problem again today, and came up with something that might help me a little more, and maybe unconfuse the situation.

I want to insert all the generated memberID's in the members table into the @.MemberProfileLookupValues table with the AlcoholID's and MemSmokingID's

Hovever the New Profiler accepts the MemberID, OptionID, and ValuesID
Where the old way it was done was basically a pivotted way of doing it, i'm basically just un-pivvotting the table in the new table.

Code Snippet

--Real Code that i actually used.
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT m.MemberID, '12', CASE MC.HealthInterestID
WHEN 1 THEN '71'
WHEN 2 THEN '72'
WHEN 3 THEN '73'
WHEN 4 THEN '74'
WHEN 5 THEN '75'
WHEN 6 THEN '76'
WHEN 7 THEN '77'
WHEN 8 THEN '78'
WHEN 9 THEN '79'
WHEN 10 THEN '80'
WHEN 11 THEN '81'
WHEN 12 THEN '82'
WHEN 13 THEN '83'
WHEN 14 THEN '84'
END
FROM Members m, _MemberProfileLookupValues ml, memHealthInterests mc
WHERE m.memberID = ml.MemberID
AND m.MemberID = mc.MemberID
AND m.Active = 1
AND ml.OptionID &lt;&gt; 12
GO

The problem that i had with this statement is that it Inserted the memberID with all of these field per memberID, instead of looking for where the values is = lets say 9 and insering the values 79 (According to the CASE)

|||

Carel,

I've been hoping (obviously, against hope) that you would see the mistakes inherrent in attempting to shove a EAV data model into a relationation data engine. Here, I'm going to allow someone else to attempt to explain it again. (From: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61024&whichpage=2, Michael Jones:

I think that the query you posted is a perfect illustration of the biggest disadvantage of the Entity/Attribute model, that it saves a little work up front in data modeling by allowing “open ended” insertion of new attributes at the cost of having to program the true data structure into each query. Of course, there are other annoying little problems, like enforcing not null, DRI, domain integrity, default values, check constraints, creating useful indexes, transactional integrity, etc. Basically, it takes all the most useful features of a relational data model, and throws them away.

I have to revise my other non-PC comment: "Encoded in binary, then base64 encoded into text? That’s one of the stupidest thing I've ever seen, but it looks like a stoke of genius compared to using an Entity/Attribute data model!!! What brain-dead morons came up with that!? I know it had to be a committee, because no one could be that stupid all on their own!"

The 'Original Members' table is the 'best' way to store this data. Both attempts of using some form of EAV is a bastardization of the relational database, and will always cause you more grief than it is worth. The task at hand is but one more example.

Using the code you provided (and thank you for the DDL and sample data!!!), it seems that you may be over-complicating the process. I think that it will work for you like this:

Code Snippet

INSERT INTO @.MemberProfileLookupValues
( MemberID,
OptionID,
ValueID
)
SELECT
m.MemberID,
'6',
CASE m.MaritalStatusID
WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
ELSE NULL
END
FROM @.Members m

EntryID MemberID OptionID ValueID
-- -- -- --
1 1 6 8
2 2 6 7

Please do yourself a favor and do some research on the issues, problems, and pitfalls related to EAV data and relational databases.

(There are EAV databases available -I don't know how they evaluate...)

|||

Hi Arnie

Thanks for the help with that thread. I read what went on there and i agree with what the guys are talking about. Unfortunately i have been given the task of converting from the old model (Which is right) to the new model i.e. (EAV).

I don't have much say in the matter as the company outsourced the EAV system which was created and actually paid money for it.

When i ran my query to insert the memberID's into the EAV model then it would run through the whole case statement and insert each value per member. for example.

Instead of looking for where the value is 1 and then assigning it value 7.

It would insert values (7, 8, 9, and 10) per memberID for all memberID's. That was my problem. (And it would do it Multiple Times)

Maybe something is wrong, but it looks right to me and it makes sense. (i'm not too concerned about the type of model at present moment) i just want the data to go into the tables right.

Code Snippet

--INSERT memSmokingID Data of Members from Members Table

SET NOCOUNT ON

INSERTINTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT m.MemberID,'9',CASE M.MemSmokingID

WHEN 1 THEN'59'

WHEN 2 THEN'60'

WHEN 3 THEN'61'

WHEN 4 THEN'62'

END

FROM Members M

INNERJOIN _MemberProfileLookupValues ML ON M.MemberID = ML.MemberID

WHERE M.Active = 1

AND ml.OptionID <> 9

GO

|||

Why do you JOIN to [_MemberProfileLookupValues]?

That JOIN produces a row in the resultset for each row in [_MemberProfileLookupValues] -and I think that you only want one row per [Members] row.

Unless I don't have a complete picture of the data, you would be better off, as in my previous example, leaving the JOIN and WHERE clause out of the query. (I suspose you could still have the [m.Active = 1] filter though...)

|||

As i said in my previous post, stupidity resides EVERYWHERE

Thanks Arnie (AGAIN!!!) he he

makes sense now to me why it would keep on inserting more and more duplicates.

Bulk insert Unc path problems

Hi!
I have an webapp that uses bulk insert to insert data into an sql server. This works fine when sqlserver and webserver is on the same machine. But when i tried to use seperate machines i get access denied message when trying bulk insert. Ive created a share on the webserver where i store the files to be bulk inserted. And I use the appropiate unc path to the file to be bulk inserted in SQL query. I guess it has something with permissions on the share or security.
What permissions do I need to make this work.
Thanks.
Niclas AhlqvistI've never tried a bulk insert using a UNC path to another machine, but at a guess:
You will need to give permission to the account that SQL Server is running in. Often, for security reasons, the account is a machine account so that, if the server is compromised, the SQL Server process cannot damage other parts of the network. If your SQL Server is running in a machine account you will need to give it a domain account to run in so that it can access the file.
Alternatively, copy the file to the SQL Server machine first.sql

Bulk insert problem

Hi!
I have done bcp out using -V and -q switch from one table and using only -V
switch on another table on the server running on sql server 2000. the
datatype used is native i.e. -n switch.
When I am trying to import it using BCP IN in the server having same table
structures running on sql server 7.0, process is completing successfully. Bu
t
I want to use Bulk Insert and its not importing data on sql server 2000
rather throwing error.
ref:
truncate table rplctn_cnt_lg
Bulk insert rplctn_cnt_lg from 'c:\rpl\11072CNT.txt' with
(DATAFILETYPE='NATIVE')
Error
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.
The statement has been terminated.
Kindly help me with this solution. We have one project in hand where the
replication uses BCP and Bulk Insert combination. No of tables affected more
than 200. No. of sites more than 1000 where sql server 7.0/msde is there. On
e
master database running on sql server 2000.
TIA... Regards,
Piyush AgarwalHi
Assuming you are using the correct version number with the -V flags, you may
want to check out:
http://tinyurl.com/6fg44
John
"Piyush" wrote:

> Hi!
> I have done bcp out using -V and -q switch from one table and using only -
V
> switch on another table on the server running on sql server 2000. the
> datatype used is native i.e. -n switch.
> When I am trying to import it using BCP IN in the server having same table
> structures running on sql server 7.0, process is completing successfully.
But
> I want to use Bulk Insert and its not importing data on sql server 2000
> rather throwing error.
> ref:
> truncate table rplctn_cnt_lg
> Bulk insert rplctn_cnt_lg from 'c:\rpl\11072CNT.txt' with
> (DATAFILETYPE='NATIVE')
> Error
> 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.
> The statement has been terminated.
> Kindly help me with this solution. We have one project in hand where the
> replication uses BCP and Bulk Insert combination. No of tables affected mo
re
> than 200. No. of sites more than 1000 where sql server 7.0/msde is there.
One
> master database running on sql server 2000.
> TIA... Regards,
> Piyush Agarwal
>

Bulk Insert of excel sheet

Hi

I need to bulk insert a excel sheet into a sql 2005 db datatable. I have to do this with three different excel files, inserting them into three different tables (each excel file has one sheet). This works like a charm for two of them, one excel file is causing troubles, as data types of the columns of the inserted data sheet are 'ntext'. This ntext declaration is causing problems within my app where I access that table.

So, any idea where I can set what datatype the columns of an inserted excel sheet should be within the sql datatable? I need the columns to be varchar(255) as it is by doing this with the two other excel files. The excel file causing troubles is being generated by another app.

Any help would be much appreciated!

t-sql code:

USE KOMAX
GO

EXEC sp_dboption Komax, 'select into/bulkcopy',True
EXEC sp_dboption Komax, 'ansi_nulls',True
EXEC sp_dboption Komax, 'ansi_warnings',True
GO

-- Delete existing Table
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Messages')
DROP TABLE Messages
-- Insert Excel Sheet
SELECT * INTO Messages FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\Messages.XLS', [Messages$])
GO

EXEC sp_dboption Komax, 'select into/bulkcopy',False
EXEC sp_dboption Komax, 'ansi_nulls',False
EXEC sp_dboption Komax, 'ansi_warnings',False
GO

Best Regards
Benjamin

Capone wrote:

The excel file causing troubles is being generated by another app.

I would say you've already found the problem.

Adamus

|||But it somehow has to be possible to define what datatype the excel columns are after inserted in sql!|||

Of course there are many ways.

Traditionally:

ALTER TABLE MyTable

ALTER COLUMN MyField varchar(255)

Adamus

|||That's a way, right. I'm just trying to figure out where this ntext is coming from and where to change that behaviour. My concern is: what if one of the other fil starts causing this problem as well? For example: if the 3rd part app which generates this files changes just something? I prefer a safe solution...

I don't wanna alter my whole database after a bulk insert.

But thanks a lot for your posts though!|||

Is the application creating the table (at runtime) before the insert?

If so, the datatype is hardcoded into the application. You can make the change there.

If the table already exists, change the datatype with the previous posted code and that should resolve the issue. There shouldnt' be a conversion problem frm ntext to varchar

Adamus

Bulk Insert Import Question

Hi:
I am importing data from text files in sql tables using bulk insert object
in dts package. First row of text files contains headings. For some text
files, headings are also imported in the tables and for some headings are no
t
imported. I am not sure why is that happening.
I do not want to import headings. Is there anyway to tell bulk insert
object to not import first row (headings)? Please let me know.
Thanks a lotBULK INSERT [YourTable] FROM 'c:\yourfile.txt'
WITH (FIRSTROW = 5)
Where 5 indicates "start importing from the 5th row of text". Adjust for
your import as necessary. Don't use quotation marks around the number.
"Mike" wrote:

> Hi:
> I am importing data from text files in sql tables using bulk insert object
> in dts package. First row of text files contains headings. For some text
> files, headings are also imported in the tables and for some headings are
not
> imported. I am not sure why is that happening.
> I do not want to import headings. Is there anyway to tell bulk insert
> object to not import first row (headings)? Please let me know.
> Thanks a lot|||Thank you very much
"Mark Williams" wrote:
> BULK INSERT [YourTable] FROM 'c:\yourfile.txt'
> WITH (FIRSTROW = 5)
> Where 5 indicates "start importing from the 5th row of text". Adjust for
> your import as necessary. Don't use quotation marks around the number.
> --
>
> "Mike" wrote:
>

BULK INSERT from a mapped drive

Hi
I'm trying to BULK INSERT some data from a simple text file, residing on a
mapped drive (F:\)
Well, I know, it's better to use UNC-Paths, but it was "programmed" in that
manner some years ago. (Some dozens of stored procs which I don't want to
change)
This problem persists since we upgraded from NT/2000 to Server2003 (still
using SQL 2000):
while trying to bulk insert, I get an error, that the file can't be
found/accessed. (Path is correct)
MSDTC is configured running as a Domain-Account, and accessing the network
(COM-Configuration)
MSSQLSERVER-service is also configured as a Domain-Account.
Could this be a setting in Server2003 which I didn't found yet ?
Any help would be appreciated
Many thanks & regards,
MarcFist thing to try would be to log in as the SQL Server service account as se
e if the mapping exists for that
account.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Marc" <newsgroups@.ris-nospam-web.net> wrote in message news:409143a0$0$713$5402220f@.news.su
nrise.ch...
> Hi
> I'm trying to BULK INSERT some data from a simple text file, residing on a
> mapped drive (F:\)
> Well, I know, it's better to use UNC-Paths, but it was "programmed" in tha
t
> manner some years ago. (Some dozens of stored procs which I don't want to
> change)
> This problem persists since we upgraded from NT/2000 to Server2003 (still
> using SQL 2000):
> while trying to bulk insert, I get an error, that the file can't be
> found/accessed. (Path is correct)
> MSDTC is configured running as a Domain-Account, and accessing the network
> (COM-Configuration)
> MSSQLSERVER-service is also configured as a Domain-Account.
> Could this be a setting in Server2003 which I didn't found yet ?
> Any help would be appreciated
> Many thanks & regards,
> Marc
>|||Tibor,
Yes, the mapped drive is visible & accessible by the Account.
Marc
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:O0QxpUhLEHA.1388@.TK2MSFTNGP09.phx.gbl...
> Fist thing to try would be to log in as the SQL Server service account as
see if the mapping exists for that
> account.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Marc" <newsgroups@.ris-nospam-web.net> wrote in message
news:409143a0$0$713$5402220f@.news.sunrise.ch...
a[vbcol=seagreen]
that[vbcol=seagreen]
to[vbcol=seagreen]
(still[vbcol=seagreen]
network[vbcol=seagreen]
>|||Hmm, then I can only assume that there's some new stuff in W2K3 that I haven
't heard about before. Perhaps
post this to a Windows forum as a general question ("service accessing share
through mapped drive")?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Marc" <newsgroups@.ris-nospam-web.net> wrote in message news:40914a26$0$704$5402220f@.news.su
nrise.ch...
> Tibor,
> Yes, the mapped drive is visible & accessible by the Account.
> Marc
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:O0QxpUhLEHA.1388@.TK2MSFTNGP09.phx.gbl...
> see if the mapping exists for that
> news:409143a0$0$713$5402220f@.news.sunrise.ch...
> a
> that
> to
> (still
> network
>|||Tibor,
Thank's for your help...
Marc
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:uoy$WViLEHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hmm, then I can only assume that there's some new stuff in W2K3 that I
haven't heard about before. Perhaps
> post this to a Windows forum as a general question ("service accessing
share through mapped drive")?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Marc" <newsgroups@.ris-nospam-web.net> wrote in message
news:40914a26$0$704$5402220f@.news.sunrise.ch...
schrieb[vbcol=seagreen]
as[vbcol=seagreen]
residing on[vbcol=seagreen]
in[vbcol=seagreen]
want[vbcol=seagreen]
>

bulk insert fails

Hi

I have a page that bulkinsert data to my sql server, I build up the bulk insert part like this...

1 sb.Append("Exec p_BulkInsertPDI'<ROOT><PROT>")23 sb.Append("<PDI NID=""" & HiddenField1.Value & """ AID=""" & HiddenField1a.Value & """ MID="" GID="" UID=""" & UserID & """/>")45 sb.Append("</PROT></ROOT>'")

The problem I have here is that sometimes the AID value doesn't have any value beacuse on the previous page haven't sent any value to that hiddenfield.
So when I try to run this, I get a error message like this... "Conversion failed when converting the nvarchar value 'AID=' to data type int".

It would be the best if I could insert Null values if no value have been provided. Is this possible to do?

Regards

You could use the IF and Only If , e.g

sb.append("...." & _

IIF(hiddenvalue.value = "", "NULL", hiddenvalue.value) & _

".........")

EDIT: Got the syntax slightly wrong - it has been corrected.

|||Ahhh.. This sounds very promising, I'll try it and get back...|||

Hi Again

I replaced it so it look like this ...

sb.Append("<PD NID=""" & HiddenField7.Value &""" AID=""" & HiddenField7a.Value &""" FID=""" & (IIf(HiddenField7b.Value ="","NULL", HiddenField7b.Value)) &""" GID=""" & (IIf(HiddenField7d.Value ="","NULL", HiddenField7d.Value)) &""" MID=""" & (IIf(HiddenField7c.Value ="","NULL", HiddenField7c.Value)) &""" PID=""" & PID &"""/>")sb.Append("<PD NID=""" & HiddenField12.Value &""" AID=""" & HiddenField12a.Value &""" FID=""NULL"" GID=""NULL"" MID=""NULL"" UID=""" & UserID &"""/>")

And this is my sp...
 
p_BulkInsertPDI (@.FormData ntext)ASDECLARE @.hDoc int exec sp_xml_preparedocument @.hDoc OUTPUT,@.FormData BEGINSET NOCOUNT ON;INSERT INTO tbl_Form_Answers(NodeID, AprovalID, FaultID, Grade, MeasureID, ProtocolID)SELECT * FROM OPENXML(@.hDoc,'ROOT/PROT/PD',1)WITH ( NIDInteger , AIDInteger , FIDInteger , GID integer, MID integer, PID integer ) XMLEmpEXEC sp_xml_removedocument @.hDocEND
 
 

But now I get this errror message.. "Conversion failed when converting the nvarchar value 'NULL' to data type int"

How can I get this to work? I really don't want to make x number of insertation to the db, I would prefer to just do one.

Regards

|||

Ahhh, I didn't see that one coming. First thing you can try is use the dbnull.value instead:

sb.append("...." (IIf(HiddenField7d.Value ="",DBNull.value, HiddenField7d.Value)) )

Not sure if this will work. Otherwise your best bet is to temporarily assign a number as null. e.g. use a value you know will never occur (such as -1). After the inserts, you can then update the database and change all the -1 to NULL. Not the best method, but it should work.:

sb.append("...." (IIf(HiddenField7d.Value ="",-1, HiddenField7d.Value)) )

 
p_BulkInsertPDI
(@.FormData ntext)
AS
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.FormData
BEGIN
SET NOCOUNT ON;
INSERT INTO tbl_Form_Answers(NodeID, AprovalID, FaultID, Grade, MeasureID, ProtocolID)
SELECT *
FROM OPENXML(@.hDoc,'ROOT/PROT/PD',1)
WITH ( NIDInteger , AIDInteger , FIDInteger , GID integer, MID integer, PID integer ) XMLEmp
UPDATE tbl_Form_Answers SET Grade = NULL WHERE Grade = -1
EXEC sp_xml_removedocument @.hDoc
END

|||

Hi

The DBNull.Value part didn't work so I ended up with your second proposal wich worked fine, I guess I have to live with the update part. It must be better than to make 20 database insert, which would have been the case if this wouldnt work.

Thanks for all your help

Best Regards

Bulk insert

Hi

I have a text file with this information
-BEGIN------ tekst.txt----
10, "firstname", "lastname"
11, "Mette", "Larsen"
--| |--
6 000 000, "Michael", "Houmaark"
-END------- tekst.txt----

I use this SQL-query
-BEGIN------SQL-----
bulk insert tlf.dbo.bruger_data from 'C:\TEKST.txt'
with
(
FIRSTROW = 1,
FIELDTERMINATOR = '";"',
ROWTERMINATOR = '"\n'
)
-END-------SQL-----

But when the data is in the table its still have the " arround the firstname
and lastname
what do I do ???

Best Regards
Michael HMichael Houmaark (mhoum@.tdc.dk) writes:
> I have a text file with this information
> -BEGIN------ tekst.txt----
> 10, "firstname", "lastname"
> 11, "Mette", "Larsen"
> --| |--
> 6 000 000, "Michael", "Houmaark"
> -END------- tekst.txt----
> I use this SQL-query
> -BEGIN------SQL-----
> bulk insert tlf.dbo.bruger_data from 'C:\TEKST.txt'
> with
> (
> FIRSTROW = 1,
> FIELDTERMINATOR = '";"',
> ROWTERMINATOR = '"\n'
> )
> -END-------SQL-----
>
> But when the data is in the table its still have the " arround the
> firstname and lastname

You need to use a format file, because your field delimiters are not
consistent.

-BEGIN------ Format file
8.0
3
1 SQLCHAR 0 0 ", \"" 1 col1 ""
2 SQLCHAR 0 0 "\", \"" 2 col2 Danish_Norwegian_CS_AS
3 SQLCHAR 0 0 "\"\n" 3 col3 Danish_Norwegian_CS_AS
-END------ Format file

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Bulk Copy Program

Hi
I have a flat file that contains records many records. The first field
states the following for each record:
'I' = insert
'U' = update
'D' = delete
Is it possible for the BCP to achieve this objective? I know there be some
coding involved. I also noticed that there's an ODBC API that I can use in
..NET.
I'm not to sure but can BCP delete or update records?
Thanks in advance
Ross
Hi,
BCP can just copy the data into a file and then using a BCP IN loading back
to a table. Using this you cant dor a delete or update.
Thanks
Hari
MCDBA
"Ross Pellegrino" wrote:

> Hi
> I have a flat file that contains records many records. The first field
> states the following for each record:
> 'I' = insert
> 'U' = update
> 'D' = delete
> Is it possible for the BCP to achieve this objective? I know there be some
> coding involved. I also noticed that there's an ODBC API that I can use in
> ..NET.
> I'm not to sure but can BCP delete or update records?
> Thanks in advance
> Ross
>
>
|||Thanks for the info.
Ross
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:4352FAE8-F9EE-4C92-8E89-E372F442538B@.microsoft.com...
> Hi,
> BCP can just copy the data into a file and then using a BCP IN loading
back[vbcol=seagreen]
> to a table. Using this you cant dor a delete or update.
> Thanks
> Hari
> MCDBA
>
> "Ross Pellegrino" wrote:
some[vbcol=seagreen]
in[vbcol=seagreen]

Bulk Copy into CSV file with column name

Hi
i am exporting a data from table to CSV file using BCP command
but only the data are exported in CSV file,actually i want column name also in CSV file.
Appreciate your help for this issue

Thanks in Advance

You can't do it with bcp. You can either add the columns by hand, or write some fancy SQL to retrieve the column names from the syscolumns table. You'd probably have to do it in a DTS package.|||You can use a query that contains the header row and orders the data appropriately. See http://www.umachandar.com/technical/SQL70Scripts/Main8.htm for an example. Note that with this approach you will have to convert all the data to string format.

Buld Data load import

HI

I have a table XYZ that needs to contain a million records as operational data.

XYZ has a column named SLOT_VALUE which has values 1,2,3....100,000

What is the easiset way to bulk load this information in the shortest possible time...

Insert scripts/ Batch program like if..while loop takes heck lot of time....

Hello,

If you mean that you just want to populate that table with incrementing values from 1 to 1MIL, then the easiest would be to execute something like

select top 1000000 identity(int,1,1) as Num
into TempNumTable
from syscolumns c1
cross join syscolumns c2
cross join syscolumns c3
cross join syscolumns c4
... etc

You can't just directly insert into your already created table as the identity() function need a select into clause. You can just insert from the newly created table into your XYZ operational table.

If this is not what you mean, and you have a flat file that you want to bulk load into the table, have a look at the BULK INSERT command (use TABLOCK and consider dropping any existing indexes on the table prior to the load).

Cheers,

Rob

|||

Guess the easiest is to have t-sql script using a while loop...I thought of bcp but then I need to create a file...that would be redundant...

Anyhow thnx for the post.

|||

No worries. Just remember that performing this operation within a while loop will entail 1 million individual inserts! The set based solution described above would be much faster and much more efficient. If this is a once-off operation, then it's probably not an issue.

Cheers,
Rob

Building Search

Hi

I've never done this before and I have all kinds of issues conflicting in my head (search rank, noise words, injection attacks ..etc). simply i need to search several columns in a table in the database using one search text (just like the simple search in Google). if multiple words are you used then the search should search for each of them. also manage to ignore noise words and other issues.

what is the best way of doing this? I looked at FTS in SQL 2000 but didn't know how to handle all the above mentioned issues. this should be simple, right? but i have been looking all day. I guess i don't know what im looking for because i've never implemented a web search b4.

please help

p.s. I know t-sql and asp.net/c# well.SQL Server hasFull-Text Search capabilities built into it. This might be a solution to your problem.

Terri