InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 17 May 2008 08:56:11 GMT20http://www.sqlservercentral.com/Forums/Topic498630-338-1.aspxI want to initiate a Procedure and then return-continue execution. The reasoning behind this is to use SQL Express but because Express does not have an Agent I need to initiate a recurring task somehow.I have an external app feeding data in SQL so I am hoping there is a way, as part of the call by that external app, that I can initiate a 'task'.I am sure this is a fairly common need with SQL Express - ideas will be great.Mon, 12 May 2008 04:04:07 GMTrobertcaustinhttp://www.sqlservercentral.com/Forums/Topic500855-338-1.aspxHi. I read a post where the issue was similar to my position. I have read up on Crosstab Queries but am unsure how to implement or if they will provide a solution in my, case.I have two databases 'Database 1 and Database 2'. Database 1 is the starting point. Database 2 is being used to test. During testing development work as well as transactional data will be input into database 2. I would want to migrate data from the tables that hold the development work or data that I wish to keep back to database 1. So the question is, data already exists in the tables in Database 1. How would I migrate the additional data from the parent tables + child tables and retain referential integrity?Feedback or links to example code appreciated. Thank,Phil.Wed, 14 May 2008 13:26:29 GMTPhilip Horanhttp://www.sqlservercentral.com/Forums/Topic502469-338-1.aspxI'm not a full-time DBA, so excuse my style of expressing my question.I have a database which has 2 tables in SQL 2005. Both these tables have similar column names, EXCEPT for new extra columns in FY2007_DATA. I can visually see the difference in columns in Database Diagrams. My goal is to :- I want to compare FY2007 tbl column names to FY2006 column names and display only those columns as results that do not match.Tbl 1 :- FY2006_DATA Tbl 2:- FY2007_DATA With online reading and help I have managed to get this script to do exactly opposite of what i want. Below is the query /* This query compares the column names from two tables and displays the ones that have an exact match. It does not care for case-sensitiveness */ Select a.Table_Name, a.Column_Name, (b.Table_Name), (b.column_name) From [2006-2011].INFORMATION_SCHEMA.Columns AS a Join [2006-2011].INFORMATION_SCHEMA.Columns AS b on a.Column_Name = b.Column_NameWhere a.TABLE_NAME = 'FY2006_DATA'And b.TABLE_NAME = 'FY2007_DATA'AND a.Column_Name IN(Select Column_Name = LEFT(c.column_name, 20) FROM[H1B_2006-2011].INFORMATION_SCHEMA.Columns AS c WHERE c.TABLE_NAME = 'FY2007_DATA' )When I change "AND a.Column_Name IN.." to "AND a.Column_Name NOT IN.." so that the results will (should) display the extra columns in FY2007, in fact I do not see any results, but query executes perfect. How can I achieve my goal.?Thank youFri, 16 May 2008 23:00:36 GMTgauravushttp://www.sqlservercentral.com/Forums/Topic501330-338-1.aspxHi All,Great forum!!!Here's my problem. I'm trying to build a course schedule of classes for my job and I want to stop the course descriptions (varchar(max)) from repeating in each record. My customer feels once is enough. He only wants to see one record with a course description and all subsequent records with the same course description are returned... however, without any data in the course description field. Here's an example of what I'm trying to do:[b]Course Room Date Description[/b]CIS101 123 1/1/08 Basic CIS classCIS101 124 1/1/08CIS101 125 1/1/08CIS102 111 1/1/08 Advanced CIS clasCIS102 112 1/1/08CIS103 136 1/1/08 Bla Bla Bla Any Ideas??Thanks !Thu, 15 May 2008 08:00:39 GMTChrishttp://www.sqlservercentral.com/Forums/Topic479357-338-1.aspxI'm trying to convert the following Oracle PL\SQL to MSSQL 2005 and I'm struggling!!Would anyone happen to know how in the world to re-write the following in SQL Server??[b]SELECT LEAD(E.ID_PRVD_ORG,1,NULL) OVER (PARTITION BY E.ID_PRSN ORDER BY E.ID_PRSN,E.DT_BGN ) AS LEAD_ID_PRVD_ORG FROM E[/b]where E is my table nameThere doesn't appear to be an equivalent function in SQL Server to Oracle's LEAD function.Any help would be greatly appreciated.Thanks,DaveThu, 03 Apr 2008 10:12:51 GMTDavid.Mizellehttp://www.sqlservercentral.com/Forums/Topic500971-338-1.aspxDECLARE @I INTSET @I = 1WHILE @I < 10000BEGIN SELECT getdate() SET @I = @I + 1END Run the above in query analyzer.I was expecting to get a ton of records that will increment the time by 3 or 4 ms,but what I see is a set of records that increment on average about 14 ms. (not every record gets the incremented time of course, there are tens of records with one time and then another set of records with the incremented time)Funny part is if I output to a file instead the increment jumps to about 30 ms.What is wrong in my assumption about the 3 or 4 ms?Wed, 14 May 2008 20:05:14 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic499535-338-1.aspxI have start date and end date fields in database where I want to scramble the dates. How to create random dates for start date and end date should be 1 day less than start date. For example start date be 10/2/1990 and end date will be 10/3/1990.Thanks in advance Tue, 13 May 2008 06:55:38 GMTAu4848http://www.sqlservercentral.com/Forums/Topic500380-338-1.aspxOK, so I've nabbed this proper case function from somewhere a little while ago:[code]USE [Preqin]GO/****** Object: UserDefinedFunction [dbo].[f_ProperCaseENHANCED2] Script Date: 05/14/2008 12:59:45 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[f_ProperCaseENHANCED2] (@String VARCHAR(8000)) RETURNS VARCHAR(8000) AS BEGIN DECLARE @TempString VARCHAR(8000) DECLARE @PS VARCHAR(8000)SET @PS = ''-- lower case entire stringSET @TempString = lower(@String)WHILE patindex('%[-( '']%',@TempString) > 0 BEGIN -- Check to see if first character of @TempString is whitespace IF (patindex('%[-( '']%',SUBSTRING(@TempString,1,1)) > 0) BEGIN SET @PS = @PS + SUBSTRING(@TempString,1,1) END ELSE -- @TempString starts with a Name BEGIN IF SUBSTRING(@TempString,1,2) = 'mc' BEGIN SET @PS = @PS + 'Mc' SET @TempString = SUBSTRING(@Tempstring,3,LEN(@TempString)) END IF SUBSTRING(@TempString,1,3) = 'mac' BEGIN SET @PS = @PS + 'Mac' SET @TempString = SUBSTRING(@Tempstring,4,LEN(@TempString)) END -- upper case first character and return string up to the next space SET @PS = @PS + UPPER(SUBSTRING(@TempString,1,1)) + SUBSTRING(@TempString,2,patindex('%[-( '']%',@TempString)-1) END -- truncation string that we have already processed SET @TempString = SUBSTRING(@TempString, patindex('%[-( '']%',@TempString)+1,LEN(@TempString)) -- Trim off spaces SET @TempString = RTRIM(LTRIM(@TempString))ENDIF SUBSTRING(@TempString,1,2) = 'mc'BEGIN SET @PS = @PS + 'Mc' SET @TempString = SUBSTRING(@Tempstring,3,LEN(@TempString))ENDIF SUBSTRING(@TempString,1,3) = 'mac'BEGIN SET @PS = @PS + 'Mac' SET @TempString = SUBSTRING(@Tempstring,4,LEN(@TempString))END-- proper case last word/nameSET @PS = @PS + UPPER(SUBSTRING(@TempString,1,1)) +SUBSTRING(@TempString,2,LEN(@TempString))-- check for spaces in front of special charactersSET @PS = Replace(@PS,' -','-')SET @PS = Replace(@PS,' ''','''')RETURN (@PS)END[/code]Now, in the main this seems to work well, however, there are some modifications I would like to make to it, so I guess I need to actually understand what it's doing, which I kinda do, but not fully. An 'initials' field in one of my tables contains a value 'a.b.' the propercase function converts it to 'A.b.' I would like it to become 'A.B.'I understand the capitalization of the first letter of the word is achieved by this part:[code]UPPER(SUBSTRING(@TempString,1,1))[/code]i.e. the substring in this case is 'a.b' and its taking 1 character and starting at the first character, the UPPER is then converting it to uppercase - so 'a.b.' becomes 'A.b'However, I get a bit lost trying to work out how I tell it to capitalize the first letter after a full stop (period for the US guys :p )I've been playing trying to sort some strings out for other problems, and used charindex, not sure if I could use it somehow like this:[code]charindex('.',@TempString,1)[/code]Making the above something like:[code]UPPER(SUBSTRING(@TempString,charindex('.',@TempString,1),1))[/code]But, A. I don't know if that will work, and B. I don't know how I could integrate it in to the above function.Any help guys?Wed, 14 May 2008 06:21:34 GMTpeitechhttp://www.sqlservercentral.com/Forums/Topic502307-338-1.aspxUSE GLPDEMOGOselect t.name as TriggerName, ta.name as TableName, o.parent_obj into GLPDemo.dbo.Temp_TablesAndTriggersfrom sysobjects o inner join sys.triggers t on t.object_id = o.id inner join syscomments con c.id = t.object_id inner join sys.tables taon ta.object_id = o.parent_objwhere xtype = 'tr' and c.text like '%Audit%'DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)DECLARE TCursor CURSOR forSELECT TriggerName, TableName from Temp_TablesAndTriggersOPEN TCursorFETCH NEXT FROM TCursor INTO @DBTrigger, @DBTableWHILE @@FETCH_STATUS = 0select @exestr = ' DISABLE TRIGGER GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTableEXECUTE sp_executesql @exestr;FETCH NEXT FROM TCursor INTO @DBTrigger, @DBTableCLOSE TCursorDEALLOCATE TCursor--DROP TABLE #Temp_TablesAndTriggersFri, 16 May 2008 13:03:22 GMTveenagireeshahttp://www.sqlservercentral.com/Forums/Topic501515-338-1.aspxI know you can return 1 datatable with the following to fill an xml file in a web app: select * from myTable FOR XML path('dbname'), root('root');How do you return multiple xml datatables to fill an xml file?Thu, 15 May 2008 11:06:56 GMTmaryjane198820http://www.sqlservercentral.com/Forums/Topic499217-338-1.aspxIf i have a dynamic sql query will it take advantage of indexes?Mon, 12 May 2008 17:15:54 GMTPaul Fechnerhttp://www.sqlservercentral.com/Forums/Topic502176-338-1.aspxContinuing my crusade to tidy up our contacts database, I wanted to write something which would detect a name like this :DeBeersAND keep it like this, i.e. not 'proper case' it to DebeersSO... I thought I needed to search for a string that was basically uppercase, lowercase, uppercase, lowercase...The field I am searching on has a CI collation - so as I understand it, case insensitive. I thought I could use something like IFCHARINDEX([A-Z],fieldtocheck,1)>0ANDCHARINDEX([a-z],fieldtocheck,2)>0ANDCHARINDEX([A-Z],fieldtocheck,3)>0ANDCHARINDEX([a-z],fieldtocheck,4)>0THEN SET @string = @stringBUT... 1. I'm not sure if that would work (my knowledge of actual programming / creating functions is pretty basic) and 2. how I deal with the fact that the collation is case insensitive - is there a convert to CS or something?Comments welcome!Fri, 16 May 2008 09:28:11 GMTpeitechhttp://www.sqlservercentral.com/Forums/Topic501822-338-1.aspxHelloI am trying to insert records into a fairly large table (>25Mil records). The table contains notes for a call center (CustomerNotes), and contains all the text entered by the agents during phone conversations. Since the software reading from the table is not an in house app i'm not allowed to modify the tables in any way, and I cant add any tables to the database. The database calls are all in the code (which I don't have) and horribly don't have any (NOLOCK)'s so blocking is a serious issue on this table.Another internal system allows the bulk inserting of records into this table, and I have recently inherited this system (its under my control). At the moment these bulk inserts are run daily to weekly and was discovered that only about 50% of the records ever make it into the destination table.The original strategy:1) Read the rows from the source tables (on ServerA) with a cursor2) Join the single row from the cursor onto the destination table (on ServerB) to calculate the next NoteId for the customer3) Insert this row into the table (on serverB).I know that both cursors and cross server queries are both performance no-no's so I removed both and put the whole query into a transaction to make sure if it deadlocked I could rollback and try again. Unfortunately the next NoteId needs to be calculated for each insert (hence the grouping). My strategy1) Precalculate everything (other than the NoteId)2) Move the data from ServerA to ServerB into a new database3) INSERT INTO CustomerNotes SELECT tn.CustomerId, ISNULL(MAX(cn.NoteId), 0) + 1 AS [NoteId], tn.NoteTextFROM TempNotes WITH (NOLOCK) AS tnLEFT JOIN CustomerNotes WITH (READPAST) AS cnON tn.CustomerId = cn.CustomerIdGROUP BY tn.CustomerId, tn.NoteTextIf I open a transaction (in another query window) and lock some of the rows the whole query just waits indefinitely. I removed the insert and just tried the select, and it does not READPAST - 15 hours later (on our test server) it still hadn't selected the rows. Without the locked rows the select runs in 1min19sec. The code above is run in a while loop to keep trying to insert the locked records, rolling back if locking occurs, but this was all removed for my tests.Any Ideas / Suggestions? RegardsRobFri, 16 May 2008 01:37:47 GMTRobinChttp://www.sqlservercentral.com/Forums/Topic500302-338-1.aspxHi, Does anybody know or prove it, what happens with the data base when a JOB (supose that it takes more than 400miliseconds every second) is running at the same time that there are doing queries, or other things to the database??:w00t:thanksWed, 14 May 2008 04:46:42 GMTetxebhttp://www.sqlservercentral.com/Forums/Topic502272-338-1.aspxThe SP below will popup an error message when the users of the app leave the "Freight Code" field blank. They can go back, fill that info in and click SAVE again.My problem is that they can only try to correct once. For example:-working on order, leave field blank, hit SAVE-error pops up, order remains on screen unsaved and SAVE button is available-leave field blank again, hit HIT-error pops up, order remains on screen unsaved [b]but SAVE button is grayed out[/b]-user must close out of screen, loosing all unsaved workFor some reason I only get one chance to make the correction before hitting the SAVE button again. This is my first experience with RAISERROR and I'm not sure if this is due to the "severity" and "state" of the syntax.Any suggestions would be greatly appreciated![b]CREATE TRIGGER mandatory_freight_code_field ON oe_hdrFOR INSERTASDECLARE @FreightCode as varchar(1)DECLARE @SourceCode as varchar(4)select @FreightCode = freight_code_uid from inserted--1 BOTH--2 ALLOWED--3 BILL-BOTH--4 BILL-OUT--5 BILL-IN--6 COLLECT--7 PREPAID IF @@ROWCOUNT = 0 RETURN IF @FreightCode in (1,2,3,4,5,6,7) RETURN IF @FreightCode = '' OR @FreightCode is null BEGIN RAISERROR ('*********************YOU MUST ENTER A DEFAULT FREIGHT CODE************************ *************************GO TO THE SHIP-INFO TAB TO CORRECT*************************** *************************************************************************************************',16,1) END[/b]Fri, 16 May 2008 12:01:26 GMTbjohnsonhttp://www.sqlservercentral.com/Forums/Topic499986-338-1.aspxI have a table setup as such labeled - tbl_items:seq account amount disp---- -------- ------- ----10 12345 5000.00 +20 23451 500.00 -30 34512 1000.00 -40 12453 2000.00 -50 44423 1000.00 B60 22456 500.00 -110 12345 4356.55 +120 68901 4000.00 -130 68904 356.55 -I'm trying to return consecutive rows. So for example:[code]Select * from tbl_items where account = 12345[/]then return all records below that record until the next '+' in column disp.so the result set would be:seq account amount disp---- -------- ------- ----10 12345 5000.00 +20 23451 500.00 -30 34512 1000.00 -40 12453 2000.00 -50 44423 1000.00 B60 22456 500.00 -I've tried a WHILE loop, nested selects and nothing... I'm now to the point of being confused as ever... Any assistance would surely keep me out of the nut house..Tue, 13 May 2008 14:46:48 GMTRich96http://www.sqlservercentral.com/Forums/Topic499787-338-1.aspxThis problem is really foxing me and I'm struggling to think of a strategy to solve it, let alone write the code! I belive it's a fairly tricky problem, so any help you guys can give would be much appreciated.Two tables are linked by a person ID. They are Address and SportsClub. A person can be in a single club whilst living at mulitple addresses, or at a single address whilst at multiple clubs - or a combination of both. The first table below holds records of the clubs someone is a member of over time. The second table holds their postal adresses as they change over time.Person Club [ClubFromDate] [ClubToDate]14 CB1 01/01/2001 01/02/200114 MK4 01/02/2001 01/03/200114 LD5 01/03/2001 01/08/200114 AA7 01/08/2001 01/02/2002Person Address [AddFromDate] [AddToDate]14 Cambs 01/01/1995 01/04/200114 Bristol 01/04/2001 01/12/200114 London 01/12/2001 01/12/2007I need to build a single table containing the records for both, such as:Person Club Address ClubFromDate ClubToDate AddFromDate AddToDate14 CB1 Cambs 01/01/2001 01/02/2001 01/01/1995 01/04/200114 MK4 Cambs 01/02/2001 01/03/2001 01/01/1995 01/04/200114 LD5 Cambs 01/03/2001 01/08/2001 01/01/1995 01/04/200114 LD5 Bristol 01/03/2001 01/08/2001 01/04/2001 01/12/200114 AA7 Bristol 01/08/2001 01/02/2002 01/04/2001 01/12/200114 AA7 London 01/08/2001 01/02/2002 01/12/2001 01/12/2007I think the solution in plain English is as follows: > Start with the first Club. If the ClubFromDate falls between AddFromDate and AddToDate, then display the row joined with that Address. Next, if the ClubToDate falls between AddFromDate and AddToDate, then display another row joined with that Address.> Loop through each Club.I can't see how to do this in SQL and any solution I come up with will be very clunky and probably use a cursor - however these tables have millions of rows and performance is a bit of an issue. I look forward to hearing your creative ideas and thanks again in advance!Tue, 13 May 2008 10:04:20 GMTTom Clarkhttp://www.sqlservercentral.com/Forums/Topic501617-338-1.aspxi have this query which runs good on sql server 2000:select col1,col2 from table1,table2where table1.col1*=table2.col2am upgrading to sql 2005 and the database compatibility level will be 90, if i used left outer join i should get the same result right?select col1,col2 from table1 left outer join table2on table1.col1=table2.col2is there any diffirence between these 2 queries?Thu, 15 May 2008 14:14:25 GMTmobashahttp://www.sqlservercentral.com/Forums/Topic501046-338-1.aspxHi All,Please I need your help. Your time is very much appreciated.I will explain it with a simple example.Table1:memberid initialjob123 GS124 PDRTable2:memberid intermediatejob123 AAP125 AVTable3:memberid lastjob123 TS126 CSI need to have all these tables into TABLEFINAL:memberid initialjob intermediatejob lastjob123 GS AAP TS124 PDR NULL NULL125 NULL AV NULL126 NULL NULL CSThank you,MaryThu, 15 May 2008 00:30:13 GMTnicolamarianahttp://www.sqlservercentral.com/Forums/Topic501479-338-1.aspxOK, so one can add [code]USE [/code]At the top of a script and it will automatically use the DB you have specified, however, can this be extended to tell it to use only a certain server / dababase connection?I tried something along the lines of USE [code]And it didn't work[/code](As you may have guessed, this is to force certain queries to be run on local copies of live data - where this live data gets downloaded every evening - so database names are the same, but servers are not.)Thu, 15 May 2008 10:02:28 GMTpeitechhttp://www.sqlservercentral.com/Forums/Topic501873-338-1.aspxIs there a query or function that will print query results directly to a printer?Fri, 16 May 2008 04:11:39 GMTasbains8http://www.sqlservercentral.com/Forums/Topic499221-338-1.aspxI am trying to get some more performance out of an existing stored procedure.It uses some dynamic sql to alter a temp table and add some additional columns (only known at runtime).My question is this - why use nvarchar? I am certain that it will only consist of english characters.[sup]DECLARE @rateString nvarchar(4000)DECLARE @DynamicSQL NVARCHAR(4000)CREATE TABLE #tblraw ( [LoadDate] [smalldatetime] NULL, [LoadTime] [nvarchar](50) NULL )SET @DynamicSQL = 'ALTER TABLE #tblraw ADD ' + @DataloggerColumsSQL EXEC(@DynamicSQL)[/sup]Mon, 12 May 2008 17:31:06 GMTPaul Fechnerhttp://www.sqlservercentral.com/Forums/Topic501715-338-1.aspxGood Morning,I am sure there is an easy solution to this problem, but since I have been looking at it for sometime I am possibly unable to find the easy solution for this. I am returning a list of vehicles from a table. I am also joining that table with the Odometer reading for that vehicle within a given monthly period. Oct 2007 or Apr 2008. There can be multiple odometer reading within a month, so what I am after is the First reading for a car in that month, and the first reading for a car in the next month.[font="Courier New"][size="2"]SELECT REGISTRATION_NO, ODO_READING_KM as NextReading, READING_DATE, TRANSACTION_MONTHFROM COM_ODO_READINGSWHERE TRANSACTION_MONTH BETWEEN DATEADD(MONTH, 1, CAST((CAST(@StatusDateYear as VARCHAR) + '-' + CAST(@StatusDateMonth as VARCHAR) + '-01 00:00:00.000') AS DATETIME)) AND DATEADD(DAY, -1, DATEADD(MONTH, 2 ,CAST((CAST(@StatusDateYear AS VARCHAR) + '-' + CAST(@StatusDateMonth AS VARCHAR) + '-01 00:00:00.000') AS DATETIME)))Order BY REGISTRATION_NO[/size][/font]This is the query that returns the Next reading User supplies Oct 2007 as the month and year, it will use Nov 2007 as the month to use.[font="Courier New"][size="2"]SELECT REGISTRATION_NO AS, ODO_READING_KM as PreviousReading, READING_DATE as ReadingDate, TRANSACTION_MONTHFROM COM_ODO_READINGS WHERE TRANSACTION_MONTH BETWEEN CAST((CAST(@StatusDateYear as VARCHAR) + '-' + CAST(@StatusDateMonth as VARCHAR) + '-01 00:00:00.000') AS DATETIME) AND DATEADD(DAY, -1, DATEADD(MONTH, +1 ,CAST((CAST(@StatusDateYear AS VARCHAR) + '-' + CAST(@StatusDateMonth AS VARCHAR) + '-01 00:00:00.000') AS DATETIME)))Order By REGISTRATION_NO[/size][/font]The user enters the parameters from Reporting Services and as two separate entries, Month and Year (Business specified this method). Now, these queries will return the same vehicle more than once if there were more than 1 odometer reading taken and entered for that month selected. It is rare and there are specific business cases that would warrant this to occur.Like I mentioned that I am sure there is a simple solution for this and for me looking at this problem for some time now, well several hours, it seems to allude me. Your help with this would be useful. If you need more information please let me know.Kind RegardsMichael RogersThu, 15 May 2008 17:30:41 GMTmichael.rogershttp://www.sqlservercentral.com/Forums/Topic501714-338-1.aspxHi all,I need to change the following selection:acct_nbr txn_date txn_amt 123 2008-01-14 50.00123 2008-02-24 120.00123 2008-02-21 70.00124 2008-01-30 20.00124 2008-02-03 110.00125 2008-03-19 70.00into this:acct_nbr txn_date_1 txn_amt_1 txn_date_2 txn_amt_2 txn_date_3 txn_amt_3 123 2008-01-14 50.00 2008-02-24 120.00 2008-02-21 70.00124 2008-01-30 20.00 2008-02-03 110.00 NULL NULL 125 2008-03-19 70.00 NULL NULL NULL NULL The transactions may vary from 1 to 3 maximum.Can you please help me?Thank youRegardsMarkThu, 15 May 2008 17:17:27 GMTmaxgiosehttp://www.sqlservercentral.com/Forums/Topic501622-338-1.aspxI have the following code:SELECT Top 1 B.OrderNumber ,B.[OrderGroupNumber] ,A.PaymentTypeXID ,'PaymentType' = Case WHEN a.PaymentTypeXID IN (0,7,9,16) THEN A.[CCName] ELSE c.PaymentType END ,C.PaymentType ,C.PaymentCategory ,A.PaymentID ,S.XID ,'AccountID' = CASE WHEN A.[PaymentTypeXID]=0 AND S.[XID] IN (0,1,2,3) THEN a.[CClastfour] WHEN A.[PaymentTypeXID]=1 THEN A.BankAccount --Bank Draft WHEN A.[PaymentTypeXID]=5 THEN a.[CheckNumber] -- Personal Check WHEN A.[PaymentTypeXID]=2 THEN a.[GCCode] -- Gift Certficate WHEN A.[PaymentTypeXID]=6 THEN a.[CheckNumber] -- Cashier Check WHEN A.[PaymentTypeXID]=7 THEN a.[CClastfour] -- Credit Card Credit WHEN A.[PaymentTypeXID]=8 THEN '' -- Consultant Account WHEN A.[PaymentTypeXID]=13 THEN '' -- Credit Personal Check WHEN A.[PaymentTypeXID]=14 THEN '' -- Credit Money Order WHEN A.[PaymentTypeXID]=4 THEN a.[CheckNumber] -- Money Order WHEN A.[PaymentTypeXID]=16 THEN a.[CCLastFour] -- Re-occuring Payment WHEN A.[PaymentTypeXID]=9 THEN a.[CClastFour] -- Blind Credit WHEN A.[PaymentTypeXID]=11 THEN 'Pay #'+ CONVERT(VARCHAR(20),b.ordernumber) COLLATE SQL_Latin1_General_CP1_CI_AS + CONVERT(VARCHAR(20),a.[ConsultantID]) COLLATE SQL_Latin1_General_CP1_CI_AS WHEN A.[PaymentTypeXID]=10 THEN a.[BankAccount] -- Re-occuring Payment WHEN A.[PaymentTypeXID]=12 THEN a.[GCCode] -- Gift Certficate Credit WHEN A.[PaymentTypeXID]=15 THEN '' -- Credit Cashier Check END-- ,A.[BankAccount] ,'ApprovalCode' = CASE WHEN A.[PaymentTypeXID]=0 AND S.[XID] IN (0,1,2,3) THEN p.ResponseAuthorizationCode WHEN A.[PaymentTypeXID]=0 AND S.[XID] IN (0,1,2,3) AND p.ResponseAuthorizationCode IS NULL THEN 'APPROVED' WHEN a.[PaymentTypeXID]=2 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=1 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=6 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=5 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=7 THEN p.ResponseAuthorizationCode WHEN a.[PaymentTypeXID]=7 AND p.ResponseAuthorizationCode IS NULL THEN 'APPROVED' WHEN a.[PaymentTypeXID]=8 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=13 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=14 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=4 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=16 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=9 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=10 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=11 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=12 THEN 'APPROVED' WHEN a.[PaymentTypeXID]=15 THEN 'APPROVED' END ,A.[CCName] ,A.[CCLastFour] ,P.ResponseAuthorizationCode AS AuthorizationCode ,A.[CheckNumber] ,A.TransactionTotal ,A.[ConsultantID]FROM dbo.uvw_OrderGroupPayment AS A With (NoLock) INNER JOIN dbo.OrderForm AS B With (NoLock) ON A.ORderGroupNumber = B.OrderGroupNumber LEFT OUTER JOIN SharedDimension.dbo.DimPaymentType AS C With (NoLock) ON A.PaymentTypeXID = C.XID LEFT OUTER JOIN CreditCardProcessorLog AS P With (noLock) ON A.OrderGroupNumber = P.OrderGroupNumber LEFT OUTER JOIN SharedDimension.dbo.DimCCTranType AS S WITH (NOLock) ON P.[TransactionTypeXID] = S.[XID]WHERE B.OrderNumber = @OrderNumberAND PaymentTypeXID NOT IN (3,8,11,12,13,14,15)Error:Msg 451, Level 16, State 1, Line 4Cannot resolve collation conflict for column 4 in SELECT statement.I have checked the collation on both databases and they are the same. any ideas?Thu, 15 May 2008 14:22:15 GMTalorenzinihttp://www.sqlservercentral.com/Forums/Topic501571-338-1.aspxHow insert values from TABLE2 into @TempTable if a row into TABLE1 not in TABLE2?--Query:DECLARE @TempTable (IdTemp int, TempDate datetime)INSERT INTO @TempTable (IdTemp, TempDate)SELECT T1.Id, MAX(T2.Date2)FROM Table1 T1 INNER JOIN Table2 T2 ON T1.Id = T2.IdGROUP BY T1.Id/*Example:TABLE1 Id = '1'TABLE2 = Id '1' not exists*/Thu, 15 May 2008 12:31:15 GMTeduardo.munizhttp://www.sqlservercentral.com/Forums/Topic500256-338-1.aspxHi Friends,I have two OLAP database with the same structure in my Development Server. I have a common query which joins 5 tables and the joins follows the correct columns. When I am running the Query, In 1st Database the query follows a Index seek which Take 4% of my query Cost, where in 2nd Database the query follows the Index scan which takes 44% of my Query Cost. I checked with the following Steps to resolve the issue:-------------------------------------------------------> As the structure of both database is same, the datatype is also same for index column.--> As we are running the same query, the where clause is same, I mean to say it contains the same index column for filtration of data.Can you please tell me what may be the reason?Cheers!Sandy.Wed, 14 May 2008 03:16:48 GMTSandyhttp://www.sqlservercentral.com/Forums/Topic500877-338-1.aspxI have an existing table of transactions from our retail stores and I'm trying to produce a summarization of the data. For each store, I have a row that shows total_sales, total_customers, and then individual columns showing how many customers paid by cash, check, VISA, MasterCard, Discovery Card, Gift Card etc.I need to come up with a view which sums all of the VISA, MasterCard, and Discovery cards together as a single number. As an example of what my table currently looks like:Store Cust_Count Cash Check VISA MasterCard Discover GiftCard001 210 110 35 30 25 5 5What I want to end up with is:Store Cust_Count Cash Check Cards001 210 110 35 65 <---- SUM( all card types )T-SQL is complaining at me about not being able to perform an aggregate function on an expression containing an aggregate or subquery. I can't quite seem to figure out a way around this short of stuffing all of the intermediate results into a temp table and then summing them together.Can anyone point me toward a way of accomplishing this?Thanks!Wed, 14 May 2008 13:58:51 GMTLarry Krusehttp://www.sqlservercentral.com/Forums/Topic500594-338-1.aspxThe following is a shortened version of my script. As you can see in the Total field, its taking the values from Reg_Hrs and adding to Reg_Ern. I was wondering if there is a way to pass the values of Reg_Hrs and Reg_Ern to 2 variables so Total = @reg_hrs + @reg_ern. Of course the variables need to by dynamic as the values change for each employee.select a.name,reg_hrs = (select sum(al_hours) from ps_al_chk_hrs_ern where emplid = a.emplid and row_nbr = 1),reg_ern = (select sum(earnings) from ps_al_chk_hrs_ern where emplid = a.emplid and row_nbr = 1),total = ((select sum(al_hours) from ps_al_chk_hrs_ern where emplid = a.emplid and row_nbr = 1) + (select sum(earnings) from ps_al_chk_hrs_ern where emplid = a.emplid and row_nbr = 1))from ps_employees aWed, 14 May 2008 09:07:30 GMTis250sphttp://www.sqlservercentral.com/Forums/Topic501485-338-1.aspxI built a custom auditing construct where I store the text of the stored procedure I am currently executing. The code I use to accomplish this is below:[code]CREATE PROCEDURE mySprocAS--some list of commands/inserts/etc-- audit this procedureDECLARE @sql_handle varbinary(max)DECLARE @AuditedText varchar(max)SELECT @sql_handle = sql_handle FROM sys.dm_exec_requests WHERE sql_handle IS NOT NULLSELECT @AuditedText = [text] FROM sys.dm_exec_sql_text(@sql_handle)INSERT INTO AuditLog(AuditedText)VALUES(@AuditedText)[/code]99 out of 100 times this works perfectly but I'm noticing that sometimes it will pull text completely unrelated to what was actually executed....for example this was returned once:[i]create procedure sys.sp_trace_getdata (@traceid int, @records int = 0 )asselect * from OpenRowset(TrcData, @traceid, @records)[/i]or a backup statement....My question is, why is the sql_handle not returning the handle of the text that was executed when I thought this was specific to the spid tied to the procedure? Could there be multiple sql_handles in the DMV and I'm pulling back the wrong one? How can I ensure this sql_handle is for this procedure?Any ideas?Thu, 15 May 2008 10:10:38 GMTChris Gierlackhttp://www.sqlservercentral.com/Forums/Topic499399-338-1.aspxHi,I would like to know if there is a way to execute stored procedures every second. I have tried with jobs and the minimun execution schedule is every minute. So the unique solution that I have found is to execute once and controle it inside the stored procedure with a delay.But doing that I do not know how many time is needed. Do you know if there is any function in sql server 2005 to know how many milliseconds is needed by a stored procedure when a job executes it?In my case I have an stored procedure that makes a bulk of a csv file to one table each second, and executing it manually I can see how many time it takes in milliseconds (using a function inside the stored procedure). But using a job that executes it, I just can see when it starts and when it finishes, so I do not know how many time is needed to execute the stored procedure by the job.Thank you in advance:PTue, 13 May 2008 03:10:55 GMTetxebhttp://www.sqlservercentral.com/Forums/Topic500258-338-1.aspxI have table with Two columns. i.e TIME and ACTION. IT looks like thisTIME ACTION12:40:01 BUY 12:40:31 BUY 12:40:51 BUY 12:41:01 SELL 12:41:21 SELL12:41:31 SELL 12:41:41 SELL 12:41:51 BUY 12:42:01 BUY 12:42:29 SELL 12:42:31 SELL 12:42:41 SELL 12:42:51 BUYI want to remove rows whenever ACTION column with Repetative values.After that it should display like thisTIME ACTION12:40:01 BUY 12:41:01 SELL 12:41:51 BUY 12:42:29 SELL 12:42:51 BUYPlease help me anyoneWed, 14 May 2008 03:18:56 GMTsameerhttp://www.sqlservercentral.com/Forums/Topic501325-338-1.aspxHiHow to insert data from one db to another db with same sql server ?I am having a schema TEST with table as EMP I need to select values from TEST.EMP and INSERT INTOTEST1.EMPLOYEE. TEST1 is different schema, both are available in same server.RegardsThu, 15 May 2008 07:58:37 GMTsharmahttp://www.sqlservercentral.com/Forums/Topic499384-338-1.aspxHelloI would like to write a script that outputs update statements;For example, if I have a table with ID, Name, Phone ans 3 records:1, John, 5556667772, Lynn, 6667778883, Mary, 777666888I would like to output the following statements:Update Clients set phone = xxx where ID = 1Update Clients set phone = yyy where ID = 2Update Clients set phone = zzz where ID = 3(Don't as why I don't do one update statement :) I really need to have all the updates of each row:) )Thanks!!Tue, 13 May 2008 02:35:58 GMTTerryhttp://www.sqlservercentral.com/Forums/Topic500985-338-1.aspxHi how to Select from one sql database and insert into another database.Source db as Sql Server 2000Target db as Sql Server 2005.I need to select one table data from Server 2000 & insert into Sql Server 2005Wed, 14 May 2008 21:51:47 GMTsharmahttp://www.sqlservercentral.com/Forums/Topic501188-338-1.aspxHow to load data from .csv file using SELECT * INTO USING OpenRowset.Without creating source table, i need to insert the data from .csv fileRegardsThu, 15 May 2008 05:44:34 GMTsharmahttp://www.sqlservercentral.com/Forums/Topic501241-338-1.aspxhave a data like in a name field like belowMartinsMartin'show can I get both record when some one passes in search input string only Martins withouth single quote.i.e if I pass Martins It should return both Martins and Martin'sit is a specific case, think in general where uer will pass Martins not Martin.I know select * from table1 where [name] like '%martin%' will return all row my question is if we pas '%martins%' .can i remove ' single quote it from field in where condition if yes then how? Thu, 15 May 2008 06:36:05 GMTnaveed.kambohhttp://www.sqlservercentral.com/Forums/Topic500873-338-1.aspxto copy 1 db to another db on the same server. it is to be used for training purposes, instead of letting new users whack away at the live/current database. so i need to make a copy of the database and let the new train on our software using the test db.currently i am doing this manually using the management console but would like to know if there is a TSQL way. Don't know a lot about dts but am needing the abilitiy to access each clients system log into different servers and copy select database to another.Wed, 14 May 2008 13:56:01 GMTroy.tollisonhttp://www.sqlservercentral.com/Forums/Topic500710-338-1.aspxHi everyone,I have a log table which contains a list of items found on shelves during many stock-take operations. Sample data could look like this:[code]Item_ID Location username ScanDate1 A Jim 21-02-20082 A Jim 21-02-20083 B Jim 21-02-20084 B Sue 26-02-20085 B Sue 26-02-20083 C Tom 21-02-20081 C Tom 21-02-20082 C Dave 25-03-20084 A Tom 29-02-2008[/code]From this data I want to get a distinct list of the locations, along with the latest date that each location was scanned and who did the scanning. So the desired result for the above would be[code]Location DateLastScanned Username A 29-02-2008 Tom B 26-02-2008 Sue C 25-03-2008 Dave[/code]The problem is that a simple DISTINCT command brings back distinct combinations of all 3 fields which is no good to me. I just want the latest date that each location was scanned, and who did the scanning. Any ideas?Wed, 14 May 2008 10:27:11 GMTthomas.lemesurierhttp://www.sqlservercentral.com/Forums/Topic501114-338-1.aspxHi,I'm trying to write a SELECT that gets a list of people from a table in one database and for each row returned, appends related data (as columns from a stored procedure) held in another database (on same server). Is it possible?I've been trying loads of different permutations of the following theme:SELECT m.Ref, m.Knownas, m.Lastname, BuildingPasses.dbo.SelectCardUsageSummary(m.CardNumber)FROM Members mI was hoping this would return data in the following format:Ref | Knownas | LastUse | VisitsLast2Weeks1 | John | Smith | 15/05/2008 | 92 | Jane | Black | 14/05/2008 | 8Thu, 15 May 2008 03:02:40 GMTr_caine