Calculate difference between previous row and insert to new column -problem

Hi!

I have an algorithm that uses cursors to calculate difference between row and row-1 in a certain (int-type) column. How could I insert the difference value to this same table (or generate new dynamic table) as a new column?

I need this information to do some reporting with Reporting Services and show the difference there...

Thanks!

-Jukka

hi,

can you post your proc?|||

My knee-jerk reaction would be to suggest self-joining the table. This depends on whether you have a ready-made criteria that you can use either for keying or ordering your table. A simple-minded example might be something like:

declare @.xample table
( rowId integer primary key,
theValue numeric (5,2)
)
insert into @.xample
select number,
33.34*dbo.rand() + 33.33*dbo.rand() + 33.33*dbo.rand()
from master.dbo.spt_values
where name is null
and number <= 5

select a.rowId,
a.theValue as [A Value],
b.theValue as [B Value],
a.theValue - b.theValue as Difference
from @.xample a
join @.xample b
on a.rowId = b.rowId + 1

/*
rowId A Value B Value Difference
-- - - -
1 6.82 37.87 -31.05
2 20.28 6.82 13.46
3 35.15 20.28 14.87
4 56.60 35.15 21.45
5 35.21 56.60 -21.39
*/

c# And SQL Server. Why "select" can transac but "insert" ?

string connectionString;
string queryString;
SqlConnection sqlconnection;connectionString="server=fatyi;integrated security=SSPI;initial catalog=tempdb";
queryString="insert into products values(5,'BaoMa',200596,900000,0)";
sqlconnection=new SqlConnection(connectionString);
SqlCommand addScorce=new SqlCommand(queryString,sqlconnection);
sqlconnection.Open();
addScorce.ExecuteNonQuery();
sqlconnection.Close();

help to find out why? if i use "select" it can transaction in this code. And here "insert" .My WebAplication could work,but have no result of "insert".and no error information.The "insert" purview of "products" table have selected.
I help you can help me to point out maybe where is error.
Thank you !Did you try using a Try...Catch block?

And also, are there 5 columns in your table? You might consider specifying your column names in your INSERT statement, like:
INSERT INTO products (column1, column2, column3, column4, column5) values(5,'BaoMa',200596,900000,0)

Terri

C# and SQL Express Problems

Hi,

Im having an issue with the INSERT statement in C# using SQL Express.

First off, I believe my INSERT statement is correct. Using it in console mode works fine (adding it manually to the db), I've also had a resident SQL expert check it out and he said it looked good.

In my software, it seems to work, but nothing saves. Im writing my own DVD catalogue, mostly to get practice with both C# and SQL working together.

For example:

On my form, there a 3 options.

1. Catalogue Number

2. Dvd Type

3. Dvd Name

The catalogue option gets the number from a table in the DB via SELECT statement. This works.

The DVD Type reads options from another table in the DB via SELECT statement. This works.

The DVD Name is manually typed in a text box.

Once all 3 are filled out, I click the button to save it to the DB. No exceptions are thrown, and the form moves to the "next" entry. (Ie. in catalogue number, if it was 1, it becomes 2)

Upon exiting the program, I look at the DB to find nothing there.

Heres my code for adding (teh click handler for the button).

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (txtID.Text, cmbType.Text, txtName.Text)"; //This is wrong for the purpose of this post, simply to eliminate a few lines of String.Concat code!

sqlConn = new SqlConnection(sConnection);

sqlCommand = new SqlCommand(sQuery, sqlConn);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

I apologize if this has come up before, I didnt find an exact solution to this.

Thanks in advance for any help!

What is the error you are getting?

can you also post the exact String.Concat value?

|||

There is no error.

It seems to work, only after the program finishes execution, the changes dont commit.

Heres the code with the String.Concat

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (";

sQuery = String.Concat(sQuery, " ' ", txtID.Text, " ' ");

sQuery = String.Concat(sQuery, " ' ", cmbType.Text, " ' ");

sQuery = String.Concat(sQuery, " ' ", txtName.Text, " ' )";

sqlConn = new SqlConnection(sConnection);

sqlCommand = new SqlCommand(sQuery, sqlConn);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

|||

I wouldn't understand why.

Just for your knowledge, you should be using parameterized queries as they are securer and prevent SQL injection attacks - it is best practice to use them. It can also resolve some common problems and reduces the whole string parsing routine as well as making the code cleaner. :-)

Example:

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (@.p1, @.p2, @.p3)";

SqlParameter p1 = new SqlParameter("@.p1", this.txtID.Text);

SqlParameter p2 = new SqlParameter("@.p2", this.cmbType.Text);

SqlParameter p3 = new SqlParameter("@.p3", this.txtName.Text);

using (sqlConn = new SqlConnection(sConnection))

{

using (sqlCommand = new SqlCommand(sQuery, sqlConn))

{

sqlCommand.Parameters.Add(p1);

sqlCommand.Parameters.Add(p2);

sqlCommand.Parameters.Add(p3);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

}

}

Also if you are finding that once the application closes and you find the values in the database being inserted, be sure that you are not entering into the debugger (stepping through) line by line as this can also cause some confusion on what is actually happen, just let it run and see what happens.

|||

Ok, I switched it over to the Parameterized Query, but it still does the same thing.

Im getting really frustrated by this now!!! It does the same thing on 2 machines.

It really doesnt make any sense at all. I query the table for the highest "ID" of the movie (SELECT MAX(ID) FROM Dvd), add 1, and display that value for the next DVD to be entered. When I enter one and click ADD, the number increments like it should, so if nothing else, it seems to be putting at least the ID into the table by creating a new row for it. I try this several times (ie. Add 10 DVDs), and the number counts correctly, once I exit the program, there is nothing in the DB. When I restart the program, the counter starts at 1 again...

Any ideas why nothing is actually saving in the database? Ive never seen anything like this before.

|||

maybe you should post the entire class?

gives us an overall view on what might be going wrong...

have a good one

adam

|||

About an hour ago, I came across the reason behind it.

It has to do with using the Express Editions of C# and SQL Server (or at least the person posted the reasoning behind it).

Since the db is an object in the solution, it gets copied (in its original, empty state) everytime the program gets compiled. So, while I was looking at what I believed to be the db, it was actually somewhere else, and everytime the program was run, a "fresh" copy of the db was copied over the one that actually had the data.

Does anyone know of any programs that will load SQL Server (Express) .mdf files? I have yet to come across one. I tried installing the Management Tools Suite, but I cant get it to open the file correctly (the program errors when I try), I have a feeling it has to do with this machine, I'll try another machine tomorrow and see what I can come up with. I'll post the results here, but in the meantime, if anyone knows of any software that will open and show the .mdf files, please let me know!

Thanks.

|||

As it turns out, that was the problem the whole time. Now, I just copy the .mdf file over and attach it to SQL Management Studio Express.

The SQL Management Studio Express allows you to "attach" the .mdf file to the current open db. THis will allow you to run queries to get whatever data from the database.

Thanks everyone for your help.

|||

hi,

i read your answer, but still i could not get the solution. because when i open the SQL Server Mangement, there is only one record affected by insert statment so i can not insert more than one record becase the insert treated as an update statment.

and when i used the update statment, no changes are done to the table data

pls pls help

|||Hmm, if you want to post your code, (or at least snippets of it) to show where things should be working and are not, that would be helpful. Off the top of my head, you may have an improper insert statement, or else the connectionstring you use may be off a bit. Post what you have and I'll try to help out.|||

Hi again,

I used the below code and it affect only one row

private void form_Load(object sender, EventArgs e)

{

strSql=”update tbl set fldName=’AAA’”;
cmd=new sqlCommand(strSql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close()

}

So I changed my code and used button_Click instead of form_load and I was able to insert many rows. But when I close the application and open it again the previous data disappeared like that if I am using blank database.

So any idea. What I understand that the application creates a copy of the .mdf file while it is running and after closing the application it overwrite the original .mdf file

Correct me if am wrong and how to overcome this because I need to use it as database with historical data

I really really need your help

Thanks for your time

|||i get the answer. thats by setting the copy to output directory to copy if newer. before it was set to copy always.|||

I just set mine to copy never!

But it doesnt overwrite the file AFTER closing the application. It copies the .MDF to the output directory at compile time. All I did at first, was run the program, once it was finished, I would copy the db.mdf and db_log.ldf files to a TEMP folder and open them from there with SQL Management Studio Express. The reason I copied, when I tried to attach the file, it wouldnt let me browse to C:\documents and settings\......\visual studio 2005\Projects\...\bin

Did that copy if newer setting solve the problem(s)?

Bypassing locks when doing insert or update

Hi,

I want to bypass locks while doing Insert or Update. I am only updating a log db and I don't care about one or two fields getting junk as I won't use it later (atleast as long as I am working with my current company ;) )

I am using MS SQL 2000

I am getting too many deadlocks and messages like these

"Process ID was deadlocked with another process and has been chosen a victim. Please rerun the transaction".

Please tell me how to achieve this.

Regards,
Noorul

you can try using a 'NOLOCK' locking hint....but u have to be sure..it wont affect ur data (ACID)....

correct..and longer part will be to try and find the source of deadlocks..and remove it..

|||Have a look to see what you have in the way of indexes on your table, particularly your clustered index. Hopefully you have an id field which only increments as your clustered index, so that it never tries to move the pages around, and your inserts can just jump straight in and out again.

Updates shouldn't have to be any different - make sure you have a good indexing strategy so that the system doesn't try to lock more of the table than it needs to.

Why are you updating a log anyway? And should I assume you mean 'audit' ?

Rob|||You absolutely CANNOT bypass locking when doing UPDATES or INSERTS or DELETES. Nor would you want to. You are getting deadlocks because your code is accessing the data in differring order, you are holding transactions open too long, and/or you are not using NOLOCK hints on your SELECT statements where appropriate.|||

Please take a look at the links below on how to resolve deadlocks. You can't eliminate them by just using locking hints in your various statements. Deadlocks are typically due to errors in your execution logic.

http://support.microsoft.com/kb/832524/

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx

|||Thanks All

I read about the clustered Index from Microsoft kb 169960. I created the clustered index with 70 fill factor. It has reduced the deadlocks down to zero. Actually I have an application that has 10 threads logging the sent SMS messages into same table. My application don't read from it, it just does insert. So far, so good. With 70 fill factor, how will that affect the memory consumption?

Regards,
Noorul

Bypassing locks when doing insert or update

Hi,

I want to bypass locks while doing Insert or Update. I am only updating a log db and I don't care about one or two fields getting junk as I won't use it later (atleast as long as I am working with my current company ;) )

I am using MS SQL 2000

I am getting too many deadlocks and messages like these

"Process ID was deadlocked with another process and has been chosen a victim. Please rerun the transaction".

Please tell me how to achieve this.

Regards,
Noorul

you can try using a 'NOLOCK' locking hint....but u have to be sure..it wont affect ur data (ACID)....

correct..and longer part will be to try and find the source of deadlocks..and remove it..

|||Have a look to see what you have in the way of indexes on your table, particularly your clustered index. Hopefully you have an id field which only increments as your clustered index, so that it never tries to move the pages around, and your inserts can just jump straight in and out again.

Updates shouldn't have to be any different - make sure you have a good indexing strategy so that the system doesn't try to lock more of the table than it needs to.

Why are you updating a log anyway? And should I assume you mean 'audit' ?

Rob|||You absolutely CANNOT bypass locking when doing UPDATES or INSERTS or DELETES. Nor would you want to. You are getting deadlocks because your code is accessing the data in differring order, you are holding transactions open too long, and/or you are not using NOLOCK hints on your SELECT statements where appropriate.|||

Please take a look at the links below on how to resolve deadlocks. You can't eliminate them by just using locking hints in your various statements. Deadlocks are typically due to errors in your execution logic.

http://support.microsoft.com/kb/832524/

http://msdn2.microsoft.com/en-us/library/aa937573(SQL.80).aspx

|||Thanks All

I read about the clustered Index from Microsoft kb 169960. I created the clustered index with 70 fill factor. It has reduced the deadlocks down to zero. Actually I have an application that has 10 threads logging the sent SMS messages into same table. My application don't read from it, it just does insert. So far, so good. With 70 fill factor, how will that affect the memory consumption?

Regards,
Noorul

bypass a transaction

Is there some way to bypass a transaction, when doing a insert. Meaning when
i'm inside a BEGIN TRANS and wants to do a INSERT that will not be removed i
f
a ROLLBACK is done, is there any way to do that?
Example:
BEGIN TRANSACTION
INSERT log_table values ( 'Survive' )
ROLLBACK TRANSACTION
Afterwards the log_table contains the 'Survive' row...
CheersTroy,
Try using a SAVEPOINT. See 'Transaction Savepoints' in the SQL BOL.
HTH
Jerry
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:2216FE6C-190B-40F8-8EA6-BC1621CF4EEE@.microsoft.com...
> Is there some way to bypass a transaction, when doing a insert. Meaning
> when
> i'm inside a BEGIN TRANS and wants to do a INSERT that will not be removed
> if
> a ROLLBACK is done, is there any way to do that?
> Example:
> BEGIN TRANSACTION
> INSERT log_table values ( 'Survive' )
> ROLLBACK TRANSACTION
> Afterwards the log_table contains the 'Survive' row...
> Cheers|||Hi Jerry and thanks for your answer :)
But i don't think SAVEPOINT is what i am lokking for, i'm not easy to please
;) Let me clear out the question..
Inside the transaction we have several update statements. For each update
statement (that takes quite a while) we want to log, to a table, showing
progress and status. But if the transactions rolls back our logging is also
rolled back, and this is what i am trying to avoid...
Cheers
"Jerry Spivey" wrote:

> Troy,
> Try using a SAVEPOINT. See 'Transaction Savepoints' in the SQL BOL.
> HTH
> Jerry
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:2216FE6C-190B-40F8-8EA6-BC1621CF4EEE@.microsoft.com...
>
>|||No can do, there are no such independent subtransactions in SQL Server.. Wha
t you can do is to log
to a table variable and after the transaction use that table variable to log
into a real table. You
will not be able to investigate during the transaction, though. A table vari
able is not rolled back.
Another option is to open a new connection (through an extended stored proce
dure, for example). This
is not a light-weigh solution, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:A65DF86B-0BE8-4836-A55B-3F95DD328E21@.microsoft.com...
> Hi Jerry and thanks for your answer :)
> But i don't think SAVEPOINT is what i am lokking for, i'm not easy to plea
se
> ;) Let me clear out the question..
> Inside the transaction we have several update statements. For each update
> statement (that takes quite a while) we want to log, to a table, showing
> progress and status. But if the transactions rolls back our logging is als
o
> rolled back, and this is what i am trying to avoid...
> Cheers
> "Jerry Spivey" wrote:
>|||Hi Tabor,
Thanks for your answer, that cleared it out. I must find another way to
solve this....
Cheers
"Tibor Karaszi" wrote:

> No can do, there are no such independent subtransactions in SQL Server.. W
hat you can do is to log
> to a table variable and after the transaction use that table variable to l
og into a real table. You
> will not be able to investigate during the transaction, though. A table va
riable is not rolled back.
> Another option is to open a new connection (through an extended stored pro
cedure, for example). This
> is not a light-weigh solution, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:A65DF86B-0BE8-4836-A55B-3F95DD328E21@.microsoft.com...
>|||You can also do a combo. Like creating a global temptable and have your code
inserting into it.
Others can read using NOLOCK. But also insert into a table variable. The tab
le variable is used to
log after all is done.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:3D2C764E-F69F-42BA-81CB-114F157052E7@.microsoft.com...
> Hi Tabor,
> Thanks for your answer, that cleared it out. I must find another way to
> solve this....
> Cheers
>
> "Tibor Karaszi" wrote:
>|||hhmm, that could actually be someting to go for...
What about a traditional flat text file, any possibilities there?! Else i
might try the @.@.logtable solution and see who thats works out..
Cheers
"Tibor Karaszi" wrote:

> You can also do a combo. Like creating a global temptable and have your co
de inserting into it.
> Others can read using NOLOCK. But also insert into a table variable. The t
able variable is used to
> log after all is done.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:3D2C764E-F69F-42BA-81CB-114F157052E7@.microsoft.com...
>|||Yes, file is an option, the problem is how you write to the file. Whichever
"hack" you use (like
xp_cmdshell and pipe something to a file etc), it will not be light-weight a
nd will slow down your
application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:88692115-5C8A-4165-85DE-E46A85C489FB@.microsoft.com...
> hhmm, that could actually be someting to go for...
> What about a traditional flat text file, any possibilities there?! Else i
> might try the @.@.logtable solution and see who thats works out..
> Cheers
> "Tibor Karaszi" wrote:
>|||Put the data in a variable table, and save it after the transaction has
completed.
Variable tables by design are non-transactional.
AFAIK, normal tables and temp tables are both affected by transactions, as
are schema modifications etc.
eg.
begin trans
insert into tblBob values ( 'something' )
insert into @.myTable values ( 'done' )
rollback trans
insert into myRealTable SELECT value FROM @.myTable
"Troy" <Troy@.discussions.microsoft.com> wrote in message
news:2216FE6C-190B-40F8-8EA6-BC1621CF4EEE@.microsoft.com...
> Is there some way to bypass a transaction, when doing a insert. Meaning
when
> i'm inside a BEGIN TRANS and wants to do a INSERT that will not be removed
if
> a ROLLBACK is done, is there any way to do that?
> Example:
> BEGIN TRANSACTION
> INSERT log_table values ( 'Survive' )
> ROLLBACK TRANSACTION
> Afterwards the log_table contains the 'Survive' row...
> Cheers|||Of course, if you're doing a BULK INSERT - or something else which drops the
connection immidiately, it wont get to the end of the code to insert the
data from the Variable table
C'est la vie.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4343db0d$0$139$7b0f0fd3@.mistral.news.newnet.co.uk...
> Put the data in a variable table, and save it after the transaction has
> completed.
> Variable tables by design are non-transactional.
> AFAIK, normal tables and temp tables are both affected by transactions, as
> are schema modifications etc.
> eg.
> begin trans
> insert into tblBob values ( 'something' )
> insert into @.myTable values ( 'done' )
> rollback trans
> insert into myRealTable SELECT value FROM @.myTable
>
>
> "Troy" <Troy@.discussions.microsoft.com> wrote in message
> news:2216FE6C-190B-40F8-8EA6-BC1621CF4EEE@.microsoft.com...
> when
removed
> if
>

Buttons

Hi.

Is there a way to insert a command button or flash button on to a report?

im using SSRS 2005 with VS 2005

Thanks...

You can't put standard controls on a report just specific RS controls.

You could put an image of a button and have some click through action on it.

|||But what about the effects of a button? Are there any rollover or click events?|||I can't think of a way.

Button That Execute Stored Procedure

I have a button in a form that I want to execute a simple stored procedure to insert a single value into a database table.
The Stored Procedure is:
=================
CREATE PROCEDURE bplog_insert_invoice_detail (@.invo_Id Int) AS
INSERT INTO Invoice_Detail
(Invo_Id)
VALUES(@.Invo_Id)
GO
=================
How do i pass the value from and text field (@.invo_id) and execute the stored procedure when a button is clicked.
Regards.

This is untested, but it should work:

SqlConnection cn = new SqlConnection(connectionString);
SqlComment cmd = cn.CreateCommand();

cmd.CommandText = "bplog_insert_invoice_detail";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parm = new SqlParameter("@.invo_Id", SqlDbType.Int)
parm.Value = parameterValue;
cmd.Parameters.Add(parm);
cmd.ExecuteNonQuery();

Hope that helps.

Bulkload copying identity columns within a table

The database I must insert my data into is rather old, and poorly designed.
We have a situation were a table can be the child of many tables; the "key"
in the child can reference more than one parent. I have created a simple
example that gets at the heart of my problem; how can I copy an identity
column from the parent into more than one column in the child.
The sample below produces:
Parent
1TestData
Child
1 1 0 DataTest -> I need 1 1 1 DataTest
Using SQL 2000, SQLXML 3.0 SP3. Table, Schema and XML:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
GO
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
GO
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<!-- This double copy does not work -->
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID"
child="Child"
child-key="ParentID ParentID_B" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Parent" sql:relation="Parent">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ParentID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Foo" type="xsd:string" />
<xsd:element name="Child" sql:relation="Child"
sql:relationship="Children">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChildID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Bar" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
Thanks in advance.
I also discovered that if I extend my sample data another row; 1 more parent
and child, that the subsequent values of ParentID_B increase based on the
number of parent's. Maybe I do not understand some mechanics going on behind
the scenes. Clearly there is a pattern here.
The XML below produces
ParentID Foo
-- --
1 TestData
2 TestData
ChildID ParentID ParentID_B Bar
-- -- -- --
1 1 0 DataTest
2 2 1 DataTest
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
|||What is the exactly question here? It looks like your previous post is
deleted.
What is the column type for ParentID_B?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>I also discovered that if I extend my sample data another row; 1 more
>parent
> and child, that the subsequent values of ParentID_B increase based on the
> number of parent's. Maybe I do not understand some mechanics going on
> behind
> the scenes. Clearly there is a pattern here.
> The XML below produces
> ParentID Foo
> -- --
> 1 TestData
> 2 TestData
> ChildID ParentID ParentID_B Bar
> -- -- -- --
> 1 1 0 DataTest
> 2 2 1 DataTest
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ROOT>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> </ROOT>
|||My original post described my problem, but the second post was to describe
some new information I had learned after playing with the code for a day. The
original post is still valid, it contains a table schema, xsd and xml
document.
However, I will try and re-describe this:
Imagine two tables, one called "Parent" and one called "Child". The schema
for these tables is as follows:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
In "Child" the value of "ParentID" and "ParentID_B" should be the same; they
need to be populated with the value from Parent.ParentID after the insert.
(See the xsd in the original post)
I thought I could use a relationship like this to populate both columns when
using SQLXML:
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID" <-This is copied more than once (does not
work)
child="Child"
child-key="ParentID ParentID_B" />
However, what I find is that the value of ParentID_B will be zero in the
first child, 1 in the second, 2 in the third etc. In other words, ParentID
will get the correct value, but ParentID_B will always be one less than
ParentID.
Since my relationship above does not work, how can I copy the values for
subsequent children of “Parent” and have both ParentID and ParentID_B be the
same value from the table "Parent"?
This example is heavily oversimplified, my real database is much more
complex, but this shows the mechanics of my problem more clearly I believe.
"Bertan ARI [MSFT]" wrote:

> What is the exactly question here? It looks like your previous post is
> deleted.
> What is the column type for ParentID_B?
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>
>
|||This looks like a bug in our code. Thanks for reporting it
Unfortunately, I don't know any simple workaround for now. Is it possible
for you to create a script that will update ParentID_B columns after
bulkload?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FEEBE11C-8B69-43CF-9635-B473A36CE22A@.microsoft.com...[vbcol=seagreen]
> My original post described my problem, but the second post was to describe
> some new information I had learned after playing with the code for a day.
> The
> original post is still valid, it contains a table schema, xsd and xml
> document.
> However, I will try and re-describe this:
> Imagine two tables, one called "Parent" and one called "Child". The schema
> for these tables is as follows:
> CREATE TABLE Parent (
> ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> Foo VarChar(10) NULL
> )
> CREATE TABLE Child (
> ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> ParentID Int NOT NULL,
> ParentID_B Int NOT NULL,
> Bar VarChar(10) NULL
> )
> In "Child" the value of "ParentID" and "ParentID_B" should be the same;
> they
> need to be populated with the value from Parent.ParentID after the insert.
> (See the xsd in the original post)
> I thought I could use a relationship like this to populate both columns
> when
> using SQLXML:
> <sql:relationship name="Children"
> parent="Parent"
> parent-key="ParentID ParentID" <-This is copied more than once (does not
> work)
> child="Child"
> child-key="ParentID ParentID_B" />
> However, what I find is that the value of ParentID_B will be zero in the
> first child, 1 in the second, 2 in the third etc. In other words, ParentID
> will get the correct value, but ParentID_B will always be one less than
> ParentID.
> Since my relationship above does not work, how can I copy the values for
> subsequent children of "Parent" and have both ParentID and ParentID_B be
> the
> same value from the table "Parent"?
> This example is heavily oversimplified, my real database is much more
> complex, but this shows the mechanics of my problem more clearly I
> believe.
>
> "Bertan ARI [MSFT]" wrote:

Bulkload copying identity columns within a table

