How To Get Name When Given Event Class From Trace
Oct 26, 2007
When viewing trace information via SQL Profiler, you can see the name of the event class. But when viewing trace information via T-SQL (either from a trace table or trace file using ::fn_trace_gettable), you see the id of the event class and not its name.
If I knew the name of the event class, then I could easily find the id by drilling down from this BOL article:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0f0fe567-e115-4ace-b63c-73dc3428c0f6.htm
For instance, I recently captured Lock:Acquired event class from the Locks event category. I can see from BOL that its id is 24 by drilling down into Locks:Acquired Event Class from the above link. I just have to view the Description field for EventClass data column.
Is there a way to do this in reverse, meaning if I knew the id of the event class, could I find the name easily? Is SQL Profiler using a built-in function to convert the id to a name?
I've searched through BOL for this information, but haven't been able to locate an article that lists all of the event classes by id and their associated names. I've also scoured Google for this information and haven't been able to locate it.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
View 3 Replies
ADVERTISEMENT
Feb 7, 2008
Hi all.
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass 80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen during very high traffic. I have isolated the query, included at the bottom of this post (cleaned up). There is very little info on this event class out on the web. Version is SQL 2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get the MJP. I would expect that a query without a JP is a query without a JP and it would be all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can’t make it happen in testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed light? Know of good links, etc?
Thanks!!
bbRichbb
SELECT
p.Product_Id,
MIN(ae.Enum_Value) AS color,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id,
COUNT(ps.SWATCH_STATUS) AS total_available_colors
FROM
Attribute_Enum_Value ae
INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id
AND ae.Attribute_Type_Id = pae.Attribute_Type_Id
INNER JOIN Product p
INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id
INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id
INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr
AND p.Product_Id = ps.PRODUCT_ID
WHERE
(pae.Attribute_Type_Id = 500001)
AND (p.Product_Class_Id = 2)
AND (p.Status_Code = 'ACTV')
AND (ps.SWATCH_STATUS = 'ACTV')
GROUP BY
p.Sequence_Number,
p.Product_Id,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id
HAVING
(p.Product_Id = 1209645)
ORDER BY
p.Sequence_Number,
p.Product_Id
View 1 Replies
View Related
Feb 7, 2008
Hi all.
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass
80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen
during very high traffic. I have isolated the query, included at the bottom of this post
(cleaned up). There is very little info on this event class out on the web. Version is SQL
2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall
direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get
the MJP. I would expect that a query without a JP is a query without a JP and it would be
all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can€™t make it happen in
testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed
light? Know of good links, etc?
Thanks!!
bbRichbb
SELECT
p.Product_Id,
MIN(ae.Enum_Value) AS color,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id,
COUNT(ps.SWATCH_STATUS) AS total_available_colors
FROM
Attribute_Enum_Value ae
INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id
AND ae.Attribute_Type_Id = pae.Attribute_Type_Id
INNER JOIN Product p
INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id
INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id
INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr
AND p.Product_Id = ps.PRODUCT_ID
WHERE
(pae.Attribute_Type_Id = 500001)
AND (p.Product_Class_Id = 2)
AND (p.Status_Code = 'ACTV')
AND (ps.SWATCH_STATUS = 'ACTV')
GROUP BY
p.Sequence_Number,
p.Product_Id,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id
HAVING
(p.Product_Id = 1209645)
ORDER BY
p.Sequence_Number,
p.Product_Id
View 1 Replies
View Related
Feb 1, 2006
I'm trying to audit all the database activities using Server-side tracein SQL Server 2000.Basically I need to get the login information (login Id, Login timeetc) along with the name of the database.Can some one suggest a way todo that?Thanks in advance.J
View 1 Replies
View Related
Sep 13, 2007
I have discovered trace output in MSSQLDATAMSSQL.1MSSQLLOG that I have not kicked off. It is at various times and limited to 20MB. So that tells me a server event is kicking off a pre-defined trace. The trace contains mostly hash warnings and sort warnings.
I have looked through my Agent Jobs, Agent Alerts, and perfmon and don't find anything that is set up to kick off a trace under a specified condition.
I have checked the job activity, SQL error logs, SQL server logs, and the server's event viewer for any odd events or event times that correlate with the times of the traces.
I have checked each database's sys.sql_modules for a definition containing '%sp_trace%'.
Where else can I check to find what would be triggering these traces?
Our app logins don't have permissions high enough to run traces, I verified:
You do not have permission to run 'SP_TRACE_CREATE'
I am the DBA, not a .NET programmer -- so I am lacking experience if there's anything on the .NET side.
This is SQL 2005 64-bit running active/passive on a Win2003 clustered pair.
View 1 Replies
View Related
Feb 21, 2008
Hai Guys,
I have a doubt Regarding SqlDataReader
i can able to create object to Sqlconnection,Sqlcomand etc...
but i am unable to create object for SqlDataReader ?
Logically i understand that SqldataReader a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.
sqlDatareader belongs to which class is it sealed or static class?
can we create own class like SqldataReader .......
Reply Me ...... if any one know the answer..............
View 8 Replies
View Related
Feb 1, 2008
Does any one inherit SqlDatasource class?
I treid it as :
public class MYDataSource : System.Web.UI.WebControls.SqlDataSource
{public MYDataSource(){
}
}
Debugger dont give any error or warning when i buld project. But when i use it in a page Visual studio is crashed.
Can any one help me ?
View 1 Replies
View Related
Jun 2, 2015
Recently we migrated our environment to 2012.
We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.
Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.
I have gone through so many websites but i never find.
View 13 Replies
View Related
Oct 25, 2011
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date  10/25/2011 10:55:46 AM
Log  SQL Server (Current - 10/25/2011 10:55:00 AM)
Source  spid
Message
Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Â
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
View 2 Replies
View Related
Apr 8, 2008
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file.
The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30
WHERE targetinstance isa 'CIM_DataFile'
AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message.
However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
View 2 Replies
View Related
May 31, 2007
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup)
We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers.
We were having
Event Type: Failure Audit
Event Source: Security
Event Category: Object AccessEvent ID: 560
Date: 5/23/2007
Time: 6:27:15 AM
User: domainuser
Computer: MACHINENAME
Description:
Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,1623975729}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: Domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: User
Client Domain: Domain
Client Logon ID: (0x0,0x6097C608)
Accesses: READ_CONTROL
Connect to service controller
Enumerate services
Query service database lock state
Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015
Applied the following fix
http://support.microsoft.com/kb/907460/
Now we are getting
Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: user
Client Domain: domain
Client Logon ID: (0x0,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D
View 4 Replies
View Related
Nov 2, 2007
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
View 4 Replies
View Related
Oct 22, 2007
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
David
View 5 Replies
View Related
Oct 6, 2006
Does anybody know of a good facility to take a SQL 2000 to SQL 2005 Admin course that is geared towards experienced DBAs, that is not so costly. I'm not the type that can sit and read on my own to learn, it has to be a real project or a classroom environment.
Also, the NYC area.
thanks,
Paul
View 1 Replies
View Related
May 8, 2008
Just started SQL Class and I am struggling with an assignment.
Would someone be kind to give me some help please?
your task is to make a stored procedure that will only billcustomers making monthly payments higher than $500.A Customer can have multiple Plans (services for which they pay amonthly fee).
You have to execute a billing procedure for each single active planunder an eligible customer account. Eligible customer account is anaccount that makes estimated monthly payment higher than $500.
You are given 2 tables, "Customers" and "Plans". Table "Customers" has columns: CustomerID, EmailAddressTable "Plans" has columns: PlanID, CustomerID, MonthlyPrice, isClosed,NextBillDate
Also, you are given two stored procedures named "Bill_Plan" and"Send_Email". All stored procedures returns 0 upon success and negativenumber upon failure.The "Bill_Plan" actually bills a customer for a plan. The storedprocedure takes one input parameter and have one output parameter.
Bill_Plan
@nPlanID = @PlanID,
@dtNewBillDate = @NextBillDate output
You have to use the output parameter @NextBillDate to updatePlans.NextBillDate with its new value.
Upon successful billing you have to send en email to the customer'semail address. The "Send_Email" stored procedure takes two inputparameters.
Send_Email
@sEmailAddress = @EmailAddress,
@sSubject = 'Your Invoice is Ready'
You have to rollback any single billing transaction upon any kind offailure and continue billing the remaining plans. (Important!!!)
Thank signed I don't want to flunk!
View 6 Replies
View Related
Feb 27, 2007
Hello,I created a class in my .Net code and I have an SQL 2005 table with a column of type image (I suppose I should use this type)After I define the class properties I need create a new record and save the class in the database.Can I do it the same way as I would save, for example, a string in a varchar field?Thanks,Miguel
View 1 Replies
View Related
Jan 17, 2008
Hello,
I have the class below. And trying to execute it on a button click event.
What am i doing wrong ?
Thanks
Here is the button click event1 protected void Button1_Click(object sender, EventArgs e)
2 {
3 signup_data_entry signup = new signup_data_entry();
4 signup.signup_data_entry();
5
6 }
Here is my class file. please advice
1 public class signup_data_entry
2 {
3 public signup_data_entry()
4 {
5 //SqlConnection con = new SqlConnection("cellulant_ConnectionString");
6 SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"]);
7
8 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con);
9 command.CommandType = CommandType.StoredProcedure;
10
11 con.Open();
12
13 //string IP = new string();
14
15
16 command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));
17 command.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription"));
18
19 command.Parameters[0].Value = 4;
20 command.Parameters[1].Value = "SouthEast";
21
22 int i = command.ExecuteNonQuery();
23
24
25 }
26 }
View 6 Replies
View Related
Jan 25, 2008
hiiiiiiiiii I am creating a web application using vb.net in which i m using the concept of classes. now i am done all the code for inserting the values in the database using the class but it is difficult to fetch the values from the database using select command and sending them to a WebForm . i want to know how i send send the values coming from the select command to a datagrid or another web controlif possible provide me a sample code thanks for your help
View 2 Replies
View Related
Dec 28, 2001
Not able to use Enterprise Mgr after updgrade. Getting Class not registered? Any idea what to do to correct? Thanks, A
View 1 Replies
View Related
Jul 23, 2005
Can anyone recommend a good SQL Server book or class forBeginner-Intermediate, please?Thanks
View 2 Replies
View Related
Feb 4, 2008
there was a post some time ago but nobody answered. Maybe somebosy will answer now?
I have created a class which contains a few UDF's. The class has a static constructor that reads from the databse and loads a Dictionary<> collection.
What is the lifetime of the class?
What will cause the static constructor to be called again?
When will it be garbage collected?
If the life time is limited by default, can it be extended?
Thanks.
View 2 Replies
View Related
May 30, 2007
Good morning, all!
I'm tasked with creating POCO (C# Class Object) and XML definitions for three different kinds of records coming from my input file. The problem is that I'm not sure how to do this within the SSIS framework. It seems to me that I can somehow define a class and include XML element tags in the definition, but I'm not sure where to look/who to ask/how to do that!
The goal is to be able to assign the data types and class attributes for various parts of each input record based on position, and to serialize the data as XML (and therefore my inputs to my Data Flow will be XML objects instead of a flat file, as I had been doing it...).
Can anyone point me at a good tutorial? Or a simple example?
Also, is "DateTime" a recognized data type in C#?
Thanks a bunch!
Jim Work
View 6 Replies
View Related
Feb 14, 2007
Hi,
when i am trying to open a table from Microsoft Visua Studio 2005
I got the following error
Class Not Registered.Loking for object with classID:......
how resolve this?????
View 1 Replies
View Related
May 29, 2007
I recently took a class on SQL 2005. The classwork had lab files but I did not manage to hang on to the files. Are those files available for DL from MS?
I am specifically looking for the files from Module 7 "Planning for Source Control, Unit Testing, and Deployment"
TIA
View 1 Replies
View Related
Nov 14, 2007
Hi, I'm having trouble with the ReportingService2005 object
I'm trying to use the Get and SetPolicies methods and have used ideas from other snippets in order to get this:
...
Microsoft.SqlServer.ReportingServices2005.ReportingService2005 RS = new Microsoft.SqlServer.ReportingServices2005.ReportingService2005();
RS.Credentials = System.Net.CredentialCache.DefaultCredentials;
RS.Url = http://www.MyServer.co.uk/ReportServer/ReportService2005.asmx;
bool ip;
Policies = RS.GetPolicies("/", out ip);
RS.SetPolicies("/", Policies);
...
Does anyone have any experience with this object, or any working examples, I suspect I'm still missing some permissions thing but at the moment all I get is this annoyingly unhelpful message:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: --
BODY
{font-family:Verdana;font-weight:normal;font-size:8pt;color:black;}
H1
{font-family:Verdana;font-weight:700;font-size:15pt;}
LI
{font-family:Verdana;font-weight:normal;font-size:8pt;display:inline;}
.ProductInfo
{font-family:Verdana;font-weight:bold;font-size:8pt;color:gray;}
A:link
{font-size:8pt;font-family:Verdana;color:#3366CC;text-decoration:none;}
A:hover
{font-size:8pt;font-family:Verdana;color:#FF3300;text-decoration:underline;}
A:visited
{font-size:8pt;font-family:Verdana;color:#3366CC;text-decoration:none;}
Reporting Services Error
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
SQL Server Reporting Services
--.
Thanks for your help,
Sean.
View 2 Replies
View Related
Aug 2, 2006
I created a new DataSet object using the
wizard and had no probs, it's very straightforward. I created a
GetProducts() method and also added a GetProductCount() method. I
read somewhere that when the DataSet is saved, it will generate the
TableAdapter classes and store them in the project nested under the
DataSet object. This isn't happening, the files aren't there and
yes I'm displaying all files. There *is* however the TableAdapter
class but it's in not in the project, it's a temporary file w/ the
following path:
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET
Fileswebsitef38f8b5500014aeSources_App_Codepw_products.xsd.72cecc2a.cs.
I saved the DataSet several times, compiled the entire solution and
even closed the solution yet this temporary file isn't getting saved
where it should be. Does anyone have any ideas abt this?
I'm eager to start developing my DAL and def want to use these
TableAdapter classes.
Thx in advance,
-Pete
View 2 Replies
View Related
Jun 8, 2007
Hi,
I'm working on both VWDEE and VBEE and, in each, when I try to add a database diagram I get the following message (after the 'Do you want to create required objects' prompt) 'Invalid Class String'.
Any ideas?
View 2 Replies
View Related
Jan 17, 2008
Hello,
I put my stored procedure in my class and try to execute it but get this error
1st here is my class
1 public signup_data_entry()
2 {
3 SqlConnection con = new SqlConnection("cellulant_ConnectionString");
4
5
6 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con);
7 command.CommandType = CommandType.StoredProcedure;
8
9 con.open();
10
11 command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));
12 command.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription"));
13
14 command.Parameters[0].Value = 4;
15 command.Parameters[1].Value = "SouthEast";
16
17 int i = command.ExecuteNonQuery();
18
19
20 }
and here is the error message on the testing server. Error line is line 27 below
Source Error:
Line 25: command.CommandType = CommandType.StoredProcedure;
Line 26:
Error >>> Line 27: con.open();
Line 28:
Line 29: command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));
Source File: c:inetpubwwwrootcellulant1App_Codesignup_data-entry.cs Line: 27
View 1 Replies
View Related
Apr 17, 2008
HI,
I am going to make a big web application. so im going to use sql connection with class for example i have 100 web pages and i will make a slq connection in class.vb
Class1.vb(see the below code)
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasicPublic Class Class1
Private conn As New SqlConnection("Data Source=DANISHSQLEXPRESS;Initial Catalog=ARBASHHR.MDF;Integrated Security=True")Private cmd As New SqlCommand()
Private da As New SqlDataAdapter(cmd)
End Class
default.aspx(here is asp.net page i want to use the sql connection from that class.)
but this below page saying that Name "cmd" is not declared and connection
Try
cmd.Connection = conn
conn.Open()Dim ds As New DataSet
cmd.CommandText = "SELECT * FROM Users where (UserName = N'" & TextBox1.Text & "')" 'And (Password = N '" & TextBox2.Text & "')"da.Fill(ds, "data")
conn.Close()
For Each dt As DataTable In ds.Tables
For Each dr As DataRow In dt.RowsTextBox3.Text = dr.Item("Password")
Next drNext dt
If TextBox2.Text = TextBox3.Text ThenResponse.Redirect("Welcome.aspx")
Else
Label3.Text = "Invalid Username/password"
End IfCatch ex As Exception
' MsgBox(ex.Message) '("Error Loading Information From Database..", MsgBoxStyle.Critical, "Error")
End Try
End Sub
please let me know where im doing mistake.....
Thank in Advance
View 2 Replies
View Related
Jun 12, 2008
Can I use this class in ASP.NET web site with many visitors?SqlLayer.cs: public static class SqlLayer{ public static string ConnectionString = ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString; public static int ExecuteNonQuery(string StoredProcedureName, string[] ParamNames, object[] ParamValues) { if (ParamNames.Length != ParamValues.Length) { throw new Exception("ParamNames.Length != ParamValues.Length"); } using (SqlConnection cSqlConnection = new SqlConnection(ConnectionString) { cSqlConnection.Open(); SqlCommand cSqlCommand = cSqlConnection.CreateCommand(); cSqlCommand.CommandType = CommandType.StoredProcedure; cSqlCommand.CommandText = StoredProcedureName; cSqlCommand.Parameters.Add("@ReturnValue", DbType.Int32); cSqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue; for (int i = 0; i < ParamValues.Length; i++) { cSqlCommand.Parameters.AddWithValue(ParamNames[i], ParamValues[i]); } cSqlCommand.ExecuteNonQuery(); return (int) cSqlCommand.Parameters["@ReturnValue"].Value; } } public delegate void ActionForReader(SqlDataReader Reader); public static void ExecuteReader(string StoredProcedureName, string[] ParamNames, object[] ParamValues, ActionForReader cActionForReader) { using (SqlConnection SqlConnection1 = new SqlConnection(ConnectionString)) { SqlConnection1.Open(); SqlCommand SqlCommand1 = SqlConnection1.CreateCommand(); SqlCommand1.CommandType = CommandType.StoredProcedure; SqlCommand1.CommandText = StoredProcedureName; for (int i = 0; i < ParamValues.Length; i++) { SqlCommand1.Parameters.AddWithValue(ParamNames[i], (ParamValues[i] == null ? DBNull.Value : ParamValues[i])); } using (SqlDataReader Reader = SqlCommand1.ExecuteReader()) { if (cActionForReader != null) { //if (Reader.HasRows == false) throw new Exception("Reader has no rows"); //if (Reader.RecordsAffected == 0) throw new Exception("Reader RecordsAffected = 0"); while (Reader.Read()) { if(Reader!=null) cActionForReader(Reader); } } } } }} Using: SqlLayer.ExecuteNonQuery("SomeStoredProcedure", "ID", ID);---------SqlLayer.ExecuteReader("SomeStoredProcedure", new string[]{"Param1","Param2"}, new object[]{Value1,Value2}, Action);...void ActionForForumCollection(SqlDataReader Reader) {LabelContent.Text += Reader[0].ToString()+" - "+Reader[1].ToString();}
View 1 Replies
View Related
Jul 11, 2004
hi
i was reading book about asp.net and i found example for a dataacess class but i didn't understand this part
Private m_FieldData As New NameObjectCollection
Private _m_ConnectionString As String
Private m_dbConnection As SqlConnection
Private Sub AddParameters( _
ByVal objCommand As SqlCommand, _
ByVal objValues() As Object)
Dim objValue As Object
Dim I As Integer
Dim objParameter As SqlParameter
objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)
I = 0
For Each objParameter In objCommand.Parameters
If objParameter.Direction = ParameterDirection.Input _
Or objParameter.Direction = _
ParameterDirection.InputOutput Then
objValue = objValues(I)
objParameter.Value = objValue
I = I + 1
End If
Next
End Sub
Private Sub AddFieldParameters _
(ByVal objCommand As SqlCommand)
Dim objParameter As SqlParameter
objCommand.Parameters.Clear()
SqlCommandBuilder.DeriveParameters(objCommand)
For Each objParameter In objCommand.Parameters
objParameter.Value = _
_FieldData.Item(objParameter.ParameterName. _
Substring(1))
Next
End Sub
Public Function ExecDataReader _
(ByVal strStoredProc As String, _
ByVal ParamArray objValues() As Object) _
As SqlDataReader
Dim objCommand As SqlCommand
Dim objReader As SqlDataReader
objCommand = New SqlCommand
objCommand.CommandText = strStoredProc
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = dbConnection
Try
objCommand.Connection.Open()
If (objValues.Length = 0) Then
AddFieldParameters(objCommand)
Else
AddParameters(objCommand, objValues)
End If
objReader = objCommand. _
ExecuteReader(CommandBehavior.CloseConnection)
Catch ex As Exception
If objCommand.Connection.State.Open Then
objCommand.Connection.Close()
End If
End Try
Return objReader
End Function
can anybody help me what the author want to do
View 3 Replies
View Related
Dec 5, 2004
Anyone have code to convert a sql proc to a C# class?
Specifially something to create the columns returned from the proc.
Thanks.
View 2 Replies
View Related
Jun 27, 2005
hate to gripe, but this was bad. I have taken several sql server classes over the years, and have always been happy with the class, but avoid the 2733a (upgrading you database administration skills to sql 2005). Class is poorly devloped and incomplete. Half of the examples were to do things such as mirroring and replication from a command prompt. I thought I was in an Oracle 8 class. It was clear much of this functionality is not complete. In fact most of the class was command prompt based. They had absolutley nothing about the upgrade process from SQL 2000 to SQL 2005. I hope MS is not taking a step backwards, I always thought the strong suite for MS was the enterprise manager tool suite. The instructor did tell me there was an upgrade to the class that just came out, but it has a long way to go.
View 1 Replies
View Related
