Can I have SQL server clear a value after a certain amount of time?

I have an application that allows users to "reserve" a certain time and location for practice sessions. I want them to look at a web form that shows which times are available and then allows them to select an open slot and then submit this form with some personal information. My problem is that as soon as they select an open slot, other users should see that this slot is no longer available. On the other hand, if someone selects an open slot and then takes too long to type in their personal information, I want it somehow to kick them out and re-open this slot back up.

I know that I can create a field called 'status' in my database that gets turn to something like 'pending' as soon as the first user clicks on an open slot, but how would I turn this field back to the 'open' state if the user took too long to enter their data?

Any help would be surely appreciated.

Cheers,
AzFyou can store the time the page was requsted in some variable and check against some preset time like 5 min or 10 min...also when do you set the slot as reserved...as soon as the user clicks on the spot or at the end of the page after the user finished entering all the info...? i would suggest doing the latter...

hth

Can I grant a group access to my db

I would like to be able to do something like this:
use mydb
exec sp_grantdbaccess 'localhost\Users'
go
When I do, I get this error msg:
Windows NT user or group 'localhost\Users' not found. Check the name
again.
The error message implies that I should be able to add a group.
A co-worker read on the web somewhere that this should work:
exec sp_grantdbaccess 'localhost\domain Users'
but it also fails the same way.
Any help would be appreciated,
TIA,
DaveDid you add that group as a login to SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <kaatzd@.hotmail.com> wrote in message
news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...
>I would like to be able to do something like this:
> use mydb
> exec sp_grantdbaccess 'localhost\Users'
> go
> When I do, I get this error msg:
> Windows NT user or group 'localhost\Users' not found. Check the name
> again.
> The error message implies that I should be able to add a group.
> A co-worker read on the web somewhere that this should work:
> exec sp_grantdbaccess 'localhost\domain Users'
> but it also fails the same way.
> Any help would be appreciated,
> TIA,
> Dave
>|||Users is a predefined group, my understanding was that SQL would
recognize it.
Be that as it may, someone else in my company gave me the solution:
exec sp_grantdbaccess 'NT AUTHORITY\Authenticated Users'
and then I also needed to add above users to a role for my db, like so:
exec sp_addrolemember 'db_datareader', 'NT AUTHORITY\Authenticated
Users'
Thanks,
Dave
Tibor Karaszi wrote:
> Did you add that group as a login to SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dave" <kaatzd@.hotmail.com> wrote in message
> news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...
> >I would like to be able to do something like this:
> >
> > use mydb
> > exec sp_grantdbaccess 'localhost\Users'
> > go
> >
> > When I do, I get this error msg:
> > Windows NT user or group 'localhost\Users' not found. Check the name
> > again.
> >
> > The error message implies that I should be able to add a group.
> >
> > A co-worker read on the web somewhere that this should work:
> >
> > exec sp_grantdbaccess 'localhost\domain Users'
> >
> > but it also fails the same way.
> >
> > Any help would be appreciated,
> > TIA,
> > Dave
> >

Can I grant a group access to my db

I would like to be able to do something like this:
use mydb
exec sp_grantdbaccess 'localhost\Users'
go
When I do, I get this error msg:
Windows NT user or group 'localhost\Users' not found. Check the name
again.
The error message implies that I should be able to add a group.
A co-worker read on the web somewhere that this should work:
exec sp_grantdbaccess 'localhost\domain Users'
but it also fails the same way.
Any help would be appreciated,
TIA,
DaveDid you add that group as a login to SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <kaatzd@.hotmail.com> wrote in message
news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...
>I would like to be able to do something like this:
> use mydb
> exec sp_grantdbaccess 'localhost\Users'
> go
> When I do, I get this error msg:
> Windows NT user or group 'localhost\Users' not found. Check the name
> again.
> The error message implies that I should be able to add a group.
> A co-worker read on the web somewhere that this should work:
> exec sp_grantdbaccess 'localhost\domain Users'
> but it also fails the same way.
> Any help would be appreciated,
> TIA,
> Dave
>|||Users is a predefined group, my understanding was that SQL would
recognize it.
Be that as it may, someone else in my company gave me the solution:
exec sp_grantdbaccess 'NT AUTHORITY\Authenticated Users'
and then I also needed to add above users to a role for my db, like so:
exec sp_addrolemember 'db_datareader', 'NT AUTHORITY\Authenticated
Users'
Thanks,
Dave
Tibor Karaszi wrote:[vbcol=seagreen]
> Did you add that group as a login to SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dave" <kaatzd@.hotmail.com> wrote in message
> news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...

Can I get ordinary users to create reports rather than admin...?

Can I get ordinary users to create reports rather than admin using a web browser interface?

If you mean with the Reporting Model, yes. Without Reporting Builder you will need Visual Studio.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for replying - It looks OK however the Reporting Builder program would have to be installed on the end users machines (meaning we would have to convince IT Departments to let them do this) and would have to use a DBA to set up the initial database models for each business. So I'm thinking this is probably not the best solution.

A reporting section is integrated within the current application that the user can configure. In the new application we didnt want to re-code this functionality and not to have a DBA to configure the reports and prefer not to install extra applications.

If you could think of any 3rd parties that are similar to Reporting Builder that could integrate with our application I would be really grateful? You mentioned Visual Studio - Will I be able to integrate it their?

can i do RDA transfer using integrated security?

or do i need to setup SQL security users and logins.?

The following articles will help you to understand the various ways for configuring security for replication

http://msdn2.microsoft.com/en-us/library/bb226707.aspx

http://msdn2.microsoft.com/en-us/library/ms172917.aspx

Manish Agnihotri

Program Manager

SQL Compact

sql

Can I disconnect users on a timed basis?

I know SQL Server is a pretty powerful beast and I can use SQL Server Agent
Jobs to run things on a timed basis, but can anyone answer...
Is it possible to write scripts or stored procedures (or whatever is needed)
to disconnect a user from the database after a period of inactivity?
If so, can someone give me a clue how I would do this or possibly point me
in the right direction.
Thanks!Hi,
Yes it is possible to do, But please check the status of that user before
disconnecting. If it is runnable please wait for some more time and
kill the user once the status become "Sleeping"
declare @.status varchar(30)
declare @.pid int,@.sql nvarchar(100)
select @.status=status from master..sysprocesses where loginame='user_name'
if @.status = 'sleeping'
begin
select @.pid = spid from master..sysprocesses where loginame='user_name'
set @.sql= 'kill '+convert(char,@.pid)
exec sp_executesql @.sql
end
Schedule the script thru SQL Agent jobs. THe above script can take care of 1
user kill . If you have mutiple user, you have to slightly modify the script
Thanks
Hari
MCDBA
"Dave Roys" <daveroys@.xtra.co.nz> wrote in message
news:u4FeDTmAEHA.2600@.TK2MSFTNGP09.phx.gbl...
> I know SQL Server is a pretty powerful beast and I can use SQL Server
Agent
> Jobs to run things on a timed basis, but can anyone answer...
> Is it possible to write scripts or stored procedures (or whatever is
needed)
> to disconnect a user from the database after a period of inactivity?
> If so, can someone give me a clue how I would do this or possibly point me
> in the right direction.
> Thanks!
>

Can I carry my existing DB users to a new server and restore??

Hi,

Quick question, I have about 20 users in my local server and database. We
are looking to restore that database to a new server in a new network and
still be able to retain the database users.

We have created all 20 logins in the new server (exact names) but when we
restore the database I do not see the users anymore!

Is there something we can do to preserve them or a script we can run to link
them again?

Thanks a bunch!!!

S"SD" <npspam@.nowhere.net> wrote in message
news:138249382eb68f5a6766a476adc3da38@.news.teranew s.com...
> Hi,
> Quick question, I have about 20 users in my local server and database. We
> are looking to restore that database to a new server in a new network and
> still be able to retain the database users.
> We have created all 20 logins in the new server (exact names) but when we
> restore the database I do not see the users anymore!

sp_change_users_login is probably what you need.

> Is there something we can do to preserve them or a script we can run to
link
> them again?
> Thanks a bunch!!!
> S
>|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<7Jtjc.122289$e17.75072@.twister.nyroc.rr.com>...
> "SD" <npspam@.nowhere.net> wrote in message
> news:138249382eb68f5a6766a476adc3da38@.news.teranew s.com...
> > Hi,
> > Quick question, I have about 20 users in my local server and database. We
> > are looking to restore that database to a new server in a new network and
> > still be able to retain the database users.
> > We have created all 20 logins in the new server (exact names) but when we
> > restore the database I do not see the users anymore!
> sp_change_users_login is probably what you need.
>
> > Is there something we can do to preserve them or a script we can run to
> link
> > them again?
> > Thanks a bunch!!!
> > S

You can run the following script to generate a create script for all
of your users. It will generate a create statement for each login
using the encrypted password and SID. Replace "MY_DB" in the script
with your database name, then run the script against your source
database. Copy the output of the script to your destination server and
run it. Then, when you restore your database, your users will
automatically be synched with their logins. Hope this helps!

Lisa

-----
--Description: Scripts logins for one db
--*****IMPORTANT replace MY_DB with the name of the database that
holds the users that you need scripted

-- Begin Script, Create sp_help_revlogin procedure --
USE master
GO
SET NOCOUNT ON
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_MY_DBLogins @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)

IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT a.sid, a.name, a.xstatus, a.password FROM
master..sysxlogins a
join MY_DB..sysusers MY_DB on a.sid = MY_DB.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'

ELSE
DECLARE login_curs CURSOR FOR
SELECT a.sid, a.name, a.xstatus, a.password FROM
master..sysxlogins a
join MY_DB..sysusers MY_DB on a.sid = MY_DB.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'

OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus,
@.binpwd
IF (@.@.fetch_status = -1)
BEGIN
--PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd
+ ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' +
@.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus,
@.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
-- End Script --
--
EXEC master..sp_help_MY_DBLogins
--drop proc sp_help_MY_DBLogins

Can I buy 1 per seat license if I have 20 users login using same i

Hi,
May I know can I just buy 1 per seat license if I have 20 users login using
same id? If I have misunderstood this per seat license concept, pls highligh
t
to me.
Thanks
andrewNo. A seat does not correspond to a login id.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:161927B9-FBEE-405F-B869-3DA69522B237@.microsoft.com...
> Hi,
> May I know can I just buy 1 per seat license if I have 20 users login
> using
> same id? If I have misunderstood this per seat license concept, pls
> highlight
> to me.
> Thanks
> andrew|||Besides, per Seat was last available only on SQL Server 7.0. Now, your only
options are CAL based on per user or per device or per processor. This has
nothing to do with concurrent connections.
Sincerely,
Anthony Thomas
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:u51B5$uKFHA.2284@.TK2MSFTNGP10.phx.gbl...
No. A seat does not correspond to a login id.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:161927B9-FBEE-405F-B869-3DA69522B237@.microsoft.com...
> Hi,
> May I know can I just buy 1 per seat license if I have 20 users login
> using
> same id? If I have misunderstood this per seat license concept, pls
> highlight
> to me.
> Thanks
> andrew

Can I buy 1 per seat license if I have 20 users login using same i

Hi,
May I know can I just buy 1 per seat license if I have 20 users login using
same id? If I have misunderstood this per seat license concept, pls highlight
to me.
Thanks
andrew
No. A seat does not correspond to a login id.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:161927B9-FBEE-405F-B869-3DA69522B237@.microsoft.com...
> Hi,
> May I know can I just buy 1 per seat license if I have 20 users login
> using
> same id? If I have misunderstood this per seat license concept, pls
> highlight
> to me.
> Thanks
> andrew
|||Besides, per Seat was last available only on SQL Server 7.0. Now, your only
options are CAL based on per user or per device or per processor. This has
nothing to do with concurrent connections.
Sincerely,
Anthony Thomas

"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:u51B5$uKFHA.2284@.TK2MSFTNGP10.phx.gbl...
No. A seat does not correspond to a login id.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:161927B9-FBEE-405F-B869-3DA69522B237@.microsoft.com...
> Hi,
> May I know can I just buy 1 per seat license if I have 20 users login
> using
> same id? If I have misunderstood this per seat license concept, pls
> highlight
> to me.
> Thanks
> andrew

Can I buy 1 per seat license if I have 20 users login using same i

Hi,
May I know can I just buy 1 per seat license if I have 20 users login using
same id? If I have misunderstood this per seat license concept, pls highlight
to me.
Thanks
andrewNo. A seat does not correspond to a login id.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:161927B9-FBEE-405F-B869-3DA69522B237@.microsoft.com...
> Hi,
> May I know can I just buy 1 per seat license if I have 20 users login
> using
> same id? If I have misunderstood this per seat license concept, pls
> highlight
> to me.
> Thanks
> andrew|||Besides, per Seat was last available only on SQL Server 7.0. Now, your only
options are CAL based on per user or per device or per processor. This has
nothing to do with concurrent connections.
Sincerely,
Anthony Thomas
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:u51B5$uKFHA.2284@.TK2MSFTNGP10.phx.gbl...
No. A seat does not correspond to a login id.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net/home/listings.aspx
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:161927B9-FBEE-405F-B869-3DA69522B237@.microsoft.com...
> Hi,
> May I know can I just buy 1 per seat license if I have 20 users login
> using
> same id? If I have misunderstood this per seat license concept, pls
> highlight
> to me.
> Thanks
> andrew

can I bind a Label control to a SqlDataSource to display one field from a table?

Hi,

What's the 'new' declarative way to fetch one field from a SQL table and display the result in a Label control?

I have a users table with a fullname field. I want to query the table and retrieve the fullname where the usertable.username = Session("loggedinuser")

I can make the SqlDataSource ok that has the correct Select query, but not sure how to bind the label to that.

thanks,
BruceThe only way I know of, and I hope there is a better way, but I don't know it is to put a formview control on the page. Bind the formview to the datasource, and place the label within the formview, then databind the label to the fullname column.|||Thanks Motley,

I did end up doing that approach. It works ok, just feels a little cumbersome..but maybe it's the correct way for now.

I just feel like there's a missing control that lets you easily bind to a singe output parameter from a query/stored procedure.

-Bruce|||I agree bruce, but unfortunely I lost my inventation to the meeting where the MS team discussed this|||If you're wanting to retrieve the value from a return (output) parameter - that is possible. Take a look at the SqlDataSource controls Selected event. In that event, after the select has taken place, you can retrieve the output paramter's value...

Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected
Me.txtWhatEver.Text = e.Command.Parameters("parameterNameHere").Value
End Sub

This might help too... http://www.asp.net/QuickStart/util/srcview.aspx?path=~/aspnet/samples/data/RetValAndOutputParams.src

I do wish there were a way to retrieve data other than what can be returned in an output parameter... like a field in a DataRow for example. The data controls (like a FormView) are doing this somehow... so why can't I get access to that some functionality in code-behind?

Can I add a link in a users "My Reports" to a report on a different report server?

We are in the process of commissioning a new reporting server. In the interim, I want to be able to update/modify reports and publish to one location only, and publish links to the users "My Reports" on the old server. Any ideas folks?

Report Manager only talks to a single server, so you can't mix reports from one server with another.

A quick workaround would be to upload an HTML file as a resource into the user's My Reports folder which redirects them to the new server.

Can i access SQL Login Users in ASP.Net application

Hello All,

I have SQL Server 2005 Installed and i have numbers of SQL Logins users which i want to use in my ASP.Net Application for authentication.

I am using ASP.Net 2.0 (VS.Net 2005).

Can i use the SQL Logins for ASP.Net web application authentication like the way we use Active Directory users?
Thanks in advance,
Wajahat Abbas
http://www.wajahatabbas.com

SQL Authentication is specific to SQL. You will not be able to use SQL Logins as you use Windows users.

See also:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaspnetauthentication.asp

Thanks
Laurentiu

|||

Thanks for replying.

Ya, i was thinking that with SQL 2005 and ASP.Net release, MS will increase the ASP.Net authenticaiton modes.

As SQL Server 2005 provides very cool features for SQL Users (like bad password, rating), so once we able to consume those things in ASP.Net it would be a peice of cake.
Anyway may be in next releases.

Thanks & Regards,
Wajahat Abbas
http://www.wajahatabbas.com
http://www.dotnetpakistan.com

Can i access SQL Login Users in ASP.Net application

Hello All,

I have SQL Server 2005 Installed and i have numbers of SQL Logins users which i want to use in my ASP.Net Application for authentication.

I am using ASP.Net 2.0 (VS.Net 2005).

Can i use the SQL Logins for ASP.Net web application authentication like the way we use Active Directory users?
Thanks in advance,
Wajahat Abbas
http://www.wajahatabbas.com

SQL Authentication is specific to SQL. You will not be able to use SQL Logins as you use Windows users.

See also:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaspnetauthentication.asp

Thanks
Laurentiu

|||

Thanks for replying.

Ya, i was thinking that with SQL 2005 and ASP.Net release, MS will increase the ASP.Net authenticaiton modes.

As SQL Server 2005 provides very cool features for SQL Users (like bad password, rating), so once we able to consume those things in ASP.Net it would be a peice of cake.
Anyway may be in next releases.

Thanks & Regards,
Wajahat Abbas
http://www.wajahatabbas.com
http://www.dotnetpakistan.com

Can CONTAINSTABLE be made to match unconditionally?

Say that users searching a Books database are able to search by Author,
Title, and Publisher using three separate text boxes. They can choose to
leave any or all text boxes empty if they do not care to filter by that
particular item.
Say I have a query that looks similar to this:
b.* FROM Books b
INNER JOIN CONTAINSTABLE( Books, Author, @.author) authorRank ON b.BookId =
authorRank.[KEY]
INNER JOIN CONTAINSTABLE( Books, Title, @.title) titleRank ON ( b.BookId =
titleRank.[KEY] )
INNER JOIN CONTAINSTABLE( Books, Publisher, @.publisher) publisherRank ON (
b.BookId = publisherRank.[KEY] )
This works great if the user chooses to enter something in each of the three
text boxes. However, CONTAINSTABLE does not accept an empty string for the
search condition (nor does it accept *, %, or other wildcards except when
searching for a prefix). How can I get the query to unconditionally match
title and publisher if they leave title and publisher blank but type
something for author?
If there is no way to allow any title and publisher to be returned when the
user leaves those text boxes blank, I will need to write a query for if they
type all three text boxes, a query for if they type in none of the text
boxes, a query if they type in just the publisher but not the title or
author, a query... etc. Not a good solution.
How can I achieve the results I want without writing multiple queries?
Thank you.Hi, Greg
Try something like this (untested):
SELECT * FROM Books
WHERE (@.author IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Author, @.author)
)) AND (@.title IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Title, @.title)
)) AND (@.publisher IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @.publisher)
))
Razvan|||That doesn't let me use any of the ranking information provided by
CONTAINSTABLE. It may be workable. I may have found some sort of
sp_configure setting that will change the behavior of noise words. I'm not
sure about it yet.
"Razvan Socol" wrote:

> Hi, Greg
> Try something like this (untested):
> SELECT * FROM Books
> WHERE (@.author IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Author, @.author)
> )) AND (@.title IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Title, @.title)
> )) AND (@.publisher IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @.publisher)
> ))
> Razvan
>|||> That doesn't let me use any of the ranking information provided by
> CONTAINSTABLE.
In this case, you might want to use something like this (also
untested):
SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookID=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookID=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
b.BookID=z.[KEY]
WHERE (@.author IS NULL OR x.[KEY] IS NOT NULL)
AND (@.title IS NULL OR y.[KEY] IS NOT NULL)
AND (@.publisher IS NULL OR z.[KEY] IS NOT NULL)
Razvan|||If @.author is NULL (or blank), then CONTAINSTABLE will throw an error, which
is pretty much the entire problem here.
"Razvan Socol" wrote:

> In this case, you might want to use something like this (also
> untested):
> SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
> LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookID=x.[KEY]
> LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookID=y.[KEY]
> LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
> b.BookID=z.[KEY]
> WHERE (@.author IS NULL OR x.[KEY] IS NOT NULL)
> AND (@.title IS NULL OR y.[KEY] IS NOT NULL)
> AND (@.publisher IS NULL OR z.[KEY] IS NOT NULL)
> Razvan
>|||> If @.author is NULL (or blank), then CONTAINSTABLE will throw an error [...]
Aha! I told you it was untested... :) I assumed that CONTAINSTABLE will
return an empty resultset when given a NULL search condition.
Obviously, I was wrong.
In this case, we can use a non-existent word instead of NULL, like
this:
SET @.title=ISNULL(@.title,'NotSpecified')
SET @.author=ISNULL(@.author,'NotSpecified')
SET @.publisher=ISNULL(@.publisher,'NotSpecifi
ed')
SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookId=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookId=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
b.BookId=z.[KEY]
WHERE (@.author='NotSpecified' OR x.[KEY] IS NOT NULL)
AND (@.title='NotSpecified' OR y.[KEY] IS NOT NULL)
AND (@.publisher='NotSpecified' OR z.[KEY] IS NOT NULL)
If you think the word "NotSpecified" may appear in a title of a book,
you can change it with another inexisting word.
Razvan

can connect to all servers but one on certain box

I have an issue where users can't connect to a server - Server A - from a pa
rticular client computer - Box B. They can connect to other servers from Box
B - even servers in the same cluster as Server A. On other client machines
the same users can connect
to all servers - including Server A. They are using Windows authorization. I
s there any setting that might need to be changed either in Windows or SQL S
erver on either Server A or Box B that might correct this situation?Sounds like you want to check Client Network Utility and Server Network
Utility on the involved machines.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"hawthorne" <anonymous@.discussions.microsoft.com> wrote in message
news:A3F01255-54EF-4F09-A8AD-0CBA21C6D04B@.microsoft.com...
> I have an issue where users can't connect to a server - Server A - from a
particular client computer - Box B. They can connect to other servers from
Box B - even servers in the same cluster as Server A. On other client
machines the same users can connect to all servers - including Server A.
They are using Windows authorization. Is there any setting that might need
to be changed either in Windows or SQL Server on either Server A or Box B
that might correct this situation?|||Can you PING Server A from Box B ?

Can a virtual server have local Windows users and groups?

Hi,
I have a scenario which works fine on a non-clustered SQL server, and I now
want to implement it on a clustered SQL server.
In a stand-alone, non-clustered environment:
- I have local Windows security groups on the machine; call the groups
MyGroup1, MyGroup2.
- I grant these groups login to the server, and access to the DB, as
follows: (later I add them to DB roles)
declare @.servername sysname
declare @.pos int
set @.servername = serverproperty('MachineName')
set @.pos = charindex(N'\', @.servername, 0)
if @.pos > 0
set @.servername = left(@.servername, @.pos-1)
use master
declare @.loginame sysname
set @.loginame = @.servername + '\MyGroup1'
if (not exists (select name from syslogins where name = @.loginame))
begin
exec sp_grantlogin @.loginame
end
exec sp_defaultdb @.loginame, MyDatabase
I'd like to do something similar on a clustered SQL server. But does a
cluster have a concept of local Windows groups, or must they be domain
groups?
How would I go about setting this up?
Thanks,
John.
I think I can answer this, since a cluster requires a domain account to
run - just make domain groups. Besides creating the local ones would be a
pain, think about failover.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"John [412075]" <John_dot_Knox_hyphen_Davies@.wonderware0com> wrote in
message news:OmdbtRLBFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a scenario which works fine on a non-clustered SQL server, and I
> now
> want to implement it on a clustered SQL server.
> In a stand-alone, non-clustered environment:
> - I have local Windows security groups on the machine; call the groups
> MyGroup1, MyGroup2.
> - I grant these groups login to the server, and access to the DB, as
> follows: (later I add them to DB roles)
> declare @.servername sysname
> declare @.pos int
> set @.servername = serverproperty('MachineName')
> set @.pos = charindex(N'\', @.servername, 0)
> if @.pos > 0
> set @.servername = left(@.servername, @.pos-1)
> use master
> declare @.loginame sysname
> set @.loginame = @.servername + '\MyGroup1'
> if (not exists (select name from syslogins where name = @.loginame))
> begin
> exec sp_grantlogin @.loginame
> end
> exec sp_defaultdb @.loginame, MyDatabase
>
> I'd like to do something similar on a clustered SQL server. But does a
> cluster have a concept of local Windows groups, or must they be domain
> groups?
> How would I go about setting this up?
> Thanks,
> John.
>

can a view of a table imrpove table input performance?

I have a table (sql Server2k) which users connect to through ODBC - read
only. I have to input/write a lot of data to this table everyday. I
observed that it takes a lot longer to populate this table than one which is
not public. I am guessing that people have this table open through ODBC. I
f
I had the users connect to a view of this table (Select * From tbl1) would
this resolve the performance issue?
I realize that people would have to refresh the view each day to get the
most recent data, but I can send out a memo advising them of this.
Any suggestions appreciated. Thanks,
RichUnless it is an indexed view, a view doesn't actually store data. So when
you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT
... view definition) which, long story short, means you're still accessing
the base tables...
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@.microsoft.com...
>I have a table (sql Server2k) which users connect to through ODBC - read
> only. I have to input/write a lot of data to this table everyday. I
> observed that it takes a lot longer to populate this table than one which
> is
> not public. I am guessing that people have this table open through ODBC.
> If
> I had the users connect to a view of this table (Select * From tbl1) would
> this resolve the performance issue?
> I realize that people would have to refresh the view each day to get the
> most recent data, but I can send out a memo advising them of this.
> Any suggestions appreciated. Thanks,
> Rich|||I guess I was thinking about a snapshot view. Is there such a thing?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Unless it is an indexed view, a view doesn't actually store data. So when
> you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT
> ... view definition) which, long story short, means you're still accessin
g
> the base tables...
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@.microsoft.com...
>
>|||So to create an indexed view is it:
Create Index view1Ind
On view1(columnID)
While I am at it, is it possible to have on the most elemental index on a
table and have the rest of the indexes on the view? where the view would
contain all of the data in the table?
"Tibor Karaszi" wrote:

> That would be creating an index on the view, an indexed view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:8500A93B-2C02-452B-A8F2-E45BA68B6F82@.microsoft.com...
>|||On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote:

>So to create an indexed view is it:
>Create Index view1Ind
>On view1(columnID)
Hi Rich,
To create an indexed view, you must use a unique clustered index:
CREATE UNIQUE CLUSTERED INDEX view1Ind
ON view1(columnID)
(assuming that columnID is unique in the view, of course).
Once you have this index in place, you can define additional views on
the index, but these can't be clustered.

>While I am at it, is it possible to have on the most elemental index on a
>table and have the rest of the indexes on the view? where the view would
>contain all of the data in the table?
I guess you can, but what's the point? If the view is an exact copy of
the table, you'll gain some performance by dropping the view and
indexing the table instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Basically, I want to have my cake and eat it. I have about 8-9 indexes on
this one table. But data input (only I input the data programatically -
batch operation) is becoming slow on this one table because people are
opening the table through ODBC connection. I wasnt users to be able to quer
y
the table with as little lag as possible, but I also what to be able to writ
e
data to the table without the lag.
By the way, when I create a new index using the GUI tool in table desing, it
has a selection option of "Do not automatically recompute statistics". I
have been checking this option on for each index I creat. What would yield
more performance? to have this option checked on or off?
Thanks,
Rich
"Hugo Kornelis" wrote:

> On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote:
>
> Hi Rich,
> To create an indexed view, you must use a unique clustered index:
> CREATE UNIQUE CLUSTERED INDEX view1Ind
> ON view1(columnID)
> (assuming that columnID is unique in the view, of course).
> Once you have this index in place, you can define additional views on
> the index, but these can't be clustered.
>
> I guess you can, but what's the point? If the view is an exact copy of
> the table, you'll gain some performance by dropping the view and
> indexing the table instead.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||> Basically, I want to have my cake and eat it.
That tend to be difficult in real life. Indexed views is not a way to make m
odifications have less
lag. The opposite. Then you modify the tables, the index on the view also ne
ed to be maintained,
which can be more costly compared to a similar index on the base table (depe
nding on the
circumstances).
How real time does the reporting tables need to be. Why not create snapshots
which you update
regularly?
In general, you do *not* want to turn off auto-update statistics. Statistics
are not updated (in
general) when you modify. They are updated when you SELECT, if they are out-
of-date. This is so that
the optimizer has good information to go on so it can pick a good plan. See
http://msdn.microsoft.com/library/e...l/statquery.asp for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:54F21B87-C722-45AD-A4E2-BB7DDD3A140F@.microsoft.com...
> Basically, I want to have my cake and eat it. I have about 8-9 indexes on
> this one table. But data input (only I input the data programatically -
> batch operation) is becoming slow on this one table because people are
> opening the table through ODBC connection. I wasnt users to be able to qu
ery
> the table with as little lag as possible, but I also what to be able to wr
ite
> data to the table without the lag.
> By the way, when I create a new index using the GUI tool in table desing,
it
> has a selection option of "Do not automatically recompute statistics".
I
> have been checking this option on for each index I creat. What would yiel
d
> more performance? to have this option checked on or off?
> Thanks,
> Rich
> "Hugo Kornelis" wrote:
>|||you also can:
select * into new_table from old_table;
load data into new_table
drop table old_table;
sp_rename new_table|||This is an interesting idea. Thanks. I could invoke DTS in code (large
table) Actually, well, thinking about it more, I am inserting a few thousan
d
records a day, millions of records. Even with DTS that might be too slow.
But it is an idea. When the table is large enough I may start using OLAP on
it.
"AK" wrote:

> you also can:
> select * into new_table from old_table;
> load data into new_table
> drop table old_table;
> sp_rename new_table
>

Can a Login/Password be validated against a Domain login/passord using a prompt dialog

I have an application database on SQL Server 2K and we've got an NT domain.
I want the users to type in their login and password, but I'd like to
validate that password against the windows NT Domain. Can this be done?
Jared HoffmanHi
This sounds like something that will not be permitted as you could then save
the users account details
You can use integrated security but you don't need a login box, unless you
want a secondary method of verification that you maintain.
John
"Jared Hoffman" <hoffmanj@.kenyon.edu.NO_SPAM> wrote in message
news:edQWKBfDEHA.3804@.TK2MSFTNGP09.phx.gbl...
> I have an application database on SQL Server 2K and we've got an NT
domain.
> I want the users to type in their login and password, but I'd like to
> validate that password against the windows NT Domain. Can this be done?
>
> Jared Hoffman
>|||If the users login to their domain, then they can simply use their nt
credentials to connect.
Change the SQL Server to use Windows Authentication. And change your
application connection string
to make Trusted Connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||My application will be used on computers that may or may not be joined to
the domain so I can't rely on Windows Authentication. I'd like to be able to
authenticate users in such a way I don't have to store another set of user
passwords.
Thanks,
Jared Hoffman
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:pd5X%23SfDEHA.3608@.cpmsftngxa06.phx.gbl...
> If the users login to their domain, then they can simply use their nt
> credentials to connect.
> Change the SQL Server to use Windows Authentication. And change your
> application connection string
> to make Trusted Connections.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||previous post:
"My application will be used on computers that may or may not be joined to
the domain so I can't rely on Windows Authentication. I'd like to be able to
authenticate users in such a way I don't have to store another set of user
passwords.
"
Couple of options:
1. Standard SQL Security
2. Application roles
3. Trusted Security using duplicate nt username and passwords
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.