sql permissions: level

Showing posts with label level. Show all posts

Thursday, March 29, 2012

Default Isolation Level in Oracle

Does anyone know what the default transaction isolation level is in Oracle
and how does it defer from SQL Server 2005 ? I have heard that Snapshot
Isolation is Oracles default but has some differences. What are they ? If
Snapshot Isolation is Oracles default, why could it not be considered to be
default for SQL Server ?
ThanksHello,
Snapshot Isolation is equalent to Rollback segment in Oracle. By default in
SQL Server the isolation level is Read committed. Incase if you are planning
to use
Snapshot isolation you should really plan to size the TempDB database, this
is because the entire row versioning is handled in Tempdb.
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
> Does anyone know what the default transaction isolation level is in Oracle
> and how does it defer from SQL Server 2005 ? I have heard that Snapshot
> Isolation is Oracles default but has some differences. What are they ? If
> Snapshot Isolation is Oracles default, why could it not be considered to
> be default for SQL Server ?
> Thanks
>|||Also (I was waiting for someone who know Oracle before jumping in :-) ):
> If
>> Snapshot Isolation is Oracles default, why could it not be considered to be default for SQL
>> Server ?
Snapshot was introduced in SQL Server 2005. If this would be the new default, it would break tens of
thousands of existing applications. Backwards compatibility is a big thing. Also, snapshot generates
potentially a lot of I/O in tempdb, so it is not a panacea.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Snapshot Isolation is equalent to Rollback segment in Oracle. By default in SQL Server the
> isolation level is Read committed. Incase if you are planning to use
> Snapshot isolation you should really plan to size the TempDB database, this is because the entire
> row versioning is handled in Tempdb.
> Thanks
> Hari
> "Hassan" <hassan@.hotmail.com> wrote in message news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
>> Does anyone know what the default transaction isolation level is in Oracle and how does it defer
>> from SQL Server 2005 ? I have heard that Snapshot Isolation is Oracles default but has some
>> differences. What are they ? If Snapshot Isolation is Oracles default, why could it not be
>> considered to be default for SQL Server ?
>> Thanks
>|||Tibor,
What do you think the standard isolation level should be assuming all the
kinks are worked out with regards to backwards compatibility and tempdb
issues.
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23mBYQnedHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Also (I was waiting for someone who know Oracle before jumping in :-) ):
>
>> If
>> Snapshot Isolation is Oracles default, why could it not be considered to
>> be default for SQL Server ?
> Snapshot was introduced in SQL Server 2005. If this would be the new
> default, it would break tens of thousands of existing applications.
> Backwards compatibility is a big thing. Also, snapshot generates
> potentially a lot of I/O in tempdb, so it is not a panacea.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Snapshot Isolation is equalent to Rollback segment in Oracle. By default
>> in SQL Server the isolation level is Read committed. Incase if you are
>> planning to use
>> Snapshot isolation you should really plan to size the TempDB database,
>> this is because the entire row versioning is handled in Tempdb.
>> Thanks
>> Hari
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
>> Does anyone know what the default transaction isolation level is in
>> Oracle and how does it defer from SQL Server 2005 ? I have heard that
>> Snapshot Isolation is Oracles default but has some differences. What are
>> they ? If Snapshot Isolation is Oracles default, why could it not be
>> considered to be default for SQL Server ?
>> Thanks
>>
>|||Hassan,
I don't see the usage of tempdb as an "issue". Using snapshot, you do a conscious trade. You buy
concurrency and pay with resource usage. In order to use either of the snapshot types, SQL Server
need to keep track of old values for all modifications in the database. I wouldn't want this on by
default.
So, it is not as much a matter of the default for the client app (ADO etc), it is more whether we
want this defaulted on for a database.
Also, I don't find it realistic to change such an integral part of the product as the isolation
level. The change would have to be between two releases, and that would immediately affect most of
the apps out there. Unless you refer to RCSI (and not pyre snapshot), but I haven't investigated
RCSI closely enough to say whether you truly get the same behavior across the board for traditional
RC compared to RCSI.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:uI2SH3gdHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Tibor,
> What do you think the standard isolation level should be assuming all the kinks are worked out
> with regards to backwards compatibility and tempdb issues.
> Thanks
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23mBYQnedHHA.4616@.TK2MSFTNGP03.phx.gbl...
>> Also (I was waiting for someone who know Oracle before jumping in :-) ):
>>
>> If
>> Snapshot Isolation is Oracles default, why could it not be considered to be default for SQL
>> Server ?
>> Snapshot was introduced in SQL Server 2005. If this would be the new default, it would break tens
>> of thousands of existing applications. Backwards compatibility is a big thing. Also, snapshot
>> generates potentially a lot of I/O in tempdb, so it is not a panacea.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Snapshot Isolation is equalent to Rollback segment in Oracle. By default in SQL Server the
>> isolation level is Read committed. Incase if you are planning to use
>> Snapshot isolation you should really plan to size the TempDB database, this is because the
>> entire row versioning is handled in Tempdb.
>> Thanks
>> Hari
>> "Hassan" <hassan@.hotmail.com> wrote in message news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
>> Does anyone know what the default transaction isolation level is in Oracle and how does it
>> defer from SQL Server 2005 ? I have heard that Snapshot Isolation is Oracles default but has
>> some differences. What are they ? If Snapshot Isolation is Oracles default, why could it not be
>> considered to be default for SQL Server ?
>> Thanks
>>
>>
>|||The reason I bring this up with the default isolation level is time and
again, we get hit hard on our SQL Servers because of blocking ( change in
query plan,etc.) that immediately results in an unavailable SQL Server as
all the worker threads are consumed. I was hoping that if we get to the
default level similar to Oracle with RCSI, then half of our major issues on
SQL would have been resolved automatically ;)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OuFKIBhdHHA.3616@.TK2MSFTNGP05.phx.gbl...
> Hassan,
> I don't see the usage of tempdb as an "issue". Using snapshot, you do a
> conscious trade. You buy concurrency and pay with resource usage. In order
> to use either of the snapshot types, SQL Server need to keep track of old
> values for all modifications in the database. I wouldn't want this on by
> default.
> So, it is not as much a matter of the default for the client app (ADO
> etc), it is more whether we want this defaulted on for a database.
> Also, I don't find it realistic to change such an integral part of the
> product as the isolation level. The change would have to be between two
> releases, and that would immediately affect most of the apps out there.
> Unless you refer to RCSI (and not pyre snapshot), but I haven't
> investigated RCSI closely enough to say whether you truly get the same
> behavior across the board for traditional RC compared to RCSI.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:uI2SH3gdHHA.1220@.TK2MSFTNGP03.phx.gbl...
>> Tibor,
>> What do you think the standard isolation level should be assuming all the
>> kinks are worked out with regards to backwards compatibility and tempdb
>> issues.
>> Thanks
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23mBYQnedHHA.4616@.TK2MSFTNGP03.phx.gbl...
>> Also (I was waiting for someone who know Oracle before jumping in :-) ):
>>
>> If
>> Snapshot Isolation is Oracles default, why could it not be considered
>> to be default for SQL Server ?
>> Snapshot was introduced in SQL Server 2005. If this would be the new
>> default, it would break tens of thousands of existing applications.
>> Backwards compatibility is a big thing. Also, snapshot generates
>> potentially a lot of I/O in tempdb, so it is not a panacea.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Snapshot Isolation is equalent to Rollback segment in Oracle. By
>> default in SQL Server the isolation level is Read committed. Incase if
>> you are planning to use
>> Snapshot isolation you should really plan to size the TempDB database,
>> this is because the entire row versioning is handled in Tempdb.
>> Thanks
>> Hari
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
>> Does anyone know what the default transaction isolation level is in
>> Oracle and how does it defer from SQL Server 2005 ? I have heard that
>> Snapshot Isolation is Oracles default but has some differences. What
>> are they ? If Snapshot Isolation is Oracles default, why could it not
>> be considered to be default for SQL Server ?
>> Thanks
>>
>>
>>
>|||I see that you are saying. But changing to RCSI is only a matter of flipping a database option, no
code changes are necessary.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:%238tyY4mdHHA.4688@.TK2MSFTNGP04.phx.gbl...
> The reason I bring this up with the default isolation level is time and again, we get hit hard on
> our SQL Servers because of blocking ( change in query plan,etc.) that immediately results in an
> unavailable SQL Server as all the worker threads are consumed. I was hoping that if we get to the
> default level similar to Oracle with RCSI, then half of our major issues on SQL would have been
> resolved automatically ;)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OuFKIBhdHHA.3616@.TK2MSFTNGP05.phx.gbl...
>> Hassan,
>> I don't see the usage of tempdb as an "issue". Using snapshot, you do a conscious trade. You buy
>> concurrency and pay with resource usage. In order to use either of the snapshot types, SQL Server
>> need to keep track of old values for all modifications in the database. I wouldn't want this on
>> by default.
>> So, it is not as much a matter of the default for the client app (ADO etc), it is more whether we
>> want this defaulted on for a database.
>> Also, I don't find it realistic to change such an integral part of the product as the isolation
>> level. The change would have to be between two releases, and that would immediately affect most
>> of the apps out there. Unless you refer to RCSI (and not pyre snapshot), but I haven't
>> investigated RCSI closely enough to say whether you truly get the same behavior across the board
>> for traditional RC compared to RCSI.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message news:uI2SH3gdHHA.1220@.TK2MSFTNGP03.phx.gbl...
>> Tibor,
>> What do you think the standard isolation level should be assuming all the kinks are worked out
>> with regards to backwards compatibility and tempdb issues.
>> Thanks
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23mBYQnedHHA.4616@.TK2MSFTNGP03.phx.gbl...
>> Also (I was waiting for someone who know Oracle before jumping in :-) ):
>>
>> If
>> Snapshot Isolation is Oracles default, why could it not be considered to be default for SQL
>> Server ?
>> Snapshot was introduced in SQL Server 2005. If this would be the new default, it would break
>> tens of thousands of existing applications. Backwards compatibility is a big thing. Also,
>> snapshot generates potentially a lot of I/O in tempdb, so it is not a panacea.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23R80lbedHHA.4720@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Snapshot Isolation is equalent to Rollback segment in Oracle. By default in SQL Server the
>> isolation level is Read committed. Incase if you are planning to use
>> Snapshot isolation you should really plan to size the TempDB database, this is because the
>> entire row versioning is handled in Tempdb.
>> Thanks
>> Hari
>> "Hassan" <hassan@.hotmail.com> wrote in message news:OE3wrSadHHA.3976@.TK2MSFTNGP06.phx.gbl...
>> Does anyone know what the default transaction isolation level is in Oracle and how does it
>> defer from SQL Server 2005 ? I have heard that Snapshot Isolation is Oracles default but has
>> some differences. What are they ? If Snapshot Isolation is Oracles default, why could it not
>> be considered to be default for SQL Server ?
>> Thanks
>>
>>
>>
>>
>

default isolation level

Hi all,
the default isolation level in sql 2k is read committed..
say incase i am connecting to the DB through a jdbc driver and the
application has a default isolation level of repeatable read...
when a transaction is opened from the application end...wat would be the
isolation level...
ll the db override or the application takes up priority..
any help on the context is appreciated..
thnks
anu
Application should override the default level.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C46A6429-6D51-40BF-A4EE-9584224F3625@.microsoft.com...
> Hi all,
> the default isolation level in sql 2k is read committed..
> say incase i am connecting to the DB through a jdbc driver and the
> application has a default isolation level of repeatable read...
> when a transaction is opened from the application end...wat would be the
> isolation level...
> ll the db override or the application takes up priority..
> any help on the context is appreciated..
> thnks
> anu

default isolation level

Hi all,
the default isolation level in sql 2k is read committed..
say incase i am connecting to the DB through a jdbc driver and the
application has a default isolation level of repeatable read...
when a transaction is opened from the application end...wat would be the
isolation level...
ll the db override or the application takes up priority..
any help on the context is appreciated..
thnks
anuApplication should override the default level.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C46A6429-6D51-40BF-A4EE-9584224F3625@.microsoft.com...
> Hi all,
> the default isolation level in sql 2k is read committed..
> say incase i am connecting to the DB through a jdbc driver and the
> application has a default isolation level of repeatable read...
> when a transaction is opened from the application end...wat would be the
> isolation level...
> ll the db override or the application takes up priority..
> any help on the context is appreciated..
> thnks
> anu

