check memory pressure

Whats the easy way to check for memory pressure to SQL or if its time to add
more RAM or if theres a memory leak
Its easy from a CPU or disk perspective to look at processor % or queue
length to atleast judge there is some bottleneck, but I have been very
uncertain about how to troubleshoot memory and get to know instantly there
is memory problems
ThanksAre you having performance issues? Is the server failing to run batches or
jobs or are you just trying to avert a fire?
"Hassan" wrote:

> Whats the easy way to check for memory pressure to SQL or if its time to a
dd
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks
>
>|||You may want to refer to the following paper. Though it is SQL2005 centric
but is useful for SQL2000 too.
http://download.microsoft.com/downl...otPerfProbs.doc
thanks,
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:54C8BF2A-CD55-46ED-90D7-C0431A9D51B0@.microsoft.com...[vbcol=seagreen]
> Are you having performance issues? Is the server failing to run batches
> or
> jobs or are you just trying to avert a fire?
> "Hassan" wrote:
>|||Following links have some useful information:
1. Monitoring Memory Usage at
http://msdn.microsoft.com/library/d.../>
on_8x0l.asp
2. Identifying Bottlenecks and Performance-Tuning SQL Server
http://www.informit.com/articles/ar...5&seqNum=2&rl=1
"Hassan" wrote:

> Whats the easy way to check for memory pressure to SQL or if its time to a
dd
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks

check memory pressure

Whats the easy way to check for memory pressure to SQL or if its time to add
more RAM or if theres a memory leak
Its easy from a CPU or disk perspective to look at processor % or queue
length to atleast judge there is some bottleneck, but I have been very
uncertain about how to troubleshoot memory and get to know instantly there
is memory problems
Thanks
Are you having performance issues? Is the server failing to run batches or
jobs or are you just trying to avert a fire?
"Hassan" wrote:

> Whats the easy way to check for memory pressure to SQL or if its time to add
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks
>
>
|||You may want to refer to the following paper. Though it is SQL2005 centric
but is useful for SQL2000 too.
http://download.microsoft.com/downlo...tPerfProbs.doc
thanks,
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:54C8BF2A-CD55-46ED-90D7-C0431A9D51B0@.microsoft.com...[vbcol=seagreen]
> Are you having performance issues? Is the server failing to run batches
> or
> jobs or are you just trying to avert a fire?
> "Hassan" wrote:
|||Following links have some useful information:
1. Monitoring Memory Usage at
http://msdn.microsoft.com/library/de...rfmon_8x0l.asp
2. Identifying Bottlenecks and Performance-Tuning SQL Server
http://www.informit.com/articles/art...&seqNum=2&rl=1
"Hassan" wrote:

> Whats the easy way to check for memory pressure to SQL or if its time to add
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks

check memory pressure

Whats the easy way to check for memory pressure to SQL or if its time to add
more RAM or if theres a memory leak
Its easy from a CPU or disk perspective to look at processor % or queue
length to atleast judge there is some bottleneck, but I have been very
uncertain about how to troubleshoot memory and get to know instantly there
is memory problems
ThanksAre you having performance issues? Is the server failing to run batches or
jobs or are you just trying to avert a fire?
"Hassan" wrote:
> Whats the easy way to check for memory pressure to SQL or if its time to add
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thanks
>
>|||You may want to refer to the following paper. Though it is SQL2005 centric
but is useful for SQL2000 too.
http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc
thanks,
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:54C8BF2A-CD55-46ED-90D7-C0431A9D51B0@.microsoft.com...
> Are you having performance issues? Is the server failing to run batches
> or
> jobs or are you just trying to avert a fire?
> "Hassan" wrote:
>> Whats the easy way to check for memory pressure to SQL or if its time to
>> add
>> more RAM or if theres a memory leak
>> Its easy from a CPU or disk perspective to look at processor % or queue
>> length to atleast judge there is some bottleneck, but I have been very
>> uncertain about how to troubleshoot memory and get to know instantly
>> there
>> is memory problems
>> Thanks
>>|||Following links have some useful information:
1. Monitoring Memory Usage at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_8x0l.asp
2. Identifying Bottlenecks and Performance-Tuning SQL Server
http://www.informit.com/articles/article.asp?p=390585&seqNum=2&rl=1
"Hassan" wrote:
> Whats the easy way to check for memory pressure to SQL or if its time to add
> more RAM or if theres a memory leak
> Its easy from a CPU or disk perspective to look at processor % or queue
> length to atleast judge there is some bottleneck, but I have been very
> uncertain about how to troubleshoot memory and get to know instantly there
> is memory problems
> Thankssql

