alias for variable [memory] table?

i have this sql Q:
a delete statement not allowed to use with "as" (delete from table as t
where..),
so when i need "as" i do it in sub q, like this:
delete from customers where 3<(select count(*) from customer t where
t.date=customer.date)
but when table in memory table, I got err:
declare @.t table([id] int, [date] smalldatetime)
delete from @.t where 3<(select count(*) from @.t t where @.t.date=t.date)
I got err:Must declare the scalar variable "@.t"
have a solution for like situations?Indeed, you need to use an alias. Try something like this:
declare @.t table([id] int, [date] smalldatetime)
delete a from @.t a where 3<(select count(*) from @.t t where
a.date=t.date)
Razvan|||Your attempt at inventing syntax makes no sense in terms of the SQL
language model. An alias is supposed to act as it materializes a new
working table with the data from the original table expression in it.
To be consistent, this syntax says that you have done nothing to the
base table.
The next question is why would you use that proprietary in memory table
in the first place? It looks like you are mimicking a scratch tape in a
1950's file system instead of writing SQL. But you did not post enough
for anyone to give you a relatioanl solution.|||Use an alias, but use it where it belongs:
delete <alias>
from <table> [as] <alias>
where <condition>
ML
http://milambda.blogspot.com/|||Here is another way, without aliases:
DELETE @.t WHERE date IN (
SELECT date FROM @.t
GROUP BY date
HAVING COUNT(*)>3
)
Razvan
PS. I hope your real columns have better names...|||to --CELKO--
I realy interest you, but i not understand at all, please explain your
approach!!
for anyone to give you a relatioanl solution.
my example is very clear, i need to delete from this table rec that
appear more
then 3 times. did you have better way?sql

Alert error text

Hi,
I looking for a property or global variable that would give me the alret
error text (like the one it sends in email response to an alert) . I need it
in my job which runs as a response to an alert.
thanks
I think Gert Drapers has exactly what you need - using Alert Tokens... Few
people know about these, but they are so cool...
http://www.sqldev.net/sqlagent/SQLAg...0TSQL%20st ep
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
"sonny" <sonny@.discussions.microsoft.com> wrote in message
news:28277FC8-95C2-4648-BED2-B34D1C78D7D0@.microsoft.com...
> Hi,
> I looking for a property or global variable that would give me the alret
> error text (like the one it sends in email response to an alert) . I need
> it
> in my job which runs as a response to an alert.
>
> thanks

Alert error text

Hi,
I looking for a property or global variable that would give me the alret
error text (like the one it sends in email response to an alert) . I need it
in my job which runs as a response to an alert.
thanksI think Gert Drapers has exactly what you need - using Alert Tokens... Few
people know about these, but they are so cool...
http://www.sqldev.net/sqlagent/SQLAgentStepTokens.htm#Example%20using%20TSQL%20step
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
"sonny" <sonny@.discussions.microsoft.com> wrote in message
news:28277FC8-95C2-4648-BED2-B34D1C78D7D0@.microsoft.com...
> Hi,
> I looking for a property or global variable that would give me the alret
> error text (like the one it sends in email response to an alert) . I need
> it
> in my job which runs as a response to an alert.
>
> thanks

Alert error text

Hi,
I looking for a property or global variable that would give me the alret
error text (like the one it sends in email response to an alert) . I need it
in my job which runs as a response to an alert.
thanksI think Gert Drapers has exactly what you need - using Alert Tokens... Few
people know about these, but they are so cool...
p" target="_blank">http://www.sqldev.net/sqlagent/SQLA...%20ste
p
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
"sonny" <sonny@.discussions.microsoft.com> wrote in message
news:28277FC8-95C2-4648-BED2-B34D1C78D7D0@.microsoft.com...
> Hi,
> I looking for a property or global variable that would give me the alret
> error text (like the one it sends in email response to an alert) . I need
> it
> in my job which runs as a response to an alert.
>
> thanks

affinity mask - license

