Is this SSAS bug fixed in SP2 ?

Can someone with the SP2 beta installed try the following and tell us if the formatting bug has been fixed (using the provided Adventure Works DB) ?

with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000

select [Measures].[DP] on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]

On SSAS 2005 SP1, I get scientific notation for the 2nd and 3rd rows returned by the query (as in 2.2076E-05)

This is a major issue for us as we tend to run SSAS queries from SQL Server using OPENQUERY.

For instance, the following query fails because of this bug:

select convert(money,isnull(substring("[Measures].[DP]",1,50),0))

from openquery(olapserver,'with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000

select [Measures].[DP] on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]')

This bug is not fixed in SP2 CTP 1 November.

Regards

Thomas Ivarsson

|||

FYI: Results

DP
All Resellers 0.000214378
Specialty Bike Shop 2.2076E-05
Value Added Reseller 8.0309E-05
Warehouse 0.000111993

|||

I appologize, but what is exactly the bug here ?

The results seem to be absolutely correct to me...

|||

There is no bug in AS. It is merely a formatting issue. The following should work as expected.

select convert(money,convert(real,("[Measures].[DP]")))

from openquery(olap,'with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000

select [Measures].[DP] on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]')

|||

It is a bug, there is no reason why the same measure should come back sometimes as a decimal, and sometimes as a real number. The problem seems to be that it refuses to round small numbers to 0.

Try formatting this measure using FORMAT_STRING="#,#" in the MDX query, and you'll find that it still comes back in scientific notation.

|||

The Specialty Bike Shop rows should show 0.000022076 instead of 2.2076E-05

|||

Sorry - but I disagree with you. There is a difference between cell properties VALUE and FORMATTED_VALUE. VALUE contains just, well, the value of the cell. The data type for it is VARIANT in OLEDB, and there is no formatting involved. FORMATTED_VALUE is a string which represents visual formatting of the VALUE. If you don't specify FORMAT_STRING, then the default FORMAT_STRING is used - "Standard". The formatting is actually done by OLEAUT32 function VarFormat, and it decides that if there are so many leading 0's after the decimal point, it formats using scientific notation. There is nothing wrong about it. You can specify your own FORMAT_STRING of course and dictate the rules.

So the conclusion is that there is no AS bug here - and Michael solution should work for you.

Mosha.

|||

How do you explain the following:

1) Requesting 5 digits after the decimal point returns the proper formatting:

with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000, format_string="#,#.00000"

select {[Measures].[DP]} on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]

Returns:
All Resellers .00021
Specialty Bike Shop .00002
Value Added Reseller .00008
Warehouse .00011

2) Requesting no digits after the decimal point returns wrong formatting:

with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000, format_string="#,#"

select {[Measures].[DP]} on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]

Returns:
All Resellers 0.000214378
Specialty Bike Shop 2.2076E-05
Value Added Reseller 8.0309E-05
Warehouse 0.000111993

|||

This is how formatting works - for different format strings you get different results - that's the reason why there are different format strings in the first place - so people can choose how they want the results to be formated. Note, that there is nothing special here about MSAS - any Windows application which uses standard Windows formatting functionality (starting with Visual Basic 3) - is going to behave the same.

You assumption that #,# returns "wrong" formatting is not correct. This is how #,# is designed to work. If this isn't what you want - use #,#.00000 or anything else that suits you.

|||

I'd be interested to know how you come to the conclusion that #,# should return scientific notation.

Please refer to VB Language Reference at the following link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafmtuserdefinednumericformats.asp

If I wanted scientific notation, I would specify 0.00E+00

I specified #,# in the format string, and this didn't work for 2 of the 4 returned values. Did you really look at the 4 rows returned by the query I gave as an example?

|||

Since the #,# doesn't specify what happens after the digital points - the OLEAUT32 decides to use scientific notation if there are more or equal than 4 leading zeros, and not scientific notation if there are less than 4 leading zeros. I thought I mentioned that already in one of the earlier replies. Using 0.00E+00 will force scientific notation always.

You can easily test this by using

with

member measures.x1 as 0.0001, format_string='#'

member measures.x2 as 0.00001, format_string='#'

select {x1,x2} on 0

from [Adventure Works]

HTH,

Mosha (http://www.mosha.com/msolap)

|||

I can see your point, but I don't agree with your interpretation that #,# doesn't specify what happens to the digital point.

#,# specifies that there is NO decimal point, and that all values less that 0.5 should be rounded to 0. I have tried the following in VBA (Excel 2003): MsgBox ("*" & Format(0.00000006, "#,#") & "*"), and got "**" as a result, which means the format returned an empty string.

If I try MsgBox ("*" & Format(0.00000006, "#,0") & "*"), I get "*0*". Under no circumstances does the format decide on its own to return scientific notation.

I'm not in the office right now, so I cannot try the MDX query with "#,0" which IMO should return "0" for all 4 rows.

Is this SSAS bug fixed in SP2 ?

Can someone with the SP2 beta installed try the following and tell us if the formatting bug has been fixed (using the provided Adventure Works DB) ?

with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000

select [Measures].[DP] on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]

On SSAS 2005 SP1, I get scientific notation for the 2nd and 3rd rows returned by the query (as in 2.2076E-05)

This is a major issue for us as we tend to run SSAS queries from SQL Server using OPENQUERY.

For instance, the following query fails because of this bug:

select convert(money,isnull(substring("[Measures].[DP]",1,50),0))

from openquery(olapserver,'with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000

select [Measures].[DP] on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]')

This bug is not fixed in SP2 CTP 1 November.

Regards

Thomas Ivarsson

|||

FYI: Results

DP
All Resellers 0.000214378
Specialty Bike Shop 2.2076E-05
Value Added Reseller 8.0309E-05
Warehouse 0.000111993

|||

I appologize, but what is exactly the bug here ?

The results seem to be absolutely correct to me...

|||

There is no bug in AS. It is merely a formatting issue. The following should work as expected.

select convert(money,convert(real,("[Measures].[DP]")))

from openquery(olap,'with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000

select [Measures].[DP] on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]')

|||

It is a bug, there is no reason why the same measure should come back sometimes as a decimal, and sometimes as a real number. The problem seems to be that it refuses to round small numbers to 0.

Try formatting this measure using FORMAT_STRING="#,#" in the MDX query, and you'll find that it still comes back in scientific notation.

|||

The Specialty Bike Shop rows should show 0.000022076 instead of 2.2076E-05

|||

Sorry - but I disagree with you. There is a difference between cell properties VALUE and FORMATTED_VALUE. VALUE contains just, well, the value of the cell. The data type for it is VARIANT in OLEDB, and there is no formatting involved. FORMATTED_VALUE is a string which represents visual formatting of the VALUE. If you don't specify FORMAT_STRING, then the default FORMAT_STRING is used - "Standard". The formatting is actually done by OLEAUT32 function VarFormat, and it decides that if there are so many leading 0's after the decimal point, it formats using scientific notation. There is nothing wrong about it. You can specify your own FORMAT_STRING of course and dictate the rules.

So the conclusion is that there is no AS bug here - and Michael solution should work for you.

Mosha.

|||

How do you explain the following:

1) Requesting 5 digits after the decimal point returns the proper formatting:

with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000, format_string="#,#.00000"

select {[Measures].[DP]} on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]

Returns:
All Resellers .00021
Specialty Bike Shop .00002
Value Added Reseller .00008
Warehouse .00011

2) Requesting no digits after the decimal point returns wrong formatting:

with member [Measures].[DP] as [Measures].[Reseller Order Quantity] / 1000000000, format_string="#,#"

select {[Measures].[DP]} on 0,

[Reseller].[Business Type].members on 1

from [Adventure Works]

Returns:
All Resellers 0.000214378
Specialty Bike Shop 2.2076E-05
Value Added Reseller 8.0309E-05
Warehouse 0.000111993

|||

This is how formatting works - for different format strings you get different results - that's the reason why there are different format strings in the first place - so people can choose how they want the results to be formated. Note, that there is nothing special here about MSAS - any Windows application which uses standard Windows formatting functionality (starting with Visual Basic 3) - is going to behave the same.

You assumption that #,# returns "wrong" formatting is not correct. This is how #,# is designed to work. If this isn't what you want - use #,#.00000 or anything else that suits you.

|||

I'd be interested to know how you come to the conclusion that #,# should return scientific notation.

Please refer to VB Language Reference at the following link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafmtuserdefinednumericformats.asp

If I wanted scientific notation, I would specify 0.00E+00

I specified #,# in the format string, and this didn't work for 2 of the 4 returned values. Did you really look at the 4 rows returned by the query I gave as an example?

|||

Since the #,# doesn't specify what happens after the digital points - the OLEAUT32 decides to use scientific notation if there are more or equal than 4 leading zeros, and not scientific notation if there are less than 4 leading zeros. I thought I mentioned that already in one of the earlier replies. Using 0.00E+00 will force scientific notation always.

