Skip to main content

Posts

Showing posts from April, 2013

Copy Table Structre using TSQL

It was very tricky question asked to me during one of DBA session and that time my answer was no Idea, but we can do this very easily with select command.
TSQL

select*fromintoZFI_TAB_4_DAYBUK2SAP..MYAC.ZFI_TAB_4_DAYBUKwhere 1=2

ZFI_TAB_4_DAYBUK2 is new table ZFI_TAB_4_DAYBUK  is a source table


We can also use SET FMTONLY to view metdata of table or query returns.

SETFMTONLYON; GO
SELECTBUKRS,BELNR,BUZEI,GJAHR,BLART,LTEXT, CONVERT(datetime,LEFT(BUDAT,4)+SUBSTRING(BUDAT,5,2)+RIGHT(BUDAT,2))asBUDAT, CONVERT(datetime,LEFT(BLDAT,4)+SUBSTRING(BLDAT,5,2)+RIGHT(BLDAT,2))asBLDAT, BSCHL,GSBER,GTEXT,UMSKZ,DEB_CRE,DMBTR,WAERS,ACC_TYPE, SGTXT,SAKNR,TXT50,KUNNR,CUST,LIFNR,VEND,XBLNR,TXT60,BUSINESS_AREA, CONVERT(datetime,LEFT(CPUDT,4)+SUBSTRING(CPUDT,5,2)+RIGHT(CPUDT,2))asCPUDT, VEND_CUST_GRP --into ZFI_TAB_4_DAYBUK2 FROMZFI_TAB_4_DAYBUK GO
SETFMTONLYOFF

How to see current executing query

There are various way in sql server to see current executing query ..
Using DMV
         select*fromsys.sysprocesseswherestatus='Running' select * from sys.dm_exec_Requests where status='Running select * from sys.dm_exec_sessions where status='Running'
Using DBCC
a.DBCC opentran b.DBCC inputbuffer(SP_ID)

SSMS GUI using Activity Monitor.






DBCC opentran DBCC inputbuffer(SP_ID)
CREATETABLE T1(Col1 int, Col2 char(3)); GO BEGINTRAN INSERTINTO T1 VALUES (101,'abc'); GO

Dam Dam Lake Gurgaon @ 13.03.13

"UP and Down of ECG Line shows we are live and straight line shows we are no more"

That's very true and  popular thought for life . In Day to Day life we face lots of challenges and situations like up and down, We should to face these with ease.
Friends Since few months i was also facing some new problems and challenges of life but by god grace problems are also being short outs and life going forward and cool with family and friends by learning learning learning ..  The best thing about brave person is how he is managing life in such situation. Every human has different aspects for managing situation but my thought is Stay cool.
I want to share about last month trip which helped me to bring myself cool from worth situation. I went for outing after 6 months with our boss colleague (Hasan Sir, Dev Sir and Pratyush Sir). Actually We planned to attend mirage of one of colleague Dharmendra and additionally we planned to visit this Place  The place was DADAM lake.

Damdama lake …

DATALENGTH for Text Data Type

Last Friday i was working on our one of legacy application. And we were required for finding length from text field but when i tried below sql..
selecttop 2 circuit_id,len(prov_config)from config
sql server given error
Msg 8116, Level 16, State 1, Line 4 Argument data type text is invalid for argument 1 of len function.

When I tried to convert into varchar then result was completely different than actual.
selecttop 2 circuit_id,len(cast(prov_config asvarchar)),prov_config from config

Finally I tried Datalength which I got from SQLAuthority.
selecttop 2 circuit_id,Datalength(prov_config ),prov_config from config
I Solved my problem using Datalength but the question still running in my mind why there is two function for finding Length LEN for char and DATALENGTH for Text and why the errors comes Argument data type text is invalid for argument 1 of len function. .


Thanks for reading  Suman

How to find Windows uptime

Sometimes if we require to find the windows uptime or when last booted our system we can do by
net statistics server....

Just open cmd prompt from Run.

and type net statistics server.






Thanks
Suman