The database I must insert my data into is rather old, and poorly designed.
We have a situation were a table can be the child of many tables; the "key"
in the child can reference more than one parent. I have created a simple
example that gets at the heart of my problem; how can I copy an identity
column from the parent into more than one column in the child.
The sample below produces:
Parent
--
1 TestData
Child
--
1 1 0 DataTest -> I need 1 1 1 DataTest
Using SQL 2000, SQLXML 3.0 SP3. Table, Schema and XML:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
GO
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
GO
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<!-- This double copy does not work -->
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID"
child="Child"
child-key="ParentID ParentID_B" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Parent" sql:relation="Parent">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ParentID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Foo" type="xsd:string" />
<xsd:element name="Child" sql:relation="Child"
sql:relationship="Children">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChildID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Bar" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
Thanks in advance.I also discovered that if I extend my sample data another row; 1 more parent
and child, that the subsequent values of ParentID_B increase based on the
number of parent's. Maybe I do not understand some mechanics going on behind
the scenes. Clearly there is a pattern here.
The XML below produces
ParentID Foo
-- --
1 TestData
2 TestData
ChildID ParentID ParentID_B Bar
-- -- -- --
1 1 0 DataTest
2 2 1 DataTest
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>|||What is the exactly question here? It looks like your previous post is
deleted.
What is the column type for ParentID_B?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>I also discovered that if I extend my sample data another row; 1 more
>parent
> and child, that the subsequent values of ParentID_B increase based on the
> number of parent's. Maybe I do not understand some mechanics going on
> behind
> the scenes. Clearly there is a pattern here.
> The XML below produces
> ParentID Foo
> -- --
> 1 TestData
> 2 TestData
> ChildID ParentID ParentID_B Bar
> -- -- -- --
> 1 1 0 DataTest
> 2 2 1 DataTest
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ROOT>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> </ROOT>|||My original post described my problem, but the second post was to describe
some new information I had learned after playing with the code for a day. Th
e
original post is still valid, it contains a table schema, xsd and xml
document.
However, I will try and re-describe this:
Imagine two tables, one called "Parent" and one called "Child". The schema
for these tables is as follows:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
In "Child" the value of "ParentID" and "ParentID_B" should be the same; they
need to be populated with the value from Parent.ParentID after the insert.
(See the xsd in the original post)
I thought I could use a relationship like this to populate both columns when
using SQLXML:
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID" <-This is copied more than once (does not
work)
child="Child"
child-key="ParentID ParentID_B" />
However, what I find is that the value of ParentID_B will be zero in the
first child, 1 in the second, 2 in the third etc. In other words, ParentID
will get the correct value, but ParentID_B will always be one less than
ParentID.
Since my relationship above does not work, how can I copy the values for
subsequent children of “Parent” and have both ParentID and ParentID_B be
the
same value from the table "Parent"?
This example is heavily oversimplified, my real database is much more
complex, but this shows the mechanics of my problem more clearly I believe.
"Bertan ARI [MSFT]" wrote:

> What is the exactly question here? It looks like your previous post is
> deleted.
> What is the column type for ParentID_B?
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>
>|||This looks like a bug in our code. Thanks for reporting it
Unfortunately, I don't know any simple workaround for now. Is it possible
for you to create a script that will update ParentID_B columns after
bulkload?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FEEBE11C-8B69-43CF-9635-B473A36CE22A@.microsoft.com...
> My original post described my problem, but the second post was to describe
> some new information I had learned after playing with the code for a day.
> The
> original post is still valid, it contains a table schema, xsd and xml
> document.
> However, I will try and re-describe this:
> Imagine two tables, one called "Parent" and one called "Child". The schema
> for these tables is as follows:
> CREATE TABLE Parent (
> ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> Foo VarChar(10) NULL
> )
> CREATE TABLE Child (
> ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> ParentID Int NOT NULL,
> ParentID_B Int NOT NULL,
> Bar VarChar(10) NULL
> )
> In "Child" the value of "ParentID" and "ParentID_B" should be the same;
> they
> need to be populated with the value from Parent.ParentID after the insert.
> (See the xsd in the original post)
> I thought I could use a relationship like this to populate both columns
> when
> using SQLXML:
> <sql:relationship name="Children"
> parent="Parent"
> parent-key="ParentID ParentID" <-This is copied more than once (does not
> work)
> child="Child"
> child-key="ParentID ParentID_B" />
> However, what I find is that the value of ParentID_B will be zero in the
> first child, 1 in the second, 2 in the third etc. In other words, ParentID
> will get the correct value, but ParentID_B will always be one less than
> ParentID.
> Since my relationship above does not work, how can I copy the values for
> subsequent children of "Parent" and have both ParentID and ParentID_B be
> the
> same value from the table "Parent"?
> This example is heavily oversimplified, my real database is much more
> complex, but this shows the mechanics of my problem more clearly I
> believe.
>
> "Bertan ARI [MSFT]" wrote:
>

Bulkadmin role (BULK INSERT)

Hello,

I am trying to load a simple tab-delimited data file to SQL Server. I
created a format file to go with it, since the data file differs from
the destination table in number of columns.

When I execute the query, I get an error saying that only sysadmin or
bulkadmin roles are allowed to use the BULK INSERT statement. So, I
proceeded with the Enterprise Manager to grant myself those roles.
However, I could not find sysadmin or bulkadmin roles using the
Enterprise Manager. From what I read from my books, I thought these
were fixed server roles and that they would be there.

So I have a few questions:
1) How do I create a user account/role that can issue BULK INSERT
commands?

2) Why is BULK INSERT considered a dangerous operation that it
requires special privileges? What are its implications? I have a
couple of books that say that a user should be aware of its
implications before using it, but they don't actually describe what
those implications might be.

3) It seems that I can load the data file using BCP utility, without
such privileges. If so, what is the difference?

Thanks!> So, I proceeded with the Enterprise Manager to grant myself those
> roles. However, I could not find sysadmin or bulkadmin roles using the
> Enterprise Manager. From what I read from my books, I thought these
> were fixed server roles and that they would be there.
> So I have a few questions:
> 1) How do I create a user account/role that can issue BULK INSERT
> commands?

The roles are there but you need to be a sysadmin role member or a member of
that fixed server role in order to add members. Ask your DBA to do this.

> 2) Why is BULK INSERT considered a dangerous operation that it
> requires special privileges? What are its implications? I have a
> couple of books that say that a user should be aware of its
> implications before using it, but they don't actually describe what
> those implications might be.

The main security implication is that BULK INSERT accesses external data
under the security context of the SQL Server service account rather than the
invoking user's account.

> 3) It seems that I can load the data file using BCP utility, without
> such privileges. If so, what is the difference?

Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
BCP access data under the security context of the invoking user.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0406052020.16b6b4e6@.posting.google.c om...
> Hello,
> I am trying to load a simple tab-delimited data file to SQL Server. I
> created a format file to go with it, since the data file differs from
> the destination table in number of columns.
> When I execute the query, I get an error saying that only sysadmin or
> bulkadmin roles are allowed to use the BULK INSERT statement. So, I
> proceeded with the Enterprise Manager to grant myself those roles.
> However, I could not find sysadmin or bulkadmin roles using the
> Enterprise Manager. From what I read from my books, I thought these
> were fixed server roles and that they would be there.
> So I have a few questions:
> 1) How do I create a user account/role that can issue BULK INSERT
> commands?
> 2) Why is BULK INSERT considered a dangerous operation that it
> requires special privileges? What are its implications? I have a
> couple of books that say that a user should be aware of its
> implications before using it, but they don't actually describe what
> those implications might be.
> 3) It seems that I can load the data file using BCP utility, without
> such privileges. If so, what is the difference?
> Thanks!|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<bRGwc.3644$uX2.3489@.newsread2.news.pas.earthlink.n et>...
> > So, I proceeded with the Enterprise Manager to grant myself those
> > roles. However, I could not find sysadmin or bulkadmin roles using the
> > Enterprise Manager. From what I read from my books, I thought these
> > were fixed server roles and that they would be there.
> > So I have a few questions:
> > 1) How do I create a user account/role that can issue BULK INSERT
> > commands?
> The roles are there but you need to be a sysadmin role member or a member of
> that fixed server role in order to add members. Ask your DBA to do this.

Hello Dan,

This was for personal use, so that makes me the DBA. I believe I
disabled the "sa" account when I first installed SQL Server (based on
some suggestions due to security risks). Perhaps that has something
to do with it. I will look into it.

> Client-based bulk insert techniques like SQLOLEDB IRowsetFastLoad and ODBC
> BCP access data under the security context of the invoking user.

Thanks! This clarifies the risk implications of BULK INSERT vs. bcp
that was not in the books. It looks like Bcp is the sure way to go
for most users.

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP