Database Issues

Topics: Developer Forum
Nov 15, 2006 at 3:36 AM
Hi,
I looked in to the following OnJobAdded stored procedure

SELECT @next_printer = ISNULL(MAX(dbo.Printers.Id),1) + 1
FROM dbo.Printers

INSERT INTO dbo.Printers (
Id,
Name
)
VALUES(
@next_printer,
@PrinterName
)



Also the Priters.Id is defined as
CREATE TABLE dbo.Printers
(
Idnumeric (18, 0) NOT NULL IDENTITY(1, 1),

The problem is that ID won't be allowed to insert if we define Identity.

Solution:

Either we need to remove IDENTITY(1, 1) or Adjust the stored procedure.

I would delete Identity from printers.

LEt me know

Thanks
Ram

Nov 16, 2006 at 3:25 AM
One mOre issue is that it the OnJobWritten is firing four times, I used trace to see the jobs and found that it is firing four times and failing at :

Violation of PRIMARY KEY constraint 'PK_Print Job Events'. Cannot insert duplicate key in object 'Print Job Events'.
The statement has been terminated.
OnJobWritten: Violation of PRIMARY KEY constraint 'PK_Print Job Events'. Cannot insert duplicate key in object 'Print Job Events'.
OnJobWritten: The statement has been terminated.
@P

Thanks
Ram
Nov 16, 2006 at 3:45 AM
Sorry,
I don't have a way to update my previous comments. I am adding my suggestions. I checked OnJobWritted stored procedure and I modified the stored procedure in order to avoid INSERT Failures in case of multiple call. My question is why onJobWritten is firing more times. If that is the behaviour then we may have to modify the stored procedure to handle this. I have updated this stored procedure and if all you accept then you can take these changes

Thanks
RAm
CREATE PROCEDURE dbo.OnJobWritten
/* -- OnJobWritten --------------------------------------------------------------
* Triggered when a job is written and a PRINTERCHANGEJOB_WRITTEN event is raised
* Parameters:
* PrinterName As String
* JobId As Integer
* Username As String
* Document As String
* Machinename As String
* Pages As Integer
* Copies As Integer
* Submitted As DateTime
* Status As String
* PagesPrinted As Integer
* Paused As Boolean
* Deleted As Boolean
* Deleting As Boolean
* Printed As Boolean
* Printing As Boolean
* InError As Boolean
* Offline As Boolean
* PaperOut As Boolean
* UserInterventionRequired As Boolean
*/
(
@PrinterName varchar(220),
@JobId numeric(18,0),
@Username varchar(255),
@Document varchar(255),
@Machinename varchar(255),
@Pages numeric(18,0),
@Copies numeric(18,0),
@Submitted smalldatetime,
@Status varchar(255),
@PagesPrinted numeric(18,0),
@Paused bit,
@Deleted bit,
@Deleting bit,
@Printed bit,
@Printing bit,
@InError bit,
@Offline bit,
@PaperOut bit,
@UserInterventionRequired bit,
@QueuedTime int = 0,
@JobSize int = 0
)
AS
DECLARE @printerrebootcount int,
@PrinterId int

BEGIN
SET NOCOUNT ON

/* Deal with incorrect input params */
If @copies <= 0
select @copies = 1

IF @Pages <= 0
select @pages = 1

IF @Printed = 1 AND @PagesPrinted <= 0
select @PagesPrinted = @Pages

SELECT @PrinterId = dbo.Printers.Id,
@printerrebootcount = ISNULL(Reboot Count, 1)
FROM dbo.Printers
WHERE dbo.Printers.Name = @PrinterName

IF ISNULL(@JobId,0) > 0
BEGIN

update dbo.Print Jobs
SET User name =@Username,
Document Name = @Document,
Machine name = @MachineName,
Pages = @Pages,
Copies = @Copies,
Printed = @Printed,
Deleted = @Deleted,
Pages Printed = @PagesPrinted
where dbo.Print Jobs.Printer Id = @PrinterId
and dbo.Print Jobs.Job Id = @JobId
and dbo.Print Jobs.Printer Reboot Count = @printerrebootcount


IF NOT EXISTS(SELECT 'A'
FROM dbo.Print Job Events
WHERE Printer Id = @PrinterId
AND Job Id = @JobId
AND Printer Reboot Count = @printerrebootcount
AND Event=4
)
BEGIN
/* Record this print job event */
INSERT INTO dbo.Print Job Events
(
Printer Id,
Printer Reboot Count,
Job Id,
Submitted,
Event,
Paused,
Deleted,
Deleting,
Printed,
Printing,
Offline,
Paper Out,
User Intervention Required,
Position,
Pages Printed,
Queued Time,
Job Size
)
SELECT
@PrinterId,
@printerrebootcount,
@JobId,
@Submitted,
4, --JOB_WRITTEN event
@Paused, --Paused
@Deleted, --Deleted
@Deleting, --Deleting
@Printed, --Printed
@Printing, --Printing
@Offline, --Offline
@PaperOut, --Paper Out
@UserInterventionRequired, --User intervention required
0, --Position
@PagesPrinted, -- Pages Printed
@QueuedTime,
@JobSize
END
ELSE
BEGIN
UPDATE dbo.Print Job Events
SET
Submitted = @Submitted,
Paused =@Paused,
Deleted=@Deleted,
Deleting = @Deleting,
Printed =@Printed ,
Printing = @Printing,
Offline = @Offline,
Paper Out =@PaperOut ,
User Intervention Required =@UserInterventionRequired,
Pages Printed = @PagesPrinted,
Queued Time = @QueuedTime,
Job Size= @JobSize
WHERE Printer Id = @PrinterId
AND Job Id = @JobId
AND Printer Reboot Count = @printerrebootcount
AND Event=4
END

END


END



GO
Coordinator
Nov 16, 2006 at 8:05 AM
OnJobWritten gets fired every time a chunk of data is written to the spool file by an application or when a chunk of data is written from the spool file to the printer device...therefore a typical print job will trigger a number of OnJobWritten events.
The data (BytesWritten etc.) and Status (spooled, printed etc.) change for each event as the state of the job changes.

Nov 16, 2006 at 2:27 PM
Thank you for the response, then OnJobWritten stored procedure may require changes as each time it is being exuecuted if record exist then an update needs to be issued.

Can we incorporate the cahnge in the above stored procedure.

Thanks
Ram
Feb 9, 2007 at 5:59 PM
Hi,

Im trying to deploy the application in a machine running Windows XP with VS.NET 2005.

Im unable to finish the process because my machine doesnt recognize the extension ".dbproj" as a valid project extension.

Does anybody know a way to run the database scripts without build the dbproj project ? Wich is the sequence of execution of the sql files ?
Coordinator
Feb 11, 2007 at 12:12 PM
The extension .dbproj is for the "Visual Studio Team System for Database Professionals" plug in for VS2005.

See http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=725&SiteID=1