I have this project that I need help with. There are 9 tables that I need to capture everything that happens to them (update, insert, delete). I was thinking of creating triggers. If someone does any of these actions against them then I need to insert into another table the date, the table name, the command that was run, and the records that are affected by it. Now I know how to do the date and table name, that's easy. My question is how do I capture the command. Once I have the command I can get the records affected.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
Is there a way to capture every change made in a database? I would like to be able to audit and report on all changes made in our corp. database. Is there a system tool or function that can accomodate such a thing? Transaction Log maybe??
Hi, We would like to capture events in our system. There seem to be three obvious capture points for us - application, triggers, transaction log. The latter seems to be the most attractive, since we`re looking for a solution with minimal performance impact. In general, our problem is similar to populating data warehouses from on-line databases. Can anyone proffer some advice? In particular, being quite new to SQL Server, I am not sure how difficult/possible it is to read the transaction log in order to cull events. Some direction here would be greatly appreciated. Thanks, Karl
Hi all, Is there anyway to capture the SQL Server Error and act accordingly? I donot want sql server to raise an error when a Primary key violation has occured. Instead i want to capture that error(number,description etc) and act accordingly. Whats happening is, from the application we are trapping this sql error and raising it. Instead, if somebody inserts a record which already exists, then we want to trap that error from the sqlprocedure itself and then do an update to that record.
I was wondering how you go about capturing text as it is entere into a textarea? i want it captured exactly as it is typed, with carriage returns and everything. is there an easy way to do this?
Access front-end, SQL Server 2005 backend.I have users connected to SQL Server via a Microsoft Access user-interface.Connection is via NT login.I want to log users' activities to the database with their userid.How can I capture their NT User ID (via VBA in Access)?Thanks,Bubbles
I've situation where I've to capture a DML executing against let say Table1 and later may at the end of the day or week I want to be able to extract all the DML executed against Table1 and execute them against similar tables on different sql server to synchronize the data. I don't want to use profiler as this is quite expensive resource for my problem niether can use any third party tool.
Is it possible to capture sql statement in the trigger?
I hope I made my question clear. Urgent help will be highly appreciated.
I have a stored procedure which will do 2 insert statements on 2 different tables. In my 2nd insert statement, I need to know how to capture the exact identity primary key value of the newly inserted record from the first insert statement. I am not sure how to get the correct key value of the new record because there may be more than one user inserting at the same time. Therefore, it is tough to capture the key value that belongs to the user doing his transaction at the time. Please help out. Thanks in advance.blumonde
In my BLL I have a method that adds a new row to a table in the database... [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)] public bool AddContact(string firstname, string lastname, string middleinit, bool active, Guid uid, bool newsletter) { ContactsDAL.tblContactsDataTable contacts = new ContactsDAL.tblContactsDataTable(); ContactsDAL.tblContactsRow contact = contacts.NewtblContactsRow(); contact.FirstName = firstname; contact.LastName = lastname; contact.MiddleName = middleinit; contact.Active = active; contact.UserID = uid; contact.Newletter = newsletter; contacts.AddtblContactsRow(contact); int rowsAffected = Adapter.Update(contact); return rowsAffected == 1; } The primary key in this table is a BigInt set as an identity column....How do I capture the value of the primary key that gets created when the new row is added?
I've a textbox that displays the current time in this format "hh:mm:ss tt" but when it is save into the database it'll display the date and time together. So how do I save only the time into the database? My codes is as shown below:
txtTime.Text = DateTime.Now.ToLongTimeString() Dim parameterDate As SqlParameter = New SqlParameter("@Date_5",SqlDbType.DateTime) parameterDate.Value = txtDate.Text objCommand.Parameters.Add(parameterDate)
I've tried using Format() but it still get the same results. Can someone help me out? Thanks!
Is there a way I can view the transaction log mean I want to see the transactions occurred during the last few hours before the commit? And is there a way to capture all the logins who access the DB
Does anybody know of a way to set up a task which will send an email with an attachment other than using sp_sendmail. Sp_SendMail will run a query and send the results as a text file. But I need to run a stored procedure which generates an Excel spreadsheet then have an email sent with the Excel file as an attachment. The sp_sendmail text file just doesn't work with the information I am generating so I need to find another solution.
I am trying to find a easy way of capturing the SQL user login that is being used for the the record row that is being added/updated. I am now using a insert and update trigger to update the last edit date and record add date. Is there a way I could incorporate the user login capturing within the triggers ???
I have to leave a production trace running for a couple of days to track down a little bit of data corruption. I am setting the stop time and maximum file size and I am running it from another machine and writing to a file and not a table and all of that good stuff.
however since I am going to be leaving this running for a while I do not want to deal with a gigantic file to sift through I am trying to limit my trace to all of the stored procs, which can be coming from other databases, to those inserting and and updating a particular table.
I have been goofing around with the Objects:Closed and Objects:Open and the SP:Starting And SP:Completed events, but I can not seem to make this work. I skimmed through the first 5 pages of my google search and my BOL search, but came up empty.
Is this possible? I thought I had done this before.
I need to capture the logout time and login time. I can capture the login time already but I cannot capture the logout time.My logout time and login time need to be on the same entry.This is the coding in the logout button:
Code: protected void logoutButton_Click(object sender, EventArgs e) { SqlCommand cmd = new SqlCommand("SELECT TOP 1 LogId FROM LoggedInUsers where MemberID = @MemberID ORDER BY LogId DESC"); cmd.Parameters.AddWithValue("@MemberID", Session["MemberID"]); Session.Abandon(); }
I also need to do in global.asax. And this is the coding:
One of my co-worker told me I can do this to capture errors and insert into error table but when I test it, it doesn't work. Here is what I try to accomplish. SQL 2012. In reality, I have more complicate queries than below.
1. Insert data FROM SourceEmployee INTO Employee table and capture emp_id and error msg insert into dbo.##temperror table 2. Continue on the process until no more record. Basically, skip the error records and do a while loop until end of record.
--DROP TABLE dbo.Employee; CREATE TABLE [dbo].[Employee] ( [emp_id] [int] NOT NULL, [last_name] [varchar](20) NULL, [first_name] [varchar](15) NOT NULL,
I'm trying to organize this SQL query from a packet capture and I'm more of a network/application guy, not so much of a DBA. To me it looks like they are using variables in their query and the "@" is a delimiter.
S E L E C T [ t 0 ] . [ S E C T I O N N A M E ] , [ t 0 ] . [ P A R A M E T E R N A M E ] , [ t 0 ] . [ I N T V A L U E ] , [ t 0 ] . [ S T R I N G V A L U E ] , [ t 0 ] . [ D A T E V A L U E ] , [ t 0 ] . [ I N F R A S T R U C T U R A L ] , [ t 0 ] . [ S i t e P a r a m e t e r s I d ] F R O M [ d b o ] . [ S I T E _ P A R A M E T E R S ] A S [ t 0 ] W H E R E ( [ t 0 ] . [ S E C T I O N N A M E ] = @ p 0 ) A N D ( [ t 0 ] . [ P A R A M E T E R N A M E ] = @ p 1 ) @ 4@ @ p 0 n v a r c h a r ( 3 ) , @ p 1 n v a r c h a r ( 1 0 ) @ p 0 4 W e b @ p 1 4 M a x Q u e r i e s
Happy New Year everyone!I would like to capture CPU Utilization % using TSQL. I know this canbe done using PerfMon but I would like to run TSQL command (maybe onceevery 5 minutes) and see what is the CPU Utilization at that instant sothat I can insert the value in a table and run reports based on thedata.I have spent a good amount of time scouring google groups but this isall I have found:SELECT(CAST(@@CPU_BUSY AS float)* @@TIMETICKS/ 10000.00/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) ASCPUBusyPctFROMmaster..SysProcesses AS SP2WHERESP2.Cmd = 'LAZY WRITER'Problem is this gives me total amount of time CPU in %) has been busysince the server last started. What I want is the % for the instant -the same number we see in Task Manager and PerfMon.Any help would be appreciated.Thanks
I trashed SQL Server2000 when I added the WINXP SP2, and had to reinstallit. When I did, my database(DEV_DATA) remained intact, but when I go inthru Enterprise Manager, it is not located in the system tree, and istherefore inaccessible. Can anyone suggest how I could get this databaseincluded.
I'm wondering if SSIS can capture the table schema structure, including the primary keys, foreign keys and indexes applied from the source table to the destination table? My source tables will be coming from AS400/DB2 and I'm using OLEDB Provider for DB2. I would like to automatically generate the table schema of my destination table in SQL Server 2005 (thru SSIS) as similar as my source table from AS400.
