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
905 Views

Similar Articles

[PageSpeed] 48

Reply:

Similar Artilces:

Linking to an Excel Pivot Table
Does anyone know how to break the link between an Excel pivot table that uses Access as it's data source without recreating the pivot table? It links to a network drive at work, I need to relink to my local drive but it keeps looking for my "m" drive on my network at work. ...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

Pivot table will not returned value's
Goodmorning Everybody, Yesterday i made a macro which gets the value from Cell AM1 and fits it as a criteria in a Pivotfield in another file. The macro as seen at the end, works smooth except for this part: Set pt = Sheets("Table Combi").PivotTables("PivotTable3") Set pf = pt.PivotFields("ARF Code") ActiveSheet.PivotTables("PivotTable3").PivotFields("ARF Code").CurrentPage = "" & k & "" If pf.CurrentPage <> " & k & " Then pf.CurrentPage = "(Blank)" End If Does ...

delete dupl from 1 table that match table 2
I have 2 tables joined by an acct # but diff data in the 2 other columns. I want to delete the row of information from table A that have a matching acct # in table b. ie: appl acct # amount (table a) appl acct # amount (table b) b 1234 $1.00 b 1234 $5.00 c 111 $1.00 c 12345 $5.00 c 1001 $2.00 c 1001 $3.00 want to delete rows from table a. for accts #1234 & #1001 DELETE [Table A].[Acct #] ...

Protect the acess to a table in Acess 2007
Hi I`m using Acess 2007 and I would like to protect the acess of others to a table in a database used by several persons. Is it possible? Can somebody help me? Thanks ...

varying table length in a MACRO
I would like to perform a macro on multiple spreadsheets with varying numbers of rows. When recording the macro, I used the keystrokes to go to the end of the spreadsheet; however, when I apply the macro to other spreadsheets, it uses a fixed row area. How can I get this to go to the end of the spreadsheet? tlwhite Range("A2").Select ActiveCell.SpecialCells(xlLastCell).Select Range(Selection, Cells(1)).Select Range("B1:M2028").Select Range("M2028").Activate Hi, The macro below will loop through the named sheets and find the...

Make Table Query Question 07-21-07
Hello All: Access 2003 I have a make table query and one of the field is a $$$ dollar amount. I set the properties to "currency". However, in the table, the field type is "Number". Any ideas why this is happening? -Joe You could try typecasting the field in your query. For eample, if the design view shows the field like this: Amount: [Quantity] * [UnitPrice] try changing it to: Amount: CCur(Nz([Quantity] * [UnitPrice],0)) In general, the better solution is to design the table the way you want it, and then use an Append query to populate it, rather than a Ma...

Exclude pivot table data from pivot chart?
I have a pivot table set up and some of the data that is on the table, I do not want on the pivot chart. Reason being is that a graph of the data I am speaking of does not make sense. Is there a way I can make a pivot chart without these two columns of data being included? Thanks in advance for any help. The pivot chart displays what's in the pivot table. You could create another pivot table, using the first pivot table as the source. Set it up without the two columns, and create the chart from that. coal_miner wrote: > I have a pivot table set up and some of the data that is...

Can't modify table style
I'm flabbergasted after spending over 30 minutes trying to trouble shoot a table of contents, so I deleted it and tried to create a new table of contents formatted like the one I had before (font properties wise). So I select the table of contents tab on the ribbon (Word 2007), then I select "Insert a tabel of contents..." under the automatic and manual table images above and I want to "modify" the "formal" format, since it's pretty close to what I want except for the font color, type and size. I also want to change the "options&quo...

Tables/Queries
Hello, I've seen some posts regarding a similar problem to what I'm having but cant find the resolutution! I have a database with tableA which contains basic inventory info. I have a field in tableA named location. There is a second table (tableB) which has a list of all the locations our company delivers to. I've been warned to stay away from lookup fields, so I'm using combo boxes on the form to look up the location and then populate the location field in tableA when the value is selected. Here is the issue (and again, I've found some posts similar): Running a...

Access - Macro
Hello, I have 2 databases. DB1 and DB2. DB2 is more or less like a rater. I give the input to the DB2 and DB2 produce the output back to me. In DB1, I have a macro (plus calling vba code) that will 1. send the input file to DB2 2. Run the macro in DB2 (part of the macro in DB2 will run a Make table query which will make an Output table in DB1.) 3. Update the result from Output table into another table in DB1. The problem I have is the macro failed at step3. it said the table doesn't exist... I ignored the message, and run the query of step 3 manually and it works f...

Extract Top 5 value from a pivot table
Hello, I have a a pivot table listing by country and months of the number of times a certain internet page has been reviewed. I would like to have a formula (x5) which would extract the 5 highest pages viewed for a given month and given country. I tried the Getpivotdata and sumproduct functions, but I am getting nowhere. Can you help? Thank you Could you provide a sample of your data? On 23 Kwi, 01:47, Andre C <Andre C...@discussions.microsoft.com> wrote: > Hello, > > I have a a pivot table listing by country and months of the number of times > a ...

Help on muliple table queries
Hi all, I wonder if you can help. I have 5 tables of data with the same column names. I want to run a query which includes all records from all tables within a certain time period using a from/to date. Is this possible/simple, and how do I do it!! Thanks Natalie Hello Natalie. "Natalie" wrote: > Hi all, > I wonder if you can help. I have 5 tables of data with the same > column names. I want to run a query which includes all records from > all tables within a certain time period using a from/to date. > > Is this possible/simple, and how do I do it...

Need some pointers
I just started playing with the XML extensions of the CRL and have a question and have not seen how why what I have here is happening. Now I do know my XML is not complete and the file layout we are starting with is simply a prototype Any way here is a cut out of my xml file <IADSNOTES type='public' count='4'><Note ID='1' app='Readr' csm='0'><File href='C:\IADS\DEMO\DEMO.SGM'/><Frame num='1' id='INTRODUCTION TO IADS' title=''/><User>Guest</User><Pos ScrollPos='0'/><Te...

Table advise
I have a table that has the following ID Account Category Limit Each account may have only 1 item from each of the categories and 1 limit, for example: Credit = $5000 Cash = $1000 Check = $2000 Not: Credit = $5000 Credit = 2000 Credit = 1000 and so on At present the table I have allows each account number to have multiple accounts of each category. TIA On 2 May 2007 07:52:57 -0700, myxmaster@hotmail.com wrote: >I have a table that has the following >ID >Account >Category >Limit > >Each account may have only 1 item from each of the categories and 1 >limit, for ex...

Pivot table Control of height Access 2003
I have set up a daily pivot table for a production schedule. It works fine. The table is not long enough to see all the records (presently approx 10 records vertically) without scrolling. The pivot table is contained in another form as a subform . I have tried changing the window size of the pivot table in the form ,looked for height control or number of records to display and help menu without any solution . Does anybody know how the number of records displayed are controlled in the Pivot Table and how do I increase the view to see all records for that day ? ...

Event Handle scope.
I have a thread that uses WaitForMultipleObjects. I have various events and was wondering whether the handles that represent the events (CONST HANDLE *lpHandles in WaitForMultipleObjects Function) need to be declared globally to the dialog class(i.e member variables of the dialog class) so that other functions in the dialog class can set those events. If i declare event handles local to the thread function that contains the WaitForMultipleObjects then all event handling would need to be done in this function. Is that correct or am i missing something Cheer Macca Yes, you need to make the ev...

Vba excel add tables & shapes in word
Working with Office 2007 i build a word document from within excel. I start with a empty document, and sets the size and orientation from excel. I can add tables and shapes to the first page. When adding to second page tables are placed correct, but shapes are placed on first page. How do i get the shapes intended for page 2 to appear on page 2? Thanks -- H. C. The .Shapes.AddShape method (as well as the other .AddXXX methods) has an optional argument named Anchor. In order to place the shape anywhere other than the top left corner of page 1, you must supply a Range as the value...

set default record to a table
I have a shared db with various front ends (mde). I would like to set a paricular record as my default record, so whenever someone opens up the various db forms it opens up to the same default record. Is this possible? Maybe this is a matter of terminology... In Access, data is stored in tables. Each table has it's own records (collections of related data). When you say you want a "record" to be the default one pulled up by everyone, how do you know they are all using their different forms to look at the same data (i.e., table)? Next, why? What is it about ...

How to put a table in Slide Layout so it can be applied to a page?
I am working with Slide Masters and Layouts for the first time. I'm doing OK with making them and applying them to pages. But when I make a table it doesn't work. When I go to Insert>Layout Placeholder> Table, then I get a box that has the word "Table" in it. Is that right? When I look for controls to make it look like a table with headers, cells etc., right-click on it, whatever, I don't see any table controls, just the usual text/box attributes (size etc.). So in the ribbon I go to Table Styles and click on one of them (Light #5) and then I have a no...

Place actual selection name in table instead of ID #?
I have a CBO that places the companies ID in my table instead of the actual companys name. I know this is how it is suppose to work but I need it to place the actual name that the user selects into the table. How is this done I know of a way? In that case you have to change the field in the designated table because initally you would store a number and now you want to store text. So change the field to text and then change the bound column of the CBO to 2 (which is probably the field which shows the name). This way you can still use the combo but it will store the name in the table (as...

Pivot Table Group by Week?
Hello- I see you can group pivot tables by month, year, quarter, etc.. What happen to 'by week'? Thanks. Use days and set them to 7 that will group like 01/01/05 - 01/07/05 -- Regards, Peo Sjoblom "DTTODGG" <DTTODGG@discussions.microsoft.com> wrote in message news:8D92FECA-04E9-4341-8E14-FBD33421FD56@microsoft.com... > Hello- > > I see you can group pivot tables by month, year, quarter, etc.. > > What happen to 'by week'? > > Thanks. Simply Beautiful. Thank you Peo. Now, can you help with my "Wrap data onto one page"...

having a table in excel
How do I have a drop down list in a cell of a bunch of items and when you select on of the items in the drop down it will fill in the whole row and columns for that item. For example: Item Description Price Qty So when you select the item in column A it will fill in the Description, price and the quantity. Thanks Hi ashaback, I have a tutorial on how to do this here http://edferrero.m6.net/DataTutor01.shtml Ed Ferrero http://edferrero.m6.net/ > How do I have a drop down list in a cell of a bunch of items and when you > select on of the items in the d...

error when trying to alter table in transaction
Hello there I have table which hold SQL script for alter modules I'm tring to run it one by one in try_catch inside of transaction and i'm getting this error: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. why? ...

Filter for Pivot Table
Hello everyone, I've used these google groups a couple times in past month and you've all been really helpful. I hope I will be able to contribute sometime, but alas, it looks like I have one more question, if anyone could help that would be awesome! So, what I'm wanting to do, is get the total for different categories of a list of imported data which may range from 2,000 cells to I guess, well, 65,536 cells (or whatever the maximum on office 2003 may be). Looking at other suggestions made, it seems the common consensus is to use a pivot table. I tried this, and it worked great...