RAISERROR Behavior Question

I have a RAISERROR statement being displayed to the screen prior to a print statement, however the print statement was executed before the RAISERROR statement. Why would this happen? The print statement is not part of any conditional logic. It is executed sequentially followed by an IF statement that generates the RAISERROR.

Step 1. Loop through all Databases and dynamically run DBCC ShowContig
Step 2. End the loop and Print "SCANNING COMPLETED"
Step 3. Declare Cursor to read DBCC ShowContig results
Step 4. Check current time to see if it is ok to continue processing
If it is NOT ok, RAISERROR
Else begin DBReindex process
Step 5. Close and Deallocate Cursor

If I replace RAISERROR with a print statement, everything prints in order. What gives?

DaveIf you post the code it would be easier to find solution. However, If you are not saving the value of @.@.error in a variable it resets to Zero when you go back to check it again .

In other words You should

Declare @.err int
And while checking @.@.error use Select @.err = @.@.error and then check @.err value which will stay stored . I am not sure if this is what you needed to know .. Like I said , code posting might help|||The logic itself works fine so posting the code most likely won't help. The RAISERROR is generated after a conditional statement checking the length of a local variable. There is no need to check @.@.ERROR at that time. The only issue is why a RAISERROR gets sent to the screen before a print statement when the print statement executed first. I was told a few hours ago that Print statements are first sent to the buffer and its possible a RAISERROR does not hit the buffer. That may explain why it appears first.

Thanks, Dave|||what's the severity level and state values? i've experimented for a couple of minutes with different severity levels including 20 and up, at which point the print statement does not get displayed at all.|||Raiserror ("My error message", 16, 1) with log

Dave|||i put print 'test' before your raiserror and got this:

test
Server: Msg 50000, Level 16, State 1, Line 2
My error message|||I'll be testing all of the error routines again this week. Once I recreate the issue I'll see if the code is small enough to post.

Thanks, Dave

raiseerror does not raise exception

Hi friends

i've a stored proc (sql 2005) that'll raiseerror statement when something violated.

but my C# application that calls this stored proc does not any throw exception when this happens !!

i remember visual basic used to through an exception for this type of things.

is it different in C# and how do we handle this scenario ?

Thanks for ur ideas.

can you show us the SP code?

moving thread to the SQL Forums

|||am doing something like below

BEGIN TRY
BEGIN TRAN

/* one of below statements may result in error*/
insert into mytable1 values (...blah..)
insert into mytable1 values (...blah..)

COMMIT
END TRY
BEGIN CATCH
DECLARE

@.ErrorMessage VARCHAR(4000)
SELECT @.ErrorMessage = 'Message: '+ ERROR_MESSAGE();

raiserror (@.ErrorMessage)
END CATCH;

i used executescalar ,executereader (in C#) but none of them throw any exception but i could access data CATCH returns though|||

Hi prk,

You'll need to specify the severity and state during the RAISERROR call:

raiserror (@.ErrorMessage,16,1)

Cheers,

Rob

|||

Thanks Rob

will give that a try

Quotations in this sql statement

I'm having trouble getting the quotations right in this sql statement because of the single quotes in the displayname. It keeps breaking my application. How would you use quotes to get this to work?

Select description from lawschools_tbl where displayname='Certificat, L'Institut d'études Politiques/'

When you need to use a quote ' in a SQL String, you need to replace the single quote with two single quotes.

Select description from lawschools_tblwhere displayname='Certificat, L''Institut d''études Politiques/'

|||

Actually I was making it too hard. thanks for your help though with the sql syntax.

Quiz help required

1) What is the commonly fixed database role of a db_datawriter?

a)Add, change or delete data from all the tables
b)Assign statement and object permissions
c)Backup and restore databases
d)Read data from any table

2) ). How would you add a country field to your database to ensure that your Argentinean subsidiary does business only with other Argentinean companies?

a)CHECK constraint
b)PRIMARY KEY constraint
c)FOREIGN KEY constraint
d)DEFAULT constraint

3) You want to set up replication between two databases, so the financial data and the sales data will be the same. You want the data to replicate at 1:00 a.m. every morning. You would like to completely remove all data from the financial database each night and overwrite data from the sales database. Which database replication model would you choose?

