|
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.
|
|
|
|
|
After I researched for 2 days, I assume that there's no way to reset the identity of a column (PK) and affect for each FK on other table at the same time. We have to do it manually...here's the example.
Scenario: I want to update the value of parent.pk (1,2,9,10 to become 1,2,3,4) and will reflect to
FK' values. But the problem arise is; I couldn't update ordinarily coz it's Identity.
So, here i'm trying to share the solution of my question was.
Parent
------------
pk (int, identity)
name (varchar)
Child
------------
child_pk(int)
parent_fk(int)
descr (varchar)
==========
9 STEPS
==========
--1. drop constraint of FK to PK (identity)
alter table child
drop constraint FK_child_parent
--2. drop PK of identity column
alter table parent
drop constraint pk_parent
--3. Add another column with same DT as Identity column to parent. and allow null
alter table parent
add new_pk int null
--4. update the new column with the values of the identity column
update parent
set new_pk = pk
--5. if the new column permit null, alter the column to not null
alter table parent
alter column new_pk int not null
--6. drop the identity column [IMPORTANT]
alter table parent
drop column pk
select identity(int, 1,1)
as pk, * into parent2
from parent
-- update the child table. update the FK value to the new value of PK in parent table
update child
set parent_fk = parent2.pk
from child, parent2
where child.parent_fk = parent2.new_pk
--drop the obsolete column
alter table parent2
drop column new_pk
-- drop the obsolete table
drop table parent
--7. Rename the table to the dropped table's name
exec sp_rename 'parent2', 'parent'
--8. if a primary key exists on the new column, recreate the key. In this case you recreate the
-- primary key on parent
alter table parent
add constraint pk_parent primary key (pk)
--9. On other tables. recreate any foreign keys that originally pointed to the old Identity col
-- and point them to the new column. here we recreate the foreign key on child
alter table child with nocheck
add constraint FK_child_parent
foreign key(parent_fk)
references parent(pk)
Jati Indrayanto.
Everything is possible.
|
|
|
|
|
Hi all,
I have a question about the performance of FIND function compared to the SELECT statement. I am developing a VC++ program that queries data from 60+ tables. Before today, I had used only 300 recordsets in the main table and didn't see any problem. Today I added 7000 entries to it, and the a dialog that queries 10 tables (including the main table) took almost 2 seconds to show the result! I use FindFirst/FindNext on all the queries.
Will converting Find to SELECT/WHERE statement help a lot here?
My spec: SQL Express 2005, Pentium4D, 1GB RAM.
Any comments will be appreciated here. Thanks in advance.
|
|
|
|
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)
