Bulk Loading: TempFilePath security question

I've got a program written that runs on one computer and performs bulk
updates to a SQL Server 2000 table on a different computer. I also
need transactional behavior, so I have to specify a value for
TempFilePath on my SQLXMLBulkLoad3Class object.
I do not have permissions to create folders, files, or interact in any
way with the computer hosting SQL server. This means that the value of
TempFilePath must be a UNC path that can be written to by the
application performing bulk loads, and (at a minimum) read from by the
computer hosting SQL server.
I'd like to make access to this UNC path as restricted as possible. I
am using SQL authentication, not Windows authentication.
How can I tell what ID will try from the SQL Server computer to read
from the TempFilePath?
How much permission will this ID need in the directory? Read only?
Read/write?If you do this with a connection using standard SQL login, then the thread
that is running the query will be using the account that the SQL Server
service runs under to access the file share.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
<isbat1@.yahoo.com> wrote in message
news:1128017182.904592.4100@.z14g2000cwz.googlegroups.com...
> I've got a program written that runs on one computer and performs bulk
> updates to a SQL Server 2000 table on a different computer. I also
> need transactional behavior, so I have to specify a value for
> TempFilePath on my SQLXMLBulkLoad3Class object.
> I do not have permissions to create folders, files, or interact in any
> way with the computer hosting SQL server. This means that the value of
> TempFilePath must be a UNC path that can be written to by the
> application performing bulk loads, and (at a minimum) read from by the
> computer hosting SQL server.
> I'd like to make access to this UNC path as restricted as possible. I
> am using SQL authentication, not Windows authentication.
> How can I tell what ID will try from the SQL Server computer to read
> from the TempFilePath?
> How much permission will this ID need in the directory? Read only?
> Read/write?
>

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 Vs BCP.exe

We have the following code written in the TSQL using the Bulk Insert command which works fine in SQL Server 2000 and 2005

Code Snippet

