Writing a Program - sql server 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Writing a Program - sql server 2008

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-24 : 04:32:26

hi ,

could any one help me to know the best way to right a program, i have the following update queries , i am inserting specific word like below into an field ec_job_ind in my table....
below are the update queries for my table dw_t_ec_job ,
I need to create an SSIS Package to update this particular row EC_JOB_IND with the below specific words whenever new row is inserted ,

So am not sure how this has to be done , do i have to create an SSIS package for each of the update query and schedule it or create a program which can be run at a paricular time and these coloumn get updated which matches the criteria ,

Updating Column EC_JOB_IND,

• UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-MODEM' WHERE
PROJECT_NAME LIKE 'QCOM.QCT.MODEM%'OR PROJECT_NAME LIKE 'MODEM%'

• UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-SQM' WHERE
PROJECT_NAME LIKE 'QCOM.QCT.SQM%'

• UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-AUDIO' WHERE
PROJECT_NAME LIKE 'AUDIO%'

• UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-MULTIMEDIA' WHERE
PROJECT_NAME LIKE 'QCOM.QCT.MULTIMEDIA%'

• UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-WCONNECT' WHERE
PROJECT_NAME LIKE 'QCOM.QCT.WCONNECT%'

• UPDATE DW_T_EC_JOB SET EC_JOB_IND='BREW' WHERE
PROJECT_NAME LIKE 'BREW%'

• UPDATE DW_T_EC_JOB SET EC_JOB_IND='SBA' WHERE
PROJECT_NAME LIKE 'SANBUILDS%'

• UPDATE DW_T_EC_JOB SET EC_JOB_IND='TARGET' WHERE
PROJECT_NAME LIKE 'CRMBUILDS%'

Please Help.......

Kristen
Test

22859 Posts

Posted - 2010-01-24 : 04:50:21

The updates look fine to me.

You could put them all in a stored procedure, and then just EXECUTE that.

But I would ask: Why are you doing this as a scheduled task? Until the task runs there will be some rows that have no correct value for EC_JOB_IND, and soon after the job runs and data changes in DW_T_EC_JOB then EC_JOB_IND will be wrong again, on those rows. Thus anty reports will be unreliable again.

I would do this when the data is Inserted, or Updated. If you are importing the data from another source then make it part of that import. If the data is being freely changed (e.g. by users entering data on forms) then use a TRIGGER. (In a Trigger you can write code that is smarter than the UPDATE statements you have there, but what you have WILL work just fine - so long as your tables are not too big it should be fine without making it smarter

)

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-25 : 12:17:12

Thank you for reply...

Could you please let me know how i could create an stored procedure , for updating ec_job_ind column with all the queries below...as the data is being pulled from another source..

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 12:27:49

OK, Lets get stoopid...

Do you have SQL Server Managment Studio (SSMS) Installed?

I just usually create a *.sql File and run it there...then I hand it off to release manglement and they update the other environments

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-25 : 12:31:22

hey i meant the SYNTAX for stored procedure.....

-Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 12:39:59

k...but I'm sure we are not on te same page

CREATE PROC <procname>
AS

BEGIN

...code

END

RETURN

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 14:04:15

CREATE PROC

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-25 : 14:16:13

Can anybody help me in writing the Code* part?

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-25 : 14:23:25

I need to write code for 2 stored procedures
1. For the set of Update queries above for the table DW_T_EC_JOB
2.And the other for updating table dw_t_report_table, column RUNTIME_CODE,where column RUNTIME (which i have in my table dw_t_report_table) falls in column RUNTIME_CATEGORY

RUNTIME_CODE RUNTIME_CATEGORY
1 0 - 5 min
2 5 - 15 min
3 15 - 30 min
4 30 - 1 hr
5 1 - 2 hrs
6 2 - 5 hrs
7 5 - 10 hrs
8 10 hrs - 20 hrs
9 20 hrs - 40 hrs
10 40+ hrs

Please help me in writing the code for the stored procedure..

Thanks in Advance for any help...

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 14:52:55

place the update where I have ...code?

I don't get it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rds207
Posting Yak Master

198 Posts

Posted - 2010-01-25 : 14:59:59

i meant the stored procedure ...

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 15:13:02

[code]
CREATE PROC <procname>
AS

BEGIN

UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-MODEM' WHERE
PROJECT_NAME LIKE 'QCOM.QCT.MODEM%'OR PROJECT_NAME LIKE 'MODEM%'

UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-SQM' WHERE
PROJECT_NAME LIKE 'QCOM.QCT.SQM%'

UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-AUDIO' WHERE
PROJECT_NAME LIKE 'AUDIO%'

UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-MULTIMEDIA' WHERE
PROJECT_NAME LIKE 'QCOM.QCT.MULTIMEDIA%'

UPDATE DW_T_EC_JOB SET EC_JOB_IND='HCI-WCONNECT' WHERE
PROJECT_NAME LIKE 'QCOM.QCT.WCONNECT%'

UPDATE DW_T_EC_JOB SET EC_JOB_IND='BREW' WHERE
PROJECT_NAME LIKE 'BREW%'

UPDATE DW_T_EC_JOB SET EC_JOB_IND='SBA' WHERE
PROJECT_NAME LIKE 'SANBUILDS%'

UPDATE DW_T_EC_JOB SET EC_JOB_IND='TARGET' WHERE
PROJECT_NAME LIKE 'CRMBUILDS%'

END

RETURN

[/code]

This? you couldn't do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 02:28:16

[code]
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '<'.
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 02:30:14

quote:
Originally posted by Kristen

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '<'.

thats why i always give


CREATE PROC yourprocname
AS
...

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 03:19:34

I used to find your advice helpful, but its really gone downhill. Now I only get errors ... like this one:


Server: Msg 2714, Level 16, State 5, Procedure yourprocname, Line 3
There is already an object named 'yourprocname' in the database.

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-26 : 09:42:03

mine would be the tequila ....tql version

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 10:39:59

quote:
Originally posted by Kristen

I used to find your advice helpful, but its really gone downhill. Now I only get errors ... like this one:

Server: Msg 2714, Level 16, State 5, Procedure yourprocname, Line 3
There is already an object named 'yourprocname' in the database.

Time for a coffee may be

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:43:37

Yeah ... I was going to use a different schema for each [yourprocname], but I've got a better plan now the caffeine has kicked in

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-01-26 : 14:03:24

you could always just grab a GUID as the new proc name, then build the SP with dynamic SQL

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources