|
Hello,
I am attempting to create an autocomputed column with persisted values. The column's value is defined by this function:
CREATE FUNCTION [dbo].[GetMinsFromSStart]
(
@DayOfWeekID tinyint,
@Hour tinyint,
@Minute tinyint,
@Second tinyint
)
returns smallint
as
begin
declare @MinsFromSessionStart smallint;
set @MinsFromSessionStart = 420 + (@DayOfWeekID - 1) * 1440 + @Hour * 60 + @Minute;
if (@Second > 0)
set @MinsFromSessionStart = @MinsFromSessionStart + 1;
if (@MinsFromSessionStart > 0)
return @MinsFromSessionStart;
else if (@MinsFromSessionStart < 0)
begin
set @MinsFromSessionStart = 10080 + @MinsFromSessionStart;
return @MinsFromSessionStart;
end
return 10080;
end
Unfortunately, I am getting this error:
'Tick' table
- Unable to modify table.
Computed column 'MinsFromSStart' in table 'Tmp_Tick' cannot be persisted because the column is non-deterministic.
Why is the above function considered non-deterministic?
Thank you for any input,
Michal Kreslik
|
|
|
|
|
Your table has a computed column. You have not included the code for the computed column so I can't say.
Some functions are non-deterministic. There is a list of non-deterministic functions in the Books-on-line. For example GETDATE() is non-deterministic because, given the same inputs, the output can be different.
|
|
|
|
|
The code for the computed column is simply using the above function:
dbo.GetMinsFromSStart([DayOfWeekID], [Hour], [Minute], [Second])
I think there's nothing non-deterministic in that.
Michal
|
|
|
|
|
This link[^] might help. I'm going out on a limb here and guessing that since your function has three different return statements in it, that is making it non-deterministic. Also I'm more used to using 'end if;' to close off any if conditions, but your code has none. Are you sure it is logically what you are after?
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Hello, Chris,
thanks for the link - but I have already read these guidelines before posting a question here.
Also, quite naturally, I have been testing my function whether it's returning the correct results according to the intended logic: and it does. Just for info, the logic behind this function is to return the number of minutes elapsed from the start of Forex trading session. The first minute of the forex trading session starts every week on Sunday at 17:00:01 EST. The inputs are DayOfWeek index (0 for Sunday thru 6 for Saturday) and hour, minute & second values.
The three different return statements are there because it makes the function work faster (which is a concern as I'm running that on an OLAP db with 255 million+ rows). The first condition is true for most input sets, so the function doesn't have to go thru all of the other logical branches.
I'm certain my function complies to the Microsoft definition of a "deterministic function":
Determinism
Deterministic functions always return the same result any time they are called with a
specific set of input values and given the same state of the database. Nondeterministic
functions may return different results each time they are called with a specific set of input
values even if the database state that they access remains the same.
Also, there's no trace of "endif" keyword in the T-SQL reference for IF..ELSE:
http://msdn2.microsoft.com/en-us/library/ms182717.aspx
So where might be the problem?
Thanks for help,
Michal
|
|
|
|
|
I have now written a simple function that returns the higher value of the two:
CREATE FUNCTION [dbo].[GetHigherValue]
(
@Value1 int,
@Value2 int
)
RETURNS int
AS
BEGIN
declare @ReturnVal int;
if (@Value1 > @Value2)
set @ReturnVal = @Value1;
else
set @ReturnVal = @Value2;
return @ReturnVal;
END
There's only one return statement in the above function and it's by all means deterministic, but I'm stil getting the same "non-deterministic" error when I attempt to use it in the computed column specification.
Any ideas?
Thanks,
Michal
|
|
|
|
|
It seems like every UDF (user-defined funcion) is by default non-deterministic.
Is there any way to change this?
Thanks,
Michal
|
|
|
|
|
Ok, guys,
so the correct answer is that it was not created with the "WITH SCHEMABINDING" option.
Now it works ok.
Michal
|
|
|
|
|
Hi experts.
I'm trying to upload binary file into an oracle blob field. I retrieve the binary file from an XML document loaded in memory. I'll try to explain this in detail:
1) A page builds an XMLDocument with client code. In a node of this document, it sets the type to binary and attach a local filesystem document. Then, assigns the value to an hidden input field and sends it to itself
2) This page receives the post data, selects the binary content of the xml node and try to insert the binary data into the blob field (server side code).
I show you the code of this two parts:
1) This part works propertly (I only shows it for if it helps you)
function creaObjecte()
{
// create ADO-stream Object
var ado_stream = new ActiveXObject("ADODB.Stream");
// create XML document with default header and primary node
var xml_dom = new ActiveXObject("MSXML2.DOMDocument");
xml_dom.loadXML('<?xml version="1.0" ?> <root/>');
// specify namespaces datatypes
xml_dom.documentElement.setAttribute("xmlns:dt", "urn:schemas-microsoft-com:datatypes");
// create a new node and set binary content
var l_node1 = xml_dom.createElement("file1");
l_node1.dataType = "bin.base64";
// open stream object and read source file
ado_stream.Type = 1; // 1=adTypeBinary
ado_stream.Open();
ado_stream.LoadFromFile("C:\\TEMP\\b.pdf");
// store file content into XML node
l_node1.nodeTypedValue = ado_stream.Read(-1); // -1=adReadAll
ado_stream.Close();
xml_dom.documentElement.appendChild(l_node1);
document.getElementById("p_arxiu").value = xml_dom.xml;
}
2) I'm using this code. It inserts data into the blob, but doesn't inserts the binary data correctly (I use a pl/sql procedure for download the binary fields for the moment)
Protected Sub btnenviar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnenviar.Click
' Cadena de connexió a oracle
'Dim connectionString As String = "Data Source=AJSTJUST; Password=ajstjust;User ID=AJSTJUST"
Dim connectionString As String = "Data Source=AJSTJUSTCREAF; Password=sipan_mact;User ID=sipan_mactuacions"
' Recuperem l'arxiu binari del node que toca
Dim nodeBinari As XmlNode
Dim xmlHidden As XmlDocument
xmlHidden = New XmlDocument()
xmlHidden.LoadXml(arxiuBinari)
' Seleccionem el node que conté el binari
nodeBinari = xmlHidden.DocumentElement.SelectSingleNode("file1")
If (nodeBinari Is Nothing) Then
Response.Write("No s'ha trobat el node
")
Exit Sub
End If
' Passem el contingut del node a format binari
Dim binariFinal() As Byte = Convert.FromBase64String(nodeBinari.InnerText)
Dim queryString As String = _
"INSERT INTO DEMO (ID,THEBLOB) VALUES (1,'" & binariFinal(1) & "')"
Using connection As New OracleConnection(connectionString)
Dim command As New OracleCommand(queryString)
command.Connection = connection
Try
connection.Open()
command.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Using
End Sub
I think the problem is in this little piece of code:
' Passem el contingut del node a format binari
Dim binariFinal() As Byte = Convert.FromBase64String(nodeBinari.InnerText)
Dim queryString As String = _
"INSERT INTO DEMO (ID,THEBLOB) VALUES (1,'" & binariFinal(1) & "')"
But I'm newbie in .NET and I don't know how to do this.
Any help would be aprreciated. Thanks.
|
|
|
|
|
Hello
I´m working on a project that implements a SQL server 2005 DB as the main DB. Users should be able to extract an given project from the main server DB and "copy" it to there own local machine and work on it there (offline). When they return to work, they should merge it with the main DB.
And I hoped it would be possible to show conflicts through vs.NET so that the user can solve them.
I´m wondering if anyone out there has ever implementet a solution with Merge replication from SQL Server 2005 in Vs.Net 2005.
And if this could be the solution to my problem?
Thanx 
Spaz
|
|
|
|
|
please help me about
SQL: how many type of Store Procedure parameter ?
Pavan Pareta
|
|
|
|
|
Do you mean Input, Output and Return value, or do you mean the actual datatypes. If it's the datatypes, then there are as many types of parameter as there are allowed by the database engine in your tables. I'm not going to list them because they are database dependent.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
please help me about
SQL: diffrence between Clustered and Non Clustered Indexing
Pavan Pareta
|
|
|
|
|
This information is readily available through Google or reading the Books Online.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
Clustered means that the data is stored physically according to the index ordering.
Non-clustered means that there's only a logical mapping.
Thus, if you add/modify the clustered index on your table, the SQL has to reorder the data physically on the disk. If you add/modify a non-clustered index, the data remains where it is and some internal mapping table is added to the database.
Michal
|
|
|
|
|
|
could someone please suggest to me a query i can run on the query analyzer to retrieve logical file name (physical file location) of a database?
|
|
|
|
|
SELECT filename FROM sysfiles
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
can we give more than one fields as primary key in a table.if we can how we can give in sql server2000 by design view and how we can give unique key by design view
|
|
|
|
|
A table can have only one primary key. However, if you want to define primary key for multiple columns, you can select those columns using CTRL key and select "Set Primary Key" option.
|
|
|
|
|
Dear all coder,
How to reset the Identity of some column (PK), and the foreign key (FK) will be updated too as the new PK (identity column) with SQL Sript?
Thanks in advance.
Jati Indrayanto.
Everything is possible.
|
|
|
|
|
How do I reset the identity column?
--------------------------------------------------------------------------------
Answer:
You can use the DBCC CHECKIDENT statement, if you want to reset or reseed the identity column. For example, if you need to force the current identity value in the jobs table to a value of 100, you can use the following:
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 100)
GO
See DBCC CHECKIDENT in SQL Server Books Online for more details.
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
|
|
|
|
|
Thanks for your comment, Sir Sylvester.
If I use
DBCC CHECKIDENT(jobs, RESEED, 100)
It will affect right after when we insert new row right?
Conversely, I wish it will change the current values of PK'.
For example I have 2 tables
Parent Child
------ --------
pk (int identity) fk (int)
name (varchar) descr(varchar)
Suppose the values of PK are 1,9,10 (all has reference in Child)
Then, I wanna change the value of PK to (1,2,3) and cascade the value of FK in Child.
Is it possible to do that with built-in SP or function in SQL 2k5?
Or do I have to make manually?
Thanks In Advance.
Jati Indrayanto.
Everything is possible.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
Thank u very much Dear for your smart reply ... !!!!
![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
