Can we insert NULL in place of foreign key.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now I
tried out turning "Allow Null" on for the column "DepartmentID" in the
"Student" table. As soon as I did this, I was able to insert a row with
"DepartmentID" as <NULL>.
Now isn't this wrong? Or is it that the database overrides the checking of
the foreign key against the table "Department" when I check the "Allow Null"
option ON?
It would be really helpful if someone could elaborate on this.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriHi
Yes, referencing table may accept NULL's. Like people that don't have
children.
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:C5A2D39D-5C28-4661-B68B-B2CA6BA48CDF@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow
> Null"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Aayush Puri wrote:
> I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow Nul
l"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
SQL's foreign key constraints work like check constraints.
Specifically, values are permitted as long as the constraint isn't
violated. A null comparison returns an UNKNOWN result rather than a
FALSE one and UNKNOWN isn't regarded as a violation of the constraint.
So the behaviour you mentioned is "right" according to the SQL
standard. Whether it makes good logical sense is a different matter.
I'd suggest that you either disallow nulls or that you create some
default value in the Depatrtments table to represent the case that you
would otherwise have used a null for - a "Not Applicable" department
for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Can SSIS parse this text report without a lot of programming?

I've got some machines that output text files after each shot of parts. I'd like to take the data in those files and parse it and insert it into a SQL Server database for future massaging. The text files look like the example I've posted below. Can SSIS parse out the set points and actual values even though the file isn't CSV or tab delimited and the data is kind of 'strewn' all over the report? Each report does have the exact same format so the report format doesn't change from report to report, just the data. Thanks in advance.

Ernie

WP4.57 C Y C L E P R O T O C O L

Order data 18.05.06 11:27:57

Order number : 2006 Recipe no. : 15761

Machine number : 7 Recipe name : Stabilizer Bar Innsulator

Machine Operator: 1257 Art.descrip.: Stabilizer Bar Grommet

Shot Volume : 285.8

Part quantity : 100096 Type of mat.: M370-34

Shot quantity : 782 Batch number: 20124-125

-

Temperatures in ?C

Set Act Set Act

Fixed heat.platen right 182 182 Tempering screw 83 83

middle 180 180 Tempering inject.cylinder 85 85

left 182 182 Tempering circuit 3 90 91

Tempering circuit 4 0 39

Movab.heat.platen right 182 182 Tempering circuit 5 0 39

middle 180 180

left 182 182 Mould temperature 1 0 39

Mould temperature 2 0 39

Third heat.platen right 0 39 Mould temperature 3 0 39

middle 0 39 Mould temperature 4 0 39

left 0 39 Mould temperature 5 0 39

Mould heating circuit 6 0 39 Compound temp.after screw 104 104

Mould heating circuit 7 0 39 Compound temp.after nozzle 0 39

Mould heating circuit 8 0 39

Mould heating circuit 9 0 39

Mould heating circuit 10 0 39

Times in sec

Injection time 51.20 Transfer time 1 2.00

Internal mould press.time 0.00 Transfer time 2 2.00

Dwell pressure time 7.00 Transfer time 3 2.00

Controlled cure time 180.00 Transfer time 4 2.00

Calculated cure time 0.00 Transfer time 5 2.00

last cycle time 276

last opening time 25

Measure when injecting Measure at injection end

max. injection speed mm/s 11.9 Injection length mm 2.0

Injection energy kNm 247.2 Injection time sec 51.20

max. int.mould pres. bar 2 Hydraulic pressure bar 190

max. dwell pressure bar 192 Internal mould pressure bar 0

Pad mm 0.4

Stock Temperatures and Pressures During Metering

Stock Temperatures(C) Set Actual Metering Pressures(bar) Set Actual

Temperature 1st Step 105 106 Pressure 1st Step 135 131

Temperature 2nd Step 105 106 Pressure 2nd Step 135 129

Temperature 3rd Step 105 105 Pressure 3rd Step 135 122

Temperature 4th Step 105 106 Pressure 4th Step 135 135

Temperature 5th Step 105 109 Pressure 5th Step 135 137

Protocol Complete

Yes absolutely, SSIS can do this. Import it as a single, very wide, column and parse out the various sections in the pipeline. Given the complexity you're probably going to have to do this in an aysnchronous script component.

-Jamie

Can SQLPutData be used against varchar(max)?

Hi,
I am trying to insert data into varchar(max) via ODBC.
When I try to insert data. I am getting following error. Is SQLPutData
supported for varchar(max)?
1394-1e4c ENTER SQLPutData
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)
DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The text, ntext,
or image pointer value conflicts with the column name specified. (7125)
Thank you,
KM
I realized that I was binding the parameter with SQL_LONGVARCHAR.
It worked when I used SQL_VARCHAR.
Can't we use SQL_LONGVARCHAR for binding varchar(max)? Aren't they
completely compatible?
Thank you,
KM
"KM" wrote:

> Hi,
> I am trying to insert data into varchar(max) via ODBC.
> When I try to insert data. I am getting following error. Is SQLPutData
> supported for varchar(max)?
> 1394-1e4c ENTER SQLPutData
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> 1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
> insert/update. The insert/update of a text or image column(s) did not
> succeed. (0)
> DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The text, ntext,
> or image pointer value conflicts with the column name specified. (7125)
> Thank you,
> KM

Can SQLPutData be used against varchar(max)?

Hi,
I am trying to insert data into varchar(max) via ODBC.
When I try to insert data. I am getting following error. Is SQLPutData
supported for varchar(max)?
1394-1e4c ENTER SQLPutData
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
HSTMT 36665B40
PTR 0x37CDFF80
SQLLEN 20
DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
insert/update. The insert/update of a text or image column(s) did not
succeed. (0)
DIAG [42000] [Microsoft][SQL Native Client][SQL Server]The t
ext, ntext,
or image pointer value conflicts with the column name specified. (7125)
Thank you,
KMI realized that I was binding the parameter with SQL_LONGVARCHAR.
It worked when I used SQL_VARCHAR.
Can't we use SQL_LONGVARCHAR for binding varchar(max)? Aren't they
completely compatible?
Thank you,
KM
"KM" wrote:

> Hi,
> I am trying to insert data into varchar(max) via ODBC.
> When I try to insert data. I am getting following error. Is SQLPutData
> supported for varchar(max)?
> 1394-1e4c ENTER SQLPutData
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> 1394-1e4c EXIT SQLPutData with return code -1 (SQL_ERROR)
> HSTMT 36665B40
> PTR 0x37CDFF80
> SQLLEN 20
> DIAG [HY000] [Microsoft][SQL Native Client]Warning: Partial
> insert/update. The insert/update of a text or image column(s) did not
> succeed. (0)
> DIAG [42000] [Microsoft][SQL Native Client][SQL Server]
The text, ntext,
> or image pointer value conflicts with the column name specified. (7125)
> Thank you,
> KM

can someone tell me what I am doing wrong

