Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. LogicalmanUS

    sql server2000: join querry on one table

    From your initial post, you indicate that both husband and wife records exist in the same table, just on different rows. If that is the case, you problem exists in the table name of the second table accessed. You wrote: select a.name , b.name, a.phone from table1 as a left join table2 as b on...
  2. LogicalmanUS

    Import Failure on dates

    When importing from text files (I have a number of such jobs running nightly) I tend to populate a basic varchar table (one having ALL columns of varchar data type), and then simply call a SProc to format the data and transfer it into the correct table(s). This has two bonuses (for me). One...
  3. LogicalmanUS

    Preventing blocking locks for (required) long running transactions

    regilobo, Interesting set-up. My initial reaction was to mention Rowlocking, but, as I am nwo aware, 2000 determines it's own level of locking, so that one's out. What if you were to use hash tables to emulate the real tables that require updating. For example, a request is made a select...
  4. LogicalmanUS

    SQL Gurus... HELP!!!

    JoshWithrow, Try this one then: SELECT o.Name AS Office, COUNT(e.ID) AS OEmpCount, SUM(p.mListQty) AS OPosts, (select count(Agent) from aPostcardTracking where Office = o.Name GROUP BY Office ) AS PEmpCount FROM aPostcardTracking p RIGHT OUTER JOIN aEmployees e ON p.Agent = e.ID RIGHT...
  5. LogicalmanUS

    Function For Fixing Date

    icebo3, You can use the first part: DECLARE @myDATE datetime DECLARE @myDATE2 datetime DECLARE @iDAYS int SET @myDATE = GETDATE() SET @iDAYS = datepart(dw,@myDATE)-2 SET @myDATE2 = GETDATE()-@iDAYS --This is Mondays Date to introduce a variable into your own code: select a,b from...
  6. LogicalmanUS

    Substitute for OPENROWSET

    JamesLean, The use of: SELECT * FROM (EXEC myproc1) would serve no practical purpose. My reference to 'You can use one SProc to call another SProc and receive it's result record set' was meant to refer to the ability to directly access the result set from a SProc as you would a standard...
  7. LogicalmanUS

    Correlated subquery SQL not working

    organicglenn, Hmm, I've spent the last 10 minutes looking at the results of the SProc (I recreated it locally and got the printout in QA), and I've now got a brain-ache!! OK. Right. First off, you say you are returning multiple rows based on tripusers rather than simply on Trips, and you...
  8. LogicalmanUS

    Cursor Writing

    sr727, OK. I re-created the tables (including the one missing from the first posting - OilCautions), and all appears quite normal. To answer your question as to how to change or delete the Oil.OilName and Cautions.Description columns, this would depend on under what circumstances are the...
  9. LogicalmanUS

    Stored procedure to return table?

    Jason, OK, no great problem there. In that case you do need a SProc (as per your first suggestion) to return the results, as you will need to create a string for the TSQL statement, and then execute it. Here goes, copy and paste it as a new SProc: <CODE> -- Written by Logicalman for...
  10. LogicalmanUS

    Preventing blocking locks for (required) long running transactions

    regilobo, From re-reading your initial question, it comes to mind that if the remote DBs are causing updates to the data, and this appears to be a Subscription PULL type set up, then you may be better off simply storing the data pulled from the remotes, and then run all the transactions once...
  11. LogicalmanUS

    Function For Fixing Date

    icebo3, No, the 2 is the SQL WeekDay for Monday. The WeekDay function in SQL returns 1 for Sunday through 7 for Saturday. Therefore, the above algorithm takes the current WeekDay number, subtracts 2, and the result is the number of days elpased since the last Monday. It will fail if it...
  12. LogicalmanUS

    No PK's, No FK's, No Indexes, No Service!

    pulsar6, You state that the tables 'work together but do not relate to each other', how do they achieve this? What type of application drives this database? is there any coding in the Application that makes these apparent joins? As twifosp correctly points out, what denotes 'old data' in the...
  13. LogicalmanUS

    Preventing blocking locks for (required) long running transactions

    regilobo, Are you certain you are not reaching a Deadlock situation here? If so, and in any case, you may want to use the SET LOCK_TIMEOUT function to cause the secong thread to timeout quickly. I wasn't aware of the stated Client/Server deadlock. Where is the reference to this? Thanks...
  14. LogicalmanUS

    Stored procedure to return table?

    Jason, Here you go: select text, sum(case text2 when 'b1' then num else 0 end) AS B1, sum(case text2 when 'b2' then num else 0 end) AS B2, sum(case text2 when 'b3' then num else 0 end) AS B3 from tPIVOT group by text (I named my table tPIVOT but the column headers were the same as per your...
  15. LogicalmanUS

    Substitute for OPENROWSET

    Hexonx, I'm unsure what you mean when you state that the result set is already in the database. When running a Sproc, the result set is alive only as long as the SProc is there, unless it is used as an output from the SProc, and in that case it is only alive as long as the process that called...
  16. LogicalmanUS

    Function For Fixing Date

    icebo3, Here's the function to find the Monday of the current week (up to and including Saturday) and the number of days lapsed since the Monday. Copy and paste it in QA and run it from there: DECLARE @myDATE datetime DECLARE @iDAYS int SET @myDATE = GETDATE() SET @iDAYS =...
  17. LogicalmanUS

    SQL Gurus... HELP!!!

    JoshWithrow, The below code does 75% of your needs. Unfortunately I'm unclear on exactly what you need in respect of '% of Employees that show up in the PostcardTracking table per office'. Can you give a further explanation as to exactly what you need. Is it the % of times an Office appears...
  18. LogicalmanUS

    Cursor Writing

    sr727, I think I need a little more information as to what you intend to do with the tables, e.g. what records need updating, deleting etc, and, if this involves both the above tables, then where is the key field link? One thing you should also consider is that Cursers are not always the...
  19. LogicalmanUS

    Sending email to several contacts

    Marco, You can use the recordsetclone object based on the recordsource object of the form. The basic advantage is that it makes a copy of the records rather than the actual source: Dim rst As Recordset Set rst = Me.RecordsetClone At this point you can use the new recordset object (rst)...
  20. LogicalmanUS

    Connecting to MS Access Database via Stored Procedure

    SQLBill, I think ter79 needs clarification of terms here. SQL Server is (predominantly) a server-side application. Access is (predominantly) a Client-side Application. Stored Procedures (SProcs) and Views reside solely in SQL Server. Queries reside solely in Access MDB files. An...

Part and Inventory Search

Back
Top