Sql Update command: schedule

Hi,
I'm having a problem with Reporting Services report subscriptions.
I have a set of reports that need to be sent via email on a schedule. I need
to send them in Excel format, but the files sent to my email account are
unreadable in Excel. I am using Excel 2003 and can manually export reports
and open them in Excel without any problems, yet when I use a subscription to
email the Excel files they are not valid files. They have the .xls extension,
but Excel can't open them properly, it just opens them as a load of
unintelligible text.
The same thing happens when I set up a subscription to email a report in XML
format, I could export it manually and it worked fine, yet when I run the
subscription to email the XML report to me I get an invalid XML file.
Has anyone else had this problem or does anyone know how to fix it?
If it helps, the report server is stored on a network server, not my local PC.
The server is running Windows Server 2003 Pro, my PC is running Windows XP
Pro and has Office 2003. Office is not installed on the server, but I doubt
that makes any difference, since I can manually export to Excel without any
problems.
Thanks in advance
TabbyWhen you run them "locally" due you mean from VS.NET on your workstation?
If yes... I found that generating reports "locally" can have totally
different results than running them on the report server.
If this is the case...try loading the report via a browser from the report
server.
If this is not the case... try pdf/html or other output formats to narrow
down the problem. I found problems exporting to excel but not other formats
when report objects are overlapping.
Tabby Cool wrote:
>Hi,
>I'm having a problem with Reporting Services report subscriptions.
>I have a set of reports that need to be sent via email on a schedule. I need
>to send them in Excel format, but the files sent to my email account are
>unreadable in Excel. I am using Excel 2003 and can manually export reports
>and open them in Excel without any problems, yet when I use a subscription to
>email the Excel files they are not valid files. They have the .xls extension,
>but Excel can't open them properly, it just opens them as a load of
>unintelligible text.
>The same thing happens when I set up a subscription to email a report in XML
>format, I could export it manually and it worked fine, yet when I run the
>subscription to email the XML report to me I get an invalid XML file.
>Has anyone else had this problem or does anyone know how to fix it?
>If it helps, the report server is stored on a network server, not my local PC.
>The server is running Windows Server 2003 Pro, my PC is running Windows XP
>Pro and has Office 2003. Office is not installed on the server, but I doubt
>that makes any difference, since I can manually export to Excel without any
>problems.
>Thanks in advance
>Tabby
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||No, I am not running htem from Visual Studio, I am running them direct from
the Report Manager.
When you run reports in Visual Studio, can you actually export them? I
thought you could only preview them, there is no option to export a report
from the preview screen.
I have tried exporting them to both XML and Excel, I can do this perfectly
from the Report Manager, yet when I use a subscription to email the reports
to me in either of these formats the files are unusable.
William N wrote:
>When you run them "locally" due you mean from VS.NET on your workstation?
>If yes... I found that generating reports "locally" can have totally
>different results than running them on the report server.
>If this is the case...try loading the report via a browser from the report
>server.
>If this is not the case... try pdf/html or other output formats to narrow
>down the problem. I found problems exporting to excel but not other formats
>when report objects are overlapping.
>>Hi,
>[quoted text clipped - 23 lines]
>>Tabby|||I have tried exporting to PDF, again, this works fine when performed manually,
but the email attachments from the subscription are not valid PDFs and can't
be opened by Adobe Acrobat.
It seems there is something different with the export procedure when using a
subscription as opposed to exporting manually, I don't understand why it
would work perfectly one way and not the other.|||Seems like the report is not getting the the data?
Are you saving the credentials in your data connection?
Tabby Cool wrote:
>I have tried exporting to PDF, again, this works fine when performed manually,
>but the email attachments from the subscription are not valid PDFs and can't
>be opened by Adobe Acrobat.
>It seems there is something different with the export procedure when using a
>subscription as opposed to exporting manually, I don't understand why it
>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Yes, the credentials are stored in the data source.
William N wrote:
>Seems like the report is not getting the the data?
>Are you saving the credentials in your data connection?
>>I have tried exporting to PDF, again, this works fine when performed manually,
>>but the email attachments from the subscription are not valid PDFs and can't
>[quoted text clipped - 3 lines]
>>subscription as opposed to exporting manually, I don't understand why it
>>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Not sure what else to suggest.
I always try to simplify as much as possible first. Maybe try creating a
report with just a "Hello" text box and see if that works (no data sets or
anything else).
If it doesn't, then it's probably a server/install issue.
Good luck.
Tabby Cool wrote:
>Yes, the credentials are stored in the data source.
>>Seems like the report is not getting the the data?
>[quoted text clipped - 5 lines]
>>subscription as opposed to exporting manually, I don't understand why it
>>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1

