Showing posts with label null. Show all posts
Showing posts with label null. Show all posts

Friday, March 23, 2012

Scheduledefinition.Item returns always null

Hi All,

I have a built an application to manage the report-subscription on my ReportServer.
I'm having some troubles with retrieving the subscription properties.

My problem:
I can retrieve all my propertie except the schedule definition, when I deserialize it, the Sceduledefenition.Item always returns null.

I use the next code to deserialize it:
//////////////////////////////////////////////////////////////////////////////////////////////////////
private XmlAttributeOverrides GetSchedule()
{
XmlAttributeOverrides xmlAttrOverride = new XmlAttributeOverrides();
XmlAttributes XmlAttr = new XmlAttributes();
XmlAttr.Xmlns = false;
xmlAttrOverride.Add(typeof(ScheduleDefinition), XmlAttr);
xmlAttrOverride.Add(typeof(MinuteRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(DailyRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(WeeklyRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(MonthlyRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(MonthlyDOWRecurrence), XmlAttr);
xmlAttrOverride.Add(typeof(DaysOfWeekSelector), XmlAttr);
xmlAttrOverride.Add(typeof(MonthsOfYearSelector), XmlAttr);
return xmlAttrOverride;
}

private ScheduleDefinition DeserializeScheduleDefenition(string MatchData)
{
XmlAttributeOverrides xmlAttrOverride = GetSchedule();
XmlSerializer ser = new XmlSerializer(typeof(ScheduleDefinition),xmlAttrOverride);
Stream stream = new MemoryStream(System.Text.Encoding.Default.GetBytes(MatchData));
stream.Position = 0;
return (ScheduleDefinition)ser.Deserialize(stream);
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I'have tried almost everything, but could not find the solution.

Please, this is very important for me, so every tip or shared thought could be very helpfull.

Thnx in advance

I have done some of investigation on this problem again and came up with the next weird thing:

When I create the subscription with my own application everything works well, so the subscription is added to the list in REPORTMANAGER.
when I use my own application to retrieve the properties of the subscription everything goes well also.

But now it comes:
When I update the subscription with my own application, the subscription is updated well also. (I use the SetSubscriptionProperties() method of reportingservices2005)

If I go to the reportmanager to edit my subscription I can see that my function updated the subscription well.

BUT when I retrieve the susbscription properties with my own application I get the Problem I wrote about in the original post.

AND when I edit the susbscription with the reportmanager and I retrieve the properties again, it goes all OK again. AND VICE VERCA If I update it with my OWN application, I get the problem again.

This is very weird for me to understand, am I doing something wrong with the SetSubscriptionProperties() method
OR am I doing something wrong with the GetSubscriptionProperties() method.

Hopefully somebody can help now with this issue.

|||

This posting is about same problem and has some suggestions: http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e2df8bbce14df692/b9276e2c55fca249

Tuesday, March 20, 2012

Scheduled Job Not Working

I have created a scheduled job in the enterprise manager, which checks around 20 different date columns and replaces any non-dates with NULL using the following syntax: -

update policy set [exp] =null where isdate (substring([exp],4,2)+'-'+left([exp],2)+'-'+'200'+right ([exp],1 ))=0
go
update policy set [eff] =null where isdate (substring([eff],4,2)+'-'+left([eff],2)+'-'+'200'+right ([eff],1 ))=0
go
update policy set [written] =null where isdate (substring([written],4,2)+'-'+left([written],2)+'-'+'200'+right ([written],1 ))=0
go

etc......

When I start the job, if fails within 2 seconds. If I copy the syntax in to the query analyzer it works fine.

All help appreciated.

A couple of questions

1 what is the error

2 who is the owner of the Job (and does he have permissions to the DB)

3 is the correct DB seleted in the step?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

to view the errro message , right click on the job, select view job history, click show step details (top right)

Click on the stepid and the error will be displayed in the bottom part

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi there,

The error message is: The job failed. The Job was invoked by User KEELAN_WESTALL\James. The last step to run was step 1 (UpdateDates).

The user is James (me)

I have security to the server/database/tables etc.

The correct DB is selected.

|||

The error message is: -

Executed as user: sa. The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256) The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256) The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256). The step failed.

|||

Convert to varchar first, take a look at this

select substring(1952005,1,2) -- will fail

select substring(convert(varchar(8),1952005),1,2) -- correct

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

As a test I ran the job just changing one column using the following syntax: -

update policy set [exp] =null where isdate (convert (varchar(8), (substring([eff],4,2)+'/'+left([eff],2)+'/'+'200'+right ([eff],1))))=0
go

It worked perfectly.

Thanks very much Denis.