a)Transactional replication
b)DTC replication
c)Subscriber replication
d)Snap shot replication
e)Merge replication

4) You start SQL-Server with the -f option. Unfortunately now you can't establish a connection to your SQL-Server. What should you do?

a)Edit regsitry
b)Restore registry from backup
c)Rebuild master database
d)Reinstall SQL -Server
e)Run regrebuld.exe

5). You define full-text indexing on the ProductName column in the Products table. You then execute a full-text query on the column. You specify a word that you know is present in the column, but the result set is empty. What is the most likely cause?

a)The Microsoft Service is not running
b)The SQL ServerAgent Service is not running
c)The catalog is not populated
d)You did not create a unique SQL Server index on the ProductName column

6) Exchange and SQL 7.0 are running on the same server. You notice the performance in exchange is degraded. The Min server memory, Maximum server memory and set working area are set as they were automatically in the installation. What do you do to free memory for exchange?

a)Increase Min server memory
b)Set working area to 0
c)Set working area to 1
d)Increase memory allocated to the procedure cache option
e)Reduce Min server memory

7) What functions are performed by the SQL Server Agents?
(Choose all that apply)

a)Notification
b)Job execution
c)User security managment
d)Replication management
e)Alert management.

8) The SQL server that Michael manages crashed. The disk drives were not damaged but there was data that had not been written to some databases. Which transactions will be rolled forward in each database when his SQL server starts the automatic recovery process?

a)All committed transactions that are in the transaction log between the last checkpoint and the failure
b)All committed transactions that are in the transaction log
c)All committed transactions that are in the transaction log between the last two checkpoints
d)All uncommitted transactions that are in the transaction logPlease do not post this kind of questions here .|||Check in your courseware, i believe the answer is hiding somewhere. Good luck & Take care.

quick transaction inside a stored procedure question

a transaction block inside a sproc

if after one of the tran statements, I bust out of the sproc with a return statement

do I have to rollback explicitly first or does the tran rollback automatically?

appreciate the tip

After returning to another procedure the @.@.TRANCOUNT should be still increased by 1. You can test that by printing out the @.@.trancount variables in both procedure. In common, I would always handle the transcation context on my own by settings explizit transaction heaviour.

HTH, jens Suessmeyer.|||thanks jens|||

Could you please rate the thread as solved or helpful or whatever, that its no longer present as unanswerd ?

Thanks, Jens.

Quick SQL Select Statement ?

I am using SQL Server Express and ASP.

I have a table that contains news articles, headlines, start and end
dates. I am trying to create a recordset that shows all of the
articles that are greater than or equal to the start date and less
than or equal to the end date. For some reason I cannot get it to
function properly using a statement similar to StartDate >= Now() AND
EndDate <= Now(). SQL doesnt like the Now() piece of the statement.
Anyone have an idea how I can get this to work? I have also tried
CURDATE() but to no avail.

Please help.t8ntboy wrote:

Quote:

Originally Posted by

I am using SQL Server Express and ASP.
>
I have a table that contains news articles, headlines, start and end
dates. I am trying to create a recordset that shows all of the
articles that are greater than or equal to the start date and less
than or equal to the end date. For some reason I cannot get it to
function properly using a statement similar to StartDate >= Now() AND
EndDate <= Now(). SQL doesnt like the Now() piece of the statement.
Anyone have an idea how I can get this to work? I have also tried
CURDATE() but to no avail.

StartDate <= GetDate() and EndDate >= GetDate()|||On Mar 20, 4:33 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

t8ntboy wrote:

Quote:

Originally Posted by

I am using SQL Server Express and ASP.

>

Quote:

Originally Posted by

I have a table that contains news articles, headlines, start and end
dates. I am trying to create a recordset that shows all of the
articles that are greater than or equal to the start date and less
than or equal to the end date. For some reason I cannot get it to
function properly using a statement similar to StartDate >= Now() AND
EndDate <= Now(). SQL doesnt like the Now() piece of the statement.
Anyone have an idea how I can get this to work? I have also tried
CURDATE() but to no avail.

