bug with TABLE variable??

Consider the following code but don't think about what it's supposed to do
as I've simplified it a lot.
The error I get is a syntax error when I try to save my proc so what this
proc is doing is not important.
DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
INSERT @.grpIds SELECT grp_id FROM tab_grp
DELETE
@.grpIds
FROM
@.grpIds
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = @.grpIds.grp_id
Error:
@.grpIds must be declared
If I replace the table variable by a normal table, there's no error any
more.
It seems to be a bug.
Should I use a temporary table then?
Thanks
Henri
You have to alias the table variable in the from clause, then you can
reference it in other parts of your statement:
DELETE
g
FROM
@.grpIds AS g
INNER JOIN tab_rub_deny
ON tab_rub_deny.grp_id = g.grp_id
Jacco Schalkwijk
SQL Server MVP
"Henri" <hmfireball@.hotmail.com> wrote in message
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
Please see http://www.aspfaq.com/2475
Of particular interest:
"Table variables must be referenced by an alias, except in the FROM clause.
Consider the following two scripts: " [...]
http://www.aspfaq.com/
(Reverse address to reply.)

> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>
|||It works now :-)
Thanks a lot for your help,
and thanks for the interesting resource link too :-)
Henri
"Henri" <hmfireball@.hotmail.com> a crit dans le message de
news:u7f%23PkS4EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Consider the following code but don't think about what it's supposed to do
> as I've simplified it a lot.
> The error I get is a syntax error when I try to save my proc so what this
> proc is doing is not important.
> DECLARE @.grpIds TABLE (grp_id int PRIMARY KEY)
> INSERT @.grpIds SELECT grp_id FROM tab_grp
> DELETE
> @.grpIds
> FROM
> @.grpIds
> INNER JOIN tab_rub_deny
> ON tab_rub_deny.grp_id = @.grpIds.grp_id
> Error:
> @.grpIds must be declared
> If I replace the table variable by a normal table, there's no error any
> more.
> It seems to be a bug.
> Should I use a temporary table then?
> Thanks
> Henri
>
>

Bug in DBCC SQLPerf ?

I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.
Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:

> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.
|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:

> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>

Browser problem?

I've installed SQL Server 2005 Express on Windows 2000 server. I've enabled
tcp/ip for remote connections. I also have SQL Server 7 on the same server.
I can connect to the SQL Server Express instance remotely if I specify the
dynamic port:
sqlcmd -S<ip address>\sqlexpress,<port number> -Usa -Ppassword
I've started SQL Server Browser and tried to connect without specifying the
dynamic port but it doesn't work?
What am I doing wrong?
What are the specific error messages, numbers when you can't
connect?
-Sue
On Tue, 17 Oct 2006 05:11:02 -0700, Axel
<Axel@.discussions.microsoft.com> wrote:

>I've installed SQL Server 2005 Express on Windows 2000 server. I've enabled
>tcp/ip for remote connections. I also have SQL Server 7 on the same server.
>I can connect to the SQL Server Express instance remotely if I specify the
>dynamic port:
>sqlcmd -S<ip address>\sqlexpress,<port number> -Usa -Ppassword
>I've started SQL Server Browser and tried to connect without specifying the
>dynamic port but it doesn't work?
>What am I doing wrong?

Brainbench test questions

Hi,
I've been asked to take a brainbench test before an interview for a great
job. Does anyone know where I can find some test questions to practice with?
Thanks a bunch.
Susan
Susan
What does mean "great job"? DBA or what?
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:53057DE9-77E0-4E6F-BF5D-E450B6544990@.microsoft.com...
> Hi,
> I've been asked to take a brainbench test before an interview for a great
> job. Does anyone know where I can find some test questions to practice
> with?
>
> Thanks a bunch.
> Susan
|||Sorry... great job means a senior DBA with a government agency.
"Uri Dimant" wrote:

> Susan
> What does mean "great job"? DBA or what?
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:53057DE9-77E0-4E6F-BF5D-E450B6544990@.microsoft.com...
>
>
|||Here you go
http://vyaskn.tripod.com/iq.htm
Good luck
"Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
news:B466C086-EF06-4E32-B04F-C833072E9CDE@.microsoft.com...[vbcol=seagreen]
> Sorry... great job means a senior DBA with a government agency.
> "Uri Dimant" wrote:
|||thanks!
"Uri Dimant" wrote:

> Here you go
> http://vyaskn.tripod.com/iq.htm
> Good luck
>
> "Susan Cooper" <SusanCooper@.discussions.microsoft.com> wrote in message
> news:B466C086-EF06-4E32-B04F-C833072E9CDE@.microsoft.com...
>
>

BPA Install Problem

I'm installing the latest version of BPA and seem to have run into an issue.
I've uninstalled the app and deleted the old BPA database, and while
reinstalling, I'm seeing an error message come up. "One or more rules could
not be installed. There is an error in XML document (55, 15). Invalid SQL
Server edition."
I'm running everything locally, and running SQL 2k, SP3 on WinXP SP1
Where can I start to look to debug this problem?
Steve
Please uninstall and reinstall, but before reinstalling make sure that the
destination folder is empty. At the very least you should make sure that
bpadmin.exe.config file doesn not exist, and that no files or folders are
left under repository folder.
Let me know if that doesn't work.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Carroll" <steve_carroll72@.h_o_t_m_a_i_l.com> wrote in message
news:uELb%23aYTEHA.2908@.TK2MSFTNGP10.phx.gbl...
> I'm installing the latest version of BPA and seem to have run into an
issue.
> I've uninstalled the app and deleted the old BPA database, and while
> reinstalling, I'm seeing an error message come up. "One or more rules
could
> not be installed. There is an error in XML document (55, 15). Invalid
SQL
> Server edition."
> I'm running everything locally, and running SQL 2k, SP3 on WinXP SP1
> Where can I start to look to debug this problem?
>
|||Well,
Its apparent that I'm a database guy and not an application guy!
That worked like a charm. Thanks!
BTW... great app!
-Steve
"Christian Kleinerman [MS]" <ckleiner@.online.microsoft.com> wrote in message
news:e870CqYTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Steve
> Please uninstall and reinstall, but before reinstalling make sure that the
> destination folder is empty. At the very least you should make sure that
> bpadmin.exe.config file doesn not exist, and that no files or folders are
> left under repository folder.
> Let me know if that doesn't work.
> - Christian
> --
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Steve Carroll" <steve_carroll72@.h_o_t_m_a_i_l.com> wrote in message
> news:uELb%23aYTEHA.2908@.TK2MSFTNGP10.phx.gbl...
> issue.
> could
> SQL
>

BPA Feedback and Question

I've been exploring the best practices tool and find most things great! I have installed it on multiple machines and then changed the database that each client logs into to be the same one so that we have one central repository and it seems to be working
well. I would like to see a way to schedule a job that would run the scans at off hours since we are trying to make it mandatory for our developers to run a clean scan before they move out of our development environment. I was also surprised when i not
iced that there were no stored procedures in the slqbpa database, isn't that going against best practices ;)
Debi
In the released version there will be a command line version that will let
you use sql agent or some other scheduler to do what you want.
About stored procedures... not that strictly a best practice... depends on
your application or on what you're trying to do. The extensible architecture
of bpa called for some adhoc query building which may be better in the app
than doing dynamic sql inside stored procs. That said, I would like to see
some of the logic moved to stored procs, post v1. In the pre-7.0 days,
stored procs were way more a perf best practice.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debi" <anonymous@.discussions.microsoft.com> wrote in message
news:B924F619-4B1C-4657-B61B-41DA672541CE@.microsoft.com...
> I've been exploring the best practices tool and find most things great! I
have installed it on multiple machines and then changed the database that
each client logs into to be the same one so that we have one central
repository and it seems to be working well. I would like to see a way to
schedule a job that would run the scans at off hours since we are trying to
make it mandatory for our developers to run a clean scan before they move
out of our development environment. I was also surprised when i noticed
that there were no stored procedures in the slqbpa database, isn't that
going against best practices ;)
|||Hello Debi,
how did you manage this?
I tried to install BPA on different machines...
but the tool doesn't support the same database for the different
installations.
did you install BPA on different machines with different databases?
what did you do with these databases - drop them?
how did you change the database to connect to?
Thanks Thomas
"Debi" wrote:

> I've been exploring the best practices tool and find most things great! I have installed it on multiple machines and then changed the database that each client logs into to be the same one so that we have one central repository and it seems to be worki
ng well. I would like to see a way to schedule a job that would run the scans at off hours since we are trying to make it mandatory for our developers to run a clean scan before they move out of our development environment. I was also surprised when i n
oticed that there were no stored procedures in the slqbpa database, isn't that going against best practices ;)
|||Have a look at http://www.sqldbatips.com/showarticle.asp?ID=9
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mueller-Lynch Thomas" <MuellerLynchThomas@.discussions.microsoft.com> wrote
in message news:1EBAEBEF-38D1-471A-B277-B685AE3EB4BE@.microsoft.com...[vbcol=seagreen]
> Hello Debi,
> how did you manage this?
> I tried to install BPA on different machines...
> but the tool doesn't support the same database for the different
> installations.
> did you install BPA on different machines with different databases?
> what did you do with these databases - drop them?
> how did you change the database to connect to?
> Thanks Thomas
> "Debi" wrote: