Database

Re: Jobs succeeds when executed manually but fails when scheduled

10-Sep-08 23:31

no...i don't hv any thing like user permission..
Actually i am new to SQL and Database..
So any thing else i am missing out...????

Ashfield10-Sep-08 23:56

Ashfield

10-Sep-08 23:56

When you run it its running with your security settings and permissions, when it runs as a scheduled job it isn't. So, does the account the scheduler is running under have the necessary pemissions in the database to perform what ever it is your sql does?

Bob
Ashfield Consultants Ltd

Re: Jobs succeeds when executed manually but fails when scheduled

annu0811-Sep-08 0:15

Re: Jobs succeeds when executed manually but fails when scheduled

11-Sep-08 0:15

No we dont hv any security settings and permission.when run manually or scheduled.

annu0811-Sep-08 1:42

Re: Jobs succeeds when executed manually but fails when scheduled

11-Sep-08 1:42

Can u let me know the possible reasons so that i can have a cross check on it.

Ashfield11-Sep-08 3:06

Ashfield

11-Sep-08 3:06

annu08 wrote:
No we dont hv any security settings and permission.

Sorry, but you do, even if they are only the default ones - and this is not recommended.

Have a look to see what account your scheduled job runs under, then have a look in the security details under SQL Server Management to see what rights they have, at a server, database and table level in relation to yourself.

Other tahn that I have no suggestions as to why this is happening.

Bob
Ashfield Consultants Ltd

Re: Jobs succeeds when executed manually but fails when scheduled

annu0811-Sep-08 3:19

Re: Help required with Joins

11-Sep-08 3:19

k, will check the details and get back with the exact problem.
Anyway thank u.
Regards,
Annu

Help required with Joins

Rajiya10-Sep-08 20:14

Rajiya

10-Sep-08 20:14

Hi All,

I am writing a query for a daily report generation. It involves data from 4 tables. Am so confused about which joins to use. It also makes use of GROUP BY CLAUSE on Cust_NBR and make use of agrregate function Count.
I have written a query as follows but it's not working.
Plz help me...as it is urgent.

select SH.ARBL_NBR Airbill,S.cust_NBR Account_Number,S.Shpt_Id,S.City_Nam sender_City,
S.St_Prvnc_Cd Sender_State,s.cntry_id Sender_Country,S.Postl_Cd sender_postal,
S.Svc_Lvl_Cd Sender_Service_Area_Code,
R.City_Nam Receiver_City,R.St_Prvnc_Cd Receiver_State,R.cntry_id Reciever_Country,
R.Postl_Cd Receiver_Postal, R.Svc_Lvl_Cd Receiver_Service_Area_Code,
U.Frst_Nam||' ' ||U.last_nam Name,
S.STR_ADDR_1 Sender_Street_Address1, S.Str_Addr_2 Sender_Street_Address2
from shpt SH
Right Outer Join shpt_addr S
on sh.shpt_id = S.shpt_id
Right Outer Join shpt_addr R
ON S.shpt_id = R.shpt_id
Right Outer Join Users U
ON SH.User_Id = U.User_Id
Right Outer Join Cust C
On S.Cust_Nbr = C.Cust_Nbr
where S.ADDR_TYP_CD = 1 and R.Addr_Typ_Cd = 2
and c.creat_dt between trunc(sysdate) and trunc(sysdate-90)
group By S.Cust_Nbr Desc
Having Count(sh.shpt_id >= 25)

EBeylo10-Sep-08 20:43

EBeylo

10-Sep-08 20:43

Hi, what kind of Error do u get??

Re: Help required with Joins

Rajiya10-Sep-08 21:20

Rajiya

10-Sep-08 21:20

Error i get is ORA-00933: SQL command not properly ended.

There is some mistake in the query which i am not getting.

Plz help me out.

Re: Help required with Joins

EBeylo10-Sep-08 22:56

EBeylo

10-Sep-08 22:56

Hi, try this please maybe the syntax problem is in the Having count-->

select R.City_Nam Receiver_City,R.St_Prvnc_Cd Receiver_State,R.cntry_id Reciever_Country,
R.Postl_Cd Receiver_Postal, R.Svc_Lvl_Cd Receiver_Service_Area_Code,
U.Frst_Nam||' ' ||U.last_nam Name,
S.STR_ADDR_1 Sender_Street_Address1, S.Str_Addr_2 Sender_Street_Address2
from shpt SH
Right Outer Join shpt_addr S
on sh.shpt_id = S.shpt_id
Right Outer Join shpt_addr R
ON S.shpt_id = R.shpt_id
Right Outer Join Users U
ON SH.User_Id = U.User_Id
Right Outer Join Cust C
On S.Cust_Nbr = C.Cust_Nbr
where S.ADDR_TYP_CD = 1 and R.Addr_Typ_Cd = 2 
and c.creat_dt between trunc(sysdate) and trunc(sysdate-90)
group By S.Cust_Nbr Desc
Having Count(sh.shpt_id) >= 25

Access to SQL

TheEagle10-Sep-08 20:07

TheEagle

10-Sep-08 20:07

Hi,
I didn't work on Access for a very long time.A customer gave me an Access application.He wants me to make a User/Administrator web application depending on that Access file.How could I see the code reading from the tables?It works as an EXE application.

Another problem is Iam trying to migrate it to SQL database.Some tables have more than 20000 records so their migration take a very long time(3 hours at least).Is there any wayy to finish this faster?

I am too late but i will never give up

Re: Access to SQL

Paul Conrad11-Sep-08 6:33

Paul Conrad

11-Sep-08 6:33

TheEagle wrote:
Some tables have more than 20000 records so their migration take a very long time(3 hours at least).Is there any wayy to finish this faster?

Is it possible the database is not normalized?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham

return Left or right value

nelsonpaixao10-Sep-08 13:09

10-Sep-08 13:09

hi,

i have a table, i query it and i get a row(or more).OK

how can i return a value in the left or right of a particular column(cell)?

for exemple, i have table that has some columns with same kind of data ( race standings: 1st,2nd,3rd,4th...), so i query for Hamilton in any of the columns(standings).

if hamilton is in column 3rd, how can i get the value in the column in the left or right? is it possible?

i am trying to avoid typing larger code here!!! Blush | :O

(this is an exemple it has nothing to do with F1 ok Dead | X|

)

nelsonpaixao@yahoo.com.br

trying to help & get help

Mycroft Holmes10-Sep-08 15:29

Mycroft Holmes

10-Sep-08 15:29

When you query for "Hamilton" you should get the columns ordinal position/index. The simply get the previous/next column

Set the column index to i and:
Dataset.Tables(0).Rows(2)(i + 1)

[edit]
Oops - wrong forum.
Assuming you know the name/index of the column with "hamilton"

You can apply the same logic to a sql construct. Look into the schema information views (system views), this will give you the ordinal position of the column you found and simply increment the ordinal position to get the column name. This will require a lot of dynamic SQL
[/edit]

Never underestimate the power of human stupidity
RAH

nelsonpaixao11-Sep-08 14:10

11-Sep-08 14:10

No oops, you are in the right forum. Blush | :O

No C#!!!
I usually do "everything" in sql and trigger in c# events, i d´ont filter/sort in datagridview (just small things), i just throw info msg´s in c# like OK:row added,deleted,edited ERROR:whatever...

I thought on that idea during a work, didn ´t need it but...maybe i will need on later works.

Thanks Cool | :cool:

nelsonpaixao@yahoo.com.br

trying to help & get help

PIEBALDconsult10-Sep-08 16:49

PIEBALDconsult

10-Sep-08 16:49

Hamilton will always be in the 1st column, so simply query for the 2nd. Big Grin | :-D

OK, OK...

It sounds like you need a more flexible database design; rather than

Race, 1stDriver, 2ndDriver, 3rdDriver, ...

Have something more like:

Race, Place, Driver

nelsonpaixao11-Sep-08 14:00

11-Sep-08 14:00

the design/strucure is fine, all queries get what i need.
this was an exemple like i said before, sql is finished.

But during the work i thought on that idea, maybe i could/need it on later works. D'Oh! | :doh:

nelsonpaixao@yahoo.com.br

trying to help & get help

Enhance this query

DrukenProgrammer10-Sep-08 9:19

DrukenProgrammer

10-Sep-08 9:19

SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes FROM TableName start, TableName end WHERE start.ID = end.ID AND start.Status = 'Ready' AND end.Status = 'Completed'

Ready-Complete ok, but I want one more condition added to it, I need time between Date_Timeworked when status is Ready and Rejected?

Wendelius10-Sep-08 9:57

Wendelius

10-Sep-08 9:57

Your question was answered under the previous post http://www.codeproject.com/script/Forums/View.aspx?fid=1725&select=2718135&fr=39#xx2718135xx[^]

DrukenProgrammer12-Sep-08 23:52

DrukenProgrammer

12-Sep-08 23:52

Thank you Mika, it works

Blue_Boy10-Sep-08 10:02

Blue_Boy

10-Sep-08 10:02

SELECT DATEDIFF(minute, start.Date_TimeWorked, end.Date_TimeWorked) AS ElapsedMinutes FROM TableName start, TableName end WHERE start.ID = end.ID AND start.Status = 'Ready' AND end.Status = 'Completed' and (Date_Timeworked between '2008-09-10' and '2008-09-10' and start.Status = 'Ready' and start.Status = 'Rejected')

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

nelsonpaixao10-Sep-08 13:15