network error

Hello,
I get the following error:
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
Need help.
I have 2 sql server instances on two different machines.
Created a linked serve between SERVE1 and SERVER2.
Tried to run a select query using four-part-naming-convention from server1
to get the query results.
From Server1 ran the following query:
select * from servername2.datbasename.owner.tablename
got the following error message:
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
Need help to resolve this issue.
Don
Go into Enterprise Manager and click on the linked server. See if you can
see any of the tables. If not, you haven't got the connection setup
correctly. Take the parameters from the linked table and try to connect
with the same authentication in Query Analyzer. Does that work? I'm
assuming you can ping the other server? Also, if the second server is an
instance, let us know if you have "Hide Server" checked in the server
configuration on that server.
"Dony" <donb@.yahoo.com> wrote in message
news:enTc1PMtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I get the following error:
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
>
> Need help.
> I have 2 sql server instances on two different machines.
> Created a linked serve between SERVE1 and SERVER2.
> Tried to run a select query using four-part-naming-convention from server1
> to get the query results.
> From Server1 ran the following query:
> select * from servername2.datbasename.owner.tablename
> got the following error message:
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> Need help to resolve this issue.
> Don
>
>

network error

Hello,
> I get the following error:
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
>
> Need help.
> I have 2 sql server instances on two different machines.
> Created a linked serve between SERVE1 and SERVER2.
> Tried to run a select query using four-part-naming-convention from server1
> to get the query results.
> From Server1 ran the following query:
> select * from servername2.datbasename.owner.tablename
> got the following error message:
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> Need help to resolve this issue.
> Don
>
>
Hi
Check the destination server for login failures.
See if you can run a system stored procedure like sp_who2 against server2.
Regards
Mike
"Dony" wrote:

> Hello,
>
>
>

network error

Hello,
> I get the following error:
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
>
> Need help.
> I have 2 sql server instances on two different machines.
> Created a linked serve between SERVE1 and SERVER2.
> Tried to run a select query using four-part-naming-convention from server1
> to get the query results.
> From Server1 ran the following query:
> select * from servername2.datbasename.owner.tablename
> got the following error message:
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> Need help to resolve this issue.
> Don
>
>Hi
Check the destination server for login failures.
See if you can run a system stored procedure like sp_who2 against server2.
Regards
Mike
"Dony" wrote:

> Hello,
>
>
>

network error

Hello,
I get the following error:
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
Need help.
I have 2 sql server instances on two different machines.
Created a linked serve between SERVE1 and SERVER2.
Tried to run a select query using four-part-naming-convention from server1
to get the query results.
From Server1 ran the following query:
select * from servername2.datbasename.owner.tablename
got the following error message:
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
Need help to resolve this issue.
DonGo into Enterprise Manager and click on the linked server. See if you can
see any of the tables. If not, you haven't got the connection setup
correctly. Take the parameters from the linked table and try to connect
with the same authentication in Query Analyzer. Does that work? I'm
assuming you can ping the other server? Also, if the second server is an
instance, let us know if you have "Hide Server" checked in the server
configuration on that server.
"Dony" <donb@.yahoo.com> wrote in message
news:enTc1PMtEHA.1216@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I get the following error:
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
>
> Need help.
> I have 2 sql server instances on two different machines.
> Created a linked serve between SERVE1 and SERVER2.
> Tried to run a select query using four-part-naming-convention from server1
> to get the query results.
> From Server1 ran the following query:
> select * from servername2.datbasename.owner.tablename
> got the following error message:
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> Need help to resolve this issue.
> Don
>
>

Network

We are developing with SQL 7 Developer Edition and planning to install
MSDE with the VB application. We received the following question from
the IT dept. of our client:
"Does your version of SQLServer database work well across a frame
relay wide area network?"
Should there be any problems regarding speed, etc.?
Thanks!
Ernie Adsett"Ernie Adsett" <ernie@.amt.nb.ca> wrote in message
news:JhAPb.69757$IF6.1700023@.ursa-nb00s0.nbnet.nb.ca...

quote:

---

> We are developing with SQL 7 Developer Edition and planning to install
> MSDE with the VB application. We received the following question from
> the IT dept. of our client:
> "Does your version of SQLServer database work well across a frame
> relay wide area network?"
> Should there be any problems regarding speed, etc.?
> Thanks!
> Ernie Adsett
>

---

If your application is written to avoid unnecessary network traffic (use
stored procedures, avoid returning unnecessary rows in result sets etc.),
then there shouldn't be any special considerations. But to get a definite
answer, you would need to test it, of course.
Simon|||"Ernie Adsett" <ernie@.amt.nb.ca> wrote in message
news:JhAPb.69757$IF6.1700023@.ursa-nb00s0.nbnet.nb.ca...

quote:

---

> We are developing with SQL 7 Developer Edition and planning to install
> MSDE with the VB application. We received the following question from
> the IT dept. of our client:
> "Does your version of SQLServer database work well across a frame
> relay wide area network?"
> Should there be any problems regarding speed, etc.?

---

Depends entirely on your design and how good it is.

quote:

---

> Thanks!
> Ernie Adsett
>

---

sql

Network

We are developing with SQL 7 Developer Edition and planning to install
MSDE with the VB application. We received the following question from
the IT dept. of our client:
"Does your version of SQLServer database work well across a frame
relay wide area network?"
Should there be any problems regarding speed, etc.?

Thanks!

Ernie Adsett"Ernie Adsett" <ernie@.amt.nb.ca> wrote in message
news:JhAPb.69757$IF6.1700023@.ursa-nb00s0.nbnet.nb.ca...
> We are developing with SQL 7 Developer Edition and planning to install
> MSDE with the VB application. We received the following question from
> the IT dept. of our client:
> "Does your version of SQLServer database work well across a frame
> relay wide area network?"
> Should there be any problems regarding speed, etc.?
> Thanks!
> Ernie Adsett

If your application is written to avoid unnecessary network traffic (use
stored procedures, avoid returning unnecessary rows in result sets etc.),
then there shouldn't be any special considerations. But to get a definite
answer, you would need to test it, of course.

Simon|||"Ernie Adsett" <ernie@.amt.nb.ca> wrote in message
news:JhAPb.69757$IF6.1700023@.ursa-nb00s0.nbnet.nb.ca...
> We are developing with SQL 7 Developer Edition and planning to install
> MSDE with the VB application. We received the following question from
> the IT dept. of our client:
> "Does your version of SQLServer database work well across a frame
> relay wide area network?"
> Should there be any problems regarding speed, etc.?

Depends entirely on your design and how good it is.

> Thanks!
> Ernie Adsett

Nested Transaction!

The following example is given in BOL under the topic 'Nested
Transaction':
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction,this will roll back TransProc's
nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit of the inner
transaction from the first EXECUTE statement of TransProc was
overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
---
The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
Apart from this, there is another transaction named 'OutOfProc' (which,
if I am not mistaken, isn't a part & parcel of the stored procedure
'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
where or which is the nested transaction? Shouldn't a nested
transaction have 1 transaction under another transaction something like
this (similar to nested If...Else conditions):
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
--Do Something
BEGIN TRANSACTION InProc1
--Do Something More
BEGIN TRANSACTION InProc2
--Do Something More
COMMIT TRANSACTION InProc
---
Or does the presence of more than 1 SQL statement (the 2 INSERT
queries) within the transaction 'InProc' (in the stored procedure
'TransProc') make it a nested transaction?
Thanks,
ArpanA begin tran must have either a commit/rollback tran. Thus, the TransProc's
Inproc transaction is participating in the OutProc transaction. If
commit/rollback tran OutProc is not explicitly called and the connection to
the server is dropped, the transaction will be forced to rollback.
As stated in bol, if outer most commit/rollback is what really important. It
decides the final commit all or rollback all.
-oj
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
> INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
> COMMIT TRANSACTION InProc
> GO
> /* Start a transaction and execute TransProc */
> BEGIN TRANSACTION OutOfProc
> GO
> EXEC TransProc 1, 'aaa'
> GO
> /* Roll back the outer transaction,this will roll back TransProc's
> nested transaction */
> ROLLBACK TRANSACTION OutOfProc
> GO
> EXECUTE TransProc 3,'bbb'
> GO
> /* The following SELECT statement shows only rows 3 and 4 are
> still in the table. This indicates that the commit of the inner
> transaction from the first EXECUTE statement of TransProc was
> overridden by the subsequent rollback. */
> SELECT * FROM TestTrans
> GO
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Thanks, OJ, for your help. How stupid of me....actually I had
misinterpreted the example given in BOL :-)
Thanks once again,
Regards,
Arpan|||Transactions are not really nested. There can only be one outstanding
transaction context for a connection. The global variable @.@.TRANCOUNT
reports whether or not a transaction context is outstanding, and its value
immediately before a Transact-SQL statement begins executing determines
whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
0, no transaction context is outstanding for the connection. Each time a
BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
@.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
transaction started by that BEGIN TRANSACTION statement. Each sucessive
BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
COMMIT TRANSACTION statement are changes made within the transaction made
permanent by that COMMIT TRANSACTION statement. All Transact-SQL statements
that alter the state or schema of the database operate within the context of
a transaction. If a transaction context is not already outstanding, then a
new transaction context is initiated before executing the statement. If
IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
committed immediately after the statement completes. This is called
"autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit COMMIT
WORK must be issued to commit the transaction. A transaction can span
multiple statements, multiple stored procedure calls, even multiple batches.
If a transaction is outstanding, dynamic SQL executed via either EXEC() or
sp_executesql executes within that transaction context. Since changes made
while a transaction context is outstanding are not made permanent until
they're committed, ROLLBACK backs all of the changes for the entire
transaction context. The only exception is when a savepoint is specified on
a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
log that identifies a reference point which can be specified in a ROLLBACK
statement to partially backout changes made while a transaction context is
outstanding. When a ROLLBACK savepoint statement is executed, all changes
made after the save point are backed out, and the transaction context
remains outstanding.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
> INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
> COMMIT TRANSACTION InProc
> GO
> /* Start a transaction and execute TransProc */
> BEGIN TRANSACTION OutOfProc
> GO
> EXEC TransProc 1, 'aaa'
> GO
> /* Roll back the outer transaction,this will roll back TransProc's
> nested transaction */
> ROLLBACK TRANSACTION OutOfProc
> GO
> EXECUTE TransProc 3,'bbb'
> GO
> /* The following SELECT statement shows only rows 3 and 4 are
> still in the table. This indicates that the commit of the inner
> transaction from the first EXECUTE statement of TransProc was
> overridden by the subsequent rollback. */
> SELECT * FROM TestTrans
> GO
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Dude, paragraph breaks :)

> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection.
I don't know that this particularly means that transactions aren't nested.
By your definition IF..THEN statements are not really nested. I don't think
that nested transactions implies any technical innerworkings more than it
just simply implies that you can syntactically do:
BEGIN TRANSACTION
BEGIN TRANSACTION
BEGIN TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
It is more or less meaningless to us whether a stack is used or a counter
and whether or not ROLLBACK kills the whole stack or just goes back to the
original point. Either way I still consider them nested because of syntax.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection. The global variable @.@.TRANCOUNT
> reports whether or not a transaction context is outstanding, and its value
> immediately before a Transact-SQL statement begins executing determines
> whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
> 0, no transaction context is outstanding for the connection. Each time a
> BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
> @.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
> transaction started by that BEGIN TRANSACTION statement. Each sucessive
> BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
> decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
> COMMIT TRANSACTION statement are changes made within the transaction made
> permanent by that COMMIT TRANSACTION statement. All Transact-SQL
> statements
> that alter the state or schema of the database operate within the context
> of
> a transaction. If a transaction context is not already outstanding, then
> a
> new transaction context is initiated before executing the statement. If
> IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
> committed immediately after the statement completes. This is called
> "autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit
> COMMIT
> WORK must be issued to commit the transaction. A transaction can span
> multiple statements, multiple stored procedure calls, even multiple
> batches.
> If a transaction is outstanding, dynamic SQL executed via either EXEC() or
> sp_executesql executes within that transaction context. Since changes
> made
> while a transaction context is outstanding are not made permanent until
> they're committed, ROLLBACK backs all of the changes for the entire
> transaction context. The only exception is when a savepoint is specified
> on
> a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
> log that identifies a reference point which can be specified in a ROLLBACK
> statement to partially backout changes made while a transaction context is
> outstanding. When a ROLLBACK savepoint statement is executed, all changes
> made after the save point are backed out, and the transaction context
> remains outstanding.
>
> "Arpan" <arpan_de@.hotmail.com> wrote in message
> news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
>|||There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there m
ust always be a matching commit transaction for every begin transaction, and
that transactions can be "nested," many newbies erroneously assume that a r
ollback only backs out the innermost block of statements--that is, to the po
int of the innermost BEGIN TRANSACTION. The assumption follows from the pat
tern etched in their brain by the second example. A break exits the innermo
st block; therefore, it is logical to assume (erroneously, of course) that a
rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the b
lock of code bounded by the outermost begin/commit pair, the transaction con
text is initiated by the first BEGIN TRANSACTION and is terminated either by
any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:#7aRUwomFHA.2484@.TK2
MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't thi
nk
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
>
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
>
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of syntax
.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>
>|||I like to think of it as:
"Nested transactions are allowed in syntax but not semantics."
I don't know what "real" nested transactions mean, or if there is a formal d
efinition of what nested
transaction semantics means? :
Perhaps a rollback of an inner transaction would allow commit of an outer tr
ansaction? We can do
that with savepoints.
Or the other way: A commit of an inner transaction will still be committed i
f the outer transaction
does a rollback? True, we don't have this in SQL server (which, I believe, B
rian wished for in an
earlier post in some other thread). To some extent, we can work around it wi
th table variables or
opening a new connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
By your definition
> IF..THEN statements are not really nested. I don't think that nested tran
sactions implies any
> technical innerworkings more than it just simply implies that you can synt
actically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
and whether or not
> ROLLBACK kills the whole stack or just goes back to the original point. E
ither way I still
> consider them nested because of syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||But the one break can get us out of several scope operators. I see your
point, but as long as you have to commit them one at a time, I think the
common term nested for transactions is probably going to stick...
And you code still looks pretty good in plain text :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:u9My2XpmFHA.4028@.TK2MSFTNGP10.phx.gbl...
There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there
must always be a matching commit transaction for every begin transaction,
and that transactions can be "nested," many newbies erroneously assume that
a rollback only backs out the innermost block of statements--that is, to the
point of the innermost BEGIN TRANSACTION. The assumption follows from the
pattern etched in their brain by the second example. A break exits the
innermost block; therefore, it is logical to assume (erroneously, of course)
that a rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the
block of code bounded by the outermost begin/commit pair, the transaction
context is initiated by the first BEGIN TRANSACTION and is terminated either
by any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:#7aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't
> think
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of
> syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||No doubt we could use a more convienient model for how transactions work,
but your statement is great.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDRWzWqmFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I like to think of it as:
> "Nested transactions are allowed in syntax but not semantics."
>
> I don't know what "real" nested transactions mean, or if there is a formal
> definition of what nested transaction semantics means? :
> Perhaps a rollback of an inner transaction would allow commit of an outer
> transaction? We can do that with savepoints.
> Or the other way: A commit of an inner transaction will still be committed
> if the outer transaction does a rollback? True, we don't have this in SQL
> server (which, I believe, Brian wished for in an earlier post in some
> other thread). To some extent, we can work around it with table variables
> or opening a new connection.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
>

Nested set show leaves of parent

Hello,

I have the following code which will show all bottom level leaf nodes of the hierachy:

SELECT name
FROM tree
WHERE rgt = lft + 1;

I'd like to be able to filter results by a node. For example in a tree such as:

Products

ReleaseProduct

Release1

Release build 1

Release build 2

Release 2

Release 2 build 1

Release 2 build 2

Build Product

Build 1

Build 2

If Build 2 is chosen (any node with no children) I'd like to just show the Buuild 2, if ReleaseProduct is chosen Release build 1, Release build 2, Release 2 build 1 and Release 2 build 2 will be shown and if BuildProduct is chosen I'd like to display Build 1, Build 2.

I understand the prinicipals but my SQL is quite lacking anything further than the select, where statements. If anyone could please lend me a little advice on how to go about this I would be very grateful!

Thanks :)

Hello,

Can you post the schema of the table in question and what version of SQL Server you are using?

If 2005, a recursive CTE sounds like it may suit, otherwise a more "creative" solution may apply. let us know the specifics and I'm sure we can help out.

Cheers,

Rob

|||Thank's for the quick reply!

The schema is as follows:

CREATE TABLE site_category(
site_id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);

So a site may be a root, parent or child depending on the left and right values of the nodes in the hierachy. I'm using 2005 Express.

Thanks for the help!|||

Hello,

I don't know what lft or rgt is, but I'm going to assume that they contain the site_id of the parent node. So, to simplify this, let's call it ParentSiteID:

with Sites(SiteName, site_id, ParentID, NestLevel)
AS
(
SELECT [name], site_id, parentSiteID, 0
FROM site_category
WHERE [name] = 'Site123'
UNION ALL
SELECT sc.[Name], sc.Site_ID, s.Site_ID,(NestLevel + 1)
FROM Sites s
JOIN site_category sc ON s.Site_ID = sc.ParentSiteID
)
SELECT *
FROM Sites

The above example will return "Site123" and all child nodes therein (including any nested relationships). The NestLevel column indicates how deep the nesting level is. You'll need to adjust this to cater for your lft/rgt columns...

Cheers,

Rob

|||

The lft and rgt fields store values used to determine the level in the hierachy. The example from the MySQL site I am using as a guide is:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Following this I have got to the heading 'Finding the Depth of the Nodes' which produces the results I am after.

Where I'm having trouble is the heading 'Find the Immediate Subordinates of a Node' which is exactly what I need and is explained with code but I just can't figure it out! I feel there may be some subtle differences in the SQL used in this MySQL example and the TSQL SQL Server is expecting. Not to mention my SQL knowledge isn't great at this point!

I havn't tried your example but feel this post may offer a better explanation as (I may be wrong) your example looks like it assumes I am using an Adjacency List Model.

I appreciate your time! :)

|||

Hello,

OK, I understand what you're trying to do:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT TOP 100 node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, depth, node.lft
HAVING depth <= 1
ORDER BY node.lft;

Does that do what you want?

Cheers,

Rob

|||

That works exactly how I want!

Is the TOP keyword and value an approximation of the rows to be returned to be returned, as the complete result set is not loaded into memory?

Thanks :)

|||

Actually, the only reason to use TOP in the sub query is because without it, you cannot use an order by. So you could actually remove it and the corresponding order by:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, depth, node.lft
HAVING depth <= 1
ORDER BY node.lft;

Cheers,

Rob

|||Oh I see, Thanks again!sql

Nested Select?

Hi all,
I have the following table
id (autonumber)
category1 (int)
category2 (int)
booking_month (int)
booking_year (int)
I have records in the table for booking_year = 2004 and booking_year = 2005,
for example
id, category1, category2, booking_month, booking_year
1, 20, 30, 4, 2004
1, 20, 31, 10, 2004
1, 20, 30, 4, 2005
I need a SQL statement there lists all those records that are in 2004 but no
in 2005 for a particular category1.
Any ideas?
Thanks,
IvanYou could use a nested sub-query, however, you can also just select from
[booking] as B04 for booking_year = 2004 and then left join [booking] as B05
on booking_year = 2005. Only include records where B05.id is NULL.
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have the following table
> id (autonumber)
> category1 (int)
> category2 (int)
> booking_month (int)
> booking_year (int)
> I have records in the table for booking_year = 2004 and booking_year =
2005,
> for example
> id, category1, category2, booking_month, booking_year
> 1, 20, 30, 4, 2004
> 1, 20, 31, 10, 2004
> 1, 20, 30, 4, 2005
> I need a SQL statement there lists all those records that are in 2004 but
no
> in 2005 for a particular category1.
> Any ideas?
> Thanks,
> Ivan
>|||"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in
news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl:

> Hi all,
> I have the following table
> id (autonumber)
> category1 (int)
> category2 (int)
> booking_month (int)
> booking_year (int)
> id, category1, category2, booking_month, booking_year
> 1, 20, 30, 4, 2004
> 1, 20, 31, 10, 2004
> 1, 20, 30, 4, 2005
> I need a SQL statement there lists all those records that are in 2004
> but no in 2005 for a particular category1.
SELECT * FROM [tablename]
WHERE (booking_year <> 2005) AND (category1 = particular_value)
HTH|||This does not work:
SELECT o1.category2 FROM offline o1
LEFT JOIN offline o2
ON o2.booking_year = 2005
WHERE (o1.category1 = 989 AND o1.booking_month = 4 AND o1.booking_year =
2004)
and o2.id_no is null
"JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
news:%23qJsJP3DFHA.1188@.tk2msftngp13.phx.gbl...
> You could use a nested sub-query, however, you can also just select from
> [booking] as B04 for booking_year = 2004 and then left join [booking] as
B05
> on booking_year = 2005. Only include records where B05.id is NULL.
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl...
> 2005,
but
> no
>|||Or like this
SELECT * FROM [tablename]
WHERE booking_year =2004 AND category1 not in (select category1 from
[tablename] t1 where year = 2005)
Hth
"Chris Cheney" <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message
news:Xns95F9884DB1851cjc1nospamucscamacu
k@.131.111.8.48...
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in
> news:uW4JVI3DFHA.548@.TK2MSFTNGP14.phx.gbl:
>
> SELECT * FROM [tablename]
> WHERE (booking_year <> 2005) AND (category1 = particular_value)
> HTH|||The compare between category1 and category2 should be in the join.. on..
clause. Try this:
select
B04.booking_year,
B04.category1,
B04.category2
from
offline as B04
left join
offline as B05
-- join both aliases of offline on category1 and category2. also
filter B05 on 2005.
on B05.booking_year = 2005 and
B05.category1 = B04.category1 and
B05.category2 = B04.category 2
where
B04.booking_year = 2004 and
B05.id is NULL -- Doesn't matter which B05 column is null
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OLdjle3DFHA.3120@.TK2MSFTNGP12.phx.gbl...
> This does not work:
> SELECT o1.category2 FROM offline o1
> LEFT JOIN offline o2
> ON o2.booking_year = 2005
> WHERE (o1.category1 = 989 AND o1.booking_month = 4 AND o1.booking_year =
> 2004)
> and o2.id_no is null
> "JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:%23qJsJP3DFHA.1188@.tk2msftngp13.phx.gbl...
> B05
> but
>|||The original B04 records total 112.
The original B05 records total 24.
Logic and simple math tell me that I should get 112-24 = 88!!
When I run your suggested statement I get 85 records.
When I run this statement:
SELECT category2 FROM offline WHERE category1 = 989 AND booking_month = 4
AND booking_year = 2004 AND category2 NOT IN
(SELECT category2FROM offline WHERE category1 = 989 AND booking_month = 4
AND booking_year = 2005)
I get 87 records.
Strange indeed!!!
"JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
news:ubdN7t3DFHA.2232@.TK2MSFTNGP14.phx.gbl...
> The compare between category1 and category2 should be in the join.. on..
> clause. Try this:
> select
> B04.booking_year,
> B04.category1,
> B04.category2
> from
> offline as B04
> left join
> offline as B05
> -- join both aliases of offline on category1 and category2. also
> filter B05 on 2005.
> on B05.booking_year = 2005 and
> B05.category1 = B04.category1 and
> B05.category2 = B04.category 2
> where
> B04.booking_year = 2004 and
> B05.id is NULL -- Doesn't matter which B05 column is null
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OLdjle3DFHA.3120@.TK2MSFTNGP12.phx.gbl...
from
as
=
2004
>|||This was my original statement that I tried but I get always 1 less than the
expected result :(
"AM" <shahdharti@.gmail.com> schrieb im Newsbeitrag
news:e8PTwj3DFHA.1496@.TK2MSFTNGP14.phx.gbl...
> Or like this
> SELECT * FROM [tablename]
> WHERE booking_year =2004 AND category1 not in (select category1 from
> [tablename] t1 where year = 2005)
> Hth
>
> "Chris Cheney" <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message
> news:Xns95F9884DB1851cjc1nospamucscamacu
k@.131.111.8.48...
>|||Ivan,
Can you be more precise about what you want? What does
"in 2004 but no in 2005" mean? I assume it means
Find all rows where booking_year = 2004 but for which
there is not a matching row with booking_year = 2005.
But ... you haven't made it clear what "matching row" means. Does
a matching 2005 row need to have the same category1, category2,
and booking_month, or just some of those columns? And your
data here says id is "autonumber", but you show three identical id
values - does the matching 2005 row have to have the same id value?
You said you think you should get a number of rows that is
the number of 2004 rows minus the number of 2005 rows, but
how do you know every one of the 2005 rows in your table
matches exactly one 2004 row? Maybe some 2004 rows appear
twice, and maybe some 2005 rows have no corresponding 2004
row.
You may know what it means for a 2004 row to be in 2005 as
well, but unless you describe it clearly in terms of the columns of
this table, you can't expect to be able to write a query that will
give you what you want.
Steve Kass
Drew University
Ivan Debono wrote:

>Hi all,
>I have the following table
>id (autonumber)
>category1 (int)
>category2 (int)
>booking_month (int)
>booking_year (int)
>I have records in the table for booking_year = 2004 and booking_year = 2005
,
>for example
>id, category1, category2, booking_month, booking_year
>1, 20, 30, 4, 2004
>1, 20, 31, 10, 2004
>1, 20, 30, 4, 2005
>I need a SQL statement there lists all those records that are in 2004 but n
o
>in 2005 for a particular category1.
>Any ideas?
>Thanks,
>Ivan
>
>|||Perhaps you should be joining on booking_month too ?
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OzdKJc4DFHA.3536@.TK2MSFTNGP15.phx.gbl...
> The original B04 records total 112.
> The original B05 records total 24.
> Logic and simple math tell me that I should get 112-24 = 88!!
> When I run your suggested statement I get 85 records.
> When I run this statement:
> SELECT category2 FROM offline WHERE category1 = 989 AND booking_month = 4
> AND booking_year = 2004 AND category2 NOT IN
> (SELECT category2FROM offline WHERE category1 = 989 AND booking_month = 4
> AND booking_year = 2005)
> I get 87 records.
> Strange indeed!!!
> "JohnnyAppleseed" <someone@.microsoft.com> schrieb im Newsbeitrag
> news:ubdN7t3DFHA.2232@.TK2MSFTNGP14.phx.gbl...
=
> from
[booking]
> as
booking_year
> =
> 2004
>

Nested SELECT HELP....

Hi all,
I have the following databasedesign :

www.marinescu.dk/databasedesign.pdf

which i have a nested SELECT on but i need some more information which i
don't know how to retrieve. I have the following SELECT :

SELECT DISTINCT Resource.ResourceID, Localized.ResourceValue,
Localized.Font, Resource.ResourceName, Resource.Comment, Type.TypeName FROM
Localized, Resource,Type WHERE Localized.ResourceID = Resource.ResourceID
AND Resource.TypeID = Type.TypeID ORDER BY Resource.ResourceID

For some Resources there are Rules. I will like to have a new column named
RulesText in my query where there will be shown the RuleText if there is any
for that particular Resource.

Could anybody help me here ??

Best Regards
Mihai MarinescuSELECT DISTINCT
R.ResourceID, L.ResourceValue, L.Font, R.ResourceName, R.Comment,
T.TypeName, RL.RuleText
FROM Localized AS L
JOIN Resource AS R ON L.ResourceID = R.ResourceID
JOIN Type AS T ON R.TypeID = T.TypeID
LEFT JOIN ResourceRule AS RR ON RR.resourceid = R.resourceid
LEFT JOIN Rules AS RL ON RL.ruleid = RR.ruleid
ORDER BY R.ResourceID

(untested)

--
David Portas
----
Please reply only to the newsgroup
--

Nested Recordsets

I need to decrease the amount of time it takes to return a set of data of the following format:

State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]

current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn

As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.Unclear what you want here. If you just want to return all the counties, cities, streets, etc for a given State, just use a multi-table query with indexed foreign keys.|||Are you using Cursors or subqueries to retrive all those informations ?|||hydrated??|||Look it up in Books Online, Rudy.|||thank you, but i couldn't find it

would you be kind enough to give me the exact url|||Ha! Made you look!

Sorry, Rudy, but for some reason at 12:30 last night the idea of you searching Books Online for "hydrated" seemed hillariously funny to me.

D'oh!|||yeah, well, i even tried google, searching for "microsoft sql server hydrate"

found this -- http://www.devarticles.com/showblog/345/Could-you-pee-on-my-sandwich-please|||The order of data retrieval looks a little backwards, - should you let the user select a country first? And don't you have a relation between countries and states?

Nested or Subquery help

i am new to SQL Server DB.
i have a typical query problem.
master table contains Data in following format
id stype sid svalue
1 status 1 approved
2 status 2 pending
3 request 1 draft
4 request 2 waiting
etc

in my query i need to substitute the svalue in place of sid
e.g.
select requestid ,bstatus, brequest from request ;
should give
----------
R1122 approved draft
----------
instead of
R1122 1 1
*****************
link field is column name (quite unusual )
like for status column , it is bstatus column name and it is stored in master table as status .

i need to do the following in 1 query only.
kindly suggest some nested query to do the same
kindly help
Smitaselect requestid
, s.svalue as statusvalue
, r.svalue as requestvalue
from request
inner
join master as s
on bstatus = s.sid
and s.stype = 'status'
inner
join master as r
on bstatus = r.sid
and r.stype = 'request'

Nested Loop Join - need help :)

i'm running the following code on Ms SQL Server 2000, Query Analyzer to analyze the result of Nested Loop Join.

SET STATISTICS PROFILE ON
GO
SELECT pdN.ProductID, pdN.ProductName,
spN.CompanyName, spN.ContactName
FROM dbo.ProductsNew pdN
INNER JOIN dbo.SuppliersNew spN
ON pdN.SupplierId = spN.SupplierId
GO

but the execution plan give me the following result :-

http://i31.photobucket.com/albums/c366/i3lu3fun/executionplan.jpg

instead of using nested, why does it using hash join? is there anything wrong with my code?Hi

Welcome to the forum :D

Nowt wrong with your query. I didn't enlarge your image however the optimiser will select the best plan it can (within certain provisos e.g. it selects the best plan within a time limit, it bases it's plan on available statistics etc.). As such - it is probable for this query that a hash join is better than a nested loop join. BOL illustrates such cases:

A nested loops join is particularly effective if the outer input is quite small and the inner input is preindexed and quite large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are far superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.

Short of using hints (which is a bad idea unless you really know what you are doing and, arguably, not even then) all you can do is make sure that you write good, efficient SQL (as you have), ensure statistics are up to date and indexes are optimal. SQL Server does the rest.

HTH|||Thank you for replying :D

Is it possible for me to still get the result of using nested loop join because i need to make a comparison between the execution time of using nested loop, hash & merge join. Will i get the result that i want if i use FORCE option?|||You can force these using Join Hints - check the BOL entries for "FROM" and "Hints".|||BTW - out of curiosity - how come you want to compare the three rather than leave it up to the optimiser?|||hehe, thanks.
I'm doing my FYP, and i need this results to be included in the report. Plus, i need to come up with a better algorithm, to retrieve data in distributed database. Thanks again for ur help.|||where is it the BOL entries :p, sorry newbie here i couldn't find it.|||I'm doing my FYP, and i need this results to be included in the report.
Aw - an RFH.

Ah well - I noticed a little gotcha that I didn't know re hints (unsurprising as you can probably tell I don't tend to use them) - see if you can spot it. Having reread BOL I think that it is referred to but it is rather under stated...|||where is it the BOL entries :p, sorry newbie here i couldn't find it.Well - you are looking for Join Hints so I guess you need to search for... :rolleyes:

Nested Loop in SQL Query

Hi,
I'm probably missing something obvious (either that or doing this totally wrong).
I'm trying to use a nested loop to generate the following results:
Unit Day1 Day2 Day3 Day4 Day5
Name1 25 45 89 54 76
Name2 48 54 81 74 98
What I have so far is this:
WHILE @.FCount < @.TotalFoodUnits
BEGIN
SELECT
(SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@.FCount + 1)) AS Unit

WHILE @.FDCount < @.Days
BEGIN
SELECT
(SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @.FDCount, @.pdStartDate)) AS Rev
SET @.FDCount = @.FDCount + 1
END
SET @.FCount = @.FCount + 1
END
Any suggestions pleaseThis won't do what you want. It looks like you need to do a pivot-table type query that aggregates your data. With loops, you're just going to generate a whole bunch of separate result sets. Doing a pivot type table is difficult if you don't know how many columns you'll have. One thing I've done is create a dynamic SQL statement with cursors that pivots them all together. When the statement is built, it looks like it was basically hardcoded, but it is correct for the data as it is when it is run. The cursor(s) you need depend on what the varying numbers of rows or columns are that you have. Build your SQL statement by looping through the cursors as needed. See if you can hardcode a statement that will work and then look to see what the repeating elements are and how would you get those repeating elements with a SQL statement. You may need to query schema tables to get field names or use DISTINCT to get what values you have. I know thi sisn't very clear, but if you hardcode it and then see how that statement repeats, you might see what I'm talking about.
|||Along the lines of what PDraigh has advised, you might find this post helpful:http://forums.asp.net/1041295/showpost.aspx
|||Hi,
Thanks for the info above, it actually led me to this page:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
This provides an alternative solution which pivots the data using .net rather than trying to force sql into doing it for you.

Nested Cases - is it possible?

I have the following code :
select {?TroubleType}
case "All Types":
{tbl_tickets.CustomerAcctNumber} <> '0' and
{tbl_tickets.OpenDate} >= {?BeginDate} and
{tbl_tickets.OpenDate} <= {?EndDate} and
{tbl_tickets.CustomerAcctNumber} <> '184114' and
{tbl_tickets.TroubleType} <> ""
case {?TroubleType}:
{tbl_tickets.TroubleID}<> 0 and
{tbl_tickets.OpenDate}>= {?BeginDate} and
{tbl_tickets.OpenDate}<= {?EndDate} and
{tbl_tickets.CustomerAcctNumber}<> '184114' and
{tbl_tickets.TroubleType}= {?TroubleType}

and I need to duplicate it for 3 other parameter fields. Is there a way to nest the case statements - I continually get an error everytime I try add just one other case statement to a new parameter...Not clear. U want to put on case within another?|||Yes -

I need to do this same type of functionality for a total of 4 parameters - and have it interchangeably choose the option selected or all for each individual paramter:

example: Give me all the cities
and just troubletype of -email
with all the status

Or Just this city, with just this trouble with just open status...|||Still I'm not clear. But if you want to nest ur case statements, here is an example

//starts here

select {?state}
case "MN" :
(

select {?city}

case "City A" :
"here is some code for city A"

case "City B" :

"here is some code for city B"

)

case "CA" :
"Here is some code for CA"

case "FL" :
"Here is some code for FL"

Needs Help !

Hi all
I have the following two tables
Table #1
Item No Name
1 Alfa
2 Bravo
3 Charlie
4 Delta
Table #2
Sr No Item No Item Name Shipped Time
1 1 Alfa 10 Mins
2 1 Alfa 20 Mins
3 2 Bravo 50 Mins
4 1 Alfa 40 Mins
5 1 Alfa 30 Mins
6 3 Charlie 30 Mins
7 4 Delta 20 Mins
8 4 Delta 10 Mins
There is a relation ship between the two tables on Item No
Can somebody help me in writing the query to get following output
Item Name Sr No Shipped Time
Alfa 1 10 Min
2 20 Min
4 40 Min
5 30 Min
Bravo 3 50 Min
Charlie 6 30 Min
Delta 7 20 Min
8 10 Min
Best Regards
SierraHi
This is an idea which was given by Steve Kass.Modify it for your needs
select CustomerID, OrderID from (
select
CustomerID as Position_1,
1 as Position_2,
CustomerID,
cast(OrderID as varchar(15)) as OrderID
from Northwind..Orders
union all
select distinct
CustomerID,
2,
'',
N''
from Northwind..Orders
) Report
order by Position_1, Position_2
go
"Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message news:urBbgdGKFHA.4
84@.TK2MSFTNGP15.phx.gbl...
Hi all
I have the following two tables
Table #1
Item No Name
1 Alfa
2 Bravo
3 Charlie
4 Delta
Table #2
Sr No Item No Item Name Shipped Time
1 1 Alfa 10 Mins
2 1 Alfa 20 Mins
3 2 Bravo 50 Mins
4 1 Alfa 40 Mins
5 1 Alfa 30 Mins
6 3 Charlie 30 Mins
7 4 Delta 20 Mins
8 4 Delta 10 Mins
There is a relation ship between the two tables on Item No
Can somebody help me in writing the query to get following output
Item Name Sr No Shipped Time
Alfa 1 10 Min
2 20 Min
4 40 Min
5 30 Min
Bravo 3 50 Min
Charlie 6 30 Min
Delta 7 20 Min
8 10 Min
Best Regards
Sierra|||I guess this query should solve your problem
item --> Table #1
iShipped --> Table #2
select i.Name, s.SrNo, s.[Shipped Time]
from item i inner join iShipped s on i.[item no] = s.[item no]
order by i.Name
Krish
"Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message
news:urBbgdGKFHA.484@.TK2MSFTNGP15.phx.gbl...
Hi all
I have the following two tables
Table #1
Item No Name
1 Alfa
2 Bravo
3 Charlie
4 Delta
Table #2
Sr No Item No Item Name Shipped Time
1 1 Alfa 10 Mins
2 1 Alfa 20 Mins
3 2 Bravo 50 Mins
4 1 Alfa 40 Mins
5 1 Alfa 30 Mins
6 3 Charlie 30 Mins
7 4 Delta 20 Mins
8 4 Delta 10 Mins
There is a relation ship between the two tables on Item No
Can somebody help me in writing the query to get following output
Item Name Sr No Shipped Time
Alfa 1 10 Min
2 20 Min
4 40 Min
5 30 Min
Bravo 3 50 Min
Charlie 6 30 Min
Delta 7 20 Min
8 10 Min
Best Regards
Sierra|||Sireea,
Ideally this should be done at the client side. Still if you have some compe
lling reasons..
CREATE TABLE #Table1(ItemNo int, ItemName VARCHAR(10))
INSERT INTO #Table1 VALUES(1,'Alfa')
INSERT INTO #Table1 VALUES(2,'Bravo')
INSERT INTO #Table1 VALUES(3,'Charlie')
INSERT INTO #Table1 VALUES(4,'Delta')
CREATE TABLE #Table2(SrNo int, ItemNo int, Shippedin int)
INSERT INTO #Table2 VALUES(1,1,10)
INSERT INTO #Table2 VALUES(2,1,20)
INSERT INTO #Table2 VALUES(3,2,50)
INSERT INTO #Table2 VALUES(4,1,40)
INSERT INTO #Table2 VALUES(5,1,30)
INSERT INTO #Table2 VALUES(6,3,30)
INSERT INTO #Table2 VALUES(7,4,20)
INSERT INTO #Table2 VALUES(8,4,10)
SELECT CASE WHEN Exists(SELECT 1 FROM #Table2 T WHERE B.SrNo > T.SrNo AND A
.ItemNo = T.ItemNo)
THEN '' ELSE A.ItemName END As ItemName,
SrNo, Shippedin
FROM #Table1 A
INNER JOIN #Table2 B
ON A.ItemNo = B.ItemNo
ORDER By A.ItemNo
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Sierra" <senthilvel_sundaram@.yahoo.com> wrote in message news:urBbgdGKFHA.4
84@.TK2MSFTNGP15.phx.gbl...
Hi all
I have the following two tables
Table #1
Item No Name
1 Alfa
2 Bravo
3 Charlie
4 Delta
Table #2
Sr No Item No Item Name Shipped Time
1 1 Alfa 10 Mins
2 1 Alfa 20 Mins
3 2 Bravo 50 Mins
4 1 Alfa 40 Mins
5 1 Alfa 30 Mins
6 3 Charlie 30 Mins
7 4 Delta 20 Mins
8 4 Delta 10 Mins
There is a relation ship between the two tables on Item No
Can somebody help me in writing the query to get following output
Item Name Sr No Shipped Time
Alfa 1 10 Min
2 20 Min
4 40 Min
5 30 Min
Bravo 3 50 Min
Charlie 6 30 Min
Delta 7 20 Min
8 10 Min
Best Regards
Sierra

need to write complex query without cursor

Hi all,
I am trying to do the following and am getting stuck.Your help wil be higly
appreciated.
I am matching two tables.If i get a single matching row then get that single
row
but if i get >1 matching rows i should select only the one with the latest
date.
for example
Table1
--
id value
1 10
2 20
3 30
Table 2
--
id param updateDate
1 10 day1
1 20 day2
1 25 day3
2 20 day2
2 40 day 3
3 30 day 2
(day1 <day2<day3 etc)
So i should get results as
Result
--
1 25 day3
2 40 day3
3 30 day2
how can I do this without a cursor?
Thanks for your help.on SQL 2005,
select id, param, updateDate
from table2
where row_number() over(partition by id order by updateDate desc) = 1
*untested*|||Try this, if you don't have SQL Server 2005:
select Table2.id, Table2.param, Table2.updateDate
from Table2
join Table1 on Table1.id = Table2.id
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
)
or
select id, param, updateDate
from Table2
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
) and exists (
select * from Table1
where Table1.id = Table2.id
)
This can yield multiple rows per id if (id, updateDate) is not unique
and there are two or more updates for an id on most recent update date.
For SQL Server 2005, Alexander has the right idea, but windowed
functions cannot appear in the WHERE clause, and you'll have to do this:
select id, param, updateDate
from (
select
id, param, updateDate,
row_number() over (partition by id order by updateDate desc) as rn
from Table2
) as T
where rn = 1
Steve Kass
Drew University
tech77 wrote:

>Hi all,
>I am trying to do the following and am getting stuck.Your help wil be higly
>appreciated.
>I am matching two tables.If i get a single matching row then get that singl
e
>row
> but if i get >1 matching rows i should select only the one with the latest
>date.
>for example
>Table1
>--
>id value
>1 10
>2 20
>3 30
>Table 2
>--
>id param updateDate
>1 10 day1
>1 20 day2
>1 25 day3
>2 20 day2
>2 40 day 3
>3 30 day 2
>(day1 <day2<day3 etc)
>So i should get results as
>Result
>--
>1 25 day3
>2 40 day3
>3 30 day2
>how can I do this without a cursor?
>Thanks for your help.
>|||Here's one method, assuming that each id will only have one row per
day. If you have multiple rows per id per day, you have to have some
rule to determine which row you want returned.
Stu
DECLARE @.Table1 TABLE (id int, value int)
INSERT INTO @.TABLE1
SELECT 1, 10
UNION ALL
SELECT 2, 20
UNION ALL
SELECT 3, 30
DECLARE @.Table2 TABLE (id int, param int, UpdateDate smalldatetime)
INSERT INTO @.TABLE2
SELECT 1, 10, '20060301'
UNION ALL
SELECT 1, 20, '20060302'
UNION ALL
SELECT 1, 25, '20060303'
UNION ALL
SELECT 2, 20, '20060302'
UNION ALL
SELECT 2, 40, '20060303'
UNION ALL
SELECT 3, 30, '20060302'
SELECT t2.id, t2.param, t2.UpdateDate
FROM @.table1 t1 JOIN @.Table2 t2 ON t1.id=t2.id
JOIN (SELECT id, MAX(updateDate) AS upDateDate
FROM @.Table2 t2
GROUP BY id) t2_2 ON t2.ID = t2_2.ID
AND t2.UpDateDate = t2_2.UpdateDate

Need to use MID function in SQL

When I try to use the MID statement in a SQL view, it reports 'function not recognized'. Is there some other way to execute the following?

CASE WHEN Mid(SearchID , 4 , 1) = '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END.

I have a column with two data set possibilities: aaa-bbbbb and aaaa-bbbbb. I only want the data to the left of the dash.

Thanks.

Ernie

You have to combine sql sever string function

like "left" and "right" to achive you requirements

I think the equivalent of vb mid function is the "substring" function

This example shows how to return only a portion of a character string. From the authors table, this query returns the last name in one column with only the first initial in the second column.

USE pubs SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors ORDER BY au_lname 

|||

create table #test (SearchID varchar(49))
insert into #test values('aaa-bbbbb')
insert into #test values('aaaa-bbbbb')

one way using ParseName
Select ParseName(Replace(SearchID , '-', '.'), 2)
from #test

and another using left and charindex
select distinct LEFT(SearchID ,CHARINDEX('-',SearchID )-1 )
from #test

and a third using case substring and left
select CASE substring(SearchID , 4 , 1) when '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END
from #test

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Thanks! I appreciate the help.