Difference between Flat File Source Output ?

Can someone tell me the difference between the Flat File Source Output - External Columns and Output Columns ?

I always end up changing the datatype properities in both to make things work :-)

? Hi, Have you tried setting the datatypes for the external columns using the Advanced pane of the Flat File Connection Manager? What you set there ought to be carried through to the Output columns. Andrew Watt [MVP] <cgpl@.discussions.microsoft..com> wrote in message news:760e2f6b-3480-4430-850e-8b30237e462d@.discussions.microsoft.com... Can someone tell me the difference between the Flat File Source Output - External Columns and Output Columns ? I always end up changing the datatype properities in both to make things work :-)|||

By definition, output columns represent the column schema of the component itself. External columns represent the column schema of the database that the component is connected to.

Try editing the column defs in the connection manager and see if that eases the experience.

Did I get current source code?

Hi,
I have been working with a programmer overseas for the past six months.
Every so often I request and get the source code for a Delphi program
running on MSSQL. My question please is, how can I tell if I am getting
valid or current source code?
regards,
s
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 03-Dec-04Use Delphi to compile and test it?
This isn't a SQL Server question.
David Portas
SQL Server MVP
--

Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping b
y
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:[vbcol=seagreen]
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in
a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impac
t
> than trying to take seconds (or even minutes) off of long running procedur
es
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...

Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> > Hi everyone,
> >
> > We are running a SQL Server 2000 instance which is getting hit with
> > hundreds of queries a minute. The vast majority of these are very
> > short-lived, low overhead queries. Some of them are highly resouce
> > intensive (< 1%). My problem is that lately, our SQL Server CPU
> > utilization has climbed way up and I can't begin to figure out what is
> > causing it... the high volume of quick queries, or the low volume of
> > slow queries.
> >
> > I'm fairly skilled at SQL Server performance tuning, but this has got
> > me stumped. How can I isolate the category of queries are causing all
> > of the activity? I've tried using profiler, but it displays each of the
> > queries individually ... there's no way to group activity together by
> > meaningful categories (that I'm aware of).
> >
> > Can anyone point me in the right direction?
> >
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping by
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>> "Rich" <rich@.adgooroo.com> wrote in message
>> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
>> > Hi everyone,
>> >
>> > We are running a SQL Server 2000 instance which is getting hit with
>> > hundreds of queries a minute. The vast majority of these are very
>> > short-lived, low overhead queries. Some of them are highly resouce
>> > intensive (< 1%). My problem is that lately, our SQL Server CPU
>> > utilization has climbed way up and I can't begin to figure out what is
>> > causing it... the high volume of quick queries, or the low volume of
>> > slow queries.
>> >
>> > I'm fairly skilled at SQL Server performance tuning, but this has got
>> > me stumped. How can I isolate the category of queries are causing all
>> > of the activity? I've tried using profiler, but it displays each of the
>> > queries individually ... there's no way to group activity together by
>> > meaningful categories (that I'm aware of).
>> >
>> > Can anyone point me in the right direction?
>> >
>> The basic technique here is to use profiler or a server trace to gather
>> execution statistics for individual queries over a window of time. Then
>> load the results into a table and analyze them. For instance, grouping
>> by
>> query text (or truncated or scrubbed text) and then summing the IO and
>> CPU
>> statistics. This will isolate the queries driving the CPU use.
>> David
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impact
> than trying to take seconds (or even minutes) off of long running procedures
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> > Hi David,
> >
> > Great suggestion! I didn't know you could export this information from
> > Profiler into another format.
> >
> > One more question. Can you suggest which performance monitors I should
> > select in profiler to get just the query CPU and IO information for
> > completed queries? Or better yet, do you know of any online tutorials
> > with this information?
> >
> > Thanks!
> > -Rich
> >
> >
> > David Browne wrote:
> >> "Rich" <rich@.adgooroo.com> wrote in message
> >> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> >> > Hi everyone,
> >> >
> >> > We are running a SQL Server 2000 instance which is getting hit with
> >> > hundreds of queries a minute. The vast majority of these are very
> >> > short-lived, low overhead queries. Some of them are highly resouce
> >> > intensive (< 1%). My problem is that lately, our SQL Server CPU
> >> > utilization has climbed way up and I can't begin to figure out what is
> >> > causing it... the high volume of quick queries, or the low volume of
> >> > slow queries.
> >> >
> >> > I'm fairly skilled at SQL Server performance tuning, but this has got
> >> > me stumped. How can I isolate the category of queries are causing all
> >> > of the activity? I've tried using profiler, but it displays each of the
> >> > queries individually ... there's no way to group activity together by
> >> > meaningful categories (that I'm aware of).
> >> >
> >> > Can anyone point me in the right direction?
> >> >
> >>
> >> The basic technique here is to use profiler or a server trace to gather
> >> execution statistics for individual queries over a window of time. Then
> >> load the results into a table and analyze them. For instance, grouping
> >> by
> >> query text (or truncated or scrubbed text) and then summing the IO and
> >> CPU
> >> statistics. This will isolate the queries driving the CPU use.
> >>
> >> David
> >

Devide by zero error

help please.
I have sql statement that cast two date range into decimal and then devive
by one another.
When I compile this code I get error: Source: Microsoft OLE DB provider for
sql
Error desc: Devide by zero error encountered.
How would I resolve this issue?Please disregard. I found a thread that already had the answer.
search for : Divide by Zero
"ITDUDE27" wrote:

> help please.
> I have sql statement that cast two date range into decimal and then devive
> by one another.
> When I compile this code I get error: Source: Microsoft OLE DB provider fo
r
> sql
> Error desc: Devide by zero error encountered.
> How would I resolve this issue?

Development/Production Environment with Visual Source Safe

Am new to RS. Installed it on test server and was quickly able to
generate a report with multiple datasources and even write some code to
render it directly to a PDF file. Quite a useful and efficient tool.
Now, we want to bring RS into our development environment. We have
multiple developers working on an internal web app in VS.NET that we
want to incorporate reports into. Developers use Visual Source Safe to
bring project files from the development server to their own machines
to do development and then check changes back into the development
server. Periodically, our app is release from development to
production. Pretty straightforward.
What I'm curious about is:
(1) Does SourceSafe version control the report definitions? They look
like files in VS .NET but they reside in the RS database, so I'm
uncertain.
(2) Can developers install RS on their machines for developing reports
under VS.NET while using the RS server/database on the development
server for previewing?
Thanks.
JeffAnswers to your questions:
1. You can (and should) check reports, report projects, report
solutions--however you want to organize it--into VSS. But it is a separate
process that you have to enforce with policy and procedure as VSS will not
reach into the report catalog (database) and handle versioning there.
2. Yes. If your developers have VS.NET 2003, then just install any version
of Reporting Services on their workstations, just unselect and server
components if they are offered in the setup.
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"JeffW" <jwilson@.telnetww.com> wrote in message
news:1110499289.694022.73370@.g14g2000cwa.googlegroups.com...
> Am new to RS. Installed it on test server and was quickly able to
> generate a report with multiple datasources and even write some code to
> render it directly to a PDF file. Quite a useful and efficient tool.
> Now, we want to bring RS into our development environment. We have
> multiple developers working on an internal web app in VS.NET that we
> want to incorporate reports into. Developers use Visual Source Safe to
> bring project files from the development server to their own machines
> to do development and then check changes back into the development
> server. Periodically, our app is release from development to
> production. Pretty straightforward.
> What I'm curious about is:
> (1) Does SourceSafe version control the report definitions? They look
> like files in VS .NET but they reside in the RS database, so I'm
> uncertain.
> (2) Can developers install RS on their machines for developing reports
> under VS.NET while using the RS server/database on the development
> server for previewing?
> Thanks.
> Jeff
>

Determining Table Name from TableID in Cube Partition

Hi all,

I am trying to determine source table name of the cube partition using AMO.
I am able to get the partition source (which is of {Microsoft.AnalysisServices.DsvTableBinding} data type). There I can see the TableID property, but not the table's real database name. (TableID has dot between database schema and table name replaced by underscore).

Is there a way how to translate TableID into its original database name?

Thank you very much in advance.

Marek Stefanek
Use the TableID to find the DataTable object in the DataSourceView.Schema (which is a DataSet). Then in the ExtendedProperties collection of the DataTable, you should see some properties such as the original database table name and schema and the friendly name.|||Hi Matt,

Thank you a lot, it helped.

If I may, I would like to ask you another question: Is it possible to get the complete list of extended properties names? It is easy to retrieve the extended property value if you know the property name. But I have no idea from where I could get the property names.

Looking at properties of DataTable object during Script Task debug does not provide much information:

{System.Data.DataTable}
CaseSensitive: False
Columns: {System.Data.DataColumnCollection}
Constraints: {System.Data.ConstraintCollection}
Container: Nothing
DataSet: {System.Data.DataSet}
DefaultView: {System.Data.DataView}
DesignMode: False
DisplayExpression: ""
ExtendedProperties: {System.Data.PropertyCollection}
HasErrors: False
ChildRelations: {System.Data.DataRelationCollection.DataTableRelationCollection}
IsInitialized: True
Locale: {System.Globalization.CultureInfo}
MinimumCapacity: 50
Namespace: ""
ParentRelations: {System.Data.DataRelationCollection.DataTableRelationCollection}
Prefix: ""
PrimaryKey: {Length=0}
RemotingFormat: Xml {0}
Rows: {System.Data.DataRowCollection}
Site: Nothing
TableName: "dbo_VF_VYSLEDKY_2006"

It is probably obvious for skilled .NET developer, but for me its quite confusing.

Thanks!

Marek
|||

It's not a complete list, but here are the ones I think you're interested in:

public const string DBTableName = "DbTableName";
public const string DBSchemaName = "DbSchemaName";
public const string DBColumnName = "DbColumnName";
public const string FriendlyName = "FriendlyName";
public const string Description = "Description";