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 loop through a record set in a stored procedure?

Below is a stored procedure that designed to populate a drop down menu system on a website. It works fine as long as the 'id's in the first select start at 1 and are sequential. It fails to grab all the sub tables if the ids are not sequential. So, how do I structure the loop so that the WHERE clause usesnot the loop iterator, but rather, the ids from the first Select statement.

Alternatively, is there a more elgant approach that will return the same set of recordsets?

Any help would be much appreciated
Thanks

ALTER PROCEDUREdbo.OPA_GetMenuItems
AS
Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = @.i)
ORDER BYmainNavId, sort
end

RETURN

Here's one way: You could get the resultset into a table variable. Add an additional column in the table variable and mark it off as processed after each record.

ALTER PROCEDURE dbo.OPA_GetMenuItems
AS
Declare @.i tinyint ,
@.tc tinyint
Set @.i = 1

/* Select for top level menu items*/

DECLARE @.t tabke (id int, label varchar(100), url varchar(100), sort varchar(100), Processed char(1) )
DECLARE @.minid int

INSERT INTO @.t
SELECT
id, label, url, sort, 'N'
FROM
mainNav
ORDER BY
sort

WHILE EXISTS (SELECT 1 FROM @.t WHERE Processed = 'N')
BEGIN
--Get the first record
SELECT
@.minid = id
FROM
@.t
WHERE
Processed = 'N'
ORDER BY
id

--your processing code
/*
Select for submenu items
SELECT id, label, url, sort, mainNavId
FROM SubNav
WHERE (mainNavId = @.i)
ORDER BY mainNavId, sort
end
*/

--make sure you mark the record as Processed
UPDATE
@.t
SET
Processed = 'Y'
WHERE
id = @.minid

END

|||

Depends on if you want the items to come back in one or multiple recordsets.

Here's a single recordset:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav
UNION
SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's two recordsets:

SELECT id,label,url,sort,NULL as mainNavId
FROM mainNav

SELECT id,label,url,sort,mainNavId
FROM SubNav
ORDER BY mainNavId,sort

Here's multiple recordsets:

Declare@.itinyint,
@.tctinyint
Set@.i = 1

/* Select for top level menu items*/

SELECTid, label, url, sort
FROMmainNav
ORDER BYsort

Set@.tc = @.@.rowcount

while@.i <= @.tc

begin
Set@.i = (@.i + 1)

/* Select for submenu items
SELECTid, label, url, sort, mainNavId
FROMSubNav
WHERE(mainNavId = (SELECT TOP 1 FROM (SELECT TOP @.i id,sort FROM mainNav ORDER BY sort,id) ORDER BY sort DESC,id DESC))
ORDER BYsort
end

You can also do it using a cursor if you want, which would probably be easier/faster if you have a LOT of menu items, but I'm guessing that since it's a menu, you are only talking about 5-20 items and not thousands.

|||

Basic cursor logic works like this, but the syntax is NOT correct, I haven't used a cursor in a LONG time:

DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id

OPEN MyCursor
READ NEXT FROM MyCursor INTO @.id
WHILE (@.@.FETCHSTATUS<>-2)
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav WHEREmainNavID=@.id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @.id
END
CLOSE MyCursor
DEALLOCATE MyCursor

That should be pretty close, all except for the READ NEXT FROM... I forget the command/syntax, that definately is not correct. And the cursor declaration is a bit off, but close.

|||

The multiple recordsets approach is the one I'm after - I need an .Net data to spit out the menus back in C#. I tryed your example and it did not compile - Sql came back with:

Incorrect Syntax near keyword 'From'.
Line 24: incorrect syntax:near'@.i'.

any ideas?

|||Ah, you must be using SQL Server 2000, yeah, that's not valid on 2000, only 2005. I'm not sure what your code looks like, but I would do the single resultset approach. Just loop through the resultset, and whatever you would do when you get a new resultset, just do it when the mainNav field changes value.|||

Thanks for your help. I actually took a different direction and managed to get around the problem with a left join...

Thanks again...

how do i know which SP am I working on?

How do I know which service pack of SQL 2000, am I working on?
Is there any 'select' for this?
Pls help.
regards
KP
http://support.microsoft.com/default...b;en-us;321185
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> How do I know which service pack of SQL 2000, am I working on?
> Is there any 'select' for this?
> Pls help.
> regards
> KP
|||select serverproperty('ProductLevel')
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
> How do I know which service pack of SQL 2000, am I working on?
> Is there any 'select' for this?
> Pls help.
> regards
> KP
|||hi jasper,
thanx for quick response. it works.
now tell me, if i know that i'm working on SP4 now, is there any way to
downgrade to SP3? There are a lot of issues experienced in SP4 ...
regards
KP
"Jasper Smith" wrote:

> select serverproperty('ProductLevel')
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:96D7A601-8C86-4844-A8F7-256DDB987397@.microsoft.com...
>
>
|||Read the readme:
To revert to a pre-SP4 version of SQL Server
1.. Detach all user databases. For more information, see "How to attach
and detach a database (Enterprise Manager)" in SQL Server Books Online.
2.. Uninstall SQL Server. In Control Panel, double-click Add/Remove
Programs, select the instance of SQL Server that you want to uninstall, and
click Remove.
3.. Reinstall SQL Server 2000 from the CD-ROM or from the location where
you originally installed SQL Server.
4.. Apply any service packs and hotfixes that were installed before
Database Components SP4.
5.. Restore the databases master, msdb, and model from the last backup
that was created before you installed. If the location of the data files has
not changed, this restoration automatically attaches any user databases that
were attached at the time the backup was created.
6.. Attach any user databases that were created after the last backup of
the master database.
7.. Configure replication if necessary.
Warning When you revert to the pre-SP4 version of SQL Server 2000, all
changes made to the databases master, msdb, and model since applying SP4 are
lost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
schrieb im Newsbeitrag
news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...[vbcol=seagreen]
> hi jasper,
> thanx for quick response. it works.
> now tell me, if i know that i'm working on SP4 now, is there any way to
> downgrade to SP3? There are a lot of issues experienced in SP4 ...
> regards
> KP
> "Jasper Smith" wrote:
|||Also if you're having issues with SP4 then do contact PSS to open a support
case
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...[vbcol=seagreen]
> hi jasper,
> thanx for quick response. it works.
> now tell me, if i know that i'm working on SP4 now, is there any way to
> downgrade to SP3? There are a lot of issues experienced in SP4 ...
> regards
> KP
> "Jasper Smith" wrote:
|||what kind of issues are seen here?is there any known issue in SP4 that it's
not recommended to be installed?
"Jasper Smith" wrote:

> Also if you're having issues with SP4 then do contact PSS to open a support
> case
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:CD3FF987-647B-4642-A95E-A1968D71F100@.microsoft.com...
>
>

How do i join 2 databases on seperate servers?

Trying to extract data from two databases on different servers... I know how
to write Select statements to get data from them seperately but have not
managed to join the two together.
Can anyone help with this issue?
Thank you
ck
use the four-part-notation
SELECT Somecolumns
>From SomelocalTable T1
INNER JOIN LinkedServername.Databasename.Ownername.SomeTableN ame T1
ON T1.SomejoinColumn = T2.SomejoinColumn
HTH, Jens Suessmeyer.

How do i join 2 databases on seperate servers?

Trying to extract data from two databases on different servers... I know how
to write Select statements to get data from them seperately but have not
managed to join the two together.
Can anyone help with this issue?
Thank you
ckuse the four-part-notation
SELECT Somecolumns
>From SomelocalTable T1
INNER JOIN LinkedServername.Databasename.Ownername.SomeTableName T1
ON T1.SomejoinColumn = T2.SomejoinColumn
HTH, Jens Suessmeyer.

How do I insert values into CE from a DataGridView row/rows?

Hi all... can someone tell me why this does not work and how to make it work?

SELECT SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM,
PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM ProposalDetail
INSERT INTO temp
(
SQLKey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList,
PropDCost, PropDGMDollar, PropDGM, PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus,
create_timestamp, update_timestamp,update_originator_id, create_date
)
VALUES
(
'1', '3/20/2007 8:13:10 AM', 'SomeUser', 'b1', '4', '0761046', 'APP', 'Miscellaneous Options',
'54.000', '27.00', '27.00', '50.00', '54.00', '54.00', '54.00', '1', 'Active', '3/20/2007 8:13:10 AM', '3/20/2007 8:13:10 AM', '0',
'3/20/2007 8:13:10 AM'
)
WHERE (PropDNumb = 'b1') AND (PropDRevNumb = '1')

Thanks a ton!

figured it out and oddly enough this is solid and works great...

'SQL STATEMENT......

Dim conGridInsert As New SqlCeCommand

conGridInsert.CommandText = "INSERT INTO ProposalDetail" & _

"(SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date)" & _

"SELECT SQLkey, CreateDateTime, Alias, PropDNumb, '" & ComboRevision.Text & "' as PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date" & _

" " & _

" FROM ProposalDetail ProposalDetail_1 " & _

" " & _

" WHERE (PropDNumb = '" & txtProposalNum.Text & "') AND (PropDRevNumb = '" & OldRevNumber & "')"

conGridInsert.Connection = CnGridInsert

CnGridInsert.Open()

conGridInsert.ExecuteNonQuery()

CnGridInsert.Close()

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 identify the replication generated column(s) in a table

I need to dynamically generate a select command that does not include
replication generated columns. Is there a way to do this? I could use
GetOleDbSchemaTable and filter for column names with "rowguid" but that does
not seem robust to me. Is there a system table or stored procedure that can
help? Dropping a replication subscription and publication removes these
columns so I suspect that the information is available somewhere.
Thanks
Dropping a publication doesn't drop the guid columns added to a merge
publication. As far as I know we can't identify those columns added.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||What about something like this:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and name <>'rowguid'
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
print @.mystring
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kohn" <Kohn@.discussions.microsoft.com> wrote in message
news:915755D4-E2FC-4790-B0E8-6E493DE1BA4F@.microsoft.com...
>I need to dynamically generate a select command that does not include
> replication generated columns. Is there a way to do this? I could use
> GetOleDbSchemaTable and filter for column names with "rowguid" but that
> does
> not seem robust to me. Is there a system table or stored procedure that
> can
> help? Dropping a replication subscription and publication removes these
> columns so I suspect that the information is available somewhere.
> Thanks
>
|||Hi Hilary - unfortunately this doesn't work . We could query for the
rowguid column on the table, but that might already have existed prior to
the replication setup. As far as I can tell there isn't a way of knowing if
the rowguid column is added by the replication setup or by a user
beforehand.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It will work for the majority of the cases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O$uP0uJNHHA.3424@.TK2MSFTNGP02.phx.gbl...
> Hi Hilary - unfortunately this doesn't work . We could query for the
> rowguid column on the table, but that might already have existed prior to
> the replication setup. As far as I can tell there isn't a way of knowing
> if the rowguid column is added by the replication setup or by a user
> beforehand.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Another thing to take into account is that replication does not require
the column to be called rowguid. It just requires a column with the
ROWGUIDCOL property set. Maybe that is where you should start looking.
Hilary Cotter wrote:
> What about something like this:
> declare @.mystring varchar(2000)
> set @.mystring='select '
> select @.mystring=@.mystring+' '+name+', ' From syscolumns where
> id=object_id('customers')
> and name <>'rowguid'
> select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
> print @.mystring
>
|||I had a quick look and it seems you have to look for colomns where
syscolumns.colstat = 2. You can also only have one column per table with
the ROWGUIDCOL property set, so you can be pretty sure that is the
column used by replication. So to modify Hilary's query:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and colstat <> 2
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from
customers'
print @.mystring
JE wrote:[vbcol=seagreen]
> Another thing to take into account is that replication does not require
> the column to be called rowguid. It just requires a column with the
> ROWGUIDCOL property set. Maybe that is where you should start looking.
>
> Hilary Cotter wrote:
|||The information for identifying the column with the rowguid property solves
the problem. My app retrieves the information with the GetOleDbSchemaTable
function (see below).
By the way, rowguid columns created by the wizard are removed when dropping
the publication. I suspect it uses the preserve_rowguidcol column in the
sysmergearticles tables.
cn.Open()
Dim t As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
New Object() {Nothing, Nothing, TableName, Nothing})
cn.Close()
cn.Dispose()
Dim SelectRows() As DataRow
SelectRows = t.Select("(DATA_TYPE <> 72) AND
(COLUMN_HASDEFAULT=FALSE) AND (COLUMN_HASDEFAULT=False) AND
((ISNULL(COLUMN_DEFAULT,'T')='T') OR
(COLUMN_DEFAULT<>'(newsequentialid())'))")
Dim SelectListStringBuilder As New System.Text.StringBuilder
For Each r As DataRow In SelectRows
SelectListStringBuilder.Append(r.Item("COLUMN_NAME "))
SelectListStringBuilder.Append(",")
Next
SelectListStringBuilder.Length -= 1
Debug.WriteLine(SelectListStringBuilder.ToString)
Thanks for the help

How do I get uppercase values returned only.

I have a table where inactive names are lower case and active names are
uppercase. Note: Not my design.

Anyways I want to select all names form this table where the name is
uppercase. I see collate and ASCII pop up in searches but the examples
don't seem usable in queries as much as they were for creating tables
and such.

Thanks,
Philselect * from mytable where name = upper(name)

Joe Weinstein at BEA|||No luck with that

Here is my query
SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE (last_nme = UPPER(last_nme))
ORDER BY last_nme, first_nme

I still see lower case names.|||I fixed it with this
where ASCII(last_nme) = (ASCII(UPPER(last_nme))

Thanks for the responses.
Phil|||As Phillip found out... this will only work if you have set your
instance of SQL Server set to be case-sensitive. The default
installation makes the instance NOT case-sensitive.

-Tom.|||Phillip (pputzback@.ECommunity.com) writes:
> No luck with that
> Here is my query
> SELECT *, last_nme AS Expr1, first_nme AS Expr2
> FROM members
> WHERE (last_nme = UPPER(last_nme))
> ORDER BY last_nme, first_nme
> I still see lower case names.

This should do it:

SELECT *, last_nme AS Expr1, first_nme AS Expr2
FROM members
WHERE last_nme COLLATE Finnish_Swedish_CS_AS =
UPPER(last_nme) COLLATE Finnish_Swedish_CS_AS
ORDER BY last_nme, first_nme

You may prefer to use something else than Finnish_Swedish. It's the
CS_AS part that is the important.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How do I get the row number?

I have tryed to find a way to include the row number in a querry but without result.

I have a table select some rows ordered and now I will add a column called rank

How do I get the rownumber in the field rank ?

Thanks in advance

Jan OThe way I'd suggest assigning a row number to your resultset is to INSERT your resultset into a temporary table or table variable which has an IDENTITY column, something like this:

-- set up a table variable to hold the resultset
DECLARE @.myTable (Rank int identity(1,1) primary_key,
 ColumnA varchar(20),
 ColumnB varchar(20)
 )
-- insert the resultset into the table variable
INSERT INTO
 @.myTable
(
 ColumnA,
 ColumnB
)
SELECT
 ColumnA,
 ColumnB
FROM
 someTable
ORDER BY
 someCriteria
-- return the resultset to the calling program in rank order, including the rank column
SELECT
 Rank,
 ColumnA,
 ColumnB
FROM
 @.myTable
ORDER BY
 Rank

Terri|||Thanks,

Have hoped not to do it so, a lot of other calculation dependences.

Jan|||Well, an alternative (which performs poorly) would be at the very bottom of this link:Returning a Row Number in a Query It involves using a subquery for every row in your resultset and could be horrendous performance-wise.

Can you just assign the rank in the front end?

Terri|||I agree with Terri. If possible, assign in the front end. I have used the temp table solution before, as well. It really depends on why you need to have the rownumber. If it is just for display purposes, it should be no problem to create on the front end when binding your resultset.|||Thanks again.

I have started to solve the "problem" in the front end.
I have a datagrid with paging so I have a few lines left.
Got it working whitout paging , but I had prefered asystem rownr from SQL.

But you cant get everything for free :)

Thanks
Jan

How do I get the actual name of a column or table in a sql select statement?

Hello fellow .net developers,

In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist.

Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist.

So, what I need to know is: is there a sql statement that can return this type of information?

Example:

Table Names in Database: Customers, Suppliers

Columns in Customers Table: Name, Phone, Email, Address

I click on the word "Customers" in the first dropdownlist.

I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist.

I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool.

Thanks in advance,

RobertYou might try it this way:

To get table names:

SELECT TABLE_NAME FROM Information_Schema.tables

Of course, that gives you some of those default tables as well like sysconstraints and dtproperties and I'm not sure how to get rid of those without hardcoding it into the query.

To get the Columns:

SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

Hope that helps,
-Ian|||sp_Columns @.TableName afaik|||Well the tips you guys came up with worked really well.

Thanks for the help guys.

Basically I took Ian's idea and played with it in SQL Server's Enterprise Manager.

I found that if you say:
SELECT * FROM Information_Schema.tables

instead of:
SELECT TABLE_NAME FROM Information_Schema.tables

you can see all the information available from information_schema.tables

When I looked at the Table_Type column, I noticed that a value of "BASE TABLE" gives you all the user tables plus dtproperties. So I made my where statement filter out the table name dtproperties and keep only the base tables table type.

here is the final sql statement I used for getting the table names:
SELECT TABLE_NAME
FROM Information_Schema.tables
WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <> 'dtproperties')

Also, Ian's columns idea:
SELECT COLUMN_NAME FROM Information_Schema.Columns WHERE TABLE_NAME = 'Customers'

worked great without having to tweak it at all.

once again thanks for the help

Robert|||I still think sp_tables is better. It's a procedure with all the execution paths compiled. too all their own though.
----

sp_tables
Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause).

Syntax
sp_tables [ [ @.table_name = ] 'name' ]
[ , [ @.table_owner = ] 'owner' ]
[ , [ @.table_qualifier = ] 'qualifier' ]
[ , [ @.table_type = ] "type" ]

Arguments
[@.table_name =] 'name'

Is the table used to return catalog information. name is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported.

[@.table_owner =] 'owner'

Is the table owner of the table used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If the owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server?, if the current user owns a table with the specified name, the columns of that table are returned. If the owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified name owned by the database owner. If one exists, the columns of that table are returned.

[@.table_qualifier =] 'qualifier'

Is the name of the table qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

[,[@.table_type =] "'type'"]

Is a list of values, separated by commas, that gives information about all tables of the table type(s) specified, including TABLE, SYSTEM TABLE, and VIEW. type is varchar(100), with a default of NULL.

Note Single quotation marks must surround each table type, and double quotation marks must enclose the entire parameter. Table types must be uppercase. If SET QUOTED_IDENTIFIER is ON, each single quotation mark must be doubled and the entire parameter must be surrounded by single quotation marks.

Return Code Values
-----
A. Return a list of objects that can be queried in the current environment
EXEC sp_tables

B. Return information about the syscolumns table in the Company database
EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"
-----|||kragie,

I agree about using Stored Procedures as much as possible, especially if you don't have to code it yourself. There are so many benefits: speed, security, reusability, etc...

The reason why I didn't use the sp_tables procedure was because I wanted to exclude a couple tables (dtproperties and a settings table) that are considered to be user tables from being put in the dropdownlist on the web page. The only way i could figure out a way to do this was to write the select statement manually.

I plan on writing a custom stored procedure that will either use the custom select statement or further filter the results of the sp_tables procedure to get the data. That way i get a speed boost and more flexibility with what data I'm playing with.

This morning I was just trying to figure out if any of this stuff is even possible, now that I know it is I plan on refining the solution so it isn't inefficient.

Once again thanks for the suggestions!

BTW, your signature is hilarious. I couldn't stop laughing for 15 seconds.

How do I get SQL Server version (numbers only) programatically

Hi
I want to get SQL Server version. I know this way: Select @.@.version
But this gives me a very long string, i.e.
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 18 2006
00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on
Windows
NT 5.0 (Build 2195: Service Pack 4)
i want this value in a c++ application, and depending upon that i have
to fire query.
I just want the the major version number 6.0 ,7.0 , 8.0 or whatever
maybe.
How do I get these numbers only?
Regards,
Ravi ShankarFor the more recent versions of SQL Server, you can use SELECT
SERVERPROPERTY('ProductVersion'). Otherwise, you'll need to parse the
@.@.VERSION string. Transact-SQL example:
DECLARE @.Version nvarchar(125)
IF SERVERPROPERTY('ProductVersion') IS NOT NULL
BEGIN
SELECT SERVERPROPERTY('ProductVersion')
END
ELSE
BEGIN
SET @.Version =
SUBSTRING(@.@.VERSION, CHARINDEX('- ',
@.@.VERSION) + 2, 13)
SET @.Version = LEFT(@.Version, CHARINDEX(' ',
@.Version))
SELECT @.Version
END
Hope this helps.
Dan Guzman
SQL Server MVP
<ravidhari@.gmail.com> wrote in message
news:1156937494.514331.282750@.m73g2000cwd.googlegroups.com...
> Hi
> I want to get SQL Server version. I know this way: Select @.@.version
>
> But this gives me a very long string, i.e.
>
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 18 2006
> 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on
> Windows
> NT 5.0 (Build 2195: Service Pack 4)
> i want this value in a c++ application, and depending upon that i have
> to fire query.
> I just want the the major version number 6.0 ,7.0 , 8.0 or whatever
> maybe.
> How do I get these numbers only?
>
> Regards,
> Ravi Shankar
>|||SELECT SERVERPROPERTY('ProductVersion')
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Ravi,
You can try using "exec master..xp_msver".
It will return a recordset containing lots of info about your SQL Server
installation. One of the records will contain information about the
"ProductVersion".
Cheers!
SQLCatz

How do I get SQL Server version (numbers only) programatically

Hi
I want to get SQL Server version. I know this way: Select @.@.version
But this gives me a very long string, i.e.
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 18 2006
00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on
Windows
NT 5.0 (Build 2195: Service Pack 4)
i want this value in a c++ application, and depending upon that i have
to fire query.
I just want the the major version number 6.0 ,7.0 , 8.0 or whatever
maybe.
How do I get these numbers only?
Regards,
Ravi ShankarFor the more recent versions of SQL Server, you can use SELECT
SERVERPROPERTY('ProductVersion'). Otherwise, you'll need to parse the
@.@.VERSION string. Transact-SQL example:
DECLARE @.Version nvarchar(125)
IF SERVERPROPERTY('ProductVersion') IS NOT NULL
BEGIN
SELECT SERVERPROPERTY('ProductVersion')
END
ELSE
BEGIN
SET @.Version = SUBSTRING(@.@.VERSION, CHARINDEX('- ',
@.@.VERSION) + 2, 13)
SET @.Version = LEFT(@.Version, CHARINDEX(' ',
@.Version))
SELECT @.Version
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ravidhari@.gmail.com> wrote in message
news:1156937494.514331.282750@.m73g2000cwd.googlegroups.com...
> Hi
> I want to get SQL Server version. I know this way: Select @.@.version
>
> But this gives me a very long string, i.e.
>
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 18 2006
> 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on
> Windows
> NT 5.0 (Build 2195: Service Pack 4)
> i want this value in a c++ application, and depending upon that i have
> to fire query.
> I just want the the major version number 6.0 ,7.0 , 8.0 or whatever
> maybe.
> How do I get these numbers only?
>
> Regards,
> Ravi Shankar
>|||SELECT SERVERPROPERTY('ProductVersion')
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Ravi,
You can try using "exec master..xp_msver".
It will return a recordset containing lots of info about your SQL Server
installation. One of the records will contain information about the
"ProductVersion".
Cheers!
SQLCatz

How do I get a list of tables in T-SQL

Hi,
Is there anything equivalent to Oracle'sSelect * from tab in MS SQL.I have got the answer

select name from sysobjects where type = 'U'

|||That will work, but try one of these 2 alternate methods to assure forward-compaitbility with future SQL Server versions:

EXEC sp_table
SELECT * FROM INFORMATION_SCHEMA.TABLES

Note that each of the 3 options yields slightly different results as they filter the information differently. Generally sys* tables should not be queried directly as they are subject to chage.

Terri

How do I force upper case in a select statement

I want all of the columns in a select statement to be converted to upper
case. What is the proper syntax for that?SELECT UPPER(col1), UPPER(col2), UPPER(col3), ...
FROM dbo.YourTable;
"Thirsty Traveler" <nfr@.nospam.com> wrote in message
news:ubO0jhqeGHA.3692@.TK2MSFTNGP03.phx.gbl...
>I want all of the columns in a select statement to be converted to upper
>case. What is the proper syntax for that?
>

How do I find the oldest record in a table

Hi
In one of my tables in the MS SQL database all records are time stamped.
I want to know what is the oldest record.
I have used SELECT TOP 1 * FROM <table> WHERE Time > May 12 1990...
-and it works because I know the data in the table is newer than 1990 but is
there a more intelligent way of doing it?
(I also think it work because the primary key has an ascending sorting
order).
Thanks for Your help.
Regards
Kjell Arne JohansenIs the time stamp unique in your table?
If so, use:
SELECT TOP 1 * FROM <table> ORDER BY ts DESC
Or
SELECT * FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1)
If it's not unique, use:
SELECT TOP 1 * FROM <table> ORDER BY ts DESC, key DESC
Or
SELECT * FROM T1
WHERE key =
(SELECT MAX(key) FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1))
BG, SQL Server MVP
www.SolidQualityLearning.com
"Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
news:E3jhe.10021$SL4.226180@.news4.e.nsc.no...
> Hi
> In one of my tables in the MS SQL database all records are time stamped.
> I want to know what is the oldest record.
> I have used SELECT TOP 1 * FROM <table> WHERE Time > May 12 1990...
> -and it works because I know the data in the table is newer than 1990 but
> is there a more intelligent way of doing it?
> (I also think it work because the primary key has an ascending sorting
> order).
> Thanks for Your help.
> Regards
> Kjell Arne Johansen
>|||Thank You for your examples.
The time is not unique. I will have to use a combination of time and two
other fields.
Regards
Kjell Arne
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> skrev i melding
news:%23OzlmXGWFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Is the time stamp unique in your table?
> If so, use:
> SELECT TOP 1 * FROM <table> ORDER BY ts DESC
> Or
> SELECT * FROM T1
> WHERE ts = (SELECT MAX(ts) FROM T1)
> If it's not unique, use:
> SELECT TOP 1 * FROM <table> ORDER BY ts DESC, key DESC
> Or
> SELECT * FROM T1
> WHERE key =
> (SELECT MAX(key) FROM T1
> WHERE ts = (SELECT MAX(ts) FROM T1))
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
> news:E3jhe.10021$SL4.226180@.news4.e.nsc.no...
>|||Then order by all three columns, desc... and use Top 1
"Kjell Arne Johansen" wrote:

> Thank You for your examples.
> The time is not unique. I will have to use a combination of time and two
> other fields.
>
> Regards
> Kjell Arne
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> skrev i meldin
g
> news:%23OzlmXGWFHA.3540@.TK2MSFTNGP15.phx.gbl...
>
>

How do I figure out time and date at the same time?

Dear all,
I've got some problems with date and time (very silly, I know)
select * from crm_1 where log < datepart(yyyy,getdate())
and log < datepart(mm,getdate())
and log < datepart(dd,getdate())
The aforementioned query doesn't find this value:
2005-06-09 08:27:17.810
CREATE TABLE [dbo].[CRM_1] (
[ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
[log] [datetime] NOT NULL)
GO
Thanks a lot and regards,I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...ime
.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
> Dear all,
> I've got some problems with date and time (very silly, I know)
> select * from crm_1 where log < datepart(yyyy,getdate())
> and log < datepart(mm,getdate())
> and log < datepart(dd,getdate())
> The aforementioned query doesn't find this value:
> 2005-06-09 08:27:17.810
> CREATE TABLE [dbo].[CRM_1] (
> [ipcliente] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
> [log] [datetime] NOT NULL)
> GO
> Thanks a lot and regards,|||Try this:
select * from crm_1 where datepart(yyyy,log) < datepart(yyyy,getdate())
and datepart(mm,log) < datepart(mm,getdate())
and datepart(dd,log) < datepart(dd,getdate())
Or better yet, do some more reading as Tibor suggests.
ML|||It has been very useful. thanks a milion
"Tibor Karaszi" wrote:

> I've elaborated on that topic here: http://www.karaszi.com/SQLServer/in...i
me.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:59611C1B-3D45-407E-B593-3D4CB5C9A0E8@.microsoft.com...
>

How do I exclude null fields?

Is there a way to write a select statement that will pull only fields that are not null?

SELECT col1, col2 FROM yourtable WHERE yourcol IS NOT NULL

col1, col2, and yourcol are columns in your table.

If you have specific question, please post it here.

|||Thanks!

How do I dynamically change the "TOP X" portion of a SELECT

I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error.

@.TopX int ( or varchar)

SELECT @.TopX CompanyName, Amount FROM Sales Where....

Why will this not work?

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

DECLARE @.stmtvarchar(500)DECLARE @.top varchar(5)SET @.top ='10'SET @.stmt ='SELECT TOP ' + @.top +' * FROM [Products]'EXEC(@.stmt)

You can dynamically create a sql statement:|||

Upgraded to 2005 and that worked perfectly...

Thanks!

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.