Replacement for xp_cmdshell in SQL2k

ISSUE (in SQL Server 2000): Some Client applications’ stored procedures ar
e
using “xp_cmdshell”.
xp_cmdshell is an extended stored procedure in Master database. This command
takes a string of dos commands and executes them in the DOS command shell.
Now the Client wants a replacement for this xp_cmdshell . i.e., I need some
other function or procedure or something which I can place instead of this
xp_cmdshell in the respective stored procedures using this xp_cmdshell.
Note: The Client doesn’t want to use this xp_cmdshell at all.The client probably heard that having xp_cmdShell enabled adds to your
security risk, and wants a safe way to do the same things they have been
doing.
What have they been doing with xp_cmdshell? There may be ways to replace
that functionality, there may not.
You could create jobs that execute cmdexec steps, and have the stored
procedures activate the jobs.
Please provide more info about what they do and why they want to discontinue
using xp_cmdshell.
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> are
> using "xp_cmdshell".
> xp_cmdshell is an extended stored procedure in Master database. This
> command
> takes a string of dos commands and executes them in the DOS command shell.
> Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> some
> other function or procedure or something which I can place instead of this
> xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> Note: The Client doesn't want to use this xp_cmdshell at all.
>|||The solution depends on what you are executing via xp_cmdshell. One
approach is to create a new extended stored procedure to encapsulate the
needed functionality.
Another method is to move the processing out of SQL Server and into
application code, either on the client or on a server. It is likely that
this is the better approach if your client is concerned about security.
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
> ISSUE (in SQL Server 2000): Some Client applications' stored procedures
> are
> using "xp_cmdshell".
> xp_cmdshell is an extended stored procedure in Master database. This
> command
> takes a string of dos commands and executes them in the DOS command shell.
> Now the Client wants a replacement for this xp_cmdshell . i.e., I need
> some
> other function or procedure or something which I can place instead of this
> xp_cmdshell in the respective stored procedures using this xp_cmdshell.
> Note: The Client doesn't want to use this xp_cmdshell at all.
>|||Thank You for your response.
It is because of some security reasons the Client does not want any non-DBA
to use "xp_cmdshell" command.
This command takes a string as input and executes the same in the DOS
command shell. The string is nothing but a valid DOS command.
Example: Exec xp_cmdshell 'dir *.*'
Similarly as above my requirement has FTP, move, jview, copy, del, bcp etc.,
DOS commands as inputs to this extended stored procedure in the form of a
string.
I hope I explained it more clearly to you now.
-Aizaz
"David Lundell" wrote:

> The client probably heard that having xp_cmdShell enabled adds to your
> security risk, and wants a safe way to do the same things they have been
> doing.
> What have they been doing with xp_cmdshell? There may be ways to replace
> that functionality, there may not.
> You could create jobs that execute cmdexec steps, and have the stored
> procedures activate the jobs.
> Please provide more info about what they do and why they want to discontin
ue
> using xp_cmdshell.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
>
>|||Thank You for your response.
A string of DOS commands is the input "xp_cmdshell".
Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
Which means we need some way in which we can take a string (which is a valid
DOS command) and execute it in DOS command shell (which is the functionality
of xp_cmdshell)
"Dan Guzman" wrote:

> The solution depends on what you are executing via xp_cmdshell. One
> approach is to create a new extended stored procedure to encapsulate the
> needed functionality.
> Another method is to move the processing out of SQL Server and into
> application code, either on the client or on a server. It is likely that
> this is the better approach if your client is concerned about security.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:AC38DE7E-AD41-4414-AC0C-A8BC3C377DC7@.microsoft.com...
>
>|||> Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
So why are you using xp_cmdshell for this task? Is there some reason you
cannot perform file manipulation like this directly from you application
code?
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...[vbcol=seagreen]
> Thank You for your response.
> A string of DOS commands is the input "xp_cmdshell".
> Example:- Exec xp_cmdshell 'copy file1.txt file2.txt'
> Which means we need some way in which we can take a string (which is a
> valid
> DOS command) and execute it in DOS command shell (which is the
> functionality
> of xp_cmdshell)
> "Dan Guzman" wrote:
>|||There is some processing done in a stored procedure and this extended stored
procedure is called after that. Once the xp completes its job, some more
processing done in the original stored procedure.
Also there are many stored procedures which internally call xp for different
tasks like bulk copy etc. Most of these stored procedures are scheduled SQL
tasks and are not called from any application. So replacing this
functionality in application code can be ruled out.
Thanks for your inputs/suggestions...
"Dan Guzman" wrote:

> So why are you using xp_cmdshell for this task? Is there some reason you
> cannot perform file manipulation like this directly from you application
> code?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "aizaz" <aizaz@.discussions.microsoft.com> wrote in message
> news:B6A188BB-55FD-48C7-9A0F-6451EC953F7F@.microsoft.com...
>
>|||You might consider splitting the stored procedures that currently execute
xp_cmdshell into separate procs. This would allow you to replace
xp_cmdshell functionality with a CmdExec job step. For example:
1) Transact-SQL step: EXEC proc 1
2) CmdExec step: BCP
1) Transact-SQL step: EXEC proc 2
Your client might be more receptive to this method since the command runs
outside the SQL Server service process.
Hope this helps.
Dan Guzman
SQL Server MVP
"aizaz" <aizaz@.discussions.microsoft.com> wrote in message
news:0299E709-4A85-4158-B9A6-E62B1722765A@.microsoft.com...[vbcol=seagreen]
>
> There is some processing done in a stored procedure and this extended
> stored
> procedure is called after that. Once the xp completes its job, some more
> processing done in the original stored procedure.
> Also there are many stored procedures which internally call xp for
> different
> tasks like bulk copy etc. Most of these stored procedures are scheduled
> SQL
> tasks and are not called from any application. So replacing this
> functionality in application code can be ruled out.
> Thanks for your inputs/suggestions...
> "Dan Guzman" wrote:
>

Replace() & upper () in stored procedure

Hi;

I have a stored procedure :

<code
Create Procedure ControlDept
(

@.DeptID nvarchar(10)
)
As
If Exists
(
Select DeptName From Departments Where
DeptID LIKE @.DeptID
)
Return 1
Else
Return 0

Now I want to apply replace and upper functions to DeptID in database before saying
"DeptID LIKE @.DeptID".

for example the parameter is :"D&V"
DeptID in database is:"d & v" //there are spaces

if I say DeptID LIKE @.DeptID nothing is found because of character nonmatching
So I have to apply replace & upper functions to the column DeptID in database

but how?
can you help me please??You probably don't need the "Upper" function since *most* SQL Server functions are case insensitive by default.

As for the spaces, not sure what to tell you there. That's a one-off solution that you will have to code manually. For instance, what happens when the value is "D& V"? There are 2 spaces now, and you'd have to code a check for that too.

Also, if you are using LIKE, you need to have a % character. For example:
DeptID LIKE @.DeptID + '%'

Replace function: replacing an apostrophe with whatever

hey

i'm having a problem with a stored procedure, to cut a long story short i need to replace apostrophe's in my text, like so

SET @.prOtherValue = REPLACE(@.prOtherValue,''', '''')

this doesn't work tho

whats the work around

cheers!!!!

SET @.prOtherValue = REPLACE(@.prOtherValue,''', '')

|||

nar.. no good

this is the context i have it in

SET @.prOtherValue = REPLACE(@.prOtherValue,''','')

EXEC( 'UPDATE #_AllParticipantResponses SET '+@.qcTitle+' = '''+@.prOtherValue+''' WHERE participantId='+@.globalparticipantId+'')

the problem is in @.prOtherValue, sometimes i'll have the word isn't or whatever word, which contains an apostrophe

if you copy and paste the above into query analyzer, watch what hapens to the EXEC statement if you have an apostrophe where i have bolded

|||

SET @.prOtherValue=REPLACE(@.prOtherValue,'''','')

EXEC('UPDATE #_AllParticipantResponses SET '+ @.qcTitle+' = '''+ @.prOtherValue+''' WHERE participantId= '+convert(Varchar,@.globalparticipantId))

|||

hehe...

nar, you have 2 appostrophes for the second argument

in my database right, i have a column called OtherValue. in this column, i have words like isn't can't it's ( focus on the single appostrophe in each word)

when i try and pass words like isn't to my @.prOtherValue, the update statement fails because the @.prOtherValue value contains an appostrophe ( ' )

apostrophe's on t-sql have some sort of formatting value

when i try to execute the update statement, if i have an appostrophe in a word, it terminates

what i need to do isREPLACE(@.prOtherValue,''','whatever') but you can't have'''

cheers!!!

|||

Replace(prOtherValue,char(39),'|')

|||

Finally got a chance to try it out

Thanks for that

replace failed node without downtime? (sp1 question)

Is there a manual procedure whereby I could get a replacement node up to SP1
binaries without having to interrupt service by failing the cluster over
just to apply SP1 to the newly rebuilt node?
Hopefully I'm making sense - the node that went down was a passive node and
it's been rebuilt - but in order to get the binaries up to SP1 the only way
I know how is to fail the cluster to the new node just to get the SP1
binaries on it.
Thanks in advance!
Karl
Hi,
My understanding of your issue is: After adding a new node in SQL 2005
cluster instance, the SQL version on new node is RTM and old node is SP1
In such scenario, we have following two ways to apply SQL Sp1 for this SQL
2005 cluster.
1. Apply SP1 again on the active node (OLD node) and the new node
will be upgraded to SP3 as well.
2. Add registry Need_SP_Key on the passive node and install SQL
service pack 1on Passive node directly, In this way, the SP1 will only be
installed on Passive node.
Hope this helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
================================================== ====
Get Secure! - www.microsoft.com/security
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties,and confers no rights.
================================================== ====
--[vbcol=seagreen]
SP1[vbcol=seagreen]
and[vbcol=seagreen]
way[vbcol=seagreen]
|||Yes, you understand what I'm trying to do. I have a couple of questions,
please.
If I try #1 on the old (already SP1) node - will it stop the SQL Service or
will it leave the active node online while it patches the passive node
remotely?
For #2 when I search for Need_SP_Key I only get SQL 2000 information and the
2005 BOL has nothing I can find about it. Sounds like the best option
though.
-Karl
"Vincent Xu [MSFT]" <v-xuwen@.online.microsoft.com> wrote in message
news:1EObEyf4GHA.2336@.TK2MSFTNGXA01.phx.gbl...
Hi,
My understanding of your issue is: After adding a new node in SQL 2005
cluster instance, the SQL version on new node is RTM and old node is SP1
In such scenario, we have following two ways to apply SQL Sp1 for this SQL
2005 cluster.
1. Apply SP1 again on the active node (OLD node) and the new node
will be upgraded to SP3 as well.
2. Add registry Need_SP_Key on the passive node and install SQL
service pack 1on Passive node directly, In this way, the SP1 will only be
installed on Passive node.
Hope this helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
================================================== ====
Get Secure! - www.microsoft.com/security
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties,and confers no rights.
================================================== ====
--[vbcol=seagreen]
SP1[vbcol=seagreen]
and[vbcol=seagreen]
way[vbcol=seagreen]
|||Hi,
Found more information:
You can even install SP1 on node 2 directly:
- Verified that SP1 was successfully installed on Node1, we did that by
running
Select @.@.Version & physically checking the File Version property of
sqlservr.exe on
Node1 which is 2005.90.2047.0
- We made Node1 as a active node.
- Started installation on Node2.
Note: This time setup wont allow us to check the checkbox for instance in
"Feature
Selection" window, as Node1 (which is a active node) is already on SP1, so
we will
continue so we continued setup without it.
- Let it run for all products till setup got completed.
- Verified that SP1 was successfully installed on Node2, we did that by
running
Select @.@.Version & physically checking the File Version property of
sqlservr.exe on
Node2 which is 2005.90.2047.0
- Verified by failing over also.
Hope this helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
================================================== ====
Get Secure! - www.microsoft.com/security
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties,and confers no rights.
================================================== ====
--[vbcol=seagreen]
<1EObEyf4GHA.2336@.TK2MSFTNGXA01.phx.gbl>[vbcol=seagreen]
or[vbcol=seagreen]
the[vbcol=seagreen]

Repeater and Stored PRocedure

Q1 - I want to compare file names uploaded by users with the name of the file present in the database.
I have a repeated control that shows the data alongwith an array of <input type=file> that is created
dynamically when the data is displayed in the repeater control. (So there is an input tag within the
ItemTEmplate tag in the repeater)
Now I want to compare the names of the files uploaded by the user with the values as displayed in the
repeater. Is there a way I can do this? So I need to be able to get the data (for example Name) as
displayed in the repeater control into a variable like Dim strName As String. Something like:
Dim strName As String = (value from the repeater control)
Can I do this? How?

Q2 - I want to return a value from a stored procedure that will be stored in a variable in VB and will be
used for comparison. So, I want to return a 0 or 1 and then use .ReturnValue method to store this value.
The problem that I am facing is that I am using a cursor. And as soon as return is called in the SP it exits
the procedure and returns only one value (1 if found 0 otherwise) So it does not loop through the complete
cursor. What can I do to fix this?
CODE:

OPEN cur1
--print 'Cursor Open'
FETCH NEXT FROM cur1 INTO @.varFile, @.varFileEx, @.varFileSt

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.varFile = @.FileName AND @.varFileEx = @.FileExt)
BEGIN
set @.varReturn = 0
END
ELSE
BEGIN
set @.varReturn = 1
END
FETCH NEXT FROM cur_FileSystem INTO @.varFileName, @.varFileExt, @.varFileStatus
return (@.varReturn)
END
CLOSE cur_FileSystem
DEALLOCATE cur_FileSystem

Please help.
Thanks

Once you call Return, the stored procedure is exited. So your loop is never executed more than once. It is difficult to tell exactly what you are trying to do, but clearly what you are doing will not return a result of more than a single row.|||Any help with Q 1 ?

REPEATED RECORDS

Why this SQL procedure gives contiguous repeated records ( 3 or 4 times ) ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ALTER PROCEDUREGetProductsOnPromotInDep

(@.DepartmentIDINT)

AS

SELECT Product.ProductID, Title

FROMProductINNER JOIN

(ProductCategoryINNER JOIN

(CategoryINNER JOINDepartment ONDepartment.DepartmentID = Category.DepartmentID)

ONProductCategory.CategoryID = Category.CategoryID)

ONProduct.ProductID = ProductCategory.ProductID

WHERECategory.DepartmentID = @.DepartmentID

ANDProductCategory.CategoryID = Category.CategoryID

ANDProduct.ProductID = ProductCategory.ProductID

ANDProduct.OnPromotion = 1

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maybe you could slap thedistinct keyword in there.

With out seeing the data...it may be the multiple results are from multiple categories in a department, or multiple departments in a category.

|||Thank you, Steve.