Doubt

CREATE PROCEDURE emp_ins @.no integer,@.name varchar(20),@.sa integer,@.msg
varchar(20)output AS
if @.no<0
BEGIN
set @.msg='Empno>0'
RAISEERROR(@.msg,10,1)
end
insert into emp values(@.no,@.name,@.sa)

my requirement is i want to implement normal validations when the user exceeds the condition i want to store the message into out parameter and i want to return that message into the application. is it possible or not
waiting for valuable reply
Sri

If you want to abort processing after the error, I would add a RETURN statement immediately after the RAISERROR (still in the BEGIN...END block.)

Code Snippet

CREATE PROCEDURE Emp_Ins
( @.no integer,
@.name varchar(20),
@.sa integer,
@.msg varchar(20) OUTPUT
AS
IF ( @.no < 0 )
BEGIN
SET @.msg = 'Empno > 0'
RAISEERROR( @.msg, 10, 1 )
RETURN
END
INSERT INTO Emp VALUES ( @.no, @.name, @.sa )

|||Thank u Arnie.
Before u r reply i tried with the same code i got it additionally u given a point that with return statement transaction will be aborted.Arnie u may feel that it may be silly in case of multiple ifs how to write the program like prevoius program because i m ne to sqlserver envi i m much familiar with envi thats wy i m requesting u
Thanks and Regards
Sridhar
|||

Baba urf Sivaji wrote:

i want to return that message into the application. is it possible or not

You should use the proper Severity number & it should be 16 rather 10. You can return the control after the Raiseerror. Now the message will be captured on your UI & the control alos return back to the UI.

RAISERROR (@.msg,16,1);

Return;

|||You need to have the application declare an OUTPUT parameter to capture the value of @.Msg.

Double join... kind of...

I'm having problem getting the output i want.

I have a table looking like this (tbl_team)

team_id | team_name
1 | TEAM1
2 | TEAM2
3 | TEAM3
...

and another one like this (tbl_match)

match_id | home_team | away_team | score
1 | 1 | 3 | 3-1
2 | 2 | 3 | 0-0
3 | 2 | 1 | 1-5
...

I am trying to form a question looking like this:

home team | away team | score
TEAM1 | TEAM3 | 3-1
TEAM2 | TEAM3 | 0-0
TEAM2 | TEAM1 | 1-5
...

can anyone help me with this? It's easy to make this output in my programming enviroment (VS.NET) but I would really like for a sql-statement to have this output.

Thanks!select h.team_name as hometeam
, a.team_name as awayteam
, score
from tbl_match
inner
join tbl_team as h
on home_team = h.team_id
inner
join tbl_team as a
on away_team = a.team_id

Double Byte Characters scrambled in PDF Output

Does anybody know how I can Clean up some European Characters such as "?"
in a text field:
"45000 m² new office project designed by Arquitectonica. Revised proposal
and clarifications issued to client on 26 Aug: 1239K? for MOEX, 985K for
Synthese as option. Ten competitors, including Coteba. Contract award
expected by 15 Sept 04."
Is there a certain data type to use or is there a conversion fucntion to
use?
HelpWhat you can do in this case is :
write custom code for all the clean up.
You can write code tab of the Report Properties in the Report tab under main menu.
Write your own VB.NET code.
>--Original Message--
>Does anybody know how I can Clean up some European Characters such as "?"
>in a text field:
>"45000 m=B2 new office project designed by Arquitectonica. Revised proposal
>and clarifications issued to client on 26 Aug: 1239K? for MOEX, 985K for
>Synthese as option. Ten competitors, including Coteba. Contract award
>expected by 15 Sept 04."
>Is there a certain data type to use or is there a conversion fucntion to
>use?
>Help
>
>.
>

Don't want output file if there are no rows returned

I'm using a simple data flow to extract rows from a table (using a SQL query) and put them in a flat file. If the query returns no rows, I don't want a file to be created. Right now it creates a file with the headers (since I do want the headers if there is data).

Any one know how to do this?

Kevin

Kevin,

Unfortunately I don't think there is any way around this. The workaround is to check to see if any rows were inserted (put a ROWCOUNT component immediately prior to the flat file destination adapter). If NumberOfRows=0, delete the resultant file.

-Jamie

|||

I did get it to work as Jamie suggests. For others who are trying to do the same, I'll post my solution below.

* At the Control Flow level, create a Sequence Container

* Place a Data Flow task in the Container

* Create a variable (called RowCounter of type Int32)

* Within the Data Flow Task, I have four objects in this order: 1) a Data Flow Source (such as OLE DB), 2) a Transformation (such as Copy Column), 3) the Row Count Transformation, and 4) a Data Flow Destination (such as Flat File).

* The Row Count Transformation is set up with the variable RowCounter

* Now back at the Control Flow level, place a File System Task in the Sequence Container

* Connect the Precedence Constraint from the Data Flow to the File System Task

* Open the properties of the Precedence Constraint and set the Evaluation Operator to "Expression and Constraint", the Value to "Success", and the Expression to "RowCounter == 0"

* Finally set the properties of the File System Task as follows: Operation = "Delete File" and Source Connection to the name of the File Connection that you used in the Data Flow Task.

Domain Administrator Password Change Fails File output

Windows 2003 Ent.
RS 2000
SQLSERVER 2000 Ent.
The networking group changed the domain administrator password. I have
changed the RS serverice in the control panel. I have changed all the
subscriptions to use the new password. I have used the RSCONFIG with the -e
and -u to supply the domain administrator password change but they still do
not export to the file share. Any responses would be helpfull!!Are the reports rendering correctly? I'd like to view the log files first.
You can zip and email them to me at v-rxwang@.microsoft.com.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Scott,
I've received the log files. From the ReportServerService__<datetime> logs
I can see:
ReportingServicesService!crypto!1114!8/3/2005-20:03:48:: i INFO:
Initializing crypto as user: INTERINTRA\Administrator
ReportingServicesService!crypto!1114!8/3/2005-20:03:48:: i INFO: Exporting
public key
ReportingServicesService!library!1114!8/3/2005-20:03:48:: Exception caught
while starting service. Error: System.Runtime.InteropServices.COMException
(0x8009000B): Key not valid for use in specified state.
This indicates a encryption key and Reporting Services Windows Service
account problem. I suggest that you follow these steps:
1. Backed up ReportServer and ReportServerTempdb
2. Run rskeymgmt -e -f <name> -p <password>
3. Run rskeymgmt -d
4. Restarted the ReportServerservice and access the reportserver via
http://servername/reportserver to activate it.
5. Test and see if the subscriptions still have the problem. If the problem
still exists, zip and email the log files to me again please.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you for all your help!
Scott
"William Wang[MSFT]" wrote:
> Hi Scott,
> I've received the log files. From the ReportServerService__<datetime> logs
> I can see:
> ReportingServicesService!crypto!1114!8/3/2005-20:03:48:: i INFO:
> Initializing crypto as user: INTERINTRA\Administrator
> ReportingServicesService!crypto!1114!8/3/2005-20:03:48:: i INFO: Exporting
> public key
> ReportingServicesService!library!1114!8/3/2005-20:03:48:: Exception caught
> while starting service. Error: System.Runtime.InteropServices.COMException
> (0x8009000B): Key not valid for use in specified state.
> This indicates a encryption key and Reporting Services Windows Service
> account problem. I suggest that you follow these steps:
> 1. Backed up ReportServer and ReportServerTempdb
> 2. Run rskeymgmt -e -f <name> -p <password>
> 3. Run rskeymgmt -d
> 4. Restarted the ReportServerservice and access the reportserver via
> http://servername/reportserver to activate it.
> 5. Test and see if the subscriptions still have the problem. If the problem
> still exists, zip and email the log files to me again please.
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||It's my pleasure!
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.