Need to build a search stored procedure

I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.

I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?

This question has been answered several times in this forum. Please search for "dynamic where"/similar keywords.|||

What is the standard way of creating a search page of this sort?

|||

This thread discusses the SQL LIKE clause and dynamic queries... should be helpful:

http://forums.asp.net/thread/1529167.aspx

|||I searched dynamic where as well as many other keywords and have had no luck in finding any results. Can you offer any additional assistance?|||

SELECT *

FROM Table

WHERE (Column1 = @.col1 OR @.col1 IS NULL)

AND (Column2 = @.col2 OR @.col2 IS NULL)

AND (Column3 = @.col3 OR @.col3 IS NULL)

...

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restores
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
Matt
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>
|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>
|||Thanks to both of you - that works perfectly
Matt.
[vbcol=seagreen]
> matt
> ALTER DATABASE ... SET SINGLE_USER
> go
> ALTER DATABASE ... SET MULTI_USER
> "matt roberts" <mattwoberts@.gmail.com> wrote in message
> news:6ae5d004cb228c82777fb4f8119@.news.microsoft.co m...

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restor
es
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
MattALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||Thanks to both of you - that works perfectly
Matt.
[vbcol=seagreen]
> matt
> ALTER DATABASE ... SET SINGLE_USER
> go
> ALTER DATABASE ... SET MULTI_USER
> "matt roberts" <mattwoberts@.gmail.com> wrote in message
> news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
>

Need to boot connections to my database

Hi,
We have a batch file that runs every night that drops a database, and restores
it to "baseline" version. But sometimes this fails because users have gone
home and left SQL Suery Analyser connected to the database, which makes the
drop fail.
I need to somehow "kill" all connections to the database - is there a way
to do this in a batch file? I tried stopping and restarting the sqlserver
service on the PC, but that doesn't seem to do it.
Thanks!
MattALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then Restore
THEN back to MULTI_USER
HAve a look at ALTER DATABASE syntax in SQL Books On Line
--
HTH. Ryan
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>|||matt
ALTER DATABASE ... SET SINGLE_USER
go
ALTER DATABASE ... SET MULTI_USER
"matt roberts" <mattwoberts@.gmail.com> wrote in message
news:6ae5d004cb228c82777fb4f8119@.news.microsoft.com...
> Hi,
> We have a batch file that runs every night that drops a database, and
> restores it to "baseline" version. But sometimes this fails because users
> have gone home and left SQL Suery Analyser connected to the database,
> which makes the drop fail.
> I need to somehow "kill" all connections to the database - is there a way
> to do this in a batch file? I tried stopping and restarting the sqlserver
> service on the PC, but that doesn't seem to do it.
> Thanks!
> Matt
>

Need to audit structure change

How to audit changes made by SQL users on table
structure. Some changes include data type, column name,
and number of columns.
Thanks for suggestion.Look up sp_trace_create in the books online - one of the security audit
event types will allow you to capture all DDL which should get everything
you're looking for here. You can also use sql profiler to build the trace
for you and then script it out if you don't want to use the GUI all the
time...
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Fuller" <anonymous@.discussions.microsoft.com> wrote in message
news:020801c3ba8a$69ee1650$a101280a@.phx.gbl...

quote:

---

> How to audit changes made by SQL users on table
> structure. Some changes include data type, column name,
> and number of columns.
> Thanks for suggestion.

---

Need table of words/glossary/etc.

Hi!

I have a client who wants to have a function to have a system create a validation system for users who register for a system. It would email them a registration code, which they want to be two or three random words strewn together.

So, I'm looking for a big table of words - random, glossary terms, etc. Does anyone have anything like this? (A flat file that I can import would be fine.)

Thanks!I have a dictionary of words in an access db,... by why would you do this, why not generate some random sequence based on something like the data/time they subscribed??|||I have a dictionary of words in an access db,... by why would you do this, why not generate some random sequence based on something like the data/time they subscribed??

Because that would be logical and clients don't operate on logic :)

(They're afraid that random words would be too difficult for the users of the system, most of which are senior citizens.)|||A smart person would suggest sending them a validation link in the email that they can click and autovalidates... but there you go. ;)

So do you want this db? I think it is about 4 megs but I can't be sure...|||This is completely out of my hands. I've made my arguments and I've been shot down. Other people have offered alternatives, they've shot them down too. So, this is what I have to go with.

If you could provide the table, that'd be great.

Thanks!|||how do you want it? http download from somewhere? I can upload it in the next few hours (once I get home).|||Either post it somewhere or email it to rflagg@.gmail.com

Thanks!|||should be in your gmail|||Thanks! That table is great (and very extensive). Much appreciated!|||...sounds like a "lazy hacker" looking for a quick and dirty dictionary attack...maybe wrong though ;)|||And I just helped him,.. great,... ah well, if that is the case then people should protect themselves against such attacks anyway...|||I'm in a similar situation. Could you possible send me a copy of the db ?

[My Email] (laasunde@.online.no)

Thank you.|||Any way you can prove to me that it's not for dictionary attacks?|||Don't know how I could prove that over the Internet.

Need the db for an assignment at uni..|||I promise you that's not what it's for. I'm a professional, I do ASP.NET and SQL development for a living as full-time work, as contracts on the side, and I maintain two websites that I wrote myself for two non-profit animal rescue groups dedicated to the saving and placement of homeless and stray animals. One of which is Animal Allies (http://www.animalallies.com) which services the Washington D.C. metro area (where I live). If you'd like more evidence of this, you can email me at tarkon@.animalallies.com and I'll respond.

Besides that, there's very little I can do to assure you this is for honest and legitimate purposes other than my personal guarantee (and the fact that I don't even know what a dictionary attack is).

And lastly, it's been my experience most hackers are Linux people and not SQL Server 2000 developers.|||heheh no worries Tarkon, I wasn't that concerned to be honest,... just messing with your head. ;)sql

Need stored proc to make aliases to users...

I need a stored proc to make all aliases to users, then assign these newly created users to the same db's (which the aliases were assigned to).

Are there any other ways, or just to write a stored proc?

ANY suggestions accepted, just pls help, i need this for yesterday, and have not much experience in this :(Have a look at SP_ADDALIAS under BOL.

HTH|||Originally posted by Satya
Have a look at SP_ADDALIAS under BOL.

HTH

I mean I want to change the aliases to users, not to add an alias to a user.
(eg. you have a db, and there are 50 users and 10 aliases. You don't want to use aliases. So you have to make users from them.)

I need help in this, sorry is anybody misunderstood :)

So I have to drop all aliases, and create users with the same names, and assign them to the correct db-s.

But this have to work automatically...any scripts? commands?...etc

Need some feedback. What do you think about storing my data like

Hi,
I need to store a hierarchy in my database (a tree)
I have 3 types of data: Cases, Groups and Users. I can have any
combination of them.
Check the following link ('Groups', 'Cases', 'Users' and 'Tree' will be
tables in my database):
http://www.lemforever.com/temp/tree.jpg
What do you guys think? Do you think is a good solution? I still want to
have a relational database
and I don't want to violate any integrity rules.
Thanks a lot.I just realized that I could have used my previous post for asking that.
Sorry about that.
If you have any feedback please use either one.
Thanks

Need Reporing Server Options in VS 2005

I installed VS 2005 for one of our better users so he can start using VS 2005 for Report Designer. Because I'm a programmer, I installed Reporting Services with SQL 2005 so I automatically see the option in my VS 2005 to create a new Reporting Services project but he doesn't. What do I need to install to give him the Reporting Services Project options in VS 2005 ?

You need to install the client tools of SQL Server 2005, particularly the Business Intelligence Development Studio.

-- Robert

need programmer to build a DTS...

I need to get a dts package built in sql 2000. Basic tasks are:
* Users login to site and upload excel files to their own directory
* Need to validate the data within the excel files
* Then import that data into an existing database
* If a record already exists in db, then update it with new data
* original files to be zipped and moved to another folder after import
* exceptions logged or emailed
I haven't built a dts before and am looking to hire someone to build it
out. Usually go to elance but thought I could get some direction here...Hi
Read some info
www.sqldts.com
<dan@.globenettravel.com.au> wrote in message
news:1152108021.520182.221220@.a14g2000cwb.googlegroups.com...
>I need to get a dts package built in sql 2000. Basic tasks are:
> * Users login to site and upload excel files to their own directory
> * Need to validate the data within the excel files
> * Then import that data into an existing database
> * If a record already exists in db, then update it with new data
> * original files to be zipped and moved to another folder after import
> * exceptions logged or emailed
> I haven't built a dts before and am looking to hire someone to build it
> out. Usually go to elance but thought I could get some direction here...
>

Need interface for displaying condition of subscriber DB to users

Does anyone know how to create something like this ...
I have transactional replication set up to maintain copies of several
production databases (located in regional offices) in our corporate office.
The users in the corporate office use the copies for reporting.
Transactional replication is set to push every 30 mins, but with WAN
connections sometimes being down, that might not always be the case. I want
to provide the corporate office users with a simple display of how current
the copies are. For example:
"The Sydney reporting database was last synchronized with the live database
22 mins ago.
The Melbourne reporting database was last synchronized with the live
database 1 day 2 hours 15 mins ago."
etc
Ideally this would take the form of a web page. Any SQL scripts out there
that can give me this data?
on your publisher issue the following in your master database
sp_serveroption 'SubscriberServerName','data access','true'
and then in your distribution database run the following
select time, entry_time from
SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
msrepl_transactions
where transaction_timestamp=xact_seqno
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:uzgQp%23huEHA.2096@.tk2msftngp13.phx.gbl...
> Does anyone know how to create something like this ...
> I have transactional replication set up to maintain copies of several
> production databases (located in regional offices) in our corporate
office.
> The users in the corporate office use the copies for reporting.
> Transactional replication is set to push every 30 mins, but with WAN
> connections sometimes being down, that might not always be the case. I
want
> to provide the corporate office users with a simple display of how current
> the copies are. For example:
> "The Sydney reporting database was last synchronized with the live
database
> 22 mins ago.
> The Melbourne reporting database was last synchronized with the live
> database 1 day 2 hours 15 mins ago."
> etc
> Ideally this would take the form of a web page. Any SQL scripts out there
> that can give me this data?
>
|||Thanks, that works.
Is there any way to get this information from the subscriber? Our
distribution databases are on the same server as the publishers, and
therefore subject to the same problem that this solution is alerting people
to - that the WAN link is sometimes down.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u3eLn8iuEHA.2016@.TK2MSFTNGP15.phx.gbl...
> on your publisher issue the following in your master database
> sp_serveroption 'SubscriberServerName','data access','true'
> and then in your distribution database run the following
> select time, entry_time from
> SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
> msrepl_transactions
> where transaction_timestamp=xact_seqno
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:uzgQp%23huEHA.2096@.tk2msftngp13.phx.gbl...
> office.
> want
> database
>
|||This proc is run on your publisher and queries your subscriber. You could
also run it on your subscriber and query the publisher like this
On your subscriber run this:
sp_serveroption 'PublisherServerName','data access','true'
select time, entry_time from
MSreplication_subscriptions,
PublisherServerName.distribution.dbo.msrepl_transa ctions
where transaction_timestamp=xact_seqno
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23w96THnuEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Thanks, that works.
> Is there any way to get this information from the subscriber? Our
> distribution databases are on the same server as the publishers, and
> therefore subject to the same problem that this solution is alerting
people[vbcol=seagreen]
> to - that the WAN link is sometimes down.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u3eLn8iuEHA.2016@.TK2MSFTNGP15.phx.gbl...
SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,[vbcol=seagreen]
there
>
|||OK thats good, I was just wondering if there was a way of getting the
information from the subcriber only - in the event that the distributor or
publisher were unreachable.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ulXfReouEHA.444@.TK2MSFTNGP10.phx.gbl...
> This proc is run on your publisher and queries your subscriber. You could
> also run it on your subscriber and query the publisher like this
> On your subscriber run this:
> sp_serveroption 'PublisherServerName','data access','true'
> select time, entry_time from
> MSreplication_subscriptions,
> PublisherServerName.distribution.dbo.msrepl_transa ctions
> where transaction_timestamp=xact_seqno
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:%23w96THnuEHA.2172@.TK2MSFTNGP14.phx.gbl...
> people
> SubScriberServerName.SubscriberDatabaseName.dbo.MS replication_subscriptions,
> there
>