BULK INSERT MYTABLE FROM 'C:\TextDataFiles\MYTABLE.txt' WITH ( FORMATFILE = 'C:\MYTABLE.fmt' , MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\MYTABLE.bad')

Please note the MAXERRORS is set to 0. So, if a error occurs nothing is written to the table.

Now we are trying to convert this functionality using the bcp.exe (actually using the API like bcp_control in C++) with the following code

Code Snippet

bcp "MYTABLE" in "'C:\TextDataFiles\MYTABLE.txt' -m0 -f"'C:\MYTABLE.fmt' -e 'C:\MYTABLE.bad' -b500000 -SMYSERVER -Uuid -Ppwd -h"CHECK_CONSTRAINTS"

This code does not work the same way as the BULK INSERT code above. It default the max errors to 10 (as documented) and inserts row(s) before the first error. How can I have the same functionality, fail on first error, with the bcp?

Your help is appreciated.

Since it will still insert the rows that are processed before the first error occurs (then failing and halting the insertions) I would probably push the data to a temporary table, do a check to make sure all data was inserted properly, then move the data from the temp table to the production table. This way you won't get half of the data if the bcp fails.

|||That is the way it works because of the batch=500000. You will always get the "batch" inserted before the error occurs.

|||

Why is the behaviour different in the TSQL for BULK INSERT, we have the same value for the BATCH. Is it possible BATCH INSERT is started in a commitment boundary automatically?. I do not understand why there is a different in the behaviour between the 2 commands.I would like to keep the existing functionality, as it, if possible, with out making significant changes like inserting to temp table and then moving to the perminant tables.

|||The Bulk Insert command creates an "implicit" transaction around the command. So if there is an error, everything gets rolled back. bcp.exe does not.

If you remove the "-b500000" from the bcp.exe command, you will get all or nothing.
sql

BULK INSERT Query

Hey i am planning to use the BULK INSERT to copy the data into my Server. Th
e
data is being written to the file by external application.
But i want the data that is being written should be deleted after the
insert. Can BULK INSERT statement support such option to be specified. other
wise my file will grow infintly over time.
Any suggestion or pointer will be helpful.
MCAD
Vineet BattaBULK INSERT itself will not manage the files for you. You'll have to do
that yourself via another script. You might want to look into using a DTS
package to manage the BULK INSERT and if successful, execute a script task
to move/delete the file.
--Brian
(Please reply to the newsgroups only.)
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
> Hey i am planning to use the BULK INSERT to copy the data into my Server.
> The
> data is being written to the file by external application.
>
> But i want the data that is being written should be deleted after the
> insert. Can BULK INSERT statement support such option to be specified.
> other
> wise my file will grow infintly over time.
> Any suggestion or pointer will be helpful.
> --
> MCAD
> Vineet Batta|||But does BULK Insert can keep track of what data has been pushed to server
from last time , so that when it runs the second time it just picks up delta
only.
--
MCAD
Vineet Batta
"Brian Lawton" wrote:

> BULK INSERT itself will not manage the files for you. You'll have to do
> that yourself via another script. You might want to look into using a DTS
> package to manage the BULK INSERT and if successful, execute a script task
> to move/delete the file.
> --
> --Brian
> (Please reply to the newsgroups only.)
>
> "vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
> news:816DC10F-5FAA-413D-9632-AEA438311802@.microsoft.com...
>
>|||No, but you can insert into a staging table and then run queries to
unset/update data into the main table as desired.
Hope this helps.
Dan Guzman
SQL Server MVP
"vineetbatta" <vineetbatta@.discussions.microsoft.com> wrote in message
news:08DC46E9-ED63-413D-83E5-B7FA3EC47762@.microsoft.com...
> But does BULK Insert can keep track of what data has been pushed to server
> from last time , so that when it runs the second time it just picks up
> delta
> only.
> --
> MCAD
> Vineet Batta
>
> "Brian Lawton" wrote:
>

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?
Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:
> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT - does it lock the file while reading?

Howdy, all. We have a (log-type) file that's constantly being written
to. We need to grab the latest rows out of this file, insert them into
a table, and then process them. We've found out the hard way that BCP
locks the file while it's inserting, so rows can't be written to the
file while the BCP is running.
Our current workaround is to make a copy of the file (using ftp), but
we're running into other problems.
I'm trying to find a better way to do this. We've narrowed down a
couple.
1) use TAIL or something similar to grab the records since we last ran.
2) use BULK INSERT, provided it doesn't lock the file.
3) get suggestions off of USENET. :)
Any suggestions or comments?
Thanks,
MichaelWHy not BCP into a "Different" table and then run a Stored Proc to Process
the records into the main table from the One you BCP'd into?
1) Step 2, where any locks would be created on the main table, will be
faster.
2) If there's still a concurrency issues, you can rewrite the Step 2 code to
process the records from one table into the main table in smaller batches...
reducint the batch size until the concurrency issue goes away.
"M Bourgon" wrote:
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael
>|||"M Bourgon" <bourgon@.gmail.com> wrote in message
news:1110928274.048339.317490@.f14g2000cwb.googlegroups.com...
> Howdy, all. We have a (log-type) file that's constantly being written
> to. We need to grab the latest rows out of this file, insert them into
> a table, and then process them. We've found out the hard way that BCP
> locks the file while it's inserting, so rows can't be written to the
> file while the BCP is running.
> Our current workaround is to make a copy of the file (using ftp), but
> we're running into other problems.
> I'm trying to find a better way to do this. We've narrowed down a
> couple.
> 1) use TAIL or something similar to grab the records since we last ran.
> 2) use BULK INSERT, provided it doesn't lock the file.
> 3) get suggestions off of USENET. :)
>
> Any suggestions or comments?
> Thanks,
> Michael
>
Would a half baked suggestion do?
Write an ActiveX Script to open the file shared read only ,
find your starting point and loop to the end doing either
1. a write out of the lines as is to a scratch file that you can then
BulkCopy in
2. a parse of the log file lines and then update the DB directly.|||The problem is that the file's still locked during that time, and I
can't afford to lose any rows.|||Not a bad idea, though I may just use tail for simplicity, assuming it
doesn't lock the file as well. Thanks|||Answering my own question - yes, bulk insert DOES lock the file -
couldn't append to the file, got "The process cannot access the file
because it is being used by another process.". Looks like I'll
tail/etc the file. Thanks, guys.

Building Dynamic Sql in Stored Proc issue

Hi all,

I'm gonna need some help with this one.

I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:

SELECT N_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROM dbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ............and so on

What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.

So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this? Help!!

ThanksYou can store your parameters into a 15-column temporary table and then join the main query with it.|||Alternativley...use and exec statemnt ( not for the weak of stomach )

declare @.var_1 varch(10), @.var_2 varchar (10)
set @.var_1 = 'value_1'
set @.var_2 = 'value_2'

exec( ' select * from table where x = ' +@.var_1+ ' and y = ' +@.var_2+ '')

so :

exec('..............
Where upper(title) like %'+@.parameter_value'%
and company_id = '@.parameter_value'
and Nmt_practice_code = '@.parameter_value'')

You effectively have to enclose the whole statement in an exec statement.
Its messy this way, and you will take a while to debig to get it right.

rdjabarov has a good idea.....its more elegant than my approach....