Problems in Creating Views in Sql

Hi everybody,
I have couple of Access databases needed to be
converted to access/Sql Client/Server database, which is new to me. I
am trying to convert my access quries to Sql Views or Stored
Procedures. I have run into a problem currently when creating view in
Sql:
I need to create some columns based on conditions of other columns,
for example:
1. Column example 1:
IIf([Column1]=-1,[ListPrice]*[Volume],IIf([Column2]=-1,0,[ListPrice]*Volume]))
2. Column example 2:
IIf(Len([Product].[P_No])<10,"",SUBSTRING([Product].[P_No],6,5))
3.Column example 3:
IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
The above are actually the columns that I need to create.
I have these functions built in Access Queries, but not sure how to
build them in Sql views. Have tried different ways, but failed.
Any body knows how to do it?
Any help will be greatly appreciated!
Thanks in advance!"Shelley" <schow@.hersheys.com> wrote in message
news:e085e628.0402020838.450efcdc@.posting.google.com...
> 3.Column example 3:
> IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...
> "Shelley" <schow@.hersheys.com> wrote in message
> news:e085e628.0402020838.450efcdc@.posting.google.com...
> > 3.Column example 3:
> > IIf([CustType]="D",([Field1]+[Field2])*[Volume],Field1*Volume)
> Take a look at the CASE statement in the online help.
Thanks Freddy.
This one works.

Problems in Creating Views in Sql

Hi everybody,
I have couple of Access databases needed to be
converted to access/Sql Client/Server database, which is new to me. I
am trying to convert my access quries to Sql Views or Stored
Procedures. I have run into a problem currently when creating view in
Sql:
I need to create some columns based on conditions of other columns,
for example:
1. Column example 1:
IIf([Column1]=-1,[ListPrice]*[Volume],IIf([Column2]=-1,0,[ListPrice]*Volume]))
2. Column example 2:
IIf(Len([Product].[P_No])<10,"",SUBSTRING([Product].[P_No],6,5))
3.Column example 3:
IIf([CustType]="D" ,([Field1]+[Field2])*[Volume],Field1*Vol
ume)
The above are actually the columns that I need to create.
I have these functions built in Access Queries, but not sure how to
build them in Sql views. Have tried different ways, but failed.
Any body knows how to do it?
Any help will be greatly appreciated!
Thanks in advance!"Shelley" <schow@.hersheys.com> wrote in message
news:e085e628.0402020838.450efcdc@.posting.google.com...

quote:

---

> 3.Column example 3:
> IIf([CustType]="D" ,([Field1]+[Field2])*[Volume],Field1*Vol
ume)

---

Take a look at the CASE statement in the online help.|||"Freddy" <noemail@.noemail> wrote in message news:<OT0rBBb6DHA.2524@.TK2MSFTNGP11.phx.gbl>...

quote:

---

> "Shelley" <schow@.hersheys.com> wrote in message
> news:e085e628.0402020838.450efcdc@.posting.google.com...
> Take a look at the CASE statement in the online help.

---

Thanks Freddy.
This one works.

Problems after service window

This is unbelevable.. I've been struggeling a bit with getting distributed transactions to work, but yesterday I finally got it all working.

A couple of hours later (yesterday evening) there was a service window in the organisation and a couple of hotfixes etc was installed in the server (Win2k3).

This morning when I tried to launch the job (an SSIS package) I get the error message "Unable to load package". If I run the package as a cmd program I get a permission denied error. The package is stored in SSIS package store (MSDB\...\). I have added the account running the job-step in the local administrators group and also made it sys_admin of the SQL-database, but with the same result (package could not be loaded).

I have also tried to import the package again since I thought it might have been corrupted, but no success.

Any idas?

I would find out what those hotfixes were, what they do, and what problems they were meant to solve.

-Jamie

|||

It was

KB922616 - Security problem in HTML

KB917344 - Security problem in JScript

KB921398 - Windows explorer problem

