Peduliar problem in SSRS

Hi all ,

I am encountering a peculiar problem while creating a linked report in SSRS.

The scenario:

My first reports contains 3 columns . The first 3 columns contain data from vendor table in AdventureWorks DB. They are VendorID , Name, CreditRating. The fourth column contains a link to the second report. It is just a link which jumps to the other report and passes the above three values as parameters to the second report.

The second report contains 4 parameters which includes VendorID , Name, CreditRating and one more Postedby parameter which has to be entered manually when the second report is displayed. The three parameters from first report are passed on and one extra parameter has to entered manually then only the second report displays.

It works fine when I check it on Visual Studio preview mode. I click the link for a particular row in First Report and it asks me to enter the Postedby Parameter. After entering it it displays the 2nd report. But the problem arises when I deploy it to report server. When I click the link on first report it throughs an error like " The Parameter 'PostedBy' is missing a value" whereas it should prompt the user to enter the PostedBy Parameter.

I am stuck in this and not able to understand why this happens in ReportServer and not in Visual Studio Preview mode.

Does anybody has any idea why this is happening ?

Any Suggestion will be appreciated.

Regards...

Girija Shankar Beuria

You must specify the default value for the fourth parameter. Visual Studio is an interactive environment, so you get to enter the value on request by the VS. But in production environment, reports are consumed by other applications and the report will not be able to request for an input from the calling application and this applies to Report Manager also which is an external web application that consumes the reporting services.

Shyam

|||

just go to the code and see if there is any parameter hard coded in xml.

Remove that parameter and save it .

This will probably solve your problem.

Vamsi Krishna Korasiga

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

Peculiar T-SQL requirement.

Hi all,

I have a scenario in which I need to do an amount balancing ( meaning, OFFSET few records with positive amount whose sum equals the value in the negative amount in the same recordset).

Consider the below example.

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0

Above 6 records are fetched for a particular condition. My requirement now is to update the OFFSET column of the positive valued records whose sum equals the value in the negative amount (145) .

That is, I need the below result.

Col1 Col2 Amount OFFSet

A 01 100 1
A 01 20 1
A 01 30 0
A 01 25 1
A 01 -145 0
A 01 15 0

Only the records whose sum of amount matches the negative amount value should be OFFSet'd no matter in what sequence they are.

I guess I'm confusing a bit, let me know if you need more explanation.

Any help would be appreciated.

Thanks,

DBLearner

What you describe is a HUGE logic problem, not a SQL problem. That is very complicated to accomplish.

What do you do with multiple records match your criteria. For example:

Col1 Col2 Amount OFFSet

A 01 100 0
A 01 20 0
A 01 30 0
A 01 25 0
A 01 -145 0
A 01 15 0
A 01 25 0
A 01 100 0
A 01 25 0
A 01 25 0
A 01 25 0
A 01 15 0
A 01 10 0

What now?

-145 = 100+20+25
-145 = 25+25+25+25+20+25
-145 = 100+20+15+10
etc

sql

Peculiar Replication (Could be OT)

We have 2 types of Production Database. One is an OLTP database another contains all the Master Tables. We also have 2 copies of each and this repeats itself for each country where the plant is located. Is there someone who can explain to me how we might
setup this Replication so that the Master tables which are maintained in one location gets replicated over to the other places and also the updated from those locations are also propogated to all locations. We had MS come into the picture and they have cr
eated triggers such that the data is copied over but they seem to delete the record and insert it back again for an Update and sometimes inbetween the delete and insert the leased line goes down and the result is a total failure.
Regards,
Trevor Benedict R
Trevor,
this is typically implemented using merge replication. You'll need to consider partitioning of data for each subscriber, or the posibility of conflicts and how they should be resolved. There's a good set of explanations of general concepts in BOL.
The network failures you experience are usually solved in merge by simply restarting the merge agent.
HTH,
Paul Ibison

Peculiar problem with seemingly identical data

Hello,

I have this peculiar problem concerning MS SQL Server.

My company works with an mailing application (ASP) which uses SQL
Server as it's repository. What I want to do is send data directly
from my own application to this SQL Server in order to feed the
mailing application.

To test if this was possible I linked the tables from SQL Server in MS
Access and entered the data. This worked fine and the data was picked
up correctly by the mailing application.

The problem occurs when I send the data from my application (Java
application with JDBC connection). The data is in this case no longer
picked up by the application. The strange thing is that the data which
is entered through Access and the data from the Application look
identical in de database view. The problem also occurs when the data
is send with the tool winSQL and when I view the data in here it still
looks identical.

Even more strange is when I select the record which is not working in
Access and copy it into a new record (only changing the key) it
suddenly works!

Has anyone have an idea how this can be?

Thanks in advance,

Sander Janssen.Sander Janssen (sjanssen@.plex.nl) writes:
> My company works with an mailing application (ASP) which uses SQL
> Server as it's repository. What I want to do is send data directly
> from my own application to this SQL Server in order to feed the
> mailing application.
> To test if this was possible I linked the tables from SQL Server in MS
> Access and entered the data. This worked fine and the data was picked
> up correctly by the mailing application.
> The problem occurs when I send the data from my application (Java
> application with JDBC connection). The data is in this case no longer
> picked up by the application. The strange thing is that the data which
> is entered through Access and the data from the Application look
> identical in de database view. The problem also occurs when the data
> is send with the tool winSQL and when I view the data in here it still
> looks identical.
> Even more strange is when I select the record which is not working in
> Access and copy it into a new record (only changing the key) it
> suddenly works!

I don't have a clue. The thing is, I have no idea by which criterias
your application is picking up data. You need to find this out one
way or another. One way to do this is to use the Profiler to catch
which SQL it is using.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Which JDBC Driver and what version of SQL Server?

"Sander Janssen" <sjanssen@.plex.nl> wrote in message
news:e3b8d8ea.0307040443.5bc4a8d@.posting.google.co m...
> Hello,
> I have this peculiar problem concerning MS SQL Server.
> My company works with an mailing application (ASP) which uses SQL
> Server as it's repository. What I want to do is send data directly
> from my own application to this SQL Server in order to feed the
> mailing application.
> To test if this was possible I linked the tables from SQL Server in MS
> Access and entered the data. This worked fine and the data was picked
> up correctly by the mailing application.
> The problem occurs when I send the data from my application (Java
> application with JDBC connection). The data is in this case no longer
> picked up by the application. The strange thing is that the data which
> is entered through Access and the data from the Application look
> identical in de database view. The problem also occurs when the data
> is send with the tool winSQL and when I view the data in here it still
> looks identical.
> Even more strange is when I select the record which is not working in
> Access and copy it into a new record (only changing the key) it
> suddenly works!
> Has anyone have an idea how this can be?
> Thanks in advance,
> Sander Janssen.|||Hello,

I have found the source of the problem. It isn't as peculiar as I
thought at first though. The problem was that I wrote a date to an
identifier field and the characters ":" were the source of the problem
in the mailing application. When I replaced them with underscores it
all works fine!

Thanks for your help anyway!

Sander Janssen.

Peculiar problem with BCP data load

I have table in database with total column length about
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
Don
Try creating a clustered index on the table, preferably one that is in order
of the data to some degree.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>

Peculiar problem with BCP data load

I have table in database with total column length about
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
DonTry creating a clustered index on the table, preferably one that is in order
of the data to some degree.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>