Can you export Stored Procedure using BCP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can you export Stored Procedure using BCP?

Author

Topic

boblarson
Starting Member

17 Posts

Posted - 2008-05-10 : 17:44:31

Can you export the results of a stored procedure using BCP?

If so, what is the syntax? If not, I was trying to convert my SP to a view, but it has a CASE statement within it and so I can't save a view with that (at least not as I've been trying).

Thanks,

Bob Larson

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-10 : 20:41:14

You can try with osql or sqlcmd.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-05-10 : 22:21:59

bcp "exec database.dbo.stored_procedure" queryout

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

varshachyk
Starting Member

2 Posts

Posted - 2008-12-22 : 10:47:17

HI,

I tried to execute the below command...
bcp "sp_readerrorlog" queryout "H:\temp.dat" -c -T
but i am getting below error

SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'sp_readerrorlog'.

Please help me...

shreeVarsha

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-22 : 10:54:08

Why there is need of this? Error log are stored in Log folder in default folder for sqlserver?check location with properties of SQL error log.

boblarson
Starting Member

17 Posts

Posted - 2008-12-22 : 12:52:33

quote:
Originally posted by varshachyk
I tried to execute the below command...
bcp "sp_readerrorlog" queryout "H:\temp.dat" -c -T
but i am getting below error

SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'sp_readerrorlog'.

According to jhocutt, I think you should be using:

bcp "exec YourDatabaseNameHere.dbo.sp_readerrorlog" queryout "H:\temp.dat" -c -T

Thanks,

Bob Larson

varshachyk
Starting Member

2 Posts

Posted - 2008-12-23 : 06:35:02

hi,

After changing to

bcp "exec master.dbo.sp_readerrorlog" queryout "H:\temp.dat" -c -T

i am getting the below error
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column

shreeVarsha

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-23 : 08:50:48

quote:
Originally posted by varshachyk

hi,

After changing to

bcp "exec master.dbo.sp_readerrorlog" queryout "H:\temp.dat" -c -T

i am getting the below error
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain at least one column

shreeVarsha

You'd need to store it in a table. Something like,

create table ##test  (a varchar(1000), b varchar(100))
go
insert into ##test
exec master.dbo.xp_readerrorlog
go
exec master..xp_cmdshell 'bcp "select * from ##test" queryout C:\7.txt -c -t   -S servername -U username -P password'
go
drop table ##test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources