Sql Update command: hourly

Showing posts with label hourly. Show all posts

Friday, March 23, 2012

Problems creating full-text population schedule via Management Studio

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

Wednesday, March 7, 2012

Problem with writting a Report (SQL slow running)

I am trying to write a report that calculates the average number of sales
over 7, 14, 31 and 365 days for each hourly period of the day. the problem
is it takes over 4 minutes to run.

e.g.
Average Xactions per Hour
7 Days 14 Days 31 Days 365 Days
00:00 - 01:00 1,141.6 579.2 261.6 28.8
01:00 - 02:00 1,298.0 649.6 293.4 30.0

The report was use to be purely ASP running SQL Statements.
I then changed it to ASP Running a SP 24 times - this reduced running time
by about 1 minute.
I then changed it so that the stored proc looped internally 24 times and
returns the data.

I have ran the Index Tuning Wizard on the SQL and Implemented the indexes
suggested - this actually increase execution time by 20 seconds.

Below is the stored procedure I am currently using that loops internally 24
times and returns the data.

Can anyone suggest a better way / any improvements I could make ?

Many Thanks

Steve

-----------------------
--------

CREATE procedure ams_RPT_Gen_Stats
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strTest varchar(1),
@.strCurrency varchar(3),
@.strVFEID varchar(16)
as
declare @.strStep varchar(32)

set @.strStep = 'Start of Stored Proc'

/* start insert sp code here */

create table ##Averages (
TheHour varchar(2),
Day7Avge float ,
Day14Avge float ,
Day31Avge float ,
Day365Avge float
)

declare @.numHour varchar(2)
declare @.strSQL varchar(2000)
declare @.Wholesalers varchar(64)

declare MyHours cursor FORWARD_ONLY READ_ONLY for
select convert(char(2), timestamp,14) as TheHour
from xactions
group by convert(char(2), timestamp,14)
order by convert(char(2), timestamp,14)

if @.strTest = 'Y'
select @.Wholesalers = VALUE FROM BUSINESSRULES WHERE NAME =
'TEST_Wholesalers'

open MyHours

fetch next from MyHours into @.numHour

while @.@.fetch_status = 0

begin

set @.strSQL = 'insert into ##Averages (TheHour, Day7Avge) ( select ''' +
@.numHour + ''', ' +
'count(*) / 7.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 8) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '

if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '

if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.strCurrency + ''' '

if @.strVFEID <> '*'
set @.strSQL = @.strSQL + ' and xactions.VFEID = ''' + @.strVFEID + ''''
set @.strSQL = @.strSQL + ')'

exec ( @.strSQL )

set @.strSQL = 'update ##Averages set Day14Avge = ( select ' +
'count(*) / 14.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 15) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '

if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '

if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.strCurrency + ''' '

if @.strVFEID <> '*'
set @.strSQL = @.strSQL + ' and xactions.VFEID = ''' + @.strVFEID + ''' '

set @.strSQL = @.strSQL + ') where TheHour = ''' + @.numHour + ''' '

exec ( @.strSQL )

set @.strSQL = 'update ##Averages set Day31Avge = ( select ' +
'count(*) / 31.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 32) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '

if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '

if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.strCurrency + ''' '

if @.strVFEID <> '*'
set @.strSQL = @.strSQL + ' and xactions.VFEID = ''' + @.strVFEID + ''' '

set @.strSQL = @.strSQL + ' ) where TheHour = ''' + @.numHour + ''' '

exec ( @.strSQL )

set @.strSQL = 'update ##Averages set Day365Avge = ( select ' +
'count(*) / 365.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 366) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '

if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '

if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.strCurrency + ''' '

if @.strVFEID <> '*'
set @.strSQL = @.strSQL + ' and xactions.VFEID = ''' + @.strVFEID + ''' '

set @.strSQL = @.strSQL + ' ) where TheHour = ''' + @.numHour + ''' '

exec ( @.strSQL )

fetch next from MyHours into @.numHour

end -- while fetch

close MyHours
deallocate MyHours

select * from ##Averages order by TheHour

drop table ##Averages

/* end insert sp code here */

if (@.@.error <> 0)
begin
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' +
CONVERT(VARCHAR,@.@.Error)
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end

return 0

GOMany Thanks

I'll have a look at making those changes.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E5F2C02272EYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> > I am trying to write a report that calculates the average number of
> > sales over 7, 14, 31 and 365 days for each hourly period of the day.
> > the problem is it takes over 4 minutes to run.
>
> Troubleshooting performance problems over newsgroups is often difficult
> because, without access to the database it's not possible to test
> various scenarios. And without complete knowledge about the tables
> involved it is even more difficult. Just seeing the procedure code,
> may sometimes be sufficient, but not always.
> Anyway, I have two suggestions for your procedure, although none of
> them are likely to improve performance radically.
> The first is that you use a global temptable. Change this to a local
> temp table. This avoids problems if two users run this procedure
> simultaneously.
> The other is that you rewrite the procedure to not use dynamic SQL.
> As far as I can see, the only reason that you use dynamic SQL, is
> that you intended to have a comma-separated list in @.Wholesalers.
> I would suggest that you handle the list like this:
> CREATE TABLE #wholesalers (id int NOT NULL)
> INSERT #wholesalers (id)
> SELECT number FROM iter_intlist_to_table(@.wholesalers)
> You find the code for iter_intlist_to_table at
> http://www.algonet.se/~sommar/array...ist-of-integers.
> Armed with this temp table, you can, as far as I can see, rip out the
> dynamic SQL and replace it with static. This may give some performance
> benefit, but only some single second.
> However, once you have rewritten the code into static SQL, it will be
> more pleasant to take a look at it.
> It also helps if you post CREATE TABLE and CREATE INDEX statements
> for the involved table. Some hints about data sizes is also good.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, March 23, 2012

Problems creating full-text population schedule via Management Studio

Wednesday, March 7, 2012

Problem with writting a Report (SQL slow running)

Sql Update command

Blog Archive

About Me