Known fields are not returned by using a stored procedure

Hi,
I am trying to use a stored procedure to get data, but I can't get it right.
So far I have done this:
1) In the "Edit selected dataset" dialogue in the query tab I have entered
the name of the stored procedure.
2) In the Parameter tab I have entered the parameters for the stored
procedure, e.g. "@.UserId" etc.
3) I have added report parameters to the report and related them to the sql
parameters by selecting them in the parameter tab of the "Edit selected
dataset" dialogue.
For report parameters that should be null, I have checked "allow null" and
set default parameter to "None".
4) In the field tab of the "Edit selected dataset" I have entered field
names of fields, that I know that this stored procedure will return.
When I try to preview the report, I get an error message saying that there
are no fields corresponding to the field names I have entered. I have tried
query analyzer with the same parameters, and I know which fiels should be
there.
What am I doing wrong?
Thanks for any help.
DorteGoing against SQL Server I have yet seen the need to enter the fields by
hand (although it should have worked). What back end are you going against?
Have you tried clicking on the refresh field list button (it is the button
on the right of the ... that looks like the refresh button for IE)? Do you
get any data back when you execute it in the data tab. RS has to run the
stored procedure to be able to get the list. If you have a parameter then
when you do this (execute it from the data tab) it should prompt you for a
value.
Bruce L-C
"Dorte" <Dorte@.discussions.microsoft.com> wrote in message
news:ED389036-E3CC-4DF5-9BBB-17C133BC7A89@.microsoft.com...
> Hi,
> I am trying to use a stored procedure to get data, but I can't get it
right.
> So far I have done this:
> 1) In the "Edit selected dataset" dialogue in the query tab I have entered
> the name of the stored procedure.
> 2) In the Parameter tab I have entered the parameters for the stored
> procedure, e.g. "@.UserId" etc.
> 3) I have added report parameters to the report and related them to the
sql
> parameters by selecting them in the parameter tab of the "Edit selected
> dataset" dialogue.
> For report parameters that should be null, I have checked "allow null" and
> set default parameter to "None".
> 4) In the field tab of the "Edit selected dataset" I have entered field
> names of fields, that I know that this stored procedure will return.
>
> When I try to preview the report, I get an error message saying that there
> are no fields corresponding to the field names I have entered. I have
tried
> query analyzer with the same parameters, and I know which fiels should be
> there.
> What am I doing wrong?
> Thanks for any help.
> Dorte|||Now it works!
I'm using SQL server 2000, and I did allready enter the fields by hand, but
apparently that wasn't enough! But your advice to push the refresh button did
the
trick!!
Thanks a lot!
Dorte
"Bruce Loehle-Conger" wrote:
> Going against SQL Server I have yet seen the need to enter the fields by
> hand (although it should have worked). What back end are you going against?
> Have you tried clicking on the refresh field list button (it is the button
> on the right of the ... that looks like the refresh button for IE)? Do you
> get any data back when you execute it in the data tab. RS has to run the
> stored procedure to be able to get the list. If you have a parameter then
> when you do this (execute it from the data tab) it should prompt you for a
> value.
> Bruce L-C
> "Dorte" <Dorte@.discussions.microsoft.com> wrote in message
> news:ED389036-E3CC-4DF5-9BBB-17C133BC7A89@.microsoft.com...
> > Hi,
> > I am trying to use a stored procedure to get data, but I can't get it
> right.
> > So far I have done this:
> >
> > 1) In the "Edit selected dataset" dialogue in the query tab I have entered
> > the name of the stored procedure.
> >
> > 2) In the Parameter tab I have entered the parameters for the stored
> > procedure, e.g. "@.UserId" etc.
> >
> > 3) I have added report parameters to the report and related them to the
> sql
> > parameters by selecting them in the parameter tab of the "Edit selected
> > dataset" dialogue.
> > For report parameters that should be null, I have checked "allow null" and
> > set default parameter to "None".
> >
> > 4) In the field tab of the "Edit selected dataset" I have entered field
> > names of fields, that I know that this stored procedure will return.
> >
> >
> > When I try to preview the report, I get an error message saying that there
> > are no fields corresponding to the field names I have entered. I have
> tried
> > query analyzer with the same parameters, and I know which fiels should be
> > there.
> >
> > What am I doing wrong?
> >
> > Thanks for any help.
> >
> > Dorte
>
>sql

Killing xp_cmdshell

Hi.
I have run xp_cmdshell from within a stored procedure, and it has hung. Is
there any way to kill the process, other than rebooting SQL Server?
Cheers
NeilIf you do not have luck using "kill spid", go to the server and end the tas
k
using "task manager".
AMB
"NeilDJones" wrote:

> Hi.
> I have run xp_cmdshell from within a stored procedure, and it has hung. Is
> there any way to kill the process, other than rebooting SQL Server?
> Cheers
> Neil

Killing an active connection

Is there any stored procedure to kill an active database connection?
Thanks
use sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.
|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.
|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegr oups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>

Killing an active connection

Is there any stored procedure to kill an active database connection?
Thanksuse sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegroups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>sql

Killing an active connection

Is there any stored procedure to kill an active database connection?
Thanksuse sp_who to find out what sessions are running.
use kill <spid> to kill the session you want.|||Sorry forgot to mention - if it is a long running transaction, you need
to be aware. As if you kill that, it may even take LONGER to finish
off and you cannot kill a rollback transaction.|||Thanks so much!
"MSLam" <MelodySLam@.googlemail.com> escreveu na mensagem
news:1143580695.100118.311230@.e56g2000cwe.googlegroups.com...
> Sorry forgot to mention - if it is a long running transaction, you need
> to be aware. As if you kill that, it may even take LONGER to finish
> off and you cannot kill a rollback transaction.
>

killed/rollback stuck on object_name(99)

Hello:
I have a process that's been stuck for two days.. It's a stored procedure
that runs as part of a scheduled SqlAgent job. I tried to kill the process
which put it into a rollback. Kill with statusonly returns:
SPID 52: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
I ran dbcc page for the resource that is listed in the wait type
(PAGEIOLATCH_UP)
and it points to Obj_id 99. Running "select object_name(99)" returns the
object name "Allocation".
Does anyone know what this means and how to allow the rollback to complete?
This spid blocks other processess that try to run in the affected database.
Even Enterprise Manager is blocked. Can't refresh table list or procedure
list in EM. Current activity times out. I can use sp_who2 to see active
processes.hi,
Right, try again using this:
KILL <your_process> WITH STATUSONLY
Because of your process has been running a long time the rollback will take
a lot of time (not the same, of course, but a lof anyway)
Rollback is undoing changes and transactions commited
Current location: Alicante (ES)
"tthrone" wrote:

> Hello:
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the proces
s
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
> Does anyone know what this means and how to allow the rollback to complete
?
> This spid blocks other processess that try to run in the affected database
.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>|||Hi Enric,
I did that. It returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
>
It's been returning the same thing for two days. It doesn't appear to be
making any progress on the rollback. The original process should have done
123,000 row inserts on a previously empty table. I can't imagine 123k rows
should take 2 days to rollback. I think it's totally stuck and idle.
"Enric" wrote:
> hi,
> Right, try again using this:
> KILL <your_process> WITH STATUSONLY
> Because of your process has been running a long time the rollback will tak
e
> a lot of time (not the same, of course, but a lof anyway)
> Rollback is undoing changes and transactions commited
> --
> Current location: Alicante (ES)
>
> "tthrone" wrote:
>|||First, try to find out what application and T-SQL statement caused this
situation so perhaps it won't repeat:
DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
client application owning this SPID.
SP_LOCK (spid) will list information about what specific objects the SPID
currently has locked and what type of lock (table, page, etc.).
Next, try to diagnose what is going on with your server hard disks, memory,
etc. that may have caused this unusual cirsumstance. If the server is
running critically low on disk space, this can cause problems when
attempting rollback a large transaction. Also, go into the windows
management console and review the event logs for possible evidence.
This article describes how get more detailed information about the current
status of the SPID:
http://support.microsoft.com/defaul...kb;en-us;171224
For example, the Process Status Structure (PSS) has the following values:
0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
0x2000 -- Process is being killed
0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
0x400 -- Process has received an ATTENTION signal, and has responded by
raising an internal exception
0x100 -- Process in the middle of a single statement transaction
0x80 -- Process is involved in multi-database transaction
0x8 -- Process is currently executing a trigger
0x2 -- Process has received KILL command
0x1 -- Process has received an ATTENTION signal
This article describes how to identify and troubleshoot an orphaned
connection:
http://support.microsoft.com/kb/137983/EN-US/
If the SPID can't be killed, then:
1. stop the SQL Server service (no need to reboot)
2. using Windows Explorer, move the data and transaction log file(s) to
another location
3. re-start the service
4. restore the database from the most recent backup
"tthrone" <tthrone@.discussions.microsoft.com> wrote in message
news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
> Hello:
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the
> process
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
> Does anyone know what this means and how to allow the rollback to
> complete?
> This spid blocks other processess that try to run in the affected
> database.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>|||Thanks JT. I know some answers to questions/issues you listed. I know the
transation that was in-flight, but I don't know why it stuck. Still can't
figure out why it remains stuck, but I found something interesting in the
process of doing some of what you suggested.
For one, I see this spid blocks some of my attempts to use sysobjects. I
mentioned that I can't refresh procedures or tables in EM on this database.
I think that's why. I have it narrowed down to one (maybe a few) affected
tables. I can query sysobjects so long as I don't try to read certain rows.
Not sure how that happened!
I think I'm going to have to try your suggestion about stopping the service
and restoring.
Thanks for the help.
"JT" wrote:

> First, try to find out what application and T-SQL statement caused this
> situation so perhaps it won't repeat:
> DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
> client application owning this SPID.
> SP_LOCK (spid) will list information about what specific objects the SPID
> currently has locked and what type of lock (table, page, etc.).
> Next, try to diagnose what is going on with your server hard disks, memory
,
> etc. that may have caused this unusual cirsumstance. If the server is
> running critically low on disk space, this can cause problems when
> attempting rollback a large transaction. Also, go into the windows
> management console and review the event logs for possible evidence.
> This article describes how get more detailed information about the current
> status of the SPID:
> http://support.microsoft.com/defaul...kb;en-us;171224
> For example, the Process Status Structure (PSS) has the following values:
> 0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
> 0x2000 -- Process is being killed
> 0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
> 0x400 -- Process has received an ATTENTION signal, and has responded by
> raising an internal exception
> 0x100 -- Process in the middle of a single statement transaction
> 0x80 -- Process is involved in multi-database transaction
> 0x8 -- Process is currently executing a trigger
> 0x2 -- Process has received KILL command
> 0x1 -- Process has received an ATTENTION signal
> This article describes how to identify and troubleshoot an orphaned
> connection:
> http://support.microsoft.com/kb/137983/EN-US/
> If the SPID can't be killed, then:
> 1. stop the SQL Server service (no need to reboot)
> 2. using Windows Explorer, move the data and transaction log file(s) to
> another location
> 3. re-start the service
> 4. restore the database from the most recent backup
>
> "tthrone" <tthrone@.discussions.microsoft.com> wrote in message
> news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
>
>|||When querying sysobjects (or any other blocked table), you can get around
the locks by changing the isolation level to read uncommitted data. However,
this should not be used in a production system except perhaps in some
reporting situations.
set transaction isolation level read uncommitted
select * from sysobjects
"tthrone" <tthrone@.discussions.microsoft.com> wrote in message
news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
> Thanks JT. I know some answers to questions/issues you listed. I know
> the
> transation that was in-flight, but I don't know why it stuck. Still can't
> figure out why it remains stuck, but I found something interesting in the
> process of doing some of what you suggested.
> For one, I see this spid blocks some of my attempts to use sysobjects. I
> mentioned that I can't refresh procedures or tables in EM on this
> database.
> I think that's why. I have it narrowed down to one (maybe a few) affected
> tables. I can query sysobjects so long as I don't try to read certain
> rows.
> Not sure how that happened!
> I think I'm going to have to try your suggestion about stopping the
> service
> and restoring.
> Thanks for the help.
> "JT" wrote:
>|||I normally do set the transaction isolation level to read uncommitted. I di
d
that in this case as well.
I even tried using the hint "with(readuncommitted)" but it was still blocked
by the stuck spid when I tried to return the sysobject rows of tables that
were affected.
Our DBA is going to bounce the service later today. I'm hoping it will
clear up after the restart.
"JT" wrote:

> When querying sysobjects (or any other blocked table), you can get around
> the locks by changing the isolation level to read uncommitted data. Howeve
r,
> this should not be used in a production system except perhaps in some
> reporting situations.
> set transaction isolation level read uncommitted
> select * from sysobjects
> "tthrone" <tthrone@.discussions.microsoft.com> wrote in message
> news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
>
>

Kill Process

Hi Peepz,

Im just a newbie using SQL Server anyway i noticed in my present company that most of the process/Stored Procedure that are being executed or connection that was being made by the application is not being terminated or disconnected. they use very large IO and CPU resources making the server slow or sometimes hang.(well that was my diagnostics). i try to kill them one by one but it seems endless process is redundant. to cut my problem short im thinking of having an application that would automatically kill all this unwanted process but ofcourse i should specify the parameter which process to kill. is this possible? i saw the KILL statement in the online books but it seems uncomplete with what i wanted to accomplish.

Thanks allHi Peepz,
Uh, hi, I think...

Im just a newbie using SQL Server anyway i noticed in my present company that most of the process/Stored Procedure that are being executed or connection that was being made by the application is not being terminated or disconnected. they use very large IO and CPU resources making the server slow or sometimes hang.(well that was my diagnostics). i try to kill them one by one but it seems endless process is redundant. to cut my problem short im thinking of having an application that would automatically kill all this unwanted process but ofcourse i should specify the parameter which process to kill. is this possible? i saw the KILL statement in the online books but it seems uncomplete with what i wanted to accomplish.

Thanks all

Sounds like an application using connection pooling (like ASP.NET, WebLogic or any of a myriad of others). I'll use WebLogic as an example: it grabs a set minimum number of connections when the app starts. It periodically tests the connections to verify that they are still "working". It then hands the connection off to a process to complete a database task. When the task is complete, the connection is returned to the pool to be "loaned" out to another thread/process.

The advantage here is that connection pooling cuts down a lot of overhead associated with building and tearing down connections. The disadvantage is that it can be really hard (from a dba perspective) to tell which SPID is really consuming a lot of resources at the current point in time (since the statistics that are displayed in EM, sp_who and sp_who2 are cumulative from the time the connection is established).

Instead of looking at the processes, you need to run trace files and examine your locked processes/objects views to determine if there are specific SPIDs that are struggling.

Regards...er...regardz,

hmscott|||LOL.. Yea. honestly this is my first time to handle a large set-up for database management. i always see a user still connected and using large amount of resources even that the person is out of the office for more than a day. i also see users that have logged out but their username is still present. by the way how would i create a trace file?

Thanks and Regards

Keezeg|||LOL.. Yea. honestly this is my first time to handle a large set-up for database management.

Welcome! This is a great place to get answers. Read the sticky posts at the top if you haven't had a chance yet, then head over to the lounge to learn how to make the perfect Margarita (I find that DB problems come into better focus after about the third or fourth!). Pick up any reading materials you can about administration; Kalen Delaney's Inside SQL Server is a good one and SAM's SQL Server 2000 DBA Survival Guide is my favorite.

i always see a user still connected and using large amount of resources even that the person is out of the office for more than a day.

This sounds a bit different than what I was trying to describe. In general, application servers that use connection pooling would use a login that is not tied to a particular user. Is it possible to drop by the user's workstation and figure out what apps are running?

i also see users that have logged out but their username is still present.

Ditto the comment above.

by the way how would i create a trace file?

Use SQL Profiler. You can read about it in SQL Books on Line (be sure to get the updated version from www.microsoft.com/sql). Basically, you start, SQL Profiler, pick the database you want to trace, select the statements you want to log and hit start (or is it run?). Then either save the trace as a file or into a db table (my favorite; but preferably on a non-production server and in a new database). Then you can query the trace file to see what each SPID is doing (and see which queries are taking too long).

Welcome aboard!
:beer:

Regards,

13AP3|||Welcome! This is a great place to get answers. Read the sticky posts at the top if you haven't had a chance yet, then head over to the lounge to learn how to make the perfect Margarita (I find that DB problems come into better focus after about the third or fourth!). Pick up any reading materials you can about administration; Kalen Delaney's Inside SQL Server is a good one and SAM's SQL Server 2000 DBA Survival Guide is my favorite.

Thanks for the references.. i will read the sticky posts.. hahaha i think a couple of shots could focus me better to the problem. like having a date with a not so beautiful chick... having a few drinks she would be looking super hot. hehehehe

This sounds a bit different than what I was trying to describe. In general, application servers that use connection pooling would use a login that is not tied to a particular user. Is it possible to drop by the user's workstation and figure out what apps are running?

basically the applications running was created in VB, its a Monitoring System more on reports generation, data extraction and record entry. i think the application is not disconnecting properly on the database thats why im asking the Application team to check how or what way the programs disconnects when the user exits. there is quite many users of this application and they all do the same thing with the program. heavy report generation.

Use SQL Profiler. You can read about it in SQL Books on Line (be sure to get the updated version from www.microsoft.com/sql). Basically, you start, SQL Profiler, pick the database you want to trace, select the statements you want to log and hit start (or is it run?). Then either save the trace as a file or into a db table (my favorite; but preferably on a non-production server and in a new database). Then you can query the trace file to see what each SPID is doing (and see which queries are taking too long).

i will be going to do this. atleast i can manipulate the data. i can easily query out the data that i wanted to see from table.

Kill Process

Hi Peepz,

Im just a newbie using SQL Server anyway i noticed in my present company that most of the process/Stored Procedure that are being executed or connection that was being made by the application is not being terminated or disconnected. they use very large IO and CPU resources making the server slow or sometimes hang.(well that was my diagnostics). i try to kill them one by one but it seems endless process is redundant. to cut my problem short im thinking of having an application that would automatically kill all this unwanted process but ofcourse i should specify the parameter which process to kill. is this possible? i saw the KILL statement in the online books but it seems uncomplete with what i wanted to accomplish.

Thanks allThe syntax for the "KILL" process is quite simple. ex. "Kill 50", were 50 is the process id. What you are talking about doing should be relatively easy if you know what your parameters are. I always suggest instead of creating a work around for a problem, maybe you should solve the problem.

Good luck
I hope this helped|||The syntax for the "KILL" process is quite simple. ex. "Kill 50", were 50 is the process id. What you are talking about doing should be relatively easy if you know what your parameters are. I always suggest instead of creating a work around for a problem, maybe you should solve the problem.

i know the syntax but the problem is not that easy... thanks.

Kill connection

Hello,
I do maintenance on the Back end
and have like 10 - 20 connections open...specialized scrips i run and they
dont need to be stored proc's

is there a way to kill the connection when the script is finished...from my
client side....
not just disconnect...cause server still has the pool of the connection...i
want to kill the pool'd connection also

thanks
Dave PDaveP (analizer1@.yahoo.com) writes:

Quote:

Originally Posted by

I do maintenance on the Back end
and have like 10 - 20 connections open...specialized scrips i run and they
dont need to be stored proc's
>
is there a way to kill the connection when the script is finished...from
my client side.... not just disconnect...cause server still has the
pool of the connection...i want to kill the pool'd connection also

I'm not sure that I follow. You have a maintenance job that runs from
a client that opens multiple connections?

When the client exits, all pooled connections will go away, since the
pools lives in the process space of the client, not of SQL Server.

--
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

Kick off Stored Procedure to run Nightly?

I have a stored procedure that updates a table. I want that table to be updated nightly. I looked into the DTS package but it seems a bit difficult. Is there some other schedule in Enterprise that I can kick off my stored proc without having to build a DTS package?

In DTS, they ask for all of these symbols and want the query, but my code is already in stored proc form.

Help?What you need to do is use SQL Agent. You can schedule the job to run nightly at your prferred time. When you create a new SQL Agent job, you then ad a job step. In this job step you specify the SQL command you want to run. I'm assuming you are using SQL Server 2000 since you refer to DTS. Using SQL Server Enterprise manager, expand the SQL Server you are working with, then expand the 'Managment' tree, then expand 'SQL Agent' tree and right click on Jobs and choose New Job... The tabs here should be self explanatory.|||Ok I see where the job steps are. In the command window, I can type in my proc name sp_DailyOrders and it will know how to kick it off?|||In the step name tab, just give the step a name like 'Execute procedure', make sure the database is the correct database where your procedure lives, and type in exec sp_your_proc_name in the command section. Then move to the Scheduile tab and click the button to add a new schedule and choose the frequency etc that you want the job to tun. If you have operators setup on your server, then you can use the notification tab to have the job email you when it completes or fails.|||Thanks soooooooo much! You are awesome!

Josql

keyword search in dynamic stored procedure

Hello Gang ..
I have a dynamic stored procedure which needs to be able to process as
part of it's search a form field which may contain several words
seperated by a space. ie: earth diamonds brazil ocean
My dynamic stored procedure works great, right now the parameter
containing the form input is a treating the entire entry as a string
without breaking it up. I wonder if anyone here can help .. here is
what my sp looks like right now:
CREATE PROCEDURE sp_JobSearch
(
@.keyWords varchar(100),
@.companyName varchar(50),
@.jobType varchar(10),
@.jobCategory varchar(10),
@.country varchar(10),
@.state varchar(10),
@.province varchar(50),
@.city varchar(50),
@.order varchar(20)
)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare
@.sql varchar(8000)
Set @.sql = 'Select' + char(10) + 'a.jobId,' + char(10) +
'a.keyWords,' + char(10) + 'a.jobShortDescription,' + char(10) +
'a.jobTypeId,' + char(10) + 'a.jobCategoryId,'
+ char(10) + 'a.employerId,' + char(10) +
'a.createDate,' + char(10) + 'b.city,' + char(10) + 'b.stateId,' +
char(10) + 'b.province,'
+ char(10) + 'b.countryId,' + char(10) +
'c.stateAbreviation,' + char(10) + 'd.countryAbreviation,' + char(10)
+ 'e.employerName,'
+ char(10) + 'f.jobType,' + char(10) +
'g.jobCategory,' + char(10) + 'a.jobDuties,' + char(10) +
'a.jobBenefits,' + char(10) + 'a.jobFullDescription,'
+ char(10) + 'a.jobSalaryLow,' + char(10) +
'a.jobSalaryHigh,' + char(10) + 'h.educationLevel' + char(10) +
'from tblJobs a' + char(10) +
'LEFT OUTER JOIN tblEmployers e ON
a.employerId = e.employerId' + char(10) +
'LEFT OUTER JOIN
tblEmployerLocations b ON a.jobLocationId = b.locationId' + char(10)
+
'LEFT OUTER JOIN tblCountries d ON
b.countryId = d.countryId' + char(10) +
'LEFT OUTER JOIN tblStates c ON
b.stateId = c.stateId' + char(10) +
'LEFT OUTER JOIN tblJobTypes f ON
a.jobTypeId = f.jobTypeId' + char(10) +
'LEFT OUTER JOIN tblJobCategories
g ON a.jobCategoryId = g.jobCategoryId' + char(10) +
'LEFT OUTER JOIN
tblEducationLevels h ON a.jobEducationLevelId = h.educationLevelId' +
char(10) +
'where
getdate() between a.jobStartDate
and a.jobEndDate AND
a.active = 1 AND a.deleted = 0 AND
a.statusId = 2 AND
e.active = 1 AND e.deleted = 0 AND
e.employerStatusId = 2 ' + char(10)
if @.keyWords = ''
begin
set @.sql = @.sql + 'AND
a.keyWords like ' + char(10) + '''%''' + char(10)
end
else
begin
set @.sql = @.sql + 'AND
a.keyWords like' + char(10) + '''' + '%' + @.keyWords + '%' + '''' +
char(10)
set @.sql = @.sql + 'OR
a.jobDuties like' + char(10) + '''' + '%' + @.keyWords + '%' + '''' +
char(10)
set @.sql = @.sql + 'OR
a.jobBenefits like' + char(10) + '''' + '%' + @.keyWords + '%' + ''''
+ char(10)
set @.sql = @.sql + 'OR
a.jobFullDescription like' + char(10) + '''' + '%' + @.keyWords + '%'
+ '''' + char(10)
set @.sql = @.sql + 'OR
g.jobCategory like' + char(10) + '''' + '%' + @.keyWords + '%' + ''''
+ char(10)
set @.sql = @.sql + 'OR
h.educationLevel like' + char(10) + '''' + '%' + @.keyWords + '%' +
'''' + char(10)
end
if @.companyName = ''
begin
set @.sql = @.sql + 'AND
e.employerName like ' + char(10) + '''%''' + char(10)
end
else
begin
set @.sql = @.sql + 'AND
e.employerName like' + char(10) + '''' + '%' + @.companyName + '%' +
'''' + char(10)
end
if @.jobType != ''
begin
set @.sql = @.sql + 'AND
a.jobTypeId = ' + char(10) + @.jobType + char(10)
end
else
begin
set @.sql = @.sql + 'AND
a.jobTypeId like ' + char(10) + '''%''' + char(10)
end
if @.jobCategory != ''
begin
set @.sql = @.sql + 'AND
a.jobCategoryId = ' + char(10) + @.jobCategory + char(10)
end
else
begin
set @.sql = @.sql + 'AND
a.jobCategoryId like ' + char(10) + '''%''' + char(10)
end
if @.country = ''
begin
set @.sql = @.sql + 'AND
b.countryId like ' + char(10) + '''%''' + char(10)
end
else
begin
set @.sql = @.sql + 'AND
b.countryId = ' + char(10) + @.country + char(10)
end
if @.state != '' AND @.province = ''
begin
set @.sql = @.sql + 'AND
b.stateId = ' + char(10) + @.state + char(10)
end
if @.province != '' AND @.state =
''
begin
set @.sql = @.sql + 'AND
b.province like ' + char(10) + '''' + '%' + @.province + '%' + '''' +
char(10)
end
if @.city != ''
begin
set @.sql = @.sql + 'AND
b.city like ' + char(10) + '''' + '%' + @.city + '%' + '''' +
char(10)
end
if @.order = 'createDate'
begin
set @.sql = @.sql +
'order by a.createDate desc'
end
else if @.order = 'relevance'
begin
set @.sql = @.sql +
'order by a.keyWords'
end
exec (@.sql)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GOOn Thu, 19 May 2005 01:02:09 -0500, pagino wrote:

>Hello Gang ..
>I have a dynamic stored procedure which needs to be able to process as
>part of it's search a form field which may contain several words
>seperated by a space. ie: earth diamonds brazil ocean
>My dynamic stored procedure works great, right now the parameter
>containing the form input is a treating the entire entry as a string
>without breaking it up. I wonder if anyone here can help .. here is
>what my sp looks like right now:
(snip)
Hi pagino,
If there's a question in your post, then I couldn't find it. I did read
that it works great right now - which is A Good Thing, as this code
looks terribly difficult to maintain...
If you're looking for generic advice, then I for the most part agree
with Celko's comments, though I would have chosen nicer words to soften
up the news.
You might find the following useful:
http://www.sommarskog.se/dyn-search.html
And maybe this as well:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo ..
After much research I opted away from the dynamic stored proc ..
But I'll have a look at your suggestions for sure ..
Thanks for taking the time to help constructively ..
Message posted via http://www.webservertalk.com|||Hugo ..
I have a question for you ..
How would you have approached this search proc, given that there is no
flexibility in terms of redoing the db schema ?
Message posted via http://www.webservertalk.com|||>> How would you have approached this search proc, given that there is no fl
exibility in terms of redoing the db schema ? <<
First of all, there are tools for doing text searching that have more
power and run orders of magnitude faster than SQL extensions. Text
searching in SQL is like putting feather on a fish.
The schema IS the real problem. Do you really have locations where you
do know the name of the country that they are in? Probably, since you
have all those expensive OUTER joins. Why is country, state, city, etc
not all attributes of location? ISince you are using ISO-3316 country
codes, why use a LIKE for it and not equality? Ditto for lots of other
things here.
What you wanted was more like this:
CREATE TABLE JobKeywords
(job_id INTEGER NOT NULL
REFERENCES Jobs(job_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
keyword VARCHAR(15) NOT NULL,
PRIMARY KEY (job_id, keyword));
CREATE TABLE ClientSearchWords
(client_id INTEGER NOT NULL
REFERENCES Clients(client_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
keyword VARCHAR(15) NOT NULL,
PRIMARY KEY (client_id, keyword));
Now do a standard Relational division to get the candidate jobs for all
clients. You can put that in a VIEW. Use a NULL for matching all
values.
SELECT M.client_id, M.job_id, L.coutnry_name, L.province_name,
L.city_name, J.eduation_level, etc.
FROM KeywordMatches AS M,
Jobs AS J,
Locations AS L,
Clients AS C,
..
WHERE L.location_id = COALESCE (M.location_id, L.location_id)
AND J.eduation_level <= COALESCE (C.eduation_level,
J.eduation_level)
AND ...;
A better way is to use a CASE expression to get a score:
( CASE WHEN J.eduation_level <= C.eduation_level
THEN 5 ELSE 0 END
+ CASE WHEN ..
+ CASE WHEN ..) AS score|||On Fri, 27 May 2005 23:07:41 GMT, Pagino via webservertalk.com wrote:

>Hugo ..
>I have a question for you ..
>How would you have approached this search proc, given that there is no
>flexibility in terms of redoing the db schema ?
Hi Pagino,
I don't really know what "this search proc" is. You haven't posted the
table structure (as CREATE TABLE statements), nor sample data (as INSERT
statements) and expected output and/or a description to explain what the
proc does. You only posted some code that is (due to extensive use of
dynamic SQL, bad formatting and line wrappings due to usenet line length
limitations) impossible to grasp in the limited time I have available
for spending in these groups.
Since I don't know the DB schema, I really can't tell if it's good or
bad. But if it is bad, then I advise you not to accept the "no
flexibility" part. Bad schemas need to be fixed. If it can't be done
now, then it HAS to be scheduled to be done ASAP. When you know that the
foundation of your house is bad, you don't accept it. You make sure that
it's fixed as soon as possible. And until it's fixed, you take whatever
measures are necessary to make sure the floor doesn't collapse - but
those are all temporary measures; the permanent solution is to fix the
foundation. Remember that the tbale design is the foundation of your
database.
Anyway, for search procedures, the usual first step is to tell the
customer what the cost (either in money or in performance, but usually
even in both) would be of implementing all their wishes. That is often
enough to convince them to tone down their wish list. Most people always
want the ability to search the DB on every column, but once it starts to
cost money, they usually have no problem to identify the three or four
search types that will actually ever be used.
The next step is to write a stored procedure for each of the search
types. The last step is to find the indexes that will speed up these
queries to the desired performance without affecting insert, update and
delete performance too much. That final step is usually the hardest :)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks guys for your replies ..
Here is what I have:
tableCountries
tableStates
tableEducationLevels
tableContacts
tableLocations
tableEmployers
tableJobs
The tableJobs will hold an integer telling it which contact, country,
state, location, employer is attributed to the job.
On the job search interface we would display something like this in a form
select:
US Texas - El Paso
US Alaska - Snowtown
So we know what the country, state and city is. The problem comes when
they select ALL locations .. I think that's how it was chosen to be handled
previously. That's why the LIKE operator was used.
The search interface requires for the user to be able to:
NOTE: If nothing is entered or chosen, then everything for that filter is
to be returned.
1. enter keywords
2. enter a company name or several
3. select job type (full time, part time)
4. select job category (managerial, administrative, etc)
5. select location (US Virginia - Fairfax, US California - Sacramento)
Here is the scripted the tables associated with the proc:
----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableCountries]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableCountries]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableEducationLevels]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableEducationLevels]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableEmployerContacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableEmployerContacts]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableEmployerLocations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableEmployerLocations]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableEmployers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableEmployers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableJobCategories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableJobCategories]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableJobPayTerms]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableJobPayTerms]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableJobTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableJobTypes]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableJobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableJobs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableRoles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tableStates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tableStates]
GO
CREATE TABLE [dbo].[tableCountries] (
[countryId] [int] IDENTITY (1, 1) NOT NULL ,
[countryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryAbreviation] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[createDate] [smalldatetime] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableEducationLevels] (
[educationLevelId] [int] IDENTITY (1, 1) NOT NULL ,
[educationLevel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableEmployerContacts] (
[contactId] [int] IDENTITY (1, 1) NOT NULL ,
[employerId] [int] NOT NULL ,
[locationId] [int] NULL ,
[firstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[middleInitial] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[businessPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[otherPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createDate] [smalldatetime] NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableEmployerLocations] (
[locationId] [int] IDENTITY (1, 1) NOT NULL ,
[employerId] [int] NULL ,
[locationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[province] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stateId] [int] NULL ,
[zipCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[countryId] [int] NULL ,
[createDate] [smalldatetime] NOT NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableEmployers] (
[employerId] [int] IDENTITY (1, 1) NOT NULL ,
[employerStatusId] [int] NULL ,
[userId] [int] NULL ,
[firstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[middleInitial] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employerLogo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[businessPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[website] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createDate] [smalldatetime] NOT NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableJobCategories] (
[jobCategoryId] [int] IDENTITY (1, 1) NOT NULL ,
[jobCategory] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobCategoryDescription] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableJobPayTerms] (
[jobPayTermId] [int] IDENTITY (1, 1) NOT NULL ,
[jobPayTerm] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableJobTypes] (
[jobTypeId] [int] IDENTITY (1, 1) NOT NULL ,
[jobType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableJobs] (
[jobId] [int] IDENTITY (1, 1) NOT NULL ,
[employerId] [int] NOT NULL ,
[statusId] [int] NOT NULL ,
[jobTitle] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobTypeId] [int] NULL ,
[jobCategoryId] [int] NULL ,
[jobEducationLevelId] [int] NULL ,
[jobPayTermId] [int] NULL ,
[jobSalaryLow] [money] NULL ,
[jobSalaryHigh] [money] NULL ,
[jobShortDescription] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[jobFullDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobDuties] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobBenefits] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobContactId] [int] NULL ,
[addJobContact] [bit] NOT NULL ,
[jobLocationId] [int] NULL ,
[addJobLocation] [bit] NOT NULL ,
[jobStartDate] [smalldatetime] NOT NULL ,
[jobEndDate] [smalldatetime] NOT NULL ,
[jobComments] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted] [bit] NOT NULL ,
[active] [bit] NOT NULL ,
[createDate] [smalldatetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableRoles] (
[roleId] [int] IDENTITY (1, 1) NOT NULL ,
[roleName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createDate] [smalldatetime] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tableStates] (
[stateId] [int] IDENTITY (1, 1) NOT NULL ,
[stateName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stateAbreviation] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[createDate] [smalldatetime] NOT NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO
---
Message posted via http://www.webservertalk.com|||On Tue, 31 May 2005 23:28:05 GMT, Pagino via webservertalk.com wrote:

>Thanks guys for your replies ..
>Here is what I have:
(snip)
Hi Pagino,
First things first - let's start with the table design. There are many
things wrong. In a completely random order:
1. Why prefix all table names with "table"? What other data structures
do you expect to find in a relational database? The only thing this
achieves, is to make your code harder to read.
2. Why are almost all columns defined as "varchar(50)"? Do you really
expect to get people with up to 50 middle initials? With letters and
symbols in their phone and fax numbers? And do you really think that
you'll never have to store a URL that needs more than 50 characters?
3. Why are almost all columns NULLable? What would be the significance
of storing rows in e.g. tableCountries with countryName and
countryAbbreviation both set to NULL?
4. Why don't you have any PRIMARY KEYs? Or FOREIGN KEYs? Is data
integrity not important in your company?
5. Why the deleted and active columns? Common practice is to include
datetime columns valid_from and valid_thru - or (performansewise better)
keep old data in an audit table, and only the current data in the
regular table.
6. What is the use of the addJobContact and addJobLocation columns?
(This is not a rhetorical question like the above - I really don't
understand what you use these columns for).
In an earlier post, you said that it's not possible to redo the design.
If that is indeed true, then the best advise I can give you is to start
looking for another job. You really don't want to continue working with
a DB such as this. (Personally, I wouldn't even want to be seen dead
with it).

>On the job search interface we would display something like this in a form
>select:
>US Texas - El Paso
>US Alaska - Snowtown
>So we know what the country, state and city is. The problem comes when
>they select ALL locations .. I think that's how it was chosen to be handled
>previously. That's why the LIKE operator was used.
Check http://www.sommarskog.se/dyn-search.html for better alternatives.

>The search interface requires for the user to be able to:
>NOTE: If nothing is entered or chosen, then everything for that filter is
>to be returned.
>1. enter keywords
Check http://www.sommarskog.se/arrays-in-sql.html to find out how to
handle a delimited list in a parameter. The code you wrote won't work if
multiple keywords are entered. If I enterte as keyword "SQL Server,
design", then your query would only return rows where the EXACT TEST
"SQL Server, design" is included in any of the columns keyWords,
jobDuties, jobBenefits, etc.

>2. enter a company name or several
>3. select job type (full time, part time)
>4. select job category (managerial, administrative, etc)
>5. select location (US Virginia - Fairfax, US California - Sacramento)
I suggest that you use a drop-down box for valid job types, categories
and locations. Then simply pass either the ID of the chosen type,
category, location to the stored procedure (or pass NULL if ALL was
selected). Again: see Erland's site for much more details.
All this being said, the best advise is already given by Joe Celko: just
buuy a specialized too for this. It'lll save you lots of time and lots
of troouble, and it'll perform faster than any pure SQL Server based
alternative.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo ..
Thanks for the reply .. LOL !
I don't know why the primary and foreign keys didn't show up in the script,
they do exist, trust me. The names of the tables do not actually start with
table (I agree that would have been silly) and the columns allowing NULLS
.. well that will be changed before the last db revision is done :)
I will carry away with your suggestions however, I think they are good
suggestions and very probably the best I've gotten so far ..
In terms of tools that would do the search, which ones out there would you
recommend ?
Thanks in advanced ..
Message posted via http://www.webservertalk.com|||On Fri, 03 Jun 2005 23:48:00 GMT, Pagino via webservertalk.com wrote:
(snip)
>In terms of tools that would do the search, which ones out there would you
>recommend ?
Hi Pagino,
Since this is not a field I am familiar with, I can't do any good
recommendations on this.
A good p[lace to ask would be the fulltext group for SQL Server
(microsoft.public.sqlserver.fulltext). Maybe the people there know how
to make the fulltext search capabilities of SQL Server do this job, in
which case you don't need to buy other tools. And otherwise, they are
likely to have good recommendations where elses to look.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

keyword search in dynamic stored procedure

Hello Gang ..
I have a dynamic stored procedure which needs to be able to process as part
of it's search a form field which may contain several words seperated by a
space. ie: earth diamonds brazil ocean
My dynamic stored procedure works great, right now the parameter containing
the form input is a treating the entire entry as a string without breaking
it up. I wonder if anyone here can help .. here is what my sp looks like
right now:
CREATE PROCEDURE sp_JobSearch
(
@.keyWords varchar(100),
@.companyName varchar(50),
@.jobType varchar(10),
@.jobCategory varchar(10),
@.country varchar(10),
@.state varchar(10),
@.province varchar(50),
@.city varchar(50),
@.order varchar(20)
)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare
@.sql varchar(8000)
Set @.sql = 'Select' + char(10) + 'a.jobId,' + char(10) + 'a.keyWords,' +
char(10) + 'a.jobShortDescription,' + char(10) + 'a.jobTypeId,' + char(10)
+ 'a.jobCategoryId,'
+ char(10) + 'a.employerId,' + char(10) + 'a.createDate,' + char(10) +
'b.city,' + char(10) + 'b.stateId,' + char(10) + 'b.province,'
+ char(10) + 'b.countryId,' + char(10) + 'c.stateAbreviation,' + char(10) +
'd.countryAbreviation,' + char(10) + 'e.employerName,'
+ char(10) + 'f.jobType,' + char(10) + 'g.jobCategory,' + char(10) +
'a.jobDuties,' + char(10) + 'a.jobBenefits,' + char(10) +
'a.jobFullDescription,'
+ char(10) + 'a.jobSalaryLow,' + char(10) + 'a.jobSalaryHigh,' + char(10) +
'h.educationLevel' + char(10) +
'from tblJobs a' + char(10) +
'LEFT OUTER JOIN tblEmployers e ON a.employerId = e.employerId' + char(10)
+
'LEFT OUTER JOIN tblEmployerLocations b ON a.jobLocationId = b.locationId'
+ char(10) +
'LEFT OUTER JOIN tblCountries d ON b.countryId = d.countryId' + char(10) +
'LEFT OUTER JOIN tblStates c ON b.stateId = c.stateId' + char(10) +
'LEFT OUTER JOIN tblJobTypes f ON a.jobTypeId = f.jobTypeId' + char(10) +
'LEFT OUTER JOIN tblJobCategories g ON a.jobCategoryId = g.jobCategoryId' +
char(10) +
'LEFT OUTER JOIN tblEducationLevels h ON a.jobEducationLevelId =
h.educationLevelId' + char(10) +
'where
getdate() between a.jobStartDate and a.jobEndDate AND
a.active = 1 AND a.deleted = 0 AND a.statusId = 2 AND
e.active = 1 AND e.deleted = 0 AND e.employerStatusId = 2 ' + char(10)
if @.keyWords = ''
begin
set @.sql = @.sql + 'AND a.keyWords like ' + char(10) + '''%''' + char(10)
end
else
begin
set @.sql = @.sql + 'AND a.keyWords like' + char(10) + '''' + '%' + @.keyWords
+ '%' + '''' + char(10)
set @.sql = @.sql + 'OR a.jobDuties like' + char(10) + '''' + '%' + @.keyWords
+ '%' + '''' + char(10)
set @.sql = @.sql + 'OR a.jobBenefits like' + char(10) + '''' + '%' +
@.keyWords + '%' + '''' + char(10)
set @.sql = @.sql + 'OR a.jobFullDescription like' + char(10) + '''' + '%' +
@.keyWords + '%' + '''' + char(10)
set @.sql = @.sql + 'OR g.jobCategory like' + char(10) + '''' + '%' +
@.keyWords + '%' + '''' + char(10)
set @.sql = @.sql + 'OR h.educationLevel like' + char(10) + '''' + '%' +
@.keyWords + '%' + '''' + char(10)
end
if @.companyName = ''
begin
set @.sql = @.sql + 'AND e.employerName like ' + char(10) + '''%''' + char(10)
end
else
begin
set @.sql = @.sql + 'AND e.employerName like' + char(10) + '''' + '%' +
@.companyName + '%' + '''' + char(10)
end
if @.jobType != ''
begin
set @.sql = @.sql + 'AND a.jobTypeId = ' + char(10) + @.jobType + char(10)
end
else
begin
set @.sql = @.sql + 'AND a.jobTypeId like ' + char(10) + '''%''' + char(10)
end
if @.jobCategory != ''
begin
set @.sql = @.sql + 'AND a.jobCategoryId = ' + char(10) + @.jobCategory + char
(10)
end
else
begin
set @.sql = @.sql + 'AND a.jobCategoryId like ' + char(10) + '''%''' + char
(10)
end
if @.country = ''
begin
set @.sql = @.sql + 'AND b.countryId like ' + char(10) + '''%''' + char(10)
end
else
begin
set @.sql = @.sql + 'AND b.countryId = ' + char(10) + @.country + char(10)
end
if @.state != '' AND @.province = ''
begin
set @.sql = @.sql + 'AND b.stateId = ' + char(10) + @.state + char(10)
end
if @.province != '' AND @.state = ''
begin
set @.sql = @.sql + 'AND b.province like ' + char(10) + '''' + '%' +
@.province + '%' + '''' + char(10)
end
if @.city != ''
begin
set @.sql = @.sql + 'AND b.city like ' + char(10) + '''' + '%' + @.city + '%'
+ '''' + char(10)
end
if @.order = 'createDate'
begin
set @.sql = @.sql + 'order by a.createDate desc'
end
else if @.order = 'relevance'
begin
set @.sql = @.sql + 'order by a.keyWords'
end
exec (@.sql)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GODynamic SQL is a sure sign of a bad programmer. It says that you had
no idea what you were doign until a user told you at run time. It
says that you have a schema that is so screwed up you need to invent
things on the fly.
as part of it's search a form field which may contain several words
seperated by a space.<<
Forms? Fields? Those do not exist in SQL; you are still writing file
system code. You even put those silly redundant "tbl-' prefixes on
table names! And you use absurd names like "jobTypeId" or "StatusId",
which makes no sense (types are not identifiers, status oif what' --
think about it). You camelcase it to make it harder to read. And
where is you DDL? What names are VARCHAR(50) -- can you give one
example? Why are you using flags in SQL? Why do you think that "!="
is Standard SQL? Why do you think that alphabetizing the PHYSICAL
appearance of tasble names in a FROM clause will give you a meaningful
alias for the base tables.
Frankly, after almost 20 years with SQL, this is close to the worst
code I have seen. Certainly, the top ten worst.|||--CELKO-- wrote:
[snip]
> Frankly, after almost 20 years with SQL, this is close to the worst
> code I have seen. Certainly, the top ten worst.
Ooh. I'd love to see #1, if you can find/post it? :-)|||--CELKO-- wrote:
> What names are VARCHAR(50) -- can you give one
> example?
Erm, a company name? The UK Government Data Standards Catalogue notes
that Organisation Name was 'amended as the result of discussion at
Schema Group on 21 August 2002-09-21 from 70 characters to 255
characters.'
http://www.govtalk.gov.uk/gdsc/html/frames/default.htm
Here's one I found fairly quickly by searching on the bureaucracy's own
web site (http://www.companies-house.gov.uk/):
WEST MIDLANDS AND SHROPSHIRE CO-OWNERSHIP HOUSING SOCIETY (OAKS
CRESCENT) LIMITED
Jamie.|||Damien wrote:
> --CELKO-- wrote:
> Ooh. I'd love to see #1, if you can find/post it? :-)
Google the exact phrase, "This is the worst use of SQL I have ever
seen"
Jamie.|||ROTFL
onedaywhen wrote:
> Damien wrote:
>
>
> Google the exact phrase, "This is the worst use of SQL I have ever
> seen"
> Jamie.
> --
>|||CELKO ..
My web app executes this dynamic stored proc .. (that's where the forms and
fields come from).
Sorry you didn't like the naming schemes and I'm not sure why you are
making so many assumptions and judging the code ..
Anyhow .. could you post the best SQL code you've ever seen and did you
write it?
Message posted via http://www.webservertalk.com|||onedaywhen wrote:
> Damien wrote:
worst
> Google the exact phrase, "This is the worst use of SQL I have ever
> seen"
> Jamie.
> --
Well, that cleared that one up.
Well, I've just had confirmation from Amazon.com that SQL Programming
Style has shipped. Had to pay international shipping since Amazon.co.uk
aren't stocking it yet :-(
Damien|||>> I'd love to see #1, if you can find/post it? <<
#1? A shortened two-page version of it is in SQL PROGRAMMING STYLE. in
Section 6.1. It is a report from an accounting system built with
UNIONs in dynamic SQL that overflows the size limits of SQL Server.
Every account that appears in the report was done as a separate SELECT
statement witht eh same WHERE clause then UNIONed (not even UNION ALL)
into final result that was used to compute some totals. You can do it
in one statement with SUM(CASE..) constructs.
#2 was a "One True Lookup Table" in which the guy tried to fix it by
adding the needed CHECK() constraints. He had a case expression with
almost fifty "WHEN code_type = ' AND code_value = ' THEN 1 ELSE 0"
clauses on it. Since all the codes were in that one table and it had
a clustered index, the disk drive had to jump from one end of the table
to another for each row in the result set. Ever seen an out-of-balance
washing machine?
#3 was a VB programmer who was given no training and no help. He wrote
one update statement per column everywhere in a moderately sized
procedure for an educational testing service. He honestly did not know
the syntax for multi-column updates. I removed several hundred lines
of code and made it run 2300 times faster. I am good -- maybe 2-3
orders of magnitude improvement if the code stinks, but I am not that
good; he was that bad.|||>> Well, I've just had confirmation from Amazon.com that SQL Programming Sty
le has shipped. Had to pay international shipping since Amazon.co.uk aren't
stocking it yet :-( <<
Bummer! Barnes & Noble in States is pretty good about getting MKP
stuff to the shelf, but I don't know about the UK and Europe.

Key Maintenance and Stored Procedures

Basically I would like to ask whether parameters can be used to pass the value of the 'symmetric key id', 'certificate' and optionally 'password' to a stored procedure that uses encryption functions.

The reason this is appealing is that when encryption keys etc change over time (we have a requirement to decrypt data, destroy and create new keys, then encrypt data every time we lose a staff - don't ask), as we would be passing the value of keys, passwords and certificates as parameters to a standard stored procedure.

Hardcoded Example (Working)

USE PSS

GO

CREATE PROC insert_payer_ba

-- define parameters

@.param_rec_id NVARCHAR(MAX),

@.param_bsb NVARCHAR(MAX),

@.param_account NVARCHAR(MAX),

@.param_account_name NVARCHAR(MAX)

AS

BEGIN

OPEN SYMMETRIC KEY bartlett_sym

DECRYPTION BY CERTIFICATE bartlett_cert

WITH PASSWORD = 'Bartlett12_3';

DECLARE @.en_rec_id varbinary(max);

SELECT @.en_rec_id = EncryptByKey(Key_GUID('bartlett_sym'),@.param_rec_id);

DECLARE @.en_bsb varbinary(max);

SELECT @.en_bsb = EncryptByKey(Key_GUID('bartlett_sym'),@.param_bsb);

DECLARE @.en_account varbinary(max);

SELECT @.en_account = EncryptByKey(Key_GUID('bartlett_sym'),@.param_account);

DECLARE @.en_account_name varbinary(max);

SELECT @.en_account_name = EncryptByKey(Key_GUID('bartlett_sym'),@.param_account_name);

INSERT INTO [PSS].[dbo].[payer_ba_sym]

(rec_id, bsb, account, account_name)

VALUES (

@.en_rec_id,

@.en_bsb,

@.en_account,

@.en_account_name

);

CLOSE SYMMETRIC KEY bartlett_sym;

END

GO

PROPOSED USAGE (WHICH DOESN'T WORK)

USE PSS

GO

CREATE PROC insert_payer_ba

-- define parameters

@.param_symkeyguid NVARCHAR(MAX), --(tried VARBINARY as well)

@.param_cert NVARCHAR(MAX),

@.param_certpass VARBINARY(MAX),

@.param_rec_id NVARCHAR(MAX),

@.param_bsb NVARCHAR(MAX),

@.param_account NVARCHAR(MAX),

@.param_account_name NVARCHAR(MAX)

AS

BEGIN

OPEN SYMMETRIC KEY @.param_symkeyguid

DECRYPTION BY CERTIFICATE @.param_cert

WITH PASSWORD = @.param_certpass;

end

DECLARE @.en_rec_id varbinary(max);

SELECT @.en_rec_id = EncryptByKey(Key_GUID(@.param_symkeyguid),@.param_rec_id);

DECLARE @.en_bsb varbinary(max);

SELECT @.en_bsb = EncryptByKey(Key_GUID(@.param_symkeyguid),@.param_bsb);

DECLARE @.en_account varbinary(max);

SELECT @.en_account = EncryptByKey(Key_GUID(@.param_symkeyguid),@.param_account);

DECLARE @.en_account_name varbinary(max);

SELECT @.en_account_name = EncryptByKey(Key_GUID(@.param_symkeyguid),@.param_account_name);

INSERT INTO payer_ba_sym

(rec_id, bsb, account, account_name)

VALUES (

@.en_rec_id,

@.en_bsb,

@.en_account,

@.en_account_name

);

CLOSE SYMMETRIC KEY @.param_symkeyguid;

END

GO

Any assistance in correcting this syntax (if indeed these functions accept parameters would be greatly appreciated).

- Andrew

You can do this if you use dynamic SQL. That is build the SQL query as a string, then pass to the "EXEC" statement. For example:

DECLARE @.sqlstring NVARCHAR(60);

SET @.sqlstring = 'OPEN SYMMETRIC KEY ' + @.param_symkeyguid + ' DECRYPTION BY CERTIFICATE ' + @.param_cert;

EXEC (@.sqlstring);

Note that this procedure is a bit vulnerable especially because you are passing in a password as a parameter. You could make this somewhat more secure by having the certificate be encrypted by the database master key encrypted by the service master key. This will avoid the password issue.

In general, I would hesitate to pass sensitive information in as parameters (this includes the symmetric key and certificate ids). You should be sure that the proc checks the parameters very careful and that the permission on the procedure and the underlying objects are tightly controlled.

Please let me know if you would like further info.

Thanks,

Sung

|||

Also note that the other reason you need to check parameters and monitor permissions very closely is that this sort of parsing is VERY vulnerable to SQL injection attacks.

Sung

|||

Thanks Sung,

You are right about passing paswords as variables. In fact I will only pass the certifcate and key names as vars.

The syntax seems to work, I shall do some more testing, thanks mate.

- Andrew

|||

Hey Andrew,

I would still be careful about possible SQL injection attacks. One way you can minimize this is by doing a simple parameter check such as

if (cert_id(@.cert_name) is not null) ... <your code here>

else ... <your error code here>

This should be done for each type on all names passed in. This, at a minimum, checks to make sure that people are actually passing in a valid object names.

Hope this helps,

Sung

|||

I am including a couple of links for SQL injection articles that may be helpful. I highly recommend reading the second link even if you are already familiar with SQL injection.

· SQL Injection http://msdn2.microsoft.com/en-us/library/ms161953.aspx

· New SQL Truncation Attacks And How To Avoid Them http://msdn.microsoft.com/msdnmag/issues/06/11/SQLSecurity/default.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hello Sung,

My old syntax prior to passing parameters for the certificate and key was this:

DECLARE @.en_rec_id varbinary(max);

SELECT @.en_rec_id = EncryptByKey(Key_GUID(bartlett_sym),@.param_rec_id); it worked but values for key etc were hardcoded

If I apply your method to the encryption statements:

DECLARE $sqlstring NVARCHAR(MAX);

SET @.sqlstring = 'EncryptByKey(Key_GUID(' + "'" @.param_bartlett_sym + "'" + '),' + @.param_rec_id + ')';

DECLARE @.en_rec_id varbinary(max);

SELECT @.en_rec_id = EXEC(@.sqlstring); -- when I run the code it bombs here near EXEC, so I need some help with this line

Any assistance appreciated.

- Andrew

|||

Hey Andrew,

It's actually a little easier than that. Only DDL and perhaps a few other statement types don't support dynamic SQL. Built-ins should already support dynamic SQL so you could simply directly call:

SELECT @.en_rec_id = EncryptByKey(Key_GUID(@.param_bartlett_sym),@.param_rec_id)

Also, Laurentiu suggested another website to check:

http://www.sommarskog.se/dynamic_sql.html

You can also look into the "sp_executesql" and "quotename" functions as they might help you.

Sung

|||

Thanks Sung,

Sorry about the delayed reply. Actually passing the sym key in the manner described eg

SELECT @.en_rec_id = EncryptByKey(Key_GUID(@.param_bartlett_sym),@.param_rec_id)

results in null.

So I'm not sure how to get a result...

This doesn't work (might be on the right track thought).

DECLARE @.en_rec_id varbinary(max);

DECLARE @.str_cert NVARCHAR(MAX);

SET @.str_cert = 'SELECT @.en_rec_id = EncryptByKey(Key_GUID(' + "'" + @.param_cert + "'" + '),' + @.param_rec_id + ')';

EXECUTE sp_executesql @.str_cert, N'@.en_rec_id VARBINARY OUTPUT', @.en_rec_id OUTPUT;

Basically I wish to run the dynamic query and have it pass the value of @.en_rec_id as output for later use in an insert statement in the same stored procedure.

See original post above.

Any help greatly appreciated.

- Andrew

|||

Hey Andrew,

Sorry for the late response.

I did a quick test and it seems to work for me. Quick question, where do you open the key? You can verify the key is actually open by checking the sys.open_keys catalog view. You will need to have the key open prior to encrypting with it.

Thanks,

Sung

Keeping users out while updating data

I have a flat file with data that I need to use to update existing records
in my database with. I wrote a Stored Proc to do this, but I want to run it
when everyone is out of the database. My question is, how do I keep users
from accessing the database while my update is running? I thought about
taking it offline but BOL said that while it's offline it can not be
modified. Does this mean the data or the structure or both?
Thanks
Mike
You could set it to single_user temporarily...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"M Smith" <msmith@.avma.org> wrote in message
news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> I have a flat file with data that I need to use to update existing records
> in my database with. I wrote a Stored Proc to do this, but I want to run
it
> when everyone is out of the database. My question is, how do I keep users
> from accessing the database while my update is running? I thought about
> taking it offline but BOL said that while it's offline it can not be
> modified. Does this mean the data or the structure or both?
> Thanks
> Mike
>
|||OK, I set the database to start up in single_user mode. The problem is when
I try to open query analyzer to execute my stored proc it won't let me in.
It gives me a log in failure because SQL Server is in single user mode. How
can I execute my stored proc?
Mike
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> You could set it to single_user temporarily...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
records[vbcol=seagreen]
run[vbcol=seagreen]
> it
users
>
|||Setting it to restricted_user might be better assuming the normal users do
not have elevated privileges - with single_user the risk is someone getting
the connection before you which sounds like what is happening
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M Smith" <msmith@.avma.org> wrote in message
news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> OK, I set the database to start up in single_user mode. The problem is
when
> I try to open query analyzer to execute my stored proc it won't let me in.
> It gives me a log in failure because SQL Server is in single user mode.
How[vbcol=seagreen]
> can I execute my stored proc?
> Mike
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> records
> run
> users
about
>
|||How about just using a locking hint for a more restrictive lock. If you used the holdlock locking hint:
SELECT * FROM [TABLE] (HOLDLOCK) it would be as if you were briefly the only user of that table.
There are other locks less restrictive than that like tablock and UPDLOCK, which will let others read the data.
|||Hi,
I do agree with Jaspers suggestion, set the database to restricted user and
using the same connection (inside query analyzer) try to execute the
procedure
Alter database northwind set RESTRICTED_USER with rollback immediate
go
exec procedures_name
go
Alter database northwind set MULTI_USER
Thanks
Hari
MCDBA
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uH2v#LdOEHA.1616@.TK2MSFTNGP12.phx.gbl...
> Setting it to restricted_user might be better assuming the normal users do
> not have elevated privileges - with single_user the risk is someone
getting[vbcol=seagreen]
> the connection before you which sounds like what is happening
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> when
in.[vbcol=seagreen]
> How
to
> about
>

Keeping users out while updating data

I have a flat file with data that I need to use to update existing records
in my database with. I wrote a Stored Proc to do this, but I want to run it
when everyone is out of the database. My question is, how do I keep users
from accessing the database while my update is running? I thought about
taking it offline but BOL said that while it's offline it can not be
modified. Does this mean the data or the structure or both?
Thanks
MikeYou could set it to single_user temporarily...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"M Smith" <msmith@.avma.org> wrote in message
news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> I have a flat file with data that I need to use to update existing records
> in my database with. I wrote a Stored Proc to do this, but I want to run
it
> when everyone is out of the database. My question is, how do I keep users
> from accessing the database while my update is running? I thought about
> taking it offline but BOL said that while it's offline it can not be
> modified. Does this mean the data or the structure or both?
> Thanks
> Mike
>|||OK, I set the database to start up in single_user mode. The problem is when
I try to open query analyzer to execute my stored proc it won't let me in.
It gives me a log in failure because SQL Server is in single user mode. How
can I execute my stored proc?
Mike
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> You could set it to single_user temporarily...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
records[vbcol=seagreen]
run[vbcol=seagreen]
> it
users[vbcol=seagreen]
>|||Setting it to restricted_user might be better assuming the normal users do
not have elevated privileges - with single_user the risk is someone getting
the connection before you which sounds like what is happening
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M Smith" <msmith@.avma.org> wrote in message
news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> OK, I set the database to start up in single_user mode. The problem is
when
> I try to open query analyzer to execute my stored proc it won't let me in.
> It gives me a log in failure because SQL Server is in single user mode.
How
> can I execute my stored proc?
> Mike
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> records
> run
> users
about[vbcol=seagreen]
>|||How about just using a locking hint for a more restrictive lock. If you used
the holdlock locking hint:
SELECT * FROM [TABLE] (HOLDLOCK) it would be as if you were briefly the
only user of that table.
There are other locks less restrictive than that like tablock and UPDLOCK, w
hich will let others read the data.|||Hi,
I do agree with Jaspers suggestion, set the database to restricted user and
using the same connection (inside query analyzer) try to execute the
procedure
Alter database northwind set RESTRICTED_USER with rollback immediate
go
exec procedures_name
go
Alter database northwind set MULTI_USER
Thanks
Hari
MCDBA
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uH2v#LdOEHA.1616@.TK2MSFTNGP12.phx.gbl...
> Setting it to restricted_user might be better assuming the normal users do
> not have elevated privileges - with single_user the risk is someone
getting
> the connection before you which sounds like what is happening
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> when
in.[vbcol=seagreen]
> How
to[vbcol=seagreen]
> about
>

Keeping users out while updating data

I have a flat file with data that I need to use to update existing records
in my database with. I wrote a Stored Proc to do this, but I want to run it
when everyone is out of the database. My question is, how do I keep users
from accessing the database while my update is running? I thought about
taking it offline but BOL said that while it's offline it can not be
modified. Does this mean the data or the structure or both?
Thanks
MikeYou could set it to single_user temporarily...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"M Smith" <msmith@.avma.org> wrote in message
news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> I have a flat file with data that I need to use to update existing records
> in my database with. I wrote a Stored Proc to do this, but I want to run
it
> when everyone is out of the database. My question is, how do I keep users
> from accessing the database while my update is running? I thought about
> taking it offline but BOL said that while it's offline it can not be
> modified. Does this mean the data or the structure or both?
> Thanks
> Mike
>|||OK, I set the database to start up in single_user mode. The problem is when
I try to open query analyzer to execute my stored proc it won't let me in.
It gives me a log in failure because SQL Server is in single user mode. How
can I execute my stored proc?
Mike
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> You could set it to single_user temporarily...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> > I have a flat file with data that I need to use to update existing
records
> > in my database with. I wrote a Stored Proc to do this, but I want to
run
> it
> > when everyone is out of the database. My question is, how do I keep
users
> > from accessing the database while my update is running? I thought about
> > taking it offline but BOL said that while it's offline it can not be
> > modified. Does this mean the data or the structure or both?
> >
> > Thanks
> > Mike
> >
> >
>|||Setting it to restricted_user might be better assuming the normal users do
not have elevated privileges - with single_user the risk is someone getting
the connection before you which sounds like what is happening
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"M Smith" <msmith@.avma.org> wrote in message
news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> OK, I set the database to start up in single_user mode. The problem is
when
> I try to open query analyzer to execute my stored proc it won't let me in.
> It gives me a log in failure because SQL Server is in single user mode.
How
> can I execute my stored proc?
> Mike
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> > You could set it to single_user temporarily...
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >
> > "M Smith" <msmith@.avma.org> wrote in message
> > news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> > > I have a flat file with data that I need to use to update existing
> records
> > > in my database with. I wrote a Stored Proc to do this, but I want to
> run
> > it
> > > when everyone is out of the database. My question is, how do I keep
> users
> > > from accessing the database while my update is running? I thought
about
> > > taking it offline but BOL said that while it's offline it can not be
> > > modified. Does this mean the data or the structure or both?
> > >
> > > Thanks
> > > Mike
> > >
> > >
> >
> >
>|||How about just using a locking hint for a more restrictive lock. If you used the holdlock locking hint:
SELECT * FROM [TABLE] (HOLDLOCK) it would be as if you were briefly the only user of that table
There are other locks less restrictive than that like tablock and UPDLOCK, which will let others read the data.|||Hi,
I do agree with Jaspers suggestion, set the database to restricted user and
using the same connection (inside query analyzer) try to execute the
procedure
Alter database northwind set RESTRICTED_USER with rollback immediate
go
exec procedures_name
go
Alter database northwind set MULTI_USER
Thanks
Hari
MCDBA
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uH2v#LdOEHA.1616@.TK2MSFTNGP12.phx.gbl...
> Setting it to restricted_user might be better assuming the normal users do
> not have elevated privileges - with single_user the risk is someone
getting
> the connection before you which sounds like what is happening
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "M Smith" <msmith@.avma.org> wrote in message
> news:eagtzxcOEHA.2780@.TK2MSFTNGP09.phx.gbl...
> > OK, I set the database to start up in single_user mode. The problem is
> when
> > I try to open query analyzer to execute my stored proc it won't let me
in.
> > It gives me a log in failure because SQL Server is in single user mode.
> How
> > can I execute my stored proc?
> >
> > Mike
> >
> > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > news:e15w6VcOEHA.2344@.TK2MSFTNGP10.phx.gbl...
> > > You could set it to single_user temporarily...
> > >
> > > --
> > > Aaron Bertrand
> > > SQL Server MVP
> > > http://www.aspfaq.com/
> > >
> > >
> > >
> > >
> > > "M Smith" <msmith@.avma.org> wrote in message
> > > news:#tN5xTcOEHA.644@.tk2msftngp13.phx.gbl...
> > > > I have a flat file with data that I need to use to update existing
> > records
> > > > in my database with. I wrote a Stored Proc to do this, but I want
to
> > run
> > > it
> > > > when everyone is out of the database. My question is, how do I keep
> > users
> > > > from accessing the database while my update is running? I thought
> about
> > > > taking it offline but BOL said that while it's offline it can not be
> > > > modified. Does this mean the data or the structure or both?
> > > >
> > > > Thanks
> > > > Mike
> > > >
> > > >
> > >
> > >
> >
> >
>

Keeping the CPU load to 50%

Hi,
I have a stored procedure which does intense calculations. It does
calculations against millions of records and when it is executed the CPU
performance goes up to 100%. Is there a way in SQL queries which specifies
that CPU performance should not go beyond 50% for that particular stored
procedure.
thanks in advance for your help.
Bhagwati
If it is so critical , consider buying a new processor ( if it keeps
getting 100% for 10-20 min)or optmize the query
"Bhagwati" <Bhagwati@.discussions.microsoft.com> wrote in message
news:2E9E6FA1-7CCC-459E-825A-55A162D6F9F7@.microsoft.com...
> Hi,
> I have a stored procedure which does intense calculations. It does
> calculations against millions of records and when it is executed the CPU
> performance goes up to 100%. Is there a way in SQL queries which specifies
> that CPU performance should not go beyond 50% for that particular stored
> procedure.
> thanks in advance for your help.
> Bhagwati
|||No you can not limit any thread to a certain percentage of the resources
without killing it altogether. You might want to consider upgrading to
SQL2005 and look at doing the calculations in a CLR sp or UDF. The CLR can
usually do complex calculations hundreds or even thousands of times faster
than SQL Server.
Andrew J. Kelly SQL MVP
"Bhagwati" <Bhagwati@.discussions.microsoft.com> wrote in message
news:2E9E6FA1-7CCC-459E-825A-55A162D6F9F7@.microsoft.com...
> Hi,
> I have a stored procedure which does intense calculations. It does
> calculations against millions of records and when it is executed the CPU
> performance goes up to 100%. Is there a way in SQL queries which specifies
> that CPU performance should not go beyond 50% for that particular stored
> procedure.
> thanks in advance for your help.
> Bhagwati

Keeping the CPU load to 50%

Hi,
I have a stored procedure which does intense calculations. It does
calculations against millions of records and when it is executed the CPU
performance goes up to 100%. Is there a way in SQL queries which specifies
that CPU performance should not go beyond 50% for that particular stored
procedure.
thanks in advance for your help.
BhagwatiIf it is so critical , consider buying a new processor ( if it keeps
getting 100% for 10-20 min)or optmize the query
"Bhagwati" <Bhagwati@.discussions.microsoft.com> wrote in message
news:2E9E6FA1-7CCC-459E-825A-55A162D6F9F7@.microsoft.com...
> Hi,
> I have a stored procedure which does intense calculations. It does
> calculations against millions of records and when it is executed the CPU
> performance goes up to 100%. Is there a way in SQL queries which specifies
> that CPU performance should not go beyond 50% for that particular stored
> procedure.
> thanks in advance for your help.
> Bhagwati|||No you can not limit any thread to a certain percentage of the resources
without killing it altogether. You might want to consider upgrading to
SQL2005 and look at doing the calculations in a CLR sp or UDF. The CLR can
usually do complex calculations hundreds or even thousands of times faster
than SQL Server.
Andrew J. Kelly SQL MVP
"Bhagwati" <Bhagwati@.discussions.microsoft.com> wrote in message
news:2E9E6FA1-7CCC-459E-825A-55A162D6F9F7@.microsoft.com...
> Hi,
> I have a stored procedure which does intense calculations. It does
> calculations against millions of records and when it is executed the CPU
> performance goes up to 100%. Is there a way in SQL queries which specifies
> that CPU performance should not go beyond 50% for that particular stored
> procedure.
> thanks in advance for your help.
> Bhagwati

Keeping the CPU load to 50%

Hi,
I have a stored procedure which does intense calculations. It does
calculations against millions of records and when it is executed the CPU
performance goes up to 100%. Is there a way in SQL queries which specifies
that CPU performance should not go beyond 50% for that particular stored
procedure.
thanks in advance for your help.
BhagwatiIf it is so critical , consider buying a new processor ( if it keeps
getting 100% for 10-20 min)or optmize the query
"Bhagwati" <Bhagwati@.discussions.microsoft.com> wrote in message
news:2E9E6FA1-7CCC-459E-825A-55A162D6F9F7@.microsoft.com...
> Hi,
> I have a stored procedure which does intense calculations. It does
> calculations against millions of records and when it is executed the CPU
> performance goes up to 100%. Is there a way in SQL queries which specifies
> that CPU performance should not go beyond 50% for that particular stored
> procedure.
> thanks in advance for your help.
> Bhagwati|||No you can not limit any thread to a certain percentage of the resources
without killing it altogether. You might want to consider upgrading to
SQL2005 and look at doing the calculations in a CLR sp or UDF. The CLR can
usually do complex calculations hundreds or even thousands of times faster
than SQL Server.
--
Andrew J. Kelly SQL MVP
"Bhagwati" <Bhagwati@.discussions.microsoft.com> wrote in message
news:2E9E6FA1-7CCC-459E-825A-55A162D6F9F7@.microsoft.com...
> Hi,
> I have a stored procedure which does intense calculations. It does
> calculations against millions of records and when it is executed the CPU
> performance goes up to 100%. Is there a way in SQL queries which specifies
> that CPU performance should not go beyond 50% for that particular stored
> procedure.
> thanks in advance for your help.
> Bhagwati