Monday, March 19, 2012
Displaying SQL Statement as output on report
Everytime we run a report, we need to verify that the SQL statement has not
been modified since it was last verified as accurate. To combat the constant
requests to show the SQL behind the scenes, I would like to show the SQL as
part of the report.
Is this possible and how would it be done?
I am guessing that it would require custom code that captures the SQL in a
variable and then outputs that variable into the report.Could you get away with driving each report by a seperate stored procedure
( this is what we do anyway ).
Tell them the name of the stored procedure for each report.
If they want to go the extra paranoid step :
Write a custom assembly which uses sp_helptext on the stored proc name to
get the text of the storedproc ( a line at a time )
Generate a SHA1 hash of the text ( you might want to ignore whitespace and
non alphanumeric characters )
Write to some custom reportlog file, reportname, who ran, when, on what
machine, the hash of the procedure name
( Of course you need to record somewhere the hash of the stored procedure
when its verified )
You could even throw an exception if hash doesnt match the last verified
hash.
If thats not good enough ( !! ) you could encrypt the hash with a private
key, i.e. give it a full digital signature.
Or you could just generate a hash of the entire db schema !!
> Everytime we run a report, we need to verify that the SQL statement has
> not
> been modified since it was last verified as accurate.|||I would think the best thing to do is to put everything in SP. Then you have
the date the SP was created and can compare that against when it was
verified. If you keep the date of verification in a table you can then run a
report that shows any unverified SP based on the creation date. This seems
to me to be a much better solution. Straight forward and verifiable (which
is usually what you need for Sarbanes-Oxley).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <David@.discussions.microsoft.com> wrote in message
news:4C2307F6-5002-4713-8B92-DC9A468FFEF2@.microsoft.com...
> The requirements of Sarbanes-Oxley are getting downright outrageous.
> Everytime we run a report, we need to verify that the SQL statement has
not
> been modified since it was last verified as accurate. To combat the
constant
> requests to show the SQL behind the scenes, I would like to show the SQL
as
> part of the report.
> Is this possible and how would it be done?
> I am guessing that it would require custom code that captures the SQL in a
> variable and then outputs that variable into the report.
Tuesday, February 14, 2012
display data using mxdatagrid or datagrid in webmatrix
NAME ID
name1 1
name2 2
name3 3
what i did is connect to the database, click and drag it over to the canvas and codes were generated. if i run it, by default it should show all the contents of the table, correct? well, it isn't. i've tried it using a datagrid and it still doesn't work.
is there an access problem with the database? or, what settings to have to make/change in my sql server? by the way, i have MSDE.
What error are you getting? Do you get the same error when using a DataGrid?|||i'm getting the following error message:
<%@. Page Language="VB" %>
<%@. Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<script runat="server"
' Insert page code here
'
Function test() As System.Data.IDataReader
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='test'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)Dim queryString As String = "SELECT [list].* FROM [list]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnectiondbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)Return dataReader
End Function</script>
<html>
<head>
</head>
<body>
<form runat="server">
<wmx:SqlDataSourceControl id="SqlDataSourceControl1" runat="server" DeleteCommand="" ConnectionString="server='(local)'; trusted_connection=true; database='Northwind'" SelectCommand="SELECT * FROM [Employees]" UpdateCommand=""></wmx:SqlDataSourceControl>
<wmx:MxDataGrid id="MxDataGrid1" runat="server" OnLoad="MxDataGrid1_Load" DataSource="<%# SqlDataSourceControl1 %>" BorderStyle="None" BorderWidth="1px" DataKeyField="EmployeeID" CellPadding="3" BackColor="White" AllowPaging="True" DataMember="Employees" AllowSorting="True" BorderColor="#CCCCCC" DataSourceControlID="SqlDataSourceControl1">
<PagerStyle horizontalalign="Center" forecolor="#000066" backcolor="White" mode="NumericPages"></PagerStyle>
<FooterStyle forecolor="#000066" backcolor="White"></FooterStyle>
<SelectedItemStyle font-bold="True" forecolor="White" backcolor="#669999"></SelectedItemStyle>
<ItemStyle forecolor="#000066"></ItemStyle>
<HeaderStyle font-bold="True" forecolor="White" backcolor="#006699"></HeaderStyle>
</wmx:MxDataGrid>
<!-- Insert content here -->
</form>
</body>
</html>
**********************************************
Server Error in '/' Application.
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File --
<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration
Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.
<!-- Web.Config Configuration File --
<configuration>
<system.web>
<customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
</system.web>
</configuration>
****************************************
the sql server path is not in the same folder as the default webpage folder. does that matter? and how do i change the sql server path.|||and is there good textbook that cover ASP.NET using MS WebMatrix?|||This is a good, free online book:Inside ASP.NET WebMatrix by Alex Homer and Dave Sussman.
Terri