check query status

Is it possible to create an SSIS package that checks for a running Query on my SQL db?

I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?

There is nothing specific in SSIS that can give you that info; but perhaps you can put a query that gives you that inside of an execute sql task...|||

I want to use SSIS to create the package and use the SQL Task in the package, But how can I 'ping' the sql server to verify the sql query is still running or not? That's the portion I'm stuck on.

What would that query look like? Can this even be done?

|||Your question is one of a Transact-SQL nature and as such should probably be asked over in that forum. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Come back here if you need help implementing the resulting query inside SSIS.|||You can use sp_who or sys.sysprocesses to get lists of running processes. sys.sysprocesses would be easier to work with, if you are on 2005.

check my SP

i m using SQL Server 2000, in this i have the follwowing SP,

CREATE PROCEDURE AddNewCountry
@.CountryName varchar(50),
@.CountryCode varchar(5)

AS
IF exists(Select CountryName from Countries where CountryCode=@.CountryCode or CountryName=@.CountryName)
begin
raiserror('Country Already Exist',16,1)
end
else
begin
INSERT INTO Countries
VALUES
(@.CountryName,
@.CountryCode)
end
GO
in this SP in the where clause i used the following line :
IF exists(Select CountryName from Countries where CountryCode=@.CountryCode or CountryName=@.CountryName) (used OR operator)
but it inserted the data if i give 092,'america'

and if i used AND operator it chekc 092, 'america' if yes then shows error but if i give 092,'Japan' it inserted new row in the Countries table.

plz give me some other idea what changes i made in my SP as unique Country added with unique Code. if code exists then not allowed to used to enter data in the table same in
the case of country name .

plz give me idea what updation i made as after this my SP works fine.

In this case you have to use Or operator instead of AND operator.

And you can enforce Unique Constraint on both tables (not composite)

ex.

Create Table Countries(

CountryCode Int Unique,

CountryName Varchar(100) Unique

)

Check login

Hello, everyone. I am trying to create a procedure that checks a user's login, in the database. The login is based on e-mail address and password.

How do I construct the stored procedure to check if the username and password are correct?

Thanks,

Antonio

You could either use a stored procedure, or a function.

Code Snippet

CREATE PROCEDURE dbo.VerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)

AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

(or)

CREATE FUNCTION dbo.fnVerifyLogin

( @.eMail varchar(200),

@.Pwd varchar(20)
)
RETURNS varchar(10)
AS

SELECT isnull(( SELECT DISTINCT 'Verified'
FROM MyUsersTable

WHERE ( eMail = @.eMail
AND Pwd = @.Pwd
)
), 'BOGUS' )
GO

|||

Just a couple of points to make about password storage in databases.

You should never store a password in a clear (unencrypted) form in a database. Preferably you should not even store it in a decryptable form, but rather a value managled by a one-way hash. SQL Server 2005 now has the HashBytes function which will perform a cryptographically valid hash operation.

It is also recommended that you not perform a straightforward hashing but rather salt the value before hashing with another value. This means adding another known piece of information to the value before hashing the combination. This has a 2-fold advantage:

It makes a brute force attack of the hash algorithm with all the possible values harder (basically impossible if the salt is different for each password). Be aware there are projects on the internet where people are building hash lists for all the characters strings up to a certain length composed of a standard set of characters (mainly alpha/digit - upper case only) for some standard hash algorithms. Good reason for using longer passwords (12+ characters) and at least one unusual character. If you use a different salt for each user (I normally use something based upon the primary ID in the User Table) then it prevents the insider attack of moving the password hash from a user whose password is known to another allowing access to the system as that user (restoring the old hash removes signs of the hack). If the salt is held as a field in the table they can move that too (though a unique constraint might make that more difficult).sql

check file date and copy file

Hi,

I need to set up create a package so that I could check the date of the files posted in a folder, e.g. H:\source. If there is no file created later than one day exists, then continue to check again one hour later. If files do exists, then copy then to c:\dest and then upzip the files. Once this is done, sent an notification email to user@.mydomain.com.

Thanks,

Check out the FileWatcher task on SQLIS.com. It should help with identifying when the file appears. The rest of the tasks mentioned here are included with SSIS. You can use the File System task to copy files, and the Execute Process task to run a commandline utility to unzip them. The Send Mail task is used to send emails.|||

Hi,

I installed the program in the sqlis.com, but when I open the ssis business intelligent console, I can't find the filewatcher task in the toolbox. Can you tell me how to add this task in?

Thanks,

|||

