How can I retrieve a recordset from a matrix-like table?

I have a table defined as (int Row, int Column, money Data); as it were a matrix.

Which is the more efficient way of retrieving a result set with the following form?

Column1 Column2 Column3
---- ---- ----
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...


Thanks a lot in advance.Lookup "Crosstab queries" in Books Online, and you will see a perfect example of how to do what you want to do.

How can I query a trusted domain (from MS SQL)?

I've defined a linked ADSI server and I seem to be able to query the
local domain ( localdomain.com ) with:

DBCC TRACEON(7300)
GO
SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
''LDAP://DC=localdomain,DC=com'' ')

But I also have a trusted domain ( trusteddomain.com ) which I would
also like to query from the same SQL-enviroment but this does not work
:( :

DBCC TRACEON(7300)
GO
SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
''LDAP://DC=trusteddomain,DC=com'' ')

Error returned:
"OLE DB error trace [Non-interface error: OLE DB provider
ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without
DBPROPVAL_OO_BLOB being supported].
OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
IRowset::GetNextRows returned 0x8007202b]."

What am I doing wrong? From within windows-explorer I have no problem
obtaining the userlist from trusted domain when I want to assign
file-permissions.
Any idea?Write a VB script with the following:

Set objRoot = GetObject(LDAP://dc=trusteddomain,DC=com)
WScript.Echo objRoot.Name

and run it on the SQL server. IF this does not work, then a linked ADSI
server won't work either. I've never used trusted domains in W2K, but check
DNS settings. The client needs to find a domaincontroller for the given
domain. Also, you could try adding the domaincontroller of the trusted
domain to your query:

LDAP://nameofdcintrusteddomain/dc=trusteddomain,dc=com

Arild

"Ammar" <ammar_fake@.vip.hr> wrote in message
news:647d9cb9.0404211523.d8941c7@.posting.google.co m...
> I've defined a linked ADSI server and I seem to be able to query the
> local domain ( localdomain.com ) with:
> DBCC TRACEON(7300)
> GO
> SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
> ''LDAP://DC=localdomain,DC=com'' ')
> But I also have a trusted domain ( trusteddomain.com ) which I would
> also like to query from the same SQL-enviroment but this does not work
> :( :
> DBCC TRACEON(7300)
> GO
> SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
> ''LDAP://DC=trusteddomain,DC=com'' ')
> Error returned:
> "OLE DB error trace [Non-interface error: OLE DB provider
> ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without
> DBPROPVAL_OO_BLOB being supported].
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> IRowset::GetNextRows returned 0x8007202b]."
> What am I doing wrong? From within windows-explorer I have no problem
> obtaining the userlist from trusted domain when I want to assign
> file-permissions.
> Any idea?|||If should of course be:

Set objRoot.GetObject("LDAP://dc=trusteddomain,DC=com")

(Outlook Express is a bit too _smart_ )

Arild

"Arild Bakken" <arildb_@.hotmail.com> wrote in message
news:O2N%23mZDKEHA.208@.tk2msftngp13.phx.gbl...
> Write a VB script with the following:
> Set objRoot = GetObject(LDAP://dc=trusteddomain,DC=com)
> WScript.Echo objRoot.Name
> and run it on the SQL server. IF this does not work, then a linked ADSI
> server won't work either. I've never used trusted domains in W2K, but
check
> DNS settings. The client needs to find a domaincontroller for the given
> domain. Also, you could try adding the domaincontroller of the trusted
> domain to your query:
> LDAP://nameofdcintrusteddomain/dc=trusteddomain,dc=com
>
> Arild
> "Ammar" <ammar_fake@.vip.hr> wrote in message
> news:647d9cb9.0404211523.d8941c7@.posting.google.co m...
> > I've defined a linked ADSI server and I seem to be able to query the
> > local domain ( localdomain.com ) with:
> > DBCC TRACEON(7300)
> > GO
> > SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
> > ''LDAP://DC=localdomain,DC=com'' ')
> > But I also have a trusted domain ( trusteddomain.com ) which I would
> > also like to query from the same SQL-enviroment but this does not work
> > :( :
> > DBCC TRACEON(7300)
> > GO
> > SELECT * FROM OPENQUERY(ADSI, 'SELECT displayName FROM
> > ''LDAP://DC=trusteddomain,DC=com'' ')
> > Error returned:
> > "OLE DB error trace [Non-interface error: OLE DB provider
> > ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without
> > DBPROPVAL_OO_BLOB being supported].
> > OLE DB error trace [OLE/DB Provider 'ADSDSOObject'
> > IRowset::GetNextRows returned 0x8007202b]."
> > What am I doing wrong? From within windows-explorer I have no problem
> > obtaining the userlist from trusted domain when I want to assign
> > file-permissions.
> > Any idea?

how can I post back a statement from a store procedure to the .aspx page

Hi all,
Anyone can show me how can I catch the 'Print' statement that I have defined in my store procedure using SQL server 2000 DB on the .aspx page? ( I am using ASP.NET 1.0)
My store procedure as follow:
CREATE PROC NewAcctType
(@.acctType VARCHAR(20))
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
PRINT 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
PRINT 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
Thanks for all your replies

The best thing to do would be to either create another parameter and set its type to output or return the statement as a select.
@.message varchar(100) output
set @.message = 'The account type is already exists'
or
select 'The account type is already exist' as message

Nick

|||Hi Nick,
As you stated:
@.message varchar(100) output
set @.message = 'The account type is already exists'
Do I put a return statement like "Return @.message"?
How about if there is no errror in the procedure, do I still need to return any value to the .aspx page?
Thanks.|||Hi Nick,
when I executed my store procedure in SQL 2000 server, I got an error said"Cannot use the OUTPUT option in a DECLARE statement"
But without the Declare keyword, I got an incorrect syntax error, so how can I solve this problem? Is that necessary to put the "output" keyword at the end of the declare varaible statement?
Thanks|||syntax is:
create procedure whateverName
@.message varchar(100) output
as
set @.message = ''
if @.@.ERROR
set @.message = 'your text here.'
If you have no error, the top set statement will allow a blank to be passed back.
Nick|||

Nick,
Here is my syntax:
CREATE PROC DeleteCust
(@.SSN VARCHAR(12), @.message VARCHAR(40) output)
AS
BEGIN

--checks if the SSN is already exist
IF NOT EXISTS (SELECT * FROM Customer WHERE SSN = @.SSN)
BEGIN
SET @.message = 'The SSN is not exist!'
RETURN
END
BEGIN TRANSACTION
DELETE FROM Customer WHERE SSN = @.SSN

--if there is an error on the delete, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Delete failure on Customer table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END

I executed this proc as:
declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
Given that SSN is invalid, I suppose got the message 'The SSN is not exist!", however, I didn't get that message from the execution instead the system message showed "The command(s) completed successfully.", so anywhere I was wrong with the above SP or the execute statement?
Appreciated your reply

|||

If your just looking for the value after a run in QA, add:

declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
select @.message
Nick

|||

Thanks nick. I got the message when I run the Store Procedure in SQL server. However, how can I get the error message when I called the Store Procedure on my .aspx page?
I have these codes on my page: ('DeleteCust' is my store procedure name, 'SSN.Text' is the value from the input box)
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));

var myCommand : SqlDataAdapter = new SqlDataAdapter("DeleteCust", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.SSN", SqlDbType.VarChar, 12)).Value = SSN.Text;
Then, what should I put it here to catch the @.message value? The @.message is VARCHAR, and I have a Return keyword within my procedure, and the return value type is INT?
ping

|||

Add (Code is in VB):

dim parm as new sqlParameter("@.message", sqlDBtype.varchar, 100)

parm.direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parm)
After you do your call to stored proc:
strMessage(Or whatever variable you are adding to) = myCommand.SelectCommand.Parameters(1).Value
Nick

|||Hi Nick,
Could u explain more detail for these statements coz I am new to doing asp, I want to know it more about the meaning of those codes.
parm.direction = ParameterDirection.Output
strMessage(what variable should I add it here? could u give me an example? are u talking about @.message?)
when do I use strMessage() ?
Many thanks.

|||

Here is a quick and dirty article on output parms.

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624

Nick

How can I obtain the login name of the caller of my UDF written in a CLR language?

I am using MS SQL Server 2005.
Is there a way to obtain the login name of the caller of my user
defined function implement in managed C++?
I know I can get the information using the code segment below but I
want to avoid connecting back to the database for performance reasons.
String^ queryString = "select suser_sname()"
SqlConnection conn = gcnew SqlConnection(connectionString);
SqlCommand command = new SqlCommand(queryString, connection);
conn.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
reader[0];
}
Thanks,
StevenExamine using the Security object.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160176423.328986.327470@.i42g2000cwa.googlegroups.com...
>I am using MS SQL Server 2005.
> Is there a way to obtain the login name of the caller of my user
> defined function implement in managed C++?
> I know I can get the information using the code segment below but I
> want to avoid connecting back to the database for performance reasons.
> String^ queryString = "select suser_sname()"
> SqlConnection conn = gcnew SqlConnection(connectionString);
> SqlCommand command = new SqlCommand(queryString, connection);
> conn.Open();
> SqlDataReader reader => command.ExecuteReader(CommandBehavior.CloseConnection);
> while (reader.Read())
> {
> reader[0];
> }
> Thanks,
> Steven
>|||Darn Spell Checker.
Look into the SecurityPrinicipal object.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uBNLaka6GHA.4620@.TK2MSFTNGP02.phx.gbl...
> Examine using the Security object.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "smaully" <smauldin@.ingrian.com> wrote in message
> news:1160176423.328986.327470@.i42g2000cwa.googlegroups.com...
>>I am using MS SQL Server 2005.
>> Is there a way to obtain the login name of the caller of my user
>> defined function implement in managed C++?
>> I know I can get the information using the code segment below but I
>> want to avoid connecting back to the database for performance reasons.
>> String^ queryString = "select suser_sname()"
>> SqlConnection conn = gcnew SqlConnection(connectionString);
>> SqlCommand command = new SqlCommand(queryString, connection);
>> conn.Open();
>> SqlDataReader reader =>> command.ExecuteReader(CommandBehavior.CloseConnection);
>> while (reader.Read())
>> {
>> reader[0];
>> }
>> Thanks,
>> Steven
>|||Arnie,
That will return the Windows user not the sql login.
-Steve|||I read that that is what you desired; "obtain the login name of the caller".
There was nothing to indicate you were using SQL Logins.
Since that is know to and in SQL Server, and not known to the client
application, the only way is to ask SQL Server to provide it. That may
require a 'wasted' round trip. You might call a stored procedure, have the
stored procedure call the UDF, and pass the SQL Login back as a output
parameter.
Of course, I don't know how you are using the UDF, so that may not work for
you.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"smaully" <smauldin@.ingrian.com> wrote in message
news:1160415121.924478.80610@.m7g2000cwm.googlegroups.com...
> Arnie,
> That will return the Windows user not the sql login.
> -Steve
>

How can I get current year in the user defined function.

I have a user defined function in datebase SQL 2000.
function looks like

create function Getcurrentdate
(@.month int, @.day int) returns smalldatetime
begin

declare date1 as smalldatetime
--get current year
--convert month, day and year into current date. then return

return date1
end

my problem was , after using getDate(). I get error meassage which is "can'not use getDate() inside user function"
How can I get current year in the user defined function. Thanks

Unfortunately, you can't use GetDate() in a UDF, here's a link thatgives details and possible work-arounds:http://www.aspfaq.com/show.asp?id=2439

|||

Hi
Here is another option a UDF doing time interval in SQL Server, I am assuming you know SmallDateTime will not give you seconds because of limited resolution. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||Thanks, you guys. it's helpful