|
I've got a query going out to a Cache database via ODBC.
It almost always works (90% or so).
The other 10% of the time, it returns the wrong data for one of the columns (a LONGVARCHAR column)
By wrong, I mean the data is from another row.
At first, I though maybe there was too much text in the column and it was trashing a buffer or something, so I cahange my parameter from a VarChar to Text, but it made no difference. I just retried it again now without specifying the datatype at all, and it made no difference.
Further investigation showed that the correct data was only 1600 characters, so that isn't going to be the problem, anyway.
I've even cleared the parameters for each loop of the read and it still does it.
I just now tried to do a select specifically on one of the bad rows and it was still bad, so ti has to be something going on with ODBC or the remote server. Right?
Anyone ever see anything like this before?
|
|
|
|
|
Usually when I have problems like this, I first try and eliminate something like ODBC. Is there any way to connect natively to the database. Preferably by running something on the server that is hosting the database? If you can do something like that, it will help to eliminate whether the database itself is doing something unusual or unpredictable. As far as ODBC goes, can you try other drivers and duplicate the issue still?
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
The server is in Idaho or someplace up thataway, and I'm in Texas, so I won't being doing any hands-on. (Nope. Can't remote.)
There are no native drivers. There is a slight possibility that I could get a .NET driver... If they let me.
And no other ODBC driver will work. Freaking InterSystems Cache.
The DBA up there is goign to check with InterSystems and see if they have any ideas.
|
|
|
|
|
This sounds like a classic No-Win No-Win situation. You better get a good prescription for anti-depressants.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Could you post the query? Be aware that Cache ::pinch of salt over the shoulder:: isn't SQL-92 compliant and the worst thing (in my opinion) is that it doesn't support operator precedence and that could be a factor here.
When I have used it (against my will) I used an ADO.net connector when I could, but prod used ODBC. I don't recall any differences.
|
|
|
|
|
select ID, DateOfChecksum, Checksum, RoutineCode, RoutineName, Site from CARC.RoutinesOST
But, like I said, it mostly works.
|
|
|
|
|
GenJerDan wrote: it mostly works
I think that's their slogan.
Is CARC.RoutinesOST a view or something else other than a table?
Can you make a copy in another database (e.g. SQL Server) to test against?
What you describe certainly is a mystery.
|
|
|
|
|
It's a table.
I can't think of any way to get a copy of it.
The really fun part is that the WAN connection (or something) is so bad that it takes me 4+ hours to retrieve the data... The connection goes off into never-never land frequently.
|
|
|
|
|
GenJerDan wrote: The other 10% of the time, it returns the wrong data for one of the columns (a
LONGVARCHAR column)
Try two queries.
One that gets everything but the blob. The second that gets only the blob.
And given the connection problems presumably this is already a data migration process rather than a processing process. If so a second query for the blob could be used to verify.
|
|
|
|
|
Data migration?
No, they want me to pull this data EVERY DAY for use in a web app here.
Pulling the column in question all by its lonesome results in the same problem.
Pulling the other columns just gives me the other columns. They've always been correct.
|
|
|
|
|
GenJerDan wrote: No, they want me to pull this data EVERY DAY for use in a web app her
Which would be migration. A non-migration app would be one that pulled it for every user query.
GenJerDan wrote: Pulling the column in question all by its lonesome results in the same problem.
Given the size maybe you can convert it to a varchar in the SQL. Disassociation from the type might fix the problem.
|
|
|
|
|
jschell wrote: Given the size maybe you can convert it to a varchar in the SQL. Disassociation
from the type might fix the problem
Hmmm...I'll give that a try.
|
|
|
|
|
This may be a silly suggestion (I know nothing about Cache and don't use ODBC anymore) but can /n (return) in the data be causing an issue.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No idea. It defintiely uses \n rather than \r\n, but all of the records use the same eoln so they should all be broken.
It's just too weird. And it's the same records every time. Wish I could substitute the text in the column with something else to see if it still does it, but no way can we do that.
|
|
|
|
|
..can you encode it's contents in base64? That'd at least eliminate "weird characters" as a cause.
|
|
|
|
|
Is there a way to create a single .Net application that uses SMO and have it work correctly on a box with SQL 2008 R2 as well as on a box with SQL 2012?
The two solutions I've found so far are:
1) Create a version of the application for each version of SMO.
2) Create a version of the application with the latest SMO. Install the latest SMO on all client boxes.
I'm not really thrilled with either option.
Any thoughts or help is greatly appreciated
|
|
|
|
|
EricCiz wrote: I'm not really thrilled with either option.
I don't know any others besides these, do you?
The second option "sounds" like a simple one; just program against the latest interface and update all the components on the client-computers.
The first option "is" the simple one; create a version-agnostic wrapper, and two version-specific implementations (as small as possible).
|
|
|
|
|
I have a sp for selecting all the records(policies here) and it should display 3 records and remainig in 'all others' category and total. But this is displaying 'all others' if there is less than 3 records. Waht should do for this
CREATE Procedure [dbo].[cpGetFirmRemittedPolicySummaryByPolicyType]
@firmId int = null,
@totalPoliciesToShow int = 0,
@currentDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON
declare @debug bit = 0
declare @ALL_OTHERS varchar(10) = 'All Others'
-- If the @currentDate is NULL then use the current system date, otherwise use the date passed
-- This is used for testing purposes
if (@currentDate is NULL) set @currentDate = GETDATE()
if (@debug = 1) select @currentDate CurrentDate
declare @priorYear int = Year(@currentDate) - 1
declare @janFirstThisYear datetime = convert(datetime, '01/01/' + CAST(YEAR(@currentDate) as varchar(4)), 101)
declare @lowestPolicyRank int
declare @rankCutoff int
declare @rowCount int
declare @tblPolicyData table
(
PolicyTypeName varchar(250),
RemittedPolicyCount int,
PolicyCountRank int
)
declare @tblAggregatedPolicyData table
(
PolicyTypeName varchar(250),
PolicyCountRank int,
MTDRemittedPolicyCount int,
YTDRemittedPolicyCount int,
PriorYearRemittedPolicyCount int,
IsAllOtherColumn smallint DEFAULT (0)
)
declare @tblResults table
(
Id int NOT NULL IDENTITY (1,1),
PolicyTypeName varchar(250),
MTDRemittedPolicyCount int,
YTDRemittedPolicyCount int,
PriorYearRemittedPolicyCount int
)
if (@totalPoliciesToShow <> 0)
begin
-- Try the current month
INSERT INTO
@tblPolicyData
SELECT
[PolicyTypeName],
sum([RemittedPolicyCount]) RemittedPolicyCount,
ROW_NUMBER() OVER(ORDER BY Sum(RemittedPolicyCount) DESC) AS PolicyCountRank
FROM
[dbo].[FirmPolicyDetailMonthly]
where
FirmId = @firmId
and substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6) = substring(CONVERT(varchar(8), @currentDate, 112), 1, 6)
group by
substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6), PolicyTypeName
select @rowCount = ISNULL(COUNT(*), 0) from @tblPolicyData
-- If no policies for the current month, try the current year
if (@rowCount = 0)
BEGIN
INSERT INTO
@tblPolicyData
SELECT
[PolicyTypeName],
sum([RemittedPolicyCount]) RemittedPolicyCount,
ROW_NUMBER() OVER(ORDER BY Sum(RemittedPolicyCount) DESC) AS PolicyCountRank
FROM
[dbo].[FirmPolicyDetailMonthly]
where
FirmId = @firmId
and RemittedDate between @janFirstThisYear AND @currentDate
group by
YEAR(RemittedDate), PolicyTypeName
END
END
-- If the rowCount in @tblPolicyData is still 0 then either we are selecting all policies
-- types or we are not selecting all policies types and there have been are no remitted policies
-- for MTD or YTD so we are moving to previous year
select @rowCount = ISNULL(COUNT(*), 0) from @tblPolicyData
if (@rowCount = 0)
BEGIN
INSERT INTO
@tblPolicyData
SELECT
[PolicyTypeName],
sum([RemittedPolicyCount]) RemittedPolicyCount,
ROW_NUMBER() OVER(ORDER BY Sum(RemittedPolicyCount) DESC) AS PolicyCountRank
FROM
[dbo].[FirmPolicyDetailMonthly]
where
FirmId = @firmId
and YEAR(RemittedDate) >= @priorYear
group by
PolicyTypeName
END
if (@debug = 1) select 'PolicyData' PolicyData, * from @tblPolicyData
select @lowestPolicyRank = ISNULL(MAX(PolicyCountRank), 0) from @tblPolicyData
if (@totalPoliciesToShow = 0)
begin
set @rankCutoff = @lowestPolicyRank
end
else
begin
if (@totalPoliciesToShow < @lowestPolicyRank)
set @rankCutoff = @totalPoliciesToShow
else
set @rankCutoff = @lowestPolicyRank
end
if (@debug = 1) select @lowestPolicyRank LowerstPolicRank, @rankCutoff RankCutoff
INSERT INTO
@tblAggregatedPolicyData (PolicyTypeName, PolicyCountRank, MTDRemittedPolicyCount, YTDRemittedPolicyCount, PriorYearRemittedPolicyCount)
SELECT
PolicyTypeName, PolicyCountRank, 0, 0, 0
FROM
@tblPolicyData
WHERE
PolicyCountRank <= @rankCutoff
if (@debug = 1) select 'AllPoliciesBeforeTotalCalcs' Message,* from @tblAggregatedPolicyData
-- Update MTD counts for top Remitted Policy Types
UPDATE
@tblAggregatedPolicyData
SET
MTDRemittedPolicyCount = detail.RemittedPolicyCount
FROM
@tblAggregatedPolicyData r INNER JOIN (
SELECT
PolicyTypeName, SUM(RemittedPolicyCount) as RemittedPolicyCount
FROM
FirmPolicyDetailMonthly
WHERE
FirmId = @firmId
and substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6) = substring(CONVERT(varchar(8), @currentDate, 112), 1, 6)
GROUP BY
PolicyTypeName) detail ON r.PolicyTypeName = detail.PolicyTypeName
WHERE
r.PolicyCountRank <= @rankCutoff
if (@debug = 1) select 'MTD-TopX' Message,* from @tblAggregatedPolicyData
-- Insert MTD 'ALL OTHERS' category if needed. ALL OTHERS is only used when @totalPoliciesToShow is not
-- zero. If ALL OTHERS is required, it is inserted with a rank that is one more than the lowest found rank.
IF (@totalPoliciesToShow <> 0)
BEGIN
select @rowCount = ISNULL(COUNT(*), 0) from FirmPolicyDetailMonthly where
FirmId = @firmId
and substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6) = substring(CONVERT(varchar(8), @currentDate, 112), 1, 6)
and PolicyTypeName not in
(select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)
IF (@rowCount <> 0)
BEGIN
INSERT INTO
@tblAggregatedPolicyData (PolicyTypeName, PolicyCountRank, MTDRemittedPolicyCount, IsAllOtherColumn)
SELECT
@ALL_OTHERS as PolicyTypeName, @lowestPolicyRank + 1, Sum(RemittedPolicyCount) as RemittedPolicyCount, 1
FROM
FirmPolicyDetailMonthly
WHERE
FirmId = @firmId
and substring(CONVERT(varchar(8), RemittedDate, 112), 1, 6) = substring(CONVERT(varchar(8), @currentDate, 112), 1, 6)
and PolicyTypeName not in
(select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)
END
END
if (@debug = 1) select 'MTD-AllOthers' Message,* from @tblAggregatedPolicyData
-- Insert YTD for top remitted policies
UPDATE
@tblAggregatedPolicyData
SET
YTDRemittedPolicyCount = detail.RemittedPolicyCount
FROM
@tblAggregatedPolicyData r INNER JOIN (
SELECT
PolicyTypeName, SUM(RemittedPolicyCount) as RemittedPolicyCount
FROM
FirmPolicyDetailMonthly
WHERE
FirmId = @firmId
and RemittedDate between @janFirstThisYear AND @currentDate
GROUP BY
PolicyTypeName) detail ON r.PolicyTypeName = detail.PolicyTypeName
WHERE
r.PolicyCountRank <= @rankCutoff
if (@debug = 1) select 'YTD-TopX' Message,* from @tblAggregatedPolicyData
-- 'ALL OTHERS' Policies YTD
IF (@totalPoliciesToShow <> 0)
BEGIN
select @rowCount = ISNULL(COUNT(*), 0) from FirmPolicyDetailMonthly where
FirmId = @firmId
and RemittedDate between @janFirstThisYear AND @currentDate
and PolicyTypeName not in
(select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)
IF (@rowCount <> 0)
BEGIN
-- If the ALL OTHERS line item doesn't exist, add empty one
select @rowCount = ISNULL(Count(*), 0) from @tblAggregatedPolicyData where PolicyTypeName = @ALL_OTHERS
if (@rowCount = 0) INSERT INTO @tblAggregatedPolicyData (PolicyTypeName, IsAllOtherColumn, PolicyCountRank) SELECT @ALL_OTHERS, 1, @lowestPolicyRank + 1
UPDATE
@tblAggregatedPolicyData
SET
YTDRemittedPolicyCount = detail.RemittedPolicyCount
FROM
@tblAggregatedPolicyData r INNER JOIN (
SELECT
@ALL_OTHERS as PolicyTypeName, Sum(RemittedPolicyCount) as RemittedPolicyCount
FROM
FirmPolicyDetailMonthly
WHERE
FirmId = @firmId
and RemittedDate between @janFirstThisYear AND @currentDate
and PolicyTypeName
not in (select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)) detail
ON r.PolicyTypeName = detail.PolicyTypeName
END
END
if (@debug = 1) select 'YTD-AllOthers' Message,* from @tblAggregatedPolicyData
-- Prior Year for top remitted policies
UPDATE
@tblAggregatedPolicyData
SET
PriorYearRemittedPolicyCount = detail.RemittedPolicyCount
FROM
@tblAggregatedPolicyData r INNER JOIN (
SELECT
PolicyTypeName, SUM(RemittedPolicyCount) as RemittedPolicyCount
FROM
FirmPolicyDetailMonthly
WHERE
FirmId = @firmId
and Year(RemittedDate) = @priorYear
GROUP BY
PolicyTypeName) detail ON r.PolicyTypeName = detail.PolicyTypeName
WHERE
r.PolicyCountRank <= @rankCutoff
if (@debug = 1) select 'PY-TopX' Message,* from @tblAggregatedPolicyData
-- 'ALL OTHERS' Policies Prior Year
IF (@totalPoliciesToShow <> 0)
BEGIN
select @rowCount = ISNULL(COUNT(*), 0) from FirmPolicyDetailMonthly where
FirmId = @firmId
and Year(RemittedDate) = @priorYear
and PolicyTypeName not in
(select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)
IF (@rowCount <> 0)
BEGIN
-- If the ALL OTHERS line item doesn't exist, add empty one
select @rowCount = ISNULL(Count(*), 0) from @tblAggregatedPolicyData where PolicyTypeName = @ALL_OTHERS
if (@rowCount = 0) INSERT INTO @tblAggregatedPolicyData (PolicyTypeName, IsAllOtherColumn, PolicyCountRank) SELECT @ALL_OTHERS, 1, @lowestPolicyRank + 1
UPDATE
@tblAggregatedPolicyData
SET
PriorYearRemittedPolicyCount = detail.RemittedPolicyCount
FROM
@tblAggregatedPolicyData r INNER JOIN (
SELECT
@ALL_OTHERS as PolicyTypeName, Sum(RemittedPolicyCount) as RemittedPolicyCount
FROM
FirmPolicyDetailMonthly
WHERE
FirmId = @firmId
and Year(RemittedDate) = @priorYear
and PolicyTypeName
not in (select PolicyTypeName from @tblPolicyData where PolicyCountRank <= @rankCutoff)) detail
ON r.PolicyTypeName = detail.PolicyTypeName
END
END
if (@debug = 1) select 'PY-AllOthers' Message,* from @tblAggregatedPolicyData
INSERT INTO
@tblResults (PolicyTypeName, MTDRemittedPolicyCount, YTDRemittedPolicyCount, PriorYearRemittedPolicyCount)
select
UPPER(PolicyTypeName) as PolicyTypeName,
ISNULL(MTDRemittedPolicyCount,0) MTDRemittedPolicyCount,
ISNULL(YTDRemittedPolicyCount,0) YTDRemittedPolicyCount,
ISNULL(PriorYearRemittedPolicyCount,0) PriorYearRemittedPolicyCount
from
@tblAggregatedPolicyData
order by
IsAllOtherColumn,
MTDRemittedPolicyCount DESC, YTDRemittedPolicyCount DESC, PriorYearRemittedPolicyCount DESC
-- Insert the TOTAL row into @tblResults as the last row, therefore it wil have the highest Id value
INSERT INTO
@tblResults (PolicyTypeName, MTDRemittedPolicyCount, YTDRemittedPolicyCount, PriorYearRemittedPolicyCount)
SELECT
'TOTAL', SUM(MTDRemittedPolicyCount), SUM(YTDRemittedPolicyCount), SUM(PriorYearRemittedPolicyCount)
FROM
@tblAggregatedPolicyData
SELECT
PolicyTypeName, ISNULL(MTDRemittedPolicyCount, 0) MTDRemittedPolicyCount,
ISNULL(YTDRemittedPolicyCount, 0) YTDRemittedPolicyCount,
ISNULL(PriorYearRemittedPolicyCount, 0) PriorYearRemittedPolicyCount
FROM
@tblResults
ORDER BY
Id
SET NOCOUNT OFF
RETURN 0
END
|
|
|
|
|
It might be helpfull if you divide this script into several simpeler stored procedures.
Complex tasks are always easier when broken into smaller pieces.
|
|
|
|
|
Hi guys.,
I had a kind of requirement where i will select my department in ddl and click on save button.,then the code should be saved in the format of selected department-S.no/Fin.Year.
For Eg:
If i select my Dept name as IT and my current financial year is
2012-04-01 2013-03-31
then output should be in the format of
IT-00001 / 12-13
(Dept-S.no/Fin.Year)
if a save one more record then,
output should be
IT-00002 / 12-13
If my Fin.Year changed to
2013-04-01 2014-03-31
then again the o/p should be
IT-00001 / 13-14
|
|
|
|
|
..and what's your question?
|
|
|
|
|
As Eddy said, you really should ask a question you want answered rather than state a problem.
Try looking into string concatenation, you are going to have to CAST or CONVERT the datetime and int into varchar values and concatenate them!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi guys.,
I had an grid view where i had placed an link button to print an report.In this button click event i need to call the SSRS report and need to get the output as pdf file.
I had used this below code,the code is running fine,but i'm unable to see the prompt to open/save pdf file.Plz reply ASAP.
protected void btnAuthenticateAndPrint_Click(object sender, EventArgs args)
{
try
{
LinkButton lb = (LinkButton)sender;
GridViewRow row = (GridViewRow)lb.NamingContainer;
Label lbOrderID = row.FindControl("lbOrderID") as Label;
int OrderId = Convert.ToInt32(lbOrderID.Text);
da = new SqlDataAdapter("Get_PODetails", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@MPDI_ID", OrderId);
ds = new DataSet();
da.Fill(ds, "PO");
if (ds.Tables["PO"].Rows.Count > 0)
{
lblPOId.Text=ds.Tables["PO"].Rows[0]["MPDI_ID"].ToString();
lblVendid.Text = ds.Tables["PO"].Rows[0]["MVDI_ID"].ToString();
lblBranch.Text = ds.Tables["PO"].Rows[0]["MBRI_ID"].ToString();
lblDate.Text = Convert.ToDateTime(ds.Tables["PO"].Rows[0]["MPDI_Date"]).ToString("dd-MM-yyyy");
}
rs = new RSWebService.ReportingService2005();
rsExec = new REWebService.ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = "http://localhost/ReportServer/ReportService2005.asmx";
rsExec.Url = "http://localhost/ReportServer/ReportExecution2005.asmx";
byte[] Sendresults = null;
byte[] bytes = null;
string historyID = null;
string deviceInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
string format = "PDF";
string encoding = null;
string mimeType = null;
string extension = null;
REWebService.Warning[] warnings = null;
string[] streamIDs = null;
string _reportName = @"/FIMO GOF Assets Reports/PURCHASE ORDER";
REWebService.ExecutionInfo ei = rsExec.LoadReport(_reportName, historyID);
REWebService.ParameterValue[] parameters = new REWebService.ParameterValue[4];
parameters[0] = new REWebService.ParameterValue();
parameters[0].Name = "MVDI_ID";
parameters[0].Value = lblVendid.Text;
parameters[1] = new REWebService.ParameterValue();
parameters[1].Name = "MBRI_ID";
parameters[1].Value = lblBranch.Text;
parameters[2] = new REWebService.ParameterValue();
parameters[2].Name = "MPDI_Date";
parameters[2].Value = lblDate.Text;
parameters[3] = new REWebService.ParameterValue();
parameters[3].Name = "ReportParameter1";
parameters[3].Value = lblPOId.Text;
rsExec.SetExecutionParameters(parameters, "en-us");
Sendresults = rsExec.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
MemoryStream ms = new MemoryStream(Sendresults);
if (format == "PDF")
{
Response.ContentType = "application/pdf";
Response.AddHeader("Content-disposition", "inline;filename=output.pdf");
Response.AddHeader("Content-Length", Sendresults.Length.ToString());
}
Response.OutputStream.Write(Sendresults, 0, Sendresults.Length);
Response.OutputStream.Flush();
Response.OutputStream.Close();
}
catch(Exception Ex)
{
throw Ex;
}
}
Thanks & Regards,
Vinay
|
|
|
|
|
Don't crosspost, choose one forum and stick to it.
One of these days I'm going to think of a really clever signature.
|
|
|
|
|
Hi everyone. I have different databases that are related. During backup process, I will need to append other databases so that I will have a single file containing all the databases.
My problem now is to programmatically identify the individual databases in the single backup file. How do I accomplish this? Please help.
|
|
|
|
|