is this wrong ?

declare @.name1 varchar(100)
select @.name = 'table1'
Truncate table @.name
I get an error at the truncate table statement
Whats the correct way of writing this ?
ThanksWhy do it with a variable? You just need to say:
truncate table table1;
(See TRUNCATE TABLE
<http://msdn.microsoft.com/library/e..._ta-tz_2hk5.asp> in BOL.)
Is this part of something larger that's causing you issues? If you need
to do this in a repeating loop for many tables then you'll have to use
dynamic sql (see sp_executesql
<http://msdn.microsoft.com/library/e..._ea-ez_2h7w.asp>
in BOL). Something like:
exec sp_executesql
N'TRUNCATE TABLE @.tablename',
N'@.tablename sysname',
@.tablename = N'table1';
with a looping wrapper (ie. cursor) around it.
*mike hodgson*
http://sqlnerd.blogspot.com
Hassan wrote:

>declare @.name1 varchar(100)
>select @.name = 'table1'
>Truncate table @.name
>I get an error at the truncate table statement
>Whats the correct way of writing this ?
>Thanks
>
>|||Mike Hodgson (e1minst3r@.gmail.com) writes:
> exec sp_executesql
> N'TRUNCATE TABLE @.tablename',
> N'@.tablename sysname',
> @.tablename = N'table1';
This has the same problem as the original post. You cannot use a variable
to hold the name of a table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hassan wrote:
> declare @.name1 varchar(100)
> select @.name = 'table1'
> Truncate table @.name
> I get an error at the truncate table statement
> Whats the correct way of writing this ?
> Thanks
Use dynamic SQL:
Declare @.sql nvarchar(255)
Set @.sql = N'Truncate Table [' + @.name + N']'
EXEC (@.sql)
David Gugick - SQL Server MVP
Quest Software|||Yeah (oops). I discovered that just after I'd posted this reply (when I
was building up a reply to the next post regarding dropping all foreign
keys in a database) - same with an ALTER TABLE.
*mike hodgson*
http://sqlnerd.blogspot.com
Erland Sommarskog wrote:

>Mike Hodgson (e1minst3r@.gmail.com) writes:
>
>This has the same problem as the original post. You cannot use a variable
>to hold the name of a table.
>
>|||SQL is a compiled programming language. Do you have any idea what a
compiler is? Please, please do not try to write SQL; you have no idea
what you are doing and need at least a year of intense education and
not just in SQL. .|||How is the book coming along?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1140477991.507316.78270@.f14g2000cwb.googlegroups.com...
> SQL is a compiled programming language. Do you have any idea what a
> compiler is? Please, please do not try to write SQL; you have no idea
> what you are doing and need at least a year of intense education and
> not just in SQL. .
>

Is this join not possible?

Ok maybe I am having a senior moment here or something, I thought this
was something you could do, but maybe I am just remembering wrong.
I am trying to join Customers and Addresses, but I only 1 a maximum of
1 address joined. So in other words, if I did a join and there was a
customer w/ 5 addresses, that would result in 5 records being
returned, and I just want it to give me 1 record with the first
address joined.
I am using this Select statement:
SELECT C.*, A.*
FROM Customers C
LEFT OUTER JOIN
(SELECT TOP 1 *
FROM CustomerAddresses
WHERE (CustomerAddresses.CustomerId = C.CustomerId)) AS A
ON C.CustomerId = A.CustomerId
It is saying that "C.CustomerId" is not valid in my nested select
statement.
I can probably do this with a join to a user defined function
returning a table, but I could have sworn that what I was trying to do
was do-able...
Can anyone help?Hi
"cmay" wrote:

> Ok maybe I am having a senior moment here or something, I thought this
> was something you could do, but maybe I am just remembering wrong.
With SQL 2005 this can be done with a function and APPLY
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
> I am trying to join Customers and Addresses, but I only 1 a maximum of
> 1 address joined. So in other words, if I did a join and there was a
> customer w/ 5 addresses, that would result in 5 records being
> returned, and I just want it to give me 1 record with the first
> address joined.
> I am using this Select statement:
> SELECT C.*, A.*
> FROM Customers C
> LEFT OUTER JOIN
> (SELECT TOP 1 *
> FROM CustomerAddresses
> WHERE (CustomerAddresses.CustomerId = C.CustomerId)) AS A
> ON C.CustomerId = A.CustomerId
> It is saying that "C.CustomerId" is not valid in my nested select
> statement.
If you are using SQL 2000 then assumming you have an AddressId primary on
your address table, you could try
SELECT C.*, A.*
FROM Customers C
LEFT JOIN (
(SELECT CustomerId, MAX(AddressId) AS AddressId
FROM CustomerAddresses
GROUP BY CustomerId ) B
JOIN CustomerAddresses A ON B.AddressId = A.AddressId AND B.CustomerId
= A.CustomerId ) ON C.CustomerId = A.CustomerId AND C.CustomerId =
B.CustomerId

> I can probably do this with a join to a user defined function
> returning a table, but I could have sworn that what I was trying to do
> was do-able...
> Can anyone help?
>
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0E784E06-484B-44B2-803F-2EF3E53FAE1E@.microsoft.com...[vbcol=seagreen]
> Hi
> "cmay" wrote:
>
> With SQL 2005 this can be done with a function and APPLY
> http://msdn2.microsoft.com/en-us/library/ms175156.aspx
> If you are using SQL 2000 then assumming you have an AddressId primary on
> your address table, you could try
> SELECT C.*, A.*
> FROM Customers C
> LEFT JOIN (
> (SELECT CustomerId, MAX(AddressId) AS AddressId
> FROM CustomerAddresses
> GROUP BY CustomerId ) B
> JOIN CustomerAddresses A ON B.AddressId = A.AddressId AND
> B.CustomerId
> = A.CustomerId ) ON C.CustomerId = A.CustomerId AND C.CustomerId =
> B.CustomerId
>
Or you can use OUTER APPLY or ROwNumber.
These are from AdventureWorks
SELECT C.ContactID, A.AddressID
FROM HumanResources.Employee C
outer apply
(
SELECT TOP 1 *
FROM HumanResources.EmployeeAddress EA
WHERE (EA.EmployeeID = c.EmployeeID)
) AS A
order by contactID
with ca(ContactID,AddressID,RowNum)
as(
SELECT
C.ContactID,
ea.AddressID,
ROW_NUMBER() over (partition by c.ContactID order by ea.AddressID) rownum
FROM HumanResources.Employee C
left join HumanResources.EmployeeAddress EA
on EA.EmployeeID = c.EmployeeID
)
select ContactID,AddressID
from ca
order by ContactID
David|||Not the way you've written it. You can't correlate inside a nested
query like that.
Here's a possible way to do it (Assuming 'xxx' is your primary key for
the CustomerAddresses table)
-Dave
SELECT c.*, a.*
FROM dbo.Customers c
LEFT JOIN (
SELECT CustomerID, MAX(xxx) AS xxx
FROM dbo.CustomerAddresses
GROUP BY CustomerID) ca
ON ca.CustomerID = c.CustomerID
LEFT JOIN dbo.CustomerAddresses a
ON a.xxx = ca.xxx|||For reaons known only to Microsoft, if the table on the right hand side of a
join is correlated, you have to write APPLY instead of JOIN. Perhaps MS will
one day follow SQL-99. In the meantime, just swear (whilst the kids aren't
around).
OTOH, since TOP is an MS extension, even if MS did support ANSI SQL-99
correlated joins, your query isn't ANSI. Use:
row_number() over (partition by CustomerId order by <something> ) as AddrNo
where <something> is some means of ordering your addresses that allows you
to qualify what you mean by "the first address". You can then add :
and A.AddrNo = 1
to your join's ON clause.
"cmay" <cmay@.walshgroup.com> wrote in message
news:1169932681.524014.158670@.l53g2000cwa.googlegroups.com...
> Ok maybe I am having a senior moment here or something, I thought this
> was something you could do, but maybe I am just remembering wrong.
> I am trying to join Customers and Addresses, but I only 1 a maximum of
> 1 address joined. So in other words, if I did a join and there was a
> customer w/ 5 addresses, that would result in 5 records being
> returned, and I just want it to give me 1 record with the first
> address joined.
> I am using this Select statement:
> SELECT C.*, A.*
> FROM Customers C
> LEFT OUTER JOIN
> (SELECT TOP 1 *
> FROM CustomerAddresses
> WHERE (CustomerAddresses.CustomerId = C.CustomerId)) AS A
> ON C.CustomerId = A.CustomerId
> It is saying that "C.CustomerId" is not valid in my nested select
> statement.
> I can probably do this with a join to a user defined function
> returning a table, but I could have sworn that what I was trying to do
> was do-able...
> Can anyone help?
>|||On 28 Jan, 09:53, "Mark Yudkin" <DoNotContac...@.boingboing.org> wrote:
> For reaons known only to Microsoft, if the table on the right hand side of
a
> join is correlated, you have to write APPLY instead of JOIN. Perhaps MS wi
ll
> one day follow SQL-99. In the meantime, just swear (whilst the kids aren't
> around).
>
This is not for "reasons known only to Microsoft". Standard SQL also
doesn't allow correlated queries to reference outside of the derived
table expression unless the LATERAL keyword is used. Microsoft chose
to use APPLY instead of LATERAL but the restriction is exactly the
same one in the case of SQL99 and SQL2003 - namely that a standard
(non-lateral) derived query cannot define itself recursively by using
a correlation outside of its own scope.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I think you're confusing LATERAL and recursive SQL (CTE's, also only partly
supported in SQL 2005) with correlation between table expressions in JOINS.
And in any case, there is no excuse for creating product-specific,
non-standard syntax when following the standard would involve no loss of
functionality. APPLY is not in the SQL-99 (ISO 9074, part 2) standard (let
alone SQL 2003), but the functionality is.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1169994883.390623.313580@.j27g2000cwj.googlegroups.com...
> On 28 Jan, 09:53, "Mark Yudkin" <DoNotContac...@.boingboing.org> wrote:
> This is not for "reasons known only to Microsoft". Standard SQL also
> doesn't allow correlated queries to reference outside of the derived
> table expression unless the LATERAL keyword is used. Microsoft chose
> to use APPLY instead of LATERAL but the restriction is exactly the
> same one in the case of SQL99 and SQL2003 - namely that a standard
> (non-lateral) derived query cannot define itself recursively by using
> a correlation outside of its own scope.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Is this join not possible?

Ok maybe I am having a senior moment here or something, I thought this
was something you could do, but maybe I am just remembering wrong.
I am trying to join Customers and Addresses, but I only 1 a maximum of
1 address joined. So in other words, if I did a join and there was a
customer w/ 5 addresses, that would result in 5 records being
returned, and I just want it to give me 1 record with the first
address joined.
I am using this Select statement:
SELECT C.*, A.*
FROM Customers C
LEFT OUTER JOIN
(SELECT TOP 1 *
FROM CustomerAddresses
WHERE (CustomerAddresses.CustomerId = C.CustomerId)) AS A
ON C.CustomerId = A.CustomerId
It is saying that "C.CustomerId" is not valid in my nested select
statement.
I can probably do this with a join to a user defined function
returning a table, but I could have sworn that what I was trying to do
was do-able...
Can anyone help?Hi
"cmay" wrote:
> Ok maybe I am having a senior moment here or something, I thought this
> was something you could do, but maybe I am just remembering wrong.
With SQL 2005 this can be done with a function and APPLY
http://msdn2.microsoft.com/en-us/library/ms175156.aspx
> I am trying to join Customers and Addresses, but I only 1 a maximum of
> 1 address joined. So in other words, if I did a join and there was a
> customer w/ 5 addresses, that would result in 5 records being
> returned, and I just want it to give me 1 record with the first
> address joined.
> I am using this Select statement:
> SELECT C.*, A.*
> FROM Customers C
> LEFT OUTER JOIN
> (SELECT TOP 1 *
> FROM CustomerAddresses
> WHERE (CustomerAddresses.CustomerId = C.CustomerId)) AS A
> ON C.CustomerId = A.CustomerId
> It is saying that "C.CustomerId" is not valid in my nested select
> statement.
If you are using SQL 2000 then assumming you have an AddressId primary on
your address table, you could try
SELECT C.*, A.*
FROM Customers C
LEFT JOIN (
(SELECT CustomerId, MAX(AddressId) AS AddressId
FROM CustomerAddresses
GROUP BY CustomerId ) B
JOIN CustomerAddresses A ON B.AddressId = A.AddressId AND B.CustomerId
= A.CustomerId ) ON C.CustomerId = A.CustomerId AND C.CustomerId =B.CustomerId
> I can probably do this with a join to a user defined function
> returning a table, but I could have sworn that what I was trying to do
> was do-able...
> Can anyone help?
>
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0E784E06-484B-44B2-803F-2EF3E53FAE1E@.microsoft.com...
> Hi
> "cmay" wrote:
>> Ok maybe I am having a senior moment here or something, I thought this
>> was something you could do, but maybe I am just remembering wrong.
> With SQL 2005 this can be done with a function and APPLY
> http://msdn2.microsoft.com/en-us/library/ms175156.aspx
>> I am trying to join Customers and Addresses, but I only 1 a maximum of
>> 1 address joined. So in other words, if I did a join and there was a
>> customer w/ 5 addresses, that would result in 5 records being
>> returned, and I just want it to give me 1 record with the first
>> address joined.
>> I am using this Select statement:
>> SELECT C.*, A.*
>> FROM Customers C
>> LEFT OUTER JOIN
>> (SELECT TOP 1 *
>> FROM CustomerAddresses
>> WHERE (CustomerAddresses.CustomerId = C.CustomerId)) AS A
>> ON C.CustomerId = A.CustomerId
>> It is saying that "C.CustomerId" is not valid in my nested select
>> statement.
> If you are using SQL 2000 then assumming you have an AddressId primary on
> your address table, you could try
> SELECT C.*, A.*
> FROM Customers C
> LEFT JOIN (
> (SELECT CustomerId, MAX(AddressId) AS AddressId
> FROM CustomerAddresses
> GROUP BY CustomerId ) B
> JOIN CustomerAddresses A ON B.AddressId = A.AddressId AND
> B.CustomerId
> = A.CustomerId ) ON C.CustomerId = A.CustomerId AND C.CustomerId => B.CustomerId
>> I can probably do this with a join to a user defined function
>> returning a table, but I could have sworn that what I was trying to do
>> was do-able...
>> Can anyone help?
Or you can use OUTER APPLY or ROwNumber.
These are from AdventureWorks
SELECT C.ContactID, A.AddressID
FROM HumanResources.Employee C
outer apply
(
SELECT TOP 1 *
FROM HumanResources.EmployeeAddress EA
WHERE (EA.EmployeeID = c.EmployeeID)
) AS A
order by contactID
with ca(ContactID,AddressID,RowNum)
as(
SELECT
C.ContactID,
ea.AddressID,
ROW_NUMBER() over (partition by c.ContactID order by ea.AddressID) rownum
FROM HumanResources.Employee C
left join HumanResources.EmployeeAddress EA
on EA.EmployeeID = c.EmployeeID
)
select ContactID,AddressID
from ca
order by ContactID
David|||Not the way you've written it. You can't correlate inside a nested
query like that.
Here's a possible way to do it (Assuming 'xxx' is your primary key for
the CustomerAddresses table)
-Dave
SELECT c.*, a.*
FROM dbo.Customers c
LEFT JOIN (
SELECT CustomerID, MAX(xxx) AS xxx
FROM dbo.CustomerAddresses
GROUP BY CustomerID) ca
ON ca.CustomerID = c.CustomerID
LEFT JOIN dbo.CustomerAddresses a
ON a.xxx = ca.xxx|||For reaons known only to Microsoft, if the table on the right hand side of a
join is correlated, you have to write APPLY instead of JOIN. Perhaps MS will
one day follow SQL-99. In the meantime, just swear (whilst the kids aren't
around).
OTOH, since TOP is an MS extension, even if MS did support ANSI SQL-99
correlated joins, your query isn't ANSI. Use:
row_number() over (partition by CustomerId order by <something>) as AddrNo
where <something> is some means of ordering your addresses that allows you
to qualify what you mean by "the first address". You can then add :
and A.AddrNo = 1
to your join's ON clause.
"cmay" <cmay@.walshgroup.com> wrote in message
news:1169932681.524014.158670@.l53g2000cwa.googlegroups.com...
> Ok maybe I am having a senior moment here or something, I thought this
> was something you could do, but maybe I am just remembering wrong.
> I am trying to join Customers and Addresses, but I only 1 a maximum of
> 1 address joined. So in other words, if I did a join and there was a
> customer w/ 5 addresses, that would result in 5 records being
> returned, and I just want it to give me 1 record with the first
> address joined.
> I am using this Select statement:
> SELECT C.*, A.*
> FROM Customers C
> LEFT OUTER JOIN
> (SELECT TOP 1 *
> FROM CustomerAddresses
> WHERE (CustomerAddresses.CustomerId = C.CustomerId)) AS A
> ON C.CustomerId = A.CustomerId
> It is saying that "C.CustomerId" is not valid in my nested select
> statement.
> I can probably do this with a join to a user defined function
> returning a table, but I could have sworn that what I was trying to do
> was do-able...
> Can anyone help?
>|||On 28 Jan, 09:53, "Mark Yudkin" <DoNotContac...@.boingboing.org> wrote:
> For reaons known only to Microsoft, if the table on the right hand side of a
> join is correlated, you have to write APPLY instead of JOIN. Perhaps MS will
> one day follow SQL-99. In the meantime, just swear (whilst the kids aren't
> around).
>
This is not for "reasons known only to Microsoft". Standard SQL also
doesn't allow correlated queries to reference outside of the derived
table expression unless the LATERAL keyword is used. Microsoft chose
to use APPLY instead of LATERAL but the restriction is exactly the
same one in the case of SQL99 and SQL2003 - namely that a standard
(non-lateral) derived query cannot define itself recursively by using
a correlation outside of its own scope.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I think you're confusing LATERAL and recursive SQL (CTE's, also only partly
supported in SQL 2005) with correlation between table expressions in JOINS.
And in any case, there is no excuse for creating product-specific,
non-standard syntax when following the standard would involve no loss of
functionality. APPLY is not in the SQL-99 (ISO 9074, part 2) standard (let
alone SQL 2003), but the functionality is.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1169994883.390623.313580@.j27g2000cwj.googlegroups.com...
> On 28 Jan, 09:53, "Mark Yudkin" <DoNotContac...@.boingboing.org> wrote:
>> For reaons known only to Microsoft, if the table on the right hand side
>> of a
>> join is correlated, you have to write APPLY instead of JOIN. Perhaps MS
>> will
>> one day follow SQL-99. In the meantime, just swear (whilst the kids
>> aren't
>> around).
> This is not for "reasons known only to Microsoft". Standard SQL also
> doesn't allow correlated queries to reference outside of the derived
> table expression unless the LATERAL keyword is used. Microsoft chose
> to use APPLY instead of LATERAL but the restriction is exactly the
> same one in the case of SQL99 and SQL2003 - namely that a standard
> (non-lateral) derived query cannot define itself recursively by using
> a correlation outside of its own scope.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Is this Index supposed to make view faster?

Ok,
When I did this to add the index on the view, the view is no faster at
all...Did I do something wrong? :
USE tsNess
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS
ON
GO
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM dbo.tblTravelDetail t1 INNER JOIN
dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
= t2.TravelDetailId INNER JOIN
dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
= t4.TravelDetailId INNER JOIN
dbo.tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
dbo.amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
dbo.period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type
Thanks,
TrintHi
select <column lists> from V1 with (noexpand)
See an execution plan for the query
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123505778.551931.242730@.o13g2000cwo.googlegroups.com...
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint
>|||Did you create indexes on the view?
Once you create an indexed view, make sure your view does not use the base
table indexes.
The way to do that is use the option (noexpand)
eg:-
Select PeriodID from V1 (noexpand)
where ...
To get the adv of indexed view, you should make sure its using the indexes
on the view rather than the old base table indexes.
The next thing is see the reads/Cpu and Dur with and without the option
noexpand. Moreover dont leave out exe plan.
Before you go for indexed view it wud be good to see how much time it would
take to create an index on a prod server.
The retireval performance should not be an overhead while saving
or in other words see whether the tables used in your indexed view are
updated/inserted frequently. If so I dont think its a good idea to go for
indexed view.
Thanks,
Prad
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123505778.551931.242730@.o13g2000cwo.googlegroups.com...
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint
>|||Ok,
Uri and Pradeep, I get this error when trying to create an index on
view one or V1:
An index cannot be created on the view 'V1' because the view definition
includes an unknown value (the sum of a nullable expression).
Thanks,
Trint|||Trint,
Another reason why we dont go for indexed view always...
See BOL you have a lot of requirements to be satisfied in creating one apart
from the time it takes and the ovehead while saving...
One more thing that I have obsereved is the fields on which u intend to
create index should not have duplicates...
You cannot have :
a.. A derived table.
a.. Rowset functions.
a.. UNION operator.
a.. Subqueries.
a.. Outer or self joins.
a.. TOP clause.
a.. ORDER BY clause.
a.. DISTINCT keyword.
a.. COUNT(*) but (COUNT_BIG(*) is allowed.)
a.. A SUM function that references a nullable expression.
a.. The full-text predicates CONTAINS or FREETEXT.
a.. COMPUTE or COMPUTE BY clause.
a.. If GROUP BY is not specified, the view select list cannot contain
aggregate expressions.
a.. If GROUP BY is specified, the view select list must contain a
COUNT_BIG(*) expression, and the view definition cannot specify HAVING,
CUBE, or ROLLUP.
a.. A column resulting from an expression that either evaluates to a float
value or uses float expressions for its evaluation cannot be a key of an
index in an indexed view or a table.
From BOL
Thanks,
Pradeep Kutty
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123514588.376478.113330@.g43g2000cwa.googlegroups.com...
> Ok,
> Uri and Pradeep, I get this error when trying to create an index on
> view one or V1:
> An index cannot be created on the view 'V1' because the view definition
> includes an unknown value (the sum of a nullable expression).
> Thanks,
> Trint
>|||Trint,
To start off, I would like to ask if you have already indexed the base
tables? Indexing a view is not the place to start. Under normal
conditions, view performance is just fine if you properly index the base
tables.
For the rest of the reply, I will assume you have a properly normalized
data model that is properly indexed.
If you must index the view, then make sure sure:
- dbo.tblTravelDetailAmount.amount is defined as NOT NULL;
- the expressions never evaluate to NULL. Currently your CASE
expressions will evaluate to NULL for each amountTypeID that is not
explicitely mentioned in the expression. You could add "ELSE 0" to each
CASE expression to solve that;
- in the case of dbo.tblTravelDetail.MemberId that you match the
literal's data type to the column's. For example, if the MemberId is
defined as int, then change the predicate to WHERE t1.MemberId = 222;
- you add COUNT_BIG(*) to the selection list.
Maybe then you can index the view.
Note that if only few rows in table dbo.tblTravelDetailAmount have
amount=0, then the predicate WHERE t2.amount<>0 may not help performance
(it may even hurt performance) if you select from the base tables.
HTH,
Gert-Jan
trint wrote:
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint

Is this a good scenario for a Service Broker application usage

Hi,

Please excuse me if I get some terms wrong - I am not a developer!!

I am working for a company who has a requirement to interface to a customer to query for work. The customer has an established Web Services that it uses for external interfacing, these services are passive at their end, in that it will be my clients who initiate all the communications using SOAP, receiving responses in XML.

There are, as I see it, seven conversations.

Receiving Datasets

1. Requests All new Jobs - This will be a post to a HTP with a list of job numbers sent back, These jobs will then need to be polled from the same webservice.

2. Job Changes - existing jobs that have changed definitions (costs etc..)- simular to above

3. Job Changes - Appointment Times

Sending Datasets

1. Completed Jobs - one at a time

2. Cancelled Jobs

3. Appointments (I believe this is responses to 1 and 3 above)

4. Subsequent Jobs - new work derived from initial Job.

I believe the sending datasets will be one way only (ie POST).

I have suggested using Service Broker for this application, with a custom App sitting inbetween ServiceBroker and the Web Service to deal with the HTTP POSTS and GETS, and communicating with the Service Broker, posting to queues and reading from them.

Is this viable?

Many Thaks

Lawrenso

We have found customers using Service Broker services to talk to webservices, a common scenario. If you build your apps as internally activated stored procedures (written using CLR), they will always run under the security context of the database engine service account and hence that principle will need access to the web from the machine on which the database engine is running. The other option is to use external applications (running as whatever user you want on whatever machine you choose) that connect to the database to RECEIVE messages from the queue and post requests to the web service. The second pattern does not have a built-in activation mechansim, but you could look at our External Activator sample (on GotDotNet codegallery and www.sqlservicebroker.com).

Rushi

Is this a BUG in SQL Server 2000?

I get wrong Query results for a very simple Query. The Query is as below:

SELECT RolePages.PageName, RolePages.Allow, RolePages.RoleId, Roles.RoleName
FROM Roles INNER JOIN
RolePages ON RolePages.RoleId = Roles.RoleId
WHERE (Roles.RoleName = 'Anonymous')

WHEN I RUN THE ABOVE QUERY MY RESULTS ARE AS BELOW: ('Allow' is bit type column)

PageNameAllowRoleIdRoleName

Home 1 2Anonymous
Registeration 12Anonymous
SpecialUserMessage 12Anonymous
ForgotPassword 12Anonymous

BUT, WHEN I RUN THE same QUERY IN A STORED PROCEDURE THE RESULTS ARE:

PageNameAllowRoleIdRoleName

Home -1 2Anonymous
Registeration -12Anonymous
SpecialUserMessage -12Anonymous
ForgotPassword -12Anonymous

Can someone please tell me if this is a SQL Server bug and if it can be fixed ? I am using SQL Server 2000 Desktop version.Are you using different clients for this? What client are you using for the stored procedure. Access (and possibly OleDb in general - I do not know) uses -1 for true. TO be safe, I would alsways check for !=0 (or <>0 for you VB types).|||I am running the query in VS 2003 and seeing the results. Then I run the stored procedure containing this query in VS 2003. So its a straight run of the query/ stored procedure directly from the database. Is this what you were asking ?|||Never check for 1 or -1. Always check for 0. ADO and ADO.net use -1 for true and they convert SQL's 1 to -1 when you use either to access data from SQL. SQL uses 1 for true. They both use 0 for false, so use that.|||OK. Thanks for the help. I think, the idea is that anything other than '0' is treated as a 'True'. So then one could say even if the query return's a '1' and the stored procedure for the same query returns a '-1', they both will be considered 'True'. That makes the results of the query and the query inside a stored procedure 'consistent'.

Thanks once again to all who helped clarify this.

Is there something wrong with my IF code...

<%
If required_equip = Desktop Then
If required_equip = Laptop Then
End If
If required_equip = Other Then
End If
Response.Redirect "nh_request.asp"
End If
%>
Just wondering if the above would work within my form to check a radio
button on submission...
I have 1 radio button, that if selected i want the person to be taken to a
2nd form after they submit the first form... if that radio button is NOT
selected.. then they submit the form and they are done...
can anyone tell me if the above would work and if so, where i should place
it within my code so it checks when the submit button is clicked.What does this have to do with SQL Server?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj#03$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>|||You are asking this question in the wrong forum... This should be posted in
one of the development forums.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj%2303$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>

Is there something wrong with my IF code...

<%
If required_equip = Desktop Then
If required_equip = Laptop Then
End If
If required_equip = Other Then
End If
Response.Redirect "nh_request.asp"
End If
%>
Just wondering if the above would work within my form to check a radio
button on submission...
I have 1 radio button, that if selected i want the person to be taken to a
2nd form after they submit the first form... if that radio button is NOT
selected.. then they submit the form and they are done...
can anyone tell me if the above would work and if so, where i should place
it within my code so it checks when the submit button is clicked.
What does this have to do with SQL Server?
http://www.aspfaq.com/
(Reverse address to reply.)
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj#03$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>
|||You are asking this question in the wrong forum... This should be posted in
one of the development forums.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj%2303$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>

Is there something wrong with my IF code...

<%
If required_equip = Desktop Then
If required_equip = Laptop Then
End If
If required_equip = Other Then
End If
Response.Redirect "nh_request.asp"
End If
%>
Just wondering if the above would work within my form to check a radio
button on submission...
I have 1 radio button, that if selected i want the person to be taken to a
2nd form after they submit the first form... if that radio button is NOT
selected.. then they submit the form and they are done...
can anyone tell me if the above would work and if so, where i should place
it within my code so it checks when the submit button is clicked.What does this have to do with SQL Server?
http://www.aspfaq.com/
(Reverse address to reply.)
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj#03$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>|||You are asking this question in the wrong forum... This should be posted in
one of the development forums.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj%2303$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>

Is there any way to 'undo' an update query?

I guess I am in big trouble now.

I have updated a table by querying in SQL Query Analyzer and one of table columns was all set to wrong data (of course it was because of my mistake in query sentence). I am wondering if there is any way to recover this table?

Unfortunately my database was not backuped after this table was created. I am wondering what the log file is for. Maybe can there any way to retrieve a table's old status by using the log table?
Any help?

without a database backup there's no way to restore data from a previous point in time. A log file can be used to store incremental changes from the last database/log backup.

in the future you may want to start your TSQL statement with a BEGIN TRAN, and allow yourself the ability to roll it back should you find yourself in a similar situation.

|||OK. I guess I gotta do all annoying import job again to make the table.
Anyway, thanks a lot, Greg. I appreciate your advice.