We 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 RepliesHi All - Need some Replication help...
I built SP's to create the Distributor/Publisher and Subscriber parts of Snapshot Replication. All run fine on 2 SQL2K installs on the SAME Domain.
BUT if I try to cross to another Domain (SQL2K as well) = NO JOY.
Error @ Subscriber doing a PULL: "Cannot connect to Distributor..."
Tried using sa, administrator, NEW Local user, etc. & continue getting the error. ALSO tried changing the Login of SQL & SQL Agent Services to the SAME Login on BOTH machines.
Help me beautiful SQL Gurus!!!
RobbieD
Lets say I have a single instance SQL Server on a 2 node cluster with node names Server X and Server Y. These are part of Domain A.
SQL Agent Account runs under service account - DomainASQLAct.
Now I have Server Z which is a standalone SQL Instance on Domain B and there is no trust between domain A and B, not even one way trust.
Even though I dont have trust, I need to configure log shipping for the SQL Instance which is clustered under Domain A to a standalone box in Domain B.
Microsoft recommends Local Account(SQL Agent) to be used for Cross Domain Log Shipping. Also when there is no trust, MSFT recommends to use the same login name and password, however as my setup includes a cluster and cluster cannot have local account I'm not sure what will be the best strategy to follow here.
Hi There
I have service broker working 100% with an initiator - forwarder - target, all in the same domain using windows authentication on the endpoints, for all instances' the sql server service run under the same domain account, which in turn is granted connect on the various endpoints. This all works 100%
However i have a scenario where the initiator will be in a different trusted domain.
I need to know if i am correct in thinking that the initiator sql server servcie account can run under DomainAsqlservice, the target instance sql server servcie can run under DomainBsqlservice, then on the forwarder i simply grant connect on the forwarder endpoint to both DomainAsqlservice and DomainBsqlservice.
Is this correct, will this work?
Thanx
I have created SSIS package to transfer table data(1500 rows tbl size=140 MB).
To transfer data across the network its taking around 1 hr.
I have tried by removing indexes and constraints on destination table.
How should I improve the speed as the table is very small.
I want to use Transaction(MS DTC) in SSIS package across domain. It's working fine if both the servers are in Corpnet microsoft domain but failing it one of the server at extranet microsoft domain. I did all the required settings for MS DTC service to run for distrubuted transaction and in SSIS package did the "TransactionOption" property for the container object to "Required" and for all inner tasks to "Supported". Is it possible or a Security voilation which won't allow to do through SSIS package.
Example: I have a database on Extranet server and a database at Corpnet Server.
Sql Job will pull the data from Extranet Database to Corpnet database through SSIS package and update back to Extranet database. Job will reside on Corpnet server.
SSIS package is failing with the error message: The AcquireConnection method call to the connection manager "<Connection Manager name>" failed with error code 0xC0202009.
Any special setting do we require for this. I did the following setting on both the servers:
Ø MS DTC should run on both the servers under €œNetwork Service€?
Ø Set the following on both the servers(ExtranetCoptnet) to run on a Distributed Transaction:
§ Go to "Administrative Tools > Component Services"
§ On the left navigation tree, go to "Component Services > Computers
§ €œMy Computer" (you may need to double click and wait as some nodes
need time to expand)
§ Right click on "My Computer", select "Properties"
§ Select "MSDTC" tab
§ Click "Security Configuration"
§ Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP"
§ The service will restart
Note: If I will chage the Extranet server to Corpnet then it's working file for me.
Hi,
We have two domains in our organisation, both with transitive trust of one another.
In our main domain we have a report server which is available for all users across both domains to access and use. Most of the time there isn't a problem but I'm having a bit of an issue with users from our second domain accessing a linked report containing 13 subreports.
The main report is fine and all users can view this, however, all subreports show an error message to users in our second domain.
As a bit of an experiment I have even given one user Content Manager rights on the report server but this hasn't solved the problem. I have checked that permissions are correct on ther server, folders and data sources dozens of times and cannot find any reason why second domain users are having this problem.
If anyone has any suggestions on how to solve this I would be grateful for help.
With humble thanks in advance,
Paul
Hi,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.comThanks in advance for the help.AJ
View 2 Replies View RelatedHi 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.
I 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?
Hi
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
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
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.
I have client machine in one domain, say A domain , on which I have installed .net 2.0 without any problem. I have report server running in other domain (B domain) where I have deployed few models which will be used from domain A to create ad hoc reports by launching Report Builder. I can access other reports in domain B from A
But when I click on Report Builder ..it tries to connect to server in domain B but fails to connect and shows following log in a text pop up
I know, problem is because of cross domain communication but question is then how to fix it ? what changes I need to do for that in firewall, domain etc etc settings?
Here is the error, I have highlighted lines which might give some clue:
------------------------------------------------------------------------------------------
PLATFORM VERSION INFO
Windows : 5.2.3790.65536 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)
SOURCES
Deployment url : http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later in the log.
* Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected:
+ Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
+ The remote server returned an error: (500) Internal Server Error.
COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.
WARNINGS
There were no warnings during this operation.
OPERATION PROGRESS STATUS
* [12/19/2006 6:49:58 AM] : Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application has started.
ERROR DETAILS
Following errors were detected during this operation.
* [12/19/2006 6:50:14 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
--- Inner Exception ---
System.Net.WebException
- The remote server returned an error: (500) Internal Server Error.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.
Hi,
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".
I 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 ?
we 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 RelatedI 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
I'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
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'
Hello 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
Hi all,it happen to me a strange problem:i have a mdb file (in Access 2K) with SQL Server 2K linked tables whoruns on a workstation which is on a different domain that the SQLServer. It works.If i create a mdb file from a workstation which is a the domain of theSQL Server and then i run it a my non-domain workstation i have errormessage:Login failed for user '(null)'. Reason: Not associated with a trustedSQL Server connectionBut if i reattached my tables it works.If someone have an idea....PS: same ODBC on both machines
View 1 Replies View RelatedCurrently running a SQL 2000 server in 2000 domain and want to migrate it to a new 2003 domain of the same name.
How do I go about it and is there any problems with this plan?
Hi
I'm trying to set up replication from one SQL server to another.
The publishing server is not a member of a domain and is located in a hosting center (but we have full control over the server). I can set up a Snapshot publication just fine.
The subscribing server is located in another remote location and is a member of a domain. Here I can also set up the subscription without errors.
The errors, I think, comes when the snapshot is about to be created, the error is, on the publisher server:
[298] SQLServer Error: 18456, Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. [SQLSTATE 28000]
And the snapshot is not created.
Is it even possible to set up replication like this. I need to transfer the data from one sql server to another so we have a working "backup" so to speek if the other server does not respond.
I should've posted this thread here....
http://www.dbforums.com/t1005070.html
I need to format the following tables:
tbEmployees
EmployeeID | fName | lName
-------------------------------------------
jdoe | Joe | Doe
bsmith | Blake | Smith
tbDepartments
DepartmentID | Department
-------------------------------
ENG | Engineering
DET | Detailing
tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
------------------------------
ENG | jdoe
DET | bsmith
tbProjects
ProjectID |
---------------
1001
tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
-------------------------------------------
1001 | jdoe | ENG
1001 | bsmith | DET
To the following view :
vProjects
ProjectID | Engineer | Detailer
-------------------------------------
1001 | Joe Doe | Blake Smith
Any Ideas?
Mike B
Is it possible change to cross tab using code?
Before code:
NAMETYPEVALUE
AAAHEIGHT66
AAAWEIGHT198
After code
NAMEHEIGHTWEIGHT
AAA66198
Hi,
I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.
I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.
It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.
AcademicYearId StudentId coursecode completionstatusid
05/06 912345 FWFT1 CONTINUING
05/06 914587 FWFT1 CONTINUING
05/06 987654 FWFT1 WITHDRAWN
05/06 954376 FWFT1 CONTINUING
05/06 934512 FWFT1 TRANSFERRED
05/06 923000 FWFT1 TRANSFERRED
04/05 945109 FWFT1 COMPLETED
I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this
Year CourseCode Students Continuing Withdrawn Transferred Completed
05/06 FWFT1 6 3 1 2 0
04/05 FWFT1 1 0 0 0 1
Can anyone help me with the the extra columns please.
thought my question was misinterpreted.actual table can be smaller or larger. but,within a procedure need to turn a created #temptablesimilar to(examid is in asc order):(id, p/f, examid, examname)1 f xxxx xxxxx1 f xxxx xxxxx1 f xxxx xxxxx2 p3 p4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx4 f xxxx xxxxx(note, id = 4 has 7 failed exams).to a table with up to five columns of failedexamnames:1 f xxxxx xxxxx xxxxx2 p2 p4 f xxxxx xxxxx xxxxx xxxxx xxxxxcan get rid of exameid if helps.--Sent by ricksql from yahoo subpart from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new
View 2 Replies View Related Hi!
I m using sql 2005.I have a table as showing below.
year
Region
loan_amt
pur_id
purpose
1981
Andhra pradesh
$20,000.00
1
Animal Husbandary
1981
Arunachal Pradesh
$110,000.00
1
Animal Husbandary
1981
Assam
$240,000.00
1
Animal Husbandary
1981
Bihar
$75,000.00
1
Animal Husbandary
1981
Chhattisgarh
$55,500.00
1
Animal Husbandary
1981
Gujarat
$77,500.00
1
Animal Husbandary
1982
Goa
$44,888.00
1
Animal Husbandary
1982
Himachal pradesh
$4,000.00
1
Animal Husbandary
1982
Himachal pradesh
$20,000.00
1
Animal Husbandary
1982
Jammu and kashmir
$30,000.00
1
Animal Husbandary
1882
Jharkhand
$35,000.00
1
Animal Husbandary
1982
Karnataka
$40,000.00
1
Animal Husbandary
1982
Kerala
$20,000.00
1
Animal Husbandary
1982
Madhya pradesh
$5,000.00
1
Animal Husbandary
I want to produce report as by using crosstab query as showing bellow
Year Asam Hyadrabad goa arunachal pardesh etc............
1981 1000.00 2000.00 8000.2 00000 000000 ......
1981
'
'
Is it possible by crosstab query ?or please suggest me another way as early as possible.
Thanx in advance.
Abhishek
I wanna develope a report using cross tab query. I have table wit the name spend in which i have spenddate and there is also a amount field which will show the sum of amount field against months, i wanna develop repport in this format
merchant name jan 2007 feb 2007 dec 2007 jan 2008 feb2008
test 45 50 25 10 11
Please can any one help me how to do this this is very urgent ?
I'm trying to run the cross tab query so that it returns only one row. If there is data I want to dump in the field value (fText), otherwise I put in a dash. The problem I'm having is that it's returning multiple rows when I want a single one. I could create a # table and dump the data in, but it seems a bit long winded.
Many thanks.
SELECT distinct (CASE WHEN f.TypeID=1 THEN 1 ELSE 0 END) f1,
(CASE WHEN f.TypeID=2 THEN f.fText else '-' END) f2,
(CASE WHEN f.TypeID=3 THEN f.fText else '-' END) f3,
(CASE WHEN f.TypeID=4 THEN f.fText else '-' END) f4,
(CASE WHEN f.TypeID=5 THEN f.fText else '-' END) f5,
(CASE WHEN f.TypeID=6 THEN f.fText else '-' END) f6,
(CASE WHEN f.TypeID=7 THEN f.fText else '-' END) f7,
(CASE WHEN f.TypeID=8 THEN f.fText else '-' END) f8,
(CASE WHEN f.TypeID=9 THEN f.fText else '-' END) f9,
(CASE WHEN f.TypeID=10 THEN f.fText else '-' END) f10
FROM tblTest f WHERE (f.fID=219)