I am trying to schedule an hourly incremental update of the FT catalog on an indexed query. I have tried creating the catalog and schedule on both a remote machine (my workstation) and directly on the server. I get different errors on the two machines. I have tried creating the scheduled updates during the catalog creation process as well as seperately. I am able to create the catalog and update it manually in both cases. I cannot get the schedules created. The text of the errors are as follows:

NOTE: We run on an alternate port

From my workstation during the catalog creation process:

===================================

Create full-text population schedule failed.

===================================

Apply to target server failed for Job 'Start Incremental View Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)

For help, click:
removed

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextWizardForm.PerformActions()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)

===================================

The specified @.server_name ('XXX.XXX.XXX.XXX,0000') does not exist. (.Net SqlClient Data Provider)

For help, click:
removed

Server Name: XXX.XXX.XXX.XXX,0000
Error Number: 14262
Severity: 16
State: 1
Procedure: sp_add_jobserver
Line Number: 88

Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Upon recieveing that error, the catalog is created but contains no schedule population events. Trying to add the population job via the FT properties dialog results in the following (which is almost identical to the previous):

===================================

Cannot execute changes.

===================================

Apply to target server failed for Job 'Start Incremental Table Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)

For help, click:
removed

Program Location:

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

===================================

The specified @.server_name ('XXX.XXX.XXX.XXX,8081') does not exist. (.Net SqlClient Data Provider)

For help, click:
removed

Server Name: XXX.XXX.XXX.XXX,8081
Error Number: 14262
Severity: 16
State: 1
Procedure: sp_add_jobserver
Line Number: 88

Program Location:

If I immediately try to creat it again using the same method, it successfully creates the scheduled item and job but the job is malformed and will not run:

===================================

Start failed for Job 'Start Incremental Table Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)

For help, click:
removed

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.StartJobAction.DoAction(ActionCollection actions, Int32 index)
at Microsoft.SqlServer.Management.SqlManagerUI.ActionCollection.DoWorkOnThread()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

===================================

Cannot start the job "Start Incremental Table Population on SyllabiDBI.qry_FTSearch" (ID 24C9505F-1388-46AD-AD4B-3024A8D3D154) because it does not have any job server or servers defined. Associate the job with a job server by calling sp_add_jobserver. (.Net SqlClient Data Provider)

For help, click:
removed

Server Name: XXX.XXX.XXX.XXX,0000
Error Number: 14256
Severity: 16
State: 1
Procedure: sp_start_job
Line Number: 51

Program Location:

Now, on the server itself, I recieve the follwoing when trying to create the schedule via the initial catalog setup:

===================================

Create full-text population schedule failed.

===================================

Object reference not set to an instance of an object. (SqlManagerUI)

Program Location:

And then when trying to add the scheduled jobs:

===================================

Object reference not set to an instance of an object. (SqlManagerUI)

Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPropertiesSchedule.OnRunNow(Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

Again, when I try to run the resulting job, they are malformed and result in the a similar error as to the one I posted above:

===================================

Start failed for Job 'Start Incremental View Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)

For help, click:
removed

Program Location:

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

===================================

SQLServerAgent Error: Request to run job Start Incremental View Population on SyllabiDBI.qry_FTSearch (from User XXX\Administrator) refused because the job has no job steps. (.Net SqlClient Data Provider)

For help, click:
removed

Server Name: XXX
Error Number: 22022
Severity: 16
State: 1

Program Location:

I believe it is a problem with the Management Studio IDE, but I can't be sure. So far, I have been unable to find another way to create the schedule or jobs. If I can create the jobs and schedule them via T-SQL I will happily do so.

Any insight is greatly appreciated.

I would like to know how you created this job, that is having this issue. It should work fine if you created under SQLAgent node.

Programatically you can use SMO objects (Look for JobServer in BOL)or agent SPs (look for sp_add_job and other related sps in BOL) to get this done

Thanks,

Gops Dwarak

sql

Monday, March 26, 2012

Problems Exporting To Excel When Using Report Subscription

Friday, March 23, 2012

Problems creating full-text population schedule via Management Studio

Sql Update command

Blog Archive

About Me