Click here to Skip to main content
15,867,453 members
Home / Discussions / Database
   

Database

 
QuestionHow to get the last record .. Stored Procedure? Pin
kindman_nb1-Nov-08 5:54
kindman_nb1-Nov-08 5:54 
AnswerRe: How to get the last record .. Stored Procedure? Pin
Blue_Boy1-Nov-08 7:01
Blue_Boy1-Nov-08 7:01 
GeneralRe: How to get the last record .. Stored Procedure? Pin
Rob Graham1-Nov-08 7:07
Rob Graham1-Nov-08 7:07 
AnswerRe: How to get the last record .. Stored Procedure? Pin
Rob Graham1-Nov-08 7:05
Rob Graham1-Nov-08 7:05 
GeneralRe: How to get the last record .. Stored Procedure? Pin
r_mohd1-Nov-08 11:46
r_mohd1-Nov-08 11:46 
QuestionPervasive 9 Pin
Zeyad Jalil1-Nov-08 4:01
professionalZeyad Jalil1-Nov-08 4:01 
AnswerRe: Pervasive 9 Pin
Ashfield2-Nov-08 21:14
Ashfield2-Nov-08 21:14 
Questionstored procedure for executing another proc generates error Pin
sunny741-Nov-08 0:47
sunny741-Nov-08 0:47 
Dear All,



I am trying to put the code for generating an eXcel file using xp_cmdshell into a stored procedure for the purpose of calling it from code.

The code was given by a member of one of the forums sometime back.

USE northwind
EXEC master..xp_cmdshell 'bcp "select ''categoryname'',''description'' union all select categoryname,description from northwind.dbo.categories" queryout "d:\Shared\test1.xls" -c -S"172.16.121.203" -U"sa" -P""'

The above code works brilliantly.

I tried to put it into a stored procedure but getting various errors.The sp creation code is as below:

CREATE PROCEDURE [dbo].[SpWriteExcel]
AS
--create a variable @sql
--DECLARE @SQLStatement varchar(500)
DECLARE @SQLStatement1 varchar(500)

-- @SQLStatement = USE northwind

SELECT @SQLStatement1 = "EXEC master..xp_cmdshell 'bcp select ' 'categoryname' ' ,' 'description' ' union all select categoryname,description from northwind.dbo.categories queryout 'd:\Shared\test2.xls' -c -S\'172.16.121.203\' -U 'sa' -P' ''"

--Execute the SQL statement
--EXEC(@SQLStatement)
EXEC(@SQLStatement1)
GO


The error is with SELECT @SQLStatement1. So I tried to change it in various ways but did not get any success.

One of the ways I changed it to is as below:

SELECT @SQLStatement1 = " EXEC master..xp_cmdshell 'bcp "+"select categoryname,description union all select categoryname,description from northwind.dbo.categories"+" queryout "+ "d:\Shared\test3.xls"+" -c "+"-S"+"172.16.121.203"+"-U"+"sa"+" -P"+""+"'"


Could you pls write a proc for me which contains the above command and which takes the database name, table name and the excel file path as parameters.

Thanks for your help.
AnswerRe: stored procedure for executing another proc generates error Pin
Ashfield2-Nov-08 20:10
Ashfield2-Nov-08 20:10 
Questionstored procedure problem in Mysql Pin
avvaru.murali31-Oct-08 1:21
avvaru.murali31-Oct-08 1:21 
AnswerRe: stored procedure problem in Mysql Pin
Wendelius2-Nov-08 0:05
mentorWendelius2-Nov-08 0:05 
QuestionFunction syntax in Mysql Pin
kyi kyi30-Oct-08 17:51
kyi kyi30-Oct-08 17:51 
AnswerRe: Function syntax in Mysql Pin
Ashfield30-Oct-08 22:23
Ashfield30-Oct-08 22:23 
AnswerRe: Function syntax in Mysql Pin
Paul Conrad31-Oct-08 7:42
professionalPaul Conrad31-Oct-08 7:42 
QuestionRestore DataBase Pin
Thaer Hamael30-Oct-08 2:23
Thaer Hamael30-Oct-08 2:23 
AnswerRe: Restore DataBase Pin
Wendelius30-Oct-08 7:49
mentorWendelius30-Oct-08 7:49 
QuestionSql server 2005 Pin
jetwll30-Oct-08 1:08
jetwll30-Oct-08 1:08 
AnswerRe: Sql server 2005 Pin
Ashfield30-Oct-08 2:15
Ashfield30-Oct-08 2:15 
Questionsql query Pin
joindotnet29-Oct-08 20:02
joindotnet29-Oct-08 20:02 
AnswerRe: sql query Pin
Meysam Mahfouzi29-Oct-08 20:05
Meysam Mahfouzi29-Oct-08 20:05 
QuestionRe: sql query Pin
joindotnet29-Oct-08 20:12
joindotnet29-Oct-08 20:12 
AnswerRe: sql query Pin
Giorgi Dalakishvili29-Oct-08 20:29
mentorGiorgi Dalakishvili29-Oct-08 20:29 
AnswerRe: sql query Pin
vaseeem30-Oct-08 9:31
vaseeem30-Oct-08 9:31 
AnswerRe: sql query Pin
Eddy Vluggen30-Oct-08 23:42
professionalEddy Vluggen30-Oct-08 23:42 
GeneralRe: sql query Pin
joindotnet30-Oct-08 23:58
joindotnet30-Oct-08 23:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.