Getting the OUT parameter of stored proc in .cmd file.

Hi,
Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
the output parameter of stored proc in .cmd command. Based on the value i
need to terminate the program. Below are the details.
In the .cmd file we use the below syntax for truncating a table.
isql -E -S%1 -d%2 -Q"truncate table tb_mast"
Right now this needs to be replaced by calling a stored procedure by passing
the table name and get the return value to check sucess/failure through the
output parameter. If any error the program (.cmd) should terminate.
Store procedure is ready and it is working fine. Procedure will be like
sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
parameter2 will be set as 0, else 1.
How do I call the procedure in the .cmd file and check the success/failure b
y
getting the output parameter?
Please help me in this regard. Hope my explanation is clear.
Thanks in advance.
Vivek.
rHi,
May not be possible directly. Why do you want to have a command file to do
this?
You could truncate table inside the stored procedure by implementing a
condition right.If there is any technical difficulty please write back.
Thanks
Hari
SQL Server MVP
"rvivek2000" wrote:

> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to acces
s
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passi
ng
> the table name and get the return value to check sucess/failure through th
e
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure
by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
> --
> r
>|||rvivek2000 wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to acces
s
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passi
ng
> the table name and get the return value to check sucess/failure through th
e
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure
by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
>
Have a look at the EXIT command that can be used with isql. You can use
it to set the ERRORLEVEL based the results of a query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Getting the OUT parameter of stored proc in .cmd file.

Hi,
Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
the output parameter of stored proc in .cmd command. Based on the value i
need to terminate the program. Below are the details.
In the .cmd file we use the below syntax for truncating a table.
isql -E -S%1 -d%2 -Q"truncate table tb_mast"
Right now this needs to be replaced by calling a stored procedure by passing
the table name and get the return value to check sucess/failure through the
output parameter. If any error the program (.cmd) should terminate.
Store procedure is ready and it is working fine. Procedure will be like
sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
parameter2 will be set as 0, else 1.
How do I call the procedure in the .cmd file and check the success/failure by
getting the output parameter?
Please help me in this regard. Hope my explanation is clear.
Thanks in advance.
Vivek.
--
rHi,
May not be possible directly. Why do you want to have a command file to do
this?
You could truncate table inside the stored procedure by implementing a
condition right.If there is any technical difficulty please write back.
Thanks
Hari
SQL Server MVP
"rvivek2000" wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passing
> the table name and get the return value to check sucess/failure through the
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
> --
> r
>|||rvivek2000 wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passing
> the table name and get the return value to check sucess/failure through the
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
>
Have a look at the EXIT command that can be used with isql. You can use
it to set the ERRORLEVEL based the results of a query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Getting the last Error Message

I have a process that runs that pulls data from an Oracle database to SQL
server, when I encounter an error I'd like to write the error description
back to Oracle for the record that failed for reporting purposes. I can get
the code using @.@.ERROR but I'd like to have the message associated with this
error code populated with the field and table name and operation associated
with the error, example: Error Code 515, here's the associated message from
sysmessages: Cannot insert the value NULL into column '%.*ls', table '%.*ls'
;
column does not allow nulls. %ls fails.
I want the completed message that looks like this: Cannot insert the value
NULL into column 'vm2_location', table 'Intergy.dbo.vmailto'; column does no
t
allow nulls. UPDATE fails.
How can I get this...?
Thanks in advance..
DanIt's probably not what you are looking for, but DBCC OUTPUTBUFFER() will
contain the error message that is sent to the client. Unfortunately, I can't
think of any meaningful way of formatting it.
On the bright side, SQL Server 2005 has an ERROR_MESSAGE function which can
be used to capture the error message within a TRY..CATCH block.
"Alien2_51" wrote:

