Showing posts with label existing. Show all posts
Showing posts with label existing. Show all posts

Friday, February 24, 2012

Best Way To Update Existing Rows

IUpdating existing rows in a SQL server 2005 destination table

--I have a CSV file and every row needs to perform an update on a production table

The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ?

That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow?

thanks in advance

Dave

Mr Pro Tools wrote:

IUpdating existing rows in a SQL server 2005 destination table

--I have a CSV file and every row needs to perform an update on a production table

The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ?

I assume you can write SQL, right?

All explained here: http://msdn2.microsoft.com/en-us/library/ms141138.aspx

Mr Pro Tools wrote:

That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow?

thanks in advance

Dave

Again, I assume you can write SQL. Just paste your SQL into the 'SQL Statement' proeprty of the Execute SQL Task.

-Jamie

|||

Thanks Jamie

First method works --Profiler is telling me its taking about a second per update

but is too slow as I have 900,000 records to update across two tables each having 7 million records which will take

889,641 records in the file supplied / 3600 per hour (1 per second)

= 250 hours

= 10 days

the Excecute SQL task looks like another option and sure I can paste into the SQL Statement window my sproc Exec dbo.MySproc ?,?

How do I map ?,? to each row in the Execute SQL Task--in other words what are the main properties I have to set for row by row processing and is it much faster than the OLE DB Command Transformation method --which is nice and simple and slow

thanks in advance

Dave

|||

Mr Pro Tools wrote:

Thanks Jamie

First method works --Profiler is telling me its taking about a second per update

but is too slow as I have 900,000 records to update across two tables each having 7 million records which will take

889,641 records in the file supplied / 3600 per hour (1 per second)

= 250 hours

= 10 days

the Excecute SQL task looks like another option and sure I can paste into the SQL Statement window my sproc Exec dbo.MySproc ?,?

How do I map ?,? to each row in the Execute SQL Task--in other words what are the main properties I have to set for row by row processing and is it much faster than the OLE DB Command Transformation method --which is nice and simple and slow

thanks in advance

Dave

I'm confused (seems to happen alot these days ) Why are you trying to map any columns at all using '?' ?

All you have to do is load the data into a temporary staging area (using OLE DB Destination) and then issue a SQL statement along the lines of:

UPDATE t

SET t.<col> = te.<col> ,......

FROM target t INNER JOIN temp te ON <some join columns>

Also, if OLE DB Command really is taking one second per row then your bottleneck is not the OLE DB Comamnd. its something else.

-Jamie

|||

Mr Pro Tools wrote:

How do I map ?,? to each row in the Execute SQL Task--in other words what are the main properties I have to set for row by row processing and is it much faster than the OLE DB Command Transformation method --which is nice and simple and slow

Dave,

The OLEDB command is just slow becasue performs the same command in a row by row basis. The intention of using a Execute SQL task is to be able to perform execute a SQL command over a set of rows at once. What Jamie is sugesting is to push the rows in your CSV file to an staging table and then, in control flow to issue un Update stament against your target table using the staging table rows. That would be a one time update.

|||

UPDATE t

SET t.<col> = te.<col> ,......

FROM target t INNER JOIN temp te ON <some join columns>

Yeah that is simple enough what Im getting at is can the Execute SQL Task do row by row processing like the DTS DDQ task and how do set the properties if so

In my case the updates will only occur if one of columns in the temp staging table has an particular value in a column

If it has --we do the update otherwise do a delete

thats why I had ? as parameters for a store prod

|||

No, the Execute SQL Task does not do row-by-row processing. As already established, that's what the OLE DB Command does.

-Jamie

Thursday, February 16, 2012

Best way to make a database not in use?

Hi.

I am almost wrapping up a project of my own and I will have the option to restore the database, so dropping the existing one and then running the SQL script to create the database, tables, stored procedures and so on.

My question is, (using .NET 2.0 C#) - what is the best way to resolve the "cannot drop database as it is currently in use" message, or some message of the similar sort?

Even though I specified "USE MASTER" in the commandText string, there will be somewhere, in a situation, where the database will still be in use.

How can I forcefully make it NOT in use, drop it and do other things I like to?

What is the best way?

Many thanks!

Commonly we just cursor through open spids and execute KILL session_id/spid:

--2005
select session_id
from sys.dm_exec_requests
where db_name(database_id) = 'your database'

--2000 and earlier
select spid
from master..sysprocesses
where db_name(dbid) = 'your database'

|||Are you using SQL 2005? If so, in Management Studio, in Object Explorer, navigate to [Database Name]\Management\Acticity Monitor. Double click Activity Monitor - the Activity Monitor appears.

Using the Activity Monitor (with "Process Info" selected in the left-hand pane), you can see all active processes. As you have noticed, you won't be able to drop your database with active processes. Simply right-click processes that you wish to terminate, and choose "Kill Process". Note that you can see the database name as part of each process' information.

--

If you are using SQL 2000, (from my memory) an easy way to terminate all processes is to right-click the database, choose "Detach", and then when the dialog appears, there's an option to clear all existing users. After this has finished, you can drop the database.|||

Many thanks, I will give this a shot.

It's just amazing, how we can do ALL of this using SQL Server, kill processes, query the master tables as well as each of our own created tables/databases - fasinates me always :)

|||If you have any problems, or need to post again (SQL or otherwise), it's always helpful to specify product & the version of the software you are using. (SQL 2000 or SQL 2005, VB, VB.NET, C#, VS.NET 2003/2005 ... etc.)|||

Just:

use [YourDb]

alter database [YourDb] set single_user with rollback immediate

-- if there are no users with dbo permissions, you may use restricted_user instead

go

-- do your restore here

go

alter database [YourDb] set multi_user

|||gavrilenko_s - good suggestion. |||

ok, thanks for that

one more Q

if we have more than 1 SPID for a database, how can I kill them all?

I was thinking about doing this, however I think it's bad practice and prefer to do it the proper way!

DECLARE @.numberOfProcess int
SET @.numberOfProcess = 0
SET @.numberOfProcess = (select COUNT (spid) from master..sysprocesses where db_name(dbid) = 'db')
--SELECT @.numberOfProcess
IF (@.numberOfProcess > 0)
BEGIN
WHILE (@.numberOfProcess IS NOT NULL)
BEGIN
SET @.numberOfProcess = (select TOP 1 (spid) from master..sysprocesses where db_name(dbid) = 'db')
-- SELECT @.numberOfProcess
DECLARE @.SqlStatement NVARCHAR (20)
SET @.SqlStatement = 'KILL ' + CAST(@.numberOfProcess AS CHAR(2))
SELECT @.sqlStatement
EXEC sp_executesql @.SqlStatement
END

END


|||

Hi,

The method gavrilenko_s posted before is the best (which is always use for kicking users).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Best way to make a copy of an existing sql server database to another (physical) server

Hello!
What is the best way to make a copy of an existing sql server database to another (physical) server?
Plan to make a full backup of another sql server database to another server. I've read about detach and attach
and copying the datafiles and log files but some say it is prone to data loss? Is this true?

And another thing, what if the existing sql server can't affor downtime for me to copy db files etc.?

Anybody here who have done the same?

Any help is very much appreciated.

Thanks!

(a) if u don't have down time then the best method is Backup/restore...

(b) If you have down time both Detach/attach and backup/restore can be used. ..

(c) You can also use COPY Database Wizards

(d) You have to copy all the server level components to the new server. by copying use database Jobs/Packages/operator etc are not copied

(e) If you have to transfer the login also refer http://support.microsoft.com/kb/246133

I would always prefer Backup/restore method

Madhu

|||Thank you very much!

Tuesday, February 14, 2012

Best way to handle existing rows

I need to create a fairly simple package that copies data from one database using a sql querie and inserts it into another. what's the best way to handle rows that already exist in the destination table? The package needs to run once a day and ignore rows inserted from previous runs.You can pass the data through a lookup transform and compare them to the destination dataset. The rows that don't match will be marked as errors so click on the "configure the error output" and set it to redirect rows instead of failure. Then you can connect the red arrow to your destination and map the rows. There will be two extra columns but you don't need to worry about them as they are just for error tracking and you don't technically have errors.|||

The method brent describes is discussed in more detail here: http://www.sqlis.com/default.aspx?311

Its also worth saying that if the data in the source is timestamped when it canges then you can use that timestamp to only pull out data changed since previous load. i.e. Reduce the amount of data that you have to process.

-Jamie

Sunday, February 12, 2012

Best way to copy a replicated database?

I need to copy an installation to another server without breaking merge
replication on the existing database. Is the best approach to simply script
out the database, do an export of the data, then recreate on the other end
and set up a new publication?
Earl,
if it is the publisher or the subscriber, as long as the machine name is
different in practice you'll need to reinitialize. You caould do a nsync
one, provided you could ensure the data is synchronized. This can be
achieved by stopping all activity during the process, or by using
datacompare after the restore. After that, you'll need to recreate the
publication (if publisher) and initialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul. These are PocketPCs, so it sounds like the goal should be to
gather up one last round of data, then create a new database, load the data,
then create a new publication and initialize?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:untCSjg2FHA.2492@.TK2MSFTNGP09.phx.gbl...
> Earl,
> if it is the publisher or the subscriber, as long as the machine name is
> different in practice you'll need to reinitialize. You caould do a nsync
> one, provided you could ensure the data is synchronized. This can be
> achieved by stopping all activity during the process, or by using
> datacompare after the restore. After that, you'll need to recreate the
> publication (if publisher) and initialize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
that sounds like a pretty clean way of doing it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Friday, February 10, 2012

Best way to accomplish this - Move to new server

I just purchased a new server and I want to move all my
existing databases, DTS scripts, logins, etc to the new
server. What is the best/easiest/efficient way to
accomplish this?Easiest way is probably to restore a backup to the other server
or detach/attach
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
or use the Copy database wizard
INF: Understanding and Troubleshooting the Copy Database Wizard in SQL
Server 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274463
Also check out
INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274188
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||Backup and Restore work well for me. Setup SQL Server on the new machine
with the same service pack as the existing one, and the same disk layout if
possible. Restore the Master and MSDB databases for your logins & DTS and
jobs etc. and any other DB's you have. If the DB's are large it may be
faster to de-attach/re-attach from one machine to the other.
It might be possible to just copy the OS files for the databases between the
machines if they have the same directory structure, but I've never tried
this.
Mike Kruchten
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||You may want to read this:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.sqlmantra.com/314546
--
Rohtash Kapoor
http://www.sqlmantra.com
"Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> I just purchased a new server and I want to move all my
> existing databases, DTS scripts, logins, etc to the new
> server. What is the best/easiest/efficient way to
> accomplish this?|||sorry wrong link. The correct one is:
support.microsoft.com?kbid=314546
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:Ov0ldsT1DHA.3216@.TK2MSFTNGP11.phx.gbl...
> You may want to read this:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://www.sqlmantra.com/314546
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Mike Taylor" <mtaylor103@.hotmail.com> wrote in message
> news:0b8c01c3d539$6a135590$a501280a@.phx.gbl...
> > I just purchased a new server and I want to move all my
> > existing databases, DTS scripts, logins, etc to the new
> > server. What is the best/easiest/efficient way to
> > accomplish this?
>