Usage (scope) of ALTER TABLE in sproc

hi @ll,

I have a question about the usage of ALTER TABLE in a stored procedure 
(SQL Server 2005):

First concurrent query, started first, should be later encapsulated in a 
stored procedure:

BEGIN TRANSACTION [Test] ;
ALTER TABLE [myTable]
         NOCHECK CONSTRAINT ALL ;

-- Function from BOL, WAITFOR DELAY wrapper.
EXEC TimeDelay_hh_mm_ss '00:00:10';

ALTER TABLE [myTable]
         CHECK CONSTRAINT ALL ;
COMMIT TRANSACTION [Test] ;


Second concurrent query:

INSERT  INTO [myTable]
         (
           [Id],
           [Abbreviation],
           [Name]
         )
         SELECT  NEWID(),
                 [Abbreviation],
                 [Name]
         FROM    [myTable] ;

When running it in SSMS it produces the desired result. The first query 
grabs a schema lock and blocks the second query as long it runs. The 
second query is executed after the commit and is denied as there are 
unique constraints on [Abbreviation] and [Name].

Can I use the ALTER TABLE statement in a sproc like this:

CREATE PROCEDURE [mySproc] ()
AS
BEGIN

ALTER TABLE [myMasterTable]
         NOCHECK CONSTRAINT ALL ;
ALTER TABLE [myDetailTable]
         NOCHECK CONSTRAINT ALL ;

-- modify myMasterTable
-- modify myDetailTable

ALTER TABLE [myMasterTable]
         CHECK CONSTRAINT ALL ;
ALTER TABLE [myDetailTable]
         CHECK CONSTRAINT ALL ;

END ;

In fact I'm trying to insert a entire bunch of records in different 
tables having a complex referential integrity graph.

Are there any pitfalls?

mfG
--> stefan <--
0
Stefan
11/16/2009 2:10:00 PM
sqlserver.programming 1873 articles. 0 followers. Follow

0 Replies
834 Views

Similar Articles

[PageSpeed] 0

Reply:

Similar Artilces:

Pivot Tables & Median
How do you make a pivot table calculate median through the calculated feild option or is there another simple elegant way? Hi Steve you can't add the median to a pivot table. You may use a helper column in your source table and use this helper column as new data item in your pivot table -- Regards Frank Kabel Frankfurt, Germany steve wrote: > How do you make a pivot table calculate median through the > calculated feild option or is there another simple elegant > way? ...

Protecting Tables from being viewed
Hi all, I need help to protect my tables from being viewed by my users. Basically, i want to them to view data via forms, which is much easier to control. To prevent this problem, I disabled all menu and the database window on startup, but they can easily bypass the strartup if they know how. Also, all my link tables are linked tables, but they still need to show up on the database window anyway (am I right? Is there anyway to avoid that?). Also, I tried to create MDE files, which did not help because they only lock code-based objects.... I ran out of options, if you have any suggestion or s...

dual monitor usage
Hi, My name is Harry and I'm programming to interface with 2 monitors: one is just regular LCD and the other is LCD with TV/Video I/O. I'm trying to interface both using one graphic card. if anybody knows how to program, please help me out. thanks much H ...

Data migration
Can anyone please help with the questions below thanks. Is it possible to link tables in CRM? If so, how is this accomplished? Does a CRM picked list use a linked table? - If so how do I find the table that this picked list is linked to? - If not, where does it store the pick data. You can map fields from one entity to another in CRM's Deployment Manager, but data will be transferred only when the mapped entity is created from the originating entity. For example, if you map and Account's company name to the Contact company name field, you can pre-populate the compa...

New system
We are rebuilding our Dynamics system from scratch following a very badly implemented version due to heavy customisation. The only issue we have is that we want to migrate the FIFO stock table across. Having looked at the SQL table IV10200, this appears to be a standalon table - the only field we are concerned at is the DEC_ROW_ID. We want to simply populate the IV10200 with the data from the old system and want to do this directly in SQL as this is quick and simple. Is there any danger by doing this? If it is a standalone table, then I cannot see any system problems arising? We have ...

averages in pivot table
I created a pivot table to show data over a 12 month time frame. I am trying to get the monthly average. However, it does not include months with null values in the average. For example the value for Feb is 60 and March is 60 and all other months are null for a total of 120 for the year. The monthly average for the whole year should be 10. However the pivot table will give an average of 60. How do I get the pivot table to include null values in the calculation of the average? You have to replace blanks/null with 0, that way you'll get 10.. -- Regards, Peo Sjoblom &q...

