October 30, 2019 at 11:50 am
Hi,
I have to take output of stp using bcp command. the stp contains one parameter. I want to know how to pass parameter to stp and get the output. the batch contains below bcp command
set DBName=DBLive
set DBServerName=ServerLive
set FileName=ClosePrice_N.csv
bcp "EXEC "%DBName%..stp " " queryout %FileName% -Uadmin -Padmin -S%DBServerName% -t, -w
How to pass parameter to the stp
Kindly help.
Regards,
Saumik
October 30, 2019 at 12:11 pm
As always when you call a procedure, you put the parameter value after the stored procedure. For instance:
et DBName=DBLive
set DBServerName=ServerLive
set FileName=ClosePrice_N.csv
set param="My parameter value"
bcp "EXEC %DBName%..stp '%param%'" queryout %FileName% -Uadmin -Padmin -S%DBServerName% -t, -w
Note that you should only wrap %param% in single quotes if it is a string. You would not do this for a numeric parameter.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 30, 2019 at 1:02 pm
Hi Erland,
I followed the step as you guided. however the output did not come. On checking the profiler i found that parameter value is not getting passed. The value of parameter is time for e.g. 17:00:00 or 17:30:00
October 30, 2019 at 1:08 pm
So how does your script actually look like?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 30, 2019 at 1:26 pm
October 30, 2019 at 1:36 pm
It would be better to paste the code for the script, using the insert/edit code sample button rather than posting a picture. That would also permit you hide your sa password.
Try
set param=17:30:00
without quotes, or else the quotes will be included in the value, which is not going to work well.
I hope that you realise that you are asking a question about how to use the DOS command line. While an SQL Server tool is the use case, it is not really an SQL Server question.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 30, 2019 at 1:53 pm
Noted about the type of the question as well as insert/edit code.
Now i passed the parameter without quotes, but still blank value is getting passed to the stp instead of the time.
October 30, 2019 at 4:54 pm
I created this procedure in tempdb:
CREATE OR ALTER PROCEDURE slafs_sp @time time(0) AS
SELECT * FROM sys.objects WHERE convert(time(0), modify_date) <= @time
set DBNAME=tempdb
set DBServerName=SOMMERWALD
set FileName=slask.csv
set param=23:59:59
bcp "EXEC %DBName%..slafs_sp '%param%'" queryout %FileName% -T -S%DBServerName% -t, -w
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 31, 2019 at 5:57 am
Hi Erland,
Thanx for the help. i figured out the issue. there was space between Param variable and the value in Param definition.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy