Showing posts with label following. Show all posts
Showing posts with label following. Show all posts

Friday, March 30, 2012

prediction in ms sql server 2005

Hey

Does anyone know if the following is possible:

I want to add a column to a table that contains the predicted value according to a decision tree mining model. (I know that this is possible). But now I would like that when a new row is added to this table, and every column except the prediction column is filled in manually, can ms sql server add the predicted value automatically for this row?
I know it is possible to execute a Singleton query for this kind of single prediction, but I would like to integrate this in my data table, because for now my steps would be:
- Create the table with one prediction column
- Add the known values of all columns for one row
- Use singleton query in Mining model prediction tab to know the predicted value
- Fill in the predicted value manually in my table.

I hope my question is clear.

Thanks in advance for the help.

SmileykeYou could probably do this with an INSERT trigger on your SQL Server database table that makes a singleton prediction query via a linked server to the AS server that holds your mining model.|||And how would this query look like?
I mean, you put me in the right direction I think, but I can't make it work.

Thnx|||Please see this article I just posted for details on how to do this: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx|||Ok, I tried this, and it was very helpful, but it still doesn't work here.

Do you have any idea why the first query here works, but the second one doesn't? The error is given at the end:

1st working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings from [Abalone Training Half]')

2nd not working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

The error is:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "DMServer" reported an error. The provider did not give any information about the error.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T" against OLE DB provider "MSOLAP" for linked server "DMServer".

As you notice, the predicted class is here Rings, and the input attributes are sex, length diameter and height.

I really hope you can still help me.

Smileyke|||

I may be wrong, but the Sex column seems TEXT. In this case, shouldn't "SELECT I AS Sex" be actually "SELECT 'I' AS Sex" ?

In this case, your OPENQUERY should look like below (2 single quotes around I )

SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT ''I'' AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

|||Thank you. That was indeed the problem.

Now the complete trigger works, so thank you all.

smileyke

Predict Probability in Decision Trees

Hello,

I installed the bike buyer example and i am learning the DMX language. Now i wrote the following query (using MS decision trees):

SELECT
T.[Last Name],
[Bike Buyer],
PredictProbability(Predict([Bike Buyer])) AS [Probability]
From
[v Target Mail]
PREDICTION JOIN
OPENQUERY
(....... And so on..)

Now the result is surprising to me. In the resulttabel all the probabilities are equal.

Bike Buyer Probability
1 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
1 0.99994590500919611

and so on.

Now i am wondering what predictProbability means. I thought that PredictProbability meant the probability that the prediction is correct. Now all the probabilities are the same and the input is different. Can somebody tell me what PredictProbability means or am I using it wrong?

Thanx in advance,

Joris Valkonet

This is an interesting query - I would write it as "PredictProbability([Bike Buyer])" however, the syntax is semantically the same. Another thing to try is PredictNodeId([Bike Buyer]) to see exactly the node used for the prediction and then check that node to see the distribution.

It's possible that PredictProbability(Predict([Bike Buyer])) is exposing a bug and you should use the simpler syntax above.

HTH

-Jamie

|||

Thank you for your reply.
I modified my query to PredictProbability([bike buyer]) and this made no difference. The result is the same.

Then I checked the PredictNodeId([Bike Buyer]) and this is the result:

FirstName

LastName

bike buyer

Expression

Abby

Malhotra

1

000000003

Abby

Prasad

0

000000003

Abby

Rodriguez

0

000000003

Abby

Srini

0

000000003

Abigail

Brown

0

000000003

Abigail

Bryant

1

000000003

Abigail

Davis

0

000000003

Abigail

Flores

0

000000003

Now the nodeId is the rootnode of the tree and the probability of the root node is indeed the value 0.99994590500919611. So if I understand this correct, the rootnode of the decision tree is used for all probability predictions. Is this correct?

(Maybe my query is incorrect and therefore the whole DMX query below)

SELECT
t.[FirstName],
t.[LastName],
[bike buyer],
PredictNodeId([bike buyer]) AS [Predicted NodeId]
From
[v Target Mail]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],
'SELECT
[Gender],
[FirstName],
[MiddleName],
[LastName],
[BirthDate],
[MaritalStatus],
[EmailAddress],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned],
[AddressLine1],
[AddressLine2],
[Phone]
FROM
[dbo].[ProspectiveBuyer]
') AS t
ON
[v Target Mail].[First Name] = t.[FirstName] AND
[v Target Mail].[Middle Name] = t.[MiddleName] AND
[v Target Mail].[Last Name] = t.[LastName] AND
[v Target Mail].[Birth Date] = t.[BirthDate] AND
[v Target Mail].[Marital Status] = t.[MaritalStatus] AND
[v Target Mail].[Gender] = t.[Gender] AND
[v Target Mail].[Email Address] = t.[EmailAddress] AND
[v Target Mail].[Yearly Income] = t.[YearlyIncome] AND
[v Target Mail].[Total Children] = t.[TotalChildren] AND
[v Target Mail].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[v Target Mail].[House Owner Flag] = t.[HouseOwnerFlag] AND
[v Target Mail].[Number Cars Owned] = t.[NumberCarsOwned] AND
[v Target Mail].[Address Line1] = t.[AddressLine1] AND
[v Target Mail].[Address Line2] = t.[AddressLine2] AND
[v Target Mail].[Phone] = t.[Phone]

|||

Something wierd is happening here - Predict([Bike Buyer]), which is equivalent to saying just [Bike Buyer], returns the highest probability value for the prediction. That means that whatever node the tree goes to, it will return the highest probability state for that node. I can't see how you are getting different prediction results from the same node.

However, something else struck me as odd. The predict probability of 0.999... doesn't seem right. In fact the only time I have ever seen anything like this is when all the data at the node was missing, and the actual non-missing values only recieved the bayesian prior. By default prediction never returns missing (if you are asking for a value, it assumes you want an actual value). If you do a Predict([Bike Buyer], INCLUDE_MISSING) you will get predictions that include the missing value. If there were no observations in the training data of the states 1 and 0 then they would only have their prior probabilities which would be equal and then the choice of a state would be arbitrary.

What confuses me is that PredictProbabililty should behave the same way.

Can you do a SELECT FLATTENED NODE_DISTRIBUTION FROM [v Target Mail].CONTENT and post the results?

Thanks

|||

Thanks for your analysis. I tried to post the whole result of the query "SELECT FLATTENED NODE_DISTRIBUTION FROM [v Target Mail].CONTENT ", but then an unknown error occurs. I think because the resulttable is to big. So... i will give the first top rows of the result.

Thanks....

ATTRIBUTE_NAME

ATTRIBUTE_VALUE

SUPPORT

PROBABILITY

VARIANCE

.VALUETYPE

Bike Buyer

Missing

0

0.000432432

0

1

Bike Buyer

0.494048907

18484

0.999567568

0.249964584

3

Birth Date

5.45E-06

0

0

16892175

7

Birth Date

0.080783333

0

0

0

8

Birth Date

22673.85945

0

0

16892175

9

Date First Purchase

-0.000941648

0

0

76769.40019

7

Date First Purchase

3242.190525

0

0

0

8

Date First Purchase

37852.25763

0

0

76769.40019

9

Number Cars Owned

-0.079859089

0

0

1.295870198

7

Number Cars Owned

301.5988757

0

0

0

8

Number Cars Owned

1.502705042

0

0

1.295870198

9

Number Children At Home

-0.015796323

0

0

2.318367003

7

Number Children At Home

9.530872281

0

0

0

8

Number Children At Home

1.004057563

0

0

2.318367003

9

Total Children

-0.003992138

0

0

2.599718694

7

Total Children

2.60E+01

0

0

0

8

Total Children

1.844351872

0

0

2.599718694

9

Yearly Income

2.02E-06

0

0

1042319181

7

Yearly Income

116.9361238

0

0

0

8

Yearly Income

57305.77797

0

0

1042319181

9

36.04143269

0

0

0.166743652

11

Bike Buyer

Missing

0

0.000205761

0.00E+00

1

Bike Buyer

1

4858

0.999794239

0

3

1

0

0

5.14E-07

11

Bike Buyer

Missing

0

0.000536481

0

1

Bike Buyer

0.507518797

1862

0.999463519

0.249943468

3

Date First Purchase

-0.010374704

0

0

1160.841795

7

Date First Purchase

679.5475673

0

0

0

8

Date First Purchase

37822.50644

0

0

1160.841795

9

Geography Key

0.000170418

0

0

36083.71548

7

Geography Key

1.990727272

0

0

0

8

Geography Key

236.4581096

0

0

36083.71548

9

Number Cars Owned

-0.072103841

0

0

1.254812169

7

Number Cars Owned

2.37E+01

0

0

0

8

Number Cars Owned

1.523630505

0

0

1.254812169

9

Yearly Income

1.29E-06

0

0

1131929217

7

Yearly Income

7.102048444

0

0

0

8

Yearly Income

60633.72718

0

0

1131929217

9

392.8960717

0

0

0.113511443

11

Bike Buyer

Missing

0

0.000123274

0

1

Bike Buyer

0.256103576

8110

0.999876726

0.190514534

3

|||

I see the problem - you have Bike Buyer modeled as continuous where it should be discrete. PredictProbability was giving you the probability that BikeBuyer exists in this case. To get the confidence of the prediction for continuous predictions, you want to use PredictStdev.

Essentially what was happening was that the model was creating a linear regression to predict bike buyer rather than a classification model. Changing the content type to "Discrete" will fix your issue. If you are creating the model from the source table (rather than a cube), you can click on "Detect" on the data types page of the wizard and it will automatically determing that Bike Buyer should be discrete.

Wednesday, March 28, 2012

Pre-defined lists of values as report parameter input

Has anyone come up with a solution for the following scenario yet?
We have many reports which have input parameters that are lists of values.
As an example the user might want to report on 30 of 100 available product
codes, or 12 of 50,000 account numbers.
What might be an idea would be to store the lists of values that userâ's want
to report on, in a general purpose Lists table. One column would be a List
Name and the other a delimited list of values. This List table could then be
used as the input to a Drop Down box for the relevant report parameter.
Has anybody tried something similar, or does anyone have any other
suggestions?
(Weâ're aware that multi-select list boxes will be available in SSRS 2005,
but that would still be a bit tiresome for a user that needs to select a few
values from a long list).
Regards,
John MarshHi John,
I have done similar thing like that.
i have a lookup parameter report which is a webpage with listbox and the
value is populated by calling a param report. The param report contain 2
fileds and it like name- value pair. The report is called programttically and
the result is returned in xml format so that it can be bind to a dataset and
to tht listbox or dropdown box.
So this act as a lists of values as report parameter input..
Regards
Bava
"John Marsh" wrote:
> Has anyone come up with a solution for the following scenario yet?
> We have many reports which have input parameters that are lists of values.
> As an example the user might want to report on 30 of 100 available product
> codes, or 12 of 50,000 account numbers.
> What might be an idea would be to store the lists of values that userâ's want
> to report on, in a general purpose Lists table. One column would be a List
> Name and the other a delimited list of values. This List table could then be
> used as the input to a Drop Down box for the relevant report parameter.
> Has anybody tried something similar, or does anyone have any other
> suggestions?
> (Weâ're aware that multi-select list boxes will be available in SSRS 2005,
> but that would still be a bit tiresome for a user that needs to select a few
> values from a long list).
> Regards,
> John Marsh
>

predecessor logic

Is there an easy way to do the following?
I have a data load running on a DB/2 mainframe database. When the job
finishes, a message is sent to a Reporting Services system via a text file
that the data load is complete. Iâ'm using SQL Agent to watch for the
creation of the text file and upon creation it starts a RS.EXE job to build a
new SNAPSHOT based on the newly loaded data.
What I want Reporting Services to do upon completion of the SNAPSHOT, which
can take anywhere from 20min to 4 hrs based on the amount of data loaded, is
run a series of reports in order. The problem is there is nothing I can find
that will trigger a report upon the completion of a snapshot creation.
Also, several of the reports have a dependence on a predecessor report. I
can't find any kind of predecessor logic in the RS scheduling.
This logic is easily done in Crystal Enterprise. Iâ'm looking to replace
Crystal Enterprise with RS but I need a clean and easy method to handle
predecessor based scheduling via RS.
--
KurtHi Kurt,
Admittedly, there is not a easy way to do this, however, if you are calling
the UpdateSnapshot SOAP API, there is an RS event that fires when that is
complete. You can use the event to create subscriptions to it. Use this
option on the subscription pane: When the report content is refreshed. This
option is available only for report snapshots
Check the following article for more informaiton
How to create a file share subscription
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm
/hrs_webui_v1_9jky.asp
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
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.

Precision Problem

Hi,

I can't seem to get a stored procedure to return decimal places. Here are the steps to re-create the problem:

--first create the following procedure
CREATE PROCEDURE test_precision
AS
BEGIN
RETURN 5.2
END

--then run the following code
DECLARE @.x DECIMAL(18,2)
EXECUTE @.x = test_precision
SELECT @.x

I would like this code to return 5.20, but instead it returns 5.00. Any assistance would be greatly appreciated. Thanks.if you are using SQL 2005 that is the problem. Your stored Procedure would work correctly in SQL 2000.

The work around
declare @.x FLOAT(18,2)

give that a try.|||ooops!! allow me to take that back - wrong thought process. sorry.

you need to edit your proc to read 5.20sql

Precedence Constrains

Hi there!

I've a few tasks in my dataflow. Some of them are executed depending on an precedence constrain. Works fine.

The problem is following: I want to write a success message (execute SQL task), when all the tasks who have been executed have done this with success. For example, i ve ten tasks, and 2 of them are executed (successfully). This is good, i want to execute my task to write the success message.

I tried to set these precedence constraints with EvalOP = ExpressionOrConstraint, Values = Success and an expression, what returns TRUE, when the task above is not executed (the condition from a precedence constraint above). But the task to write my message will never be executed, because it seems the task does'nt give any result (neither success nor failure) when they ar not executed, and so my AND-Constraint for the success message task will only get TRUE, when ALL task are executed (and successful).