HI all,
I have this trigger on a table
**********
CREATE TRIGGER addtotal ON dbo.ClaimFinancialLoss
AFTER INSERT, UPDATE, DELETE
AS
if update(Ammount)
begin
Declare @.tot int, @.id int
select @.id = ClaimID from inserted
--Print 'Id after insert' +str(@.id)
if exists (select * from deleted)
Begin
select @.id = ClaimID from deleted
--Print 'Id after deleted' +str(@.id)
end
Select @.tot=sum(Ammount) from ClaimFinancialLoss group by ClaimID Having
ClaimID = @.id
update claim set total = @.tot where ClaimID = @.id
end
**************
This updates the claim table. On the claim table I have these two triggers
(they are kept seperate just for simplicity at the moment)
****************
CREATE TRIGGER dateupdated ON dbo.Claim
AFTER INSERT
AS
declare @.id int
select @.id=claimid from inserted
update claim set updated = getdate() where claimid = @.id
*************
CREATE TRIGGER layerchange ON [dbo].[Claim]
FOR INSERT, UPDATE AS
if update(layerid)
select layerid as ins from inserted
select layerid as del from deleted
declare @.id int
select @.id=claimid from inserted
begin
delete from ClaimDeductables where claimid = @.id
insert into claimdeductables SELECT PolLayer.LayerCap,
PolLayer.PayOrder,claim.claimid, Layer.LayerID
FROM Claim INNER JOIN
CP_Covertype ON Claim.LayerID = CP_Covertype.CPCKey
INNER JOIN
Covertype ON CP_Covertype.CTKey = Covertype.CTKey
INNER JOIN
PolLayer ON Covertype.CTKey = PolLayer.CTKey INNER
JOIN
Layer ON PolLayer.layerid = Layer.LayerID
WHERE (Claim.ClaimID = @.id)
raiserror('You have made changes to the layers of this claim',9,1,1)
end
*****************
THe raiserror always fires, when ever I update Claimfinancialloss.ammount.
To me this error should not be raised As I have a if update(Layerid), and I
am not updating the layerid of the claim, only the total (through the
trigger.)
Is having two update triggers on the same table, one of them updating the
claimtable again, causing the layerid column to simulate a be updated.
Thanks
Robertyou need a begin after if update(layerid) because if without begin
looks only at the first statement after the if
example
declare @.x int
select @.x =4
if @.x <> 4
print 'yes'
print'blah'
you see, blah will alway be printed
declare @.x int
select @.x =4
if @.x <> 4
begin
print 'yes'
print'blah'
end
If you use begin and end this won't happen since the if will skip that
whole statement
http://sqlservercode.blogspot.com/|||Without investigating this further, you missed that a trigger is fired
per STATEMENT NOT per ROW, so you better should investigate eliminating
this misdesign.
HTH, jens Suessmeyer.|||HI,
YEp, you hit the nail on the head.
Stupid me, I did have a begin statement after the iff statement, But then
during testing I inserted two select statements after the if statement,
naturally the first select statement was executed as a direct result of the
if statment then the other statments executed as a matter of course.
Thanks
Robert
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140101176.000869.264260@.g43g2000cwa.googlegroups.com...
> you need a begin after if update(layerid) because if without begin
> looks only at the first statement after the if
> example
> declare @.x int
> select @.x =4
> if @.x <> 4
> print 'yes'
> print'blah'
> you see, blah will alway be printed
>
> declare @.x int
> select @.x =4
> if @.x <> 4
> begin
> print 'yes'
> print'blah'
> end
> If you use begin and end this won't happen since the if will skip that
> whole statement
> http://sqlservercode.blogspot.com/
>|||HI Jens,
Yes I realise that, as I said this is a tempory measure, testing. But
anyway, I was pointed to the fact that I never had my begin statement in the
right place by another post.
Thanks anyway for you valued input
Robert
<Jens.Suessmeyer@.googlemail.com> wrote in message
news:1140101824.576386.320190@.g14g2000cwa.googlegroups.com...
> Without investigating this further, you missed that a trigger is fired
> per STATEMENT NOT per ROW, so you better should investigate eliminating
> this misdesign.
> HTH, jens Suessmeyer.
>

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
Eddie
It could be blocking problems, or that the operations takes so long time because lots of data and lack of
indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that I
> can retrieve the records from every table in the database (by "select"), but
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to get
> rid of this problem?
> Thanks.
> Eddie
>
|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
that I[vbcol=seagreen]
but[vbcol=seagreen]
and[vbcol=seagreen]
get
>
|||To check if you are suffering from blocks run a start a Trace in SQL Profiler before you try and run one of the qeries that times out, make sure that you select all of the locks events, you add these to the trace in the Trace properties events tab.
I addition check that the queries you are running actually reference the indexes you have created - are the inserts or updates particulary complicated statements?
Ed
|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
EddieIt could be blocking problems, or that the operations takes so long time because lots of data and lack of
indexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that I
> can retrieve the records from every table in the database (by "select"), but
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to get
> rid of this problem?
> Thanks.
> Eddie
>|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
> > I have a problem in the SQL 2000 Server. In one my database, I found
that I
> > can retrieve the records from every table in the database (by "select"),
but
> > when I wanted to update or insert the record in each table, It failed
and
> > return error "timeout expired". What is the problem? What should I do to
get
> > rid of this problem?
> >
> > Thanks.
> >
> > Eddie
> >
> >
>|||To check if you are suffering from blocks run a start a Trace in SQL Profiler before you try and run one of the qeries that times out, make sure that you select all of the locks events, you add these to the trace in the Trace properties events tab.
I addition check that the queries you are running actually reference the indexes you have created - are the inserts or updates particulary complicated statements
Ed|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can Select but Can't Update or Insert in Tables

I have a problem in the SQL 2000 Server. In one my database, I found that I
can retrieve the records from every table in the database (by "select"), but
when I wanted to update or insert the record in each table, It failed and
return error "timeout expired". What is the problem? What should I do to get
rid of this problem?
Thanks.
EddieIt could be blocking problems, or that the operations takes so long time bec
ause lots of data and lack of
indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Eddie Leung" <eddielg@.image.com.hk> wrote in message news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx
.gbl...
> I have a problem in the SQL 2000 Server. In one my database, I found that
I
> can retrieve the records from every table in the database (by "select"), b
ut
> when I wanted to update or insert the record in each table, It failed and
> return error "timeout expired". What is the problem? What should I do to g
et
> rid of this problem?
> Thanks.
> Eddie
>|||In my database, the size is only 4GB and we have already built the indexes
in the tables. We also process all our application without applying
transaction. What is the possible way to block the database? As I know, it
can almost block on table level, when does it exist to block on whole
database? As it happens in the first time, we want to prevent it from the
same potential again. Would you mind if you may advise and suggest?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OICXFEHLEHA.556@.TK2MSFTNGP10.phx.gbl...
> It could be blocking problems, or that the operations takes so long time
because lots of data and lack of
> indexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Eddie Leung" <eddielg@.image.com.hk> wrote in message
news:eEHu9CHLEHA.3664@.TK2MSFTNGP10.phx.gbl...
that I[vbcol=seagreen]
but[vbcol=seagreen]
and[vbcol=seagreen]
get[vbcol=seagreen]
>|||To check if you are suffering from blocks run a start a Trace in SQL Profile
r before you try and run one of the qeries that times out, make sure that yo
u select all of the locks events, you add these to the trace in the Trace pr
operties events tab.
I addition check that the queries you are running actually reference the ind
exes you have created - are the inserts or updates particulary complicated s
tatements?
Ed|||I am also getting this problem.
I don't know if the original poster held images within his SQL table, but I
am and it appears to be the cause of the problem.
If I remove the image columns then the simple update query that I am trying
to run works fine.
There do not appear to be any locks on this table.
"Eddy" <anonymous@.discussions.microsoft.com> wrote in message
news:68BFB7E7-158C-49DD-A932-1E6648378272@.microsoft.com...
> To check if you are suffering from blocks run a start a Trace in SQL
Profiler before you try and run one of the qeries that times out, make sure
that you select all of the locks events, you add these to the trace in the
Trace properties events tab.
> I addition check that the queries you are running actually reference the
indexes you have created - are the inserts or updates particulary
complicated statements?
> Ed

