Have a situation where I need to check 100+ columns in the dataflow against lookup values to make sure all values are valid, and wanted to take a poll. Would it be better to
1) load the data into a working table and use traditional stored procedure (either NOT IN or LEFT OUTER JOIN where x is null) in order to weed out my bad values against my lookup table.....example
SELECT a.Col1
b.Col2
FROM Table1 a
LEFT OUTER JOIN Table2 b
ON (a.JoinCol = b.JoinCol)
WHERE b.JoinCol IS NULL
This results in poor performance b/c my temp work table is not really optimized for joins over to the lookup tables & I have so many columns that I don't really want to add all these indexes - my thoughts were that the index builds would take longer than the table scans.
OR
2) Use a huge number of lookup transforms in my data flow and keep it all in SSIS.
#1 is easier to maintain (my opinion) for future purposes but slower b/c I don't want to deal with indexes on the work table b/c it will be highly volatile. So - less cumbersome but slower
#2 will be more difficult to maintain b/c of the sheer # of lookups (since I can't change the SQL statement @ run time I have to put them all in separate lookups). Probably will run faster though b/c I won't have to deal with the transfer of the data to the db and also will avoid the table scans from #1. So - more cumbersome but faster.
I need help from you guru's. I have this sp which normailzes some data to a temp table. I need to extend the "return results" select statement to join and get more data. So I add task_charge_type to the select and created 3 joins to get it all together. I can execute the alter sp command with no errors; however when I try to execute the sp I get the following errors:
Msg 209, Level 16, State 1, Procedure sp_psactualsbyweek, Line 69 Ambiguous column name 'PROJ_NAME'. Msg 209, Level 16, State 1, Procedure sp_psactualsbyweek, Line 69 Ambiguous column name 'PROJ_NAME'.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- sp_psactualsbyweek.sql -- -- This stored procedure generates a report of the actual hours worked by a -- resource by project and task for a specific date range from the table -- MSP_WEB_WORK.
ALTER procedure [dbo].[sp_psactualsbyweek] -- date range variables @startdate datetime, @enddate datetime as
-- temp table to hold normalized data create table #actuals_data ( RES_NAME nvarchar(510), PROJ_NAME nvarchar(510), TASK_NAME nvarchar(510), WORK_DAY datetime, HOURS decimal(25,6) )
-- cursor to convert denormalized data declare actuals_csr cursor for
SELECT DISTINCTr.RES_NAME, p.PROJ_NAME, a.TASK_NAME, w.WWORK_START, w.WWORK_FINISH, w.WWORK_VALUE FROMMSP_WEB_RESOURCES AS r INNER JOINMSP_WEB_ASSIGNMENTS AS a ON a.WRES_ID = r.WRES_ID INNER JOINMSP_WEB_WORK AS w ON w.WASSN_ID = a.WASSN_ID INNER JOINMSP_WEB_PROJECTS AS p ON p.WPROJ_ID = a.WPROJ_ID wherew.WWORK_TYPE = 1 -- actual work and((w.WWORK_START between @startdate and @enddate) or (w.WWORK_FINISH between @startdate and @enddate)) order by 1, 2, 3, 4
open actuals_csr fetch next from actuals_csr into @res_name, @proj_name, @task_name, @start, @finish, @value while @@fetch_status = 0 begin select @days = DATEDIFF(day, @start, @finish) + 1 select @count = 0 while @count < @days begin select @date = DATEADD(day ,@count, @start) insert into #actuals_data values( @res_name, @proj_name, @task_name, @date, ((@value/1000)/60) ) select @count = @count + 1 end
fetch next from actuals_csr into @res_name, @proj_name, @task_name, @start, @finish, @value end close actuals_csr deallocate actuals_csr
-- return results select RES_NAME, PROJ_NAME, Task_Name, Task_Charge_Type = case when pe.ProjectEnterpriseOutlineCode30ID = 530 Then 'Expensed' when pe.ProjectEnterpriseOutlineCode30ID = 529 and te.TaskEnterpriseOutlineCode30ID = 527 Then 'Capital' end, Work_Day, sum(HOURS) as 'TOTAL HOURS' from #actuals_data INNER JOINMSP_WEB_PROJECTS AS p on #actuals_data.PROJ_NAME = p.PROJ_NAME INNER JOINMSP_VIEW_PROJ_PROJECTS_ENT AS pe ON pe.WPROJ_ID = p.WPROJ_ID INNER JOINMSP_VIEW_PROJ_TASKS_ENT AS te ON te.WPROJ_ID = pe.WPROJ_ID where WORK_DAY between @startdate and @enddate group by RES_NAME, PROJ_NAME, Task_Name, Work_Day order by res_name
drop table #actuals_data
--Msg 209, Level 16, State 1, Procedure sp_psactualsbyweek, Line 69 --Ambiguous column name 'PROJ_NAME'. --Msg 209, Level 16, State 1, Procedure sp_psactualsbyweek, Line 69 --Ambiguous column name 'PROJ_NAME'.
I have created an SSIS package with protection level - EncryptSensitiveWithUserKey. It is running alright from Visual Studio Environment but i need to call the package from an application, so i created a stored proc to call it, but getting the following error while running the stored proc:
Error: 2007-04-16 17:55:05.78
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
When do a fact table load...I have to perform lookups against the dimension tables. The dimensions tables I have support slow changes, however, and thus have multiple rows for a single legacy key under different effective start and end dates. In order to do this lookup, I have to not just join on the legacy key, but also validate that the date of the transaction I'm loading is between the effective date range of the dimension item.
It seems the Lookup task only supports equijoins. Am I missing something here? How is this accomplished if you can use greater than or equal to and less than type join conditions?
All I am in trying to clean and standardize the data during the ETL processes using the €œLookup Data Flow Transformation€? in SSIS€¦ I am able to clean data by replacing the values in columns with values from a reference table, using an exact lookup to locate values in a reference table. What I would like to do is €œif there is NO exact match€? replace it by e.g. zero or some other value which means €œno reference data available€?, how do I do this? Any help is much appreciated. Thanks, Manojkumar
I am just getting started studying SSIS with Kirk Haselden's "Integration Services" book. The problem I am trying to solve would seem easy enough to solve in code, but I am still early in the book and would like to be able to focus on the aspects of SSIS that would help me expedite this with SSIS, or to find out early whether what I need to do cannot be easily done.
The problem itself is simple enough: I have a database of roughly 100 tables. Ignoring the poor normalization in the database for the moment, my more pressing problem is that that I need to rekey all of the main OLTP tables from a mashup of different key schemes to UNIQUEIDENTIFIERS. For example, Client table is presenrly keyed on an INT, Client Number. ClientFile table is keyed as FileType = NVARCHAR(2), ClientNumber INT, FileNumber INT (incrementing, meaningless number). Child tables to ClientFile are the same key structure as ClientFile, plus yet another (incrementing, meaningless number) INT, etc like this. I would like to know if and how or where I should be looking to convert the Client table to a UNIQUEIDENTIFIER key, and the same for the ClientFile, makes its key also a GUID, and have a reference to the new Client tables GUID key as a foreign key in the ClientFile table, and on and on like that. The Client is at the top of the food chain.
In essence, I would like to have every table's key be called ID and be a UNIQUEIDENTIFIER (GuidRow), and I would like for example, the ClientFile table to reference the Client table with a column named ClientID. I would like ClientFile's children to have a foreign key called ClientFileID, and their own keys to be ID (RowGuid).
There are also several lookups in each table where, of course, the actual string values were stored instead of a key to the value (i.e. full state or country name instead of a code from a state or country lookup table) that I need to convert to something more sensible, like replacing the state name with a state code and a country name with a country code and link to appropriate respective tables. :) In fact, some of the values I need to break out from columns could also be keyed with RowGuids as well and I would much prefer to use those than string or INT keys.
Other than those problems, most of the rest of the data in those tables could essentially ba a straight copy operation since the source database is SQL Server 2000 and moving to SQL 2005 (one notable exception is that I am converting ntext columns to nvarchar(MAX) columns.
I am assuming this is probably ridiculously simple and I just haven't found my way there yet, but I still have much of this book and the help files to go through and the Index didn't give me any comfort that this was something I will or will not be able to do easily.
The real help I am looking for is two fold: a) somebody tell me to stop reading this 700 page (very well written) tome if I would be better off writing this all in code myself, and b) if this is something that most of you could do with SSIS with both hands tied behind your back, please at least help me focus on the important transforms and tools so that I don't smend a month becoming a data warehouse wizard and ultimately not solve the problem I am most concerned with.
Please be mecriful with the heat, I have already confessed that I am new to this and am scrambling to come up to speed as fast as I can, but am beginning to think this problem is either to trivial for coverage in this book, or perhaps just not what SSIS was designed to do.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
A project I'm working on consists of a Main stored procedure which then runs about 30 nested procedures. The client wants to know when a certain nested SP fails, but wihtout rollbacks, as they may want to fix a data item manually (such as a missing Patient ID, that they have to call someone about). At this point, we don't want to roll back anything but halt the rest of the nested SP's and send out an email to someone that they have to check out a missing PatientID.
I'm wondering if an SSIS package would handle this better than just using a Stored Procedure. When that SP runs, it will also update a "Process tracking" table in the backend, that would update [Lastprocessran] with a number. I'm thinking that if they run the main SP again, after making a manual correction, that they could re-run the main SP, and it would bypass any step that already ran successfully based upon the [Lastprocessrun] number.
I have an SSIS package with around 25 lookups. Developing the package itself was slow. Now, everytime I try to load the package it takes forever and whenever I execute it I get an error.
Here are my questions:
1. Is there a way I can optimize the package? 2. Is it abnormal to have so many lookups? I am loading a dimension table with many fields and I need to look up on 25 tables to get the keys. I know one alternative is to use left joins in the source query and get the keys in the Source itself but we can have more visibility of what's happenning with Lookups. I would like to know other possibilities with lookups.
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
Iam working with a query where iam joining different fields from two tables and this is working well SELECT Ind.Industry_Name,Com.Company_Name,Com.Company_Address FROM Industry AS Ind INNER JOIN Company AS Com INNER JOIN ON Ind.Ind_ID_PK = Com.Ind_ID_FK Here iam getting the values depending on the Ids of both the tables. But now i want to join three different tables..can i use this Query SELECT Ind.Industry_Name,Com.Company_Name,Com.Company_Address,Pla.Plant_Name,Pla.Created_On FROM Industry AS Ind INNER JOIN Company AS Com INNER JOIN Plant AS Pla ON Ind.Ind_ID_PK = Com.Ind_ID_FK and Ind.Ind_ID_PK = Pla.Ind_ID_FKIam getting an error like : Incorrect syntax near 'Ind_ID_FK'. i.e here Com.Ind_ID_FK . I checked by removing and Ind.Ind_ID_PK = Pla.Ind_ID_FK but iam getting same error.
I am making a stored procedure, and I need to set some variables based on the results of a JOIN statement. If I something like: SELECT table.field, table.field2 FROM table INNER JOIN table2 ON table.field = string How could I get the results and set them in variables? Any help would be greatly appreciated. GKC
I'm trying to replicate a SQL join across two seperate data sources in SSIS. If I were to write SQL to do this, it would be as follows:
SELECT Costs.CostRateEntryId,
Costs.UserId,
Costs.HourlyRate * 8 AS DailyCostRate,
Dates.DateKey,
Dates.ActualDate,
FROM Costs
INNER JOIN Dates ON Dates.ActualDate >= Costs.EffectiveDate AND Dates.ActualDate <= Costs.EndDate
Unfortunately, as the tables 'Dates' and 'Costs' are in two seperate SQL2005 systems, I can't really do this. I was hoping that it could be achieved in SSIS, but I cant seem to find any way that I can do a join that's <= or >=.
Can anybody help me out in 1) implementing cursors in SSIS. I want to process each row at a time from a dataset. I was trying to use Foreachloop container but in vain. Can you please answer in detail.
my few other questions are: 1) Can i do nested inner join in SSIS. If yes, how? ( I have three table i need to join Tab1 to table 2 and get join the table 3 to get the respective data) 2) I have a resultsets. I want to split the data according to data in a col. Say for instance: Col1 Col2 A 1 A 2 B 3 C 4 C 5 i want to split the data according A, B and C . i.e., if Col1= A then do this, if Col1= B then do this..etc. How can i do this using conditional split task in SSIS
This example is working: Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'. I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.
First, does anyone know any good SQL Server sites with articles and whatnot on query design, etc. Not so much basic "How to get data out of your table", but more complex topics like conditional stored procedures, working with triggers, etc. The MSDN is helpful but I often have trouble understanding what's going on.
And second, any pointers (or links) on how I can go about having a stored procedure query a pair of product tables to get information to display in my shopping cart? I've got:
So depending on if the 'IsCustom' field is True or False, I want to join the [cartitems].[partID] to either the [StdProducts].[stdpartID] or the [CustomProducts].[custompartID] to get the description and other information.
Eventually, I'll probably need to branch this type of procedure out even further (not just either/or scenario) to include the option of pointing to 6-12 different child tables depending on criteria within the parent table for a different scenario. Depending on performance, I could either hard code in the various child tables or have another table containing the table names and the appropriate key that would indicate which table to use.
Hopefully that made some sense, since I'm not entirely positive how to go about this type of thing or what you would call whatever I'm trying to do (and thus what I would be searching for).
I have a database of news articles and i have a stored procedure that basically pulls one from the database based on an ID number. The author (Press Contact) and publication are stored as just ID numbers and pulled in via JOINs.
SELECT Articles.date_published, Articles.headline, Publication.press_contact, Publication.pub_name, Articles.body FROM Articles LEFT OUTER JOIN PressContact ON PressContact.press_id = Articles.press_id LEFT OUTER JOIN Publication ON Publication.publication_id = Articles.publication_id WHERE (Articles.id = @ID)
Everything works great in this setup. However, we've recently added a press_id2 field to the articles table to be able to store a 2nd press contact. So now I need my stored procedure to pull out both press contact names and I'm not sure the best way to do that. I tried to JOIN the PressContact table a 2nd time on PressContact.press_id = Articles.press_id2 but that didn't seem to work. Can anyone give me any suggestions? Thanks in advance.
I have a multiple table dataset that needs to be returned, with at least 5 joins, some inner, some left outer.
Currently, this is done via a parameterized stored prodedure, which is used fairly frequently. The parameters only affect the where clause, not the joins.
Would it be better to create the view with the joins already done, then pass in the parameters with the stored procedure? Which is better for overall performance? Which is better for quicker response times to the calling asp.net application?
FROM [db_db].[dbo].[TEST] TC join [NEW_DB].[dbo].[Users]users on users.FirstName=TC.Username join [New_DB]..[Method_Master] Mstr on Mstr.Description=TC.Method join [New_DB]..[TestSource] TS on TS.Name = TC.TestName
basically there is a join between three tables to repalce all the varchars to the corresponding ID.
Please guide me in how should I go about in designing my SSIS package to achieve this requirement.
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
I have a stored procedure using UNION joins on three SQL queries. Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me. I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :) I need to return one result set, sorted by date... one complete result per day. eg: 5/15/2007 | XX | XX | XX | XX | XX | XX |5/16/2007 | XX | XX | XX | XX | XX | XX |5/17/2007 | XX | XX | XX | XX | XX | XX | Currently, when I run the query, I'm getting three separate date values for each date... eg:5/15/2007 | XX | XX | 00 | 00 | 00 | 00 |5/15/2007 | 00 | 00 | XX | XX | 00 | 00 |5/15/2007 | 00 | 00 | 00 | 00 | XX | XX |5/16/2007 | XX | XX | 00 | 00 | 00 | 00 |5/16/2007 | 00 | 00 | XX | XX | 00 | 00 |5/16/2007 | 00 | 00 | 00 | 00 | XX | XX |etc How do I fix this? I've looked through my query ad naseum and don't see anything that sets me off as "wrong". Here is the stored procedure if you can help. I'd really really love the help!
C R E A T E P R O C E D U R E sp_ApptActivityDate (@strWHERE as varchar(500), @strWHERECANCELED as varchar(500)) as exec ('SELECT [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts, SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_ALL_ApptActivity ' + @strWHERE + ' UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots, COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_Active_ApptActivity' + @strWHERE + ' UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts, SUM(length * 5) / 60 AS TotalCancelSlotHoursFROM dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + ' ORDER BY dbo.vw_ALL_ApptActivity.[Date] ' )GO
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