> I have a process that runs that pulls data from an Oracle database to SQL
> server, when I encounter an error I'd like to write the error description
> back to Oracle for the record that failed for reporting purposes. I can ge
t
> the code using @.@.ERROR but I'd like to have the message associated with th
is
> error code populated with the field and table name and operation associate
d
> with the error, example: Error Code 515, here's the associated message fro
m
> sysmessages: Cannot insert the value NULL into column '%.*ls', table '%.*l
s';
> column does not allow nulls. %ls fails.
> I want the completed message that looks like this: Cannot insert the value
> NULL into column 'vm2_location', table 'Intergy.dbo.vmailto'; column does
not
> allow nulls. UPDATE fails.
> How can I get this...?
> Thanks in advance..
> Dan|||The later sounds a lot nicer, nonetheless OUTPUTBUFFER is still an option,
thanks for the reply!!!
Dan
"Mark Williams" wrote:
> It's probably not what you are looking for, but DBCC OUTPUTBUFFER() will
> contain the error message that is sent to the client. Unfortunately, I can
't
> think of any meaningful way of formatting it.
> On the bright side, SQL Server 2005 has an ERROR_MESSAGE function which ca
n
> be used to capture the error message within a TRY..CATCH block.
> "Alien2_51" wrote:
>

Getting started with Sql Server 2005....Create Database

Hi allI'm a newbie with Visual studio 2005 .Recently i installed it ,AFAIK sqlServer 2005also is shiped with vs2005.after installing vs2005 i have the following entries in my StartMenu/All Programms/1) Microsoft Sql Server 2005Configuration Tools sql Server Configuration Manager Sql Server Error and usage Reporting SQL Server Surface Area Configuration2) Microsoft Visual studio 2005 .... ......Apparently there isn't any ENTERPRISE MANAGER and query analyzer.?By the way in Visual studio IDE i noticed "Server Explorer" nexed to "Solution Explorer"but i didn't find any Database entry or "Create New Database" or some thing like this.!!!Could any one help me.? i want to Create Database, Tables,...Thanks in advance.Regards.

You're looking for Management Studio Express. Start with:

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

Jeff

|||

ZarrinPour:

Hi all I'm a newbie with Visual studio 2005 .Recently i installed it ,AFAIK sqlServer 2005 also is shiped with vs2005.after installing vs2005 i have the following entries in my StartMenu/All Programms/ 1) Microsoft Sql Server 2005 Configuration Tools sql Server Configuration Manager Sql Server Error and usage Reporting SQL Server Surface Area Configuration 2) Microsoft Visual studio 2005 .... ...... Apparently there isn't any ENTERPRISE MANAGER and query analyzer.? By the way in Visual studio IDE i noticed "Server Explorer" nexed to "Solution Explorer" but i didn't find any Database entry or "Create New Database" or some thing like this.!!! Could any one help me.? i want to Create Database, Tables,... Thanks in advance. Regards.

That depends on the edition of VS2005 if you have standard you can use the Advanced version of Express from the link below however profesional comes with the developer edition which means you need to copy the contents of your CD/DVD to your hard drive and rerun setup and choose add components or repair and add the management tools that gives you Management Studio. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

sql

getting recordset via ado/sproc

Trying to get output from sqlserver stored procedure into access recordset via ado.

I have stored procedure with select statement that returns rows when executed from query analyser:

CREATE procedure ps_tblMackVendors_Select_test
AS
set quoted_identifier on
declare @.strsql nvarchar(300)
set @.strsql = 'Select * from tblMackVendors
exec (@.strsql)
GO

When I execute following bit of code from Access, the set rst = cmd.execute runs ok, but the rst.movefirst gets error 3704, Operation is not allowed when the object is closed.

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = mcnnSQLDB
cmd.CommandText = "ps_tblMackVendors_Select_test"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute
rst.MoveFirst

I have tested the stored procedure & the connection using an output parameter, doing a cmd.execute (without the rst..) & the value is retrieved by access okprobably you need to rethink your app structure before you go too far with it. i bet your rst will successfully movefirst if you just do a straight select from your sp.|||My question would be why do you have

CREATE procedure ps_tblMackVendors_Select_test
AS
set quoted_identifier on
declare @.strsql nvarchar(300)
set @.strsql = 'Select * from tblMackVendors
exec (@.strsql)
GO

instead of,...

CREATE procedure ps_tblMackVendors_Select_test
AS
Select * from tblMackVendors
GO

???|||well, i understand he's just experimenting, getting ready for the real stuff, but i'm saying "don't go that route, there is a sign there - no outlet!"|||What happens if you open a recordset using a sql string instead of a stored procedure ? Also, you should debug your vb code and view the locals to see what is going on with the rst object.|||my sp uses string for qry because I want to build the sql from values passed to the sp in the final version.
I did just try it without the string for the sql & it worked - good idea. But it does work with the string when I call the sp from query analyzer. Why do you think there is a difference ?|||multiple recordsets is one option,...

using sp_executesql instead of exec might help.

personally I'd really avoid this method of doing things though... if you are going to use dynamic sql then use it in the application, not in a stored proc.

Getting Paragraph Count

Hi,
I need to get a paragraph count of text
1. Pasted into a SQLServer VARCHAR field from Word, and also
2. Entered directly into a SQLServer VARCHAR field entered via an Access ADP
textbox
I tried using
CHARINDEX (ColumnName, CHAR(10),1)
CHARINDEX (ColumnName, CHAR(13) ,1)
CHARINDEX (ColumnName, ' ', 1)
to find the first instance of a blank line, but all returned zero.
Is there a way to count the paragraphs, i.e. blank lines between paragraphs?
Once I know how to find the blank lines, I can loop through text to find all
occurences.
Thanks.
AlanNEVER MIIND!
I had expression order reversed!
Alan
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:ulLxGNQUGHA.5172@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to get a paragraph count of text
> 1. Pasted into a SQLServer VARCHAR field from Word, and also
> 2. Entered directly into a SQLServer VARCHAR field entered via an Access
ADP
> textbox
> I tried using
> CHARINDEX (ColumnName, CHAR(10),1)
> CHARINDEX (ColumnName, CHAR(13) ,1)
> CHARINDEX (ColumnName, ' ', 1)
> to find the first instance of a blank line, but all returned zero.
> Is there a way to count the paragraphs, i.e. blank lines between
paragraphs?
> Once I know how to find the blank lines, I can loop through text to find
all
> occurences.
> Thanks.
> Alan
>|||You could try this sort of thing. If you remove all the paragraph dividers
from a piece of text, you can then compare the lengths of the original strin
g
with the new one, to see how much text you removed. Divide it by the length
of the paragraph divider, and you have how many there were. Add one because
you're counting the paragraph-splits, not the paragraphs themselves.
Like this (but obviously you wouldn't have to use variables, you could just
use your column names directly):
declare @.text varchar(8000)
set @.text = 'Here is some text.
Here is a second paragraph.
Here is a third.'
declare @.paragraphdivider char(4)
set @.paragraphdivider = char(13)+char(10)+char(13)+char(10)
declare @.paragraphs int
select @.paragraphs = (len(@.text) - len(replace(@.text,@.paragraphdivider,''))
)
/ len(@.paragraphdivider) + 1
select @.paragraphs
"Alan Z. Scharf" wrote:

> NEVER MIIND!
> I had expression order reversed!
> Alan
>
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:ulLxGNQUGHA.5172@.TK2MSFTNGP12.phx.gbl...
> ADP
> paragraphs?
> all
>
>

Getting Error code from SqlServer to FrontEnd Application

Hi,

Frndz, Asume that we have some error in stored procedure. We shall control that error by using transactions in Backend(Sql Server). But how z t possible to give the information to the front end application that error has occured in backend ? Will anyone plz help me.

If u cant able to understand plz mail me at mneduu@.gmail.com

Thankz in Advance.

Thanks & Regards
(M. Nedu)You could have a variable as an integer, you can set the varaible with @.@.ERROR. The variable will assign a number in the varaible if an error occurs.

Then before you commit your transaction you can have an if stment, so

If @.Variable <> 0
Begin
@.Reason = 'what ever you want to put in'
End
Else
Commit

Somthing like that