In per processor license for Enterprise version of SQL Server 2000, can we
change the affinity mask variable to change the CPU usage and hence restrict
the number of sql server per processor licenses?
Example: In a 4 CPU server, single OS, if I change the sql server affinity
so that it only uses CPU1 and CPU2, and does not use the 3 & 4 processors,
then I would pay only for 2 per processor licenses for SQL Server 2000?
No. You need # of processors corresponding to the numbers that the host OS can see and uses. Here's
a quote from http://www.microsoft.com/sql/howtobuy/faq.mspx
A Processor license gives you the right to install any number of copies of SQL Server 2000 on a
single computer, as long as you have purchased Processor licenses for all of the processors on that
computer. If you have made a processor inaccessible to all operating system copies on which the SQL
Server software is set up to run, you do not need a software license for that processor.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:627E25D7-42D2-4BBE-9991-C204FA1FD206@.microsoft.com...
> In per processor license for Enterprise version of SQL Server 2000, can we
> change the affinity mask variable to change the CPU usage and hence restrict
> the number of sql server per processor licenses?
> Example: In a 4 CPU server, single OS, if I change the sql server affinity
> so that it only uses CPU1 and CPU2, and does not use the 3 & 4 processors,
> then I would pay only for 2 per processor licenses for SQL Server 2000?
>
>
|||I read the Licensing FAQs.. However everywhere it says:
"If you have made a processor inaccessible to all operating system copies on
which the SQL Server software is set up to run, you do not need a software
license for that processor."
Why would you make a processor inaccessible to OS? Do you mean when you have
2 or more OS installed on the same server?
Can you give an illustration of this scenario for clarification, the FAQ and
licesing link does not say much?
Thanks
"Tibor Karaszi" wrote:

> No. You need # of processors corresponding to the numbers that the host OS can see and uses. Here's
> a quote from http://www.microsoft.com/sql/howtobuy/faq.mspx
> A Processor license gives you the right to install any number of copies of SQL Server 2000 on a
> single computer, as long as you have purchased Processor licenses for all of the processors on that
> computer. If you have made a processor inaccessible to all operating system copies on which the SQL
> Server software is set up to run, you do not need a software license for that processor.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pari" <Pari@.discussions.microsoft.com> wrote in message
> news:627E25D7-42D2-4BBE-9991-C204FA1FD206@.microsoft.com...
>
|||You might want5 to approach an MS sales rep on this. As I understand it, the reasoning why just
cutting down affinity mask doesn't cut it is that everything SQL Server does it does through the
OS...
The licensing terms used to say "numbers of physical processors in the box" (or something to that
effect), but as big boxes came out where you started partitioning the OS, it made more sense to have
it "number of processors that the OS can use", methinks...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:D38F26A9-3F72-4720-B48C-CF581801526D@.microsoft.com...[vbcol=seagreen]
>I read the Licensing FAQs.. However everywhere it says:
> "If you have made a processor inaccessible to all operating system copies on
> which the SQL Server software is set up to run, you do not need a software
> license for that processor."
> Why would you make a processor inaccessible to OS? Do you mean when you have
> 2 or more OS installed on the same server?
> Can you give an illustration of this scenario for clarification, the FAQ and
> licesing link does not say much?
> Thanks
> "Tibor Karaszi" wrote:

affinity mask - license

In per processor license for Enterprise version of SQL Server 2000, can we
change the affinity mask variable to change the CPU usage and hence restric
t
the number of sql server per processor licenses?
Example: In a 4 CPU server, single OS, if I change the sql server affinity
so that it only uses CPU1 and CPU2, and does not use the 3 & 4 processors,
then I would pay only for 2 per processor licenses for SQL Server 2000?No. You need # of processors corresponding to the numbers that the host OS c
an see and uses. Here's
a quote from http://www.microsoft.com/sql/howtobuy/faq.mspx
A Processor license gives you the right to install any number of copies of S
QL Server 2000 on a
single computer, as long as you have purchased Processor licenses for all of
the processors on that
computer. If you have made a processor inaccessible to all operating system
copies on which the SQL
Server software is set up to run, you do not need a software license for tha
t processor.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:627E25D7-42D2-4BBE-9991-C204FA1FD206@.microsoft.com...
> In per processor license for Enterprise version of SQL Server 2000, can we
> change the affinity mask variable to change the CPU usage and hence restr
ict
> the number of sql server per processor licenses?
> Example: In a 4 CPU server, single OS, if I change the sql server affinity
> so that it only uses CPU1 and CPU2, and does not use the 3 & 4 processors,
> then I would pay only for 2 per processor licenses for SQL Server 2000?
>
>|||I read the Licensing FAQs.. However everywhere it says:
"If you have made a processor inaccessible to all operating system copies on
which the SQL Server software is set up to run, you do not need a software
license for that processor."
Why would you make a processor inaccessible to OS? Do you mean when you have
2 or more OS installed on the same server?
Can you give an illustration of this scenario for clarification, the FAQ and
licesing link does not say much?
Thanks
"Tibor Karaszi" wrote:

> No. You need # of processors corresponding to the numbers that the host OS
can see and uses. Here's
> a quote from http://www.microsoft.com/sql/howtobuy/faq.mspx
> A Processor license gives you the right to install any number of copies of
SQL Server 2000 on a
> single computer, as long as you have purchased Processor licenses for all
of the processors on that
> computer. If you have made a processor inaccessible to all operating syste
m copies on which the SQL
> Server software is set up to run, you do not need a software license for t
hat processor.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pari" <Pari@.discussions.microsoft.com> wrote in message
> news:627E25D7-42D2-4BBE-9991-C204FA1FD206@.microsoft.com...
>|||You might want5 to approach an MS sales rep on this. As I understand it, the
reasoning why just
cutting down affinity mask doesn't cut it is that everything SQL Server does
it does through the
OS...
The licensing terms used to say "numbers of physical processors in the box"
(or something to that
effect), but as big boxes came out where you started partitioning the OS, it
made more sense to have
it "number of processors that the OS can use", methinks...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:D38F26A9-3F72-4720-B48C-CF581801526D@.microsoft.com...[vbcol=seagreen]
>I read the Licensing FAQs.. However everywhere it says:
> "If you have made a processor inaccessible to all operating system copies
on
> which the SQL Server software is set up to run, you do not need a software
> license for that processor."
> Why would you make a processor inaccessible to OS? Do you mean when you ha
ve
> 2 or more OS installed on the same server?
> Can you give an illustration of this scenario for clarification, the FAQ a
nd
> licesing link does not say much?
> Thanks
> "Tibor Karaszi" wrote:
>

affinity mask - license

In per processor license for Enterprise version of SQL Server 2000, can we
change the affinity mask variable to change the CPU usage and hence restrict
the number of sql server per processor licenses?
Example: In a 4 CPU server, single OS, if I change the sql server affinity
so that it only uses CPU1 and CPU2, and does not use the 3 & 4 processors,
then I would pay only for 2 per processor licenses for SQL Server 2000?No. You need # of processors corresponding to the numbers that the host OS can see and uses. Here's
a quote from http://www.microsoft.com/sql/howtobuy/faq.mspx
A Processor license gives you the right to install any number of copies of SQL Server 2000 on a
single computer, as long as you have purchased Processor licenses for all of the processors on that
computer. If you have made a processor inaccessible to all operating system copies on which the SQL
Server software is set up to run, you do not need a software license for that processor.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:627E25D7-42D2-4BBE-9991-C204FA1FD206@.microsoft.com...
> In per processor license for Enterprise version of SQL Server 2000, can we
> change the affinity mask variable to change the CPU usage and hence restrict
> the number of sql server per processor licenses?
> Example: In a 4 CPU server, single OS, if I change the sql server affinity
> so that it only uses CPU1 and CPU2, and does not use the 3 & 4 processors,
> then I would pay only for 2 per processor licenses for SQL Server 2000?
>
>|||I read the Licensing FAQs.. However everywhere it says:
"If you have made a processor inaccessible to all operating system copies on
which the SQL Server software is set up to run, you do not need a software
license for that processor."
Why would you make a processor inaccessible to OS? Do you mean when you have
2 or more OS installed on the same server?
Can you give an illustration of this scenario for clarification, the FAQ and
licesing link does not say much?
Thanks
"Tibor Karaszi" wrote:
> No. You need # of processors corresponding to the numbers that the host OS can see and uses. Here's
> a quote from http://www.microsoft.com/sql/howtobuy/faq.mspx
> A Processor license gives you the right to install any number of copies of SQL Server 2000 on a
> single computer, as long as you have purchased Processor licenses for all of the processors on that
> computer. If you have made a processor inaccessible to all operating system copies on which the SQL
> Server software is set up to run, you do not need a software license for that processor.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pari" <Pari@.discussions.microsoft.com> wrote in message
> news:627E25D7-42D2-4BBE-9991-C204FA1FD206@.microsoft.com...
> > In per processor license for Enterprise version of SQL Server 2000, can we
> > change the affinity mask variable to change the CPU usage and hence restrict
> > the number of sql server per processor licenses?
> >
> > Example: In a 4 CPU server, single OS, if I change the sql server affinity
> > so that it only uses CPU1 and CPU2, and does not use the 3 & 4 processors,
> > then I would pay only for 2 per processor licenses for SQL Server 2000?
> >
> >
> >
>|||You might want5 to approach an MS sales rep on this. As I understand it, the reasoning why just
cutting down affinity mask doesn't cut it is that everything SQL Server does it does through the
OS...
The licensing terms used to say "numbers of physical processors in the box" (or something to that
effect), but as big boxes came out where you started partitioning the OS, it made more sense to have
it "number of processors that the OS can use", methinks...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pari" <Pari@.discussions.microsoft.com> wrote in message
news:D38F26A9-3F72-4720-B48C-CF581801526D@.microsoft.com...
>I read the Licensing FAQs.. However everywhere it says:
> "If you have made a processor inaccessible to all operating system copies on
> which the SQL Server software is set up to run, you do not need a software
> license for that processor."
> Why would you make a processor inaccessible to OS? Do you mean when you have
> 2 or more OS installed on the same server?
> Can you give an illustration of this scenario for clarification, the FAQ and
> licesing link does not say much?
> Thanks
> "Tibor Karaszi" wrote:
>> No. You need # of processors corresponding to the numbers that the host OS can see and uses.
>> Here's
>> a quote from http://www.microsoft.com/sql/howtobuy/faq.mspx
>> A Processor license gives you the right to install any number of copies of SQL Server 2000 on a
>> single computer, as long as you have purchased Processor licenses for all of the processors on
>> that
>> computer. If you have made a processor inaccessible to all operating system copies on which the
>> SQL
>> Server software is set up to run, you do not need a software license for that processor.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pari" <Pari@.discussions.microsoft.com> wrote in message
>> news:627E25D7-42D2-4BBE-9991-C204FA1FD206@.microsoft.com...
>> > In per processor license for Enterprise version of SQL Server 2000, can we
>> > change the affinity mask variable to change the CPU usage and hence restrict
>> > the number of sql server per processor licenses?
>> >
>> > Example: In a 4 CPU server, single OS, if I change the sql server affinity
>> > so that it only uses CPU1 and CPU2, and does not use the 3 & 4 processors,
>> > then I would pay only for 2 per processor licenses for SQL Server 2000?
>> >
>> >
>> >
>>

Affecting SELECT rows to a table variable

Hi,

I would like to know how to add SELECT row to a table variable. It's not for my SELECT syntax(code following is just an ugly example) that I want help it's for the use of table variable.
Your help will greatly appreciate!!!

ex :
DECLARE @.MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)

SET @.MyTestVar = (SELECT idTest, anotherColumn FROM tTest)-- This cause an error :
-- Must declare the variable '@.MyTestVar'. ? What?

Use an Insert statement and treat the table variable as the table:

DECLARE @.MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)

Insert @.MyTestVar

SELECT idTest, anotherColumn FROM tTest

There is more information in the Books On-line:

Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

So, you cannot do a Select..Into or use the results of a stored procedure to populate the table variable.

|||

Thanks for the fast answer!