<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
Link server locking tables
Link server locking tables
Rate Topic
Display Mode
Topic Options
Author
Message
wnarretto
wnarretto
Posted Saturday, February 02, 2013 11:06 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 03, 2013 1:36 PM
Points: 163,
Visits: 76
hello SQL world-
i am trying to copy tables form server A to server B using a link server. is there a configuration in the link server that will allow me to copy the tables over to server B without locking the database?
Post #1414955
Bhuvnesh
Bhuvnesh
Posted Sunday, February 03, 2013 11:37 PM
SSCrazy
Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562,
Visits: 3,451
wnarretto (2/2/2013)
hello SQL world-
i am trying to copy tables form server A to server B using a link server. is there a configuration in the link server that will allow me to copy the tables over to server B without locking the database?
NO.
why you want to avoid LOCking (which is impossible) ?
Read about locks
http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1415120
wnarretto
wnarretto
Posted Monday, February 04, 2013 10:16 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 03, 2013 1:36 PM
Points: 163,
Visits: 76
the idea is to be able to touch, view or manipulate data from the other tables that reside within that same database that is being locked by the linked server. the tables being copied over i don't care to touch them until they are done being copied but as i said earlier..... there are other tables within that database that need to be accessed. it is a pain when the link server locks the entire database and then you have to wait until its done before you can continue with touching the other tables that nothing to do with the link server. these tables just happen to reside with the database.
Post #1415395
smamidala
smamidala
Posted Tuesday, February 05, 2013 5:34 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, February 11, 2013 3:10 PM
Points: 5,
Visits: 20
The simple answer is YES. We can connect through link server and can access other tables while importing/exporting other tables.
Post #1415765
Tarun Jaggi
Tarun Jaggi
Posted Tuesday, February 05, 2013 5:47 AM
SSC Eights!
Group: General Forum Members
Last Login: Monday, May 06, 2013 2:52 AM
Points: 973,
Visits: 463
I think you are at target database and trying to get the data (source table along with other tables at source database) using linked server.
Can you try other way around like create the linked server (of target database) at source, do all the transformations (with other tables) and then populate the data at target database using linked server.
Or you can do that using SSIS in very efficient manner.
Post #1415774
wnarretto
wnarretto
Posted Tuesday, February 05, 2013 11:21 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 03, 2013 1:36 PM
Points: 163,
Visits: 76
thanks for an attempted answer but i think i was not clear on what i am asking.
ok so you are wanting to manipulate table A and table B from Database A. a sql job kicks off and starts copying table C and table D from Database B to Database A. when this job kicks off you are not able to refresh Database A or physically access table A or table B. this job that copies tables uses a link server to connect to Database A from Database B. the link server is locking Database A. i understand locking and why you would want to have it happen. i just think that just because you are copying tables from Database B to Database A that you should be locking the other tables. all i want to know is if there is a parameter within the code of a link server that will allow the locking to not happen or at least not lock the tables that are not being effected by the link server.
thanks-
Post #1416012
ScottPletcher
ScottPletcher
Posted Tuesday, February 05, 2013 11:48 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,324,
Visits: 1,778
Are you doing a SELECT ... INTO?
If so, that may hold schema locks for the entire duration.
Instead, do an initial "dummy" SELECT ... INTO ... WHERE 1 = 0 to just create the schema, then do the actual load with a standard:
INSERT INTO
SELECT ...
FROM
That way, SQL won't have to hold schema locks, which can affect access to other objects in the db.
SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1416022
wnarretto
wnarretto
Posted Tuesday, February 05, 2013 12:18 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, May 03, 2013 1:36 PM
Points: 163,
Visits: 76
Thank you Mr. Pletcher....
yes the original code is doing a select into. Your answer is what i was suggesting but a coworker mentioned that their was a filter or parameter within the link server script that would allow it to not lock the entire database. he claims to not remember the parameter name but swears it is there.
thank you thou. :)
Post #1416027
ScottPletcher
ScottPletcher
Posted Tuesday, February 05, 2013 1:25 PM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,324,
Visits: 1,778
wnarretto (2/5/2013)
Thank you Mr. Pletcher....
yes the original code is doing a select into. Your answer is what i was suggesting but a coworker mentioned that their was a filter or parameter within the link server script that would allow it to not lock the entire database. he claims to not remember the parameter name but swears it is there.
thank you thou. :)
I don't know of any "hint" or other coding that would not hold a lock for
schemas
: you can use WITH (NOLOCK) to prevent any locks on the data rows being read. But you prevent locks being held on the schema/data being INSERTed: SQL requires certain locks just to maintain integrity.
IIRC, tempdb now has some built-in mechanisms by which it releases such schema locks much more quickly, for temp tables, but I don't think this applies to permanent tables in non-tempdb dbs.
SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1416062
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
