Query To Get The Domain From The Email
Jul 20, 2005Hi,
I wanted to get the domain name from the email in a query..
So if the email is Join Bytes!, I just to display hotmail.com
Thanks in advance for the help.
AJ
ADVERTISEMENT
Email Address Domain Strip Of Query
Oct 1, 2007Could 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
Cross Domain Email
Jul 8, 2015We are using SQL Server 2012 . We have configured the Database mail Services. Issue is, the mails are going to only inside the domain not going cross domain Email address like hotmail,gmail and etc. But from outlook client the mails are going to cross domain Emails. Same issue is in SSRS Email Services also. We have checked the SMTP Server, we could not figured it out.
View 2 Replies View RelatedEmail Subscription Can't Use External Domain
Apr 7, 2008Hi there,
I have successfully set up a Report Subscription and it sends the report to our internal users at a given time each day.
I recently added an external email address in the 'CC' & 'BCC' boxes but i got an error saying the address was invalid (i've tried with just the 'To' box as well). There's nothing special about the external address, i've tried with gmail, BT, and Yahoo.
So it would seem that any address outside of my local domain is invalid.
Is there some config setting that i'm overlooking? Has anyone had this problem?
I'm using SQL Server 2005 Standard Edition.
Any help would be great.
Thanks,
Lee.
PATINDEX - Search For More Than One Email Domain
Sep 30, 2014I want to search a table and display only results that contain particular Hotmail and yahoo email addresses. The code I am using currently is:
Select Name, Email, PATINDEX('%Hotmail%, Email) From tblPerson
How do I search for more than one email domain? Do I use a case statement, if so how?
How To Extract Domain Name From An Email Address??
Aug 2, 2007Hi
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
Like Condition - Retrieving All Email Address Using Particular Domain Extension
Oct 30, 2014I 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.
User On Trusted Domain Does Have Permission To Access Linked Server On AD Deployed In Another Domain
Sep 28, 2007Hi,
We have the followoing:
-A "master domain" AD, a "sub domain" AD, a trust relationship between the two (sub trust master)
-A sql server 2005 on a win server 2003 in "sub domain" AD
-A linked server to "sub domain" AD
-A linked server login using a "sub domain" admin acccount
-A view to this linked server
-A grant on masterDomain/Domain Users to the database
-A grant on subDomain/Domain Users to the database
-We want all connections done through "Windows Authentication" not "Database Authentication".
Queries on the view work fine using "sub domain" user accounts.
Queries on the view fail using "master domain" user accounts (including master domain admin accounts)
"Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation."
All connections are done through "Windows Authentication" not "Database Authentication".
Can we establish cross domain connectivity with "Windows Authentication" ?
Below are details of the implementation:
SELECT TOP (100) PERCENT *
FROM OPENQUERY(ADSI,
'SELECT displayname, givenName, sn, cn (etc...)
FROM ''LDAP://OU=PEOPLE,DC=subDomain,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')
EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false',
@rmtuser='subDomainAdminAccnt', @rmtpassword='sunDomainAdminAccntPassword';
In SQL Server Mngt Studio in Server Objects/Linked Servers/Providers/ ADSI properties security tab I have:
"connections will: <be made using this security context> Remote login:'subDomainAdminAccnt' With password: 'subDomainAdminAccntPassword'
Error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT displayname, givenName, sn, cn
FROM 'LDAP://OU=PEOPLE,DC=subDomain,DC=com'
WHERE
objectCategory = 'Person'
AND objectClass = 'user'
" against OLE DB provider "ADsDSOObject" for linked server "ADSI".
Problems With Change Sql Permissions After Migrating Domain User/group Accounts Into Root Domain
Apr 5, 2007I have a root domain and child domain.
After using ADMT to migrate the domain user or group into the root domain, when I use enterprise manager to try and change the permissions allocated to that domain user/group, i get the 'Error 15401 NT user or Group not found'.
This is a correct error as the user is now in the root domain, however sql (in sysxlogins) still thinks its in the child domain.
Is there a simpler way, other than collecting the users permissions, deleting the user from SQL then adding back in with the correct domainusername format, then adding the permissions back?
I tried renaming the 'name' in sysxlogins (not recommended) and while that worked, whenever I tried to add the migrated user to another database, the login name was missing and would not resolve.
I believe it is something to do with the SID not matching.
Any ideas on how to fix this ?
SQL Security :: Domain Migration Altered SA Or Domain Admin Access To DBs
Jun 19, 2015we recently migrated from our in-house domain to the Enterprise domain. Everything went smooth except for the fact that I can no longer accept my dBs using my SA or my domain admin account. There is only 1 account I can get into the management studio with but it has no admin privileges, so I can't make any password changes or add accounts. I don't have a test environment so kind of hesitant to experiment with our production system.
View 6 Replies View RelatedTransact SQL :: Query From Database From A Different Server And Domain
May 7, 2015In my procedure I need to pull data from a database which is on other server and domain.
As usual I have used the below query but having troubles with mode of authentication.
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=x.x.x.xsyz;Trusted_Connection=yes;','SELECT top 10 * FROM pcm.stats.visits') AS a
I get the below error:
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication"
I understand from the error that the domain and mode of authentication is different.
I use windows auth and we are given sql auth on the remote server which is on different domain.
I was suggested to use 'sp_addlinkserver' to link the remote server and query the data.
I am just curious to know if we can succeed through link server, is there any script to use sql authentication of remote server in my procedure and pull the data.
Access Otehr Sql Server Ina Domain W/ Query Analyzer
Mar 2, 2006Hi everybody,
I do not know if this is the correct area to post this topic? So, How to access
different sql server with query analyzer? Usually, when to install sql server, it
access the database server locally installed, now I like to access other sql server
within a domain using query analyzer. How to configure this in order I could use
query analyzer to access other sql server within a domain? Thanks in advanced.
den2005
Select Query To Get Email Id
Feb 2, 2007I have three tables as
Employeemaster
Designation
Grade
In grade table i initalised grades for designations from designation table
those designations have been initalised in employeemaster for employees
If I select particular employee code depending on designation iam getting his higher authority designation
now if i select designation from higher authority i need to get particular authority email id from employeemaster
Malathi Rao
Email The Results Of The Query
Apr 17, 2007Hello,
I want to email the results of the query. The query output can be email either as the HTML body of the email to be sent or as a excel attachement.
I am wondering as to what would be the best way to achive this.
a) I could have a email client module in C# or VB.NET 2.0, which would fetch the query resultset and store it in a dataset. I format it as HTML body and send the email using System.NET. For excel, can use XSLT
Please suggest if there is any other approach that would be more suitable.
Thanks.
Creating A Formated SQL Query And Email It
Feb 4, 2002I need to create a "simple" sql query but have it formatted and then emailed
to individuals. I am trying to convert over from Oracle but I am having a
hard time getting the query to be formatted the way I want. Here is the select statement I tried to use with no luck with xp_sendmail:
SELECT ALARM ID, TIMESTAMP as TIME, CLASS, RESOURCE, P, L, F, ALARM_MESSAGE
FROM ALARM_LOG WHERE ([TIMESTAMP] < GETDATE() - 1) ORDER BY [TIMESTAMP];
Here what the output should look like with the column headers underlined and
then the column data (notice the column heading ALARM MESSAGE is on the
next line before any data):
ALARM ID TIME CLASS RESOURCE P L F
-------------------- ----------- ----- ---------------- - - -
ALARM_MESSAGE
-------------------------------------------------------------
B3_TEMP_ALM 02-02@11:21 ALARM BUILDING3_TEMP N G G
BUILDING 3 TEMPERATURE ALARM, CALL SECURITY.
B5_TEMP_ALM 02-02@11:22 ALARM BUILDING5_TEMP N G G
BUILDING 5 TEMPERATURE ALARM, CALL SECURITY.
How can I run the query and have it formatted and emailed out?
What is the best way to accomplish this?
Thank you,
Mark
Query Trigger Email To User
Aug 6, 2013I need to trigger an email to user when a work order due date is approaching 1 day prior to the due date. Also I need to trigger when a user adds, delete, or modify work order to check on work order due.
Not sure how to query it in SQL.
Due Date = NeedByDate
user = AssignedTo
Sending Query Result By Email
Aug 30, 2007Hi Guys,
Whats the best way to send a small query result using Send Email Task. I have log table not more then 50 rows with 3 columns I need to query this table and send email.
I tried using Exe SQL Task and in result set I tried to store in variable so that I can attached that variable as email source. I think query result I can't store in string variable like this. Any simple way?
Thank you - Ashok
Stored Procedure, SQL Query And Email
May 25, 2007I have a situation that I need to accomodate as follows:
Customer account table as a bit value as to whether to send an email
For each customer with a true value set I need to run a query against their products table to check whether the reorder value is less than current stock.
if the recordcount for this query is > 0 then I need to send the customer an email using the address in the customer file.
This process needs to be run once per week.
Can anybody offer advice, samples as to resolve this problem
Thanks
Automatically Email Query Results
Aug 14, 2007
I'm very new to SQL but I have figured out how to do my first query. Now I would like to automate it to send the query as an attachment to users automatically (scheduler/cron??). How would you go about this, I need step by step hints. I'm using SQL Query Analyzer ver 8.00.2039 to generate the query from CDR records. Is this even possible?
Thank you for any guidance.
Send Query Results Via Email
Sep 27, 2007is it possible to use send mail task to send results of a query via email? if it can be sent then what would the result look like? will it be like excel type format or fixed length? or comma delimited?
and obviously -- how can I accomlish it?
thanks
Generate A Email Using Smtp Server (was Help With Query....)
Jan 21, 2005Help with query: I currently cannot be alerted by SQL Mail so I would like to take the script that was generated by SQL server and using the store procedure sp_sqlsmtpmail to generate a email using smtp server to alert me. The store procedure does work. I would like to know if this is possible.
Thanks
Lystra
-- Script generated on 1/21/2005 10:04 AM
-- By: MAMSIsa
-- Server: (local)
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Demo: Full tempdb'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'Demo: Full tempdb'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Full tempdb', @message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10, @include_event_description_in = 5, @database_name = N'tempdb', @category_name = N'[Uncategorized]'
Then
Exec sp_sqlsmtpmail
@vcTo = 'lwilliams@Huc.com',
@vcBody ='Check out problem Immediataly.',
@vcSubject ='DOCSITE01FDK - Full Tempdb Log'
END
MS SQL 6.5 Procedure To Send Query Results Via Email
Jul 20, 2005Hi,I'm not sure if this is possible as i've googled everywhere, but i have aselect query that returns a customer record with their associated salesorders. I would like to automate a process which sends an email reminder toeach customer in the database, that has outstanding orders. This emailreminder should have the results of the query regarding their account.The table structure are as follows.---------------------Customer_tbl---------------------CustomerIDAccountNoNameEmailAddress---------------------Order_tbl---------------------OrderIDCustomerIDReferenceAmountDateOutstanding_flgCan anyone help?Sen.
View 2 Replies View RelatedTransact SQL :: Create Alert Email From Query
Jun 21, 2015How can I turn this query into an Email alert if one of the counts are GT 50. The Source are machines that I capture counts(Unprocessed_Cntr) from every 5 minutes and load to the Load_Time_Capture table. I also add a datetime to each capture(Unprocessed_Capture ).
If any individual source has a count > 50 or if the combined(ALL) GT 50 send an email to support person.
SELECT
CASE GROUPING([Source])
WHEN 1 THEN 'ALL'
ELSE [Source] END AS 'Input Source',
avg([Unprocessed_Cntr]) as 'Average Queue Past 15 Min'
FROM Load_Time_Capture
WHERE Unprocessed_Capture >= DateADD(mi, -15, Current_TimeStamp)
GROUP BY [source] WITH ROLLUP
Transact SQL :: Send Email Only If Query Contains Results
Aug 8, 2015I’m running a data integrity procedure from an agent job that is a scheduled weekly maintenance task which emails the results of my query, and is working properly.
I would however like this to only receive the email it the query contains results, and not send it no records are found to prompt me to take action.
My code less the personal information
EXEC msdb.dbo.sp_send_dbmail
@profile_name
= '',
@recipients
= '',
@subject
= 'Database Integrity - Import Errors',
[Code] ....
Reporting Services :: Data Driven Email Subscription With Different Email Per Report Page
Jul 6, 2015I 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 RelatedDbmail Doesn't Rely On IIS SMTP, How To Set Bounced Email Redirect Email Etc.? Thanks
May 4, 2007Under 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
None-Domain Server Cannot Access SQL2005 Data On Windows 2003 Domain Server
Sep 26, 2006I'm trying to run a test from my test environment which is a non-domain Windows 2000 server to access my domain 2003 with SQL2005. I have install 2005 tools to try to access the SQL server.
- I have try following the KB265808 - no success.
- Reading alot of blogs and it seems all are pointing to the same problem. "Remote access" but the settign is enabled.Error Message:
TITLE: Connect to Server
------------------------------
Cannot connect to ardsqldatawh.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
Question: Could Windows 2003 security be blocking access? I'm using sa account to access.
Also, sa account does not seems to work for remote access. It is ok when accessing locally.
Any help would be appreciated.
949jc
Help Split List Of Email Add Comma For Evry Email
May 12, 2008need 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'
Schedule And E-mail Query Results In HTML Formated Email
Aug 14, 2004Could anyone walk me through how to do the following:
Schedule a query to run at a designated time
Embed the resulting table in a HTML email
Email the HTML formated results someone automatically
Can this be done with SQL Server only or is there a third party app?
I know its alot but I'm having a hard time finding resourses.
Export Query Results To Excel File And Add As Attachment In Email
Jan 2, 2014Is there a way to export query results to an excel fie and add that file as an attachment in the email? All this has to be done using SQL query and it needs to be automated. My coworker tried using Openrowset and BCP, but it is not working.
View 3 Replies View RelatedQuery To Pull Data From Multiple Columns (Three Email Fields)
Mar 12, 2015I 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 RelatedSQL 2012 :: Possible To Send Pivot Query Results As Automated Database Email?
Nov 26, 2014possible to send Pivot query results as automated database email ?
View 3 Replies View RelatedLoad A Text File With Email Addresses And Compare Against A Database Table That Has Email Addresses And User_id
Jul 12, 2007Hello ALL
what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.
I also want to update some users using a different text file.
Please help me with the best way to do this
Thanks in advance