Setting recordsource to secured table
Hi, I have a form that I am trying to re-use to display data from 2 different tables and the information is changed in the click event from the menu that selects the form and it sets the recordsource, label captions, and textbox sources. This works fine on my userid (the owner), but there are no permissions on those tables for other user groups. I thought I could get around that using the "with owneraccess option", but it doesn't seem to be working. I have confirmed that the owner of both tables has full permissions. What am I doing wrong? Thanks, beth Here is a snippet o...

Floating table
Does anyone know if it is possible (preferably without using Macros) to have a table that always stays at the top of the screen? Basically when I scroll down along the information I lose the table, but I cant use a standard frame freeze as then the information becomes very difficult to make out!!! Maybe this can be done by manipulating the frame freeze somehow or maybe there is some other way of doing it. If anyone has any ideas please let me know. Thanks, Niall. If the table is too big for Freeze Panes, then I'm not sure how making it "floating" will help. You can split th...

Pivot Table
Is there any way to suppress the display of blank cells within the data area of a pivot table? Debra Dalgleish has posted code to do this: http://groups.google.co.uk/group/microsoft.public.excel.misc/browse_frm/thread/3fbd903e92adc270/2cb8900b96680905?lnk=st&q=hiding+0+pivottable+group:*excel*+author:dalgleish&rnum=4&hl=en#2cb8900b96680905 or http://tinyurl.com/axk75 Kirk P. wrote: > > Is there any way to suppress the display of blank cells within the data area > of a pivot table? -- Dave Peterson ...

Table Cells
In Word 2007, is there a quick way to determine what table cell you the insertion point is in? I have a vague recolleciton of a feature to do this in earlier versions of Word. Thanks, Ross See if either of the macros at http://word.mvps.org/FAQs/AppErrors/SumAboveIncorrect.htm and http://gregmaxey.mvps.org/Table_Cell_Data.htm will work in Word 2007. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Ross from Orlando" <rossp@rosspayne.net> wrote in message news:efe3a4eb-2f63-4060-b7ad-86471be0dcca@j24...

Pivot table won't updade data when refresh selected
I have a simple pivot table which works on data from a different sheet in the same workbook. When I change the text on the data source and then click refreh an the pivot table it doesn't update. The changes I am making are just simple corrections e.g. changing lower case letters for upper case. Please help. ...

Temp table creation
Are there any differences when creating a local temp table using the syntax create table #temp to create table tempdb..#temp thanks in advance Ralph Yes, create table #temp will create a local temporary table in tempdb that can only be accessed by your connection. create table tempdb..#temp is illegal and will give you a syntax error. Tom "Ralph" <ralphd42@hotmail.com> wrote in message news:8DD1A6D5-A2DE-4578-A4C6-65FD71D3F27E@microsoft.com... > Are there any differences when creating a local temp table using the > syntax create ta...

Importing spreadsheet to a table from a button?
We recieve information atleast once a week if not more. Instead of having to import the table manualy into a already existing table I was wanting to know if there would be anyway that I could just go to a form that contains a button that says "Import" or whatever we decided to label it as and when you select the button, it will have a code or command (I figure a code will need to be wrote) to just go and import the data straight to the table without going through the wizard or anything else. Although if the wizard did have to be used it wouldn't be that big of a deal. If anyon...

Usage data
I am interested in developing an application that monitors data usage. For example, I need to know how many rows that a user accesses from specific tables; maybe also, what columns they access. This data should probably be gathered at the data-access layer because it would be more certain and not make me rely on having to parse ad-hoc queries. Does SQL provide any sort of monitoring tool that can give this data? Suggestions? Thanks, Howard SQL 2008 has a lot of features and built in reports... also maybe performance dash board? (It's a seperate installation package) ...

Access and processor usage
I am just wondering why, with nothing else running and executing an update query against a very large table, does Access seem to be causing less than 10% processor usage. Then it says "There is not enough disk space or memory to undo the changes". I have 2 gb RAM, Core 2 duo e6300 processor and plenty of disk space. Why doesn't Access peg the CPU? Joel Hi, Joel. Please don't multipost. Your question has already been answered elsewhere, so anyone answering this post would likely duplicate those answers and get upset with you for wasting his valuable time. Please see th...

querry 2 tables
I have a table which I input labor charges(laborID). I have a second table which I input parts charges(partsID). when I querry the 2 table to create my billing information I get duplicated results. If I have 1 labor charge and multiple parts charges the labor charges or duplicated as if to keep the rows equal. EXAMPLE of what i expect to see: workorderID laborID partsID 1 1 1 1 2 1 3 What I actually see is the laborID duplicated for for partsID 2 & 3 This seems so easy but I just can...

Resource Availability Incorrect in Resource Usage for Project 2007
We are running Project 2007 with Project Server - SP2. The problem we are seeing is with Resource Usage in the enterprise - in the thick client. We have resources who are shared on multiple projects. When a PM was using the thick client and was looking at the availability of their resources in the Resource Usage view, this shared resources had assigned hours in the "other projects" area of the view, but there were no hours associated with any of the projects listed. So..the "other projects" total was 5 hours, but each project under that heading had nothing. When I went...

Optimizing Memory Usage in Exchange Server 2003
Please can someone advise me what to do in the following situation: I recently upraded my Exchange 2003 server to 4GB RAM and saw the event 9665 in the event viewer. It refers to KB815372 which details how to optimize memory usage in Exchange. After reading the article; I am considering putting the /3GB switch in the boot.ini; however there are some mitigating factors in my environment. My Exchange server is actually the DC and it has SQL2000 installed on it. (I know, I know - terrible. Budget constraints). I know that SQL and Exchange are both applications that are memory hogs/lovers;...

Pivot Table #21
In my pivot table i have days on the market. I need to sort them so that they show in groups such as: 1-100,101-200,201-300,301-400. then a second column showing values number sold such as:1-100 31 then a third column showing the avg. sales price suchas:1-100 31 $94,451.61 Hi Right click on a value>Group and Outline>Group>by 100>OK -- Regards Roger Govier "fearthespear" <fearthespear@discussions.microsoft.com> wrote in message news:67455DF6-C775-4758-B731-65805B359728@microsoft.com... > In my pivot table i have days on the market. I need to sort them s...

usage of CoTaskMemFree?
In SAPI4 com i'm using the Phoneme function. The docs indicate to use CoTaskMemFree to recover memory allocated by phoneme function, however I'm not sure if this is how it should be accomplished:(pdPhoneme is a pointer passed to the phoneme function which returns a phoneme representation for a text string within the PSDATA structure. PSDATA pdPhoneme; SDATA dPhoneme; CoTaskMemFree(pdPhoneme); Is there any way to tell how much memory the CoTaskMem Free has recovered? thanks t1rem@earthlink.net (Lester) wrote in message news:<40da4237.3036075@news.east.earthlink.net>... &g...

I don't know how to make a data table
I don't know how to make a data table. Please help make one for me by tomorrow. AsianPride210, This is easier than you think. Column A in cell A1 TIME,cells A2 through A62. 1:00 2:00 3:00 etc. 60:00 In column B data point that changes, cell B2 through B62 what the data point was. In column C second data point that changes, cell C2 through C62 what the data point was. Looks something like: TIME 1st Data 2nd Data 1:00 15 121 2:00 16 122 3:00 16 107 4:00 18 201 and so on and...

Linked Tables and forms
Hi everyone, I had a department ask me the question on whether they can use a switchboard to link 3 or 4 different databases? I am pretty sure it would be possible as long as the databases are split correct? Would i just have these linked tables on the database and have the necessary forms and reports loaded onto the front end? If someone can offer me their thoughts or a link to were i can find more information it is appreciated. Thanks You can link to multiple back end databases; howver linking only applies to tables. If you have forms, reports, queries, or macros in the databases...

CPU Usage
Does anyone know how to get CPU usage? And if so can I obtain it from another machine on the network? Scott O Yes, use the PDH Interface of Performance Monitoring in the Platform SDK. ScottyO :) "ScottyO" <ofriels@abc.com> wrote in message news:eaLgKxFuDHA.2444@TK2MSFTNGP12.phx.gbl... > Does anyone know how to get CPU usage? And if so can I obtain it from > another machine on the network? > > Scott O > > ...

Charts not working after pasting table from html page
After pasting a table from a webpage into excel 2003, it is imposible to make the charts function work as normal When I retype the table manually, chart wizard works fine Even formating the cells as text and numbers, the chart wizard does not work Pasting special (text or unicode) and reformating the correct cells to numbers does not work Looks like the Chart wizard treat the table as just one text-entry and one number Drives me nuts....this worked on Excel 2000 so why does it not work here... Must be one normal way to make this (typical task) work in Excel 2003? Hi try the following - for...

Cannot Clean Up ActivityBase Table
Is there a fix for this yet? I would hate to have to clean up the database everyday? TechKnowledge Microsoft CRM Deletion Service Failure Error "Cannot Clean Up ActivityBase Table" Appears in the Application Event Log on the Microsoft CRM Server Printable Link Email this link Document ID: 30380 Date Created: 4/24/2003 Date Last Modified: 6/18/2003 10:39:47 AM Language: English - United States Country: USA Product: Microsoft CRM Versions: 1.0 Modules: Microsoft CRM Miscellaneous Issue Microsoft CRM Deletion Service failure error - "Cannot clean up ActivityBase table."...