How to Create Store Procedure

Hi all,
I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do?Okay, first off I am FAMOUS for writing the kludgiest code on the planet. This proc may or may not work for you (it does for me). It may or may not be very fast. It may or may not meet with best practices guidelines (it probably doesn't). But it should give you some things to work with (a stored procedure, parameters, working with results). You need to do a bit of research on your own. Google is your friend.

Supporting Tables

create table Users (Username varchar(255), [Password] varchar(255))

Supporting Data

insert Users (UserName, [Password]) values ('tscott','123')
go
insert Users (UserName, [Password]) values ('ascott','abc')
go
insert Users (UserName, [Password]) values ('bscott','Abc')
go

select * from users2

Stored Procedure

CREATE PROC spValidatePassword ( @.UserName varchar(255),
@.Password varchar(255)
)

/************************************************** *****
* spValidatePassword
* hmscott
*
* Validates a username/password against a table of known username/passwords
* Warning: data is not encrypted; these passwords are not secure. This
* sample is for demonstration only and is not intended for production
* use.
*
* Parameters:
* IN: UserName The usernmae provided by the user
* Password The password provided by the user
*
* Returns:
* Single-row recordset (ReturnCode):
* -1: UserName not found
* 0: Password incorrect for this user
* 1: Password correct for this user
************************************************** ****/

AS

DECLARE @.LocalPassword varchar(255), @.ReturnCode int

SELECT @.LocalPassword = [Password]
FROM dbo.Users2
WHERE UserName = @.UserName

IF @.LocalPassword IS NULL
BEGIN
SELECT @.ReturnCode = -1 -- User not valid
END
ELSE
BEGIN
IF @.LocalPassword = @.Password COLLATE Latin1_General_CS_AS
-- IF @.LocalPassword = @.Password -- Use this line instead of the above line if you want the passwords to be case insensitive
BEGIN
SELECT @.ReturnCode = 1 -- Password matches
END
ELSE
BEGIN
SELECT @.ReturnCode = 0 -- Password does not match
END
END

SELECT @.ReturnCode as ReturnCode

spValidatePassword 'nouser', 'Foo'

spValidatePassword 'bscott', 'Abc'

spValidatePassword 'bscott', 'abc'sql

How to create sql server login for workgroup computer?

How can I add a new sql server login using windows authentication where the login id is from a user belonging in a workgroup?

No domain connection. Just a workgroup account will have a login in the sql server using windows authentication. Thanks
Thats not possible, WIndows authentication can be used if the server can prove your identity. I a (non-trusted) workgroup, this is not possible, because everyone can join the workgoup and could spoof another ones identity.

You can only use the local accounts of the SQL Server to do so. In those enviroments you can impersonate the user with a remote account on the SQL Server machine programmatically (like in C# to run under a remote account of the SQL Server box).

There is another way of choosing the same name and the same password on the SQL Server as well as on the client bix, but I don′t think and stated that several times, that this is not a proficient solution.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How to create SQL Server Express server (INSTANCENAME) programmatically?

In deploying SQL Express, how do I create a new server (INSTANCENAME)
programmatically?
What would be the best approach to accomplish this.

Thank you so much,(ianicbass@.yahoo.com) writes:
> In deploying SQL Express, how do I create a new server (INSTANCENAME)
> programmatically?
> What would be the best approach to accomplish this.

You should look in Books Online under installing SQL Server, and
particularly unattended installation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to create sql server 2005 instance(after uninstalling sql server express)

I have uninsttaled sql server 2005 express then installed sql server 2005 developer edition.All things are installed and working fine like management studio,BOL,reporting services ,e.t.c.

But there is no instance of sql server 2005.I can connect to sql server 2000 instance from management studio.

I have check from all ways but there isn't any instance.

Could I create a instance of sql server 2005 from some where if yes the how.

You can also check my post at sqlteam forum http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63600

If you run setup again you can create another instance of SQL Server on the same machine. Just give it a new instance name when you run setup.

|||

Thanks Alexander.

I achieve it exactly the same way a week earlier.What I were trying to find is that is there any way we can create an instance without reinstalling full sql server 2005.As at that time when there isn't any instance but i can connect from management studion to sql server 2000.

|||No You need to go through installation again to install a new instance. For speed you can limit what you install to only a new DB Engine which is quicker than installing all the sql server 2005 components again.|||

Thanks alexander.

That indeed increased my knowledge

How to create SQL Login audit log?

Hi, All
I use SQL Server 2000 and Win 2003 Server. I try to create a SQL Server
Login audit log using Profiler, but cannot find the tools. I looked at the
Windows Event Viewer, it only logs Windows authentications users for SQL
Server not SQL Server users (Mixed mode users).
Please help on creating SQL Server Login log (username,password and time).
Thanks
KaiHello,
SQL Profiler is a component of SQL Client Tools. Check if you have
installed SQL Client Tools on your server. By default, you can start SQL
Profiler by clicking Start > All Programs > Microsoft SQL Server >
Profiler.
To audit on successful login attempts or failed login attempts, you need to
select the option "Audit level: success" or "Audit level: failure" or
"Audit level: All" in the SQL Server Properties (Security Tab). You can
refer to the following information in the "SQL Server Properties (Security
Tab)" topic in SQL server Books Online(BOL):
All
Audit on both successful and failed login attempts. You can record
attempted user accesses as well as other SQL Server log information, and
enable auditing for both security modes, and you can record information on
both trusted and nontrusted connections. Log records for these events
appear in the Windows application log, the SQL Server error log, or both,
depending on how you configure logging for your SQL Server.
If you select this option, you must stop and restart the server to enable
auditing.
--
For more information, refer to the "SQL Server Properties (Security Tab)"
topic in BOL.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Guo,
Thanks for your help.
I setup "All" for Audit level in security, after that, in application
view, I restart SQL Server, I found user colum is "N/A" or "System". Any
thing I did not do?
Thanks
Kai
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:15x7bmQjFHA.588@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> SQL Profiler is a component of SQL Client Tools. Check if you have
> installed SQL Client Tools on your server. By default, you can start SQL
> Profiler by clicking Start > All Programs > Microsoft SQL Server >
> Profiler.
> To audit on successful login attempts or failed login attempts, you need
> to
> select the option "Audit level: success" or "Audit level: failure" or
> "Audit level: All" in the SQL Server Properties (Security Tab). You can
> refer to the following information in the "SQL Server Properties (Security
> Tab)" topic in SQL server Books Online(BOL):
> --
> All
> Audit on both successful and failed login attempts. You can record
> attempted user accesses as well as other SQL Server log information, and
> enable auditing for both security modes, and you can record information on
> both trusted and nontrusted connections. Log records for these events
> appear in the Windows application log, the SQL Server error log, or both,
> depending on how you configure logging for your SQL Server.
> If you select this option, you must stop and restart the server to enable
> auditing.
> --
> For more information, refer to the "SQL Server Properties (Security Tab)"
> topic in BOL.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello,
User colum is "N/A" in application view of Event Viewer if you log on as a
SQL login. However, double-click the event , you may read the information
looks like the followings in the event properties:
18454 :
Login succeeded for user 'sa'. Connection: Non-Trusted.
18456 :
Login failed for user 'sa'.
or you can find the following information in the SQL logs:
2005-07-20 16:48:10.32 logon Login failed for user 'sa'.
2005-07-20 16:48:14.75 logon Login succeeded for user 'sa'. Connection:
Non-Trusted.
By default, the SQL logs located in the folder "C:\Program Files\Microsoft
SQL Server\MSSQL\LOG".
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

How to create SQL from XSD file

Hello,
I am looking for a way to build an SQL database from an XML schema.1. Try XSLT
2. Read XML using XML Dom, then do a loop -thru|||Also, you may want to look at the SchemaGen option of the SQLXML XML
Bulkload facility. It can give you a first cut at generating the table
definitions.
Best regards
Michael
"Neo" <pravinsable@.gmail.com> wrote in message
news:1144871606.564411.290310@.u72g2000cwu.googlegroups.com...
> Hello,
> I am looking for a way to build an SQL database from an XML schema.
>sql

How to create sql for retrieving 10 records per time in .NET

I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.
ThanksAs a great man once said: Huh?

I'm sure that your posting made sense to you, but I don't even have a clue what you meant. Could you try to repost and fill in a few of the details that I'm missing? I'd like to help you if I could, but at the moment I don't understand enough of what you want to have a chance!

-PatP|||Hi,

as Pat said, this isn't very clear and you really should repost. I just want to comment on what you may need to consider mentioning.

Almost all databases are used by multiple users at the same time. Selecting the 10 first records in a particular order makes perfect sense, as does selecting record 11 to 20 of that same recordset. But selecting 10 and then (at a later time) selecting "the ten next" doesn't have any clear meaning since the data may well have changed in the meantime. So perhaps it's a good idea to clarify how you want your system to deal with the different change scenarios: records may have been added, removed or updated, or any mix of the three between the two queries.

One way to "page in the database":
Initialize:
- establish a single orderby column for your query (if you have several columns, combine them into one column yielding equivalent sort order)
- select the top 10 records
- persist the value of the orderby column for the last record. In ASP.NET, use ViewState for this.

Each round trip:
- use the value of the last record in the previous result set to select the next ten:
SELECT TOP 10 ... WHERE ... AND ordCol > @.lastValue ORDER BY ordCol
- persist the last value again

Of course, if you require to page the other way (previous page) you must also persist the value of the first record. Furthermore, since there's no BOTTOM keyword to mirror TOP, you must use reverse sort order (otherwise, if you did SELECT TOP 10 ... WHERE ordCol < @.firstVal, the first page would be returned even if you tried to get the previous one from, say, page 5). Since you still want to display in-order, fix this by selecting into a temp table and then select in-order from that one:

SELECT TOP 10 INTO #page ... WHERE ... AND ordCol < @.firstVal ORDER BY ordCol DESC
SELECT * FROM #page ORDER BY ordCol

I hope this helps!

Dag|||Or you could just set-up paging in a datagrid...|||Huh?

Just a guess...

You want to do paging

http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx

Make sure to say hi to Jeff for me
__________________|||I meant that you could use a .NET DataGrid control that allows you to view a recordset one page at a time. You can define how many records you want displayed on each page. Of course this deals with paging at the Application level - don't know if that's what he wants but just thought I'd mention it.

I don't have a clue about paging at the SQL Server level...:-)|||Sorry for unclear question. Yes, what i want to do is paging.
Brett: The URL http://weblogs.sqlteam.com/jeffs/ar...03/22/1085.aspx is unavailable
mmcdonald: Thanks for your suggestion but the user don't want the result be shown in datagrid :(