renamed table [need to quickly update 200 SPs]

Hi
I have had to rename one of my tables (sp_Rename []) I now need to update
all my stored procedures, I want to some how use syscomments (maybe) and
loop through my SPs using REPLACE command to change the references to the
old table name
Any help / advise appreciated
LukeHi,
One solution is, you can generate a script with all the SPs available.
Open some text editor and use Replace All option giving your old and new
File Name.
Run the script again in Query Analyser
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Luke Ward" wrote:

> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>
>|||Don't fiddle with system tables ever! I would hope you have some
sort of code mgmt (ie VSS) in place where your master copies of
all procs are kept. That's where your changes should be made, tested
against a copy of your production data, and then applied to the
production database when you're satisfied with the results.
I think your best bet is to manually change the table name within
the procs. If you know a scripting language (like Perl), you could
write a script to blow through the procs and make the change and then
compile them back into the database.. That's what I would do.
"Luke Ward" <lukeward@.campbelluk.com> wrote in message
news:e6GC%23xsWFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have had to rename one of my tables (sp_Rename []) I now need to update
> all my stored procedures, I want to some how use syscomments (maybe) and
> loop through my SPs using REPLACE command to change the references to the
> old table name
> Any help / advise appreciated
> Luke
>

Removing SQLServer 2005 Express Registry Keys

MS update cannot install a Service Pack to SQL Server 2005 Express because a
component of an earlier version (a CTP, the Reporting Services component)
cannot be uninstalled. Can anyone tell me which set of registry keys I
should delete after removing SQL Server 2005 in order to reinstall on a
clean sustem?
Many thanks.Hi Andrew
"Andrew Chalk" wrote:

> MS update cannot install a Service Pack to SQL Server 2005 Express because
a
> component of an earlier version (a CTP, the Reporting Services component)
> cannot be uninstalled. Can anyone tell me which set of registry keys I
> should delete after removing SQL Server 2005 in order to reinstall on a
> clean sustem?
> Many thanks.
Do these help?
http://msdn2.microsoft.com/en-us/library/ms143505.aspx
http://support.microsoft.com/kb/909967/
http://blogs.msdn.com/astebner/arch.../13/465401.aspx
John|||Thanks.This http://blogs.msdn.com/astebner/arch.../13/465401.aspx
had the brute force method that did it.
- A
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EC308ED0-5BEE-486D-9D66-573845FC5360@.microsoft.com...
> Hi Andrew
> "Andrew Chalk" wrote:
>
> Do these help?
> http://msdn2.microsoft.com/en-us/library/ms143505.aspx
> http://support.microsoft.com/kb/909967/
> http://blogs.msdn.com/astebner/arch.../13/465401.aspx
> John
>

Removing SQLServer 2005 Express Registry Keys

MS update cannot install a Service Pack to SQL Server 2005 Express because a
component of an earlier version (a CTP, the Reporting Services component)
cannot be uninstalled. Can anyone tell me which set of registry keys I
should delete after removing SQL Server 2005 in order to reinstall on a
clean sustem?
Many thanks.
Hi Andrew
"Andrew Chalk" wrote:

> MS update cannot install a Service Pack to SQL Server 2005 Express because a
> component of an earlier version (a CTP, the Reporting Services component)
> cannot be uninstalled. Can anyone tell me which set of registry keys I
> should delete after removing SQL Server 2005 in order to reinstall on a
> clean sustem?
> Many thanks.
Do these help?
http://msdn2.microsoft.com/en-us/library/ms143505.aspx
http://support.microsoft.com/kb/909967/
http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
John
|||Thanks.This http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
had the brute force method that did it.
- A
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EC308ED0-5BEE-486D-9D66-573845FC5360@.microsoft.com...
> Hi Andrew
> "Andrew Chalk" wrote:
>
> Do these help?
> http://msdn2.microsoft.com/en-us/library/ms143505.aspx
> http://support.microsoft.com/kb/909967/
> http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
> John
>

Removing SQLServer 2005 Express Registry Keys

MS update cannot install a Service Pack to SQL Server 2005 Express because a
component of an earlier version (a CTP, the Reporting Services component)
cannot be uninstalled. Can anyone tell me which set of registry keys I
should delete after removing SQL Server 2005 in order to reinstall on a
clean sustem?
Many thanks.
Hi Andrew
"Andrew Chalk" wrote:

> MS update cannot install a Service Pack to SQL Server 2005 Express because a
> component of an earlier version (a CTP, the Reporting Services component)
> cannot be uninstalled. Can anyone tell me which set of registry keys I
> should delete after removing SQL Server 2005 in order to reinstall on a
> clean sustem?
> Many thanks.
Do these help?
http://msdn2.microsoft.com/en-us/library/ms143505.aspx
http://support.microsoft.com/kb/909967/
http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
John
|||Thanks.This http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
had the brute force method that did it.
- A
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EC308ED0-5BEE-486D-9D66-573845FC5360@.microsoft.com...
> Hi Andrew
> "Andrew Chalk" wrote:
>
> Do these help?
> http://msdn2.microsoft.com/en-us/library/ms143505.aspx
> http://support.microsoft.com/kb/909967/
> http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
> John
>

Removing SQLServer 2005 Express Registry Keys

MS update cannot install a Service Pack to SQL Server 2005 Express because a
component of an earlier version (a CTP, the Reporting Services component)
cannot be uninstalled. Can anyone tell me which set of registry keys I
should delete after removing SQL Server 2005 in order to reinstall on a
clean sustem?
Many thanks.Hi Andrew
"Andrew Chalk" wrote:
> MS update cannot install a Service Pack to SQL Server 2005 Express because a
> component of an earlier version (a CTP, the Reporting Services component)
> cannot be uninstalled. Can anyone tell me which set of registry keys I
> should delete after removing SQL Server 2005 in order to reinstall on a
> clean sustem?
> Many thanks.
Do these help?
http://msdn2.microsoft.com/en-us/library/ms143505.aspx
http://support.microsoft.com/kb/909967/
http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
John|||Thanks.This http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
had the brute force method that did it.
- A
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:EC308ED0-5BEE-486D-9D66-573845FC5360@.microsoft.com...
> Hi Andrew
> "Andrew Chalk" wrote:
>> MS update cannot install a Service Pack to SQL Server 2005 Express
>> because a
>> component of an earlier version (a CTP, the Reporting Services component)
>> cannot be uninstalled. Can anyone tell me which set of registry keys I
>> should delete after removing SQL Server 2005 in order to reinstall on a
>> clean sustem?
>> Many thanks.
> Do these help?
> http://msdn2.microsoft.com/en-us/library/ms143505.aspx
> http://support.microsoft.com/kb/909967/
> http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx
> John
>

removing partial string from column

how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the frontTry this to get the lastname:
lastname = left(column1, patindex('%,%', column1) - 1)|||The error message was :-

update Agency
set lastname = left(lastname, patindex('%,%', lastname) - 1)

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

Dario D'Alessandro, Dario
Stan Dickson, Stan
Ray John, Ray
Juan Sanchez, Juan

I trying to remove the Dario|||Gotta make sure there is at least one comma, or it errors out. Try this:

set lastname = left(lastname, charindex(',', lastname + ',') - 1)|||Thanks that did actually work.. not sure how that charindex worked. i was using to REPLACE statements to make it work. It was more efficient using your method.|||If you need a method of parsing name strings into component parts, I have a UDF that will do that too.

Removing decimals from a nvarchar field

I am trying to remove decimals...for example I have values like this:
01.234.678 and I want to update it to 01234678 or 12345678.
Thanks
SELECT REPLACE(column, '.', '') FROM table;
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:B084D109-B3E8-4D0D-B0BC-CC7D836ACA8D@.microsoft.com...
>I am trying to remove decimals...for example I have values like this:
> 01.234.678 and I want to update it to 01234678 or 12345678.
> Thanks

Removing decimals from a nvarchar field

I am trying to remove decimals...for example I have values like this:
01.234.678 and I want to update it to 01234678 or 12345678.
ThanksSELECT REPLACE(column, '.', '') FROM table;
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:B084D109-B3E8-4D0D-B0BC-CC7D836ACA8D@.microsoft.com...
>I am trying to remove decimals...for example I have values like this:
> 01.234.678 and I want to update it to 01234678 or 12345678.
> Thanks