check link server availability in sp

I am changing a stored proc to use linked servers.
We have 5 linked sql servers, each in different physical locations and
from time to time the network connection at one of the locations may go
down. It is critical that if I can't get to a linked server, that my sp
doesn't fail and return an error code. I just need to run a different
sql statement against just the one sql server.
I am trying to simulate this in my lab by doing any of the following:
- physically disconnecting the linked sql server's cat5 cable from the
network
- pausing the linked server
- changing the name of linked server on my local server so the names
don't match.
In each case I get a message saying
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
I can't change the code, I really need to accomplish this in stored
procedure(s)
Here is my stored procedure.
SET XACT_ABORT OFF
SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
EXEC @.iReturnCode = sp_executesql @.sSql
SET XACT_ABORT ON
PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
IF @.iReturnCode = 1
BEGIN
PRINT 'can't connect to linked server'
-- run sql statement1 on local sql server
END
ELSE
BEGIN
PRINT 'no error'
-- run sql statement2 on local and linked sql server
END
Any ideas?I have written a small activeX dll that I use to ping the target servers
using the sp_OA methods for active X integration with transactSQL. I've
wrapped this up in a procedure IsHostAlive_sp @.hostName that will check.
The server itself must be able to resolve the address.
You can have the DLL if you want, its a VB6 project.
Phil
<rinfo@.mail.com> wrote in message
news:1130424229.400896.218240@.g14g2000cwa.googlegroups.com...
>I am changing a stored proc to use linked servers.
> We have 5 linked sql servers, each in different physical locations and
> from time to time the network connection at one of the locations may go
> down. It is critical that if I can't get to a linked server, that my sp
> doesn't fail and return an error code. I just need to run a different
> sql statement against just the one sql server.
> I am trying to simulate this in my lab by doing any of the following:
> - physically disconnecting the linked sql server's cat5 cable from the
> network
> - pausing the linked server
> - changing the name of linked server on my local server so the names
> don't match.
> In each case I get a message saying
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> I can't change the code, I really need to accomplish this in stored
> procedure(s)
> Here is my stored procedure.
> SET XACT_ABORT OFF
> SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
> EXEC @.iReturnCode = sp_executesql @.sSql
> SET XACT_ABORT ON
> PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
> PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
> IF @.iReturnCode = 1
> BEGIN
> PRINT 'can't connect to linked server'
> -- run sql statement1 on local sql server
> END
> ELSE
> BEGIN
> PRINT 'no error'
> -- run sql statement2 on local and linked sql server
> END
> Any ideas?
>|||Ping is probably a decent first step, but a small one.
If it's not on the same LAN, pings may get dropped on the floor due to
outright refusal of traffic, this is very common, e.g. ping
www.microsoft.com
Even a successful ping result does not mean everything is okay. Did you
check the actual port SQL Server is listening on (may be disabled or
blocked)? Are you sure that the specific SQL Server instance is running?
Are you sure that your login credentials are good?
Another approach might be to inspect the results of an osql call.
create table #foo( dbname sysname null )
insert #foo exec master..xp_cmdshell
'osql -S<linkedservername> -U<username> -P<password> -dMaster -Q"SELECT TOP
1 Name FROM sysdatabases"'
SELECT * FROM #foo
drop table #foo
This will take 10 seconds if <linkedservername> is not available. The
result will be:
(3 row(s) affected)
dbname
----
----
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
NULL
(3 row(s) affected)
"Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
news:uOu4tow2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I have written a small activeX dll that I use to ping the target servers
>using the sp_OA methods for active X integration with transactSQL. I've
>wrapped this up in a procedure IsHostAlive_sp @.hostName that will check.
>The server itself must be able to resolve the address.
> You can have the DLL if you want, its a VB6 project.
> Phil|||You can use SQLDMO as in this example
http://www.sqldbatips.com/displaycode.asp?ID=38
In SQL2005 there is a bultin system procedure to do this
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
<rinfo@.mail.com> wrote in message
news:1130424229.400896.218240@.g14g2000cwa.googlegroups.com...
>I am changing a stored proc to use linked servers.
> We have 5 linked sql servers, each in different physical locations and
> from time to time the network connection at one of the locations may go
> down. It is critical that if I can't get to a linked server, that my sp
> doesn't fail and return an error code. I just need to run a different
> sql statement against just the one sql server.
> I am trying to simulate this in my lab by doing any of the following:
> - physically disconnecting the linked sql server's cat5 cable from the
> network
> - pausing the linked server
> - changing the name of linked server on my local server so the names
> don't match.
> In each case I get a message saying
> Server: Msg 11, Level 16, State 1, Line 1
> General network error. Check your network documentation.
> I can't change the code, I really need to accomplish this in stored
> procedure(s)
> Here is my stored procedure.
> SET XACT_ABORT OFF
> SET @.sSQL = ' SELECT count(id) FROM LINKEDSVR1.dbName.dbo.tli '
> EXEC @.iReturnCode = sp_executesql @.sSql
> SET XACT_ABORT ON
> PRINT CONVERT(CHAR(5), @.@.Error) + ' - @.@.ErrorCode'
> PRINT CONVERT(CHAR(5), @.iReturnCode) + ' - @.iReturnCode'
> IF @.iReturnCode = 1
> BEGIN
> PRINT 'can't connect to linked server'
> -- run sql statement1 on local sql server
> END
> ELSE
> BEGIN
> PRINT 'no error'
> -- run sql statement2 on local and linked sql server
> END
> Any ideas?
>