Default Isolation Level

Is there a way to change the server default isolation level from READ
COMMITTED to something else. I mean the server default not resetting a
connection's default with SET TRANSACTION ISOLATION LEVEL <>-- Kevin Jackson wrote: --
>>Is there a way to change the server default isolation level from READ
>COMMITTED to something else. I mean the server default not resetting a
>connection's default with SET TRANSACTION ISOLATION LEVEL
--
Hi Kevin,
The answer to your question is NO.
May I ask why you wish to change this default isolation level? What specific issues are you facing?
Hope this helps,
-Eric CÃ¡rdenas
SQL Server support|||Hi Kevin,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Eric is right that you cannot change the server default isolation level.
Here I'd like to add some more information.
The server setting is in effect unless overridden at a lower level.
1) Server level is always "Read Committed" - there is no way to change it.
2) Database level is always at the server level - there is no way to change
it.
3) For a given connection, use "DBCC USEROPTIONS" to see options that are
"SET", anything not listed is at the default server level.
4) For a given query, locking hints can use used to change the isolation
level.
I hope this information would answer your question and if you need more
help, please feel free to post any new message here and I am ready to
provide help.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql

Tuesday, March 27, 2012

Default field to another field value on db level

Hi All,

I need to create a new field on a table and have that field default to another field value in that same table. Is there a way to do this w/ a default constraint rather than adding a trigger to the table? If i can't use a default constraint does anyone have a template trigger i could use? Below is an example of what i'm trying to do (Field_C is the new field and i want it to use Field_A value if no other value is specified on insert). Any help would be greatly appreciated.

