query - specify multiple items

Hi,
I am trying to develop a report where the user is able to specify the
information to be pulled from one specific field.
For example... the table has 2 fields... Company ID, Company Name
I only want to see information where Company ID = ABC and XYZ
but I want the user to be able to specify multiple Company ID's in the
report parameter
Thanks,
JenI've got part of the query figured out:
SELECT Company ID, Company Name
FROM Company Profile
WHERE (Company ID (@.CompanyID))
But this only lets me query one company id at a time. how can I alter the
query to allow users to query multiple company id's?
thanks
jen
"Jennifer Mar" <jmar@.cityharvest.org> wrote in message
news:O6rTcrxZHHA.1300@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am trying to develop a report where the user is able to specify the
> information to be pulled from one specific field.
> For example... the table has 2 fields... Company ID, Company Name
> I only want to see information where Company ID = ABC and XYZ
> but I want the user to be able to specify multiple Company ID's in the
> report parameter
> Thanks,
> Jen
>|||Hello Jennifer,
If you are using the SQL Server 2005, you could use the Multi-value
Parameter.
When you enable the Multi-value for the parameter, you could get a dropdown
list in the report.
Then you need to use the IN statement to query the data.
You could refer this article:
Adding a Multivalue Parameter
http://msdn2.microsoft.com/en-us/aa337396.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||We're currently running sql server 2000. i am using visual studio .net to
build the reports.
The way the query is build now, it allows the user to enter the parameters
in a seperate text box for each company id they query. how can i build the
query so that they can just enter a string of unlimited company id's?
thanks!
jen
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:OO42Wm4ZHHA.1784@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jennifer,
> If you are using the SQL Server 2005, you could use the Multi-value
> Parameter.
> When you enable the Multi-value for the parameter, you could get a
dropdown
> list in the report.
> Then you need to use the IN statement to query the data.
> You could refer this article:
> Adding a Multivalue Parameter
> http://msdn2.microsoft.com/en-us/aa337396.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
>
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Jen,
You could use the parameter as the String and use the comma mark to
seperate each company id.
And in the query, you could use the parameter like this:
Select * from tbl_companies where companyid in (@.Parameters)
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||hi wei,
thanks so much for checking in. everything worked out great :)
jen
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:5JytuQ7aHHA.1028@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hello Jen,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Query - need help using the IN function/statement

Hello all,
I am trying to find the instances in a field containg specific keywords or
strings of information. My table name is History, and my field name is
Notes. So what I am trying to do is find every record where History.Notess
conatins;
'chrom' or 'cell' or 'lab'
I think I need the IN functin as opposed to using a bunch of OR statements.
The OR statments work, but there are so many different keywords/strings,
that it is a real mess to enter all of the information.
Thank you for your help
JohnJohn,
You can put all the keywords in a table variable / temporary table /
permanent table a use:
select distinct notes
from history as h inner join t1 on h.notes like '%' + t1.keyword + '%'
Example:
use northwind
go
create table t1 (
c1 varchar(255)
)
go
create table t2 (
keyword varchar(25) not null unique
)
go
insert into t1 values('microsoft')
insert into t1 values('oracle')
insert into t1 values('microfocus')
go
insert into t2 values('micro')
insert into t2 values('of')
go
select distinct
t1.c1
from
t1
inner join
t2
on t1.c1 like '%' + t2.keyword + '%'
go
drop table t1, t2
go
Column [notes] can not be of type text / ntext.
AMB
"John Lloyd" wrote:

> Hello all,
> I am trying to find the instances in a field containg specific keywords or
> strings of information. My table name is History, and my field name is
> Notes. So what I am trying to do is find every record where History.Notes
s
> conatins;
> 'chrom' or 'cell' or 'lab'
> I think I need the IN functin as opposed to using a bunch of OR statements
.
> The OR statments work, but there are so many different keywords/strings,
> that it is a real mess to enter all of the information.
> Thank you for your help
> John
>sql

quastion about triggers

hi,

I have a few quastions about triggers:

1) I want that to run a specific function or exe, after a row update.

How can I run a function via the trigger?

2) Is ther any trigger that for an updae of a culomn (not a row)?

thanks,

moria

Hi Moria,
First of all, there are differences id you implement the trigger on a CLR basis (2005) or on a TSQL basis (<2005), so next time, try to give some information about your system, that the proposed solution will better fit your needs.
"How can I run a function via the trigger?"
You talked about executing a EXE, therefore you cann call the extended procedure SP_CMDSHELL which is able to execute a program via the command line (More information about that can be found in the BOL)
COMMENT: It′s odd to execute a command in a trigger. Trigger bahve synchronously, while executing the trigger other people using the data won′t be able to change the lock data (depends on your locking, worst case would be table locking). If there is an error in your command (or a long running command), like waiting for input or something, the trigger will never come back, so you have to
keep that in mind.

"Is ther any trigger that for an updae of a culomn (not a row)?"
Well, yes and no. A trigger is fired per statement (not per ROW) and regardless if your "watched" column changed or not, but if you are in the trigger you have the possibility to use the
IF UPDATE(SomeColumn)
DoSomething
But the trigger is fired regardless, if the column changed or not, keep that in mind, if you have more than this statement in your trigger.
HTH, Jens Suessmeyer.

|||

first of all, thank you for the detailed answer.

I am working with SQL 2000, and .NET 2005.

>>"More information about that can be found in the BOL"

what's BOL, and where can I find it?

>>we understand that an exe can block the SQL, but we want to know when a user updates a table so that we will run a certain program to take the new data, analyze it and reload it to another table. if not by an exe, how do you recommend to do it?

>>In case of deleted a column, is the trigger fired? which one?

thanks a lot,

moria.

|||

Hello again,

"what's BOL, and where can I find it?"
http://www.aspfaq.com/show.asp?id=2229

If this is an external program you sure have to use the exe, but what about wrting a entry in a table that something changed on the program running on an at basis or as a service checks the data, does its recalculation (or whatever) and marks the process as done in the table ? Thats are designing question which cannot be decided on the basis of the only few information given.

HTH, Jens Suessmeyer.

qry wont use index

I've got a query which just won't use an index, instead, it does a table scan on a specific column. Basically the query is:

select count(*)
from table1 t1
inner join table2 t2 on (t1.myref = t2.myref)
where myint2 in (1,2,3)
and (myval between -1 and -2 or myint1 = 1)

In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.

EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.

What's going on?The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).

-PatP|||how's that? when changing
where myint2 in (1,2,3)
into
where myint2 = 1
I still get a table scan.

Besides, I do get an index-scan in the re-created setup.
I tried a defrag, reindex AND recompute statistics. It just wont show up.
I really don't get it.|||Hogtie the optimizer and try it. Use SET FORCEPLAN ON (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_8mni.asp) and hint the index. See if you get a result set while you are young enough to still care.

Let me know what you find out, I'm curious now!

-PatP|||hah! now your stuck! :>
the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).|||oh btw: it takes 14 minutes to come back with a rowcount of 85.
The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?|||Unfortunately, nothing changed over the weekend.
I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
Still a full table scan, even with index-hint and forceplan set.

Qns on SQL Express Database

Hi

Is it possible to blank/null out all the data in a specific table in the database, executed in a button click event?

What do you mean? Truncate? Delete? Update?

Probably truncate, as a table with all rows filled with blnaks/nulls make no sense to me.

|||

My situation:

I am writing a program to collect real live data like temperature. At the start of my program the tables in the database are blank. The collected real live data will go into the database tables. The user has the option of deleting all the data in the tables/table at one shot (not manually delete row by row) and start the collection of data afresh.

-

So, I think deleting all the data would be suitable. How to go about doing it? Will the number of rows of data affect the deleting process?

BTW, could help me out in my thread here?

Thanks.

|||

Transactions are logged in SQL Server, so the number of rows will defintely affect the performance. If you don′t want to log the deletions of the data you might use the TRUNCATE Statement for that. This is only possible if you don′t use any (or drop them first) Foreign key constraints. If you don′t want to drop the deletions you will have to delete the data in the relational order (first the child records then the parent records) if you did not specify any Cascading DMl operations.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi, am I looking at the correct syntax for the truncate statement?

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

if yes, how do I use it? Don't understand what its trying to tell me. =(

|||Thats quite easy: TRUNCATE TABLE ownerorschema.TableName
and you should be done.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||May I know what is owner or schema?|||

Hi,

beginning with Sql Server 2005 the objects are stored in a schema. Schema can be owned by multiple owners. This is a switch in design between the "old" SQL Server 2k version. You might have a look in the BOL (Books Online - the help files of SQL Server) to see which actually changed. There is much information about it.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

q; delete files

I need to delete all the *.txt file in a specific folder modified date is
older than 2 months,. How can I do this in a stored procedure?JIM.H. wrote:
> I need to delete all the *.txt file in a specific folder modified date is
> older than 2 months,. How can I do this in a stored procedure?
http://realsqlguy.com/serendipity/a...th-The-Old.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you are capable of SQL Server 2005 you would do that the best with a
CLR procedure. Looping though the files collections and delewting the
appropiate files.
HTH, Jens Suessmyer.
http://www.sqlserver2005.de
--

q; delete files

I need to delete all the *.txt file in a specific folder modified date is
older than 2 months,. How can I do this in a stored procedure?JIM.H. wrote:
> I need to delete all the *.txt file in a specific folder modified date is
> older than 2 months,. How can I do this in a stored procedure?
http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you are capable of SQL Server 2005 you would do that the best with a
CLR procedure. Looping though the files collections and delewting the
appropiate files.
HTH, Jens Suessmyer.
--
http://www.sqlserver2005.de
--