please help me create the table for the data in the following data lines
after this
i am out of my wits to thing for what data type to use. because for
everything i tried i got the error below the column name will be for the
table i appreciate a lot if someone can help me .
column 1 column 2 column 3
OMIM_Name No.of av Description
100650 .0001 ALCOHOL INTOLERANCE, ACUTE ACETALDEHYDE DEHYDROGENASE 2, ALLELE
2, INCLUDED; ALDH2*2, INCLUDED ALDH2, GLU487LYS The ALDH2*2-encoded protein
has a change from glutamic acid (glutamate) to lysine at residue 487 (Yoshid
a
et al., 1984). Hempel et al. (1985) and Hsu et al. (1985) also showed that
the catalytic deficiency in mitochondrial ALDH in Orientals can be traced to
a structural point mutation at amino acid position 487 of the polypeptide. A
substitution of lysine for glutamic acid results from a transition of G-C to
A-T. To study the mechanism by which the ALDH2*2 allele exerts its dominant
effect in decreasing ALDH2 activity in liver extracts and producing cutaneou
s
flushing when the subject drinks alcohol, Xiao et al. (1995) cloned ALDH2*1
cDNA and generated the ALDH2*2 allele by site-directed mutagenesis. These
cDNAs were transduced using retroviral vectors into HeLa and CV1 cells, whic
h
do not express ALDH2. The normal allele directed synthesis of immunoreactive
ALDH2 protein with the expected isoelectric point and increased aldehyde
dehydrogenase activity. The ALDH2*2 allele directed synthesis of mRNA and
immunoreactive protein, but the protein lacked enzymatic activity. When
ALDH2*1-expressing cells were transduced with ALDH2*2 vectors, both mRNAs
were expressed and immunoreactive proteins with isoelectric points ranging
between those of the 2 gene products were present, indicating that the
subunits formed heteromers. ALDH2 activity in these cells was reduced below
that of the parental ALDH2*1-expressing cells. Thus, the authors concluded
that ALDH2*2 allele is sufficient to cause ALDH2 deficiency in vitro. Xiao e
t
al. (1996) referred to the ALDH2 enzyme encoded by the ALDH2*1 allele (the
wildtype form) as ALDH2E and the enzyme subunit encoded by ALDH2*2 as ALDH2K
.
They found that the ALDH2E enzyme was very stable, with a half-life of at
least 22 hours. ALDH2K, on the other hand, had an enzyme half-life of only 1
4
hours. In cells expressing both subunits, most of the subunits assemble as
heterotetramers, and these enzymes had a half-life of 13 hours. Thus, the
effect of ALDH2K on enzyme turnover is dominant. Their studies indicated tha
t
ALDH2*2 exerts its dominant effect both by interfering with the catalytic
activity of the enzyme and by increasing its turnover. Because genetic
epidemiologic studies have suggested a mechanism by which homozygosity for
the ALDH2*2 allele inhibits the development of alcoholism in Asians, Peng et
al. (1999) recruited 18 adult Han Chinese men, matched by age, body-mass
index, nutritional state, and homozygosity at the ALDH2 gene loci from a
population of 273 men. Six individuals were chosen for each of the 3 ALDH2
allotypes, i.e., 2 homozygotes and 1 heterozygote. Following a low dose of
ethanol, homozygous ALDH2*2 individuals were found to be strikingly
responsive with pronounced cardiovascular hemodynamic effects as well as
subjective perception of general discomfort for as long as 2 hours following
ingestion. Among 71 Japanese nondrinkers and 268 drinkers of alcohol, Liu et
al. (2005) found that drinkers had a significantly higher frequency of the
487glu allele. Individuals with the 487lys allele had an increased risk of
alcohol-induced flushing (odds ratio of 33.0)
100690 .0001 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1, ASN217LYS
In a 30-year-old woman with slow-channel congenital myasthenic syndrome
(601462), Engel et al. (1996) identified a heterozygous 651C-G transversion
in exon 6 of the CHRNA1 gene, resulting in an asn217-to-lys (N217K)
substitution at a conserved residue in the M1 transmembrane domain. The
mutation cosegregated with the disease through 3 generations. Functional
expression studies showed that the N217K mutation slowed the rate of AChR
channel closure, increased the apparent affinity for ACh, and enhanced
desensitization. Cationic overload of the postsynaptic region caused an
endplate myopathy
100690 .0002 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1, VAL156MET
In a patient with SCCMS (601462), Croxen et al. (1997) identified a
heterozygous 466G-A transition in the CHRNA1 gene, resulting in a
val156-to-met (V156M) substitution in a putative ACh-binding region of the
protein. Functional studies suggested that the V156M mutation stabilizes the
open state of the AChR channel
100690 .0003 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1, THR254ILE
In a 60-year-old woman whose myasthenic syndrome (SCCMS; 601462) had first
become symptomatic at the age of 16, Croxen et al. (1997) identified a
heterozygous 761C-T transition in the CHRNA1 gene, resulting in a
thr254-to-ile (T254I) substitution in the M2 transmembrane domain which line
s
the AChR channel pore. The patient was previously reported by Chauplannaz an
d
Bady (1994). Functional expression studies suggested that the T254I mutation
stabilized the open state of the AChR channel
C:\Disease_Database_TongBoon_Dec2005_Mar
ch2006\Results>bcp OMIM.dbo.av in
AVoutp
ut_less_then_100.txt -c -T
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
BCP copy in failedGood god! Please post a description of the source data, rather than posting
a
big blob. Also see http://www.aspfaq.com/etiquette.asp?id=5006 for some
pointers.
How are you importing this? Where are you importing it to? Where is it from?
What is the structure of the source, and what of the destination?
ML
http://milambda.blogspot.com/|||What vesrion are you using?
"urgent" <urgent@.discussions.microsoft.com> wrote in message
news:89CCAC1D-8F47-4D01-A35D-25254C26BD9B@.microsoft.com...
> please help me create the table for the data in the following data lines
> after this
> i am out of my wits to thing for what data type to use. because for
> everything i tried i got the error below the column name will be for the
> table i appreciate a lot if someone can help me .
>
> column 1 column 2 column 3
> OMIM_Name No.of av Description
> 100650 .0001 ALCOHOL INTOLERANCE, ACUTE ACETALDEHYDE DEHYDROGENASE 2,
> ALLELE
> 2, INCLUDED; ALDH2*2, INCLUDED ALDH2, GLU487LYS The ALDH2*2-encoded
> protein
> has a change from glutamic acid (glutamate) to lysine at residue 487
> (Yoshida
> et al., 1984). Hempel et al. (1985) and Hsu et al. (1985) also showed that
> the catalytic deficiency in mitochondrial ALDH in Orientals can be traced
> to
> a structural point mutation at amino acid position 487 of the polypeptide.
> A
> substitution of lysine for glutamic acid results from a transition of G-C
> to
> A-T. To study the mechanism by which the ALDH2*2 allele exerts its
> dominant
> effect in decreasing ALDH2 activity in liver extracts and producing
> cutaneous
> flushing when the subject drinks alcohol, Xiao et al. (1995) cloned
> ALDH2*1
> cDNA and generated the ALDH2*2 allele by site-directed mutagenesis. These
> cDNAs were transduced using retroviral vectors into HeLa and CV1 cells,
> which
> do not express ALDH2. The normal allele directed synthesis of
> immunoreactive
> ALDH2 protein with the expected isoelectric point and increased aldehyde
> dehydrogenase activity. The ALDH2*2 allele directed synthesis of mRNA and
> immunoreactive protein, but the protein lacked enzymatic activity. When
> ALDH2*1-expressing cells were transduced with ALDH2*2 vectors, both mRNAs
> were expressed and immunoreactive proteins with isoelectric points ranging
> between those of the 2 gene products were present, indicating that the
> subunits formed heteromers. ALDH2 activity in these cells was reduced
> below
> that of the parental ALDH2*1-expressing cells. Thus, the authors concluded
> that ALDH2*2 allele is sufficient to cause ALDH2 deficiency in vitro. Xiao
> et
> al. (1996) referred to the ALDH2 enzyme encoded by the ALDH2*1 allele (the
> wildtype form) as ALDH2E and the enzyme subunit encoded by ALDH2*2 as
> ALDH2K.
> They found that the ALDH2E enzyme was very stable, with a half-life of at
> least 22 hours. ALDH2K, on the other hand, had an enzyme half-life of only
> 14
> hours. In cells expressing both subunits, most of the subunits assemble as
> heterotetramers, and these enzymes had a half-life of 13 hours. Thus, the
> effect of ALDH2K on enzyme turnover is dominant. Their studies indicated
> that
> ALDH2*2 exerts its dominant effect both by interfering with the catalytic
> activity of the enzyme and by increasing its turnover. Because genetic
> epidemiologic studies have suggested a mechanism by which homozygosity for
> the ALDH2*2 allele inhibits the development of alcoholism in Asians, Peng
> et
> al. (1999) recruited 18 adult Han Chinese men, matched by age, body-mass
> index, nutritional state, and homozygosity at the ALDH2 gene loci from a
> population of 273 men. Six individuals were chosen for each of the 3 ALDH2
> allotypes, i.e., 2 homozygotes and 1 heterozygote. Following a low dose of
> ethanol, homozygous ALDH2*2 individuals were found to be strikingly
> responsive with pronounced cardiovascular hemodynamic effects as well as
> subjective perception of general discomfort for as long as 2 hours
> following
> ingestion. Among 71 Japanese nondrinkers and 268 drinkers of alcohol, Liu
> et
> al. (2005) found that drinkers had a significantly higher frequency of the
> 487glu allele. Individuals with the 487lys allele had an increased risk of
> alcohol-induced flushing (odds ratio of 33.0)
> 100690 .0001 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1,
> ASN217LYS
> In a 30-year-old woman with slow-channel congenital myasthenic syndrome
> (601462), Engel et al. (1996) identified a heterozygous 651C-G
> transversion
> in exon 6 of the CHRNA1 gene, resulting in an asn217-to-lys (N217K)
> substitution at a conserved residue in the M1 transmembrane domain. The
> mutation cosegregated with the disease through 3 generations. Functional
> expression studies showed that the N217K mutation slowed the rate of AChR
> channel closure, increased the apparent affinity for ACh, and enhanced
> desensitization. Cationic overload of the postsynaptic region caused an
> endplate myopathy
> 100690 .0002 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1,
> VAL156MET
> In a patient with SCCMS (601462), Croxen et al. (1997) identified a
> heterozygous 466G-A transition in the CHRNA1 gene, resulting in a
> val156-to-met (V156M) substitution in a putative ACh-binding region of the
> protein. Functional studies suggested that the V156M mutation stabilizes
> the
> open state of the AChR channel
> 100690 .0003 MYASTHENIC SYNDROME, CONGENITAL, SLOW-CHANNEL CHRNA1,
> THR254ILE
> In a 60-year-old woman whose myasthenic syndrome (SCCMS; 601462) had first
> become symptomatic at the age of 16, Croxen et al. (1997) identified a
> heterozygous 761C-T transition in the CHRNA1 gene, resulting in a
> thr254-to-ile (T254I) substitution in the M2 transmembrane domain which
> lines
> the AChR channel pore. The patient was previously reported by Chauplannaz
> and
> Bady (1994). Functional expression studies suggested that the T254I
> mutation
> stabilized the open state of the AChR channel
>
> C:\Disease_Database_TongBoon_Dec2005_Mar
ch2006\Results>bcp OMIM.dbo.av in
> AVoutp
> ut_less_then_100.txt -c -T
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][SQL Native Client]String data, right truncation
> BCP copy in failed
>|||
this is my new nick i am urgen log in with another email thi post is being
transfer to another title call trouble with bcp ....2005
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Friday, March 30, 2012
help!! Filter !! Somone has to know!
Am I just not explaining myself or does nobody use SSRS 2005 or if so not create very complex filtering requirements out there yet? Can I get some responses hopefully? I can't find anything out on the net about how to do what I'm trying to do here:
HERE IS A PRINT SCREEN OF THE FILTER TAB WHERE i WANT THIS ALL TO HAPPEN...PROPERTIES OF MY TABLE:
http://www.photopizzaz.biz/filtertab_ssrs2005table.jpg
essentially I want this check to filter records on my table...match to this criteria from my dataset:
In SSRS 2005, how can I add this check (what do I have to put in the Expression, operator, and value in filters tab of table properties) to my table filter to bringin only customers that match one of the 2 main OR statements?
essentially I want this check to filter records on my table...match to this criteria from my dataset:
(Fields!Branch.Value = '00002' and
Fields!CustomerNumber.Value = '0000002' or
Fields!CustomerNumber.Value = '0000003' or
Fields!CustomerNumber.Value = '0000004' or
Fields!CustomerNumber.Value = '0000155' or
Fields!CustomerNumber.Value = '0000156' or
Fields!CustomerNumber.Value = '0000159' or
Fields!CustomerNumber.Value = '0000160' or
Fields!CustomerNumber.Value = '0000161' or
Fields!CustomerNumber.Value = '0000118' or
Fields!CustomerNumber.Value = '0000153' or
Fields!CustomerNumber.Value = '0000152' or
Fields!CustomerNumber.Value = '0000108' or
Fields!CustomerNumber.Value = '0000158' or
Fields!CustomerNumber.Value = '0000133')
OR
(Fields!Branch.Value <> '00002' and
Fields!CustomerNumber.Value = '0000053' or
Fields!CustomerNumber.Value = '0000058' or
Fields!CustomerNumber.Value = '0000072' or
Fields!CustomerNumber.Value = '0000073' or
Fields!CustomerNumber.Value = '0000079' or
Fields!CustomerNumber.Value = '0000080' or
Fields!CustomerNumber.Value = '0000143' or
Fields!CustomerNumber.Value = '0000146' or
Fields!CustomerNumber.Value = '0000157' or
Fields!CustomerNumber.Value = '0000135')
See the post from Robert Bruckner (MSFT) titled Answer Re: And/or filter field not enabled in the group filter tab: http://forums.microsoft.com/msdn/showpost.aspx?postid=227116
This post was very helpful to me in building some complex "and/or" filtering on tables and groups, describing what values to put in the expression, type, and value fields.
Good luck...
-Chris
Wednesday, March 28, 2012
Help! Trying to create a trigger in SQL 2000...
Hello All!
I am trying to create a trigger that upon insertion into my table an email will be sent to that that recipeinent with a image attached ( like a coupon)That comes from a different table, problem is, It will not allow me to send the email ( using xp_sendmail) with the coupon attached. I am using varbinary for the coupon and nvarchar for the rest to be sent, I get an error that Invaild operator for data type. operator equals add, type equals varchar.
Looks basically like this(This is my test tables):
CREATE TRIGGER EileenTest ON OrgCouponTestMain
FOR Insert
AS
declare @.emailaddress varchar(50)
declare @.body varchar(300)
declare @.fname varchar(50)
declare @.coupon varbinary(4000)
if update(emailaddress)
begin
Select
@.emailaddress=(select EmailAddress from OrgCouponTestMain as str),
@.fname=(select EmailAddress from OrgCouponTestMain as str)
@.Coupon=(select OrgCoupon1 from OrgCouponTest2 as image)
SET @.body= 'Thank you' +' '+ @.fname +' '+ ',Here is the coupon you requested' +' ' + @.coupon
exec master.dbo.xp_sendmail
@.recipients = @.emailaddress,
@.subject = 'Coupon',
@.message = @.body
END
Hello my friend,
Try the following: -
SET @.body = 'Thank you ' + @.fname + ', Here is the coupon you requested ' + CAST(@.coupon AS VARCHAR(8000))
Kind regards
Scotty
|||
Hi there,
I've recently developed a functionality at my work place that is very similar to the one you need.
My suggestion is that you make things other way (very much simpler i think):
Instead of coding in T-SQL, you can develop your application in any .NET language. I suggest you to develop a Web Service or an ASP.NET page that retrieves the "destinations" from the SQL Server, loads the coupon and sends it to them.
Then you just have to develop a very simple COM Class that the ONLY thing it does is calling the Web Service (our ASP.NET page) you developed. There is many documentation on the Web on how to call a COM Class from an SQL Trigger / Job.
It's a bit tricky at the beggining but when you figure it out, it's easy as a walk in the park. And remember, when you need to change your application, you just have to change the .NET Web Service that it's called by the COM Class (much more friendly than coding in T-SQL).
Even if this isn't a web server, you can always create a website on localhost to host the service/page.
Hope I've helped you out!
gonzzas
|||
Thank you both for your input, Ask scotty, it didn't work, got the email out but no image, and of course there is that other option, which unless i can figure this out i will be doing instead. Thank you!
|||So anyone else have an idea? This is what comes up in the email message, notice the symbols instead of the image...
Thank you Eileen, Here is the coupon you requested /
|||Anyone else have a suggestion? Here is what comes in the email , notice the symbols instead of the image:
Thank you Eileen, Here is the coupon you requested /
|||Hi,
The problem is focused on how to display the image which saved as Image Data type in database. Because what you get from database directly is binary steam of the image, it can't be displayed in your email directly. What I suggest is pass the steam (and other information ) from Stored Procedure to .NET, and use SqlDataReader to read the image and send the mail to your customer.
HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or image" ?https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=531518&SiteID=1
Hope it can helps. Thanks.
sqlHelp! this is so basic for some of you!
HI! i am a college student and i need help with my PL/SQL project. we have to create a package with subprograms (1 private and 1 public).
the public function will accept a SSN as a parameter and return a formatted SSN (###-##-####) or an error message that identifies what test failed and why the SSN was not good, no testing will occur in the public function.
the public function will call a private function/procedure in the same package. the private program unit will test a parameter that is passed in by the public function:
--the parameter passed in may not contain any alphas
--may not contain all the same numbers (111-11-1111)
--will accept only the following values as good:
123456789
123-45-6789
123-456789
12345-6789
create a table called Test_SSN with one column (varchar2(11)). create a database trigger that calls your packaged function to test the SSN before inserting it in the table.
If you have ANY suggestions, PLEASE RESPOND!!! My teacher gave me a hint to use counter:=counter + 1. HELP HELP HELP!!!Post what you have managed to do yourself, and people can offer suggestions and corrections. There is no educational value in subcontracting your homework to others!|||Sorry, i wasnt trying to get someone to do it for me, ok, i have made a couple of tests, they are anonymous blocks right now, but they will be part of my functions in my package..
DECLARE
SSN VARCHAR(100) := '123-45-6789';
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN MUST BE BETWEEN 9 AND 11 CHARACTERS');
ELSE
IF LENGTH(SSN) = 11 AND
SUBSTR(SSN, '-') = 4 AND
SUBSTR(SSN, '-',2) =7 THEN
DSMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH (SSN) = 10 AND
SUBSTR(SSN, '-') = 4 OR
SUBSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE RAISE_APPLICATION_ERROR(-20001,'HYPHEN ENTERED
INCORRECTLY');
END IF;
END IF;
END IF;
END IF;
END;
/
To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!|||Originally posted by oraculous
To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!
The TRANSLATE function will be helpful here:
TRANSLATE( ssn, 'x0123456789-', 'x' )
This will translate all digits and '-' to NULL, leaving behind any other (invalid) characters. So if the result is NOT NULL that means there were some invalid characters in the string.
The 'x' is just there as a dummy, to prevent the 3rd argument being '', which doesn't work. It gets translated to 'x', so doesn't affect the result.
You could test and display the invalid chars like this:
v_invalid VARCHAR2(11);
...
v_invalid := TRANSLATE( ssn, 'x0123456789-', 'x' );
if v_invalid is not null then
raise_application_error(-20001,'Invalid chars in SSN: '||v_invalid);
end if;|||thank you so much!! i didnt even think about translate...you are a great help...it is appreciated!!|||Does anyone know how i can use a loop if i use something like TEMP_SSN VARCHAR2 := SUBSTR(SSN,1,3)||SUBSTR(SSN,5,2)||SUBSTR(SSN,8,4) and then use a counter:=counter + 1...??
does anyone know how i could use this or if it would work here instead of using all those [SUBSTR(SSN,1) <> '-' AND].....???
CREATE PROCEDURE SSN_PROC (SSN VARCHAR2)
-- SSN VARCHAR(100) := '123-45-6789';
SSN_INVALID VARCHAR2 (11):=TRANSLATE(SSN,'x0123456789-','x');
TEMP_SSN VARCHAR2;
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN Must Be Between 9 And 11 Characters');
ELSE
IF LENGTH(SSN) = 11 AND
INSTR(SSN, '-') = 4 AND
INSTR(SSN, '-',1,2) =7 AND
SUBSTR(SSN,1) <> '-' AND
SUBSTR(SSN,2) <> '-' AND
SUBSTR(SSN,3) <> '-' AND
SUBSTR(SSN,5) <> '-' AND
SUBSTR(SSN,6) <> '-' AND
SUBSTR(SSN,8) <> '-' AND
SUBSTR(SSN,9) <> '-' AND
SUBSTR(SSN,10) <> '-' AND
SUBSTR(SSN,11) <> '-' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH (SSN) = 10 AND
INSTR(SSN, '-') = 4 OR
INSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
IF SSN_INVALID IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid Characters In SSN:'||SSN_INVALID);
END IF;
END IF;
END IF;
END IF;
END IF;
END;
/|||You can use a FOR loop:
FOR i IN 1..11 LOOP
IF i NOT IN (4,7) AND SUBSTR(SSN,i,1) = '-' THEN
RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
END IF;
END LOOP;
Note the 3rd parameter to SUBSTR, i.e. the length of the substring required. You had SUBSTR(SSN,1) which is the substring from 1 to end of SSN, i.e. is equal to SSN.|||oooOOHH thanks man, you are a great help!!sql
the public function will accept a SSN as a parameter and return a formatted SSN (###-##-####) or an error message that identifies what test failed and why the SSN was not good, no testing will occur in the public function.
the public function will call a private function/procedure in the same package. the private program unit will test a parameter that is passed in by the public function:
--the parameter passed in may not contain any alphas
--may not contain all the same numbers (111-11-1111)
--will accept only the following values as good:
123456789
123-45-6789
123-456789
12345-6789
create a table called Test_SSN with one column (varchar2(11)). create a database trigger that calls your packaged function to test the SSN before inserting it in the table.
If you have ANY suggestions, PLEASE RESPOND!!! My teacher gave me a hint to use counter:=counter + 1. HELP HELP HELP!!!Post what you have managed to do yourself, and people can offer suggestions and corrections. There is no educational value in subcontracting your homework to others!|||Sorry, i wasnt trying to get someone to do it for me, ok, i have made a couple of tests, they are anonymous blocks right now, but they will be part of my functions in my package..
DECLARE
SSN VARCHAR(100) := '123-45-6789';
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN MUST BE BETWEEN 9 AND 11 CHARACTERS');
ELSE
IF LENGTH(SSN) = 11 AND
SUBSTR(SSN, '-') = 4 AND
SUBSTR(SSN, '-',2) =7 THEN
DSMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH (SSN) = 10 AND
SUBSTR(SSN, '-') = 4 OR
SUBSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE RAISE_APPLICATION_ERROR(-20001,'HYPHEN ENTERED
INCORRECTLY');
END IF;
END IF;
END IF;
END IF;
END;
/
To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!|||Originally posted by oraculous
To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!
The TRANSLATE function will be helpful here:
TRANSLATE( ssn, 'x0123456789-', 'x' )
This will translate all digits and '-' to NULL, leaving behind any other (invalid) characters. So if the result is NOT NULL that means there were some invalid characters in the string.
The 'x' is just there as a dummy, to prevent the 3rd argument being '', which doesn't work. It gets translated to 'x', so doesn't affect the result.
You could test and display the invalid chars like this:
v_invalid VARCHAR2(11);
...
v_invalid := TRANSLATE( ssn, 'x0123456789-', 'x' );
if v_invalid is not null then
raise_application_error(-20001,'Invalid chars in SSN: '||v_invalid);
end if;|||thank you so much!! i didnt even think about translate...you are a great help...it is appreciated!!|||Does anyone know how i can use a loop if i use something like TEMP_SSN VARCHAR2 := SUBSTR(SSN,1,3)||SUBSTR(SSN,5,2)||SUBSTR(SSN,8,4) and then use a counter:=counter + 1...??
does anyone know how i could use this or if it would work here instead of using all those [SUBSTR(SSN,1) <> '-' AND].....???
CREATE PROCEDURE SSN_PROC (SSN VARCHAR2)
-- SSN VARCHAR(100) := '123-45-6789';
SSN_INVALID VARCHAR2 (11):=TRANSLATE(SSN,'x0123456789-','x');
TEMP_SSN VARCHAR2;
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN Must Be Between 9 And 11 Characters');
ELSE
IF LENGTH(SSN) = 11 AND
INSTR(SSN, '-') = 4 AND
INSTR(SSN, '-',1,2) =7 AND
SUBSTR(SSN,1) <> '-' AND
SUBSTR(SSN,2) <> '-' AND
SUBSTR(SSN,3) <> '-' AND
SUBSTR(SSN,5) <> '-' AND
SUBSTR(SSN,6) <> '-' AND
SUBSTR(SSN,8) <> '-' AND
SUBSTR(SSN,9) <> '-' AND
SUBSTR(SSN,10) <> '-' AND
SUBSTR(SSN,11) <> '-' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH (SSN) = 10 AND
INSTR(SSN, '-') = 4 OR
INSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
IF SSN_INVALID IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid Characters In SSN:'||SSN_INVALID);
END IF;
END IF;
END IF;
END IF;
END IF;
END;
/|||You can use a FOR loop:
FOR i IN 1..11 LOOP
IF i NOT IN (4,7) AND SUBSTR(SSN,i,1) = '-' THEN
RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
END IF;
END LOOP;
Note the 3rd parameter to SUBSTR, i.e. the length of the substring required. You had SUBSTR(SSN,1) which is the substring from 1 to end of SSN, i.e. is equal to SSN.|||oooOOHH thanks man, you are a great help!!sql
Friday, March 23, 2012
help! question about hardware request for SQL Server and Analysis Services
I built a database with a huge table which has 18 billion lines for 6(or 7) columns.
And in the Analysis Services the table is used to create a dimention and a measure.
One of the other two dimentions is made form a 4000 line table,and the other is
made from a 3 million line table(time dimension table).
The specification of my Server is Windows Server 2003 with Xeon Intel Cpu 5160 @.3.00GHz,
2.99GHz,4.00G RAM.
The problem comes out when i start the processing of Analysis Services.
the "time out" error comes out after 1 hour, however, the performance i need
is completing the Processing for one cube within 10 munites.
I want to know the hardware request to reach my needs and how to speed the
processing of Analysis Services.
Thank you
So, to be clear, you have an 18 billion row fact table, you have built a cube on top of it and you want a full process to complete in 10 minutes?
Chris
|||Dear Mr
yes! I wanna a full process within 10 minutes.
Can it be done?
thanks in advance
tomigisi
|||The short answer is no, not if you're using MOLAP storage for your measure group. The best throughput I've ever seen for cube processing with MOLAP storage is around 200000 rows per second, and that was a maximum rather than something that could be consistently achieved throughout the whole process; you should be happy with 60-70000 rows per second if you've got a top-end server and a properly-tuned relational datasource.
I would experiment with using ROLAP or HOLAP storage, or with pre-aggregating your fact table in some way to reduce the overall number of rows.
Sorry,
Chris
|||We have a monster server: 8 - 3.2GHZ processors, a disk frame that reads/writes 400 MB/sec, 32 GB of ram, etc and we couldn't even process 18 Billion rows that fast. The slowdown is reading rows from the DB, and most likely not the server (unless you have slow disks).
sqlMonday, March 19, 2012
Help! How do I create an SQL database on my machine? + Access -> SQL ?
According to my technical support for web hosting, for database format they only use "MS SQL" operating on a windows system, usualy clients create they're sites on their local machines using "MS SQL".
I'm just wondering....
Is 'MS SQL' free? where can I get it?
I also have the option of creating the site round an access databse but I'm worried about the security issues,
do you think it would be wise to use access?
Is it easy to convert a site written in ASP.NET&ACCESS into a site using ASP.NET&"ms sql"?
because then I could code a site in the former on my machine and then change the code to link to an sql server.when my suport persons talking about "MS SQL" does he actually mean "msde"?
I've been having some trouble with that too:
http://www.gotdotnet.com/Community/MessageBoard/Thread.aspx?id=181961&Page=1#183129|||'MS SQL Server' is not free. The good news are that MS has released a scaled down version of MS Sql Server which is. It's called MSDE (MS Desktop Edition) and is ideal for developing on your local machine, as well as OK for low traffic production servers. Download ithere.|||thanks for the reply andre,
that last post went up before I read your replied,
MSDE wont install because it says I need a strong SA password but I have no idea how to set one up for my account.|||more info, I think i downloaded the wrong package file before "sql2kdesksp3" I'm now downloading sql2ksp3 is that the right onw? is the other stuff I need to download to?|||downloading msde2000a now aswell|||its still having the same SAPWD option,
Is there a detailed explanation anywhere on using the SAPWD switch or have I goy my wires crossed.
Monday, March 12, 2012
Help! Dates and SQL
Hi
I would like to create a SP where it will populate TableA based from TableB.
TableB will be populated on a monthly basis using a DTS and within that I
would like to run the SP to populate TableA.
I would like to create a SP where it will populate TableA based from TableB.
TableB will be populated on a monthly basis using a DTS and within that I
would like to run the SP to populate TableA.
Can someone here please help me create the sql statements as a starting
point.
TIA!
Bob
TableB (source)
from_date to_date curr_code ex_rate
1/1/2004 1/10/2004 CAD .75000
1/11/2004 1/16/2004 CAD .74321
1/17/2004 2/4/2004 CAD .72222
2/5/2004 2/20/2004 CAD .71111
2/21/2004 2/28/2004 CAD .77888
3/1/2004 3/3/2004 CAD .79002
3/4/2004 3/14/2004 CAD .76803
3/15/2004 3/23/2004 CAD .70022
3/24/2004 4/2/2004 CAD .73365
etc...
TableA (destination):
date curr_code ex_rate
1/2004 CAD 0.738477 calculation:(.75000+
..74321+.72222) / 3
2/2004 CAD 0.737403
(.72222+.71111+.77888) / 3
3/2004 CAD 0.74798
(.79002+.76803+.70022+.73365) / 4
etc.."B" <no_spam@.no_spam.com> wrote in message
news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
> Hi
> I would like to create a SP where it will populate TableA based from
> TableB.
> TableB will be populated on a monthly basis using a DTS and within that I
> would like to run the SP to populate TableA.
> Can someone here please help me create the sql statements as a starting
> point.
> TIA!
> Bob
>
> TableB (source)
> from_date to_date curr_code ex_rate
> 1/1/2004 1/10/2004 CAD .75000
> 1/11/2004 1/16/2004 CAD .74321
> 1/17/2004 2/4/2004 CAD .72222
> 2/5/2004 2/20/2004 CAD .71111
> 2/21/2004 2/28/2004 CAD .77888
> 3/1/2004 3/3/2004 CAD .79002
> 3/4/2004 3/14/2004 CAD .76803
> 3/15/2004 3/23/2004 CAD .70022
> 3/24/2004 4/2/2004 CAD .73365
> etc...
> TableA (destination):
> date curr_code ex_rate
> 1/2004 CAD 0.738477 calculation:(.75000+
> .74321+.72222) / 3
> 2/2004 CAD 0.737403
> (.72222+.71111+.77888) / 3
> 3/2004 CAD 0.74798
> (.79002+.76803+.70022+.73365) / 4
> etc..
>
Here's one possible solution. In future, please post CREATE TABLE and INSERT
statements for your tables and data - other people can then simply cut and
paste into Query Analyzer, and we don't have to guess about data types,
keys, constraints etc.:
http://www.aspfaq.com/etiquette.asp?id=5006
Note that 'date' is a reserved keyword, so you should avoid using it as a
column name - I've used start_of_month instead. See "Reserved Keywords" in
Books Online.
Simon
create table b (
from_date datetime not null,
to_date datetime not null,
curr_code char(3) not null,
ex_rate decimal(6,5) not null,
constraint pk_b primary key (from_date, to_date, curr_code)
)
go
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040101', '20040110', 'CAD', 0.75000)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040111', '20040116', 'CAD', 0.74321)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040117', '20040204', 'CAD', 0.72222)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040205', '20040220', 'CAD', 0.71111)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040221', '20040228', 'CAD', 0.77888)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040301', '20040303', 'CAD', 0.79002)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040304', '20040314', 'CAD', 0.76803)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040315', '20040323', 'CAD', 0.70022)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040324', '20040402', 'CAD', 0.73365)
go
select dt.start_of_month, max(b.curr_code) as 'curr_code', avg(b.ex_rate) as
'ex_rate'
from
(
select cast(convert(char(6), from_date, 112) + '01' as datetime) as
'start_of_month', count(*) as 'num'
from b
group by cast(convert(char(6), from_date, 112) + '01' as datetime)
) dt
join b
on dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
datetime)
or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
datetime)
group by dt.start_of_month
go
drop table b
go|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d1418$1_1@.news.bluewin.ch...
> "B" <no_spam@.no_spam.com> wrote in message
> news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
>> Hi
>> I would like to create a SP where it will populate TableA based from
>> TableB.
>> TableB will be populated on a monthly basis using a DTS and within that I
>> would like to run the SP to populate TableA.
>>
>> Can someone here please help me create the sql statements as a starting
>> point.
>>
>> TIA!
>> Bob
>>
>>
>> TableB (source)
>> from_date to_date curr_code ex_rate
>> 1/1/2004 1/10/2004 CAD .75000
>> 1/11/2004 1/16/2004 CAD .74321
>> 1/17/2004 2/4/2004 CAD .72222
>> 2/5/2004 2/20/2004 CAD .71111
>> 2/21/2004 2/28/2004 CAD .77888
>> 3/1/2004 3/3/2004 CAD .79002
>> 3/4/2004 3/14/2004 CAD .76803
>> 3/15/2004 3/23/2004 CAD .70022
>> 3/24/2004 4/2/2004 CAD .73365
>> etc...
>>
>> TableA (destination):
>> date curr_code ex_rate
>> 1/2004 CAD 0.738477 calculation:(.75000+
>> .74321+.72222) / 3
>> 2/2004 CAD 0.737403
>> (.72222+.71111+.77888) / 3
>> 3/2004 CAD 0.74798
>> (.79002+.76803+.70022+.73365) / 4
>> etc..
>>
>>
>>
>>
> Here's one possible solution. In future, please post CREATE TABLE and
> INSERT statements for your tables and data - other people can then simply
> cut and paste into Query Analyzer, and we don't have to guess about data
> types, keys, constraints etc.:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Note that 'date' is a reserved keyword, so you should avoid using it as a
> column name - I've used start_of_month instead. See "Reserved Keywords" in
> Books Online.
> Simon
> create table b (
> from_date datetime not null,
> to_date datetime not null,
> curr_code char(3) not null,
> ex_rate decimal(6,5) not null,
> constraint pk_b primary key (from_date, to_date, curr_code)
> )
> go
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040101', '20040110', 'CAD', 0.75000)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040111', '20040116', 'CAD', 0.74321)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040117', '20040204', 'CAD', 0.72222)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040205', '20040220', 'CAD', 0.71111)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040221', '20040228', 'CAD', 0.77888)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040301', '20040303', 'CAD', 0.79002)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040304', '20040314', 'CAD', 0.76803)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040315', '20040323', 'CAD', 0.70022)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040324', '20040402', 'CAD', 0.73365)
> go
<snip
Oops - the query I posted before won't handle multiple currencies correctly.
This version should.
select dt.start_of_month, dt.curr_code, avg(b.ex_rate) as 'ex_rate'
from
(
select cast(convert(char(6), from_date, 112) + '01' as datetime) as
'start_of_month', curr_code, count(*) as 'num'
from b
group by cast(convert(char(6), from_date, 112) + '01' as datetime),
curr_code
) dt
join b
on dt.curr_code = b.curr_code and
(
dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
datetime)
or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
datetime)
)
group by dt.start_of_month, dt.curr_code
Simon|||The solution you recently posted is exactly what I am looking for.
And I am aware that "date" is a reserved word, i quickly sent the original
post out of haste.
Many thanks for your time!
Bob
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d1418$1_1@.news.bluewin.ch...
> "B" <no_spam@.no_spam.com> wrote in message
> news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
> > Hi
> > I would like to create a SP where it will populate TableA based from
> > TableB.
> > TableB will be populated on a monthly basis using a DTS and within that
I
> > would like to run the SP to populate TableA.
> > Can someone here please help me create the sql statements as a starting
> > point.
> > TIA!
> > Bob
> > TableB (source)
> > from_date to_date curr_code ex_rate
> > 1/1/2004 1/10/2004 CAD .75000
> > 1/11/2004 1/16/2004 CAD .74321
> > 1/17/2004 2/4/2004 CAD .72222
> > 2/5/2004 2/20/2004 CAD .71111
> > 2/21/2004 2/28/2004 CAD .77888
> > 3/1/2004 3/3/2004 CAD .79002
> > 3/4/2004 3/14/2004 CAD .76803
> > 3/15/2004 3/23/2004 CAD .70022
> > 3/24/2004 4/2/2004 CAD .73365
> > etc...
> > TableA (destination):
> > date curr_code ex_rate
> > 1/2004 CAD 0.738477 calculation:(.75000+
> > .74321+.72222) / 3
> > 2/2004 CAD 0.737403
> > (.72222+.71111+.77888) / 3
> > 3/2004 CAD 0.74798
> > (.79002+.76803+.70022+.73365) / 4
> > etc..
> Here's one possible solution. In future, please post CREATE TABLE and
INSERT
> statements for your tables and data - other people can then simply cut and
> paste into Query Analyzer, and we don't have to guess about data types,
> keys, constraints etc.:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Note that 'date' is a reserved keyword, so you should avoid using it as a
> column name - I've used start_of_month instead. See "Reserved Keywords" in
> Books Online.
> Simon
> create table b (
> from_date datetime not null,
> to_date datetime not null,
> curr_code char(3) not null,
> ex_rate decimal(6,5) not null,
> constraint pk_b primary key (from_date, to_date, curr_code)
> )
> go
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040101', '20040110', 'CAD', 0.75000)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040111', '20040116', 'CAD', 0.74321)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040117', '20040204', 'CAD', 0.72222)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040205', '20040220', 'CAD', 0.71111)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040221', '20040228', 'CAD', 0.77888)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040301', '20040303', 'CAD', 0.79002)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040304', '20040314', 'CAD', 0.76803)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040315', '20040323', 'CAD', 0.70022)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040324', '20040402', 'CAD', 0.73365)
> go
> select dt.start_of_month, max(b.curr_code) as 'curr_code', avg(b.ex_rate)
as
> 'ex_rate'
> from
> (
> select cast(convert(char(6), from_date, 112) + '01' as datetime) as
> 'start_of_month', count(*) as 'num'
> from b
> group by cast(convert(char(6), from_date, 112) + '01' as datetime)
> ) dt
> join b
> on dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
> datetime)
> or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
> datetime)
> group by dt.start_of_month
> go
> drop table b
> go
Help! Creating Custom Data Flow Task
I'm trying to create a custom data flow destination, and it has a custom property that needs to get value from variable(similar to the FileNameVariable property of Raw File Destination), how can I do that?
Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.
What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?
You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.
|||
DarrenSQLIS wrote:
Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.
What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?
You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.
So provide a custom IDTSCustomProperty90 in ProvideComponentProperties() and let users type in the variable name is enough(of course, I should also follow what you said)?
I wanted to make it behave the same way as the FileNameVariable property of Raw File Destination, that is: users don't type in the variable name, instead, they select from all available variable names.
|||
Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.
What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?
You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.
How can I get the matching variable and it's value at runtime? And generally in which method should I do that? Thanks.
|||
Here's a sample from a component I developed to raise custom events from a package's control flow; the same basic technique should work for you in data flow as well.
First, create a property that stores the variable name. This will be set by the package developer at design time.
Code Snippet
private string variableName;
///
/// This property gets or sets the name of the variable in which the
/// event text is stored.
///
[Browsable(true)]
[Category("Custom")]
[Description("Gets or sets the name of the variable in which the event text is stored.")]
public string VariableName
{
get
{
return variableName;
}
set
{
if (value.IndexOf("::") == -1)
{
variableName = "User::" + value;
}
else
{
variableName = value;
}
}
}
(The set accessor for the property is simply explicitly adding the User namespace to the specified variable name unless a namespace is already specified. I've yet to add a drop-down so the package developer can pick from a list of available variables.)
Then, you can use the component's VariableDispenser object (which is passed in a parameter to many of the methods you overload when developing your component) to lock and work with the package variable identified by the property. Here's a sample from the Validate method in my component:
Code Snippet
Variables vars = null;
DTSExecResult result;
variableDispenser.LockOneForRead(variableName, ref vars);
if (vars[variableName].DataType != TypeCode.String)
{
// The variable is accessable, but is not the correct data type
componentEvents.FireError(0, SubComponent(variableDispenser),
string.Format("Variable '{0}' is not a String variable", variableName), "", 0);
result = DTSExecResult.Failure;
}
else
{
result = DTSExecResult.Success;
}
vars.Unlock();
return result;
The key thing here is that you're using the variableDispenser to lock the specific variable identified by the component's property, working with it, and then unlocking it when you're done.
(The SubComponent method which is referenced in the code sample above is just a little helper routine that returns a string in a standard format to identify where an error or event is coming from. I need this type of string in many places, so I have a function that can be reused, but it's not really relevant to your question...)
Hope this helps!
Help! Create index with substring
Good day!
We had decided to migrate from Oracle to SQL Server, so faced some problems. Using Oracle we could create indexes like that
create index obj_id_cnum on obj_id (substr(cnum,1,2))
But Microsoft SQL Server doesn't allow this code. How can we do the same using SQL Server. Thanks you.
dynamic sql...
declare @.SQL nvarchar(100)
set @.SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')'
exec sp_executesql @.SQL
|||Is obj_id a table, I am assuming? If so, then you are right, you cannot apply an index on a function directly. Two ways you can approach this:
1. If using enterprise edition, you can index a view and it will be used:
create view obj_id_indexed
as
select obj_id_key, cnum, substring(cnum,1,2) as cnumSub,
from obj_id
create unique clustered index on obj_id_cnum(obj_id_key, cnumSub)
Now your queries will see this index on the the view and apply it just like the
2. You can however add a computed column to your table and then index it:
alter table obj_id
add cnumSub as substring(cnum,1,2)
I would suggest that all of these techniques are probably the "wrong" way to go about this sort of thing. Almost any time you need to use a substring on a value in a SQL table there is a problem with normalization. Better would be to break the column into two parts, and then you have a much better chance of making the indexes work for you.
|||Thanks you very much! I do appreciate your help!|||Derek Comingore - RSC wrote:
dynamic sql...
declare @.SQL nvarchar(100)
set @.SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')'
exec sp_executesql @.SQL
set @.SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')' gives an error 'Invalid column name 'cnum'.
|||Note that we are considering adding function / expression based indexes for a future version of SQL Server. For now, the easiest workaround is to use an indexed computed column.Friday, March 9, 2012
HELP! Cannot pass GUID's through variables?
I have a project that uses GUID's througout and I'm completely stumped.
1) I create a "batch" GUID to batch the records I'm about to process.
2) I call a web service on a remote machine, and reserve the batch records by inserting the batch GUID into a string works fine
3)I call another web service that returns the rows that I just reserved as XML objects and insert into a string variable
4)I need to use the "batch"GUID variable which is typed as a string (DT_WSTR) as an added column so in a Data Flow Task I do the following:
a) use the XML string variable as the source of a XML Source Task -- works (now that I'm passing custom objects and not a dataset -- curious as to why I can't consume a dataset but thats a different question)
This is where things get tricky:
I've tried to add the BatchGUID as a derived column, as a datatype of DT_WSTR (unicode string) and convert it to uniqueidentifier in the Data Conversion task error, cannot convert unicode to uniqueidentifier (I know I can in C# and SQL Server so why not here).
I've tried to CAST the BatchGUID as a uniqueidentifier and pass that to the datasource -- again conversion error.
I've tried using the type Object and Casting to anything and that doesn't work either.
I've tried to pass the unicode all the way to the SQL Server Destination -- and insert into UniqueIdentifier field... again no go.
All help would be appreciated, at this point I can't see any way of using a UniqueIdentifier as a key field, and maintaining it through the package...
Is this a bug?
Oh and if you want to have some real fun, try returning the type of UniqueIdentifier as an output parameter using a ADO.NET connection.
Thanks!
Maybe a parameterized insert using the SQL Task and a property expression on the query to exchange the value of the GUID ID variable into the insert statement? Have you tried that?
Kirk Haselden
Author "SQL Server Integration Services"
Could you elaborate more on how to implement the property expression in the query? I am doing exqactly as you said using a SQL task and trying to insert into custom logging tables via a sp call. I am using parameter mapping to map system variables to parameters in my sql call, however in my sp I am defining the guids as uniqueidentifiers but ssis system variable guids are strings. What is the easiest way to handle this and convert so it works?
Thanks!
|||There's some examples here:
Using dynamic SQL in an OLE DB Source component
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
Setting Expressions
http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx
Dynamic modification of SSIS packages
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx
-Jamie
Wednesday, March 7, 2012
Help! "Remove files older than 1 week" is not there!
Help!
When I use the Enterprise Manager and create a Database Maintenance Plan
there is an option which will allow me to "Remove files older than 'X'
days".
The only problem is that the drop-down box that is supposed to let me choose
"days, hours, weeks" etc is completely blank! This means that this function
is unavailable to me, thus my hard drive keeps filling up! Please help!
How can I fix this?
I have included a screen shot of this problem.
Michaaael
begin 666 EnterpriseManager.jpg
M_]C_X `02D9)1@.`!`0```0`!``#_VP!#``8$!08%! 8&!08'!P8("A *"@.D)
M"A0.#PP0%Q08&!<4%A8:'24?&ALC'!86("P@.(R8G*2HI&1\M, "TH,"4H*2C_
MVP!#`0<'!PH("A,*"A,H&A8:*"@.H*"@.H*"@.H*"@.H*"@.H*"@.H* "@.H*"@.H*"@.H
M*"@.H*"@.H*"@.H*"@.H*"@.H*"@.H*"@.H*"C_P `1" &)`?@.#`2(``A$!`Q$!_\0`
M'0`!``(#`0$!`0````````````4&`P0'" D"`?_$`&$0``$#`P("! @.)!P<(
M!0D)`0$"`P0`!1$&$A,A!Q0BE146,4%35I32%R,R4515M-'3-T)A=I*3U#-2
M<76#D>0D-#9S=*2SPP@.E@.;&R.$-(5V)RH\+A)D1&8X*$H;7PP?_$`!L!`0$!
M``,!`0`````````````!`@.,$!08'_\0`-!$!``$"! ,&! 4$`P````````$"
M$0,$$E$%$R$6,4%289$&<8&Q%2.AP=$B4V+P,C/Q_]H`# ,!``(1`Q$`/P"V
MV^QI595W*ZZ@.BVN'"="0XZ\%-ME1!!"PK"23_1SQ\]8<Z9ZSUGX2K%UCTO6A
MN\F/+NSY.52G_2%LEJL/0UJ.+8[9!ML9:XCBFH<=#*%++X!40D 9P ,_H%<"
MU=T3,6FY:GM-FOSERO6GHR)\N,]!$9#D8H2M;C3G$6"4!:,I4$DY.W<1@.^KD
M<C@.YBC5B5S$WF.[:W\PQ57,3T=J4O3:I"7U=)ED+Z1A+AEC<! \P._/G/]];4
MRX:=DR$2/A&T\U)".&IUJ5M*QR\O;P/)G `&37FC3G1Y>+R]:G$]6\&3)D2(
M]*BR692HG6%A*%.MMK*D>?DO;S&WD:Q7_0TVUZQN5@.:FVIYR(MS :^[<HK"%H
M0X4`J*G=J%G&2T5;QYQRS7?_``;+:M/-Z_1GFU;/2.=,]9ZS\)5BZQZ7K0W>
M3'EW9\G*OZI>FU2$OJZ3+(7TC"7#+&X#Y@.=^?.?[ZXJST0W%S 1#[ZV'(^K6=
M0BSJ@.RI;$=M22P%IV<0IWK4I2=NU1W).0".=4VSZ)OUX<E,6^ &VY.C+=;<@.+
ME--S-S:2I:4QU*#JB #R2D\P1Y014IX/EJHF8Q>[Y',JV>G'EZ;>"P]TF61P
M+ "@.N6#N`.0#V^>"36+A:5X'!^$:P<'=OV=93MW8QG&[RXKS0YHF _-6V#-=A
MMH$]#;D2.J4T)4A*U[$*;C[N*L*5Y"$'(YCESIJ;1-^TU%ZS=X;
PDKAK=8
ME-2$-OH`*FEEI2@.A8!SM5@.\CRY&MQP3+S-HQ>OT3FSL]/![3P:2V.DZR\-&-
MJ>N#`QY,#?YL#^ZORRO3;*W%L])ED;6X<K4B6 5'YSV^?E-<5E]"EY<M6GKG
M99\*?;[A&:>G/K6&46E2F0^H2#D[4!M05O\`/_-!4@.*B]4=%UQMG2#<]*6B;
M"N,F&N.VA;\EB$M]3S:5I2AMUP%1RK&$E7F\F0*XJ>%96J;1B^OMT7F5;.]Q
MV])L%>WI!TVH+0I"DJ?3@.@.C_`-[S'!'Z0#YJ_<A>FY* B3TF61Y .0ER6%#/
MS\UUY\9Z+M0RK3;Y,.!)2^\S,D2$35,16V$1G4MN'>MW/94H!06E!!\FX D;
M5NZ)KP_8]23)[\:WRK4S"D,-OOLAB8U)4H)<3)+@.:"<))!!4%'L\C5GA&5CK
MSO3PWM]SF5;/0;UPTZ_#;CO](VGG"TOB-NJE=M!S\X7@._H)!(R<5J23IF5MZ
MUTE6)[;G;Q)05C/S957FGQ!U+X#\+>#?\EZGX1V<=KC]5W;>/P-W%X>?S]NW
M'/..=9=2]'6I]-Q9LB[VYMMN"MMN6&9;#ZXQ<!+?%2VM2FPK'(J !R.?,9W'
M!<M>T8O7Z)S:MGI*0O3<E 1)Z3+(\@.'(2Y+"AGY^:ZR*DV!125=*%G)2<I)F
MCD<8R.W\Q/\`?7FZ)T9ZJG2(#-N@.1IW7GG([#L.X1WVBZAOB*;4XAPH0K8"H
M!1!(!QG%8AT=:G5*BLM6YMYN3&=F-RF9;#D4LM$AU9D)66DA!&%94,$I!^4,
MS\&RW][[?[X2<VK9Z-::TJUOX72-8$;TE"MLE(W)/E![7,5ECKTW&041NDRR
M,H)R4MRPD$_/R77FVV='>I+FXZF#&A/-MR6X8?%RC!EU]Q.Y+33I<V.KQ^:@.
MJ(\X%9="
\8;GJ*)=I<FT^ [;(N,E/5.*[\2I(6WL4M&%=H^4CF,']%G@.V7
MB)GF]WRDYL[/1#K6E7=G%Z1K`O8D(3NDI.U(\@.':Y"L[+VGF&DML=)UE;;3Y
M$HF `?\`8%UQB\=%<*TZ(U%?'+E)E\"VVJZ6Q24)9W-2W5(4'F^UA0V'`2LC
MR')S@.5>)T9ZJG2(#-N@.1IW7GG([#L.X1WVBZAOB*;4XAPH0K8"H!1!(!QG%8
MHX3EJHF>;TC>T>$3X^DKS*MGHHITNI;2STD6$K: #:C*3E 'D [7+%?M2]-J
MD)?5TF60OI&$N&6-P'S [\^<_P!]>6=0Z9NNGVX3MSCMB--0IR-(CR&Y#+P2
MHI5M<;4I!*2,$9R,C(YC-\OW0^];V]5N6^Z.7)NTHMKD!3$(D75,Q02E31"S
MR"L@.%._<01RK57!\M3:^+W^GK$?>3FSL[HS<; TS)0.DJPJ6^$ NJE@.K2$DD
M!)W\O*?[ZU&!I5J6))Z0].N.;MRM\A)"\^4*[7,'F#\^:X9K'HK<L;T]NVW1
MNXLQ;J+5UUU4:+%+@.C!Y:5.+?RA:3N24E.W*<;MW8JN3]"7VWW2/`GMVZ*_)
MAIGL*?ND5#3K"E;4K2Z7-BLD' "LD#.,<ZE'!\M7%Z<7O^1S*MGIP2-,IB1F
M$=(FG4&,I2FG42 E:0KRC(7Y#_?^G%8<Z9ZMU;X2K%U?T76AM\N?)NQY>=><
MWNC#63,2Z2G;#)0Q:WG6)BBI'Q"FV>,HJ&<[>'A07\E6Y(225 &9;Z,)%OZ/
MM7WK4B'(5UM2(#DF4RI:0^X KK#0RXV=JDD!6P\SR." GA&6BWYM[VCP\9
MM^YS*MG="O3:D-(/299"AH@.MI,L801Y".WRQ7Z<>T\[GB=)UE7E)0=TP'LGR
MCY?D.!_=7CZNEZ2T%:Y6G]+S;^_-1+U->VK?;V8ZTI C)<2A]\DI5D[E;$I.
MP@.]KM#E6\7@.>#A1>JN?;_?#JD8LSX.[7!>E9SK:G>D'32$MMI:;0A ].$I'D'
M-9/]YK]27M+R!$*ND+3:78J VAU#Z0K .4_GXY>8C!_IKS[>NBV_)OMW9L5O
M<D6]F...BP>-(:3(F)C*4%EILE*WB`.?#2>>1Y1BJY/TE>+?8X]VGLQHL62RF
M0PA^8RA]UI2MJ7$L%?%4DD'!"<$#/DYUFG@.V7JM;%^QS9V>GEITNXA2'.DBP
MJ0I?$4E4I)!5_./:\OZ:_:UZ;6<KZ3+(H[PYDRP>T!@.*^7Y<#RUQ#HYZ*9=Z
MUC8H6H!BQ7%Y^.9MJG1Y*4NML*=X9<;+B4JY`X5S(SCR$B!O^ EH2.CRPZKLC
MLE;#[R[=<V7U)5U>8D;D[5822E;?: `.W&"LDUF.$9>:]$8DS[6ZW_B5YDVO
M9Z/:>T\UOX72=94;U%:MLP#<H^4GM\S7XSIGJW5OA*L75_1=:&WRY \F['EYU
MY!I78[/8?GGVA.=.SU]G3/\`ZRK%_)\'_.A\C^;\KR?H\E'3IEUA#+O258EL
MHQM0J4"E.!@.8&[ERKR#2G9[#\\^T'.G9ZZ2UI5*FU)Z1K %-_((DIRGGGEVN
M7,DTX6E>!P?A&L'!W;]G64[=V,9QN\N*\BTIV>P_//LG.G9[!N"]*SG6U.](
M.FD);;2TVA#Z<)2/(.:R?[S7XE#2LE>YSI#T[YB0)"<%6 "KY7E.`2?.:\@.T
MIV>P_//LO.G9Z^;.F6^%P^DJQ(X6>'ME`;,^7':Y9K^O+TV\%A[I,LC@.6 %!
M<L'<`<@.'M\\$FO(%*=GL/SS[0<Z=GL%#VGD.N.(Z3K*EQS&]0F %6/)D[^=;
M5ON.G("7.#TB:=4XM2EE:Y1)W$8)QQ-I/GY@.UXTI3L]A^>?8YT[/7Z%Z;1'X
M".DRR)8P1PQ+`3@.^48WX\]?QPZ9<XO$Z2K$OBXXFZ4#OQY,]KGBO(-*=GL/S
MS[0<Z=GL%#VGD.N.(Z3K*EQS&]0F`%6/)D[^=?G?IO>A?PF63>@.J*5=;&4E7
MRB.WRSY_GKR!2G9[#\\^T'.G9Z\4G2ZHZ6%=)%A+"3E+9E)V@. _.!NQYS_?6=
M+^FVP@.,])-A9*4!!4W*"2L G&["^> <#] `KQY2G9[#\\^T'.G9Z^:.F6GUO
M-=)5B0\O.Y:90"E9.3D[N?.OZ5Z;)>)Z3+(2\,.'K8[8QC"NWSY ?/7D"E.SV
M'YY]H.=.SV"'M/!I+8Z3K+PT8VIZX,#'DP-_FP/[J_+R]-O+;6]TF61Q;9RA
M2I8)2?G';Y>05Y I3L]A^>?:#G3L]AF38"L+/2A9]X! 5UT9`.,CY?Z!_=6-
ME>FV5N+9Z3+(VMPY6I$L`J/SGM\_*:\@.4IV>P_//M!SIV>NBUI52G%*Z1K 5
M.?+)DIROGGGVN?,`T0UI5#3C:.D:P);<QO2)*0%8\F1NYUY%I 3L]A^>?9.=.
MSV&J38%%)5TH6<E)RDF:.1QC([?S$_WUBSIG_P!95B_D^#_G0 ^1_-^5Y/T>2
MO(-*=GL/SS[0O.G9Z_"]-@.LD=)ED!9&&SUL=@.8QA/;Y<OFH\O3;P6'NDRR.!
M8 4%RP=P!R >WSP2:E-=6_3FF%"8="V"39FGE)G/LVQA3D-KS.\/A$N('YV#
ME(YX5@.XJ&IDV:Z]&&J[Q;M&V6VP1%7X-FFW,-OR$;<%X(X8+23GL'.XCM83R
MS\[?+_Y?HYOZFW<91CWV%&LM_;N29\1V69S,H(;4&G$-A.XJ[9RH^?EM\_/"
MH3H)_E-(?U%<?MZ*5QYBCE8M6''6TS'M*T]8NZ=_TH/R2Z@.__:?:*\Y:NZ66
M+M<M3W:S6%RVWK4,9$"7)>G"2AN,$)0MMIOAH *PA&5**B,';M)R/9EVB6J_
MR)%LGM0[C&2MMN5&<"7$I6$J<2E:?,<%"@.#YBD^<5'_!CH?U2 L?L3?W5Z.0S
MV#E\/3B43,WGQWM_$..JF9GH\KR>G9XVV6W$LKC,EY=N=:95/*H$-4-;:P(\
M8(!;0LMC*0L^;GRJ&B])\*%?]57"WV:XP_&'XQ]Z-=4MS8SO'+JN!(#/8;5D
M)4@.I).T=KE7L'X,=#^J5C]B;^ZGP8Z']4K'[$W]U=JGB>4IB8C"GKZ_+^(9T
M5;O+EUZ<85VN"Y4_2\GLWZ/?V$L71*-KK,=ME*%$L*W)/#*CC:>UCS9.+2O3
ML]4>7+LKBI+4^;/=;MT\PX\Q4DY/6&RA9=*">PHJ& $CGC)]4?!CH?U2L?
ML3?W4^#'0_JE8_8F_NJ?B.3TZ>5-OG]-S15N\AQ>F*9"T99[%'@.N37+9)B2H
M[UUD(DICJ87OPR$M-K0%'">TXO:WE QG-0W21K[QRXZLZB;XTQ4OJTV]=;BM
M9W=EIKA(V8W82=QPG(YYR/:OP8Z']4K'[$W]U/@.QT/ZI6/V)O[JY*>+96BK7
M3A3?YG+JW>#+WJ>;<5.HCNR8<.1#A1),9M]7#?ZLPVTE2P,!7-&X`@.[2K_M/
M4%=/&_4ESNO@.&3%ZY<HERQ N? =7P&0UP'W.$>*P=N[9A."I7,YKU'\&.A_5
M*Q^Q-_=3X,=#^J5C]B;^ZE?%LKB1$584].G?\OXA(PYCQ>4I_3=UJWSHOB_L
MZS#O,3=UW.WPA(2]NQP^?#V[<?G>7L^2M"5TLL7#3ZK%<K"XNU.V2WVA_J\X
M-O*5$<4M#J5EM24A6]0*"E7FPKY_7GP8Z']4K'[$W]U/@.QT/ZI6/V)O[JQ'$
M\I'=A3OW^M_NNBK=XYNO2D]==*0+3)%^BN0[4FU!-NO)CPWDI"DI6['+2MY*
M2 H;QN"?S...OWCID\(7/64WQ=C*\87K:]P)+_&::ZFI)VK3L'%2O;@.CLX!/E
MKUO\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZM?BF5_M3[^L3]X@.T5;O,S/_2"X-PB2
MO =QE<"\/7?;-O/&V\2.ZSP&CP1L;3Q=R1SQ@.CSY%7T_TL>"M+6BP.V7CPXM
MMN5KE+3*V./M3%I62@.["&U)*$\R%@.C/(>;V#\&.A_5*Q^Q-_=3X,=#^J5C]B
M;^ZL1Q+)QTC"GW^?\R:*MWCG3W28Q8=.3=/VV)?H5J=GIN##L"]"/,0KA!M:
M''@.SM6@.X2H (3@.CF55%Z.U\]I>^ZHND1J:])O$"5":?=FGK$=3JDJ#RG0G*U
MI*<D@.)R>?*O;7P8Z']4K'[$W]U/@.QT/ZI6/V)O[JY?Q?+=?RYZ]_5.7.[Q5I
MWI(FVVTZECW2-X=F7IZ \Y(N3RGDGJKN\)<2K)<2H ((W# J^,_](+@.W")*\
M!W&5P+P]=]LV\\;;Q([K/ :/!&QM/%W)'/&"//D>F?@.QT/ZI6/V)O[J?!CH?
MU2L?L3?W5C$XGE,29FK"GKZ^D1]H6**H\7A;4&K/"^B-)Z=ZEP? /6_\HXN[
MC\=T+^3@.;=N,>4Y_15\TWT\7:T,Z)9=C27V-/,OQWV6YO!;N"%(V,A:4HP.$
MD)P2%$D9Y$DUZM^#'0_JE8_8F_NI\&.A_5*Q^Q-_=6J^+97$ITU84S'6>_>]
M_O*1AU1XO'-CZ3V&K4B%JC3[=_"]0N:AE<60&42'%,J1L4@.((QO(6?S3C:4X
M-98?2FW&O.HYZ8=Z4_?66DOS_"R$7!E:' OXF0A@.!#:DA*2V&\82G! `%>P?
M@.QT/ZI6/V)O[J?!CH?U2L?L3?W5)XKE9O/*GKZ_5>75N\?=)O2QX\6BY0? O
M4>N7AN[;^M<79LB)C\/&P9SMW;OTXQYZRZKZ66-06?5K:["XQ==3(@.B;($X*
M90J-MP6FN'N2%;3R4M6,CGRY^O/@.QT/ZI6/V)O[J?!CH?U2L?L3?W5*>*96F
M(B,*>G=U^4_M'L:*MW@.+QBO7@./P+X8N/@.?Z#UE? ^5O_`)/.WY7:\GEY^6KY
M9=:VUO1&D>N+Q>]&WCK<.)A2$3HKCJ77$[PE0#@.<0.9VI"#RW*Y5[!^#'0_J
ME8_8F_NI\&.A_5*Q^Q-_=7)B<:P*XM.'/?P^7ZQT2,.8\7E)CINV7.#='-/
M[KC:IESEVPIFX:1UU2E*2\CADN[5+."E3>1@.<O+57N^NX5WCZ :D3[%QKQ88<
M:"PI<E*H3[3#A4D.QRV5*W))2K#@.!\N .5>U?@.QT/ZI6/V)O[J?!CH?U2L?L
M3?W5BGBN5HF].%/O\_YE>75N\S,_](+@.W")*\!W&5P+P]=]LV\\;;Q([K/ :
M/!&QM/%W)'/&"//D4._W^VQNBFPZ3M$GKC[LQ=YNCA;4$LOE'";9;*@.DG#...
M?)0W$;5$9%>U?@.QT/ZI6/V)O[J?!CH?U2L?L3?W5*.*97#F)HPYCZ[7M]Y-$
MSXOG=2OHC\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZNUVAP_)+/)G=\[J5]$?@.QT/Z
MI6/V)O[J?!CH?U2L?L3?W4[0X?DDY,[OG=2OHC\&.A_5*Q^Q-_=3X,=#^J5C
M]B;^ZG:'#\DG)G=\[J5]$?@.QT/ZI6/V)O[J?!CH?U2L?L3?W4[0X?DDY,[OG
M=2OHC\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZG:'#\DG)G=\[J5]$?@.QT/ZI6/V)O
M[J?!CH?U2L?L3?W4[0X?DDY,[OG=2OHC\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZG
M:'#\DG)G=\[J5]$?@.QT/ZI6/V)O[J?!CH?U2L?L3?W4[0X?DDY,[OG=2OHC\
M&.A_5*Q^Q-_=3X,=#^J5C]B;^ZG:'#\DG)G=\[J5]$?@.QT/ZI6/V)O[J?!CH
M?U2L?L3?W4[0X?DDY,[OG=2OHC\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZG:'#\DG
M)G=\[J5]$?@.QT/ZI6/V)O[J?!CH?U2L?L3?W4[0X?DDY,[OG=2OHC\&.A_5*
MQ^Q-_=0]&6A@.,G25C _V)O[J3\0X4=9HGW.3.[YW4KZ$>('1YG'BYIO/^S-?
M=6=OHUT(XD*;TI85I/G3#;(_[JXZ/B;+US:F+_6&IR]4=[B.M.E/H[U+.;;N
M%RNC]M8E*?5$1$6&)A!RCB@.IW*2#A03D`G&X' `J]]UYH=C1&J+/IR=<D-W)
MI:HT!R.H1XSB@.2H-=G*$J5SVY*03R"037ICX,=#^J5C]B;^ZGP8Z']4K'[$W
M]U>%;+?Y?HY+RX%T$_RFD/ZBN/V]%*M&M8KUBZ:]*631RK?9&I5NF, ]12^V
MT@.`2%;6MR1DJ;\N1\HTKAS%<8N+5B1TO,S[RU3TBSK>G+6;3< )[?4VHC<BX.
MRD);G.R0OB<51<PXD<(J45*+:,I!)()R:M-0-JO=JOTI,JQW.#<HR%I;4[#?
M0\A*PAPE)*21G!!Q^D5/5B$*BM2:AM>FX#<R\RN TZ\B,TE+:G7'G5G"6VVT
M`J6H_P`U()P"?(#4K5*Z6+;!N%BMSEPA7M_J5SC2V'[,TEZ1" =2KLO\`#(/$
M2G)"DA#APHG:<9 6#3>H;7J2`Y,LTKCM-/+C.I4VIIQEU!PIMQM8"D*'\U0!
MP0?(14K7G75FH=4C1NGG-0RKW%CNZY1!9?9;=AS9UK)<VEQMD(<"E *&U*$*
M.U)"<D$ZKC>OW&;4AV1J2-I4Z@.NFQUR/-?DIA;!U/BH96B84[N,!N4,9059
M0"'I2J_...86:Q]8\+/R8W">9C)W0GSQW7?Y-MC"#QU'GE+>XIP<XQ7%6V]4M
M(L:-6R.D"38QIE_@.NVZ.ZQ/5/X_8XJ(RUX=X'#">.HI)R5845@.1^L+-JR^:Y
M,>X1-4R;2WJ/3KK1="QP6Q%=$EQ*V/BVR%D;ULD)"B"".5!Z:JJZFZ0-.::N
MKEMN\R2B:W"-Q<;8@.2)'#C!1275%M"@.E(*3DDC'G\HKE^BTZLE=.-TMCMWG/
M6ZP7:=/GA<U985&F1VS"CI03E100XK:4A",'!)(SJ]-MINK_`$DWJ5#8U B-
M)T2];VG;9;5RD29"GG"(RR&G`D*&"2-I'+M)SS#O\22Q,BLRH;S3\9]"7&G6
ME!2'$*&0I)'(@.@.@.@.BLM<`Z04:LAZ5T]9H%HN5IZIIE2D/6@.SY.V>E#:!#2F*
M\-NW&4NO%Q)_3A6[%;I&K)5S?D:Q&NTRWK?97+0+(VMO#Q0#*XB, ",D\;=O$
MD#">0&-M!V_3^H;7J#PEX(E=8\'37+?*^+4CAOMXWH[0&<9',9!\QJ5KS WT7
MV_4=AZ4=1S;U;M0)TY/U'<40DQPXEIM]Q22)#[*4@.N,K0$I0Z2IM"DJ)`W!8
MJCUUU]"TUI^WW!&LHTZS6G4"[S/>$@.,*=+3YCY?SM<*=B2E0)2-R-JB<X#U?
M50NO23I6TWF?:[C<G6)-O6PW,68;Y8BE['"XKP1PT!6X<U* \O/D:Y_T&R;[
M,O\`;93;VH']./Z6B.37;JJ0I#ET4O)4T9',@.H*B2U\7\G_V:C]1,7*-J[II
MBMV*]RG=20H4&UJCP'5LON&(II1+V TA*%. J4I0``5Y2,4'=>OL^%?!^R3U
MC@.\?U9S@.[=VW'%V\/=G\S=NQSQCG6I<=0VNW7VT6:;*X=RNW&ZDSPU'B\)(
M6YV@.,)PD@.]HC/FS7%;G:-7V28BS1W]2)@.0>C]F&7K&@.NH$U#R4*4REPI0IW8
M"< I=*/DD*VU5+Y9->WEGH_>M</4B+I&>O(=>DO+2IMM2$ I9D.MEUE+J0XE
ME4D<1)4,DA*54'J"[7"+:+5-N5P=X,*&RN0^YM*MC:$E2C@.`DX /(#-0FE]<
MZ?U/*,6SS75R>JHFH:D17HRW8ZR0EYL.I25MDC&Y.1S'/F,Q6HW#?.A.]BU0
M+NAR38I++,.<R[US?P5I#:TKRM3F>6<JW'F"K()HFBK9<KWJ# HF<:M=RA1](
M65:+D[<8;L5)>=C(9#+0<2"XI*D**B!L`QVB2!0=KM4]FYP&ID9$E#3F=J9,
M9R.X,$CFVXE*D^3S@.9&".1%;5>9>C:/TD/"(N:K5(N+&CYSD83W'TMFY]<?2
MSQ0Z0VISAJ3@..9&W:<8`(Q06.D5&A+ZY9+CJY^ZJT_&XK$F!) :V2N*GB\)R6
M\IQ3X:XP/ 0E![)3A>P$/2MPN4&W=6\(38T3K+R8S''=2WQ759VMIR>THX.$
MCF<5M5Y[UO9G;O;X*[-$UW*LEOU3;IL@.7$2"XVREM27EQ O_`"T@.;T;O+A62
MWR"B,O\`]M_'O_\`$GA7QS__`#_!_@.+A>R_)_M=W_MT'?Z5Y5?7K>S]&6@.)T
MNX:D;N]Q>N=JDQ9,Y]$EZ;*;=:A*(=4`E*"VA621MR%)!))KTKI*%.MVE;-!
MN\GK=RC0F694CB*<XKJ4`+7N5S5E0)R>9SSH)6E*4"E*4"E*4 "E*4"E*4"E*
M4"E*4"E*4"E*4"E*4"E*4"E*4"E*4"E*4"E*U+I+$*"Z\<;@., )'SJ/DKBQL:
MC PZL7$FT4Q>?HU33-<Q3'?+2O=Y3!^*9 7(^8^1/]-5.7+?EKW2'5+/S'R#
M^@.>:HR_W--KM%QNLH..HB,.27 GFI00DJ(&?/RK038+](2EZY:CD0G7 %)9M
M;#'!2DCS+>;<4X<Y[8VA0QA*>>?S:K\1^)L2N<*8IPZ?"9M'Z 7O+VXY.1B-7
M6J4U66/(=CN!;#BFU#SI-4RY7"1I:Y06)=]%U:EOLL*C26FTRV^*YPTNI+24
MIX844@.A2!Y5=LG:@.VZO!XAPW,\)QHHQ9M/?$Q/\`Y+MX.-1F*;TK;8[[UE88
MF;4NGDE8Y!7]/Z:GZYG5YT]-Z[;T[SEUOL+_`$_,?_\`?IK[?X6X]B9N9RF9
MF]41>)W])]7E9_*1A_F4=SC72%_Y2NA?]CG_`&5=*R](EMN<WI\T])LG@.XRK
M?:YDC9/D*8;6%(2S\I*%G(+P5C',)/,4K[.8>;#JMJ=NKTI*KY"@.PY(6D);A
MRUR4%&QS!*E--D'.>6T^0<^>!/5#0+E!N[T:=:9L:="=QPY$9U+K:\<4'"DD
M@.X((_I!J9K4(4I2@.U;A;8-QZMX0A1I?5GDR6..TESA.ISM<3D=E0R<*',9K:
MI2@.K]YU.W;KJJ S;+E<7664299A-H7U5E:EI2M25*"EYX3N$M)6OL'LY*0JP
M51>D&UW&5*+MCM4[PLN+PH=UA7#@.(8>!44&6WO1Q64*4%!.U[ Y3HV#.'*UX'
M5<ND/4[EOT__`-:,Z@.@.N#4&&$]78;C0ENL;]_'[;8<1M2@.I/%P2 5$!T^R62
MU6&*N+8[9!ML9:RXIJ''0RA2R "HA( S@. 9_0*Q:@.O;=DC!^1%DO-*VH2IG8
M=SRW$-M,C<H=IQ;@.`)[ P2I21@.GC^G^CR]P;=(2[%N3LW@.QT73CNP4,WA2)#
M+CX2&D!;_$0V^D+EK2</85_*N%,A>]$2;L[:$0=(M6VRI7M>MCKS*D(;,^V.
M+"F4J+2 I$:0HH;*DG&X]MPIH.RU"P]36Z;=XMOAJ==<D(FJ2X$80DQ7T,/)
M.<'(<< &`0=I.?)FG]'$+KVH9=TA28TFRV]ZY,PIK#G$3/Z[(:EN+21V0EI0
M+.4J7N4E>=A3M.I:^CQMW4[/ANR1G[:AZ]2)"E%"F9;DJ7'>8+C><NX;!3AQ
M)"5L`CY+:B'5:BM638-NTK>9UWC=;ML:$\]*C\-*^*TE"BM&U7)64@.C!Y'/.
MN2Z=T+J(7NQW#4+=W?NS:+<OKJ9$)2(R6F&$OM./K2N5E3B)!*&CPW.+@.J3Q
M'%":Z7M+2[YX=QIKQBZY9>I6SG'/@.Z5\?N=^/6G9OXC':;W*/!Y@.;4Y#I=J;
M;9@.-,1X/4([&6&HX2A*4-H)2G:$$@.)*0"D<B`0" <@.;5<57H"\O)U6ZJ%P9$
MO:AEYM3"GG8YO$R4^RC?E/QC#C78<^+45)2OD%8RVW13T"+!,_2\Z]V)"Y1%
MBF&WK6RXL1PVZ&$\.*V$\*1R0I2OCRKRN.! =;A7"+-DSV(SN]V"\(\A.TC8
MX6T.`<QS[#B#D9'/'E!K:KDLC1DUF3-?EZ?:N<>1<(DR;$1(1),Z.BW]63'4
MN04%XM/I#V7L`@.A8)<RD0EPT\]I^#<[O=XL:-(2S#59HZW&R\XIFY2IS=K:P
M20HMIC-!*-R00-@.6$"@.[A)=6RV%-L.OJ*T(V-E(("E %7&$@.E1YYP#@.$X!
MRU19&F;BUHJ-;TI:?G*OK%U?0TOL-A5T3+=2E2L;@.A)6`< JVYV@.G;4?T:Z.
ME:8\42F#U52=/JCWA8>"U.2QU7A!Q6XES8$R$H/-*$Y2G:D@.$.E5%6Z]MW*T
M2Y\"+)>X#TJ.&!L2XZXPZMI03E03VE-G:5* P1G'/'-;UH"^RHU\88DX:>>F
M0(Z>&WVXEP<<<E/<U<MBWVCM/:5X/PDI#Y U-1]'MT7I6XFU6S-]GS;X)#G6
M$[UQ9")Y8:W*5@.-*<<C+X8(2%G>4A04J@.ZUJ&ZL6*P7.[S$.KC6^*[+=2T 5
MJ0V@.J(2"0,X!QDBI"N07W2,^3:M61&]+\>_S6;H$WWK+3?66GTO=78W!7%<V
MI<9;X;J4MIX>0KXMO===,Z>\`ZIO'@.^+U>ROPHA1AS=Q907(X [J\DJ4ZI)8W
M.*RI>$Y42GD$A;XM@.OSEKU3%@.P94ER*AR%<5Q@.'TLK22G:I0W I!"U<N7RCD<
MS4U7$$:!N4/2\*U)TZT_,9L35LMTEI3 1:;@.VIX.3DE2@.IL.*6RZ'&@.IT\/*
MDI4E*3>NDJRRKOX),2U>$E,/$@.&0&TM*. %J!(*,#<1(:/&:4$E"7$J<00M=
MLN$6YQEOP7>*TAYZ.I6TIPXTXIMP<P/(M"AGR'&1D<ZVJY*O1KL.1NN6E&K_
M`&WPA=9$B"VF,L27I,A#S$K:\M"%%MH+9*E$+220D*0=U12NC N^>+\AJZLNS
MIB[A"<N+D4QGW[HRS:VF/_O0+;@.$G<O#P&-JE@.;MN0[+<[A%MD9#\YWA-+>9
MCI5M*LN.N);;'('RK6D9\@.SDX'.L-CNK%YA.RHJ'4-MRI$0AP '>R\ME9Y$\
MBILD?HQG'DJE2-)27>BF-8W83LLLRF)2H$UQEQ;C#<Q+YCG:E#():3PPV,-)
MY("M@.WU"S= WA-J<=L;/4-13+G>2[<.LE*VHSZ9QCC>"5):XCD5SAH\B^WMW
M!1H.OU'Z>NK%^L%LN\-#J(UPBM2VDN@.!:4.("@.% $C.",X)JH=%&F5Z>\*J1
M!N5NB2.$$1IO46NVG?N6EF$@.-)R%(!65%:M@.!"0A)53]*Z-O%OTO#BV[2C5D
M>9L0MUW:6F&?"KJE1PM:0A:VW7$MMR@.@.R,)W/)R"E3F [?4?;+JQ<9MVBL(=
M2Y;)28CQ6 `I99:>!3@.\QM>2.>.8/Z">06#1MPAI(O&E)UXTXS*DB-8YB;<5
MM\1J)L>#*%HBI"5MS!V2%?'$X.]:JLNF],7BUZHF7:9#ZQ'7-9#<14POE@.&#
M%85*:<7@.N*2I#K:BZ LHW*1M*E(>#I5:MVN$6T6J;<K@.[P84-E<A]S:5;&T)
M*E' !)P`>0&:X5J6PWF-IW2D.\:<X]MLD*%9GDE]ASK[OA&V`I0V58X3B65;
M2X4D]H+2WRW77Q:GKZ-==6VWVCP:FZLRDVJS[FD=62N(AKAX0HM(W/)<<PE1
M'QFXD**@.`N$34 =O,2URK=.A2Y45V6V'RTH;&^!O!*%JYA4@.)_24+\VTJFJX
MKJ/0%Y>OMZ\$0NJ6IS>AI,53"=\<)LX4RAM64=M$.2V$.)X9P O"%<[KT86=
M_3]J<@.J@.7*-%?><D-(EKB#JR0EI/#X<8)::W*WK"6PL'M*4H+7MH+K2E*!2E
M*!2E*!2E*!2E*!2E*!2E*!2E*!5<UDX0S%;![*E*41_1C[ZL= 5[6+6Z+'=_F
M+*?[Q_\`2O#^)(JGAF-H[[1[7B_Z.UD;<^F[F?2+^3_4_P#5<K_A*JVVYI3N
MFHJF%,.15;=KC[J@.<%O&W"4J2KLGY8"<Y(V@.57]5VYV[Z7O%MC*;2_,AO1VU
M.$A(4M"D@.G )QD_,:UX6OM+HL2!J"<W:[LET(?ASWUQEE03VE[0I(<[61Q1D
M*(/,XKP/@.?$HY6+AWZWB;?1W.*4SJIGP5<:15>[]<Y2]3V.Q-1;XQ!C-+AEP
M27TAB4A(4IUK<HJ5MVA.2$'!/EJ1-ZEG5\O3D?5%MDS(L=UYUYC3SJV4J;&5
MM;A,R%)'E)&P*[!4%]FJ]>IVE;QH+4CLUYXV^9JWAP9<))E(:>\'M N+2I67
M&]J70I(RHYPD9QC8Z'I"F!J"WZ:M3+]@.;@..(G7A]M3+SCH;)1L3Y$MX/98QN
M2D[UJ"E;%?0<0RF!F<3\^B*K=UV<KAU4Y:O&IF>D^D1X>,]\_P",1?QO:ZZ:
M-NKM\TI:+I)0VV_+BMO.);SM"E)!.,Y.,_I-7C1SA$U]O/94WN(_2"/OKF_1
M9^3C37^P,_\`A%=*T:UF3(>\R4!']YS_`/\`*_/N#433QJFG#CI%57MU_9WL
MS-\M,U;0INI?RYQ/ZBD?\:/2FI?RYQ/ZBD?\:/2OUF7S\+=I>USH-WNLV[)C
M-3;K-1,<CQG5.ML;8P8"0XI*"O(9"L[4X*B,'&XVZHMF2Q,E1)4-YI^,^AMQ
MIUI04AQ"D.$*21R(((((J4K<,E*4H%*4H%*4H%*4H%*4H%*4H %*4H%*4H%*4
MH%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H %*4H%*4H%*4
MH%*4H%*4H%8)\9,R(ZPOR+'(_,?,:STK&+ATXM$X=<7B8M*TU 33-X<WD,N1W
MEM/)*5I."#6N['9=5N=9;6K&,J2#70+M:F;@.C*LH>2.RL?\`<?G%5 279YL96
M"RIQ/F4V-PK\DXM\/9KA^)-6'$U8?A,?OM]GT.7SM&-%IFTN<W.#?VF]1VF+
M8;#=+%=Y*96),MQEQM089;Y!*#M(4SN"@.<@.D'D:SVN?K.U:;% BMFE=-1;>&E
M,A*+D[N[0.Y1);)*B225')).3FK?@.YQ@.YK;AVR7+(X+*MI_/4,)_OK>5XYQ&
MJFG P:-5HB.Z9GI%MS$RN#UJKFT=_>K.A+5)M.D[-:I02J7%C-L+#9W J QR
M^<5UBR0NHP$-J'QBNTO^D_\`^Q6M9K*W`/%=4''_`)\<D_T?4O7U_P]P/$R
MM=6<S7_95?IM?K/U_9YN<S48D1AX?_&'.?\`T@.6/U=D_\>+2G_I L?J[)_X\
M6E?6//3&G+<G3%JBQGVMI0M3SB(RGY1"W5ON*&]94XZ=RSE9QN.5;4 [1->'
M8GH;EW=(]RH9^]VJ_1795CN<&Y1D+0VIV&^AY"5@.+)22DD9P0<?I%:52]ELL
MWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*
M:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]R
MJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W
M2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>A
MN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'
M8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]
MRGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=
MW2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>
MAN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>
M'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FH
MLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JL
MTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TC
MW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;E
MW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)
MZ&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<I
MX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ+-!IP.]/D5Q(
M4$KTY(4`I)2<%^+Y0>8/Z#2M.Q_EMA?J[*^TQJ5J$7C47YW]G_S*@.JF+O)8F
M16Y4-YI^,^AEQIUI04AQ"@.X0I)'(@.@.@.@.BH>L2U!2E*!2E*!2M&\W2+9 [>N;/
M6XB.E:&_BVENJ*EK"$I"$ J)*E)&`#Y:QP+U"G0S)C*D*0EU+"VU1G$NMK)2
M`%ME(6CDI*NT!A)"OD\Z"2I2L4E]F+&=D2G6V8[2"XXZXH)2A(&2HD\@.`!G-
M!EI2E I6)+R527& '-Z$)626U!.%%0&%8P3V3D Y'+.,C.6@.4K6N$Z/;V$O3
M'.&VIUI@.':3E;BTMH'+YU*2/T9Y\JR*>2F2A@.AS>M"E@.AM13A)2#E6,`]H8!
M.3SQG!P&6E:SDZ.W<6("W,2WVG'VT;3VD(*$J.?)R+B/[_T&LCK[++C*'76T
M+>7PVDJ4`7%;2K:D><[4J.!Y@.3YJ#+2E8I#[,9L+D.MM(*TMA 2U!(*E*"4IR
M?.5$`#SD@.4&6E*4"E:,ZZP8)*94EM"PME!0.TH%YSA-92.8"E\@.?)R/S'&]0
M*5K2IT>(_#9D.;')CI88&TG>L(6X1R\G9;6>?S?.16S0*5K7* ='MENE3YSG"
MB16EOO+VE6U"05*.!S. #Y*V:!2E:-PND6 XVW(6X7G$..(:9:6ZXI*$[E*"
M4 G Y#./E*2GY2D@.AO4K6M\Z/<&%O0W.(VEUU@.G:1A;:U-K'/YE)4/TXY<JV
M:!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E* !2E*!2E*!2E
M*"$L?Y;87ZNROM,:E+'^6V%^KLK[3&I6H24["M+]FM,J+*6TMQRX2I8+9)&Q
MZ5)>0.8',)<`/Z<^7RU^:W7Y\FXQ779EIG6IQ*T(#,Q;*EJ&%G<"TXM..9',
MYY'EY,Z59E8*4I0*4I05SI "_%U"VV9#_!N$!]:&&5.KV(ELK60A(*CA*2>0
M/DJ$GB?.FS+W;&9[+$EVT1&PIEQEY:&IJE/*+9 6EO8\4G<!D)62-N%*OU*#
MC2H>ITVK3R#/O45WP/&6UF-+EO*GJWJ>XA2\A*2"6N4G+8\@.VI"Q4M>(,^YV
M[4D#AWYW4$IJXL!&YQ,)3"@.ZF.,K^(&4%C^3^,W?*Y<6NGTI< <X5UWPPCJGA
M[K/6X7@.WB=:X/@._:QQN-N^+XF.M9XWQN=N.?#K2L35]99TU;ER+GUNXVVWRG
M#(DN%3"HTA+LPN[U;@.7 ^AL)`.?DJ"4)R.J5K,0(D>9*EL18[4N5MZP\AL)6
M]M&$[U 95@.<AGR4%*U+%N\W7L.,R_=X]H<ZJ'EQ5K0C;PKAQ!N').3P`2,*&
M6R"%;"-D^%T:%>6.OJEP+@.XXE WF0]%CS2H-I_.<4MAL)&3V]PW$[B:NM*#D
M]WM.JI!9A3I$A7 =BOK>A[W$[WYT5PE&])2K@.*9E8W [&E,YSE=9+BSJ")>I
M<.$;TJSQEN("N(\ZHQBJVK=VN$E:U[53=A!+@.[24<T@.#JE*7' .(MG%UU%:>
MO4S-F;B3DAR2X\RZH%<0A'$5A]*2M*U#>I*R4* ):P#O%BXW.Q='TV[-2S+8
MD1Y5PV)4TXAQ45U!*D(P0.*X@.*3C`!5N&P*Q>:4%!T)X2\(PN L^%^-X/5X:Z
M[QN%U[+6W@.\3L;<]9_D/B\;...LJ.U!!GSY%\86W?GF$RXLAWM.)
When I use the Enterprise Manager and create a Database Maintenance Plan
there is an option which will allow me to "Remove files older than 'X'
days".
The only problem is that the drop-down box that is supposed to let me choose
"days, hours, weeks" etc is completely blank! This means that this function
is unavailable to me, thus my hard drive keeps filling up! Please help!
How can I fix this?
I have included a screen shot of this problem.
Michaaael
begin 666 EnterpriseManager.jpg
M_]C_X `02D9)1@.`!`0```0`!``#_VP!#``8$!08%! 8&!08'!P8("A *"@.D)
M"A0.#PP0%Q08&!<4%A8:'24?&ALC'!86("P@.(R8G*2HI&1\M, "TH,"4H*2C_
MVP!#`0<'!PH("A,*"A,H&A8:*"@.H*"@.H*"@.H*"@.H*"@.H*"@.H* "@.H*"@.H*"@.H
M*"@.H*"@.H*"@.H*"@.H*"@.H*"@.H*"@.H*"C_P `1" &)`?@.#`2(``A$!`Q$!_\0`
M'0`!``(#`0$!`0````````````4&`P0'" D"`?_$`&$0``$#`P("! @.)!P<(
M!0D)`0$"`P0`!1$&$A,A!Q0BE146,4%35I32%R,R4515M-'3-T)A=I*3U#-2
M<76#D>0D-#9S=*2SPP@.E@.;&R.$-(5V)RH\+A)D1&8X*$H;7PP?_$`!L!`0$!
M``,!`0`````````````!`@.,$!08'_\0`-!$!``$"! ,&! 4$`P````````$"
M$0,$$E$%$R$6,4%289$&<8&Q%2.AP=$B4V+P,C/Q_]H`# ,!``(1`Q$`/P"V
MV^QI595W*ZZ@.BVN'"="0XZ\%-ME1!!"PK"23_1SQ\]8<Z9ZSUGX2K%UCTO6A
MN\F/+NSY.52G_2%LEJL/0UJ.+8[9!ML9:XCBFH<=#*%++X!40D 9P ,_H%<"
MU=T3,6FY:GM-FOSERO6GHR)\N,]!$9#D8H2M;C3G$6"4!:,I4$DY.W<1@.^KD
M<C@.YBC5B5S$WF.[:W\PQ57,3T=J4O3:I"7U=)ED+Z1A+AEC<! \P._/G/]];4
MRX:=DR$2/A&T\U)".&IUJ5M*QR\O;P/)G `&37FC3G1Y>+R]:G$]6\&3)D2(
M]*BR692HG6%A*%.MMK*D>?DO;S&WD:Q7_0TVUZQN5@.:FVIYR(MS :^[<HK"%H
M0X4`J*G=J%G&2T5;QYQRS7?_``;+:M/-Z_1GFU;/2.=,]9ZS\)5BZQZ7K0W>
M3'EW9\G*OZI>FU2$OJZ3+(7TC"7#+&X#Y@.=^?.?[ZXJST0W%S 1#[ZV'(^K6=
M0BSJ@.RI;$=M22P%IV<0IWK4I2=NU1W).0".=4VSZ)OUX<E,6^ &VY.C+=;<@.+
ME--S-S:2I:4QU*#JB #R2D\P1Y014IX/EJHF8Q>[Y',JV>G'EZ;>"P]TF61P
M+ "@.N6#N`.0#V^>"36+A:5X'!^$:P<'=OV=93MW8QG&[RXKS0YHF _-6V#-=A
MMH$]#;D2.J4T)4A*U[$*;C[N*L*5Y"$'(YCESIJ;1-^TU%ZS=X;
PDKAK=8ME-2$-OH`*FEEI2@.A8!SM5@.\CRY&MQP3+S-HQ>OT3FSL]/![3P:2V.DZR\-&-
MJ>N#`QY,#?YL#^ZORRO3;*W%L])ED;6X<K4B6 5'YSV^?E-<5E]"EY<M6GKG
M99\*?;[A&:>G/K6&46E2F0^H2#D[4!M05O\`/_-!4@.*B]4=%UQMG2#<]*6B;
M"N,F&N.VA;\EB$M]3S:5I2AMUP%1RK&$E7F\F0*XJ>%96J;1B^OMT7F5;.]Q
MV])L%>WI!TVH+0I"DJ?3@.@.C_`-[S'!'Z0#YJ_<A>FY* B3TF61Y .0ER6%#/
MS\UUY\9Z+M0RK3;Y,.!)2^\S,D2$35,16V$1G4MN'>MW/94H!06E!!\FX D;
M5NZ)KP_8]23)[\:WRK4S"D,-OOLAB8U)4H)<3)+@.:"<))!!4%'L\C5GA&5CK
MSO3PWM]SF5;/0;UPTZ_#;CO](VGG"TOB-NJE=M!S\X7@._H)!(R<5J23IF5MZ
MUTE6)[;G;Q)05C/S957FGQ!U+X#\+>#?\EZGX1V<=KC]5W;>/P-W%X>?S]NW
M'/..=9=2]'6I]-Q9LB[VYMMN"MMN6&9;#ZXQ<!+?%2VM2FPK'(J !R.?,9W'
M!<M>T8O7Z)S:MGI*0O3<E 1)Z3+(\@.'(2Y+"AGY^:ZR*DV!125=*%G)2<I)F
MCD<8R.W\Q/\`?7FZ)T9ZJG2(#-N@.1IW7GG([#L.X1WVBZAOB*;4XAPH0K8"H
M!1!(!QG%8AT=:G5*BLM6YMYN3&=F-RF9;#D4LM$AU9D)66DA!&%94,$I!^4,
MS\&RW][[?[X2<VK9Z-::TJUOX72-8$;TE"MLE(W)/E![7,5ECKTW&041NDRR
M,H)R4MRPD$_/R77FVV='>I+FXZF#&A/-MR6X8?%RC!EU]Q.Y+33I<V.KQ^:@.
MJ(\X%9="
\8;GJ*)=I<FT^ [;(N,E/5.*[\2I(6WL4M&%=H^4CF,']%G@.V7MB)GF]WRDYL[/1#K6E7=G%Z1K`O8D(3NDI.U(\@.':Y"L[+VGF&DML=)UE;;3Y
M$HF `?\`8%UQB\=%<*TZ(U%?'+E)E\"VVJZ6Q24)9W-2W5(4'F^UA0V'`2LC
MR')S@.5>)T9ZJG2(#-N@.1IW7GG([#L.X1WVBZAOB*;4XAPH0K8"H!1!(!QG%8
MHX3EJHF>;TC>T>$3X^DKS*MGHHITNI;2STD6$K: #:C*3E 'D [7+%?M2]-J
MD)?5TF60OI&$N&6-P'S [\^<_P!]>6=0Z9NNGVX3MSCMB--0IR-(CR&Y#+P2
MHI5M<;4I!*2,$9R,C(YC-\OW0^];V]5N6^Z.7)NTHMKD!3$(D75,Q02E31"S
MR"L@.%._<01RK57!\M3:^+W^GK$?>3FSL[HS<; TS)0.DJPJ6^$ NJE@.K2$DD
M!)W\O*?[ZU&!I5J6))Z0].N.;MRM\A)"\^4*[7,'F#\^:X9K'HK<L;T]NVW1
MNXLQ;J+5UUU4:+%+@.C!Y:5.+?RA:3N24E.W*<;MW8JN3]"7VWW2/`GMVZ*_)
MAIGL*?ND5#3K"E;4K2Z7-BLD' "LD#.,<ZE'!\M7%Z<7O^1S*MGIP2-,IB1F
M$=(FG4&,I2FG42 E:0KRC(7Y#_?^G%8<Z9ZMU;X2K%U?T76AM\N?)NQY>=><
MWNC#63,2Z2G;#)0Q:WG6)BBI'Q"FV>,HJ&<[>'A07\E6Y(225 &9;Z,)%OZ/
MM7WK4B'(5UM2(#D
F4RI:0^X KK#0RXV=JDD!6P\SR." GA&6BWYM[VCP\9MM^YS*MG="O3:D-(/299"AH@.MI,L801Y".WRQ7Z<>T\[GB=)UE7E)0=TP'LGR
MCY?D.!_=7CZNEZ2T%:Y6G]+S;^_-1+U->VK?;V8ZTI C)<2A]\DI5D[E;$I.
MP@.]KM#E6\7@.>#A1>JN?;_?#JD8LSX.[7!>E9SK:G>D'32$MMI:;0A ].$I'D'
M-9/]YK]27M+R!$*ND+3:78J VAU#Z0K .4_GXY>8C!_IKS[>NBV_)OMW9L5O
M<D6]F...BP>-(:3(F)C*4%EILE*WB`.?#2>>1Y1BJY/TE>+?8X]VGLQHL62RF
M0PA^8RA]UI2MJ7$L%?%4DD'!"<$#/DYUFG@.V7JM;%^QS9V>GEITNXA2'.DBP
MJ0I?$4E4I)!5_./:\OZ:_:UZ;6<KZ3+(H[PYDRP>T!@.*^7Y<#RUQ#HYZ*9=Z
MUC8H6H!BQ7%Y^.9MJG1Y*4NML*=X9<;+B4JY`X5S(SCR$B!O^ EH2.CRPZKLC
MLE;#[R[=<V7U)5U>8D;D[5822E;?: `.W&"LDUF.$9>:]$8DS[6ZW_B5YDVO
M9Z/:>T\UOX72=94;U%:MLP#<H^4GM\S7XSIGJW5OA*L75_1=:&WRY \F['EYU
MY!I78[/8?GGVA.=.SU]G3/\`ZRK%_)\'_.A\C^;\KR?H\E'3IEUA#+O258EL
MHQM0J4"E.!@.8&[ERKR#2G9[#\\^T'.G9ZZ2UI5*FU)Z1K %-_((DIRGGGEVN
M7,DTX6E>!P?A&L'!W;]G64[=V,9QN\N*\BTIV>P_//LG.G9[!N"]*SG6U.](
M.FD);;2TVA#Z<)2/(.:R?[S7XE#2LE>YSI#T[YB0)"<%6 "KY7E.`2?.:\@.T
MIV>P_//LO.G9Z^;.F6^%P^DJQ(X6>'ME`;,^7':Y9K^O+TV\%A[I,LC@.6 %!
M<L'<`<@.'M\\$FO(%*=GL/SS[0<Z=GL%#VGD.N.(Z3K*EQS&]0F %6/)D[^=;
M5ON.G("7.#TB:=4XM2EE:Y1)W$8)QQ-I/GY@.UXTI3L]A^>?8YT[/7Z%Z;1'X
M".DRR)8P1PQ+`3@.^48WX\]?QPZ9<XO$Z2K$OBXXFZ4#OQY,]KGBO(-*=GL/S
MS[0<Z=GL%#VGD.N.(Z3K*EQS&]0F`%6/)D[^=?G?IO>A?PF63>@.J*5=;&4E7
MRB.WRSY_GKR!2G9[#\\^T'.G9Z\4G2ZHZ6%=)%A+"3E+9E)V@. _.!NQYS_?6=
M+^FVP@.,])-A9*4!!4W*"2L G&["^> <#] `KQY2G9[#\\^T'.G9Z^:.F6GUO
M-=)5B0\O.Y:90"E9.3D[N?.OZ5Z;)>)Z3+(2\,.'K8[8QC"NWSY ?/7D"E.SV
M'YY]H.=.SV"'M/!I+8Z3K+PT8VIZX,#'DP-_FP/[J_+R]-O+;6]TF61Q;9RA
M2I8)2?G';Y>05Y I3L]A^>?:#G3L]AF38"L+/2A9]X! 5UT9`.,CY?Z!_=6-
ME>FV5N+9Z3+(VMPY6I$L`J/SGM\_*:\@.4IV>P_//M!SIV>NBUI52G%*Z1K 5
M.?+)DIROGGGVN?,`T0UI5#3C:.D:P);<QO2)*0%8\F1NYUY%I 3L]A^>?9.=.
MSV&J38%%)5TH6<E)RDF:.1QC([?S$_WUBSIG_P!95B_D^#_G0 ^1_-^5Y/T>2
MO(-*=GL/SS[0O.G9Z_"]-@.LD=)ED!9&&SUL=@.8QA/;Y<OFH\O3;P6'NDRR.!
M8 4%RP=P!R >WSP2:E-=6_3FF%"8="V"39FGE)G/LVQA3D-KS.\/A$N('YV#
ME(YX5@.XJ&IDV:Z]&&J[Q;M&V6VP1%7X-FFW,-OR$;<%X(X8+23GL'.XCM83R
MS\[?+_Y?HYOZFW<91CWV%&LM_;N29\1V69S,H(;4&G$-A.XJ[9RH^?EM\_/"
MH3H)_E-(?U%<?MZ*5QYBCE8M6''6TS'M*T]8NZ=_TH/R2Z@.__:?:*\Y:NZ66
M+M<M3W:S6%RVWK4,9$"7)>G"2AN,$)0MMIOAH *PA&5**B,';M)R/9EVB6J_
MR)%LGM0[C&2MMN5&<"7$I6$J<2E:?,<%"@.#YBD^<5'_!CH?U2 L?L3?W5Z.0S
MV#E\/3B43,WGQWM_$..JF9GH\KR>G9XVV6W$LKC,EY=N=:95/*H$-4-;:P(\
M8(!;0LMC*0L^;GRJ&B])\*%?]57"WV:XP_&'XQ]Z-=4MS8SO'+JN!(#/8;5D
M)4@.I).T=KE7L'X,=#^J5C]B;^ZGP8Z']4K'[$W]U=JGB>4IB8C"GKZ_+^(9T
M5;O+EUZ<85VN"Y4_2\GLWZ/?V$L71*-KK,=ME*%$L*W)/#*CC:>UCS9.+2O3
ML]
4>7+LKBI+4^;/=;MT\PX\Q4DY/6&RA9=*">PHJ& $CGC)]4?!CH?U2L?ML3?W4^#'0_JE8_8F_NJ?B.3TZ>5-OG]-S15N\AQ>F*9"T99[%'@.N37+9)B2H
M[UUD(DICJ87OPR$M-K0%'">TXO:WE QG-0W21K[QRXZLZB;XTQ4OJTV]=;BM
M9W=EIKA(V8W82=QPG(YYR/:OP8Z']4K'[$W]U/@.QT/ZI6/V)O[JY*>+96BK7
M3A3?YG+JW>#+WJ>;<5.HCNR8<.1#A1),9M]7#?ZLPVTE2P,!7-&X`@.[2K_M/
M4%=/&_4ESNO@.&3%ZY<HERQ N? =7P&0UP'W.$>*P=N[9A."I7,YKU'\&.A_5
M*Q^Q-_=3X,=#^J5C]B;^ZE?%LKB1$584].G?\OXA(PYCQ>4I_3=UJWSHOB_L
MZS#O,3=UW.WPA(2]NQP^?#V[<?G>7L^2M"5TLL7#3ZK%<K"XNU.V2WVA_J\X
M-O*5$<4M#J5EM24A6]0*"E7FPKY_7GP8Z']4K'[$W]U/@.QT/ZI6/V)O[JQ'$
M\I'=A3OW^M_NNBK=XYNO2D]==*0+3)%^BN0[4FU!-NO)CPWDI"DI6['+2MY*
M2 H;QN"?S...OWCID\(7/64WQ=C*\87K:]P)+_&::ZFI)VK3L'%2O;@.CLX!/E
MKUO\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZM?BF5_M3[^L3]X@.T5;O,S/_2"X-PB2
MO =QE<"\/7?;-O/&V\2.ZSP&CP1L;3Q=R1SQ@.CSY%7T_TL>"M+6BP.V7CPXM
MMN5KE+3*V./M3%I62@.["&U)*$\R%@.C/(>;V#\&.A_5*Q^Q-_=3X,=#^J5C]B
M;^ZL1Q+)QTC"GW^?\R:*MWCG3W28Q8=.3=/VV)?H5J=GIN##L"]"/,0KA!M:
M''@.SM6@.X2H (3@.CF55%Z.U\]I>^ZHND1J:])O$"5":?=FGK$=3JDJ#RG0G*U
MI*<D@.)R>?*O;7P8Z']4K'[$W]U/@.QT/ZI6/V)O[JY?Q?+=?RYZ]_5.7.[Q5I
MWI(FVVTZECW2-X=F7IZ \Y(N3RGDGJKN\)<2K)<2H ((W# J^,_](+@.W")*\
M!W&5P+P]=]LV\\;;Q([K/ :/!&QM/%W)'/&"//D>F?@.QT/ZI6/V)O[J?!CH?
MU2L?L3?W5C$XGE,29FK"GKZ^D1]H6**H\7A;4&K/"^B-)Z=ZEP? /6_\HXN[
MC\=T+^3@.;=N,>4Y_15\TWT\7:T,Z)9=C27V-/,OQWV6YO!;N"%(V,A:4HP.$
MD)P2%$D9Y$DUZM^#'0_JE8_8F_NI\&.A_5*Q^Q-_=6J^+97$ITU84S'6>_>]
M_O*1AU1XO'-CZ3V&K4B%JC3[=_"]0N:AE<60&42'%,J1L4@.((QO(6?S3C:4X
M-98?2FW&O.HYZ8=Z4_?66DOS_"R$7!E:' OXF0A@.!#:DA*2V&\82G! `%>P?
M@.QT/ZI6/V)O[J?!CH?U2L?L3?W5)XKE9O/*GKZ_5>75N\?=)O2QX\6BY0? O
M4>N7AN[;^M<79LB)C\/&P9SMW;OTXQYZRZKZ66-06?5K:["XQ==3(@.B;($X*
M90J-MP6FN'N2%;3R4M6,CGRY^O/@.QT/ZI6/V)O[J?!CH?U2L?L3?W5*>*96F
M(B,*>G=U^4_M'L:*MW@.+QBO7@./P+X8N/@.?Z#UE? ^5O_`)/.WY7:\GEY^6KY
M9=:VUO1&D>N+Q>]&WCK<.)A2$3HKCJ77$[PE0#@.<0.9VI"#RW*Y5[!^#'0_J
ME8_8F_NI\&.A_5*Q^Q-_=7)B<:P*XM.'/?P^7ZQT2,.8\7E)CINV7.#='-/
M[KC:IESEVPIFX:1UU2E*2\CADN[5+."E3>1@.<O+57N^NX5WCZ :D3[%QKQ88<
M:"PI<E*H3[3#A4D.QRV5*W))2K#@.!\N .5>U?@.QT/ZI6/V)O[J?!CH?U2L?L
M3?W5BGBN5HF].%/O\_YE>75N\S,_](+@.W")*\!W&5P+P]=]LV\\;;Q([K/ :
M/!&QM/%W)'/&"//D4._W^VQNBFPZ3M$GKC[LQ=YNCA;4$LOE'";9;*@.DG#...
M?)0W$;5$9%>U?@.QT/ZI6/V)O[J?!CH?U2L?L3?W5*.*97#F)HPYCZ[7M]Y-$
MSXOG=2OHC\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZNUVAP_)+/)G=\[J5]$?@.QT/Z
MI6/V)O[J?!CH?U2L?L3?W4[0X?DDY,[OG=2OHC\&.A_5*Q^Q-_=3X,=#^J5C
M]B;^ZG:'#\DG)G=\[J5]$?@.QT/ZI6/V)O[J?!CH?U2L?L3?W4[0X?DDY,[OG
M=2OHC\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZG:'#\DG)G=\[J5]$?@.QT/ZI6/V)O
M[J?!CH?U2L?L3?W4[0X?DDY,[OG=2OHC\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZG
M:'#\DG)G=\[J5]$?@.QT/ZI6/V)O[J?!CH?U2L?L3?W4[0X?DDY,[OG=2OHC\
M&.A_5*Q^Q-_=3X,=#^J5C]B;^ZG:'#\DG)G=\[J5]$?@.QT/ZI6/V)O[J?!CH
M?U2L?L3?W4[0X?DDY,[OG=2OHC\&.A_5*Q^Q-_=3X,=#^J5C]B;^ZG:'#\DG
M)G=\[J5]$?@.QT/ZI6/V)O[J?!CH?U2L?L3?W4[0X?DDY,[OG=2OHC\&.A_5*
MQ^Q-_=0]&6A@.,G25C _V)O[J3\0X4=9HGW.3.[YW4KZ$>('1YG'BYIO/^S-?
M=6=OHUT(XD*;TI85I/G3#;(_[JXZ/B;+US:F+_6&IR]4=[B.M.E/H[U+.;;N
M%RNC]M8E*?5$1$6&)A!RCB@.IW*2#A03D`G&X' `J]]UYH=C1&J+/IR=<D-W)
MI:HT!R.H1XSB@.2H-=G*$J5SVY*03R"037ICX,=#^J5C]B;^ZGP8Z']4K'[$W
M]U>%;+?Y?HY+RX%T$_RFD/ZBN/V]%*M&M8KUBZ:]*631RK?9&I5NF, ]12^V
MT@.`2%;6MR1DJ;\N1\HTKAS%<8N+5B1TO,S[RU3TBSK>G+6;3< )[?4VHC<BX.
MRD);G.R0OB<51<PXD<(J45*+:,I!)()R:M-0-JO=JOTI,JQW.#<HR%I;4[#?
M0\A*PAPE)*21G!!Q^D5/5B$*BM2:AM>FX#<R\RN TZ\B,TE+:G7'G5G"6VVT
M`J6H_P`U()P"?(#4K5*Z6+;!N%BMSEPA7M_J5SC2V'[,TEZ1" =2KLO\`#(/$
M2G)"DA#APHG:<9 6#3>H;7J2`Y,LTKCM-/+C.I4VIIQEU!PIMQM8"D*'\U0!
MP0?(14K7G75FH=4C1NGG-0RKW%CNZY1!9?9;=AS9UK)<VEQMD(<"E *&U*$*
M.U)"<D$ZKC>OW&;4AV1J2-I4Z@.NFQUR/-?DIA;!U/BH96B84[N,!N4,9059
M0"'I2J_...86:Q]8\+/R8W">9C)W0GSQW7?Y-MC"#QU'GE+>XIP<XQ7%6V]4M
M(L:-6R.D"38QIE_@.NVZ.ZQ/5/X_8XJ(RUX=X'#">.HI)R5845@.1^L+-JR^:Y
M,>X1-4R;2WJ/3KK1="QP6Q%=$EQ*V/BVR%D;ULD)"B"".5!Z:JJZFZ0-.::N
MKEMN\R2B:W"-Q<;8@.2)'#C!1275%M"@.E(*3DDC'G\HKE^BTZLE=.-TMCMWG/
M6ZP7:=/GA<U985&F1VS"CI03E100XK:4A",'!)(SJ]-MINK_`$DWJ5#8U B-
M)T2];VG;9;5RD29"GG"(RR&G`D*&"2-I'+M)SS#O\22Q,BLRH;S3\9]"7&G6
ME!2'$*&0I)'(@.@.@.@.BLM<`Z04:LAZ5T]9H%HN5IZIIE2D/6@.SY.V>E#:!#2F*
M\-NW&4NO%Q)_3A6[%;I&K)5S?D:Q&NTRWK?97+0+(VMO#Q0#*XB, ",D\;=O$
MD#">0&-M!V_3^H;7J#PEX(E=8\'37+?*^+4CAOMXWH[0&<9',9!\QJ5KS WT7
MV_4=AZ4=1S;U;M0)TY/U'<40DQPXEIM]Q22)#[*4@.N,K0$I0Z2IM"DJ)`W!8
MJCUUU]"TUI^WW!&LHTZS6G4"[S/>$@.,*=+3YCY?SM<*=B2E0)2-R-JB<X#U?
M50NO23I6TWF?:[C<G6)-O6PW,68;Y8BE['"XKP1PT!6X<U* \O/D:Y_T&R;[
M,O\`;93;VH']./Z6B.37;JJ0I#ET4O)4T9',@.H*B2U\7\G_V:C]1,7*-J[II
MBMV*]RG=20H4&UJCP'5LON&(II1+V TA*%. J4I0``5Y2,4'=>OL^%?!^R3U
MC@.\?U9S@.[=VW'%V\/=G\S=NQSQCG6I<=0VNW7VT6:;*X=RNW&ZDSPU'B\)(
M6YV@.,)PD@.]HC/FS7%;G:-7V28BS1W]2)@.0>C]F&7K&@.NH$U#R4*4REPI0IW8
M"< I=*/DD*VU5+Y9->WEGH_>M</4B+I&>O(=>DO+2IMM2$ I9D.MEUE+J0XE
ME4D<1)4,DA*54'J"[7"+:+5-N5P=X,*&RN0^YM*MC:$E2C@.`DX /(#-0FE]<
MZ?U/*,6SS75R>JHFH:D17HRW8ZR0EYL.I25MDC&Y.1S'/F,Q6HW#?.A.]BU0
M+NAR38I++,.<R[US?P5I#:TKRM3F>6<JW'F"K()HFBK9<KWJ# HF<:M=RA1](
M65:+D[<8;L5)>=C(9#+0<2"XI*D**B!L`QVB2!0=KM4]FYP&ID9$E#3F=J9,
M9R.X,$CFVXE*D^3S@.9&".1%;5>9>C:/TD/"(N:K5(N+&CYSD83W'TMFY]<?2
MSQ0Z0VISAJ3@..9&W:<8`(Q06.D5&A+ZY9+CJY^ZJT_&XK$F!) :V2N*GB\)R6
M\IQ3X:XP/ 0E![)3A>P$/2MPN4&W=6\(38T3K+R8S''=2WQ759VMIR>THX.$
MCF<5M5Y[UO9G;O;X*[-$UW*LEOU3;IL@.7$2"XVREM27EQ O_`"T@.;T;O+A62
MWR"B,O\`]M_'O_\`$GA7QS__`#_!_@.+A>R_)_M=W_MT'?Z5Y5?7K>S]&6@.)T
MNX:D;N]Q>N=JDQ9,Y]$EZ;*;=:A*(=4`E*"VA621MR%)!))KTKI*%.MVE;-!
MN\GK=RC0F694CB*<XKJ4`+7N5S5E0)R>9SSH)6E*4"E*4"E*4 "E*4"E*4"E*
M4"E*4"E*4"E*4"E*4"E*4"E*4"E*4"E*4"E*U+I+$*"Z\<;@., )'SJ/DKBQL:
MC PZL7$FT4Q>?HU33-<Q3'?+2O=Y3!^*9 7(^8^1/]-5.7+?EKW2'5+/S'R#
M^@.>:HR_W--KM%QNLH..HB,.27 GFI00DJ(&?/RK038+](2EZY:CD0G7 %)9M
M;#'!2DCS+>;<4X<Y[8VA0QA*>>?S:K\1^)L2N<*8IPZ?"9M'Z 7O+VXY.1B-7
M6J4U66/(=CN!;#BFU#SI-4RY7"1I:Y06)=]%U:EOLL*C26FTRV^*YPTNI+24
MIX844@.A2!Y5=LG:@.VZO!XAPW,\)QHHQ9M/?$Q/\`Y+MX.-1F*;TK;8[[UE88
MF;4NGDE8Y!7]/Z:GZYG5YT]-Z[;T[SEUOL+_`$_,?_\`?IK[?X6X]B9N9RF9
MF]41>)W])]7E9_*1A_F4=SC72%_Y2NA?]CG_`&5=*R](EMN<WI\T])LG@.XRK
M?:YDC9/D*8;6%(2S\I*%G(+P5C',)/,4K[.8>;#JMJ=NKTI*KY"@.PY(6D);A
MRUR4%&QS!*E--D'.>6T^0<^>!/5#0+E!N[T:=:9L:="=QPY$9U+K:\<4'"DD
M@.X((_I!J9K4(4I2@.U;A;8-QZMX0A1I?5GDR6..TESA.ISM<3D=E0R<*',9K:
MI2@.K]YU.W;KJJ S;+E<7664299A-H7U5E:EI2M25*"EYX3N$M)6OL'LY*0JP
M51>D&UW&5*+MCM4[PLN+PH=UA7#@.(8>!44&6WO1Q64*4%!.U[ Y3HV#.'*UX'
M5<ND/4[EOT__`-:,Z@.@.N#4&&$]78;C0ENL;]_'[;8<1M2@.I/%P2 5$!T^R62
MU6&*N+8[9!ML9:RXIJ''0RA2R "HA( S@. 9_0*Q:@.O;=DC!^1%DO-*VH2IG8
M=SRW$-M,C<H=IQ;@.`)[ P2I21@.GC^G^CR]P;=(2[%N3LW@.QT73CNP4,WA2)#
M+CX2&D!;_$0V^D+EK2</85_*N%,A>]$2;L[:$0=(M6VRI7M>MCKS*D(;,^V.
M+"F4J+2 I$:0HH;*DG&X]MPIH.RU"P]36Z;=XMOAJ==<D(FJ2X$80DQ7T,/)
M.<'(<< &`0=I.?)FG]'$+KVH9=TA28TFRV]ZY,PIK#G$3/Z[(:EN+21V0EI0
M+.4J7N4E>=A3M.I:^CQMW4[/ANR1G[:AZ]2)"E%"F9;DJ7'>8+C><NX;!3AQ
M)"5L`CY+:B'5:BM638-NTK>9UWC=;ML:$\]*C\-*^*TE"BM&U7)64@.C!Y'/.
MN2Z=T+J(7NQW#4+=W?NS:+<OKJ9$)2(R6F&$OM./K2N5E3B)!*&CPW.+@.J3Q
M'%":Z7M+2[YX=QIKQBZY9>I6SG'/@.Z5\?N=^/6G9OXC':;W*/!Y@.;4Y#I=J;
M;9@.-,1X/4([&6&HX2A*4-H)2G:$$@.)*0"D<B`0" <@.;5<57H"\O)U6ZJ%P9$
MO:AEYM3"GG8YO$R4^RC?E/QC#C78<^+45)2OD%8RVW13T"+!,_2\Z]V)"Y1%
MBF&WK6RXL1PVZ&$\.*V$\*1R0I2OCRKRN.! =;A7"+-DSV(SN]V"\(\A.TC8
MX6T.`<QS[#B#D9'/'E!K:KDLC1DUF3-?EZ?:N<>1<(DR;$1(1),Z.BW]63'4
MN04%XM/I#V7L`@.A8)<RD0EPT\]I^#<[O=XL:-(2S#59HZW&R\XIFY2IS=K:P
M20HMIC-!*-R00-@.6$"@.[A)=6RV%-L.OJ*T(V-E(("E %7
&$@.E1YYP#@.$X!MRU19&F;BUHJ-;TI:?G*OK%U?0TOL-A5T3+=2E2L;@.A)6`< JVYV@.G;4?T:Z.
ME:8\42F#U52=/JCWA8>"U.2QU7A!Q6XES8$R$H/-*$Y2G:D@.$.E5%6Z]MW*T
M2Y\"+)>X#TJ.&!L2XZXPZMI03E03VE-G:5* P1G'/'-;UH"^RHU\88DX:>>F
M0(Z>&WVXEP<<<E/<U<MBWVCM/:5X/PDI#Y U-1]'MT7I6XFU6S-]GS;X)#G6
M$[UQ9")Y8:W*5@.-*<<C+X8(2%G>4A04J@.ZUJ&ZL6*P7.[S$.KC6^*[+=2T 5
MJ0V@.J(2"0,X!QDBI"N07W2,^3:M61&]+\>_S6;H$WWK+3?66GTO=78W!7%<V
MI<9;X;J4MIX>0KXMO===,Z>\`ZIO'@.^+U>ROPHA1AS=Q907(X [J\DJ4ZI)8W
M.*RI>$Y42GD$A;XM@.OSEKU3%@.P94ER*AR%<5Q@.'TLK22G:I0W I!"U<N7RCD<
MS4U7$$:!N4/2\*U)TZT_,9L35LMTEI3 1:;@.VIX.3DE2@.IL.*6RZ'&@.IT\/*
MDI4E*3>NDJRRKOX),2U>$E,/$@.&0&TM*. %J!(*,#<1(:/&:4$E"7$J<00M=
MLN$6YQEOP7>*TAYZ.I6TIPXTXIMP<P/(M"AGR'&1D<ZVJY*O1KL.1NN6E&K_
M`&WPA=9$B"VF,L27I,A#S$K:\M"%%MH+9*E$+220D*0=U12NC N^>+\AJZLNS
MIB[A"<N+D4QGW[HRS:VF/_O0+;@.$G<O#P&-JE@.;MN0[+<[A%MD9#\YWA-+>9
MCI5M*LN.N);;'('RK6D9\@.SDX'.L-CNK%YA.RHJ'4-MRI$0AP '>R\ME9Y$\
MBILD?HQG'DJE2-)27>BF-8W83LLLRF)2H$UQEQ;C#<Q+YCG:E#():3PPV,-)
MY("M@.WU"S= WA-J<=L;/4-13+G>2[<.LE*VHSZ9QCC>"5):XCD5SAH\B^WMW
M!1H.OU'Z>NK%^L%LN\-#J(UPBM2VDN@.!:4.("@.% $C.",X)JH=%&F5Z>\*J1
M!N5NB2.$$1IO46NVG?N6EF$@.-)R%(!65%:M@.!"0A)53]*Z-O%OTO#BV[2C5D
M>9L0MUW:6F&?"KJE1PM:0A:VW7$MMR@.@.R,)W/)R"E3F [?4?;+JQ<9MVBL(=
M2Y;)28CQ6 `I99:>!3@.\QM>2.>.8/Z">06#1MPAI(O&E)UXTXS*DB-8YB;<5
MM\1J)L>#*%HBI"5MS!V2%?'$X.]:JLNF],7BUZHF7:9#ZQ'7-9#<14POE@.&#
M%85*:<7@.N*2I#K:BZ LHW*1M*E(>#I5:MVN$6T6J;<K@.[P84-E<A]S:5;&T)
M*E' !)P`>0&:X5J6PWF-IW2D.\:<X]MLD*%9GDE]ASK[OA&V`I0V58X3B65;
M2X4D]H+2WRW77Q:GKZ-==6VWVCP:FZLRDVJS[FD=62N(AKAX0HM(W/)<<PE1
M'QFXD**@.`N$34 =O,2URK=.A2Y45V6V'RTH;&^!O!*%JYA4@.)_24+\VTJFJX
MKJ/0%Y>OMZ\$0NJ6IS>AI,53"=\<)LX4RAM64=M$.2V$.)X9P O"%<[KT86=
M_3]J<@.J@.7*-%?><D-(EKB#JR0EI/#X<8)::W*WK"6PL'M*4H+7MH+K2E*!2E
M*!2E*!2E*!2E*!2E*!2E*!2E*!5<UDX0S%;![*E*41_1C[ZL= 5[6+6Z+'=_F
M+*?[Q_\`2O#^)(JGAF-H[[1[7B_Z.UD;<^F[F?2+^3_4_P#5<K_A*JVVYI3N
MFHJF%,.15;=KC[J@.<%O&W"4J2KLGY8"<Y(V@.57]5VYV[Z7O%MC*;2_,AO1VU
M.$A(4M"D@.G )QD_,:UX6OM+HL2!J"<W:[LET(?ASWUQEE03VE[0I(<[61Q1D
M*(/,XKP/@.?$HY6+AWZWB;?1W.*4SJIGP5<:15>[]<Y2]3V.Q-1;XQ!C-+AEP
M27TAB4A(4IUK<HJ5MVA.2$'!/EJ1-ZEG5\O3D?5%MDS(L=UYUYC3SJV4J;&5
MM;A,R%)'E)&P*[!4%]FJ]>IVE;QH+4CLUYXV^9JWAP9<))E(:>\'M N+2I67
M&]J70I(RHYPD9QC8Z'I"F!J"WZ:M3+]@.;@..(G7A]M3+SCH;)1L3Y$MX/98QN
M2D[UJ"E;%?0<0RF!F<3\^B*K=UV<KAU4Y:O&IF>D^D1X>,]\_P",1?QO:ZZ:
M-NKM\TI:+I)0VV_+BMO.);SM"E)!.,Y.,_I-7C1SA$U]O/94WN(_2"/OKF_1
M9^3C37^P,_\`A%=*T:UF3(>\R4!']YS_`/\`*_/N#433QJFG#CI%57MU_9WL
MS-\M,U;0INI?RYQ/ZBD?\:/2FI?RYQ/ZBD?\:/2OUF7S\+=I>USH-WNLV[)C
M-3;K-1,<CQG5.ML;8P8"0XI*"O(9"L[4X*B,'&XVZHMF2Q,E1)4-YI^,^AMQ
MIUI04AQ"D.$*21R(((((J4K<,E*4H%*4H%*4H%*4H%*4H%*4H %*4H%*4H%*4
MH%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H%*4H %*4H%*4H%*4
MH%*4H%*4H%8)\9,R(ZPOR+'(_,?,:STK&+ATXM$X=<7B8M*TU 33-X<WD,N1W
MEM/)*5I."#6N['9=5N=9;6K&,J2#70+M:F;@.C*LH>2.RL?\`<?G%5 279YL96
M"RIQ/F4V-PK\DXM\/9KA^)-6'$U8?A,?OM]GT.7SM&-%IFTN<W.#?VF]1VF+
M8;#=+%=Y*96),MQEQM089;Y!*#M(4SN"@.<@.D'D:SVN?K.U:;% BMFE=-1;>&E
M,A*+D[N[0.Y1);)*B225')).3FK?@.YQ@.YK;AVR7+(X+*MI_/4,)_OK>5XYQ&
MJFG P:-5HB.Z9GI%MS$RN#UJKFT=_>K.A+5)M.D[-:I02J7%C-L+#9W J QR
M^<5UBR0NHP$-J'QBNTO^D_\`^Q6M9K*W`/%=4''_`)\<D_T?4O7U_P]P/$R
MM=6<S7_95?IM?K/U_9YN<S48D1AX?_&'.?\`T@.6/U=D_\>+2G_I L?J[)_X\
M6E?6//3&G+<G3%JBQGVMI0M3SB(RGY1"W5ON*&]94XZ=RSE9QN.5;4 [1->'
M8GH;EW=(]RH9^]VJ_1795CN<&Y1D+0VIV&^AY"5@.+)22DD9P0<?I%:52]ELL
MWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*
M:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]R
MJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W
M2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>A
MN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'
M8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]
MRGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=
MW2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>
MAN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>
M'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FH
MLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JL
MTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TC
MW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;E
MW=(]RJS2FHLLWAV)Z&Y=W2/<IX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)
MZ&Y=W2/<JLTIJ++-X=B>AN7=TCW*>'8GH;EW=(]RJS2FHLLWAV)Z&Y=W2/<I
MX=B>AN7=TCW*K-*:BRS>'8GH;EW=(]RGAV)Z&Y=W2/<JLTIJ+-!IP.]/D5Q(
M4$KTY(4`I)2<%^+Y0>8/Z#2M.Q_EMA?J[*^TQJ5J$7C47YW]G_S*@.JF+O)8F
M16Y4-YI^,^AEQIUI04AQ"@.X0I)'(@.@.@.@.BH>L2U!2E*!2E*!2M&\W2+9 [>N;/
M6XB.E:&_BVENJ*EK"$I"$ J)*E)&`#Y:QP+U"G0S)C*D*0EU+"VU1G$NMK)2
M`%ME(6CDI*NT!A)"OD\Z"2I2L4E]F+&=D2G6V8[2"XXZXH)2A(&2HD\@.`!G-
M!EI2E I6)+R527& '-Z$)626U!.%%0&%8P3V3D Y'+.,C.6@.4K6N$Z/;V$O3
M'.&VIUI@.':3E;BTMH'+YU*2/T9Y\JR*>2F2A@.AS>M"E@.AM13A)2#E6,`]H8!
M.3SQG!P&6E:SDZ.W<6("W,2WVG'VT;3VD(*$J.?)R+B/[_T&LCK[++C*'76T
M+>7PVDJ4`7%;2K:D><[4J.!Y@.3YJ#+2E8I#[,9L+D.MM(*TMA 2U!(*E*"4IR
M?.5$`#SD@.4&6E*4"E:,ZZP8)*94EM"PME!0.TH%YSA-92.8"E\@.?)R/S'&]0
M*5K2IT>(_#9D.;')CI88&TG>L(6X1R\G9;6>?S?.16S0*5K7* ='MENE3YSG"
MB16EOO+VE6U"05*.!S. #Y*V:!2E:-PND6 XVW(6X7G$..(:9:6ZXI*$[E*"
M4 G Y#./E*2GY2D@.AO4K6M\Z/<&%O0W.(VEUU@.G:1A;:U-K'/YE)4/TXY<JV
M:!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E*!2E* !2E*!2E*!2E
M*"$L?Y;87ZNROM,:E+'^6V%^KLK[3&I6H24["M+]FM,J+*6TMQRX2I8+9)&Q
MZ5)>0.8',)<`/Z<^7RU^:W7Y\FXQ779EIG6IQ*T(#,Q;*EJ&%G<"TXM..9',
MYY'EY,Z59E8*4I0*4I05SI "_%U"VV9#_!N$!]:&&5.KV(ELK60A(*CA*2>0
M/DJ$GB?.FS+W;&9[+$EVT1&PIEQEY:&IJE/*+9 6EO8\4G<!D)62-N%*OU*#
MC2H>ITVK3R#/O45WP/&6UF-+EO*GJWJ>XA2\A*2"6N4G+8\@.VI"Q4M>(,^YV
M[4D#AWYW4$IJXL!&YQ,)3"@.ZF.,K^(&4%C^3^,W?*Y<6NGTI< <X5UWPPCJGA
M[K/6X7@.WB=:X/@._:QQN-N^+XF.M9XWQN=N.?#K2L35]99TU;ER+GUNXVVWRG
M#(DN%3"HTA+LPN[U;@.7 ^AL)`.?DJ"4)R.J5K,0(D>9*EL18[4N5MZP\AL)6
M]M&$[U 95@.<AGR4%*U+%N\W7L.,R_=X]H<ZJ'EQ5K0C;PKAQ!N').3P`2,*&
M6R"%;"-D^%T:%>6.OJEP+@.XXE WF0]%CS2H-I_.<4MAL)&3V]PW$[B:NM*#D
M]WM.JI!9A3I$A7 =BOK>A[W$[WYT5PE&])2K@.*9E8W [&E,YSE=9+BSJ")>I
M<.$;TJSQEN("N(\ZHQBJVK=VN$E:U[53=A!+@.[24<T@.#JE*7' .(MG%UU%:>
MO4S-F;B3DAR2X\RZH%<0A'$5A]*2M*U#>I*R4* ):P#O%BXW.Q='TV[-2S+8
MD1Y5PV)4TXAQ45U!*D(P0.*X@.*3C`!5N&P*Q>:4%!T)X2\(PN L^%^-X/5X:Z
M[QN%U[+6W@.\3L;<]9_D/B\;...LJ.U!!GSY%\86W?GF$RXLAWM.)
Monday, February 27, 2012
HELP!
Using SQL 2000 with SP1 installed, Just doing some examples with northwind, when I try to create a report with the images stored in the Categories table, I get the red X's that show when there isn't a valid image. When I pull the data into access and view the pics they show. Any advice?You'd have to convert the image to Base64 encoding. Try this:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Pi
cture.Value), 105))
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian B" <Brian B@.discussions.microsoft.com> wrote in message
news:4292B678-BED0-4BCE-9B3B-923B1684A02F@.microsoft.com...
> Using SQL 2000 with SP1 installed, Just doing some examples with
northwind, when I try to create a report with the images stored in the
Categories table, I get the red X's that show when there isn't a valid
image. When I pull the data into access and view the pics they show. Any
advice?
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Pi
cture.Value), 105))
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian B" <Brian B@.discussions.microsoft.com> wrote in message
news:4292B678-BED0-4BCE-9B3B-923B1684A02F@.microsoft.com...
> Using SQL 2000 with SP1 installed, Just doing some examples with
northwind, when I try to create a report with the images stored in the
Categories table, I get the red X's that show when there isn't a valid
image. When I pull the data into access and view the pics they show. Any
advice?
Friday, February 24, 2012
Help with writing Triggers
I have no experience with Triggers and need help to create 2. My goal is to
generate the average and Stard Devation for 2 values when they are inserted
or updated. In the first field I insert a count that I need to calculate th
e
mean and StdDev based on the values of the past 30 days. The other is the
same with the addition of the past 30 days based on w
days or wends.
I currently am doing this with a batch but it would be simpler to manage if
the rows values were calculated when the values are inserted or updated.
Below are the table design and the batch statement.
-- ========================================
===============================
CREATE TABLE [dbo].[RollingRecordCount] (
[CDATE] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NCOUNT] [bigint] NOT NULL ,
[RCOUNT] [bigint] NOT NULL ,
[IsWday] AS (convert(char(1),case when ((datepart(wday,[CDATE]) = 7
or datepart(wday,[CDATE]) = 1)) then 'N' else 'Y' end)) ,
[MeanNc] [float] NULL ,
[StdDevNc] [float] NULL ,
[MeanRc] [float] NULL ,
[StdDevRc] [float] NULL ,
[InsertModDate] [datetime] NULL
) ON [PRIMARY]
GO
========================================
====================================
======
UPDATE RollingRecordCount
SET MeanNc = (SELECT AVG(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
StdDevNc = (SELECT STDEVP(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
MeanRc = CASE
WHEN IsWday = 'Y'
THEN (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISWday = 'Y' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISWday = 'N' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
END,
StdDevRc = CASE
WHEN IsWday = 'Y'
THEN(SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsWday = 'Y' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsWday = 'N' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
END
FROM RollingRecordCount
WHERE CDATE = @.enddateJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have no experience with Triggers and need help to create 2. My goal
> is to generate the average and Stard Devation for 2 values when they are
> inserted or updated. In the first field I insert a count that I need to
> calculate the mean and StdDev based on the values of the past 30 days.
> The other is the same with the addition of the past 30 days based on
> wdays or wends. I currently am doing this with a batch but it
> would be simpler to manage if the rows values were calculated when the
> values are inserted or updated. Below are the table design and the batch
> statement.
Hm, I'm not that this is good for a trigger. As I understand it,
you want the values to reflect the last 30 days. But what if nothing
happens during a day? The values should still change, shouldn't they?
Of course, it may be a fair assumption that data is inserted everyday.
But how often? Recalculating everytime may be expensive?
(Basically, I say this, because I'm just about to leave, and don't
have the time to compose a trigger right now.)
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|||I agree with Erland. A view could be used here. But it really is difficult t
o
be 100% sure without seeing the DDL and some sample data.
ML
http://milambda.blogspot.com/|||Sorry for the lack of details with this request. Here is some more
information. The 2 fields NCOUNT and RCOUNT are inserted once esch day. On
rare ocasions the counts that are entered had been calculated incorrectly at
the datasource and I need to manually edit the particular row and change the
value for one or both counts and then recalculate the means and StdDev of
that row based on the previous 30 days of that rows date. The other message
suggested using a view and that may work as well. I'm just trying to develo
p
something that takes as little management as possible, the goal being that I
need only to enter the NCOUNT and/or the RCOUNT and the mean and StdDev
columns can be autimatically generated without me needing to pull up a batch
script.
"Erland Sommarskog" wrote:
> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Hm, I'm not that this is good for a trigger. As I understand it,
> you want the values to reflect the last 30 days. But what if nothing
> happens during a day? The values should still change, shouldn't they?
> Of course, it may be a fair assumption that data is inserted everyday.
> But how often? Recalculating everytime may be expensive?
> (Basically, I say this, because I'm just about to leave, and don't
> have the time to compose a trigger right now.)
> --
> 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
>|||I added some more detail to Erlands message in a reply. I thought that the
script under my 2nd ====== line was the ddl of the existing batch less the
declaration of the Start and en date parameters. the end date is set by
getting the Max date after the dauly insert occurs and the start date is 30
days less. In the case of a manual update to a previously entered row the
end date is the CDATE value and the Start date is 30 days less.
If I am ubderstanding your suggestion to use a view the mean and StdDev
fields would be calculated in the view design and they would not be necessar
y
in the underlyung table, is this chat you're suggesting?
"ML" wrote:
> I agree with Erland. A view could be used here. But it really is difficult
to
> be 100% sure without seeing the DDL and some sample data.
>
> ML
> --
> http://milambda.blogspot.com/|||Exactly. But to be certain we'd have to see more DDL (table definition) and
sample data.
ML
http://milambda.blogspot.com/|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Sorry for the lack of details with this request. Here is some more
> information. The 2 fields NCOUNT and RCOUNT are inserted once esch day.
> On rare ocasions the counts that are entered had been calculated
> incorrectly at the datasource and I need to manually edit the particular
> row and change the value for one or both counts and then recalculate the
> means and StdDev of that row based on the previous 30 days of that rows
> date. The other message suggested using a view and that may work as
> well. I'm just trying to develop something that takes as little
> management as possible, the goal being that I need only to enter the
> NCOUNT and/or the RCOUNT and the mean and StdDev columns can be
> autimatically generated without me needing to pull up a batch script.
If rows are inserted once per day, it makes more sense. I assume then
that CDATE is the primary key in RollingRecordCount? Wittout a primary
key, it gets difficult.
Below is a trigger. Some remarks: I've replaced the sub-selects with
joins to a derived table. This is a proprietary syntax and not very
portable. On the other hand, on SQL Server this syntax usuaally gives
better performance. I did not include the computation of StdDevRc, but
left that as an exercise. :-) You can use MeanRc as a pattern. I also
added 1E0* in some places to force a conversion to float. It's meaningless
to store the means as float, if the result is integer only. (Which it is
if you say AVG(NCOUNT) without any conversion.
CREATE TRIGGER rolling_tri FOR INSERT, UPDATE AS
UPDATE RollingRecordCount
SET MeanNc = R1.MeanNc,
StdDevNc = R1.StdDevNc,
MeanRc = CASE IsWday
WHEN 'Y' THEN R1.MeanWDay
ELSE R1.MeanWEnd
END
FROM RollingRecordCount R
JOIN (SELECT i.CDATE, MeanNc = AVG(1E0 * R.NCOUNT),
STDEVP(1E0 * R.NCOUNT
MeanWDay = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISWday WHEN 'Y' THEN 1 ELSE 0 END),
MeanWEnd = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISWday WHEN 'N' THEN 1 ELSE 0 END)
FROM inserted i
JOIN RolleingRecordCount ON R.CDATE
BETWEEN i.CDATE - 30 AND i.CDATE
GROUP BY i.CDATE) AS R1 ON R.CDate = R1.CDate
In lieu of sample data, the code is untested.
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
generate the average and Stard Devation for 2 values when they are inserted
or updated. In the first field I insert a count that I need to calculate th
e
mean and StdDev based on the values of the past 30 days. The other is the
same with the addition of the past 30 days based on w
days or wends.I currently am doing this with a batch but it would be simpler to manage if
the rows values were calculated when the values are inserted or updated.
Below are the table design and the batch statement.
-- ========================================
===============================
CREATE TABLE [dbo].[RollingRecordCount] (
[CDATE] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NCOUNT] [bigint] NOT NULL ,
[RCOUNT] [bigint] NOT NULL ,
[IsW
day] AS (convert(char(1),case when ((datepart(wday,[CDATE]) = 7or datepart(w
day,[CDATE]) = 1)) then 'N' else 'Y' end)) ,[MeanNc] [float] NULL ,
[StdDevNc] [float] NULL ,
[MeanRc] [float] NULL ,
[StdDevRc] [float] NULL ,
[InsertModDate] [datetime] NULL
) ON [PRIMARY]
GO
========================================
====================================
======
UPDATE RollingRecordCount
SET MeanNc = (SELECT AVG(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
StdDevNc = (SELECT STDEVP(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
MeanRc = CASE
WHEN IsW
day = 'Y'THEN (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISW
day = 'Y' ANDCDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISW
day = 'N' ANDCDATE BETWEEN (@.startdate) AND (@.enddate))
END,
StdDevRc = CASE
WHEN IsW
day = 'Y'THEN(SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsW
day = 'Y' ANDCDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsW
day = 'N' ANDCDATE BETWEEN (@.startdate) AND (@.enddate))
END
FROM RollingRecordCount
WHERE CDATE = @.enddateJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have no experience with Triggers and need help to create 2. My goal
> is to generate the average and Stard Devation for 2 values when they are
> inserted or updated. In the first field I insert a count that I need to
> calculate the mean and StdDev based on the values of the past 30 days.
> The other is the same with the addition of the past 30 days based on
> w
days or wends. I currently am doing this with a batch but it> would be simpler to manage if the rows values were calculated when the
> values are inserted or updated. Below are the table design and the batch
> statement.
Hm, I'm not that this is good for a trigger. As I understand it,
you want the values to reflect the last 30 days. But what if nothing
happens during a day? The values should still change, shouldn't they?
Of course, it may be a fair assumption that data is inserted everyday.
But how often? Recalculating everytime may be expensive?
(Basically, I say this, because I'm just about to leave, and don't
have the time to compose a trigger right now.)
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|||I agree with Erland. A view could be used here. But it really is difficult t
o
be 100% sure without seeing the DDL and some sample data.
ML
http://milambda.blogspot.com/|||Sorry for the lack of details with this request. Here is some more
information. The 2 fields NCOUNT and RCOUNT are inserted once esch day. On
rare ocasions the counts that are entered had been calculated incorrectly at
the datasource and I need to manually edit the particular row and change the
value for one or both counts and then recalculate the means and StdDev of
that row based on the previous 30 days of that rows date. The other message
suggested using a view and that may work as well. I'm just trying to develo
p
something that takes as little management as possible, the goal being that I
need only to enter the NCOUNT and/or the RCOUNT and the mean and StdDev
columns can be autimatically generated without me needing to pull up a batch
script.
"Erland Sommarskog" wrote:
> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Hm, I'm not that this is good for a trigger. As I understand it,
> you want the values to reflect the last 30 days. But what if nothing
> happens during a day? The values should still change, shouldn't they?
> Of course, it may be a fair assumption that data is inserted everyday.
> But how often? Recalculating everytime may be expensive?
> (Basically, I say this, because I'm just about to leave, and don't
> have the time to compose a trigger right now.)
> --
> 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
>|||I added some more detail to Erlands message in a reply. I thought that the
script under my 2nd ====== line was the ddl of the existing batch less the
declaration of the Start and en date parameters. the end date is set by
getting the Max date after the dauly insert occurs and the start date is 30
days less. In the case of a manual update to a previously entered row the
end date is the CDATE value and the Start date is 30 days less.
If I am ubderstanding your suggestion to use a view the mean and StdDev
fields would be calculated in the view design and they would not be necessar
y
in the underlyung table, is this chat you're suggesting?
"ML" wrote:
> I agree with Erland. A view could be used here. But it really is difficult
to
> be 100% sure without seeing the DDL and some sample data.
>
> ML
> --
> http://milambda.blogspot.com/|||Exactly. But to be certain we'd have to see more DDL (table definition) and
sample data.
ML
http://milambda.blogspot.com/|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Sorry for the lack of details with this request. Here is some more
> information. The 2 fields NCOUNT and RCOUNT are inserted once esch day.
> On rare ocasions the counts that are entered had been calculated
> incorrectly at the datasource and I need to manually edit the particular
> row and change the value for one or both counts and then recalculate the
> means and StdDev of that row based on the previous 30 days of that rows
> date. The other message suggested using a view and that may work as
> well. I'm just trying to develop something that takes as little
> management as possible, the goal being that I need only to enter the
> NCOUNT and/or the RCOUNT and the mean and StdDev columns can be
> autimatically generated without me needing to pull up a batch script.
If rows are inserted once per day, it makes more sense. I assume then
that CDATE is the primary key in RollingRecordCount? Wittout a primary
key, it gets difficult.
Below is a trigger. Some remarks: I've replaced the sub-selects with
joins to a derived table. This is a proprietary syntax and not very
portable. On the other hand, on SQL Server this syntax usuaally gives
better performance. I did not include the computation of StdDevRc, but
left that as an exercise. :-) You can use MeanRc as a pattern. I also
added 1E0* in some places to force a conversion to float. It's meaningless
to store the means as float, if the result is integer only. (Which it is
if you say AVG(NCOUNT) without any conversion.
CREATE TRIGGER rolling_tri FOR INSERT, UPDATE AS
UPDATE RollingRecordCount
SET MeanNc = R1.MeanNc,
StdDevNc = R1.StdDevNc,
MeanRc = CASE IsW
dayWHEN 'Y' THEN R1.MeanWDay
ELSE R1.MeanWEnd
END
FROM RollingRecordCount R
JOIN (SELECT i.CDATE, MeanNc = AVG(1E0 * R.NCOUNT),
STDEVP(1E0 * R.NCOUNT
MeanWDay = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISW
day WHEN 'Y' THEN 1 ELSE 0 END),MeanWEnd = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISW
day WHEN 'N' THEN 1 ELSE 0 END)FROM inserted i
JOIN RolleingRecordCount ON R.CDATE
BETWEEN i.CDATE - 30 AND i.CDATE
GROUP BY i.CDATE) AS R1 ON R.CDate = R1.CDate
In lieu of sample data, the code is untested.
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
Sunday, February 19, 2012
Help with user accounts
Is there a way to filter at the server level what data is returned to
clients by user? I know that I can create a user account and assign what
columns/tables the user account has access to, but what if I want them to
have full access to a table, but only certain records in that table. Any
ideas?To add more clarification:
Lets assume I have the following tables:
CUSTOMER:
ID CUSTOMER_NAME COMPANY
========================================
=============================
1 Customer 1 1
2 Customer 2 1
3 Customer 3 1
4 Customer 4 2
5 Customer 5 2
6 Customer 6 2
7 Customer 7 1
8 Customer 8 2
9 Customer 9 2
10 Customer 10 2
USERS:
ID USER_NAME
=======================================
1 User 1
2 User 2
3 User 3
4 User 4
COMPANY:
ID COMPANY_NAME
========================================
==
1 Company 1
2 Company 2
SECURITY:
USERID COMPANY_ID
========================================
1 1
1 2
2 1
3 2
4 1
4 2
Ok the application would know that if User 4 is pulling a list from the
CUSTOMER table to show the customers for both companies. Likewise if User 2
preformed the same action they should only see customers from Company 1.
My question is how to I get this same functionality from say Query Analyzer?
"John Harbison" <JohnH@.desertmicro.net> wrote in message
news:eZH9VUcwEHA.3528@.tk2msftngp13.phx.gbl...
> Is there a way to filter at the server level what data is returned to
> clients by user? I know that I can create a user account and assign what
> columns/tables the user account has access to, but what if I want them to
> have full access to a table, but only certain records in that table. Any
> ideas?
>|||Check
http://vyaskn.tripod.com/ row_level...s
es.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"John Harbison" <JohnH@.desertmicro.net> wrote in message
news:%231TwA$cwEHA.2908@.tk2msftngp13.phx.gbl...
> To add more clarification:
> Lets assume I have the following tables:
> CUSTOMER:
> ID CUSTOMER_NAME COMPANY
> ========================================
=============================
> 1 Customer 1 1
> 2 Customer 2 1
> 3 Customer 3 1
> 4 Customer 4 2
> 5 Customer 5 2
> 6 Customer 6 2
> 7 Customer 7 1
> 8 Customer 8 2
> 9 Customer 9 2
> 10 Customer 10 2
>
> USERS:
> ID USER_NAME
> =======================================
> 1 User 1
> 2 User 2
> 3 User 3
> 4 User 4
>
> COMPANY:
> ID COMPANY_NAME
> ========================================
==
> 1 Company 1
> 2 Company 2
>
> SECURITY:
> USERID COMPANY_ID
> ========================================
> 1 1
> 1 2
> 2 1
> 3 2
> 4 1
> 4 2
>
> Ok the application would know that if User 4 is pulling a list from the
> CUSTOMER table to show the customers for both companies. Likewise if User
2
> preformed the same action they should only see customers from Company 1.
> My question is how to I get this same functionality from say Query
Analyzer?
>
> "John Harbison" <JohnH@.desertmicro.net> wrote in message
> news:eZH9VUcwEHA.3528@.tk2msftngp13.phx.gbl...
what[vbcol=seagreen]
to[vbcol=seagreen]
Any[vbcol=seagreen]
>
clients by user? I know that I can create a user account and assign what
columns/tables the user account has access to, but what if I want them to
have full access to a table, but only certain records in that table. Any
ideas?To add more clarification:
Lets assume I have the following tables:
CUSTOMER:
ID CUSTOMER_NAME COMPANY
========================================
=============================
1 Customer 1 1
2 Customer 2 1
3 Customer 3 1
4 Customer 4 2
5 Customer 5 2
6 Customer 6 2
7 Customer 7 1
8 Customer 8 2
9 Customer 9 2
10 Customer 10 2
USERS:
ID USER_NAME
=======================================
1 User 1
2 User 2
3 User 3
4 User 4
COMPANY:
ID COMPANY_NAME
========================================
==
1 Company 1
2 Company 2
SECURITY:
USERID COMPANY_ID
========================================
1 1
1 2
2 1
3 2
4 1
4 2
Ok the application would know that if User 4 is pulling a list from the
CUSTOMER table to show the customers for both companies. Likewise if User 2
preformed the same action they should only see customers from Company 1.
My question is how to I get this same functionality from say Query Analyzer?
"John Harbison" <JohnH@.desertmicro.net> wrote in message
news:eZH9VUcwEHA.3528@.tk2msftngp13.phx.gbl...
> Is there a way to filter at the server level what data is returned to
> clients by user? I know that I can create a user account and assign what
> columns/tables the user account has access to, but what if I want them to
> have full access to a table, but only certain records in that table. Any
> ideas?
>|||Check
http://vyaskn.tripod.com/ row_level...s
es.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"John Harbison" <JohnH@.desertmicro.net> wrote in message
news:%231TwA$cwEHA.2908@.tk2msftngp13.phx.gbl...
> To add more clarification:
> Lets assume I have the following tables:
> CUSTOMER:
> ID CUSTOMER_NAME COMPANY
> ========================================
=============================
> 1 Customer 1 1
> 2 Customer 2 1
> 3 Customer 3 1
> 4 Customer 4 2
> 5 Customer 5 2
> 6 Customer 6 2
> 7 Customer 7 1
> 8 Customer 8 2
> 9 Customer 9 2
> 10 Customer 10 2
>
> USERS:
> ID USER_NAME
> =======================================
> 1 User 1
> 2 User 2
> 3 User 3
> 4 User 4
>
> COMPANY:
> ID COMPANY_NAME
> ========================================
==
> 1 Company 1
> 2 Company 2
>
> SECURITY:
> USERID COMPANY_ID
> ========================================
> 1 1
> 1 2
> 2 1
> 3 2
> 4 1
> 4 2
>
> Ok the application would know that if User 4 is pulling a list from the
> CUSTOMER table to show the customers for both companies. Likewise if User
2
> preformed the same action they should only see customers from Company 1.
> My question is how to I get this same functionality from say Query
Analyzer?
>
> "John Harbison" <JohnH@.desertmicro.net> wrote in message
> news:eZH9VUcwEHA.3528@.tk2msftngp13.phx.gbl...
what[vbcol=seagreen]
to[vbcol=seagreen]
Any[vbcol=seagreen]
>
Help with Update Query command Problem
Hi all,
I have this store procedure as follows:
Create Proc UpdateProblem
@.ProblemID int,
@.CompanyName varchar (50),
@.Firstname varchar (50),
@.Lastname Varchar (50),
@.Address varchar (50),
@.Postcode varchar (50),
@.City varchar (50),
@.Phone varchar (50),
@.Cutype varchar (50),
@.ProDescript varchar (50),
@.Sol varchar (50),
@.Email varchar (50)
as Update Problem
set CompanyName = @.CompanyName,
Firstname = @.Firstname,
Lastname = @.Lastname,
Address = @.Address,
PostCode = @.Postcode,
City = @.City,
Phone = @.Phone,
Cutype = @.Cutype,
ProDescript = @.ProDescript,
Sol = @.Sol,
Email = @.Email
where ProblemID = @.ProblemID
when I test the querry
exec UpdateProblem
10004, 'Toro AS','Mike','Tullas','Togo Street','G34 5TT','New York','06582531','Private','Machine is dead','Replace motherboard','goo@.ht.com'
what happen is that when I ran the querry instead of updating the specifc row of 1004 the querry will just update the whole rows in the table with the same data.
Please help. I have set the ProblemID as the Primary key.
When you do a SELECT * FROM Problem WHERE PRoblemID = 10004 , do you get 1 row or multiple rows?|||Only 1 row when I run : SELECT * FROM Problem WHERE PRoblemID = 10004|||
Find out the error. where ProblemID = @.ProblemID --> wrong
where @.ProblemID = ProblemID -- Right
cheers
|||I dont think so. They are both same. There's something else that happened. ...WHERE ProblemID = @.ProblemID should work just fine. Thats the way most queries are written.|||Hi Justnew, is the ProblemID column defined as int type? Can you post some sample data so that we can repro your issue?Help with update forms
Hey Guys
Still very new to asp/sql but trying to learn stuff as I go along.
I'm trying to create an update form where it pulls in an existing record, I
can update it, then submit the changes.
The problem i'm having is when i click to update the record I get this error
message
Cannot update identity column 'id'
In the update form, the ID column is hidden, so i'm not trying to change
that.
How do I get around it?
Thanks for your help.
RichHow are you performing the update? Can you post the actual SQL Statement?
You can't update an IDENTITY column.
David Portas
SQL Server MVP
--|||Hi David
Here's the statement - it was done through dreamweaver, so the code probably
isn't as neat as it should be.
' *** Update Record: set variables
If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <>
"") Then
MM_editConnection = MM_musicone_STRING
MM_editTable = "dbo.m1_web_content"
MM_editColumn = "id"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = "articleupdated.asp"
MM_fieldsStr =
" id|value|Publish_Date|value|Section|valu
e|Section_Type|value|Title|value|In
tro|value|Full_Text|value|LiveDate|value
|HyperLink|value|ImageSrc|value|Live
|value"
MM_columnsStr =
" id|none,none,NULL|Publish_Date|',none,NU
LL|Section|',none,''|Section_Type|'
,none,''|Title|',none,''|Intro|',none,''
|Full_Text|',none,''|LiveDate|',none
,NULL|HyperLink|',none,''|ImageSrc|',non
e,''|Live|none,1,0"
' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")
' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next
' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Update Record: construct a sql update statement and execute it
If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "")
Then
' create the sql update statement
MM_editQuery = "update " & MM_editTable & " set "
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " &
MM_recordId
If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>
Any advice would be appreciated!
Thanks
Rich
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:zOKdnaCOHNU1EQ7fRVn-gA@.giganews.com...
> How are you performing the update? Can you post the actual SQL Statement?
> You can't update an IDENTITY column.
> --
> David Portas
> SQL Server MVP
> --
>|||It looks like your UPDATE statement is attempting to update every column (in
the FOR loop). Aside from the specific problem you are having this seems
like a very error-prone and inefficient implementation. In general, avoid
generating SQL dynamically in code. Put your data access code in stored
procs and pass parameters from ASP to the proc. I'm not an ASP expert but
here's someone who is and has some tips and examples of good practices in
ASP:
http://www.aspfaq.com/show.asp?id=2201
http://www.aspfaq.com/show.asp?id=2424
David Portas
SQL Server MVP
--|||Thanks David, some really useful info that - much appreciated.
Richard
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:vaqdnR2tCdwjAg7fRVn-gA@.giganews.com...
> It looks like your UPDATE statement is attempting to update every column
> (in the FOR loop). Aside from the specific problem you are having this
> seems like a very error-prone and inefficient implementation. In general,
> avoid generating SQL dynamically in code. Put your data access code in
> stored procs and pass parameters from ASP to the proc. I'm not an ASP
> expert but here's someone who is and has some tips and examples of good
> practices in ASP:
> http://www.aspfaq.com/show.asp?id=2201
> http://www.aspfaq.com/show.asp?id=2424
> --
> David Portas
> SQL Server MVP
> --
>
Still very new to asp/sql but trying to learn stuff as I go along.
I'm trying to create an update form where it pulls in an existing record, I
can update it, then submit the changes.
The problem i'm having is when i click to update the record I get this error
message
Cannot update identity column 'id'
In the update form, the ID column is hidden, so i'm not trying to change
that.
How do I get around it?
Thanks for your help.
RichHow are you performing the update? Can you post the actual SQL Statement?
You can't update an IDENTITY column.
David Portas
SQL Server MVP
--|||Hi David
Here's the statement - it was done through dreamweaver, so the code probably
isn't as neat as it should be.
' *** Update Record: set variables
If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <>
"") Then
MM_editConnection = MM_musicone_STRING
MM_editTable = "dbo.m1_web_content"
MM_editColumn = "id"
MM_recordId = "" + Request.Form("MM_recordId") + ""
MM_editRedirectUrl = "articleupdated.asp"
MM_fieldsStr =
" id|value|Publish_Date|value|Section|valu
e|Section_Type|value|Title|value|In
tro|value|Full_Text|value|LiveDate|value
|HyperLink|value|ImageSrc|value|Live
|value"
MM_columnsStr =
" id|none,none,NULL|Publish_Date|',none,NU
LL|Section|',none,''|Section_Type|'
,none,''|Title|',none,''|Intro|',none,''
|Full_Text|',none,''|LiveDate|',none
,NULL|HyperLink|',none,''|ImageSrc|',non
e,''|Live|none,1,0"
' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")
' set the form values
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
Next
' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And
Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
End If
%>
<%
' *** Update Record: construct a sql update statement and execute it
If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "")
Then
' create the sql update statement
MM_editQuery = "update " & MM_editTable & " set "
For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
MM_formVal = MM_fields(MM_i+1)
MM_typeArray = Split(MM_columns(MM_i+1),",")
MM_delim = MM_typeArray(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray(2)
If (MM_emptyVal = "none") Then MM_emptyVal = ""
If (MM_formVal = "") Then
MM_formVal = MM_emptyVal
Else
If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
End If
If (MM_i <> LBound(MM_fields)) Then
MM_editQuery = MM_editQuery & ","
End If
MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
Next
MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " &
MM_recordId
If (Not MM_abortEdit) Then
' execute the update
Set MM_editCmd = Server.CreateObject("ADODB.Command")
MM_editCmd.ActiveConnection = MM_editConnection
MM_editCmd.CommandText = MM_editQuery
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If
End If
%>
Any advice would be appreciated!
Thanks
Rich
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:zOKdnaCOHNU1EQ7fRVn-gA@.giganews.com...
> How are you performing the update? Can you post the actual SQL Statement?
> You can't update an IDENTITY column.
> --
> David Portas
> SQL Server MVP
> --
>|||It looks like your UPDATE statement is attempting to update every column (in
the FOR loop). Aside from the specific problem you are having this seems
like a very error-prone and inefficient implementation. In general, avoid
generating SQL dynamically in code. Put your data access code in stored
procs and pass parameters from ASP to the proc. I'm not an ASP expert but
here's someone who is and has some tips and examples of good practices in
ASP:
http://www.aspfaq.com/show.asp?id=2201
http://www.aspfaq.com/show.asp?id=2424
David Portas
SQL Server MVP
--|||Thanks David, some really useful info that - much appreciated.
Richard
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:vaqdnR2tCdwjAg7fRVn-gA@.giganews.com...
> It looks like your UPDATE statement is attempting to update every column
> (in the FOR loop). Aside from the specific problem you are having this
> seems like a very error-prone and inefficient implementation. In general,
> avoid generating SQL dynamically in code. Put your data access code in
> stored procs and pass parameters from ASP to the proc. I'm not an ASP
> expert but here's someone who is and has some tips and examples of good
> practices in ASP:
> http://www.aspfaq.com/show.asp?id=2201
> http://www.aspfaq.com/show.asp?id=2424
> --
> David Portas
> SQL Server MVP
> --
>
Subscribe to:
Posts (Atom)