managing lookup table rebuilds

I am using a Delphi program to rebuild a lookup table from a source table 
imported (via an SSIS package) from a linked server. The catch is that the 
imports can happen throughout the day, so that the lookup table would often 
have to be built while users are accessing it via the front end app (also 
written in Delphi).

My idea is to name the new lookup table by appending the date & time, then 
drop and recreate a synonym to point to it. I'm thinking that this will cause 
no locks because a running query will point to the old table during the 
DROP/CREATE SYNONYM. There may be an occasional problem if a query runs in 
between execution of the DROP & CREATE statements, but I think I can manage 
this easily enough with Delphi's exception handling.

Am I on the right track, here, or is this a bit wacky?

(Nb. I don't simply update the lookup table because there are issues with 
undocumented legacy code, business rules, and data changes by 
accounting/finance that have me prefering to start from a clean slate each 
time.)

Thanks!
- Al

0
Utf
1/18/2010 1:05:01 AM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
624 Views

Similar Articles

[PageSpeed] 45

I wonder if you can rename a synonym that is being referenced in a currently 
executing query.  If, as I suspect, there is a lock of some type held on it 
then your rename could be blocked until such time as your rename comes up in 
the worker queue, which depending on application design could be quite a 
while.  :-)

I would imagine that all pending executions that are going to reference the 
synonym will also have to have their query plan recompiled since the 
underlying object reference is changed.

Having said all that, I still think it should work.  You might want to try 
redoing the synonym in a transaction, so that nothing will be able to 
reference it until the repoint is complete.

You can obviously test out everything I mention above easily with a few SSMS 
connections.

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Allen_N" <AllenN@discussions.microsoft.com> wrote in message 
news:AEA28409-60BE-4E49-B6E1-4F72B2A7FAE9@microsoft.com...
>I am using a Delphi program to rebuild a lookup table from a source table
> imported (via an SSIS package) from a linked server. The catch is that the
> imports can happen throughout the day, so that the lookup table would 
> often
> have to be built while users are accessing it via the front end app (also
> written in Delphi).
>
> My idea is to name the new lookup table by appending the date & time, then
> drop and recreate a synonym to point to it. I'm thinking that this will 
> cause
> no locks because a running query will point to the old table during the
> DROP/CREATE SYNONYM. There may be an occasional problem if a query runs in
> between execution of the DROP & CREATE statements, but I think I can 
> manage
> this easily enough with Delphi's exception handling.
>
> Am I on the right track, here, or is this a bit wacky?
>
> (Nb. I don't simply update the lookup table because there are issues with
> undocumented legacy code, business rules, and data changes by
> accounting/finance that have me prefering to start from a clean slate each
> time.)
>
> Thanks!
> - Al
> 


0
TheSQLGuru
1/18/2010 1:24:00 AM
Good advice, Kevin. Thanks!


"TheSQLGuru" wrote:

> I wonder if you can rename a synonym that is being referenced in a currently 
> executing query.  If, as I suspect, there is a lock of some type held on it 
> then your rename could be blocked until such time as your rename comes up in 
> the worker queue, which depending on application design could be quite a 
> while.  :-)
> 
> I would imagine that all pending executions that are going to reference the 
> synonym will also have to have their query plan recompiled since the 
> underlying object reference is changed.
> 
> Having said all that, I still think it should work.  You might want to try 
> redoing the synonym in a transaction, so that nothing will be able to 
> reference it until the repoint is complete.
> 
> You can obviously test out everything I mention above easily with a few SSMS 
> connections.
> 
> -- 
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
> 
> 
> "Allen_N" <AllenN@discussions.microsoft.com> wrote in message 
> news:AEA28409-60BE-4E49-B6E1-4F72B2A7FAE9@microsoft.com...
> >I am using a Delphi program to rebuild a lookup table from a source table
> > imported (via an SSIS package) from a linked server. The catch is that the
> > imports can happen throughout the day, so that the lookup table would 
> > often
> > have to be built while users are accessing it via the front end app (also
> > written in Delphi).
> >
> > My idea is to name the new lookup table by appending the date & time, then
> > drop and recreate a synonym to point to it. I'm thinking that this will 
> > cause
> > no locks because a running query will point to the old table during the
> > DROP/CREATE SYNONYM. There may be an occasional problem if a query runs in
> > between execution of the DROP & CREATE statements, but I think I can 
> > manage
> > this easily enough with Delphi's exception handling.
> >
> > Am I on the right track, here, or is this a bit wacky?
> >
> > (Nb. I don't simply update the lookup table because there are issues with
> > undocumented legacy code, business rules, and data changes by
> > accounting/finance that have me prefering to start from a clean slate each
> > time.)
> >
> > Thanks!
> > - Al
> > 
> 
> 
> .
> 
0
Utf
1/18/2010 2:35:01 AM
Reply:

