Database

Re: "begin catch" and "end catch"

20-Jan-09 6:39

George_George wrote:
I think you mean if transaction are started at client, it is client's responsibility to rollback transaction

Not exactly. What if the transaaction was started by another procedure which then calls this one.

George_George wrote:
I think it is always better to implement transaction at server side, not client side, correct?

This would be a matter of opinion and architecture decision.

George_George wrote:
In this situation, what is your suggested solution to return meaningful error information to client

Depending on the situtation, I would return the original error or use RAISERROR to add more descriptive information.

The need to optimize rises from a bad design.My articles[^]

Hamid_RT22-Jan-09 23:18

Hamid_RT

22-Jan-09 23:18

See Using TRY...CATCH in Transact-SQL[^].

Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )

Convert Columns as Rows

Vignesh Krishnan19-Jan-09 1:40

19-Jan-09 1:40

Hi Guys,

Does any body tel me How to COnvert Column in to rows
Say Examples my Table contains
A B C D
1 1 3 5
2 5 6 7

I want this to be shown as

columnNames Value1 value2
A 1 2
B 1 5
C 3 6
D 5 7

any body assist me on this??

Wendelius19-Jan-09 2:05

19-Jan-09 2:05

You could have a look at pivoting[^].

The need to optimize rises from a bad design.My articles[^]

Vignesh Krishnan19-Jan-09 3:12

19-Jan-09 3:12

Thanks Buddy and could YOU tel me the best URL or PDF to study abt SQL SERVER REPORTING SERVICES

Wendelius19-Jan-09 6:55

19-Jan-09 6:55

Vignesh Krishnan wrote:
Thanks Buddy

You're welcome

Vignesh Krishnan wrote:
the best URL or PDF to study abt SQL SERVER REPORTING SERVICES

Don't know what's best, but you could start from here: Tutorials (Reporting Services)[^]

The need to optimize rises from a bad design.My articles[^]

Vignesh Krishnan19-Jan-09 8:45

19-Jan-09 8:45

Hi

I have records like this
Say
Tme_On /dial /connect /month
1 1 1 jan
2 3 4 Dec
3 3 5 nov

I want this to be shown as
Total Jan Dec Nov
Tme_On 1 2 3
Dial 1 3 3
Connect 1 4 5

Can any one Help me ...

swjam19-Jan-09 11:26

swjam

19-Jan-09 11:26

Like Mika said use pivots. If you can't even have a look at it and at least pattern from a sample solution to fit your problem then I think you're in the wrong industry... Or is the codez urgentz?

----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list

Vignesh Krishnan20-Jan-09 2:10

20-Jan-09 2:10

I have tried it and i have got it.dont think wrong on person those who was unknown to u swjam.

http://www.codeproject.com/script/Forums/View.aspx?fid=1725&select=2886125#xx2886125xx[^]

ColinM12319-Jan-09 11:45

ColinM123

19-Jan-09 11:45

Online Guitar Tools

Wendelius20-Jan-09 8:17

20-Jan-09 8:17

Infinite loop? Smile | :)

The need to optimize rises from a bad design.My articles[^]

Wendelius20-Jan-09 8:15

MS Access Query: Bit value in a Where Clause

20-Jan-09 8:15

I strongly encourage you to read the usage examples and test different variations using your tables. But to get you to start: For example, to get the first row of your data, you would fetch tme_on and month. I added an inline view since I don't have your table but you would replace that with a select to your table. The same idea goes with the other rows.

select 'tme_on' as heading, [jan],[dec], [nov]
from (
	select 1 as tme_on, 'jan' as [month]
	union
	select 2 as tme_on, 'dec' as [month]
	union
	select 3 as tme_on, 'nov' as [month]
) alias
pivot (
    sum(tme_on)
    for [month] in ([jan],[dec], [nov])
) as resulttable

The need to optimize rises from a bad design.My articles[^]

swjam19-Jan-09 0:03

swjam

19-Jan-09 0:03

The Query inside my MS Access database looks like the following:

Code:

SELECT 
	dbo_MyTab1.MyCol4, dbo_MyTab2.MyCol3
FROM 
	dbo_MyTab2 INNER JOIN dbo_MyTab1
		ON dbo_MyTab2.MyCol2 = dbo_MyTab1.MyCol2
WHERE 
    (((dbo_MyTab1.MyCol1)=0)
	AND dbo_MyTab1.MyCol3 NOT LIKE '%Expr%')
    AND ((dbo_MyTab2.MyCol4)=False)));

Given that in the backend database:

Code:

MyTab1.MyCol1 is (bit, not null)
MyTab2.MyCol4 is (Flag(bit), not null)

The problem is that when I execute the query, it prompts a dialog box asking for the value of MyTab1.MyCol1.
There should be none of this dialog pop-up. Anyone encountered this before?

Hope this helps make the problem clearer?

----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list

vertical to Horzontal data display

Learner52018-Jan-09 23:22

Learner520

18-Jan-09 23:22

i got 2 tables Reports_tbl and Manger_tbl
Reports_tbl
Manager DepID NumOfEmp_Reports
1 1 2
1 2 5
1 3 1
2 1 13
2 2 10
2 3 0
3 1 1
3 2 2
3 3 9

Manager_tbl
ManagerID ManagerName
1 Sam
2 Joseph
3 Paul

assuming that DeptID as Fin =1, Mng =2 and Sec =3 because i have to show Name of departments and NumOfEmp_Reports to sam,joeph,and paul

i have written query

select m.ManagerName, r.NumOfEmp_Reports as Fin ,r.NumOfEmp_Reports as Mng ,r.NumOfEmp_Reports as Sec
from Manager_tbl as m
Inner join Rerports_tbl as r
on m.managerId =r.mangaerId

the result of this query is as

ManagerName Fin Mng Sec
Sam 2 2 2
Sam 5 5 5
sam 1 1 1
Joseph 13 13 13
Joseph 10 10 10
Joseph 0 0 0
Paul 1 1 1
Paul 2 2 2
Paul 9 9 9

but i have to diplay result as following

ManagerName Fin Mng Sec
Sam 2 5 1
Joseph 13 10 0
Paul 1 2 9

thnx in adv for any kind help.
regards learner

Re: vertical to Horzontal data display

Wendelius19-Jan-09 2:17

Re: vertical to Horzontal data display

19-Jan-09 2:17

If I interpreted yhe question correctly, you could use PIVOT[^]

The need to optimize rises from a bad design.My articles[^]

Learner52019-Jan-09 3:12

Learner520

19-Jan-09 3:12

thanks for your kind help.

Re: vertical to Horzontal data display

Wendelius19-Jan-09 6:52

Save Query Result as XML File in SQL Server 2005

19-Jan-09 6:52

You're welcome

The need to optimize rises from a bad design.My articles[^]

Abhijit Jana18-Jan-09 23:22

Abhijit Jana

18-Jan-09 23:22

Hi,

My stored procedure returning XML data (I am using FOR XML Auto ) . I want to save the same as XML File from that procedure itself . I am using SQL Server 2005 .
Any Idea to implement  Smile | :)

Thanks in advance !

cheers,
Abhijit
CodeProject MVP
My Recent Article : Exploring Session in ASP.Net

Done !!!

Abhijit Jana19-Jan-09 2:04

Abhijit Jana

19-Jan-09 2:04

I have used bcp and execute it by xp_cmdshell Smile | :)

cheers,
Abhijit
CodeProject MVP
My Recent Article : Exploring Session in ASP.Net

Re: Done !!!

Wendelius19-Jan-09 8:50

How to get leaf nodes of a particular Parent using sql Query

19-Jan-09 8:50

Just as an alternative because xp_cmdshell has some security issues, I think you could create a linked server against ODBC DSN and use ODBC Text Driver. Using that, you could write to the linked server as it was a table.

The need to optimize rises from a bad design.My articles[^]

vidhyap18-Jan-09 23:10

vidhyap

18-Jan-09 23:10

Hi,

I have to query the table,which have more than 3 or 4 level of hierarchy.
I need to get all the leaf nodes of particular Parent.i tried Querying like this-select * from Hierarchy where UniqId not in (select Parent from Hierarchy).but it returns leaf nodes of all the parent.what i need is to get leaf nodes of particular Parent.

can anybbody please help me to solve this issue,its realy very important.

Thanks and regards
Vidhya

Re: How to get leaf nodes of a particular Parent using sql Query

kingindra18-Jan-09 23:16

kingindra

18-Jan-09 23:16

can you please let me know the table structure?

Re: How to get leaf nodes of a particular Parent using sql Query

Wendelius19-Jan-09 2:09