Database

Re: 2 basic transaction issues

15-Jan-09 21:38

Thanks Bob,

Sorry one more question, there are 3 modes, autocommit, explicit and implicit. How to disable/enable implicit mode? Smile | :)

regards,
George

Ashfield16-Jan-09 1:18

Ashfield

16-Jan-09 1:18

I don't think you can, but you will need to check the documentation

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Time Addition Problem

AliAmjad14-Jan-09 19:22

AliAmjad

14-Jan-09 19:22

I am using the following code to Add up the difference of two time fields but It's not showing the correct record when the hours part increases above 24 means It shows the correct addition only if It's between 24 hours. For example

WaitingTimeTotal (hh:mm:ss)
----------------
03:18:46
03:25:06
________
06:43:52 which is correct but

WaitingTimeTotal (hh:mm:ss)
----------------
03:18:46
03:25:06
16:23:46
07:24:20
________
wrong answer!!!

The code i am using is

convert(varchar,convert(DateTime, SUM(convert(real, ConfTime - EnterTime))),108)WaitingTimeTotal

Can you please guide me. Thanks in advance !

AliAmjad(MCP)

First make it Run THEN make it Run Fast!

modified on Thursday, January 15, 2009 1:28 AM

Re: Time Addition Problem

Wendelius15-Jan-09 4:13

Difference between MS SQL and ORACLE

15-Jan-09 4:13

If you want to have total hour amount, this could be one way.

I used inline views to both generate a test data (inner one) and to ease the summary (the outer one).

select convert(varchar(50), datepart(hour, datesum) + (datepart(day, datesum) - 1) * 24)
       + ':' + convert(varchar(50), datepart(minute, datesum))
       + ':' + convert(varchar(50), datepart(second, datesum))
from (select SUM(convert(real, datecolumn)) as datesum
      from (select CONVERT(datetime, '03:18:46') as datecolumn
            union
            select CONVERT(datetime, '03:25:06') as datecolumn
            union
            select CONVERT(datetime, '16:23:46') as datecolumn
            union
            select CONVERT(datetime, '07:24:20') as datecolumn) alias1 
      ) alias2

Now the result is 30:31:57. Was this what you were after?

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

Subin Alex14-Jan-09 16:48

Subin Alex

14-Jan-09 16:48

Hi, i just attended an interview and was asked about the difference between MS SQL and ORACLE ? plz help by providing the striking features between these.

Thanx,Subin

Ashfield14-Jan-09 21:12

Ashfield

14-Jan-09 21:12

Well, if you have to ask this then you obviously have not grasped one of the important concepts of being a developer - DO YOUR OWN RESEARCH! Google gave about 1.5 million hits for "compare oracle sql server"

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Wendelius15-Jan-09 0:41

15-Jan-09 0:41

Comparing two different products this way is IMO impossible. Instead of comparing blindly features or capabilites or products in the overall product suites, you should concentrate on the requirements. If you can define what you or the customer needs, then there would be a starting point to conversation considering if one product can fulfill a requirement better than another.

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

Subin Alex15-Jan-09 2:52

Subin Alex

15-Jan-09 2:52

thanx

Dynamic Parameters in procedure

leckey15-Jan-09 5:12

leckey

15-Jan-09 5:12

Don't expect a call-back.

"Well, we're getting "F"'d at work. WPF, WCF, and WWF... WTF?" --John Simmons

monika_vasvani14-Jan-09 7:51

monika_vasvani

14-Jan-09 7:51

Hi all

I have tried to do one general procedure for insert,update,delete.

first i am trying insert.I have one problem:how to pass dynamic parameters and values in stored procedure?

Thanks
monika

Re: Dynamic Parameters in procedure

Wendelius14-Jan-09 8:01

Be careful with creating Dynamic SQL

14-Jan-09 8:01

Could you specify a little more, what do you mean by dynamic parameters. The amount of parameters or the amount of data passed using a parameter, dynamic datatype etc.

If it's possible to describe the scenario it would help.

Mika

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

David Mujica14-Jan-09 8:43

David Mujica

14-Jan-09 8:43

If your intention is to create dynamic SQL strings in a general procedure and execute them, I would not recommend it. First, creating dynamic strings in a .NET enviornment is a bad thing unless you use the stringbuilder class. Second, by creating strings and having the SQL engine execute them will force the SQL engine to parse and develop an execution plan for each statement, this could lead to high CPU usage on your DB server depending on how many SQL statements are being processed per second. You should try to use parameterized queries or stored procedures when dealing with a DB engine, then each time a similar query is requested the statement is already parsed and an execution plan has already been choosen, it will just substitue the parameters in and return your rows. In general, dynamic SQL bad. This is true for almost all DB engines not just MS-SQL server.

Just something to think about ... Smile | :)

Re: Dynamic Parameters in procedure

RyanEK14-Jan-09 13:54

RyanEK

14-Jan-09 13:54

If you're using sql2k8 you can use Table Value Parameters.
If you're using sql2k5 you can simulate this by passing a list to a stored procedure using XML parameters.

Ryan

Re: Dynamic Parameters in procedure

Ashfield14-Jan-09 21:17

Ashfield

14-Jan-09 21:17

monika_vasvani wrote:
I have tried to do one general procedure for insert,update,delete.

Bad practice. The first time the stored proc runs it gets optimised - say it happens to be an insert. The next time it runs it will use the same query plan (as it will be cached), but you may be doing an update, so the optimal query plan would be totally different. This can lead to performance issues. If you want a single proc to call for all the actions have one, but have it call a different proc for insert, another proc for update eetc.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

What is the meaning of the return value and error message?

George_George14-Jan-09 4:24

Re: What is the meaning of the return value and error message?

14-Jan-09 4:24

Hello everyone,

I create the following table,

create table ABC (a varchar unique);

then create the following store procedure,

CREATE PROCEDURE [dbo].[prc_AddABC]  

AS
BEGIN

INSERT INTO [dbo].[ABC]
values ('a');

SELECT @@ERROR AS ABCERROR;

END -- end of store procedure

After execute the procedure twice, which will cause insert duplicate value 'a' on unique column a error.

What I got from Management Studio is,

ABCERROR 2627

return value -4

My question is, why @@error is not the same as return value? what does the message and return value mean? Where could I find

related documents to read their meanings?

thanks in advance,
George

Wendelius14-Jan-09 4:39

Re: What is the meaning of the return value and error message?

14-Jan-09 4:39

@@error gives you the error number as SQL Server generates it. You can use it to identify the problem that occured in the database. Described: http://msdn.microsoft.com/en-us/library/ms188790(SQL.90).aspx[^]

George_George wrote:
return value -4

Depends where you got this from. If you got this in an exception at client side, it most likely describes the error that the library sees (OleDbCommand, SqlCommand etc.) If thats the case, you can find the SQL Server error number inside the exception.

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

George_George14-Jan-09 5:00

Re: What is the meaning of the return value and error message?

14-Jan-09 5:00

Thanks Mika,

I got this error from Management Studio when execute the store procedure I posted in my post. My confusion is, @@ERROR is enough for us to track the exact error, why there is an addtional return value? And who returns -4 -- I did not write return -4 in my store procedure code posted. Smile | :)

regards,
George

Wendelius14-Jan-09 5:48

Re: What is the meaning of the return value and error message?

14-Jan-09 5:48

George_George wrote:
@@ERROR is enough for us to track the exact error

That's true.

George_George wrote:
who returns -4 --

This is something I don't understand. If I create your procedure, run it twice, I get the following result:

Msg 2627, Level 14, State 1, Procedure prc_AddABC, Line 3
Violation of UNIQUE KEY constraint 'UQ__ABC__3BD0198F4316F928'. 
Cannot insert duplicate key in object 'dbo.ABC'.
The statement has been terminated.

(1 row(s) affected)

And that's expected. However, no -4 anywhere. Can you post the whole result of your test.

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

George_George14-Jan-09 5:57

Re: What is the meaning of the return value and error message?

14-Jan-09 5:57

Hi Mika!

-4 is found from the results window and what you posted is found from message window. I have a screen snapshots, but how could I post here? Smile | :)

BTW: in the message window, I got the same result. But my confusion is the information in the results window -- there are two tabs, one for results window and one for message window.

Could you see the same content in result window?

regards,
George

Wendelius14-Jan-09 6:25

Re: What is the meaning of the return value and error message?

14-Jan-09 6:25

Yes that info was from messages window. My result window has this (on the grid, right click / select all / copy with headers):

ABCERROR
2627

So it prints out @@ERROR which is exactly what your procedure does. No -4.
Are you sure the procedure is like in your post? No modifications done? Use the scripting ability and script it to for example query window to see if you do something after the SELECT @@ERROR AS ABCERROR;

About the question of scripting several procedures at the same tame, use the wizard for the database. Select db, right click then Tasks / Generate Scripts... From there on it's simple. Select what you want.

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

George_George14-Jan-09 16:34

Re: What is the meaning of the return value and error message?

14-Jan-09 16:34

Hi Mika,

I checked again the code I posted is the same as what I executed. I have some screen snapshots, how could I post here? If we can not post screen snapshot, do you have any email?

regards,
George

Wendelius14-Jan-09 20:01

Re: What is the meaning of the return value and error message?

14-Jan-09 20:01

If you're getting -4 on the result tab and the column header is ABCERROR, I have no idea what could be the cause. Since you're selecting @@error it should return the same as the messages tab.

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

George_George14-Jan-09 21:55