Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-MYou need to keep your aliasing straight. You were mixing a "p" alias and the
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or the
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility = (SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
> You need to keep your aliasing straight. You were mixing a "p" alias and the
> base table name without ever defining it. Also, when a correlated sub-query
> does not return any results, its value is NULL. Your updatable column does
> not allow NULLs; so, you have to code for that condition either by excluding
> those updates--which I coded--or using something like a CASE statement or the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility => (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
> > Can someone help me with this update, I have exhausted
> > all my effort into resolving this -
> >
> > update pfile set facility = (select max(a.facility_num)
> > from efile a
> > where p.Order_num = a.Order_num and
> > pfile.facility <> a.facility_num and
> > a.facility_num IS NOT NULL and
> > pfile.facility like '9999%'
> > and a.facility_num not like 'N/A%' group by a.Order_num)
> >
> >
> > I checked the corresponding select statement works
> >
> > select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> > b.facility
> > from efile a, pfile b
> > where a.Order_num = b.Order_num and a.facility_num <> b.facility
> > and b.facility like '9999%'
> > and a.facility_num not like 'N/A%' group by a.Order_num
> >
> > This works with group by clause too.
> >
> > When I run update statement, I get
> > "Cannot insert null values into column facility "
> >
> > However, I checked there is no null value in the select
> > statement or existing data. Then I changed the column
> > to allow nulls, it put null value for all records.
> >
> > I also tried the statement without max(facility_num)
> > as there isn't more than one record as of now.
> > Still I get "Cannot insert null value " error, any ideas
> > how to resolve this?
> >
> > I am stuck, I have to meeet deadline.
> >
> > Thanks for your help!
> > -M
> >
> >
> >
> >|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>sql

Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-M
You need to keep your aliasing straight. You were mixing a "p" alias and the
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or the
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility =
(SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:

> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>
|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
[vbcol=seagreen]
> You need to keep your aliasing straight. You were mixing a "p" alias and the
> base table name without ever defining it. Also, when a correlated sub-query
> does not return any results, its value is NULL. Your updatable column does
> not allow NULLs; so, you have to code for that condition either by excluding
> those updates--which I coded--or using something like a CASE statement or the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility =
> (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>

Having problems with update statement

Can someone help me with this update, I have exhausted
all my effort into resolving this -
update pfile set facility = (select max(a.facility_num)
from efile a
where p.Order_num = a.Order_num and
pfile.facility <> a.facility_num and
a.facility_num IS NOT NULL and
pfile.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num)
I checked the corresponding select statement works
select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
b.facility
from efile a, pfile b
where a.Order_num = b.Order_num and a.facility_num <> b.facility
and b.facility like '9999%'
and a.facility_num not like 'N/A%' group by a.Order_num
This works with group by clause too.
When I run update statement, I get
"Cannot insert null values into column facility "
However, I checked there is no null value in the select
statement or existing data. Then I changed the column
to allow nulls, it put null value for all records.
I also tried the statement without max(facility_num)
as there isn't more than one record as of now.
Still I get "Cannot insert null value " error, any ideas
how to resolve this?
I am stuck, I have to meeet deadline.
Thanks for your help!
-MYou need to keep your aliasing straight. You were mixing a "p" alias and th
e
base table name without ever defining it. Also, when a correlated sub-query
does not return any results, its value is NULL. Your updatable column does
not allow NULLs; so, you have to code for that condition either by excluding
those updates--which I coded--or using something like a CASE statement or th
e
ISNULL function to provide a different value when NULL appears.
Here is my alternative:
UPDATE p
SET facility =
(SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
FROM pfile AS p
WHERE p.facility LIKE '9999%'
AND EXISTS(
SELECT MAX(e.facility_num)
FROM efile AS e
WHERE e.Order_num = p.Order_num
AND e.facility_num <> p.facility
AND e.facility_num IS NOT NULL
AND e.facility_num NOT LIKE 'N/A%'
)
Now, if you give it some thought, you should be able to upgrade this from a
correlated sub-query expression to a direct update using multiple table
joins. This would be preferrable because the statement above will be
sloooooooow.
Good luck.
Sincerely,
Anthony Thomas
"Me" wrote:

> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>|||Anthony,
Thanks for the reply!
Still it didn't work, but I found a work around.
Appreciate your help!
-M
"AnthonyThomas" wrote:
[vbcol=seagreen]
> You need to keep your aliasing straight. You were mixing a "p" alias and
the
> base table name without ever defining it. Also, when a correlated sub-que
ry
> does not return any results, its value is NULL. Your updatable column doe
s
> not allow NULLs; so, you have to code for that condition either by excludi
ng
> those updates--which I coded--or using something like a CASE statement or
the
> ISNULL function to provide a different value when NULL appears.
> Here is my alternative:
> UPDATE p
> SET facility =
> (SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> FROM pfile AS p
> WHERE p.facility LIKE '9999%'
> AND EXISTS(
> SELECT MAX(e.facility_num)
> FROM efile AS e
> WHERE e.Order_num = p.Order_num
> AND e.facility_num <> p.facility
> AND e.facility_num IS NOT NULL
> AND e.facility_num NOT LIKE 'N/A%'
> )
> Now, if you give it some thought, you should be able to upgrade this from
a
> correlated sub-query expression to a direct update using multiple table
> joins. This would be preferrable because the statement above will be
> sloooooooow.
> Good luck.
> Sincerely,
>
> Anthony Thomas
>
> "Me" wrote:
>|||Um, you forgot your WHERE clause on your update statement. As written, it
would try to update every row in pfile.
And you don't have a table corresponding to your p alias.
Jeff
"Me" <Me@.discussions.microsoft.com> wrote in message
news:143D3A21-3743-4F66-8B2E-91764A750E62@.microsoft.com...
> Can someone help me with this update, I have exhausted
> all my effort into resolving this -
> update pfile set facility = (select max(a.facility_num)
> from efile a
> where p.Order_num = a.Order_num and
> pfile.facility <> a.facility_num and
> a.facility_num IS NOT NULL and
> pfile.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num)
>
> I checked the corresponding select statement works
> select a.Order_num, a.facility_num,a.oth_facility_num, b.Order_num,
> b.facility
> from efile a, pfile b
> where a.Order_num = b.Order_num and a.facility_num <> b.facility
> and b.facility like '9999%'
> and a.facility_num not like 'N/A%' group by a.Order_num
> This works with group by clause too.
> When I run update statement, I get
> "Cannot insert null values into column facility "
> However, I checked there is no null value in the select
> statement or existing data. Then I changed the column
> to allow nulls, it put null value for all records.
> I also tried the statement without max(facility_num)
> as there isn't more than one record as of now.
> Still I get "Cannot insert null value " error, any ideas
> how to resolve this?
> I am stuck, I have to meeet deadline.
> Thanks for your help!
> -M
>
>

Having major problems with my insert query logic

I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.

Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.

Here is some example code from my program:
sub Test{
foreach my $table(keys %$HoH){
foreach my $field(keys %{$HoH->{$table}}){
if($table eq "CPU"){
my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values ('$field',
'$HoH->{CPU}{$field}{Name}',
'$HoH->{CPU}{$field}{MaxClockSpeed}' ,
'$HoH->{Host}{SystemNetName}')";
print "$CPUstatement\n";
if ($db->Sql($CPUstatement))
{
print "Error on SQL Statement\n";
Win32::ODBC::DumpError();
}
else
{
print "successful\n";
}
}
}


}
}

Thanks,
LauraI'm assuming that your hash values are printing as expected. The construction looks strange (but it could be fine) to me.

Is the CPUNumber the primary key for the CPU table? If so, you could use something like:my $CPUstatement = "IF EXISTS (SELECT * FROM CPU WHERE CPUNUMBER = '$field')
THEN UPDATE CPU
SET Name = '$HoH->{CPU}{$field}{Name}'
, MaxClockSpeed = '$HoH->{CPU}{$field}{MaxClockSpeed}'
, SystemNetName = '$HoH->{Host}{SystemNetName}'
WHERE CPUNumber = '$field'
ELSE INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values (
'$field'
, '$HoH->{CPU}{$field}{Name}'
, '$HoH->{CPU}{$field}{MaxClockSpeed}'
, '$HoH->{Host}{SystemNetName}')";-PatP|||I tried your code and I am getting an error -> Incorrect Systax near the keyword 'THEN'

What do you suppose that means? I copied and pasted the code as is.

Thanks,
Laura|||That error means that I don't proofread very well ;) I was composing as I typed, and simply got ahead of myself then didn't clean up afterwards. Just remove the word THEN from that statement. Sorry.

-PatP|||oh wow. That's so cool, it worked.

Thanks Pat for your help. I learn new things everyday.

-Laura|||I just love it when I can make a lovely lady happy!

-PatP

Have SQL report IP of query-er

I am trying to come up with a way to save the IP of the last computer that ran an update in our servers. I don't want to have to re-write the .net code, so I am wondering if there is a way, using functions available in SQL 2000 that I can get the IP of the machine running the update.

I know that SQL server 'knows' this information because it can log it in it's log files, but I don't know how to get at it from a stored procedure.

Thanks.

DECLARE @.Hostname VARCHAR(255)
SET @.Hostname=host_name()
CREATETABLE #Results
(
Results VARCHAR(4000)
)

DECLARE @.Commandstring VARCHAR(300)
SET @.Commandstring='ping '+ @.Hostname
INSERTINTO #Results
EXEC master..xp_cmdshell @.Commandstring

SelectDISTINCTSUBSTRING(Results,12,CHARINDEX(':',Results)-12)AS HostIpAdressfrom #Results
Where ResultsLIKE'Reply from%'

DROPTABLE #Results

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for
each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or
if I'm just missing something. Anyway the scan results is stored as an
XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
Now here is the insert statement for the table I am having trouble
with.
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(), LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
#temp.Name
If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.Because your procedure don't use sp_executeSql you can use a table variable,
not a temp table.
Declare @.Tab table
(
Field1 nvarchar(10),
Field2 int,
...(exactly the fields in the xml file)
)
INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, @.Tab.ID, @.TabParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
@.Tab.scanattribute, getdate(), getdate()
FROM tblScan
INNER JOIN tblScanAttribute
JOIN@.Tab ON tblScanAttribute.Name =
@.Tab.Name
Of course fields must match...
Hope it helps
Benga.
"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...
> Using ms sql 2000
> I have 2 tables.
> I have a table which has information regarding a computer scan. Each
> record in this table has a column called MAC which is the unique ID for
> each Scan. The table in question holds the various scan results of
> every scan from different computers. I have an insert statement that
> works however I am having troulbe getting and update statement out of
> it, not sure if I'm using the correct method to insert and thats why or
> if I'm just missing something. Anyway the scan results is stored as an
> XML document(@.iTree) so I have a temp table that holds the relevent
> info from that. Here is my Insert statement for the temporary table.
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
>
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(), LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.
>|||While this is good to know my real problem is that I need the statement
that will do what my insert does accept I need it to be an update
statement. I need the update because an insert is only going to happen
once for each client.
Benga wrote:
> Because your procedure don't use sp_executeSql you can use a table variabl
e,
> not a temp table.
> Declare @.Tab table
> (
> Field1 nvarchar(10),
> Field2 int,
> ...(exactly the fields in the xml file)
> )
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, @.Tab.ID, @.TabParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> @.Tab.scanattribute, getdate(), getdate()
> FROM tblScan
> INNER JOIN tblScanAttribute
> JOIN@.Tab ON tblScanAttribute.Name =
> @.Tab.Name
> Of course fields must match...
> Hope it helps
> Benga.
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151351218.116752.197980@.m73g2000cwd.googlegroups.com...|||Fixed it, no problems.
rhaazy wrote:
> While this is good to know my real problem is that I need the statement
> that will do what my insert does accept I need it to be an update
> statement. I need the update because an insert is only going to happen
> once for each client.
> Benga wrote:

Have Insert statement, need equivalent Update.

Using ms sql 2000
I have 2 tables.
I have a table which has information regarding a computer scan. Each
record in this table has a column called MAC which is the unique ID for

each Scan. The table in question holds the various scan results of
every scan from different computers. I have an insert statement that
works however I am having troulbe getting and update statement out of
it, not sure if I'm using the correct method to insert and thats why or

if I'm just missing something. Anyway the scan results is stored as an

XML document(@.iTree) so I have a temp table that holds the relevent
info from that. Here is my Insert statement for the temporary table.

INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)

Now here is the insert statement for the table I am having trouble
with.

INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
ScanID, AttributeValue, DateCreated, LastModified)
SELECT @.MAC, #temp.ID, #temp.ParentID,
tblScanAttribute.ScanAttributeID, tblScan.ScanID,
#temp.scanattribute, DateCreated = getdate(),
LastModified =
getdate()
FROM tblScan, tblScanAttribute JOIN #temp ON
tblScanAttribute.Name =
#temp.Name

If there is a way to do this without the temporary table that would be
great, but I haven't figured a way around it yet, if anyone has any
ideas that would be great, thanks.rhaazy (rhaazy@.gmail.com) writes:
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
> 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> Now here is the insert statement for the table I am having trouble
> with.
> INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
> SELECT @.MAC, #temp.ID, #temp.ParentID,
> tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> #temp.scanattribute, DateCreated = getdate(),
> LastModified =
> getdate()
> FROM tblScan, tblScanAttribute JOIN #temp ON
> tblScanAttribute.Name =
> #temp.Name
> If there is a way to do this without the temporary table that would be
> great, but I haven't figured a way around it yet, if anyone has any
> ideas that would be great, thanks.

I have some difficulties to understand what your problem is. If all
you want to do is to insert from the XML document, then you don't
need the temp table, but you could use OPENXML directly in the
query.

But then you talk about an UPDATE as well, and if your aim is to insert
new rows, and update existing, it's probably better to use a temp
table (or a table variable), so that you don't have to run OPENXML twice.
Some DB engines support a MERGE command which performs the task of
UPDATE and INSERT in one statement, but this is not available in
SQL Server, not even in SQL 2005.

If this did not answer your question, could you please clarify?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My app runs on all my companies PCs every month a scan is performed and
the resulst are stored in a database. So the first time a scan is
performed for any PC it will be an insert, but after that it will
always be an update. I tried using openxml in my insert statement but
kept getting an error stating my sub query is returning more than one
result... So since I couldn't do it that way I'm trying this method.
All the relevent openxml is there I just couldn't figure out how to
insert each column using it. If you have any suggestions I'm open to
give it a try.

Erland Sommarskog wrote:
> rhaazy (rhaazy@.gmail.com) writes:
> > INSERT INTO #temp
> > SELECT * FROM openxml(@.iTree,
> > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > WITH(
> > ID nvarchar(50) './@.ID',
> > ParentID nvarchar(50) './@.ParentID',
> > Name nvarchar(50) './@.Name',
> > scanattribute nvarchar(50) '.'
> > )
> > Now here is the insert statement for the table I am having trouble
> > with.
> > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > ScanID, AttributeValue, DateCreated, LastModified)
> > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > #temp.scanattribute, DateCreated = getdate(),
> > LastModified =
> > getdate()
> > FROM tblScan, tblScanAttribute JOIN #temp ON
> > tblScanAttribute.Name =
> > #temp.Name
> > If there is a way to do this without the temporary table that would be
> > great, but I haven't figured a way around it yet, if anyone has any
> > ideas that would be great, thanks.
> I have some difficulties to understand what your problem is. If all
> you want to do is to insert from the XML document, then you don't
> need the temp table, but you could use OPENXML directly in the
> query.
> But then you talk about an UPDATE as well, and if your aim is to insert
> new rows, and update existing, it's probably better to use a temp
> table (or a table variable), so that you don't have to run OPENXML twice.
> Some DB engines support a MERGE command which performs the task of
> UPDATE and INSERT in one statement, but this is not available in
> SQL Server, not even in SQL 2005.
> If this did not answer your question, could you please clarify?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Fixed it no problems.
rhaazy wrote:
> My app runs on all my companies PCs every month a scan is performed and
> the resulst are stored in a database. So the first time a scan is
> performed for any PC it will be an insert, but after that it will
> always be an update. I tried using openxml in my insert statement but
> kept getting an error stating my sub query is returning more than one
> result... So since I couldn't do it that way I'm trying this method.
> All the relevent openxml is there I just couldn't figure out how to
> insert each column using it. If you have any suggestions I'm open to
> give it a try.
> Erland Sommarskog wrote:
> > rhaazy (rhaazy@.gmail.com) writes:
> > > INSERT INTO #temp
> > > SELECT * FROM openxml(@.iTree,
> > > 'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> > > WITH(
> > > ID nvarchar(50) './@.ID',
> > > ParentID nvarchar(50) './@.ParentID',
> > > Name nvarchar(50) './@.Name',
> > > scanattribute nvarchar(50) '.'
> > > )
> > > > Now here is the insert statement for the table I am having trouble
> > > with.
> > > > INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> > > ScanID, AttributeValue, DateCreated, LastModified)
> > > SELECT @.MAC, #temp.ID, #temp.ParentID,
> > > tblScanAttribute.ScanAttributeID, tblScan.ScanID,
> > > #temp.scanattribute, DateCreated = getdate(),
> > > LastModified =
> > > getdate()
> > > FROM tblScan, tblScanAttribute JOIN #temp ON
> > > tblScanAttribute.Name =
> > > #temp.Name
> > > > If there is a way to do this without the temporary table that would be
> > > great, but I haven't figured a way around it yet, if anyone has any
> > > ideas that would be great, thanks.
> > I have some difficulties to understand what your problem is. If all
> > you want to do is to insert from the XML document, then you don't
> > need the temp table, but you could use OPENXML directly in the
> > query.
> > But then you talk about an UPDATE as well, and if your aim is to insert
> > new rows, and update existing, it's probably better to use a temp
> > table (or a table variable), so that you don't have to run OPENXML twice.
> > Some DB engines support a MERGE command which performs the task of
> > UPDATE and INSERT in one statement, but this is not available in
> > SQL Server, not even in SQL 2005.
> > If this did not answer your question, could you please clarify?
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/pr...oads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Have a better UPDATE statement than what I wrote?

/*
This is a long post. You can paste the whole message
in the SQL Query Analyzer.
I have a scenario where there are records
with values pointing to wrong records and I need to fix them
using an Update statement.

I have a sample code to reproduce my problem.
To simplify the scenario I am trying to use Order related
tables to explain a little better the tables i have to work with.
Please don't bother looking at the wrong relationship and how
the tables are designed. That's not my current problem. My
job is to correct the wrong data either using code or manually.

Here are the tables I have created:

TBLORDERS where two fields I am interested in are:
ORDERTYPENO linking to TBLORDERTYPE
LASTSTATUSNO linking to TBLSTATUS

TBLORDERTYPE where one field I am interested in is
ORDERPROCESSINGNO

TBLORDERPROCESSING

Each order has a link to OrderTypeNo and each
OrderTypeNo has a link to OrderProcessingNo.

TBLORDERSTATUSES where one field I am
interested in is
STATUSNO

TBLSTATUS where one field I am interested in is
ORDERPROCESSINGNO

I have the sample code here:
*/

--DROP DATABASE TestDB

CREATE DATABASE TestDB
GO

USE TestDB

CREATE TABLE TBLORDER
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERNUMBER VARCHAR(50),
ORDERTYPENO INT,
LASTSTATUSNO INT
)

INSERT INTO TBLORDER (IDNO, ORDERNUMBER, ORDERTYPENO, LASTSTATUSNO)
SELECT 1, 'ORDERTEST1', 1, 3 UNION ALL
SELECT 2, 'ORDERTEST2', 1, 3 UNION ALL
SELECT 3, 'ORDERTEST3', 2, 16 UNION ALL
SELECT 4, 'ORDERTEST4', 2, 16 UNION ALL
SELECT 5, 'ORDERTEST5', 2, 16 UNION ALL
SELECT 6, 'ORDERTEST6', 2, 16 UNION ALL
SELECT 7, 'ORDERTEST7', 4, 5 UNION ALL
SELECT 8, 'ORDERTEST8', 4, 5 UNION ALL
SELECT 9, 'ORDERTEST9', 6, 22 UNION ALL
SELECT 10, 'ORDERTEST10', 6, 22 UNION ALL
SELECT 11, 'ORDERTEST11', 7, 20

CREATE TABLE TBLORDERSTATUSES
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERNO INT,
STATUSNO INT
)

INSERT INTO TBLORDERSTATUSES (IDNO, ORDERNO, STATUSNO)
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 1, 3 UNION ALL
SELECT 4, 1, 4 UNION ALL
SELECT 5, 2, 1 UNION ALL
SELECT 6, 2, 2 UNION ALL
SELECT 7, 2, 3 UNION ALL
SELECT 8, 2, 4 UNION ALL
SELECT 9, 3, 15 UNION ALL
SELECT 10, 3, 16 UNION ALL
SELECT 11, 3, 17 UNION ALL
SELECT 12, 4, 15 UNION ALL
SELECT 13, 4, 16 UNION ALL
SELECT 14, 4, 17 UNION ALL
SELECT 15, 5, 15 UNION ALL
SELECT 16, 5, 16 UNION ALL
SELECT 17, 5, 17 UNION ALL
SELECT 18, 6, 15 UNION ALL
SELECT 19, 6, 16 UNION ALL
SELECT 20, 6, 17 UNION ALL
SELECT 21, 7, 5 UNION ALL
SELECT 22, 7, 6 UNION ALL
SELECT 23, 8, 5 UNION ALL
SELECT 24, 8, 6 UNION ALL
SELECT 25, 9, 22 UNION ALL
SELECT 26, 9, 23 UNION ALL
SELECT 27, 9, 24 UNION ALL
SELECT 28, 9, 25 UNION ALL
SELECT 29, 10, 22 UNION ALL
SELECT 30, 10, 23 UNION ALL
SELECT 31, 10, 24 UNION ALL
SELECT 32, 10, 25 UNION ALL
SELECT 33, 11, 18 UNION ALL
SELECT 34, 11, 19 UNION ALL
SELECT 35, 11, 20 UNION ALL
SELECT 36, 11, 21

CREATE TABLE TBLORDERTYPE
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERTYPE VARCHAR(50),
ORDERPROCESSINGNO INT
)

