February 2, 2013 at 11:06 am
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?
February 3, 2013 at 11:38 pm
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 4, 2013 at 10:16 am
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.
February 5, 2013 at 5:34 am
The simple answer is YES. We can connect through link server and can access other tables while importing/exporting other tables.
February 5, 2013 at 5:47 am
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.
February 5, 2013 at 11:21 am
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-
February 5, 2013 at 11:48 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 5, 2013 at 12:18 pm
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. 🙂
February 5, 2013 at 1:25 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply