Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
Manny
Deferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
David Portas
SQL Server MVP
|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.c om...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny
sql
Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts
Wednesday, March 28, 2012
Invalid stored procedures are getting created which have errors
Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny
Invalid stored procedures are getting created which have errors
Hello,
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
--
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny
i'm having a strange issue with SQL server 2000 (sp3). i'm able to
create stored procedures that have critical erros. for example, i'm
able to create the following stored procedure in the tempdb table even
the the table, nor the columns exist any where. is there a setting
i've changed on the database that is supressing the validation of the
stored procedures.
CREATE PROCEDURE spAccountCustomerAdd
AS
select asdfkljasdk,dkajfk,dkjf
from blah11
any help would be appreciated..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
--
David Portas
SQL Server MVP
--|||To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.
SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@.gmail.com> wrote in message
news:6162b3aa.0410281304.73741c5a@.posting.google.com...
> Hello,
> i'm having a strange issue with SQL server 2000 (sp3). i'm able to
> create stored procedures that have critical erros. for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
> any help would be appreciated..
> Manny
Monday, March 26, 2012
Invalid stored procedures [How to check?]
I am on a development project where we are using SQL Server 2000 and using
almost exclusively stored procedures.
Because the team is all over the country we have run into a problem.
Because we are in the development/design phase things are changing as new
requirements are thrust upon us by managment.
Sometimes the datatype of table changes or a column is deleted. This makes
the stored procedure invalid. However, you don't know its invalid until you
access the stored procedure.
Is there a way to know that a stored procedure has become invalid or is
there a way to force a recompile so that you can see which stored procedure
now have problems?
Thanks!
EnzoPrior to posting my original message I read the portion of the BOL that you
pasted to this message.
The problem with sp_recompile is that it does not actually do the recompile!
It only marks it to be recompiled. What I want is way to actually
recompile all my stored procs without essentially dropping and recreating
them to find which ones are now invalid.
In Oracle and other DBMS system a recompile is an actuall recompile and not
simply marking a flag somewhere in the DD.
I guess the only way to do this is how we are doing it now which is to drop
and recreate every single stored proc.
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:ufC3E#kRDHA.2460@.TK2MSFTNGP10.phx.gbl...
> BOL:
> Recompiling a Stored Procedure
> As a database is changed by such actions as adding indexes or changing
data
> in indexed columns, the original query plans used to access its tables
> should be optimized again by recompiling them. This optimization happens
> automatically the first time a stored procedure is run after Microsoft®
SQL
> ServerT 2000 is restarted. It also occurs if an underlying table used by
the
> stored procedure changes. But if a new index is added from which the
stored
> procedure might benefit, optimization does not automatically happen (until
> the next time the stored procedure is run after SQL Server is restarted).
> SQL Server provides three ways to recompile a stored procedure:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
>
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon, and causes the stored procedure to execute
> more slowly because the stored procedure must be recompiled each time it
is
> executed.
>
> c.. You can force the stored procedure to be recompiled by specifying
the
> WITH RECOMPILE option when you execute the stored procedure. Use this
option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
>
> Note If an object referenced by a stored procedure is deleted or renamed,
> an error is returned when the stored procedure is executed. If, however,
an
> object referenced in a stored procedure is replaced with an object of the
> same name, the stored procedure executes without having to be recompiled.
>
> To recompile a stored procedure next time it is run
> Transact-SQL
>
> See Also
> Creating a Stored Procedure
> Deferred Name Resolution and Compilation
> Executing a Stored Procedure
> Programming Stored Procedures
> "Enzo" <touringcarclub@.hotmail.com> wrote in message
> news:e$JHDbjRDHA.2148@.TK2MSFTNGP11.phx.gbl...
> > I am on a development project where we are using SQL Server 2000 and
using
> > almost exclusively stored procedures.
> >
> > Because the team is all over the country we have run into a problem.
> >
> > Because we are in the development/design phase things are changing as
new
> > requirements are thrust upon us by managment.
> >
> > Sometimes the datatype of table changes or a column is deleted. This
> makes
> > the stored procedure invalid. However, you don't know its invalid until
> you
> > access the stored procedure.
> >
> > Is there a way to know that a stored procedure has become invalid or is
> > there a way to force a recompile so that you can see which stored
> procedure
> > now have problems?
> >
> > Thanks!
> > Enzo
> >
> >
>
>
almost exclusively stored procedures.
Because the team is all over the country we have run into a problem.
Because we are in the development/design phase things are changing as new
requirements are thrust upon us by managment.
Sometimes the datatype of table changes or a column is deleted. This makes
the stored procedure invalid. However, you don't know its invalid until you
access the stored procedure.
Is there a way to know that a stored procedure has become invalid or is
there a way to force a recompile so that you can see which stored procedure
now have problems?
Thanks!
EnzoPrior to posting my original message I read the portion of the BOL that you
pasted to this message.
The problem with sp_recompile is that it does not actually do the recompile!
It only marks it to be recompiled. What I want is way to actually
recompile all my stored procs without essentially dropping and recreating
them to find which ones are now invalid.
In Oracle and other DBMS system a recompile is an actuall recompile and not
simply marking a flag somewhere in the DD.
I guess the only way to do this is how we are doing it now which is to drop
and recreate every single stored proc.
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:ufC3E#kRDHA.2460@.TK2MSFTNGP10.phx.gbl...
> BOL:
> Recompiling a Stored Procedure
> As a database is changed by such actions as adding indexes or changing
data
> in indexed columns, the original query plans used to access its tables
> should be optimized again by recompiling them. This optimization happens
> automatically the first time a stored procedure is run after Microsoft®
SQL
> ServerT 2000 is restarted. It also occurs if an underlying table used by
the
> stored procedure changes. But if a new index is added from which the
stored
> procedure might benefit, optimization does not automatically happen (until
> the next time the stored procedure is run after SQL Server is restarted).
> SQL Server provides three ways to recompile a stored procedure:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
>
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon, and causes the stored procedure to execute
> more slowly because the stored procedure must be recompiled each time it
is
> executed.
>
> c.. You can force the stored procedure to be recompiled by specifying
the
> WITH RECOMPILE option when you execute the stored procedure. Use this
option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
>
> Note If an object referenced by a stored procedure is deleted or renamed,
> an error is returned when the stored procedure is executed. If, however,
an
> object referenced in a stored procedure is replaced with an object of the
> same name, the stored procedure executes without having to be recompiled.
>
> To recompile a stored procedure next time it is run
> Transact-SQL
>
> See Also
> Creating a Stored Procedure
> Deferred Name Resolution and Compilation
> Executing a Stored Procedure
> Programming Stored Procedures
> "Enzo" <touringcarclub@.hotmail.com> wrote in message
> news:e$JHDbjRDHA.2148@.TK2MSFTNGP11.phx.gbl...
> > I am on a development project where we are using SQL Server 2000 and
using
> > almost exclusively stored procedures.
> >
> > Because the team is all over the country we have run into a problem.
> >
> > Because we are in the development/design phase things are changing as
new
> > requirements are thrust upon us by managment.
> >
> > Sometimes the datatype of table changes or a column is deleted. This
> makes
> > the stored procedure invalid. However, you don't know its invalid until
> you
> > access the stored procedure.
> >
> > Is there a way to know that a stored procedure has become invalid or is
> > there a way to force a recompile so that you can see which stored
> procedure
> > now have problems?
> >
> > Thanks!
> > Enzo
> >
> >
>
>
Monday, March 19, 2012
Invalid Cursor State
Hi,
I have a stored procedure that calls 2 other stored procedures and combines the results into a temporary table. The results of the temporary table is then returned from the stored procedure.
When I execute the stored procedure in Query Analyzer, I get the exact data I want in the correct format - no errors.
When I execute that stored procedure in Omnivex SQL Link 3, I get an "Invalid Cursor State" error.
I did some digging on that error, and found that it could be related to print statements within the stored procedures. I removed all print statements from all 3 stored procedures and the error is still occuring.
Any suggestions?I suggest you post the code for the stored procedure and somebody will help you rewrite it without using a cursor. 99 times out of 100 they aren't necessary and only impede performance.|||Dude...I don't believe they are using a Cursor in the code...
What is Omnivex SQL Link 3 anyway?
EDIT: AHA
http://www.omnivex.com/index.asp
Is there code on the SQL Link side? Or does it act like ODBC...my Guess it's middleware and that's where you're problem resdies|||Good point.
I wonder if the stored proc might be returning spurious information before the final recordset. Maybe a simple SET NOCOUNT ON would solve the problem.|||That is brilliant! The SET NOCOUNT ON/OFF did the trick! Thank you so much!!!!|||"An expert is a person who has made all possible mistakes in a very narrow field." - Niels Bohr
I have a stored procedure that calls 2 other stored procedures and combines the results into a temporary table. The results of the temporary table is then returned from the stored procedure.
When I execute the stored procedure in Query Analyzer, I get the exact data I want in the correct format - no errors.
When I execute that stored procedure in Omnivex SQL Link 3, I get an "Invalid Cursor State" error.
I did some digging on that error, and found that it could be related to print statements within the stored procedures. I removed all print statements from all 3 stored procedures and the error is still occuring.
Any suggestions?I suggest you post the code for the stored procedure and somebody will help you rewrite it without using a cursor. 99 times out of 100 they aren't necessary and only impede performance.|||Dude...I don't believe they are using a Cursor in the code...
What is Omnivex SQL Link 3 anyway?
EDIT: AHA
http://www.omnivex.com/index.asp
Is there code on the SQL Link side? Or does it act like ODBC...my Guess it's middleware and that's where you're problem resdies|||Good point.
I wonder if the stored proc might be returning spurious information before the final recordset. Maybe a simple SET NOCOUNT ON would solve the problem.|||That is brilliant! The SET NOCOUNT ON/OFF did the trick! Thank you so much!!!!|||"An expert is a person who has made all possible mistakes in a very narrow field." - Niels Bohr
Monday, March 12, 2012
invalid column name ref: calculated columns
hello
we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.
we are trying to use a already selected feild in a calculation to return another feild or column as they like to be known. this as we have found is not allowed!! is there anyone who knows of a clever work around so we dont have to write the calculation out 10000000000... times..
below is the code with the problem areas separted ta
cq
Alter Procedure AXACCBodereaux
(
@.ReportsPrintMenuHoldCoveredText73 datetime
)
As
SELECT QuoteRegister.QuoteNumber, QuoteRegister.PolicyNumber, QuoteRegister.Datecoverfrom, QuoteRegister.Datecoverto,
QuoteRegister.Broker, QuoteRegister.CLNTName, CLNTMaster.CLNTAddress1, CLNTMaster.CLNTAddress2, CLNTMaster.CLNTAddress3,
CLNTMaster.CLNTAddress4, CLNTMaster.CLNTPostcode, CLNTMaster.CLNTTradelongname,
Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end AS Type,
QuoteRegister.[BuildingSI] + QuoteRegister.[Total ContentsSumInsured] AS [Material Damage], Rating.TotalBI, Rating.PLIndemnitylimit,
percentnonliab = Case When Rating.TotalExcludingliabs <>0 Then Rating.KeyednonliabsdiscAmount / Rating.TotalExcludingliabs Else 0 end,
percentliab = Case When Rating.Totalliabilities <>0 Then Rating.KeyedliabilitiesdiscountAmount / Rating.Totalliabilities Else 0 end,
(Rating.[Total MD Premium] + Rating.TotaGITPrem + Rating.TotalmoneyPrem + Rating.DeteriorationofstocklimitPrem ) AS sum1,
MDPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then ( sum1 - ( sum1 * percentnonliab))
Else sum1 - sum1 *( Rating.NonliabsdiscountPercent/100) end ,
TOTBIPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then Rating.TotalBIPrem - Rating.TotalBIPrem * percentnonliab
Else Rating.TotalBIPrem - Rating.TotalBIPrem * (Rating.NonliabsdiscountPercent/100) end ,
PLPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem) * percentliab Else (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem)*(Rating.Totalliabilitiesdiscou ntPercent/100) end ,
ELPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then Rating.TotalELPrem-(Rating.TotalELPrem*(percentliab))
Else Rating.TotalELPrem - Rating.TotalELPrem*(Rating.Totalliabilitiesdiscoun tPercent/100) end ,
QuoteRegister.[Total Premium], (QuoteRegister.[Total Premium]*0.275) AS Commission,
(QuoteRegister.[Total Premium]*0.05) AS IPT, ([Total Premium]+IPT-Commission) AS NIA,
0 AS Finalised, QuoteRegister.Proposalreceived, QuoteRegister.CreationDate, QuoteRegister.CLNTCode,
QuoteRegister.DateonHoldCoveredBDX, Rating.KeyedliabilitiesdiscountAmount, Rating.KeyednonliabsdiscAmount
FROM (QuoteRegister INNER JOIN CLNTMaster ON QuoteRegister.CLNTCode =
CLNTMaster.CLNTCode) INNER JOIN Rating ON (CLNTMaster.CLNTCode = Rating.CLNTCode) AND
(QuoteRegister.QuoteNumber = Rating.QuoteNumber)
WHERE (((Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end ) <> 'MTA') AND ((QuoteRegister.DateonHoldCoveredBDX)=
@.ReportsPrintMenuHoldCoveredText73) AND ((QuoteRegister.ReasonForIssueCode) <> 'MTA') AND
((QuoteRegister.Insurer)='AXACC') AND ((Rating.QuoteNumber)=QuoteRegister.QuoteNumber) AND
((CLNTMaster.CLNTCode)=QuoteRegister.CLNTCode) AND ((Rating.ReasonForIssueCode)<>'MTA'));
/* set nocount on */
returnOriginally posted by colonelquinn
hello
we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.
You consider that a slight problem?
Also, I'm not sure I follow the problem...of course yuo can have derived data...what's the issue?|||derived data??
sorry dont know what that is only started doing this on friday..
to simplify the problem if you do this
select a ,b, c , a+b+c as d
or
select a,b,c, d+e as f , f+g as h
errors fly out saying any pre referenced value are invalid column names.
of course i could type out the calculation in every place but when i come to change the calc i am bound to forget to do it in every place.
i have tried doing two sql queies within the procedure but that gives the same error.
any ideas
#
thanks very much
cq|||Yeah You can't do that...
USE Northwind
GO
SELECT CustomerId + Convert(varchar(10),EmployeeId) As A, A+'Cant be done!'
FROM Orders|||surely there is a work around??!!!?!?!?!?!?|||USE Northwind
GO
SELECT A + ' Can be done!' FROM (SELECT CustomerId + Convert(varchar(10),EmployeeId) As A FROM Orders) As xxx
But it looks like a lot of rework...since you're doing it anyway...|||just define a view with as many of the combinations as you wish -- a+b+c, d+e, etc.
then query the view instead of the table
rudy
http://r937.com|||thankyou gentlemen
there are no errors so far...
now i wait till we have the data|||...or create calculated columns for the combinations you need.
blindman
we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.
we are trying to use a already selected feild in a calculation to return another feild or column as they like to be known. this as we have found is not allowed!! is there anyone who knows of a clever work around so we dont have to write the calculation out 10000000000... times..
below is the code with the problem areas separted ta
cq
Alter Procedure AXACCBodereaux
(
@.ReportsPrintMenuHoldCoveredText73 datetime
)
As
SELECT QuoteRegister.QuoteNumber, QuoteRegister.PolicyNumber, QuoteRegister.Datecoverfrom, QuoteRegister.Datecoverto,
QuoteRegister.Broker, QuoteRegister.CLNTName, CLNTMaster.CLNTAddress1, CLNTMaster.CLNTAddress2, CLNTMaster.CLNTAddress3,
CLNTMaster.CLNTAddress4, CLNTMaster.CLNTPostcode, CLNTMaster.CLNTTradelongname,
Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end AS Type,
QuoteRegister.[BuildingSI] + QuoteRegister.[Total ContentsSumInsured] AS [Material Damage], Rating.TotalBI, Rating.PLIndemnitylimit,
percentnonliab = Case When Rating.TotalExcludingliabs <>0 Then Rating.KeyednonliabsdiscAmount / Rating.TotalExcludingliabs Else 0 end,
percentliab = Case When Rating.Totalliabilities <>0 Then Rating.KeyedliabilitiesdiscountAmount / Rating.Totalliabilities Else 0 end,
(Rating.[Total MD Premium] + Rating.TotaGITPrem + Rating.TotalmoneyPrem + Rating.DeteriorationofstocklimitPrem ) AS sum1,
MDPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then ( sum1 - ( sum1 * percentnonliab))
Else sum1 - sum1 *( Rating.NonliabsdiscountPercent/100) end ,
TOTBIPREM = Case When Rating.KeyednonliabsdiscAmount <>0 Then Rating.TotalBIPrem - Rating.TotalBIPrem * percentnonliab
Else Rating.TotalBIPrem - Rating.TotalBIPrem * (Rating.NonliabsdiscountPercent/100) end ,
PLPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem) * percentliab Else (Rating.TotalPLPrem + Rating.TotalPRPrem) -
(Rating.TotalPLPrem + Rating.TotalPRPrem)*(Rating.Totalliabilitiesdiscou ntPercent/100) end ,
ELPREM = Case When Rating.KeyedliabilitiesdiscountAmount <>0 Then Rating.TotalELPrem-(Rating.TotalELPrem*(percentliab))
Else Rating.TotalELPrem - Rating.TotalELPrem*(Rating.Totalliabilitiesdiscoun tPercent/100) end ,
QuoteRegister.[Total Premium], (QuoteRegister.[Total Premium]*0.275) AS Commission,
(QuoteRegister.[Total Premium]*0.05) AS IPT, ([Total Premium]+IPT-Commission) AS NIA,
0 AS Finalised, QuoteRegister.Proposalreceived, QuoteRegister.CreationDate, QuoteRegister.CLNTCode,
QuoteRegister.DateonHoldCoveredBDX, Rating.KeyedliabilitiesdiscountAmount, Rating.KeyednonliabsdiscAmount
FROM (QuoteRegister INNER JOIN CLNTMaster ON QuoteRegister.CLNTCode =
CLNTMaster.CLNTCode) INNER JOIN Rating ON (CLNTMaster.CLNTCode = Rating.CLNTCode) AND
(QuoteRegister.QuoteNumber = Rating.QuoteNumber)
WHERE (((Case Rating.ReasonForIssueCode When 'NBC' Then 'NB' Else 'RNL' end ) <> 'MTA') AND ((QuoteRegister.DateonHoldCoveredBDX)=
@.ReportsPrintMenuHoldCoveredText73) AND ((QuoteRegister.ReasonForIssueCode) <> 'MTA') AND
((QuoteRegister.Insurer)='AXACC') AND ((Rating.QuoteNumber)=QuoteRegister.QuoteNumber) AND
((CLNTMaster.CLNTCode)=QuoteRegister.CLNTCode) AND ((Rating.ReasonForIssueCode)<>'MTA'));
/* set nocount on */
returnOriginally posted by colonelquinn
hello
we have a slight problem.. we are upszing a access database to sql server and hence are rewriting all the queries as stored procedures.
You consider that a slight problem?
Also, I'm not sure I follow the problem...of course yuo can have derived data...what's the issue?|||derived data??
sorry dont know what that is only started doing this on friday..
to simplify the problem if you do this
select a ,b, c , a+b+c as d
or
select a,b,c, d+e as f , f+g as h
errors fly out saying any pre referenced value are invalid column names.
of course i could type out the calculation in every place but when i come to change the calc i am bound to forget to do it in every place.
i have tried doing two sql queies within the procedure but that gives the same error.
any ideas
#
thanks very much
cq|||Yeah You can't do that...
USE Northwind
GO
SELECT CustomerId + Convert(varchar(10),EmployeeId) As A, A+'Cant be done!'
FROM Orders|||surely there is a work around??!!!?!?!?!?!?|||USE Northwind
GO
SELECT A + ' Can be done!' FROM (SELECT CustomerId + Convert(varchar(10),EmployeeId) As A FROM Orders) As xxx
But it looks like a lot of rework...since you're doing it anyway...|||just define a view with as many of the combinations as you wish -- a+b+c, d+e, etc.
then query the view instead of the table
rudy
http://r937.com|||thankyou gentlemen
there are no errors so far...
now i wait till we have the data|||...or create calculated columns for the combinations you need.
blindman
Wednesday, March 7, 2012
Introducing (NOLOCK) into production code for Selects
I have a large application that is multi-user and quite transactional,
driven by a VB
front end. It uses mostly stored procedures for retrieving data. I was
running into locking
contention on tables with 300,000 to 2 million records that are read and
updated by all users.
We allow the users to see the top 1000 rows from a table in "browse" mode in
VB (as a disconnected recordset), from which they can select a single record
to edit and update (also disconnected during editing).
I have read in Kalens book that we should use a lock hint for our Selects
(NOLOCK). But the only example given is like this:
SELECT <field list> FROM <table> (NOLOCK) WHERE .......
This is followed by the explanation that any Lock Hint needs to be wrapped
in a BEGIN TRAN/COMMIT.
Or IMPLICIT_TRANSACTIONS must be set on.
My application has thousands of lines of code, and the implications of
IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a production
app.
Can a simple Select issue a NOLOCK without being wrapped in an explicit
transaction?
I am trying to find an easy way to modify hundreds of stored procs that
retrieve data just for browsing without creating a Shared Lock. Most of the
parameterized Procs do some decision making before issuing a single Select.
For example (abbreviated to save space)
If @.Account_Type = 'P'
Select <some fields> from <view>
Else
Select <other fields> from <view>
Which begs another question, can NOLOCK be used when selecting from a view?
As in:
If @.Account_Type = 'P'
Select <some fields> from <view> (NOLOCK)
Else
Select <other fields> from <view> (NOLOCK)
Thanks for your input.On Fri, 11 Nov 2005 21:15:11 -0500, "jkotuby" <jkotuby@.snet.net>
wrote:
>Can a simple Select issue a NOLOCK without being wrapped in an explicit
>transaction?
Yes.
What Kalen says about lock hints in transactions (I don't have her
book here) may hold for locks, but doesn't apply to nolocks!
I've seen tons of production code done the way you want.
Not sure I approve of it, but it does what it does.
J.|||As I stated in an earlier post, NOLOCK will make your queries return
incorrect results at lightning speed. You must weigh the risk of returning
wrong answers against the performance benefits. Don't use it if the results
will be used in an INSERT or UPDATE.
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>|||Locking hints in general are not required to be wrapped in a transaction.
Some hints may require a transaction to get the desired overall result.
These would be things that need to hold the lock for the duration of or
across several statements. But NOLOCK is not one of them. The correct way
to use it would be to include the previously optional WITH as shown:
SELECT * FROM Table WITH (NOLOCK)
Just be aware that using NOLOCK will potentially give you dirty reads. If
that is OK for your application then fine but be aware of what implications
it may have. Reads in general are compatible with there reads. So if you
are being blocked a lot you may have transactions open for too long a period
of time and see what you can do to reduce that. A lack of proper indexes
will increase the time it takes for a DML operation along with the number of
rows affected.
Andrew J. Kelly SQL MVP
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>
driven by a VB
front end. It uses mostly stored procedures for retrieving data. I was
running into locking
contention on tables with 300,000 to 2 million records that are read and
updated by all users.
We allow the users to see the top 1000 rows from a table in "browse" mode in
VB (as a disconnected recordset), from which they can select a single record
to edit and update (also disconnected during editing).
I have read in Kalens book that we should use a lock hint for our Selects
(NOLOCK). But the only example given is like this:
SELECT <field list> FROM <table> (NOLOCK) WHERE .......
This is followed by the explanation that any Lock Hint needs to be wrapped
in a BEGIN TRAN/COMMIT.
Or IMPLICIT_TRANSACTIONS must be set on.
My application has thousands of lines of code, and the implications of
IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a production
app.
Can a simple Select issue a NOLOCK without being wrapped in an explicit
transaction?
I am trying to find an easy way to modify hundreds of stored procs that
retrieve data just for browsing without creating a Shared Lock. Most of the
parameterized Procs do some decision making before issuing a single Select.
For example (abbreviated to save space)
If @.Account_Type = 'P'
Select <some fields> from <view>
Else
Select <other fields> from <view>
Which begs another question, can NOLOCK be used when selecting from a view?
As in:
If @.Account_Type = 'P'
Select <some fields> from <view> (NOLOCK)
Else
Select <other fields> from <view> (NOLOCK)
Thanks for your input.On Fri, 11 Nov 2005 21:15:11 -0500, "jkotuby" <jkotuby@.snet.net>
wrote:
>Can a simple Select issue a NOLOCK without being wrapped in an explicit
>transaction?
Yes.
What Kalen says about lock hints in transactions (I don't have her
book here) may hold for locks, but doesn't apply to nolocks!
I've seen tons of production code done the way you want.
Not sure I approve of it, but it does what it does.
J.|||As I stated in an earlier post, NOLOCK will make your queries return
incorrect results at lightning speed. You must weigh the risk of returning
wrong answers against the performance benefits. Don't use it if the results
will be used in an INSERT or UPDATE.
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>|||Locking hints in general are not required to be wrapped in a transaction.
Some hints may require a transaction to get the desired overall result.
These would be things that need to hold the lock for the duration of or
across several statements. But NOLOCK is not one of them. The correct way
to use it would be to include the previously optional WITH as shown:
SELECT * FROM Table WITH (NOLOCK)
Just be aware that using NOLOCK will potentially give you dirty reads. If
that is OK for your application then fine but be aware of what implications
it may have. Reads in general are compatible with there reads. So if you
are being blocked a lot you may have transactions open for too long a period
of time and see what you can do to reduce that. A lack of proper indexes
will increase the time it takes for a DML operation along with the number of
rows affected.
Andrew J. Kelly SQL MVP
"jkotuby" <jkotuby@.snet.net> wrote in message
news:OaQvt7y5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have a large application that is multi-user and quite transactional,
> driven by a VB
> front end. It uses mostly stored procedures for retrieving data. I was
> running into locking
> contention on tables with 300,000 to 2 million records that are read and
> updated by all users.
> We allow the users to see the top 1000 rows from a table in "browse" mode
> in
> VB (as a disconnected recordset), from which they can select a single
> record
> to edit and update (also disconnected during editing).
> I have read in Kalens book that we should use a lock hint for our Selects
> (NOLOCK). But the only example given is like this:
> SELECT <field list> FROM <table> (NOLOCK) WHERE .......
> This is followed by the explanation that any Lock Hint needs to be wrapped
> in a BEGIN TRAN/COMMIT.
> Or IMPLICIT_TRANSACTIONS must be set on.
> My application has thousands of lines of code, and the implications of
> IMPLICIT_TRANSACTIONS seem quite capable of causing breakage in a
> production
> app.
> Can a simple Select issue a NOLOCK without being wrapped in an explicit
> transaction?
> I am trying to find an easy way to modify hundreds of stored procs that
> retrieve data just for browsing without creating a Shared Lock. Most of
> the
> parameterized Procs do some decision making before issuing a single
> Select.
> For example (abbreviated to save space)
> If @.Account_Type = 'P'
> Select <some fields> from <view>
> Else
> Select <other fields> from <view>
> Which begs another question, can NOLOCK be used when selecting from a
> view? As in:
>
> If @.Account_Type = 'P'
> Select <some fields> from <view> (NOLOCK)
> Else
> Select <other fields> from <view> (NOLOCK)
>
> Thanks for your input.
>
Labels:
application,
code,
database,
driven,
introducing,
microsoft,
multi-user,
mysql,
nolock,
oracle,
procedures,
production,
retrieving,
selects,
server,
sql,
stored,
transactional,
vbfront
Subscribe to:
Posts (Atom)