how do I make 30 sec running query (select c1 sum(x) from t1 where c1 > 1000 group by c1) run

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.

Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?

Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?

Thanks in for you help in advance

if the memory available is not enough for internal operations like aggregation and ordering, SQL Server will implictly go to Tempdb and will store the results intermediately here. You cannot avoid is, beside putting more available RAM on the process. Don′t know why this slows down your process that much, did you had a look in the SQL Server logs, esprically on database growth ? Maybe SQL Server is increasing the data files one by one, leading to the problem that the query wioll be halted for the time needed to extend the database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How do I lose the exponential display in Query Analyzer?

I have a SQL Server 2000 database with a table with a
field defined as simply [float]. I inserted several
values into the table for this field such as 0.0024.
My question is that when I query the table as follows:
select rcf from tableX
I get such values as 2.3999999999999998E-3. Is there
any reason why SQL Server simply does not display
0.0024? How can I prevent exponential display? Thanks!
GusFloat is an approximate data type and some values cannot be precisely
stored. QA is showing the actual value stored in the database. You can
cast the value to an exact type for display purposes:
DECLARE @.x float
SET @.x = 0.0024
SELECT @.x
SELECT CAST(@.x AS decimal(10,4))
See 'Approximate numeric data' in the SQL Server 2000 Books Online
<createdb.chm::/cm_8_des_04_82ic.htm> for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Gus" <gcoll@.yahoo.com> wrote in message
news:be7512a6.0309241846.4892c7ff@.posting.google.com...
> I have a SQL Server 2000 database with a table with a
> field defined as simply [float]. I inserted several
> values into the table for this field such as 0.0024.
> My question is that when I query the table as follows:
> select rcf from tableX
> I get such values as 2.3999999999999998E-3. Is there
> any reason why SQL Server simply does not display
> 0.0024? How can I prevent exponential display? Thanks!
> Gus

How do I lengthen the time out for SQL queries?

I'd like to make the connection time unlimited for a certain sql query to SQL Server from ASP.NET, how do I do this? I already tried setting "Connect Timeout=0" in the sql connection string, this doesn't work...

Please help.Have you tried setting the CommandTimeout property for the SqlCommand object?

SqlCommand.CommandTimeout = iSecondsForTimeout

|||Thanks, that was it.sql

how do i know who accessed/ran a procedure or query

how do i know who accessed/ran a procedure or query

Quote:

Originally Posted by devikacs

how do i know who accessed/ran a procedure or query

You can use a tool such as the SQL Profiler to filter queries as they happen, or you will have to modify your stored procedures to keep a history of who runs it. You could also build history functionality into your front end.

Other than that, I do not know of a way to keep track of people logging in directly to your server and executing queries.|||

Quote:

Originally Posted by Motoma

You can use a tool such as the SQL Profiler to filter queries as they happen, or you will have to modify your stored procedures to keep a history of who runs it. You could also build history functionality into your front end.

Other than that, I do not know of a way to keep track of people logging in directly to your server and executing queries.

thanks. i want to make a log of who accessed, so wanted to know if there's any way i can detect when some user accesses a database, or runs a query of his own

How Do i insert For XML Result in another table

Hi,
I have table with nText field and i want to insert 'for xml query' into that field, what is datatype for 'for xml quey result'
e.g (select * from publishers for XML auto elements)

JigarHi Jigar,

I'm not sure I understand. Do you mean that you want to save the results of that SELECT query, which returns XML, in the nText field? If so, nText is fine, since the data is text.

Or do you mean something else?

Don|||I have table with fields like
itemID int,
XML NText

is there any way i can insert through StoredProcedure .

my storedprocedure should do like this
--gets data from first table using for XML
--Insert XML data into another table as described above.

i can not find any way to handle this through storedprocedure

currently i am doing following using client

-- open datareader that featches xml from first table
-- save xml using another call to database.

i was wondering if there is any way to do that using same sproc.|||Well, I've been mulling this over without much of a solution. You could always use a cursor but that would be ugly, fragile, and slow.

I'll keep thinking about this. I'm sure I'm missing something simple.

Anyone else?

Don

how do I ignore punctuation?

Just as the title suggests, how do I ignore punctuation in an SQL query using full txt searching?

Say if I wanted to search on:

O'brien
or
Mary's Hat.

I would expect that the search engine would look for:
Obrien OR O'brien
and
Marys hat OR Mary's Hat.

Im using SQL 2005

