Issues with deleting XPs

We were recently audited and asked to get rid of the following XPs:
xp_cmdshell
xp_regaddmultistring
xp_regdeletevalue
xp_regenumvalues
xp_regread
xp_regremovemultistring
xp_regwrite
But we found a possible problem with xp_regread and received the following
error messages:
Server: Msg 2812, Level 16, State 62, Line 52
Could not find stored procedure 'master.dbo.xp_regread '.
Server: Msg 3165, Level 16, State 1, Line 2
Could not adjust the replication state of database 'DBName'. The database
was successfully restored,
Now the database did restore and seems to be fine, and as we don't use
replication right now, I don't see the harm anyway. However, I'm curious to
know if deleting these XPs will have other affects other than this one, and
whether or not any of you guys would know of good reasons not to do what the
auditors have asked, namely delete the above mentioned XPs.
Thanks.
Michael MacGregor
Database ArchitectHi Michael,
As to your problem with xp_regread, have you tried to run a sp_help on
it? If it produces no entry when passed to sp_help (e.g. exec sp_help
@.objectname = 'xp_regread') then there might be some trouble.
Will you be using replication at any point in the future (sorry, I know
it's sometimes hard to say whether you will or will not be doing
something in the future)? These extended stored procedures are mainly
called by the system stored procs that are used for replication so if
you remove them, replication will be hard to do.
You can view what stored procs have dependencies on the ones you want
to delete by using Enterprise Manager/Management Studio. I would
consider having a look through what procs are dependant on those you
want to delete and see if you can live without them or not.
Also, I trust that if/when you delete these extended stored procs you
have/will delete the stored procedures that are dependant on them?
All that being said, I have found xp_cmdshell a useful thing in the
past. It's not something I use regularly but it can be handy for things
like moving/deleting files, calling batch scripts and bcp from T-SQL
etc. Like I said, I don't use it often but I'm glad it's there when I
need it.
Also, most of those stored procs aren't accessible by non-admins unless
the permissions have been explicitly granted to them. I believe that
the only exception is xp_regread but some would consider being able to
read the registry quite harmless depending on what is stored in there,
of course.sql

Issues with deleting XPs

We were recently audited and asked to get rid of the following XPs:
xp_cmdshell
xp_regaddmultistring
xp_regdeletevalue
xp_regenumvalues
xp_regread
xp_regremovemultistring
xp_regwrite
But we found a possible problem with xp_regread and received the following
error messages:
Server: Msg 2812, Level 16, State 62, Line 52
Could not find stored procedure 'master.dbo.xp_regread '.
Server: Msg 3165, Level 16, State 1, Line 2
Could not adjust the replication state of database 'DBName'. The database
was successfully restored,
Now the database did restore and seems to be fine, and as we don't use
replication right now, I don't see the harm anyway. However, I'm curious to
know if deleting these XPs will have other affects other than this one, and
whether or not any of you guys would know of good reasons not to do what the
auditors have asked, namely delete the above mentioned XPs.
Thanks.
Michael MacGregor
Database ArchitectHi Michael,
As to your problem with xp_regread, have you tried to run a sp_help on
it? If it produces no entry when passed to sp_help (e.g. exec sp_help
@.objectname = 'xp_regread') then there might be some trouble.
Will you be using replication at any point in the future (sorry, I know
it's sometimes hard to say whether you will or will not be doing
something in the future)? These extended stored procedures are mainly
called by the system stored procs that are used for replication so if
you remove them, replication will be hard to do.
You can view what stored procs have dependencies on the ones you want
to delete by using Enterprise Manager/Management Studio. I would
consider having a look through what procs are dependant on those you
want to delete and see if you can live without them or not.
Also, I trust that if/when you delete these extended stored procs you
have/will delete the stored procedures that are dependant on them?
All that being said, I have found xp_cmdshell a useful thing in the
past. It's not something I use regularly but it can be handy for things
like moving/deleting files, calling batch scripts and bcp from T-SQL
etc. Like I said, I don't use it often but I'm glad it's there when I
need it.
Also, most of those stored procs aren't accessible by non-admins unless
the permissions have been explicitly granted to them. I believe that
the only exception is xp_regread but some would consider being able to
read the registry quite harmless depending on what is stored in there,
of course.

Issue inserting null value into a formview/gridview control

Hi,

My formview or gridview control stops updating or deleting a record once the record has a null value.

I have table tblTest with the following

pkID int NOT NULL **IDENTITY COLUMN**
string1 varchar(30)
string2 varchar(30)

I then create a SqlDataSource with the statement:

Select * From [tblTest]

I have the insert, update and delete statements generated, and choose optimistic concurrency. I add a couple records of dummy data.

I then drag a Formview control onto the page, and bind it to the SqlDataSource I just created. I then fire it up in my browser, and I can then update, insert and delete records. However, as soon as I update a record with a null value, I can no longer update or delete that record.

So, if I had a record in my FormView like:

string1: foo
string2: bar

I can update and delete normally. And when I update to:

string1: foo
string2:

the database correctly inserts a null value into string2. However, once that null is in the record, I can't change anything about the record. If I try to delete the record, the FormView will then display the previous record, but I can still page to the record that should have been deleted, and it still exists in the db. If I try to update the record, the edits I make will not keep and the process will fail silently.

What am I doing wrong? Should i be binding to a different object?

Regards,

Chris

Do you set your DataKeyNames for the formview? ( I thought you did). Anyway, here is a working copy and you may find your isseue by yourself.

<asp:FormView ID="fv1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="id"
AllowPaging="true" >

<EditItemTemplate>

col1:

<asp:TextBox ID="col1TextBox" runat="server" Text='<%# Bind("col1") %>'></asp:TextBox><br />
col2:

<asp:TextBox ID="col2TextBox" runat="server" Text='<%# Bind("col2") %>'></asp:TextBox><br />

<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"

Text="Update"
</asp:LinkButton
<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel"
</asp:LinkButton
</EditItemTemplate>
<InsertItemTemplate
col1:

<asp:TextBox ID="col1TextBox" runat="server" Text='<%# Bind("col1") %>'></asp:TextBox><br />
col2:

<asp:TextBox ID="col2TextBox" runat="server" Text='<%# Bind("col2") %>'></asp:TextBox><br /

<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"

Text="Insert"
</asp:LinkButton
<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel"
</asp:LinkButton
</InsertItemTemplate>
<ItemTemplate
id:

<asp:Label ID="idLabel" runat="server" Text='<%# Eval("id") %>'></asp:Label><br /
col1:

<asp:Label ID="col1_Label" runat="server" Text='<%# Bind("col1") %>'></asp:Label><br />
col2:
<asp:Label ID="col2_Label" runat="server" Text='<%# Bind("col2") %>'></asp:Label><br /
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Delete"

Text="Delete"
</asp:LinkButton
<asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"

Text="Edit"
</asp:LinkButton
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="New"

Text="New"
</asp:LinkButton
</ItemTemplate>

</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conn1 %>"
DeleteCommand="DELETE FROM [test1] WHERE [id] = @.id"
InsertCommand="INSERT INTO [test1] ( [col1], [col2]) VALUES ( @.col1, @.col2)"
SelectCommand="SELECT [id], [col1], [col2] FROM [test1]"
UpdateCommand="UPDATE [radios] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.id">
<DeleteParameters>
<asp:Parameter Name="id" Type="Int32" />
</DeleteParameters>
<UpdateParameters>

<asp:Parameter Name="col1" Type="String" />
<asp:Parameter Name="col2" Type="String" />
<asp:Parameter Name="id" Type="Int32" />
</UpdateParameters>
<InsertParameters>

<asp:Parameter Name="col1" Type="String" />
<asp:Parameter Name="col2" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

|||

Ok, our SqlDataSources differ somewhat. Was your auto-generated, or did you create it manually? The following code was generated for me:

============================================================================

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @.original_id AND [col1] = @.original_col1 AND [col2] = @.original_col2" InsertCommand="INSERT INTO [tblTest] ([col1], [col2]) VALUES (@.col1, @.col2)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [tblTest]" UpdateCommand="UPDATE [tblTest] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.original_id AND [col1] = @.original_col1 AND [col2] = @.original_col2"> <DeleteParameters> <asp:Parameter Name="original_id" Type="Int32" /> <asp:Parameter Name="original_col1" Type="String" /> <asp:Parameter Name="original_col2" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="col1" Type="String" /> <asp:Parameter Name="col2" Type="String" /> <asp:Parameter Name="original_id" Type="Int32" /> <asp:Parameter Name="original_col1" Type="String" /> <asp:Parameter Name="original_col2" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="col1" Type="String" /> <asp:Parameter Name="col2" Type="String" /> </InsertParameters> </asp:SqlDataSource>

=============================================================================================

 Specifically, my update and delete statements have different, more complicated WHERE clauses. When I change the WHERE clause so that it only uses the ID, the formview control works as it should:

 DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @.original_id"

UpdateCommand
="UPDATE [tblTest] SET [col1] = @.col1, [col2] = @.col2 WHERE [id] = @.original_id"

Am I generating my statements incorrectly? It's bizarre that the SqlDataSource auto-generated command statements choke once they start working with null fields...

Regards,

Chris

|||

Hello,

Both of the codes are generated by the IDE, but you checked the "Use Optimistic Concurrency" to get the more complicated where clause. You may need find more information to use this. If you don't check this one, you will have the same code as I have.

HTH.

|||

That did it, thanks so much!

Regards,

Chris

|||The wizard portion that generates SQL Strings for optimistic queries has that bug. You can still use the wizard to generate the basic query, but you'll need to go in and modify it if any of the fields can be null.