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

Sorry for the piece-by-piece nature of this post, I moved it from a
dormant group to this one and it was 3 separate posts in the other
group. Anyway...

I'm trying to bulk insert a text file of 10 columns into a table with
12. How can I specify which columns to insert to? I think format
files are what I'm supposed to use, but I can't figure them out. I've
also tried using a view, as was suggested on one of the many websites
I've searched, but I clearly did that incorrectly as well.

----------
Update:

I'm working with the view, and I've got a view that contains the exact
columns from the table I want. I ran my bulk insert command,

BULK INSERT Test..IV10401 FROM 'c:\bulkInsertFile2.txt'

and it returned the error:

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'IV10401' with unique index
'AK2IV10401'.
Note: Bulk Insert through a view may result in base table default
values being ignored for NULL columns in the data file.
The statement has been terminated.

The AK2IV10401 key is comprised of 3 columns and I'm sure that each of
my rows in the insert file is unique according to those three. What
should I be checking for?

--------
Update 2:

I can only successfully insert 1 row. It seems to be treating each row

as an individual primary key when it should be treating them as
composite keys. I cannot alter the table, since it was created by
Great Plains Dynamics. Is there some sort of switch that I'm missing
in my bulk insert statement or can I suppress the errors?ughh, bulk insert is going to be the end of me. i just need to insert
two seperate .txt files into two separate tables, but i can't do it. i
did finally get one to go through by not demanding that the index
AK2IV10401 is unique. i don't know what problems that will cause for
me in the future, but i would at least like to get to the future to see
SOMETHING happen. As for the second table, there is a Primary Key that
is blocking all my progress and I don't know how to get around this.
Here is the error I get.

Violation of PRIMARY KEY constraint 'PKIV10402'. Cannot insert
duplicate key in object 'IV10402'.
The statement has been terminated.

I REALLY don't think I'm violating anything, so why is it kicking and
screaming at me?

-pk|||pk (philip.kluss@.gmail.com) writes:
> Sorry for the piece-by-piece nature of this post, I moved it from a
> dormant group to this one and it was 3 separate posts in the other
> group. Anyway...
> I'm trying to bulk insert a text file of 10 columns into a table with
> 12. How can I specify which columns to insert to? I think format
> files are what I'm supposed to use, but I can't figure them out. I've
> also tried using a view, as was suggested on one of the many websites
> I've searched, but I clearly did that incorrectly as well.

Format files are a bit tedious, but for 10 columns it's not that
bad. Here is an example:

8.0
10
1 SQLCHAR 0 0 "\t" 1 X ""
2 SQLCHAR 0 0 "\t" 2 X ""
...
10 SQLCHAR 0 0 "\r\n" 12 X ""

First row is the version of the file format. Next row lists the number
of fields in the bulk file. Next ten rows details the fields.

First column is the field number. Second column number is the data type.
This is always SQLCHAR for an ANSI file, and SQLNCHAR for a Unicode
file. Other data types applies only to binary data files.

Third column is prefix length. This is always 0 for a text file. Fourth
column is column length. Use this for fixed-length columns or leave 0.
Fifth column is the field terminator. In the example, I'm assuming
tab, save for the last row that is terminated by carriage return+line feed.

The sixth column is the column number for the table column in SQL Server.
This does not have to follow the numbers in the file. If the number is 0,
that file in the text file is not imported.

The seventh column is the column name, but this column is informational
only.

The eigth column specifies the collation. This is good if you need to
convert data between charsets when importing.

> ----------
> Update:
> I'm working with the view, and I've got a view that contains the exact
> columns from the table I want. I ran my bulk insert command,
> BULK INSERT Test..IV10401 FROM 'c:\bulkInsertFile2.txt'
> and it returned the error:
> Server: Msg 2601, Level 14, State 3, Line 1
> Cannot insert duplicate key row in object 'IV10401' with unique index
> 'AK2IV10401'.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> The AK2IV10401 key is comprised of 3 columns and I'm sure that each of
> my rows in the insert file is unique according to those three. What
> should I be checking for?

Maybe the keys are already in the table?

> --------
> Update 2:
> I can only successfully insert 1 row. It seems to be treating each row
> as an individual primary key when it should be treating them as
> composite keys. I cannot alter the table, since it was created by
> Great Plains Dynamics.

Without access to table definition, data file and the BCP command
it's hard to tell what is going on.

A common technique is to bulk load into a staging table, and then
clean up data there, before moving to the target table.

> Is there some sort of switch that I'm missing
> in my bulk insert statement or can I suppress the errors?

Well, you can use -b and -m to set the batch size, and increase the
number of errors permitted. See Books Online for further details.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'll describe the table the best I can. Then I'm headed home for the
day in hopes that someone can explain my error. I appreciate your
response Erland but I feel I haven't given enough info. So here it
goes.

I've got an empty table IV10402. I didn't create the table, Great
Plains Dynamics did, but I need to import to it. There are several
indexes and primary keys defined on it that I can't, with good
confidence, alter. It has 11 columns and I have a txt file that
consists of 10, so I've created a format file which I'm fairly certain
is correct. It appears as follows.

-----------

8.0
10
1 SQLCHAR 0 15 "\t" 1
PRCSHID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "\t" 2
EPITMTYP SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 31 "\t" 3
ITEMNMBR SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 9 "\t" 4
UOFM SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 41 "\t" 5
QTYFROM ""
6 SQLCHAR 0 41 "\t" 6
QTYTO ""
7 SQLCHAR 0 41 "\t" 7
PSITMVAL ""
8 SQLCHAR 0 41 "\t" 8
EQUOMQTY ""
9 SQLCHAR 0 41 "\t" 9
QTYBSUOM ""
10 SQLCHAR 0 12 "\n" 10
SEQNUMBR ""

