I've setup SQL Mail and I'm trying to setup SQL Server Agent mail as
well, but this does not work.
Both MSSQLServer and SQLServerAgent services run under the standard
local Administrator account. A mailprofile and a postbox is created for
this account, client is Outlook 2000, mailserver is Exchange 5.5. The
mailaccount works fine, I can sent and receive mail.
When activating SQLMail, no problem. I can use xp_sendmail without a
problem.
However, when trying to activate SQL Server Agent mail, after choosing
the Exchange profile(the same 1 as for SQL Mail, there is only 1
profile) I receive a MAPI Logon failed:
Error 22022:SQL Server Agent error: MapiLogon Ex Failed due to MAPI
error 273: MAPI Logon failed.
I can understand what this means:the Administrator account is not
recognized as a legal login. Since both services run under this login,
and this poses noprob for SQL Mail, it makes no sence to me. Obviously I
am missing something, but I don't know what.
I have googled the internet and came up with guiet a view similar
questions. Read several Q&A's, among others also the ones from
Microsoft, but either the suggested causes were not, or the possible
reasons did not aply. I found 1 solution which I tried: enter the
profile(BTW, there is only 1 profile), accept it tho testing it
generates the failure and stop&restart the SQLServerAgent. It did not
work.
Now a new feature has arissen: the mailsession droplist, with which to
set the mailprofile is greyed out, and the TEST button as well! This
means I cannot change the SQLServerAgent Mail settings anymore! Probably
I can rectify this by changing the service under which the
SQLServerAgent runs, but still I wouild like to know what is causing
this behaviour, and how to solve it.
Currently I'm not using SQLServerAgentMail, I use SQL Mail, but this
problem is nagging me and it irritates me that I cannot find out what is
wrong.
Systeminfo: SQL2K, W2K, Exchange 5.5, all applicable servicepacks and
patches, Outlook 2000 v9.0.0.2711.
Any hints apreciated,
Hans Brouwer
Tnx,
Hans Brouwer
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
It's not clear how you have the service accounts set up. If
by "standard local Administrator account" you mean Local
System, then this would be the problem. If you are using
Exchange as your mail server, the service account needs to
be setup as a domain account.
-Sue
On Wed, 14 Apr 2004 03:40:34 -0700, hansje
<hansjes@.anonymous.com> wrote:
>I've setup SQL Mail and I'm trying to setup SQL Server Agent mail as
>well, but this does not work.
>Both MSSQLServer and SQLServerAgent services run under the standard
>local Administrator account. A mailprofile and a postbox is created for
>this account, client is Outlook 2000, mailserver is Exchange 5.5. The
>mailaccount works fine, I can sent and receive mail.
>When activating SQLMail, no problem. I can use xp_sendmail without a
>problem.
>However, when trying to activate SQL Server Agent mail, after choosing
>the Exchange profile(the same 1 as for SQL Mail, there is only 1
>profile) I receive a MAPI Logon failed:
>Error 22022:SQL Server Agent error: MapiLogon Ex Failed due to MAPI
>error 273: MAPI Logon failed.
>I can understand what this means:the Administrator account is not
>recognized as a legal login. Since both services run under this login,
>and this poses noprob for SQL Mail, it makes no sence to me. Obviously I
>am missing something, but I don't know what.
>I have googled the internet and came up with guiet a view similar
>questions. Read several Q&A's, among others also the ones from
>Microsoft, but either the suggested causes were not, or the possible
>reasons did not aply. I found 1 solution which I tried: enter the
>profile(BTW, there is only 1 profile), accept it tho testing it
>generates the failure and stop&restart the SQLServerAgent. It did not
>work.
>Now a new feature has arissen: the mailsession droplist, with which to
>set the mailprofile is greyed out, and the TEST button as well! This
>means I cannot change the SQLServerAgent Mail settings anymore! Probably
>I can rectify this by changing the service under which the
>SQLServerAgent runs, but still I wouild like to know what is causing
>this behaviour, and how to solve it.
>Currently I'm not using SQLServerAgentMail, I use SQL Mail, but this
>problem is nagging me and it irritates me that I cannot find out what is
>wrong.
>Systeminfo: SQL2K, W2K, Exchange 5.5, all applicable servicepacks and
>patches, Outlook 2000 v9.0.0.2711.
>Any hints apreciated,
>Hans Brouwer
>
>
>Tnx,
>Hans Brouwer
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Hi Sue,
I use the Administrator account of the server, where SQL Server is
installed, not the local system account.
Tnx,
Hans Brouwer
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you are using the Administrator's account local to the
server then this isn't a domain account and won't work. You
need a domain account if you are using Exchange as the mail
server.
-Sue
On Thu, 15 Apr 2004 00:21:04 -0700, hansje
<hansjes@.anonymous.com> wrote:
>Hi Sue,
>I use the Administrator account of the server, where SQL Server is
>installed, not the local system account.
>Tnx,
>Hans Brouwer
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Tnx for the info Sue; it does kleave me flabbergasted. The server in
question is a stand-alone server, not part of a domain, obviously part
of the companynetwork. Why does SQLMail not need to be part of a
domainaccount? MSSQLServer is also running under the local
Adminstratoraccount.
I can think of 1 reason, why the SQLServerAgent should run under a
domainaccount, and that is for running jobs executing distributed
queries on remote servers. However, jobs can run which do not execute
remote queries. I can't imagine wy the mailfunctionality IS dependant on
a domaionaccount..
I know, if these are the facts I'll have to live with it, but I would
like to know the reason for choosing such a configuration.
Tnx anyway,
Hans Brouwer
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||You can find the requirement of a domain account for SQL
Mail when using an Exchange mail server in the following
Microsoft knowledge base article:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
If you'd rather just use smtp to run mail related tasks,
take a look at a free extended stored procedure which uses
just smtp for mail. You can find it at:
http://www.sqldev.net/xp/xpsmtp.htm
-Sue
On Tue, 20 Apr 2004 04:12:11 -0700, hansje
<hansjes@.anonymous.com> wrote:
>Tnx for the info Sue; it does kleave me flabbergasted. The server in
>question is a stand-alone server, not part of a domain, obviously part
>of the companynetwork. Why does SQLMail not need to be part of a
>domainaccount? MSSQLServer is also running under the local
>Adminstratoraccount.
>I can think of 1 reason, why the SQLServerAgent should run under a
>domainaccount, and that is for running jobs executing distributed
>queries on remote servers. However, jobs can run which do not execute
>remote queries. I can't imagine wy the mailfunctionality IS dependant on
>a domaionaccount..
>I know, if these are the facts I'll have to live with it, but I would
>like to know the reason for choosing such a configuration.
>Tnx anyway,
>Hans Brouwer
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Monday, March 26, 2012
many-to-many search
Hi,
I've currently got three tables, with the second being a link table to
establish a many-to-many relationship, and I'm trying to do a full-text
search. I want it to bring up instances in the first table (only one row of
the left table per search) where the text is found, and increase the rank of
the search results, where the search string occurs in the related table.
I'm currently doing a subquery that adds the sum of the rank of the linked
tables to create a total rank (example below), but I'm concerned that since
the two full-text queries aren't linked, the two ranks aren't relevant to
each other.
Has anyone else found a good/better way to do this?
Regards,
Dunc
--/ snip /--
SELECT * FROM (
SELECT VenueDetailID, VenueName, Addr1, Addr3, Addr4,
ISNULL(B.RANK, 0) + ISNULL(
(SELECT SUM(Rank)
FROM VenueExtras Y
INNER JOIN LinkExtrasVenue
ON Y.VenueExtrasID = LinkExtrasVenue.VenueExtrasID
AND LinkExtrasVenue.VenueID = A.VenueDetailsID
INNER JOIN CONTAINSTABLE(VenueExtras, ExtraName, 'ISABOUT ("cocktails"
WEIGHT(0.1))') AS Z
ON Y.VenueExtrasID = Z.[Key]), 0)
AS Rank
FROM VenueDetail AS A
LEFT JOIN CONTAINSTABLE(VenueDetail , VenueName, 'ISABOUT ("cocktails"
WEIGHT(0.1))') AS B
ON A.VenueDetailID = B.[Key]
WHERE A.Status > 0
) SubQuery
ORDER BY Rank DESC, VenueName
can you post the schema of all related tables?
Is it possible to consolidate VenueExtras and VenueDetails into a single
table?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
news:esp%234lheEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've currently got three tables, with the second being a link table to
> establish a many-to-many relationship, and I'm trying to do a full-text
> search. I want it to bring up instances in the first table (only one row
of
> the left table per search) where the text is found, and increase the rank
of
> the search results, where the search string occurs in the related table.
> I'm currently doing a subquery that adds the sum of the rank of the linked
> tables to create a total rank (example below), but I'm concerned that
since
> the two full-text queries aren't linked, the two ranks aren't relevant to
> each other.
> Has anyone else found a good/better way to do this?
> Regards,
> Dunc
> --/ snip /--
> SELECT * FROM (
> SELECT VenueDetailID, VenueName, Addr1, Addr3, Addr4,
> ISNULL(B.RANK, 0) + ISNULL(
> (SELECT SUM(Rank)
> FROM VenueExtras Y
> INNER JOIN LinkExtrasVenue
> ON Y.VenueExtrasID = LinkExtrasVenue.VenueExtrasID
> AND LinkExtrasVenue.VenueID = A.VenueDetailsID
> INNER JOIN CONTAINSTABLE(VenueExtras, ExtraName, 'ISABOUT ("cocktails"
> WEIGHT(0.1))') AS Z
> ON Y.VenueExtrasID = Z.[Key]), 0)
> AS Rank
> FROM VenueDetail AS A
> LEFT JOIN CONTAINSTABLE(VenueDetail , VenueName, 'ISABOUT ("cocktails"
> WEIGHT(0.1))') AS B
> ON A.VenueDetailID = B.[Key]
> WHERE A.Status > 0
> ) SubQuery
> ORDER BY Rank DESC, VenueName
>
|||In the name of keeping things a little secure, I've changed the names to
protect the innocent (table and field names, that is) on the example. I've
actually normalised them out of being one table (it used to contain a bitmap
field that related to each extra (which used to be stored in a global
array)) so I can search on them with a ranking; also there may eventually be
as many as 100 different extras.
In the example, I've simplified the scheme to the following:
Table: VenueDetails
VenueDetailsID
VenueName
Addr1
Addr2
Addr3
Addr4
Description
Table: LinkExtrasVenue
VenueID
VenueExtrasID
Table: VenueExtras
VenueExtrasID
ExtraName
A venue listing may have a name of "Bob's Cocktail Palace", which may have
extras of "Cocktails" and "Cocktails recommended". This needs to be ranked
above "Bill's Bar" which has an extra of "Cocktails", which in turn needs to
be ranked higher than "Brian's bar" which is situated on "15 Cocktail St"
Confused yet? I am!
At the risk of looking like I'm having a free plug, so you can see how it
works - check out:
http://www.fluidfoundation.com/venue...sp?BarRef=1405 - you'll notice
this one has the two cocktails flags set, whereas:
http://www.fluidfoundation.com/venue...sp?BarRef=2820 doesn't, so
*should* come second in the rankings when you search on "Cocktails". But
doesn't.
Dunc
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OOec7vheEHA.2812@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> can you post the schema of all related tables?
> Is it possible to consolidate VenueExtras and VenueDetails into a single
> table?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
> news:esp%234lheEHA.3928@.TK2MSFTNGP11.phx.gbl...
row[vbcol=seagreen]
> of
rank[vbcol=seagreen]
> of
linked[vbcol=seagreen]
> since
to
>
I've currently got three tables, with the second being a link table to
establish a many-to-many relationship, and I'm trying to do a full-text
search. I want it to bring up instances in the first table (only one row of
the left table per search) where the text is found, and increase the rank of
the search results, where the search string occurs in the related table.
I'm currently doing a subquery that adds the sum of the rank of the linked
tables to create a total rank (example below), but I'm concerned that since
the two full-text queries aren't linked, the two ranks aren't relevant to
each other.
Has anyone else found a good/better way to do this?
Regards,
Dunc
--/ snip /--
SELECT * FROM (
SELECT VenueDetailID, VenueName, Addr1, Addr3, Addr4,
ISNULL(B.RANK, 0) + ISNULL(
(SELECT SUM(Rank)
FROM VenueExtras Y
INNER JOIN LinkExtrasVenue
ON Y.VenueExtrasID = LinkExtrasVenue.VenueExtrasID
AND LinkExtrasVenue.VenueID = A.VenueDetailsID
INNER JOIN CONTAINSTABLE(VenueExtras, ExtraName, 'ISABOUT ("cocktails"
WEIGHT(0.1))') AS Z
ON Y.VenueExtrasID = Z.[Key]), 0)
AS Rank
FROM VenueDetail AS A
LEFT JOIN CONTAINSTABLE(VenueDetail , VenueName, 'ISABOUT ("cocktails"
WEIGHT(0.1))') AS B
ON A.VenueDetailID = B.[Key]
WHERE A.Status > 0
) SubQuery
ORDER BY Rank DESC, VenueName
can you post the schema of all related tables?
Is it possible to consolidate VenueExtras and VenueDetails into a single
table?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
news:esp%234lheEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've currently got three tables, with the second being a link table to
> establish a many-to-many relationship, and I'm trying to do a full-text
> search. I want it to bring up instances in the first table (only one row
of
> the left table per search) where the text is found, and increase the rank
of
> the search results, where the search string occurs in the related table.
> I'm currently doing a subquery that adds the sum of the rank of the linked
> tables to create a total rank (example below), but I'm concerned that
since
> the two full-text queries aren't linked, the two ranks aren't relevant to
> each other.
> Has anyone else found a good/better way to do this?
> Regards,
> Dunc
> --/ snip /--
> SELECT * FROM (
> SELECT VenueDetailID, VenueName, Addr1, Addr3, Addr4,
> ISNULL(B.RANK, 0) + ISNULL(
> (SELECT SUM(Rank)
> FROM VenueExtras Y
> INNER JOIN LinkExtrasVenue
> ON Y.VenueExtrasID = LinkExtrasVenue.VenueExtrasID
> AND LinkExtrasVenue.VenueID = A.VenueDetailsID
> INNER JOIN CONTAINSTABLE(VenueExtras, ExtraName, 'ISABOUT ("cocktails"
> WEIGHT(0.1))') AS Z
> ON Y.VenueExtrasID = Z.[Key]), 0)
> AS Rank
> FROM VenueDetail AS A
> LEFT JOIN CONTAINSTABLE(VenueDetail , VenueName, 'ISABOUT ("cocktails"
> WEIGHT(0.1))') AS B
> ON A.VenueDetailID = B.[Key]
> WHERE A.Status > 0
> ) SubQuery
> ORDER BY Rank DESC, VenueName
>
|||In the name of keeping things a little secure, I've changed the names to
protect the innocent (table and field names, that is) on the example. I've
actually normalised them out of being one table (it used to contain a bitmap
field that related to each extra (which used to be stored in a global
array)) so I can search on them with a ranking; also there may eventually be
as many as 100 different extras.
In the example, I've simplified the scheme to the following:
Table: VenueDetails
VenueDetailsID
VenueName
Addr1
Addr2
Addr3
Addr4
Description
Table: LinkExtrasVenue
VenueID
VenueExtrasID
Table: VenueExtras
VenueExtrasID
ExtraName
A venue listing may have a name of "Bob's Cocktail Palace", which may have
extras of "Cocktails" and "Cocktails recommended". This needs to be ranked
above "Bill's Bar" which has an extra of "Cocktails", which in turn needs to
be ranked higher than "Brian's bar" which is situated on "15 Cocktail St"
Confused yet? I am!
At the risk of looking like I'm having a free plug, so you can see how it
works - check out:
http://www.fluidfoundation.com/venue...sp?BarRef=1405 - you'll notice
this one has the two cocktails flags set, whereas:
http://www.fluidfoundation.com/venue...sp?BarRef=2820 doesn't, so
*should* come second in the rankings when you search on "Cocktails". But
doesn't.
Dunc
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OOec7vheEHA.2812@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> can you post the schema of all related tables?
> Is it possible to consolidate VenueExtras and VenueDetails into a single
> table?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
> news:esp%234lheEHA.3928@.TK2MSFTNGP11.phx.gbl...
row[vbcol=seagreen]
> of
rank[vbcol=seagreen]
> of
linked[vbcol=seagreen]
> since
to
>
Friday, March 9, 2012
Managing SQL Server remotely
I've combed through SQL Help to find the answer to my question but I think it's telling me it can't be done. I work both from an office with my servers and from home. When I'm at home I would like to access my SQL server remotely using a tool such as MS SQL Server Management Studio. But it appears there is no way to access my SQL Server for management purposes using Management Studio over a remote internet connection. I can access the server using Management Studio while I'm on the internal office network but not from home.
Has anyone been able to do this or might recommend a third party tool as robust as Management Studio?
Thanks
From what I know, either you can remote into that machine and manage your SQL Server, or if your SQL Server has a dedicated IP you can register the server on your local machine and manage the server from there.
|||I can find no place in Management Studio (linked or registered) to enter an IP number? If anyone stumbles on documentation to pull this off I'd sure appreciate it. It appears all references to 'remote' in the help files means within the same internal network.
Again, a third party tool is doable.
|||I used File > Connect Object Explorer..., and in the 'Server Name' field, typed the IP of my Database server.
|||So you have been able to connect to SQL off your network usng Management Studio. That is good news. However, I keep getting errors but I probably don't have all my ducks in a row on settings. I tried a raw IP, then raw IP with server name (67.81.4.38/ServerName) and then with the http (http://67.81.4.38/SeverName) and still no luck.
I get the feeling I need to open a port or two on the server. Some documentation sure would help. I'll keep at it but if anyone knows of some good documentation please let me know. Thanks.
TITLE: Connect to Server
----------
Cannot connect to XX.XX.XX.X.
----------
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: 2)
|||
Hi,
You may try these steps below.
1, Enable the remote connection of your SQLServer. Start->Microsoft SQL Server 2005->Configuration Tools->SQL Server Configuration Manager-> select SQL Server 2005 Network Configuration, right click on the "TCP/IP", enabled.
2, you can use ping command to check if the remote server can be accessible
3, because the default port of SqlServer is 1433. So you can use telnet command (such as telnet x.x.x.x 1433) to check if the port works. If error occurs in this step, you may
try to check the following issue:
1) If the SqlServer service is running on your remote machine
2) Check if the SQL Server port number is 1433. (If not, you may use your customerize port and retry step2 ).
3) Open your firewall, check if the 1433 port has been forbidden.
4, Try to use enterprise manager or query analyzer to connect the romote server.If the error still occurs, maybe the authentication mode on your remote SqlServer is Windows Authentication Only.You should enable the SqlServer authentication and restart yourSqlServer. (Right click on your server node, choose properties, switch to security tab, choose "SQL Server and Windows Authentication Mode")
Hope that helps. Thanks.
|||Thank you so much. I'm going to go in tomorrow and try it out. You'll probably be hearing back from me.
Subscribe to:
Posts (Atom)