I can't see any of them should affect this... I don't think they were ment to solve any spesific problem(s). I guess more or less all security patches for the servers are installed here (I'm not responsible for the server patching).

However, I'm not so sure the hotfixes are responsible. Perhaps the reboot triggered something, some policy, some settings that were changed but didn't take effect until the reboot or whatever...

Guess I could try to remove the patches and see if it helps.

|||

COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msi.dll" "c:\windows\system32\msi.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msiexec.exe" "c:\windows\system32\msiexec.exe"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msihnd.dll" "c:\windows\system32\msihnd.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msimsg.dll" "c:\windows\system32\msimsg.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msisip.dll" "c:\windows\system32\msisip.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msi.dll" "c:\windows\system32\dllcache\msi.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msiexec.exe" "c:\windows\system32\dllcache\msiexec.exe"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msihnd.dll" "c:\windows\system32\dllcache\msihnd.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msimsg.dll" "c:\windows\system32\dllcache\msimsg.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msisip.dll" "c:\windows\system32\dllcache\msisip.dll"

C:\WINDOWS\$MSI31Uninstall_KB893803v2$(2

Problems after service window

This is unbelevable.. I've been struggeling a bit with getting distributed transactions to work, but yesterday I finally got it all working.

A couple of hours later (yesterday evening) there was a service window in the organisation and a couple of hotfixes etc was installed in the server (Win2k3).

This morning when I tried to launch the job (an SSIS package) I get the error message "Unable to load package". If I run the package as a cmd program I get a permission denied error. The package is stored in SSIS package store (MSDB\...\). I have added the account running the job-step in the local administrators group and also made it sys_admin of the SQL-database, but with the same result (package could not be loaded).

I have also tried to import the package again since I thought it might have been corrupted, but no success.

Any idas?

I would find out what those hotfixes were, what they do, and what problems they were meant to solve.

-Jamie

|||

It was

KB922616 - Security problem in HTML

KB917344 - Security problem in JScript

KB921398 - Windows explorer problem

I can't see any of them should affect this... I don't think they were ment to solve any spesific problem(s). I guess more or less all security patches for the servers are installed here (I'm not responsible for the server patching).

However, I'm not so sure the hotfixes are responsible. Perhaps the reboot triggered something, some policy, some settings that were changed but didn't take effect until the reboot or whatever...

Guess I could try to remove the patches and see if it helps.

|||

COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msi.dll" "c:\windows\system32\msi.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msiexec.exe" "c:\windows\system32\msiexec.exe"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msihnd.dll" "c:\windows\system32\msihnd.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msimsg.dll" "c:\windows\system32\msimsg.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msisip.dll" "c:\windows\system32\msisip.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msi.dll" "c:\windows\system32\dllcache\msi.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msiexec.exe" "c:\windows\system32\dllcache\msiexec.exe"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msihnd.dll" "c:\windows\system32\dllcache\msihnd.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msimsg.dll" "c:\windows\system32\dllcache\msimsg.dll"
COPY "C:\WINDOWS\$MSI31Uninstall_KB893803v2$\msisip.dll" "c:\windows\system32\dllcache\msisip.dll"

C:\WINDOWS\$MSI31Uninstall_KB893803v2$(2

Problem: Trigger and multiple updates to the table

Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.

UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20

Error I am getting is :

Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

What is the best possible way to make it work? Thank you.Figure out which trigger is blowing up, and fix it? Knowing that there is more code than we can see, I can't for the life of me figure out a way to help you find out where the problem is, much less how to fix it.

-PatP|||I guess this is the problematic trigger. Should I use cursor to handle multiple inserts/updates?

CREATE trigger t_thickupdate on dbo.sched_lot_hdr
for update
as

-- declare local variables
declare
@.lotno int,
@.charthick varchar(10),
@.decthick decimal(6,5)

-- populate local variables
set @.lotno = (select lotno from inserted)
set @.charthick = (select matcharthick from inserted)
set @.decthick = (select matthick from inserted)

-- determine which was updated: character thickness or decimal thickness
if update(matthick)

-- decimal thickness was updated
begin
set @.charthick = (select thkfrac from prod_thk_stds where thkdec = @.decthick)
update sched_lot_hdr set matcharthick = @.charthick where lotno = @.lotno
end
else

-- character thickness was updated
begin
set @.decthick = (select thkdec from prod_thk_stds where thkfrac = @.charthick)
update sched_lot_hdr set matthick = @.decthick where lotno = @.lotno
end|||A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based...|||Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense...|||I'd suggest using:CREATE TRIGGER t_thickupdate
ON dbo.sched_lot_hdr
FOR UPDATE
AS

-- determine which was updated: character thickness or decimal thickness
IF Update(matthick) -- decimal thickness was updated
UPDATE sched_lot_hdr
SET matcharthick = s.thkfrac
FROM inserted AS i
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
JOIN prod_thk_stds AS s
ON (s.thkdec = i.matthick)
ELSE -- character thickness was updated
update sched_lot_hdr
set matthick = s.thkdec
FROM inserted AS i
JOIN prod_thk_stds AS s
ON (s.thkfrac = i.matcharthick)
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)

RETURNNote that you need to test this up one side and down the other before you put it into production, this was a quick paste up job with no opportunity for me to test it!

-PatP|||yeah :(

This already been developed. I came to this project recently and working on some enhancements. Thanks for the help. I think I understand the problem, I will resolve it.

Update: thats fast. Thanks Pat, I will test it.|||Originally posted by Brett Kaiser
A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based... Yea vous! I'm with you on that!
Originally posted by Brett Kaiser
Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense... Nah, that happens all the time when folks convert from what I call "unit-record" code to a database. They find ways to get the database to "clean up" data coming from disparate sources that would have required application changes when the only thing that could reach the data was their application.

-PatP