I created am inventory table with few columns say, Servername, version, patching details, etc
I want a tracking of the table.
Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.
Or can it record before and after column changes based on the LSN only?
An extract from a file based legacy accounting system is performed every night. The system does not have a primary key because transactions are managed through program code. (the more things change...). The extract is copied to text in Unix and FTP'd to Windows, where the file is loaded into SQL Server by kill & fill. Because of the expense of modifying the source system, there is enormous inertia/resistance to injecting a primary key at the source, so kill & fill it stays.
In reading about Change Data Capture, it seemed to me that column level insert update and delete are stored in tables that remember the before and after content of each column tracked. In my reading I have seen many references to the LSN to decide when and what to record as changed, but I have not seen any refereference to the necessity of a primary key for Change Data Capture to work. This is in contrast to replication, where the requirement for the existence of a primary key is made plain.
Is it possible to use Change Data Capture against a table without a primary key? How to use it to change the extract from kill and fill to incremental.
I need to capture changes in my table and store it in another table. Here is how my table would look like.
CREATE TABLE [dbo].[ABC](
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
[Code] ....
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
100 02014-12-20 00:55:44.667
100 12014-12-22 00:55:44.723
100 02014-12-24 00:55:44.723
100 12014-12-26 00:55:44.723
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
Is there a way to find the max number of sessions that were on a sql server since its last restart? I know how to get how many total connections there were since last restart, and the current number of sessions, but not the max number of sessions that were running since last restart. Also, opinion on the optinum number of allowable sessions, or a good way to figure out the balance?
View 0 Replies View RelatedWe have a big software that run a warehouse distribution center, written in .NET Backhand is a SQL Server 2008 R2 STD database.
Now, it seems there is a problem with the sessions not being properly closed after each call to the DB. Here is the message got form SQL:
DESCRIPTION:A new connection was rejected because the maximum number of connections on session ID 57 has been reached. Close an existing connection on this session and retry.
In the .NET code, connection is made with the following code:
If oConn Is Nothing Then oConn = New SqlConnection
If oConn.State = ConnectionState.Open Then oConn.Close()
With oConn
.ConnectionString = "Server=" & Server & ";Database=" & DB & ";User ID=" & User & ";Password=" & Pass & ";Connection Timeout=" & 5 & ";MultipleActiveResultSets=" & True
.Open()
End With
This code is called once, at the opening of the software
StoredProc are call with the code:
Try
oCmd = New SqlCommand
With oCmd
.Connection = oConn
.CommandType = CommandType.StoredProcedure
[Code] .....
So every Command is closed after execution, yet, they stay active in the SQL Server. Is there something I'm missing here?
I have the following data
MASTER
id
name
DETAIL
id
master_id
name
I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.
How can I do that???
Hi
Any one please tell me is there any possible way to identify the table modified date.
I have checked the table created date from sysobjects or by right click properties. my requirement is to identify the exact date of table modification and column creation,alter dates.
Is there any such provision in sql server 2000 or 2005 , My application is in sql server 2000.
I need to confirm this because some database structure modification has affected my application and causing dataloss i need to check with the date of structural change of table and lost data date
can any one help
I want to export the datas of a table into a excel file using an Sql Query.
When I execute that query the Excel file should be created automatically in the given path with all the datas in that table.
Can anybody give me a solution for this problem?
I have created a linked server using my local SQL2005. The linked server can be seen as a linked server, but the database can not been expanded to see the tables, stored procedures, views, and other details regarding the linked server. The only method that I have found that will allow me to see all the details is to use XP's Remote Desktop Connection to access the database. Has anyone else experienced this problem, if so, how did you resolve it?
View 1 Replies View RelatedHello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern
View 1 Replies View RelatedI am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
View 2 Replies View RelatedI would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique
EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value
Here is the sample data for table.
create table #sample (emp varchar(max),data1 varchar(max),data2 varchar(max), Rdate date)
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate())
insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate())
[Code] ...
I need to update the data1, data2 from yesterday or before but they should be non blank and non null and update to todays data.
It means, I can't update to 2015-03-12 values to 2015-03-13, as they are blanks or nulls.
So I need to go 2015-03-11 and check the values, if they are not blank , i need to update with todays values.
Before the update :
empdata1data2Rdate
john3/13/2015
tony3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
After the update:
empdata1data2Rdate
johncd3/13/2015
tonyab3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?
View 2 Replies View RelatedI have a scenario where I have to Update a table with date when there are new records in another table
For example:
I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.
Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?
Hi
I have came across a table in SQL server 2000 which named 'Order Details' in the sample 'Northwind' database which is available with the product. Am using the eval version . Generally no table name exist with a 'space' between the words. But the table 'Order Details' exist in the Northwind sample database.
Due to the naming convention i can't run sql queries on that. Is anyone aware of this type of issues.
Pls help
I have two table 'Cal_date' and 'RPT_Invoice_Shipped'.Table cal_data has column month_no, start_date and end_date. And table RPT_Invoice_Shipped has columns Day_No, Date, Div_code, Total_Invoiced, Shipped_Value, Line_Shipped, Unit_Shipped, Transaction_Date.
I am using below insert statment to insert data in RPT_Invoice_Shipped table.
insert into [Global_Report_Staging].[dbo].[RPT_Invoice_Shipped]
(Day_No, Date, Div_code, Total_Invoiced, Transaction_Date)
select , CONVERT(DATE,Getdate()) as Date, LTRIM(RTRIM(div_Code)),
sum(tot_Net_Amt) as Total_Invoiced, (dateadd(day, -1, convert(date, getdate())))
from [Global_Report_Staging].[dbo].[STG_Shipped_Invoiced]
WHERE CONVERT(DATE,Created_date )=CONVERT(DATE,Getdate())
group by div_code
while inserting in column Day_No in RPT_Invoice_Shipped table, I have to use formula (Transaction_Date-start_date+1) where Transaction_Date from STG_Shipped_Invoiced and start_date from Cal_date table. I was using datepart (mm, Transaction_Date) so it gives month_no, and this month_no we can join with month_no of Cal_date table and fetch start_date from Cal_date table, so that we can use start_date for formula (Transaction_Date-start_date+1).
But I am getting difficulty to arrange this in above query. how to achieve this?
I am trying to replicated table A into table B withing the same server and the same database.
I am getting an error message when trying to configure the subscription: You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
Is there any workaround for this?
I am using SQL Server 2008 R2.
One of our Application creating run time SQL job to run batch process and application itself deleting this run time SQL job, but when doing deletion it’s not checking whether job is running or not, just doing direct delete. Our challenge is how to capture deleted job details, after incident happen we could see error details only in this log.
We can run profile to trace but we don’t know when it will trigger and we cannot possible keep active profiler as it will kill server.so we can't run the trace for log time as we don't know when the issue happens.
I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it
I need that query for alter table
in it
reply me soon
does anyone know how to modify table structure in sql server management console(2005)? thanks
View 1 Replies View RelatedI have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
Hi all,
Sql server 7
Is there anyway to findout if list of tables that has been modified after august 2003 in a database.
Waiting for ur reply.
TIA
Adil
Script to find the details of creation date and modified date of all files located in a path?
I wanna write few custom messages before I delete some files from a path.
Does anyone know how to get a table's 'last modified date' in SQL 7 ?
Sysobjects contains the 'create date', but I can't find a 'last modified date' anywhere......
Hello All,I would like to get last modified timestamp for a table. Is there any way toget that information using any commands?Thanks in Advance,Muthu.
View 2 Replies View RelatedHi,
How to find out the Last Modified (Structure) in the SQL Table?. Is there any query please let me know.
Regards,
S.Balavenkatesh
Hi Everybody,
i am it sutck when i am writing this sql in sqlserver2005
my requirement is to list all the table name and along with their filed name if some one modified (table definition ) for a specifc date range ?
is there any way to get these information in sql server 2005 ?
regards
sujithf
I am looking for two sql scripts,1. How can i check when a tables structure was last modified? 2. How can i find the structure differences between two databases that were created the same but changed over time individualy?Looking for sql scripts to do this. Any help on either would be great.Thanks
View 2 Replies View RelatedHi
View 9 Replies View RelatedHi all,
What is the best way to show when a table was last MODIFIED?
Thanks,
Stan
Hello again,
Two Short questions this time,
I have a table with several indexes, currently most of them are very narrow (one column), and the question is, when I modify the table by updating a record, does all the indexes are calculated again?? Even if the modified field isn't indexed??? Or the server is smart and knows what indexes to calculate if any.
Second question, can I give to a query a low priority(In dynamic SQL), for example when I don't want my query to exploit too many system resources so it won't interfere the main system ?
Inon.