Altering stored procedure which is part of sys schema

I am trying to alter sys.sp_helpmergeconflictrows which is part fof sys schema and is in System Stored Procedures.

Reason why I need this is because Conglict Viewer in merge replication fails to show data from one of my tables, because aforementioned sp fails during execution. It fails because sql query is declared as nvarchar(4000) and it needs to be longer. So, I tired to change it to nvarchar(max), but I cannot.

I tried few things in order to gain permission to alter that sp, but I fial always.

Can it be done at all, and if can, how?

Thanks

If you have a bug in the conflict viewer (or more specifically this stored proc), you should open a ticket with MS Tech Support to have this resolved.

Bryan

sql

Altered Date for Stored Procedures

Is there a system table that has the date when the strored procedure was
altered?
thanksOnly for SQL Server 2005 (sys.procedures.modify_date)
Earlier versions do not track this information (though there are columns
that *look* like they might, but are never updated).
A
"Dev" <Dev@.discussions.microsoft.com> wrote in message
news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> Is there a system table that has the date when the strored procedure was
> altered?
> thanks|||Aaron,
thanks for information.
you are right for the earlier versions, there is column LAST_ALTERED in
INFORMATION_SCHEMA.ROUTINES but is same as the created date and does not
change when the procedure is altered.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:

> Only for SQL Server 2005 (sys.procedures.modify_date)
> Earlier versions do not track this information (though there are columns
> that *look* like they might, but are never updated).
> A
>
>
> "Dev" <Dev@.discussions.microsoft.com> wrote in message
> news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
>
>sql

Altered Date for Stored Procedures

Is there a system table that has the date when the strored procedure was
altered?
thanksOnly for SQL Server 2005 (sys.procedures.modify_date)
Earlier versions do not track this information (though there are columns
that *look* like they might, but are never updated).
A
"Dev" <Dev@.discussions.microsoft.com> wrote in message
news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> Is there a system table that has the date when the strored procedure was
> altered?
> thanks|||Aaron,
thanks for information.
you are right for the earlier versions, there is column LAST_ALTERED in
INFORMATION_SCHEMA.ROUTINES but is same as the created date and does not
change when the procedure is altered.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Only for SQL Server 2005 (sys.procedures.modify_date)
> Earlier versions do not track this information (though there are columns
> that *look* like they might, but are never updated).
> A
>
>
> "Dev" <Dev@.discussions.microsoft.com> wrote in message
> news:2419DE45-9187-41A0-B677-397259B779A3@.microsoft.com...
> > Is there a system table that has the date when the strored procedure was
> > altered?
> > thanks
>
>

ALTER TABLE problem

Hiya all,

Im doing a system tool application. The app has the functionality to edit and change language strings that other applicaction uses.

The table look as following:

STRING_ID English Swedish
----------
100 Cancel Avbryt
101 Apply Verst'a'll

STRING_ID : Int, NOT NULL, Primary key
English : NVARCHAR(512), NOT NULL
Swedish : NVARCHAR(512), NOT NULL

In my tool you're able to add new strings.

Now I want to be able to add languages by adding new columns to the table.

Example:

STRING_ID English Swedish Arabic
------------
100 Cancel Avbryt <Cancel in arabic>
101 Apply Verst'a'll <Apply in arabic>

I've made a Stored Procedure looking like following:

CREATE PROC My_sp_AddNewLanguage
@.NewLanguageName nvarchar(512), @.RetrievalMsg nvarchar(255) OUTPUT
AS
-- Find out how many rows that should be affected when altering the table
DECLARE @.NrOfRows integer
SELECT * FROM String_Resource
SET @.NrOfRows = @.@.ROWCOUNT

ALTER TABLE String_Resource
ADD @.NewLanguageName NVARCHAR(512) NOT NULL
DEFAULT ('')
IF @.@.ROWCOUNT <> @.NrOfRows
BEGIN
SET @.RetrievalMsg = 'Unable to Add New Language'
RETURN 8301 -- 8301 is something I've defined in my code
END
SET @.RetrievalMsg = 'Your new Language has now been added'
RETURN 0
GO

But I cant seem to do this because of the @.NewLanguageName in the following row:

ALTER TABLE String_Resource
ADD @.NewLanguageName NVARCHAR(512) NOT NULL
DEFAULT ('')

So my question is: How can you add a column to the Table using a variable @.Variable that contains the name of the new column?

OR

Can anybody tell me how I can write DEFAULT('') into a NVARCHAR variable since in Store Procedure Strings are using the '-sign and the DEFAULT ('') expression has those signs in it.

I've tried doing @.SQLQuery = N'ALTER TABLE String_Resource ADD ' + @.NewLanguageName + ' NVARCHAR(512) NOT NULL DEFAULT('')'

But it doesnt work since the DEFAULT('') expression screws up the string.

Thanks for your time,
FarekYou've got a really good idea, but I think you are going about it all wrong!

Take a look at the master.dbo.sysmessages table. It is designed to do exactly what you are trying to do. The secret is to have multiple rows with the same message id, but only one row per language. By adding rows to the language table, you can then add new rows to the message table for that language, and you are on your way!

The syntax should go something like:CREATE TABLE tLanguage (
languageId INT IDENTITY
CONSTRAINT XPKtLanguage
PRIMARY KEY (languageId)
, name NVARCHAR(25) NOT NULL
)
GO

CREATE TABLE tMessage (
languageId INT NOT NULL
CONSTRAINT XFK01tMessage
FOREIGN KEY (languageId)
REFERENCES tLanguage (languageId)
, messageId INT NOT NULL
CONSTRAINT XPKtMessage
PRIMARY KEY (languageId, messageId)
, message NVARCHAR(50) NOT NULL
)
GO-PatPsql

ALTER TABLE CHANGE question ...

I need to be able to change a table column name from within my C# code. The system in this part of the application is intended to be highly configurable and column names on the table being operated upon can change. The code adds square brackets to the column name (because the user might set up a column name with one or more spaces) but I'm not sure if I'm doing it right because I'm getting an error.

The code that builds the SQL command is:

SqlCommand =new SqlCommand("ALTER TABLE wto_facilities CHANGE [" +oldAccomType +"][" + accomType.Text +"] varchar(20)", conn);

 On the first test run, the code produces the following: ALTER TABLE wto_facilities CHANGE [Hotel] [Hotels] varchar(20)

However, it's giving me the following error: Incorrect syntax near 'Hotels'

It looks fine to me, but it's obviously not.

To change a column name you need to use sp_Rename

EXEC sp_rename 'table.column', 'newcolumnname', 'column'

allow direct updates to systemtables

