Is there any way to find out what the data souce connection string is at
runtime?
I have a report that is sent out to various sites, each with a common data
schema, but various connection strings. The report uses custom code in the
report rdl, so each time I deploy the report to a new site, I have to reset
the custom code connection string to match the individual site I am deploying
it to.
I would like to be able to have it dynamically query the connection string
so I can just pass it in to my code as a variable. Any ideas?
MichaelI think the report only knows about the data source name (if using shared ds)
What we have is a bunch of reports using the same data source.
We then deploy to different folders, as long as you are not "overwriting
data source" then you can just configure the different data sources in report
manager.
So 1 report gets deployed to 2 or more folders, each folder having its own
datasource. (the config manager is not intuitive)
We find this easier to manage 2 different regions using the same reports on
2 different databases like this.
We even have it so different sub regions have different sub folders using
linked reports with their sub region 'hardcoded' as a linked report param -
they are not even aware of the other sub regions' reports existing due to
security setup
You could always used linked reports to point back at the different folders
if the users complain.
"Michael C" wrote:
> Is there any way to find out what the data souce connection string is at
> runtime?
> I have a report that is sent out to various sites, each with a common data
> schema, but various connection strings. The report uses custom code in the
> report rdl, so each time I deploy the report to a new site, I have to reset
> the custom code connection string to match the individual site I am deploying
> it to.
> I would like to be able to have it dynamically query the connection string
> so I can just pass it in to my code as a variable. Any ideas?
> Michael|||Adolf,
I too am trying to deploy my solution like you described, one folder for
each region with it's own datasource. My problem is how do you get the
reports to use the correct datasource? For example, my VS2005 solution has a
shared data source which I use to build my reports. When the reports are
deployed, the reports went to Region1 directory and the datasource went to
Data Sources directory. I manually added a datasource in the Region1
directory with the same name as the one in the Data Sources directory, but
the reports aren't using it. How do I get them to use that one instead. I
can't find anything that explains how to do this. Thank you.
"adolf garlic" wrote:
> I think the report only knows about the data source name (if using shared ds)
> What we have is a bunch of reports using the same data source.
> We then deploy to different folders, as long as you are not "overwriting
> data source" then you can just configure the different data sources in report
> manager.
> So 1 report gets deployed to 2 or more folders, each folder having its own
> datasource. (the config manager is not intuitive)
> We find this easier to manage 2 different regions using the same reports on
> 2 different databases like this.
> We even have it so different sub regions have different sub folders using
> linked reports with their sub region 'hardcoded' as a linked report param -
> they are not even aware of the other sub regions' reports existing due to
> security setup
> You could always used linked reports to point back at the different folders
> if the users complain.
> "Michael C" wrote:
> > Is there any way to find out what the data souce connection string is at
> > runtime?
> >
> > I have a report that is sent out to various sites, each with a common data
> > schema, but various connection strings. The report uses custom code in the
> > report rdl, so each time I deploy the report to a new site, I have to reset
> > the custom code connection string to match the individual site I am deploying
> > it to.
> >
> > I would like to be able to have it dynamically query the connection string
> > so I can just pass it in to my code as a variable. Any ideas?
> >
> > Michaelsql
Showing posts with label various. Show all posts
Showing posts with label various. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Data Retrieval Question
I have a report that queries a table in the database (using a stored procedure) and displays various things about a customer. The report is basically displaying the same information that one of the screens in our web app displays. The problem is that one of the fields that I need to display on the report is not stored in the database table it is calculated. The calculation is performed by doing some database calls and various other stuff. A method exists for this calculation in our data layer that I would like to call from the report. My question is what is the best way to go about doing this call? Can/Should I do this from a custom code assembly?
Thanks...
Spyork
> Can I do this from a custom code assembly?
Yes. Here's how. ( http://msdn.microsoft.com/sql/sqlwarehouse/reportingservices/default.aspx?pull=/library/en-us/dnsql2k/html/erscstcode.asp )
Thanks...
Spyork
> Can I do this from a custom code assembly?
Yes. Here's how. ( http://msdn.microsoft.com/sql/sqlwarehouse/reportingservices/default.aspx?pull=/library/en-us/dnsql2k/html/erscstcode.asp )
> Should I do this from a custom code assembly?
Yes. Unless you want to rewrite your code in the funny little Reporting Services text editor dialog that accepts VB code.
- Ian
Sunday, March 11, 2012
Data Modeling Question
I'm designing a database for a medical group that must keep track of various
"People"
Some are doctors and some are patients.
The client currently categorizes patients according to the type of
procedure(s) they have been seen for (e..g, "Jane is a Botox patient because
she had Botox injections" while "Ralph is a hair transplant patient because
he's had hair transplants." And on and on it goes). These procedures are
obviously not mutually exclusive given that any given patient can have more
than one type of procedure.
As I see the situation we have [Patients] and [Procedures]. We do NOT have
[patient types] even though that's how the client understands them. We just
have patients who have various procedures.
My whiz bang plan is to simply have a many-to-many relationship between
[Patients] and [Procedures].
This will work fine for identifying the so called "patient types"... just
SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
"the Botox patients".
Question 1: What would be a good way to classify a patient who has not yet
had any procedure? Say Bambi comes in and gets scheduled for Botox. The
doctors would want her to show up on reports as a "Botox patient" even
though she hasn't yet had the procedure.
Question 2: Given that [Doctors] and [Patients] are fundamentally different
"things" in this database, is it reasonable to have two tables - one for
Doctors and another for Patients... or is it recommended to have one table
("People") and then have some "PersonType" column that flags the person as a
doctor or a patient (and then have a bunch of NULLS for columns not relevant
to each row's designated "person type"). The one-table approach seems kind
of ugly. Just wanted some feedback on this before I go off and implement.
Thank you for your time and consideration.
-JThis has similarities to the database I work with, which is hr/payroll
data. There is a table of 'positions' (job titles a person can have).
You may have an equivalent 'procedures' table. Procedures table would
likely have budget/costs associated with the procedure.
Another table would have the procedure history for a person. A person
could have multiple records in that table, each would have a key for
the person, the procedure name, the procedure key (for joining to
'procedures' table). This table would have a startdate and enddate for
the procedure. A person who is scheduled, but has not yet had the
procedure merely has a futuredated record in this table (based on
startdate). When the procedure is done, you give the record an
enddate.
Doctors and patients all belong in the same table b/c a doctor could be
a patient and vice versa. Each person has their own unique id and also
a second field which is the id of that persons PCP. So if you have:
name, uniqueid, PCPid
dr smith, 1, 0
dr jones, 2, 0
sick guy,3,1
dr williams,4,2
This means that sick guy goes to dr. smith. Dr williams goes to Dr.
Jones.
You likely WILL need a flag field that lets you clearly determine who
is a doc ('flagdoc' that has y/n or 1/0 for everyone).
Based on similar relationships, this is how my company does it.
Theoretically, maybe you don't have to put the procedure name in the
procedure history, but it's nice having it there.
HTH,
wayne|||>> is it reasonable to have two tables - one for Doctors and another for Pat
ients... or is it recommended to have one table ("People") <<
Doctors and patients are logically different, so I would scrape the
idea of a general "Peoiple" table. Where is the "Treatments" table
that would show the dates (scheduled, actual, etc.), location,
doctor(s), etc. for Bambi's Botox?
As a patient. The assorted procedures done to them are events and not
attributes of the patient himself.|||> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
I would suggest you document people throughout their lifecycle with you. So
when the patient comes in, planning to get Botox, a row is created in the
Patients and PatientProcedures table. Another table would be related to the
patientProcedures table that would document the status of the relationship.
Planned, Scheduled, Occurred, FollowUp, OopsPatientLooksLikeJoanRivers and
so on (I will assume you are
with the jokes since you started it out
with "Bambi" "). Then you have the best of both scenarios.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
Tough call. I would would not suggest the one table approach, but a table
for generic "people" attributes, and another for patient attributes. I
wouldn't have a PersonType in this case because a person could be both (the
key of the two subordinate tables would be the same as for the Person table
so a person could only be mapped once.) The existance of a row in the
patient table would indicate that the person is a patient. (Will you have
nurses, sleep makers (can't spell anesthesiologist) and such. Particularly
for billing and/or scheduling I would imagine.)
Now you have everything you need (I think) you can tell the type of patient
immediately, including their status "Planned" "Botox", "Scheduled" "Hair
Transplant" and after > 1 procedures takes place: "Planned" "Repeat"
"Botox". Then they can get specific about the types of patient that they
are looking at.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jordan R." <A@.B.COM> wrote in message
news:ux0URV4LGHA.3100@.tk2msftngp13.phx.gbl...
> I'm designing a database for a medical group that must keep track of
> various "People"
> Some are doctors and some are patients.
> The client currently categorizes patients according to the type of
> procedure(s) they have been seen for (e..g, "Jane is a Botox patient
> because she had Botox injections" while "Ralph is a hair transplant
> patient because he's had hair transplants." And on and on it goes). These
> procedures are obviously not mutually exclusive given that any given
> patient can have more than one type of procedure.
> As I see the situation we have [Patients] and [Procedures]. We do NOT have
> [patient types] even though that's how the client understands them. We
> just have patients who have various procedures.
> My whiz bang plan is to simply have a many-to-many relationship between
> [Patients] and [Procedures].
> This will work fine for identifying the so called "patient types"... just
> SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
> "the Botox patients".
> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
> Thank you for your time and consideration.
> -J
>|||wouldn't it be pretty common for a doctor to also be a patient of
his/her own group practice'
The futuredated record mentioned above could be a bit dangerous b/c
people will definitely back out on things. Our place has a whole
module for 'applicants'. If they are hired, then they get records for
jobs, etc. The data model for people who say they 'want to do something
in the future' could be pretty complex...|||The more I ponder, the one table layout works well when the patient
only goes to one doctor and does not switch too often. We use the
above layout for employees and their dependents (which is a nice,
static relationship).
It does sound like the patients at your place can have numerous doctors
work on them over the course of time. Your db revolves around the
procedure--which can have one patient, one or two docs. Splitting out
may well be the best way to go.|||>> wouldn't it be pretty common for a doctor to also be a patient of his/her
own group practice? <<
No, not in the US; insurnace companies would go nuts. Can you say
"FRAUD!!"?
So we need both an actuial and schedule appointment date. Sounds like
a good source for stats and predictions!|||Louis Davidson wrote:
> Tough call. I would would not suggest the one table approach, but a table
> for generic "people" attributes, and another for patient attributes.
Which country? The generic "people" approach seems to be the one taken
by the UK's National Health Service (the world's largest?) In the
interest of standards, they have published their data dictionary:
http://www.nhsia.nhs.uk/datastandar...
.asp?shownav=1
Jamie.|||Yes, but a person in one country is still a person in another. I would
suggest that whatever this person needs would be the best approach. At a
minimum First Name, Last Name, mailing address, etc, perhaps some form of Id
Number, perhaps. The basics.
Some of these things in their list would be very offensive to Americans.
For some reason we will give up our "tax" number (social security number,
which is becoming too much of a citizen id number)
Then the patient would have a file number, perhaps if all records are stored
in a paper format, medical information, etc. This table would be related to
the appointment calendar, billing.
Then doctors information, abilities, schedule, etc.
By no means is this a required way to do it. Having two tables with some
minor overlap of information is not horrible when the two concepts are going
to have little interaction (for example, if you had to balance a doctor's
appointment schedule as a patient AND a doctor, this would be essential.)
Frankly if the only overlapping information in a medical system is that a
doctor is entered as a patient of another doctor AND a doctor of patients,
the world would rejoice at not having to explain why they want to see the
doctor 10 times.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1139819374.327665.281610@.f14g2000cwb.googlegroups.com...
> Louis Davidson wrote:
> Which country? The generic "people" approach seems to be the one taken
> by the UK's National Health Service (the world's largest?) In the
> interest of standards, they have published their data dictionary:
> http://www.nhsia.nhs.uk/datastandar...t.asp?shownav=1
> Jamie.
> --
>|||Louis Davidson wrote:
> Some of these things in their list would be very offensive to Americans.
> For some reason we will give up our "tax" number (social security number,
> which is becoming too much of a citizen id number)
That's why I opened with, 'Which country?" :) If the OP (or other
interested reader) is in the UK then choosing to follow the NHS model
is one way of resolving the quandary.
FWIW here we seem to be moving in the opposite direction e.g. identity
cards for all :(
Jamie
"People"
Some are doctors and some are patients.
The client currently categorizes patients according to the type of
procedure(s) they have been seen for (e..g, "Jane is a Botox patient because
she had Botox injections" while "Ralph is a hair transplant patient because
he's had hair transplants." And on and on it goes). These procedures are
obviously not mutually exclusive given that any given patient can have more
than one type of procedure.
As I see the situation we have [Patients] and [Procedures]. We do NOT have
[patient types] even though that's how the client understands them. We just
have patients who have various procedures.
My whiz bang plan is to simply have a many-to-many relationship between
[Patients] and [Procedures].
This will work fine for identifying the so called "patient types"... just
SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
"the Botox patients".
Question 1: What would be a good way to classify a patient who has not yet
had any procedure? Say Bambi comes in and gets scheduled for Botox. The
doctors would want her to show up on reports as a "Botox patient" even
though she hasn't yet had the procedure.
Question 2: Given that [Doctors] and [Patients] are fundamentally different
"things" in this database, is it reasonable to have two tables - one for
Doctors and another for Patients... or is it recommended to have one table
("People") and then have some "PersonType" column that flags the person as a
doctor or a patient (and then have a bunch of NULLS for columns not relevant
to each row's designated "person type"). The one-table approach seems kind
of ugly. Just wanted some feedback on this before I go off and implement.
Thank you for your time and consideration.
-JThis has similarities to the database I work with, which is hr/payroll
data. There is a table of 'positions' (job titles a person can have).
You may have an equivalent 'procedures' table. Procedures table would
likely have budget/costs associated with the procedure.
Another table would have the procedure history for a person. A person
could have multiple records in that table, each would have a key for
the person, the procedure name, the procedure key (for joining to
'procedures' table). This table would have a startdate and enddate for
the procedure. A person who is scheduled, but has not yet had the
procedure merely has a futuredated record in this table (based on
startdate). When the procedure is done, you give the record an
enddate.
Doctors and patients all belong in the same table b/c a doctor could be
a patient and vice versa. Each person has their own unique id and also
a second field which is the id of that persons PCP. So if you have:
name, uniqueid, PCPid
dr smith, 1, 0
dr jones, 2, 0
sick guy,3,1
dr williams,4,2
This means that sick guy goes to dr. smith. Dr williams goes to Dr.
Jones.
You likely WILL need a flag field that lets you clearly determine who
is a doc ('flagdoc' that has y/n or 1/0 for everyone).
Based on similar relationships, this is how my company does it.
Theoretically, maybe you don't have to put the procedure name in the
procedure history, but it's nice having it there.
HTH,
wayne|||>> is it reasonable to have two tables - one for Doctors and another for Pat
ients... or is it recommended to have one table ("People") <<
Doctors and patients are logically different, so I would scrape the
idea of a general "Peoiple" table. Where is the "Treatments" table
that would show the dates (scheduled, actual, etc.), location,
doctor(s), etc. for Bambi's Botox?
As a patient. The assorted procedures done to them are events and not
attributes of the patient himself.|||> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
I would suggest you document people throughout their lifecycle with you. So
when the patient comes in, planning to get Botox, a row is created in the
Patients and PatientProcedures table. Another table would be related to the
patientProcedures table that would document the status of the relationship.
Planned, Scheduled, Occurred, FollowUp, OopsPatientLooksLikeJoanRivers and
so on (I will assume you are
with the jokes since you started it outwith "Bambi" "). Then you have the best of both scenarios.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
Tough call. I would would not suggest the one table approach, but a table
for generic "people" attributes, and another for patient attributes. I
wouldn't have a PersonType in this case because a person could be both (the
key of the two subordinate tables would be the same as for the Person table
so a person could only be mapped once.) The existance of a row in the
patient table would indicate that the person is a patient. (Will you have
nurses, sleep makers (can't spell anesthesiologist) and such. Particularly
for billing and/or scheduling I would imagine.)
Now you have everything you need (I think) you can tell the type of patient
immediately, including their status "Planned" "Botox", "Scheduled" "Hair
Transplant" and after > 1 procedures takes place: "Planned" "Repeat"
"Botox". Then they can get specific about the types of patient that they
are looking at.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jordan R." <A@.B.COM> wrote in message
news:ux0URV4LGHA.3100@.tk2msftngp13.phx.gbl...
> I'm designing a database for a medical group that must keep track of
> various "People"
> Some are doctors and some are patients.
> The client currently categorizes patients according to the type of
> procedure(s) they have been seen for (e..g, "Jane is a Botox patient
> because she had Botox injections" while "Ralph is a hair transplant
> patient because he's had hair transplants." And on and on it goes). These
> procedures are obviously not mutually exclusive given that any given
> patient can have more than one type of procedure.
> As I see the situation we have [Patients] and [Procedures]. We do NOT have
> [patient types] even though that's how the client understands them. We
> just have patients who have various procedures.
> My whiz bang plan is to simply have a many-to-many relationship between
> [Patients] and [Procedures].
> This will work fine for identifying the so called "patient types"... just
> SELECT... WHERE a Procedure Type is "botox" (however I encode that) to get
> "the Botox patients".
> Question 1: What would be a good way to classify a patient who has not yet
> had any procedure? Say Bambi comes in and gets scheduled for Botox. The
> doctors would want her to show up on reports as a "Botox patient" even
> though she hasn't yet had the procedure.
> Question 2: Given that [Doctors] and [Patients] are fundamentally
> different "things" in this database, is it reasonable to have two tables -
> one for Doctors and another for Patients... or is it recommended to have
> one table ("People") and then have some "PersonType" column that flags the
> person as a doctor or a patient (and then have a bunch of NULLS for
> columns not relevant to each row's designated "person type"). The
> one-table approach seems kind of ugly. Just wanted some feedback on this
> before I go off and implement.
> Thank you for your time and consideration.
> -J
>|||wouldn't it be pretty common for a doctor to also be a patient of
his/her own group practice'
The futuredated record mentioned above could be a bit dangerous b/c
people will definitely back out on things. Our place has a whole
module for 'applicants'. If they are hired, then they get records for
jobs, etc. The data model for people who say they 'want to do something
in the future' could be pretty complex...|||The more I ponder, the one table layout works well when the patient
only goes to one doctor and does not switch too often. We use the
above layout for employees and their dependents (which is a nice,
static relationship).
It does sound like the patients at your place can have numerous doctors
work on them over the course of time. Your db revolves around the
procedure--which can have one patient, one or two docs. Splitting out
may well be the best way to go.|||>> wouldn't it be pretty common for a doctor to also be a patient of his/her
own group practice? <<
No, not in the US; insurnace companies would go nuts. Can you say
"FRAUD!!"?
So we need both an actuial and schedule appointment date. Sounds like
a good source for stats and predictions!|||Louis Davidson wrote:
> Tough call. I would would not suggest the one table approach, but a table
> for generic "people" attributes, and another for patient attributes.
Which country? The generic "people" approach seems to be the one taken
by the UK's National Health Service (the world's largest?) In the
interest of standards, they have published their data dictionary:
http://www.nhsia.nhs.uk/datastandar...
.asp?shownav=1
Jamie.|||Yes, but a person in one country is still a person in another. I would
suggest that whatever this person needs would be the best approach. At a
minimum First Name, Last Name, mailing address, etc, perhaps some form of Id
Number, perhaps. The basics.
Some of these things in their list would be very offensive to Americans.
For some reason we will give up our "tax" number (social security number,
which is becoming too much of a citizen id number)
Then the patient would have a file number, perhaps if all records are stored
in a paper format, medical information, etc. This table would be related to
the appointment calendar, billing.
Then doctors information, abilities, schedule, etc.
By no means is this a required way to do it. Having two tables with some
minor overlap of information is not horrible when the two concepts are going
to have little interaction (for example, if you had to balance a doctor's
appointment schedule as a patient AND a doctor, this would be essential.)
Frankly if the only overlapping information in a medical system is that a
doctor is entered as a patient of another doctor AND a doctor of patients,
the world would rejoice at not having to explain why they want to see the
doctor 10 times.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"onedaywhen" <jamiecollins@.xsmail.com> wrote in message
news:1139819374.327665.281610@.f14g2000cwb.googlegroups.com...
> Louis Davidson wrote:
> Which country? The generic "people" approach seems to be the one taken
> by the UK's National Health Service (the world's largest?) In the
> interest of standards, they have published their data dictionary:
> http://www.nhsia.nhs.uk/datastandar...t.asp?shownav=1
> Jamie.
> --
>|||Louis Davidson wrote:
> Some of these things in their list would be very offensive to Americans.
> For some reason we will give up our "tax" number (social security number,
> which is becoming too much of a citizen id number)
That's why I opened with, 'Which country?" :) If the OP (or other
interested reader) is in the UK then choosing to follow the NHS model
is one way of resolving the quandary.
FWIW here we seem to be moving in the opposite direction e.g. identity
cards for all :(
Jamie
Friday, February 24, 2012
Data leaked a buffer with ID 1 of type 1
We are using a datareader component to retrieve data from a Pervasive 8.6 database. We have four separate datareader components in various packages retrieving data into our datawarehouse in SQL2005. One of the components has started to fail regularly with the following error.
Date 6/8/2007 3:05:00 AM
Log Job History (LoadMAXDailyBookings)
Step ID 1
Server US-CO-DEN-101
Job Name LoadMAXDailyBookings
Step Name Load Bookings Step
Duration 00:00:37
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Destination Write Bookings Detail" (121)" wrote 0 rows.
End Info
Log:
Name: PipelineBufferLeak
Computer: US-CO-DEN-101
Message: component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
End Log
Log:
Name: OnTaskFailed
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnPostExecute
Computer: US-CO-DEN-101
Message: (blank)
End Log
Log:
Name: OnWarning
Computer: US-CO-DEN-101
Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Log
Warning: 2007-06-08 03:05:36.92
Code: 0x80019002
Source: LoadMAXDailyBookings
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution...
The other components run without any problems as did this one up until we installed service pack 2. We then started getting these occasional failures. Any thoughts on what is happening here?
Thanks,
Phil
Do you have package logging turned on? The PipelineBufferLeak, I believe, is a warning, not an error, and hence would not stop execution.Turning on package logging might help get a more clear error message....|||
I do have logging turned on and the error in the log is more vague than within the job history. I'm copying in the first rows indicating the error from the log. The execution is halted due to whatever error is happening.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookings,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{F4C2AE85-1513-446A-BEC2-43A861F79D26},6/8/2007 3:05:36 AM,6/8/2007 3:05:36 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
|||Make a copy of that package and strip everything out of the data flow except for the data reader source. Then hook it into a row count transformation. Re-run the package. What happens?|||The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.|||
pkdenver wrote:
The job ran successfully returning 64 rows. However, the package doesn't fail regularly so the fact that this instance ran successfully probably won't offer much. I will load this abbreviated package to the server and schedule it in the same time frame as the current package, which fails with the buffer leak error. I'll turn logging on to capture whatever message might come across.
The only reason I suggested this is because the error message returned is a DataReader failure message, so this takes out all processing that might unduly influence things. Can you use something other than a DataReader Source? OLE DB Source, for instance?|||
I see your point regarding isolating the datareader. As recommended last week, I deployed a package which performs the query and dumps to a rowcounter. We get the same failure as the regular job. Both jobs failed on one day (Sat). It ran fine on Sun and Mon. Here are the error rows from the logger: I'm wondering if we are getting a timeout on this query as it is a more complex query than the other three jobs running against the DB.
Regarding an alternate driver, the Pervasive OLE DB driver is very unstable in the SSIS environment and so it can't be used at all. We are looking at the timeout value to see if the Pervasive ODBC driver can be tweaked.
OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016268,0x,Execute phase is beginning.
OnPipelinePrePrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,PrimeOutput will be called on a component. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1071607563,0x,The component "Get Bookings from MAX" (1) was unable to process the data.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Bookings from MAX" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
OnPipelinePostPrimeOutput,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,A component has returned from its PrimeOutput call. : 1 : Get Bookings from MAX
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
OnError,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
OnError,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.
OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,1074016264,0x,Post Execute phase is beginning.
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,0,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:35 AM,6/9/2007 3:05:35 AM,50,0x,Post Execute
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Post Execute
OnInformation,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.
OnInformation,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1074016265,0x,Cleanup phase is beginning.
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,50,0x,Cleanup
OnProgress,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,100,0x,Cleanup
PipelineBufferLeak,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,component "Get Bookings from MAX" (1) leaked a buffer with ID 1 of type 1 with 0 rows and a reference count of 1.
OnTaskFailed,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnPostExecute,US-CO-DEN-101,SQLService,Migrate Booking Data from Max,{57d33354-ab1d-4225-9ca8-cb2e902b1782},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
OnWarning,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,-2147381246,0x,SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
OnPostExecute,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,0,0x,(null)
PackageEnd,US-CO-DEN-101,SQLService,LoadMAXDailyBookingsTest,{EB65E4AD-3861-46EA-BA30-142DA62EF044},{7E7B1A7D-2179-4B74-A7A6-83FC99A5FBF6},6/9/2007 3:05:36 AM,6/9/2007 3:05:36 AM,1,0x,End of package execution.
Data Issues Very frustrated
I have various sql, sybase & access databases that i need to report from onto
the 1 table. A number of joins link them all together. As you can only use 1
dataset, what would be the best way to report on my data into the one table.
I have spent weeks trying to work this out. ThanksYou could use a stored procedure or view to do all the joins and then call
the one view or stored procedure from your dataset.
John K.
===
"Tango" wrote:
> I have various sql, sybase & access databases that i need to report from onto
> the 1 table. A number of joins link them all together. As you can only use 1
> dataset, what would be the best way to report on my data into the one table.
> I have spent weeks trying to work this out. Thanks|||Thanks John,
Whats your preference here. Id prefer to only have to research 1 options as
time is limited at this stage. Let me know if you need any more info.
"John K" wrote:
> You could use a stored procedure or view to do all the joins and then call
> the one view or stored procedure from your dataset.
> John K.
> ===> "Tango" wrote:
> > I have various sql, sybase & access databases that i need to report from onto
> > the 1 table. A number of joins link them all together. As you can only use 1
> > dataset, what would be the best way to report on my data into the one table.
> > I have spent weeks trying to work this out. Thanks
the 1 table. A number of joins link them all together. As you can only use 1
dataset, what would be the best way to report on my data into the one table.
I have spent weeks trying to work this out. ThanksYou could use a stored procedure or view to do all the joins and then call
the one view or stored procedure from your dataset.
John K.
===
"Tango" wrote:
> I have various sql, sybase & access databases that i need to report from onto
> the 1 table. A number of joins link them all together. As you can only use 1
> dataset, what would be the best way to report on my data into the one table.
> I have spent weeks trying to work this out. Thanks|||Thanks John,
Whats your preference here. Id prefer to only have to research 1 options as
time is limited at this stage. Let me know if you need any more info.
"John K" wrote:
> You could use a stored procedure or view to do all the joins and then call
> the one view or stored procedure from your dataset.
> John K.
> ===> "Tango" wrote:
> > I have various sql, sybase & access databases that i need to report from onto
> > the 1 table. A number of joins link them all together. As you can only use 1
> > dataset, what would be the best way to report on my data into the one table.
> > I have spent weeks trying to work this out. Thanks
Sunday, February 19, 2012
Data Integration Tools Recommendation
Hi all
In our company we do a lot of data imports from various files/data stores.
I was wondering what are the good Data Integration software out there which can provide "industrial-strength" to this process. I heard one of them is Informatica (www.informatica.com) but that seems more for Data Warehousing.
Our DB is SQL Server. Maybe we should change that too?
Comments/Suggestions, anyone?
Much Appreciated,
ManikWhy can't you use DTS from SQL server?|||Hi Satya
We use DTS right now. However, the problem is that DTS is not giving us very good performance.
What we really need is a software that can help us model the data import process and then run the import and provide logging etc.
DTS works ok for now but we have cases where we have to update several thousand records against a > 1000 row Excel file and we have to do this very often (and also on demand). This is very slow.
Also, the various imports are getting bigger and more cumbersome to manage, hence the need for an integrated tool that can create, manage and execute these imports in a systematic manner.
Regards
Manik|||Not aware of such tools but you can search under SQL Performance (http://www.sql-server-performance.com/) website for any recommendations and information.
HTH
In our company we do a lot of data imports from various files/data stores.
I was wondering what are the good Data Integration software out there which can provide "industrial-strength" to this process. I heard one of them is Informatica (www.informatica.com) but that seems more for Data Warehousing.
Our DB is SQL Server. Maybe we should change that too?
Comments/Suggestions, anyone?
Much Appreciated,
ManikWhy can't you use DTS from SQL server?|||Hi Satya
We use DTS right now. However, the problem is that DTS is not giving us very good performance.
What we really need is a software that can help us model the data import process and then run the import and provide logging etc.
DTS works ok for now but we have cases where we have to update several thousand records against a > 1000 row Excel file and we have to do this very often (and also on demand). This is very slow.
Also, the various imports are getting bigger and more cumbersome to manage, hence the need for an integrated tool that can create, manage and execute these imports in a systematic manner.
Regards
Manik|||Not aware of such tools but you can search under SQL Performance (http://www.sql-server-performance.com/) website for any recommendations and information.
HTH
Subscribe to:
Posts (Atom)