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 derfloh 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. davism

    SQL to select most recent row from current date.

    Hi, I have a table with something like: MemberID[tab][tab]EffDate[tab][tab]TermDate ----------------------------------------------- 1[tab][tab][tab][tab]12/01/2018[tab][tab]12/31/2018 1[tab][tab][tab][tab]4/01/2019[tab][tab]5/31/2019 1[tab][tab][tab][tab]7/01/2019[tab][tab]12/31/2019 I want...
  2. davism

    1 SP on a different DB to used for multiple databases

    Would not a SYNONYM be even better? CREATE SYNONYM and then DROP it?
  3. davism

    1 SP on a different DB to used for multiple databases

    We have some database that like span different states. We have code for a stored procedure and that stored procedure resides in a Stage database on the same server so a linked server is not necessary. CREATE PROCEDURE dbo.TestA (@StateID CHAR(2)) AS BEGIN SET NOCOUNT ON UPDATE...
  4. davism

    Index on Temp Table

    Yep, understand that. It's basically saying the knowledge of the DBA's as general practice is not wise. Although, they had run things very well in all the different environments for varying sizes. It appears you all are saying there is no general practice on SQL Server. Basically a "try before...
  5. davism

    Index on Temp Table

    I suppose I'm missing the point here especially if the table is not a temp table. I mean, I have previously dealt generated from persistent tables in the order of millions of rows to generating a small subset to the of 10's of thousands. Our DBA always told us to create the temp table and then...
  6. davism

    Index on Temp Table

    Hi, In SQL Server 2008 R2 and up, is it a performance issue if in a stored procedure a temp table is created, then loaded, then an index created on it? Or should it create that a temp table is created, an index created on that temp table and then loaded? Also, if it not true that IF after...
  7. davism

    DDL to files for DB artifacts (Stored Procedures, Views, Triggers, etc) in a SQL Server DB.

    Hi, Is there a way or command to use to get the DDL's to a file for each DB artifact in a SQL Server database? So, for instance, if there are 3 stored procedures in a database: I want the DLL for each stored procedure to go into it's own unique file: stored procedure 1 name is Test1. All...
  8. davism

    SQL SERVER 200 and GROUP BY and HAVING with COUNT

    That is working out great! Thanks so much and very much appreciate that.
  9. davism

    SQL SERVER 200 and GROUP BY and HAVING with COUNT

    Correct, but it also produced: 5, 1, 3 6, 1, 3 BUT that was an easy fix as with the "on" condition I added "...and t1.testtypeid=2". The results sure enough only gave me the 3, 1, 2 10, 1, 2 Let me review this a little more and apply it.
  10. davism

    SQL SERVER 200 and GROUP BY and HAVING with COUNT

    Hi RyanEK No, unfortunately, that is not going to work. If you take the example of: insert into @temp select 2, 1, 1 insert into @temp select 3, 1, 2 insert into @temp select 5, 1, 3 insert into @temp select 6, 1, 3 insert into @temp select 7, 1, 4 insert into @temp select 8, 2, 3 insert...
  11. davism

    SQL SERVER 200 and GROUP BY and HAVING with COUNT

    Hi all, I'm having a little bit of difficult and hopefully somebody can help me out here. I have a table like: GUID, TESTID, TESTDATE, TESTTYPE, TESTTYPEID Now what I want to do is I know there are multiple rows with the same TESTID for the same TESTTYPEID. So, I am trying to get an...
  12. davism

    Global Temporary Table and SQL Server 2000

    Ok, let me take a stab and this. I will probably go off a child proc just based on previous findings from execution plans. But let me take a stab at this and will let you know ASAP. Thanks!
  13. davism

    Global Temporary Table and SQL Server 2000

    I was hoping to not kill the DMBS with all the queries that would provide repeated data. However, you have some very good informaton here and it coincides with the performance with the use of the keys and such. One quick question though, for the optimizer to know of the existence of the key...
  14. davism

    Global Temporary Table and SQL Server 2000

    Thanks George on the explanation for the Global Temporary table and all. One of the thoughts I had on the global temptable was to create with the SPID in the name. BUT you sorta got the whole idea shot down when you mentioned that even if I used global temp tables that it would then still...
  15. davism

    Global Temporary Table and SQL Server 2000

    I have a question with regard to temporary tables and a usp_Test1 stored procedure (SP). This is related to a SQL Server 2000. (Yes, I know it's older technology but it is what it is.) I'm looking a situation: • On a website, when a User or whomever requests a “Click Here” • The above SP is...
  16. davism

    Split on Comma to use with an IN statement

    You're right. Thanks. :-)
  17. davism

    Split on Comma to use with an IN statement

    Hi all, How can I split information on a comma and put use for an IN statement? For example, I want to exclude certain states. Now, I want to take the variable that is passed in and put it in a temporary table then use the temporary table in a NOT IN (select * from #States) statement. How...
  18. davism

    Limit output to so many rows by like STATE.

    But what if I'm not using SQL Server 2005 but rather SQL Server 2000. Don't ask why and I'm not happy about it but do. :-(
  19. davism

    Limit output to so many rows by like STATE.

    Hi all, What is the best way to limit like the number of users per U.S. state to 5 or less? Let's say that I have a table where I have like: User State --------------- test1 AL test2 AL test3 CO test4 AL test5 CO test6 CO test7 CO test8 AL test10 CO test11 CO...
  20. davism

    NULL AND NOT NULL in SQL WHERE

    Thanks for the information. I ended up using you're initial statement with the OR. It works appropriately. I guess I was just looking too deep into this. Thanks!

Part and Inventory Search

Back
Top