There is a Table DISPLAY_DETAILS in which a Column - DISPLAY_NAME, which displays data in it as :
Joe Barnard(123456);Paul Johnson(114454); - Display as Name(Employee Id).
There is a EMployee Master Table EMP_MASTER Which contains Employee Id and Email Address Columns.
From this: Joe Barnard(123456);Paul Johnson(114454); I need to get the Email Address for these Employee Ids - 123456 and 114454
Hello Everyone,
I am bit confused.
I am using vwd2005 express,c# and sql express.
I have a webform that registers new employee(title,name,age,address,phone,email) and inserts those data into the sqldatabase.
The problem is there might exists 'N' number or email ids and phone nos for a single user.
for eg: user A might have 3 email address where has user B might have 5 email address.
so in that case its not appropriate to create 5 textboxes in the webform and create 5 column like email1,email2,email3,email4,email5 in the database for both users..
i hope your getting the point.
I tried to create a separate table for emails and phone.
But i am new to relational database.
So if i need to use Relational database then could anyone help me here to :-
1.create a table structure here.(what would be the structure of tbl_employee, tbl_email and tbl_phone)
2.How to write insert query if i am using RD here?
plz help explaining the concept with a simple running example.
Thanks in advance.
Jack.
We have an identifier of who has performed an ection which populates a table with their email address in the format firstname.surname@domain. I am looknig for some code to split out the first name and surname (and, if possible, capitalise both) so uor audit report can show the actual name instead of the email address.
Has anyone encountered this issue previously and know a solution?
Many thanks in anticipation.
Is there a handy-dandy way for me to be able, from a stored procedure, to capture the notification email address for a user associated with a job?
I need to send an email out from a stored procedure, and I want it to be set up to send to the same user that is set up in the job that calls the stored procedure.
the same notification stored proc will be used in multiple jobs, but the jobs are already set up with a notification email to be sent to the defined users "on completion" (error or not). I want a separate email to be sent, but I want to use xp_Sendmail because I have to perform a select who's output I want to appear in the body of the email (and unless I am missing something, I have no control over the body of the standard "job completion" email).
I would prefer to NOT have to "hard code" the user email address in the stored proc that calls xp_sendmail, OR to put it in some user table in the database, when the user I want to send it to will always be the same one defined in at the JOB level in Enterprise Manager.
Any thoughts? Thanks in advance for your instantaneous and informative answer-packed responses! ;)
I'm trying to strip out the First & Last Name from an Email Address. The email address is formatted as "FirstName.LastName@emailaddress.com" but sometimes a middle initial is used (ex: "First.M.Last@emailaddress.com").
I can locate the '@' sign and the first '.' period(from left to right) but need to find the first '.' from the left of the '@' sign.
I'm doing this in a view.
Here's some sample data and what I've tried:
CREATE TABLE dbo.Table1
(EmailAddr VARCHAR(255))
INSERT INTO dbo.Table1 (EmailAddr)
SELECT 'Andy.Smith@gmaddr.com'
UNION ALL
SELECT 'Betty.Lee-Jones@gmaddr.com'
[Code] .....
Hello,
I will like to know the best practice for grouping email addresses in a table.
We have a basic table that has email address for admin users. Which goes thus
CLIENTS EMAIL
emailID int
username: varchar 20
Client_email varchar 150
The Above table is for our admin users. Enabling them to send out emails to clients. Based on their bills.
We have a second table for mass mailing. Which has emails in groups. Each group is based on the above entry. And groups could range from 5 users up to 100,000 users.
Whats the best way to implement this.
Do I create a seperate column for each email entry, knowing that this table would be extremely big, with a lot of repeated values, ie username etc. Or group them TOGETHER as varchar(max)
PLEASE ADVICE, best practices
thanks
Mary
Yes O !
Hi,I have 2 fields in a database: 'email' and 'website'.In the majority of cases, the website field is not populated even though theemail address is. In 90% of cases, the website will be 'www.' followed bywhatever is after the '@' symbol in the email address.Would anyone be able to help me with the SQL that would take whatever isafter the '@' sign in the email address, add it to 'www.' and populate the'website' field?Thanks!
View 3 Replies View RelatedHi
do you know incase a script which extracts domain names from an email address?
like foreg. we have xyz@dmc.com or abce@dmc-int.com
should give
dmc and
dmc-int
i ve a filed named "Email" of datatype .i ve created this field to insert email address in this field.but when i insert an email address like "bilal@yahoo.com" from a textbox wh is in an ASP.net webpage then in the filed i see this "System.Web.UI.WebControls.TextBox".
can u plz tell me how to fix this
Regards
Ahmed Bilal Jan
Could this query be made more efficient?
It takes ages to run it. It strips of everything from an email address and keeps only the domain.
SUBSTRING(Mailaddress, CHARINDEX('@', Mailaddress) + 1, CHARINDEX('.', SUBSTRING(Mailaddress, CHARINDEX('@', Mailaddress) + 1, 100)) - 1) AS mail_domain
this is what i am looking for,
A client with an email address that does not end in uk cannot make a room booking
Any ideas
ms sql server
data:
idno Email Category
1 a@a.com c
2 b@b.com c
3 b@b.com c
4 b@b.com c
only unique email address record are requierd.
1 a@a.com c
2 b@b.com c
what could be the query for such requirements.
(NOTE: FROM a NON-IT PERSON) We've implented SQL 2005 and Reporting Services, but our subscriptions only work internally: when we try to send reports via subscriptions to valid external email addresses, we get a message: invalid email address and the entire subscription does not work.. PLEASE ADVISE. IT WILL BE GREATLY APPRECIATED!
I would like to use ISS to extract the user name and email address out of the active directory. I would like to put it in an MS-SQL 2005 table that I can use stored procedures on. I am hoping I can use a query to do it but I understand there is a 1000 entry limit on the extraction of the Active Directory and I have more than 1000. Could someone point me in the right direction please. Thanks.
View 1 Replies View RelatedI just moved our SSRS server from one machine to another. It was sending our report emails from a service account. So when my end users get the emailed report it was from a service account (reports@blah.com) .
Now that I moved the DB to another server - the reports get emailed successfully - but they are being sent from my email address!
I have checked the rsreportserver config file. I also checked in the report server smtp setup and they both show the service account. I even checked the subscription owners and accounts.
SSRS is using my account, but I can't find where it's getting it from. Where else can I look?
how do I set up a SQL2000 server to send an alert to both an email address and a pager when a specific event occurs..please help!!! The server is in a different location and we need notification of problems!! thanks
View 1 Replies View Relatedi have a situation where is need to get email address from a varchar column. Here is some sample data for five records; The format can be change.
dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk
Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;
dummy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;
dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;
dummy text;
t: +11 (0) 222 222 2222;
e: myemail@test.com;
w:http://www.yahoo.com/faqs;
I am trying to retrieving all email address using particular domain extension for e.g. gmail.com
I wrote the below query
Select policyid,emailaddress from policyholder
WHERE policyholder.EmailAddress like '%gmail.com%'
It works fine, but i want to use the LIKE condition against ‘emailaddress’ column in email table as I have more than 100 domain extensions
I wrote something like
WHERE policyholder.EmailAddress like (select emailaddress from email) but I am getting the below error
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Dear Mr Portas and Mr Sommarskog,Cause I am a newbie in sql, can I ask for your email address?My email Address is Join Bytes!ThanxBest RegardsMichael--Message posted via http://www.sqlmonster.com
View 3 Replies View RelatedI need a query to pull the data from Sql server. my requirement is i need to pull the data from multiple columns, in that there are three email fields are there like email1, email2, email3. i need query to retreive the data from table first it search for email in the above 3 fields if any one of the fields contains the record the it display as Main mail id.
View 6 Replies View RelatedHi,
The Browser role has the task Manage Individual Subscriptions. Fine, the user can create a subscription. The problem is that the reportserver populate the email address text box with the Windows User ID and does not let the user change this to put his email address instead. The text box is greyed out.
I found that to enable the text box I have to add the task Manage all subscriptions to the role "Browser".
This is a major security problem because with this setting any user can edit any subscription.
I suspect that I could work at the Active Directory level to populate a field that would in turn populate the email address with the correct data., however this is not an option for me. I have absolutely no way to do it or get it done. Does anybody has an idea on how I could allow a user to edit his email address in his subscriptions and not let him tamper with other users subscriptions?
Thanks
Philippe
I am implementing a fax solution (Right Fax) and pulling email information from a table and passing into a variable. In order to send out a fax via email, the syntax is in the following example below:
Example:
To send a fax from Outlook to Jane Doe at 555-1212, enter:
[RFAX:Jane Doe@/FN=555-1212]
When the following syntax gets passed into the Send Mail Task, into the "To" line and the package is executed I'm receiving the following.
[Send Mail Task] Error: An error occurred with the following error message: "The specified string is not in the form required for an e-mail address.".
[Send Mail Task] Warning: The address in the "To" line is malformed. It is either missing the "@" symbol or is not valid.
I realize it’s a malformed email address. Is it possible to create a group and instead of the fax syntax pass the group name?
I have subscription by email problem on the following configuration:
Windows XP prof.
SQL Server 2005 Enterprise
Configured Database Mail with remote SMTP server to send email successfully
Configured SSRS to use remote SMTP server
Edited the Rsreportserver.config file as following (by the instructions from MSDN)
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>smtp.broadband.rogers.com</SMTPServer>
<SMTPServerPort>25</SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SMTPAuthenticate>0</SMTPAuthenticate>
<From>My email Address</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
I scheduled my subscription and tested it. I didn't receive any mail from the setting email address, but got an error message in ReportServerService log file as following.
Error sending mail, CDO error -2147220978, will not resend
Error sending email. System.Runtime.InteropServices.COMException (0x8004020E): The server rejected the sender address. The server response was: 530 authentication required - for help go to http://help.yahoo.com/help/us/mail/pop/pop-11.html
I did a little search, but couldn't find any solution for it. Is anybody successfully to get the Reporting Services subscription by email function working through MS SQL Server Management Studio or if you have a solution for this issue, would you mind to share the experience with me. I will be very appreciated.
Hi
everybody.
I am new to VBA,.,, I Have created a folder under inbox in Outlook 2007 which stored the receipients information
like name, E-mail, which is not present in the address book of outlook.
I have imported this folder in access 2007. My problem is ... it does not display the email address.
Rest of the informations is displayed... Is there any way i could do this.??
is there any way i could get the email addresses to appear(import/get) in to the access table???
Is there any module i mite have to create??
Plz help...
thanks a million
I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?
It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).
Hi all,
I have following problem:
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
{
engine.CreateDatabase();
}
serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";
using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(
Configuration.Default.SyncServerAddress, "", "", connStr))
{
rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");
}
Everythink works fine, when I use 'sa' user account in serverConnStr.
But, when I change conn string to:
"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Please help.
Thank you.
Fipil.
I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.
View 2 Replies View RelatedUnder IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.
Any idea?
thanks
need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this
emall
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm
need to do ilke this
Code Snippet
@list_email = (SELECT emall FROM tblLogin)
--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm
@recipients = @list_email
Code Snippet
IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )
BEGIN
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
Date_born AS 'td','' ,
Age AS 'td','' ,
BirthdayToday AS 'td','' ,
BirthdayThisWeek AS 'td'
FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>
<tr>
<td>name</td>
<td>fname</td>
<td>date</td>
<td>age</td>
<td>aaaaaaaaa</td>
<td>bbbbbbbbbbbbbbb</td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'rrr@iec.co.il',
@copy_recipients='rrrrr@iec.co.il',
@body = @body,
@body_format ='HTML',
@subject ='ggggggggggggggggggggg',
@profile_name ='ilan'
END
ELSE
print 'no email today'
I need to select last order for each employees for homework. I use northwind database for testing. I can solve it by correlated subquery but the professor said me it is not optimized.
select orderid, customerid, employeeid, orderdate
from orders as o1
where orderdate = (select max(orderdate) from orders as o2 where o1.employeeid = o2.employeeid);
I have a database that has dozens of tables. Many of these tables reference the employee ID.For example tblDaysOff has a column employeeID that is matched on tblEmployees.ID, and there are many such tables.
Now the employee IDs are changing the way they are generated. Instead of a alphanumeric value being stored as a text value, all employee IDs will be uniqueidentifiers stored as text values.The question is, how can I change every instance of "somevalue" in every record in every column where the column name is "employeeID" in every table in the database to "differentvalue" where employeeID = "somevalue"?This is what I have cobbled together from multiple sources ... but there is a syntax error where @max is located.
Code:
USE CsDB
DECLARE @t TABLE(tRow int identity(1, 1), tSchemaName nvarchar(max), tTableName nvarchar(max))
INSERT INTO @t
SELECT SCHEMA_NAME(schema_id), t.name
FROM sys.tables AS t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE c.name LIKE '%employeeID%'
[code]...
Obviously I don't want to run this and then have to try and recover the database when things go away.
I have a table Item_used like this
Itemname Employee Quantity
pencil samlopez 10
pencil samlopez 5
All I want is to make a report that sum all the quantity of the same items with the same employee like this
Itemname Employee Quantity
pencil samlopez 15
Dear All,i want to know how to get top three salary getters from the employee(eid , ename, salary) table
i tried this select top 3 salary from employee order by salary desc
but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks