How can I retrieve data?

Here is my sql procedure:

ALTER PROCEDURE dbo.SoftWareShow
/*
(
@.parameter1 int = 5,
@.parameter2 datatype OUTPUT
)
*/
@.SoftID uniqueidentifier
AS
SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @.SoftID)
RETURN

where I retrieve data using sqldatasource, an error appear. how can do ?

(1) your sql in your stored proc is wrong. You are using the parameter@.softId in your sql which is not in the parameter list in your procdefinition. either you would need to change the parameter name from@.parameter1 to @.softid or the otherway.
(2) you have declared @.parameter2 as OUTPUT type but you dont seem tobe returning anything through it. So you can get rid of it.

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

How can I read type of all fields in joined query?

How can I read type of all fields in joined query?

Is there a stored procedure method to use for this purpose?

Thanks

What language needs to know this? c#/VB? T-Sql?

If c#/vb, and you are loading things into a DataTable, the meta-data is available in the DataTable object. I suspect it's availabe in DataReaders, etc.

|||

Hi David,

I need T-SQL stored procedure that gave me type of all fields in a joined query.

Thanks for your help

|||

create procedure get_stuff

as

begin

select col_1, col_2, col_3 from some_table
union
select col_a, col_b, col_c from some_other_table

end

That's the basic syntax. Check the manual for how to add parameters if needed.

How can I programmatically tell if agents are running?

Ideally I am looking for a procedure that I can call in T-SQL to verify that the Log Reader, Snapshot, and Sql Agents are all up and running on a given server. I found the undocumented sp_MSrepl_distributionagentstatussummary proc and got it to work for the distribution agent. I also found sp_MSrepl_agentstatusummary, but I've yet to get this to work. Overall, I'd really like to have a better way to do this.

This absolutely must be something callable from the sqlcmd interface. It can not be through a UI nor anthing more complicated than standard T-SQL.

Any suggestions?Query the status column in the history tables in the distribution database. there should be MSsnapshot_history, MSlogreader_history, MSdistribution_history.|||

Thanks for your response Greg.

I've spent some time looking at these tables and can't quite figure how to determine if one of the agents are down or not. I've induced some failures, took some of the agents offline, and generally tried to muck up the environment but couldn't find a reliable way to indicate if any of the darned things were actually running. I suppose I can look for the max timestamp of a 'started' process that doesn't have a 'error' or 'completed' record whose timestamp is more recent, but that doesn't feel to be a reliable method.

|||

Books Online explains each column for each table, as well as the various values for column runstatus.

For example, MSdistribution_history (this is cut/paste from books online):

runstatus

int

The Running status:

1 = Start.

2 = Succeed.

3 = In progress.

4 = Idle.

5 = Retry.

6 = Fail.

|||

Yup, this is a great example of what I was saying about the table not being a reliable indicator of the actual status of the agent. When I manually stopped the Sql Agent, I noticed rows in MSdistribution_history that looked like the following:

agent_id runstatus start_time time duration comments

5 2 <time> <time> 88854 The process was successfully stopped.

It seems that the runstatus 'Success' really means stopped in this case. It was immediately followed by a 'Starting agent' row when I turned it back on again. This led me to wonder if there are any other conditions I'd have to look for in order to see if things are enabled.

Maybe that's the confusion here... I don't care if a particular job is running, just that the agent is started / enabled / turned on / in a state where it can be used, etc. I know I can start looking for the most recent status log that indicates the agent is operable, but I've been in this business too long not to be lazy and just look for a darned enabled / disabled flag.

|||

Replication agents can be invoked numerous ways - from command line, ActiveX, or SQL jobs. The history tables will give you the status of the agent at its current state. For example, if your agent is set to run in continuous mode, and status = 3, then all is good. If status = 6, then your agent has failed. If your agent is scheduled to run at regular intervals, and you query it one hour later, and status = 2, that means the agent job ran successful. IF it's 6, then it failed. If it's 3, then the agent is still running.

Now, how the agent is invoked is totally up to you. HOw you determine if it's in a state where it can be used, well, I'm not sure how to define that for you. YOu need to be more specific with what you're trying to accomplish. Otherwise the replication history tables will give you the status of the current/last agent run.

how can I post back a statement from a store procedure to the .aspx page

Hi all,
Anyone can show me how can I catch the 'Print' statement that I have defined in my store procedure using SQL server 2000 DB on the .aspx page? ( I am using ASP.NET 1.0)
My store procedure as follow:
CREATE PROC NewAcctType
(@.acctType VARCHAR(20))
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
PRINT 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
PRINT 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
Thanks for all your replies

The best thing to do would be to either create another parameter and set its type to output or return the statement as a select.
@.message varchar(100) output
set @.message = 'The account type is already exists'
or
select 'The account type is already exist' as message

Nick

|||Hi Nick,
As you stated:
@.message varchar(100) output
set @.message = 'The account type is already exists'
Do I put a return statement like "Return @.message"?
How about if there is no errror in the procedure, do I still need to return any value to the .aspx page?
Thanks.|||Hi Nick,
when I executed my store procedure in SQL 2000 server, I got an error said"Cannot use the OUTPUT option in a DECLARE statement"
But without the Declare keyword, I got an incorrect syntax error, so how can I solve this problem? Is that necessary to put the "output" keyword at the end of the declare varaible statement?
Thanks|||syntax is:
create procedure whateverName
@.message varchar(100) output
as
set @.message = ''
if @.@.ERROR
set @.message = 'your text here.'
If you have no error, the top set statement will allow a blank to be passed back.
Nick|||