alter table FOO add Field_C varchar(50) not null constraint FOO_default DEFAULT Field_A

thanks,
Davethis worked for me.....

CREATE Trigger TRG_FOO_default_INS
on dbo.FOO
for Insert
as

Declare @.default_FieldC varchar(50)

select @.default_FieldC = Field_C from inserted

If (@.default_FieldC is null) or (@.default_FieldC = '')
BEGIN
Update FOO set Field_C = Field_A where PK_ID in (select PK_ID from inserted)
END -- update externalname

Wednesday, March 7, 2012

decimal datatpe with less than or equal

Hi All,
I have a field whose datatype is decimal.
I am trying to see if the value is less than or equal to zero. I am
getting Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Here is what I was doing
select field1=
CASE WHEN table2.MaxLabrHrs<=0.00
THEN table1.field2* table2.LaborAmount
end
I am not sending DDL hoping that it will be easy fix.
Problem is here when I put : table2.MaxLabrHrs<=0.00
Field MaxLabrHrs is decimal datatype (9,2).
If I put only less than then it works.
Thanks a million in advance.
Best regards,
mamun>> I am not sending DDL hoping that it will be easy fix.
It may not be. The error might be to generated from another section of the
code. It might be worthwhile to check the datatypes & data in the field2
column as well as the LaborAmount column.
Anith|||I don't think it's that column that's giving the error.
What types are the "field2" and "LaborAmount" columns you mention?
It's most likely that some row where MaxLabrHrs=0 has an unconvertible
varchar column.
And it would be easier to fix with DDL, btw ;)
microsoft.public.dotnet.languages.vb wrote:

>Hi All,
>I have a field whose datatype is decimal.
>I am trying to see if the value is less than or equal to zero. I am
>getting Server: Msg 8114, Level 16, State 5, Line 1
>Error converting data type varchar to numeric.
>Here is what I was doing
>select field1=
>CASE WHEN table2.MaxLabrHrs<=0.00
>THEN table1.field2* table2.LaborAmount
>end
>
>I am not sending DDL hoping that it will be easy fix.
>Problem is here when I put : table2.MaxLabrHrs<=0.00
>Field MaxLabrHrs is decimal datatype (9,2).
>If I put only less than then it works.
>Thanks a million in advance.
>Best regards,
>mamun
>
>|||Thanks a ton.
Yes, the field2 in the table1 is numeric.
Is there anything I can do without changing the numeric datatype in the
table1?
If not then I will change that to decimal.
Thanks again,
best regards,
mamun|||NUMERIC & DECIMAL are well compatible and so that is a non-issue. You might
want to check the data in all involved columns. Also check the datatype of
the LaborAmount as well.
If you still find it a problem, consider posting the table DDLs & some
sample data.
Anith|||The following codes were written from Mr. Anith Sen's help probably two
years ago (or more). Then it became necessity to use another field
MaxLabrHrs in the criteria table.
This is the code I am using:
select V_LABCST=
CASE WHEN t2.LaborIndicator = 'R' AND t2.LaborAmount > 0.00 and
t2.MaxLabrHrs <=0.00
THEN t1.V_APPLBRHRS * t2.LaborAmount
WHEN t2.LaborIndicator = 'R' and (t2.LaborAmount=0.00 or
t2.LaborAmount='' or t2.LaborAmount is null) and t2.MaxLabrHrs <=0.00
THEN t1.V_APPLBRHRS * t3.Labor_Rate
WHEN t2.LaborIndicator = 'R' and t2.MaxLabrHrs>0.00
THEN CASE
WHEN t1.V_APPLBRHRS<t2.MaxLabrHrs THEN
t1.V_APPLBRHRS * t3.Labor_Rate
WHEN t1.V_APPLBRHRS>t2.MaxLabrHrs THEN
t2.MaxLabrHrs * t3.Labor_Rate
END
END
from Filtered_Data_Hold t1
JOIN Criteria t2
ON t1.Supplier_ID = t2.SupplierID
join Claims t3 on t1.Claim_Number=t3.Claim_Number
where t1.Claim_Number='1I23456'
CREATE TABLE [dbo].[Criteria] (
[Supplier_ID] [varchar] (10) NOT NULL ,
[LaborIndicator] [char] (2) ,
[LaborAmount] [decimal](8, 2) NULL ,
[MaxLabrHrs] [decimal](9, 2) NOT NULL
)
Insert into criteria('AX812', 'R', 67.00 , .66)
CREATE TABLE [dbo].[Filtered_Data_Hold] (
[Claim_Number] [char] (9) NOT NULL ,
[Supplier_ID] [char] (7) NULL ,
[Req_Lab_Hrs] [numeric](6, 1) NOT NULL ,
[V_APPLBRHRS] [numeric](6, 1) NOT NULL ,
[V_LABCST] [numeric](10, 2) NOT NULL
) ON [PRIMARY]
GO
insert Filtered_Data_Hold('1I23456', 'AX812', 1.0, 1.0, 67.00)
CREATE TABLE [dbo].[Claims] (
[Claim_Number] [char] (9) NOT NULL ,
[VAppLabr_Hrs] [numeric](6, 1) NOT NULL
)
Insert Claims ('1I23456', 1.0)
I want the result to be 67.00*.66=44.22 (when there is max labor hours
in the criteria table greater than 0.00, I want to multiply max labour
hours with the labor rate).
Thanks a ton for your help.
best regards,
mamun|||comparing t2.LaborAmount to the empty string is the problem
(t2.LaborAmount=0.00 or
-->>>> t2.LaborAmount=''
or t2.LaborAmount is null)
microsoft.public.dotnet.languages.vb wrote:

>The following codes were written from Mr. Anith Sen's help probably two
>years ago (or more). Then it became necessity to use another field
>MaxLabrHrs in the criteria table.
>This is the code I am using:
>select V_LABCST=
>CASE WHEN t2.LaborIndicator = 'R' AND t2.LaborAmount > 0.00 and
>t2.MaxLabrHrs <=0.00
> THEN t1.V_APPLBRHRS * t2.LaborAmount
> WHEN t2.LaborIndicator = 'R' and (t2.LaborAmount=0.00 or
>t2.LaborAmount='' or t2.LaborAmount is null) and t2.MaxLabrHrs <=0.00
> THEN t1.V_APPLBRHRS * t3.Labor_Rate
> WHEN t2.LaborIndicator = 'R' and t2.MaxLabrHrs>0.00
> THEN CASE
> WHEN t1.V_APPLBRHRS<t2.MaxLabrHrs THEN
> t1.V_APPLBRHRS * t3.Labor_Rate
> WHEN t1.V_APPLBRHRS>t2.MaxLabrHrs THEN
> t2.MaxLabrHrs * t3.Labor_Rate
> END
> END
>from Filtered_Data_Hold t1
> JOIN Criteria t2
> ON t1.Supplier_ID = t2.SupplierID
>join Claims t3 on t1.Claim_Number=t3.Claim_Number
>where t1.Claim_Number='1I23456'
>
>CREATE TABLE [dbo].[Criteria] (
> [Supplier_ID] [varchar] (10) NOT NULL ,
> [LaborIndicator] [char] (2) ,
> [LaborAmount] [decimal](8, 2) NULL ,
> [MaxLabrHrs] [decimal](9, 2) NOT NULL
> )
>Insert into criteria('AX812', 'R', 67.00 , .66)
>
>CREATE TABLE [dbo].[Filtered_Data_Hold] (
> [Claim_Number] [char] (9) NOT NULL ,
> [Supplier_ID] [char] (7) NULL ,
> [Req_Lab_Hrs] [numeric](6, 1) NOT NULL ,
> [V_APPLBRHRS] [numeric](6, 1) NOT NULL ,
> [V_LABCST] [numeric](10, 2) NOT NULL
> ) ON [PRIMARY]
>GO
>insert Filtered_Data_Hold('1I23456', 'AX812', 1.0, 1.0, 67.00)
>
>CREATE TABLE [dbo].[Claims] (
> [Claim_Number] [char] (9) NOT NULL ,
> [VAppLabr_Hrs] [numeric](6, 1) NOT NULL
> )
>Insert Claims ('1I23456', 1.0)
>
>
>I want the result to be 67.00*.66=44.22 (when there is max labor hours
>in the criteria table greater than 0.00, I want to multiply max labour
>hours with the labor rate).
>
>Thanks a ton for your help.
>best regards,
>mamun
>
>