You can easily test this by using

with

member measures.x1 as 0.0001, format_string='#'

member measures.x2 as 0.00001, format_string='#'

select {x1,x2} on 0

from [Adventure Works]

HTH,

Mosha (http://www.mosha.com/msolap)

|||

I can see your point, but I don't agree with your interpretation that #,# doesn't specify what happens to the digital point.

#,# specifies that there is NO decimal point, and that all values less that 0.5 should be rounded to 0. I have tried the following in VBA (Excel 2003): MsgBox ("*" & Format(0.00000006, "#,#") & "*"), and got "**" as a result, which means the format returned an empty string.

If I try MsgBox ("*" & Format(0.00000006, "#,0") & "*"), I get "*0*". Under no circumstances does the format decide on its own to return scientific notation.

I'm not in the office right now, so I cannot try the MDX query with "#,0" which IMO should return "0" for all 4 rows.

Is this some structural problem?

Hello-
We were on SQL 7.0 / Windows NT and have moved to SQL 2000 sp4a / Windows
2003. Since upgrade, we have seen some strange behavior.
Following are some of the observations so far:
1. We have witnessed exceedingly large database restoration timings. With
SQL 7.0, it used to take around 1.5-2.0 hours to restore database sized 125+
GB (with around 70 GB of data... we had some fragmentation problems!). Now,
the whole process takes from 4 to 9 hours. The restoration times are very
large when restored the first time (true, I am trying to create a database as
well).
2. We are using a vertical solution that used to have high level of
fragmentation. The fragmentation used to be so high that it used to increase
data device to 122+ GB with actual data of ~ 65+ GB. This forces me to
rebuild indexes and run the maintenance DBCC on every weekend. Since
upgrading to SQL 2000, this problem has reduced a lot. But, the maintenance
job timings since upgrade have also increased by 50% (it used to take ~ 8
hours and now the job is taking ~ 12 hours).
3. On at least two occasions (the latest on last Friday), the database has
generated exceedingly large transaction log backups. True, the application
system administrator has told me there was a runaway process that might have
created exceedingly large logs. But, the backups were so huge (+61 GB) that
it filled up the whole backup drive resulting in failures for other
transaction log backups till I cleaned the drive and took a full backup. The
normal hourly transaction log backups have total of size of 600+ MB (624 MB
as of now).
My question is: Am I seeing some kind of structural problems and has anyone
encountered similar problems?
Please share your experiences and expertise.
--
Regards,
MZeeshanHello Mzeeshan,
Before going any further, I'd like to confirm if the current transaction
log backup is larger than before and if you are restoring from the
transaction log backup. If yes, it is normal that it take more time to
restore.
The reason why the log backup is larger than before is that you run DBCC
command to rebuild indexes every week. Reindexing can cause the transaction
log grow much.
Because of the changes in the recovery model in SQL Server 2000, when you
use the Full recovery mode and you run DBCC DBREINDEX, the transaction log
may expand significantly more compared to that of SQL Server 7.0 in an
equivalent recovery mode with the use of SELECT INTO or BULK COPY and with
"Trunc. Log on chkpt." off.
Although the size of the transaction log after the DBREINDEX operation
might be an issue, this approach provides better log restore performance.
The following article has addressed the similar issue:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
If you only rely on full database backups (not transaction log backups),
the recovery model can be changed to Simple to have a smaller transaction
log file. To do this, please follow these steps:
1. Start SQL Server Enterprise Manager (SEM).
2. Expand a server group, and then expand a server.
3. Expand Databases, right-click the database to modify, and then click
Properties.
4. Click the Options tab.
5. Select "Simple" from the Model list under the Recovery section.
6. Click OK.
You can also run the following command to set the recovery model to Simple:
ALTER DATABASE database_name SET RECOVERY Simple
Note: Replace database_name with your database's name
Note: If you set the recovery option of the database to SIMPLE, the
transaction log is set to be truncated at every checkpoint and thus
prevents the log from filling up. However, with the Simple Recovery model,
the database can be recovered only to the point of the last database backup
and not to the last transaction and you cannot backup the transaction log
since the sequence of transaction logs is not being maintained. Also, you
cannot restore the database to the point of failure or to a specific point
in time. To do that, you need to use the Full Recovery or Bulk-Logged
Recovery model.
After that, run the "dbcc shrinkfile" command against the transaction log
to shrink the log file.
For more information on shrinking the transaction Log, please refer to the
following article:
272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
<http://support.microsoft.com/?id=272318>
Please understand that the latest SQL Server 2000 Service Pack is Microsoft
SQL Server 2000 Service Pack 3a instead of SQL 2000 SP4a. Please clarify
the Service Pack you installed.
In addition, I want to let you know that performance issues can be caused
by various factors, and it is difficult to locate the root cause in a
newsgroup thread. If the issue still exists after you have used the
troubleshooting steps above, to efficiently troubleshoot a performance
issue, we recommend that you contact Microsoft Product Support Services and
open a support incident and work with a dedicated Support Professional.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
I hope above information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/l...an2.asp?ad_id=7
http://brettatkin.com/clients/tgc/l...ean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><br>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%><br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><br>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%><br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br>
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><br>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><br>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%><br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><br>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%><br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br>
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><br>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
BrettIf the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/listings_edit_clean2.asp?ad_id=7
http://brettatkin.com/clients/tgc/listings_edit_clean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").V alue)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value )%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br >
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><b r>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%>< br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><b r>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)% ><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> <br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value )%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").V alue)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value )%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br >
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value )%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><b r>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value )%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value )%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br >
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").V alue)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><b r>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%>< br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><b r>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)% ><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> <br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value )%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value )%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br >
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value )%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><b r>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value )%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%><b r>
Submit:<%=(rs_listing.Fields.Item("submit_date").V alue)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
Brett
If the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/listings_edit_clean2.asp?ad_id=7
http://brettatkin.com/clients/tgc/listings_edit_clean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><br>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%><br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><br>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%><br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br>
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><br>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/tgc.asp" -->
<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%><br>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%><br>
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%><br>
Username: <%=(rs_listing.Fields.Item("user_name").Value)%><br>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%><br>
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%><br>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%><br>
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%><br>
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%><br>
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%><br>
Payment Methods:<br>
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%><br>
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%><br>
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%><br>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%><br>
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%><br>
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
BrettIf the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Is this query possible?

Hi folks.

So I have a table which stores the following information:

a recordID (PK)
a regionID
a month (in text; such as "January 2005")
and other fields which probrably aren't important to know for this question.

The table can have more than one record for each region and month in question. The problem is for the report I want to pull up ONLY the last record for each region for the month. I got around this by doing a seperate query for each region (5 total). Now I have to do the totals for all regions combined. I could do this math using the array but I was wondering if I was able to do a query to do it for me.

So, I want to query the database and retreive 1 record. That record would contain aggragate data from each of the regions (the last record for each region and the month in question).

I might not be asking the right question, which aludes that I might not understand the problem or capabilities of SQL (newbie).

Thanks,
Douglas.If I am following you correctly, I'd try something like this:

SELECT
 myTable.regionID,
 myTable.month
FROM
 myTable
INNER JOIN
 (
 SELECT
 regionID,
 max(recordID) AS recordID
 FROM
 myTable
 GROUP BY
 regionID
 ) AS SQ ON myTable.recordID = SQ.recordID

Terri|||That was perfect. Thank you.
I forgot to say thanks. Actually, I didn't even know that was an option. I use this sort of stuff a ton now.
Douglas.

Is this possible?

Hello,

My question relates to the following select statement:

Select Report_description from Report where Report_name = (grab this value from the item selected from a listbox)

I wonder whether it would be possible to make the above statement a stored procedure but instead of filling in the last value in the bracket, I would like to grab that value from else where, for example from an item from a listbox which has been selected by the user.

Hi is Dude

we use this n number of time. The thing you need to do is, just create the comma separated value of the selected item list in the front end.

As an Example

list selected values as

'i','am','a',boy' (you need to do this in the front end itself)

in query do like this

Select Report_description from Report where Report_name in('i','am','a',boy')

you need to use the IN operator to select the selected values for the Report table

Regards,

Thanks.

Gurpreet S. Gill

|||

Hi Gill,

Its great to know that this can be done. Unfortunately I am a newbie to all this. Could you please elaborate? For example if I was using ASP.NET, and I suppose alll this code would go into the code behind file of the list box control? So what would the code actually look like? And would I just leave the last value in the stored procedure as a blank space?

Thank you so much

|||

I cant say much about the ASP.NET, but this code works for me.

here the ListBox1 is the List box from where you want to collect the values, CSV is string variable, used in IN clause of SQL

Try this

Dim CSV As String, SQL As String, i As Integer

CSV = ""

'Loop to all the Items in the ListBox1

For i = 0 To ListBox1.Items.Count - 1

'Check if selected or not

If ListBox1.Items(i).Selected Then

' if selected, make the comma separated value single Quote around it

CSV = CSV & "'" & ListBox1.Items(i).Text & "' , "

End If

Next

' Ignore the last extra comma

CSV = Left(CSV, Len(CSV) - 3)

' Create the SQL command

SQL = "Select Report_description from Report where Report_name IN( " & CSV & " )"

' Your codes goes here

' Use the SQL variable to execute the query

'

Kiind Regards,

Gurpreet S. Gill

|||

ohhhh, PLEASE IGNORE THIS post twice same

Dim CSV As String, SQL As String, i As Integer

CSV = ""

'Loop to all the Items in the ListBox1

For i = 0 To ListBox1.Items.Count - 1

'Check if selected or not

If ListBox1.Items(i).Selected Then

' if selected, make the comma separated value single Quote around it

CSV = CSV & "'" & ListBox1.Items(i).Text & "' , "

End If

Next

' Ignore the last extra comma

CSV = Left(CSV, Len(CSV) - 3)

' Create the SQL command

SQL = "Select Report_description from Report where Report_name IN( " & CSV & " )"

' Your codes goes here

' Use the SQL variable to execute the query

'

Kind Regards,

Gurpreet S. GIll

|||thank you very much gill!!!

Is this indication of Deadlock Occurs?

Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec0xA9CA60C0) Value:0x802d1c0c
Cost0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
Johan
Looks like deadlock did you look through the profile who the culprit
SQL is
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:

> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec0xA9CA60C0) Value:0x802d1c0c
> Cost0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>
|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:

> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>
|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
Your transaction (process ID #52) was deadlocked on {lock | communication
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
Basically I need some confirmation, LOG entry below:
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec0xA9CA60C0)
Value:0x802d1c0c
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:

> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>
sql

Is this indication of Deadlock Occurs?

Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec:(0xA9CA60C0) Value:0x802d1c0c
Cost:(0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
JohanLooks like deadlock did you look through the profile who the culprit
SQL is
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:
> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec:(0xA9CA60C0) Value:0x802d1c0c
> Cost:(0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:
> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
--
Your transaction (process ID #52) was deadlocked on {lock | communication
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
--
Basically I need some confirmation, LOG entry below:
--
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec:(0xA9CA60C0)
Value:0x802d1c0c
--
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:
> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>

Is this indication of Deadlock Occurs?

Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec0xA9CA60C0) Value:0x802d1c0c
Cost0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
JohanLooks like deadlock did you look through the profile who the culprit
SQL is
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:

> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec0xA9CA60C0) Value:0x802d1c0c
> Cost0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:

> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
--
Your transaction (process ID #52) was deadlocked on {lock | communicati
on
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
--
Basically I need some confirmation, LOG entry below:
--
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec0xA9CA60C0)
Value:0x802d1c0c
--
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:

> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>

Is this how a trigger is used?

I was following this article:
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931&Product=3D= sql
and got it working nicely. I would like to elaborate on this. Rather = then sending a succeed registration page to the client I would like to = send them a page telling them an email has been sent to their e-mail = address. That part of it I can do. My trouble is once I generate a = password for the client I need to let SQL 2000 SP3 know it's time to = send them an e-mail. Is this a trigger? Can anyone suggest what = applications I might need to do this (except Exchange) and some = guidelines on how this can be done? Thanks.
I cannot use Exchange for this as I have Outlook 2003 installed and it = is not supported on the same server where Exchange 2003 is installed. I = only have the one Server Windows 2000 SP3.
-- George Hester
__________________________________Assuming you are using a stored procedure to generate the password, you can
send an e-mail from the stored procedure (I really don't recommend doing
this in a trigger). You can see some information about sending e-mail from
SQL Server at http://www.aspfaq.com/2403
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
I was following this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;247931&Product=sql
and got it working nicely. I would like to elaborate on this. Rather then
sending a succeed registration page to the client I would like to send them
a page telling them an email has been sent to their e-mail address. That
part of it I can do. My trouble is once I generate a password for the
client I need to let SQL 2000 SP3 know it's time to send them an e-mail. Is
this a trigger? Can anyone suggest what applications I might need to do
this (except Exchange) and some guidelines on how this can be done? Thanks.
I cannot use Exchange for this as I have Outlook 2003 installed and it is
not supported on the same server where Exchange 2003 is installed. I only
have the one Server Windows 2000 SP3.
--
George Hester
__________________________________|||Hi Aaron:
Can you believe it? I got the SQL Mail setup and it seems to be =working. Using my ISPs SMTP server. I did the test and it connected to =the MAPI profile successfully. Anyway I proceeded to use the extended =stored procedure xp_sendmail in Query Analyzer:
xp_sendmail @.recipients =3D 'hesterloli@.hotmail.com',
@.message =3D 'Hello',
@.subject =3D 'From SQL Server 2000'
Actually I sent one to that address and one to my POP3 account. Both =successfully as reported by Query Analyzer.
But I forgot to have Outlook 2003 open before I did that. So to see if =I got the mail I went to open Outlook 2003. Know what happened? =Outlook 2003 could not open. I use MAPI profiles and when I tried to =start Outlook 2003 the Error message I got was, "The service could not =be started." No offer to start in safe mode. Just the error message =box. I post it next time if I can replicate the issue again.
All I know is that it sounds like some dll went belly-up. I rebooted =and Outlook 2003 was fine and there were the two e-mails from SQL in =Outlook 2003 and Outlook Express which handles my Hotmail account.
Now I don't know what to do. I could try the xp_sendmail again with =Outlook 2003 open and see if that avoids the issue. But I just don't =know. Ever heard of this before?
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> Assuming you are using a stored procedure to generate the password, =you can
> send an e-mail from the stored procedure (I really don't recommend =doing
> this in a trigger). You can see some information about sending e-mail =from
> SQL Server at http://www.aspfaq.com/2403
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> > > > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I was following this article:
> > =http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931&Product=3D=
sql
> > and got it working nicely. I would like to elaborate on this. Rather =then
> sending a succeed registration page to the client I would like to send =them
> a page telling them an email has been sent to their e-mail address. =That
> part of it I can do. My trouble is once I generate a password for the
> client I need to let SQL 2000 SP3 know it's time to send them an =e-mail. Is
> this a trigger? Can anyone suggest what applications I might need to =do
> this (except Exchange) and some guidelines on how this can be done? =Thanks.
> > I cannot use Exchange for this as I have Outlook 2003 installed and it =is
> not supported on the same server where Exchange 2003 is installed. I =only
> have the one Server Windows 2000 SP3.
> > -- > George Hester
> __________________________________
> >|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C3C2BA.A0DC3EC0
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0010_01C3C2BA.A0DDC560"
--=_NextPart_001_0010_01C3C2BA.A0DDC560
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
That's the strangest thing. I use xp_sendmail in Query Analyzer send =the mail to myself at my ISP. And the mail goes into my Inbox. Yes =that's right. Not my Outbox. But my Inbox. I saw an unsent mail there =so deleted it. Tried xp_sendmail again and sure enough there it was =ahgain in my Inbox. But as I was writng this it flew out of my Inbox =for destinations unknown. I suspect it will come back to me. Oh and =Outlook 2003 seems to have survived. I need to close it down and =re-open it to be sure...Nope it is dead dead dead. No error this time =but it won't start. Ah there it was in Task Manager. Let me end the =process and try again... well here's the error:
Got to reboot again. See ya...
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> Assuming you are using a stored procedure to generate the password, =you can
> send an e-mail from the stored procedure (I really don't recommend =doing
> this in a trigger). You can see some information about sending e-mail =from
> SQL Server at http://www.aspfaq.com/2403
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> > > > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I was following this article:
> > =http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931&Product=3D=
sql
> > and got it working nicely. I would like to elaborate on this. Rather =then
> sending a succeed registration page to the client I would like to send =them
> a page telling them an email has been sent to their e-mail address. =That
> part of it I can do. My trouble is once I generate a password for the
> client I need to let SQL 2000 SP3 know it's time to send them an =e-mail. Is
> this a trigger? Can anyone suggest what applications I might need to =do
> this (except Exchange) and some guidelines on how this can be done? =Thanks.
> > I cannot use Exchange for this as I have Outlook 2003 installed and it =is
> not supported on the same server where Exchange 2003 is installed. I =only
> have the one Server Windows 2000 SP3.
> > -- > George Hester
> __________________________________
> >
--=_NextPart_001_0010_01C3C2BA.A0DDC560
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

That's the strangest thing. I use =xp_sendmail in Query Analyzer send the mail to myself at my ISP. And the mail =goes into my Inbox. Yes that's right. Not my Outbox. But my =Inbox. I saw an unsent mail there so deleted it. Tried =xp_sendmail again and sure enough there it was ahgain in my Inbox. But as I =was writng this it flew out of my Inbox for destinations unknown. I suspect =it will come back to me. Oh and Outlook 2003 seems to have survived. =I need to close it down and re-open it to be sure...Nope it is dead dead =dead. No error this time but it won't start. Ah there it was in Task =Manager. Let me end the process and try again... well here's the =error:
Got to reboot again. See =ya...
-- George Hester__________________________________
"Aaron Bertrand [MVP]" wrote in message news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...> =Assuming you are using a stored procedure to generate the password, you can> send =an e-mail from the stored procedure (I really don't recommend doing> =this in a trigger). You can see some information about sending e-mail =from> SQL Server at > > -- > Aaron Bertrand> SQL Server MVP> => > > > > "George Hester" wrote in message> news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...> I was following this article:> > =http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931=&Product=3Dsql> > and got it working nicely. I =would like to elaborate on this. Rather then> sending a succeed =registration page to the client I would like to send them> a page telling them an =email has been sent to their e-mail address. That> part of it I can =do. My trouble is once I generate a password for the> client I need =to let SQL 2000 SP3 know it's time to send them an e-mail. Is> =this a trigger? Can anyone suggest what applications I might need to =do> this (except Exchange) and some guidelines on how this can be =done? Thanks.> > I cannot use Exchange for this as I have =Outlook 2003 installed and it is> not supported on the same server where =Exchange 2003 is installed. I only> have the one Server Windows 2000 =SP3.> > -- > George Hester> __________________________________> > =

---

--=_NextPart_001_0010_01C3C2BA.A0DDC560--
--=_NextPart_000_000F_01C3C2BA.A0DC3EC0
Content-Type: image/gif;
name="outlookerror07.gif"
Content-Transfer-Encoding: base64
Content-ID: <000a01c3c2e4$89a91f00$c673c318@.hesterloli.com>
R0lGODlh7AF+AAAAACwAAAAA7AF+AIYAAABAQEABBHwAAX4AAn4AAH8AAIABA30AA34BBH0BBXwB
BnsBBnwCCHoBB3sCB3sCCHsDC3gDDHgEDnYDDXcDDXgDDncEDncED3YEEHUEEHYCCXkCCXoCCnkD
CnkDC3kGGHAGGXAHGXAHGm8HGnAHG24HHG4IHG4HG28IH2wIIGwIHW0IHW4IHm0IH20JIGsJIGwE
EXQEEXUFEnQFE3MFE3QFFHMFFXIGFXIGFnEGFnIFFXMGF3EGGHEJImoJIWsJImsJI2oKJGqAAACA
gIAAAP8A/wD/AAD//wDU0Mj///8AUkAAAAAAAABSQAAAAAAAAAAAAEBAQEBAQEBAQEBACgAAAAAA
AAAAAFJAAAAAAAAAUkAAAAAAAAAAAABAQEBAQEBAQEBAQAoAAAAAAAAAAABSQAAAAAAAAFJAAAAA
AAAAAAAAQEBAQEBAQEBAQEAKAAAAAAAAAAAAUkAAAAAAAABSQAAAAAAAAAAAAEBAQEBAQEBAQEBA
CgAAAAAAAAAH/4BJgoOEhYaHiImKi4yNjo+QkZKTlJWWl5iZmpucnZ6aAUlKo6SlpqeoqaqrrK2u
r7CxsrO0tba3uLm6u7y9vr+5RKFKn8XGx8jJysvMzc7P0IfCoklC1tfY2dhB3N3e3+Dh4t4w5ebm
Kenq6+zt7u/rJvLz9CX29/j5+vYk/f7/AAMG5EGwoMGDCBMm3MGDocOGEB3SmEijhsWLGDNq3Mix
44WPIEOKFGmhpMmTKEtWWMmyJcsIMGPKnEmzps2bMSFEeMCzp8+fQIMKHfrzgNGjSJMqXWrUwAGn
UJ9KjQrVgNWrWLNmTTKNmLav2saJDXKurNly6ciehQGvrdt0KP/iyo1Lr64JfgLz/iuhdyAJHn8D
Ax6ssLBhhDsSJ66xg7HjxjV4UKzYsbJlixdqfNScuYKGCppDZx4teuSFlKhPuly9EqdrmhBiy55N
VDbR27h7HnhggLfv3sAfMB1OvKnW41mfIl9uleuwamCjCxlLvTq3FEFSrH3LPQUK7+C/iw9vd969
8vL28ePbF+BgwYIPF1RMv779+/gXT77sUbT/zKEFmJkGBFZg2oEhpaYga6u95iBMOkUYgYQUTmih
bbnhttuGwnXYYW8I+BYiiMQBR9VUKJ6oYorKWQVLAM11BV10ANQIwDbWfbPWjtjtyFZbMIwnZHjv
xOXCXOilx17/ewEtySR87+0Qn3wMyYdYfvRFth9/XGZEGmkEEvjfmKYpmBqDLtFUAQRrtlnhmxPO
JuecdGZop1C7iahnb0z91hSLJ7bInFbKEcOIMDE+Z82N2tjoqDU5ggNpdTBkZxYpbqHlHQyYYpoC
CZ+Gh0KQR5JX3nnolTBKPiQ4+c8oAr0HmA4k0GolQTvogOWuvDZGUZcaDRjmsJiNCVqBCCZrJmpo
tlRTBRG0+YEHHlxoLZzYXhvnnT2F6O0DI/7mW54g+qYnAiXy1pS4wAHqropXGboIogY4R801N9Yo
RL5HHAGAEUYwGsQo3xBMMDg+npWdWuaMUs4o7piSDsQpUEzx/zqjonBkkHTJY8pdrpIAa6sj+8NX
yCIr4dd7tbb8l5U6xCzzzDQf1Gtjkv0KLJcakFlDmD5/WeayKDX7kpoQfFDBB0xf6OaaF9IpdZ3c
PgABAldffVtv4CLg7bd89tnuioEOilyhg9RIiNrOJXqvEEfgi2+//wYMKSnd4C3WpGNVip12DSvx
sBLtXAyxxYRjHB4LjDfOuHcmjDLPqvbUtSrJSuDT6uYBlfyPrC3TaiuVNJc+88321aDzzsUOS2yx
X/6MbLIHEl200dA+y/TuH2jLJoVPZ0shBFWDG5vx3ybvU7led+3tcMFFzy6g6pI9VbxrAyAI223X
K+M1cfcL9/++Q9ho93SlDIz3wek7LDinpAQOsfoOwy94KesYTninhydeCgsoKIV4PKaEyWVuZKsq
hapUlrLLNZCBCGQg6ETXsluZ7oI265WWVse6yvQsdsISEwj/MxLbqQZ3SONd0yLkpmtN7YUY4lbW
sKa8b/GEa177GrjGJbbqvUsqgjLbVdCWPe51z15e2Rf48OUofaGPfkpQnxQPxo32RXGKVyRL/dz3
voupQ38V858Yw6iOAI4RVGY04AJLELkDZg5za1xjBFNGR3+wLHQVJN0FS4e6+qhuIh1snevEBDv/
hMlAtBuaCS2Au9zNpAIeUGHvgPc731nyTcSrGtZ4QsMdKq//JyBKAAISIMrmoYspY2OXn6zHSkHJ
K23aIwS9kDijfOVrX0UoAgCQgASBXZF9UTRYFqVYKYdh0ZhbvF8XE5c/ZvZvYmP04ilSYAIUtFGN
sJJjAeGYzQRCUGWl+Bx8KDg6+eyxdBnk1QYBGUgP+gxoIxRaCRfJSBTSJJIqdBq2YAjD4s1QazUE
JSdJSVBR8nApq/yhU4IoxKq8MgmOkiWMvKcoIRRBbovK5S57eTdwCFOKeYtiMUVKMPsFc5nwG1zh
nLm/MZIRYmi82HdIQEBsvtGbJ8NpyhbYD1iFU5yA6Qc5X3aYXJ3zdH1UzB8p08EQuu4iIzxkIhVp
wkbaRJKT/5xQ8PTZQuFtS5OZ7GRABTrKghr0lEuRnlp9SBW2vgt7sNxeLI/4PWtcNJcW3ZcBbMTR
JxbspCCtokjVN1KyCJawKU3mSr/YUmg6lmLfGUVcQmVAm3Jzm9q8bE/ByVk7jhOPFjyqzNK5q3Uy
tZ0b+eCYiBXP0pCEnlalCVa1Kjx+vtCfNASoJ3d4w4GaNVzDSWgrGSpEIkJ0rscVxCzryqi8LqqJ
vvQoYKFITC3izaQLK8Xg0OIpxnoKcY4NI8TMiCmaEpAUIDMFX0ixxgey56dz7Mcdh2olo4pWV0nV
DzubKrtBQnW1s5tqgmBrT9lKkrZb9Wpti7dJ5O3Whr0ta/9Bn4fK6qlybAp9q4sasdyKKlFft8SG
wCJFDsNWihuVOjGKF8aj7pTxxUX6jjpAlSSQac4ErVrP5jSHsrxMELQwu68OSIsl06K2I6qNHTyD
xpl5LjK2M5mtgm07NdzO8MG8NZeECQrcHrYyKsQ1m3EV0eG3iVg6fCOxiU2cYob5zVLb4c54wAMX
8IDqxXMJlTVPhZe5oKAfJ+McoJ8EVH/QV4/3za9+T7szp77unQEW8EfoWU+jXfXAv0swgis5ZQaH
VbefjLBZzwq9C09PRW4lW7yIwOpWu9rVE6UlmsGiZhQLoc24NuyKexRnIGVMO7/+dZ3Fo7HJojFJ
9vCzXHD/HOiT6ZjQP6ZgaO9L5PwY+ciphbQImUxV20FZJlK+ZJyoTDUZ5jagIeotuEbdZYRieLgN
TY5VAkDvetv73rGu66yzUWtb5xrX5VgY4M7ylnJkjC0HD3YZWRAkjpnKLvxQtlx2LOge62W+QC6q
kPGb36W209FAKySYIi1pSn8b3JgOHtQUfMlMcqvBYsUyWdkNrlJDxdRfVmi8tSLrffO73zC49Zpz
bVheEzxTv274qFwQJDp/x3GjAmDHTlVNiaOg2TsONLQ/K+0gU9sgN7s2tjOSZENuu7W1I7ClDZzP
KZN7TlaOeahxSPOwuTvV7grzoPQ+KFpG4++AD7zgB0/4/8Ib/vDKfQ7iF8/4xjv+8ZCP/CZkJPnK
W/7ymM+85otB+c17/vOgD73oG9/50Zv+9KhPveopUfrVu/71sI/95lsv+9rb/va4hwbtc8/73vv+
95HYPfCHT/zi5174xk++8pcveuQz//nQjz7pFS/96lv/+oJ3fiOGwP3ucx/74A+/+Aeh/UR0v1/o
R3/3x8/+9i+//IcYQvrnP/8huF8RRmwE2/LvCeQmwv+Z4H8AKHgDGID6twkFSAkJ2H8LmHjUAAnc
R38SqH72h4A2YoD4d4CFwD1q04CQMFcFeIHbkzYk+IH8V4IQpYH/tzYqOIIruAwgaIIJGIOM4IEo
iAg2+P9/EZWDi+CBOQh/gyB/EziE/VKBmMCDjtCANgiAHIiEKjiANEiDkiCFG3iDL3iFKViDVYiF
yECFPWiFKLiECHiEYDgJPugIQJgEQkiERGiElQCF+yeCESVXsSSCsKQ9crh/KWiHe9iHddiBWWiH
efiHeGhEAnhchYiHdLiILMiCMXiBhHiHLhiCjiiIcaiHiLiBl2gIgwiClyiAHWiJh+iIJAiJjJiJ
gdiIdbiHohiJqZiFOHiHnsiEm4iKkmiLiZCGa8iGEzgERHAJC6iII/iIpciJjViFmEiKiiiMcuWC
fRiGsMiEyAiNyEWFh0iMw1iM2TiDpOiM2biNNyiF1aj/jc9YjuPojMx4juSYjtHYjsw4ieD4jeb4
isFIjeF4jO+IjrA4L9S3CLvYL0pAhAFZf7/4hoewg+vojpxojabojfDoh/hYiuLYjluYkPKIiIY4
jfLIjo9YjxaJkNs4h/rojdjIig25jxPZRMe4kQrJkvy3jBDpksUIk1fYkXzYhCdpjxzWj+ZHf6Qw
gT9Zf5YgjTJZlAtJkfO4hav4jhPZjCVJlBZZjvu4kvP4lArJjQlplfkIjSSZlRWpjiVZkUbJkTM5
jTCplWW5lRF5kUc5ihH5gzyJCP9oCj5ZCgQ5lF/ZklW5gikJhteIklw5lgdZgn0ZmCOJlkW5iitJ
ljJp/42GiZhJOZKReZhemZh5uZcsiZRQqJNTaZScyY8P6I91GZQAaZcEWZAK2Il66YrJdYutCZLN
aIXCmJODeIvVSItNGYi1qIm3GYe6OYv6iJuK+ZuJqJh8GJvHiYoiKYeyyY646JoY6ZSnyJxdCY9R
WJuSuZmHmX+iGFdF5JC5GJeG8I/oR5dHYJ7054v3t54GyZ6PV37kWZ6qMITq6Z72mYT3OX2h2ZND
mAptyGr5GaACWnnwyYanwIa+iJoDuqAMWngFKpDo2YsAaobaWYad4JYKSID4CXwVCp6w54TOcILS
IJ6FEJ/yeaD0OaGRgJUWygkYqoViGQ0hCJt/B6It+v8IavmiPZicg9mCysCiVNmZPYqfSPigErgK
KaqgOMqFNrqiMfqFT/oMHimkldekxnilNxqlGeijyWClWeqhUIqGJEoIJmqeEZp+CUqhrkiIa/qb
vMmI2CiS0QmnhtidbzqnymmneDqky/mStUiTbiqVrImn2GmbhKmHa0qdPOqhqpmnwHmThzqcgDqd
kBiKw6iooUiT2Mmc47iohgB/5BmhZ3oECaqkG3qVWTmci+mVjumZwXmPggmZU2qVsMqKr3qpGpmX
YWmMXjipe+mFflmZudqZstqagxmn8RiT6CiNKfmXjwCq8zeqZ1qqZmiPNgmIsbmqjVmrjPmWcqqV
sFn/nM4Zi8H5rbOIrd+okswKld1aREspi88YriYpouyql9Gpjt0Ime7aku9KjBwYjie5q1J5KGNK
pqMJlKOQnirqpK+6q6Corb/KrfZ6q1jarhMLrGs5sJo4lZ3qkOwKlhNLkbOZlqwKo28ZlUx5rBS7
lYjZr+s4rrSqsWoZnvupCKEqkAq7sB9orcJaq6s5mfq6sicbqx/Jp1HZjQNbmBD7s/WancpKllGI
pUKrtFSqtCxbmS57kTCLssRKpYigi7z4n62WoYY6p9T5nI4Kr2y6lpa4r6dom3Z6nefKqFx5nHEr
lmcrkW7JmtwpiPs6m5WKiX67kH96qHSYqFLbikI6/4cgmbV+2IQomYdwyquQALZhK6E6e39e2qCN
t7mxl4ZqeLk5a6qay7mo57mwB7pqaKK9WKqka7qwG7vIoLpcwbqnObaym7u6awy0W7sRKIHd92ou
2qW7W7zK17vKRQTe532v9rpTKJv9N5nGAJXGW73PgLzJ27zNO4ZgOrzdywyoa73iW7kF+6zay4DF
OYl9m6g4uYzY2qjwOrmROL+7Ob72O3nla3h92aHF2q3jyrSqmrLfe78EPAnYK6XWSr39y7QSq67e
aavLWsASjAkHHKKcmZEXjKqC+Zkcm475KKITHMJklr+FR7X4usDFmsHMirSEKcIuDJoP9Xjo6r64
Of+56vu+gCupbquchtq2g/vCQFwIFQx94RvERvysJDx+RXzETDzCNdvEUBzEQxzFVAy7U1zFWLyg
V5zFXHyfW9zFYHx/XxzGZCx+Y1zGaGx9Z5zGbPx8a9zGcGx8bxzHdPx7c1zHeIx7d5zHfPy5SdzH
gJx8exzIhGx6g1zIiPx5h5zIjIx5i9zIkBx5j5zF0FXJlnzJmJzJmqzJkezEMdzJawMMoqwKAbDE
bTzJWAwAn0ygpQzKX/vHiazKnicMpszGqFzFsgyOZwmRu9zLvPzLvhxLtOzKI/rExAxRhlLLXegc
yozGt0zFuax5w3zMEmXMxJzLRfzD2czM1FzNq+z/ytGsCVHrtZUwzd3sd+eMzAN8qskqztyczs8c
xeFMhpXIea0Mz7CMyNiMgRIZvebczfEMxfMMjGb5Cf9MzQHdxPvMz8Y5vAd9zAnNxAN9hP+6zsF3
z+cc0Ue80FX6zhmdz4U80azczGWs0UbM0ZKnPQ9NzCYdxNEczMAc0zA907/s0QAN0oQs0pK30q7c
0kCsytob1EI91ERd1EbN06Ds0y8MAPjW1E791FAd1VIN1SRNxkrtwpuc1Vq91Vydzujs1WANelcd
1mQtCWNd1mi9k9ac1my9eGfd1nDtzXE9126N03R91/a81ni918vw1nxd1n7912Ed2ILt1YRd2B/N
QxVHvdiM3diO/diQHdmSPdmUXdmWfdmYndmavdmc3dmPPdWgHdqiPdqkXdqmfdqondqqvdqs3dqu
/dqwHduyPdtQHQgAOw==--=_NextPart_000_000F_01C3C2BA.A0DC3EC0--|||I know that XP gets annoyed if I have two mail users at the same time on one machine (one is SQL
Server, the other is me, as interactive user), where W2K was OK with it. It seems "reasonable" that
W2KS is based on XP and exposes the same behavior. Did you consider using xp_smtp_sendmail instead?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:ONOZbMuwDHA.3116@.tk2msftngp13.phx.gbl...
Hi Aaron:
Can you believe it? I got the SQL Mail setup and it seems to be working. Using my ISPs SMTP
server. I did the test and it connected to the MAPI profile successfully. Anyway I proceeded to
use the extended stored procedure xp_sendmail in Query Analyzer:
xp_sendmail @.recipients = 'hesterloli@.hotmail.com',
@.message = 'Hello',
@.subject = 'From SQL Server 2000'
Actually I sent one to that address and one to my POP3 account. Both successfully as reported by
Query Analyzer.
But I forgot to have Outlook 2003 open before I did that. So to see if I got the mail I went to
open Outlook 2003. Know what happened? Outlook 2003 could not open. I use MAPI profiles and when
I tried to start Outlook 2003 the Error message I got was, "The service could not be started." No
offer to start in safe mode. Just the error message box. I post it next time if I can replicate
the issue again.
All I know is that it sounds like some dll went belly-up. I rebooted and Outlook 2003 was fine and
there were the two e-mails from SQL in Outlook 2003 and Outlook Express which handles my Hotmail
account.
Now I don't know what to do. I could try the xp_sendmail again with Outlook 2003 open and see if
that avoids the issue. But I just don't know. Ever heard of this before?
--
George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> Assuming you are using a stored procedure to generate the password, you can
> send an e-mail from the stored procedure (I really don't recommend doing
> this in a trigger). You can see some information about sending e-mail from
> SQL Server at http://www.aspfaq.com/2403
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I was following this article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;247931&Product=sql
> and got it working nicely. I would like to elaborate on this. Rather then
> sending a succeed registration page to the client I would like to send them
> a page telling them an email has been sent to their e-mail address. That
> part of it I can do. My trouble is once I generate a password for the
> client I need to let SQL 2000 SP3 know it's time to send them an e-mail. Is
> this a trigger? Can anyone suggest what applications I might need to do
> this (except Exchange) and some guidelines on how this can be done? Thanks.
> I cannot use Exchange for this as I have Outlook 2003 installed and it is
> not supported on the same server where Exchange 2003 is installed. I only
> have the one Server Windows 2000 SP3.
> --
> George Hester
> __________________________________
>|||No I didn't have to reboot. I just had to shut down the services =mssqlserver and sqlserveragent. Then Outllook 2003 fired up and I =restarted the services. Another bug? Looks like it. With Outlook 2003 =installed along side of SQL 2000 SP3 using xp_sendmail causes Outlook =2003 to fail if it is closed and restarted after using the above =extended stored procedure in Windows 2000 Server SP3.
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> Assuming you are using a stored procedure to generate the password, =you can
> send an e-mail from the stored procedure (I really don't recommend =doing
> this in a trigger). You can see some information about sending e-mail =from
> SQL Server at http://www.aspfaq.com/2403
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> > > > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> I was following this article:
> > =http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931&Product=3D=
sql
> > and got it working nicely. I would like to elaborate on this. Rather =then
> sending a succeed registration page to the client I would like to send =them
> a page telling them an email has been sent to their e-mail address. =That
> part of it I can do. My trouble is once I generate a password for the
> client I need to let SQL 2000 SP3 know it's time to send them an =e-mail. Is
> this a trigger? Can anyone suggest what applications I might need to =do
> this (except Exchange) and some guidelines on how this can be done? =Thanks.
> > I cannot use Exchange for this as I have Outlook 2003 installed and it =is
> not supported on the same server where Exchange 2003 is installed. I =only
> have the one Server Windows 2000 SP3.
> > -- > George Hester
> __________________________________
> >|||I have to echo Tibor's suggestion, and consider the free, much-less-hassle
xp_smtp_sendmail. If you have any doubts about it, consider that it was
written by a Microsoft employee who knows his ____ and, in addition, my
company is using it in production and swears by it (because we used to swear
*at* SQL Mail).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:eKwgUVuwDHA.1736@.TK2MSFTNGP09.phx.gbl...
No I didn't have to reboot. I just had to shut down the services
mssqlserver and sqlserveragent. Then Outllook 2003 fired up and I restarted
the services. Another bug? Looks like it. With Outlook 2003 installed
along side of SQL 2000 SP3 using xp_sendmail causes Outlook 2003 to fail if
it is closed and restarted after using the above extended stored procedure
in Windows 2000 Server SP3.|||I plan to. No issue with whatever works. But the bug still exists. =Too bad it is not published or at least something akin to it. I am =pretty sure I know what it is. The SQL Mail using ExtendedMAPI is not =releasing (signing off) correctly. I have seen this type of thing =before using a dll made by a MVP whose is a messaging expert. It is =called Redemption. His dll has the same type of issue. He says it is =an issue with Outlook itself. It may be. But his dll and this SQLMail =both exhibit the same destructive quality towards Outlook.
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:uipC3NwwDHA.3216@.TK2MSFTNGP11.phx.gbl...
> I have to echo Tibor's suggestion, and consider the free, =much-less-hassle
> xp_smtp_sendmail. If you have any doubts about it, consider that it =was
> written by a Microsoft employee who knows his ____ and, in addition, =my
> company is using it in production and swears by it (because we used to =swear
> *at* SQL Mail).
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> > > > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:eKwgUVuwDHA.1736@.TK2MSFTNGP09.phx.gbl...
> No I didn't have to reboot. I just had to shut down the services
> mssqlserver and sqlserveragent. Then Outllook 2003 fired up and I =restarted
> the services. Another bug? Looks like it. With Outlook 2003 =installed
> along side of SQL 2000 SP3 using xp_sendmail causes Outlook 2003 to =fail if
> it is closed and restarted after using the above extended stored =procedure
> in Windows 2000 Server SP3.
> >|||Tibor for this stored procedure to work I have to use my ISP's SMTP =server. They are blocking my port 25. Can I use that in this case and =if so how? Thanks.
-- George Hester
__________________________________
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:#uVbGVuwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> I know that XP gets annoyed if I have two mail users at the same time =on one machine (one is SQL
> Server, the other is me, as interactive user), where W2K was OK with =it. It seems "reasonable" that
> W2KS is based on XP and exposes the same behavior. Did you consider =using xp_smtp_sendmail instead?
> > -- > Tibor Karaszi, SQL Server MVP
> Archive at: =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ONOZbMuwDHA.3116@.tk2msftngp13.phx.gbl...
> Hi Aaron:
> > Can you believe it? I got the SQL Mail setup and it seems to be =working. Using my ISPs SMTP
> server. I did the test and it connected to the MAPI profile =successfully. Anyway I proceeded to
> use the extended stored procedure xp_sendmail in Query Analyzer:
> > xp_sendmail @.recipients =3D 'hesterloli@.hotmail.com',
> @.message =3D 'Hello',
> @.subject =3D 'From SQL Server 2000'
> > Actually I sent one to that address and one to my POP3 account. Both =successfully as reported by
> Query Analyzer.
> > But I forgot to have Outlook 2003 open before I did that. So to see =if I got the mail I went to
> open Outlook 2003. Know what happened? Outlook 2003 could not open. =I use MAPI profiles and when
> I tried to start Outlook 2003 the Error message I got was, "The =service could not be started." No
> offer to start in safe mode. Just the error message box. I post it =next time if I can replicate
> the issue again.
> > All I know is that it sounds like some dll went belly-up. I rebooted =and Outlook 2003 was fine and
> there were the two e-mails from SQL in Outlook 2003 and Outlook =Express which handles my Hotmail
> account.
> > Now I don't know what to do. I could try the xp_sendmail again with =Outlook 2003 open and see if
> that avoids the issue. But I just don't know. Ever heard of this =before?
> > -- > George Hester
> __________________________________
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> > Assuming you are using a stored procedure to generate the password, =you can
> > send an e-mail from the stored procedure (I really don't recommend =doing
> > this in a trigger). You can see some information about sending =e-mail from
> > SQL Server at http://www.aspfaq.com/2403
> >
> > -- > > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >
> > "George Hester" <hesterloli@.hotmail.com> wrote in message
> > news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> > I was following this article:
> >
> > =http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931&Product=3D=
sql
> >
> > and got it working nicely. I would like to elaborate on this. =Rather then
> > sending a succeed registration page to the client I would like to =send them
> > a page telling them an email has been sent to their e-mail address. =That
> > part of it I can do. My trouble is once I generate a password for =the
> > client I need to let SQL 2000 SP3 know it's time to send them an =e-mail. Is
> > this a trigger? Can anyone suggest what applications I might need =to do
> > this (except Exchange) and some guidelines on how this can be done? =Thanks.
> >
> > I cannot use Exchange for this as I have Outlook 2003 installed and =it is
> > not supported on the same server where Exchange 2003 is installed. =I only
> > have the one Server Windows 2000 SP3.
> >
> > -- > > George Hester
> > __________________________________
> >
> >
> >|||Yes, you need an SMTP server, which IMO is the very strength of the solution. No MAPI etc. MAPI was
never designed as a multi-user app (say you have SQL Server and an interactive user at the same
time), nor was it designed to be used but by interactive users. That is why you have soo many KB
articles on the subject so there's a KB article which serves as just an index to all the other KB
articles.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:OIZhP81wDHA.1576@.TK2MSFTNGP11.phx.gbl...
Tibor for this stored procedure to work I have to use my ISP's SMTP server. They are blocking my
port 25. Can I use that in this case and if so how? Thanks.
--
George Hester
__________________________________
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message
news:#uVbGVuwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> I know that XP gets annoyed if I have two mail users at the same time on one machine (one is SQL
> Server, the other is me, as interactive user), where W2K was OK with it. It seems "reasonable"
that
> W2KS is based on XP and exposes the same behavior. Did you consider using xp_smtp_sendmail
instead?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:ONOZbMuwDHA.3116@.tk2msftngp13.phx.gbl...
> Hi Aaron:
> Can you believe it? I got the SQL Mail setup and it seems to be working. Using my ISPs SMTP
> server. I did the test and it connected to the MAPI profile successfully. Anyway I proceeded to
> use the extended stored procedure xp_sendmail in Query Analyzer:
> xp_sendmail @.recipients = 'hesterloli@.hotmail.com',
> @.message = 'Hello',
> @.subject = 'From SQL Server 2000'
> Actually I sent one to that address and one to my POP3 account. Both successfully as reported by
> Query Analyzer.
> But I forgot to have Outlook 2003 open before I did that. So to see if I got the mail I went to
> open Outlook 2003. Know what happened? Outlook 2003 could not open. I use MAPI profiles and
when
> I tried to start Outlook 2003 the Error message I got was, "The service could not be started." No
> offer to start in safe mode. Just the error message box. I post it next time if I can replicate
> the issue again.
> All I know is that it sounds like some dll went belly-up. I rebooted and Outlook 2003 was fine
and
> there were the two e-mails from SQL in Outlook 2003 and Outlook Express which handles my Hotmail
> account.
> Now I don't know what to do. I could try the xp_sendmail again with Outlook 2003 open and see if
> that avoids the issue. But I just don't know. Ever heard of this before?
> --
> George Hester
> __________________________________
> "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> > Assuming you are using a stored procedure to generate the password, you can
> > send an e-mail from the stored procedure (I really don't recommend doing
> > this in a trigger). You can see some information about sending e-mail from
> > SQL Server at http://www.aspfaq.com/2403
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >
> > "George Hester" <hesterloli@.hotmail.com> wrote in message
> > news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> > I was following this article:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;247931&Product=sql
> >
> > and got it working nicely. I would like to elaborate on this. Rather then
> > sending a succeed registration page to the client I would like to send them
> > a page telling them an email has been sent to their e-mail address. That
> > part of it I can do. My trouble is once I generate a password for the
> > client I need to let SQL 2000 SP3 know it's time to send them an e-mail. Is
> > this a trigger? Can anyone suggest what applications I might need to do
> > this (except Exchange) and some guidelines on how this can be done? Thanks.
> >
> > I cannot use Exchange for this as I have Outlook 2003 installed and it is
> > not supported on the same server where Exchange 2003 is installed. I only
> > have the one Server Windows 2000 SP3.
> >
> > --
> > George Hester
> > __________________________________
> >
> >
>|||> But the bug still exists.
Have you submitted it as a bug? Do you have a bug number?
From your description, sounds like a configuration issue, not a bug.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||> Tibor for this stored procedure to work I have to use my ISP's SMTP
server.
Are you running this out of your house? If your ISP is your only path to
the Internet, how do you plan to send mail using any solution?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Could be who knows. It's been known to happen. But honestly I wonder =how much of a configuration I can do to make a MAPI profile in the =Control Panel. Have Outlook work with it without issue. Have SQL Mail =see all three profiles. And run the xp-sendmail extended stored =procedure. And then get the error I showed you after shutting down and =restarting Outlook 2002. If it's a configuration issue then the =defaults are error prone. Becuase I did nothing else then follow the =GUI's.
-- George Hester
__________________________________
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message =news:e26eDN#wDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > But the bug still exists.
> > Have you submitted it as a bug? Do you have a bug number?
> > From your description, sounds like a configuration issue, not a bug.
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> >|||Well there is nothing I can do about it Tibor. My ISP is not going to =open my port 25. I could send them a Christmas present they ain't going =to do it. I could change the port to say 2525. But that won't help as =the stored procedure says it is explicitly desgined for SMTP on port 25. = Mine works fine it just cannot talk to the outside world.
-- George Hester
__________________________________
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:#ygbjd6wDHA.3216@.TK2MSFTNGP11.phx.gbl...
> Yes, you need an SMTP server, which IMO is the very strength of the =solution. No MAPI etc. MAPI was
> never designed as a multi-user app (say you have SQL Server and an =interactive user at the same
> time), nor was it designed to be used but by interactive users. That =is why you have soo many KB
> articles on the subject so there's a KB article which serves as just =an index to all the other KB
> articles.
> > -- > Tibor Karaszi, SQL Server MVP
> Archive at: =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:OIZhP81wDHA.1576@.TK2MSFTNGP11.phx.gbl...
> Tibor for this stored procedure to work I have to use my ISP's SMTP =server. They are blocking my
> port 25. Can I use that in this case and if so how? Thanks.
> > -- > George Hester
> __________________________________
> "Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message
> news:#uVbGVuwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> > I know that XP gets annoyed if I have two mail users at the same =time on one machine (one is SQL
> > Server, the other is me, as interactive user), where W2K was OK with =it. It seems "reasonable"
> that
> > W2KS is based on XP and exposes the same behavior. Did you consider =using xp_smtp_sendmail
> instead?
> >
> > -- > > Tibor Karaszi, SQL Server MVP
> > Archive at: =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> >
> >
> > "George Hester" <hesterloli@.hotmail.com> wrote in message
> > news:ONOZbMuwDHA.3116@.tk2msftngp13.phx.gbl...
> > Hi Aaron:
> >
> > Can you believe it? I got the SQL Mail setup and it seems to be =working. Using my ISPs SMTP
> > server. I did the test and it connected to the MAPI profile =successfully. Anyway I proceeded to
> > use the extended stored procedure xp_sendmail in Query Analyzer:
> >
> > xp_sendmail @.recipients =3D 'hesterloli@.hotmail.com',
> > @.message =3D 'Hello',
> > @.subject =3D 'From SQL Server 2000'
> >
> > Actually I sent one to that address and one to my POP3 account. =Both successfully as reported by
> > Query Analyzer.
> >
> > But I forgot to have Outlook 2003 open before I did that. So to see =if I got the mail I went to
> > open Outlook 2003. Know what happened? Outlook 2003 could not =open. I use MAPI profiles and
> when
> > I tried to start Outlook 2003 the Error message I got was, "The =service could not be started." No
> > offer to start in safe mode. Just the error message box. I post it =next time if I can replicate
> > the issue again.
> >
> > All I know is that it sounds like some dll went belly-up. I =rebooted and Outlook 2003 was fine
> and
> > there were the two e-mails from SQL in Outlook 2003 and Outlook =Express which handles my Hotmail
> > account.
> >
> > Now I don't know what to do. I could try the xp_sendmail again with =Outlook 2003 open and see if
> > that avoids the issue. But I just don't know. Ever heard of this =before?
> >
> > -- > > George Hester
> > __________________________________
> > "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> > news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> > > Assuming you are using a stored procedure to generate the =password, you can
> > > send an e-mail from the stored procedure (I really don't recommend =doing
> > > this in a trigger). You can see some information about sending =e-mail from
> > > SQL Server at http://www.aspfaq.com/2403
> > >
> > > -- > > > Aaron Bertrand
> > > SQL Server MVP
> > > http://www.aspfaq.com/
> > >
> > >
> > >
> > >
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> > > news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > I was following this article:
> > >
> > > =http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931&Product=3D=
sql
> > >
> > > and got it working nicely. I would like to elaborate on this. =Rather then
> > > sending a succeed registration page to the client I would like to =send them
> > > a page telling them an email has been sent to their e-mail =address. That
> > > part of it I can do. My trouble is once I generate a password for =the
> > > client I need to let SQL 2000 SP3 know it's time to send them an =e-mail. Is
> > > this a trigger? Can anyone suggest what applications I might need =to do
> > > this (except Exchange) and some guidelines on how this can be =done? Thanks.
> > >
> > > I cannot use Exchange for this as I have Outlook 2003 installed =and it is
> > > not supported on the same server where Exchange 2003 is installed. = I only
> > > have the one Server Windows 2000 SP3.
> > >
> > > -- > > > George Hester
> > > __________________________________
> > >
> > >
> >
> >
> >|||Yes. Yes. Oh it works fine using xp-sendmail. No problem except for =the crashing Outlook 2002. That is not my ISP's fault. I can also use =CDO. But this is a little different and I would be happy with =xp-sendmail if I could just get SQL to release the call to MAPI after it =finishes the stored procedure. The one you recommend won't work because =my ISP has blocked my port 25. Unless I can get the stored procedure to =expect my SMTP server on port 2525 say. Then it will work.
-- George Hester
__________________________________
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message =news:#sEzMO#wDHA.4060@.TK2MSFTNGP11.phx.gbl...
> > Tibor for this stored procedure to work I have to use my ISP's SMTP
> server.
> > Are you running this out of your house? If your ISP is your only path =to
> the Internet, how do you plan to send mail using any solution?
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> >|||> My ISP is not going to open my port 25.
Do they really have the ability to block a port OUTBOUND? In my experience,
the port blocking has been inbound...
In any case,
> the stored procedure says it is explicitly desgined for SMTP on port 25.
Did you actually *read* the docs on xp_smtp_sendmail? From
http://www.sqldev.net/xp/xpsmtp.htm:
@.port INT 25 Optional Valid socket port number Port number
for SMTP service, default port 25
--
Aaron Bertrand
SQL Server MVP|||See my other reply.
--
Aaron Bertrand
SQL Server MVP
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:uvs8ycCxDHA.2708@.TK2MSFTNGP09.phx.gbl...
Yes. Yes. Oh it works fine using xp-sendmail. No problem except for the
crashing Outlook 2002. That is not my ISP's fault. I can also use CDO.
But this is a little different and I would be happy with xp-sendmail if I
could just get SQL to release the call to MAPI after it finishes the stored
procedure. The one you recommend won't work because my ISP has blocked my
port 25. Unless I can get the stored procedure to expect my SMTP server on
port 2525 say. Then it will work.|||Um thanks Aaron. Yes I read as much as I could understand. We all know =the word Oui in French. But I bet if you looked at a book from Marcel =Proust in the original you may have trouble finding that word.
-- George Hester
__________________________________
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message =news:OELpqlCxDHA.2156@.TK2MSFTNGP09.phx.gbl...
> > My ISP is not going to open my port 25.
> > Do they really have the ability to block a port OUTBOUND? In my =experience,
> the port blocking has been inbound...
> > In any case,
> > > the stored procedure says it is explicitly desgined for SMTP on port =25.
> > Did you actually *read* the docs on xp_smtp_sendmail? From
> http://www.sqldev.net/xp/xpsmtp.htm:
> > @.port INT 25 Optional Valid socket port number Port =number
> for SMTP service, default port 25
> > -- > Aaron Bertrand
> SQL Server MVP
> >|||A configuration issue was not it. It was how I called the extended =stored procedure xp_sendmail. See there is a statment in the BOL or at =Microsoft not sure which that when xp_sendmail is called we must first =call xp_startmail. It's true that this is not necessary but I believe =it is best to do all the procedures and determine if my issue still =occurs.
So this time I made a batch that looks like this:
DECLARE @.hc int
EXEC @.hc =3D xp_startmail @.user =3D 'My MAPI Profile',
@.password =3D NULL
If @.hc =3D 0
EXEC @.hc =3D xp_sendmail @.recipients =3D 'hesterloli@.hotmail.com',
@.message =3D 'Hello',
@.subject =3D 'From SQL Server 2000 SQL Mail'
If @.hc =3D 0
EXEC xp_stopmail
and put this in Query Analyzer. I ran it. All worked well there. The =SQL Mail started, the Mail was sent, and the SQL Mail stopped. And sure =enough I got the mail.
I then quit Outlook 2003 and tried to start it up again to see if I =avoided the error I posted a bit ago. No error.
If you think there is a better way to write this batch I'd appreciate =it. I just tried whatever I could get to not give me an error in Query =analyzer when I checked the statements.
-- George Hester
__________________________________
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message =news:e26eDN#wDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > But the bug still exists.
> > Have you submitted it as a bug? Do you have a bug number?
> > From your description, sounds like a configuration issue, not a bug.
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> >|||Hi Tibor. I got it to work. Thanks for that right now it seems the =ticket. And you too Aaron.
-- George Hester
__________________________________
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:#ygbjd6wDHA.3216@.TK2MSFTNGP11.phx.gbl...
> Yes, you need an SMTP server, which IMO is the very strength of the =solution. No MAPI etc. MAPI was
> never designed as a multi-user app (say you have SQL Server and an =interactive user at the same
> time), nor was it designed to be used but by interactive users. That =is why you have soo many KB
> articles on the subject so there's a KB article which serves as just =an index to all the other KB
> articles.
> > -- > Tibor Karaszi, SQL Server MVP
> Archive at: =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:OIZhP81wDHA.1576@.TK2MSFTNGP11.phx.gbl...
> Tibor for this stored procedure to work I have to use my ISP's SMTP =server. They are blocking my
> port 25. Can I use that in this case and if so how? Thanks.
> > -- > George Hester
> __________________________________
> "Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message
> news:#uVbGVuwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> > I know that XP gets annoyed if I have two mail users at the same =time on one machine (one is SQL
> > Server, the other is me, as interactive user), where W2K was OK with =it. It seems "reasonable"
> that
> > W2KS is based on XP and exposes the same behavior. Did you consider =using xp_smtp_sendmail
> instead?
> >
> > -- > > Tibor Karaszi, SQL Server MVP
> > Archive at: =http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
> >
> >
> > "George Hester" <hesterloli@.hotmail.com> wrote in message
> > news:ONOZbMuwDHA.3116@.tk2msftngp13.phx.gbl...
> > Hi Aaron:
> >
> > Can you believe it? I got the SQL Mail setup and it seems to be =working. Using my ISPs SMTP
> > server. I did the test and it connected to the MAPI profile =successfully. Anyway I proceeded to
> > use the extended stored procedure xp_sendmail in Query Analyzer:
> >
> > xp_sendmail @.recipients =3D 'hesterloli@.hotmail.com',
> > @.message =3D 'Hello',
> > @.subject =3D 'From SQL Server 2000'
> >
> > Actually I sent one to that address and one to my POP3 account. =Both successfully as reported by
> > Query Analyzer.
> >
> > But I forgot to have Outlook 2003 open before I did that. So to see =if I got the mail I went to
> > open Outlook 2003. Know what happened? Outlook 2003 could not =open. I use MAPI profiles and
> when
> > I tried to start Outlook 2003 the Error message I got was, "The =service could not be started." No
> > offer to start in safe mode. Just the error message box. I post it =next time if I can replicate
> > the issue again.
> >
> > All I know is that it sounds like some dll went belly-up. I =rebooted and Outlook 2003 was fine
> and
> > there were the two e-mails from SQL in Outlook 2003 and Outlook =Express which handles my Hotmail
> > account.
> >
> > Now I don't know what to do. I could try the xp_sendmail again with =Outlook 2003 open and see if
> > that avoids the issue. But I just don't know. Ever heard of this =before?
> >
> > -- > > George Hester
> > __________________________________
> > "Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
> > news:Ok8Io0rwDHA.1512@.TK2MSFTNGP10.phx.gbl...
> > > Assuming you are using a stored procedure to generate the =password, you can
> > > send an e-mail from the stored procedure (I really don't recommend =doing
> > > this in a trigger). You can see some information about sending =e-mail from
> > > SQL Server at http://www.aspfaq.com/2403
> > >
> > > -- > > > Aaron Bertrand
> > > SQL Server MVP
> > > http://www.aspfaq.com/
> > >
> > >
> > >
> > >
> > > "George Hester" <hesterloli@.hotmail.com> wrote in message
> > > news:ugKtPGrwDHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > I was following this article:
> > >
> > > =http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;247931&Product=3D=
sql
> > >
> > > and got it working nicely. I would like to elaborate on this. =Rather then
> > > sending a succeed registration page to the client I would like to =send them
> > > a page telling them an email has been sent to their e-mail =address. That
> > > part of it I can do. My trouble is once I generate a password for =the
> > > client I need to let SQL 2000 SP3 know it's time to send them an =e-mail. Is
> > > this a trigger? Can anyone suggest what applications I might need =to do
> > > this (except Exchange) and some guidelines on how this can be =done? Thanks.
> > >
> > > I cannot use Exchange for this as I have Outlook 2003 installed =and it is
> > > not supported on the same server where Exchange 2003 is installed. = I only
> > > have the one Server Windows 2000 SP3.
> > >
> > > -- > > > George Hester
> > > __________________________________
> > >
> > >
> >
> >
> >