INSERT INTO TBLORDERTYPE (IDNO, ORDERTYPE, ORDERPROCESSINGNO)
SELECT 1, 'CATEGORY 100', 1 UNION ALL
SELECT 2, 'CATEGORY 200', 5 UNION ALL
SELECT 3, 'CATEGORY 300', 3 UNION ALL
SELECT 4, 'CATEGORY 400', 2 UNION ALL
SELECT 5, 'CATEGORY 500', 4 UNION ALL
SELECT 6, 'CATEGORY 600', 9 UNION ALL
SELECT 7, 'CATEGORY 700', 8 UNION ALL
SELECT 8, 'CATEGORY 800', 7 UNION ALL
SELECT 9, 'CATEGORY 900', 6

CREATE TABLE TBLORDERPROCESSING
(
IDNO INT PRIMARY KEY NOT NULL,
ORDERPROCESSING VARCHAR(50)
)

INSERT INTO TBLORDERPROCESSING (IDNO, ORDERPROCESSING)
SELECT 1, 'ORDER PROCESSING A1' UNION ALL
SELECT 2, 'ORDER PROCESSING A9' UNION ALL
SELECT 3, 'ORDER PROCESSING Z5' UNION ALL
SELECT 4, 'ORDER PROCESSING 76' UNION ALL
SELECT 5, 'ORDER PROCESSING 98' UNION ALL
SELECT 6, 'ORDER PROCESSING AB' UNION ALL
SELECT 7, 'ORDER PROCESSING 11' UNION ALL
SELECT 8, 'ORDER PROCESSING T7' UNION ALL
SELECT 9, 'ORDER PROCESSING ZX'

CREATE TABLE TBLSTATUS
(
IDNO INT PRIMARY KEY NOT NULL,
STATUS VARCHAR(50),
ORDERPROCESSINGNO INT
)

INSERT INTO TBLSTATUS (IDNO, STATUS, ORDERPROCESSINGNO)
SELECT 1, 'ABC', 1 UNION ALL
SELECT 2, 'DEF', 1 UNION ALL
SELECT 3, 'GHI', 1 UNION ALL
SELECT 4, 'JKL', 1 UNION ALL
SELECT 5, 'MNO', 2 UNION ALL
SELECT 6, 'PQR', 2 UNION ALL
SELECT 7, 'STU', 3 UNION ALL
SELECT 8, 'VWX', 3 UNION ALL
SELECT 9, 'YZ', 3 UNION ALL
SELECT 10, '123', 3 UNION ALL
SELECT 11, '456', 3 UNION ALL
SELECT 12, '789', 3 UNION ALL
SELECT 13, '0AA', 3 UNION ALL
SELECT 14, '0BB', 3 UNION ALL
SELECT 15, '0CC', 5 UNION ALL
SELECT 16, '0DD', 5 UNION ALL
SELECT 17, '0EE', 5 UNION ALL
SELECT 18, '0FF', 8 UNION ALL
SELECT 19, '0GG', 8 UNION ALL
SELECT 20, '0HH', 8 UNION ALL
SELECT 21, '0II', 8 UNION ALL
SELECT 22, '0JJ', 9 UNION ALL
SELECT 23, '0KK', 9 UNION ALL
SELECT 24, '0LL', 9 UNION ALL
SELECT 25, '0MM', 9

/*
If you run the above, the data is CORRECT and the way
it normally should be.

Basically, each Order is linked to an OrderTypeNo. Each
OrderTypeNo is linked to an OrderProcessingNo.
Each Order has MANY OrderStatuses. Each
OrderProcessingNo has MANY Statuses.
So both TBLORDERTYPE and TBLSTATUS is pointing
to TBLORDERPROCESSING. I will mess up an Order
record for example to point to a wrong OrderType and
leave its LASTSTATUSNO and all its CHILD
TBLORDERSTATUSES STATUS records point to
the CORRECT ORDERPROCESSINGNO.
*/

UPDATE TBLORDER
SET ORDERTYPENO = 3
WHERE IDNO = 5 OR IDNO = 10

/*
So now both Order IDNO 5 & 10 are basically messed
up as they are pointing to ORDERTYPENO 3 (i.e.
ORDERPROCESSINGNO 3) whereas their
LASTSTATUSNO and all its TBLORDERSTATUS
STATUS records are pointing to ......
ORDERPROCESSINGNO 5 & 9

Now I will mess up both TBLORDER and
TBLORDERDETAILS in order for my code
NOT to fix it since this I will have to deal and
decide what to correct manually.
*/

UPDATE TBLORDER
SET ORDERTYPENO = 2, LASTSTATUSNO = 15
WHERE IDNO = 8

SELECT * FROM TBLORDER
GO

CREATE VIEW VIEW1
-- This VIEW1 returns all TBLORDER records that have the problem
AS
SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO
FROM TBLORDER
INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO
INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO
AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO
GO

CREATE VIEW VIEW2
-- This VIEW2 does a GROUP BY of all TBLORDER.IDNO &
TBLSTATUS.ORDERPROCESSINGNO
AS
SELECT TOP 100 PERCENT TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
FROM TBLORDERSTATUSES INNER JOIN
TBLORDER ON TBLORDERSTATUSES.ORDERNO = TBLORDER.IDNO
INNER JOIN
TBLSTATUS ON TBLORDERSTATUSES.STATUSNO =
TBLSTATUS.IDNO INNER JOIN
VIEW1 ON TBLORDER.IDNO = VIEW1.IDNO
GROUP BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
ORDER BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNO
GO

CREATE VIEW VIEW3
-- This VIEW3 checks to see if TBLORDERSTATUS records have more than one
ORDERPROCESSINGNO
AS
SELECT IDNO
FROM VIEW2
GROUP BY IDNO
HAVING (COUNT(*) > 1)
GO

CREATE TABLE TMPORDERS
(
IDNO INT,
OLDORDERTYPENO INT,
NEWORDERTYPENO INT,
LASTSTATUSNO INT
)

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNO
FROM TBLORDER
INNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNO
INNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNO
AND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNO
LEFT JOIN VIEW3 ON TBLORDER.IDNO = VIEW3.IDNO AND VIEW3.IDNO IS NULL

SELECT * FROM TMPORDERS

UPDATE TMPORDERS
SET NEWORDERTYPENO = TBLORDERTYPE.IDNO
FROM TBLORDERTYPE
INNER JOIN TBLORDERPROCESSING ON TBLORDERTYPE.ORDERPROCESSINGNO =
TBLORDERPROCESSING.IDNO
INNER JOIN TBLSTATUS ON TBLORDERPROCESSING.IDNO =
TBLSTATUS.ORDERPROCESSINGNO
WHERE TBLSTATUS.IDNO = TMPORDERS.LASTSTATUSNO

UPDATE TBLORDER
SET ORDERTYPENO = NEWORDERTYPENO
FROM TMPORDERS
WHERE TMPORDERS.IDNO = TBLORDER.IDNO

SELECT * FROM TBLORDER

/*
Is there a better to write my Update statement? As you can see that
I am using 3 views, 1 temp table and 2 update statements to
fix my problem.
I am not even sure if i'll need to add more update statements
to handle other corrections. If that is the case I am trying to
see if my code can be simplified in order for it to be easily
modifiable to handle other scenarios.

Thank you for your time.
*/Hello, Serge

1. To complete your DDL, you should also add the foreign keys and
unique constraints:

ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
REFERENCES TBLORDERPROCESSING (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
REFERENCES TBLORDERTYPE (IDNO)
ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
REFERENCES TBLSTATUS (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
REFERENCES TBLORDER (IDNO)
ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
REFERENCES TBLSTATUS (IDNO)

ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)

2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
clause, not in the WHERE clause (and therefore it's ignored). To
exclude from the INSERT any rows that are in VIEW3 you need to move the
condition "VIEW3.IDNO IS NULL" to the WHERE clause.

3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
(without using views and with a slight performance improvement) as:

INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)

4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
the join with the TBLORDERPROCESSING) as:

UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
WHERE S.IDNO = TMPORDERS.LASTSTATUSNO

5. The whole story can be written in a single UPDATE statement, like
this:

UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
FROM TBLORDER O INNER JOIN (
SELECT O1.IDNO, (
SELECT T4.IDNO FROM TBLORDERTYPE T4
INNER JOIN TBLSTATUS S4
ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
WHERE S4.IDNO = O1.LASTSTATUSNO
) AS NEWORDERTYPENO
FROM TBLORDER O1
INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
AND NOT EXISTS (
SELECT O2.IDNO
FROM TBLORDER O2
INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
WHERE O2.IDNO IN (
SELECT O3.IDNO
FROM TBLORDER O3
INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
)
GROUP BY O2.IDNO
HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
)
) X ON O.IDNO=X.IDNO

I have to admit that this is a rather complex UPDATE statement and,
while it's performance is better than your solution, maintainability
may be less. So you may prefer using some views to improve readability
(for example VIEW1, which can be used in two places in the above
statement). However, there are some interesting points that you can
learn from this complex statement:
a) avoiding the use of temporary tables, when subqueries can be used
b) the use of table aliases, to improve readability
c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
... IS NULL";
e) the use of "WHERE ... IN" subqueries, instead of joins (when there
is no column used from the joined subquery);
f) the use of subqueries in the SELECT clause, instead of joins (when
there is only one column used from the joined subquery).
I think that these usages of subqueries (points d,e,f) improve
readability (and may, in rare cases, even improve performance), but
that's for you to decide, in each particular case.

Razvan|||Hello Razvan,

Thank you for your detailed explanation.

It will take me a little bit of time to go through the points
and understand them.

Thanks again.

> 1. To complete your DDL, you should also add the foreign keys and
> unique constraints:
> ALTER TABLE TBLORDERTYPE ADD FOREIGN KEY (ORDERPROCESSINGNO)
> REFERENCES TBLORDERPROCESSING (IDNO)
> ALTER TABLE TBLSTATUS ADD FOREIGN KEY (ORDERPROCESSINGNO)
> REFERENCES TBLORDERPROCESSING (IDNO)
> ALTER TABLE TBLORDER ADD FOREIGN KEY (ORDERTYPENO)
> REFERENCES TBLORDERTYPE (IDNO)
> ALTER TABLE TBLORDER ADD FOREIGN KEY (LASTSTATUSNO)
> REFERENCES TBLSTATUS (IDNO)
> ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (ORDERNO)
> REFERENCES TBLORDER (IDNO)
> ALTER TABLE TBLORDERSTATUSES ADD FOREIGN KEY (STATUSNO)
> REFERENCES TBLSTATUS (IDNO)
> ALTER TABLE TBLSTATUS ADD UNIQUE (STATUS)
> ALTER TABLE TBLORDERTYPE ADD UNIQUE (ORDERTYPE)
> ALTER TABLE TBLORDERPROCESSING ADD UNIQUE (ORDERPROCESSING)
> ALTER TABLE TBLORDER ADD UNIQUE (ORDERNUMBER)
> ALTER TABLE TBLORDERSTATUSES ADD UNIQUE (ORDERNO, STATUSNO)
> 2. Your "INSERT INTO TMPORDERS [...]" does not perform as you expect,
> because the condition "AND VIEW3.IDNO IS NULL" is in the "LEFT JOIN"
> clause, not in the WHERE clause (and therefore it's ignored). To
> exclude from the INSERT any rows that are in VIEW3 you need to move the
> condition "VIEW3.IDNO IS NULL" to the WHERE clause.
> 3. This corrected "INSERT INTO TMPORDERS [...]" can be rewritten
> (without using views and with a slight performance improvement) as:
> INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)
> SELECT O1.IDNO, O1.ORDERTYPENO, O1.LASTSTATUSNO
> FROM TBLORDER O1
> INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
> INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
> WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
> AND NOT EXISTS (
> SELECT O2.IDNO
> FROM TBLORDER O2
> INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
> INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
> WHERE O2.IDNO IN (
> SELECT O3.IDNO
> FROM TBLORDER O3
> INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
> INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
> WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
> )
> GROUP BY O2.IDNO
> HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
> )
> 4. The "UPDATE TMPORDERS" statement, can be rewritten (by eliminating
> the join with the TBLORDERPROCESSING) as:
> UPDATE TMPORDERS SET NEWORDERTYPENO = T.IDNO
> FROM TBLORDERTYPE T INNER JOIN TBLSTATUS S
> ON T.ORDERPROCESSINGNO = S.ORDERPROCESSINGNO
> WHERE S.IDNO = TMPORDERS.LASTSTATUSNO
> 5. The whole story can be written in a single UPDATE statement, like
> this:
> UPDATE TBLORDER SET ORDERTYPENO = NEWORDERTYPENO
> FROM TBLORDER O INNER JOIN (
> SELECT O1.IDNO, (
> SELECT T4.IDNO FROM TBLORDERTYPE T4
> INNER JOIN TBLSTATUS S4
> ON T4.ORDERPROCESSINGNO = S4.ORDERPROCESSINGNO
> WHERE S4.IDNO = O1.LASTSTATUSNO
> ) AS NEWORDERTYPENO
> FROM TBLORDER O1
> INNER JOIN TBLORDERTYPE T1 ON O1.ORDERTYPENO = T1.IDNO
> INNER JOIN TBLSTATUS S1 ON O1.LASTSTATUSNO = S1.IDNO
> WHERE T1.ORDERPROCESSINGNO <> S1.ORDERPROCESSINGNO
> AND NOT EXISTS (
> SELECT O2.IDNO
> FROM TBLORDER O2
> INNER JOIN TBLORDERSTATUSES OS ON OS.ORDERNO = O2.IDNO
> INNER JOIN TBLSTATUS S2 ON S2.IDNO = OS.STATUSNO
> WHERE O2.IDNO IN (
> SELECT O3.IDNO
> FROM TBLORDER O3
> INNER JOIN TBLORDERTYPE T3 ON O3.ORDERTYPENO = T3.IDNO
> INNER JOIN TBLSTATUS S3 ON O3.LASTSTATUSNO = S3.IDNO
> WHERE T3.ORDERPROCESSINGNO <> S3.ORDERPROCESSINGNO
> )
> GROUP BY O2.IDNO
> HAVING COUNT(DISTINCT S2.ORDERPROCESSINGNO)>1
> )
> ) X ON O.IDNO=X.IDNO
> I have to admit that this is a rather complex UPDATE statement and,
> while it's performance is better than your solution, maintainability
> may be less. So you may prefer using some views to improve readability
> (for example VIEW1, which can be used in two places in the above
> statement). However, there are some interesting points that you can
> learn from this complex statement:
> a) avoiding the use of temporary tables, when subqueries can be used
> b) the use of table aliases, to improve readability
> c) the use of "COUNT(DISTINCT something)" instead of two "GROUP BY"-s;
> d) the use of "NOT EXISTS" subqueries, instead of "LEFT JOIN ... WHERE
> ... IS NULL";
> e) the use of "WHERE ... IN" subqueries, instead of joins (when there
> is no column used from the joined subquery);
> f) the use of subqueries in the SELECT clause, instead of joins (when
> there is only one column used from the joined subquery).
> I think that these usages of subqueries (points d,e,f) improve
> readability (and may, in rare cases, even improve performance), but
> that's for you to decide, in each particular case.
> Razvan

hard question.. is it possible to update many column of data in 1 query?

in my original database have a column which is for "path" ,the record in this column is like → 【mms://192.12.34.56/2/1/kbe-1a1.wmv】

this kind of column is about 1202,045 .. I don't think is a easy job to update by person.. it may work but have to do same job 1202,045 times..

I have to change 【 mms://192.12.34.56/2/1/kbe-1a1.wav】 to 【 mms://202.11.34.56/2/1/kbe-1a1.wav】

I tried to find the reference book and internet . can't find out the answer for this problem.

can you help? or maybe is it a impossible job?

thanks

As long as you're using an exact match it shouldn't be an issue. (UPDATE TableName SET FieldName = NewFieldValue WHERE FieldName = OldFieldValue)

But, if you're talking about have to fix the path for a lot of different files, you'll have to do either a complex sproc that can parse the path or a small application that pulls the initial set using a LIKE query for the path to be changed and then turns around and does an UPDATE on each record to the new path.

|||

thank you

problem is to fix the path but I can't really understand your suggest

can you explain more or just give me a simple example please

I appreciate your help, thank you very much

|||

You can use REPLACE to fix your path value. Here is a sample:

UPDATE pathToChangeSET path =replace(path,'192.12','202.11')

It will replace all 192.12 with 202.11 if you run this query for your table.

|||

thank you

sorry I didn't make my question clearly ..

the problem can't be resolved by use replace

for example

in the same column " voice"

some are looks like → mms:// 1.2.3.4/1/2/voice.wma

some are looks like → mms://www.showhigh.com/1/2/voice.wma

also we have others like → "this voice is mms://1.2.3.4/1/2/voice.wma " -- the last kind of data we don't know how many cheracter front of the path...

-----------

do you have any good idea to change the path without manually

thanks again..

|||

Do you want to change mms:// 1.2.3.4/, mms://www.showhigh.com/, and this voice is mms://1.2.3.4/ or anything up to the first slash (/) to mms://202.11.34.56/? If this is the case, you need to find the location of this slash and get this job done with a string operation.

Post some sample data and the result you are expecting. It will save time to get result. Thanks.

|||

sorry.. not make my question clearly enough still..

here are the example for my question..

in Table name Movie, have a column name "Movie_Voice"

Voice_no | Movie_Voice

1 | mms://1.2.3.4/1/65/05-65-01.wma want change it to be → mms/1.2.3.4/1/65/05-65-01.wma

2 | mms://media.movie.com/12/79/ef13-79-01.wma want change it to be → mms/12/79/ef13-79-01.wma

3 | there are the voice path mms://1.2.3.4/23/230.wma want change it to be → there are the voice path mms/1.2.3.4/23/230.wma

4 | your voice path mms://media.movie.com/98/2/23.wma want change it to be → your voice path mms/98/2/23.wma

------------------------------------------------------------

the voice_no 3 and voice_no 4 are the hardest part for me to think out a way to resolve ,because I don't know what are front of the mms path

the reason why I have to change the data in database . I move all wma files to root and creat a file name mms, so now I have to change all path

which alreday list in database...

thank you

|||

Hi jc,

If these are the only patterns or u have more patterns. If you can provide me a list of pattern i can create a regex to update the column for you which you can use to update the column

Satya

|||

unfortunately... I can't list a pattern for this problem

because... the pattern may be up to 4000 or more...

some of tables which including all html tag to 1 column . the column not have the mms path not only have some html tag ..

|||

Can u send me the exported table in a txt file or a mdf file

satya.tanwar@.gmail.com

coz without seeing the patterns nobody will be able to produce a desired solution

Satya

|||

thank you , I have just sent !

|||

Hi Try this function,

Select dbo.PatternReplace(TestColumn,'%mms://%/','mms/'),TestColumnfrom pro_Lesson_Text

CREATEFUNCTION dbo.PatternReplace

(

@.InputStringNVARCHAR(MAX),

@.PatternVARCHAR(100),

@.ReplaceTextNVARCHAR(MAX)

)

RETURNSNVARCHAR(MAX)

AS

BEGIN

DECLARE @.ResultNVARCHAR(MAX)SET @.Result=''

-- First character in a match

DECLARE @.FirstINT

-- Next character to start search on

DECLARE @.NextINTSET @.Next= 1

-- Length of the total string -- 8001 if @.InputString is NULL

DECLARE @.LenINTSET @.Len=COALESCE(LEN(@.InputString), 8001)

-- End of a pattern

DECLARE @.EndPatternINT

WHILE(@.Next<= @.Len)

BEGIN

SET @.First=PATINDEX('%'+ @.Pattern+'%',SUBSTRING(@.InputString, @.Next, @.Len))

IFCOALESCE(@.First, 0)= 0--no match - return

BEGIN

SET @.Result= @.Result+

CASE--return NULL, just like REPLACE, if inputs are NULL

WHEN @.InputStringISNULL

OR @.PatternISNULL

OR @.ReplaceTextISNULLTHENNULL

ELSESUBSTRING(@.InputString, @.Next, @.Len)

END

BREAK

END

ELSE

BEGIN

-- Concatenate characters before the match to the result

SET @.Result= @.Result+SUBSTRING(@.InputString, @.Next, @.First- 1)

SET @.Next= @.Next+ @.First- 1

SET @.EndPattern= 1

-- Find start of end pattern range

WHILEPATINDEX(@.Pattern,SUBSTRING(@.InputString, @.Next, @.EndPattern))= 0SET @.EndPattern= @.EndPattern+ 1

-- Find end of pattern range

WHILEPATINDEX(@.Pattern,SUBSTRING(@.InputString, @.Next, @.EndPattern))> 0

AND @.Len>=(@.Next+ @.EndPattern- 1)

SET @.EndPattern= @.EndPattern+ 1

--Either at the end of the pattern or @.Next + @.EndPattern = @.Len

SET @.Result= @.Result+ @.ReplaceTextSET @.Next= @.Next+ @.EndPattern- 1

END

END

RETURN(@.Result)

END

Result looks good Check my mail for the Results. Let me know in case anything else is required

Satya

|||

It looks like this would do it as well:

UPDATE Movie

SET Movie_voice=REPLACE(REPLACE(Movie_voice,'mms://media.movie.com/','mms/'),'mms://','mms/')

|||

Hi motley,

Its a good try but the problem is he has different patterns in the data to update. So need to update patterns not static string.

satya

|||

Maybe I misunderstood what he was asking for, but wouldn't the code you gave change:

mms://1.2.3.4/stuff to mms/stufff? And that's not what he showed what he wanted for #1 & #3.

hanlding null value in stored procedure

Hi,
I have a simple update query as
update tblApplication
set TotalSworn = MaleSworn + FemaleSworn,
TotalCivilian = MaleCivilian + FemaleCivilian,
GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
However, I need to build a stored procedure out of the above with the
fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
FemeleCivilian fields can have null values.
Any help is appreciated. Thanks.Use COALESCE or ISNULL
like this
update tblApplication
set TotalSworn = Coalesce(MaleSworn,0) + Coalesce(FemaleSworn,0),
TotalCivilian = Coalesce(MaleCivilian,0) + Coalesce(FemaleCivilian,0),
GrandTotal = Coalesce(MaleSworn,0) + Coalesce(FemaleSworn,0) +
Coalesce(MaleCivilian,0) + Coalesce(FemaleCivilian,0)
http://sqlservercode.blogspot.com/
"Jack" wrote:

> Hi,
> I have a simple update query as
> update tblApplication
> set TotalSworn = MaleSworn + FemaleSworn,
> TotalCivilian = MaleCivilian + FemaleCivilian,
> GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
> However, I need to build a stored procedure out of the above with the
> fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
> FemeleCivilian fields can have null values.
> Any help is appreciated. Thanks.
>|||Jack,
The column definition defines whether or not the column will allow NULLs.
HTH
Jerry
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:CA610AF3-57EA-4E1B-B1CC-665ABCCE6153@.microsoft.com...
> Hi,
> I have a simple update query as
> update tblApplication
> set TotalSworn = MaleSworn + FemaleSworn,
> TotalCivilian = MaleCivilian + FemaleCivilian,
> GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
> However, I need to build a stored procedure out of the above with the
> fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
> FemeleCivilian fields can have null values.
> Any help is appreciated. Thanks.
>|||Thanks for the help to both of you. I appreciate it. Regards.
"Jerry Spivey" wrote:

> Jack,
> The column definition defines whether or not the column will allow NULLs.
> HTH
> Jerry
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:CA610AF3-57EA-4E1B-B1CC-665ABCCE6153@.microsoft.com...
>
>

Handling SQLServerCE DataBase By windows application ?

Hi

i have the following problem :

i attempted to connect with a SQLServerCE DataBase

to Insert and update its rows, but i noticed that i want the reference :

System.Data.SqlServerCe

i went to (Add references) but i didn't find it ..

what should i do to break this problem ?

please help me !

Hi Imad

I have moved your treat to the Devices team who should be able to help you

mairead

PM, TS Data

|||

Hello and sorry for the delayed reply.

Just a question first - are you attemping to open a SqlServerCe database on your desktop PC or on a mobile device (like a Pocket PC / Smartphone)? This is important because we want to add the correct DLL to your project.

If you're opening the database on the desktop, then we can find the System.Data.SqlServerCe.DLL in the directory where DEVENV.EXE is. That would be something like C:\Program Files\Microsoft Visual Studio 8\Common7\IDE

If you're opening the database on a device, then we can find the System.Data.SqlServerCe.DLL in the Mobile SDK folder. That would be something like C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

If either of the DLLs are missing then it means your SQLServerCe SDK is not installed on your machine. The easiest way to fix this is to REPAIR the visual studio installation. This will re-install the SDK and ensure the DLLs are present on the machine.

Please let me know how it works out,

Kind regards,

Carlton Lane

Microsoft Visual Basic Team

|||

I am trying to use desktop application to open a Sqlserverce database that located on the PDA. but it throw this exception:

Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

I checked and see System.Data.SqlServerCe.DLL exists under:

D:\Program Files\Microsoft Visual Studio 8\Common7\IDE

D:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

Actually it throw that exception no matter what database I am trying to open. here is my code:

connStr = "Data Source =""Mobile Device\Program Files\Barcode_PDA\pda.sdf"";"
conn1 = New SqlServerCe.SqlCeConnection(connStr) < this generates the error

Thanks for your help!

|||

Hi Alex,

I'm sorry but the scenairo of opening a database on a device remotely from a desktop PC isnt supported by the SQLCE engine. Eventually, you'll get an error about the connection string being invalid. This is because the engine is targeting local data scenarios - that is where the Application and Database reside on the same machine, in the same process. This scenario starts to touch on client / server scenarios which currently arent supported.

Your current error about the missing dlls is telling us that the application is starting but cant find the engine. These dlls are found next to the System.Data.SqlServerCe.dll file. The ones in the Common7\IDE are for your PC. THe ones in SmartDevices\SDK are for the device. For a PC application, copy the ones from Common7\IDE into the executing directory of your application. But again, after you get pass this error, you will eventually get an error about the connection string being invalid because this scenario isnt supported.

HTH and good luck,

Carlton

|||

hi everybody

first thank you for helping me ..

but i want to tell you that i have solved my problem easily by installing sql server everywhere edition CTP

to install sql serverce tools and then all dll's have been loaded correctly ..

Handling SQLServerCE DataBase By windows application ?

Hi

i have the following problem :

i attempted to connect with a SQLServerCE DataBase

to Insert and update its rows, but i noticed that i want the reference :

System.Data.SqlServerCe

i went to (Add references) but i didn't find it ..

what should i do to break this problem ?

please help me !

Hi Imad

I have moved your treat to the Devices team who should be able to help you

mairead

PM, TS Data

|||

Hello and sorry for the delayed reply.

Just a question first - are you attemping to open a SqlServerCe database on your desktop PC or on a mobile device (like a Pocket PC / Smartphone)? This is important because we want to add the correct DLL to your project.

If you're opening the database on the desktop, then we can find the System.Data.SqlServerCe.DLL in the directory where DEVENV.EXE is. That would be something like C:\Program Files\Microsoft Visual Studio 8\Common7\IDE

If you're opening the database on a device, then we can find the System.Data.SqlServerCe.DLL in the Mobile SDK folder. That would be something like C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

If either of the DLLs are missing then it means your SQLServerCe SDK is not installed on your machine. The easiest way to fix this is to REPAIR the visual studio installation. This will re-install the SDK and ensure the DLLs are present on the machine.

Please let me know how it works out,

Kind regards,

Carlton Lane

Microsoft Visual Basic Team

|||

I am trying to use desktop application to open a Sqlserverce database that located on the PDA. but it throw this exception:

Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

I checked and see System.Data.SqlServerCe.DLL exists under:

D:\Program Files\Microsoft Visual Studio 8\Common7\IDE

D:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0

Actually it throw that exception no matter what database I am trying to open. here is my code:

connStr = "Data Source =""Mobile Device\Program Files\Barcode_PDA\pda.sdf"";"
conn1 = New SqlServerCe.SqlCeConnection(connStr) < this generates the error

Thanks for your help!

|||

Hi Alex,

I'm sorry but the scenairo of opening a database on a device remotely from a desktop PC isnt supported by the SQLCE engine. Eventually, you'll get an error about the connection string being invalid. This is because the engine is targeting local data scenarios - that is where the Application and Database reside on the same machine, in the same process. This scenario starts to touch on client / server scenarios which currently arent supported.

Your current error about the missing dlls is telling us that the application is starting but cant find the engine. These dlls are found next to the System.Data.SqlServerCe.dll file. The ones in the Common7\IDE are for your PC. THe ones in SmartDevices\SDK are for the device. For a PC application, copy the ones from Common7\IDE into the executing directory of your application. But again, after you get pass this error, you will eventually get an error about the connection string being invalid because this scenario isnt supported.

HTH and good luck,

Carlton

|||

hi everybody

first thank you for helping me ..

but i want to tell you that i have solved my problem easily by installing sql server everywhere edition CTP

to install sql serverce tools and then all dll's have been loaded correctly ..