knowing which jobs/dts/SSIS affect which tables

hi, we were doing something and began to wonder which jobs/dts were
affecting a certain table (we use SQL 2000)... is there anyway to check
this?

and have the features for such dependency analysis been added for SQL
2005?I've never tried this, but you might be able to trap it using Profiler;
set up a log and filter on object_id.

http://www.microsoft.com/technet/ar...9.mspx?mfr=true

Post back and let us know if that works for you.

Stu

metaperl wrote:
> hi, we were doing something and began to wonder which jobs/dts were
> affecting a certain table (we use SQL 2000)... is there anyway to check
> this?
> and have the features for such dependency analysis been added for SQL
> 2005?sql

Kirk: Importing/Exporting with column ErrorCode, ErrorColumns

I am currently redirecting lookup failures into error tables with ErrorCode and ErrorColumn. It works fine until I want to transfer data into the archived database. The SSIS pacakage generate by SQL Exporting tool is throwing an "duplicate name of 'output column ErrorCode and ErrorColumn" error. This is caused by oledb source error output. The error output automatically add ErrorCode and ErrorColumn to the error output selection and not happy with it.

I think the question is down to "How to importing/exporting data when table contains ErrorCode or ErrorColumn column?"

Can you not use the derived column component to create 2 differntly named rows containing the same data?

-Jamie

|||

Yes, we can use different name and map them on ole db destination when writing to the error tables. We really don't want to go that way unless there is no other option.

Currently it if failing on the first step of the Data Flow, OLE DB Source, it is not reaching Derived column Transformation, and the build in SQL import/export is not working because of the same issue.

It will be good to verify so we can enhence in our sql naming standard. "Don't use ErrorCode or ErrorColumn as column name in the table; otherwise you can't use sql import/exprot tool. They are reserved keywords in SSIS."

-tianyu

|||

Are you inserting into a database table? If so then of course you cannot do this - a table cannot have 2 columns with the same name.

That doesn't mean that you can't insert identically named pipeline columns into that table. You just have to set up the mappings correctly in the destination adapter.

Have I misunderstood the problem?

-Jamie

|||

Use case for my question,

In PayRoll package, data failed username lookup redirect to Error_Dim_PayRoll table during the process. Later on I want to export the error rows to an archiving database. When you use the SQL exporting tool, the wizard will fail and complaint duplicate ErrorCode and ErrorColumn.

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream.

This is based on default settings for both SQL 2005 and SSIS.

Repro Steps:

1. Create ErrorDB and ErrorDB_Reporting
2. Create Error_Dim_PayRoll tables for both database (script included bellow)
3. Run the Insert statement in ErrorDB
4. Run SQL Import/Export tool to export the row from ErrorDB to ErrorDB_Reporting (you can save generated SSIS package somewhere)
5. You will get complaints and export fails
6. Run the generated package, still fails with duplicate column name error.

CREATE TABLE [dbo].[Error_Dim_PayRoll](
[UserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorCode] [int] NULL,
[ErrorColumn] [int] NULL, [FailureReason] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

INSERT INTO Error_Dim_Customer (UserName, ErrorCode, ErrorColumn, FailureReason) VALUES(NULL, 1, 1, 'Can not lookup UserKey from Employee table by UserName')
GO

|||

Tianyu Li wrote:

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream.

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream which conflict the columns come in from the data source.

Kind of cross-tab query

Hi
I'd like to get the results below from this sort of data. I might also have
additional tables that need another 'prods' type column.
Thanks
Andrew
declare @.docs table (docID int primary key, docname varchar(25))
declare @.prods table (prodID int primary key, docID int, prodname
varchar(25))
insert @.docs values (1, 'doc1')
insert @.docs values (2, 'doc2')
insert @.docs values (3, 'doc3')
insert @.prods values (1, 1, 'prod1')
insert @.prods values (2, 2, 'prod2')
insert @.prods values (3, 1, 'prod3')
insert @.prods values (4, 2, 'prod4')
insert @.prods values (5, 3, 'prod5')
insert @.prods values (6, 2, 'prod6')
/*
docID docname prods
-- -- --
1 doc1 prod1, prod3
2 doc2 prod2, prod4, prod6
3 doc3 prod5
*/In general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the workarounds for forcing this at the server, you can check out
the following links:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||For all types of static and dynamic crosstabs server side it's recommended
you check out RAC.Powerful and easy.
www.rac4sql.net

Kimball Templates

When using the Kimball templates for designing Dimension tables, there are 3
columns I have questions about.
Isn't the RowStartDate and RowEndDate the way to manage historical values?
If so; why is the need for RowIsCurrent?
In my example dimension below, it is capturing slow changing phone numbers.
Is the RowIsCurrent just a better faster way to find the most current recor
ds instead of querying the most recent dates?
Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCu
rrent
Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
Joe Momma (360) 331-8800 2/18/2005 12/31/9999 YOn Apr 12, 7:42 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> When using the Kimball templates for designing Dimension tables, there are
3 columns I have questions about.
> Isn't the RowStartDate and RowEndDate the way to manage historical values?
If so; why is the need for RowIsCurrent?
> In my example dimension below, it is capturing slow changing phone numbers
. Is the RowIsCurrent just a better faster way to find the most current rec
ords instead of querying the most recent dates?
> Client Name: Phone Number: RowStartDate: RowEndDate; RowIs
Current
> Joe Momma (360) 533-3232 2/1/2005 2/18/2005
N
> Greg Olson (360) 822-2323 3/4/2005 12/31/9999
Y
> Joe Momma (360) 331-8800 2/18/2005 12/31/9999
Y
Joe,
if you think about the sql require to get 'the most recent record'
using a date versus using a flag you will see why we have the
flags....if you don't want to just accept that this is how it is
done...write the sql and check it out.
Best Regards
Peter
www.peternolan.com

Kimball Templates

When using the Kimball templates for designing Dimension tables, there are 3 columns I have questions about.
Isn't the RowStartDate and RowEndDate the way to manage historical values? If so; why is the need for RowIsCurrent?
In my example dimension below, it is capturing slow changing phone numbers. Is the RowIsCurrent just a better faster way to find the most current records instead of querying the most recent dates?
Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCurrent
Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
Joe Momma (360) 331-8800 2/18/2005 12/31/9999 Y
On Apr 12, 7:42 pm, "Joe" <hortoris...@.gmail.dot.com> wrote:
> When using the Kimball templates for designing Dimension tables, there are 3 columns I have questions about.
> Isn't the RowStartDate and RowEndDate the way to manage historical values? If so; why is the need for RowIsCurrent?
> In my example dimension below, it is capturing slow changing phone numbers. Is the RowIsCurrent just a better faster way to find the most current records instead of querying the most recent dates?
> Client Name: Phone Number: RowStartDate: RowEndDate; RowIsCurrent
> Joe Momma (360) 533-3232 2/1/2005 2/18/2005 N
> Greg Olson (360) 822-2323 3/4/2005 12/31/9999 Y
> Joe Momma (360) 331-8800 2/18/2005 12/31/9999 Y
Joe,
if you think about the sql require to get 'the most recent record'
using a date versus using a flag you will see why we have the
flags....if you don't want to just accept that this is how it is
done...write the sql and check it out.
Best Regards
Peter
www.peternolan.com

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
TeoI has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
Teo
I has this case at a customer site just yesterday. It turned out that the table they inserted into
had a trigger which executed an extended stored procedure. If any external (to SQL Server) code
becomes hung, you cannot kill that SPID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can see
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo
|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!
|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.

Kill command don't work

I've this problem:
A process (SPID 62) with some locks on some tables. The application which
had generated the query are not running, is closed! In SQL server I can see
the SPI 62 that are blocking other users.
I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
I need to restare sql server to solve my problem.
The query that SPID62 are running is a normal INSERT INTO without any
problems or other...
How may I do to obtain more information around the fact that the SQL KILL
command not work?
I've already tried with KILL 62 WITH: Kill 62 with status only and I obtain
: "Extimated... rollbak 0%,... 0 minutes"
But the process rest in rollback and not stop.
and why SQL server if the client go off not stop this process itself?
thanks in advance.
TeoI has this case at a customer site just yesterday. It turned out that the ta
ble they inserted into
had a trigger which executed an extended stored procedure. If any external (
to SQL Server) code
becomes hung, you cannot kill that SPID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Teo(I)" <TeoI@.discussions.microsoft.com> wrote in message
news:73DE3473-DFAC-4C77-A645-057871F3DDEC@.microsoft.com...
> I've this problem:
> A process (SPID 62) with some locks on some tables. The application which
> had generated the query are not running, is closed! In SQL server I can se
e
> the SPI 62 that are blocking other users.
> I try to kill the process (KILL 62) but nothing! The SPID 62 not die!
> I need to restare sql server to solve my problem.
> The query that SPID62 are running is a normal INSERT INTO without any
> problems or other...
> How may I do to obtain more information around the fact that the SQL KILL
> command not work?
> I've already tried with KILL 62 WITH: Kill 62 with status only and I obtai
n
> : "Extimated... rollbak 0%,... 0 minutes"
> But the process rest in rollback and not stop.
> and why SQL server if the client go off not stop this process itself?
> thanks in advance.
> Teo|||DBCC traceon 3604 on SPID 62:
Process id 62 killed by hostname SERVER01, host process ID 6816...
but this isn't true! the spid is alive..!!!
If I retry i obtain:
Process id 62 killed by hostname SERVER01, host process ID 6816...
I don't understand!|||well... I've a trigger too... But my trigger do another insert in a second
table (backup table) on a different db in the same istance. In this case a
rollback or kill must work.sql

Key column information is insufficient ....II

This is the case... I would like to learn the statement that make the
relation between these tables.
Why? Cos these are separated in two different databases and if a user
make an update in a table from database X these changes must to be
applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a
user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were
affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Leonardo Almeida" <leonardoalmeida2004@.yahoo.com.br> wrote in message
news:3f674b6d$0$62079$75868355@.news.frii.net...
>
> This is the case... I would like to learn the statement that make the
> relation between these tables.
> Why? Cos these are separated in two different databases and if a user
> make an update in a table from database X these changes must to be
> applied in the other table in the another database:
> The tables are :
> Principal Database Name : Server Information 2004
> Table Name : Clients
> Fields : ID_Client, Client
> Secondary Database Name : Index2003
> Table Name : Contratos
> Fields : ID_Con, ID_Client, Client
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
> If somebody can help me THANKS A LOT OF...
> Leonardo Almeida
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

See my reply to your previous post.

Simon|||Leonardo Almeida (leonardoalmeida2004@.yahoo.com.br) writes:
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."

Include a SET NOCOUNT ON first in the trigger. If that does not help,
remove the trigger and run the update again. I would expect in such
case that you get the error anyway. Which would indicate that the error
is in the client code which you did not show us.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What code are you talking about?

In the client code I use Delphi + ADO

ADOTable1.Open;
ADOTable1.Edit;

Now edit the Client registrer

Post the register with the command:

ADOTable1.Post;

The message arise again...
and each table has a primary key...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Leonardo Almeida (leonardoalmeida2004@.yahoo.com.br) writes:
> What code are you talking about?

The SET NOCOUNT ON command should be added to your trigger.

> In the client code I use Delphi + ADO
> ADOTable1.Open;
> ADOTable1.Edit;
> Now edit the Client registrer
> Post the register with the command:
> ADOTable1.Post;
> The message arise again...
> and each table has a primary key...

There is no .Post method in ADO, so I conclude that this is something
Delphi-specific, and I don't know Delphi.

If SET NOCOUNT ON did not help, I can only suggest to use the Profiler
to see what is going on behind the covers.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Leonardo,

Your trigger doesn't look correct to me! Why are you using Clients
table in your trigger? In order to update Contratos table using new
values in Clients, you need to use inserted table not Clients.
Something like this:

create trigger Update_Clients on Clients
for update
as
update Contratos
set Client = inserted.Client
from Contratos join inserted
on Contratos.ID_Client = inserted.ID_Client

In your current trigger, you are dealing with 3 tables (Contratos,
Clients & inserted) without joining them correctly. So when you try to
update Client field of Contratos table, it finds more than one value
in inserted table which causes that problem.
I hope this one works fine. I didn't test it...

Good Luck,
Shervin

Leonardo Almeida <leonardoalmeida2004@.yahoo.com.br> wrote in message news:<3f674b6d$0$62079$75868355@.news.frii.net>...
> This is the case... I would like to learn the statement that make the
> relation between these tables.
> Why? Cos these are separated in two different databases and if a user
> make an update in a table from database X these changes must to be
> applied in the other table in the another database:
> The tables are :
> Principal Database Name : Server Information 2004
> Table Name : Clients
> Fields : ID_Client, Client
> Secondary Database Name : Index2003
> Table Name : Contratos
> Fields : ID_Con, ID_Client, Client
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
> If somebody can help me THANKS A LOT OF...
> Leonardo Almeida
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Key column information is insufficient ....

This is the case... I would like to learn the statement that make the
relation between these tables.
Why? Cos these are separated in two different databases and if a user
make an update in a table from database X these changes must to be
applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a
user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were
affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hey Leonardo;

I noticed that there are no replies...I am getting exactly the same
error but cannot find a solution, nor a workaround. What did you
finally wind up doing? Please reply to my email directly rtodd@.metro.ca

I have similar triggers, they all work when I update my tables directly
in SQL Server. When I move to my ACCESS ADP and perform the same
updates directly in the same table, I get the same error message.

If anyone can help us....please do !

--
Posted via http://dbforums.com

KeepTogether not working.

Hi all,

I am using RS2005.
KeepTogether=true is not working both in the Preview tab nor when I export to PDF.
My tables still get split by a page break.
RepeatHeaderOnNewPage works as expected.
For a while I was simply putting each table on its own page, but this is such a tacky solution.
Any ideas?

As of now, repeating header on every new page is the only solution.

Shyam

|||

OK.

Thanks Shyam.

|||

So, can you please mark the post as answer?

Shyam

KeepTogether not working.

Hi all,

I am using RS2005.
KeepTogether=true is not working both in the Preview tab nor when I export to PDF.
My tables still get split by a page break.
RepeatHeaderOnNewPage works as expected.
For a while I was simply putting each table on its own page, but this is such a tacky solution.
Any ideas?

As of now, repeating header on every new page is the only solution.

Shyam

|||

OK.

Thanks Shyam.

|||

So, can you please mark the post as answer?

Shyam

Keeping track of last time of extract

I am building an SSIS package in which I extract data from a number of transaction tables. The rows in each transaction table contain a timestamp. I would like to do the following.

* Every time I extract rows from a table I want to extract only the records added since last extract (no rows are modified or deleted - only added)
* In the extract process I want to find the maximum timestamp in a given table. This timestamp should be stored and updated in a table on the SQL Server. This way, the next time the extraction process is run only "new" rows are extracted

How would you go about doing this in SSIS? I have thought of the following approach, but I am unsure whether it is too cumbersome.

* A package variable is defined for each of the transaction tables
* A series of Execute SQL Tasks populates each of these variables with the timestamps from the table in SQL Server
* A series of data flow tasks extract the data (using the variables populated above in the WHERE-condition). Contained in the data flow tasks is a script component which records the maximum timestamp extracted and places this in yet another package variable (I am unsure how to do this, by the way...)
* A series of Execute SQL Tasks updates the table in SQL Server with the new timestamps

How would you do this?

Thanx! This is how I'd do it. It isn't particularly a SSIS solution...just the way I'd do it!

Have 2 values stored in a table called (e.g.) tblConfig. They are:
LastExtractDate
ThisExtractDate

Step 1) UPDATE tblConfig SET LastExtractDate=ThisExtractDate, ThisExtractDate = GETDATE()

Step 2) SELECT * FROM <source_table> WHERE <some_tstamp_value> >= LastExtractDate AND <some_tstamp_value> < ThisExtractDate> (this would be inside a data-flow source adapter)

OK, column names may change etc...but you get the idea!!!

-Jamie|||Very nice... And simple. One question though (for doing this in SSIS): Do you enlist the two tasks in the same transaction (using a sequence container for instance) so that if the data flow tasks fails, you will not "miss" any rows on the next run?

/Michael|||

Reckless wrote:

Very nice... And simple. One question though (for doing this in SSIS): Do you enlist the two tasks in the same transaction (using a sequence container for instance) so that if the data flow tasks fails, you will not "miss" any rows on the next run?

/Michael

Yeah. Good idea!

-Jamie|||Hi, folks:
Exactly how do we do this. Do we do a OLE data source > something > OLE data destination. Thanks in advance.|||

Al_chan wrote:

Hi, folks:
Exactly how do we do this. Do we do a OLE data source > something > OLE data destination. Thanks in advance.

Hi Al,
Step 1 I would do in an Exec SQL Task.
Step 2 most likely in a data flow - yes, using an OLE DB Data Source.

-Jamie

Keeping Tables Sorted

we have a simple table

Key, Name, Address, City, State, Zip ............ect

I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.

I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.

Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.

Appleby
Jones
Robertson

This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.

thanks in advance -arthurCreate a clustered index for the table using the Name field:

1. In Enterprise Manger right-click the table and select All Tasks->Manage Indexes
2. Click "New..."
3. Select the Name field and give the index a name (e.g., Name). Check the box near the bottom marked "Clustered index". Click OK.

What this does is physically place the records in the table sorted according to the index. This isn't a free lunch however as the time taken to insert a new record will be a bit longer than it was before the index.|||THanks for the quick response, unfortantly, I don't have Enterprise manager (God I miss working for a big guy) Anyway .............., I do have something called SQL ExecMS, which dosn't do much more than allow me to view my table, but gives me a Sql interface, can you put the above into a SQL statement(s) (my forgetfullness of SQL is probably showing by now) Thanks again -arthur|||

CREATE CLUSTERED INDEX [SomeIndexName] ON [dbo].[TableName]([FieldName]) ON [PRIMARY]

Keeping DB schemas synched

Hi - what's the best (preferably open source or inexpensive) tool for
identifying differences (tables, columns, types, etc) between a development
database and a production one, and applying dev changes to production
automatically?
Thanks,
ChrisI don't think it is ever a good idea to make changes to a production system
"automatically". But the compare tool from www.red-gate.com is inexpensive
and should do what you are after.
Andrew J. Kelly SQL MVP
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
> Hi - what's the best (preferably open source or inexpensive) tool for
> identifying differences (tables, columns, types, etc) between a
> development
> database and a production one, and applying dev changes to production
> automatically?
> Thanks,
> Chris|||2nd the vote for red gate...I've used SQL COmpare and Data Compare for
several years to keep my schems and lookup data intact
Free 14 day trial, less than $300 per tool. VERY hard to beat
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uxykvJONGHA.2472@.TK2MSFTNGP11.phx.gbl...
>I don't think it is ever a good idea to make changes to a production system
>"automatically". But the compare tool from www.red-gate.com is inexpensive
>and should do what you are after.
> --
> Andrew J. Kelly SQL MVP
>
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
>|||Thanks Andrew- I fear I do a lot of things that aren't such good ideas when
developing! With luck, the right tool will have options to select the change
s
I'd like to apply, and then apply them.
Thanks for the rec,
Chris|||Developing is one thing, production is quite another. Yes this tool will
generate scripts for the changes but no matter how good the tool all scripts
should be tested before being applied to production.
Andrew J. Kelly SQL MVP
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:A653C08A-D917-4E85-84DE-D8B4B6A2E015@.microsoft.com...
> Thanks Andrew- I fear I do a lot of things that aren't such good ideas
> when
> developing! With luck, the right tool will have options to select the
> changes
> I'd like to apply, and then apply them.
> Thanks for the rec,
> Chris|||You should be checking your development and production scripts into some
type of source version control system. For example, Visual Source Safe has
an option to compare two projects and list files that are different, and it
has a feature for comparing two versions of a script side by side with
differences highlighted.
Also, you can script the databases to seperate folders and use a tool like
WinMerge to perform the comparisons:
http://groups.google.com/group/micr...br />
46abfa76
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
> Hi - what's the best (preferably open source or inexpensive) tool for
> identifying differences (tables, columns, types, etc) between a
> development
> database and a production one, and applying dev changes to production
> automatically?
> Thanks,
> Chris|||Thanks JT- unfortunately, I currently do all admin via the admin console, no
t
with scripts. Bad practice?
Chris
"JT" wrote:

> You should be checking your development and production scripts into some
> type of source version control system. For example, Visual Source Safe has
> an option to compare two projects and list files that are different, and i
t
> has a feature for comparing two versions of a script side by side with
> differences highlighted.
> Also, you can script the databases to seperate folders and use a tool like
> WinMerge to perform the comparisons:
> http://groups.google.com/group/micr... />
0c46abfa76
> "querylous" <querylous@.discussions.microsoft.com> wrote in message
> news:9E38D2DB-B107-45D1-A5D4-2E0970067AE8@.microsoft.com...
>
>|||In most SQL Server environments, changes to the database schema are
implemented as scripts; which are first tested against a DEV or QA server
and then promoted against the production server. These scripts are archived
using a source control system just like C# or Visual Basic projects.
"querylous" <querylous@.discussions.microsoft.com> wrote in message
news:86627853-B428-44F0-B94E-282A69894DF7@.microsoft.com...
> Thanks JT- unfortunately, I currently do all admin via the admin console,
> not
> with scripts. Bad practice?
> Chris
> "JT" wrote:
>

Keep table as it is..

Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?
You may want to post this in the replication newsgroup. Hilary should be
able to answer that one real quick.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<jostein.solstad@.gmail.com> wrote in message
news:1185459329.169314.161210@.r34g2000hsd.googlegr oups.com...
Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?

Keep table as it is..

Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?You may want to post this in the replication newsgroup. Hilary should be
able to answer that one real quick.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<jostein.solstad@.gmail.com> wrote in message
news:1185459329.169314.161210@.r34g2000hsd.googlegroups.com...
Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?

Keep table as it is..

Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?You may want to post this in the replication newsgroup. Hilary should be
able to answer that one real quick.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<jostein.solstad@.gmail.com> wrote in message
news:1185459329.169314.161210@.r34g2000hsd.googlegroups.com...
Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?

Keep speed

Hi...

I'm inserting and deleting about 30 000 records into 2 tables each day - import them from a text file using DTS.

The users add about 1000 records a day using Access and Windows .NET frontends...

Which TSQL commands should I run frequently to keep the database up to speed ?

I'm doing the following ... do you know of anything else?

Backup LOG MyDataBase WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (MyDataBase , 40)
GO

Backup LOG tempdb WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (tempdb, 70)
GO

THANKS!!!!!!!!!!!!!!!

Dave

There are a lot of different things you can do to keep the speed of your database up, such as:

Set the database to Simple Recovery and enable Auto Shrink, this will perform the same function you are doing with your DBCC Shrink commands. There are quite a few debates as to whether or not Simple Recovery and Auto Shrink impact performance but I have not seen any negative impacts myself.

Partition your database across multiple physical drives, this can dramatically improve performance

If you are using SAN disk, properly align the sector boundaries of your disk, here is a rather large document on the subject but it's a good one

http://www.hertenberger.co.za/resources/diskpar.pdf#search='how%20to%20use%20diskpar'

Delete any data older than "x" days from your tables, which it sounds like you may already be doing

Dedicate "x" amount of RAM to your instance

Dedicate "x" number of CPU's to your instance

The list goes on but that is a few examples.

|||Simple recovery model will not impact performance. However, the autoshrink certainly will. If the autoshrink kicks off when you are trying to process other queries, it will definitely slow everything down.|||

Hi Dave,

What comes to mind immediatly is that there will be a potentially large amount of fragmentation of tables/indexes in your db. This is because: a) you are performing a large amount of deletes and inserts often and b) shrinkdatabase introduces logical fragmentation.

So, I would recommend you run dbcc showcontig on your main tables, then perform rebuilds as needed with either DBCC DBREINDEX or DBCC INDEXDEFRAG. As an aside, if there has been a large amount of fragmentation on several tables, make sure your statistics are up to date, and run sp_recompile on the tables in question so that any stored procs you have can make use of the new stats immediatly.

Cheers
Rob

|||

Hi, Lesego.

If you're going to be doing queries agianst these tables that you're adding and removing data from, it's probably a good idea to update the statistics on the table. UPDATE STATISTICS is the command to use, and you'll want to run it against any statistics the table has -- you can find those most easily by exploring in the object browser in managemnet studio, but you'll have one for each index on the table, plus any that you've created yourself, plus any that the server has created automatically.

UPDATE STATISTICS might not be too important if you're selecting data directly from the table. But it will be very important if you are using the table that's the target of your insert/delete batch job in any JOINs with other tables. The query optimizer makes many decisions about how to best execute a statement based on information it can gather from the statistics on the table.

Hope that helps, and do let us know if you have more follow-up questions.

.B ekiM

Keep remote SQL data tables updated

Hello, I need some guidance in the best method to accomplish this task. I have a network with a SBS 2003 server and a SQL 2000 member server in the SBS domain. I have a remotely hosted website at discountasp.net with a SQL 2000 database. The website will host a modified e-commerce kit where corporate clients can order parts. I need to keep the products table on the remotely hosted site as updated as possible. The website orders are placed via email from the commerce kit and the fulfillment department proccesses the order against the SQL server in the SBS domain.

Any ideas on how to keep the data updated on the website?

How updated do you want?|||Within an hour perhaps. I have a remotely hosted sql server at discountasp.net and a sql server in my domain. The hosting provider does not allow dts or replication.|||

I would say create a web service at your hosting provider. Then create a service you can run on your local sql 2000 machine that does the pull/push at intervals. Just make sure that all your important tables have auto-incremented id fields so you know what you are missing since last pull/push.

It sounds like the hosting provider is the master table for orders, and your local sql server is the master for products. Create a trigger on the ISP's order table that records all insert/delete/updates to that table to an auditOrders table that contains an id field (Autoincrement int), a changetype field (char) constrained to 'I','U', or 'D', a ChangeTime field (datetime with default of GetUTCDate()), and a field for every field in Orders. Make a similiar trigger on your local products table going into auditProducts. Then create a webservice at the hosting provider that has 3 methods:

public function GetNewOrders(LastId as integer) As Dataset

public function GetLatestProductChangeID() as integer

public sub SaveProductChanges(ds as dataset)

Make a copy of auditProducts and auditOrders at the other location. To pull changes to orders, select the highest id from your local auditOrders table. Then call GetNewOrders with that id. Take the dataset and record by record (It should be in id order) start a transaction, perform the Insert/Update/Delete on the local table, and then insert the record into the local audit table, and commit the transaction. To push Products, call the GetLatestProductChangeID, then create a dataset containing all the records from the local auditProducts table where the id is larger than the id you got from the webmethod, and call SaveProductChanges with the dataset.

Alternatively from having to do the transaction, and manually record the record into the audit table, you can also copy the triggers from the other side, and it (SHOULD) recreate the record, all except for the ChangeTime which will be different obviously.

|||

Take a look at my Remote SQL data provider, WebSql Data Provider, for secure, efficent access to SQL Server over HTTP, as simple as using the native SQL provider in System.Data.SqlClient namespace.