Hello ;
Can any one point me how to trace any modification made on a certain tabeles with in any data base?
what i mean is if i have table "a" and i add updated some rows with it ... is there a way to figure iut the old data and the new data?
simply i can create another table "history_on_a" which will be filled by a trigger when any updates happen ...this solution will consume my hard disk
so is ther a built in funtion with sql ?
so if any one can tell me what to search for or any idea
managed plug-in framework that's available for download here: http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en#DMAPI.
This package includes the source code for a sample plug-in algorithm written in C#.
in this source code all .cs files are modified for clustering algorithm
if my plugin algorithm is of association or classification type then what modifications are requried in source code???
We have the following trace flags present in startup in SQL server 2000:
809 1204 3605 3913
Need to understand if these should be required in SQL Server 2005 + SP2 version. I have run the upgrade advisor tool which indcates that the behaviour of some flags has changed and some other TFs are no longer applicable. Hence, I want to know about the above mentioned TFs.
How on earth can a Profile Trace be run where SSE 2005 is installed??? In the past, with MSDE, we always installed the 'tools' on a local workstation, so that we had EnterPrise Manager and its suite of tools...no problem. Yet, with Management Studio Express (err...Distress?), there's no way to do this! I've scoured the net, and I see threads where people have done it, yet, no one seems to be clear...including Microsoft...on how to obtain this MOST IMPORTANT of all tools for an SQL deployment.
Using SQL Server 2000 std. edition, I was bitten by the bug described in KBs 818671 and 289149. Query optimizer using Hash Match Team operators would sometimes fail. I added -T8679 at SQL Server startup.
Now that I'm upgrading to SQL Server 2005, is this trace flag still required?
I see that "this was fixed in SQL 2000, SP1." However, I would like a more precise confirmation that this flag is no longer needed in SQL 2005. Sometimes, no news is not necessarily good news.
The error is intermittent, and at least partially dependent on data conditions not available to me for exhaustive regression testing (or else of course I would do that).
I'm trying to create an SSIS package that will do a straight data copy between databases. The problem is that the underlying schema of the origin may change and the requirement is that the transfer be table driven. i.e. the tables that are copied are listed in a table and there should be no human intervention when the schema changes.
I'm moving data between SQL Server and SQL Azure, so backup and restore doesn't work. Has to be an SSIS package.
What's the best way to deal with a changing schema in an SSIS package? Can I delete and rewrite the underlying XML for any tables that change? Do I need to do it programmatically with C#? Do I need to create the package from scratch each time?
I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
We are currently using 2008 environment. We do have an SSIS Package running. The package used to run everyday and take the production server full backups and restore into the another server. Then do some delete commands and do some updates in that database on that server (We have some sensitive data other than Production we have to run that scripts in any environment). After run all those delete statements another team will read the data from that database.
We are planning to migrate to 2014 and set up always on and use the replica as the source. In this case how the package will work?
How to change that SSIS package. With the 2014 always on we are directly reading the data there is no backups to restore then how to run the delete statements?
I need to run a trace on a SQL 2005 Workgroup Edition. Profiler is onlyavailable in Standard and Enterprise?SQL 2005 BOL refers to serveral SPs and functions for tracing but I don'thave those in my workgroup edition.Any other options? Can I connect a SQL 2000 Profiler to 2005?Thanks.
I am working with 3 sql 7.0 servers (Servers A, B, C) and am trying to create a trace via profiler to run against server B.
Every trace that I create keeps pointing to server A even though I have server B defined in the properties of the trace - (when I edit the trace properties it REALLY does show server B). I have server B defined in the screen where you 'Select the SQL Server to run the trace on'. I even used the wizard to run one of the canned traces and it still points to server A. Every trace I set up runs against server A. Can anyone think of what the heck I'm doin' wrong? Is is a configuration setting somewhere?
I have created a server-side procedure which captures evenst for audit purposes and save to a file onto the SQL Server. This procedure has been defined as an autoload so that when the server is restarted, this procedure automatically loads.
However, there is a fundemental flaw with this routine. When the procedure attempts to execute, it will abort due to the fact that the trace file already exists.
I have two choices:-
1. When the procedure restarts, append to existing file or 2. Make a folder which comprise of today's day ie 20080128 and move trace file/s to this folder.
Option 2 is preferred.
How can I make this achievable?
Please note I'm using SQL Server 2005.
Ken
Below is a sample of my Code
-- Create a Queue -- Declare Control Variable declare @ReturnCode int declare @Rc int -- Declare Option Variable declare @TraceID int declare @maxfilesize bigint declare @ColumnId int declare @LogicalOperator int declare @ComparisonOperator int declare @Value int declare @Option int declare @on bit declare @Event int -- Set Option Variables set @maxfilesize = 100 -- Maximum file size in megabytes set @Option = 6 -- TraceFileRollOver and ShutdownOnError set @on = 1 -- True set @ReturnCode = 0 -- No Error exec @Rc = sp_trace_create @TraceID output, @Option, N'\Server_named$SQLTraceActivity', @maxfilesize, NULL select @ReturnCode=@@Error if @ReturnCode <> 0 Begin if @ReturnCode = 1 Print 'Error 1 - Unknown error.' if @ReturnCode = 10 Print 'Error 10 - Invalid options. Returned when options specified are incompatible.' if @ReturnCode = 12 Print 'Error 12 - Cannot create tracefile - check if file already exists, or this trace already running' if @ReturnCode = 13 Print 'Error 13 - Out of memory. Returned when there is not enough memory to perform the specified action.' if @ReturnCode = 14 Print 'Error 14 - Invalid stop time. Returned when the stop time specified has already happened.' if @ReturnCode = 15 Print 'Error 15 - Invalid parameters. Returned when the user supplied incompatible parameters.' else Print 'Unexpected and Unknown error In creating trace - Please review' Goto ErrorHandler End -- Set Events -- Trace NTDomainName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 7, @on -- Trace HostName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 8, @on -- Trace IndexID for Object:Altered event exec sp_trace_setevent @TraceID, 164, 24, @on -- Trace RequestID for Object:Altered event exec sp_trace_setevent @TraceID, 164, 56, @on -- Trace SessionLoginName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 64, @on -- Trace ClientProccessID for Object:Altered event exec sp_trace_setevent @TraceID, 164, 9, @on -- Trace IntegerData for Object:Altered event exec sp_trace_setevent @TraceID, 164, 25, @on -- Trace LogionSid for Object:Altered event exec sp_trace_setevent @TraceID, 164, 41, @on -- Trace RequestID for Object:Altered event exec sp_trace_setevent @TraceID, 164, 49, @on -- Trace NTUserName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 6, @on -- Trace ApplicationName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 10, @on -- Trace StartTime for Object:Altered event exec sp_trace_setevent @TraceID, 164, 14, @on -- Trace ObjectID for Object:Altered event exec sp_trace_setevent @TraceID, 164, 22, @on -- Trace ServerName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 26, @on -- Trace ObjectName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 34, @on -- Trace XactSequence for Object:Altered event exec sp_trace_setevent @TraceID, 164, 50, @on -- Trace DatabaseID for Object:Altered event exec sp_trace_setevent @TraceID, 164, 3, @on -- Trace LoginName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 11, @on -- Trace NTDomainName for Object:Altered event exec sp_trace_setevent @TraceID, 164, 35, @on -- Trace EventSequence for Object:Altered event exec sp_trace_setevent @TraceID, 164, 51, @on -- Trace TransactionID for Object:Altered event exec sp_trace_setevent @TraceID, 164, 4, @on -- Trace SPID for Object:Altered event exec sp_trace_setevent @TraceID, 164, 12, @on -- Trace ObjectType for Object:Altered event exec sp_trace_setevent @TraceID, 164, 28, @on -- Trace IsSystem for Object:Altered event exec sp_trace_setevent @TraceID, 164, 60, @on -- Trace EventSubClass for Object:Altered event exec sp_trace_setevent @TraceID, 164, 21, @on
I have created a server-side procedure which captures evenst for audit purposes and save to a file onto the SQL Server. This procedure has been defined as an autoload so that when the server is restarted, this procedure automatically loads.
However, there is a fundemental flaw with this routine. When the procedure attempts to execute, it will abort due to the fact that the trace file already exists.
I have two choices:-
1. When the procedure restarts, append to existing file or
2. Make a folder which comprise of today's day ie 20080128 and move trace file/s to this folder.
Option 2 is preferred.
How can I make this achievable?
Please note I'm using SQL Server 2005.
Ken
Below is a sample of my Code
-- Create a Queue
-- Declare Control Variable
declare @ReturnCode int
declare @Rc int
-- Declare Option Variable
declare @TraceID int
declare @maxfilesize bigint
declare @ColumnId int
declare @LogicalOperator int
declare @ComparisonOperator int
declare @Value int
declare @Option int
declare @on bit
declare @Event int
-- Set Option Variables
set @maxfilesize = 100 -- Maximum file size in megabytes
set @Option = 6 -- TraceFileRollOver and ShutdownOnError
I am trying to migrate a system across and need some help tidying data and merging info. Firstly if I have some companies with the suffix 'Limited' and some with 'Ltd' ie BBC Ltd and BBC Limited. How do I change all these to read the company name and then Ltd?? Secondly I am pulling info from a table which has company name duplicates into a table that also may have company details relating to that company name. I wish to add the details if the company name doesn't exist in the table and also only input the once from the source table. I am using cursors for this. My scripts see below is adding every record. Any ideas why?? Thanks for any suggestions
CREATE PROCEDURE check_company2 AS declare @id varchar(50), @company_name varchar(50), @business_type varchar(50), @hardware varchar(50), @operating_system varchar(50), @networks varchar(50), @pcs varchar(50), @terminals varchar(50), @test_companies_id varchar(50) begin declare s_completelist_cur cursor for select [company name],[business type],[hardware],[operating system],[networks], [pcs],[terminals] from s_completelist open s_completelist_cur
fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals while (@@fetch_status=0)
begin print @company_name select @test_companies_id from companydetails c where c.company_name = @company_name if @test_companies_id is not null
begin print 'match found' end else begin insert into companydetails ( company_name, business_type,hardware, op_system, network, pcs, terminals) values (@company_name, @business_type,@hardware, @operating_system, @networks, @pcs, @terminals) end
fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals end close s_completelist_cur deallocate s_completelist_cur end
I have a series of DTS packages. Each package has 20 queries. Each query has a server name. Is there a way to change the servername without editing each query in each DTS package. I'd like to copy the template DTS package, then perform the modification.
Im trying to basically take an email address user@domain.com that is in a row and basically split it up or get 1 side.
Im trying to find the command if it exists or a way to do this in tran sql to basically give me everything left or right of the '@' symbol, is there an easy way of doing this? Thanks!
I have converted Access database to sql express. Access Database had AutoNumber FIelds for which trigger was generated by Upsizing wizard.
Now when I import data from client the autonumber field value changes because trigger is fired which distroys all links
I want to modify trigger so that it generates new number only when it is not supplied in a insert command.
Please help. Code is given below. Also suggest how to save because when I use save, it asks for a new .sql file name and a new file is generated instead of modifying the same trigger
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[T_AcControlLimit_ITrig] ON [dbo].[AcControlLimit] FOR INSERT AS SET NOCOUNT ON DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */ /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'IntAcControlCode' */ SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) SELECT @newc = (SELECT IntAcControlCode FROM inserted) UPDATE AcControlLimit SET IntAcControlCode = @randc WHERE IntAcControlCode = @newc
I have binded a DataGradView (DGV) with a Dataset (DS) , i execute and modify on the DataGradView the primary key (IDObj) and also the two other columns, by clicking on the UpDate button i call the function UpDateTable :
public void UpdateTable(string nameTable) { // con : ma connection OLE Ă un fichier Access 2003 // DS : dataSet // DAdp : Dataadapter // nameTable : nom de ma table
OleDbCommand comdUPDATE;
string CommandText = "UPDATE " + nameTable + " SET IDObj=@IDObj ,NameParent=@NameParent,TypeObj=@TypeObj WHERE IDObj=@IDObj";
comdUPDATE=DAdp.UpdateCommand = new OleDbCommand(CommandText, con);
Hello, I am using a component (infragistics netadvantage) within my application which uses a default database. Unfortunately this database doesn't provide me with all the functionality I need. Since the component uses stored procedures. Is there anyway I can see what stored procedures are called when an event occurs? That way I can copy over the tables and the stored procedures I need and I can alter them to suit my database.
Hi, sir, i have used Trace But it does not start automaticaly when server starts, is there any other way to track the Events, Because i have to track all the updates (Insert,Delete, UPdate etc.) of all the tables of my particuylar database .
When looking in the server trace in query analyzer – I can see how many ‘reads’ a stored procedure does. So I'm wondering if we can determine whether our query is good or bad by looking at the number of reads/writes that showing in the Trace.
When looking in the server trace in query analyzer – I can see how many ‘reads’ a stored procedure does. So I'm wondering if we can determine whether our query is good or bad by looking at the number of reads/writes that showing in the Trace.
I have decided that I needed to modify my trace file name to include date and time in a further attempt to make my file name unique. See below snippet:-
-- Build File Name
select @hr = datepart(hh,getdate())
select @min = datepart(mi,getdate())
select @sec = datepart(ss,getdate())
set @time = convert(char(2),@hr) + convert(char(2),@min) + convert(char(2),@sec)
We have replication set up. Publisher and distributor are on the same server, subscriber is in different server.
Suddenly the replication started failing and continued more than 12 hrs. When I tried to connect to the subscriber server, I am able to connect to the server but not able to run any queries or not able to open the error log from SSMS.
Is there any way to find from the default trace why the replication failed and the server is not responded?
An application that i wrote (that interacts with SQL Server 2000) is causing deadlocks on the server. i have no direct access to the server, but the user have sent me a server trace.
Having very little experience with SQL Server 2000, all that i learn from the trace is that, well, there is a deadlock, but i had already known that beforehand.
What other useful details can be interpreted from the trace above? How can i obtain information like the specific statement(s) which cause the deadlock, etc?
My problem is related to database and data. I building a project in ASP.NET 2.0 with the help of VWD in which i have certain records and each record has a unique id with it.Now my problem is that when a user wants to edit a certain record i want the user to enter a record id for that record. Now if the record id matches any record in the database then that record should be dislayed in another page with and edit option with it.
I have listed a view below and a portion of the result set that is returned when I run the code in Query Analyzer. This is part of a timesheet application that logs hours per SCHLSTUID per SECTIONID per week. This returns the SCHLSTUID(user's ID), SECTIONID, Date that the week starts, the first date that time was logged. The user could be in several SECTIONID's for the same week. I need to modify this so that it returns the date that the first time was logged for any of the SECTIONID's per week. I know that this is probably something simple that I'm overlooking but I just can't get it to work correctly.Example: SCHLSTUID SECTIONID ATTSTARTDT FirstTimeEnteredDOn601868445 EN4AR001 2005-09-18 20:59:21.120 2005-09-19 20:59:21.120601868445 MAA1R001 2005-09-18 20:59:21.120 2005-09-18 20:59:21.120This would need to return 2005-09-18 20:59:21.120------------------------------------------------------------------------------------------------------601868445 EN4AR001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 MAA1R001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427This would need to return either 2005-10-02 20:59:37.427------------------------------------------------------------------------------------------------------601868445 EN4AR001 2005-10-09 20:59:37.823 2005-10-09 20:59:37.823601868445 MAA1R001 2005-10-09 20:59:37.823 2005-10-13 20:59:37.823This would need to return 2005-10-09 20:59:37.823----------------------------------------------------------------------------------------------------------------------------------------------------CREATE VIEW dbo.vExportStartWeekASSELECT TOP 100 PERCENT schlstuid, sectionid, ATTSTARTDT, MIN(TimesheetDate) AS FirstTimeEnteredOnFROM (SELECT schlstuid, sectionid, ATTSTARTDT, ATTSTARTDT AS TimesheetDate, sunmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, ATTSTARTDT AS TimesheetDate, sunhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 1, ATTSTARTDT) AS TimesheetDate, monmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 1, ATTSTARTDT) AS TimesheetDate, monhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 2, ATTSTARTDT) AS TimesheetDate, tuemns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 2, ATTSTARTDT) AS TimesheetDate, tuehrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 3, ATTSTARTDT) AS TimesheetDate, wedmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 3, ATTSTARTDT) AS TimesheetDate, wedhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 4, ATTSTARTDT) AS TimesheetDate, Thrmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 4, ATTSTARTDT) AS TimesheetDate, Thrhrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 5, ATTSTARTDT) AS TimesheetDate, Frimns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 5, ATTSTARTDT) AS TimesheetDate, Frihrs AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 6, ATTSTARTDT) AS TimesheetDate, Satmns AS TimeEntered FROM TimeSheetDailyAttendance UNION ALL SELECT schlstuid, sectionid, ATTSTARTDT, DATEADD(d, 6, ATTSTARTDT) AS TimesheetDate, Sathrs AS TimeEntered FROM TimeSheetDailyAttendance) TimesheetDatesWHERE (TimeEntered <> 0)GROUP BY schlstuid, sectionid, ATTSTARTDTORDER BY schlstuid----------------------------------------------------------------------------------------------------------------------------------------------------This is a portion of what is returned:SCHLSTUID SECTIONID ATTSTARTDT FirstTimeEnteredDOn601868445 EN4AR001 2005-09-18 20:59:21.120 2005-09-19 20:59:21.120601868445 MAA1R001 2005-09-18 20:59:21.120 2005-09-18 20:59:21.120601868445 EN4AR001 2005-09-25 20:59:36.670 2005-09-25 20:59:36.670601868445 EN4AR001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 MAA1R001 2005-10-02 20:59:37.427 2005-10-02 20:59:37.427601868445 EN4AR001 2005-10-09 20:59:37.823 2005-10-09 20:59:37.823601868445 MAA1R001 2005-10-09 20:59:37.823 2005-10-13 20:59:37.823----------------------------------------------------------------------------------------------------------------------------------------------------Thank you for any help that you can give me.Scott