There are instructions on SQLIS.com.

"The component is provided as an MSI file, however to complete the installation, you will have to add the task to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items.... Select the SSIS Control Flow Items tab, and then check the File Watcher Task from the list."

Check Constraints or Triggers

Hi, Im facing teh following situation:

This are just sample table names, but should do for discussing
purpouses.

Create table Invoice
(
InvoiceID Integer Not Null,
CustomerType Integer Not Null,
CustomerCode Integer Not Null,
Amount DECIMAL(10,2) Not Null,
.............
)

Create Table Type1Customer
(
CustomerCode Integer Not Null,
........................
)

Create Table Type2Customer
(
CustomerCode Integer Not Null,
........................
)

I need to add a way to restrict the CustomerType and CustomerCode,
in the Invoice table to the correct values.
This means that if customerType equals 1 the customerCode should be
checked against Type1Customer and if customerType equals 2 the
customerCode should be checked against Type2Customer.

I succesfully created a check constraint. That ensures that the valid
values exists when the rows in the Invoice table are inserted or
updated, but doesnt prevent from deleting records from tables
Type1Customer and Type2Customer that are referenced from the Invoice
table.

Are triggers the only way to go?

Thanks in advance

Sebastin streigerIn addition to Erland's suggestion,
I would recommend adding CustomerType to both Type1Customer and
Type2Customer, and adding CustomerType to their FK constraints|||(sebastian.streiger@.gmail.com) writes:
> This are just sample table names, but should do for discussing
> purpouses.
> Create table Invoice (
> InvoiceID Integer Not Null,
> CustomerType Integer Not Null,
> CustomerCode Integer Not Null,
> Amount DECIMAL(10,2) Not Null,
> ............. )
> Create Table Type1Customer (
> CustomerCode Integer Not Null,
> ....................... )
>
> Create Table Type2Customer (
> CustomerCode Integer Not Null,
> ....................... )
> I need to add a way to restrict the CustomerType and CustomerCode,
> in the Invoice table to the correct values.
> This means that if customerType equals 1 the customerCode should be
> checked against Type1Customer and if customerType equals 2 the
> customerCode should be checked against Type2Customer.
>...
> Are triggers the only way to go?

With that data model, yes. But is that really the right data model?

I would rather have a CustomerCode table which could look like this:

CREATE TABLE CustomerCode (
CustomerType integer NOT NULL,
CustomerCode integer NOT NULL,
CONSTRAINT pk_CustomerCode(CustomerType, CustomerCode))

Then Invoices could refer to this table, and so could the child
tables Type1Customer and Type2Customer.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland and AK:
Thank you for answering.
I DO agree that the model is no the best one that we can have. But due
to organizational issues Im not in position to change the tables
structures by now. So, Im trying to add constraints to ensure the
data consistency.

Thanks for your valuable feedback

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

Check Constraint fails!

Hi Everybody,
Can anybody help me on the following query...
I have a table structure as follows
CREATE TABLE [dbo].[event_logs] (
[WSE_Idx] [int] NULL ,
[WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Date_Generated] [datetime] NULL ,
[WSE_lDate_Generated] [datetime] NULL ,
[WSE_Date_Written] [datetime] NULL ,
[WSE_lDate_Written] [datetime] NULL ,
[WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
go
It contains data. I tried to create the following 'check constraint' to the above table
alter table event_logs
add constraint ck_event_logs
check((WSE_Category = 'application' and wse_log_type in ('Audit Success','error')) OR
(WSE_Category = 'system' and wse_log_type in ('Warning')) OR
(WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
It is giving the following error...
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE CHECK constraint 'ck_event_logs'.
The conflict occurred in database 'slm', table 'event_logs'.
Even I modified the above alter table script as follows, still it is giving the same error.
alter table event_logs
add constraint ck_event_logs
check(WSE_Category like '%applica%')
I created the similar table structure with different table name and applied the check constraint,
it works. No error. Ofcourse table doesn't have data (Empty table).
I have created RULE on this 'event_logs' table (with data). It works fine. No Error.
Can anybody tell me why this 'Check Constraint' is giving problem?.
tks in advance,
vasumData in a table are not valid for 'check constraint' that you specified.
You mast correct data in your table or in ALTER TABLE statement put WITH
NOCHECK option.
Look ALTER TABLE in BOL.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:748BD3CB-E545-4DEA-B05B-103EEF45BFAE@.microsoft.com...
> Hi Everybody,
> Can anybody help me on the following query...
> I have a table structure as follows
> CREATE TABLE [dbo].[event_logs] (
> [WSE_Idx] [int] NULL ,
> [WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Date_Generated] [datetime] NULL ,
> [WSE_lDate_Generated] [datetime] NULL ,
> [WSE_Date_Written] [datetime] NULL ,
> [WSE_lDate_Written] [datetime] NULL ,
> [WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> go
> It contains data. I tried to create the following 'check constraint' to
the above table
> alter table event_logs
> add constraint ck_event_logs
> check((WSE_Category = 'application' and wse_log_type in ('Audit
Success','error')) OR
> (WSE_Category = 'system' and wse_log_type in ('Warning')) OR
> (WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
> It is giving the following error...
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE CHECK constraint
'ck_event_logs'.
> The conflict occurred in database 'slm', table 'event_logs'.
> Even I modified the above alter table script as follows, still it is
giving the same error.
> alter table event_logs
> add constraint ck_event_logs
> check(WSE_Category like '%applica%')
> I created the similar table structure with different table name and
applied the check constraint,
> it works. No error. Ofcourse table doesn't have data (Empty table).
> I have created RULE on this 'event_logs' table (with data). It works fine.
No Error.
> Can anybody tell me why this 'Check Constraint' is giving problem?.
> tks in advance,
> vasum
>|||thanks for the timely help. I works. I used 'with nocheck' option. Able to create new check constraint and this new check constraint is validating the any new rows coming into the table

Check constraint does not work (compare with null)

Hi!

I have a table with a check constraint. But unfortunately it does not
work like I wanted.

CREATE TABLE MAP
(
[R_ID] [T_D_ID] NOT NULL,
[R_ID1] [T_D_ID] NULL,
CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),
CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID])
)

R_ID1 should always have the value of R_ID or Null
The following statements should cause errors:

insert into map (R_ID, R_ID1)values(1,2);
update map set R_ID1=3 where R_ID=1;

But there occur no errors. Does anyone have an idea? It is an SQL Server
2000.

TIA
SusanneChange it to:

CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Susanne Klemm" <Susanne.Klemm@.appliedsystems.de> wrote in message
news:441e9f63$0$43596$bfcc4b32@.reader.news.celox.d e...
Hi!

I have a table with a check constraint. But unfortunately it does not
work like I wanted.

CREATE TABLE MAP
(
[R_ID] [T_D_ID] NOT NULL,
[R_ID1] [T_D_ID] NULL,
CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),
CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID])
)

R_ID1 should always have the value of R_ID or Null
The following statements should cause errors:

insert into map (R_ID, R_ID1)values(1,2);
update map set R_ID1=3 where R_ID=1;

But there occur no errors. Does anyone have an idea? It is an SQL Server
2000.

TIA
Susanne|||Your constraint should be

CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),|||Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .

Change it to:

CHECK (R_ID1 = R_ID)

The UNKNOWN case where R_ID1 is null will still be permitted.

Better still, get rid of R_ID1, which is apparently redundant - except
maybe if it is part of a foreign key. In the case of a foreign key I
would still look for a better design without the nullable column.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Doh! Coffee... I need coffee...

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142860722.911871.308570@.v46g2000cwv.googlegr oups.com...
Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .

Change it to:

CHECK (R_ID1 = R_ID)

The UNKNOWN case where R_ID1 is null will still be permitted.

Better still, get rid of R_ID1, which is apparently redundant - except
maybe if it is part of a foreign key. In the case of a foreign key I
would still look for a better design without the nullable column.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),

Thank you, this worked.

Susanne|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Change it to:
> CHECK (R_ID1 = R_ID)
> The UNKNOWN case where R_ID1 is null will still be permitted.

Actually, the data-modelling tool that I use, PowerDesiger 9.5, insist on
adding IS NULL conditions to all my column constraints for my nullable
columns. I would guess the reason for this is that there was a bug in SQL
2000 RTM where NULL values actually can give you constraint violations.
(There is a similar bug with rules that has been around since SQL 7 RTM,
and I suspect never will get fixed.)

> Better still, get rid of R_ID1, which is apparently redundant - except
> maybe if it is part of a foreign key. In the case of a foreign key I
> would still look for a better design without the nullable column.

To me it looks like a funny sort of bit column, as there are only two
possible values. But maybe Susanne only gave us a scaled-down example,
and the resl-world table looks a little different.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

CHECK constraint

CREATE TABLE T_StaffMain (
[snip]
SM_PostNameE VARCHAR(40) NOT NULL,
SM_PostNameD NVARCHAR(40) NOT NULL,
SM_PostLevel INTEGER DEFAULT 0 NOT NULL CHECK (SM_PostLevel IN
(0,1,2,3)),
[snip]
);
guys. i've got to change the CHECK statement above to values from 0 to
7 instead of 0 to 3.
this is in a production table, so dropping the table is not an option.
how to do this?
thanx
riyazHi
First , you have to DROP CONSTRAINT (see details in the BOL)
<rmanchu@.gmail.com> wrote in message
news:1141616935.268356.322910@.i39g2000cwa.googlegroups.com...
> CREATE TABLE T_StaffMain (
> [snip]
> SM_PostNameE VARCHAR(40) NOT NULL,
> SM_PostNameD NVARCHAR(40) NOT NULL,
> SM_PostLevel INTEGER DEFAULT 0 NOT NULL CHECK (SM_PostLevel IN
> (0,1,2,3)),
> [snip]
> );
> guys. i've got to change the CHECK statement above to values from 0 to
> 7 instead of 0 to 3.
> this is in a production table, so dropping the table is not an option.
> how to do this?
> thanx
> riyaz
>|||Hello, riyaz
You will have to drop the constraint, but you need to know it's name,
because you didn't name it when you created it. To find out the
constraint's name, you can use Enterprise Manager or the following
query:
SELECT o.name FROM sysconstraints k
INNER JOIN sysobjects o ON k.constid=o.id
INNER JOIN syscolumns c ON c.id=k.id AND c.colid=k.colid
WHERE c.id=OBJECT_ID('T_StaffMain') AND c.name='SM_PostLevel'
AND o.type='C'
You should create the new constraint with a name, like this:
ALTER TABLE T_StaffMain ADD CONSTRAINT [CK_T_StaffMain_SM_PostLevel]
CHECK (SM_PostLevel BETWEEN 0 AND 7)
Razvan

Charts displayed in tables

Is it possible to have a Chart control within a table. I am trying to create
a report which displays some information about a School and has a bar chart
to display the pupil age ranges for each school.Add a grouping to the table, where you group e.g. by Fields!SchoolName.Value
Then, enlarge the table group header and drop a chart in the table group
header (!). You can then design the chart.
At runtime, you will get one table group per school name with one chart per
table group header which should give you exactly what you want.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"MicroMoth" <stephen.adams@.forvus.co.uk> wrote in message
news:D562F265-AC31-4935-A217-628819F863C6@.microsoft.com...
> Is it possible to have a Chart control within a table. I am trying to
create
> a report which displays some information about a School and has a bar
chart
> to display the pupil age ranges for each school.|||It is possible to have a table where a row "expands" to reveal a table.
Add a second detail row to the table, and a second grouping with the same
grouping expreassion as the main table grouping. Place a rectangle in the
second detail row. The rectangle automatically takes up the whole row, and
you are now free to place your chart (plus any other controls) anywhere in
the rectangle. Set the initial visibility of the second grouping to False,
and toggle it according to one of the fields in the first detail row. Works a
treat.
"MicroMoth" wrote:
> Is it possible to have a Chart control within a table. I am trying to create
> a report which displays some information about a School and has a bar chart
> to display the pupil age ranges for each school.

Charts & Graphics

Does anyone have a good recommendation for software that allows me to create
*very* attractive charts, gauges, graphs, etc.?
Thanks,
-wpOn Mar 21, 2:44 pm, "Wild Packet" <w...@.contoso.com> wrote:
> Does anyone have a good recommendation for software that allows me to create
> *very* attractive charts, gauges, graphs, etc.?
> Thanks,
> -wp
Obviously, SQL Server Reporting Services 2005, Crystal Reports XI and
Dundas (http://www.dundas.com/Products/Chart/NET/index.aspx?
Campaign=GoogleDCP&gclid=CIXsm9urh4sCFR3VgAodRy99Ew) have pretty
decent charting/graphing capabilities; however, for the top of the
line/cream-of-the-crop, I would suggest Adobe Flex:
http://examples.adobe.com/flex2/inproduct/sdk/dashboard/dashboard.html
- http://www.adobe.com/products/flex/ . Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks! I will givce those a try.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1174530873.250975.46390@.n76g2000hsh.googlegroups.com...
> On Mar 21, 2:44 pm, "Wild Packet" <w...@.contoso.com> wrote:
>> Does anyone have a good recommendation for software that allows me to
>> create
>> *very* attractive charts, gauges, graphs, etc.?
>> Thanks,
>> -wp
> Obviously, SQL Server Reporting Services 2005, Crystal Reports XI and
> Dundas (http://www.dundas.com/Products/Chart/NET/index.aspx?
> Campaign=GoogleDCP&gclid=CIXsm9urh4sCFR3VgAodRy99Ew) have pretty
> decent charting/graphing capabilities; however, for the top of the
> line/cream-of-the-crop, I would suggest Adobe Flex:
> http://examples.adobe.com/flex2/inproduct/sdk/dashboard/dashboard.html
> - http://www.adobe.com/products/flex/ . Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Charts

In this release, the value fields for a chart must be aggregates. If you
don’t have the aggregate you need, you can create a custom field (name
it whatever you want) and wrap the base field in the SUM() aggregate
function, which should be a no-op in this case.Hi Bob,
Thanks a bunch! That did work!

Chart x-axis

I am trying to create a chart using end_date (parameter) as the x-axis. I
have set up the category group expression as "=Month(Fields!End_Date.Value)"
and this is used for my x-axis. When the report is viewed, it shows January -
September, November, December, then October. The end_date is setup as
datetime datatype. The value and the labels are correct, just in the wrong
order. How can I get this x-axis in the correct order?On Aug 14, 9:08 pm, j_rad <j_...@.discussions.microsoft.com> wrote:
> I am trying to create a chart using end_date (parameter) as the x-axis. I
> have set up the category group expression as "=Month(Fields!End_Date.Value)"
> and this is used for my x-axis. When the report is viewed, it shows January -
> September, November, December, then October. The end_date is setup as
> datetime datatype. The value and the labels are correct, just in the wrong
> order. How can I get this x-axis in the correct order?
Hi,
Try to put the sorting expression of the group =Month(Fields!
End_Date.Value)
with ascending order.
V.|||That worked. Thanks for your help.
"Vinnie" wrote:
> On Aug 14, 9:08 pm, j_rad <j_...@.discussions.microsoft.com> wrote:
> > I am trying to create a chart using end_date (parameter) as the x-axis. I
> > have set up the category group expression as "=Month(Fields!End_Date.Value)"
> > and this is used for my x-axis. When the report is viewed, it shows January -
> > September, November, December, then October. The end_date is setup as
> > datetime datatype. The value and the labels are correct, just in the wrong
> > order. How can I get this x-axis in the correct order?
> Hi,
> Try to put the sorting expression of the group =Month(Fields!
> End_Date.Value)
> with ascending order.
> V.
>

Chart with Data Fields from Multiple Queries

Is there any way to create a chart using data fields from more that one query?

I tried to create two different datasets, but the chart has to be bound to only one dataset. So when I drop the data field from the second dataset onto the chart I get a SQL error.

I've also tried UNION ALL. Each of these queries is correct by itself, but UNION ALL combines GLBUDAMOUNT and GLTRXAMOUNT into one field. I need them to be two different fields so that I can do GLBUDAMOUNT VS GLTRXAMOUNT in the chart.

Solved using a simple case statement.

Chart URL Action? Easy question ... I hope!

I am trying to create a chart action to do a URL drill into bar chart. But I
can not figure out how to get the name of the clicked bar.
Example: If the diagram below were a chart, when I click on the "two" bar, I
want to create the url: http://www.bonzo.com/foo.aspx&PICKEDBAR=Two
One == Two ==== Three ========
Is it possible to do this?Your Jump to navigate action you can use the RS global collections. Assuming
that One, Two, etc. are category groups, you can use Fields!<your database
field>.Value
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"billd" <billd@.discussions.microsoft.com> wrote in message
news:43521AC0-1DE9-4980-B579-16F0DF651586@.microsoft.com...
> I am trying to create a chart action to do a URL drill into bar chart. But
I
> can not figure out how to get the name of the clicked bar.
> Example: If the diagram below were a chart, when I click on the "two" bar,
I
> want to create the url: http://www.bonzo.com/foo.aspx&PICKEDBAR=Two
> One ==> Two ====> Three ========> Is it possible to do this?|||Thanks ... excactly the answer I needed.
You are the MVP.
"Teo Lachev [MVP]" wrote:
> Your Jump to navigate action you can use the RS global collections. Assuming
> that One, Two, etc. are category groups, you can use Fields!<your database
> field>.Value
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "billd" <billd@.discussions.microsoft.com> wrote in message
> news:43521AC0-1DE9-4980-B579-16F0DF651586@.microsoft.com...
> > I am trying to create a chart action to do a URL drill into bar chart. But
> I
> > can not figure out how to get the name of the clicked bar.
> >
> > Example: If the diagram below were a chart, when I click on the "two" bar,
> I
> > want to create the url: http://www.bonzo.com/foo.aspx&PICKEDBAR=Two
> >
> > One ==> > Two ====> > Three ========> >
> > Is it possible to do this?
>
>

Chart trend line

Good day,
I hope some one can help. In the past I have used OWC to create much charts
for the web but now see the light and have started to use SSRS. Only on thing
though, is it possible for a SSRS chart to have trend lines. It is critical
that I have trend lines in my chart, what the MD wants the MS gets.
Any one know if this is possible and how to do it'
RegardsAre you talking about showing markers and plot data as a line? If so: right
click the graph, click properties, select Data tab, select the value, click
edit and select Appearance tab. Here you have show markers and plot data as
line options.
"PLSH" wrote:
> Good day,
> I hope some one can help. In the past I have used OWC to create much charts
> for the web but now see the light and have started to use SSRS. Only on thing
> though, is it possible for a SSRS chart to have trend lines. It is critical
> that I have trend lines in my chart, what the MD wants the MS gets.
> Any one know if this is possible and how to do it'
> Regards|||Nope, I mean a trend line. I have a chart that is a simple line chart and I
need to have a trend line for each of the fields. You can do it with OWC
which is what I am converting my charts from.
"Eduardo Luczinski" wrote:
> Are you talking about showing markers and plot data as a line? If so: right
> click the graph, click properties, select Data tab, select the value, click
> edit and select Appearance tab. Here you have show markers and plot data as
> line options.
> "PLSH" wrote:
> > Good day,
> >
> > I hope some one can help. In the past I have used OWC to create much charts
> > for the web but now see the light and have started to use SSRS. Only on thing
> > though, is it possible for a SSRS chart to have trend lines. It is critical
> > that I have trend lines in my chart, what the MD wants the MS gets.
> >
> > Any one know if this is possible and how to do it'
> >
> > Regards

chart series

Hi,
I have create a report which contains a chart.
The series gets populated with the correct data but at the end of each
series, there is a text "Series 1" joined to the end of the actual text.
How is it possible to make sure the word "Series 1" does not appear at the
end of each series line?
ThanksProblem solved by restarting the machine.
"farshad" wrote:
> Hi,
> I have create a report which contains a chart.
> The series gets populated with the correct data but at the end of each
> series, there is a text "Series 1" joined to the end of the actual text.
> How is it possible to make sure the word "Series 1" does not appear at the
> end of each series line?
> Thanks

Chart Question...

Hi,
I'm trying to create a line graph/chart in reporting services. I have 2
stored procedures that outputs the count of 2 tables that created 2
datasets.
Both these procedures use a field called acctdates. I'm trying to use
the acctdates as the catagory field and the 2 counts in the data field. But
I keep receiving an error saying I can't use a field from another dataset
for the current dataset. Is there a way around this problem? or is there a
different way I should approach it?
Please help....
Thank you,
RickyYou may want to consider joining the two datasets inside a stored procedure.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ricky" <kmeas1@.gmail.com> wrote in message
news:Ow0xdjVNFHA.2372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to create a line graph/chart in reporting services. I have
> 2 stored procedures that outputs the count of 2 tables that created 2
> datasets.
> Both these procedures use a field called acctdates. I'm trying to
> use the acctdates as the catagory field and the 2 counts in the data
> field. But I keep receiving an error saying I can't use a field from
> another dataset for the current dataset. Is there a way around this
> problem? or is there a different way I should approach it?
> Please help....
>
> Thank you,
> Ricky
>

Chart Colors

Does anyone know of a way you can customise the colors used for different
series in a chart using Reporting Services. Or is there any way to create a
customised palette to use. There only appears to be a set group of colors to
choose from but I want to use specific colors for specific data series.Install RS 2000 SP1 if you have not yet done, and please read this section
in the SP1 readme about using colors in charts:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
You may also search this newsgroup for chart color related posting. You
should find several posted examples.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joel Hines" <Joel Hines@.discussions.microsoft.com> wrote in message
news:E17477B8-A076-4113-AEF1-9A3B126D6ABA@.microsoft.com...
> Does anyone know of a way you can customise the colors used for different
> series in a chart using Reporting Services. Or is there any way to create
a
> customised palette to use. There only appears to be a set group of colors
to
> choose from but I want to use specific colors for specific data series.