InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 21:55:15 GMT20http://www.sqlservercentral.com/Forums/Topic1454974-338-1.aspxHi,I'm using this bit of code to email results, but only if there are results/records:[code="sql"]IF select count(*) from (Select deddedcode as "DED CODE", dedlongDesc as DESCRIPTION, deddatetimecreated as DATE from dedcode where deddatetimecreated > DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -55) > 0begin[/code]Get seem to figure out the syntax..Tue, 21 May 2013 06:54:52 GMTkrypto69http://www.sqlservercentral.com/Forums/Topic1454636-338-1.aspxHello community,I have a problem when selecting data from an XML column.I have imported the data using the following statement:[code="sql"]CREATE TABLE XmlImport(XmlResult XML);GOINSERT INTO XmlImport(XmlResult)SELECT * FROM OPENROWSET( --BULK 'c:\SampleFolder\SampleData3.txt', BULK 'c:\SampleFolder\k5Entleerung_Export_Beispiel1.xml', SINGLE_BLOB) AS x;[/code]Then I select the imported data with the following statement:[code="sql"]WITH XMLNAMESPACES(DEFAULT 'http://www.k-5.at/Schema/Entleerung:001')SELECTExport.Gemeindekennziffer.value('.', 'nchar(5)') as GemeindeKennZiffer,substring(Expo.Erstellungsdatum.value('.','nchar(10)'),1,4) as Jahr,substring(Expo.Erstellungsdatum.value('.','nchar(10)'),6,2) as Monat,'D' as Recordtype,KueDet.KueGr.value('.','nchar(30)') as Tarifbezeichnung,Kuebel.tag.value('.','nchar(16)') as TAGFROM (SELECT XmlResult FROM XmlImport) AS b(x)cross apply x.nodes('EntleerungExports/ÖStatNummer') as Export(GemeindeKennZiffer)cross apply x.nodes('EntleerungExports/Erstellungsdatum') as Expo(Erstellungsdatum)cross apply x.nodes('EntleerungExports/Kübel/Tagnummer') as Kuebel(TAG)cross apply x.nodes('EntleerungExports/Kübel/Kübeldetails/Kübelgröße') as KueDet(KueGr)[/code]which leads to the following (undesired) results:GemeindeKennZiffer Jahr Monat Recordtype Tarifbezeichnung TAG------------------ ---- ----- ---------- ------------------------------ ----------------70367 1967 08 D 120.55 CBC958090000000070367 1967 08 D 80.00 CBC958090000000070367 1967 08 D 125.00 CBC958090000000070367 1967 08 D 120.55 DBC568250000000070367 1967 08 D 80.00 DBC568250000000070367 1967 08 D 125.00 DBC568250000000070367 1967 08 D 120.55 DBC987650000000070367 1967 08 D 80.00 DBC987650000000070367 1967 08 D 125.00 DBC9876500000000(9 row(s) affected)which should be, of course:GemeindeKennZiffer Jahr Monat Recordtype Tarifbezeichnung TAG------------------ ---- ----- ---------- ------------------------------ ----------------70367 1967 08 D 120.55 CBC958090000000070367 1967 08 D 80.00 DBC568250000000070367 1967 08 D 125.00 DBC9876500000000Please, can anybody help me to retrieve the data correctly.I've attached the xml file as textfile.Many thanx in advance,HelmutMon, 20 May 2013 10:55:22 GMTh.strausshttp://www.sqlservercentral.com/Forums/Topic1454689-338-1.aspxHi all,I have a s/p which runs bcp and creates a CSV export file. Works fine in development (my rights) but not for any other user. To run bcp, I need to use sp_configure to turn on 'configure advanced options', then xp_cmdshell to run bcp. Both of these options are OFF by default.SO: I turn on the advanced options, turn on xm_cdmshell, run my code, turn off xp_cmdshell, then turn off advanced options.It works great for me but other users get permissions errors trying to run the system stored procs. They can run the s/p in my local db, but not the ones that live in master db. I've been playing with GRANT and EXECUTE AS but have no luck yet with either one. Has anyone ever done this?Thanks!Here's my [edited] code, a few things left out for clarity:[code]ALTER PROCEDURE [dbo].[aMyAppCreateUploadCSV]ASDECLARE @SQL varchar(255)DECLARE @FileName varchar(50)DECLARE @DestFolder varchar(100)-- Turn on Advanced security optionsEXECUTE master.dbo.sp_configure 'show advanced options', 1RECONFIGURE-- make sure xp_cmdshell is turned on and reconfigure with new valueEXEC sp_configure 'xp_cmdshell', 1RECONFIGURESELECT @FileName = 'Upload_TEST.csv';SELECT @DestFolder = '\\myserver\user\UploadFiles\';SET @SQL = 'bcp "exec [MyDB].[dbo].aExtractDataCSV" queryout "' + @destFolder + @fileName + '" -t \, -T -c'EXECUTE master.dbo.xp_cmdshell @SQL-- turn off xp_cmdshell and reconfigure with new valueEXEC sp_configure 'xp_cmdshell', 0RECONFIGURE-- Turn off Advanced security optionsEXECUTE master.dbo.sp_configure 'show advanced options', 0RECONFIGURE[/code]Mon, 20 May 2013 12:46:13 GMTSigersonhttp://www.sqlservercentral.com/Forums/Topic1145948-338-1.aspxI've had this problem a number of times: every so often, I receive errors like this when trying to query an Excel xls file:[quote]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".[/quote]What I've tried:1. Ensuring the Excel file is not open. The OPENROWSET() statement is correctly configured, b/c the query works sometimes.2. Bouncing the SQL service. This [u]always[/u] fixes the problem, but doesn't tell me why it happens or when it will recur. And of course, it's a horrible "solution."3. Checking MDAC. Used the MS consistency checker, and all looked OK.4. Reading in the file saved in Excel XSLX 2007 format using the Microsoft.ACE.OLEDB.12.0' provider. This game me the following out-of-memory error:[quote]Msg 7399, Level 16, State 1, Line 1The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider ran out of memory.Msg 7320, Level 16, State 2, Line 1Cannot execute the query "SELECT * FROM [Sheet1$]" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". [/quote]5. Tweaking the "MemToLeave" setting with the -g startup switch for the service, as described here: [url=http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/#axzz1SkRKB7Fz]http://www.johnsansom.com/sql-server-memory-configuration-determining-memtoleave-settings/#axzz1SkRKB7Fz[/url]. I later ran the query given at his web page and returned the following values of Total avail mem, KB: 23800 and Max free size, Kb: 3444.UPDATE: After a service restart, I immediately checked and got values of 216056 and 91840 for Total Available Memory, Kb and Max Free Size, Kb, respectively. Again, after a service restart I had no problem running the same OpenRowset() query. I started the service without the "-g" flag, using default values of MemToLeave. BOL ([url=http://msdn.microsoft.com/en-us/library/ms190737%28v=SQL.90%29.aspx]http://msdn.microsoft.com/en-us/library/ms190737%28v=SQL.90%29.aspx[/url]) says the default is 256 Mb.This seems to suggest that there's insufficient RAM for running the OPENROWSET() query, consistent with the out-of-memory error trying to execute the query using the ACE.OLDEDB.12.0 provider for the XSLX file.So.I've seen enough blog postings to know that this is a pervasive problem with an often indeterminate solution.1. [u]Is[/u] this a problem caused by insufficient memory for Virtual Address Space?2. If so, how do I find the cause of the problem?3. If not, what can I do to fix this so it doesn't recur?This is a significant problem for me, b/c I'm using OPENROWSET for importing Excel files into SQL. I am reluctant to use SSIS, b/c it's hitting a nail with a sledge hammer.Any assistance would be welcome.RichThu, 21 Jul 2011 08:35:47 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1454382-338-1.aspxMy apologies if this question has been answered elsewhere but I can’t find a solution. I need to count the number of active jobs that exist at the end of each month. A job is active if it has been received but not yet finished. The data looks like this:CREATE TABLE #Files(Job NVARCHAR(10), Received DATETIME, Finished DATETIME)INSERT INTO #Files (JOB, Received, Finished)SELECT 'A1', CAST('20120110' AS DATETIME), CAST('20120506' AS DATETIME) UNION ALLSELECT 'A23', CAST('20120113' AS DATETIME), NULL UNION ALLSELECT 'B4', CAST('20120221' AS DATETIME), CAST('20120224' AS DATETIME) UNION ALLSELECT 'L5', CAST('20120307' AS DATETIME), CAST('20120425' AS DATETIME) UNION ALLSELECT 'D5', CAST('20120316' AS DATETIME), NULL UNION ALLSELECT 'S3', CAST('20120323' AS DATETIME), CAST('20120510' AS DATETIME) UNION ALLSELECT 'D2', CAST('20120502' AS DATETIME), CAST('20120607' AS DATETIME)SELECT * FROM #Files ORDER BY 2, 3DROP TABLE #FilesSo, from the data above, I need to calculate the following:Date Active jobs20120131 220120229 220120331 520120430 420120531 320120630 2A job can be counted multiple times depending on when it was received and finished. Any advice would be appreciated. Thanks.Sun, 19 May 2013 18:19:56 GMTAndyAushttp://www.sqlservercentral.com/Forums/Topic425680-338-1.aspxI created a Linked Server Object with Sql Authentication to connect to another server.I am executing this some time well, but some times it giving error Like"OLE DB provider "SQLNCLI" for linked server "sa_link" returned message "Cannot generate SSPI context".Msg 802, Level 16, State 1, Line 0SQL Network Interfaces: The target principal name is incorrect.OLE DB provider "SQLNCLI" for linked server "sa_link" returned message "Invalid connection string attribute"." :w00t:Mon, 26 Nov 2007 05:20:27 GMTBalu-535489http://www.sqlservercentral.com/Forums/Topic860321-338-1.aspxHi,Is there an equivilent function to Excel's 'CLEAN' in SQL to remove non printable characters?.Thanks,GFri, 05 Feb 2010 04:22:04 GMTGrinjahttp://www.sqlservercentral.com/Forums/Topic1453701-338-1.aspxselect OrderNum, OrderLine, OrderRelease FileTemplateNamefrom OrderLinesI would like to run a xcopy statement for each record and copy the file from FileTemplateName toc:\OrderFiles\<OrderNUm>\<OrderLine>\<OrderRelease>\<OrderNum>_<OrderLine>_<OrderRelease>_FileTemplateNameThu, 16 May 2013 11:27:36 GMTskb 44459http://www.sqlservercentral.com/Forums/Topic1453277-338-1.aspxI need this for an outside application to create a DB using the default. Currently we use:[code="sql"]DECLARE @sql_path nvarchar(256)SELECT TOP 1 @sql_path = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1) FROM [master].[sys].[sysdatabases][/code]This is not really effective if the master is on another drive from the default. We can't use cmdshell to get to the settings. Command shell could be turned off in the instance I am trying to install a database to. Is this the best I can do or is there a way to get at that server mdf/ldf default when creating the database?Wed, 15 May 2013 15:05:24 GMTJKSQLhttp://www.sqlservercentral.com/Forums/Topic1451907-338-1.aspxhii want to specify if my user is a member of administrator or it is a guest. how can i do it ?thanksSun, 12 May 2013 08:35:05 GMTpooya1072http://www.sqlservercentral.com/Forums/Topic1451958-338-1.aspxI know that we don't have access to a MySQL BEFORE trigger but I have a strange issue that I need to resolve.Basically, I have a requirement for truncated data to be output into a new table (un-truncated) and the original data to have a * placed at the end. However, I cannot capture the original string before it gets truncated. Below are my scripts:1) Table one, to contain truncated '*' data:CREATE TABLE [dbo].[StudentDetails]( [StudentID] [int] NOT NULL, [StudentName] [varchar](15) NULL,PRIMARY KEY CLUSTERED 2) Table two to contain 'FULL' stringCREATE TABLE [dbo].[StudentDetails_Extended]( [StudentID] [int] NOT NULL, [StudentName] [nvarchar](400) NULL,PRIMARY KEY CLUSTERED 3) I have tried to create the following trigger:CREATE TRIGGER Test_Trg ON StudentDetails INSTEAD OF INSERT--AFTER INSERT AS BEGIN SET NOCOUNT ON; SET ANSI_WARNINGS OFF INSERT INTO StudentDetails (StudentID, [StudentName]) SELECT StudentID, LEFT([StudentName],10) + '*' FROM inserted INSERT INTO StudentDetails_Extended (StudentID, [StudentName]) SELECT StudentID, [StudentName] FROM inserted SET ANSI_WARNINGS on END GO4) Then running the below:SET ANSI_WARNINGS OFFINSERT INTO [AdventureWorks].[dbo].[StudentDetails] ([StudentID] ,[StudentName]) VALUES (533 ,'MORE THAN 15 CHARS ARE INCLUDED')GOSET ANSI_WARNINGS ON5) I need to see from StudentDetails.StudentName = 'MORE THAN* and from StudentDetails_Extended.StudentName = 'MORE THAN 15 CHARS ARE INCLUDED'Is this possible?Thanks for any help in advance, this is driving me nuts :-)Sun, 12 May 2013 19:25:32 GMTphil.layzellhttp://www.sqlservercentral.com/Forums/Topic1452290-338-1.aspxThere are 5 tables we have which has a parent child relation we have following tables.Order (Fields Order#, RefType, RefNum, RefLine)OrderLine (Fields OrderLine, RefType, RefNum, RefLine)Job (Fields JobNum, JobLine, RefType, RefNum, RefLine)POLine (Fields PONum, POLine, RefType, RefNum, RefLine)TransferLIne (Fields TONum, TOLine, RefType, RefNum, RefLine)All the Tables ar related to each other using 3 fields 1.RefType 2.RefNum 3.RefLine.RefType is either 'Order', 'OrderLine', 'Job', POLine' , 'TransferLine'We would like to present the information something like below in the Treeview in Vb.net application using Stored Proc .Order# - OrderLine -- Job --- POLine ----Job ------ Order - OrderLine -- Job --- POLine ----Job ------ OrderI am also attaching the screen shot of how the program should look like.Here is the link to the image file.http://www.evernote.com/shard/s144/sh/a88f77fe-f7db-4b83-b0b0-f3641f8e61ad/b5feaf67b936b20cc8f500ceeef8a498Mon, 13 May 2013 13:19:48 GMTskb 44459http://www.sqlservercentral.com/Forums/Topic1450109-338-1.aspxThe subject says it all. Can I do this?DECLARE Test AS CURSOR LOCAL FOR[b]EXECUTE uspMyStoredProcedure[/b]OPEN Test FETCH Next FROM Test INTO ... (field list) WHILE (@@FETCH)_STATUS = 0)... and so on. I've tried a simple case and it didn't work although I got no errors. Is this even possible?TIA friends,IsTue, 07 May 2013 06:52:53 GMTSigersonhttp://www.sqlservercentral.com/Forums/Topic1451032-338-1.aspxHi All. Below is an example table of what i need to query out of:Patient Name|| Patient No.|| Age|| Cancer||Consultant|| Operation||Admiision Date|| Date of Operation || Joe Bloggs ||123 || 25 || Yes|| Dr Smith || Back || 01/01/2013 || 03/01/2013Joe Bloggs2 || 321 || 29 || NO ||Dr loggs || Spine || 02/01/2013 || 04/01/2013John Doe || 534 || 34 || No || Dr Doe || Hip || 03/01/2013 || 05/01/2012i need a query to breakdown different types of operations and the length of stay for each operation which is presume is between admission and operation dates. thanks for any help ;) Thu, 09 May 2013 05:24:30 GMTprb88http://www.sqlservercentral.com/Forums/Topic1419186-338-1.aspxI have a varchar column containing dates (not my design) with this format 2013-02-12I need to extract records between two datesthis code will do the job[code="sql"]where convert(datetime, mydatecolumn, 120) between convert(datetime, '2012-10-10',120) and convert(datetime, '2013-04-01',120][/code]but can I do simply to avoid 3 convert?[code="sql"]where mydatecolumn between '2012-10-10' and '2013-04-01'[/code]Tue, 12 Feb 2013 14:19:57 GMTdubem1-878067http://www.sqlservercentral.com/Forums/Topic929350-338-1.aspxHi fellow developers. I am new to XQuery and have an issue. Based on the following XML I can't get any results unless I remove the namespace information at the top. Is there anyway to ignore the namespace. I have tried the following:TABLE:/****** Object: Table [dbo].[Students] Script Date: 05/27/2010 14:57:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Students]( [StudentName] [varchar](50) NULL, [Labid] [int] NULL, [Experimentid] [int] NULL, [ExperimentChemical] [xml] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF---------------------------------------------------------------------------------DATA:INSERT INTO studentsSELECT 'ABC', 3, 3, ' <ATOM xmlns="urn://valence.aero/schemas/airtransport/ATOM/300" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn://valence.aero/schemas/airtransport/ATOM/300 ATOM.300.00.xsd"> <Destination> <Identity /> </Destination> <Source> <Identity> <Organisation>qantas.com.au</Organisation> <Function>EI</Function> <Location>SYD</Location> <ProcessID>OgsAtomChangeNotification</ProcessID> </Identity> <GenerationTime>2010-05-24T11:14:08.355+10:00</GenerationTime> </Source> </ATOM> '---------------------------------------------------------------------------------QUERY:SELECT *FROM studentsWHERE experimentchemical.exist('/ATOM/Source/Identity[Location="SYD"]') = 1ALSO TRIED:SELECT *FROM studentsWHERE experimentchemical.exist('//Source/Identity[Location="SYD"]') = 1Thu, 27 May 2010 16:02:42 GMTdavid.tylerhttp://www.sqlservercentral.com/Forums/Topic1449039-338-1.aspxHi,I have to insert data from one table to another in SQL 2005 (destination having lesser but the same name of columns as of the Source table). The primary key combination for the destination table is "ROBumber + ROTime" . But the source table has many duplicate entries for the same "RONumber + ROTime" combination. Its primary key is different.What I have to do is that while inserting, whenever the RONumber is duplicate, I have to increment the ROTime by 1 minute, such that the primary key condition is intact and data is successfully inserted into the destination table.Please help me whichever way is possible, either to write an SP for this or to write an SSIS. But I am unable to think how to do the same. Thanks, in advance.Thu, 02 May 2013 21:41:58 GMTPrasune Vermahttp://www.sqlservercentral.com/Forums/Topic1419359-338-1.aspxHi,Select LHP.LHP_PARAM_RESULT_VALUE,Cast(Replace(LHP.LHP_PARAM_RESULT_VALUE,',','') as decimal(18,2))from LS_HMT_PARAM_RESULTS LHPWhereISNUMERIC(LHP_PARAM_RESULT_VALUE) = 1 AND LHP_PARAM_RESULT_VALUE <> '' This Query Run some results and with [b]Arithmetic overflow error converting varchar to data type numeric.[/b] Error[b]Each time the query running with various Record counts[/b] and with the Arithmetic overflow error.Kindly Suggest Me-- Ragu ThangavelWed, 13 Feb 2013 02:48:24 GMTraguyazhinhttp://www.sqlservercentral.com/Forums/Topic1450060-338-1.aspxHi All,I have a Trigger which basically using the INSERTED and DELETED Tables - populates an "Audit" table - with DATE, TIME and various fields. This works fine.But when I lay the same logic under a Stored procedure to fire every 30 minutes I get the dreaded message ;"Conversion failed when converting datetime from character string"The code is the same - DECLARE @CurrentDate DATETIMEDECLARE @CurrentTime Char(10)SET @CurrentDate = CONVERT(char(10), GetDate(),126)Set @CurrentTime = CONVERT(VARCHAR(8),GETDATE(),108)the fields are the same in the tables - [Date] [datetime] NOT NULL, [Time] [char](10) NOT NULLThe Trigger is -INSERT INTO ([Date],[Time])SELECT @CurrentDate, @CurrentTimeThe Stored Procedure is -OUTPUT @CurrentDate, @CurrentTime INTO any help would be appreciated - Tue, 07 May 2013 04:59:24 GMTSteveEClarkehttp://www.sqlservercentral.com/Forums/Topic415829-338-1.aspxHi All,I wants to clear my concept about Temp Table, Table Variable & Common Table Expression Concept.Can anybody please clear me these three concepts and when should i use this?Also I wants to know how they are different from each other in the terms of executing in SQL Server. Cheers!Sandy.Sun, 28 Oct 2007 23:22:44 GMTIN_Sandeephttp://www.sqlservercentral.com/Forums/Topic1448412-338-1.aspxI have this code working fine. I need to subtract one column from another to get the corrected results. I tried qty-NewQty and the results were incorrect. Any ideas?[code="sql"]select s.partNo, p.descript, sum(case when battState = '99' then qty else '0'end) as CLNT, sum(case when battState = '1' then qty else '0' end) as AVAIL, sum(case when battState = '20' then qty else '0' end) as QAH, sum(case when battState = '30' then qty else '0' end) as BOOST, sum(case when battState = '31' then qty else '0' end) as RESTING from batt_State s, pnLU as p where 0=0 and s.PartNo = p.PartNo group by s.partNo, p.descript[/code]Wed, 01 May 2013 07:33:34 GMTkabaarihttp://www.sqlservercentral.com/Forums/Topic1448754-338-1.aspxHi,I've got a really annoying problem in that I have a char column (loaded from csv) that contains dates in multiple formats. Some are 'YYYY-MM-DD HH:mm:SS' others are 'DD/MM/YYYY HH:mm'If I run a where statement like CAST([StupidColumn] AS DATETIME) BETWEEN @FromDate AND @ToDate I get an error in the cast.I thought to myself, that's easy...[code="sql"] set dateformat 'dmy' ;with cteTxns AS ( select ProcessedIndicator, [Transaction Datetime] from myTable where isdate([Transaction Datetime]) = 1 ) update cteTxns set ProcessedIndicator = 0 where CAST([Transaction Datetime] AS DATETIME) = @SomeDate set dateformat 'ymd' ;with cteTxns AS ( select ProcessedIndicator, [Transaction Datetime] from myTable where isdate([Transaction Datetime]) = 1 ) update cteTxns set ProcessedIndicator = 0 where CAST([Transaction Datetime] AS DATETIME) = @SomeDate[/code]but no... isdate appears to only consider the set dateformat when it feels like it or when your system language is set to Italian or whatever lol BOL isn't much help on this subject or I'm just not reading it right.Anyone else come across this? I might end up having to write an update program to detect the date format and convert it unless anyone else has any bright ideas? he asks hopefully...Cheers guys.Thu, 02 May 2013 04:33:27 GMTBenWardhttp://www.sqlservercentral.com/Forums/Topic1094318-338-1.aspxhi all,I want to unpivot multiple columns in my table and i came across this article http://mangalpardeshi.blogspot.com/2009/04/unpivot-multiple-columns.htmlThis blog is very nice ,but the problem i have is i don't have an ID column in my table , i am not getting good results with out the where condition [b]WHERE RIGHT(Suppliers,1) = RIGHT(Cities,1)[/b] in the blog .Is there any way i can unpivot multiple columns for the example in the blog with out a ID(unique) column?Fri, 15 Apr 2011 11:38:07 GMTsrilu_bannuhttp://www.sqlservercentral.com/Forums/Topic1448375-338-1.aspxHi what’s the best to way to drop out of a cursor if a condition is meet?Many thanksWed, 01 May 2013 05:34:41 GMTEdward-445599http://www.sqlservercentral.com/Forums/Topic1446687-338-1.aspxHere is how the data looks like for UserSession TableUserName LastUpdated sessionID--------- ------------- ---------User1 1/1/2013 10AM SESS01User1 1/1/2013 11AM SESS02User1 1/1/2013 12PM SESS03User2 1/1/2013 10AM SESS04User3 1/1/2013 10AM SESS05User3 1/1/2013 11AM SESS06I would like to find users with multiple sessions and delete the old sessions and just keep the only one latest session for user.In above example it should delete following recordsUserName LastUpdated sessionID--------- ------------- ---------User1 1/1/2013 10AM SESS01User1 1/1/2013 11AM SESS02User3 1/1/2013 10AM SESS05and keep following sessionUserName LastUpdated sessionID--------- ------------- ---------User1 1/1/2013 12PM SESS03User2 1/1/2013 10AM SESS04User3 1/1/2013 11AM SESS06Thanks.Thu, 25 Apr 2013 13:25:56 GMTskb 44459http://www.sqlservercentral.com/Forums/Topic1448454-338-1.aspxHi,I need help on Pivoting a table. I have to do it in the back end only.Here is the table that returns the data...VS F1 F2 F3 F4 F5 F6 F7 F8----------------------------------V1 2 3 4 5 6 7 8 9V2 2 3 4 5 6 7 8 9V3 2 3 4 5 6 7 8 9And I have to show this table in the below format. Can this be doable in sql server 2005?VS V1 V2 V3-----------------------------------F1 2 2 2F2 3 3 3F3 4 4 4F4 5 5 5F5 6 6 6F6 7 7 7F7 8 8 8F8 9 9 9Wed, 01 May 2013 09:01:32 GMTsuneelyadahttp://www.sqlservercentral.com/Forums/Topic697397-338-1.aspxHi,I have an MS Access application whose tables are linked via ODBC connection to SQL server 2005. In one of the form when user is trying to change the data it is giving the below error [code]ODBC--call failed[Micorsoft][ODBC SQL Server Driver][SQL Server]cannot insert duplicate key row in object 'tablename' with unique index 'indexname'. (#2601)[Micorsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (#3621)[/code]But when check the same problem in Development environment, there is no problem. I can modify it with out any porblem.That column we want to change is one of the primary key columns and also it has a unique non clustered Index.Can you please suggest on this error?Wed, 15 Apr 2009 05:26:21 GMTVRThttp://www.sqlservercentral.com/Forums/Topic1447216-338-1.aspxI have an sql like this:SELECT 1 AS GROUPID, 'A' AS GROUPNAME, ''GROUPID, ''GROUPNAMEUNIONSELECT 2 AS GROUPID, 'D' AS GROUPNAME, ''GROUPID, ''GROUPNAMEUNIONSELECT 3 AS GROUPID, 'B' AS GROUPNAME, ''GROUPID, ''GROUPNAMEUNIONSELECT '' AS GROUPID, '' AS GROUPNAME, 1 GROUPID, 'A' GROUPNAMEUNIONSELECT '' AS GROUPID, '' AS GROUPNAME, 1 GROUPID, 'AA'GROUPNAMEUNIONSELECT '' AS GROUPID, '' AS GROUPNAME, 2 GROUPID, 'AA'GROUPNAMEUNIONSELECT '' AS GROUPID, '' AS GROUPNAME, 2 GROUPID, 'A'GROUPNAMEUNIONSELECT 2 AS GROUPID, 'AD' AS GROUPNAME, ''GROUPID, ''GROUPNAMEHow to display the report as shown in the attachment....Alignment based on GroupID...Please helpFri, 26 Apr 2013 21:07:46 GMTBoby B Jacobhttp://www.sqlservercentral.com/Forums/Topic1445539-338-1.aspxHi,I have a column (Total) which needs to be updated based on a calculation of 2 other columns(Assigned + Unassigned) and is Grouped By a 4th column (Delivery Date).[b]Before[/b][code="sql"]DeliveryDate Assigned Unaasigned2013-04-29 1 02013-04-29 1 12013-04-29 1 12013-04-29 1 12013-04-30 0 02013-04-30 0 12013-05-01 0 0[/code][b]After[/b][code="sql"]DeliveryDate Assigned Unaasigned Total2013-04-29 1 0 72013-04-29 1 1 72013-04-29 1 1 72013-04-29 1 1 72013-04-30 0 0 12013-04-30 0 1 12013-05-01 0 0 0[/code]The combined Assigned and Unassigned values for 2013-04-29 is 7 and is to be displayed in each of the 4 rows.Can someone show me how to do this using T-Sql please?Thanks in advance,Tue, 23 Apr 2013 09:52:36 GMTDerbyNealhttp://www.sqlservercentral.com/Forums/Topic1439480-338-1.aspxFirst of all, I know I shouldn't do a shrink file because it causes fragmentation. however, I am working with a 3rd party tool and the vendor would like us to shrink the DB so we can move the DB and upgrade the application.I am running a SQL 2005 DB on a SQL server 2008 R2 SP2 server.I am running the following query within the DB that I need to shrink.USE SCData_DefaultGODBCC SHRINKFILE ('SCData_Default', 73559);the size of the DB is 73559 MB. So I'm only trying to shrink the DB right by 200 MB. The query takes ~20 minutes to run. I see a lot of pageIOLatches when i run the Activity Monitor. After a while, I lose connection to the server from my query window and the database was never shrunk. I've also encountered an error that stated the "database could not locate file for database in 'master' in sys.databases..." However, like I stated previously I am running the query in the DB that I need to be shrunk.I have also run:USE SCData_DefaultGODBCC SHRINKFILE (1, 73559);with the above issues as well.The database is in Simple recovery mode.What can I do to fix this and be able to shrink the DB?Fri, 05 Apr 2013 14:29:50 GMTLaura Schmalzbauerhttp://www.sqlservercentral.com/Forums/Topic1446410-338-1.aspxHi,I need to update the Perc column in Table_AThis is based on the following calculation: ((Pass/VM_Id) * 100)and this needs to be grouped by DeliveryDate Where SummaryType > 0NOTES:In this example (where Id = 1), the Perc value should be updated to '0' (as the SummaryType = 0)The calculation needs to be rounded to 0 decimal placesI also have to accommodate any divide by 0 issues[code="sql"]USE [dbName]GOSET DATEFORMAT YMD/****** Object: Table [dbo].[Table_2] Script Date: 04/25/2013 10:34:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Table_A]( [Id] [int] IDENTITY(1,1) NOT NULL, DeliveryDate [SMALLDATETIME] NOT NULL, Pass [INT] NOT NULL, VM_Id [SMALLINT] NOT NULL, Perc [INT] NOT NULL, SummaryType [TINYINT] NOT NULL) ON [PRIMARY];INSERT INTO Table_A(DeliveryDate, Pass, VM_Id, Perc, SummaryType)VALUES ('2013-04-17', 1, 1, '0', '0');INSERT INTO Table_A(DeliveryDate, Pass, VM_Id, Perc, SummaryType)VALUES ('2013-04-24', 1, 1, '0', '1');INSERT INTO Table_A(DeliveryDate, Pass, VM_Id, Perc, SummaryType)VALUES ('2013-04-24', 1, 1, '0', '1');INSERT INTO Table_A(DeliveryDate, Pass, VM_Id, Perc, SummaryType)VALUES ('2013-04-25', 1, 1, '0', '1');INSERT INTO Table_A(DeliveryDate, Pass, VM_Id, Perc, SummaryType)VALUES ('2013-04-25', 0, 1, '0', '1');INSERT INTO Table_A(DeliveryDate, Pass, VM_Id, Perc, SummaryType)VALUES ('2013-04-25', 1, 1, '0', '1');INSERT INTO Table_A(DeliveryDate, Pass, VM_Id, Perc, SummaryType)VALUES ('2013-04-26', 1, 0, '0', '1');[/code]Can anyone help me to update either by:A. All rowsB. In the 1st (top) instance of each DeliveryDate (In this example where Id = 1 or 2 or 4 or 7)Thanks in advance,Thu, 25 Apr 2013 04:50:23 GMTDerbyNealhttp://www.sqlservercentral.com/Forums/Topic1446107-338-1.aspxHello - I have a column in my View script that I convert to varchar(10) and want to convert it back to a date for the output to be in excel. Here is the original line[code="sql"] CONVERT(VARCHAR(10), d.DocDate, 110) AS [Invoice Date][/code]I have tried this line with no luck.[code="sql"]CONVERT(VARCHAR(10), CONVERT(DATETIME, d.DocDate), 110) AS [Invoice Date][/code]Can someone confirm if I have the right lines in the sql liine? Or I'm open to another way of writing it out.Thank youDavidWed, 24 Apr 2013 10:19:55 GMTdavid.ostranderhttp://www.sqlservercentral.com/Forums/Topic1141863-338-1.aspxHi,I know DDL Triggers can monitor changes on both the Server level and Database level, which could track/prevent changes for all Tables in a dB, but is there a way to craft a trigger such that it only prevents the dropping of 1, specific table?And if not, is there another way to prevent a table from getting dropped?Thu, 14 Jul 2011 09:29:03 GMTupstarthttp://www.sqlservercentral.com/Forums/Topic1444465-338-1.aspxHi,A stored procedure calls on this UDF. I have to update it to exclude email addresses with '.@' or '@.' in them.[code="sql"]GO/****** Object: UserDefinedFunction [dbo].[CHKEmail] Script Date: 04/19/2013 09:58:24 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- checks for a vaild email-- returns 0 for valid addresses -- returns 101 if address is less than 5 chars-- returns 102 if address has less or more than 1 "@"-- returns 103 if address has no "."-- returns 104 if address has more than 3 chars after last "." but more than 1, and doesn't contain a valid Domain name-- returns 105 if address has "_" after "@"-- returns 106 if address contains invalid chars--ALTER FUNCTION [dbo].[CHKEmail_test] (@email VARCHAR(50)) ALTER FUNCTION [dbo].[CHKEmail] (@email VARCHAR(50)) RETURNS INT AS BEGIN -- Remove invalid characters from e-mail addressset @email = isnull(@email,'')IF @email > ''BEGINDECLARE @i INTDECLARE @atCnt INTDECLARE @chkEmail INTDECLARE @count INTDECLARE @strcount intDECLARE @strFind char(10)DECLARE @strFindnext char(10)DECLARE @strDomain char(10) -- Check domain namesDECLARE @maxemail intSET @chkEmail = 0-- Check length more than 5 chars ([Email address protected] should be--shortest address)If LEN(@email) < 5 BEGINSET @chkEmail = 101GOTO RREND-- Check email address hat at least one "@"SET @strCount=0SET @Count =1Start1: --LabelSELECT @strFind=SUBSTRING(@email,@Count,1)IF @strFind='@'BEGINSET @strCount=@strCount+1 ENDSET @count=@Count+1IF @Count <= len(@email)BEGIN Goto Start1ENDIF @strcount <> 1BEGINSET @chkEmail=102GOTO RREND -- Check e-mail address has at least one "."SET @strCount=0SET @Count =1Start2: --LabelSELECT @strFind=SUBSTRING(@email,@Count,1)IF @strFind='.'BEGINSET @strCount=@strCount+1 ENDSET @count=@Count+1IF @Count <= len(@email)BEGIN Goto Start2ENDIF @strcount < 1BEGINSET @chkEmail=103GOTO RREND -- Check e-mail address has no more than 3 chars after the last ".", but--more than 1SET @strCount=0SET @Count = len(@email)SET @maxemail = len(@email)Start3: --LabelSELECT @strFind=SUBSTRING(@email,@Count,1)IF @strFind='.'BEGINSet @strcount = @maxemail - @countSET @strDomain = SUBSTRING(@email,@Count+1,@maxemail)set @count = 0ENDSET @count=@Count-1IF @Count > 0BEGINGoto Start3ENDIF @strcount = 3 and @strDomain not in ('biz','cat','com','int','net','org','pro','tel','xxx','edu','gov','mil')BEGINSET @chkEmail = 104GOTO RRENDIF @strcount > 3 and @strDomain not in ('aero','asia','coop','info','jobs','mobi','museum','name','travel')BEGINSET @chkEmail = 104GOTO RRENDIF @strcount < 2BEGINSET @chkEmail = 104GOTO RREND-- Check e-mail address has no "_" after "@".SET @strCount=0SET @Count=1Start4: --LabelSELECT @strFind=SUBSTRING(@email,@Count,1)IF @strFind='@'BEGINSELECT @StrFindnext=SUBSTRING(@email,@Count + 1,1)IF @StrFindnext='_'BEGINSET @chkEmail = 105GOTO RREND ENDSET @count=@Count+1IF @Count <= len(@email)BEGIN Goto Start4END-- Check each char for validity.SET @strCount=0SET @Count =1Start5: --LabelSELECT @strFind=SUBSTRING(@email,@Count,1)IF @strFind IN('~','`','!','#','$','%','^','&','*','(',')','=','+','\','|',']','[','}','{','<','>','?','/','"',':',' ',';')BEGINSET @chkEmail = 106GOTO RRENDSET @count=@Count+1IF @Count <= len(@email)BEGIN Goto Start5ENDENDRR:RETURN @ChkEmailEND/*SELECT dbo.CHKEmail('put email here') AS INVALID*/[/code]I tried altering this chunk to include those, but that doesn't work. Can anyone explain why? Or, I guess, is there a better solution out there?[code="sql"]IF @strFind IN('~','`','!','#','$','%','^','&','*','(',')','=','+','\','|',']','[','}','{','<','>','?','/','"',':',' ',';', '.@', '@.')[/code]ThanksFri, 19 Apr 2013 08:44:53 GMTerikdhttp://www.sqlservercentral.com/Forums/Topic1445593-338-1.aspxI pass an XML parameter from a C# program. This seems to work fine if i change the encoding from utf-16 to utf-8. Why does the XML parser fail with utf-16 as the default encoding?I know I can use the N' : SET @XMLContent = N'<?xml version="1.0" encoding="utf-16"?><...'And then it works fine. But I don't set the XML content in SQL, I set it in my C# program.Is there a way to convert it to Unicode in the stored procedure?Thank you.Tue, 23 Apr 2013 11:18:39 GMTTcW_1978http://www.sqlservercentral.com/Forums/Topic1148528-338-1.aspxI'm running something like this:[code="sql"]more:waitfor delay '00:00:05'insert top (1000) into TableB Select * from TableA A where A.ID not in (select ID from TableB) -- not already insertedif @@rowcount > 0 goto more[/code]Assuming there are 100,000 records to be inserted (100 loops), should I expect to get a result when it's halfway through if I run:select count(*) from TableB with (nolock) Or will my count be 0 until it completes ?[b][u]Update[/u]: I cancelled the insert, modified some code, and now it seems to be working .... basically: inefficient code.[/b]Tue, 26 Jul 2011 11:32:29 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic1445170-338-1.aspxIs it possible to nest (or perhaps stack via CTE) left and right? For example, if i had a list of websites in different formats like:http://www.google.comhttps://www.google.comwww.google.comwww.google.infowww.google.mobiwww.google.deAnd I just wanted to pull out google from each of them, using left and right with charindex on '.'I spent a little bit of time messing with it, but couldn't work out the kinks. It's mostly just a curiosity thing, I don't have a task that requires it. I understand that there's probably a better way to do this.Mon, 22 Apr 2013 14:25:41 GMTerikdhttp://www.sqlservercentral.com/Forums/Topic1441855-338-1.aspxI have a situation where CONTEXT_INFO is currently being used in one area of our PROD instance on a database table that has 5 triggers on it. These 'cascade' as they get hit.I have another table in the same database that is needing to be able to take advantage of the CONTEXT_INFO Function for a single Stored Procedure against a single Trigger on this other table.I'm being told that it will break the current process on the other triggers' table, even though the values are different.I thought the whole idea behind this (when used in a 'Session) was to isolate the CONTEXT_INFO value for that particular object(s) (i.e. Stored Procedure, Trigger) and then reset the value back for other objects to use.Am I even warm on my understanding here? Or am I so far off that I am no where on the map?Here is the T-SQL for both scenarios[b]Scenario 1[/b][code="sql"]CREATE TRIGGER [dbo].[trg_UpdateTradeInStatus]ON [dbo].[AM_TradeIn]AFTER UPDATEAS SET NOCOUNT ON; --PRINT 'Trigger: trg_UpdateTradeInStatus has fired'; --DECLARE @a int, @b int, @c int; --SELECT @a = TradeInStatusID, @b = StatusID, @c = InStoreStatusID FROM inserted; --PRINT 'INSERTED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b) + '; InStoreStatusID = ' + CONVERT(varchar, @c) --SELECT @a = TradeInStatusID, @b = StatusID, @c = InStoreStatusID FROM deleted; --PRINT 'DELETED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b) + '; InStoreStatusID = ' + CONVERT(varchar, @c) IF UPDATE(TradeInStatusID) BEGIN DECLARE @BIN VARBINARY(128), @PREV VARBINARY(128), @OTHER_TRIGGER VARBINARY(128), @EMPTY VARBINARY(128); SELECT @BIN = CAST( 'trg_UpdateTradeInStatus' AS VARBINARY(128) ); SELECT @OTHER_TRIGGER = CAST( 'trg_UpdateCompositeStatus' AS VARBINARY(128) ); SELECT @EMPTY = CAST('' AS VARBINARY(128)) SET @PREV = ISNULL(CONTEXT_INFO(), @EMPTY); DECLARE @Allow int; SET @Allow = 0; SELECT @Allow = CASE ConfigValue WHEN 'True' THEN 1 ELSE 0 END FROM AM_Configuration WHERE ConfigName = 'AllowInvalidStatusTransitions'; IF CONTEXT_INFO() IS NULL SET CONTEXT_INFO @EMPTY; -- *** DEBUG *** --PRINT 'Entering Trigger trg_UpdateTradeInStatus (Level = ' + CAST(TRIGGER_NESTLEVEL() AS varchar) + ')' --SELECT CONTEXT_INFO(); --PRINT 'Trigger: trg_UpdateTradeInStatus - Other Trigger = ' + CAST( @OTHER_TRIGGER AS VARCHAR(128) ); --PRINT 'Trigger: trg_UpdateTradeInStatus - Previous = ' + CAST( @PREV AS VARCHAR(128) ); --PRINT 'Trigger: trg_UpdateTradeInStatus - Context = ' + CAST( CONTEXT_INFO() AS VARCHAR(128) ); /* Update the Composite status (StatusID and InStoreStatusID) based on the new TradeInStatusID */ IF CONTEXT_INFO() IS NULL OR CONTEXT_INFO() <> @OTHER_TRIGGER BEGIN SET CONTEXT_INFO @BIN; -- *** DEBUG *** --PRINT 'Trigger: trg_UpdateTradeInStatus is executing'; --PRINT 'Trigger trg_UpdateTradeInStatus - Mapping new status back to old composite'; --SELECT @a = TradeInStatusID, @b = StatusID, @c = InStoreStatusID FROM inserted; --PRINT 'INSERTED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b) + '; InStoreStatusID = ' + CONVERT(varchar, @c) --SELECT @a = TradeInStatusID, @b = StatusID, @c = InStoreStatusID FROM deleted; --PRINT 'DELETED: TradeInStatusID = ' + CONVERT(varchar, @a) + '; StatusID = ' + CONVERT(varchar, @b) + '; InStoreStatusID = ' + CONVERT(varchar, @c) UPDATE ti SET ti.StatusID = CASE WHEN i.ItemDescription = 'Video Games' AND i.StoreID = 0 AND s.StatusCode = 'RCV' THEN 6 ELSE ISNULL(s.MapStatusID, i.StatusID) END, ti.InStoreStatusID = CASE WHEN i.ItemDescription = 'Video Games' AND i.StoreID = 0 AND s.StatusCode = 'RCV' THEN NULL ELSE s.MapInStoreStatusID END, ti.PaymentRefID = CASE WHEN i.ItemDescription = 'Video Games' AND i.StoreID = 0 AND s.StatusCode = 'RCV' THEN 'VERIFIED' ELSE i.PaymentRefID END FROM AM_TradeIn ti INNER JOIN inserted i ON ti.TradeInID = i.TradeInID INNER JOIN deleted d ON ti.TradeInID = d.TradeInID INNER JOIN TradeInStatus s ON i.TradeInStatusID = s.TradeInStatusID WHERE ISNULL(i.TradeInStatusID, -1) <> ISNULL(d.TradeInStatusID, -1) AND (@Allow =1 OR dbo.TransitionValidity(d.TradeInStatusID, i.TradeInStatusID, i.StoreID) = 1); END; /* Reverse invalid TradeInStatusID transitions if Disallowed by the configuration flag */ IF @Allow = 0 BEGIN -- *** DEBUG *** --PRINT 'Reversing Update Trigger trg_UpdateTradeInStatus (Level = ' + CAST(TRIGGER_NESTLEVEL() AS varchar) + ')' UPDATE t SET t.TradeInStatusID = d.TradeInStatusID FROM AM_TradeIn t INNER JOIN inserted i ON t.TradeInID = i.TradeInID INNER JOIN deleted d ON i.TradeInID = d.TradeInID WHERE ISNULL(i.TradeInStatusID, -1) <> ISNULL(d.TradeInStatusID, -1) AND dbo.TransitionValidity(d.TradeInStatusID, i.TradeInStatusID, i.StoreID) = 0; --PRINT CONVERT(varchar, @@ROWCOUNT) + ' status transitions were reversed.' END; /* Insert a new log of the change of status or disposition */ --PRINT 'Trigger: trg_UpdateTradeInStatus - Context = ' + CAST( CONTEXT_INFO() AS VARCHAR(128) ); --PRINT 'Trigger: trg_UpdateTradeInStatus - Other Trigger = ' + CAST( @OTHER_TRIGGER AS VARCHAR(128) ); IF CONTEXT_INFO() <> @OTHER_TRIGGER BEGIN --PRINT 'Logging Trigger trg_UpdateTradeInStatus (Level = ' + CAST(TRIGGER_NESTLEVEL() AS varchar) + ')' INSERT INTO StatusDispositionHistory (TradeInID, PreviousStatusID, NewStatusID, PreviousDispositionID, NewDispositionID, DispositionChangeReasonID, CustomerCareReasonID, PreviousTradeInStatusID, NewTradeInStatusID, TransitionType, PreviousInStoreStatusID, NewInStoreStatusID, PreviousShrinkTypeID, NewShrinkTypeID, PreviousShrinkDate, NewShrinkDate, CreatedBy, UpdatedBy) SELECT i.TradeInID, d.StatusID, ti.StatusID, d.DispositionID, i.DispositionID, i.DispositionChangeReasonID, i.CustomerCareReasonID, d.TradeInStatusID, i.TradeInStatusID, CASE WHEN dbo.TransitionValidity(d.TradeInStatusID, i.TradeInStatusID, i.StoreID) = 0 THEN CASE @Allow WHEN 0 THEN 'R' ELSE 'I' END ELSE t.TransitionType END, d.InStoreStatusID, ti.InStoreStatusID, d.ShrinkTypeID, i.ShrinkTypeID, d.ShrinkDate, i.ShrinkDate, 0, 0 FROM inserted i INNER JOIN deleted d ON i.TradeInID = d.TradeInID INNER JOIN AM_TradeIn ti ON i.TradeInID = ti.TradeInID LEFT OUTER JOIN TradeInStatusTransition t ON d.TradeInStatusID = t.PrevTradeInStatusID AND i.TradeInStatusID = t.NextTradeInStatusID WHERE ISNULL(i.TradeInStatusID, -1) <> ISNULL(d.TradeInStatusID, -1); END SET CONTEXT_INFO @PREV; END --PRINT 'Trigger: trg_UpdateTradeInStatus - Exiting';[/code][b]Scenario 2[/b][code="sql"]USE [DETDB001]GO/****** Object: Trigger [dbo].[upd_LotStatusID] Script Date: 04/12/2013 11:08:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*<DOC> <SUMMARY> This trigger will align the TradeInStatusID of the TradeIn records associated to a Lot when the Lot Status changes. </SUMMARY> <REMARKS> This is part of the ACDS/Brightstar integration project. The new TradeInStatus will be communicated to Brightstar through the DSP interface. </REMARKS> <HISTORY author="Anonymous" date="1/17/2013" redmine="8578">Added mapping from Lot Status 4, 5, and 6 to TradeInStatus 17 (ONL)</HISTORY> <HISTORY author="Anonymous">Added Context_Info bypass to allow for bulk updates.</HISTORY></DOC>*/ALTER TRIGGER [dbo].[upd_LotStatusID] ON [dbo].[AM_Lots]FOR UPDATEAS-- Add Context_Info bypass to facilitate and streamline bulk updateds. (ANON 03/12/2013)DECLARE @Cinfo VARBINARY(128); -- ContextInfo Value to bypassSELECT @Cinfo = Context_Info();IF @Cinfo = 0x55555 RETURN;Set NOCOUNT ON;IF UPDATE(StatusID) BEGIN UPDATE ti SET ti.TradeInStatusID = CASE i.StatusID WHEN 8 THEN CASE WHEN ih.StatusID BETWEEN 1 AND 4 THEN 19 WHEN ih.StatusID BETWEEN 5 AND 6 THEN 18 WHEN ih.StatusID = 7 THEN 15 ELSE ti.TradeInStatusID END WHEN 7 THEN 15 WHEN 6 THEN 17 WHEN 5 THEN 17 -- Changed from RFS => ONL (RAB 1/17/2013) WHEN 4 THEN 17 -- Changed from RFS => ONL (RAB 1/17/2013) ELSE ti.TradeInStatusID END FROM AM_TradeIn ti INNER JOIN AM_TradeInLots l ON ti.TradeInID = l.TradeInID INNER JOIN inserted i ON l.LotID = i.LotID INNER JOIN deleted d ON i.LotID = d.LotID INNER JOIN AM_Consignor c ON ti.ConsignorID = c.ConsignorID LEFT OUTER JOIN AM_InvoiceHeader ih ON i.InvoiceID = ih.InvoiceID WHERE i.StatusID <> d.StatusID AND i.StatusID BETWEEN 4 AND 8 AND ti.StoreID > 0 AND c.IsBrightStar = 1; END;[/code]Any and all expertise and insight here is greatly appreciated.Thanks,SQL_ME_RICHFri, 12 Apr 2013 12:55:06 GMTSQL_ME_RICHhttp://www.sqlservercentral.com/Forums/Topic1443386-338-1.aspxThis is probably a simple join issue but here goes...We have a system (HAL) that does not contain the Dr badge number. The Dr badge number is housed in another system. A co-worker sent me all Doctor F_Name, L_Name, M_Inital, Dr_Badge_Num in a speadsheet. I imported these 4 columns into a table (DR) in the (HAL) database so I could join HAL.F_Name = DR.F_Name and HAL.L_Name = DR.L_Name and HAL.M_Initial = DR.M_Inital so I could pull in the Dr_Badge_Num Column.However this restricts my results to only existing DR table entires. Since the DR table is static and not updated I need my results to show none matching HAL.x as Null in the Dr_Badge_Num column.Example:Static DR Tablef_name, m_initial, l_name, dr_num1. John A. White 234562. Tom B. Smith 654323. Jack T. Murfy 12345HAL Table w/o Dr Number fieldf_name, m_initial, l_name1. John A. White2. Tom B. Smith3. Jack T. Murfy4. Dennis K. HugesResults:f_name, m_initial, l_name, dr_num1. John A. White 234562. Tom B. Smith 654323. Jack T. Murfy 123454. Dennis K. Huges NULLThank you!Wed, 17 Apr 2013 10:57:24 GMTVertigo44http://www.sqlservercentral.com/Forums/Topic558696-338-1.aspxHi ALL,SQL: Select MyColumn FROM (SELECT CAST(CAST(0.10*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnion SELECT CAST(CAST(0.011*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnion SELECT CAST(CAST(0.02*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumnUnion SELECT CAST(CAST(0.12*100 AS numeric(10,2)) AS varchar(5)) + '%' as MyColumn) MyTableOrder by 1 ascResult:MyColumn1.10%10.00%12.00%2.00%Problem: values are not sortedQuestion: Is it possible to sort values in ascending Order?ThanksTue, 26 Aug 2008 03:29:59 GMTSMAZ-429882