|
Your question doesn't give much idea of what you want, so here are some choices:
CREATE FUNCTION GetRange
(
@Lower INTEGER
,
@Upper INTEGER
)
RETURNS @IntegerRange TABLE
(
Member INTEGER
)
AS
BEGIN
WHILE @Lower<=@Upper
BEGIN
INSERT INTO @IntegerRange VALUES (@Lower)
SET @Lower=@Lower+1
END
RETURN
END
SELECT * FROM GetRange ( 12 , 20 )
CREATE FUNCTION GetRangeById
(
@ID INTEGER
)
RETURNS @IntegerRange TABLE
(
Member INTEGER
)
AS
BEGIN
DECLARE @Lower INTEGER
DECLARE @Upper INTEGER
SET @Lower = (SELECT [Lower] FROM Series WHERE ID=@ID)
SET @Upper = (SELECT [Upper] FROM Series WHERE ID=@ID)
WHILE @Lower<=@Upper
BEGIN
INSERT INTO @IntegerRange VALUES (@Lower)
SET @Lower=@Lower+1
END
RETURN
END
SELECT * FROM GetRange ( 100 )
CREATE FUNCTION GetAllRanges
(
)
RETURNS @IntegerRange TABLE
(
ID INTEGER
,
Member INTEGER
)
AS
BEGIN
DECLARE @ID INTEGER
DECLARE @Lower INTEGER
DECLARE @Upper INTEGER
DECLARE SeriesCursor CURSOR FOR SELECT * FROM Series
OPEN SeriesCursor
FETCH NEXT FROM SeriesCursor
INTO @ID , @Lower , @Upper
WHILE @@FETCH_STATUS=0
BEGIN
WHILE @Lower<=@Upper
BEGIN
INSERT INTO @IntegerRange VALUES (@ID , @Lower)
SET @Lower=@Lower+1
END
FETCH NEXT FROM SeriesCursor
INTO @ID , @Lower , @Upper
END
CLOSE SeriesCursor
RETURN
END
SELECT * FROM GetAllRanges()
|
|
|
|
|
Here is what my XML document looks like.
<DATAMATRIX>
<FSDATAMATRIX>
<DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE>
<FIELD1>BELGIUM</FIELD1>
<FIELD2>BEVERAGES, NON-ALCOHOLIC</FIELD2>
<FIELD4>NO</FIELD4>
<FIELD5>YES</FIELD5>
</FSDATAMATRIX>
<FSDATAMATRIX>
<DM_OBJECT_TYPE>ITEM0</DM_OBJECT_TYPE>
<FIELD1>BRAZIL</FIELD1>
<FIELD2>CHEESE</FIELD2>
<FIELD4>NO</FIELD4>
<FIELD5>YES</FIELD5>
</FSDATAMATRIX>
</DATAMATRIX>
Using the following code snipet, I got the XMLDOC variable to update, but it will only update the field that previously had a value of "BELGIUM" to the value in my variable, @XMAXPPM.
SET @XMLDOC.modify('
replace value of (/DATAMATRIX/FSDATAMATRIX/FIELD1[.="BELGIUM"]/text())[1]
with sql:variable("@XMAXPPM")
')
How do I write a modify statement that will update FIELD2 to a value only when FIELD1 is "BELGIUM" ?
Thanks,
David
|
|
|
|
|
SET @XMLDOC.modify('
replace value of (/DATAMATRIX/FSDATAMATRIX/<code>FIELD2[../FIELD1="BELGIUM"]</code>/text())[1]
with sql:variable("@XMAXPPM")
')
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
Perfect. I got my project almost complete. Your assistance is greatly appreciated.
|
|
|
|
|
I am using odp.net 10gR2.
Is there a way I can monitor the connection pool, like action connection, connection strings or active connections etc. Also, can I know the like how many connection pools will be there per cpu in my server and can I make any of the connection pool as inactive or disable or something like that ?
Thanks in advance.
|
|
|
|
|
Hi,
You should be able to monitor connection pool (and also many other things) using Performance Monitor (open from COntrol Panel). ODP installation adds several new counters and groups by default.
Hope this helps,
Mika
|
|
|
|
|
Performance Monitor integration is available in 11g and not in 10g
Still, thanks and lemme have another look at that one.
|
|
|
|
|
Sorry, you're absolutely right . It comes in 11g.
Some of the things you asked could be found out from server side. If you have a connection in your connection pool you can see it from v$-views at server. You can also separate which connections are active and running a command and which are only waiting.
In connection string you can define the maximum amount of simultaneous connections and how long they are kept in connection pool and is the connection pooling used at all.
This wasn't exactly what you we're asking but might help you to get forward.
Mika
|
|
|
|
|
Thanks Mika,
Actually I tried v$session but I am not able to figure out through which application user has logged in, and got into that connection pool. Also, its like after some number of connections (less then 20) it is not allowing me to log in and its just spinning and not giving error and I have closed all the other connection from other browsers. So practically there are no active connection, still its not letting me in.
SELECT sid, serial#, event, seconds_in_wait, client_info, status, terminal FROM V$SESSION
WHERE PROGRAM IS NOT NULL
and OSUSER = 'ASPNET'
and terminal = 'Terminal1'
order by seconds_in_wait
Anyways, lemme try some other things, if I can think of !
Thanks for your help Mika.
|
|
|
|
|
You're welcome
Mika
|
|
|
|
|
hi all, I want just to know some thing about the size of DB, if we have DB with 2 tables with no relations between both of them (no PK and FK) so does its size is less than the same one which we make a relation between both of its tables. I want to know even there is a small increment on the size of DB where there is a relation between its tables instead of the one where there is no relation between their table.
thanks for the help from all
Thanks alot
Hamody
|
|
|
|
|
Hi,
When you add relation between tables (foreign key) only a small amount of data is added to system tables. The foreign key is enforced without any additional space requirements on your tables (however the system tables where the foreign key is defined are stored in the same database so they use a small amount of space, as data always does).
When defining primary key, the information is also added to system tables, but in this case an index is typically also built to quarantee performance of the primary key. The size of the index can vary a lot so there's no way of saying how much space it will consume without the knowledge of the primary key definition along with table definition.
Hope this helps,
Mika
|
|
|
|
|
Hi ,
Iam using PostGre8.1 , while executing query using ExceuteSQL command in VC++ if the DBServer is Switched off, the ExecuteSQL command is not responding due to which my application gets hanged.
Is there any way to avoid this case?
Thanks
|
|
|
|
|
I'm not very familiar with PostGre, but I would imagine that this happens because the server side does not inform the client about shutdown. This leaves the client in a state where it thinks that the operation is still running.
You should try to find info about following things:
- is there a way to configure a heart-beat checking in PostGre. This means that the existence of the server is checked once in a while
- are there server side configuration possibilities that would allow the server to inform the clients that the server is shutting down before the connection is broken
- can you define a maximum amount of time in connection string that a single operation can last.
Hope this helps,
Mika
|
|
|
|
|
well can u help me in implementing a code for update,insert,delete operations on the database table using ado.net in c# and bind it to datagrid view.
|
|
|
|
|
|
Hi everyone,
I have Oracle 10g Standard Edition installed in my laptop, I changed the following registry keys..
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
"NLS_LANG"="ARABIC_YEMEN.AR8MSWIN1256"
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0]
"NLS_LANG"="ARABIC_YEMEN.AR8MSWIN1256"
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0]
"NLS_LANG"="ARABIC_YEMEN.AR8MSWIN1256"
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1]
"NLS_LANG"="AMERICAN_AMERICA.WE8MSWIN1252"
Everything is working fine, the data can be saved and viewed in Arabic in SqlPlus but not on my .Net app.
Please help
ps.
I also tried changing the current NLS parameters using the following but it didnt work either
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
SHUTDOWN IMMEDIATE;
Please help me out guys
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Hey and thank you guys!
I just re-installed Oracle and it's working now!
ps. Thanks Mustafa
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
hi all,
i want to Install the application on My client machines . My application is based on Server-client.So i think that i install Sql server on Server-side Machine and Application on Client -side Machine. I want to ask whether above assumption is right or not ?
If right ,then whether there are any requirement of run-time environment on client Machine so it can connect to Sql Server database which reside on server
and if i wrong, then please suggest the steps
|
|
|
|
|
if your application is windows base client need MSDE(Microsoft sqlserver database engeen) to connect to server.if no nothing is needed.
sepel
|
|
|
|
|
I already Use SQL Express 2005 . So can you provide the info according to this Configuration.
I am New Bie(with respect to MSSQL Server )
|
|
|
|
|
sorry i don't work by it.
sepel
|
|
|
|
|
I want to store the Hindi language (Indian Reginal language)
what kind of changes I have to make in postgreSQL.
plz help...
modified on Monday, July 28, 2008 4:32 AM
|
|
|
|
|
I think there are no special requirement at back end side .But require changes at front end side . Like the font of the control which contains Hindi character. Use "Krunti Dev 010" like font. Also you can Use System.Text.encoding
Hope this helps
|
|
|
|
|
"krunti Dev 010" is not available .
please give me another solution if any???
and how to use "System.Text.encoding" ??
modified on Monday, July 28, 2008 6:52 AM
|
|
|
|