Dundas Web Chart Control

I'm having trouble understanding how to build my bar chart. I have a column of info called Issue and then I'm coutning the record.

I bascially need to alter my query into time periods. I need a series for quarter 1 (1/1/2006 - 3/1/2006), one for quarter 2, 3, 4 and the entire year as a whole.

Can this data all be organized in a single select query?

Here is my current query getting the year as a whole data only:

SELECT

Issue,COUNT(*)AS NumRecordsFROM CSP_Item CI

INNER

JOIN CSP_ProblemNotification CPON CP.ID= CI.CSPNum

WHERE

CP.PNDateBETWEEN'01/01/2007'AND'01/31/2007'

GROUP

BY IssueORDERBY NumRecordsASC

Maybe...

SelectDatePart(quarter, theDate) as Quarter.

group byDatePart(quarter, theDate)

Dual Xeon or Dual P4 for SQL Server?

I am planning to build a server to be used as a SQL Server and web server.
Right now I can only use a single box for both.

I have read some threads were dual processors are having problems with some
parallel queries and the suggestions of having sql server use a single CPU.

My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
sql server and the other for everything else?

John DalbergOn Sun, 21 Nov 2004 07:14:17 -0800, John Dalberg wrote:

> I am planning to build a server to be used as a SQL Server and web server.
> Right now I can only use a single box for both.
> I have read some threads were dual processors are having problems with some
> parallel queries and the suggestions of having sql server use a single CPU.
> My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
> or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
> If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
> sql server and the other for everything else?
> John Dalberg

I forgot to mention also what about AMD's Athlon since I can get faster
Athlons for the same amount of money for Intel CPUs? Are dual Athlon
motherboards a good choice for SQL Server?

John Dalberg|||In article <u1ekecr53aij.1ek79hzro64q4$.dlg@.40tude.net>,
john_dd@.hotmail.com says...
> I am planning to build a server to be used as a SQL Server and web server.
> Right now I can only use a single box for both.
> I have read some threads were dual processors are having problems with some
> parallel queries and the suggestions of having sql server use a single CPU.
> My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
> or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
> If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
> sql server and the other for everything else?

John, you can't use Dual P4's any more, Intel fixed that after the P3.

If you want to build a cheap but fast SQL Server, assuming SQL 2000 or
higher, tape a look at the ASUS PC-DL Deluxe motherboard. I have about
30 of these running the following:

ASUS PC-DL Deluxe
Dua Xeon 2.4G (or faster) (make sure they are same lot/part)
2GB or 4GB of RAM (does not require ECC RAM)
It has onboard SATA and IDE, you can use a total of 6 drives between the
connectors without a problem.

The 2GB systems with Dual 250GB SATA drives, video, case, CD-ROM, etc...
are under $1900 each.

If you want a big box, like this:

PC-DL Deluxe
Dual Xeon 2.8ghz
2 x 80GB IDE (OS = 12GB, LOGS = 60GB)
6 x 250GB IDE (for DATA files)
Promise SX6000 Hardware IDE RAID Controller (hot swap IDE drives)
cheap video
Chenbro Server case with Dual 550W PSU's
and Windows 2003 Standard Server 5cal

This box runs about $4000

I run SQL 2000 on Dual P3 and Dual/Quad Xeon without any problems.

If you get a P4, get one that does Hyper-Threading, and use Server 2003
so that you get the most benefit from it.

As for Athlon, faster is a matter of what task is being performed and
how stable you want the system to be. When it comes to business systems
I use Intel CPU's only, but that's a personal preference and has never
cost me a job or stability.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||John Dalberg (john_dd@.hotmail.com) writes:
> I am planning to build a server to be used as a SQL Server and web server.
> Right now I can only use a single box for both.
> I have read some threads were dual processors are having problems with
> some parallel queries and the suggestions of having sql server use a
> single CPU.
> My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
> or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
> If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
> sql server and the other for everything else?

Machine configurations is not my best game, but if you are going to have
both SQL Server and web server on the same box, I would definitely go for
two CPUs. (Or at least one CPU that is hyper-threaded.)

As for SQL Server and parallel queries, yes, there is a potential problem.
I have seen more than once, SQL Server being too optimistic about parallel
query plans, and taken a plan with poor performance. What we usually do,
when we run into these queries is to add the query hint OPTION (MAXDOP 1)
which turns of parallellism for that query.

A more definitive cure is to set the configuration option "Max degress of
parllellism" to 1. Note that this is not the same as confining SQL Server
to one CPU only, but that one single query can only take one processor. Two
users executing different queries can still get one CPU each.

Giving SQL Server only one CPU *may* be a good idea, if you expect the
web server and the rest to need substantial amount of resources. But you
could also get the effect that one processor is mainly idle, while the
other is working around the clock.

So my recommendation would be set "Max degrees of Parallelism" to 1 (Or
to 2, if you have two hyper-threaded CPUs), but let SQL Server use both
CPUs.

Finally, there may be a licensing issue. I vaguely recall that for some
editions that you pay a license per processor, but please check this with
Microsoft. Licensing is definitely not my best game.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <Xns95A8BE037B8EAYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Finally, there may be a licensing issue. I vaguely recall that for some
> editions that you pay a license per processor, but please check this with
> Microsoft. Licensing is definitely not my best game.

I was told by the MS People in the Great Lakes MS Office that HT CPU's
still count as 1 CPU for licensing.

One other thing, unless he's using SBS, I was also told that a Web
Server accessing a MS SQL Database, requires a CPU License on the
Database server for each physical CPU the SQL server is permitted to
use.

I have always licensed our SQL Servers in CPU mode when being accessed
by a web server using anonymous connections to the web server.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Erland Sommarskog wrote:
> John Dalberg (john_dd@.hotmail.com) writes:
> > I am planning to build a server to be used as a SQL Server and web
server.
> > Right now I can only use a single box for both.
> > bob.mckenna@.rbc.com
> > I have read some threads were dual processors are having problems
with
> > some parallel queries and the suggestions of having sql server use
a
> > single CPU.
> > My budget is limited so I am debating whether to get 2.6G dual xeon
533FSB
> > or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
> > If I get a dual cpu motherboard, is it a good idea to have 1 cpu
used for
> > sql server and the other for everything else?
> Machine configurations is not my best game, but if you are going to
have
> both SQL Server and web server on the same box, I would definitely go
for
> two CPUs. (Or at least one CPU that is hyper-threaded.)
> As for SQL Server and parallel queries, yes, there is a potential
problem.
> I have seen more than once, SQL Server being too optimistic about
parallel
> query plans, and taken a plan with poor performance. What we usually
do,
> when we run into these queries is to add the query hint OPTION
(MAXDOP 1)
> which turns of parallellism for that query.
> A more definitive cure is to set the configuration option "Max
degress of
> parllellism" to 1. Note that this is not the same as confining SQL
Server
> to one CPU only, but that one single query can only take one
processor. Two
> users executing different queries can still get one CPU each.
> Giving SQL Server only one CPU *may* be a good idea, if you expect
the
> web server and the rest to need substantial amount of resources. But
you
> could also get the effect that one processor is mainly idle, while
the
> other is working around the clock.
> So my recommendation would be set "Max degrees of Parallelism" to 1
(Or
> to 2, if you have two hyper-threaded CPUs), but let SQL Server use
both
> CPUs.
>
> Finally, there may be a licensing issue. I vaguely recall that for
some
> editions that you pay a license per processor, but please check this
with
> Microsoft. Licensing is definitely not my best game.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

DTSTransform.DataConvert programmatic access?

I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.

The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.

I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.

I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?

Here is what is left of my code:

IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}

Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.

Phil Burns

Aptify

DTSTransform.DataConvert programmatic access?

I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.

The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.

I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.

I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?

Here is what is left of my code:

IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}

Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.

Phil Burns

Aptify

DTS/ETL/Metadata/Data Lineage

Hi,
I am trying to build a metadata capture application for my ETL processes
in SQL Server DTS Packages, any tips or pointers to articles or tutorials? I
tried to look up information online and could not find anything, I think I
might not have used the correct keywords. Well anyway thanks in advance, I'll
post anything I find.
Ignacio
We have the following opening. Given your experience, thougth I would check
with you to see if you are available for a 1 month project in Des Moines, IA.
Our Client is seeking the following:
The need we have is for SQL Server stored procedure and DTS expertise for a
1 month duration. The source will be an AS/400.
Most of the candidates he has received have Informatica and Oracle.
If you have an interest, please contact me at the following.
Mindy Sherwood
Technisource Recruiter
1-800-933-1725
msherwood@.technisource.com
url:http://www.ureader.com/msg/1145520.aspx

DTS/ETL/Metadata/Data Lineage

Hi,
I am trying to build a metadata capture application for my ETL processes
in SQL Server DTS Packages, any tips or pointers to articles or tutorials?
I
tried to look up information online and could not find anything, I think I
might not have used the correct keywords. Well anyway thanks in advance, I'l
l
post anything I find.
IgnacioWe have the following opening. Given your experience, thougth I would check
with you to see if you are available for a 1 month project in Des Moines, IA
.
Our Client is seeking the following:
The need we have is for SQL Server stored procedure and DTS expertise for a
1 month duration. The source will be an AS/400.
Most of the candidates he has received have Informatica and Oracle.
If you have an interest, please contact me at the following.
Mindy Sherwood
Technisource Recruiter
1-800-933-1725
msherwood@.technisource.com
url:http://www.ureader.com/msg/1145520.aspx

DTS with SQL Express

We have build a conversion for our customers to convert their data from our existing database system to Microsoft SQL. We've tested it with MSDE and SQL Server 2000. We based this conversion off of 183 separate DTS scripts saved as VB 6 files. We then heavily customized them to produce a final EXE in VB 6 that our end-users can run. They answer a few questions and then poof, SQL database. Works great.

Now enter SQL Server and SQL Express 2005. With no support for DTS in Express we expect out VB application will fail to execute. As 80% of our customers are MSDE/SQLExpress level customers, this is a real problem for us.

What are my options here?

When SQL Server 2005 ships, we will simultaneously release a "Backward Compatibility" redist. It will be available on the web and will contain, among other things, the same updated DTS 2000 binaries that are installed with SQL 2005.

|||That is really fantastic news. Thank you.