>
StartDate <= GetDate() and EndDate >= GetDate()

Ed:

Many thanks!!!|||t8ntboy,

You might want to use CURRENT_TIMESTAMP which is ANSI compliant and
equivalent to GETDATE(). There is no functional difference, its just easier
for someone coming from another DB platform to understand.

-- Bill

"t8ntboy" <t8ntboy@.gmail.comwrote in message
news:1174421409.303027.198580@.l75g2000hse.googlegr oups.com...

Quote:

Originally Posted by

>I am using SQL Server Express and ASP.
>
I have a table that contains news articles, headlines, start and end
dates. I am trying to create a recordset that shows all of the
articles that are greater than or equal to the start date and less
than or equal to the end date. For some reason I cannot get it to
function properly using a statement similar to StartDate >= Now() AND
EndDate <= Now(). SQL doesnt like the Now() piece of the statement.
Anyone have an idea how I can get this to work? I have also tried
CURDATE() but to no avail.
>
>
Please help.
>

Quick SQL Question

Hey,

I know thsi is a silly question but gonna ask it anyway :)

When you are using the 'insert' statement to insert records in to a database are all of the fields in the db table required for a successfull adding of a record.

Just that i have 16 fields in my db table and want to insert only 11 fields in to the new record and it is giving me an error, and i am sure my SQL is correct

So are all fields required ?

Thankyou

ChrisHey,

i have realised that the password field i am passing to the database causes the error, if i take this out, all values are added to the db. i have tried adding this field using a stored procedure but still causes the 'There is an error in your INSERT Statement" error.

Must i convert my password to some other data type before i add it?

I have out put my sql statement to a label and it sees the password fine but just doesnt add it, this is puzzeling me as it just a textbox and all the other text boxes add fine, i have checked the names of my control and database fields and all are correct.

Please help

chris|||Probelm Solved

Cheers anyway :)

quick SELECT statement question

Hello all!
I have a date/time column on my table. How can I write a select statement
to pick only items from todays date? I looked, but can't find an answer.
SELECT * User FROM TABLE WHERE datecolumn = "todays date"
Thanks!
RudySELECT
*
FROM
Table
WHERE
CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
GETDATE(), 112)
Due to the time that is stored in a datatime datatype, you need to strip the
time out as above.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy|||Hi Mike!
WOW!! I wouild have never figured that out. Thanks!!
Rudy
"Mike Epprecht (SQL MVP)" wrote:

> SELECT
> *
> FROM
> Table
> WHERE
> CONVERT(CHAR(8), datecolumn, 112) = CONVERT(CHAR(8),
> GETDATE(), 112)
> Due to the time that is stored in a datatime datatype, you need to strip t
he
> time out as above.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rudy" <Rudy@.discussions.microsoft.com> wrote in message
> news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
>
>|||Hi
Or
SELECT
*
FROM
Table
WHERE
datecolumn >= (CONVERT(CHAR(8), GETDATE(), 112) + '
00:00:00.000'
AND datecolumn <= (CONVERT(CHAR(8), GETDATE(), 112) + '
23:59:59.997'
The 1st one can not use an index if that is the only predicate in the where
clause as each row needs to be evaluated.
The 2nd one could use an index, but make sure that you use ' 23:59:59.997'
and not ' 23:59:59.999' as .999 can not be represented in datetime, so it
rounds itself to 00:00:00.000, the next day
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:2202B61C-5A3A-4FEB-B0E4-6FC0480FB1EA@.microsoft.com...
> Hi Mike!
> WOW!! I wouild have never figured that out. Thanks!!
> Rudy
> "Mike Epprecht (SQL MVP)" wrote:
>|||In addition to Mike's comments, you might want to red more about the subject
at:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5E70E4E8-EB91-4DBF-8489-5F9E3A5A204C@.microsoft.com...
> Hello all!
> I have a date/time column on my table. How can I write a select statement
> to pick only items from todays date? I looked, but can't find an answer.
> SELECT * User FROM TABLE WHERE datecolumn = "todays date"
> Thanks!
> Rudy

Quick select statement question

hi all

another easy one

how would i write it to where i select a value = 0 where all are equal to 0 rather than one line. when selecting based off a key from another table how would i select the values that all bring back 0 for that key rather than just a line or 2? i hope that makes sense, lol.What have you come up with so far?|||select case when exists(select * from MyTable where MyValue <> 0) then 1 else 0 end|||SELECT dbo.tPA00175.chrJobNumber, dbo.tPA00175.intJobKey, dbo.tPA00125.numQuantityToInv
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQuantityToInv = 0)

but this is only selecting the single values...id like to select the ones that have all 0's for the particular jobkey|||How about:

select dbo.tPA00175.chrJobNumber,
dbo.tPA00175.intJobKey,
numQuantityToInv = 0
from dbo.tPA00175 inner join (select intJobKey
from dbo.tPA00125
group by intJobKey
having max(case when numQuantityToInv = 0 then 0 else 1 end) = 0
) as t2 on dbo.tPA00175.intJobKey = t2.intJobKey|||thanks, that works nicely

Quick Question... How to use Default Values (after allowing NULL)

Hello,

I have a BIT column which accepts NULL values.

What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).

Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?

Thanks,Look up CREATE TRIGGER in Books Online, and pay special attention to the INSERTED and DELETED virtual table concepts. Then within your trigger:

update YourTable
set YourValue = 0
from YourTable
inner join INSERTED on YourTable.PKEY = INSERTED.PKEY
where YourValue is null

But really, you should be doing your inserts through a stored procedure which uses ISNULL([NewValue], 0)|||Thx for the quick response.

I'll give it a shot.

quick casting problem...

Can anyone please telkl me what is wrong with this portion of a SQL statement. I have been racking my brain over this and can't seem to get it right...

(CASE playerstats.fgm WHEN 0 THEN 0 ELSE (cast(100.00 * ((cast(SUM(playerstats.fgm)) as Decimal(8,2))/(cast(SUM(playerstats.fga)) as Decimal(8,2)))) as decimal(8,1))) AS fgp

I had it working fine, but when playerstats.fgm was a 0 then I got a divide by 0 error. This was the code when it was working ok as long as no one entered a 0 for fgm

(cast(100.00 * (cast(SUM(playerstats.fgm) as Decimal(8,2))/cast(SUM(playerstats.fga) as Decimal(8,2))) as decimal(8,1))) AS fgp

All I am trying to do is find a percentage... when playerstats.fgm = 0 then the percentage will be 0.

Any help will be much appreciated!!! :confused:one thing i notice is that you're mixing a scalar value in the outer CASE and an aggregate SUM value inside the CAST

and then you're setting 0 (an integer) as the THEN result, but CASTing the ELSE to 1 decimal place

plus, you're testing the wrong column for 0 divisor :) ;)

finally, if "fgm" and "fga" are field goals made/attempted, then you don't have to cast them in the calculation

try this -- cast( case when sum(playerstats.fga) = 0
then 0
else 100.00
* sum(playerstats.fgm)
/ sum(playerstats.fga)
end
as decimal(8,1) ) as fgp

Queue insert statement what is the best SQL server 2005 feature to use?

Hello everybody, I'm not completly aware of the SQL server 2005 possibilities so I'd need an hints from somebody with a wide knowledge to understand the direction to take!

This is what I have to do.

I insert into a table XML message. the messages are pushed automatically by an application I have no ""control" on and I get several messages "at the same time".

Everytime the message is inserted into the database I need to trasform the XML data into the correspondent relational value and I know already that in some cases it could take a while (1 second can be considered a while..)
My worry is that in the moment I process one message I loose the other one inserted after ,,,

There is some tool that helps me to handte the process as I would..

I was looking into SQL service broker?

It can be the right choice?

Thank you for any help!!

Marina B.

I've moved this thread to the service broker forum as I think its one of 2 options you want to use.

I think what you want to do is make sure the data is in SQL Server first and then transform it. You could use broker to do this;

Add the message to a broker q and then add your shredding logic to an activation proc or have the activation proc call your existing shredding code.

The other option would be to use a staging table with a type of XML on one of the columns, when you get the data insert it into a row in that table and then run a batch job (from agent or a broker timer) that reads the XML, shreds it then deletes the row from thestaging table.