Database Discussion Boards

Corporal Agarn16-Jul-13 4:18

16-Jul-13 4:18

It has to do with disk access. For our setup we have different "disks" for data, log and tempdb. Unfortunately, I cannot find the link to explain this. I believe I found it while researching RAID setups.

Re: SQL Server file locations.

Septimus Hedgehog16-Jul-13 4:44

Septimus Hedgehog

16-Jul-13 4:44

I can see the advantage splitting the database files onto different disks. I'll mention it to our support team and see if they can fill me in with more information. Thanks for the heads-up about it.

If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.

How to replace string with stuff ?

caulsonchua15-Jul-13 23:03

caulsonchua

15-Jul-13 23:03

Dear All,

May i know how to replace other table time in to other date time

Select EP_SCAN_DATE,CAST(STUFF(STUFF(EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME) as SCANDATE FROM AcmkIMS.dbo.EP_SCAN_HIST

Result :

2012-01-06 04:39:19.000

Want actual result:

example time is 09:00 replace in

2012-01-06 09:00

kindly advise

Re: How to replace string with stuff ?

Richard Deeming16-Jul-13 1:37

Richard Deeming

16-Jul-13 1:37

Don't store dates as strings, and don't use string methods to manipulate dates. If you have any control over this database, you should change the EP_SCAN_DATE column to be a datetime.

You haven't specified which DBMS you're using. Assuming MS SQL, you can use something like this:

SQL

SELECT DateAdd(hour, 9, DateDiff(dd, 0, YourDateTimeColumn))

The DateDiff(dd, 0, X) will return just the date part of X, with the time set to midnight. You then use DateAdd to add as many hours, minutes and seconds as you require.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: How to replace string with stuff ?

caulsonchua16-Jul-13 1:39

caulsonchua

16-Jul-13 1:39

HI, i used MS SQL server 2008

Re: How to replace string with stuff ?

Richard Deeming16-Jul-13 1:50

Richard Deeming

16-Jul-13 1:50

In that case, you might want to consider changing the column type to datetime2(0)[^], which is two bytes smaller than a datetime.

Alternatively, if you never need the time portion of the column, you could change the type to date[^], which is even smaller. You'd then need to cast the value to datetime2(0) before adding the time:

SQL

SELECT DateAdd(hour, 9, Convert(datetime2(0), YourDateColumn))

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: How to replace string with stuff ?

Mycroft Holmes16-Jul-13 12:44

Mycroft Holmes

16-Jul-13 12:44

Let me reinforce what Richard has said, NEVER store date as text/varchar/char, ALWAYS use the correct data type.

Text will give you endless problems whenever you try and manipulate the data. The date and datetime data types will give you the tools to manipulate the data easily (not simply it can take a bit of thinking to work out how to achieve a given result).

Never underestimate the power of human stupidity
RAH

Mounting .DBF files (Oracle Database 11g)

prathameshpitale15-Jul-13 5:33

prathameshpitale

15-Jul-13 5:33

Hello,
I recently had the oracle 11g Express Crashed.
I have copied the .dbf files from C:\oraclexe\app\oracle\oradata\XE to other folder and reinstalled Oracle 11g Express edition.
The files are

Control.dbf

sysaux.dbf

system.dbf

TEMP.DBF

UNDOTBS1.DBF

USERS.DBF

I want to remount the old database files, so that I can recover database.

Can you please tell me how can I do this?

-
Prathamesh

Re: Mounting .DBF files (Oracle Database 11g)

Maciej Los29-Jul-13 23:46

Maciej Los

29-Jul-13 23:46

I would suggest you to ask this question on proper forum[^] or see Oracle Database Online Documentation[^].

See similar question and answer: http://dba.stackexchange.com/questions/46367/mounting-a-database-from-database-files-copied-from-a-previous-installation[^]

Re: Mounting .DBF files (Oracle Database 11g)

prathameshpitale30-Jul-13 5:40

prathameshpitale

30-Jul-13 5:40

Maciej Los,
Thanks for your comment, But you can see the Question is Listed on OTN.

The similar Question you referred is also mine.

I am still in search for the solution.

-
Prathamesh

ORA-12560 TNS : protocol adapter error; ORA-1033 Oracle initialization or shutdown in progress

prathameshpitale15-Jul-13 5:33

prathameshpitale

15-Jul-13 5:33

Hello,
I am using win XP with oracle 11g Express edition. I have a customized application written in c#.net and Oracle 11g express as backend.
It was all working fine till today, But now I am getting error.
While opening c# application I am getting ORA-1033 Oracle initialization or shutdown in progress

and if I try from command prompt.
sqlplus system/system123
Error : ORA-12560: TNS: protocol adapter error

tnsnames.ora content

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = user-pc)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

Oracle Services in services.msc
NAme Status
oraclejobschedulerXE -
OracleMTSRecoveryService - Started
OracleServiceXE - Started
OracleXEClrAgent - Started
OracleXETBSListener - Started

TNSPING O/p.

C:\Documents and Settings\User>tnsping localhost

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2
013 15:57:23

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (30 msec)

C:\Documents and Settings\User>

content of listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = user-pc)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

Listener Status :

C:\Documents and Settings\User>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2013 16:00
:46

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Produ
ction
Start Date                14-JUL-2013 15:07:14
Uptime                    0 days 0 hr. 53 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\a
dmin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\user-pc\listener\a
lert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=user-pc)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

command :lsnrctl services

C:\Documents and Settings\User>lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 14-JUL-2013 16:02
:12

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
      "ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
         CLRExtProc
         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_438_FC77BB0E_BBD1.ORA))
      "ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
         CLRExtProc
         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_438_FC77BB0E_B074.ORA))
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:7 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

Tried login using sysdba

C:\Documents and Settings\User>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 14 16:03:30 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:

tried connecting from sql command line

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jul 14 16:04:32 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> shutdown immediate;
ORA-01012: not logged on
SQL> connect system/manager321
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


SQL>

Can you please suggest a solution? I have no fresh database backup.

-
Prathamesh

How to find ladder in sql query

Member 356741115-Jul-13 2:02

Member 3567411

15-Jul-13 2:02

Hi,

I have table like below

col1
5
10
15
20

I need result like

Col1 Col2
5 05--9
10 10--14
15 15--19
20 20-24

Plz help

Thank You

Anil Banjare

Re: How to find ladder in sql query

Niral Soni15-Jul-13 5:00

Niral Soni

15-Jul-13 5:00

How do you define ladder for the last value??
i.e. how do you get 24 in last row??

Apart from that, required query is -

SQL

SELECT COL1, NVL2(NEXT_COL1, COL1 || '--' || (NEXT_COL1 - 1), NULL) COL2 
FROM (
  SELECT COL1
        ,LEAD(COL1, 1, NULL) OVER (ORDER BY COL1) AS NEXT_COL1 
    FROM (SELECT LEVEL*5 COL1 FROM DUAL CONNECT BY LEVEL < 5) 
  )
;

And the output will be -

COL1	COL2
5	5--9
10	10--14
15	15--19
20	NULL

Thanks & Regards,
Niral Soni

Re: How to find ladder in sql query

karna from Hyderabad22-Jul-13 21:01

karna from Hyderabad

22-Jul-13 21:01

USE tempdb ;
GO
DECLARE @ladderTable TABLE ( Col1 INT ) ;
DECLARE @incrementValue INT = 0 ;

INSERT INTO @ladderTable ( Col1 )
VALUES ( 5 ),
( 10 ),
( 15 ),
( 20 ) ;

SELECT TOP 1
@incrementValue = Col1
FROM @ladderTable
ORDER BY Col1 ASC ;

SELECT st.Col1,
CAST(st.Col1 AS VARCHAR(20)) + '-'
+ CAST(ISNULL(dt.Col1, st.Col1 + @incrementValue) - 1 AS VARCHAR(20)) AS LadderSequence
FROM @ladderTable AS st
LEFT OUTER JOIN @ladderTable AS dt ON st.Col1 < dt.Col1
AND ( st.Col1 + @incrementValue ) >= dt.Col1 ;
GO

Thanks,
Karunakar

database course

V.14-Jul-13 22:32

14-Jul-13 22:32

I need to give an introductory database course to a bunch of people who generally are able to write simple SQL queries, but still mainly use files to save stuff. (fits files, ascii files, csv files, ...)

My course starts with presenting some pro's and con's, explaining the basic structure (tables, links, constrains, keys, indexes, ...) and diving into some basic SQL. A second lesson (still under development) would be coding against databases (creating a connection, SQL injection, ...)
I also included a lab with exercises (for lesson 1, lesson 2 to be done).

I looked at some tutorials on the internet, but still would like some advice on how to best bring this topic.

thanks in advance.

V.

(MQOTD Rules and previous Solutions )

Re: database course

Eddy Vluggen15-Jul-13 0:30

Eddy Vluggen

15-Jul-13 0:30

V. wrote:
A second lesson (still under development) would be coding against databases (creating a connection, SQL injection, ...)

I'd recommend explaining sprocs and functions, moving to Xml in the third lesson. Creating a connection to the database from a non-sql language has little to do with SQL.

What's their role? If design comes into play, you could reserve a week for the topic of database-normalization. There's examples on structures that break normalization on the wikipedia[^]; the update-anomaly is always a nice intro as people recognize it from the real world Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: database course

V.15-Jul-13 1:30

15-Jul-13 1:30

Thanks! that already helps !

V.

(MQOTD Rules and previous Solutions )

FIlestream of varbinary ? ( To Store files)

Amol_B14-Jul-13 21:22

Amol_B

14-Jul-13 21:22

After reading several documents , i am still confused whether to use Filestream or Varbinary for storing files?
my application need PDf files to be saved in database , size can be between 100 KB to 4 MB

I guess in order to use filestream we have to configure server to use that. while varbinary can work as it is.

what will be best way?Experts please comment.
Thanks.

Re: FIlestream of varbinary ? ( To Store files)

Richard MacCutchan14-Jul-13 22:29

Richard MacCutchan

14-Jul-13 22:29

Take a look at http://sqltales.wordpress.com/2012/05/15/varbinary-vs-filestream-and-other-blob-issues-3/[^].

Use the best guess

Re: FIlestream of varbinary ? ( To Store files)

Mycroft Holmes14-Jul-13 22:42

Mycroft Holmes

14-Jul-13 22:42

We use filestream only to reduce the size of the database backup which is retained for some ridiculous number of years! documents are backed up seperately.

Never underestimate the power of human stupidity
RAH

Re: FIlestream of varbinary ? ( To Store files)

Amol_B14-Jul-13 22:58

Amol_B

14-Jul-13 22:58

Thanks +5

need an sql query to extract results in the attached format

vijays.30198914-Jul-13 3:40

vijays.301989

14-Jul-13 3:40

Hi Everyone,

I need a help in sql.

Below is the details:

from the front end, i will select few sql objects
for eg, 
i am selecting 
a) ProcedureName1, View_1 from Company_BAT and 
b) GetEmpByProjID,GetAllProj, samplefunction from Company_DEV

here 
ProcedureName1,GetEmpByProjID,GetAllProj are procedures.
View_1 is a view.
samplefunction is a function.


ProcedureName1 uses	a) View_1 (View_1 uses Table_1)
			b) Table_1
			c) Table_2

View_1 uses		a) Table_1

GetEmpByProjID uses	a) Employee

GetAllProj uses	        a) Employee
			b) Project

samplefunction		a) it doesnt uses any objects

i need the output in the format as in the below screenshot in the below link:

http://vijays301989.blogspot.com/b/post-preview?token=QH4f3z8BAAA.jAJqT5poBN3LnJ_eDbQDgA.vZNULScDlF40PLWlpXSZKA&postId=6529902930179783663&type=POST


PLease advise, how to obtain result in this format.

backup in sqllocaldb

dharmaa.m12-Jul-13 7:57

dharmaa.m

12-Jul-13 7:57

hi,i have a problem!!!
How can I get the Sqllocaldb of database backup????
thankyou

Re: backup in sqllocaldb

NotPolitcallyCorrect12-Jul-13 8:36

NotPolitcallyCorrect

12-Jul-13 8:36

Try looking here[^]

Re: backup in sqllocaldb

dharmaa.m12-Jul-13 10:03

dharmaa.m

12-Jul-13 10:03

Thanks for the reply .but I do not want backup in managment studio.
I want the backup (sqllocaldb) to do the coding in Visual 2012.
is It possible??/

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.