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 SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?See srv_pfieldex,
http://msdn.microsoft.com/library/d.../>
07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?See srv_pfieldex,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_ref_07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

getting SQL instance name in extended stored proc

I have a extended stored procedure that I have been using
for a long time and it works fine. During its normal processing
it has to go back into the calling server/database and retrieve
additonal information. We are now working with multiple named
instances of MsSQL on the same box so I do not know the instance
of the server that has called me.
I already use "srv_rpcdb" to get the "database name" but how can
I discover the "instance name" of the server that has called me
so I can properly open a ADO/ODBC connection back into the same
server/database?
See srv_pfieldex,
http://msdn.microsoft.com/library/de...ef_07_3320.asp
Inside an XP you can not retrieve the user database context, so what ever
you retrieve with srv_rpcdb it will not be correct and probably always
return master.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Dennis Passmore" <DennisP@.dpassmore.com> wrote in message
news:uTp9m7xIEHA.2596@.TK2MSFTNGP10.phx.gbl...
> I have a extended stored procedure that I have been using
> for a long time and it works fine. During its normal processing
> it has to go back into the calling server/database and retrieve
> additonal information. We are now working with multiple named
> instances of MsSQL on the same box so I do not know the instance
> of the server that has called me.
> I already use "srv_rpcdb" to get the "database name" but how can
> I discover the "instance name" of the server that has called me
> so I can properly open a ADO/ODBC connection back into the same
> server/database?
>
>

Getting results of stored proc into temporary table

I have a need to get the results of a stored procedure into a temporary
table. The results of the query will be larger than 8000 characters so I
can't use a variable. Following is what is being done in the stored
procedure (findorphans):
==========
PRINT 't1'
SELECT mco_itn,chg_dt,chg_in FROM t1 LEFT JOIN t2 ON t2.mco_itn=t1.mco_itn
WHERE t2.mco_itn IS NULL
PRINT 't3'
SELECT mco_itn,chg_dt,chg_in FROM t3 LEFT JOIN t4 ON t4.mco_itn=t3.mco_itn
WHERE t4.mco_itn IS NULL
==========
I tried the following:
==========
CREATE TABLE ##msgtext (msgtext text)
INSERT INTO ##msgtext exec test..mxw_findorphans
...
DROP TABLE ##msgtext
==========
I end up getting an error as follows:
==========
t1
Server: Msg 213, Level 16, State 7, Procedure findorphans, Line 2
Insert Error: Column name or number of supplied values does not match table
definition
==========
Ultimately I am going to send the results in an email. I had it working
fine but the messages were getting cutoff due to the 8000 character limit.
In the documentation for xp_sendmail they had an example of sending a
message > 8000 which is where I am going with this. I think the problem may
be that line 2 returns 0 rows and for some reason is causing the problem.
Can anyone point me at what I am doing wrong?
TIA
MarkMark,
your temp table need to mirror exactly the columns the stored procedures
will return, so looking at it, your temp table should contain columns
mco_itn,chg_dt,chg_in
I'm also not sure if you can insert multiple resultsets into a temp table.
so you may have to make the queries in the stored procedure into a UNION.
Have you considered a user-defined function which can return a TABLE ? Look
them up in BOL.
Hope that helps.
Damien
"Mark Butler" wrote:

> I have a need to get the results of a stored procedure into a temporary
> table. The results of the query will be larger than 8000 characters so I
> can't use a variable. Following is what is being done in the stored
> procedure (findorphans):
> ==========
> PRINT 't1'
> SELECT mco_itn,chg_dt,chg_in FROM t1 LEFT JOIN t2 ON t2.mco_itn=t1.mco_itn
> WHERE t2.mco_itn IS NULL
> PRINT 't3'
> SELECT mco_itn,chg_dt,chg_in FROM t3 LEFT JOIN t4 ON t4.mco_itn=t3.mco_itn
> WHERE t4.mco_itn IS NULL
> ==========
> I tried the following:
> ==========
> CREATE TABLE ##msgtext (msgtext text)
> INSERT INTO ##msgtext exec test..mxw_findorphans
> ....
> DROP TABLE ##msgtext
> ==========
> I end up getting an error as follows:
> ==========
> t1
> Server: Msg 213, Level 16, State 7, Procedure findorphans, Line 2
> Insert Error: Column name or number of supplied values does not match tabl
e
> definition
> ==========
> Ultimately I am going to send the results in an email. I had it working
> fine but the messages were getting cutoff due to the 8000 character limit.
> In the documentation for xp_sendmail they had an example of sending a
> message > 8000 which is where I am going with this. I think the problem m
ay
> be that line 2 returns 0 rows and for some reason is causing the problem.
> Can anyone point me at what I am doing wrong?
> TIA
> Mark
>
>|||Looks like you can have multiple resultsets from a procedure:
CREATE PROC usp_test
AS
SELECT 1
SELECT 2
GO
DROP TABLE #test
CREATE TABLE #test ( value1 INT )
GO
INSERT INTO #test
EXEC usp_test
SELECT *
FROM #test
DROP PROC usp_test
GO
"Damien" wrote:
> Mark,
> your temp table need to mirror exactly the columns the stored procedures
> will return, so looking at it, your temp table should contain columns
> mco_itn,chg_dt,chg_in
> I'm also not sure if you can insert multiple resultsets into a temp table.
> so you may have to make the queries in the stored procedure into a UNION.
> Have you considered a user-defined function which can return a TABLE ? Lo
ok
> them up in BOL.
> Hope that helps.
>
> Damien
> "Mark Butler" wrote:
>|||When I send the output from the stored procedure directly into xp_sendmail
it is a text based message formated like the following:
=========
t1
mco_itn chg_dt chg_in
-- -- --
t3
mco_itn chg_dt chg_in
-- -- --
=========
I was expecting that to be true the way I was doing it but as you pointed
out that must be false.
Is there another or better way?
Do I need to CAST and concatinate the fields so they are one?
Is there a way to send the results to a text file that I can then attatch to
the email?
Thanx for any suggestions.
Mark
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:7F9C8D7E-06CF-4272-A690-34C86EC517D3@.microsoft.com...
> Mark,
> your temp table need to mirror exactly the columns the stored procedures
> will return, so looking at it, your temp table should contain columns
> mco_itn,chg_dt,chg_in
> I'm also not sure if you can insert multiple resultsets into a temp table.
> so you may have to make the queries in the stored procedure into a UNION.
> Have you considered a user-defined function which can return a TABLE ?
> Look
> them up in BOL.
> Hope that helps.
>
> Damien
> "Mark Butler" wrote:
>

Getting Results in an EMail PDF?

We have the following proc that executes an e-mail and attaches the resulting query as a TXT attachment. Is there a way to make the attachment be a PDF?

@.MYRECIPIENTS varchar (500)

, @.MYSUBJECT varchar (50)

, @.MYBODY varchar (500)

, @.MYQUERY varchar (1000)

, @.MYIMPORTANCE varchar (10)

, @.MYPROFILE varchar (50)

as

declare @.NewSubject varchar(200)

set @.NewSubject = @.MYSUBJECT + ' ' + Cast(GetDate() as varchar(20))

Execute msdb.dbo.sp_send_dbmail

@.profile_name = @.MYPROFILE

,@.recipients = @.MYRECIPIENTS

,@.subject = @.NewSubject

,@.body = @.MYBODY

,@.query = @.MYQUERY

,@.importance = @.MYIMPORTANCE

,@.attach_query_result_as_file = 1

,@.body_format = 'HTML'

Jim,

That's not directly possible. As you recall, SQL Server isn't a printer client, and has no direct capabilities to interact with printers, and PDF is a printer format.

However, you may find some usefulness from a Google? search for a freely available stored procedure that is called "sql 2 pdf".

|||Thanks for the lead. I will follow up on it

Getting proc name from within .Net code

Hi,

I'm currently in the midst of writing my first sqlclr sproc.

In our normal T-SQL sprocs we use the following:

OBJECT_NAME(@.@.PROCID)

to get the name of the sproc. We later use the result of this as a parameter to a custom message when we use RAISERROR.

Question is, is there a way of doing the same from within a SQLCLR sproc? i.e. Can someone give me a bit of code that will return the name of the current sproc? (Obviously this is not the same as the name of the .Net class that implements my sproc).

Thanks in advance for any help that you can provide.

Regards

I've had word from Umachander at MSFT who says that this isn't possible but that they are looking at it for a future version.

-Jamie

Getting error in Execute SQL Task

Hi,

I am trying to execute a Stored Procedure using an Execute SQL Task. my stored proc contains various input paramaters. And the datatype for some of the paramaters are numeric (decimal). So when I am passing values to those paramaters using Exec SQL Task, I have used variables of type Object as it is not working properly even if I declare the variable type as "double". So I have used Object datatype for those variables and it worked properly.

But suddenly from yesterday onwards I am getting an error message (find below).

[Execute SQL Task] Error: Executing the query "USP_InsertData" failed with the following error: "Failed to convert parameter value from a Object to a Double.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If any one have idea on this, please let me know to rectify this error.

Thanks in advance.

Regards,

Prakash Srinivasan

What's the value that it is trying to convert?

-Jamie

|||

Hi Jamie,

Such a fast reply it is. I am taking numeric value from table and passing that value through an SSIS's Object datatype variable to my stored proc. The datatype of the parameter in my stored proc is numeric(10,2).

Regards,

Prakash Srinivasan.

|||

Yes, but what is the actual value it is failing on? I woudl suggest that if works some of teh time then there are particular values that it doesn't like. Admittedly that seems a very strange thing to happen.

-Jamie