--------

So when I run my bulk insert command, which appears as follows,

BULK INSERT Test..IV10402 FROM 'c:\bulkInsertFile.txt'
WITH (DATAFILETYPE='char',
ROWTERMINATOR='\n',
FORMATFILE='c:\iv10402.fmt')

I get this error,

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKIV10402'. Cannot insert
duplicate key in object 'IV10402'.
The statement has been terminated.

I then go to check what the Primary Key constraint PKIV10402 is and
here is the info that I can offer up.

It is not clustered. It is based off of 6 columns, PRCSHID, EPITMTYP,
ITEMNMBR, UOFM, QTYFROM, and QTYTO. The Create UNIQUE checkbox is
checked and greyed out, so I can't use my previous workaround of
checking the "Ignore Duplicate Key" box. Index Filegroup is PRIMARY.
Fill Factor is 90%.

One last thing is that the Table Identity Column for IV10402 is set to
DEX_ROW_ID, which happens to be the one column that I'm not inserting.
Is this a problem?

Again, this table is empty when I run this insert. I'm almost positive
that there aren't actually duplicate primary keys. Did Microsoft
really offer no way to find out which rows it feels are duplicates?
That seems very shortsighted in my opinion. Thanks for reading. I'll
see you all tomorrow.

-pk|||pk (philip.kluss@.gmail.com) writes:
> ughh, bulk insert is going to be the end of me. i just need to insert
> two seperate .txt files into two separate tables, but i can't do it. i
> did finally get one to go through by not demanding that the index
> AK2IV10401 is unique. i don't know what problems that will cause for
> me in the future, but i would at least like to get to the future to see
> SOMETHING happen. As for the second table, there is a Primary Key that
> is blocking all my progress and I don't know how to get around this.
> Here is the error I get.
> Violation of PRIMARY KEY constraint 'PKIV10402'. Cannot insert
> duplicate key in object 'IV10402'.
> The statement has been terminated.
> I REALLY don't think I'm violating anything, so why is it kicking and
> screaming at me?

Because you are violating something.

Get data into a keyless staging table, and to a SELECT WHERE EXISTS
to find clashes with existing data, and "SELECT keycol, COUNT(*) FROM
tbl GROUP BY keyol HAVING COUNT(*) > 1" to find the dups in the 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|||pk (philip.kluss@.gmail.com) writes:
> It is not clustered. It is based off of 6 columns, PRCSHID, EPITMTYP,
> ITEMNMBR, UOFM, QTYFROM, and QTYTO. The Create UNIQUE checkbox is
> checked and greyed out, so I can't use my previous workaround of
> checking the "Ignore Duplicate Key" box. Index Filegroup is PRIMARY.
> Fill Factor is 90%.
> One last thing is that the Table Identity Column for IV10402 is set to
> DEX_ROW_ID, which happens to be the one column that I'm not inserting.
> Is this a problem?
> Again, this table is empty when I run this insert. I'm almost positive
> that there aren't actually duplicate primary keys. Did Microsoft
> really offer no way to find out which rows it feels are duplicates?

Either there are duplicates in the file, or the format file is incorrect
somehow, so that data ends up in the wrong columns.

Create a copy of the table, but put no indexes or constraints on the
table. Bulk load data into that table. Check for duplicate with

SELECT col1, col2, ... COUNT(*)
FROM tbl
GROUP BY col1, col2, ...
HAVING COUNT(*) > 1

Also, do something like "SELECT TOP 100 * FROM tbl" to see whether the
data makes any sense.

You can use the Object Broswer in Query Analyzer to create a script
for the table. Find the table, and scripting options is on the context
menu. Create the table in tempdb.

As for finding which rows that are problematic directly, BULK INSERT
does not seem to offer this option. BCP does, but I think that error
file covers only format errors, not insertion errors.

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

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

You are a lifesaver. It took me a while to figure out what that SQL
statement you were telling me to use was supposed to do, but as soon as
I did, it found the 2 lines out of 15000 that managed to trip the
duplicate key error. I've since corrected it and am feeling much more
confident in my troubleshooting skills for the future. Thank you very
much.

-pk

BUILTIN\Administrators not recognized - rsUnknownUserName

Hello all,

We recently moved our Team Foundation Server from one server to another, of course the reporting services was also included in that move.

On the new server, we are not able to change Reporting Services security parameters anymore, we get this error :
User or group ? BUILTIN\Administrators not recognized. (rsUnknownUserName)

The old server was an english windows 2003, the new one a french version, i guess the problem is related. The BUILTIN\Administrators group name on the new server is "BUILTIN\Administrateurs".

Is there a way to change security params without getting this error ? How can we remove from Reporting Services this reference to BUILDTIN\Administrators ? I've tried to modify the table Users directly in ReportServer DB without any success..

Please help !
Alexandre

Ok I was finally able to solve the problem ! I connected to my Reporting Services server using SQL Server Management Studio and not Reporting Services administration website. In the root folder permissions, I removed the reference to BUILTIN\Administrators and added the one to BUILTIN\Administrateurs, and it worked !

Alexandre

Build fails but no errors are returned

hi,

I moved my ssis solution from on dev machine to another.

When building the solution, visual studio keeps saying that the build failed but no errors are returned which is not really helpful...

any clues would be appreciated.

thanks

I don't believe you need to "build" anything.|||

well, the solution is built before starting the debugger process...

anyway, I've sorted my problem... errors where not displayed in the error list but I've found one returned in the output window.

It was complaining that it could not load the project files (note that I had no errors while loading the solution in VS).

I've deleted the .suo and other user files from the solution and it now works... did not realise that project file paths informations could be in the user files... I was expecting this information to be specific to solution and project files ...

|||I set my projects to never build under Tools-Options. Never had a problem.