Nested views

When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?

When you say "4 levels deep" do you mean 4 joins? you might want to test it out. There will be an overhead of creating a temp table, inserting data into it, followed by a SELECT from the temp table. If your report will be run by thousands of users simultaneously you might even see a degradation in performance due to tempdb contention.

|||

By 4 levels deep I mean View1 is a query of 6 tables and several joins, then view2 use view1 with some more tables and joins or calculations etc etc.

Basically the end result in view4 is too complex to write in 1 query or 1 view so you go as far as you can in view1, then expand that result using view2 etc.

So any ideas on performance??

|||

Off the top of the head, since its not a simple query, I cant think of any, so your best bet is to test it out quickly. You can fire up profiler or use SET STATS IO ON and some others like TIME etc and see if there is any improvement/degradation in performance.

Nested transactions

I am writing a program using VC++ 6.0 and SQL 2000 and I am trying to use nested transactions. I have 1 outer transaction and the 2 inner transactions are in sepetrate function calls inside the outer transaction. I have something like this:

BEGIN TRANSACTION;

if (!functioncall1()) // commit if function suceeds, otherwise rollback
{
Rollback Transaction;
return;
}

if (!functioncall2()) // commit if function suceeds, otherwise rollback
{
Rollback Transaction;
return;
}

COMMIT TRANSACTION ;

Both functions contain a complete transaction inside the function call. If either function fails however, I want to do a rollback of the entire transaction. This is not happening though. If functioncall1 suceeds and the transaction in that function commits, then if I do a rollback during functioncall2, the transaction in functioncall1 is not rolled back. This seems to be directly opposite of the SQL help for transaction. Am I missing something obvious here?This gets rather complicated to explain, but I'll give it a shot.

SQL transactions don't nest in the strict (relational algebra) sense of the word. When you "nest" SQL transactions, they form something more like a procedure call stack where the COMMIT behaves like a return and the ROLLBACK behaves something like throwing an execption. The first rollback to come along basically wipes you back to step 1, before the first BEGIN TRANSACTION occured.

There were reasons for this behavior, once upon a time. There is still a reasonable logical arguments for maintaining the behavior, even though it goes so badly against the mental model used by third generation programming tools (like VB, VC, C#, etc).

The simplest solution I see to your problem is to avoid nesting, and use a try/throw/catch model to allow the application side logic to match the database side.

-PatP|||I have tried using the nested transactions and I can get them to work in the SQL query analyzer, but when I try to do the same thing in the application, the rollback will undo the work done in the inner most transaction, but not to the outer transaction.|||Ah... If that is the case, please post the Transact-SQL for what you want to do. It will be much easier to help you translate the Transact-SQL to C than to guess how it is different from what you've posted. What you posted won't behave the way you want it to, because SQL transactions don't work that way.

-PatP|||When I call function 1, the function completes and the transaction is completed. Function 2 doesn't finish and a rollback occurs inside the function, but the changes that were done in function 1 never get undone.

The SQL that I can get to work in the query analyzer is:

BEGIN Transaction Test1
Begin Transaction Test2
Insert into TABLE2
Commit Transaction Test2

BEGIN Transaction Test3
Insert into TABLE3
Commit Transaction
Rollback Transaction

Here is the C++ code that I can't get to work:

void main ( )
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
if (!Function1( ) ) //If this function fails, then rollback
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

ExecuteSQL(_T("BEGIN TRANSACTION "));
if (!Function2( ) ) //If this function fails, then rollback
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

// commit transaction and return success
ExecuteSQL(_T("COMMIT TRANSACTION "));
return 1;

////////////////////////////////////////////////////////////////////////////////////
int Function1( )
{

sqlStatement.Format(_T("INSERT INTO table1( VARIABLE LIST) FROM TABLE1");

try
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
nRowCount = ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

if (nRowCount != 1)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
return 0;
}

sqlStatement.Format(_T("DELETE FROM TABLE2 ");

try
{
nRowCount = ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION "));
e->Delete();
return 0;
}


// commit transaction and return success
ExecuteSQL(_T("COMMIT TRANSACTION "));
return 1;
}

////////////////////////////////////////////////////////////////////////////////////
int Function2( )
{
// Clear any previous errors
ResetError();

// Start a transaction
try
{
ExecuteSQL(_T("BEGIN TRANSACTION "));
}
catch (CException *e)
{
return 0;
}
CString sqlStatement;
sqlStatement.Format( _T("DELETE FROM TABLE2 ");

try
{
ExecuteSQL(sqlStatement);
}
catch (CException *e)
{
e->Delete();
ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return 0;
}
}
sqlStatement.Format( _T( "INSERT INTO TABLE3( )"),
TRY
{
ExecuteSQL(sqlStatement);
}
CATCH( CException *e)
{
ExecuteSQL(_T("ROLLBACK TRANSACTION"));
return 0;
}
END_CATCH

// commit the changes
TRY
{
ExecuteSQL( _T("COMMIT TRANSACTION"));
}
CATCH( CException, e)
{
return 0;
}
END_CATCH

return 1;
}

Nested Query Problem

I am writing a stored procedure that has to insert several rows from one table to another. The problem is that the table into which the rows will be inserted, has more columns than the table that the rows come from. When the stored procedure is called, the extra columns in each new row is supposed to be populated by the stored procedure's arguments. Example:

TableA:
Columns: ID, Group, Name, Email, NewletterSubscriber

TableB:
Columns: ID, Name, Email

The arguments provided are Group, and NewsletterSubscriber.

I need to insert into Table A all records from Table B where ID > 1000 and I need to insert the Group and NewsLetterSubscriber arguments at the same time because these columns do not allow nulls.

I think it might be something like

insert into TableA (ID, Group, Name, Email, NewletterSubscriber)
values (
select * from TableB where ID > 1000)

But how do I insert the stored procedure arguments into the correct columns of the rows?

Your insert statement should be something like

insert into TableA(group, newsletterSubscriber)
values (@.arg_group, @.arg_newsletterSubscriber)

|||

As per your example:

You can do the following :

insert TableA (ID, Group, Name, Email, NewletterSubscriber)

select ID,@.Group,Name,Email,@.NewsletterSubscriber from TableB where ID > 1000

Here @.Group and @.NewsletterSubscriber are a arguments from a Stored procedure.

Thanks

Naras.

|||

I think the procedure definition you want is:

Code Snippet

CREATE PROCEDURE TransRecords_AtoB

@.DefGroup AS int,

@.DefNewsLetterSub AS bit

AS

BEGIN

-- Do you need to clear existing records if so uncomment statement below

-- DELETE FROM TableA

-- WHERE [ID] IN (

-- SELECT ID

-- FROM TableB

-- WHERE (ID > 1000)

-- )

-- Insert the required rows

INSERT INTO TableA ([ID], [Group], [Name],

[Email], [NewsLetterSubscriber])

SELECT [ID], @.DefGroup, [Name], [Email], @.DefNewsLetterSub

FROM TableB

WHERE ([ID] > 1000)

END

If you need to remove records already existing for IDs in TableB then use the commented out delete. Change the types of the arguments to match your fields.

I would recomment that you consider changing some of your column names. It is a bad idea to use identifiers that are reserved words (or might become ones). Name and Group fall into that camp and ID is also suspect. These have to be delimited as shown (and this can cause problem with autogenerated SQL in some tools). Use RecID, RecName, RecGroup etc. or something more descriptive.

Negative values

Hello Everyone,
I am writing a view and I have some negative values coming out of some
fields in one of my tables. They should be there, but when i retrive data i
need them to be non negative. I want to know if there is a function or if
there is a way to get rid of negaive sign.
Thank you allIf all are negatives, multiply the value with -1. Otherwise, you can use the
ABS function. Refer to SQL Server Books Online for more details.
--
-- Anith
( Please reply to newsgroups only )

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?
Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
....
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?
|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns>)
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>
|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.
|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:

>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems to
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
...
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns>)
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:
>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems to
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
...
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns> )
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if
i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:

>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems t
o
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?

Need to understand writing to the Eventlog.

SQL2K
SP4
Howdy all. Im trying to have info written to the Eventlog when a deadlock
occurs, and growing frustrated trying to understand. My original
understanding was that I should RClick the server/ all tasks/ Manage
messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
Eventlog/ OK/ OK.
I took those actions and purposely generated some deadlocks to no avail.
Then I found out what I really need to do is start SQL server by doing
"sqlservr -c -T1204" as instructed in
http://support.microsoft.com/defaul...b;en-us;169960.
Is this the only way? I really dont want to have to bounce all my production
boxes to accomplish this task.
TIA, ChrisR.You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
event in the app eventlog. The trace flag is to log a deadlock graph.
To log the deadlock in the app eventlog, it's sufficient to change the
logging behavior of error 1205 to 'Always write to eventlog'. This is the
same as executing the following:
EXEC sp_altermessage 1205, 'with_log', 'true'
But my experience is that after you have made that change, you need to
recycle the SQL Server instance. I have never had luck not recycling the SQL
instance, and I don't know whether this is a bug or a feature by design.
Linchi
"ChrisR" wrote:

> SQL2K
> SP4
> Howdy all. Im trying to have info written to the Eventlog when a deadlock
> occurs, and growing frustrated trying to understand. My original
> understanding was that I should RClick the server/ all tasks/ Manage
> messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
> Eventlog/ OK/ OK.
> I took those actions and purposely generated some deadlocks to no avail.
> Then I found out what I really need to do is start SQL server by doing
> "sqlservr -c -T1204" as instructed in
> http://support.microsoft.com/defaul...b;en-us;169960.
> Is this the only way? I really dont want to have to bounce all my producti
on
> boxes to accomplish this task.
> TIA, ChrisR.
>|||Perfect, thanks.
"Linchi Shea" wrote:
[vbcol=seagreen]
> You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
> event in the app eventlog. The trace flag is to log a deadlock graph.
> To log the deadlock in the app eventlog, it's sufficient to change the
> logging behavior of error 1205 to 'Always write to eventlog'. This is the
> same as executing the following:
> EXEC sp_altermessage 1205, 'with_log', 'true'
> But my experience is that after you have made that change, you need to
> recycle the SQL Server instance. I have never had luck not recycling the S
QL
> instance, and I don't know whether this is a bug or a feature by design.
> Linchi
> "ChrisR" wrote:
>

Need to understand writing to the Eventlog.

SQL2K
SP4
Howdy all. Im trying to have info written to the Eventlog when a deadlock
occurs, and growing frustrated trying to understand. My original
understanding was that I should RClick the server/ all tasks/ Manage
messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
Eventlog/ OK/ OK.
I took those actions and purposely generated some deadlocks to no avail.
Then I found out what I really need to do is start SQL server by doing
"sqlservr -c -T1204" as instructed in
http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
Is this the only way? I really dont want to have to bounce all my production
boxes to accomplish this task.
TIA, ChrisR.You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
event in the app eventlog. The trace flag is to log a deadlock graph.
To log the deadlock in the app eventlog, it's sufficient to change the
logging behavior of error 1205 to 'Always write to eventlog'. This is the
same as executing the following:
EXEC sp_altermessage 1205, 'with_log', 'true'
But my experience is that after you have made that change, you need to
recycle the SQL Server instance. I have never had luck not recycling the SQL
instance, and I don't know whether this is a bug or a feature by design.
Linchi
"ChrisR" wrote:
> SQL2K
> SP4
> Howdy all. Im trying to have info written to the Eventlog when a deadlock
> occurs, and growing frustrated trying to understand. My original
> understanding was that I should RClick the server/ all tasks/ Manage
> messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
> Eventlog/ OK/ OK.
> I took those actions and purposely generated some deadlocks to no avail.
> Then I found out what I really need to do is start SQL server by doing
> "sqlservr -c -T1204" as instructed in
> http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
> Is this the only way? I really dont want to have to bounce all my production
> boxes to accomplish this task.
> TIA, ChrisR.
>|||Perfect, thanks.
"Linchi Shea" wrote:
> You don't need to use -T1204 or run sqlsrvr -c -T1204 in order to log the
> event in the app eventlog. The trace flag is to log a deadlock graph.
> To log the deadlock in the app eventlog, it's sufficient to change the
> logging behavior of error 1205 to 'Always write to eventlog'. This is the
> same as executing the following:
> EXEC sp_altermessage 1205, 'with_log', 'true'
> But my experience is that after you have made that change, you need to
> recycle the SQL Server instance. I have never had luck not recycling the SQL
> instance, and I don't know whether this is a bug or a feature by design.
> Linchi
> "ChrisR" wrote:
> > SQL2K
> > SP4
> >
> > Howdy all. Im trying to have info written to the Eventlog when a deadlock
> > occurs, and growing frustrated trying to understand. My original
> > understanding was that I should RClick the server/ all tasks/ Manage
> > messages/ 1205 in the Error number/ Find/ Edit/ check Always write to
> > Eventlog/ OK/ OK.
> > I took those actions and purposely generated some deadlocks to no avail.
> > Then I found out what I really need to do is start SQL server by doing
> > "sqlservr -c -T1204" as instructed in
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;169960.
> >
> > Is this the only way? I really dont want to have to bounce all my production
> > boxes to accomplish this task.
> >
> > TIA, ChrisR.
> >