Can Query Designer Handle Subqueries`

When a subquery is part of an insert, update or just a from or where clause, it doesn't seem to have a way to structure it. Is there a procedure for that?

Thanks,

DavidHi David -
The only structuring functionality for your scenario is to use the block indent/unident function. You can find these menu items/shortcuts under the Edit menu.

Michael Raheem
Program Manager
SQL Server Tools Team|||Hi David,
One other alternative starting with the APril CTP is to use the query designer within the Query Editor to design each sub-query by selecting the text to design and then issuing the Design Query in Editor command.
Thank you,
Bill Ramos

Can OPENXML insert rows with some duplicate rows?

Hi All,
I'd like to insert rows that maybe have some duplicate rows with OPENXML
from stored procedure. In normal case, I've found that it cannot do that.
Is there anybody know how to accomplish this?
Thanks in advance,
Thana N.
I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
wondor about the performance of it. What is the best way for performance?
Thana N.
"Thana N." wrote:

> Hi All,
> I'd like to insert rows that maybe have some duplicate rows with OPENXML
> from stored procedure. In normal case, I've found that it cannot do that.
> Is there anybody know how to accomplish this?
> Thanks in advance,
> Thana N.
>
|||In this case you will have to decide how to deal with the duplicates.
This depends a lot on your data. If you were just inserting account
numbers then you could do:
insert into x
select acct#
from openxml(...)
group by acct#
In some cases you may want to sum the data (using sum), get the max, or
get the min. It will depend on your data.
insert into x
select acct#, sum(charges), min(timeleft), max(lastcall)
from openxml(...)
group by acct#
|||If your query would execute OpenXML more than once with the same resulting
rowset or it is large enough to benefit from an index, you may want to
insert the data from OpenXML into a temp table (and define an index if it
seems more performing).
HTH
Michael
"Thana N." <ThanaN@.discussions.microsoft.com> wrote in message
news:417C9E34-FF19-4EDB-B57C-345D36770535@.microsoft.com...[vbcol=seagreen]
> I've just found that I can use "NOT IN" or "NOT EXISTS" to do it. But I
> wondor about the performance of it. What is the best way for performance?
> Thana N.
> "Thana N." wrote:

Can not Update/Insert big5 characters in to sql server 2000

I have current current sql server 2000 database containing some columns in big5. To display these cols correctly, my asp.net nust have directive with CodePage="1252" ContentType="text/html;charset=BIG5". I can not update, or insert big5 character into these columns via .aspx page. I'm using .net framework 2.0.

Please help me, thanks a lot for any help.

The quick question is why are you using Latin code page to save data going into a Chinese alphabet database column?|||

I really don't want to use that method, but it's a legacy database used with asp. We are migrating to asp.net while the asp version's still running. So, I can not change it. But your question may give me some ideas, thank you very much.

By the way, I've try the solution in http://forums.asp.net/518209/ShowPost.aspx, It seem to be ok. But there're some words becoming '?' after updated into database.

Any hints for me

|||

I have read that thread but not everything the person said is correct so here is what you to avoid character conversion, in VS2005 the advanced option let you save your code with code pages any langauge, and you can also do encoding of the page when you save it. These will help you with the application layer but also make sure you use column level collation in the database because the Latin alphabet is 26 characters, the Chinese is more than 2000 characters, they cannot be passed arround as you want. The links below will help you. Hope this helps.

https://www.microsoft.co.ke/middleeast/msdn/arabicsupp.aspx#7

http://www.developerland.com/DotNet/General/99.aspx

|||

ThankCaddre very much,

I'll read them. My important problem is: I cannot column change the 'level collation in the database' as you said because it's a legacy database. Actually, I don't know much 'bout it. I'll check with my DBA.

regards,

|||If your database is in SQL Server 7.0 youmust migrate it or you cannot store Chinese in it correctly. Hope this helps.|||

thanks a lot,

I'm using sql server 2000, and I'm trying to use UTF-8 charset only

|||

You cannot use UTF-8 in SQL Server because SQL Server uses UC-S 2 a version of UTF-16 but here is a thread I helped someone do Chinese collation in SQL Server 2000. But you can do encoding in the application layer in UTF-8. Hope this helps.

http://forums.asp.net/1067798/ShowPost.aspx

can not send an email using databasemail from asp.net page in a trigger

Hello,

I want to send an email from a trigger. I have configured all, and if I insert something into the table directly in the db the email is sent.

I give access to the asp.net account to msdb and put it into DatabaseMailUserRole group, but it doesnt work when I launch the trigger.

Any ideas?

What is the error you are getting?

Did you try inserting the row from Query window to check db mail working?

Check mail log for more info...

|||that is what i do not understand. It do not give me any error. If I insert from query window it works, but from asp.net fails. I think it is something related to the asp.net account, but i am using atlas and i can not see the error message. Nothing to see in the log, except a transacction exception, but I do not see that always, sometimes it writes it sometimes not.