access datasource in script component

I'm trying to access a group of rows in a data source in my script component (transformation). Currently im using the connection manager in the script component, acquire the connection, and issue a query command to get the data. This will happen on every rows being pass to the script component from the data source. Is there a better way to access the a group of rows from the data source?

It depends what you want to do. Perhaps you could populate an in-memory store of the data to prevent the round-trip each time.

Are you sure that cannot be achieved with LOOKUP component?

-Jamie

Access .adp :How to INSERT all but KEY violations

I am trying to append records from one table to another in a db running on
MSDE, knowing fullwell that some of the data in the source will be
duplicates of that in the destination table's pk.
What I would like to happen is to have the stored procedure plunk in all
records that don't violate the constraint
and silently let the duplicate info fall by the wayside. The trouble is SQL
server seems to abort the whole procedure if
even a single record violates the constraint.

In a regular Access mdb, an INSERT statement (append query) would do just
that. Of course it warns you of the violation but a DoCmd.SetWarnings FALSE
takes care of that.

Any ideas as to what I need to do to achieve that same thing?For example:

INSERT INTO TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

(where key_col is the primary key).

--
David Portas
SQL Server MVP
--

About restoring

I′m using SQL server 2005.

I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".

Is there a log for this?
How can I cancel this operation?

thanks!!!!
this operation "restoring" is running for more than 10 hours, but the database is small.|||This is probably because the database you are restoring is being used. Do not select the database from the Object Explorer when restoring either using the UI or RESTORE DATABASE command|||hi,

but is there a way to cancel?

How can users access this database?

thanks!!!!|||In the log file viewer, there is a line with the following:

The database 'dbnamedevelop' is marked RESTORING and is in a state that does not allow recovery to be run.

How can I to do to use or to enable this database?

thanks!!!! :-)|||What is the sequence of events you performed on the 'dbnamedevelop' database?|||while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu|||

OK. That helps to clarify the issue.

The advice to use WITH NO RECOVERY for the initial files and WITH RECOVERY only for the final file applies to backup files, not database files.

So, for example if you had a full database backup and a series of log backups, you would restore the database backup WITH NO RECOVERY and then restore each log file using RESTORE LOG WITH NO RECOVERY.

On the last log backup, you would use RESTORE LOG WITH RECOVERY.

Each of these is a separate restore statement.

The WITH [NO] RECOVERY determines the state in which the RESTORE command leaves the database:

Recovered (all non-committed transactions rolled back and the database ready for use)

or Restoring (No roll-back performed, ready to apply the next log backup)

In your example, you specify that one RESTORE command should do both, which is not possible.

|||

Madhu K Nair wrote:

while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu

ya... there was some confusion... ofcouse it applies to Backup files... i should have mentioed full backup instead data file... The example which i mentioned was copy pasted from BOL and there was nothing wrong in it. There were Two restore not One...

Madhu

|||

So, did the second RESTORE command succeed? If it did, the database would be online.

If it did not, what messages are in the SQL log and Event log?

|||

Can you see any activity on the files under operating system, check under the pathwhere the data files are located. I have had this problem once and due to the Anti-virus & Anti-spyware on this server (development) the whole process was hung. You can kill it but before that ensure there is no activity or other wise get another fresh backup from the source database server.

Tadeu wrote:

I′m using SQL server 2005.

I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".

Is there a log for this?
How can I cancel this operation?

thanks!!!!

|||

i'm having this problem, two databases are hung in the middle of a restore.

how do i cancel this?

appreciated,

m

About restoring

I′m using SQL server 2005.

I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".

Is there a log for this?
How can I cancel this operation?

thanks!!!!
this operation "restoring" is running for more than 10 hours, but the database is small.|||This is probably because the database you are restoring is being used. Do not select the database from the Object Explorer when restoring either using the UI or RESTORE DATABASE command|||hi,

but is there a way to cancel?

How can users access this database?

thanks!!!!|||In the log file viewer, there is a line with the following:

The database 'dbnamedevelop' is marked RESTORING and is in a state that does not allow recovery to be run.

How can I to do to use or to enable this database?