Check available Time procedure

Hey Guys,
I hope someone can help on here with this. I have this Database where techs are scheduled and dispatched to perform tasks based on skus. What I am trying to achieve is finding the first available Tech based on their schedule and the appointments table.
Example User enters today's date and 5:30 AM and the search for all available techs to perform that task

the tables ddl is
USE [Schedule]
GO
/****** Object: Table [dbo].[AllDays] Script Date: 05/31/2006 01:13:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AllDays](
[ID] [int] NOT NULL,
[DayString] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_WorkingDays] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [Schedule]
GO
/***Appointments Table where trouble is *****
/****** Object: Table [dbo].[Appointments] Script Date: 05/31/2006 01:17:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Appointments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Customer_ID] [int] NOT NULL,
[Tech_ID] [int] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
[App_Date] [datetime] NOT NULL,
[Created_By] [int] NOT NULL,
[Date_Created] [datetime] NOT NULL,
[Sku_ID] [int] NOT NULL,
[Comment_ID] [int] NOT NULL,
CONSTRAINT [PK_TechsShifts] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Comments] FOREIGN KEY([Comment_ID])
REFERENCES [dbo].[Comments] ([ID])
GO
ALTER TABLE [dbo].[Appointments] WITH CHECK ADD CONSTRAINT [FK_Appointments_Techs] FOREIGN KEY([Tech_ID])
REFERENCES [dbo].[Techs] ([ID])

USE [Schedule]
GO
/****** Object: Table [dbo].[Schedule] Script Date: 05/31/2006 01:19:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schedule](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Tech_ID] [int] NOT NULL,
[AllDayID] [int] NOT NULL,
[ShiftStartTime] [datetime] NOT NULL,
[ShiftEndTime] [datetime] NOT NULL,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Schedule]
GO
ALTER TABLE [dbo].[Schedule] WITH CHECK ADD CONSTRAINT [FK_Schedule_AllDay] FOREIGN KEY([AllDayID])
REFERENCES [dbo].[AllDays] ([ID])

Plus the Techs Table which holds their ID's names etc
I have a snapshot (http://webdivisions.net/images/relation.gif) of the relationship posted here if that can help

Thanks for any inputDefine "first available tech".
Does an appointment have to fall completely within a single tech's shift?
Can an appointment span a day?
And why are you using varchar to store date values?

Look, a basic query would look like this:

select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime <= Appointments.EndTime
where not exists
(select *
from Appointments Committed
where Commited.TechID = Schedule.Tech_ID
and ((Committed.ShiftStartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Committed.ShiftEndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Appointments.StartTime BETWEEN Committed.ShiftStartTime and Committed.ShiftEndTime)))|||Thanks for the reply blindman,
What I meant by first tech can be best explained with this scenario.
End user selects date as 06/02/2006 at 10:00 AM then search. the query should return all available techs for that date and period of time and the closest time available if 10:00 AM is not available on that day.
Example*******************
Tech available from date available
Tech1 10:00 AM 06/02/2006
Tech2 11:00 AM 06/02/2006
tech2 9:00 AM 06/02/2006
************************
now as for your question
>Does an appointment have to fall completely within a single tech's shift?
Yes, all techs will be required to reschedule their appointment if they cant finish them on day assigned.
Can an appointment span a day?
No they shouldnt, its just not within the nature of the business to do this.
And why are you using varchar to store date values?
I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

I hope that answers your questions.

Thanks for your time|||...
And why are you using varchar to store date values?
I cant see where you seen that. I know varchar for dates will complicate things for me so I stayed away from doing that.

"[DayString] [varchar](20) "??

Will the logic I gave you in my sample code work for your situation?|||"[DayString] [varchar](20) "??
Oh this table hold an integer representing the day and DayString is the day itself.
**************************
ID DayString
1 Sunday
2 Monday
**************************
I tried to run the SQL the following

select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime <= Appointments.EndTime
where not exists
(select *
from Appointments Committed
where Committed.Tech_ID = Schedule.Tech_ID
and ((Committed.StartTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Committed.EndTime BETWEEN Appointments.StartTime and Appointments.EndTime)
or
(Appointments.StartTime BETWEEN Committed.StartTime and Committed.EndTime)))

the result was no records. Am I missing something here?
Thanks for your help|||Logic error. Should have been:
select top 1 Schedule.Tech_ID
from Schedule
inner join Appointments
on Schedule.ShiftStartTime <= Appointments.StartTime
and Schedule.ShiftEndTime >= Appointments.EndTime
.........to find shifts that bracket the appointments.|||Thanks Blindman :-)
After the I changed the script to what you recommended, The statements returned one row.
Now the stupid question, Is it possible to return the top 1 then the closest available techs as well?
IE, If user enters 12/02/2007 10:30 AM and we have tech1 who starts at 9:30 AM on that day and have no appointment but tech2 starts 12:00 PM. and have an appointment starting 12/02/2007 1:30 PM and ending 12/02/2007 4:00 PM. is it possible to list tech2 with his/her availablity. this way the user doesnt have to go back and query the database again by changing date and time.

second stupid question and that is related to my lack of experience in SQL is how can send this SP the date and time to look for.

Thank you very much for your help.|||In answer to your first question, just about anything can be done in SQL. But you are getting into some fairly complex business logic that is particular to your application. It will require a substantial amount of effort in requirements gathering and programming to get this right. You either need to get educated on SQL fast, or hire a contractor to do the work for you.

To answer your second question, the datetime value can be sent to the stored procedure as a parameter. Please read about stored procedures in Books Online.|||Thank you for all your help blindman
You have been great

I'll be hitting the book store today!|||Hit Books Online first. It's free.

Charting simple value over time chart problems.

I am using Visual Studio Academic version 2002.

I have a simple table with dates and values that correspond to those dates and I would like to chart it using crystal reports. I have created a new report and pulled in the 2 columns from the table.

Now I am lost. When ever I try to do a chart it only gives me the option to continue if I select the "show value and select a field". Thats fine and dandy but when I select a field under any chart type or any chart option it always says "Count of <fieldname>" which is totally wrong.

I only want

for each x value show a y value. (basically like a stock ticker)

This is incredibly frustrating since this is the simplest graph type I can imagine and it just completely is not user friendly for it. Any help would be hugely appreciated.

Thank you.Btw, I downloaded the latest version of crystal reports and it does the same thing.

So obviously I just have no clue how to do this type of chart.

The "do not summarize" button is PERMA disabled, so no matter what field I select in the "Show Values" area, they are always Counted instead rather than just showing the value.

I am puting the date field in the "on Change of" column.

and the corresponding value in the "Show value(s)" column, but again in the show value(s) column it always Counts them rather than just displaying the value.

I tried inverting the selection with putting the value field in the "on change of" and the date field in the "show values" column with erradic results.

Can this type of simple graph even be created in crystal??|||Well now I know what the problem is. Called the company and they said that only the advanced developer version of crystal reports gives the functionality to not summarize the selected values with VS.net.

This is also the same case with the downloadable evaluation versions of their software.

Personally that makes me so annoyed I just won't recommend the product as our reporting solution. If I cannot even show my boss a working example of what types of charting and reports we will be getting with the product, there is no way I am going to even try to sell it to them because the software hasn't even sold me.|||After thinking about this, I came to the conclusion that the sales lady I spoke with must have been smoking crack.

I called Crystal again, went through an hour hugabaloo to get to a tech support guy to finally get the real answer.

The "don't summarize field" works with formulas. So that if you want to create a simple x, y graph, you must create a formula to show the value of the y field.

Don't worry you are not really creating a formula and changing the value at all, it is just the stupid interface they have for their 3rd party charting tool. So, you just have to jump through their hoops to get your work done. It is not intuative at all, but it works and luckily it is simple to do.

So

Task 1: create your report,

Task 2: before you add a chart to it, click on the solution explorer and select formula fields. Right click and select -> New

Task 3: Select the field you want to display as the y value from the database connection explorer and double click on it. This will move something like { <field name> } into the textbox at the bottom of the formula form. Leave it like that. Save and close the formula dialog box.

Task 4: Add a chart to your report.
-In the data fields (you should see your new formula field in the data field there with what ever name you named it when you saved it), select your x value (field) in the top list box that has the drop down that says "On Change of".

-Then select the formula field for the bottom list box that says "show value(s)". The don't summarize button now becomes upchecked if you needed it to be, and your chart should display something near correctly at this point.

There you go.|||I've got exactly the same problem. I tried your solution but that doesn't work.. the don't summarize button is still disabled.

I'm using Visual Studio .Net 2003

Do you have any idea why the charts' not working?|||for wwater:
Make sure that you select an item in the Show Values list box, otherwise the "Don't Summarize Values" option will be greyed out. Took me a while to cotton on to how that area works - not very untuitively, as it happens!

And of course "Don't Summarize Values" won't be enabled if the field you have selected is a "Sum of Group xxxx" type field, because you can't un-sum a summing operation.....:-))

Dave|||When I select the formula field (Y) in the Show Value's list box, the "Don't Summarize Values" option is still disabled..
The chart gives always a Y value of 1 for each X value, that is totaly wrong according to the table..|||might pay you to check Crystal Support site re evaluation precedence, because some formulae are evaluated on first poass thru report, and others are done on second pass, and depending what type of formula and where it is (detail vs group) can impact on this.

MOre info on actual formula, whether it's SUM, running total, etc etc, and where it's placed may help.

Dave|||might pay you to check Crystal Support site re evaluation precedence, because some formulae are evaluated on first poass thru report, and others are done on second pass, and depending what type of formula and where it is (detail vs group) can impact on this.

MOre info on actual formula, whether it's SUM, running total, etc etc, and where it's placed may help.

Dave
hey can u help me fix my project in vb.net...please lemme know if u can help ...i would b very thankful if u could help me...please reply soon...
thanks
shruti.|||I found out why charts didn't work. I did not found a direct solution, but i did found a way to work around the problem.

Instead of making a Blank Report, choose the Report Expert and insert the Chart. Then the chart works..

wil.

chart control

I would like to set some of the properties in the chart control at run time
from the database. Is there a way to set the properties at execution time?Some of the chart properties are expressions (and therefore can be set
dynamically), while other properties are static and have to be defined at
design time.
Which properties do you want to control dynamically?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:6A3A681A-F516-48B7-BEF6-7F77F1C332EC@.microsoft.com...
>I would like to set some of the properties in the chart control at run time
> from the database. Is there a way to set the properties at execution
> time?|||I would like to set the Title and the type of chart.
"Robert Bruckner [MSFT]" wrote:
> Some of the chart properties are expressions (and therefore can be set
> dynamically), while other properties are static and have to be defined at
> design time.
> Which properties do you want to control dynamically?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bill" <Bill@.discussions.microsoft.com> wrote in message
> news:6A3A681A-F516-48B7-BEF6-7F77F1C332EC@.microsoft.com...
> >I would like to set some of the properties in the chart control at run time
> > from the database. Is there a way to set the properties at execution
> > time?
>
>|||The chart title can be an expression (although the dialog does not have an
explicit expression button).
The chart type has to be determined at design time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:D79FC3FF-C13F-4001-A242-C19C440436F3@.microsoft.com...
>I would like to set the Title and the type of chart.
> "Robert Bruckner [MSFT]" wrote:
>> Some of the chart properties are expressions (and therefore can be set
>> dynamically), while other properties are static and have to be defined at
>> design time.
>> Which properties do you want to control dynamically?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Bill" <Bill@.discussions.microsoft.com> wrote in message
>> news:6A3A681A-F516-48B7-BEF6-7F77F1C332EC@.microsoft.com...
>> >I would like to set some of the properties in the chart control at run
>> >time
>> > from the database. Is there a way to set the properties at execution
>> > time?
>>|||> Regarding: The chart type has to be determined at design time.
BTW: You could have multiple charts defined in your report with different
chart types and dynamically hide all of them based on a report parameter
value (i.e. the select chart type) except for the one you want show. You
would set the Visibility.Hidden property on the charts based on the
parameter values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23DkiTFk3FHA.700@.TK2MSFTNGP15.phx.gbl...
> The chart title can be an expression (although the dialog does not have an
> explicit expression button).
> The chart type has to be determined at design time.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Bill" <Bill@.discussions.microsoft.com> wrote in message
> news:D79FC3FF-C13F-4001-A242-C19C440436F3@.microsoft.com...
>>I would like to set the Title and the type of chart.
>> "Robert Bruckner [MSFT]" wrote:
>> Some of the chart properties are expressions (and therefore can be set
>> dynamically), while other properties are static and have to be defined
>> at
>> design time.
>> Which properties do you want to control dynamically?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Bill" <Bill@.discussions.microsoft.com> wrote in message
>> news:6A3A681A-F516-48B7-BEF6-7F77F1C332EC@.microsoft.com...
>> >I would like to set some of the properties in the chart control at run
>> >time
>> > from the database. Is there a way to set the properties at execution
>> > time?
>>
>

Chart

Hi,

I have to make a chart
x-Axis is a time: not periodic values
y-values (5 different values)

I use CR10. How I can du that? I have tested all possibilities, but allways the x axis uses doesn't display really the difference between the 2 times. Also I don't have found a solution to print every value in the chart but not everyone as axis label

best Regards
HansjrgOkay now I have found one way, but it is not the really solution to the problem. If I use as chart Numeric Axes and I calculate the minutes since beginning the axis is right .But I need also the values on the axis and here minutes are not really good. If I use Date axis line chart, than I have the problem that it uses only dates and not datetime

Thanks for any help!

best Regards
Hansjrg

CHARINDEX

SQL Server 2000

Ya know, it is always the simplest stuff that gets ya !!

I am having the hardest time getting a simple piece of code working.
Must be brain dead today.

Goal: Get the users full name from a string

Here is sample data:

"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

Code:
IF LEN(@.strReturnValue) > 0 BEGIN
SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
(CHARINDEX('CN=',@.strReturnValue)+3),
(CHARINDEX(',',@.strReturnValue)-1))
END

It will extract:
"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

I want it to extract:
Kevin Jones

Thanks.On 23 Jun 2005 08:51:27 -0700, csomberg@.dwr.com wrote:

> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))

declare @.test varchar(255)
declare @.pos int

select @.test = 'LDAP://blahblahblah/CN=Kevin
Jones,OU=DevEng,DC=nobody,DC=priv,DC=com'

select @.pos = CHARINDEX('CN=',@.test)+3

SELECT SUBSTRING(@.test, @.pos ,(CHARINDEX(',',@.test)) - @.pos)

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server
No ads - No trials - Innovative features|||...oops i should have written:

You're thinking the substring syntax is
substring( string, start, end )

when the correct syntax is
substring( string, start, length )

where length is end_position - start position

Sorry for the confusion.

hth,

victor dileo

csomberg@.dwr.com wrote:
> SQL Server 2000
> Ya know, it is always the simplest stuff that gets ya !!
> I am having the hardest time getting a simple piece of code working.
> Must be brain dead today.
> Goal: Get the users full name from a string
> Here is sample data:
> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))
> END
> It will extract:
> "Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> I want it to extract:
> Kevin Jones
> Thanks.|||Note the charindex syntax:
charindex ( string, start, length )

You're mistakenly thinking the syntax is:
charindex ( string, start, end )

The "end" parameter should actually be length from the "start"
position, and be calculated as something like:
length = end - start

Once you fix that, I think your code will work just fine.

hth,

victor dileo

csomberg@.dwr.com wrote:
> SQL Server 2000
> Ya know, it is always the simplest stuff that gets ya !!
> I am having the hardest time getting a simple piece of code working.
> Must be brain dead today.
> Goal: Get the users full name from a string
> Here is sample data:
> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))
> END
> It will extract:
> "Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> I want it to extract:
> Kevin Jones
> Thanks.

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

changing time format

I have a datetime field that looks like this:
2004-04-15 09:31:37.000
I used the convert function (convert(char(8),executiontime,108)) to produce this:
09:31:37
How do I make the result return:
093137
Thanks
You could use REPLACE()
select CONVERT(char(8),REPLACE(convert(char(8),executiont ime,108), ':', =
'')) from foo
--=20
Keith
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message =
news:4041CE38-C011-4CB7-BBBC-C651870B1C37@.microsoft.com...
>=20
>=20
> I have a datetime field that looks like this:
>=20
> 2004-04-15 09:31:37.000
>=20
> I used the convert function (convert(char(8),executiontime,108)) to =
produce this:
>=20
> 09:31:37
>=20
> How do I make the result return:
>=20
> 093137
>=20
> Thanks

changing time format

I have a datetime field that looks like this:
2004-04-15 09:31:37.000
I used the convert function (convert(char(8),executiontime,108)) to produce
this:
09:31:37
How do I make the result return:
093137
ThanksYou could use REPLACE()
select CONVERT(char(8),REPLACE(convert(char(8),
executiontime,108), ':', =
'')) from foo
--=20
Keith
"Chambers" <anonymous@.discussions.microsoft.com> wrote in message =
news:4041CE38-C011-4CB7-BBBC-C651870B1C37@.microsoft.com...
>=20
>=20
> I have a datetime field that looks like this:
>=20
> 2004-04-15 09:31:37.000
>=20
> I used the convert function (convert(char(8),executiontime,108)) to =
produce this:
>=20
> 09:31:37
>=20
> How do I make the result return:
>=20
> 093137
>=20
> Thanks