Is this the bug in SQL2000?

HI Group
I have shared memory enable on client network utility
on my sql2000 server but every time I restart the service
I will lost my setting even the box still check enable
shared memory but my sql only listen to TCP and Name pipe
Does any one have this same problem ? I must restart the
sql service 4 to 5 times before the sql server listen to
the share memory. Any clue ?
ThanksThe "Client Network Utility" is only used to configure how clients connect.
The "Server Network Utility" controls what connections SQL Server will
accept/listen on. It does not have an option for shared memory, that is
always on unless there is a failure at startup (which would be documented
in the SQL error log).
Which utility are you using? Where are you checking what SQL Server listens
on (check the SQL error log)? What is the actual failure you see?
Hint: To force a client to connect with a certain protocol, include it in
the connection string. If your instance name is MyServer\Inst1, you would
use:
for TCP/IP sockets: tcp:MyServer\Inst1
for shared memory: lcp:MyServer\Inst1
for named pipes: np:MyServer\Inst1
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||HI
I checked in the error log when SQL started
the only TCP and Namepipe is listen by server
then if I use query analyzer on the console
it will connect at TCP protocol
I wolud like to connect client from sql console
using LPC protocol not TCP. Again there is no error
indicated any thing failed on sql startup log but
it only listen to TCP and Name pipe even shared memory
have been checked.
Thanks
>--Original Message--
>The "Client Network Utility" is only used to configure
how clients connect.
>The "Server Network Utility" controls what connections
SQL Server will
>accept/listen on. It does not have an option for shared
memory, that is
>always on unless there is a failure at startup (which
would be documented
>in the SQL error log).
>Which utility are you using? Where are you checking what
SQL Server listens
>on (check the SQL error log)? What is the actual failure
you see?
>Hint: To force a client to connect with a certain
protocol, include it in
>the connection string. If your instance name is
MyServer\Inst1, you would
>use:
>for TCP/IP sockets: tcp:MyServer\Inst1
>for shared memory: lcp:MyServer\Inst1
>for named pipes: np:MyServer\Inst1
>Cindy Gross, MCDBA, MCSE
>http://cindygross.tripod.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>.
>|||There is no checkbox to make SQL Server listen on shared memory. I suspect
you're talking about the checkbox that allows a client to connect with
shared memory. Shared memory only works on the same box.
You mentioned that SQL Server is only listening on TCP and named pipes. If
the error log doesn't list shared memory as a protocol it is listening on
then you should check to see if there are other errors, particularly
related to ssnetlib.dll. Can you post your errorlog file?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.sql

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 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 bug with Convert?

I was trying to debug some DateTime.Now in a C# project and while debugging I found this.

In your Sql Management Studio, type this:

The 916 becomes 917. Why does my millisecond get screwed?

select convert(datetime, '2007-06-29 15:22:31:921') -- prints 2007-06-29 15:22:31.920

select convert(datetime, '2007-06-29 15:22:31:916') -- print 2007-06-29 15:22:31.917

That is because of the precision of the datetime data type 1/300 of a second. Check BOL for more info about datetime data type.

select convert(datetime, '2007-06-29 15:22:31:998')

go

AMB

Is this an SQL bug (SQL 2000)?

Hi....
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegroups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Is this an SQL bug (SQL 2000)?

Hi....
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegr oups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Is this an SQL bug (SQL 2000)?

Hi....
Can anyone explain the following results:
CREATE TABLE A
(
A varchar(256) NOT NULL
)
go
INSERT INTO A VALUES('test')
go
SELECT * FROM A WHERE A LIKE 'test'
go
=> Returns 1 row
DECLARE @.mytest varchar
SET @.mytest = 'test'
SELECT * FROM A WHERE A LIKE @.mytest
go
=> Returns nothing! Why?
thanks,
Neil"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1172825344.594113.234760@.j27g2000cwj.googlegroups.com...
> Hi....
> Can anyone explain the following results:
> CREATE TABLE A
> (
> A varchar(256) NOT NULL
> )
> go
> INSERT INTO A VALUES('test')
> go
> SELECT * FROM A WHERE A LIKE 'test'
> go
> => Returns 1 row
> DECLARE @.mytest varchar
> SET @.mytest = 'test'
> SELECT * FROM A WHERE A LIKE @.mytest
> go
> => Returns nothing! Why?
>
Not a bug.
"DECLARE @.mytest varchar" is equivalent to "DECLARE @.mytest varchar(1)".
So your second SELECT statement is equivalent to "SELECT * FROM A WHERE A
LIKE 't'".
Always specify the size for VARCHAR/NVARCHAR.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Is this a new SQL BUG

For the last two days, I have been checking my SQL Server error logs and this is what I find common on all the SQL Servers, with Audit Login options ON: Login failed for 'A', Login failed for 'B', Login Failed for 'C'..all the way to the last login.
For each login defined in the SQL Server, I'm finding that the error message is getting generated 18 times. My guess is that someone is trying to run some kind of code.

Any experience?Make sure you don't have any easily guessable accounts, and DEFINITELY, - change your SA password. This is not a bug (unless you don't have the latest security patch from M$, in which case it is and has been fixed). Whatch out, someone is after your server.

Is this a bug? And does SQL Server 2005 fix it?

Hi all,
In the past, I've tried something like the following in my stored procedures
to allow table filtering:
SELECT * FROM Widgets
WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
AND (@.Color IS NULL OR Widgets.Color = @.Color)
NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
parameters to the stored procedure, allowing me to filter down my Widgets
table quite nicely.
This works great, but apparently hinders SQL Server 2000's ability to index
the table! This can lead to very bad performance when doing these types of
filtered queries.
My questions are: 1) Is this behavior a bug? I'm not sure why adding the IS
NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
the same problem?
Thanks!
George Saliba
Six88 SolutionsThe ISNULL is not the cause, the OR is the bad guy.
Example:
use northwind
go
exec sp_helpindexes 'dbo.orders'
go
set showplan_text on
go
declare @.d datetime
select orderid, orderdate, customerid
from dbo.orders
where orderdate = @.d and @.d is null
select orderid, orderdate, customerid
from dbo.orders
where orderdate = @.d OR @.d is null
go
set showplan_text off
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"George Saliba" wrote:

> Hi all,
> In the past, I've tried something like the following in my stored procedur
es
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
> NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
> parameters to the stored procedure, allowing me to filter down my Widgets
> table quite nicely.
> This works great, but apparently hinders SQL Server 2000's ability to inde
x
> the table! This can lead to very bad performance when doing these types of
> filtered queries.
> My questions are: 1) Is this behavior a bug? I'm not sure why adding the I
S
> NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
> the same problem?
> Thanks!
> George Saliba
> Six88 Solutions|||Ahh, very interesting!
So why does an OR clause cause indexes to be used when one side of the OR
clause contains a scalar (@.Column IS NULL) and the other contains a table
column (Table.Column = @.Column)? It seems like if one side is a scalar it
should still be able to maintain indexes?
I guess that's my real question then. Does an OR clause have to cause SQL
Server to not use indexes, even when one side of the OR clause is purely
scalar?
Thanks!
-George
"Alejandro Mesa" wrote:
> The ISNULL is not the cause, the OR is the bad guy.
> Example:
> use northwind
> go
> exec sp_helpindexes 'dbo.orders'
> go
> set showplan_text on
> go
> declare @.d datetime
> select orderid, orderdate, customerid
> from dbo.orders
> where orderdate = @.d and @.d is null
> select orderid, orderdate, customerid
> from dbo.orders
> where orderdate = @.d OR @.d is null
> go
> set showplan_text off
> go
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "George Saliba" wrote:
>|||SQL Server Transact-SQL WHERE Clause
http://www.sql-server-performance.c...t_sql_where.asp
AMB
"George Saliba" wrote:
> Ahh, very interesting!
> So why does an OR clause cause indexes to be used when one side of the OR
> clause contains a scalar (@.Column IS NULL) and the other contains a table
> column (Table.Column = @.Column)? It seems like if one side is a scalar it
> should still be able to maintain indexes?
> I guess that's my real question then. Does an OR clause have to cause SQL
> Server to not use indexes, even when one side of the OR clause is purely
> scalar?
> Thanks!
> -George
> "Alejandro Mesa" wrote:
>|||> I guess that's my real question then. Does an OR clause have to cause SQL
> Server to not use indexes, even when one side of the OR clause is purely
> scalar?
SQL Server 2005 corrects this to some degree. I took Alejandro's script and
modified it for the new AdventureWorks database:
set nocount on
use AdventureWorks
go
exec sp_helpindex 'Sales.SalesOrderHeader'
go
set showplan_text on
go
declare @.d datetime
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
set @.d = '20040731'
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
go
set showplan_text off
go
All four queries utilize a clustered index scan. The ones with AND perform
an additional filter before the scan. Here is the output, including
sp_helpindex:
index_name index_description index_keys
AK_SalesOrderHeader_rowguid nonclustered, unique located on PRIMARY rowguid
AK_SalesOrderHeader_SalesOrderNumber nonclustered, unique located on PRIMARY
SalesOrderNumber
IX_SalesOrderHeader_CustomerID nonclustered located on PRIMARY CustomerID
IX_SalesOrderHeader_SalesPersonID nonclustered located on PRIMARY
SalesPersonID
PK_SalesOrderHeader_SalesOrderID clustered, unique, primary key located on
PRIMARY SalesOrderID
StmtText
----
---
declare @.d datetime
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
StmtText
----
----
---
|--Filter(WHERE:(STARTUP EXPR([@.d] IS NULL)))
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d]))
StmtText
----
--
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
StmtText
----
----
---
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d] OR [@.d]
IS NULL))
StmtText
----
--
set @.d = '20040731'
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d and @.d is null
StmtText
----
----
---
|--Filter(WHERE:(STARTUP EXPR([@.d] IS NULL)))
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d]))
StmtText
----
--
select SalesOrderID, OrderDate, CustomerID
from Sales.SalesOrderHeader
where OrderDate = @.d OR @.d is null
StmtText
----
----
---
|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]),
WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate]=[@.d] OR [@.d]
IS NULL))
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Hmm, it's unfortunate that this IS NULL is one of these non-sargable beasts.
It sure would make my life easier if they weren't. :(
On the upside, I realized I can remove the @.Param IS NULL part for columns
that are not nullable (since Column = @.Param where the @.Param is null will
return false always for non-nullable columns), which would leave only the
sargable part of those parts of the WHERE clause, which should increase
performance a bit. :)
Not ideal, but it will do. Thanks for your help!
-George
"Alejandro Mesa" wrote:
> SQL Server Transact-SQL WHERE Clause
> http://www.sql-server-performance.c...t_sql_where.asp
>
> AMB
> "George Saliba" wrote:
>|||"George Saliba" <GeorgeSaliba@.discussions.microsoft.com> wrote in message
news:6592A19D-5F74-44F2-94C9-401301101EA7@.microsoft.com...
> Hi all,
> In the past, I've tried something like the following in my stored
procedures
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
As mentioned the Or is the problem: If the Columns cannot be null you can
use:
WHERE (Widgets.WidgetID = COALESCE(@.WidgetID,Widgets.WidgetID))
AND (Widgets.WidgetTypeID = COALESCE(@.WidgetTypeID,Widgets.WidgetTypeID))
AND (Widgets.Color = COALESCE(@.Color,Widgets.Color))
Good Luck,
Jim|||George,
In some situation, the following syntax improves the performance a lot.
However, you have to make sure that the columns do not contains NULLs,
because this syntax will discard all rows with NULLs in any of the
columns WidgetID, WidgetTypeID, Color.
The query assumes that WidgetID is an int, WidgetTypeID is a smallint
and Color is a char or varchar.
SELECT * FROM Widgets
WHERE WidgetID BETWEEN COALESCE(@.WidgetID, -2147483648)
AND COALESCE(@.WidgetID, 2147483647)
AND WidgetTypeID BETWEEN COALESCE(@.WidgetTypeID, -32768)
AND COALESCE(@.WidgetTypeID, 32767)
AND Color LIKE COALESCE(@.Color,'%')
Hope this helps,
Gert-Jan
George Saliba wrote:
> Hi all,
> In the past, I've tried something like the following in my stored procedur
es
> to allow table filtering:
> SELECT * FROM Widgets
> WHERE (@.WidgetID IS NULL OR Widgets.WidgetID = @.WidgetID)
> AND (@.WidgetTypeID IS NULL OR Widgets.WidgetTypeID = @.WidgetTypeID)
> AND (@.Color IS NULL OR Widgets.Color = @.Color)
> NOTE: The values @.WidgetID, @.WidgetTypeID, and @.Color are nullable
> parameters to the stored procedure, allowing me to filter down my Widgets
> table quite nicely.
> This works great, but apparently hinders SQL Server 2000's ability to inde
x
> the table! This can lead to very bad performance when doing these types of
> filtered queries.
> My questions are: 1) Is this behavior a bug? I'm not sure why adding the I
S
> NULL check breaks indexing, and 2) If so, does SQL Server 2005 suffer from
> the same problem?
> Thanks!
> George Saliba
> Six88 Solutions|||Nice trick with between (I have used the '%' trick for varchar values
before). Probably not a big difference, but you could use 0 instead of the
lower bound if you know the values must all be positive integers.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42601C3E.2AE96DC5@.toomuchspamalready.nl...
> George,
> In some situation, the following syntax improves the performance a lot.
> However, you have to make sure that the columns do not contains NULLs,
> because this syntax will discard all rows with NULLs in any of the
> columns WidgetID, WidgetTypeID, Color.
> The query assumes that WidgetID is an int, WidgetTypeID is a smallint
> and Color is a char or varchar.
> SELECT * FROM Widgets
> WHERE WidgetID BETWEEN COALESCE(@.WidgetID, -2147483648)
> AND COALESCE(@.WidgetID, 2147483647)
> AND WidgetTypeID BETWEEN COALESCE(@.WidgetTypeID, -32768)
> AND COALESCE(@.WidgetTypeID, 32767)
> AND Color LIKE COALESCE(@.Color,'%')
> Hope this helps,
> Gert-Jan
>
> George Saliba wrote:

Is this a bug?

i use AMO to connect MSAS2005. Seems everything is fine except i do like this:

I try to restart the Analysis Service, and then connect to the server. This will cause windows prompt msmdsrv.exe error or debug info sometimes. This comes with the Connect() methed, but the codes will continue running - that means the code doesn't throw exceptions and executed as usual. I tried to sleep the thread for a while(Like in code make 10 seconds sleeping) but cann't pass the test for every time. Coding as below:

ControlService("MSSQLServerOLAPService", ServicesAccess.ServiceAction.Restart) //using ServiceController class to restart the windows service
System.Threading.Thread.Sleep(10000); //Even sleep the process for 10 Seconds
Server server = new Server();
server.Connect("localhost");
... ...

bool ControlService(string strServiceName, ServiceAction action)
{
try
{
oService = new ServiceController(strServiceName);

if (action == ServiceAction.Stop || action == ServiceAction.Restart)
{
if (!oService.Status.Equals(System.ServiceProcess.ServiceControllerStatus.Stopped) && oService.CanStop)
{
oService.Stop();
oService.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Stopped);
}
}

if (action == ServiceAction.Start || action == ServiceAction.Restart)
{
if (!oService.Status.Equals(System.ServiceProcess.ServiceControllerStatus.Running))
{
oService.Start();
oService.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running);
}
}
}
catch (Exception e)
{
throw e;
}
return true;
}

Sometimes you need more than 10 sec for Analysis Server to start.

As for the rest. It is hard to say what is going on. If you are able to restart Analysis Server using service control manager (SCM) and then connect to it from your AMO application that would mean your ServiceControl code is not functioning correctly.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

yes, this is the problem i tried to describe.

we don't know if the analysis service is ready to connect even we get its "running" status from the codes. And if we connect it at this time, it will tell us it's crashed.

Is this a bug?

RS 2005 (SQL 2005 SP1).

When you edit the ASP.Net tab (in IIS) for the ReportServer folder and change the Authentication Mode from Windows to None the Report Server will bomb out. Trying to open th ereportserver returns "Report Server is not responding, verify the report server is running and can be accessed from this computer".

Changing it back to "Windows" makes no difference, even re-starting the Reporting Service and IIS makes no difference, the report server will not respond.

Can some one test this?

I ended up having to re-install RS 2005....!!

What does the venet log / the logs in the log directory tell you ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

In the SQLDUMPER_ERRORLog.log file there wass this..

07/13/06 12:53:35, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 328
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x47405860
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
07/13/06 12:53:35, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
07/13/06 12:53:36, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
07/13/06 12:53:37, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
07/13/06 12:53:37, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\LogFiles\SQLDmpr0001.mdmp
07/13/06 12:53:37, ACTION, w3wp.exe, Watson Invoke: No

|||Is the report service running after that bug ? What does the reportserver log tell you ? Are there any event log entries ? It would be helpful to have all event log (at leat the last entries) to identify the bug.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Yes the report service was running after this.

Did you try to replicate the problem (on a test machine of course)....

|||

Hey, niallhannon,

I have read some of your posting and found out that you had most of the problems with the report service I have now, I wonder if you have set up the sample reports from Microsoft.

I am setting up the sample report on the testing server and I have problems with deploying to the http://myserver/reportserver and it gave the the following error, see if you can provide some tips to me:

TITLE: Microsoft Report Designer

A connection could not be made to the report server http://10.128.64.223/reportserver.

ADDITIONAL INFORMATION:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.1399.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}

</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00" target="_blank">Get Online Help</a></li><ul>
<li>Object reference not set to an instance of an object.</li>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.ReportingServices.Designer)

BUTTONS:

OK

IS this a Bug?

before reporting to MS -Connect , i just want clarify

I have a table

Create table Permssions(

GroupPermissionID int ,

List smallint,

[View] smallint,

Manage smallint)

i opened this table in Management studio and entered 1 in all columns when i try to do save and exit

"No row was updated

the data in row 1 was not committed.

Error Source : .Net SqlClient Dataprovider

Error Message : Conversion failed when converting the varchar value 'List' to datatype int

Correct the error and retry or press escape to cancel the changes "

basically the problem was with the List Column name.

Madhu

Hi Madhu,

yes I think so - it is problem with the column name 'List'; but if you run sql the data will be inserte.

CU

tosc

|||

yes i did try that... but Management studio has problem with it

Madhu

|||

yes, the management studio has a problem -> .Net SqlClient Dataprovider

tosc

Is this a bug, CURSOR_STATUS() always return -3 ?

Hi,
With this simple test, CURSOR_STATUS() function always return -3
use Northwind
go
if object_id('dbo.TestCursor') is not null
drop proc dbo.TestCursor
go
create proc dbo.TestCursor
as
declare @.ContactName varchar(50)
declare My_Curs cursor
fast_forward
for
select ContactName from dbo.Customers
open My_Curs
fetch next from My_Curs into @.ContactName
select @.ContactName as ContactName
select
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable,
CURSOR_STATUS('local', 'My_Curs_Curs') as CursStatlocal,
CURSOR_STATUS('variable', 'My_Curs_Curs') as CursStatvariable
close My_Curs
deallocate My_Curs
select
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable,
CURSOR_STATUS('local', 'My_Curs_Curs') as CursStatlocal,
CURSOR_STATUS('variable', 'My_Curs_Curs') as CursStatvariable
go
exec dbo.TestCursor
go
I have 02 questions
1. Is this is a bug with CURSOR_STATUS() function ?
2. If the SP fails in the middle and close / deallocate are not executed,
will SQL Server close and dealocate the resources of us or the next time the
SP is execute it will throw 'A cursor with the name ... already exists' ?
According to my tests SQL Server close and dealocate the cursor automaticaly
Thak you for your helpOn Fri, 30 Sep 2005 13:35:15 -0700, S.M wrote:

>Hi,
>With this simple test, CURSOR_STATUS() function always return -3
(snip)
>I have 02 questions
>1. Is this is a bug with CURSOR_STATUS() function ?
Hi S.M.,
No. Try changing your SELECT statement (at both locations) to
select
CURSOR_STATUS('local', 'My_Curs') as CursStatlocal,
CURSOR_STATUS('global', 'My_Curs') as CursStatglobal,
CURSOR_STATUS('variable', 'My_Curs') as CursStatvariable
(That is - change the names, AND include a query for global cursor).
>2. If the SP fails in the middle and close / deallocate are not executed,
>will SQL Server close and dealocate the resources of us or the next time th
e
>SP is execute it will throw 'A cursor with the name ... already exists' ?
>According to my tests SQL Server close and dealocate the cursor automaticaly[/color
]
You didn't test well, then. Include the following extra line after the
forst select but before the close command in the stored proc:
SELECT a FROM b
This won't generate an error when creating the proc, but will generate
an error when executing it. Now execute the proc twice in a row.
Results:
(first execution)
ContactName
----
Maria Anders
CursStatlocal CursStatglobal CursStatvariable
-- -- --
-3 1 -3
Server: Msg 208, Level 16, State 1, Procedure TestCursor, Line 22
Invalid object name 'B'.
(second execution)
Server: Msg 16915, Level 16, State 1, Procedure TestCursor, Line 10
A cursor with the name 'My_Curs' already exists.
Server: Msg 16905, Level 16, State 1, Procedure TestCursor, Line 12
The cursor is already open.
ContactName
----
Ana Trujillo
CursStatlocal CursStatglobal CursStatvariable
-- -- --
-3 1 -3
Server: Msg 208, Level 16, State 1, Procedure TestCursor, Line 22
Invalid object name 'B'.
Of course, when you declare the cursor to be local, it WILL be closed
and deallocated when the SP fails, since it goes out of scope.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Is this a bug with decimal(19,2)?

Seems like I've run across a place where INT works and MONEY works and DECIMAL(19,2) fails.

So, I have two questions:

* Should it work?

* Is there a better practice to handle optionally missing values, I'd rather have NULL than 0 anyway.

(there is also an xsd that shows the element as optional, but that seems to make no difference).

Apologies if this is kind of a newbie question (outside of the bug), me being an XML newbie.

Thanks.

Josh

Code Snippet

declare @.myxml XML

set @.myxml =

'<root>

<myrec>

<foo>123</foo>

<bar>123</bar>

</myrec>

<myrec>

<foo>234</foo>

</myrec>

</root>'

this works

select

t.rows.query('foo').value('.','int') as foo

from @.myxml.nodes('/root/myrec') as t(rows)

this works

select

t.rows.query('foo').value('.','int') as foo,

t.rows.query('bar').value('.','int') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

this works

select

t.rows.query('foo').value('.','money') as foo,

t.rows.query('bar').value('.','money') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

this fails

select

t.rows.query('foo').value('.','decimal(19,2)') as foo,

t.rows.query('bar').value('.','decimal(19,2)') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

/*

Msg 8114, Level 16, State 5, Line 27

Error converting data type nvarchar to numeric.

*/

Here is how you get a NULL with the value method and all three types (int, money, decimal) if the XPath expression does not find an element:

Code Snippet

declare @.myxml XML

set @.myxml =

'<root>

<myrec>

<foo>123</foo>

<bar>123</bar>

</myrec>

<myrec>

<foo>234</foo>

</myrec>

</root>'

this works

select

t.rows.value('foo[1]','int') as foo,

t.rows.value('bar[1]','int') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

this works

select

t.rows.value('foo[1]','money') as foo,

t.rows.value('bar[1]','money') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

this works

select

t.rows.value('foo[1]','decimal(19,2)') as foo,

t.rows.value('bar[1]','decimal(19,2)') as bar

from @.myxml.nodes('/root/myrec') as t(rows)

|||

Thanks.

Yes, I've seen that [1] syntax, and it seems to satisfy the value() function's demand for a singleton value, and then I don't need the separate query() method, ... but it seemed odd to me, didn't know how mainstream it was. Well, looks like it just got a lot more mainstream with me, thanks again!

Josh

Is this a BUG in SQL Server 2000?

I get wrong Query results for a very simple Query. The Query is as below:

SELECT RolePages.PageName, RolePages.Allow, RolePages.RoleId, Roles.RoleName
FROM Roles INNER JOIN
RolePages ON RolePages.RoleId = Roles.RoleId
WHERE (Roles.RoleName = 'Anonymous')

WHEN I RUN THE ABOVE QUERY MY RESULTS ARE AS BELOW: ('Allow' is bit type column)

PageNameAllowRoleIdRoleName

Home 1 2Anonymous
Registeration 12Anonymous
SpecialUserMessage 12Anonymous
ForgotPassword 12Anonymous

BUT, WHEN I RUN THE same QUERY IN A STORED PROCEDURE THE RESULTS ARE:

PageNameAllowRoleIdRoleName

Home -1 2Anonymous
Registeration -12Anonymous
SpecialUserMessage -12Anonymous
ForgotPassword -12Anonymous

Can someone please tell me if this is a SQL Server bug and if it can be fixed ? I am using SQL Server 2000 Desktop version.Are you using different clients for this? What client are you using for the stored procedure. Access (and possibly OleDb in general - I do not know) uses -1 for true. TO be safe, I would alsways check for !=0 (or <>0 for you VB types).|||I am running the query in VS 2003 and seeing the results. Then I run the stored procedure containing this query in VS 2003. So its a straight run of the query/ stored procedure directly from the database. Is this what you were asking ?|||Never check for 1 or -1. Always check for 0. ADO and ADO.net use -1 for true and they convert SQL's 1 to -1 when you use either to access data from SQL. SQL uses 1 for true. They both use 0 for false, so use that.|||OK. Thanks for the help. I think, the idea is that anything other than '0' is treated as a 'True'. So then one could say even if the query return's a '1' and the stored procedure for the same query returns a '-1', they both will be considered 'True'. That makes the results of the query and the query inside a stored procedure 'consistent'.

Thanks once again to all who helped clarify this.

is this a Bug in SQL Server 2000 ?

Here are 2 Linked server creation scripts
EXEC sp_addlinkedserver
@.server='baanb',
@.srvproduct='Oracle in OraHome92',
@.provider='MSDASQL',
@.datasrc='baan'
go
exec sp_addlinkedsrvlogin 'baanb',false,null,'pass','pass'
go
EXEC sp_addlinkedserver
@.server='baan',
@.srvproduct='Oracle in OraHome92',
@.provider=MSDAORA,
@.datasrc='baan'
go
exec sp_addlinkedsrvlogin 'baan',false,null,'pass','pass'
go
When i run these Queries
select count(*) from openquery(baanb,'select t$dsca from baan.ttiitm001400')
select count(*) from openquery(baan,'select t$dsca from baan.ttiitm001400')
I got (33732) rows from baanb
and (33831) rows from baan.
They r the same Oracle Server/same database/ same Tables but different Row
Counts Always with 99 rows difference.
But When i add this linked servers In SQL server 7.0 and run the same
queries. I got the same row count.
Any Explanations ?
Is It a bug ?
> Here are 2 Linked server creation scripts
> EXEC sp_addlinkedserver
> @.server='baanb',
> @.srvproduct='Oracle in OraHome92',
> @.provider='MSDASQL',
> @.datasrc='baan'
> go
> exec sp_addlinkedsrvlogin 'baanb',false,null,'pass','pass'
> go
> EXEC sp_addlinkedserver
> @.server='baan',
> @.srvproduct='Oracle in OraHome92',
> @.provider=MSDAORA,
> @.datasrc='baan'
>
> go
> exec sp_addlinkedsrvlogin 'baan',false,null,'pass','pass'
> go
>
> When i run these Queries
> select count(*) from openquery(baanb,'select t$dsca from
baan.ttiitm001400')
> select count(*) from openquery(baan,'select t$dsca from
baan.ttiitm001400')
> I got (33732) rows from baanb
> and (33831) rows from baan.
> They r the same Oracle Server/same database/ same Tables but different Row
> Counts Always with 99 rows difference.
> But When i add this linked servers In SQL server 7.0 and run the same
> queries. I got the same row count.
>
> Any Explanations ?
> Is It a bug ?
>
It could be a bug with a client driver. On the first one you're using an
Oracle ODBC driver in conjunction with the Microsoft OLE DB Provider for
ODBC. On the second one, you're using the Microsoft OLE DB Provider for
Oracle.
Which linked server returned the correct result? Have you tried using a
third-party OLE DB provider for Oracle and compare the results?
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

is this a Bug in SQL Server 2000 ?

Here are 2 Linked server creation scripts
EXEC sp_addlinkedserver
@.server='baanb',
@.srvproduct='Oracle in OraHome92',
@.provider='MSDASQL',
@.datasrc='baan'
go
exec sp_addlinkedsrvlogin 'baanb',false,null,'pass','pass'
go
EXEC sp_addlinkedserver
@.server='baan',
@.srvproduct='Oracle in OraHome92',
@.provider=MSDAORA,
@.datasrc='baan'
go
exec sp_addlinkedsrvlogin 'baan',false,null,'pass','pass'
go
When i run these Queries
select count(*) from openquery(baanb,'select t$dsca from baan.ttiitm001400')
select count(*) from openquery(baan,'select t$dsca from baan.ttiitm001400')
I got (33732) rows from baanb
and (33831) rows from baan.
They r the same Oracle Server/same database/ same Tables but different Row
Counts Always with 99 rows difference.
But When i add this linked servers In SQL server 7.0 and run the same
queries. I got the same row count.
Any Explanations ?
Is It a bug ?> Here are 2 Linked server creation scripts
> EXEC sp_addlinkedserver
> @.server='baanb',
> @.srvproduct='Oracle in OraHome92',
> @.provider='MSDASQL',
> @.datasrc='baan'
> go
> exec sp_addlinkedsrvlogin 'baanb',false,null,'pass','pass'
> go
> EXEC sp_addlinkedserver
> @.server='baan',
> @.srvproduct='Oracle in OraHome92',
> @.provider=MSDAORA,
> @.datasrc='baan'
>
> go
> exec sp_addlinkedsrvlogin 'baan',false,null,'pass','pass'
> go
>
> When i run these Queries
> select count(*) from openquery(baanb,'select t$dsca from
baan.ttiitm001400')
> select count(*) from openquery(baan,'select t$dsca from
baan.ttiitm001400')
> I got (33732) rows from baanb
> and (33831) rows from baan.
> They r the same Oracle Server/same database/ same Tables but different Row
> Counts Always with 99 rows difference.
> But When i add this linked servers In SQL server 7.0 and run the same
> queries. I got the same row count.
>
> Any Explanations ?
> Is It a bug ?
>
--
It could be a bug with a client driver. On the first one you're using an
Oracle ODBC driver in conjunction with the Microsoft OLE DB Provider for
ODBC. On the second one, you're using the Microsoft OLE DB Provider for
Oracle.
Which linked server returned the correct result? Have you tried using a
third-party OLE DB provider for Oracle and compare the results?
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.