thanks!!!! :-)|||What is the sequence of events you performed on the 'dbnamedevelop' database?|||while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu|||

OK. That helps to clarify the issue.

The advice to use WITH NO RECOVERY for the initial files and WITH RECOVERY only for the final file applies to backup files, not database files.

So, for example if you had a full database backup and a series of log backups, you would restore the database backup WITH NO RECOVERY and then restore each log file using RESTORE LOG WITH NO RECOVERY.

On the last log backup, you would use RESTORE LOG WITH RECOVERY.

Each of these is a separate restore statement.

The WITH [NO] RECOVERY determines the state in which the RESTORE command leaves the database:

Recovered (all non-committed transactions rolled back and the database ready for use)

or Restoring (No roll-back performed, ready to apply the next log backup)

In your example, you specify that one RESTORE command should do both, which is not possible.

|||

Madhu K Nair wrote:

while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu

ya... there was some confusion... ofcouse it applies to Backup files... i should have mentioed full backup instead data file... The example which i mentioned was copy pasted from BOL and there was nothing wrong in it. There were Two restore not One...

Madhu

|||

So, did the second RESTORE command succeed? If it did, the database would be online.

If it did not, what messages are in the SQL log and Event log?

|||

Can you see any activity on the files under operating system, check under the pathwhere the data files are located. I have had this problem once and due to the Anti-virus & Anti-spyware on this server (development) the whole process was hung. You can kill it but before that ensure there is no activity or other wise get another fresh backup from the source database server.

Tadeu wrote:

I′m using SQL server 2005.

I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".

Is there a log for this?
How can I cancel this operation?

thanks!!!!

|||

i'm having this problem, two databases are hung in the middle of a restore.

how do i cancel this?

appreciated,

m

About restoring

I′m using SQL server 2005.

I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".

Is there a log for this?
How can I cancel this operation?

thanks!!!!
this operation "restoring" is running for more than 10 hours, but the database is small.|||This is probably because the database you are restoring is being used. Do not select the database from the Object Explorer when restoring either using the UI or RESTORE DATABASE command|||hi,

but is there a way to cancel?

How can users access this database?

thanks!!!!|||In the log file viewer, there is a line with the following:

The database 'dbnamedevelop' is marked RESTORING and is in a state that does not allow recovery to be run.

How can I to do to use or to enable this database?

thanks!!!! :-)|||What is the sequence of events you performed on the 'dbnamedevelop' database?|||while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu|||

OK. That helps to clarify the issue.

The advice to use WITH NO RECOVERY for the initial files and WITH RECOVERY only for the final file applies to backup files, not database files.

So, for example if you had a full database backup and a series of log backups, you would restore the database backup WITH NO RECOVERY and then restore each log file using RESTORE LOG WITH NO RECOVERY.

On the last log backup, you would use RESTORE LOG WITH RECOVERY.

Each of these is a separate restore statement.

The WITH [NO] RECOVERY determines the state in which the RESTORE command leaves the database:

Recovered (all non-committed transactions rolled back and the database ready for use)

or Restoring (No roll-back performed, ready to apply the next log backup)

In your example, you specify that one RESTORE command should do both, which is not possible.

|||

Madhu K Nair wrote:

while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu

ya... there was some confusion... ofcouse it applies to Backup files... i should have mentioed full backup instead data file... The example which i mentioned was copy pasted from BOL and there was nothing wrong in it. There were Two restore not One...

Madhu

|||

So, did the second RESTORE command succeed? If it did, the database would be online.

If it did not, what messages are in the SQL log and Event log?

|||

Can you see any activity on the files under operating system, check under the pathwhere the data files are located. I have had this problem once and due to the Anti-virus & Anti-spyware on this server (development) the whole process was hung. You can kill it but before that ensure there is no activity or other wise get another fresh backup from the source database server.

Tadeu wrote:

I′m using SQL server 2005.

I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".

Is there a log for this?
How can I cancel this operation?

thanks!!!!

|||

i'm having this problem, two databases are hung in the middle of a restore.

how do i cancel this?

appreciated,

m