Difficulty on inserting data into several tables from XML file using Bulk Insert

Hi, I am a newbie for SQL's XML section and need some help from you guys. I just go through some simple articles with simple sample record. However, I found confusing when I want to inserting data into several tables from a prefer XML file.

My scenario is like that :

Tables :

1) trade ( id int)

2) trans ( id int , startdate datetime , enddate datetime , status bit ,comment varchar(50), tranCode int)

* tranCode = id from trade table

3) transInfo ( id int , userlogin int , amount money , transID int ) * transID = id from trans table

4) trandetails ( id int, transID int ) * transID = id from trans table

Preferred XML :

<?xml version='1.0' encoding='utf-8'?>

<ROOT>

<trade code="1">

<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >

<transinfo>

<clienttrans login="Alex" amount="1000">
</clienttrans>

</transinfo>

<trandetails>
<trandetail><value>Shipping</value></trandetail>
</trandetails>

<comments><comment>Done</comment></comments>

</trans>

<trans id = '123' startdate = '2007-08-02 11:35:00' enddate='2007-04-29 11:36:00' status = '1' >

<transinfo>

<clienttrans login="Ken" amount="20000">
</clienttrans>

</transinfo>

<trandetails>
<trandetail><value>Transportation</value></trandetail>
</trandetails>

<comments><comment>Done</comment></comments>
</trans>

</trade>

</ROOT>

I am confusing on how to arrange up the elements based on the tables which were created. Or have to re-fix my table ? Any suggestion based on my scenario. Hope able to get any ideas/opinions from here asap. Thanks alot.

Best Regards,

Hans

Problem solved although kinda confusing at the early stage when doing the XML schema.

Difficulties getting @@identity to work in transaction

I am having some problems wit a form where i need to insert a record into a table and then get the id of the record i just inserted and use it to insert a record in another table for a many to many relationship. The code is below (I cut out as much as i could to make it more readable).

The erro message i get is this:

Error saving file ATLPIXOFC.txt Reason: System.Data.SqlClient.SqlException: Prepared statement '(@.FileName varchar(13),@. i' expects parameter @.FileID, which was not supplied. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microvit_Document_Product_Document_Add_v2.insertDocumentToDB() in e:\inetpub\wwwroot\Microvit\Document\Product_Document_Add_v2.aspx.vb:line 127 at Microvit_Document_Product_Document_Add_v2.btnInsert_Click(Object sender, EventArgs e) in e:\inetpub\wwwroot\Microvit\Document\Product_Document_Add_v2.aspx.vb:line 37

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, ParameterDirection.Output))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute 1st Command

'****************************************************************************

myCommand.CommandText ="INSERT INTO [Files] ([FileName) VALUES (@.FileName); SELECT @.FileID = @.@.Identity;"

myCommand.ExecuteNonQuery()

'****************************************************************************

' Execute 2nd Command

'****************************************************************************

'myCommand.Parameters("@.FileID").Value = ddlProduct.SelectedValue

myCommand.CommandText ="INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, @.FileID)"

myCommand.ExecuteNonQuery()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

Has nothing to do with transactions.

You defined the @.FileID parameter as such:

myCommand.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, ParameterDirection.Output))

Note, you said output. The second command expects it as input.

You can fix this a number of ways. Use two different mycommand objects with different parameters, or set variables to the parameters, clear and rebuild the parameters, and reset the parameter values from your variables, change your first query to accept the same parameters, or just use a single command.

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute Command

'****************************************************************************

myCommand.CommandText ="INSERT INTO [Files] ([FileName) VALUES (@.FileName) INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, SCOPE_IDENTITY())"

myCommand.ExecuteNonQuery()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

|||

Or if you really need the identity back in your program:

ProtectedSub insertDocumentToDB()

Dim myConnStringAsString = ConfigurationManager.ConnectionStrings("Master_DataConnectionString").ConnectionString

Dim myConnectionAs System.Data.IDbConnection =New System.Data.SqlClient.SqlConnection(myConnString)

Dim myCommandAs System.Data.IDbCommand =New System.Data.SqlClient.SqlCommand

Dim myTransactionAs System.Data.IDbTransaction =Nothing

myCommand.Connection = myConnection

myCommand.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar))

myCommand.Parameters.Add(New SqlParameter("@.ProductID", SqlDbType.Int))

myCommand.Parameters("@.FileName").Value = fuDocument.FileName

myCommand.Parameters("@.ProductID").Value = ddlProduct.SelectedValue

Try

'****************************************************************************

' BeginTransaction() Requires Open Connection

'****************************************************************************

myConnection.Open()

myTransaction = myConnection.BeginTransaction()

'****************************************************************************

' Assign Transaction to Command

'****************************************************************************

myCommand.Transaction = myTransaction

'****************************************************************************

' Execute Command

'****************************************************************************

myCommand.CommandText ="SET NOCOUNT ON INSERT INTO [Files] ([FileName) VALUES (@.FileName) SET NOCOUNT OFF SELECT SCOPE_IDENTITY() SET NOCOUNT ON INSERT INTO [ProductFiles] ([ProductID], [FileID]) VALUES (@.ProductID, SCOPE_IDENTITY()) SET NOCOUNT OFF"

dim MyIdentity=myCommand.ExecuteScaler()

myTransaction.Commit()

Catch

myTransaction.Rollback()

Throw

Finally

myConnection.Close()

EndTry

EndSub

|||Oops, I guess I should mention that you can of course, create a stored procedure to do the insert as well which takes all the things you want to insert into your various tables as parameters.sql

Difficult Insert where clause.

Sorry for anyone who has seen this query and dataset before but this is a
seperate question/issue which I am working on.
I am struggling trying to get my insert statement not to insert record 5
because the ToURN has been used before in a previous record(1).
Basically I am trying to write some logic which says that if the ToURN in
one record already exists in the FromURN field of a previous record then do
not insert the record.
Does anyone know how I would write the sql to do this.
RecNO MergeFromURN MergeToURN MergeDateMerged
1 100 200 15/06/1982
2 200 300 15/06/1982
3 300 400 15/06/1982
4 500 600 15/06/1982
5 700 100 15/06/1982
6 100 100 15/06/1982
7 NULL 100 15/06/1982
8 700 0 15/06/1982
So far I have the following sql but need to go that step further to stop
record 5 being inserted because 100 already has been inserted as a from urn
in record 1.
INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
from myTable
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and MergeToURN not in
(select MoveToURN from Move))
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN
while @.@.ROWCOUNT > 0
begin
update A set MoveToURN = B.MoveToURN
from Move A
inner join Move B on A.MoveToURN=B.MoveFromURN
end
Can anyone help me with this.Stephen
You are posted this question a few times some time ago , so people gave you
solutuion (me include), so would you mind at least posting DDL+ sample data
+ expected result
CREATE TABLE #Test
(
RecNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
f INT ,
t INT ,
dt DATETIME NOT NULL
)
INSERT INTO #Test (f,t,dt) VALUES (100,200,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (200,300,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (300,400,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (500,600,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (700,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (100,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (NULL,100,'19820615')
INSERT INTO #Test (f,t,dt) VALUES (700,0,'19820615')
GO
INSERT INTO YourTable <column lists>
SELECT f, t , dt FROM #Test
WHERE RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=#Test.f)
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:2ED15ACC-2E2C-4382-AF55-3E2F997D7C24@.microsoft.com...
> Sorry for anyone who has seen this query and dataset before but this is a
> seperate question/issue which I am working on.
> I am struggling trying to get my insert statement not to insert record 5
> because the ToURN has been used before in a previous record(1).
> Basically I am trying to write some logic which says that if the ToURN in
> one record already exists in the FromURN field of a previous record then
> do
> not insert the record.
> Does anyone know how I would write the sql to do this.
> RecNO MergeFromURN MergeToURN MergeDateMerged
> 1 100 200 15/06/1982
> 2 200 300 15/06/1982
> 3 300 400 15/06/1982
> 4 500 600 15/06/1982
> 5 700 100 15/06/1982
> 6 100 100 15/06/1982
> 7 NULL 100 15/06/1982
> 8 700 0 15/06/1982
> So far I have the following sql but need to go that step further to stop
> record 5 being inserted because 100 already has been inserted as a from
> urn
> in record 1.
> INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
> SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
> from myTable
> where MergeFromURN is not null and MergeToURN is not null
> and MergeFromURN <> 0 and MergeToURN <> 0 and
> MergeFromURN <> MergeToURN and
> (MergeFromURN not in (select MoveFromURN from Move) and MergeToURN not in
> (select MoveToURN from Move))
> GROUP BY MergeFromURN, MergeToURN
> Order by MergeFromURN
> while @.@.ROWCOUNT > 0
>
----
--

>
> Can anyone help me with this.
>|||Sorry but this doesn't work as I need.
It inserts 5 rows into the table including the row. 700, 100 (5th Record).
I want to write in further logic which wouldn'd allow this record to be
inserted on the basis that the ToURN value of 100 already exists in a prior
record in the FromURN column.
Its quite hard to explain and I'm sorry for the previous posts which may be
covering the same ground.
"Uri Dimant" wrote:

> Stephen
> You are posted this question a few times some time ago , so people gave yo
u
> solutuion (me include), so would you mind at least posting DDL+ sample dat
a
> + expected result
>
> CREATE TABLE #Test
> (
> RecNo INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> f INT ,
> t INT ,
> dt DATETIME NOT NULL
> )
> INSERT INTO #Test (f,t,dt) VALUES (100,200,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (200,300,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (300,400,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (500,600,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (700,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (100,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (NULL,100,'19820615')
> INSERT INTO #Test (f,t,dt) VALUES (700,0,'19820615')
> GO
> INSERT INTO YourTable <column lists>
> SELECT f, t , dt FROM #Test
> WHERE RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=#Test.f)
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:2ED15ACC-2E2C-4382-AF55-3E2F997D7C24@.microsoft.com...
>
> ----
--
>
>
>|||Hi
> It inserts 5 rows into the table including the row. 700, 100 (5th Record).
No it does not , look at all columns and check it out
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:79692AC3-9860-40BB-9409-9F0EC9C880A5@.microsoft.com...
> Sorry but this doesn't work as I need.
> It inserts 5 rows into the table including the row. 700, 100 (5th Record).
> I want to write in further logic which wouldn'd allow this record to be
> inserted on the basis that the ToURN value of 100 already exists in a
> prior
> record in the FromURN column.
> Its quite hard to explain and I'm sorry for the previous posts which may
> be
> covering the same ground.
> "Uri Dimant" wrote:
>|||Honestly I ran it there now and it inserts 5 rows even though I don;t want i
t
to insert 700,100. Was trying to get around things without using a cursor
but i can't seem to find a way of doing this without using a cursor.
"Uri Dimant" wrote:

> Hi
> No it does not , look at all columns and check it out
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:79692AC3-9860-40BB-9409-9F0EC9C880A5@.microsoft.com...
>
>|||Hi
When I ran it I also go trow 5 in the result set.
This may have something to do with the order in which records are processed.
As in the options on your SQL server setup may be different to the person
who provided the solution, thus when you are running the select to insert ro
w
5 row 1 isn't necessarily in yet?
Just a guess
--
Chan
Programmer
"Stephen" wrote:
> Honestly I ran it there now and it inserts 5 rows even though I don;t want
it
> to insert 700,100. Was trying to get around things without using a cursor
> but i can't seem to find a way of doing this without using a cursor.
> "Uri Dimant" wrote:
>|||I wrote this SQL Statement based on Uri's initial SQL Statement and DDL
select DISTINCT t1.f, t1.t, t1.dt
from #Test t1
inner join #Test t2 on t1.RecNo < t2.RecNo and
t1.f != t2.t
AND t2.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
It returns 4 rows
f t dt
100 200 2005-07-27 07:26:33.490
200 300 2005-07-27 07:26:40.897
300 400 2005-07-27 07:26:48.523
500 600 2005-07-27 07:26:54.523
Is this what you are looking for? You should avoid cursors whenever possible
.
"Stephen" wrote:
> Honestly I ran it there now and it inserts 5 rows even though I don;t want
it
> to insert 700,100. Was trying to get around things without using a cursor
> but i can't seem to find a way of doing this without using a cursor.
> "Uri Dimant" wrote:
>|||Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
SKIN that one up and smoke it!!
"frank chang" wrote:
> I wrote this SQL Statement based on Uri's initial SQL Statement and DDL
> select DISTINCT t1.f, t1.t, t1.dt
> from #Test t1
> inner join #Test t2 on t1.RecNo < t2.RecNo and
> t1.f != t2.t
> AND t2.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
>
> It returns 4 rows
> f t dt
> 100 200 2005-07-27 07:26:33.490
> 200 300 2005-07-27 07:26:40.897
> 300 400 2005-07-27 07:26:48.523
> 500 600 2005-07-27 07:26:54.523
> Is this what you are looking for? You should avoid cursors whenever possib
le.
>
> "Stephen" wrote:
>|||Stephen, This select statement is the most appropriate one:
INSERT INTO ......
SELECT m.f, m.t , m.dt FROM #Test m
WHERE m.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=m.f)
AND
NOT EXISTS -- like MINUS operator in ORACLE, subtract the ones you don't wa
nt
(select *
from #Test t1
inner join #Test t2 on t1.RecNo < t2.RecNo
and t1.f != t2.t AND t2.f != t1.t
AND t1.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t2.f)
AND t1.RecNo = m.RecNo)
Sorry about that. I didn't drink coffee this morning.
"Stephen" wrote:
> Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
> SKIN that one up and smoke it!!
> "frank chang" wrote:
>|||Stephen, The previous SQL statment had a typo in it (I cut and pasted by
mistake)
This statement may help:
SELECT m.f, m.t , m.dt FROM #Test m
WHERE m.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f=m.f)
AND
NOT EXISTS // subtract set where from-urn number is already used
(select *
from #Test t1
inner join #Test t2 on t1.RecNo > t2.RecNo
and t2.f = t1.t
AND t1.RecNo=(SELECT MIN(RecNo) FROM #Test T WHERE T.f = t1.f)
AND t1.RecNo = m.RecNo)
Thnak you for your help.
"Stephen" wrote:
> Legend tough guy now thats the kinda sql i'm talking about!! OH YEAH!!
> SKIN that one up and smoke it!!
> "frank chang" wrote:
>

Differntiate Insert/Update in Trigger

I have a trigger like this

CREATE TRIGGER [TR_INSREC] ON [dbo].[SUB_MIS]
FOR INSERT, UPDATE
AS
IF <insert operation>
logic1
ELSE IF <update operation>
logic2
END IF
END

Now I want to know how can I get whether the trigger fires for insert or update operation. Because for insert, I put logic1 and for update, I put logic2...

Kindly help.....

You have to use Inserted & Deleted spl tables, these trigger scoped tables both table structure will be same as your main base table.

For Insert:

Only Inserted Table have data

Deleted Table will be empty

For Update:

Both Inserted & Deleted table have data

For Delete:

Inserted Table will be EMPTY

Only Deleted Table have data

Code Snippet

CREATE TRIGGER TR_INSREC ON dbo.SUB_MIS FOR INSERT, UPDATE, DELETE

AS

Begin

Declare @.InsertCount as Int;

Declare @.DeleteCount as Int;

Select @.InsertCount =0, @.DeleteCount=0

Select Top 1 @.InsertCount = 1 From Inserted;

Select Top 1 @.DeleteCount = 1 From Deleted;

IF @.InsertCount=1 And @.DeleteCount=0

Begin

--Your Insert logic1

Print 'Insert'

End

IF @.InsertCount=1 And @.DeleteCount=1

Begin

--Your Update Logic

Print 'Update'

End

IF @.InsertCount=0 And @.DeleteCount=1

Begin

--Your Delete Logic

Print 'Delete'

End

End

|||

Hi subhendude,

if you have different logic, why don;t you simply make 2 triggers - one for insert and one for delete?

Different sort order - same set up

I have copied and restore a database from one SQL 2000
server to another with the same set up. I then ran stored
procedures to insert data from one table to the resultant
table, and expect the data to be the same as that of the
original machine. I have discovered, however, that the
same data has been inserted, but the data sort order is
different of that of the original server. On the SQL
statement to insert data there is a group by and order by
clause so the inserted data should have the same order.
The resultant table does not have any indexes (on both
servers). Could anyone tell me what could have cause this?
This could be important to us, as the data in the
resultant table will be BCP out to a report server, and
the sort order could be crucial. I know I could possibly
solve the problem by adding clustered indexes, but I would
like to know the cause.
The only difference in specification is that the new
server is on Service Pack version 8:00:818 (SP3), and the
original server is on 8:00:760 (SP3).
Both servers have the same collation , and both on Windows
2000 SP3Only way to guarantee a certain order is to have ORDER BY in the queries. Not even having a
clustered index will guarantee getting the data in a certain order.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alex" <alex.au@.ace-ina.com> wrote in message news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> I have copied and restore a database from one SQL 2000
> server to another with the same set up. I then ran stored
> procedures to insert data from one table to the resultant
> table, and expect the data to be the same as that of the
> original machine. I have discovered, however, that the
> same data has been inserted, but the data sort order is
> different of that of the original server. On the SQL
> statement to insert data there is a group by and order by
> clause so the inserted data should have the same order.
> The resultant table does not have any indexes (on both
> servers). Could anyone tell me what could have cause this?
> This could be important to us, as the data in the
> resultant table will be BCP out to a report server, and
> the sort order could be crucial. I know I could possibly
> solve the problem by adding clustered indexes, but I would
> like to know the cause.
> The only difference in specification is that the new
> server is on Service Pack version 8:00:818 (SP3), and the
> original server is on 8:00:760 (SP3).
> Both servers have the same collation , and both on Windows
> 2000 SP3|||Thanks for the reply Tibor. The problem is , as I stated
earlier, the sql statement has got group by and order by
included, and insert into a table with no indexes. I am
trying to say that for some reason - same sql used to do
insert on two machines with same set up somehow result
with data stored in different order.
>--Original Message--
>Only way to guarantee a certain order is to have ORDER BY
in the queries. Not even having a
>clustered index will guarantee getting the data in a
certain order.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Alex" <alex.au@.ace-ina.com> wrote in message
news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
>> I have copied and restore a database from one SQL 2000
>> server to another with the same set up. I then ran
stored
>> procedures to insert data from one table to the
resultant
>> table, and expect the data to be the same as that of the
>> original machine. I have discovered, however, that the
>> same data has been inserted, but the data sort order is
>> different of that of the original server. On the SQL
>> statement to insert data there is a group by and order
by
>> clause so the inserted data should have the same order.
>> The resultant table does not have any indexes (on both
>> servers). Could anyone tell me what could have cause
this?
>> This could be important to us, as the data in the
>> resultant table will be BCP out to a report server, and
>> the sort order could be crucial. I know I could possibly
>> solve the problem by adding clustered indexes, but I
would
>> like to know the cause.
>> The only difference in specification is that the new
>> server is on Service Pack version 8:00:818 (SP3), and
the
>> original server is on 8:00:760 (SP3).
>> Both servers have the same collation , and both on
Windows
>> 2000 SP3
>
>.
>|||Sorry, I missed the part that the statements has ORDER BY. Does the columns you ORDER BY over have
the same collation? Try with sp_help <tblname>.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:2b58301c3930c$3af2b0e0$a601280a@.phx.gbl...
> Thanks for the reply Tibor. The problem is , as I stated
> earlier, the sql statement has got group by and order by
> included, and insert into a table with no indexes. I am
> trying to say that for some reason - same sql used to do
> insert on two machines with same set up somehow result
> with data stored in different order.
> >--Original Message--
> >Only way to guarantee a certain order is to have ORDER BY
> in the queries. Not even having a
> >clustered index will guarantee getting the data in a
> certain order.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Alex" <alex.au@.ace-ina.com> wrote in message
> news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> >> I have copied and restore a database from one SQL 2000
> >> server to another with the same set up. I then ran
> stored
> >> procedures to insert data from one table to the
> resultant
> >> table, and expect the data to be the same as that of the
> >> original machine. I have discovered, however, that the
> >> same data has been inserted, but the data sort order is
> >> different of that of the original server. On the SQL
> >> statement to insert data there is a group by and order
> by
> >> clause so the inserted data should have the same order.
> >> The resultant table does not have any indexes (on both
> >> servers). Could anyone tell me what could have cause
> this?
> >> This could be important to us, as the data in the
> >> resultant table will be BCP out to a report server, and
> >> the sort order could be crucial. I know I could possibly
> >> solve the problem by adding clustered indexes, but I
> would
> >> like to know the cause.
> >>
> >> The only difference in specification is that the new
> >> server is on Service Pack version 8:00:818 (SP3), and
> the
> >> original server is on 8:00:760 (SP3).
> >>
> >> Both servers have the same collation , and both on
> Windows
> >> 2000 SP3
> >
> >
> >.
> >|||And what Tibor is trying to say is that unless you specify ORDER BY then you
cannot guarantee the order you get the data back.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:2b58301c3930c$3af2b0e0$a601280a@.phx.gbl...
> Thanks for the reply Tibor. The problem is , as I stated
> earlier, the sql statement has got group by and order by
> included, and insert into a table with no indexes. I am
> trying to say that for some reason - same sql used to do
> insert on two machines with same set up somehow result
> with data stored in different order.
> >--Original Message--
> >Only way to guarantee a certain order is to have ORDER BY
> in the queries. Not even having a
> >clustered index will guarantee getting the data in a
> certain order.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Alex" <alex.au@.ace-ina.com> wrote in message
> news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> >> I have copied and restore a database from one SQL 2000
> >> server to another with the same set up. I then ran
> stored
> >> procedures to insert data from one table to the
> resultant
> >> table, and expect the data to be the same as that of the
> >> original machine. I have discovered, however, that the
> >> same data has been inserted, but the data sort order is
> >> different of that of the original server. On the SQL
> >> statement to insert data there is a group by and order
> by
> >> clause so the inserted data should have the same order.
> >> The resultant table does not have any indexes (on both
> >> servers). Could anyone tell me what could have cause
> this?
> >> This could be important to us, as the data in the
> >> resultant table will be BCP out to a report server, and
> >> the sort order could be crucial. I know I could possibly
> >> solve the problem by adding clustered indexes, but I
> would
> >> like to know the cause.
> >>
> >> The only difference in specification is that the new
> >> server is on Service Pack version 8:00:818 (SP3), and
> the
> >> original server is on 8:00:760 (SP3).
> >>
> >> Both servers have the same collation , and both on
> Windows
> >> 2000 SP3
> >
> >
> >.
> >|||Tibor
The two server are of the same collation
(SQL_Latin1_General_CP1_CI_AS) and nothing was specified
on the columns during insert.
What happened was that we are migrating a database from
one server to another server. Using the same codes but the
result from the second server was of different sort order.
Software wise are the same I can only think of something
in the set up. The original database is in US, and we are
migrating it to UK. Also the UK Server SP3 version
8:00:818 is the only difference to US ( 8:00:760)
>--Original Message--
>Sorry, I missed the part that the statements has ORDER
BY. Does the columns you ORDER BY over have
>the same collation? Try with sp_help <tblname>.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"Alex" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b58301c3930c$3af2b0e0$a601280a@.phx.gbl...
>> Thanks for the reply Tibor. The problem is , as I stated
>> earlier, the sql statement has got group by and order by
>> included, and insert into a table with no indexes. I am
>> trying to say that for some reason - same sql used to do
>> insert on two machines with same set up somehow result
>> with data stored in different order.
>> >--Original Message--
>> >Only way to guarantee a certain order is to have ORDER
BY
>> in the queries. Not even having a
>> >clustered index will guarantee getting the data in a
>> certain order.
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?oi=djq&as
>> ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Alex" <alex.au@.ace-ina.com> wrote in message
>> news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
>> >> I have copied and restore a database from one SQL
2000
>> >> server to another with the same set up. I then ran
>> stored
>> >> procedures to insert data from one table to the
>> resultant
>> >> table, and expect the data to be the same as that of
the
>> >> original machine. I have discovered, however, that
the
>> >> same data has been inserted, but the data sort order
is
>> >> different of that of the original server. On the SQL
>> >> statement to insert data there is a group by and
order
>> by
>> >> clause so the inserted data should have the same
order.
>> >> The resultant table does not have any indexes (on
both
>> >> servers). Could anyone tell me what could have cause
>> this?
>> >> This could be important to us, as the data in the
>> >> resultant table will be BCP out to a report server,
and
>> >> the sort order could be crucial. I know I could
possibly
>> >> solve the problem by adding clustered indexes, but I
>> would
>> >> like to know the cause.
>> >>
>> >> The only difference in specification is that the new
>> >> server is on Service Pack version 8:00:818 (SP3), and
>> the
>> >> original server is on 8:00:760 (SP3).
>> >>
>> >> Both servers have the same collation , and both on
>> Windows
>> >> 2000 SP3
>> >
>> >
>> >.
>> >
>
>.
>|||The two burning questions are:
1. The collation on the *column*, not the server. Check using sp_help <tblname>.
2. The query. That you indeed have an ORDER BY in the query.
If above both hold (same collation in all column(s) in all tables on both servers; and you do have
ORDER BY on the query and run exactly the same query on both servers), then it is strange.
Unless you have a collation where SQL Server "doesn't care". Some old SQL collation didn't care if
the upper or lower case letter was returned first. Say you have 'alex' and 'Alex' - which one should
come first? Does it matter?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:066601c39312$04bf5bd0$a001280a@.phx.gbl...
> Tibor
> The two server are of the same collation
> (SQL_Latin1_General_CP1_CI_AS) and nothing was specified
> on the columns during insert.
> What happened was that we are migrating a database from
> one server to another server. Using the same codes but the
> result from the second server was of different sort order.
> Software wise are the same I can only think of something
> in the set up. The original database is in US, and we are
> migrating it to UK. Also the UK Server SP3 version
> 8:00:818 is the only difference to US ( 8:00:760)
> >--Original Message--
> >Sorry, I missed the part that the statements has ORDER
> BY. Does the columns you ORDER BY over have
> >the same collation? Try with sp_help <tblname>.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> >"Alex" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b58301c3930c$3af2b0e0$a601280a@.phx.gbl...
> >> Thanks for the reply Tibor. The problem is , as I stated
> >> earlier, the sql statement has got group by and order by
> >> included, and insert into a table with no indexes. I am
> >> trying to say that for some reason - same sql used to do
> >> insert on two machines with same set up somehow result
> >> with data stored in different order.
> >>
> >> >--Original Message--
> >> >Only way to guarantee a certain order is to have ORDER
> BY
> >> in the queries. Not even having a
> >> >clustered index will guarantee getting the data in a
> >> certain order.
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at: http://groups.google.com/groups?oi=djq&as
> >> ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Alex" <alex.au@.ace-ina.com> wrote in message
> >> news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> >> >> I have copied and restore a database from one SQL
> 2000
> >> >> server to another with the same set up. I then ran
> >> stored
> >> >> procedures to insert data from one table to the
> >> resultant
> >> >> table, and expect the data to be the same as that of
> the
> >> >> original machine. I have discovered, however, that
> the
> >> >> same data has been inserted, but the data sort order
> is
> >> >> different of that of the original server. On the SQL
> >> >> statement to insert data there is a group by and
> order
> >> by
> >> >> clause so the inserted data should have the same
> order.
> >> >> The resultant table does not have any indexes (on
> both
> >> >> servers). Could anyone tell me what could have cause
> >> this?
> >> >> This could be important to us, as the data in the
> >> >> resultant table will be BCP out to a report server,
> and
> >> >> the sort order could be crucial. I know I could
> possibly
> >> >> solve the problem by adding clustered indexes, but I
> >> would
> >> >> like to know the cause.
> >> >>
> >> >> The only difference in specification is that the new
> >> >> server is on Service Pack version 8:00:818 (SP3), and
> >> the
> >> >> original server is on 8:00:760 (SP3).
> >> >>
> >> >> Both servers have the same collation , and both on
> >> Windows
> >> >> 2000 SP3
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||> On the SQL
> statement to insert data there is a group by and order by
> clause so the inserted data should have the same order.
I understand you basically have something like:
INSERT INTO MyTable
SELECT MyColumn
FROM MyOtherTable
GROUP BY MyColumn
ORDER BY MyColumn
And rows are not returned in sequence by the ordered column when you
run:
SELECT MyColumn
FROM MyTable
This is because the insert query is writing data to a table, not a
sequential file. Because a table is an unordered set of rows, a
relational database may return data in no particular sequence unless
constrained by an ORDER BY clause. As stated by the other responses,
you *must* specify ORDER BY on this SELECT query in order to guarantee
ordering:
SELECT MyColumn
FROM MyTable
ORDER BY MyTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Alex" <alex.au@.ace-ina.com> wrote in message
news:2b3b001c392fb$3e127730$a601280a@.phx.gbl...
> I have copied and restore a database from one SQL 2000
> server to another with the same set up. I then ran stored
> procedures to insert data from one table to the resultant
> table, and expect the data to be the same as that of the
> original machine. I have discovered, however, that the
> same data has been inserted, but the data sort order is
> different of that of the original server. On the SQL
> statement to insert data there is a group by and order by
> clause so the inserted data should have the same order.
> The resultant table does not have any indexes (on both
> servers). Could anyone tell me what could have cause this?
> This could be important to us, as the data in the
> resultant table will be BCP out to a report server, and
> the sort order could be crucial. I know I could possibly
> solve the problem by adding clustered indexes, but I would
> like to know the cause.
> The only difference in specification is that the new
> server is on Service Pack version 8:00:818 (SP3), and the
> original server is on 8:00:760 (SP3).
> Both servers have the same collation , and both on Windows
> 2000 SP3

different datatypes with UPDATE or INSERT

I'm writing an SP that retrieves data on a linked SQL server, and
selectively updates or inserts like-named rows on the local server. Two
columns on the remote server are Mileage varchar(25) and Price varchar(25),
whereas on the local server the datatypes are INT and MONEY.
As an example of what's needed for 3 sample rows:
Mileage (remote) = 23,456; 'Call for Details'; 56,789
Mileage (local) = 23,456; NULL; 56,789
Price (remote) = $9,995.00; 'Call Us'; $14,900.00
Price (local) = $9,995.00; 'NULL'; $14,900.00
How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
other words, if there are 'non-int' or 'non-money' values coming over from
the remote server, will SQL Server automatically convert these 'invalid'
values to NULL, or do I need to handle it somehow, maybe via a CASE
expression in the UPDATE or INSERT INTO, or...?
Thanks.
Message posted via http://www.webservertalk.comSQL Server will attempt a cast from a character field to a numeric. If it
fails, it will throw an error. A better option would be casting yourself and
logging any failures, including parameters that caused the failure. Humans
can then read the log and correct the data.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"The Gekkster via webservertalk.com" wrote:

> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price varchar(25)
,
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||It will try to automatically convert the data from varchar to integer.
However, if any value in the insert is invalid, it will crash. A good way
to handle this is using an Instead Of trigger. Instead of just inserting
the data, you run a check on the data to see if it is valid. Bad data goes
into an exception table, good into the real table.
There are quite a few different routines around to validate that a value is
a reasonable numeric value, but you will likely not want to use isNumeric as
it is very liberal. Search on groups.google.com for isNumeric and you will
see that is covered quite often.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"The Gekkster via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:852fc5294d584b37b647b85f7d799e95@.SQ
webservertalk.com...
> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price
> varchar(25),
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||You need to test the values in the update, and update the local table to nul
l
when the remote value is not numeric... (This should handle 99.99% of teh
cases
Update <Table> Set
LocalCol = Case IsNumeric(RemoteCol)
When 1 Then Cast (RemoteCol as Integer)
Else Null End
From ...
"The Gekkster via webservertalk.com" wrote:

> I'm writing an SP that retrieves data on a linked SQL server, and
> selectively updates or inserts like-named rows on the local server. Two
> columns on the remote server are Mileage varchar(25) and Price varchar(25)
,
> whereas on the local server the datatypes are INT and MONEY.
> As an example of what's needed for 3 sample rows:
> Mileage (remote) = 23,456; 'Call for Details'; 56,789
> Mileage (local) = 23,456; NULL; 56,789
> Price (remote) = $9,995.00; 'Call Us'; $14,900.00
> Price (local) = $9,995.00; 'NULL'; $14,900.00
> How does SQL Server handle an UPDATE or INSERT INTO in this situation? In
> other words, if there are 'non-int' or 'non-money' values coming over from
> the remote server, will SQL Server automatically convert these 'invalid'
> values to NULL, or do I need to handle it somehow, maybe via a CASE
> expression in the UPDATE or INSERT INTO, or...?
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||That's the same conclusion I came to. Even though IsNumeric may not be
'ideal' it seems to serve the purpose here well.
Thanks to all for the input.
Message posted via http://www.webservertalk.com

different behaviour of SQL Server 2000 errors dependent on windows OS

I am having a problem inserting data into a database table. There is a
stored procedure that is attempting to insert data into several tables, if
there is a duplicate entry already exists in the table then a unique key
violation is thrown back from sql server (2000) to the data access layer.
The data access layer then processes the sql exceptions and check the error
codes and the number of sql errors that occurred. If the error codes and
number of exceptions are the expected number and expected type then the
exception is surpressed and processing continues, BUT if the number of
exceptions or the number of the sql error is different then the exception is
propagated up the stack. (I didn't design this )
Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
Production\Test environment is Windows 2003 (Standard Edition) - .Net
Framework 1.1
both machines are accessing the same physical database.
So in development we get 4 exceptions back with the expected error codes and
in production\test we get back 1 error. Can anyone explain why?
Is there a setting somewhere in the registry to affect how sql server errors
are processed by the native database access driver on the client machine?
Am I loosing the plot? - YES
As far as I can see this is NOT an .Net framework issue but an issue with
error propagatation\processing with the server installation of sql server
2000
Cheers
Ollie Riches
http://www.phoneanalyser.net
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a programmer
helping programmers.
FYI
When the stored procedure calls RAISERROR the severity level is 16.
Ollie
"Ollie Riches" <ollie.riches@.phoneanalser.net> wrote in message
news:uQb9AumHFHA.2976@.TK2MSFTNGP15.phx.gbl...
> I am having a problem inserting data into a database table. There is a
> stored procedure that is attempting to insert data into several tables, if
> there is a duplicate entry already exists in the table then a unique key
> violation is thrown back from sql server (2000) to the data access layer.
> The data access layer then processes the sql exceptions and check the
error
> codes and the number of sql errors that occurred. If the error codes and
> number of exceptions are the expected number and expected type then the
> exception is surpressed and processing continues, BUT if the number of
> exceptions or the number of the sql error is different then the exception
is
> propagated up the stack. (I didn't design this )
> Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
> Production\Test environment is Windows 2003 (Standard Edition) - .Net
> Framework 1.1
> both machines are accessing the same physical database.
> So in development we get 4 exceptions back with the expected error codes
and
> in production\test we get back 1 error. Can anyone explain why?
> Is there a setting somewhere in the registry to affect how sql server
errors
> are processed by the native database access driver on the client machine?
> Am I loosing the plot? - YES
> As far as I can see this is NOT an .Net framework issue but an issue with
> error propagatation\processing with the server installation of sql server
> 2000
> Cheers
> Ollie Riches
> http://www.phoneanalyser.net
> Disclaimer: Opinions expressed in this forum are my own, and not
> representative of my employer.
> I do not answer questions on behalf of my employer. I'm just a programmer
> helping programmers.
>
>
>

different behaviour of SQL Server 2000 errors dependent on windows OS

I am having a problem inserting data into a database table. There is a
stored procedure that is attempting to insert data into several tables, if
there is a duplicate entry already exists in the table then a unique key
violation is thrown back from sql server (2000) to the data access layer.
The data access layer then processes the sql exceptions and check the error
codes and the number of sql errors that occurred. If the error codes and
number of exceptions are the expected number and expected type then the
exception is surpressed and processing continues, BUT if the number of
exceptions or the number of the sql error is different then the exception is
propagated up the stack. (I didn't design this )
Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
Production\Test environment is Windows 2003 (Standard Edition) - .Net
Framework 1.1
both machines are accessing the same physical database.
So in development we get 4 exceptions back with the expected error codes and
in production\test we get back 1 error. Can anyone explain why?
Is there a setting somewhere in the registry to affect how sql server errors
are processed by the native database access driver on the client machine?
Am I loosing the plot? - YES
As far as I can see this is NOT an .Net framework issue but an issue with
error propagatation\processing with the server installation of sql server
2000
Cheers
Ollie Riches
http://www.phoneanalyser.net
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a programmer
helping programmers.
FYI
When the stored procedure calls RAISERROR the severity level is 16.
Ollie
"Ollie Riches" <ollie.riches@.phoneanalser.net> wrote in message
news:uQb9AumHFHA.2976@.TK2MSFTNGP15.phx.gbl...
> I am having a problem inserting data into a database table. There is a
> stored procedure that is attempting to insert data into several tables, if
> there is a duplicate entry already exists in the table then a unique key
> violation is thrown back from sql server (2000) to the data access layer.
> The data access layer then processes the sql exceptions and check the
error
> codes and the number of sql errors that occurred. If the error codes and
> number of exceptions are the expected number and expected type then the
> exception is surpressed and processing continues, BUT if the number of
> exceptions or the number of the sql error is different then the exception
is
> propagated up the stack. (I didn't design this )
> Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
> Production\Test environment is Windows 2003 (Standard Edition) - .Net
> Framework 1.1
> both machines are accessing the same physical database.
> So in development we get 4 exceptions back with the expected error codes
and
> in production\test we get back 1 error. Can anyone explain why?
> Is there a setting somewhere in the registry to affect how sql server
errors
> are processed by the native database access driver on the client machine?
> Am I loosing the plot? - YES
> As far as I can see this is NOT an .Net framework issue but an issue with
> error propagatation\processing with the server installation of sql server
> 2000
> Cheers
> Ollie Riches
> http://www.phoneanalyser.net
> Disclaimer: Opinions expressed in this forum are my own, and not
> representative of my employer.
> I do not answer questions on behalf of my employer. I'm just a programmer
> helping programmers.
>
>
>

different behaviour of SQL Server 2000 errors dependent on windows OS

I am having a problem inserting data into a database table. There is a
stored procedure that is attempting to insert data into several tables, if
there is a duplicate entry already exists in the table then a unique key
violation is thrown back from sql server (2000) to the data access layer.
The data access layer then processes the sql exceptions and check the error
codes and the number of sql errors that occurred. If the error codes and
number of exceptions are the expected number and expected type then the
exception is surpressed and processing continues, BUT if the number of
exceptions or the number of the sql error is different then the exception is
propagated up the stack. (I didn't design this :))
Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
Production\Test environment is Windows 2003 (Standard Edition) - .Net
Framework 1.1
both machines are accessing the same physical database.
So in development we get 4 exceptions back with the expected error codes and
in production\test we get back 1 error. Can anyone explain why?
Is there a setting somewhere in the registry to affect how sql server errors
are processed by the native database access driver on the client machine?
Am I loosing the plot? - YES
As far as I can see this is NOT an .Net framework issue but an issue with
error propagatation\processing with the server installation of sql server
2000
Cheers
Ollie Riches
http://www.phoneanalyser.net
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a programmer
helping programmers.FYI
When the stored procedure calls RAISERROR the severity level is 16.
Ollie
"Ollie Riches" <ollie.riches@.phoneanalser.net> wrote in message
news:uQb9AumHFHA.2976@.TK2MSFTNGP15.phx.gbl...
> I am having a problem inserting data into a database table. There is a
> stored procedure that is attempting to insert data into several tables, if
> there is a duplicate entry already exists in the table then a unique key
> violation is thrown back from sql server (2000) to the data access layer.
> The data access layer then processes the sql exceptions and check the
error
> codes and the number of sql errors that occurred. If the error codes and
> number of exceptions are the expected number and expected type then the
> exception is surpressed and processing continues, BUT if the number of
> exceptions or the number of the sql error is different then the exception
is
> propagated up the stack. (I didn't design this :))
> Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
> Production\Test environment is Windows 2003 (Standard Edition) - .Net
> Framework 1.1
> both machines are accessing the same physical database.
> So in development we get 4 exceptions back with the expected error codes
and
> in production\test we get back 1 error. Can anyone explain why?
> Is there a setting somewhere in the registry to affect how sql server
errors
> are processed by the native database access driver on the client machine?
> Am I loosing the plot? - YES
> As far as I can see this is NOT an .Net framework issue but an issue with
> error propagatation\processing with the server installation of sql server
> 2000
> Cheers
> Ollie Riches
> http://www.phoneanalyser.net
> Disclaimer: Opinions expressed in this forum are my own, and not
> representative of my employer.
> I do not answer questions on behalf of my employer. I'm just a programmer
> helping programmers.
>
>
>

different behaviour of SQL Server 2000 errors dependent on windows OS

I am having a problem inserting data into a database table. There is a
stored procedure that is attempting to insert data into several tables, if
there is a duplicate entry already exists in the table then a unique key
violation is thrown back from sql server (2000) to the data access layer.
The data access layer then processes the sql exceptions and check the error
codes and the number of sql errors that occurred. If the error codes and
number of exceptions are the expected number and expected type then the
exception is surpressed and processing continues, BUT if the number of
exceptions or the number of the sql error is different then the exception is
propagated up the stack. (I didn't design this )
Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
Production\Test environment is Windows 2003 (Standard Edition) - .Net
Framework 1.1
both machines are accessing the same physical database.
So in development we get 4 exceptions back with the expected error codes and
in production\test we get back 1 error. Can anyone explain why?
Is there a setting somewhere in the registry to affect how sql server errors
are processed by the native database access driver on the client machine?
Am I loosing the plot? - YES
As far as I can see this is NOT an .Net framework issue but an issue with
error propagatation\processing with the server installation of sql server
2000
Cheers
Ollie Riches
http://www.phoneanalyser.net
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a programmer
helping programmers.FYI
When the stored procedure calls RAISERROR the severity level is 16.
Ollie
"Ollie Riches" <ollie.riches@.phoneanalser.net> wrote in message
news:uQb9AumHFHA.2976@.TK2MSFTNGP15.phx.gbl...
> I am having a problem inserting data into a database table. There is a
> stored procedure that is attempting to insert data into several tables, if
> there is a duplicate entry already exists in the table then a unique key
> violation is thrown back from sql server (2000) to the data access layer.
> The data access layer then processes the sql exceptions and check the
error
> codes and the number of sql errors that occurred. If the error codes and
> number of exceptions are the expected number and expected type then the
> exception is surpressed and processing continues, BUT if the number of
> exceptions or the number of the sql error is different then the exception
is
> propagated up the stack. (I didn't design this )
> Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
> Production\Test environment is Windows 2003 (Standard Edition) - .Net
> Framework 1.1
> both machines are accessing the same physical database.
> So in development we get 4 exceptions back with the expected error codes
and
> in production\test we get back 1 error. Can anyone explain why?
> Is there a setting somewhere in the registry to affect how sql server
errors
> are processed by the native database access driver on the client machine?
> Am I loosing the plot? - YES
> As far as I can see this is NOT an .Net framework issue but an issue with
> error propagatation\processing with the server installation of sql server
> 2000
> Cheers
> Ollie Riches
> http://www.phoneanalyser.net
> Disclaimer: Opinions expressed in this forum are my own, and not
> representative of my employer.
> I do not answer questions on behalf of my employer. I'm just a programmer
> helping programmers.
>
>
>

different behaviour of SQL Server 2000 errors dependent on windows OS

I am having a problem inserting data into a database table. There is a
stored procedure that is attempting to insert data into several tables, if
there is a duplicate entry already exists in the table then a unique key
violation is thrown back from sql server (2000) to the data access layer.
The data access layer then processes the sql exceptions and check the error
codes and the number of sql errors that occurred. If the error codes and
number of exceptions are the expected number and expected type then the
exception is surpressed and processing continues, BUT if the number of
exceptions or the number of the sql error is different then the exception is
propagated up the stack. (I didn't design this :))
Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
Production\Test environment is Windows 2003 (Standard Edition) - .Net
Framework 1.1
both machines are accessing the same physical database.
So in development we get 4 exceptions back with the expected error codes and
in production\test we get back 1 error. Can anyone explain why?
Is there a setting somewhere in the registry to affect how sql server errors
are processed by the native database access driver on the client machine?
Am I loosing the plot? - YES
As far as I can see this is NOT an .Net framework issue but an issue with
error propagatation\processing with the server installation of sql server
2000
Cheers
Ollie Riches
http://www.phoneanalyser.net
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a programmer
helping programmers.FYI
When the stored procedure calls RAISERROR the severity level is 16.
Ollie
"Ollie Riches" <ollie.riches@.phoneanalser.net> wrote in message
news:uQb9AumHFHA.2976@.TK2MSFTNGP15.phx.gbl...
> I am having a problem inserting data into a database table. There is a
> stored procedure that is attempting to insert data into several tables, if
> there is a duplicate entry already exists in the table then a unique key
> violation is thrown back from sql server (2000) to the data access layer.
> The data access layer then processes the sql exceptions and check the
error
> codes and the number of sql errors that occurred. If the error codes and
> number of exceptions are the expected number and expected type then the
> exception is surpressed and processing continues, BUT if the number of
> exceptions or the number of the sql error is different then the exception
is
> propagated up the stack. (I didn't design this :))
> Development environment is XP Pro (2002) SP1 - .Net Framework 1.1
> Production\Test environment is Windows 2003 (Standard Edition) - .Net
> Framework 1.1
> both machines are accessing the same physical database.
> So in development we get 4 exceptions back with the expected error codes
and
> in production\test we get back 1 error. Can anyone explain why?
> Is there a setting somewhere in the registry to affect how sql server
errors
> are processed by the native database access driver on the client machine?
> Am I loosing the plot? - YES
> As far as I can see this is NOT an .Net framework issue but an issue with
> error propagatation\processing with the server installation of sql server
> 2000
> Cheers
> Ollie Riches
> http://www.phoneanalyser.net
> Disclaimer: Opinions expressed in this forum are my own, and not
> representative of my employer.
> I do not answer questions on behalf of my employer. I'm just a programmer
> helping programmers.
>
>
>

Differences between 6.5 and 2000 inserts

Hi Guru's,

I am kind of baffeled. I have a table with a column of 8 varchar in 2000
and the same in 6.5. When I insert into 2000 with a data length of more than 8 chars via Cold Fusion into the table, it fails. The same Cold Fusion
program inserts into the 6.5 table, but truncates the data but does not fail.
Does anyone know why this happens. Thanks, Newbie.This is the expected behavior. sql65 should have enforced this but it didn't. If you design your data to be a certain length then the expected data should be within that limit.

Btw,this is really a good way to *encourage* developers to design the db right. Why would you want to declare a column of 8 char but expect data longer than 8.