Is there any chance to get somethink like "isSuccess OR IsNotExecuted" into a precedence constraint?

Thanks, Torsten

Put all the tasks in a Sequence Container and then have a precedence constraint to the SQL Task with an on success constraint.|||

I had the same idea, and it seems to be the only workaround. But in these way it is not possible to realize an individual errorhandling of each task.

I think i can live with that. Thanks a lot,

Torsten

Prebuild HD configuration question

I'm in the process of building a new SQL box. I have the following
configuration in mind and was wondering the best way to appoint the
hard drives. It's an HP DL585 with 4 processors. I'm going to put 4
72gb 15k drives in the internal slots in a raid 0/1 configuration. I
also will have an external drive enclosure that will house 14 36gb 15k
drives. I'm planning on putting the OS and SQL program files along
with logs on the internal drives. For the external enclosure what will
give me better performance: Raid 5 with 13 drives + 1 spare or Raid
0/1 with the 14 drives? I'm planning on putting the data files on the
external enclosure.A RAID 0+1 or 1+0(recommended over 0+1) will always outperform a comparable
Raid 5 when writes are concerned. What will work best for you depends on how
you will use your db(s). If disk space is not an issue I would recommend
going with a RAID 1+0.
--
Andrew J. Kelly SQL MVP
"BCole8888" <nospambc@.comcast.net> wrote in message
news:1106844697.121710.214580@.c13g2000cwb.googlegroups.com...
> I'm in the process of building a new SQL box. I have the following
> configuration in mind and was wondering the best way to appoint the
> hard drives. It's an HP DL585 with 4 processors. I'm going to put 4
> 72gb 15k drives in the internal slots in a raid 0/1 configuration. I
> also will have an external drive enclosure that will house 14 36gb 15k
> drives. I'm planning on putting the OS and SQL program files along
> with logs on the internal drives. For the external enclosure what will
> give me better performance: Raid 5 with 13 drives + 1 spare or Raid
> 0/1 with the 14 drives? I'm planning on putting the data files on the
> external enclosure.
>

Monday, March 26, 2012

PRB: "use database" not working after "create database"

PRB: "use database" not working after "create database"
Please help,
I have the following query:
set XACT_ABORT on
begin transaction
create database MY_DB
use MY_DB
commit transaction
The "use" statement fails saying the database does not exists, but I get no
error on the "create". And when I go to the server, sure enough the DB is no
t
there, which it should not be if the TX rolled back. So what is wrong? If th
e
"create" is bad, why do I not see an error on it?You will not see it until you commit the transaction.
set XACT_ABORT on
begin transaction
create database MY_DB
commit transaction
use MY_DB
AMB
"ATS" wrote:

> PRB: "use database" not working after "create database"
> Please help,
> I have the following query:
> set XACT_ABORT on
> begin transaction
> create database MY_DB
> use MY_DB
> commit transaction
> The "use" statement fails saying the database does not exists, but I get n
o
> error on the "create". And when I go to the server, sure enough the DB is
not
> there, which it should not be if the TX rolled back. So what is wrong? If
the
> "create" is bad, why do I not see an error on it?|||Thanks for the reply, but that doesn't work in Query Analyzer.|||Try,
use master
go
create database MY_DB
go
select
*
from
sysdatabases
where
[name] = 'MY_DB'
go
drop database MY_DB
go
AMB
"ATS" wrote:

> Thanks for the reply, but that doesn't work in Query Analyzer.|||One other thing I've noticed. Even with or without TX handling it fails in
Query Analyzer.sql

Friday, March 23, 2012

power and ^

Hi,
I am try to replicate an access query that contains the following syntax
173.3^2 which in access = 17.33
the access help describes the ^ operator as Used to raise a number to the power of an exponent.

I tried to replicate this with the power function
power(173.3,2) = 33043.29
so i guess power is not the right way to replicate this.
can any tell me how i can replicate this correctly ?Access also return the same result as power() function. What is the logic behind here.|||It turns out that it was the way that access priorities it's mathematic operations,
it did a ^ before a division.
so 173.3/10^2 results in 173.3 /100.
i had written the t-sql code as power(173.3/10,2) when it should have been 173.3/ power(10,2)
so my own fault did not read the code right.sql

Wednesday, March 21, 2012

Posting an Image to SQL Server 2005

Hello Everyone I am trying to write image files to sql server using the following code. I have recieved the following error message

Failed to convert parameter value from a String to a Byte[]. Please help. I am sure it is a problem gathering the iostream. I am not very familiar. Any help is gretaly appreciated.

Thanks in advance

Here is the code:

'Dim User As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)

'Dim t As TextBox = DirectCast(FormView1.FindControl("aspnet_UserID"), TextBox)

Dim objConnAs SqlConnection

Dim objComAs SqlCommand

IfMe.FileUpload1.HasFileThen

Dim fileExtensionAsString

Dim fileOKAsBoolean =False

fileExtension = System.IO.Path. _

GetExtension(FileUpload1.FileName).ToLower()

Dim allowedExtensionsAsString() = _

{".jpg",".jpeg",".png",".gif",".mwv"}

For iAsInteger = 0To allowedExtensions.Length - 1

If fileExtension = allowedExtensions(i)Then

fileOK =True

EndIf

Next

'Try

Dim imagestreamAs System.IO.Stream = FileUpload1.FileContent

Dim data()AsByte

ReDim data(imagestream.Length - 1)

imagestream.Read(data, 0, imagestream.Length)

imagestream.Close()

objConn =New SqlConnection(strNewConnection)

objCom =New SqlCommand("insert into ProfileImagesAndDocs(aspnet_userid,img_name,img_data,img_contenttype)values(@.aspnet_userid,@.imagename,@.Picture,@.CategoryName)", objConn)

'--------------

'this is the aspnet_userid

Dim useridparameterAs SqlParameter =New SqlParameter("@.aspnet_userid", SqlDbType.VarChar)useridparameter.Value =Me.aspnet_userid.Text

objCom.Parameters.Add(useridparameter)

'--------------

'This is the image name

Dim imagenameparameterAs SqlParameter =New SqlParameter("@.imagename", SqlDbType.VarChar)imagenameparameter.Value =Me.FileUpload1.FileName

objCom.Parameters.Add(imagenameparameter)

'--------------

'this is the picture data

Dim pictureParameterAs SqlParameter =New SqlParameter("@.Picture", SqlDbType.Image)

pictureParameter.Value = data

objCom.Parameters.Add(pictureParameter)

'--------------

'this is the profile area or category i.e. video intro

Dim categorynameParameterAs SqlParameter =New SqlParameter("@.CategoryName", SqlDbType.VarChar)

pictureParameter.Value ="IntroVideo"

objCom.Parameters.Add(categorynameParameter)

objConn.Open()

objCom.ExecuteNonQuery()

objConn.Close()

'bookmark

Label1.Text ="File uploaded!"

'Catch ex As Exception

Label1.Text ="File could not be uploaded."

EndIf

Hi,

You may use FileStream to read the image content and save it as SqlDbType.Image into your database. See the following sample.

Private Function GetFile()As String''' GetFileDim fileAs HttpPostedFile = File1.PostedFilefileName = file.FileNameReturn fileNameEnd Function''' ReadFilePrivate Function ReadFile()As Byte()Dim fileAs FileStream = File.OpenRead(GetFile())Dim contentAs Byte() =New Byte(file.Length - 1) {}file.Read(content, 0, content.Length)file.Close()Return contentEnd FunctionPrivate Sub WriteImage()Dim commAs SqlCommand = conn.CreateCommand()comm.CommandText ="insert into images(image,type) values(@.image,@.type)"comm.CommandType = CommandType.TextDim paramAs SqlParameter = comm.Parameters.Add("@.image", SqlDbType.Image)param.Value = ReadFile()param = comm.Parameters.Add("@.type", SqlDbType.NVarChar)param.Value = GetContentType(New FileInfo(fileName).Extension.Remove(0, 1))If comm.ExecuteNonQuery() = 1ThenResponse.Write("Successful")ElseResponse.Write("Fail")End Ifconn.Close()End SubPrivate Function GetContentType(ByVal extensionAs String)As StringDim typeAs String =""If extension.Equals("jpg")OrElse extension.Equals("JPG")Thentype ="jpeg"Elsetype = extensionEnd IfReturn"image/" + typeEnd Function

Thanks.
|||

Thanks the code above worked very well. I am having one other difficulty when I select a file with a *.wmv extension the file is not read and the script halts. Any Ideas?! and thanks again for the solution.

Post to HTTP With paramerters Options

Hi,

Please could some of the experts out there advise on the best way to acheive the following please. . .

In a SQL table, I have a field that when it drops to below 5, i want to automatically run a Stored Procedure that then connects to a URL to run a ASP.NET script.

IE:
In table users, there is a field called Money.
When Money reaches < 5
Run Stored Procedure
The Stored Procedure then 'somehow' sends a trigger to a URL with
paramerters as per below
www.mydomainname.com?money=4&name=peter

Any help/guidence appriciated as i am really lost on where to start!

Thanks
H

Create a triggered event on your table that does something when the 'money' column changes.

CREATE TRIGGER trgMoneyUpd

ON users

FOR UPDATE

AS

IF UPDATE(Money)

... call your ESP ...

END

Create an extended stored procedure to construct the string you want and open the URL.

http://msdn2.microsoft.com/en-US/library/aa197263(SQL.80).aspx

Hope that helps,

John (MSFT)

Remember to mark your question as answered if it is answered.

|||

Thanks for that John;

Now i have the trigger part working fine, but for the life of me i cannot seem to get my head around the extended stored procedure;

Does anyone have a simple sample they could provide that enables me to do what i want?

Thanks

Harry

|||Hi,

nothing for your solution about the extended stored procedure, but concerning the trigger, you should see that it is able to handle multiple affected rows and no affected rows (a trigger is fired regardless if a row is affected or not) , as the above mentioned one does not do that.

Jens K. Suessmeyer

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

Harry,

The trigger can call into a CLR stored procedure. Inside the CLR stored procedure you would write some .Net Framework code which will call out to the URL. Something like the following:

Code Snippet

using System;
using System.Net;

namespace InSrvWebClient
{
public class MyHttpClient
{
public static String CallWebURL()
{
String retValue = "";
String resText = "";
System.IO.Stream httpReqBodyStream;
System.IO.Stream httpResBodyStream;
System.IO.StreamWriter httpReqBody;
System.IO.StreamReader httpResBody;
System.Text.StringBuilder httpReqString;

httpReqString = new System.Text.StringBuilder(10);

try {
// Create HTTP connection
System.Net.HttpWebRequest httpClient = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(@."http://www.mydomainname.com?money=4&name=peter");

// Set network credentials if needed

/*
String strUserName = @."myUser";

String strUserPwd = @."pwd";
System.Net.NetworkCredential myCred = new System.Net.NetworkCredential(strUserName, strUserPwd);

System.Net.CredentialCache netCreds = new System.Net.CredentialCache();
netCreds.Add(new Uri(@."www.mydomainname.com?money=4&name=peter");

// Set network credentials if needed

/*
String strUserName = @."myUser";

String strUserPwd = @."pwd";
System.Net.NetworkCredential myCred = new System.Net.NetworkCredential(strUserName, strUserPwd);

System.Net.CredentialCache netCreds = new System.Net.CredentialCache();
netCreds.Add(new Uri(@."http://www.mydomainname.com"), "NTLM", myCred);
netCreds.Add(new Uri(@."http://www.mydomainname.com"), "Digest", myCred);
netCreds.Add(new Uri(@."http://www.mydomainname.com"), "Kerberos", myCred);

*/

// Set Request settings
//httpClient.Credentials = netCreds;
//httpClient.ContentType = "text/xml; charset=utf-8";
httpClient.KeepAlive = true;
httpClient.Method = "GET"; // "POST"
//httpClient.PreAuthenticate = true;
httpClient.ProtocolVersion = System.Net.HttpVersion.Version11;
httpClient.Timeout = 120000;

try {
// Get and write request body stream
//httpReqBodyStream = httppClient.GetRequestStream();
//httpReqBody = new System.IO.StreamWriter(httpReqBodyStream, System.Text.Encoding.UTF8);
//httpReqBody.Flush();
//httpReqBody.Write(soapReqString.ToString());
//httpReqBody.Close();

// Get and parse response body stream
System.Net.HttpWebResponse httpRes = (System.Net.HttpWebResponse) httpClient.GetResponse();
if (httpRes.StatusCode == System.Net.HttpStatusCode.OK)
{
httpResBodyStream = httpRes.GetResponseStream();
httpResBody = new System.IO.StreamReader(httpResBodyStream, System.Text.Encoding.UTF8);
resText = httpResBody.ReadToEnd();

// parse any responses as necessary
}
else
retValue = httpRes.StatusCode.ToString() + ": " + httpRes.StatusDescription;
httpRes.Close();
}
catch (ProtocolViolationException protE)
{
retValue = protE.ToString();
}
}
catch (WebException webE)
{
retValue = webE.ToString();
}
return retValue;
}
}
}

You will then need to register the assembly (dll) with SQL Server:

Code Snippet

create assembly testDll from '' WITH permission_set = external_access
go

CREATE FUNCTION InProcWebRequest()
RETURNS nvarchar(4000)
AS EXTERNAL NAME testDll.[InSrvWebClient.MyHttpClient].CallWebURL
go

For more information on creating a CLR stored procedure please see: http://msdn2.microsoft.com/en-us/library/5czye81z(VS.80).aspx

HTH,

Jimmy

Tuesday, March 20, 2012

Post Hotfix error

I am getting the following (MOM) error on 3 of my databases anytime I initiate a backup of the mdf. The database seems to backup as expected. The one thing that has recently changed is I implemented MS Hotfix 2187 on my SQl cluster over the weekend.

Severity: Critical Error

Status: New

Source: MSSQLSERVER

Name: Commit record has already been distributed. Check DBTABLE

Description: Unable to expand message 17055 [-1073724769] 18264 Database backed up: Database: XX03PLUS, creation date(time): 2005/12/06(18:38:50), pages dumped: 23026, first LSN: 18761:44:1, last LSN: 18761:46:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'G:\temp\ar03PLUS_fromVCforK1.bak'}).

thanks in advance,

Jerry

I thought I would reply to my own post as I have remediated the error in case someone else ever has this issue.

AS it turns out the Hotfix 'upgrade' can create orphaned users and or users with 'not as expected' permissions. By deinstalling (detaching) and reinstalling (re-attaching) via an internal script that also reassigned appropriate users and permissions I wa able to clean up the errors.

best of luck,

Jerry

Monday, March 12, 2012

Possible todo some form of DISTINCT filtering?

Hi guys, I have a dataset that returns rows that look like the following
field | value
--
1 | 7
1 | 7
2 | 4
8 | 90
Is it possible to remove the duplicate (1 | 7) row directly within a LIST
control (or similar?)? I cannot use a DISTINCT directly in the SQL as
elsewhere I need to display the duplicate row.
Thanks for any help
TazSilly me, thats what grouping is for lol.
Thanks anyways
Taz

Friday, March 9, 2012

Possible to encrypt database assembly?

Hello.

I've built a simple Visual Basic .NET project containing the following code...

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub WhoAmI()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT SUSER_SNAME()", connection)
SqlContext.Pipe.ExecuteAndSend(command)
End Using
End Sub
End Class

From Visual Studio, I want to encrypt the contents of this assembly, as a proof-of-concept.
Even though assembly contents are stored as varbinary(MAX) in the database, converting to varchar(MAX) will expose the code.

However, the Dotfuscator Community Edition reports the following error: "Dotfuscator Community Edition cannot operate on SQL Server applications.... please try Standard or Professional Edition."

Has anyone tried encrypting a database assembly and deploying to the database?

A good test would be to issue the following TSQL script against the database holding the assembly...

-- Does the sample code above run?
EXEC dbo.WhoAmI
GO
-- Is the code readable?
SELECT * , Convert(varchar(MAX), content) FROM sys.assembly_files

Hi,

There is no explicit support for encrypted assemblies in the database. You can use a code obfuscation utility to make reverse-engineering difficult, as you suggest.

Perhaps someone else has a favorite obfuscator they can recommendI don't know much about them.

Cheers,

-Isaac

|||

Note that the code you're reading from sys.assembly_files isn't related to encrypting the assembly - VS deploys your full source code and PDB files to the database to assist with debugging. If you don't want VS to do this, then you can turn it off under the project properties / Deploy / Deploy Code option. This still won't protect your assembly though as anyone can use a reflection tool such as http://www.denisbauer.com/NETTools/SQL2005Browser.aspx. Obfuscation can help here, but the real solution is to control who has access to the system views containing the assembly using metadata permissions.

Steven

|||A great answer... You've given lots of new leads. Thanks.

Wednesday, March 7, 2012

Possible to disable welll-formedness checking when inserting XML data.

Hi All,

Can anyone help with the following problem? I have a database which
contains a table with a 'text' field, and the text field contains an
xml document - typically 50-100K. Now I'd like to make use of SQL
Server 2005s XMLData type. To do this I have created a new field of
the 'xmldata' datatype, and run an SQL statement to update the contents
from one field to another - hoping to end up with a complete table of
xml (based on the old text field).

The problem I have is after a minute or so, it must come across an
badly-formed xml fragment because I get the following message:

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 1, character 67640, end tag does not match start tag

Can I turn off the checking during the update, or is it not possible to
add badly formed data to the xmldata field. Any help appreciated as
the table runs into tens of thousands of rows, so I can't really check
the contents of each!

Many thanks,

Duncan.(DSmith1974@.googlemail.com) writes:
> Can anyone help with the following problem? I have a database which
> contains a table with a 'text' field, and the text field contains an
> xml document - typically 50-100K. Now I'd like to make use of SQL
> Server 2005s XMLData type. To do this I have created a new field of
> the 'xmldata' datatype, and run an SQL statement to update the contents
> from one field to another - hoping to end up with a complete table of
> xml (based on the old text field).
> The problem I have is after a minute or so, it must come across an
> badly-formed xml fragment because I get the following message:
> Msg 9436, Level 16, State 1, Line 1
> XML parsing: line 1, character 67640, end tag does not match start tag
> Can I turn off the checking during the update, or is it not possible to
> add badly formed data to the xmldata field. Any help appreciated as
> the table runs into tens of thousands of rows, so I can't really check
> the contents of each!

Indeed, you can only pass valid XML fragments to the xml data type. They
don't have to be valid documents, that is have exactly one top-level tag,
but apart from that they must follow the XML syntax. The reason is that
the XML is stored an internal format, so SQL Server have no idea of what
do with the poorly formed XML.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Saturday, February 25, 2012

Possible causes for "login failed for user 'xyz' "

What are some of the possible causes for the following error :
Login failed for user 'xyz' assuming we have mixed mode of authentication on
SQL 2K.
I can think of just an incorrect password entered. Are there any more
reasons ?I'd think there will be some more detail in the error message. Is that all,
you are getting?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OeEWPGEkDHA.1808@.TK2MSFTNGP09.phx.gbl...
What are some of the possible causes for the following error :
Login failed for user 'xyz' assuming we have mixed mode of authentication on
SQL 2K.
I can think of just an incorrect password entered. Are there any more
reasons ?|||We can get authenticated for some apps. I just have profiled some failed
logins and wanted to know what may some reason be for that error message
seen in text data column
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23fvMIHMkDHA.2416@.TK2MSFTNGP10.phx.gbl...
> I'd think there will be some more detail in the error message. Is that
all,
> you are getting?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> What hardware is your SQL Server running on?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OeEWPGEkDHA.1808@.TK2MSFTNGP09.phx.gbl...
> What are some of the possible causes for the following error :
> Login failed for user 'xyz' assuming we have mixed mode of authentication
on
> SQL 2K.
> I can think of just an incorrect password entered. Are there any more
> reasons ?
>
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OeEWPGEkDHA.1808@.TK2MSFTNGP09.phx.gbl...
> What are some of the possible causes for the following error :
> Login failed for user 'xyz' assuming we have mixed mode of authentication
on
> SQL 2K.
> I can think of just an incorrect password entered. Are there any more
> reasons ?
Connectivity. If it takes a while for the error to appear, consider your
connectivity options.
Kind Regards, Howard

Possible bug: Lose members of server role when changing DataDir server property

Hello,

I think I have found a bug in AS and I would like it confirming before I report it. Please could someone try the following steps (don't do this on a production machine):

    Connect to your Analysis Server in SSMS with a user that is a member of the server role but NOT the user that installed the software

    Right-click on the server in Object Explorer. Select 'Properties...'

    On the general tab, change the value of the DataDir property to a different folder (it doesn't matter what)

    Click 'OK' on "Analysis Server properties" dialog. You will get a message telling you that the server needs to be restarted

    Restart your Analysis Server in Services panel.

    Re-Connect to the Analysis Server in Object Explorer again.

    Right-click on the server in Object Explorer. Select 'Properties...'

You won't be able to see any server properties and if you click on the 'Security' tab you will not be able to see yourself in the list of server role members. In other words...it seems you have been removed from the server role just by changing the data directory. If you try and do anything like deploy to the server you will get an error message "Either the 'username' user does not have permission to create a new object in 'LNVDMFIISDEV01', or the object does not exist."

It gets stranger...

    Log onto the server as the user that originally installed Analysis Services (for this is now the only user that will have server role privelages)

    Change the DataDir back to what it was before. Restart AS.

After that all of the server role members will be back where they were before. Everything as normal.

What gives? Can someone from MSFT confirm that this is a bug? If so I will raise it as such on Microsoft Connect.

Also, with this in mind, what should be the proper procedure for changing the data directory?

Regards

Jamie

I'm on SP1 by the way!

Thanks Jamie for catching this.

Please go ahead and file it.
The problem is indeed requires some attention. Maybe books online should explain better what is going on.

Changing Data directory is very big change. You will be loosing all of your data and metadata. More to it, you need to have physical access to the server machine to make sure new data folder has proper security permissions. It should be granted access to the SQLServer2005MSOLAPUser$MACHINENAME$MSSQLSERVER local group. So that during service account change Analysis Server still has an access to the data folder.

You also have an option to copy entire content of the old datafolder into the new location. In this case you will retain your data and metadata.

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

|||

Hi Edward,

Thanks for the reply. I have raised it on Connect here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=146652

-Jamie

|||

I had a similar problem when trying to change the DataDir. I lost all my cubes in the old DataDir. I changed it back to the old DataDir as per Jamie's instructions to recover. But the cube I restored in the new DataDir is no longer visible. Is there any way to recover this metadata?

Edward, My serivce pack version SP1 is 2047. I saw you note dated 8/24/06 regarding next version? Is the fix planned for SP2?

A hint for others. Change/set your DataDir immediately after install of SQL Server 2005 so that you do not encounter this problem.

|||

I found a way to do this without having the problems. I posted it to my blog, so rather than retype it, here's the link

http://skinnytrail.blogspot.com/2006/08/changing-datadir-in-ssas-sql-2005.html

Possible bug: Lose members of server role when changing DataDir server property

Hello,

I think I have found a bug in AS and I would like it confirming before I report it. Please could someone try the following steps (don't do this on a production machine):

    Connect to your Analysis Server in SSMS with a user that is a member of the server role but NOT the user that installed the software

    Right-click on the server in Object Explorer. Select 'Properties...'

    On the general tab, change the value of the DataDir property to a different folder (it doesn't matter what)

    Click 'OK' on "Analysis Server properties" dialog. You will get a message telling you that the server needs to be restarted

    Restart your Analysis Server in Services panel.

    Re-Connect to the Analysis Server in Object Explorer again.

    Right-click on the server in Object Explorer. Select 'Properties...'

You won't be able to see any server properties and if you click on the 'Security' tab you will not be able to see yourself in the list of server role members. In other words...it seems you have been removed from the server role just by changing the data directory. If you try and do anything like deploy to the server you will get an error message "Either the 'username' user does not have permission to create a new object in 'LNVDMFIISDEV01', or the object does not exist."

It gets stranger...

    Log onto the server as the user that originally installed Analysis Services (for this is now the only user that will have server role privelages)

    Change the DataDir back to what it was before. Restart AS.

After that all of the server role members will be back where they were before. Everything as normal.

What gives? Can someone from MSFT confirm that this is a bug? If so I will raise it as such on Microsoft Connect.

Also, with this in mind, what should be the proper procedure for changing the data directory?

Regards

Jamie

I'm on SP1 by the way!

Thanks Jamie for catching this.

Please go ahead and file it.
The problem is indeed requires some attention. Maybe books online should explain better what is going on.

Changing Data directory is very big change. You will be loosing all of your data and metadata. More to it, you need to have physical access to the server machine to make sure new data folder has proper security permissions. It should be granted access to the SQLServer2005MSOLAPUser$MACHINENAME$MSSQLSERVER local group. So that during service account change Analysis Server still has an access to the data folder.

You also have an option to copy entire content of the old datafolder into the new location. In this case you will retain your data and metadata.

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

|||

Hi Edward,

Thanks for the reply. I have raised it on Connect here: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=146652

-Jamie

|||

I had a similar problem when trying to change the DataDir. I lost all my cubes in the old DataDir. I changed it back to the old DataDir as per Jamie's instructions to recover. But the cube I restored in the new DataDir is no longer visible. Is there any way to recover this metadata?

Edward, My serivce pack version SP1 is 2047. I saw you note dated 8/24/06 regarding next version? Is the fix planned for SP2?

A hint for others. Change/set your DataDir immediately after install of SQL Server 2005 so that you do not encounter this problem.

|||

I found a way to do this without having the problems. I posted it to my blog, so rather than retype it, here's the link

http://skinnytrail.blogspot.com/2006/08/changing-datadir-in-ssas-sql-2005.html

Possible Bug with SQl Server 2000

Hi,
Look at the simple set of SQL statements below: Execute the following on
Query Analyzer.
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.95
exec('select '+@.a+'/'+@.b)
Ans=.6679392
Now, if I change the value of @.b to 36459.96,
I get a value of 0. Integer division is performed just because the value of
@.b increased by .01
This looks like bug with the internal workings of SQL. This is very
dangerous cause basic SQL assumption is that if a value is a float, the
division will also be a float division. Integer division on the other hand
gives erroneous results in this case.
Please solve this problem as soon as possible.
-Ling Yu.
Ling Yu,
This is not a bug, but it is surprising, and in my opinion, the query
should actually cause a syntax error. I'm glad you raised the question,
and I bet other people have been affected by this behavior.
Here's why it occurs:
The query processor must evaluate the query string 'select '+@.a+'/'+@.b
as its first step. When strings are concatenated inside exec(), the +
operator is not a T-SQL + operator. The + is applied by the front-end
application and only after the entire query string is assembled does
that string go to the query processor to be interpreted as T-SQL.
Query Analyzer (or the system it uses to prepare queries for the engine)
follows different rules than T-SQL to interpret the + that puts strings
together within exec(). In T-SQL, the + operation would fail, because
it tries to convert 'select ' and '/' into floats and add. But here, @.a
and @.b are converted to strings. The way in which they are converted to
strings is not obvious, but you can see it if you execute
exec('select ' + @.a) and exec('select ' + @.b)
What's important to realize is that exec() receives only a string,
without any type information about the parameters. Unfortunately,
floats are converted to strings by the front-end using at most 6 decimal
digits of precision. Since @.a is converted in both cases to the string
'24353', how the + in the query (not the concatenation + in the exec) is
understood depends on the string @.b converts to. If @.b is converted to
the string '36459.9', the addition is performed using decimal
arithmetic. If @.b is converted to '36460', the query executed is
select 24353/36460, with 0 as the result. The types of @.a and @.b are
unknown to the query processor, which sees only the string
representations of these numbers.
While at first glance, you might think the select query here recognizes
that it is receiving floats, it is not. exec() can only receive a
string and must interpret the types of numbers from their string
representations. A safer approach is to use sp_executesql, with
parameters, or to add typecasts in your query string.
I hope that helps.
PAI wrote:

>Hi,
>Look at the simple set of SQL statements below: Execute the following on
>Query Analyzer.
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.95
>exec('select '+@.a+'/'+@.b)
>Ans=.6679392
>Now, if I change the value of @.b to 36459.96,
>I get a value of 0. Integer division is performed just because the value of
>@.b increased by .01
>This looks like bug with the internal workings of SQL. This is very
>dangerous cause basic SQL assumption is that if a value is a float, the
>division will also be a float division. Integer division on the other hand
>gives erroneous results in this case.
>Please solve this problem as soon as possible.
>-Ling Yu.
>
|||Thanks Steve. Your answer makes sense but the whole point of exec () now does
not. Why arbitrarily choose 6 precision. The disturbing this is even if you
convert it to a string and do an exec(@.sqlstring), you get the same problem.
For example:
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.96
declare @.sql varchar(1000)
set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
exec('select '+@.a+'/'+@.b)
exec (@.sql)
Logically speaking when i cast 36459.96 to a varchar(100) it should retain
its decimal points and not round it. The only thing that helps is
exec('select cast('+@.a+' as float)/'+@.b)
This is so counterintuitive that its frustrating.
Thanks for your reply though
"Steve Kass" wrote:

> Ling Yu,
> This is not a bug, but it is surprising, and in my opinion, the query
> should actually cause a syntax error. I'm glad you raised the question,
> and I bet other people have been affected by this behavior.
> Here's why it occurs:
> The query processor must evaluate the query string 'select '+@.a+'/'+@.b
> as its first step. When strings are concatenated inside exec(), the +
> operator is not a T-SQL + operator. The + is applied by the front-end
> application and only after the entire query string is assembled does
> that string go to the query processor to be interpreted as T-SQL.
> Query Analyzer (or the system it uses to prepare queries for the engine)
> follows different rules than T-SQL to interpret the + that puts strings
> together within exec(). In T-SQL, the + operation would fail, because
> it tries to convert 'select ' and '/' into floats and add. But here, @.a
> and @.b are converted to strings. The way in which they are converted to
> strings is not obvious, but you can see it if you execute
> exec('select ' + @.a) and exec('select ' + @.b)
> What's important to realize is that exec() receives only a string,
> without any type information about the parameters. Unfortunately,
> floats are converted to strings by the front-end using at most 6 decimal
> digits of precision. Since @.a is converted in both cases to the string
> '24353', how the + in the query (not the concatenation + in the exec) is
> understood depends on the string @.b converts to. If @.b is converted to
> the string '36459.9', the addition is performed using decimal
> arithmetic. If @.b is converted to '36460', the query executed is
> select 24353/36460, with 0 as the result. The types of @.a and @.b are
> unknown to the query processor, which sees only the string
> representations of these numbers.
> While at first glance, you might think the select query here recognizes
> that it is receiving floats, it is not. exec() can only receive a
> string and must interpret the types of numbers from their string
> representations. A safer approach is to use sp_executesql, with
> parameters, or to add typecasts in your query string.
> I hope that helps.
>
> PAI wrote:
>
|||Ling Yu,
While the default conversion from float to string is not very useful,
you can use CONVERT with a format code. Use convert(varchar(100),@.a,2),
and the resulting string will always be in scientific notation with 16
decimal places of accuracy. Then it will always be interpreted as a
float, and never with too-low precision.
SK
PAI wrote:
[vbcol=seagreen]
>Thanks Steve. Your answer makes sense but the whole point of exec () now does
>not. Why arbitrarily choose 6 precision. The disturbing this is even if you
>convert it to a string and do an exec(@.sqlstring), you get the same problem.
>For example:
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.96
>declare @.sql varchar(1000)
>set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
>exec('select '+@.a+'/'+@.b)
>exec (@.sql)
>Logically speaking when i cast 36459.96 to a varchar(100) it should retain
>its decimal points and not round it. The only thing that helps is
>exec('select cast('+@.a+' as float)/'+@.b)
>This is so counterintuitive that its frustrating.
>Thanks for your reply though
>"Steve Kass" wrote:
>

Possible Bug with SQl Server 2000

Hi,
Look at the simple set of SQL statements below: Execute the following on
Query Analyzer.
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.95
exec('select '+@.a+'/'+@.b)
Ans=.6679392
Now, if I change the value of @.b to 36459.96,
I get a value of 0. Integer division is performed just because the value of
@.b increased by .01
This looks like bug with the internal workings of SQL. This is very
dangerous cause basic SQL assumption is that if a value is a float, the
division will also be a float division. Integer division on the other hand
gives erroneous results in this case.
Please solve this problem as soon as possible.
-Ling Yu.Ling Yu,
This is not a bug, but it is surprising, and in my opinion, the query
should actually cause a syntax error. I'm glad you raised the question,
and I bet other people have been affected by this behavior.
Here's why it occurs:
The query processor must evaluate the query string 'select '+@.a+'/'+@.b
as its first step. When strings are concatenated inside exec(), the +
operator is not a T-SQL + operator. The + is applied by the front-end
application and only after the entire query string is assembled does
that string go to the query processor to be interpreted as T-SQL.
Query Analyzer (or the system it uses to prepare queries for the engine)
follows different rules than T-SQL to interpret the + that puts strings
together within exec(). In T-SQL, the + operation would fail, because
it tries to convert 'select ' and '/' into floats and add. But here, @.a
and @.b are converted to strings. The way in which they are converted to
strings is not obvious, but you can see it if you execute
exec('select ' + @.a) and exec('select ' + @.b)
What's important to realize is that exec() receives only a string,
without any type information about the parameters. Unfortunately,
floats are converted to strings by the front-end using at most 6 decimal
digits of precision. Since @.a is converted in both cases to the string
'24353', how the + in the query (not the concatenation + in the exec) is
understood depends on the string @.b converts to. If @.b is converted to
the string '36459.9', the addition is performed using decimal
arithmetic. If @.b is converted to '36460', the query executed is
select 24353/36460, with 0 as the result. The types of @.a and @.b are
unknown to the query processor, which sees only the string
representations of these numbers.
While at first glance, you might think the select query here recognizes
that it is receiving floats, it is not. exec() can only receive a
string and must interpret the types of numbers from their string
representations. A safer approach is to use sp_executesql, with
parameters, or to add typecasts in your query string.
I hope that helps.
PAI wrote:
>Hi,
>Look at the simple set of SQL statements below: Execute the following on
>Query Analyzer.
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.95
>exec('select '+@.a+'/'+@.b)
>Ans=.6679392
>Now, if I change the value of @.b to 36459.96,
>I get a value of 0. Integer division is performed just because the value of
>@.b increased by .01
>This looks like bug with the internal workings of SQL. This is very
>dangerous cause basic SQL assumption is that if a value is a float, the
>division will also be a float division. Integer division on the other hand
>gives erroneous results in this case.
>Please solve this problem as soon as possible.
>-Ling Yu.
>|||Thanks Steve. Your answer makes sense but the whole point of exec () now does
not. Why arbitrarily choose 6 precision. The disturbing this is even if you
convert it to a string and do an exec(@.sqlstring), you get the same problem.
For example:
declare @.a float
declare @.b float
set @.a=24353.02821769137
set @.b=36459.96
declare @.sql varchar(1000)
set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
exec('select '+@.a+'/'+@.b)
exec (@.sql)
Logically speaking when i cast 36459.96 to a varchar(100) it should retain
its decimal points and not round it. The only thing that helps is
exec('select cast('+@.a+' as float)/'+@.b)
This is so counterintuitive that its frustrating.
Thanks for your reply though
"Steve Kass" wrote:
> Ling Yu,
> This is not a bug, but it is surprising, and in my opinion, the query
> should actually cause a syntax error. I'm glad you raised the question,
> and I bet other people have been affected by this behavior.
> Here's why it occurs:
> The query processor must evaluate the query string 'select '+@.a+'/'+@.b
> as its first step. When strings are concatenated inside exec(), the +
> operator is not a T-SQL + operator. The + is applied by the front-end
> application and only after the entire query string is assembled does
> that string go to the query processor to be interpreted as T-SQL.
> Query Analyzer (or the system it uses to prepare queries for the engine)
> follows different rules than T-SQL to interpret the + that puts strings
> together within exec(). In T-SQL, the + operation would fail, because
> it tries to convert 'select ' and '/' into floats and add. But here, @.a
> and @.b are converted to strings. The way in which they are converted to
> strings is not obvious, but you can see it if you execute
> exec('select ' + @.a) and exec('select ' + @.b)
> What's important to realize is that exec() receives only a string,
> without any type information about the parameters. Unfortunately,
> floats are converted to strings by the front-end using at most 6 decimal
> digits of precision. Since @.a is converted in both cases to the string
> '24353', how the + in the query (not the concatenation + in the exec) is
> understood depends on the string @.b converts to. If @.b is converted to
> the string '36459.9', the addition is performed using decimal
> arithmetic. If @.b is converted to '36460', the query executed is
> select 24353/36460, with 0 as the result. The types of @.a and @.b are
> unknown to the query processor, which sees only the string
> representations of these numbers.
> While at first glance, you might think the select query here recognizes
> that it is receiving floats, it is not. exec() can only receive a
> string and must interpret the types of numbers from their string
> representations. A safer approach is to use sp_executesql, with
> parameters, or to add typecasts in your query string.
> I hope that helps.
>
> PAI wrote:
> >Hi,
> >
> >Look at the simple set of SQL statements below: Execute the following on
> >Query Analyzer.
> >
> >declare @.a float
> >declare @.b float
> >set @.a=24353.02821769137
> >set @.b=36459.95
> >exec('select '+@.a+'/'+@.b)
> >
> >Ans=.6679392
> >
> >Now, if I change the value of @.b to 36459.96,
> >I get a value of 0. Integer division is performed just because the value of
> >@.b increased by .01
> >
> >This looks like bug with the internal workings of SQL. This is very
> >dangerous cause basic SQL assumption is that if a value is a float, the
> >division will also be a float division. Integer division on the other hand
> >gives erroneous results in this case.
> >
> >Please solve this problem as soon as possible.
> >
> >-Ling Yu.
> >
> >
>|||Ling Yu,
While the default conversion from float to string is not very useful,
you can use CONVERT with a format code. Use convert(varchar(100),@.a,2),
and the resulting string will always be in scientific notation with 16
decimal places of accuracy. Then it will always be interpreted as a
float, and never with too-low precision.
SK
PAI wrote:
>Thanks Steve. Your answer makes sense but the whole point of exec () now does
>not. Why arbitrarily choose 6 precision. The disturbing this is even if you
>convert it to a string and do an exec(@.sqlstring), you get the same problem.
>For example:
>declare @.a float
>declare @.b float
>set @.a=24353.02821769137
>set @.b=36459.96
>declare @.sql varchar(1000)
>set @.sql='select '+cast(@.a as varchar(100))+'/'+cast(@.b as varchar(100))
>exec('select '+@.a+'/'+@.b)
>exec (@.sql)
>Logically speaking when i cast 36459.96 to a varchar(100) it should retain
>its decimal points and not round it. The only thing that helps is
>exec('select cast('+@.a+' as float)/'+@.b)
>This is so counterintuitive that its frustrating.
>Thanks for your reply though
>"Steve Kass" wrote:
>
>>Ling Yu,
>> This is not a bug, but it is surprising, and in my opinion, the query
>>should actually cause a syntax error. I'm glad you raised the question,
>>and I bet other people have been affected by this behavior.
>>Here's why it occurs:
>>The query processor must evaluate the query string 'select '+@.a+'/'+@.b
>>as its first step. When strings are concatenated inside exec(), the +
>>operator is not a T-SQL + operator. The + is applied by the front-end
>>application and only after the entire query string is assembled does
>>that string go to the query processor to be interpreted as T-SQL.
>>Query Analyzer (or the system it uses to prepare queries for the engine)
>>follows different rules than T-SQL to interpret the + that puts strings
>>together within exec(). In T-SQL, the + operation would fail, because
>>it tries to convert 'select ' and '/' into floats and add. But here, @.a
>>and @.b are converted to strings. The way in which they are converted to
>>strings is not obvious, but you can see it if you execute
>>exec('select ' + @.a) and exec('select ' + @.b)
>>What's important to realize is that exec() receives only a string,
>>without any type information about the parameters. Unfortunately,
>>floats are converted to strings by the front-end using at most 6 decimal
>>digits of precision. Since @.a is converted in both cases to the string
>>'24353', how the + in the query (not the concatenation + in the exec) is
>>understood depends on the string @.b converts to. If @.b is converted to
>>the string '36459.9', the addition is performed using decimal
>>arithmetic. If @.b is converted to '36460', the query executed is
>>select 24353/36460, with 0 as the result. The types of @.a and @.b are
>>unknown to the query processor, which sees only the string
>>representations of these numbers.
>>While at first glance, you might think the select query here recognizes
>>that it is receiving floats, it is not. exec() can only receive a
>>string and must interpret the types of numbers from their string
>>representations. A safer approach is to use sp_executesql, with
>>parameters, or to add typecasts in your query string.
>>I hope that helps.
>>
>>PAI wrote:
>>
>>Hi,
>>Look at the simple set of SQL statements below: Execute the following on
>>Query Analyzer.
>>declare @.a float
>>declare @.b float
>>set @.a=24353.02821769137
>>set @.b=36459.95
>>exec('select '+@.a+'/'+@.b)
>>Ans=.6679392
>>Now, if I change the value of @.b to 36459.96,
>>I get a value of 0. Integer division is performed just because the value of
>>@.b increased by .01
>>This looks like bug with the internal workings of SQL. This is very
>>dangerous cause basic SQL assumption is that if a value is a float, the
>>division will also be a float division. Integer division on the other hand
>>gives erroneous results in this case.
>>Please solve this problem as soon as possible.
>>-Ling Yu.
>>
>>