Hi,
I cannot find "allow direct updates to system tables" in security tab of SQL
Server 2005 setting, while BOL addresses that!
Where is it?!
Thanks,
Leila
Leila wrote:
> Hi,
> I cannot find "allow direct updates to system tables" in security tab of SQL
> Server 2005 setting, while BOL addresses that!
> Where is it?!
> Thanks,
> Leila
You cannot do it. Updating system tables was never a good idea anyway.
What is it you are trying to achieve?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||> I cannot find "allow direct updates to system tables" in security tab of
> SQL Server 2005 setting, while BOL addresses that!
Can you show the URL(s)/article(s) where BOL says this option exists?
|||ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
> Can you show the URL(s)/article(s) where BOL says this option exists?
>
|||Perhaps you have an older version of Books Online? I checked three
computers and could not find that statement on the "Server Properties
(Security Page)" topic. Perhaps it was an omission on first release but has
since been corrected? You may want to ensure you have the most recent
refresh (2006-07-21):
http://www.microsoft.com/technet/pro...ads/books.mspx
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>
|||> Perhaps you have an older version of Books Online?
The reference was in the RTM but removed in the BOL refresh
(http://www.microsoft.com/downloads/d...displaylang=en).
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OIvIAZK8GHA.4776@.TK2MSFTNGP02.phx.gbl...
> Perhaps you have an older version of Books Online? I checked three
> computers and could not find that statement on the "Server Properties
> (Security Page)" topic. Perhaps it was an omission on first release but
> has since been corrected? You may want to ensure you have the most recent
> refresh (2006-07-21):
> http://www.microsoft.com/technet/pro...ads/books.mspx
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
>

allow direct updates to systemtables

Hi,
I cannot find "allow direct updates to system tables" in security tab of SQL
Server 2005 setting, while BOL addresses that!
Where is it?!
Thanks,
LeilaLeila wrote:
> Hi,
> I cannot find "allow direct updates to system tables" in security tab of S
QL
> Server 2005 setting, while BOL addresses that!
> Where is it?!
> Thanks,
> Leila
You cannot do it. Updating system tables was never a good idea anyway.
What is it you are trying to achieve?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I cannot find "allow direct updates to system tables" in security tab of
> SQL Server 2005 setting, while BOL addresses that!
Can you show the URL(s)/article(s) where BOL says this option exists?|||ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-
234f1ebfe622.htm
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
> Can you show the URL(s)/article(s) where BOL says this option exists?
>|||Perhaps you have an older version of Books Online? I checked three
computers and could not find that statement on the "Server Properties
(Security Page)" topic. Perhaps it was an omission on first release but has
since been corrected? You may want to ensure you have the most recent
refresh (2006-07-21):
http://www.microsoft.com/technet/pr...oads/books.mspx
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf2
6-234f1ebfe622.htm
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>|||> Perhaps you have an older version of Books Online?
The reference was in the RTM but removed in the BOL refresh
(http://www.microsoft.com/downloads/...&displaylang=en).
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OIvIAZK8GHA.4776@.TK2MSFTNGP02.phx.gbl...
> Perhaps you have an older version of Books Online? I checked three
> computers and could not find that statement on the "Server Properties
> (Security Page)" topic. Perhaps it was an omission on first release but
> has since been corrected? You may want to ensure you have the most recent
> refresh (2006-07-21):
> http://www.microsoft.com/technet/pr...oads/books.mspx
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
>

allow direct updates to systemtables

Hi,
I cannot find "allow direct updates to system tables" in security tab of SQL
Server 2005 setting, while BOL addresses that!
Where is it?!
Thanks,
LeilaLeila wrote:
> Hi,
> I cannot find "allow direct updates to system tables" in security tab of SQL
> Server 2005 setting, while BOL addresses that!
> Where is it?!
> Thanks,
> Leila
You cannot do it. Updating system tables was never a good idea anyway.
What is it you are trying to achieve?
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I cannot find "allow direct updates to system tables" in security tab of
> SQL Server 2005 setting, while BOL addresses that!
Can you show the URL(s)/article(s) where BOL says this option exists?|||ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>> I cannot find "allow direct updates to system tables" in security tab of
>> SQL Server 2005 setting, while BOL addresses that!
> Can you show the URL(s)/article(s) where BOL says this option exists?
>|||Perhaps you have an older version of Books Online? I checked three
computers and could not find that statement on the "Server Properties
(Security Page)" topic. Perhaps it was an omission on first release but has
since been corrected? You may want to ensure you have the most recent
refresh (2006-07-21):
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>> I cannot find "allow direct updates to system tables" in security tab of
>> SQL Server 2005 setting, while BOL addresses that!
>> Can you show the URL(s)/article(s) where BOL says this option exists?
>|||> Perhaps you have an older version of Books Online?
The reference was in the RTM but removed in the BOL refresh
(http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OIvIAZK8GHA.4776@.TK2MSFTNGP02.phx.gbl...
> Perhaps you have an older version of Books Online? I checked three
> computers and could not find that statement on the "Server Properties
> (Security Page)" topic. Perhaps it was an omission on first release but
> has since been corrected? You may want to ensure you have the most recent
> refresh (2006-07-21):
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23bs3LUK8GHA.3396@.TK2MSFTNGP04.phx.gbl...
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/b8a131c7-e7bd-4203-bf26-234f1ebfe622.htm
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:%23XY46PK8GHA.3740@.TK2MSFTNGP05.phx.gbl...
>> I cannot find "allow direct updates to system tables" in security tab
>> of SQL Server 2005 setting, while BOL addresses that!
>> Can you show the URL(s)/article(s) where BOL says this option exists?
>>
>