Similar Artilces:

Counting Number Records per Unique ID, saving it to another table:
I have a form frmVendors with a subform frmPayments. The main form shows information on vendors, and the subform is a continuous form which lists the checks paid out to each vendor. Users are typically entering new payments into the subform on the main form. The forms are linked to tables: Linked by VendorID (one to many) tblVendor: Key=VendorID, Vendor Name, Vendor Address, Vendor State, Vendor City tblPayments: Key=CheckID, VendorID, PaymentAmount There is a field in tblVendor called NumberChecks to show the total amount of checks paid out to that vendor. There is a text box on th...

Pivot Table inquiry
How can I remove the field length restriction in a pivot table? It looks like it cuts it off at about 150 characters. ...

Adding a series of tables
Hello, all. I am an experienced programmer but a rookie Excel user. I wonder if someone can help me with a simple problem? I am the scorekeeper for my son's Little League team and I want to keep track of the team's statistics. I have two tables per game, showing the hitting and pitching statistics in the usual way, e.g., AB R H RBI Fred 5 2 3 2 and so on. I have my spreadsheet set up with each game in a separate worksheet. What I want is a "season total" table that sums up the statistics for all of the games. Since the playe...

Integration Manager latest version?
What is the IM 10's latest build? I have 10.00.0932. Thank you, - Greg Crowe ...

Merging Workbook Table data Based upon Value comparisons
I have two workbook tables (Two different workbooks) with two matching column names. What I wish to do is to merge values from one table to another, but ONLY for those records inwhich these two columns have matching values. Would this be possible? Jay Are you saying you have two workbooks, or are the tables within one workbook? "jayceejay" <jayceejay@discussions.microsoft.com> wrote in message news:AC73B2C7-83EF-4D27-A464-32AEEE7D4214@microsoft.com... >I have two workbook tables (Two different workbooks) with two matching >column > names. What I wish to do ...

How to manage new data with external data in Excel?
Hi, I'm setting up a business planning worksheet into which I import external data from another Excel table using the built in driver. I import a few columns and then add some extra columns required to the right, such as formulas and other validated fields. The problem is that whenever new data is added or existing data is removed, the columns to the right of the external data do not move as I would like them to. In "External Data Range Properties" I ticked "Insert entire rows from new data, clear unused cells." but that doesn't seem to work. Can anyon...

PIVOT TABLE FORMATTING #2
If I have a pivot table with the following info: County, date, and lot #'s sold by county; how can i sort this horizontally so that the counties go across the top and under each county it sums up the number of lots? Hi, Put the Country field into the Column area. Do you really want to sum lot numbers or do you want to count lot number? To count or sum them they would be put in the Data area. Hope this helps because we don't have much to go on. If it does please click the Yes button. If not give us more details. What else is on your pivot table, are you counting, summing ...

Integration Manager error 04-15-10
I am on GP10 sp3. I am trying to run an integration to just change the item description on a list of items. I have successfully set up many integrations but am really having trouble with this one. All I have is the item number and the description to update in the .csv source file. Here is the error I am getting: DOC 1 ERROR: A value exceeding the maximum length was found in the 'Item Number' field of the root recordset. Maximum length is 0. Any ideas on how to resolve this? Are you using an alternate Item Maintenance window? "davidf." wrote: >...

Caption title is not appearing in caption table
I'm using Word 2003. Currently my List o' Captions looks like: Figure 1......1-3 I want it to display the caption text instead of (or along side) the Figure number. How?? Seems that I've clicked and checked everything, but probably not. Thanks! Hello TFX TFX Kurt wrote: > I'm using Word 2003. Currently my List o' Captions looks like: > > Figure 1......1-3 > > I want it to display the caption text instead of (or along side) the Figure > number. select the very first character of the list. Since it's supposed to be a TOC...

Auto-refresh Pivot Table upon opening
Over the past couple of days, I have been asking questions about a macro that auto-refreshes a pivot table upon opening it (meanwhile I have a macro that works). Yesterday I noticed that the Pivot Table options (Excel 2002) provides an option "refresh on open." Does selecting this option do exactly what I was looking for? Yes, the "Refresh on open" setting will do what you want, unless the worksheet is protected. If it is protected, you can run a macro when the workbook opens, to unprotect, refresh, and protect. K. Georgiadis wrote: > Over the past couple of da...

Lookup function 02-14-10
I have excel 2003. I did a vlookup for my transcripts page. In the first vlookup i needed it to look for the letter grade and give me the gradepoint average. Now in the other cell i need it to look for the grade. In the cell that gives the gradepoint average it has a formula in their already. Can i still do a vlookup if the cell that has the information has a formula in it already. Example. The cell that has the formula adds all the grades from each class and gives me an accumluated gradepoint average for the ten classes taken. Now that gradepoint average I want a letter ...

Pivot Table Field Header
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hello, <br> I'd like to find out if there's a way to HIDE the FIELD HEADERS from Pivot Tables. <br> I can't find it in the Pivot Table Options which seems like the place it would be found. <br><br>Thanks in advance. ...

DNS issues, reverse lookup, MX records
Hello, I've done some searching in the groups, but I'd like to pose a specific scenario. We are getting these errors: #4.7.1 smtp;450 4.7.1 Client host rejected: cannot find your reverse hostname, This is the situation. Our domain name is registered with network solutions. Our website is hosted with Yahoo on a business account. Yahoo used to host our email as well. Recently we put in a local SBS 2003 server to host our Exchange locally. Originally our Network solutions account had all DNS pointing to Yahoo's name servers, as it should have. We wanted to switch the MX reco...

How do you get a TRUE refresh of a Pivot table
Oh Pivot tables are so good and boy, do they save on calc time and size of file! BUT.........I notice that if some of the data it relies on is changed, by some magic, it still remembers the old data and, that old data will also be shown in the table unless you physically delete those references using the drop downs. I have found that sometimes you can clear the old references by re-running the wizard backwards as it seems to refresh those references. But that doesn't work in all situations. Is there a way to remove old references automatically AND how is it possible that they still exist?...

No Group dialog box appear when working in Pivot Table
I'm trying to group a range of dates in pivot table. According to help files a dialog box should appear when selecting Group..., but nothing happens except a grouping of the entire marked range. How do I go about in order to make the Grouping dialog box show? Best Regards PerD > PerD, This might be something particular to my set-up (XL2K), but I have to build the table initially by specifying the data range *exactly* (click'n'drag on the worksheet) for grouping dates to be an option. If I use a blanket source (eg: A:B) it won't cut it. Try it again from scratch, the...

Managing workstations using the management console
Hi, I have asked this question before but didn't really get anywhere. I am running 8 Windows 2003 servers, I am slowly migrating all of our companies work stations to authenticate against the domain. On the workstations that are now showing up in my list of Computers on the network I am unable to remotley view the Device Manager in the management console, I get the follwoing message when I try to launch it: ------------------------------------------------ Device Manager Unable to access the computer user.local The error was: Access Denied ---------------------------------...

formula in a pivot table?
Hello guys, is that true that excel-pivot is only able to sum (meanvalue is no selectable) from a formula made table. usually you put by drag and dro the data field into your table. but you also can put by hand a formula which is quit useful sometimes. after lunching the data-field in your pivot you can say pleasy show me the minimum or the meanvalue. It looks like that is not possible when you create a data-field using formula. then excel is just able to sum that ****... do you have any other idea?? cheers, filipu -- filipu ------------------------------------------------------------...

How to add Custom Lookups
Hello all, I am working on an custom integration to CRM. I desperately need the option of allowing the users to select a value from a custom lookup. unfortunately, the pickuplist option only allows the items to be manually maniplated using the forms customization. I need an option to feed in the data thru api or some other means. comments!! If we manipulate the xsl files and add any customizations drectly then it will be overwritten the next time we publish any changes. By customization I mean, for ex:adding a button on to the form. Any other suggestions. regards rojer You de...

Sharing only forms, not tables
I have an access database. I have designed several forms which shows information retrieved by querries. I would like to share this forms with my colleagues via server. But, i dont want my colleagues to access tables and querries, because of security reasons. Could you pleae tell me a good reference to do that? How can i keep my tables in a seperate place than forms. Thanks That's not going to work. Forms have no data of their own. They read their data from and write their data to tables. If your users have not rights to read or write the tables, your forms won't work either. -- A...

Index & Match on Pivot Table
Hi, I'm using Office 97 - and was wondering if there is a way to do an Index (Match), (Match) type function on a Pivot Table? I've tried it - but I get a #value error. Does anyone know a way around this? Nelson Nelson wrote: > Hi, > > I'm using Office 97 - and was wondering if there is a way to do an Index > (Match), (Match) type function on a Pivot Table? > > I've tried it - but I get a #value error. Does anyone know a way around > this? > > Nelson > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextur...

Pivot tables- 0 counts don't display
How can I get a 0 counts to display in the pivot chart and subsequent graphs. Thanks to Debra for answering my last question, it was a big help! You could enter a dummy record for each combination in the table, and leave the door field blank. For example: door floor core doortype 1 1 1 2 1 1 3 1 1 These records will be displayed in the column and row area, but won't be counted in the data area, where 'Count of Door' is used. Jim wrote: > How can I get a 0 counts to display in the pivot chart > and subs...

Lookup on custom entity without relationship
First,I will show existing out-of-the-box sample in MS CRM3: Address entity is in relation to account M:1.When I stay on order entity and press on lookup addresses I get result filtered only for account selected in order. Order there is no relation to address entity. My problem: I created new CustomEntity1 with relationship to Account 1:M. Now, I want to add to Order field from CustomEntity1, filtered only by currently selected in order Account.If I add relationship CustomEntity1->Order, I think, I will get in lookup all CustomEntity1 records without filter by order account. Please help ASA...

Rebuilding OWA
Hello - I installed SP2 without installing SP1 for Exchange 2003 and ran into the infamous problem with OWA. I managed to get the pages to work by replacing the \Exchsrvr\exchweb\controls with \Exchsrvr\exchweb\6.5.7226.0\controls. However, last night the server was reboot and this morning I am having problems with OWA agian. This time - I'm getting a "invalid procedure call or argument" line 1038 char 1 in ctl_view.htc on every page. I've tried reinstalling SP2, replacing the controls directory with the 6.5.7226.0 version, and a little tinkering, but no soap. Anyone...

What is the quickest way to copy a table to an array?
Hi everyone, I have a table to process. It can vary in size between 2-4 columns and 2-100 rows. The table is a simple thing, containing just plain text. What is the quickest/nicest way to copy the content of the table to a 2D array? Stephenc stephenc wrote: > Hi everyone, > > I have a table to process. It can vary in size between 2-4 columns > and 2-100 rows. The table is a simple thing, containing just plain > text. > > What is the quickest/nicest way to copy the content of the table to a > 2D array? > > Stephenc I would do this in th...

How do I distinguish between upper and lower case with pivot tabl.
How do I distinguish between upper and lower case items when creating a pivot table, e.g. a list of warehosue names which have combinations of both upper and lower case, lower case being one type of warehouse and the upper case ones representing a different type What you could do is add a column to the original table that attaches a "1" in front of every warehouse name whose first character is upper case. =IF(CODE(LEFT(A2))<91,1&A2,A2) And fill down. Then use this new column in your pivot table. HTH Jason Atlanta, GA >-----Original Message----- >How do I dist...