database errors: reference

Showing posts with label reference. Show all posts

Friday, February 24, 2012

Crosstab queries in MS SQL

Am I right in coming to the conclusion that there is no built in method for
creating crosstab queries in MS SQL 2000? I have form the reference under
"Pivot" in SQL books that demonstrates how to build your own but it doesn't
account for scenarios where I don't know how many column headings I require.
I have mocked up this SQL text in MS Access which shows what I want but I
can't seem to generate such a result set in SQL.
Any Ideas?
Mr. Smith
SQL Text:............
TRANSFORM Sum(testoutput.JobCount) AS SumOfJobCount
SELECT testoutput.Client, testoutput.ContractName,
testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year] AS ReqMt
h
FROM testoutput
GROUP BY testoutput.Client, testoutput.ContractName,
testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year]
PIVOT testoutput.TradeCategory;That's correct. To pivot in SQL 2000 you need to know how many columns
there will be because you essentially have to have a case statement for
each column. SQL 2005 is

in that respect because they've
introduced the PIVOT & UNPIVOT keywords (part of the SELECT clause
syntax) to make the syntax native to the T-SQL language used in Yukon.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mr. Smith wrote:

> Am I right in coming to the conclusion that there is no built in method fo
r
>creating crosstab queries in MS SQL 2000? I have form the reference under
>"Pivot" in SQL books that demonstrates how to build your own but it doesn't
>account for scenarios where I don't know how many column headings I require
.
>I have mocked up this SQL text in MS Access which shows what I want but I
>can't seem to generate such a result set in SQL.
>Any Ideas?
>Mr. Smith
>SQL Text:............
>TRANSFORM Sum(testoutput.JobCount) AS SumOfJobCount
>SELECT testoutput.Client, testoutput.ContractName,
>testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year] AS ReqM
th
>FROM testoutput
>GROUP BY testoutput.Client, testoutput.ContractName,
>testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year]
>PIVOT testoutput.TradeCategory;
>
>|||Mr. Smith meet RAC:)
www.rac4sql.net

Thursday, February 16, 2012

Cross Reference or Pivot table

Hi All,

The problem is about cross reference.

1. I have a third party cross reference store procedure SimpleXTab

CREATE PROCEDURE [dbo].[SimpleXTab2]
@.XField varChar(50),
@.XTable varChar(100),
@.XWhereString varChar(250),
@.XFunction varChar(10),
@.XFunctionField varChar(50),
@.XRow varchar(300),
@.ResultTable varchar(100)
AS
Declare @.SqlStr nvarchar(4000)
Declare @.tempsql nvarchar(4000)
Declare @.SqlStrCur nvarchar(4000)
Declare @.col nvarchar(100)

set @.SqlStrCur = N'Select [' + @.XField + '] into ##temptbl_Cursor from [' + @.XTable + '] ' + @.XWhereString + ' Group By [' + @.XField + ']'

/* select @.sqlstrcur */
exec sp_executesql @.sqlstrcur

declare xcursor Cursor for Select * from ##temptbl_Cursor

open xcursor

Fetch next from xcursor
into @.Col

While @.@.Fetch_Status = 0
Begin
set @.Sqlstr = @.Sqlstr + ", "
set @.tempsql = isnull(@.sqlstr,'') + isnull(@.XFunction + '( Case When ' + @.XField + " = '"+@.Col +
"' then [" + @.XFunctionField + "] Else 0 End) As [" + @.Col + "]" ,'')
set @.Sqlstr = @.tempsql
Fetch next from xcursor into @.Col

End

/* Select @.Sqlstr as [mk], len(@.sqlstr) as [leng] */

set @.tempsql = 'Select ' + @.XRow + ', ' + @.Sqlstr + 'into '+@.ResultTable+' From ' + @.XTable +
@.XWhereString + ' Group by ' + @.XRow
print @.tempsql
set @.Sqlstr = @.tempsql

Close xcursor
Deallocate xcursor

set @.tempsql = N'Drop Table ##temptbl_Cursor'
exec sp_executesql @.tempsql
print @.tempsql
/* Select @.Sqlstr as [mk], len(@.sqlstr) as [leng] */
print @.sqlstr
exec sp_executesql @.Sqlstr

if @.@.rowcount = 0 select 'No Records found'
GO

2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function.
how can i modify SimpleXtab to avoid using math function but still can generate cross reference.

exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'

Many Thanks!

no replying. I didn't describe clearly?

|||

What did you want it to do with multiple values? If there is only one value per row/column, use MAX or MIN.

Cross reference of User Name to Login Name

Is there a cross reference of User Name to Login Name in SQL Server
somewhere? In its system tables? It doesn't appear so? The user
table for a database doesn't contain the login name.
Does anybody know if this cross reference is accessible via system
tables?
ScottHello,
There should be definely mapping between Login and User. A user can not be
created with out a Login. This chain will not be there if you restore a
database from a diferent SQL Server machine. In this case the appropriate
Login will not be there for the database user. To recreate the login chain
you can use SP_CHANGE_USERS_LOGIN system stored procedure. See books online
for its usage.
Thanks
Hari
"scotty" <scasti1@.cox.net> wrote in message
news:1175554544.489300.144210@.d57g2000hsg.googlegroups.com...
> Is there a cross reference of User Name to Login Name in SQL Server
> somewhere? In its system tables? It doesn't appear so? The user
> table for a database doesn't contain the login name.
> Does anybody know if this cross reference is accessible via system
> tables?
> Scott
>|||How you get the login names for database users depends on
what version of SQL Server.
If this is just to check a particular database user, you can
execute sp_help_user 'YourUser' and it will return the user
name in the database as well as the login name at the server
level.
If you just want to know how the tables are related, for SQL
Server 2000 the syslogins and sysusers tables are related by
SID.
If you are on 2005, look at sys.server_principals and
sys.database_principals.
-Sue
On 2 Apr 2007 15:55:44 -0700, "scotty" <scasti1@.cox.net>
wrote:
>Is there a cross reference of User Name to Login Name in SQL Server
>somewhere? In its system tables? It doesn't appear so? The user
>table for a database doesn't contain the login name.
>Does anybody know if this cross reference is accessible via system
>tables?
>Scott|||On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> How you get the login names for database users depends on
> what version of SQL Server.
> If this is just to check a particular database user, you can
> execute sp_help_user 'YourUser' and it will return the user
> name in the database as well as the login name at the server
> level.
> If you just want to know how the tables are related, for SQL
> Server 2000 the syslogins and sysusers tables are related by
> SID.
> If you are on 2005, look at sys.server_principals and
> sys.database_principals.
> -Sue
> On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> wrote:
> >Is there a cross reference of User Name to Login Name in SQL Server
> >somewhere? In its system tables? It doesn't appear so? The user
> >table for a database doesn't contain the login name.
> >Does anybody know if this cross reference is accessible via system
> >tables?
> >Scott
sp_help_user info is just what I need, but I'm having trouble
executing it from .aspx page. I'll keep trying.|||I'm somewhat new to ASP and SQL Server, but in the applications that
I've designed the connection to the SQL Server was through a connetion
string and logging in as a generic user name that we called ASP_User.
Therefore sp_help_user would not return the information that you are
looking for.
I think you're looking for information that would be contained on the
client side in the Environment variables.
System.Environment Properties:
http://msdn2.microsoft.com/en-us/library/system.environment_properties.aspx
Cheers,
Jason Lepack
On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
>
>
> > How you get the login names for database users depends on
> > what version of SQL Server.
> > If this is just to check a particular database user, you can
> > execute sp_help_user 'YourUser' and it will return the user
> > name in the database as well as the login name at the server
> > level.
> > If you just want to know how the tables are related, for SQL
> > Server 2000 the syslogins and sysusers tables are related by
> > SID.
> > If you are on 2005, look at sys.server_principals and
> > sys.database_principals.
> > -Sue
> > On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> > wrote:
> > >Is there a cross reference of User Name to Login Name in SQL Server
> > >somewhere? In its system tables? It doesn't appear so? The user
> > >table for a database doesn't contain the login name.
> > >Does anybody know if this cross reference is accessible via system
> > >tables?
> > >Scott
> sp_help_user info is just what I need, but I'm having trouble
> executing it from .aspx page. I'll keep trying.- Hide quoted text -
> - Show quoted text -|||On Apr 3, 9:55 am, "Jason Lepack" <jlep...@.gmail.com> wrote:
> I'm somewhat new to ASP and SQL Server, but in the applications that
> I've designed the connection to the SQL Server was through a connetion
> string and logging in as a generic user name that we called ASP_User.
> Therefore sp_help_user would not return the information that you are
> looking for.
> I think you're looking for information that would be contained on the
> client side in the Environment variables.
> System.Environment Properties:http://msdn2.microsoft.com/en-us/library/system.environment_propertie...
> Cheers,
> Jason Lepack
> On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> > On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> > > How you get the login names for database users depends on
> > > what version of SQL Server.
> > > If this is just to check a particular database user, you can
> > > execute sp_help_user 'YourUser' and it will return the user
> > > name in the database as well as the login name at the server
> > > level.
> > > If you just want to know how the tables are related, for SQL
> > > Server 2000 the syslogins and sysusers tables are related by
> > > SID.
> > > If you are on 2005, look at sys.server_principals and
> > > sys.database_principals.
> > > -Sue
> > > On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> > > wrote:
> > > >Is there a cross reference of User Name to Login Name in SQL Server
> > > >somewhere? In its system tables? It doesn't appear so? The user
> > > >table for a database doesn't contain the login name.
> > > >Does anybody know if this cross reference is accessible via system
> > > >tables?
> > > >Scott
> > sp_help_user info is just what I need, but I'm having trouble
> > executing it from .aspx page. I'll keep trying.- Hide quoted text -
> > - Show quoted text -
The data rows returned when running sp_help_user in
query_analyzer have data that would be useful in a web application.
For instance, If you could attach a user to a custom application role
in the database, then you could have a user log in with their
user_name, run the procedure, and know what application role that
specific user starts out with, because I think its associated with
their groupname, unless I'm mistaken.
The return view has a GroupName column. Can I access this value? I
run EXEC dbo.sp_helpuser 'user' in the analyzer.
I think I can successfully run the stored procedure from .aspx but I
don't think the sp_help_user procedure was defined to return these
view column values. Apologies for bad terminology I may be using.
One of the defined parameters for that procedure is @.RETURN_VALUE(int,
Return Value)? Want the GroupName column.

Cross reference of User Name to Login Name

cross reference of database

Hi all gurus
From Pubs database I want to get some ObjectProperty value from NorthWind
ObjectProperty(<object id of Northwind DB> , <property> )
Is it possible?
Thanksuse pubs
go
select objectproperty(object_id('northwind.dbo.orders'), 'IsUserTable')
go
AMB
"AM" wrote:

> Hi all gurus
> From Pubs database I want to get some ObjectProperty value from NorthWind
> ObjectProperty(<object id of Northwind DB> , <property> )
> Is it possible?
> Thanks
>
>|||Sorry boss
Its not working
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CA3BF8A4-8AB9-4E9C-BC71-E3C85670551E@.microsoft.com...
> use pubs
> go
> select objectproperty(object_id('northwind.dbo.orders'), 'IsUserTable')
> go
>
> AMB
> "AM" wrote:
>
NorthWind|||AM,
You are right. Function objectproerty expects an object id from current
database. Try,
use pubs
go
declare @.i int
exec sp_executesql N'use northwind; select @.i =
objectproperty(object_id(''dbo.orders''), ''IsUserTable'')', N'@.i int
output', @.i output
print @.i
go
AMB
"AM" wrote:

> Sorry boss
> Its not working
> Thanks
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:CA3BF8A4-8AB9-4E9C-BC71-E3C85670551E@.microsoft.com...
> NorthWind
>
>|||Thanks
It works
But one more question
How can I use for database on other server
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9AA0B83B-509B-4D2F-87A1-A4913B5A898D@.microsoft.com...
> AM,
> You are right. Function objectproerty expects an object id from current
> database. Try,
> use pubs
> go
> declare @.i int
> exec sp_executesql N'use northwind; select @.i =
> objectproperty(object_id(''dbo.orders''), ''IsUserTable'')', N'@.i int
> output', @.i output
> print @.i
> go
>
> AMB
> "AM" wrote:
>
message
'IsUserTable')|||AM,
You have to add the server as a linked server. See sp_addlinkedserver in BOL
.
select
*
from
openquery(linked_server, 'exec sp_executesql N''set nocount on; use
northwind; select objectproperty(object_id(''''dbo.orders''''),
''''IsUserTable'''')''')
go
AMB
"AM" wrote:

> Thanks
> It works
> But one more question
> How can I use for database on other server
> Thanks
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:9AA0B83B-509B-4D2F-87A1-A4913B5A898D@.microsoft.com...
> message
> 'IsUserTable')
>
>|||Thanks for the answer
I have found one more solution
USE northwind
GO
declare @.val varchar(100)
set @.val = (SELECT a.*
FROM OPENROWSET('SQLOLEDB',
'DRIVER={SQL Server};SERVER=amish;UID=sa;database=pub
s',
'select objectproperty(object_id(''pubs.dbo.authors''),
''IsUserTable'')') AS a)
print @.val
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:39FBD04B-066B-4AFD-BBA3-6C309ED41F44@.microsoft.com...
> AM,
> You have to add the server as a linked server. See sp_addlinkedserver in
BOL.
> select
> *
> from
> openquery(linked_server, 'exec sp_executesql N''set nocount on; use
> northwind; select objectproperty(object_id(''''dbo.orders''''),
> ''''IsUserTable'''')''')
> go
>
> AMB
> "AM" wrote:
>
message
current

Friday, February 24, 2012

Crosstab queries in MS SQL

Thursday, February 16, 2012

Cross Reference or Pivot table

Cross reference of User Name to Login Name

Cross reference of User Name to Login Name

Cross reference of User Name to Login Name

cross reference of database

database errors

Blog Archive

About Me