|
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.
|
|
|
|
|
sunny74 wrote: Could you pls write a proc for me
Yes, I could, but I won't. The best solution is to print the sql rather tahn exec it, then you can see whats wrong. Its all part of the learning process, called debugging, and as a developer its something you should be able to do - after all, its what you are being paid for.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
iam using MySql db,
my stored procedure is like this..
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`IS_CITY_EXISTS` $$
CREATE PROCEDURE `IS_CITY_EXISTS`(
IN CITYNAME VARCHAR(45),
OUT AVAILABLE BOOLEAN
)
BEGIN
IF EXISTS(SELECT * FROM city_details WHERE city_name=CITYNAME) THEN
SELECT 1 INTO AVAILABLE;
ELSE
SELECT 0 INTO AVAILABLE;
END IF;
END $$
DELIMITER ;
when iam executing this procedure works fine..
CALL IS_CITY_EXISTS('kak',@Available);
but while exceuting SELECT @AVAILABLE;--IT'S giving result null.
is there any error in procedure..
how can i call this procedure in (c#)..
murali krishna
|
|
|
|
|
I've never used MySQL but two observations still:
- you set delimiter to $$. However you use semicolon after the statements.
- is 0 equal to false in mysql or does it understand false keyword. If so, 0 may be interpreted to null since it's not false.
But then again, just guessing.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Is there anybody show me how to create Function in MySql database?
I need it urgently. 
I have been seeking and trying for 2 days.
but not succeed.
i m trying to extract only number from a given string.
but everytime i try, it show error msg (syntax error)
could anybody has a sample function in mysql database pls?
thanks in advance!
|
|
|
|
|
kyi kyi wrote: I need it urgently.
Not that urgently or you would have tried here[^] - loads of samples.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Quit cross posting.
kyi kyi wrote: I need it urgently.
Oh well, that's your problem. Not mine or anyone else's.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Hi all .
I wount to restore the DataBase But the restore operation is Feild and get me the Error int message : " System.Data.SqlClient.SqlError : The Operating System returned the error '5(error not found)' while attempting 'Restore Container :: ValidationTargetForCreation' on (Microsoft.SqlServer.Express.Smo)" .
What is the Error Meaning and How i Can Solve it ??
Thanks For Any boudy hellp me ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Thaer
|
|
|
|
|
Most likely the file you're trying to restore does not exist or the directory is wrong. Remember that when you issue a restore command, the file must be defined in a way how SQL Server service sees it.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
How to Following the steps that I define aconnection Between My Server And The Client Pc ..
thanks...
jetwll
|
|
|
|
|
Try here [^]
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I want to retrieve top 2 of maximum salary records.What wud be the qeury???
|
|
|
|
|
If you've got only one table containing all salaries, your answer is as simple as this:
SELECT TOP(2) Salary
FROM TABLE
ORDER BY Salary DESC
_
|
|
|
|
|
Can u plz tell if I want to get minimum of top 2.
|
|
|
|
|
Order the records in ascending order and select top two 
|
|
|
|
|
select top 2 salary from tbl_salary order by salary desc
select top 2 salary from tbl_salary order by salary asc
Errors are like Mini Skirts, shorter they get, more revealing they become.
|
|
|
|
|
Now I got a question for you; Imagine you want to retrieve the top 3 salary records. How would you go about and do that? 
|
|
|
|
|
you din understand my question and now making fun of me.'minimum of top 2' means the lesser of the top 2 that is the second largest salary.Now if you are too smart tell this eddyvluggen.
|
|
|
|
|
joindotnet wrote: making fun of me
I was, wasn't I?
joindotnet wrote:
What wud be the qeury???
You just wanted someone to write a query for you, and someone did. Have you actually tried writing the query? If so, why didn't you post the piece that you already wrote?
|
|
|
|
|
I can write the queries by myself. I just wanted to know whether there is a simplification for it
select min(salary) from (select top 2 salary from order by salary desc)
By the way this place is for professionals not for people like u who r here to have fun.
|
|
|
|
|
I didn't know professionals could not have fun. If you ask an oversimple question or break the forum guidelines, you will get flamed.
Blog link to be reinstated at a later date.
|
|
|
|
|
I need to copy rows from datatable to database by using SQLBulkCopy. I am using ODBC connection. How can I use ODBC connection with SQLBulkCopy.
|
|
|
|
|
SQLBulkCopy is a class that is used with native SQL Server connection (SQLConnection). It cannot be used with ODBC connection. If you're using Microsoft ODBC driver for SQL Server, it has enhancements that make it possible to use bulk copy operations, but then the functionality is the same as in bcp, not the same what is defined for SQLBulkCopy.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi..
My function is to extract only number from given string.
i m using mysql database and i m new to the syntax.
is there anyone help me pls?
thanks in advance!
=====================
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END
RETURN @IntNumbers
=============================================
The following error Message is shown.
Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@locator VARCHAR(20))
RETURNS INT
AS
BEGIN
DECLARE @cnt INT
DECLARE' at line 1
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
