Invalid column name 'dbid'?

Hi,
I modified sp_helplogins a bit and made it just return the results about
one certain database (In this case, dbid is 30). So I added the condition
'l.dbid=30' to the following statement. But running the modified
sp_helplogins returned error "Invalid column name 'dbid'".
My master database does not have a table called syslogins, instead the table
is sysxlogins. But the original sp_helplogins which uses syslogins table
runs fine. I'm confused. What did I do wrong? Appreciate any help.
-- Add the User info to holding table.
select @.exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
+
' UNION
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
l.dbid = 30 and u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
EXECUTE(@.exec_stmt)
end --loop 10
Thanks,
Bing
After looking more, looks like syslogins is a view on the sysxlogins table.
'dbid' is not a field in sysloigns. Hmmm..
"bing" wrote:

> Hi,
> I modified sp_helplogins a bit and made it just return the results about
> one certain database (In this case, dbid is 30). So I added the condition
> 'l.dbid=30' to the following statement. But running the modified
> sp_helplogins returned error "Invalid column name 'dbid'".
> My master database does not have a table called syslogins, instead the table
> is sysxlogins. But the original sp_helplogins which uses syslogins table
> runs fine. I'm confused. What did I do wrong? Appreciate any help.
> -- Add the User info to holding table.
> select @.exec_stmt = '
> INSERT #tb1_UA
> (
> DBName
> ,LoginName
> ,UserName
> ,UserOrAlias
> )
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u.name
> ,''User''
> from
> ' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
> ,master.dbo.syslogins l
> where
> l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> +
> ' UNION
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u2.name
> ,''MemberOf''
> from
> ' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
> ,master.dbo.syslogins l
> where
> l.dbid = 30 and u1.sid = l.sid
> and m.memberuid = u1.uid
> and m.groupuid = u2.uid' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> EXECUTE(@.exec_stmt)
> end --loop 10
> --
> Thanks,
> Bing
>

Invalid column name 'dbid'?

Hi,
I modified sp_helplogins a bit and made it just return the results about
one certain database (In this case, dbid is 30). So I added the condition
'l.dbid=30' to the following statement. But running the modified
sp_helplogins returned error "Invalid column name 'dbid'".
My master database does not have a table called syslogins, instead the table
is sysxlogins. But the original sp_helplogins which uses syslogins table
runs fine. I'm confused. What did I do wrong? Appreciate any help.
-- Add the User info to holding table.
select @.exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
+
' UNION
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
l.dbid = 30 and u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
EXECUTE(@.exec_stmt)
end --loop 10
--
Thanks,
BingAfter looking more, looks like syslogins is a view on the sysxlogins table.
'dbid' is not a field in sysloigns. Hmmm..
"bing" wrote:

> Hi,
> I modified sp_helplogins a bit and made it just return the results about
> one certain database (In this case, dbid is 30). So I added the condition
> 'l.dbid=30' to the following statement. But running the modified
> sp_helplogins returned error "Invalid column name 'dbid'".
> My master database does not have a table called syslogins, instead the tab
le
> is sysxlogins. But the original sp_helplogins which uses syslogins table
> runs fine. I'm confused. What did I do wrong? Appreciate any help.
> -- Add the User info to holding table.
> select @.exec_stmt = '
> INSERT #tb1_UA
> (
> DBName
> ,LoginName
> ,UserName
> ,UserOrAlias
> )
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u.name
> ,''User''
> from
> ' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
> ,master.dbo.syslogins l
> where
> l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> +
> ' UNION
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u2.name
> ,''MemberOf''
> from
> ' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
> ,master.dbo.syslogins l
> where
> l.dbid = 30 and u1.sid = l.sid
> and m.memberuid = u1.uid
> and m.groupuid = u2.uid' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> EXECUTE(@.exec_stmt)
> end --loop 10
> --
> Thanks,
> Bing
>

Invalid column name 'dbid'?

Hi,
I modified sp_helplogins a bit and made it just return the results about
one certain database (In this case, dbid is 30). So I added the condition
'l.dbid=30' to the following statement. But running the modified
sp_helplogins returned error "Invalid column name 'dbid'".
My master database does not have a table called syslogins, instead the table
is sysxlogins. But the original sp_helplogins which uses syslogins table
runs fine. I'm confused. What did I do wrong? Appreciate any help.
-- Add the User info to holding table.
select @.exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
+
' UNION
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
l.dbid = 30 and u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
EXECUTE(@.exec_stmt)
end --loop 10
--
Thanks,
BingAfter looking more, looks like syslogins is a view on the sysxlogins table.
'dbid' is not a field in sysloigns. Hmmm..
"bing" wrote:
> Hi,
> I modified sp_helplogins a bit and made it just return the results about
> one certain database (In this case, dbid is 30). So I added the condition
> 'l.dbid=30' to the following statement. But running the modified
> sp_helplogins returned error "Invalid column name 'dbid'".
> My master database does not have a table called syslogins, instead the table
> is sysxlogins. But the original sp_helplogins which uses syslogins table
> runs fine. I'm confused. What did I do wrong? Appreciate any help.
> -- Add the User info to holding table.
> select @.exec_stmt = '
> INSERT #tb1_UA
> (
> DBName
> ,LoginName
> ,UserName
> ,UserOrAlias
> )
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u.name
> ,''User''
> from
> ' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
> ,master.dbo.syslogins l
> where
> l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> +
> ' UNION
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u2.name
> ,''MemberOf''
> from
> ' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
> ,master.dbo.syslogins l
> where
> l.dbid = 30 and u1.sid = l.sid
> and m.memberuid = u1.uid
> and m.groupuid = u2.uid' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> EXECUTE(@.exec_stmt)
> end --loop 10
> --
> Thanks,
> Bing
>

Invalid attempt to read when no data is present

I am getting this error message when I go to run my report. I have another
report that is almost identical and when there is no records to return it
doesn't generate an error message. Anyone have any ideas?
Thanks in advance.I found my problem, I had an extra if statement in my stored procedure. Not
sure how this stored procedure executed though but my report is working fine
now.
"GORAMS" wrote:
> I am getting this error message when I go to run my report. I have another
> report that is almost identical and when there is no records to return it
> doesn't generate an error message. Anyone have any ideas?
> Thanks in advance.

Interpreting Statistics IO

This is my orignal statistics io return:
Table 'MyTable'. Scan count 1, logical reads 4338, physical reads 0, read-
ahead reads 0.
This is my revised statistics io return:
Table 'MyTable'. Scan count 117, logical reads 536, physical reads 0, read-
ahead reads 0.
Do I view this as an improvement since the logical reads reduced? Profiler
shows a large reduction in duration and reads when comparing the original to
the revised. The Scan count grew substantially, yet the logical reads dropped
substantially. Based upon Profiler my assumption is that the logical reads
reduction is the best indicator. True?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1> Based upon Profiler my assumption is that the logical reads
> reduction is the best indicator. True?
Yes. Logical reads is as close as you can get to seeing how much SQL Server
had to actualy hit the hard drive. The only reason "Physical Reads" are zero
is because the data your query needed is already in the SQL Server data
cache.
You could run
DBCC DROPCLEANBUFFERS
to empty the cache, forcing SQL Server to actually hit the hard drive, and
you'd see how slow your query really could take.
But hard drive access is everything.|||Compare to a query that i wish i could make faster:
Table 'Worktable'. Scan count 155, logical reads 16039, physical reads 0,
read-ahead reads 0.
Table 'Patrons'. Scan count 4, logical reads 12, physical reads 0,
read-ahead reads 0.
Table 'Transactions'. Scan count 24242, logical reads 114968, physical reads
0, read-ahead reads 0.
Table 'Transactions'. Scan count 24242, logical reads 194430, physical reads
0, read-ahead reads 0.
Table 'Windows'. Scan count 1566, logical reads 3132, physical reads 0,
read-ahead reads 0.
Table 'Sessions'. Scan count 1566, logical reads 9396, physical reads 0,
read-ahead reads 0.
Table 'LCTTransactions'. Scan count 13084, logical reads 39312, physical
reads 0, read-ahead reads 0.
Table 'LCTTransactions'. Scan count 13084, logical reads 26412, physical
reads 0, read-ahead reads 0.
Table 'LCTs'. Scan count 4, logical reads 40, physical reads 0, read-ahead
reads 0.
Table 'LCTs'. Scan count 4, logical reads 372, physical reads 0, read-ahead
reads 92.
Table 'Worktable'. Scan count 135, logical reads 14779, physical reads 0,
read-ahead reads 0.|||Thanks Ian.
For further clarification on the relation to Reads shown in Profiler and the
logical and physical reads listed for the same procedure in Statistics IO, it
appears the Reads shown in Profiler are greater than the sum of the logical
and physical reads produced for the same procedure in Statistics IO. Is
Profiler showing reads against System tables whereas Statistics IO does not?
Ian Boyd wrote:
>> Based upon Profiler my assumption is that the logical reads
>> reduction is the best indicator. True?
>Yes. Logical reads is as close as you can get to seeing how much SQL Server
>had to actualy hit the hard drive. The only reason "Physical Reads" are zero
>is because the data your query needed is already in the SQL Server data
>cache.
>You could run
>DBCC DROPCLEANBUFFERS
>to empty the cache, forcing SQL Server to actually hit the hard drive, and
>you'd see how slow your query really could take.
>But hard drive access is everything.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||> Is
> Profiler showing reads against System tables whereas Statistics IO does not?
Yes. Also, STATISTICS IO doesn't show I/O performed by scalar UDFs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:616e5ea2a1085@.uwe...
> Thanks Ian.
> For further clarification on the relation to Reads shown in Profiler and the
> logical and physical reads listed for the same procedure in Statistics IO, it
> appears the Reads shown in Profiler are greater than the sum of the logical
> and physical reads produced for the same procedure in Statistics IO. Is
> Profiler showing reads against System tables whereas Statistics IO does not?
> Ian Boyd wrote:
>> Based upon Profiler my assumption is that the logical reads
>> reduction is the best indicator. True?
>>Yes. Logical reads is as close as you can get to seeing how much SQL Server
>>had to actualy hit the hard drive. The only reason "Physical Reads" are zero
>>is because the data your query needed is already in the SQL Server data
>>cache.
>>You could run
>>DBCC DROPCLEANBUFFERS
>>to empty the cache, forcing SQL Server to actually hit the hard drive, and
>>you'd see how slow your query really could take.
>>But hard drive access is everything.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1