Sunday, February 19, 2012

Debugging my sprock

From this msg im guessing that SQLE.DLL nee to be installed on my SQLServer box, is that correct? Where would i find this dll?

Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on APOLLO-ClintonSQL (Error = 0x800401f3). Ensure that client-side components, such as SQLLE.DLL, are installed and registered on PUR-CO-Clinton2. Debugging disabled for connection 59.Start here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servtools_5cfm.asp).

-PatP

Debugging "Stored procedure in T-SQL Debugger"

For 2 days already Stored procedures on SQL Server don`t work.
When debugging:
"ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL
mssdi98.dll, or one of the DLLs it references. Reason: 126(The specified
module could not be found.)."
Other object in database (tables,views) work OK.
What is going on?
Vladimir
Did you read the troubleshooting section in Books Online? I searched for below string and found the section:
"Cannot load the DLL"
Make sure you use the updated Books Online:
http://www.microsoft.com/SQL/techinf...00/default.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Majstor" <majstorv@.hotmail-removethis-.com> wrote in message news:O5q2UF0FEHA.1012@.TK2MSFTNGP11.phx.gbl...
> For 2 days already Stored procedures on SQL Server don`t work.
> When debugging:
> "ODBC: Msg 0, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL
> mssdi98.dll, or one of the DLLs it references. Reason: 126(The specified
> module could not be found.)."
> Other object in database (tables,views) work OK.
> What is going on?
> Vladimir
>

Friday, February 17, 2012

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
Paul
Paul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
PaulPaul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul

Debugger

I am trying to use the debugger for a stored procedure, each time I use it I
get the following error
<<ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionH
andler:
Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQ
L
Server is terminating this process.>>
I have tried using the debugger on the simplest of stored procedures and
this error happens without fail every time.
Any ideas?
Ta
PaulPaul,
For Debugger to work, SQL Server service account should be configured to
start using domain account.
Thanks
Shri.DBA
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:F65FA3AE-8CC5-450C-9F03-260300AFEEBD@.microsoft.com...
> I am trying to use the debugger for a stored procedure, each time I use it
I
> get the following error
> <<ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptio
nHandler:
> Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL
> Server is terminating this process.>>
> I have tried using the debugger on the simplest of stored procedures and
> this error happens without fail every time.
> Any ideas?
> Ta
> Paul

Thursday, March 29, 2012

Default Isolation Level in Oracle

Default Isolation Level in Oracle

Default Isolation Level in Oracle

default isolation level

default isolation level

Default Isolation Level

Tuesday, March 27, 2012

Default field to another field value on db level

Wednesday, March 7, 2012

decimal datatpe with less than or equal

Sunday, February 19, 2012

Debugging my sprock

Debugging "Stored procedure in T-SQL Debugger"

Friday, February 17, 2012

Debugger

Debugger

Debugger

sql permissions

Blog Archive

About Me