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 dateformat

Hi,
I'm using MS SQL Server 2000.I need to get the current dateformat from
the server.I know set dateformat command, I tried "select dateformat",
it is showing error.Is there any query or function or procedure to get
the current dateformat.
Thanks & Regards,
ManikandanHi,
Use the below dbcc to get the user options. Dateformat is one amoung them.
DBCC USEROPTIONS
Thanks
Hari
SQL Server MVP
<plmanikandan@.gmail.com> wrote in message
news:1157459291.207274.308070@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm using MS SQL Server 2000.I need to get the current dateformat from
> the server.I know set dateformat command, I tried "select dateformat",
> it is showing error.Is there any query or function or procedure to get
> the current dateformat.
> Thanks & Regards,
> Manikandan
>

Getting the dateformat

Hi,
I'm using MS SQL Server 2000.I need to get the current dateformat from
the server.I know set dateformat command, I tried "select dateformat",
it is showing error.Is there any query or function or procedure to get
the current dateformat.
Thanks & Regards,
ManikandanHi,
Use the below dbcc to get the user options. Dateformat is one amoung them.
DBCC USEROPTIONS
Thanks
Hari
SQL Server MVP
<plmanikandan@.gmail.com> wrote in message
news:1157459291.207274.308070@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm using MS SQL Server 2000.I need to get the current dateformat from
> the server.I know set dateformat command, I tried "select dateformat",
> it is showing error.Is there any query or function or procedure to get
> the current dateformat.
> Thanks & Regards,
> Manikandan
>

getting the current database name

Hi.
I'm making some stored procedures in transact sql, but at the momment i'm
needing a command which can help me getting the current database name.
In adavance, thanks a lot.
Eddy.
DB_NAME()
Andrew J. Kelly SQL MVP
"Eddy Yepes" <Eddy Yepes@.discussions.microsoft.com> wrote in message
news:AE7173F4-226C-4579-B785-25E2B8F820FA@.microsoft.com...
> Hi.
> I'm making some stored procedures in transact sql, but at the momment i'm
> needing a command which can help me getting the current database name.
> In adavance, thanks a lot.
> Eddy.

getting the current database name

Hi.
I'm making some stored procedures in transact sql, but at the momment i'm
needing a command which can help me getting the current database name.
In adavance, thanks a lot.
Eddy.DB_NAME()
Andrew J. Kelly SQL MVP
"Eddy Yepes" <Eddy Yepes@.discussions.microsoft.com> wrote in message
news:AE7173F4-226C-4579-B785-25E2B8F820FA@.microsoft.com...
> Hi.
> I'm making some stored procedures in transact sql, but at the momment i'm
> needing a command which can help me getting the current database name.
> In adavance, thanks a lot.
> Eddy.sql

getting the current database name

Hi.
I'm making some stored procedures in transact sql, but at the momment i'm
needing a command which can help me getting the current database name.
In adavance, thanks a lot.
Eddy.DB_NAME()
Andrew J. Kelly SQL MVP
"Eddy Yepes" <Eddy Yepes@.discussions.microsoft.com> wrote in message
news:AE7173F4-226C-4579-B785-25E2B8F820FA@.microsoft.com...
> Hi.
> I'm making some stored procedures in transact sql, but at the momment i'm
> needing a command which can help me getting the current database name.
> In adavance, thanks a lot.
> Eddy.

Getting the "[dbName].[schema].[tblName]" format of a table

Hi all,

Under SQL Server 2005 :

Is there a command that can get me the "dbName.schema.tblName" for a table?

Or a function that does something that look like that.

Dale

Hi Dale,

See INFORMATION_SCHEMA.TABLES view in BOL.

select

quotename(TABLE_CATALOG) + '.' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)

from

INFORMATION_SCHEMA.TABLES

where

TABLE_CATALOG = 'Northwind'

and TABLE_SCHEMA = 'dbo'

and TABLE_NAME = 'Orders'

and TABLE_TYPE = 'BASE TABLE'

AMB

|||

Is getting the list of table from the INFORMATION_SCHEMA.Tables is a bad programming habit?

I would get the list of table from there since its more complete than the list from sys.tables.

|||

Hi Dale,

I do not think so. There are developers / dba that prefer object catalog views, other prefer information_schema views, etc. Another approach could be:

use northwind

go

select

db_name(),

object_name([object_id]),

schema_name([schema_id])

from

northwind.sys.objects

where

[type] ='U'

and is_ms_shipped = 0

go

AMB

|||I agree with Dale J. As per the MSDN the "INFORMATION_SCHEMA.TABLES" view retruns each table in the current database for which the current user has permissions. But Sys.Tables will return all the table.|||

Hi Manivannan.D.Sekaran,

Seeing just what you are allow to, is called security and not bad practice.

AMB

|||

Yes AMB,

I just agreed the point of DaleJ "getting more data than this view". I didn't say it is a bad practice. (i wont say IDS are bad-practice).

I want to share one point here, the information shcema is the fixed format on the SQL-92 Standard. Whenever the SQL Server's system tables change this views structure never change.(some times it breaks the backward compatibility).

Since it is a SQL-92 standard these quires can be executed on any database (supported by Informix, Oracle, Sybase & our own SQL Server)

sql

Getting sum data from sql

I'm looking to do a command like this:

string selectCmd ="SELECT SUM(hours) FROM tasks WHERE project=" + project;

string strConnection =ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;

SqlConnection myConnection =newSqlConnection(strConnection);SqlCommand myCommand =newSqlCommand(selectCmd, myConnection);

myConnection.Open();

I would like the function to spit out the sum of the hours as the select command says, but is Open() the right syntax and how would i go about assigning the result to a variable? I'm familiar when it deals with actually returned data, but not a single number.

Int32 count = (Int32) myCommand.ExecuteScalar();

|||

Do a quick F1 search for the ExecuteScalar method of SqlCommand...

|||

great Thanks!

Getting OUTPUT parameter in an OLE DB Command

Is it possible to use stored procedure with output parameter and retrieves the values of that output parameter, in an OLE DB Command within a Data Flow?

What I wanted to do is to get the newly created identity of a row so that I can insert it to the main data set in data flow. I'm not even sure if there is even a much better design to achieve this. I've rummaged the internet but everything I got were all about Execute SQL Task.

Rather than using the OLE DB Command to get the identity column value (which is going to be significantly slower than inserting through an OLE DB Destination), have you considered generating your own keys in the data flow? There's an example here: http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/ and here: http://www.sqlis.com/37.aspx, plus numerous posts on the forum.|||

Hi jwelch. I will be perusing those sites soon. Regarding key generation, I'm relying on IDENTITY columns so I'm afraid this is not an option for me unless those sites will prove otherwise. Thanks for the reply

|||I discovered that this is not the most optimized way of achieving my goal so I abandoned this one. Unfortunately, I encountered another showstopper which you can find more about here --> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1877437&SiteID=1|||

u_r_twisted wrote:

Regarding key generation, I'm relying on IDENTITY columns so I'm afraid this is not an option for me unless those sites will prove otherwise. Thanks for the reply

Gosh, I HATE identity columns. They are a pain in the rear to work with.... Nice for some things, but when doing ETL they stink. When you look at the link at my site above (ssistalk.com) that John linked to, I have a small discussion on why using identity columns is not preferred.