Check if Column has an index

Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
Thanks
Hi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks
|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:

> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>
>
|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...[vbcol=seagreen]
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol1,IndCol2, IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,In dCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegNa me,FullTextKey,Descending,Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,I ndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:

> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
>
>

Check if Column has an index

Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
ThanksHi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:

> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>
>|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...[vbcol=seagreen]
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
>|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol
1,IndCol2,IndCol3,IndCol4,IndCol5,In
dCol6,IndCol7,IndCol8,IndCol9,IndCol10,I
ndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndC
ol16,SegName,FullTextKey,Descending,
Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5
,IndCol6,IndCol7,IndCol8,IndCol9,Ind
Col10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndC
ol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:

> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
>
>

Check if Column has an index

Hi All
Ho do I go about determining a list of all columns in a table that have an
index on them?
ThanksHi David
For starters,
EXEC sp_helpindex <tablename>
will tell you all the indexes and what their key columns are.
If you need an actual list of columns, please specify what version this is
for.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"David" <David@.discussions.microsoft.com> wrote in message
news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> Hi All
> Ho do I go about determining a list of all columns in a table that have an
> index on them?
> Thanks|||Hi Kalen
Thanks for the promt responce.
I am running SQL Server 2000 and I need an actual list of columns.
Thanks
"Kalen Delaney" wrote:
> Hi David
> For starters,
> EXEC sp_helpindex <tablename>
> will tell you all the indexes and what their key columns are.
> If you need an actual list of columns, please specify what version this is
> for.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> > Hi All
> >
> > Ho do I go about determining a list of all columns in a table that have an
> > index on them?
> >
> > Thanks
>
>|||David
SELECT OBJECT_NAME(id) AS table_name,
name AS ind_name
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
ORDER BY table_name
"David" <David@.discussions.microsoft.com> wrote in message
news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
> Hi Kalen
> Thanks for the promt responce.
> I am running SQL Server 2000 and I need an actual list of columns.
> Thanks
> "Kalen Delaney" wrote:
>> Hi David
>> For starters,
>> EXEC sp_helpindex <tablename>
>> will tell you all the indexes and what their key columns are.
>> If you need an actual list of columns, please specify what version this
>> is
>> for.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
>> > Hi All
>> >
>> > Ho do I go about determining a list of all columns in a table that have
>> > an
>> > index on them?
>> >
>> > Thanks
>>|||Hi David,
Please try the below mentioned SP and let me know if it solve ur problem: -
--EXEC dbo.ColumnsIndexed
Create PROCEDURE dbo.ColumnsIndexed
AS
SET NOCOUNT ON
DECLARE @.sTableName SYSNAME
DECLARE @.Tablename VARCHAR(50)
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
If exists (select object_name(id) from sysobjects where name='tblResults')
DROP TABLE tblResults
-- Create the temporary table...
CREATE TABLE tblResults
(
[name] nvarchar(50),
status int,
indid int,
OrigFillFactor int,
IndCol1 nvarchar(20),
IndCol2 nvarchar(20),
IndCol3 nvarchar(20),
IndCol4 nvarchar(20),
IndCol5 nvarchar(20),
IndCol6 nvarchar(20),
IndCol7 nvarchar(20),
IndCol8 nvarchar(20),
IndCol9 nvarchar(20),
IndCol10 nvarchar(20),
IndCol11 nvarchar(20),
IndCol12 nvarchar(20),
IndCol13 nvarchar(20),
IndCol14 nvarchar(20),
IndCol15 nvarchar(20),
IndCol16 nvarchar(20),
SegName nvarchar(20),
FullTextKey int,
Descending int,
Computed int ,
IsTable int
)
-- Populate the temp table...
INSERT @.t_TableNames_Temp
select name from sysobjects where xtype in ('S','U')order by name
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
Select @.tablename = rtrim(table_name) from @.t_TableNames_Temp
--PRINT @.tablename
INSERT INTO tblResults
(name,status,indid,OrigFillFactor,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16,SegName,FullTextKey,Descending,Computed,IsTable)
Exec ('SP_MSHelpindex ' + @.tablename)
DELETE FROM @.t_TableNames_Temp WHERE @.tablename = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
--RETURN 0
-- Return the results...
--select * from tblresults
select distinct object_name(id) as Table_name
,IndCol1,IndCol2,IndCol3,IndCol4,IndCol5,IndCol6,IndCol7,IndCol8,IndCol9,IndCol10,IndCol11,
IndCol12,IndCol13,IndCol14,IndCol15,IndCol16
from sysindexes JOIN tblResults on
sysindexes.name=tblResults.name
SET NOCOUNT OFF
Regards
Manu Jaidka
"Uri Dimant" wrote:
> David
> SELECT OBJECT_NAME(id) AS table_name,
> name AS ind_name
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> ORDER BY table_name
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:CC3389B3-75D2-4420-A49D-AAF68E4BD0DC@.microsoft.com...
> > Hi Kalen
> >
> > Thanks for the promt responce.
> >
> > I am running SQL Server 2000 and I need an actual list of columns.
> >
> > Thanks
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi David
> >>
> >> For starters,
> >> EXEC sp_helpindex <tablename>
> >>
> >> will tell you all the indexes and what their key columns are.
> >>
> >> If you need an actual list of columns, please specify what version this
> >> is
> >> for.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://sqlblog.com
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:43E70B99-9674-4E4A-9C1B-589AC53D31EA@.microsoft.com...
> >> > Hi All
> >> >
> >> > Ho do I go about determining a list of all columns in a table that have
> >> > an
> >> > index on them?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>

Check if a field exists before ADD a new column.

Hi All.
We have some scripts adding columns to tables. Is there a simple way to
check if the column are there already in the table before running the ADD
command. Just like you get on the table when scripting it?
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CalDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Thanx all
ghHello,
You can use something like this:
IF NOT EXISTS (SELECT * FROM syscolumns
WHERE id=OBJECT_ID('TableName') AND name='ColumnName')
[...]
Razvan|||if col_length('tb','col') is null
print('col does not exist in tb')
-oj
"Geir Holme" <geir@.multicase.no> wrote in message
news:uA2GTzcPGHA.1532@.TK2MSFTNGP12.phx.gbl...
> Hi All.
> We have some scripts adding columns to tables. Is there a simple way to
> check if the column are there already in the table before running the ADD
> command. Just like you get on the table when scripting it?
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[CalDates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>
> Thanx all
> gh
>

Check constraints on clustered columns

Hi guys 'n gals, I'm having an issue wrapping my head around a check constraint that I need to set on a table in my database.

Table: OnCall
Columns: OnCall_PKey (identity), Person_Key, StartDate, EndDate

When a new record is entered, I need a check constraint to make sure that the person entered does not already exist in the table with an overlapping time period:

If in the new record, the start date or the end date fall between the start date and the end date for an existing record having the person key in the new record then the record fails the check.

Example:
One existing data row from my table:
1496, 06/12/2007, 12/12/2007

I try to add:
1496, 09/12/2007, 15/12/2007

The record fails because the new date range overlaps the existing record in the table. No person can have overlapping time periods, however, a person can have multiple time slots in the table, it's just that none of the time slots may overlap.

Any pointers, will be gratefully received.What about defining a trigger on the table ?

Check constraints

Hi,
If I have three columns called 'userid' 'event' and 'result' and only allow
one result row per event and userID. I.e. each user can only have one result
per event.
How would a constraint expression look like for this ?
Nicalter table <yourtable> add constraint uk_<yourtable>_event_userid unique
( event, userid )
That will only allow one row to have the same event and userid.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:edSyoJV3FHA.3292@.tk2msftngp13.phx.gbl...
> Hi,
> If I have three columns called 'userid' 'event' and 'result' and only
> allow one result row per event and userID. I.e. each user can only have
> one result per event.
> How would a constraint expression look like for this ?
> Nic
>

Check Constraint help,, Alpha and Numeric

Hey there, In SQL server 2000, I need to create a constraint on one of
the columns that the field enterd must be 2 alpha charactors and the
last 3 must be numeric.
soo, for example
aa123
thanx in advanceUse something like this:
ALTER TABLE TableName ADD CONSTRAINT ConstraintName
CHECK (ColumnName LIKE '[A-Z][A-Z][0-9][0-9][0-9]')
Razvan
Bonzol wrote:
> Hey there, In SQL server 2000, I need to create a constraint on one of
> the columns that the field enterd must be 2 alpha charactors and the
> last 3 must be numeric.
> soo, for example
> aa123
> thanx in advance|||Thanx, but where would I actually put this? atm im trying through right
clicking on the column and creatinng a constraint|||You can execute the above statement in a Management Studio query window
(or in Query Analyzer if you are using SQL Server 2000).
If you want to do this using the graphical interface (in Management
Studio), you should go to the Constraints node (not the Columns node),
right click and choose "New Constraint..."; in the "Expression", type:
ColumnName LIKE '[A-Z][A-Z][0-9][0-9][0-9]'
(of course, replace ColumnName with the name of your column)
Razvan

Check Constraint help,, Alpha and Numeric

Hey there, In SQL server 2000, I need to create a constraint on one of
the columns that the field enterd must be 2 alpha charactors and the
last 3 must be numeric.
soo, for example
aa123
thanx in advanceUse something like this:
ALTER TABLE TableName ADD CONSTRAINT ConstraintName
CHECK (ColumnName LIKE '[A-Z][A-Z][0-9][0-9][0-9]')
Razvan
Bonzol wrote:
> Hey there, In SQL server 2000, I need to create a constraint on one of
> the columns that the field enterd must be 2 alpha charactors and the
> last 3 must be numeric.
> soo, for example
> aa123
> thanx in advance|||Thanx, but where would I actually put this? atm im trying through right
clicking on the column and creatinng a constraint|||You can execute the above statement in a Management Studio query window
(or in Query Analyzer if you are using SQL Server 2000).
If you want to do this using the graphical interface (in Management
Studio), you should go to the Constraints node (not the Columns node),
right click and choose "New Constraint..."; in the "Expression", type:
ColumnName LIKE '[A-Z][A-Z][0-9][0-9][0-9]'
(of course, replace ColumnName with the name of your column)
Razvan

charts in Reporting Services

hey there

Happy New Year to you all.

in RS 2005

in layout tab

I have four columns in my table

col 1 (group on Company)

col 2 (group on application)

col 3 (count on id no) // total field (count/idno) // max/idno)

col 4 (graph) as I want this to show line by line (visual statement)

I read you need to put that max field in to help with graph. in the Y axis Max field

Unfortunately I either didn't read it properly or I am doing something really wrong.

My graph is not showing correctly - eg one line has 2 in it and is showing more than a line that has 227.

call id is in data of the graph

company name is in series of the graph

can someone explain what I am doing wrong please

thanks

jewel

Anyone able to help here would like to see examples of people using these graphs!

Can someone help with some examples they are using?

Is someone using these without writing extra code etc?

cheers

Charts - width of columns

Is there a way to control the width of the columns that are produced when
using a column chart?Yes there is a way to control the width of columns and bars. The property is
called "PointWidth". However, the property is only supported on the report
server (and designer preview).
What you need to do is:
* open your RDL file in a text / xml-editor or in "code view" of designer
* search for the chart (<chart name="...">)
* add a PointWidth value between 0 and 100 (by default it is 55)
Example:
<Chart Name="Sales">
<PointWidth>30</PointWidth>
...
</Chart>
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsrdl/htm/rsp_ref_rdl_elements_fp_87lg.asp
Note: PointWidth is a constant value, so if you have an unknown number of
categories at runtime you might want to use two identical charts (one with
regular PointWidth, the other with a small PointWidth) and based on a
condition (number of categories) always hide one chart.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"PD" <PD@.discussions.microsoft.com> wrote in message
news:5C4C2447-15F9-4A7B-B4BE-2823EE08D73D@.microsoft.com...
> Is there a way to control the width of the columns that are produced when
> using a column chart?

chart series with different colors

hello, my english is not to well.
i Have a chart (columns) I need set de colors of my columns
How can a do that?
thanks !chart properties-> Data tab ->
Add values -> select that value -> click on Edit button
it will popup another window-> there select apperance tab-> there click on
series style button->it will popup another window -> there select your own
color
Regards,
Sri
"Lorein" wrote:
> hello, my english is not to well.
> i Have a chart (columns) I need set de colors of my columns
> How can a do that?
> thanks !|||Hello Sriman, thanks for your answer.
I don´t have that option in Data tab => values => Edit =>apperance
I am developing the report with Microsoft Visual Studio .Net 2003,
Perhaps for that reason I do not have the option, can be?
thanks again !!!!
Lorein.
"Sriman" wrote:
> chart properties-> Data tab ->
> Add values -> select that value -> click on Edit button
> it will popup another window-> there select apperance tab-> there click on
> series style button->it will popup another window -> there select your own
> color
> Regards,
> Sri
> "Lorein" wrote:
> > hello, my english is not to well.
> > i Have a chart (columns) I need set de colors of my columns
> > How can a do that?
> > thanks !

CHART COLUMNS

Is there a way to put a border around the bars in the cart. I didnt think
there was, but I hope to be mistaken. ThanksHave you tried:
Chart Properties> Data tab> Values> [select value]> Edit>
Appearence tab> Series style> Border and line tab
HTH,
Magendo_man
"Ben Watts" wrote:
> Is there a way to put a border around the bars in the cart. I didnt think
> there was, but I hope to be mistaken. Thanks
>
>|||Thanks, that works, one more chart question. If I have a pie chart, how do
I get it to show up if there isnt any data?
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:AB293EDA-47DE-4704-A6F2-DA40B4E652A1@.microsoft.com...
> Have you tried:
> Chart Properties> Data tab> Values> [select value]> Edit>
> Appearence tab> Series style> Border and line tab
> HTH,
> Magendo_man
> "Ben Watts" wrote:
>> Is there a way to put a border around the bars in the cart. I didnt
>> think
>> there was, but I hope to be mistaken. Thanks
>>

char(9) or char(11) type character

I have a square character in some of my columns in a database which I need t
o
weed out. Problem is, it seems like it is neither a char(9) nor a char(11)
and I have no way of knowing what it is.(old data)
I tried the following for testing purposes.
****************************************
************
CREATE TABLE [Test] (
[Column1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
insert into Test (column1) values ('testvalueStart' + char(11) +
'testvalueend' + char(11))
select charindex(char(11), column1) from Test.
****************************************
******************
The above returns 15, as expected. This is probably because I know what I am
searching for (char(11) in this case). However, in the actual table when I
try charindex for a char(9) or a char(11) on the problematic column, it
always returns a 0.
Are there any other characters that get put in as a square ? Is there any
way of finding out? Or is there a better way of approaching this problem?
Any help is appreciated.
~Naveen> Are there any other characters that get put in as a square ? Is there any
> way of finding out?
DECLARE @.foo VARCHAR(100);
SELECT @.foo = COLUMN1 FROM Test; -- assumes 1 row
-- otherwise add where clause
DECLARE @.len INT, @.i INT;
SET @.len = LEN(@.foo);
SET @.i = 1;
WHILE @.i <= @.len
BEGIN
PRINT SUBSTRING(@.foo, @.i, 1) + ' = CHAR(' +
RTRIM(ASCII(SUBSTRING(@.foo, @.i, 1))) + ')';
SET @.i = @.i + 1;
END|||Thanks Aaron for the sample. Neat thing that I could use sometime.
However, it gives me the ascii values for all the characters and for some
reason it omits the last character which happens to be the problematic one.
By trial and error I found that that the square thingy is a char(0) or a
null, so that'll take care of my problem for now.
Basically what i'm saying is the method fails for a column that has a value
of
'teststring' + char(0). Workarounds for that?
"Aaron Bertrand [SQL Server MVP]" wrote:

>
>
>
>
> DECLARE @.foo VARCHAR(100);
>
> SELECT @.foo = COLUMN1 FROM Test; -- assumes 1 row
> -- otherwise add where clause
>
> DECLARE @.len INT, @.i INT;
>
> SET @.len = LEN(@.foo);
>
> SET @.i = 1;
>
> WHILE @.i <= @.len
> BEGIN
> PRINT SUBSTRING(@.foo, @.i, 1) + ' = CHAR(' +
> RTRIM(ASCII(SUBSTRING(@.foo, @.i, 1))) + ')';
> SET @.i = @.i + 1;
> END
>
>|||Naveen,
you can use the solution
as a pattern for your own one
if it is suitable for you
(you don't post ddl and sample data
therefore i solved the problem
with guess-work about that):
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE TABLE Seq(seq INTEGER NOT NULL PRIMARY KEY);
INSERT INTO Seq
SELECT ten * 10 + unit + 1
FROM (SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Tens(ten)
CROSS JOIN
(SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9) AS Units(unit);
CREATE TABLE Foo(s VARCHAR(50) NOT NULL PRIMARY KEY);
INSERT INTO Foo(s)
SELECT 'test' + CHAR(0) + '0' + CHAR(0) + '0' UNION ALL
SELECT 'test22' + CHAR(3) + '3' + CHAR(4)+ '4' UNION ALL
SELECT 'test333' + CHAR(6) + '6' UNION ALL
SELECT 'test4444' + CHAR(10) + '10' UNION ALL
SELECT 'test55555' + CHAR(15) + '15' UNION ALL
SELECT 'test666666' + CHAR(21) + '21' UNION ALL
SELECT 'test7777777' + CHAR(28) + '28' UNION ALL
SELECT 'test88888888' + CHAR(36) + '36' UNION ALL
SELECT 'test999999999' + CHAR(45) + '45' UNION ALL
SELECT 'test0123456789' + CHAR(55) + '55' UNION ALL
SELECT 'test01234567890' + CHAR(66) + '66' UNION ALL
SELECT 'test012345678901' + CHAR(0x7F) + '7F' UNION ALL
SELECT 'TEST1' + CHAR(1) + '1';
SELECT F.s, ASCII(SUBSTRING(F.s, S.seq, 1)) AS cs, S.seq AS pos
FROM Foo AS F, Seq AS S
WHERE S.seq <= LEN(F.s)
AND ( ASCII(SUBSTRING(F.s, S.seq, 1)) < 32
OR ASCII(SUBSTRING(F.s, S.seq, 1)) = 0x7F);
DROP TABLE Foo;
DROP TABLE Seq;
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)

char(1) vs smallint

I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.Y/N is convenient if users will be querying the database directly, but if you are performing any aggregations then 1/0 allows you to more easily sum the number of flagged records. The percentage flagged, for instance, is just Sum(Flag)/Count(*). (Note that the BIT type won't work with most aggregate functions, otherwise it would be the boolean type of choice.)

You do need to be careful with 1/0 to make sure other applications interpret it correctly. Under some systems TRUE = -1 and FALSE = 0, and other situations are possible.

blindman|||Originally posted by peterlemonjello
I'm having a disagreement with a fellow developer regarding flags. I prefer to declare flag columns as smallint and apply a rule restricting the values to 1 or 0. He prefers to use a char(1) with a rule restricting the values to 'Y' or 'N'. Can anyone give me ammunition against the char(1) or tell me if I'm wrong. Oh yeah, the flag is cast to a boolean in the app written in java, if that makes a difference.
Usually developers know much more than dbas ;). I am using tinyint for flags.|||Thanx blindman and snail! Unfortunately, I'm a developer that new way too much about databases and sql server so I'm a dba now too. As a developer I always said the only thing worse than a dba is a object oriented developer turned dba, guess I'm eating my own words... LOL!!!|||actually, the only thing worse than a dba is a data architect or data modeller like me with years (decades, actually) of modelling and sql language experience, who couldn't solve a performance problem to save his life other than perhaps declaring the obvious indexes...

performance issues aside, you have to look at the implications of your design on the sql to solve business problems

blindman had a superb example -- sum(flag)/count(*)

that's the type of thing a modeller knows, that a dba might not

tinyint (or smallint) is also good because it's a lot more portable across database platforms than boolean

rudy
http://r937.com/|||Those with experience in small shops that required both development and admin duties know best! :D

...but I also think my experience in object-oriented development has helped me develop modular database applications. There is no such thing as bad experience, just people who can't see beyond their own particular project scope.

blindman

char vs. varchar

The company I'm contracting at has a guideline that table columns should be
of type char if less than 20 characters, otherwise varchar. This guideline
was just changed to a requirement. In my opinion, the choice between char an
d
varchar should consider variability of data size as well as need of
modification performance vs. read performance, and therefore shouldn't be
based on a fixed size. Any comments I could use to help my cause, or any
disagreement?
Thanks
Vern RabeVern Rabe wrote:

> In my opinion, the
> choice between char and varchar should consider variability of data
> size as well as need of modification performance vs. read
> performance, and therefore shouldn't be based on a fixed size. Any
> comments I could use to help my cause, or any disagreement?
I agree with you. When for example you got a FirstName field, there
are names from 3 chars till 18 (in an example DB). Why would you waste
the space by using char? I only use char when the column length is the
same for every row. Good luck convincing the company ;)
Kind regards,
Stijn Verrept.|||Vern Rabe wrote:
Another advantage of using varchars for non fixed length columns: when
the text entered in a char column is smaller than the size of that
column it will be padded to the correct length so you'll need to handle
this in your application or use trim queries.
Kind regards.|||I'd like to hear the company's rationale for this requirement but a length
of 20 characters seems a bit excessive to me. Data are typically read much
more often than written. Although inexpensive storage mitigates the need
for byte counting, I don't see how one can justify using a particular data
type before the schema or application is designed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:194CC9B9-0702-4E74-B3D2-602BA234DEA9@.microsoft.com...
> The company I'm contracting at has a guideline that table columns should
> be
> of type char if less than 20 characters, otherwise varchar. This guideline
> was just changed to a requirement. In my opinion, the choice between char
> and
> varchar should consider variability of data size as well as need of
> modification performance vs. read performance, and therefore shouldn't be
> based on a fixed size. Any comments I could use to help my cause, or any
> disagreement?
> Thanks
> Vern Rabe|||Char is for fixed width text while VarChar is for variable width text. If
the column is updated frequently, they may be concerned that changing the
length of data in a VarChar would result in page splits. However, this is a
very specific situation and would not justify using Char instead of VarChar
as a general rule. Find out who is responsible for defining database design
requirements, and ask them about it.
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:194CC9B9-0702-4E74-B3D2-602BA234DEA9@.microsoft.com...
> The company I'm contracting at has a guideline that table columns should
> be
> of type char if less than 20 characters, otherwise varchar. This guideline
> was just changed to a requirement. In my opinion, the choice between char
> and
> varchar should consider variability of data size as well as need of
> modification performance vs. read performance, and therefore shouldn't be
> based on a fixed size. Any comments I could use to help my cause, or any
> disagreement?
> Thanks
> Vern Rabe

char to total time

I must use a database with strange columns ( I cannot change it) ... one column store time into a string format (char) >>>

4 m 42 s
1 m 10 s

and I must get the total of seconds !!

then how can I get with >>>
4 m 42 s (= 282)
1 m 10 s (= 70)

a total = 352

??

thank youSince no two database engines seem to handle strings quite the same way, which engine are you using? Are the columns limited to just minutes and seconds, or can they add hours, days, fortnights, or other units of time? Is the formatting fixed (always two digit seconds), or can it vary? Are minutes required or optional?

-PatP|||it is for ACCESS 2000

and in the database are only m and s
but I think it is possible to find
4 h 8 m 24 s

nothing else !
maximum are hours

thanks a lot if you can find|||I have tried

Table1 is the table
hms is the column

SELECT Sum(Left([hms],InStr(1,[hms],"m")-1)*60+Mid([hms],InStr(1,[hms],"m")+2,2)) AS sumOfSeconds FROM Table1;

but it doesn't work|||In the VBA Editor, I'd addFunction hms2c(hms As String) As Integer
' ptp 20040404 Covert "[ x h][ y m][ z s]" string to integer seconds

Dim retval As Integer ' return value
Dim c As String ' current character

retval = 0: d = "": hms = LCase(hms)

While hms <> ""
c = Left(hms, 1): hms = Mid(hms, 2)
If 0 < InStr(1, "0123456789", c) Then d = d & c
If "h" = c Then retval = retval + 3600 * Val(d): d = ""
If "m" = c Then retval = retval + 60 * Val(d): d = ""
If "s" = c Then retval = retval + Val(d): d = ""
Wend

hms2c = retval
End FunctionIn the Query, I'd use:SELECT Table1.hms, hms2c([hms]) AS Expr1
FROM Table1;You'll probably find other uses for that function if you deal with these strings much. ;)

-PatP|||yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

thank you|||Originally posted by castali
yes from outside no problem , and I use VB NET ... but I found the solution on another forum .. only with SQL ! impressive !!

thank you What exactly do you mean by "outside"? everything I've suggested is from pure Access 2000. Unless you are using Office 2003 (aka Office.NET), you can't use VB.NET from within Access.

-PatP|||The solution offered by schlauberger is interesting, but it only works for very limited cases. It will fail if there are hours, or if either the minutes or the seconds are missing. If that works for your needs, enjoy!

-PatP