Nick,
Here is my syntax:
CREATE PROC DeleteCust
(@.SSN VARCHAR(12), @.message VARCHAR(40) output)
AS
BEGIN

--checks if the SSN is already exist
IF NOT EXISTS (SELECT * FROM Customer WHERE SSN = @.SSN)
BEGIN
SET @.message = 'The SSN is not exist!'
RETURN
END
BEGIN TRANSACTION
DELETE FROM Customer WHERE SSN = @.SSN

--if there is an error on the delete, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Delete failure on Customer table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END

I executed this proc as:
declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
Given that SSN is invalid, I suppose got the message 'The SSN is not exist!", however, I didn't get that message from the execution instead the system message showed "The command(s) completed successfully.", so anywhere I was wrong with the above SP or the execute statement?
Appreciated your reply

|||

If your just looking for the value after a run in QA, add:

declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
select @.message
Nick

|||

Thanks nick. I got the message when I run the Store Procedure in SQL server. However, how can I get the error message when I called the Store Procedure on my .aspx page?
I have these codes on my page: ('DeleteCust' is my store procedure name, 'SSN.Text' is the value from the input box)
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));

var myCommand : SqlDataAdapter = new SqlDataAdapter("DeleteCust", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.SSN", SqlDbType.VarChar, 12)).Value = SSN.Text;
Then, what should I put it here to catch the @.message value? The @.message is VARCHAR, and I have a Return keyword within my procedure, and the return value type is INT?
ping

|||

Add (Code is in VB):

dim parm as new sqlParameter("@.message", sqlDBtype.varchar, 100)

parm.direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parm)
After you do your call to stored proc:
strMessage(Or whatever variable you are adding to) = myCommand.SelectCommand.Parameters(1).Value
Nick

|||Hi Nick,
Could u explain more detail for these statements coz I am new to doing asp, I want to know it more about the meaning of those codes.
parm.direction = ParameterDirection.Output
strMessage(what variable should I add it here? could u give me an example? are u talking about @.message?)
when do I use strMessage() ?
Many thanks.

|||

Here is a quick and dirty article on output parms.

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624

Nick

How can I place a value from my dataset into the page header?

My report has a parameter of "End_Date" which is used to create my dataset from a stored procedure. In the dataset, a column called "Begin_Date" is returned with various other information used in the report creation.

Currently, the parameter "End_Date" is used in the page header as part of my report title. My user's whould also like to see the "Begin_Date" value in the page header.

Both End and Begin dates are unique values occuring once per report run. How do I get the "Begin_Date" contained in my dataset as a value to be displayed in the page header title?

many thanks

The current method is to add a hidden text box to the body with that value. Then reference that text box value in the page header.

In future releases we are looking at adding field references to the other page sections.

|||

Thank you Brad for the response. Please excuse my lack of understanding. In the Page header, any time I reference a field in the body I get a message basically saying "Fields can not be used in any page headers or footers".

I have tried placing a hidden text box in the body with the value and referencing it in the page header, but with no success. I continue to receive the "Fields can not be used in any page headers or footers" error message.

Have I misunderstood your response? thanks

|||

you need to give reference like these for the header textbox

=ReportItems!txtReportHeader.Value

where txtReportHeader is the text box in the body, which contains the value of the dataset field

|||

Thanks rakam, it worked like a charm....

I didn't realize you could reference ReportItems from headers and footers. Appreciate the information.

How can I Passing Table type variable as Output Param

Hi,
How can I pass table type OUTPUT param to a stored procedure ?
regards,> How can I pass table type OUTPUT param to a stored procedure ?
> regards,
Table data type is not allowed for a SP parameter. If you need to use the
rowset returned in T-SQL code, you have two possibilties:
- change the procedure to a table-valued UDF
- save the output in a temp table.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

how can i pass dataset to stored procedure

hi
i have customized dataset. i want to insert into database. how can i insert
on a single round trip. how can i pass dataset to stored procedure'examnotes (sdfsdf@.discussions.microsoft.com) writes:
> i have customized dataset. i want to insert into database. how can i
> insert on a single round trip. how can i pass dataset to stored
> procedure'
Make an XML string of it, and then use OPENXML in SQL Server to unpack it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes but i have to first save the xml file. mine is web application so how ca
n
i serialize file.
is there any other alternative?
"Erland Sommarskog" wrote:

> examnotes (sdfsdf@.discussions.microsoft.com) writes:
> Make an XML string of it, and then use OPENXML in SQL Server to unpack it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Hi,
What I think Erland was saying to create XML object ( e.g MSXML.DomDocument)
in your application and not file. This XML can be created from dataset, and
while calling SP pass the XML string as parameter ( Varchar lenght is the
constraint here).
Inside SP we can access the Node values from XML string using OpenXML.
Regards,
Vishal Khajuria
"sdfsdf" wrote:
> yes but i have to first save the xml file. mine is web application so how
can
> i serialize file.
> is there any other alternative?
>
> "Erland Sommarskog" wrote:
>|||Vishal Khajuria (Vishal Khajuria@.discussions.microsoft.com) writes:
> What I think Erland was saying to create XML object ( e.g
> MSXML.DomDocument) in your application and not file.
Yes. (Except that I don't really know how you build the XML string.
I only know that it's doable.)

> This XML can be created from dataset, and while calling SP pass the XML
> string as parameter ( Varchar lenght is the constraint here).
Pass the XML string as ntext, and length should not be a problem. (Unless
you exceed the 2GB limit for large objects.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How can I Pass an array into a Stored Procedure from Visual Basic?

Hi
How can I Pass an array into a MS SQL Server Stored Procedure from Visual
Basic?
Thanks
IanConvert it into a string
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>|||Define what you mean by an array and what you want to do with it in the
stored procedure for better answers.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>|||There are no array type in t-SQL. You will have to use other workarounds
like changing your VB array to a delimited string & pass as a single
parameter to the procedure, or build an XML string etc. As far as dealing
with such parameters & parsing them within a stored procedure, you can get
some ideas from: http://www.sommarskog.se/arrays-in-sql.html
--
Anith|||CREATE Procedure sp_test_array
@.IDList Varchar(8000)
AS
-- Notes: @.IDList must be a comma delimited list of id's
SET NOCOUNT ON
DECLARE @.Pos1 Int, @.Pos2 Int, @.id Int
-- Input value - bracket with commas
SET @.IDList = ',' + @.IDList + ','
SET @.Pos1 = 1
WHILE @.Pos1 < Len(@.IDList)
BEGIN
SET @.Pos2 = Charindex(',' , @.IDList , @.Pos1 + 1)
SET @.id = Convert(Int, Substring(@.IDList, @.Pos1 + 1, @.Pos2 - @.Pos1 - 1))
PRINT @.id
SET @.Pos1 = @.Pos2
END

How can I Pass an array into a Stored Procedure from Visual Basic?

Hi
How can I Pass an array into a MS SQL Server Stored Procedure from Visual
Basic?
Thanks
Ian
Convert it into a string
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>
|||Define what you mean by an array and what you want to do with it in the
stored procedure for better answers.
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored
"Ian" <ian@.NoWhere.com> wrote in message
news:u8f3g1U2EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I Pass an array into a MS SQL Server Stored Procedure from Visual
> Basic?
> Thanks
> Ian
>
|||There are no array type in t-SQL. You will have to use other workarounds
like changing your VB array to a delimited string & pass as a single
parameter to the procedure, or build an XML string etc. As far as dealing
with such parameters & parsing them within a stored procedure, you can get
some ideas from: http://www.sommarskog.se/arrays-in-sql.html
Anith
|||CREATE Procedure sp_test_array
@.IDList Varchar(8000)
AS
-- Notes: @.IDList must be a comma delimited list of id's
SET NOCOUNT ON
DECLARE @.Pos1 Int, @.Pos2 Int, @.id Int
-- Input value - bracket with commas
SET @.IDList = ',' + @.IDList + ','
SET @.Pos1 = 1
WHILE @.Pos1 < Len(@.IDList)
BEGIN
SET @.Pos2 = Charindex(',' , @.IDList , @.Pos1 + 1)
SET @.id = Convert(Int, Substring(@.IDList, @.Pos1 + 1, @.Pos2 - @.Pos1 - 1))
PRINT @.id
SET @.Pos1 = @.Pos2
END

How can I pass a large XML document from a column of data type ntext to a stored procedure

I'm trying to "prepare" a large xml document that we've
stored (as a string) in our database as an ntext
document. I can't seem to find a way to pass the ntext
datatype to sp_xml_preparedocument. Ntext are not allowed
as local variables, and I'm not sure how chopping up the
ntext, with readtext can help me.

sp_xml_preparedocument @.idoc output, (ntext)

In SQL 2005, you can cast the ntext to nvarchar(max) or the XML datatype and go from there using either OPENXML or XQuery to manipulate the XML.

For SQL 2000, if you know the offset of the data in the buffer, you can use the READTEXT functions to access them. Otherwise you could write code outside of the database that selected the values and converted them using native or managed data access API's. But in general in SQL 2000 you cannot have a variable that is bigger than 4000 unicode characters. SQL 2005 removes this restriction.

How can I open an OS text file inside one Stored Procedure

HI,
I have one Operating System text file in some directory in UNIX environment . How can I open this file inside one Stored Procedure.
Thanks in advance.
DillipHello,

to open flat files in the OS use the package SYS.UTL_FILE. You can use the functions/procedures fopen, get_line and fclose to access the datas in the ff.

You get direct access to the file you must enter the path of the file in your initial parameter. Otherwise Oracle can not read or write the file.

The parameter is UTL_FILE_DIR.

Here is a short example:

declare
fptr utl_file.file_type;
buff varchar2(2048);
line_no number(10):=0;
loc_no integer;
begin
fptr:=utl_file.fopen('C:\Oracle\admin\PENT\udump', 'ORA00324.TRC','R');
utl_file.get_line(fptr,buff);
utl_file.fclose(fptr);
exception
when no_data_found then
utl_file.fclose(fptr);
dbms_output.put_line('Number of lines parsed ='||line_no);
when utl_file.invalid_path then
dbms_output.put_line('invalid path');
raise_application_error(-20100,'file error');
when utl_file.invalid_mode then
dbms_output.put_line('invalid_mode');
raise_application_error(-20100,'file error');
when utl_file.invalid_filehandle then
dbms_output.put_line('invalid_filehandle');
raise_application_error(-20100,'file error');
when utl_file.invalid_operation then
dbms_output.put_line('invalid_operation');
raise_application_error(-20100,'file error');
when utl_file.read_error then
dbms_output.put_line('read_error');
raise_application_error(-20100,'file error');
when utl_file.write_error then
dbms_output.put_line('write_error');
raise_application_error(-20100,'file error');
when utl_file.internal_error then
dbms_output.put_line('internal_error');
raise_application_error(-20100,'file error');
when others then
dbms_output.put_line('un-handled');
raise_application_error(-20100,'file error');
end;

Hope that helps ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

how can i move a local directory with all files within a procedure

Hello i'd like to know how i can move all my files from C:\SQL to C:\DB
using a Table with a reference to the batch file.
it looks like that:
1.) i have batch.bat file with " call move c:\sql\* to c:\db "
2.) a table MyTable contains a column with value 'c:\db\batch.bat'
3.) i'd like to know if there are any procedure possible, that can execute
this batch.bat from MyTable.
maybe using ASP...?
can anybody tell if thats possible'Felix
Try xp_cmdshell system stored procedure in the BOL
"Felix" <andreas21@.gmx.net> wrote in message
news:uj0LKps1EHA.2292@.TK2MSFTNGP15.phx.gbl...
> Hello i'd like to know how i can move all my files from C:\SQL to C:\DB
> using a Table with a reference to the batch file.
> it looks like that:
> 1.) i have batch.bat file with " call move c:\sql\* to c:\db "
> 2.) a table MyTable contains a column with value 'c:\db\batch.bat'
> 3.) i'd like to know if there are any procedure possible, that can execute
> this batch.bat from MyTable.
> maybe using ASP...?
> can anybody tell if thats possible'
>|||thanks its working now! great!!!
master.dbo.xp_cmdshell procedure...
same question, is that possible to use such a systm procedure on mysql
server? any idea?
ive tried but there are no such cmdshell procedure like that. maybe there is
another way?
"Uri Dimant" schrieb im Newsbeitrag
news:eNzgelu1EHA.2612@.TK2MSFTNGP14.phx.gbl...
> Felix
> Try xp_cmdshell system stored procedure in the BOL
>
>
> "Felix" wrote in message
> news:uj0LKps1EHA.2292@.TK2MSFTNGP15.phx.gbl...
>

How can I make a SQL agent job to ignore warnings?

Hi! guys,

I have a SQL agent job fails because it gets 10 warnings when it runs
a stored procedure. These warnings are trivial and can be ignored. Can
I make it ignore these warnings and proceed? I think there is some
setting I can do to change the default behavour of an agent job
regarding warnings but I just don't know how to do it.

Any idea?

Thanks,

GaryGary (rooty_hill2002@.yahoo.com.au) writes:
> I have a SQL agent job fails because it gets 10 warnings when it runs
> a stored procedure. These warnings are trivial and can be ignored. Can
> I make it ignore these warnings and proceed? I think there is some
> setting I can do to change the default behavour of an agent job
> regarding warnings but I just don't know how to do it.

Exactly what warnings do you get?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95212D2EFFCFYazorman@.127.0.0.1>...
> Gary (rooty_hill2002@.yahoo.com.au) writes:
> > I have a SQL agent job fails because it gets 10 warnings when it runs
> > a stored procedure. These warnings are trivial and can be ignored. Can
> > I make it ignore these warnings and proceed? I think there is some
> > setting I can do to change the default behavour of an agent job
> > regarding warnings but I just don't know how to do it.
> Exactly what warnings do you get?

Erland,

Thanks for your reply.

I think it is warning 8153. Something like "NULL value has been
eliminated by aggregate function or by a SET statement" (not exact
words though). The job belongs to one of our vendors. Basically there
is a bug in one of the stored procedures it calls. One of SET
statements in the problematic stored procedure doesn't use isnull()
function to filter the result of a subquery properly.

Although I can modify the stored procedure to use isnull() or handle
the warnings within the code, I am not supposed to change their code
(of course I log a support call with them). The thing is I can't wait
for the response, in the meantime, I don't want to set ANSI_WARNINGS
OFF either.

That is the reason I raised my original question. It is also
interesting to me that whether we can do something on the
server/database level to refine the behavour of a agent job in
relation to error handling, or error tolerance rather.

Any idea?

Cheers,

Gary|||Gary (rooty_hill2002@.yahoo.com.au) writes:
> I think it is warning 8153. Something like "NULL value has been
> eliminated by aggregate function or by a SET statement" (not exact
> words though). The job belongs to one of our vendors. Basically there
> is a bug in one of the stored procedures it calls. One of SET
> statements in the problematic stored procedure doesn't use isnull()
> function to filter the result of a subquery properly.
> Although I can modify the stored procedure to use isnull() or handle
> the warnings within the code, I am not supposed to change their code
> (of course I log a support call with them). The thing is I can't wait
> for the response, in the meantime, I don't want to set ANSI_WARNINGS
> OFF either.
> That is the reason I raised my original question. It is also
> interesting to me that whether we can do something on the
> server/database level to refine the behavour of a agent job in
> relation to error handling, or error tolerance rather.

I have to admit that I'm versed in Agent's intricacies, since I use it
only occasionally. But I ran a two-step job, where the first step included
a number of "SELECT avg(col) FROM tbl" where col is a column with many
NULL values, and the second step was just a PRINT statement. I ran this
job, and it Agent says that it was successful. Sure, there is some output
in job history, but that is not failure.

One possibility is that there is a real error in your step as well.

If you look up the job in Agent, double-click it and go to steps,
and then Edit, there is the Advanced tab. Here you can control what is
going to happen on Success and Failure. You can also direct output
to a file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9522921677BCCYazorman@.127.0.0.1>...
> Gary (rooty_hill2002@.yahoo.com.au) writes:
> > I think it is warning 8153. Something like "NULL value has been
> > eliminated by aggregate function or by a SET statement" (not exact
> > words though). The job belongs to one of our vendors. Basically there
> > is a bug in one of the stored procedures it calls. One of SET
> > statements in the problematic stored procedure doesn't use isnull()
> > function to filter the result of a subquery properly.
> > Although I can modify the stored procedure to use isnull() or handle
> > the warnings within the code, I am not supposed to change their code
> > (of course I log a support call with them). The thing is I can't wait
> > for the response, in the meantime, I don't want to set ANSI_WARNINGS
> > OFF either.
> > That is the reason I raised my original question. It is also
> > interesting to me that whether we can do something on the
> > server/database level to refine the behavour of a agent job in
> > relation to error handling, or error tolerance rather.
> I have to admit that I'm versed in Agent's intricacies, since I use it
> only occasionally. But I ran a two-step job, where the first step included
> a number of "SELECT avg(col) FROM tbl" where col is a column with many
> NULL values, and the second step was just a PRINT statement. I ran this
> job, and it Agent says that it was successful. Sure, there is some output
> in job history, but that is not failure.
> One possibility is that there is a real error in your step as well.
> If you look up the job in Agent, double-click it and go to steps,
> and then Edit, there is the Advanced tab. Here you can control what is
> going to happen on Success and Failure. You can also direct output
> to a file.

Erland, thanks again for your reply.

I understand that I can arrange an agent job to do certain things like
"going to next step", or "reporting failure", etc.. Maybe I have not
phrased my question preciously(forgive me for my broken English), let
me try again. What I want is to let the agent NOT REGARD warnings as
part of the criteria for job failure, which means warnings are
IGNORED. I don't have problem with how an agent should do once a
failure occurs and I am sure there is no other error in the stored
procedure since I debugged it throughly.

By the way, if you try the following block, you will likely get the
warning 8153 about usage of NULL value:

declare @.iTemp int
set @.iTemp=select max(colname) from tablename;

In which "tablename" is the table name for the test while "colname" is
the column name in the table with data type integer. If we have
max(colname)= NULL for some reason (although sounds stupid), the
warning occurs. Depends on the business rule involved, in my case
here, an isnull() function should be used to avoid this warning.

If you set up a job with similiar statement in it then your job
probably will fail after 10 warnings occur (ANSI_WARNINGS is ON). But
if you don't want the job to fail just because of this, then my
original question becomes relevant.

It is quite shameful that I still don't know whether we can do this
even after using MSSQL for about 7 years.

Cheers,

Gary|||Gary (rooty_hill2002@.yahoo.com.au) writes:
> I understand that I can arrange an agent job to do certain things like
> "going to next step", or "reporting failure", etc.. Maybe I have not
> phrased my question preciously(forgive me for my broken English), let
> me try again. What I want is to let the agent NOT REGARD warnings as
> part of the criteria for job failure, which means warnings are
> IGNORED. I don't have problem with how an agent should do once a
> failure occurs and I am sure there is no other error in the stored
> procedure since I debugged it throughly.

I think I understand very well what you were after. The problem I have
I is that I am not able to recreate the situation you are describing. This
first step the first of my job:

select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders

And there are plenty of rows with NULL in that column.

> It is quite shameful that I still don't know whether we can do this
> even after using MSSQL for about 7 years.

Obviously, you can. Or at least I can. However, since I don't use
Agent that much I don't want to rule out that there is some setting
that controls this. But at the same time, I don't want to rule out
the possibility that you have a real error in your job, but which
is occluded by the warnings. Have you redirected the output of the
job to a file, and reviewed the output?

If you set up a job with the code above, does succeed or fail?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns952382AF03399Yazorman@.127.0.0.1>...
> Gary (rooty_hill2002@.yahoo.com.au) writes:
> > I understand that I can arrange an agent job to do certain things like
> > "going to next step", or "reporting failure", etc.. Maybe I have not
> > phrased my question preciously(forgive me for my broken English), let
> > me try again. What I want is to let the agent NOT REGARD warnings as
> > part of the criteria for job failure, which means warnings are
> > IGNORED. I don't have problem with how an agent should do once a
> > failure occurs and I am sure there is no other error in the stored
> > procedure since I debugged it throughly.
> I think I understand very well what you were after. The problem I have
> I is that I am not able to recreate the situation you are describing. This
> first step the first of my job:
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> select MAX(ShipRegion) from Northwind..Orders
> And there are plenty of rows with NULL in that column.
> > It is quite shameful that I still don't know whether we can do this
> > even after using MSSQL for about 7 years.
> Obviously, you can. Or at least I can. However, since I don't use
> Agent that much I don't want to rule out that there is some setting
> that controls this. But at the same time, I don't want to rule out
> the possibility that you have a real error in your job, but which
> is occluded by the warnings. Have you redirected the output of the
> job to a file, and reviewed the output?
> If you set up a job with the code above, does succeed or fail?

Erland, thank you for your time.

By running your statement, I didn't get that warning message because
the result of this MAX function will never be NULL although we have a
lots of NULL in the table.

I wrote the following block to simulate the issue I am getting in the
job.

-- 1. create a table to test this warning message.
create table testwarnings
(
TxntIdintegernot null,
maxDepositAmountfloatnull
)

-- 2. populate the table with data.
insert into testwarnings values(1, null)
insert into testwarnings values(2, 10000.00)

-- 3. run the testing block which will give the message.
declare @.fTemp float
set @.fTemp =( select max(maxDepositAmount) from testwarnings where
Txntid=1)

I set up a job with only step 3 in it. The job fails as predicated,
with warning message "Warning: Null value is eliminated by an
aggregate or other SET operation."

The job log clearly shows me that after 10 warning messages, it
aborted without any other warnings or errors. Yes, I can't rule out
any possibility of any other warnings or errors. However before it
aborts it only get 10 times of warning as above. Only thing I am
interested in here is how to make the job to ignore warnings. If it
fails, let it fail on real errors, not warnings.

Cheers,

Gary|||On 11 Jul 2004 17:04:45 -0700, Gary wrote:

[snip]
> -- 3. run the testing block which will give the message.
> declare @.fTemp float
> set @.fTemp =( select max(maxDepositAmount) from testwarnings where
> Txntid=1)
> I set up a job with only step 3 in it. The job fails as predicated,
> with warning message "Warning: Null value is eliminated by an
> aggregate or other SET operation."
> The job log clearly shows me that after 10 warning messages, it
> aborted without any other warnings or errors. Yes, I can't rule out
> any possibility of any other warnings or errors. However before it
> aborts it only get 10 times of warning as above. Only thing I am
> interested in here is how to make the job to ignore warnings. If it
> fails, let it fail on real errors, not warnings.
> Cheers,
> Gary
This may be an unattractive solution, but could you use the equivalent of

set @.fTemp = (
Select max(maxDepositAmount)
from testwarnings
where Txntid=1 and maxDepositAmount is not null)

Or even

set @.fTemp = (
Select max(coalesce(maxDepositAmount,0))
from testwarnings
where Txntid=1 and maxDepositAmount is not null)|||Gary (rooty_hill2002@.yahoo.com.au) writes:
> By running your statement, I didn't get that warning message because
> the result of this MAX function will never be NULL although we have a
> lots of NULL in the table.

If you run the batch which I posted, you do get a whole lot of

Warning: Null value is eliminated by an aggregate or other SET operation.

because there are plenty of NULLs in Northwind..Orders.ShipRegion. Of course
this presumes that you ANSI_WARNINGS ON.

> -- 3. run the testing block which will give the message.
> declare @.fTemp float
> set @.fTemp =( select max(maxDepositAmount) from testwarnings where
> Txntid=1)
> I set up a job with only step 3 in it. The job fails as predicated,
> with warning message "Warning: Null value is eliminated by an
> aggregate or other SET operation."
> The job log clearly shows me that after 10 warning messages, it
> aborted without any other warnings or errors.

One thing is not clear to me. Do you run this batch 10 times in one
job step, and this step fails. Or does the job fail the tenth time
after nine succesful executions? Or does your job have 10 equal job
steps of which the tenth fall?

Sorry for asking stupid questions, but I created a job as you posted,
and it appears to run successfully each time. I included many copies
of the SET statement in the batch, and I have run the job multiple times.
I have not testet many job steps, yet, though.

Maybe it's time to check versions. What does SELECT @.@.version say at
your box? And, if you are running the job on a different server on
which you are runnin SQL Agent, which is the version on the SQL Agent
box?

Does the log include a "Job failed" message, or how do you see that the
job actually failed?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Also, post the output of

msdb..sp_help_job @.jobname = 'your-job-name-here'

Since the information is wide, please put the output in an attachment. (Text
file, please!)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95253CEC93FYazorman@.127.0.0.1>...
> Gary (rooty_hill2002@.yahoo.com.au) writes:
> > By running your statement, I didn't get that warning message because
> > the result of this MAX function will never be NULL although we have a
> > lots of NULL in the table.
> If you run the batch which I posted, you do get a whole lot of
> Warning: Null value is eliminated by an aggregate or other SET operation.
> because there are plenty of NULLs in Northwind..Orders.ShipRegion. Of course
> this presumes that you ANSI_WARNINGS ON.
> > -- 3. run the testing block which will give the message.
> > declare @.fTemp float
> > set @.fTemp =( select max(maxDepositAmount) from testwarnings where
> > Txntid=1)
> > I set up a job with only step 3 in it. The job fails as predicated,
> > with warning message "Warning: Null value is eliminated by an
> > aggregate or other SET operation."
> > The job log clearly shows me that after 10 warning messages, it
> > aborted without any other warnings or errors.
> One thing is not clear to me. Do you run this batch 10 times in one
> job step, and this step fails. Or does the job fail the tenth time
> after nine succesful executions? Or does your job have 10 equal job
> steps of which the tenth fall?
> Sorry for asking stupid questions, but I created a job as you posted,
> and it appears to run successfully each time. I included many copies
> of the SET statement in the batch, and I have run the job multiple times.
> I have not testet many job steps, yet, though.
> Maybe it's time to check versions. What does SELECT @.@.version say at
> your box? And, if you are running the job on a different server on
> which you are runnin SQL Agent, which is the version on the SQL Agent
> box?
> Does the log include a "Job failed" message, or how do you see that the
> job actually failed?

Erland,

Your input is appreciated.

I think I made a mistake. After double checking the vendor's job, I
found it actually calls one stored procedure(let's call it sp_a) which
calls another stored procedure(let's call it sp_b). The warning
message comes from the latter one (sp_b). I have been concentrated on
problematic one sp_b and totally forgot it is actually call by sp_a in
the job.

The job did fail after 10 warning messages (they arouse from sp_b). I
mean the job status is "failed" in the job list of Enterprise Manager.
I can't post the code here due to the understandable reason ( I know
it makes our discussion more difficult), but I can tell that sp_a
loops through a CURSOR and in every row in the CURSOR, it calls sp_b
which means sp_b is repeatedly called.

Also, Erland you are right. I also created a simple job which only
with
"declare @.fTemp float" followed by 11 copies of "set @.fTemp =( select
max(maxDepositAmount) from testwarnings where Txntid=1)". The job
finishes successfully each time!!! Now I am totally lost.

Result of "select @.@.version" is "Microsoft SQL Server 2000 -
8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003
Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build
2195: Service Pack 4) " and I am doing the testing on the server
itself.

I don't know whether just because our job (yours and mine) is doing
direct statement execution , not nested stored procedure calls (as my
vendor's job does), the warning messages are treated differently by
the SQL Agent Service. I will simplify the vendor's job without
changing its characteristic then reproduce the problem so we can dig
deeper.

I will keep you posted.

Thanks again.

Gary|||Gary (rooty_hill2002@.yahoo.com.au) writes:
> The job did fail after 10 warning messages (they arouse from sp_b). I
> mean the job status is "failed" in the job list of Enterprise Manager.
> I can't post the code here due to the understandable reason ( I know
> it makes our discussion more difficult), but I can tell that sp_a
> loops through a CURSOR and in every row in the CURSOR, it calls sp_b
> which means sp_b is repeatedly called.

While it is a different scenario, I fail to see how that could affect SQL
Server Agent.

I realize that you cannot post the original code, and anyway I would
have had any use for it, without tables and data.

What I have been asking for is a "repro" - a script, together with
instructions to run it - that demonstrates the problem. The reason I
keep asking for this, is that without this I have to resort to guessing.
If you were to open a case with Microsoft about this, they would ask
you for the same thing. The one difference, is that in that case you
could actually share the code with MS, since it is not a public forum.

> Result of "select @.@.version" is "Microsoft SQL Server 2000 -
> 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003
> Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build
> 2195: Service Pack 4) " and I am doing the testing on the server
> itself.

That is SQL Server SP3, the same version as I am using.

> I don't know whether just because our job (yours and mine) is doing
> direct statement execution , not nested stored procedure calls (as my
> vendor's job does), the warning messages are treated differently by
> the SQL Agent Service. I will simplify the vendor's job without
> changing its characteristic then reproduce the problem so we can dig
> deeper.

So this is one of the other points of asking people for a repro. If
they have to condense their problematic code to a manageable repro,
they might as a side-effect find the real problem themselves and also
learnt something on the way. I realize that since you are working with
third-party code that you may be interested in this. But if that vendor
has a support desk, there is one more option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
I have not tried this, but it will probably work for you.

There is a registry entry for this:

HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent \
Key: NonAlertableErrors

Try adding the error number to the list and see if it works.

Hope it helps,
Dan Wilson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

how can i know execution stutus of a stored procedure

hi
i want to know the execution status of a stored procedure . That is i want to know whether the stored procedure was executed succesfully or not.If not i want to get the error messageIt depends on the tasks procedure is going to perform. One way is to use error handling mechanism provided by SQL server.|||you can check the value of @.@.ERROR at the end of your stored procedure and return it.....if there is any error in the stored proc it will be indicated in the value of @.@.ERROR|||you can check the value of @.@.ERROR at the end of your stored procedure and return it.....if there is any error in the stored proc it will be indicated in the value of @.@.ERROR

This is not quite true. If the sproc has multiple steps (for example a select, an insert, another select, and a delete) you will need to check the value of @.@.ERROR after each DML statement, because @.@.ERROR only contains the return value from the last operation.

I encourage developers to save the value of @.@.ERROR into a variable, and then inspect the value of that variable as needed.

How can I install OLE DB provider

I using procedure xp_enum_oledb_providers
to check my local SQL server OLE DB provider, I can't find
the OLE DB provider for "Microsoft OLE DB Provider for
DB2" .
only " IBM OLE DB Provider for DB2 Servers" listed.
How can I get "Microsoft OLE DB Provider for DB2".
Thanks,
HGTry this link.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/his/htm/his_dg_ole_db_providers_ccmh.asp
>--Original Message--
>I using procedure xp_enum_oledb_providers
>to check my local SQL server OLE DB provider, I can't
find
>the OLE DB provider for "Microsoft OLE DB Provider for
>DB2" .
>only " IBM OLE DB Provider for DB2 Servers" listed.
>How can I get "Microsoft OLE DB Provider for DB2".
>Thanks,
>HG
>
>.
>

how can i insert id field automatic in table by stored procedure

i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key
got error because allready record has same value .
how i can know the last row's student_id value and input a new valid value in one stored procedure

thanksIf you make the Student_ID field an IDENTITY field, it will automatically increment for you.

how can i include a timestamp on a file name in a stored procedure??

ok...i give up....

can this be done? (sql server 2000)

all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp.

here's my current working file output code:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees.txt" -c -Usa -P'

i'd like it to be something like this:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees" + datetime + ".txt" -c -Usa -P'

but nothing seems to work.

use:

GETDATE() or GETUTCDATE()

|||

You might also want to convert to string..

EXEC .... + CONVERT(Varchar, getdate()) + ...

|||

Bah, I always forget the little things :P

3 cheers for error checking!

|||

RTernier:

Bah, I always forget the little things :P

3 cheers for error checking!

I know.. been there.. plenty of times..

|||

i just can not seem to get the syntax correct.

i've tried:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees" + CONVERT(Varchar, getdate()) + ".txt" -c -Usa -P'

error: (unknown argument '+' on command line)

and:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees' + CONVERT(Varchar, getdate()) + '.txt" -c -Usa -P'

error: (Incorrect syntax near '+')

it always chokes on the '+'

|||

try:

EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout"c:\employees" + CONVERT(VARCHAR(50), getdate()) + ".txt" -c -Usa -P'f

But... I've never seen double quotes actually work in SQL. Would there be any errors on that front?

|||

Concatenation does not work very well with xp_cmdshell.

Declare a variable, do the concatenation and use the variable.

Declare @.Sqlvarchar(500)Set @.sql ='bcp "select * from CEData..employee" queryout "c:\employees" + CONVERT(VARCHAR(50), getdate()) + ".txt" -c -Usa -P'EXEC master..xp_cmdshell @.sql

|||

same ole error...

Unknown argument '+' on command line.

|||

this appeared to work....but in the end, no file exists:

declare @.james as varchar
set @.james = 'c:\employees' + CONVERT(VARCHAR(50), getdate()) + '.txt'
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout @.james -c -Usa -P'

it runs and says "746 rows copied" but no file exists on the c drive

by the way...this works but doesn't provide a timestamp:

declare @.james as varchar
set @.james = 'c:\employees' + CONVERT(VARCHAR(50), getdate()) + '.txt'
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:\employees.txt" -c -Usa -P'

|||

Note: put the server name appropriately:

Declare@.Sqlvarchar(500)

Set@.sql='bcp "select * from CEData..employee" queryout "c:\employees'+CONVERT(VARCHAR(50),getdate())+'.txt" -c -Usa -P -s"ServerName"'

EXEC master..xp_cmdshell @.sql

|||

man...

sooooo close.....

i tried your suggestion, and now it runs, says "746 rows copied"...

and when i look at the file, the name is: employeesOct 24 2007 3

and has 0 bytes

so...the date is getting truncated and there is no data.

did you try this and it worked?

|||

Yes I could get it to work by changing the db/table/server names. Did you use varchar(X) like I mentioned or did you exclude the size/length part as I noticed in your earlier post?

|||

i did it exactly as you typed it

i don't know why it works for you but not me. i get a truncated filename and no data

|||

The spaces and the colon in the timestamp is whats throwing off. Try this:

Declare@.Sqlvarchar(500),@.fnamevarchar(100)

SET@.fname='c:\'+Replace(replace(CONVERT(VARCHAR(50),getdate()),' ','_'),':','_')+'.txt'

Set@.sql='bcp "select * from CEData..employee" queryout "'+@.fname+'" -c -T -S"ServerName"'

EXEC master..xp_cmdshell @.sql

How can I handle OnError event in my stored proc.

Dear All:
I want to ask how can I handle OnError events in stored procedure in MSSQL.
Actually I wanted to place some Rollback procedure on this.
Can you suggest some methods for me?
KEVINfrom BOL

C. Use @.@.ERROR to check the success of several statements
This example depends on the successful operation of the INSERT and DELETE statements. Local variables are set to the value of @.@.ERROR after both statements and are used in a shared error-handling routine for the operation.

USE pubs
GO
DECLARE @.del_error int, @.ins_error int
-- Start a transaction.
BEGIN TRAN

-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'

-- Set a variable to the error value for
-- the DELETE statement.
SELECT @.del_error = @.@.ERROR

-- Execute the INSERT statement.
INSERT authors
VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for
-- the INSERT statement.
SELECT @.ins_error = @.@.ERROR

-- Test the error values.
IF @.del_error = 0 AND @.ins_error = 0
BEGIN
-- Success. Commit the transaction.
PRINT "The author information has been replaced"
COMMIT TRAN
END
ELSE
BEGIN
-- An error occurred. Indicate which operation(s) failed
-- and roll back the transaction.
IF @.del_error <> 0
PRINT "An error occurred during execution of the DELETE
statement."

IF @.ins_error <> 0
PRINT "An error occurred during execution of the INSERT
statement."

ROLLBACK TRAN
END
GO

How can I grab a notification email address from a job?

Is there a handy-dandy way for me to be able, from a stored procedure, to capture the notification email address for a user associated with a job?

I need to send an email out from a stored procedure, and I want it to be set up to send to the same user that is set up in the job that calls the stored procedure.

the same notification stored proc will be used in multiple jobs, but the jobs are already set up with a notification email to be sent to the defined users "on completion" (error or not). I want a separate email to be sent, but I want to use xp_Sendmail because I have to perform a select who's output I want to appear in the body of the email (and unless I am missing something, I have no control over the body of the standard "job completion" email).

I would prefer to NOT have to "hard code" the user email address in the stored proc that calls xp_sendmail, OR to put it in some user table in the database, when the user I want to send it to will always be the same one defined in at the JOB level in Enterprise Manager.

Any thoughts? Thanks in advance for your instantaneous and informative answer-packed responses! ;)I'm shooting from the hip on this, but I'd start with:SELECT j.name, o.email_address
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysoperators AS o
ON (o.id = j.notify_email_operator_id)-PatP|||That's what I was looking for, Oh Great One...

Or something to that effect...I obviously need to get more accustomed to the sys tables...but tried as many ways as I could think of to find a reference to something like that in BOL...but alas, apparently didn't know what to search for *L*

I guess I need to break down and read my SQL200 Unleashed book and learn a little about the architecture that is padding my paycheck *sigh*

As always, thanks for the gentle nudge ;)