|
This looks like a homework assignment.
First try and do it before you ask for help otherwise it defeats the purpose of it.
|
|
|
|
|
|
Dear All,
I need to enable xp_cmdshell in my dB server which is sql server 2000.
For doing that I run the following command on the machine where SQL server 2000 is installed with administrator privileges.
---- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
---- To update the currently configured value for advanced options.
RECONFIGURE
GO
---- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
When I run the third command to enable xp_cmdshell it gives me the following error:
Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
Valid configuration options are:
But I know that xp_cmdshell is present, although it is not visible in Enterprise MAnager. When I run commands using that proc in Query Analyzer it gets executed, which means that it present somewhere.
Unless I enable this proc I cannot use it within another proc (wrapper proc) for calling it from my web application.
Pls provide step by step instructions.
Thanks in advance.
|
|
|
|
|
Perhaps the procedure was not disabled but rather removed from the dll, I have seen this done before. If I remember correctly this procedure is in xplog70.dll, perhaps try getting a copy of this dll from another server and overwrite your own(you will have to stop the SQL service), don't forget to make a backup.
|
|
|
|
|
But if it was removed from the dll how is it working in the Query Analyzer.
So clearly it is present somewhere in the server but my Application cannot access it since it is a non sysadmin user.
|
|
|
|
|
Try this:
In enterprise manager expand your master database and select "Extended Stored Procedures", scroll down to xp_cmdshell, right-click it and select properties. Click on the permissions button. Check if your user has rights.
|
|
|
|
|
IN MSSQL 2005 to Enable xp_cmdshell
Open SQL Server 2005 Surface Area Configuration
Click Surface Area Configuration for Features
Expand Database Engine
Select xp_cmdshell
Check Enable xp_cmdshell
click Apply, click OK
|
|
|
|
|
how to print
*
**
***
****
*****
using sql remember not by using pl/sql block......
thanks in advance....
|
|
|
|
|
Whats your problem, all I see is a request for a solution.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hey,im simply asking whether its possible to do it by simple sql program, if yes then answer it.
otherwise don't mind.
|
|
|
|
|
Oh sorry, then the answer is Yes it can be done.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hey cool man you can do that....we need to use lpad function and rownum to achive this.
Assuming emp table has more than 5 rows,
select lpad('*',rownum,'*') from emp where rownum<6
Read abt rownum in google
|
|
|
|
|
Sounds like you want us to do your homework 
|
|
|
|
|
Sounds like it. Didn't sound like a real world problem.
"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
|
|
|
|
|
set nocount on
declare @ResultSql varchar(8000)
declare @anurag varchar(20)
set @anurag='anurag'
Set @ResultSql='SELECT distinct a.RegID,a.companyname, b.LocalityName FROM Registration AS a INNER JOIN Locality AS b ON a.LocalityID = b.LocalityID inner join pincode as p on a.pincodeid=p.pincodeid WHERE(a.CityID =1) and a.companyname='+@anurag
Create Table #Tbl_Myphonedata
(
Id numeric IDENTITY PRIMARY KEY,
regid numeric,
companyname varchar(30),
localityname varchar(30)
)
--Fill the temp table with the reminders
set @ResultSql = 'select regid,companyname,localityname from( ' + @ResultSql + ' ) OO'
Insert Into #Tbl_Myphonedata
(
regid,companyname,localityname
)
exec
(
@ResultSql
)
select * from #Tbl_Myphonedata
drop table #Tbl_Myphonedata
in above procedure parameter @anurag is string type parameter when i execute exec(@ResultSql) @anurag trate as colunm name but it is a simple parameter
please help me 
no knowledge in .net
|
|
|
|
|
You need to enclose it in quotes so that sql server knows it is a value not a column. Better still, used a parameterised query - faster and less prone to sql injections.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
but i don't know how to enclose string parameter in execute procedure
no knowledge in .net
|
|
|
|
|
That's called learning - look into parameterised queries in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well then, now is a good time to learn - google is your friend 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Just because you don't know how to enclose a parameter or willing to try the parameterized method like Ashfield suggested, you should bash people on the review. People maybe less likely to help in the future.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Hello
I have a program which must read a directory of files and update a database.
I can open my connection at the program start and run the process but it may take a couple of minutes or so. Is is worth opening and closing the database connection for each record I update or can I just hold the connection open for minutes at a time and close it at the end of the process?
Thanks
|
|
|
|
|
You should use connection pooling when you create the connection (creating is the slow bit) and close the connection after each write. This will put the connection into the pool and the next write operation will retrieve and use the same connection.
You could also put all the filenames into a table and bulkcopy them into the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Agreed with Mycroft.
By default connection pooling should be enabled, unless specified otherwise.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
The answer Mycroft Holmes gave is a good and correct way to go.
The only reason I see to keep the connection open in the program is if you have transactional needs over separate writes. For example: if you need a logic where every record is written succesfully to the database or no records at all (commit point is after all separate writes).
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Yeah, what they said.
I definitely wouldn't open the connection any sooner than necessary, but I also wouldn't close it any sooner either.
I prefer to get all the data collected and ready to go, then open, process all the stuff, and close.
If holding all the data in memory at once isn't an option, then opening and closing for each may be a good idea.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
