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.
| Author |
Topic |
|
avijit111@gmail.com
Yak Posting Veteran
55 Posts |
 Posted - 2009-08-20 : 09:49:32
|
| if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dept_leav_summ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[dept_leav_summ]GOCREATE TABLE [dbo].[dept_leav_summ] ( [company] [varchar] (50) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [h01_emp_num] [varchar] (6) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [h01_emp_name] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [leave_type] [varchar] (10) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [Dept_code] [varchar] (4) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [key_value] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [Dept_desc] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [Desi_code] [varchar] (6) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [Desg_desc] [varchar] (40) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [Job_code] [char] (2) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [job_desc] [varchar] (20) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [DOB] [datetime] NULL , [DOJ] [datetime] NULL , [payset] [char] (3) COLLATE SQL_1xCompat_CP850_CI_AS NULL , [prd_Frm_dt] [datetime] NULL , [prd_To_dt] [datetime] NULL , [TotDays] [float] NULL , [PreDays] [float] NULL , [Offdays] [float] NULL , [PL] [float] NULL , [CL] [float] NULL , [SL] [float] NULL , [ACL] [float] NULL , [Susp] [float] NULL , [LWP] [float] NULL , [Strk] [float] NULL , [Loff] [float] NULL , [Abs] [float] NULL , [Poff] [float] NULL , [Coff] [float] NULL , [OD] [float] NULL , [SPL] [float] NULL ) ON [PRIMARY]GOthe above ddl is there...........now what actually i would like to do is i m having one uds screen from erp main screen.their i m having dept_code,please write one sp for that which willhaving feild like means the uds having those feilds,@udsid char(10), @screenid varchar(10), @company_code glcompany, @location_code gllocn, @langid smallint,@emp_code varchar(6),@emp_name varchar(40),@leave_type varchar(5),@Dept_code varchar(4),@mode tinyint, @keyvalue varchar(255)) please write one sp,when dept_code will filled up and click ok button then trigger will fired and ...and i m having one mail procedure that also i m sending to u..-- Enable Database Mail for this instanceEXECUTE sp_configure 'show advanced', 1;RECONFIGURE;EXECUTE sp_configure 'Database Mail XPs',1;RECONFIGURE;GO -- Create a Database Mail accountEXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Primary Account', @description = 'Account used by all mail profiles.', @email_address = 'myaddress@mydomain.com', @replyto_address = 'myaddress@mydomain.com', @display_name = 'Database Mail', @mailserver_name = 'mail.mydomain.com'; -- Create a Database Mail profileEXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Default Public Profile', @description = 'Default public profile for all users'; -- Add the account to the profileEXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Default Public Profile', @account_name = ' Primary Account', @sequence_number = 1; -- Grant access to the profile to all msdb database usersEXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'Default Public Profile', @principal_name = 'public', @is_default = 1;GO --send a test emailEXECUTE msdb.dbo.sp_send_dbmail @subject = 'Test Database Mail Message', @recipients = 'testaddress@mydomain.com', @query = 'SELECT @@SERVERNAME';GO The above mail procedure will fired when dept_code will click by the user.and one thing in the above mail system how i will configure that in the sql server 2000 and pls send the mail system having four procedures also....in the procedures send and receiving mail will happen after user fill up the department_code and click ok button.the mail will autometically generate to the department_code numbers...i m sending u also department codes...'1050','2020','2021','2022','2030','2031','2032','2033','2034','3020','3050','3060', '3070','4021','4023','4024','4025','4026','4027','4030','4051','4053','4060','4081','4090','4091','4093','4101','4102', '4111','4112','4113','4122','4130','4140','6022'the dept_code user will enter and click ok the mail will generate to the deptt from the server....please someone help me..... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-08-20 : 10:37:22
|
| Please use a more descriptive title in your posts:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131423http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130674These look like duplicates of the same problem. If they are the same problem, do not create new topics to address them.Also include a description of the problem at the very beginning, instead of burying it inside code. It's extremely difficult to figure out what you're asking for. |
 |
|
|
avijit111@gmail.com
Yak Posting Veteran
55 Posts |
Posted - 2009-08-20 : 14:18:41
|
quote:
Originally posted by robvolk
Please use a more descriptive title in your posts:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131423http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130674These look like duplicates of the same problem. If they are the same problem, do not create new topics to address them.Also include a description of the problem at the very beginning, instead of burying it inside code. It's extremely difficult to figure out what you're asking for.
can you write stored procedure that mail coding what i sent to u....when i was run that in t-sql that asking stored procedure required... |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-20 : 14:29:58
|
quote:
...that mail coding what i sent to u....when i was run that in t-sql that asking stored procedure required...
You know that thing where they say if you give a monkey with a typewriter enough time he will eventually re-produce all of Shakespeare's works? This must be one of the earlier attempts.EDIT:Dude,I've read all of your "hi to all" poasts and I really have no idea what you want. I think it has something to do with writing a stored procedure that will send mail.Have you looked at xp_sendmail in Books Online?Be One with the OptimizerTG |
|
|
|
avijit111@gmail.com
Yak Posting Veteran
55 Posts |
Posted - 2009-08-21 : 01:00:40
|
quote:
Originally posted by TG
quote:
...that mail coding what i sent to u....when i was run that in t-sql that asking stored procedure required...
You know that thing where they say if you give a monkey with a typewriter enough time he will eventually re-produce all of Shakespeare's works? This must be one of the earlier attempts.EDIT:Dude,I've read all of your "hi to all" poasts and I really have no idea what you want. I think it has something to do with writing a stored procedure that will send mail.Have you looked at xp_sendmail in Books Online?Be One with the OptimizerTG
thankyou brother TG for your great attention for me... |
|
|
|
|
|
|
|
|
hi to all.......