Replacement for xp_cmdshell in SQL2k

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 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 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.
> >
>
>|||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...
> > 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.
> >
>
>|||> 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...
> 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...
>> > 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.
>> >
>>|||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:
> > 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...
> > 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...
> >> > 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.
> >> >
> >>
> >>
> >>
>
>|||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...
>
> 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:
>> > 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...
>> > 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...
>> >> > 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.
>> >> >
>> >>
>> >>
>> >>
>>

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 View with Join or SubQuery

My application uses a View stored in a database. I have queries that join
this view with other tables. I'd like to eliminate the view. For example, if
the view was defined by:
CREATE VIEW dbo.AcctBalance
AS
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
My VB code (using ADO) creates this T-SQL query:
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
All my attempts to replace the View have failed so far. Can someone provide
guidance?
Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
RichardRichard
Why do you want to eliminate the VIEW? Any reasons?
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN
(
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
) AS AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
> My application uses a View stored in a database. I have queries that join
> this view with other tables. I'd like to eliminate the view. For example,
if
> the view was defined by:
> CREATE VIEW dbo.AcctBalance
> AS
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> My VB code (using ADO) creates this T-SQL query:
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
> All my attempts to replace the View have failed so far. Can someone
provide
> guidance?
> Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
> --
> Richard
>|||The database does not belong to me, but to the customer. I'm trying to get
my code out of the customer's database. Also, if I need to revise the View,
I must code a utility to modify the View in the customer's database. Any
other change can be implemented by building a new dll. I understand that
it's partly a philosophical thing.
Your post indicates that I can join a table that is created in the
parenthesis. I like that idea and will try it. Thanks a lot.
Richard
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Richard
> Why do you want to eliminate the VIEW? Any reasons?
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN
> (
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> ) AS AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
>
> "Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
> message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
join
example,
> if
> provide
dbo.Sales.
>|||Hi,
Just to confirm, your code works perfectly for me. Thanks again.
Richard
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:O8NbIERFFHA.1392@.tk2msftngp13.phx.gbl...
> The database does not belong to me, but to the customer. I'm trying to get
> my code out of the customer's database. Also, if I need to revise the
View,
> I must code a utility to modify the View in the customer's database. Any
> other change can be implemented by building a new dll. I understand that
> it's partly a philosophical thing.
> Your post indicates that I can join a table that is created in the
> parenthesis. I like that idea and will try it. Thanks a lot.
> Richard
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> join
> example,
> dbo.Sales.
>

replace problem

Hi,
We hae a varchar column that has data values stored like:
'abc645abc56'
'adc015adc56'
and we need to extract only the numeric values from this
column for example for above 2 rows:
'64556'
'01556'
i am trying to use replace but is not allowing me to use
patters like select replace(column,'[A-Z]','')
How to use replace or any other function to solve this ?
Thanks
--HarvinderCheck each character and see if is numeric if not dump it.
Here is a function that I use. Run the script on the server in the right
database. To call it just do:
select StripNonNumeric(fieldname) from tablename
CREATE FUNCTION [dbo].[StripNonNumeric] (@.strIn varchar(30))
RETURNS FLOAT AS
BEGIN
declare @.intCounter int
declare @.strTmp varchar(300)
declare @.chrTmp varchar(1)
set @.intCounter = 1
set @.strTmp = ''
WHILE @.intCounter <Len(@.strIn)+1
BEGIN
set @.chrTmp = substring(@.strIn,@.intCounter,1)
if @.chrTmp NOT LIKE '%[^0123456789]%'
begin
set @.strTmp = @.strTmp + @.chrTmp
End
set @.intCounter =@.intCounter +1
if @.strTmp = ''
begin
set @.strTmp = null
end
END
return @.strTmp
END
"harvinder" <hs@.metratech.com> wrote in message
news:50d801c3417c$b99844a0$a401280a@.phx.gbl...
> Hi,
> We hae a varchar column that has data values stored like:
> 'abc645abc56'
> 'adc015adc56'
> and we need to extract only the numeric values from this
> column for example for above 2 rows:
> '64556'
> '01556'
> i am trying to use replace but is not allowing me to use
> patters like select replace(column,'[A-Z]','')
> How to use replace or any other function to solve this ?
> Thanks
> --Harvinder
>|||is yours better than mine?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OS1YIIYQDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Nothing built in using *just* replace, but you could create a function and
> call that inline:
>
> CREATE FUNCTION dbo.makeNumeric
> (
> @.f VARCHAR(32)
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.p TINYINT
> SET @.p = PATINDEX('%[^0-9]%', @.f)
> WHILE @.p > 0
> BEGIN
> SET @.f = STUFF(@.f, @.p, 1, '')
> SET @.p = PATINDEX('%[^0-9]%', @.f)
> END
> RETURN @.f
> END
> GO
> SELECT dbo.makeNumeric('abc645abc56')
> GO
> DROP FUNCTION dbo.makeNumeric
> GO
>
> "harvinder" <hs@.metratech.com> wrote in message
> news:50d801c3417c$b99844a0$a401280a@.phx.gbl...
> > Hi,
> >
> > We hae a varchar column that has data values stored like:
> > 'abc645abc56'
> > 'adc015adc56'
> >
> > and we need to extract only the numeric values from this
> > column for example for above 2 rows:
> > '64556'
> > '01556'
> >
> > i am trying to use replace but is not allowing me to use
> > patters like select replace(column,'[A-Z]','')
> > How to use replace or any other function to solve this ?
> >
> > Thanks
> > --Harvinder
> >
>|||What does "better" mean?
I have no idea. I really haven't looked at yours.
If you are slyly asking why I posted a function when you already had, I
didn't see your post yet until I refreshed my list, *after* I had posted
mine.
"Tammy B." <Tb@.stinkylips.com> wrote in message
news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> is yours better than mine?|||No i wasn't asking anything slyly, I was asking you to look at mine.
If yours is better then I will use yours.
You are the MVP.
get some rest
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> What does "better" mean?
> I have no idea. I really haven't looked at yours.
> If you are slyly asking why I posted a function when you already had, I
> didn't see your post yet until I refreshed my list, *after* I had posted
> mine.
>
>
> "Tammy B." <Tb@.stinkylips.com> wrote in message
> news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > is yours better than mine?
>|||better means it works better. You should get some rest and look it up.
The isnumeric part can be tricky because $ can be numeric with isnumeric and
I went through several versions, but I don't have an MVP that I like to tack
on to my posts.
I thought you might have a better version.
Gee It never occured to me that you might have posted something at the same
time as me. This is my first newsgroup. This is my first moment on earth.
Thanks for pointing stuff out to me.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> What does "better" mean?
> I have no idea. I really haven't looked at yours.
> If you are slyly asking why I posted a function when you already had, I
> didn't see your post yet until I refreshed my list, *after* I had posted
> mine.
>
>
> "Tammy B." <Tb@.stinkylips.com> wrote in message
> news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > is yours better than mine?
>|||You are an idiot
"Tammy B." <Tb@.stinkylips.com> wrote in message
news:uDHwHoZQDHA.1552@.TK2MSFTNGP10.phx.gbl...
> better means it works better. You should get some rest and look it up.
> The isnumeric part can be tricky because $ can be numeric with isnumeric
and
> I went through several versions, but I don't have an MVP that I like to
tack
> on to my posts.
> I thought you might have a better version.
> Gee It never occured to me that you might have posted something at the
same
> time as me. This is my first newsgroup. This is my first moment on
earth.
> Thanks for pointing stuff out to me.
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > What does "better" mean?
> >
> > I have no idea. I really haven't looked at yours.
> >
> > If you are slyly asking why I posted a function when you already had, I
> > didn't see your post yet until I refreshed my list, *after* I had posted
> > mine.
> >
> >
> >
> >
> > "Tammy B." <Tb@.stinkylips.com> wrote in message
> > news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > > is yours better than mine?
> >
> >
>|||How can you prove that you don't know what "better" means by giving me
definitions of "better".
what an idiot.
I explained the context in which I wanted to find code that may be better
than mine. If it is better in other ways, so much the better you idiot.
I said that the isnumeric part can be tricky because $ can be numeric with
isnumeric.
If the code is better in this respect I would use it in place of mine.
There has been much debate about this issue - none of which you are aware
of; the discussion was far boader than what you covered in your MVP
workbook.
Yes, why don't you get back to me with how your code stacks up according to
each of the definitions that you have provided.
I'll be waiting up.
You should not represent yourself as being valuable to Microsoft; when you
are just poo-pooing in the pool.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23OVGFxZQDHA.2424@.tk2msftngp13.phx.gbl...
> > better means it works better.
> I still don't understand what "works better" means. Less code? Less
> complicated code? Faster? More reliable? Covers more borderline cases?
> Meets this specific poster's needs?
> > but I don't have an MVP that I like to tack
> > on to my posts.
> It's not something I tack on to my posts. It's something I earned. Look
it
> up. http://mvp.support.microsoft.com/
>|||> what an idiot.
Welcome to my killfile. I don't know what I did to so greatly offend you,
but if all you can do is call me names, I have absolutely no interest in
carrying on a conversation with you. Have a great weekend.

Replace Function? SQL2K/EM

Hi,
I need to change some table and field names - is there a way to update
all the occurances in Views and Stored procedures?Update the source code for the views and stored procedures and then redeploy
them. You can use the rows from sysdepends to determine which views and
stored procs are affected by name change of a table.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to change some table and field names - is there a way to update
> all the occurances in Views and Stored procedures?
>|||Do they have to be updated manually?
Visual Studio has tools to do this automatically, does EM have nothing
similar for its source code ?
Tim Dot NoSpam wrote:
> Update the source code for the views and stored procedures and then redepl
oy
> them. You can use the rows from sysdepends to determine which views and
> stored procs are affected by name change of a table.
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...|||> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
EM/SSMS will not automatically rename objects because there is nothing on
the server side that will track dependencies. However, this one of the many
new features included in the upcoming Visual Studio 2005 Team Edition for
Database Professionals.
See http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150110493.525079.29150@.i40g2000cwc.googlegroups.com...
> Do they have to be updated manually?
> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
> Tim Dot NoSpam wrote:
>

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 a string across multiple stored procedures

actually 2 questions.
a. Need to replace the string "server name" available on multiple stored
procedures p1,p2...p19
with ""?
can I do this with an update to system tables like syscomments?
b. Can I create a linked server to itself?> can I do this with an update to system tables like syscomments?
NO! Updates to system tables are not supported and are strongly
discouraged.
Script out the stored procedures as ALTER or DROP/CREATE (you can do this
using Enterprise Manager, Query Analyzer, or a host of other tools). Do the
replace operation in the output file, then apply that script to the server.
Have you heard of source control?|||Not to mention that changing the text in syscomments wouldn't actually
change the procedure, right?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OTgoNCFlFHA.3568@.TK2MSFTNGP10.phx.gbl...
> NO! Updates to system tables are not supported and are strongly
> discouraged.
> Script out the stored procedures as ALTER or DROP/CREATE (you can do this
> using Enterprise Manager, Query Analyzer, or a host of other tools). Do
> the replace operation in the output file, then apply that script to the
> server.
> Have you heard of source control?
>|||> Not to mention that changing the text in syscomments wouldn't actually
> change the procedure, right?
That's correct, but I was nipping any other potential "what if I update
this, or what if I update that..." before they come up. :-)|||Yeah, no doubt :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eCverKFlFHA.2852@.TK2MSFTNGP15.phx.gbl...
> That's correct, but I was nipping any other potential "what if I update
> this, or what if I update that..." before they come up. :-)
>

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 ?

Re-order table after many inserts

I have a stored proc that is long and performs many separate intserts into my table. Is there a way that I can reorder the records themselves physically in the table after the inserts are done?

Hello,

http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspx

I think this will help you!

Happy coding...

|||

Hi,

I don′t know if the article below is applying to your case, but this can be only done if you have a clustered key based on the tables, which is defining the physical o′rder of a key, and therefore the physical order of the table (based on x columns). But if you only want to sort the tables via a specific column, then you have to look a bit deeper in the specifications of a table. Its a unordered set of data, unless you don′t have a clustered sorted index on a it, you will only get the orderd data with querying the data with the ORDER clause.

HTH, Jens Suessmeyer.

Rendering to Excel: numbers stored as text

Some of my reports are rendering to Excel with numbers stored as text (the
little green triangle in the upper corner of each cell alerts the viewer of
the problem).
While this seems innocuous, it means that a user can't do typical Excel
operations, like highlighting cells and creating a graph, or highlighing and
viewing the totals on the status bar. Although there's a number in the cell,
it acts like text.
Not all of my reports are doing this, but I'm having major trouble figuring
out the differences between those which are, and those which are not. Also,
I can't find any documentation about how the rendering makes this
determination.
Thank you!It appears I've finally found the solution. Since I knew I wasn't *really*
aggregating any values, I was using First() as my aggregation formula. When
I switched to Sum() as my aggregation, the result was forced to be numeric,
even through the Excel rendering.
How was anyone supposed to figure this out without documentation!? I mean,
if the fundamental data is numeric, then why should First( ) change it to
text? That seems SO arbitrary...!
I hope this helps someone to NOT pull their hair out like I did.
"mr_bert" wrote:
> Some of my reports are rendering to Excel with numbers stored as text (the
> little green triangle in the upper corner of each cell alerts the viewer of
> the problem).
> While this seems innocuous, it means that a user can't do typical Excel
> operations, like highlighting cells and creating a graph, or highlighing and
> viewing the totals on the status bar. Although there's a number in the cell,
> it acts like text.
> Not all of my reports are doing this, but I'm having major trouble figuring
> out the differences between those which are, and those which are not. Also,
> I can't find any documentation about how the rendering makes this
> determination.
> Thank you!|||Thanks
This just saved me a day of pulling my hair out

Rendering RTF in SQL Server 2005 Reporting Services

We are using SQL Server 2005 Reporting Services and we need to render RTF
text, which is stored in SQL Server, within reports. Does anyone know of a
vendor that has an RTF rendering extension?
p.s. I am suprised this was never in Reporting Services.Hi Chris,
Thanks for posting.
As for the RTF rendering extension, it is still not included in the SQL
Server Reporting Service 2005 and I haven't found any existing 3rd party
rendering extension so far. However, since there does exists many 3rd
party PDF/RTF , HTML/RTF converting tools, I think this should one
possible approach for customizing a own RTF report rendering extension or
just convert the report externally.
BTW, since I also think this is a good an reasonable feature, you may
consider submit this suggestion on the MSDN product feedback center also:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)