This is handled by the iFilters mechanism, using a "word breaker". Punctuation may not react the way you think in this example, because of possessives, proper names, and contractions. You can start your search by reading more about word breakers by pasting this link in Books Online in the URL Bar:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/d4bdd16b-a2db-4101-a946-583d1c674229.htm

How do I group this query here

I have the following sql statement below;

DECLARE @.val int
SET @.val = 1
WHILE (@.val <= 7 )
BEGIN
SELECT TOP(1) id, queue
FROM itn_articles WHERE asection = @.val
ORDER BY queue DESC
SET @.val = @.val + 1
END

Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable

Why do you need to do it in a loop? You can get the same results as

SELECT Id,Queue FROM itn_articles WHERE asection <= 7

|||

OMG, I feel like such a douche, lol..Thanks for the help guys I really appreciate it

How do I get number of fields returned by query?

Hi.

I am trying to get the results of a dynamic sql statement into a #table, in order to filter them. Given that I don't know how many fields will be returned, how do I accomplish this?

I believe I need to create the #table in advance, and then run the dynamic string as part of an insert [eg 'insert into #table exec (@.sql)' ], but in order to do this I need to know how many fields are going to be returned.

The results might also be returned by a procedure rather than a simple SQL statement, so I can't just parse @.sql to get the fields.

As an example,

declare @.sql nvarchar(200)

select @.sql = 'select "a" as ColA, "b" as ColB'

exec sp_executesql @.sql

returns two columns of data.

I think I need to get the results, count the fields, create the table and finally re-run the query with an insert to poulate the table. So how do I count the columns? And for bonus points, how do I get the column names?

Many thanks,

Neil Jones

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..)

|||

Thanks for your reply

Nitin Khurana wrote:

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

The problem here is that I can't edit the query. All I know at runtime is that it's a dynamic sql statement, which could be a simple select, or an execution of a stored procedure

Nitin Khurana wrote:

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

My question is, how do I get the number of columns?

Nitin Khurana wrote:

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..

Unfortunately it's not the number of rows that is the problem.

Cheers,

Neil Jones

|||

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

|||

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

|||

Try this:

SELECT * INTO #Temp
FROM (<Your SQL Query>) as T;

Note: If the columns returned by the query aren't properly named (no column name), this will not work. Otherwise the table will be created automatically with the column names from the query. Once the table ist created, you can access the Information about the columns from system views:

USE tempdb
GO
SELECT COUNT(*) FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name like '#Test%');

This will work with MS SQL Server 2005

Regards,

Paddy

|||

Here you go with a complete sample when having the statement as a variable:

USE AdventureWorks
GO

SET NOCOUNT ON;
GO

-- Create a Table for Testing
CREATE TABLE Test
( col1 INT NOT NULL
, col2 VARCHAR(20) NOT NULL
);
GO

-- Some useful Information
INSERT INTO Test VALUES (1, 'Test1');
INSERT INTO Test VALUES (2, 'Test2');
INSERT INTO Test VALUES (3, 'Test3');
INSERT INTO Test VALUES (4, 'Test4');
INSERT INTO Test VALUES (5, 'Test5');
INSERT INTO Test VALUES (6, 'Test6');
INSERT INTO Test VALUES (7, 'Test7');
INSERT INTO Test VALUES (8, 'Test8');
INSERT INTO Test VALUES (9, 'Test9');
INSERT INTO Test VALUES (10, 'Test10');
GO

-- The dynamic sql statement
DECLARE @.sql NVARCHAR(200);
SET @.sql = 'SELECT col2, col1 FROM Test';

-- Extend the statement for creating a temporary table (Note: Use Global Temporary Table (##TableName))
DECLARE @.sql2 NVARCHAR(250)
set @.sql2 = 'SELECT * INTO ##Test FROM (' + @.sql + ') AS T;';

-- Run the extended Statement
EXEC(@.sql2);
GO

-- View the result
SELECT * FROM ##Test;
GO

-- Get Information about the Temporary Table
USE tempdb
GO

-- Column Count
SELECT COUNT(*) FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- Names of Columns
SELECT name FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- CleanUp
USE AdventureWorks
GO

-- Drop the temporary Table
DROP TABLE ##Test;
GO

-- Drop the Test Data Table
DROP TABLE Test;
GO

SET NOCOUNT OFF;

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of you table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of your table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Lucky P wrote:

Here you go with a complete sample when having the statement as a variable:

That looks great, thanks!

I think I'm going to have to accept that it's not possible with all dynamic sql statements, such as "exec xp_proc1", and take what I can.

Cheers,

Neil

How do I get just one set?

I am joining two tables in an SQL query.
Table 1: item has three fields, id, org_id, and name
Table 2: item_set has five fields item_id, org_id, leftIndex, depth, and
rightIndex
The item_set table is used to help sort structures hierarchically.
For example the following query would return a parent item and all its
children starting at the top of a tree:
Select
Itm1.id as parent_id,
Itm1.name as parent_name,
Is1.leftIndex as parentIndex,
Itm2.id as child_id,
Itm2.name as child_name,
Is2.leftIndex as childIndex,
Is2.depth as degree_of_separation
From
item as itm1
Inner join
Item_set as is1
On (itm1.id = is1.item_id and is1.depth = 0)
And (itm1.org_id = is1.org_id)
Inner join
Item_set as is2
On (is2.leftIndex between is1.leftIndex and is1.rightIndex)
And (is1.item_id <> is2.item_id)
And (is1.org_id = is2.org_id)
Inner join
Item as itm2
On (is2.item_id = itm2.id)
And (itm2.org_id = is2.org_id)
order by itm1.id , itm2.id
The trouble is that there may be different representations of trees for the
same items in the item_set table. One representation of four items might
have leftIndex values 1, 2, 3, 4 for the parent and its three children and
there could be another set of leftIndex values of 20, 21, 22, 24 for the
very same set of items.
My question for the SQL experts out there is how do I write a query to get
only one set of the items such as 1, 2, 3, 4 ? I can't assume that the
sequence will always be starting at the top of the tree, e.g. depth = 0.
27-1006Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Also, rows are not records and columns are not fields, there is not
such things as a magical "id", a vague "name", etc. Read ISO_11179 for
the proper way to name things
CREATE TABLE Items
(item_id INTEGER NOT NULL ,
org_id INTEGER NOT NULL,
PRIMARY KEY(item_id, org_id),
item_name CHAR(15) NOT NULL);
Is this a nested sets model!
CREATE TABLE ItemSets
(item_id INTEGER NOT NULL
org_id INTEGER NOT NULL,
FOREIGN KEY (item_id, org_id),
REFERENCES Items (item_id, org_id)
ON DELETE CASCADE -- guess at biz rule
ON UPDATE CASCADE,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0) ,
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt));
depth is computable, so do not store it. It will get out of synch and
screw up things.
Get a copy of TREES & HIERARCHIES IN SQL and look at chapter about
compare sub-tree structures. I am not going to give you a few
thoiusand words and illustrations in a newsgroup.
The basic idea is pick the root node. find the first subtree and
substract MIN(lft) from the lft and rgt values. Find the second
subtree and repeat the process. UNION ALL the two canonical subtrees
1) if you have a table with exactly duplicated rows. the subtress are
identical
2) if the nodes match, but not the (lft, rgt) pairs, they are different
arrangements of the same nodes
3) If the nodes do not match, but the (lft, rgt) pairs do, the have the
same structure with different nodes.
The pictures will help when you buy the book.
Here is query for depth. Assume an organizational chart in a Nested
Set model.
SELECT COUNT(O2.emp) AS depth, O1.emp
FROM OrgChart AS O1, OrgChart AS O2
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
GROUP BY O1.lft, O1.emp;

How do I get a Fields List with Stored Procedures

If I type in a query I get a fields list. However I do not with a stored
procedure. I have tried pressing the Refresh button. This does not work. I
have tried entering the fields manually, but when I get to the value column,
the expression builder tells me that the dataset has no fields. I can run
the stored procedure in Reporting Services and get the correct results. I
just cannot figure out how to get a dataset to see the fields in a stored
procedure.
I've been trying everything I can think of for two days. Does anyone out
there know how to get the fields to show with a stored procedure?The value will be the same as the field name and the type is databasefield
so for example:
fieldname type value
CREW DatabaseField CREW
AND SO ON
You can't use the expression to pick fields because you have not entered the
fields manually. I find I have to enter my fields manually if I have temp
tables used in my stored proc
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||They should show automatically when you execute the dataset on the Data tab.
Are you sure you have a command type of 'storedprocedure'?
Worst case scenario try deleting your dataset and recreating it.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:6C363085-B23B-4B5B-AC32-1C0C1DA4AF3F@.microsoft.com...
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work.
> I
> have tried entering the fields manually, but when I get to the value
> column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||I have tried that. I have also tried right-clicking on the fields list pane
and adding fields. Either way when I try to pull the fields into the report,
they get put in there as SUM (Fields.fieldname.value). When I edit the sum
function away, I get an error saying that the field is not in the dataset.
When I hit Refresh, the fields disappear.
There are no temp tables or table variables in the stored procedure. It
returns one resultset.
How do I delete a dataset?
"John Grant" wrote:
> The value will be the same as the field name and the type is databasefield
> so for example:
> fieldname type value
> CREW DatabaseField CREW
> AND SO ON
> You can't use the expression to pick fields because you have not entered the
> fields manually. I find I have to enter my fields manually if I have temp
> tables used in my stored proc
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I fought with the same issue for 2 days when first working with sprocs in
rs. after you set the command type to stored procedure, run it it once from
the data window. Then refresh it. that did the trick for me. if you have
parameters, don't forget to include them as well.
--
John Cleveland
Network Manager
Urban systems Ltd
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||mmmmmmmmmmm, do you have set nocount on inside your stored proc?
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have the button to delete dataset while in the data tab
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have several good suggestions. Here is a list:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
5. If your stored procedure calls another stored procedure then try this:
add Set FMTONLY Off (the following is from Simon Sabin a SQL Server MVP).
"The issue with RS is that the rowset of the SP is defined by calling the SP
with SET FMTONLY ON because Temp tables don't get created if you select from
the temp table the metadata from the rowset can't be returned. This can be
worked around by turning FMTONLY OFF in the SP."
One of the 5 above should solve your problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:59D6F2CC-1CE4-450F-9D55-74B724CB5AAF@.microsoft.com...
>I have tried that. I have also tried right-clicking on the fields list
>pane
> and adding fields. Either way when I try to pull the fields into the
> report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the
> sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
>> The value will be the same as the field name and the type is
>> databasefield
>> so for example:
>> fieldname type value
>> CREW DatabaseField CREW
>> AND SO ON
>> You can't use the expression to pick fields because you have not entered
>> the
>> fields manually. I find I have to enter my fields manually if I have
>> temp
>> tables used in my stored proc
>> "Liz" wrote:
>> > If I type in a query I get a fields list. However I do not with a
>> > stored
>> > procedure. I have tried pressing the Refresh button. This does not
>> > work. I
>> > have tried entering the fields manually, but when I get to the value
>> > column,
>> > the expression builder tells me that the dataset has no fields. I can
>> > run
>> > the stored procedure in Reporting Services and get the correct results.
>> > I
>> > just cannot figure out how to get a dataset to see the fields in a
>> > stored
>> > procedure.
>> >
>> > I've been trying everything I can think of for two days. Does anyone
>> > out
>> > there know how to get the fields to show with a stored procedure?|||I'm afraid that none of this is working. I deleted the dataset and recreated
it. I commented out the "Set Nocount On". I ran the stored procedure and
got the result set. I pressed Refresh. No good.
Any other ideas?
"John Cleveland" wrote:
> Liz,
> I fought with the same issue for 2 days when first working with sprocs in
> rs. after you set the command type to stored procedure, run it it once from
> the data window. Then refresh it. that did the trick for me. if you have
> parameters, don't forget to include them as well.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I know how frustrating it was for me to get it going as well. So if you
would like you can contact me dierctly. I'm not with MS at all, but do know
how to get it to work. Use my contact info below if you wish.
--
John Cleveland
Network Manager
Urban systems Ltd
office: (250) 762-2517
jcleveland@.urban-systems.com
"Liz" wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
> >
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||On Apr 12, 6:02 pm, Liz <L...@.discussions.microsoft.com> wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
>
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> > "Liz" wrote:
In your SP make a select * into temptable from ...
and then in the last line write select * from temptable
When you do that if you refresh the fields appear, after that just
remove the into command and the "select * from temptable" but you
can't refresh otherwise the fields disappear.
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure... Hide quoted text -
> - Show quoted text -

How do I get a count of each set of results?

My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.

i.e.

Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2

Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...

Is there straightforward way of doing this in SQL?

ThanksIn SQL Server 2005:

select
[Type],
count() over (partition by [Type]) as [Count]
from T

In SQL Server 2000:

select
[Type],
(select count(*)
from T as Tcopy
where Tcopy.[Type] = T.[Type]
) as [Count]
from T

(both solutions untested - for a better chance at tested
solutions, include create table and insert statements that
can be cut and pasted into a query editor.)

Steve Kass
Drew University

kasterborus@.yahoo.com wrote:

Quote:

Originally Posted by

My query returns a table of results, I would like to add a count column
that contains the number of each result type returned.
>
i.e.
>
Type Count
1 3
1 3
1 3
2 2
2 2
3 4
3 4
3 4
3 4
4 2
4 2
>
Because there are 3 of type 1, 2 of type 2, 4 of type 3 etc...
>
Is there straightforward way of doing this in SQL?
>
Thanks
>

How do I get a count of all records returned.

I'm trying to put the total number of records returned from from a query in the bottom of our report. I don't want to do a count(*) in my sql stmt.

thanks.

Hello,

Try this in your table footer:

=CountRows()

Hope this helps.

Jarret

|||putting =CountRows() in my footer give me 1. What may I be missing here?|||

Use =CountRows("DataSet1") where DataSet1 is the name of your dataset that is bound to your table.

Shyam

|||

Try this:

=countDistinct(Fields!name.Value)

It works for me.

How do I find out what query someone ran?

Hello All,

We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?

Thanks in advance!

Kenny, your best bet is to fire up SQL Profiler and do some filtering so you only see the queries being executed by the application.

Thanks,
Sam Lester (MSFT)

How do I find IDENTITY columns on Table using T-SQL

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?

Found it, a little obscure:

SELECT obj.[name], col.[name], col.[colstat], col.*
FROM [syscolumns] col
JOIN [sysobjects] obj
ON obj.[id] = col.[id]
WHERE obj.type = 'U'
AND col.[status] = 0x80
ORDER BY obj.[name]

Does anyone know a way of doing this using an INFORMATIO_SCHEMA view?

|||I posted that sometime ago:

SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'

Mit Information_schema views from
http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx

select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1
order by TABLE_NAME

HTH, Jens Suessmeyer.

http://www.sqlserver2005.,de

|||

Here is some more ( in technicolor ;-) )

USE northwind
GO

DECLARE @.tableName VARCHAR(50)
SELECT @.tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @.tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO

DECLARE @.intObjectID INT
SELECT @.intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@.intObjectID, 'TableHasIdentity'),0) AS HasIdentity

Denis the SQL Menace

http://sqlservercode.blogspot.com/

How do I edit a query through code?

Hi!

Can someone tell me how or where I can find information on editind an SQL query through code?

I want to be able to run a user-defined lookup, where the user can choose what the query will look for.

Thanks in advance.

Guy

Your description is not very detailed. Do you want to create a dynamic query ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hi!

Thanks for the advice.

I was in a rush when I posted that, but I can provide some more detail now.

I want to make a query, where the user can change what the search criteria is.

I want the user to enter a string literal value from a text box or drop-down list, and then view the query with that criteria.

This needs to be done through code, and ideally work in Visual Web Developer as well as Visual Basic.

Thanks.

|||

check this

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

Madhu

|||

Hi.

Thanks for that link.

I will have a look when I have time.

Guy

How do I do this query

I have 3 tables
Users
Customerid - guid
userid - varchar
customertoworker
customerid - FK
workerid - FK
extract
customerid - guid
data layout
extract
EACFF367-8E73-4C4C-A9A7-036A0D7E57AB
customertoworker
EACFF367-8E73-4C4C-A9A7-036A0D7E57AB, 90ED4230-2711-4019-A2AD-071D52891BBF
EACFF367-8E73-4C4C-A9A7-036A0D7E57AB, 873762AB-34BD-455B-83B2-137645BE3331
users
EACFF367-8E73-4C4C-A9A7-036A0D7E57AB , RAINMAN
90ED4230-2711-4019-A2AD-071D52891BBF , BOB
873762AB-34BD-455B-83B2-137645BE3331 , JAMES
I want the data to appear in 1 row. I want to know the workers that all
users in the extract have in 1 row.
ex
RAINMAN, BOB, JAMES
select x.userid, z.userid,x.customerid
from cdtextractactivemlm x
inner join customertoworker y
on x.customerid = y.customerid
inner join useridentifier z
on z.customerid = y.workerid
order by y.customerid
The query above gives me 2 rows... can someone shed some light...
thanks for the help.Look at this example (and remember: this breaks normalization and should be
used for presentation purposes only):
http://milambda.blogspot.com/2005/0...s-as-array.html
ML
http://milambda.blogspot.com/|||niv
I understood from your narritave nothing, sorry
If you say that your query returns two rows , so try using TOP 1 clause to
get only one row as well as using ORDER BY clause to sort the output
"niv" <niv@.discussions.microsoft.com> wrote in message
news:D76674C3-3CB4-43F8-B0E0-CA8A85E3DC2F@.microsoft.com...
>I have 3 tables
> Users
> Customerid - guid
> userid - varchar
> customertoworker
> customerid - FK
> workerid - FK
>
> extract
> customerid - guid
> data layout
> extract
> EACFF367-8E73-4C4C-A9A7-036A0D7E57AB
> customertoworker
> EACFF367-8E73-4C4C-A9A7-036A0D7E57AB, 90ED4230-2711-4019-A2AD-071D52891BBF
> EACFF367-8E73-4C4C-A9A7-036A0D7E57AB, 873762AB-34BD-455B-83B2-137645BE3331
> users
> EACFF367-8E73-4C4C-A9A7-036A0D7E57AB , RAINMAN
> 90ED4230-2711-4019-A2AD-071D52891BBF , BOB
> 873762AB-34BD-455B-83B2-137645BE3331 , JAMES
> I want the data to appear in 1 row. I want to know the workers that all
> users in the extract have in 1 row.
> ex
> RAINMAN, BOB, JAMES
> select x.userid, z.userid,x.customerid
> from cdtextractactivemlm x
> inner join customertoworker y
> on x.customerid = y.customerid
> inner join useridentifier z
> on z.customerid = y.workerid
> order by y.customerid
> The query above gives me 2 rows... can someone shed some light...
> thanks for the help.|||I am using sql 2000.
This is a function written in sql?
Are there any others ways of getting the data into the format I want?
"ML" wrote:

> Look at this example (and remember: this breaks normalization and should b
e
> used for presentation purposes only):
> http://milambda.blogspot.com/2005/0...s-as-array.html
>
> ML
> --
> http://milambda.blogspot.com/|||Pure T-SQL user-defined function. Works in SQL 2000 and above.
The usual way would be to do it on the client (in the application tier).
ML
http://milambda.blogspot.com/|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
I will not even comment on that insane use of GUIDs and the single
character user_id, but what you are doing is a violation of 1NF.
Display is done in the front and not in the database. You are
basically not writing SQL at all.

How do I do this in SS2000?

Hi all,

I have a simple query which returns all the names of products attached to a particular order.

select a.orderid, b.product_name
from orders a, order_items b
where a.orderid = b.orderid

Say it returns this data: (sorry about the formatting!)

orderid product_name
===== =========
001234 Sweater (Black)
001234 Trousers (Large)
001234 T-Shirt (Pink)

What I want to do is, instead of getting 3 rows back, I want to roll up all matching values (of product_name) from the order_items table into a simple string seperated by a comma. So, for the data above I would get a single row with the orderid and a string containing something like this: "Sweater (Black), Trousers (Large), T-Shirt (pink)".

I'm sure there's an easy way to do this in SQL Server 2000 but I've not been able to work out how to do this and I couldn't see anytihng in SQL Books Online..

TIA for any help...

MikeAre you prepared to use Analysis Services?|||Look at this. You may want to put it into a function, but this is the general idea

declare @.s varchar(8000)
select @.s = b.product_name + ', ' + COALESCE(@.s, '')
 from orders a, order_items b
 where a.orderid = b.orderid
if @.s is not null
 set @.s = substring(@.s, 1, LEN(@.s) - 1)
else
 set @.s = ''
select @.s

|||Thanks for that... that works fine but I need to fine tune it a bit. At the moment it gives me all products for all orders whereas I need it to give me just the product names for each unique order. I tried using a "GROUP BY a.orderid" but it won't let me use the product_name column in this way.

I also want to be able to select the columns I need from the first table such as orderid, order_date etc.

I've experimented with both but can't seem to really get it to work... a little more help would be much appreciated!

Cheers,

Mike

PS: pkr - no I can't really use Analysis Services as this is part of a stored procedure for a web app that also has to run on Oracle so it needs to be fairly standard ANSI SQL.|||Assuming you don't know how many products you've got for a an order its difficult to write a single query. This is my suggestion.
1. Create a temp table with the OrderID and a "csv" text column, defaulted to ''
2. Insert the unique set of orderids into the temp table
3. Run a query that UPDATEs the csv column with itself plus the "," + product name|||Thanks pkr... in the end I wrote a function which is passed the order id and reads the values of the products into a cursor. It then builds the string of product names and returns it. It seems to work very well and I've learnt quite a lot about SQL functions that I didn't know before. I'm not at work so I can't post it but I will on Monday so possibly someone in the future can see how to do this.

Thanks for the help.

Mike|||I use that code within a function and it works fine. Forgot to suggest that. I would stay away from cursors unless you have to use them. There is a significant performance hit. I'd use the query above and avoid the cursor.|||Replacing cursors is nearly always a good idea. However, be careful with funcs, you can basically end up doing the same thing as a cursor. If you code it "incorrectly" the function will run for each row in the set, therefore the perf will be like a cursor anyway!|||Luckily the table I run the function on will not have that many rows in it at one time. I also looked at the stats for a few orders and it does only seem to be reading the ones it needs rather than processing the whole table which is good.

Will post the function when I get to work today... then people can tell me if could do it any better.

Mike.

How do I do an UPDATE in this complex query?

Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmdtn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB , but I am restricted to read-only. (acmdtn_RECID is the primary key)
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to run reports on the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get all of the the latest "id" records as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.

I would suggest changing this stored procedure to a function or perhaps making a version of this that is a function. Hang on and I'll try to show you. Maybe something like:

create function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID
FROM ( SELECT dbo.acmdtn.*,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum
) Rank
WHERE rn = 1
)

|||

I mocked this up with this table and data:

create table dbo.acmdtn
( id integer,
efctv_from_dt datetime,
efctv_to_dt datetime,
modify_ts datetime,
extractno integer,
Active integer,
acmdtn_RECID integer
)
go

insert into acmdtn
select 1, '1/1/7', '2/1/7', '1/1/7', 1, 0, 1 union all
select 1, '2/1/7', '4/1/7', '1/15/7', 2, 0, 1 union all
select 1, '4/1/7', '1/1/8', getdate(), 3, 0, 1 union all
select 2, '1/1/7', '3/1/7', '1/1/7', 1, 0, 2 union all
select 2, '3/1/7', '7/1/7', '2/15/7', 2, 0, 2
select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 0 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 0 2
*/

I tested the UPDATE like this:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID
FROM ( SELECT dbo.acmdtn.*,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum
) Rank
WHERE rn = 1
)

go

update selectCurrent_Acmdtn (1)
set Active = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 2
*/

Something to consider is the use of:

SELECT dbo.acmdtn.*,

This is dangerous because it is not intuitively obvious whether or not this statement will return all columns of the dbo.acmdtn table. This is because the meaning of this select statement is determined at function compile time and NOT at function execution time. Therefore, I strongly suggest that you alter this statement to explicitly list all columns returned by the select statement.

This problem comes into play whenever the structure of the table is altered because at that time the columns returned by this select are no longer the same as the columns contained in the table.

In addition, if the point of this function is only to perform the update then it would be better to eliminate from the select statement any columns that do not contribute to the update.

|||

Thanks alot for the reply. I'll give that a try.

(I think I screwed up a little because I posted this question multiple times. I kept getting a message that "the administrator may have deleted your post" and I couldn't find it on a search initially, so I kept on posting!)

The only thing that I didn't mention is that I have acmdtn_RECID as the primary key (It's an identy field that gets assigned when I do the import from the raw data, because the original raw data didn't have any primary keys).

Would that change your soloution any?

Thanks again.

|||

No, it will not really change this solution; however, you are correct in identifying that my test data would not be valid. It would be good to have an index based on extractno because this is what is used here for filtering the data.

Again, it would be good to eliminate the SELECT * syntax to pare down some of the data.

|||

Create a stored procedure to apply the UPDATE.

create PROCEDURE [dbo].[update_Current_acmdtn]

@.extractNum char(10)

AS

SET NOCOUNT ON;

with cte

as

(

SELECT

dbo.acmdtn.*,

row_number() OVER (partition BY id ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM

dbo.acmdtn

WHERE

extractno > @.extractNum

)

update cte

set Active = 1

where rn = 1

return @.@.error

go

AMB

|||

Hunchback presents a good way of performing the update. I realized that I need to factor out a piece from my query so I amended my function similar to what Hunchback did. Also, I eliminated the SELECT * syntax:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( select id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum

)

go

update selectCurrent_Acmdtn (1)
set Active = 1
where rn = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 2
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 11:10:56.793 3 0 3
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 4
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 5
*/

Thanks, Hunchback. :-)

|||

Thanks alot guys,

I got it to work.

How do I do a like '%<string>%' equivalent in FTS

How do the following equivalent query in FTS.

select brandName from Brand b where b.brandName like '%lf%'

returns "Alfa Brand"

select brandName from Brand b where CONTAINS (b.brandName, '"*lf*"')
returns Zero rows

pls help, I just can't FTS to return the row.

thx
jt

I can replicate this error. Your syntax is correct but it looks like the fulltext engine is ignoring the first wildcard (*)

Therefore your query turns into

select brandName from Brand b where CONTAINS (b.brandName, '"lf*"')

I'm not sure if this is standard behaviour across all installs of SQL. I'll see if i can find out.

|||I don't think FTS supports inter-word searching
|||There are Specific rules you will have to go with if you use FTS, depening on the wordbreaker you can use * at the beginning of a words. This depends on the wordbreaker which is used for the column / attribute.

e.g. if you search for *race in columns which contain TRACE and FASTRACE, you will find the second word as it was broken into FAST & RACE.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How do I determine the table from index name

I ran a query to identify indexes with fragmentation problems, and it gives
me the index names. I cannot tell from the names what tables or views are
being indexed. I have been wondering through the system views, but so far
nothing jumps out at me (i.e. sys.table_indexes).
I don't think that this is something that has to be solved, since I rebuild
indexes once a week, but I would like to know: Starting wtih
sys.indexes.object_id, how can I determine the table or view name?
What query/mechanism are you using to generate the list of fragmented
indexes?
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||> Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
One method:
SELECT
name AS index_name,
OBJECT_NAME(object_id) AS object_name
FROM sys.indexes
Hope this helps.
Dan Guzman
SQL Server MVP
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I
> rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||First, you might be re-inventing the wheel. You will find code in Books Online which does
defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
2005, sys.dm_db_index_physical_stats.
You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
database id as second parameter (very useful).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>I ran a query to identify indexes with fragmentation problems, and it gives
> me the index names. I cannot tell from the names what tables or views are
> being indexed. I have been wondering through the system views, but so far
> nothing jumps out at me (i.e. sys.table_indexes).
> I don't think that this is something that has to be solved, since I rebuild
> indexes once a week, but I would like to know: Starting wtih
> sys.indexes.object_id, how can I determine the table or view name?
|||My question began with a defragmentation query that I found in an Sql 2005
textbook, which produced a list of six suspects with OBJECT_NAME
(dt.object_id) = queue_messages_1003150619 or something similar. Since that
certainly didn't match any table or view in the database, I assumed it was
the name of an index. But the response from Dan Guzman includes a query that
shows that it is actually the name of the table or view - which I just said
doesn't exist. So now I'm really confused.
"Tibor Karaszi" wrote:

> First, you might be re-inventing the wheel. You will find code in Books Online which does
> defragmentation based on fragmentation level. For 2000, look in the DBCC SHOWCONTIG topic, and for
> 2005, sys.dm_db_index_physical_stats.
> You can use the OBJECT_NAME function to resolve id to name. As of 2005 with sp2, this even takes a
> database id as second parameter (very useful).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:8C739C23-B253-48C8-B78D-6D36745C3F29@.microsoft.com...
>
|||Probably a service broker queue. Perhaps you are using SB explicitly or for the internal usage of
SQL Server. One could argue that these should be hidden from us, I guess...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...[vbcol=seagreen]
> My question began with a defragmentation query that I found in an Sql 2005
> textbook, which produced a list of six suspects with OBJECT_NAME
> (dt.object_id) = queue_messages_1003150619 or something similar. Since that
> certainly didn't match any table or view in the database, I assumed it was
> the name of an index. But the response from Dan Guzman includes a query that
> shows that it is actually the name of the table or view - which I just said
> doesn't exist. So now I'm really confused.
> "Tibor Karaszi" wrote:
|||> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
I agree it's probably a queue, especially with that object name.
One could also argue not to hide these objects because objects other than
tables and views might be interesting too. If interested only in views and
tables, Bev can join to sys.objects and specify WHERE type IN('U', 'V').
Similarly, a join to object type-specific tables (sys.tables, sys.views) can
provide similar results.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:C180E8C0-6A2B-4881-A850-6872EC4A8F04@.microsoft.com...
> Probably a service broker queue. Perhaps you are using SB explicitly or
> for the internal usage of SQL Server. One could argue that these should be
> hidden from us, I guess...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:BEC6F9B0-624B-420B-AF23-CB3603F2C7E7@.microsoft.com...
>