Invisible Results or Ghost in the Query Analyzer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Invisible Results or Ghost in the Query Analyzer

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 11:57:15

I have two-fold problem I am asking your assistance on:

1.) I plugged this code into the SQA and for the life of me I can't see why I'm getting nothing in the tab_MCACAPS_Stats.

USE Xerxes

if exists
(select * from Xerxes.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_MCACAPS_Stats]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [Xerxes].[dbo].[tab_MCACAPS_Stats]

CREATE TABLE [Xerxes].[dbo].[tab_MCACAPS_Stats] (
[TableName] nvarchar (20) NULL,
[RecordCount] int NULL,
[STAMPDATE] datetime NULL,
[STAMPUSER] varchar (50) NULL
)

UPDATE [Xerxes].[dbo].[tab_MCACAPS_Stats]
SET STAMPDATE = GETDATE(),
STAMPUSER = SUSER_SNAME()

2.) I need to plug-in the record counts from other tables that I've created into tab_MCACAPS_Stats. I can generate the counts this way:

SELECT
tab_NewMembr_Work=(SELECT Count(*) FROM tab_NewMembr_Work),
tab_CUMMEMBR =(SELECT Count(*) FROM tab_CUMMEMBR),
tab_CUMMOLD =(SELECT Count(*) FROM tab_CUMMOLD)

but my attempts at joinings have taken on disastrous results. I know there's a simpler way, I just haven't found it.

Thanks for your help!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 12:01:50

1.) you're updateing what in the table? if you want more than one statement in the if you need:
if ...
begin
....
end

2.) you're joing on to what? the sql you have should work.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 12:06:30

quote:
Originally posted by spirit1

1.) you're updateing what in the table? if you want more than one statement in the if you need:
if ...
begin
....
end

2.) you're joing on to what? the sql you have should work.

Go with the flow & have fun! Else fight the flow

On 1) I'm updating the STAMPDATE and STAMPUSER fields in tab_MCACAPS_Stats. But nothing

appears when I run this code. The messages also say 0 rows affected.

On 2) Yes, this works, but I'm trying to join the results of these counts to the tab_MCACAPS_Stats table.
I haven't been successful.

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 12:15:43

1. yes that's true but there is no data to update!!! you need to first insert the data so you can update anything.
2. how are you joing them??

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 14:03:17

Well, I tried making this a temp table so I could see how the joining goes, but here's what I've attempted:

DECLARE @MCACAPS_Stats TABLE(
Tab nvarchar (20) NULL,
Recs int NULL,
STAMPDATE datetime NULL,
STAMPUSER varchar (50) NULL
)

INSERT @MCACAPS_Stats(Tab,Recs,STAMPDATE,STAMPUSER)
SELECT 'tab_NewMember_Work', SELECT Count(*) FROM tab_NewMembr_Work ,GETDATE(),SUSER_SNAME() UNION ALL
SELECT 'tab_CUMMEMBR ', SELECT Count(*) FROM tab_CUMMEMBR ,GETDATE(),SUSER_SNAME() UNION ALL
SELECT 'tab_CUMMOLD ', SELECT Count(*) FROM tab_CUMMOLD ,GETDATE(),SUSER_SNAME())

select * from @MCACAPS_Stats

But I'm getting this:

Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ')'.

Ok....what am I doing wrong? I've been banging my head at this one for so long, I'm going to have a permanent scar

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 14:15:23

what would your children say if they knew what scared you...

so let me relieve the pressure:
INSERT @MCACAPS_Stats(Tab,Recs,STAMPDATE,STAMPUSER)
SELECT 'tab_NewMember_Work', (SELECT Count(*) FROM tab_NewMembr_Work) ,GETDATE(),SUSER_SNAME() UNION ALL
SELECT 'tab_CUMMEMBR ', (SELECT Count(*) FROM tab_CUMMEMBR) ,GETDATE(),SUSER_SNAME() UNION ALL
SELECT 'tab_CUMMOLD ', (SELECT Count(*) FROM tab_CUMMOLD) ,GETDATE(),SUSER_SNAME()

a couple of ( and ) do wonders for ones health...

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 14:24:32

quote:
Originally posted by spirit1

what would your children say if they knew what scared you... so let me relieve the pressure:
INSERT @MCACAPS_Stats(Tab,Recs,STAMPDATE,STAMPUSER)
SELECT 'tab_NewMember_Work', (SELECT Count(*) FROM tab_NewMembr_Work) ,GETDATE(),SUSER_SNAME() UNION ALL
SELECT 'tab_CUMMEMBR ', (SELECT Count(*) FROM tab_CUMMEMBR) ,GETDATE(),SUSER_SNAME() UNION ALL
SELECT 'tab_CUMMOLD ', (SELECT Count(*) FROM tab_CUMMOLD) ,GETDATE(),SUSER_SNAME()

a couple of ( and ) do wonders for ones health...

Go with the flow & have fun! Else fight the flow

Thanks again, Mladen!

"It's NEVER something big,
It's always something small,
That puts those little dents,
In the back of my skull!"

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 15:42:37

OK, now that I have that resolved, I realize that the date the table was created would be more useful than the GETDATE(). Is there any function I can use to acquire this? I went BOL-ing (get it?) and found nothing up that 'alley'

Here's me code.....
DECLARE @MCACAPS_Stats TABLE(
Tab nvarchar (30) NULL,
Recs int NULL,
STAMPDATE datetime NULL,
STAMPUSER varchar (50) NULL
)

INSERT @MCACAPS_Stats(Tab,Recs,STAMPDATE,STAMPUSER)
SELECT 'tab_NewMember_Work', (SELECT Count(*) FROM tab_NewMembr_Work) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'tab_CUMMEMBR ', (SELECT Count(*) FROM tab_CUMMEMBR ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'tab_CUMMOLD ', (SELECT Count(*) FROM tab_CUMMOLD ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'look_PROVPLAN ', (SELECT Count(*) FROM look_PROVPLAN ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'look_BENEFITS ', (SELECT Count(*) FROM look_BENEFITS ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'look_PLANTYPE ', (SELECT Count(*) FROM look_PLANTYPE ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'tab_PRINMPEL ', (SELECT Count(*) FROM tab_PRINMPEL ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'tab_PROEL ', (SELECT Count(*) FROM tab_PROEL ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'tab_MMGOTHEL ', (SELECT Count(*) FROM tab_MMGOTHEL ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'tab_ELCombo ', (SELECT Count(*) FROM tab_ELCombo ) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'tab_NewMembr_ErrLog', (SELECT Count(*) FROM tab_NewMembr_ErrLog) ,GETDATE(),SUSER_SNAME() UNION
SELECT 'tab_COBMembr ', (SELECT Count(*) FROM tab_COBMembr ) ,GETDATE(),SUSER_SNAME()

select * from @MCACAPS_Stats

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 15:50:13

this will get the created date.
select crdate
from sysobjects
where name = 'tableName'

EDIT: NO go for table variables or #temp tables.
works for global ##temp tables
select crdate
from tempdb..sysobjects
where name = '##tableName'

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 15:51:56

quote:
Originally posted by spirit1

this will get the created date.
select crdate
from sysobjects
where name = 'tableName'

Go with the flow & have fun! Else fight the flow

....And to think that I thought all sysobjects was good for was to test for existence!

Thanks!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-08 : 15:54:10

Check out the crdate column in sysobjects.

SELECT crdate
FROM sysobjects
WHERE [name] = 'tab_NewMember_Work' AND type = 'U'

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 16:52:45

quote:
Originally posted by tduggan

Check out the crdate column in sysobjects.

SELECT crdate
FROM sysobjects
WHERE [name] = 'tab_NewMember_Work' AND type = 'U'

Tara

Question: what's the difference between xtype and type? Both show 'U' to be usertype.

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-08 : 16:56:36

xtype was added in SQL Server 2000. We should be using xtype now. I've just been working with SQL Server since 6.5 (a little bit of work in version 4.2.1), so I'm used to typing type = 'U'. I just need to get rid of that old habit.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 17:12:10

Well, actually I didn't even bother to check type, but this is what works!

DECLARE @MCACAPS_Stats TABLE(
Tab nvarchar (30) NULL,
Recs int NULL,
STAMPDATE datetime NULL,
STAMPUSER varchar (50) NULL
)

INSERT @MCACAPS_Stats(Tab,Recs,STAMPDATE,STAMPUSER)
SELECT 'tab_NewMember_Work ', (SELECT Count(*) FROM tab_NewMembr_Work)
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'tab_NewMember_Work ')
, SUSER_SNAME() UNION
SELECT 'tab_CUMMEMBR ', (SELECT Count(*) FROM tab_CUMMEMBR )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'tab_CUMMEMBR ')
, SUSER_SNAME() UNION
SELECT 'tab_CUMMOLD ', (SELECT Count(*) FROM tab_CUMMOLD )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'tab_CUMMOLD ')
, SUSER_SNAME() UNION
SELECT 'look_PROVPLAN ', (SELECT Count(*) FROM look_PROVPLAN )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'look_PROVPLAN ')
, SUSER_SNAME() UNION
SELECT 'look_BENEFITS ', (SELECT Count(*) FROM look_BENEFITS )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'look_BENEFITS ')
, SUSER_SNAME() UNION
SELECT 'look_PLANTYPE ', (SELECT Count(*) FROM look_PLANTYPE )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'look_PLANTYPE ')
, SUSER_SNAME() UNION
SELECT 'tab_PRINMPEL ', (SELECT Count(*) FROM tab_PRINMPEL )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'tab_PRINMPEL ')
, SUSER_SNAME() UNION
SELECT 'tab_PROEL ', (SELECT Count(*) FROM tab_PROEL )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'look_PROEL ')
, SUSER_SNAME() UNION
SELECT 'tab_MMGOTHEL ', (SELECT Count(*) FROM tab_MMGOTHEL )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'look_MMGOTHEL ')
, SUSER_SNAME() UNION
SELECT 'tab_ELCombo ', (SELECT Count(*) FROM tab_ELCombo )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'tab_ELCombo ')
, SUSER_SNAME() UNION
SELECT 'tab_NewMembr_ErrLog', (SELECT Count(*) FROM tab_NewMembr_ErrLog)
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'tab_NewMembr_ErrLog')
, SUSER_SNAME() UNION
SELECT 'tab_COBMembr ', (SELECT Count(*) FROM tab_COBMembr )
, (SELECT CRDATE FROM sysobjects WHERE NAME = 'tab_COBMembr ')
, SUSER_SNAME()

select * from @MCACAPS_Stats
Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-08 : 17:19:05

Yeah, I add type in there to be safe. But since your tables names are prefixed with tab_, you can probably guarantee that no other object has that same name. Cuz you could have a table named Tara and a stored procedure named Tara. So that's where you'd need xtype = 'U', xtype = 'P'.

BTW, I would get rid of the tab_ for your table names, that's considered bad naming standards. Tables should not be prefixed with anything that suggests table. Same thing goes with columns. Some people are of the old mind set to do dDateColumn or cCharacterColumn. You just don't need this redundant information in the object name. I'm sure someone has mentioned this here before though when they saw your table names, so I might be repeating the information.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-08 : 17:34:07

Uh, OK, why is it considered bad naming conventions? I made up my own standards; I use....

tab_ => for standard tables--tables that aren't temporary.

look_ => to describe, you guessed it: lookup tables!

key_ => for parameter tables

xxs_ => for Xerxes' own tables

it's great to be my own user and DBA, too!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-08 : 17:38:50

http://www.sqlteam.com/item.asp?ItemID=14929
http://vyaskn.tripod.com/object_naming.htm

Anytime I see tblxxx or tab_xxx, I typically think of Access or some old dbms. Not sure if you do .NET development, but the same goes with that too. IT has moved away from these prefixes. It's just redundant information that can easily be found now.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources