SQL Server 2008 :: Inner Join Database Table With XML Data

Jan 25, 2011

I have a XML data passed on to the stored proc in the following format, and within the stored proc I am accessing the data of xml using the nodes() method

Here is an example of what i am doing

DECLARE @Participants XML
SET @Participants = '<ArrayOfEmployees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Employees EmpID="1" EmpName="abcd" />
<Employees EmpID="2" EmpName="efgh" />
</ArrayOfEmployees >'

SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)

the above query produces the following result

EmployeeID EmployeeName
--------------- -----------
1 abcd
2 efgh

How do I join the data coming out from the above query with another table in the database, for example Employee in this case

i.e. somewhat like this (note the following query does not perform the join correctly)

SELECT Participants.Node.value('@EmpID', 'INT') AS EmployeeID,
Participants.Node.value('@EmpName', 'VARCHAR(50)') AS EmployeeName
FROM
@Participants.nodes('/ArrayOfEmployees /Employees ') Participants (Node)
INNER JOIN
Employee EMP ON EmployeeID = EMP .EmployeeID

My desired output after the join would be

EmployeeID EmployeeName Email Home Address
--------------- ----------- --------------- -----------
1 abcd abcd@abcd.com New York
2 efgh efgh@efgh.com Austin

View 8 Replies


ADVERTISEMENT

SQL Server 2008 :: Recover Data In A Table - Restoring A Database Alongside Existing DB

Apr 17, 2015

I need to recover some data in a table but i'm not 100% sure the right way to do this safely.

I'll need to query the two tables to compare the before and after but how do i go about restoring/attaching the backup database to SQL without causing conflicts?

If I restore, I assume this would just overwrite which is obviously the worst thing that can happen. if i attach the backup, how does this affect the current live DB? how do i make sure that it's not getting accessed and mistaken for the live DB?

The SQL server is 2008 R2 running as a VM.

View 9 Replies View Related

SQL Server 2008 :: Join Another Table But With Select Conditions?

Mar 24, 2015

I have this sql....

Select
DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName,
s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType,
ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,

[code]....

however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'

So the stand alone sql could be....

Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc

I'm just not sure how to incorporate that into my sql or whether there is a better way,

View 9 Replies View Related

SQL Server 2008 :: How To Return 1 Particular Column From Another Table In Join

Apr 25, 2015

I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?

The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join

My photos table is constructed as follows:

CREATE TABLE [User].[User_Photos](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[PhotoId] [varchar](100) NOT NULL,
[IsProfilePic] [bit] NULL,

[Code] ....

Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck.

View 3 Replies View Related

SQL Server 2008 :: Join Two Tables With Specific Data

Jul 15, 2015

I have two queries from two different tables ex ABC and BCD. For table ABC, according to my query, I got 11 records ; for table BCD I only got 9 records.

Bottom line: I would like to see only 11 records from Table ABC including certain data from table BCD after I joined this two tables.

However, no matter what I did I always got 99 records when I joined.

View 3 Replies View Related

SQL Server 2008 :: Query To Union Join Data

Jul 30, 2015

I have a query which wants to union join the data. no matter how many times I tried, I got an error. How to change my union query?

select distinct b.lev5 AS "LEVEL 1",b.lev5NAME, C.lev7 "FUND", C.lev7NAME,round (sum(a.data),2) AS AMOUNT
(Select distinct b.lev5
from bf_data a
inner join bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: Inserting Data From Staging Table To Main Table

Feb 23, 2015

I 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 Related

How Can I Send Row Data To A SQL Database Table Via VB 2008, And Permanently Save And Display The Result?

Mar 11, 2008

This is related to:
How can I make some graphics drawings stick while others disappear?
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2905460&SiteID=1


Except that now I am trying to connect and update to an Microsoft SQL Server Database File (SqlClient) via VB 2008 Express; specifically a table called HexMap? that contains some columns that I am ready to insert some row data into. Here is what my program should do:

As I hover over a hexagon map of the US a red flickering hexagon follows the location of my mouse cursor. If I click on a given hexagon, the program draws a permanent blue hexagon, and sends a new set of row data into my database. Such information as the name of the state, row, column, center x, and center y, etc. Here is a quick snapshot of this program in action:

http://farm4.static.flickr.com/3128/2325675990_4155edbdee_o.jpg
-sorry, I didn't capture the mouse cursor inside the red hexagon

I think I am missing something since I appear to be able to connect successfully to the database table. Unfortunately, I never see the changes in the database, when I try to Show Table Data (via Database Explorer). I am hoping someone will review my code snippet (below) and tell me what I am missing. What happens when I run this code is that it acts like it works just fine, except that I have no indication that any changes were actually affected.




Code Snippet
'======================================================================================
Dim CN As New SqlClient.SqlConnection()
Dim da As New SqlClient.SqlDataAdapter

'Consider using Me._adapter that is used already

CN.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Mapboard.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
CN.Open()

'Use the following code to verify that a connection to the database has achieved
If CN.State = ConnectionState.Open Then


MsgBox("Workstation " & CN.WorkstationId & "connected to database " & CN.Database & "on the " & CN.DataSource & " server")
End If

Try

Catch ex As Exception MsgBox("FAILED TO OPEN CONNECTION TO DATABASE DUE TO THE FOLLOWING ERROR" & vbCrLf & ex.Message)
End Try

'use the Connection object to execute statements
'against the database and then close the connection
da = New SqlClient.SqlDataAdapter("select * from HexMap order by Territory", CN)

If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================

Dim rows As Integer

rows = 0

Dim CMD As New SqlCommand("INSERT HexMap (Hexagon, HexRow, HexCol, HexX, HexY, Territory) VALUES(HexCounter, CaptureRow,CaptureCol,Hx,Hy,Territory_ComboBox1.Text)", CN)

CN.Open()

rows = CMD.ExecuteNonQuery

If rows = 1 Then
MsgBox("Table HexMap updated successfully")
Else
MsgBox("Failed to update the HexMap table")
End If


If CN.State = ConnectionState.Open Then CN.Close()
'==========================================================================



Thanks for reviewing my code.

Technozoide

View 1 Replies View Related

SQL Server 2008 :: Replicating Table A Into Table B Within The Same Database

Jun 9, 2015

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.

View 2 Replies View Related

SQL Server 2008 :: Table Migration From One Database To Another

Jul 7, 2015

I'm trying to migrate tables from one database to another. The tables in the source database (MY_SOURCE_DB) have a schema of "dbo". I want to create the tables in the target database (MY_TARGET_DB) with a schema of "xyz". I want to migrate all of the tables with "contract" in the table name:

select table_name from information_schema.tables
where table_name like '%contract%'

I want to migrate the full table definitions, table data and dependent objects. This needs to be done through a sql script as opposed to SSIS.

View 2 Replies View Related

SQL Server 2008 :: How To Add A Record When Data Is Not In The Table

Feb 13, 2015

I have a report that summarizes hospital readmissions. Some months may only have a female or male patient that is readmitted but, I want to show both months either way.

create table dbo.Scott_TEST
(
YearMonth char(9),
Gender char(1),
NumOfFemale int,
NumOfMale int

[Code] ....

View 2 Replies View Related

SQL Server 2008 :: Pivot Data In Table?

Jun 24, 2015

MyTable has the following data (Just a sample)

parent | NAme | Checked | contactmethod|Check2 | Other
974198 | Employment | true | Face to Face | true | null
974224 | Other | true | Face to Face | true | skills
974224 | Other | true | Contact | true | skills

I'd like to pivot on "parent"

In a perfect world I'd like to see output like

974198 | Employment | true | Face to Face | true | null

974224 | Other | true | Face to Face, Collateral Contact | true | skills

If there are more than one name or contactmethod for the same parent then they would be strung along with commas

View 1 Replies View Related

SQL Server 2008 :: Update One Row With Data From Different Row In Same Table

Sep 11, 2015

I need to update the Denominator column in one row with the value from the Numerator column in a different row. For example the last row in the table is

c010A92NULL

I need to update the Denominator, which is currently NULL, with the value from the Numerator where the MeasureID=c001 and GroupID=A.

This value is 668 so, the row should look like

c010A92668

create table dbo.TEST
(
MeasureID varchar(10),
GroupID char(1),
Numerator float,
Denominator float
)

[Code] .....

View 7 Replies View Related

SQL Server 2008 :: Queries Running Against To A Table In Database

Jun 15, 2015

How can we get most frequent queries that are running against to a table in our database?

View 3 Replies View Related

SQL Server 2008 :: Bulk Insert Data Into Table

Mar 23, 2015

I want to bulk insert data into a table named scd_event_tab inside a database named rdb.

When I do select * from rdb.dbo.scd_event_tab, i get :

JOB_ID RUN_ONPRIORITYPAYLOADTIMEOUT_INTERVALSTATUSPICKUP_TIMESCD_TYPESCHEDULE_IDDB_ADMIN_LOGIN_REQUIRED_YN

I saved the result into a csv file and then truncated the table. Now, I am trying to bulk insert the data into the table. So I used:

bulk insert
rdb.dbo.scd_event_tab from 'C:userssluintel.ctrdesktopeventtab.csv'
with
(
codepage = 'RAW',
datafiletype = 'native',
fieldterminator = ' ',
keepidentity,
keepnulls
);
go

However, I get this error:

Msg 4867, Level 16, State 1, Line 1
Bulk load data conversion error (overflow) for row 1, column 1 (JOB_ID).
Msg 4866, Level 16, State 5, Line 1

The bulk load failed. The column is too long in the data file for row 1, column 3. Verify that the field terminator and row terminator are specified correctly.

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

View 9 Replies View Related

SQL Server 2008 :: Where Clause - Look For 5 Records Data In A Table

Apr 14, 2015

I have a query needs to look for 5 records data in a table. Basically i need to hardcode. Below is my query which didn't work out.

select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD,BF_BDOB_CD,BF_TM_PERD_CD) in ***** i guess this is the wrong query****
('A1', 'B1', 'C1')
('A2', 'B2', 'C2')
('A3', 'B3', 'C3')
('A4', 'B4', 'C4')
('A5', 'B5', 'C5')

but if i use the query below it will generate more records than these 5 records

select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD) in ('A1', 'A2', 'A3', 'A4', 'A5')
and (BF_BDOB_CD) in ('B1', 'B2', 'B3', 'B4', 'B5')
and (BF_TM_PERD_CD) in ('C1', 'C2', 'C3', 'C4', 'C5')

View 4 Replies View Related

SQL Server 2008 :: Creation Of XML File With Data In Table

Jun 1, 2015

I want to create a XML file with data in my table. I have a question about tags.

SELECT
-- Root element attributes
'http://tempuri.org/Form.xsd' AS 'xmlns',
'http://www.w3.org/2001/XMLSchema-instance' AS 'xmlns:xsd',
(
SELECT
-- Creating a default element

[Code] ....

This is my query. When I use 'xmlns' namespace the result is below:

<Form xmlns="http://tempuri.org/Form.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri xmlns="">
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>

I dont want to see 'xmlns' in 'Veri' tag. When I use xmlna or another word instead of 'xmlns' the result is below:

<Form xmlns="http://tempuri.org/Form.xsd" xmlna:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri>
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>

How can I remove 'xmlns' from 'Veri' tag while using it in 'Form' tag

View 2 Replies View Related

SQL Server 2008 :: Getting Duplicate Data When Connecting To Another Table

Jul 25, 2015

I have the following two tables:

CREATE TABLE [MailBox].[Message](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SenderId] [bigint] NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[SentDate] [datetime] NOT NULL,
CONSTRAINT [PK_MailBox.Message] PRIMARY KEY CLUSTERED

[Code] ....

I'm building a messaging functionality in to my application, I'm able to insert a message into the database and this message then appears inside the other users inbox. The issue I have it when I click on this message to view the conversation I make a call to the following sp as shown here:

@UserId bigint,
@SenderId bigint
AS
BEGIN
SET NOCOUNT ON;

[Code] .....

The problem with this is I'm trying to connect to the user photos table to return their profile picture, but for some reason even though I have specified IsProfilePic I get all the photos returned, instead it should be two photos, one for the @UserId and the other for the @SenderId, its equivalent to me doing this:

Select *
From [User].[User_Photos]
where (UserId = 1 or UserId = 2) and IsProfilePic = 1

And this returns me the correct information.

View 3 Replies View Related

SQL Server 2008 :: Import Table Data From Production To Development Server

Feb 18, 2015

Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?

View 3 Replies View Related

SQL Server 2008 :: How To Insert Blog Data Into A Database

Sep 28, 2015

I've never worked with a column that's defined as a binary data type. (In the past if ever we had to include a photo we stored a link to the JPG/BMP/PNG/whatever into a column, but never actually inserted or updated a column with binary data.But now I've got to do that. So how do you put data into a column defined as BINARY(4096)?

View 3 Replies View Related

SQL Server 2008 :: Transmission Table Structure In Database Is Inconsistent

Jun 1, 2015

I have an issue in my production , SQL server 2008 R2 , in Error log it is said “ An error occurred in dialog transmission Error : 9655 , State : 3 and Error : 9736 Severity : 16 State 0

I checked in sysmessages , it is said :” The transmission queue table structure in database is inconsistent. Possible database corruption “

So do you think I need to run DBCC checkdb ? Should I run it after working hours or it is fine within working hours ?

View 9 Replies View Related

SQL Server 2008 :: Convert Data From 2 Tables To 1 Table With Transposing

Feb 9, 2015

There are 2 tables

1. Table Name : TRNNUM Field Name : TRNID
2. Table Name : TRNPRD Field Name : TRNID, PRDID

Sample of tables
Table TRNNUM
TRNID
-----
1234
2565
3458

Table TRNPRD
TRNID PRDID
----- -----
1234 AA
1234 BB
1234 CC
2565 CC
2565 EE
2565 FF
2565 HH
3458 AA
3458 BB

Desired output is
TempTable

TRNID PRDID PRDSEQ
----- ----- ------
1234 AA START
1234 BB START:AA
1234 CC START:AA:BB
2565 CC START
2565 EE START:CC
2565 FF START:CC:EE
2565 HH START:CC:EE:FF
3458 AA START
3458 BB START:AA

View 2 Replies View Related

SQL Server 2008 :: Storing Images As Binary Data In Table

Feb 25, 2015

I want to store Images as binary data in SQL table and compare it each time with a image file I am getting. I've tried below approach but getting error:

DROP TABLE #BLOBTest
CREATE TABLE #BLOBTest
(
TestID int IDENTITY(1,1),
BLOBName varChar(50),
BLOBData varBinary(MAX)
);

[Code] ....

Error: Msg 4861, Level 16, State 1, Line 10
Cannot bulk load because the file "C:Files12656.jpg" could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105).

View 4 Replies View Related

SQL Server 2008 :: Loading Data Into New Partition Table Online

Mar 1, 2015

When you load the data into a new partition table, can it to done online without any downtime? because I have few tables that are around 250 gigs and more.

View 5 Replies View Related

SQL Server 2008 :: Aggregating Data From Common Table Expression

Apr 2, 2015

I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.

********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';

WITH CTE AS (
SELECT bkg_nbr

[Code] ....

The raw data from the CTE is:

Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1

The result of the query above was:

Category RecCount
Non-Recidivists3
Recidivists 1

The outcome I was looking for was:

Category RecCount
Non-Recidivists2
Recidivists 1

What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.

View 2 Replies View Related

SQL Server 2008 :: Add A Trigger That Inserts Original Data From 1 Table To Another

Apr 10, 2015

I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,

[Code] ....

When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,

[Code] .....

The trigger I've currently written looks like this:

/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]

[Code] ...

This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?

I can't sort by uniqueidentifier in descending as those can be random.

View 9 Replies View Related

SQL Server 2008 :: Compare Data Between Identical Table Structure?

Jun 18, 2015

I have two identical table structure with at least 60 or more columns!!

Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....

I need to compare Data between two table and provide result like this!

RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0

So condition like. If table A Column Data does not Equal Table B Column data then

If datatype is Date then display Difference of Days.

if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)

if Datatype is Varchar then display No Match word.

View 0 Replies View Related

SQL Server 2008 :: How To See Table Variable Data In SSMS Debugger

Jul 28, 2015

is there a way to see the data of a table variable in the SSMS debugger? For example, if I set a breakpoint in SSMS and look at a populated table variable named @MyTable in the Locals tab at the bottom of the IDE, a value of "(table)" is displayed. There does not appear to be a way to expand or drill into this variable in the debugger to see the data. Do you know if there's a way to do this through the debugger or do you use an alternate approach when using the SSMS debugger?

View 1 Replies View Related

How Can I Get The 'entire' Database, Structure And Data, From SQL Server 2008 To SQL Server 2005?

Jun 20, 2008

1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.

View 2 Replies View Related

SQL Server 2008 :: Capture Database / Server Name In A Derived Column To Identify Source Of Data?

Feb 1, 2012

I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.

When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.

The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.

For Example I have Products table on Server 1 and 2:

Server 2 has more Products and would like to join the two together to create a staging table.

I want see the following:

Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2

get database name and server name in DATA FLOW only (without using a for each in Control Flow)

View 5 Replies View Related

SQL Server 2008 :: Restoring Database - Delete Data From Partitions

Feb 20, 2015

I am restoring a database with 10yrs worth of data which have monthly partitions but i would like to keep only 5yrs of data after the restore is done, what is the best/faster approach to delete the 5yrs data without deleting the partitions as that may cause the db in accessible.

View 9 Replies View Related

SQL Server 2008 :: Masking / Replacing Sensitive Data In Database?

Oct 8, 2015

when prod db is restored in test server, i have to hide/replace sensitive fields (name, phone etc)Is it doable in SQL 2008 and SQL 2012?

View 5 Replies View Related

SQL Server 2008 :: Does Change Data Capture Require A Table Have Primary Key

Jan 13, 2013

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.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved