Anyone here familiar with stored procedures?

Hi there! I would like to return a default status value (-101) if the -100 and 200 conditions are not met. Is there any way I can go about it?

ALTER PROCEDURE spServiceFormGet (@.TicketNo int, @.Name char(50))
AS
IF NOT EXISTS (SELECT SolutionID FROM ServiceForm where SolutionID = @.TicketNo)
RETURN -100
IF EXISTS (SELECT SolutionID, SolvedBy FROM ServiceForm
WHERE SolutionID = @.TicketNo AND SolvedBy LIKE @.Name
OR SolutionID = @.TicketNo AND SolvedBy IS NULL)
SELECT * FROM serviceform where SolutionID=@.TicketNo
RETURN 200
ELSE <-- something like that
RETURN -101 <-- something like that

Thanks,
-Gabian-Gabian,
Do a google search on MSSQL Output Variables.

You should return a @.outputStatus variable here.

ScAndal|||I am not sure I understand what a return code is buying you in this case - Is there more to the proc than you are posting?

If there is no solutionid matching the ticket (first test) than you know there will be no solutionid matching a ticket AND a name...

Why not just execute the select with the (ticketid, name) filter and interogate the result set for records. If the count = 0 - you know there are no solutionid's matching this ticketno and/or name ??|||Thanks for answering guys, I've got it!

-Gabian-sql

Anyone have experience with SYSFILES?

According to Microsoft, sysfiles.status:

0x80 = File has been written to since last backup.

Unfortunately, this query is not returning what I expect. That is, if I have inserted/updated/deleted any records, I belive that some underlying file must be written to.

So, I tried:

Code Snippet

select * from sysfiles where status & 0x80 <> 0

I updated several fields in my database, doubled and tripled the size etc. I can't get the above query to return any results.

Does anyone have any experience with this particular flag? I was hoping to check the sysfiles and know if a backup was needed or not.

Thanks mucho.

Interesting. I just tried this with 7.0, 2000, and 2005, and that bit is never set. And 6.5 uses sysdevices rather than sysfiles. I wonder if somewhere in the development cycle they decided not to implement that, and it was never taken out of the documentation drafts. Either that or it's a very long standing, little-known bug. ;-)

There might be some ways to take advantage of the differential changed map to determine if a database has changed since the last backup, but the only way I know to read it isn't well suited for inclusion in a batch job.

Code Snippet

DBCC TRACEON(3604)
DBCC PAGE(databasename, 1, 6, 3)

Granted, a database larger than about 4 GB is probably going to have more than one dcm page, and I'm not entirely sure how to determine where subsequent pages are located.

Any way to get processing status when executing a large batch process job via AMO?

When making an ExecuteCaptureLog() AMO call, is there any way the client can poll for processing status from the SSAS insance?

I think the only way you could do this is to capture the Trace events

This thread has some samples showing you how to create and use Trace events from code.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=482459&SiteID=1