InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:15:18 GMT20http://www.sqlservercentral.com/Forums/Topic800271-338-1.aspxHi all,i want to know whether a upper case letter is there in a given string or not?if i provide 'Sarat' it needs to give me a flag value like 1if i provide 'sarat' then it should give 0elsefrom a given field in a table i want to retrieve only those rows doesnt contain any upper case letter in any position.please let me know if you have answer.Thu, 08 Oct 2009 12:39:46 GMTfrom corporatehttp://www.sqlservercentral.com/Forums/Topic1305604-338-1.aspxHi,i have a huge database where users can search according to their requirements(this is location base system).my search query is working well.Now i want to show users related search...here is my query[code="sql"]select TOP 2 COMPANY,CategoryName,comp_kws,ID, len(COMPANY) - len(replace(COMPANY, @qry, '')) as cntcmp ,len(CategoryName) - len(replace(CategoryName, @qry, '')) as cntcat,len(comp_kws) - len(replace(comp_kws, @qry, '')) as cntkey--,--len(locStr) - len(replace(locStr, @qry, '')) --as cntStrfrom myView Where locStr is not null and COMPANY is not null and CategoryName is not null and comp_kws is not null GROUP BY ID,COMPANY,CategoryName,comp_kws--,locStr order by cntcmp DESC,cntcat DESC,cntkey DESC--,cntStr DESC[/code]the issue with is query is this shows similar records...i dnt want that either company be same OR company and CategoryName be same....same fields should be different and also two fields must not have same data/record.How it can be?Thu, 24 May 2012 02:44:44 GMTMethewhttp://www.sqlservercentral.com/Forums/Topic1304225-338-1.aspxTo get around Kerbos double hop.. can you set your view's SQL statement to use its own permissions?View's statementSELECT j.job_id, j.category_id, c.name AS category_name, 'P01' AS Server_NameFROM p01.msdb.dbo.sysjobs AS j INNER JOIN p01.msdb.dbo.syscategories AS c ON j.category_id = c.category_idUNIONSELECT j.job_id, j.category_id, c.name AS category_name, 'P02' AS Server_NameFROM p02.msdb.dbo.sysjobs AS j INNER JOIN p02.msdb.dbo.syscategories AS c ON j.category_id = c.category_idUNIONSELECT j.job_id, j.category_id, c.name AS category_name, 'P03' AS Server_NameFROM p03.msdb.dbo.sysjobs AS j INNER JOIN p03.msdb.dbo.syscategories AS c ON j.category_id = c.category_idUNIONSELECT j.job_id, j.category_id, c.name AS category_name, 'SQDP01' AS Server_NameFROM SQDP01.msdb.dbo.sysjobs AS j INNER JOIN SQDP01.msdb.dbo.syscategories AS c ON j.category_id = c.category_idTue, 22 May 2012 08:48:57 GMTdwilliscphttp://www.sqlservercentral.com/Forums/Topic732363-338-1.aspxI'm getting an error when trying to backup a database on a 64 bit machine. I've previously benn able to do this on another 64 bit machine. What am I doing wrong? Is there some setting I need to change?BACKUP DATABASE WITH COMPRESSION is not supported on Standard Edition (64-bit).Here is my queryBACKUP DATABASE CaseLevelDataTO DISK = 'F:\Backup\WednesdayCaseLevelData.bak'WITH COMPRESSION, INIT,FORMAT,CHECKSUM,STOP_ON_ERRORWed, 10 Jun 2009 08:57:52 GMTdndaughteryhttp://www.sqlservercentral.com/Forums/Topic1305310-338-1.aspxI've attached the ddl for the table, the insert, and the desired results. I need to write a query that does this for duplicate Patient_nbr's: - If the [b]status[/b] is the same, Pulls the record with the max [b]tracking_id[/b] - If [b]status[/b] is different, pull both recordsSo [b]status[/b] and [b]patient_nbr [/b]make the record unique. There shouldn't be a line that has the same [b]patient_nbr[/b] and [b]status[/b].Wed, 23 May 2012 13:29:55 GMTsqluser_8119http://www.sqlservercentral.com/Forums/Topic1304580-338-1.aspxI'm sure this is simple, I'm just missing something here. I have a table of data, and I want to always select the last 13 weeks. That part I got ok with this:Select *FROM vw_QA_Quality_excel_NewRateWHERE WeekFrom > dateadd(wk,-13,getdate())But the problem is that the data is not always updated, so 13 weeks from getdate() may not always show 13 weeks of data. So I'm just trying to first query my date column and find the most recent date, then count 13 weeks back from that, so there's always 13 weeks of actual data. I'm trying something like this, but SQL doesnt' like it, giving an error on the keyword 'Select'DECLARE @LASTFULLWEEK DATETIMESET @LASTFULLWEEK = Select MAX(WeekFrom) AS LASTFULLWEEK;Select *FROM vw_QA_Quality_excel_NewRateWHERE WeekFrom > dateadd(wk,-13,@LASTFULLWEEK)Any help is greatly appreciated!Tue, 22 May 2012 15:32:18 GMTericb1http://www.sqlservercentral.com/Forums/Topic1303100-338-1.aspxHi,i have .DAT file having whole world information.when i am getting information from this file,it takes time.I want to save these information in sqlserver and have some indexing etc to increase my performance...My question is Can i save this DAT file in sqlserver?Sun, 20 May 2012 07:42:52 GMTMethewhttp://www.sqlservercentral.com/Forums/Topic1304252-338-1.aspxHi AllHere is my problem. I have a table that is used for time clock punches, the IN and OUT punches do not have any way of linking the two together. The only way to see that an IN goes with an OUT is the order. Date won't work. There is a transaction id that is incremented after each punch. Here is some sample data.TC_EMPNO Date\Time Punch Type tc_type TC_ID 805880 2012-05-20 22:41:00.000 IN 1 8831916 805880 2012-05-21 07:15:00.000 OUT 11 8837290 805880 2012-05-21 22:46:00.000 IN 1 8850645 805880 2012-05-22 07:15:00.000 OUT 11 8855954The only thing I can think of is to use the TC_ID to connect the two.Any ideas would be appreciated.Thanks so muchCraigTue, 22 May 2012 09:24:42 GMTcraigalanizhttp://www.sqlservercentral.com/Forums/Topic854865-338-1.aspxHi people..Is it possible to Count number of columns in a CSV file from a T-SQL code? i think we can do that using Log Parser.. but from T-SQL code i guess OPENROWSET can do that, but i cant find how to do that??If u guyz know how, please post some sample code here :)Thanks in advance!!Wed, 27 Jan 2010 19:42:54 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic1301926-338-1.aspxIn a case I have to use dynamic sql in my query, here is the query:set @sql = 'select @subTotal = count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' + ' group by sa'exec (@sql)there is no problem with the @sql, but, after it is executed, I can't get @subTotal, I guess the variable's value didn't get retained after the exec, how do I retain the value?Thanks.Thu, 17 May 2012 10:19:42 GMThalifaxdalhttp://www.sqlservercentral.com/Forums/Topic1303086-338-1.aspxI have just starting using MS SQL Server. I want to create a DDL trigger. The idea is as follows:I have a table named DefaultValues with these columns:DefaultValueID, TableName,ColumnName, ColumnValueThe table is used to store default values for other tablesTo obtain integrity the idea is to have a trigger(after update) to catch if a column in a table changes name and then change the values in the ColumnName accordingly.Can some of you clever people assist me in my first efforts to create such a triggerSun, 20 May 2012 03:56:36 GMTmagnus S.http://www.sqlservercentral.com/Forums/Topic1302874-338-1.aspxI have 218 stored procedures that were created a very long time ago and all have Set Quoted_Identifiers set to off. We want to update all the procedures to set it to ON where there is no need for it to be off. Is there any easy way to do this or do I have to go in to each procedures and explicitly set it to on and alter each procedure? I have 5500 databases that I will have to deploy these to so I am hoping there is an easier way. Fri, 18 May 2012 15:48:17 GMTpamozerhttp://www.sqlservercentral.com/Forums/Topic1301353-338-1.aspxI know this is weird, just couldn't think of a possible reason, please help. Thanks.I have a sp, which will create a temp table and the temp table's columns are generated dynamically from another table's content. the code piece is here:[code="sql"]...............Declare @sqlCreateTable varchar(2000)declare @Category Varchar(255)declare @CategoryChecked bit --1: the DocID's this category is Checked; 0: not checked Set @sqlCreateTable = 'Create table #tFinal (DocID int' --print @sqlCreateTable DECLARE c CURSOR FOR SELECT Category From Categories OPEN c FETCH NEXT FROM c INTO @Category WHILE @@FETCH_STATUS = 0 BEGIN Set @sqlCreateTable = @sqlCreateTable + ', [' + @Category + '] varchar (50)' --print @sqlCreateTable FETCH NEXT FROM c INTO @Category END CLOSE c DEALLOCATE c Set @sqlCreateTable = @sqlCreateTable + ')' --Now the create table query is ready, exec it to create the table print @sqlCreateTable exec (@sqlCreateTable) select * from #tFinal.....................................[/code]The table #tFinal cannot be created, I double checked the dynamic sql (@sqlCreateTable) that is executed to create it, there is no any problem with it, and to confirm this, I print it out, and then copy the print output which is a create table query, I run it in another window, the table DOES get created!!!Then why it DOES NOT get created inside the sp? There is no any error output!Thanks.Wed, 16 May 2012 14:20:22 GMThalifaxdalhttp://www.sqlservercentral.com/Forums/Topic1301882-338-1.aspxHi,I'm having some trouble with what I thought was a fairly easy query...[code="sql"]select CMPCOMPANYCODE ,CMPCOMPANYNAME ,CMPCOID ,GLRBASEACCT ,GLRGLCODETYPE ,GLRCOMPONENTCODE ,GLRGLSEGSEQCODE ,GLQSEGSEQDESC ,GLQSEGMENTSEQUENCE ,GLQSEGMENT0 ,GLQCONSTANTSEGMENTVALUE0 ,GLQSEGMENT1 ,GLQCONSTANTSEGMENTVALUE1 ,GLQSEGMENT2 ,GLQCONSTANTSEGMENTVALUE2 ,GLQSEGMENT3 ,GLQCONSTANTSEGMENTVALUE3 ,GLQSEGMENT4 ,GLQCONSTANTSEGMENTVALUE4 ,GLQSEGMENT5 ,GLQCONSTANTSEGMENTVALUE5 ,GLQSEGMENT6 ,GLQCONSTANTSEGMENTVALUE6 ,GLQSEGMENT7 ,GLQCONSTANTSEGMENTVALUE7 ,eaccoid ,eacaccrcode ,EacAccrOption from GLDEFRLS INNER JOIN accropts AC ON AC.AccAccrOption = EM.EacAccrOption INNER JOIN empaccr EM ON EM.EacAccrOption = AccAccrOption INNER JOIN GLSEGSEQ ON GLRGLSEGSEQCODE = GLQGLSEGSEQCODE and --GLQGLSEGSEQCODE = 'seg08' --and glrglcodeTYPE LIKE 'A%' INNER JOIN company ON GLRCOID = CMPCOID where dbo.wsi_f_get_companycode(glrcoid) in ('FSL','FLT','SMS','SSC','TSI','CHW','ESL','SSL')-- AND GLRBASEACCT = '5208'-- INNER JOIN accropts -- ON AccAccrOption = EacAccrOption ORDER BY CMPCOMPANYCODE ,GLRCOMPONENTCODE ,GLRGLCODETYPE[/code]It says "The multi-part identifier "EM.EacAccrOption" could not be bound.'can't seem to figure it out..Thu, 17 May 2012 09:46:42 GMTkrypto69http://www.sqlservercentral.com/Forums/Topic1301565-338-1.aspxHi,I would like to import a csv file to my Database. The import should captured the Double Quotes(") and Single Quotes(')->(without delimited )1.Platform: SQL analyzer.2.SQL Script: [i]BULK INSERT Data.dbo.DailyListingFROM 'D:\Listing\Daily\Listing.csv'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '')[/i]3.Error Message:[i]Server: Msg 4863, Level 16, State 1, Line 1Bulk insert data conversion error (truncation) for row 19526, column 1 (ItemCode).Server: Msg 4863, Level 16, State 1, Line 1Bulk insert data conversion error (truncation) for row 19527, column 1 (ItemCode). [/i]4.Sample of CSV[b]ItemCode[/b] | [b]Description[/b] | [b] Packing[/b]SSPVGRTB50GX100 | PUFF 2 | 100X50G SSPZBPCC6"X48 | PIZZA 6" - CC | 48 X 6" SSPZBPAA6'X48 | PIZZA 6' - BB | 48 X 6' SSPZBPBB6"X48 | PIZZA 6" - AA | 48 X 6" SSPVGRTB50GX100 | PUFF - 2 | 100X50G Got any way to import the Double and single quotes to DB?Please help...:w00t:Thu, 17 May 2012 02:56:00 GMTyapaflyhttp://www.sqlservercentral.com/Forums/Topic1300484-338-1.aspxI have a sp and need to run it with parameterexec spGetCategoriesByDocIDAsTable 811This will return result like:[code="sql"]ID Category Checked1 Category1 02 Category2 03 Category3 14 Category4 05 Category5 1[/code]Now I and save the result into a temp table with some filtering, I am doing this way:[code="sql"]SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'EXEC spGetCategoriesByDocIDAsTable 811')[/code]It returns me error:[code="sql"]Msg 7357, Level 16, State 2, Line 1Cannot process the object "EXEC spGetCategoriesByDocIDAsTable 811". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.[/code]What's wrong with the query?Thank you.Tue, 15 May 2012 12:04:54 GMThalifaxdalhttp://www.sqlservercentral.com/Forums/Topic1300111-338-1.aspxHiI've been asked to create an SQL pivot table at work, and to be honest I have not seen this stuff in a long time (never worked with SQL pivot tables in particular).This is the task I have been given:I have two tables, Master_List and Script_Results, and both of these contain a number of fields.In the Master_List there are a list of suppliers, and they have a Record_ID.In Script_Results, the records relate to the Record_ID above, but different data is captured.What I require is to bring these two tables together, displaying all the fields. it has been suggested to me to use a pivot table - is this the best way of achieving what I need? Or is there a better way?Tue, 15 May 2012 03:40:42 GMTdoyler61185http://www.sqlservercentral.com/Forums/Topic1299015-338-1.aspxHello All,Is there a better (or more elegant ) way of executing the output of the output of the Stored Proc?In other words I am using a lot of Dynamic SQL of different levels in my process, so I have a one Stored Proc which generates an output.Than I am taking that (first) ouput and executes it. Once executed, I am taking the 2nd output and executes it. And that will give me desired result.So far I am using the following which works fine for me, since it affect only few rows, but was wondering if there is a better way to implement:sqlcmd -SServer -E -vDBNAME=Mydatabase -q"EXECUTE MystoredProc" -oC:\Output.txtsqlcmd -SServer -E -vDBNAME=Mydatabase -iC:\Output.txt -oC:\Output1.txtsqlcmd -SServer -E -vDBNAME=Mydatabase -iC:\Output1.txt -oC:\Output2.txtC:\Output2.txt just showing me how many records were affected total and I am not using it anywhere, so basically I don't need it to generate it.Thank you,Fri, 11 May 2012 18:13:37 GMTbarsukhttp://www.sqlservercentral.com/Forums/Topic1298741-338-1.aspxI have the following set of dataDTime id ticketid actionlog[color="ff0000"]2012-05-09 05:24:58.000 13957 1298 created[/color][color="ffa000"]2012-05-09 08:36:05.000 13967 1298 reply_tech[/color]2012-05-09 08:46:04.000 13968 1298 reply_tech[color="ff0000"]2012-05-09 09:08:03.000 13972 1298 reply_user[/color]2012-05-09 09:41:04.000 13976 1298 reply_user2012-05-09 09:50:03.000 13979 1298 reply_user[color="ffa000"]2012-05-09 09:52:45.000 13982 1298 reply_tech[/color][color="ff0000"]2012-05-09 09:58:05.000 13986 1298 reply_user[/color]2012-05-10 01:02:04.000 14008 1298 reply_user[color="ffa000"]2012-05-10 13:50:59.000 14019 1298 reply_tech[/color]2012-05-10 13:55:50.000 14021 1298 reply_tech2012-05-10 14:02:04.000 14023 1298 reply_userI need to get a result set of the measure of time difference from the first user action in a potential group of user interactions, to the first tech interaction in a group of tech interactions, in a series of both tech and user interactions. Of the data above, there would be three numbers in the result set from the select:03:11:0700:44:4203:52:54This would be obtained by a calculation of this:select convert(varchar(10),dateadd(second,datediff(second,'first user reply after last tech reply','first tech reply after first user reply after previous tech reply'),'1900-01-01'),8)The actual values that would be calculated would be:[code="sql"]select convert(varchar(10),dateadd(second,datediff(second,'2012-05-09 05:24:58.000','2012-05-09 08:36:05.000'),'1900-01-01'),8)select convert(varchar(10),dateadd(second,datediff(second,'2012-05-09 09:08:03.000','2012-05-09 09:52:45.000'),'1900-01-01'),8)select convert(varchar(10),dateadd(second,datediff(second,'2012-05-09 09:58:05.000','2012-05-10 13:50:59.000'),'1900-01-01'),8)[/code]Anyone have a script on how I could I mine out those date/time stamps as highlighted above (red to orange)? I have been using row_number over partition actionlog order by dtime,actionlog and a recursive CTE, but I can't seem to filter out the unwanted records.[code="SQL"]create table #tmpmatt(DTime datetime,id int,ticketid int, actionlog varchar(20))insert #tmpmattselect '2012-05-09 05:24:58.000',13957,1298,'created'union allselect '2012-05-09 08:36:05.000',13967,1298,'reply_tech'union allselect '2012-05-09 08:46:04.000',13968,1298,'reply_tech'union allselect '2012-05-09 09:08:03.000',13972,1298,'reply_user'union allselect '2012-05-09 09:41:04.000',13976,1298,'reply_user'union allselect '2012-05-09 09:50:03.000',13979,1298,'reply_user'union allselect '2012-05-09 09:52:45.000',13982,1298,'reply_tech'union allselect '2012-05-09 09:58:05.000',13986,1298,'reply_user'union allselect '2012-05-10 01:02:04.000',14008,1298,'reply_user'union allselect '2012-05-10 13:50:59.000',14019,1298,'reply_tech'union allselect '2012-05-10 13:55:50.000',14021,1298,'reply_tech'union allselect '2012-05-10 14:02:04.000',14023,1298,'reply_user'select * from #tmpmatt[/code]Fri, 11 May 2012 10:15:32 GMTmatt.newmanhttp://www.sqlservercentral.com/Forums/Topic1298022-338-1.aspx[b]Background:[/b]I am running queries against pre-2005 databases.I'm trying to normalize data from a table (ID, Value1, Value2, Value3...Value12 where Value contains each sequence of a diagnosis instead of having ID, Sequence, Diagnosis)For reporting, I need to normalize, but can't use unpivot, and have already done the 12 iterations of unioning the data together.[b]The problem:[/b] The ID has a length of 14, but when a user would be passing a parameter in, they only need to enter in the first 12 because the last two are [u]always[/u] either 00 or 01. When I run the query with the criteria [color="#666666"]ID LIKE @Parameter + '%' [/color]in Reporting Services it takes [i]ages[/i] to run.I've also tried [color="#666666"]left(ID, 12) = @Parameter[/color], which takes just as long.However, when I pass a literal value in i.e. [color="#666666"]ID LIKE 123456789123 + '%' [/color]it takes less than a second. I don't know if there is something I can do better to get that parameter in and return everything for the first 12 of that ID, so any ideas or suggestions would be VERY much appreciated!Thu, 10 May 2012 10:13:44 GMTthemangoagenthttp://www.sqlservercentral.com/Forums/Topic1299349-338-1.aspxhi all..i need to write a query whereby using case statement in where clause...can anyone guide me? what i want is in my table i need to base on one column(co_role) to determine my where clause..eg:-[code="sql"]delete from co_employee where (co_salary > 5000 or co_transfer = 'Y') and co_department = 'HR' and co_role = 'Manager'delete from co_employee where co_class = 'Contract' and co_salary > 2000 and co_department = 'HR' and co_role = 'Staff'[/code]so i was thinking to write in this way but its not working:-[code="sql"]delete from co_employee where case (co_role) when 'Manager' then ((co_salary > 5000 or co_transfer = 'Y') and co_department = 'HR') when 'Staff' then (co_class = 'Contract' and 'co_salary > 2000 and co_department = 'HR')else null end[/code]thanksSun, 13 May 2012 22:58:39 GMTkame87http://www.sqlservercentral.com/Forums/Topic1293567-338-1.aspxhi Guy , please can somebody explain me how i can do to update my table ? hier ist one example:[code="sql"]/***************************Create different table and foreign key****************************/CREATE TABLE Family1( ID_F1 int IDENTITY(1,1) NOT NULL, Name varchar(50) NOT NULL, Old int NOT NULL, Children int )----------------------------------------CREATE TABLE Family2( ID_F2 int IDENTITY(1,1) NOT NULL, Name varchar(50) NOT NULL, Old int NOT NULL , Children int NULL)-----------------------------------------CREATE TABLE Dependence( [Data_AbID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [ID_F1] [int] NOT NULL, [ID_F2] [int] NULL, [Dependance] [varchar](50) NULL)------------------------------------------SET ANSI_PADDING OFFGOALTER TABLE dependence WITH CHECK ADD CONSTRAINT [FK_F1] FOREIGN KEY(ID)REFERENCES family1 (ID)ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE dependence WITH CHECK ADD CONSTRAINT [FK_F2] FOREIGN KEY(ID)REFERENCES Family2 (ID)ON UPDATE CASCADEON DELETE CASCADEGO/***************************Fill my tables ****************************/insert into dependence(ID_F1,ID_F2)values(1,3),(1,5),(2,null),(3,4),(4,1),(5,2)--------------------insert into Family2(Name,Old)values('F',28), ('G',90), ('H',72), ('J',12), ('K',3)-------------------------------------------insert into Family1(Name,Old)values('A',1), ('B',3), ('C',7), ('D',12), ('E',34) ----------------------------------------------/*********************************Create my View V_Dependance*********************************/create view V_dependance(Name,DependOf) asselect A.Name,AD.Name from dependence AtLeft outer join Family1 A onAt.ID_F1 = A.IDleft outer join Family2 AD onAt.ID_F2 = AD.ID------------------------------------------/*********************************Now I want to update my Children Column in Family1 with help of my View and it is here where i get a problem***********************************/UPDATE Family1 SET Children = 3 WHERE Name = (SELECT Name FROM v_dependance WHERE dependOf IS NULL)--feeback: it don't walk because many rows are affected-- have somebody an idea how i can do??-------------------------------------[/code] Tue, 01 May 2012 18:35:57 GMTinf1154http://www.sqlservercentral.com/Forums/Topic1298850-338-1.aspxI have this in one table (columns are Patient#, Name, EntryDate)123456789, Jane Doe, 4/12/12123456789, Jane Doe, 4/15/12555666888, Alice Doe, 3/13/12555666888, AliceDoe, 3/20/12What t-sql can I use to pull the most recent entry date for each Patient#? This is what it should look like...123456789, Jane Doe, 4/15/12555666888, AliceDoe, 3/20/12Fri, 11 May 2012 12:34:13 GMTsqluser_8119http://www.sqlservercentral.com/Forums/Topic1298375-338-1.aspxHi,I am having a table with a column which is having data like # followed by some number for Eg. #123.I want to strip out the # and the number from the column using TSQL without using clr regex replace.Any help here would be highly appreciated.Script for generating test data is given below.[code="sql"]DECLARE @BadData TABLE(DataID INT, Data VARCHAR(50), ExpectedData VARCHAR(50))INSERT INTO @BadData(DataID,Data,ExpectedData)SELECT 1,'#123 data','data' UNIONSELECT 2,'#456data','data' UNIONSELECT 3,'data #789','data' UNIONSELECT 4,'data#012','data' UNIONSELECT 5,'data#345 some more data','data some more data' UNIONSELECT 6,'data #678some more data','data some more data' UNIONSELECT 7,'data #901 some more data','data some more data' UNIONSELECT 8,'data#234some more data','datasome more data'SELECT * FROM @BadData[/code]The column 'Data' has bad values and the column 'ExpectedData' has the expected values.I will soon post the queries i have tried so far but they didn't work, hence posting it to the forum.Fri, 11 May 2012 01:25:27 GMTShaun-884394http://www.sqlservercentral.com/Forums/Topic1296636-338-1.aspxI want to do something like this..When i execute this query below i want to know if gives filegroupid column in the result set. I want to write some logc based on that using IF case.exec master.dbo.xp_restore_filelistonly @filename ='\\Backups\test.bak'Just so you know older versions do no have filegroupid but new version of litespeed software has this, so i want to verify backup for any version.Tue, 08 May 2012 10:57:15 GMTTara-1044200http://www.sqlservercentral.com/Forums/Topic760029-338-1.aspxCan anyone tell me how I could get all my fields to be not only comma separated but quote (") delimited.i.e.current outputFRED,SMITH,3 THE STREET,ANYWHEREpreferred output"FRED","SMITH","3 THE STREET","ANYWHERE"Only sqlcmd usage allowed.Mon, 27 Jul 2009 07:16:13 GMTKelvin Phayrehttp://www.sqlservercentral.com/Forums/Topic829692-338-1.aspxHi,I would like to achieve the following:I need an update query wich would do the following.Suppose i have a deadline column in a table, with value '6/01/2010 0:00:00' (dd/mm/yyyy)Today we are the '7/12/2009 0:00:00'.When i run the update query today it should change the deadline value to '7/01/2010 0:00:00'...But suppose i run the query on '8/12/2009 0:00:00' then the deadline value should change to '8/01/2010 0:00:00'So i made this query:SELECT DATEADD(DAY,DATEDIFF(day, '6/01/2010 0:00:00', getdate()),'6/01/2010 0:00:00')But this makes the day change correctly but the month and year arent correct.Cause the result of the above query is: 2009-12-07 00:00:00.000But it should have been: 2010-01-07 00:00:00.000How can i achieve my goal?Mon, 07 Dec 2009 01:31:27 GMTtprocureurhttp://www.sqlservercentral.com/Forums/Topic1288120-338-1.aspxThis is my current script...----------------------------------------SELECT Distinct "user_view"."fullName" as 'DRNAME', "FOLL_UP"."folder" as 'ACCT', "FOLL_UP"."pname" as 'PTNAME', case when def_type = 1 then 'Example1' when def_type = 2 then 'Example2' when def_type = 3 then 'Example3' end as "DTYPE", "DOCUMENT_NAMES"."TAG" as "DOCUMET NAME", e.pt_type as "PatType", DATEDIFF (day,"FOLL_UP"."AGEDATE", getdate()) as 'DAYS_SINCE', "FOLL_UP"."AGEDATE" as 'DATE'FROM "cab"."dbo"."user_view" "user_view" INNER JOIN "cab"."dbo"."FOLL_UP" "FOLL_UP" ON "user_view"."name"="FOLL_UP"."QUEUE" INNER JOIN "cab"."dbo"."DOCUMENT_NAMES" "DOCUMENT_NAMES" ON "FOLL_UP"."DOCTYPE_ID" = "DOCUMENT_NAMES"."DOCTYPE_ID" and "DOCUMENT_NAMES"."SET_ID" = 3 INNER JOIN DB1..enc e (nolock) on e.enc = "FOLL_UP".folder and e.facility="FOLL_UP".facilityWHERE "FOLL_UP"."FACILITY"='001' AND "FOLL_UP"."AGEDATE"<{ts '2012-03-20 00:00:00'} AND "FOLL_UP"."STATUS_ID"=2ORDER BY 'DRNAME' asc-----------------------------------------Results in..DRNAME, ACCT, PTNAME, DTYPE, DOCNAME, PATTYPE, Days Since, DATEDr1, 1, PTNAME1, DTYPE1, DOCNAME1, PATTYPE1, Days Since = 20, SomeDateDr1, 2, PTNAME2, DTYPE2, DOCNAME2, PATTYPE2, Days Since = 25, SomeDateDr1, 3, PTNAME3, DTYPE3, DOCNAME3, PATTYPE3, Days Since = 10, SomeDateDr2, 1, PTNAME1, DTYPE1, DOCNAME1, PATTYPE1, Days Since = 30, SomeDateDr2, 2, PTNAME2, DTYPE2, DOCNAME2, PATTYPE2, Days Since = 19, SomeDateDr2, 3, PTNAME3, DTYPE3, DOCNAME3, PATTYPE3, Days Since = 11, SomeDateDr3, 1, PTNAME1, DTYPE1, DOCNAME1, PATTYPE1, Days Since = 2, SomeDateDr3, 2, PTNAME2, DTYPE2, DOCNAME2, PATTYPE2, Days Since = 4, SomeDateDr3, 3, PTNAME3, DTYPE3, DOCNAME3, PATTYPE3, Days Since = 5, SomeDate------------------------------------------What I need to be able to get is just the following (Oldest Days Since per DRNAME Group)Dr1...., Days Since = 25Dr2...., Days Since = 30Dr3...., Days Since = 5I have been trying to figure this out using TOP and group by with no luck... I am new to this. Sorry if this is obvious.Thank you!Mon, 23 Apr 2012 06:35:55 GMTVertigo44http://www.sqlservercentral.com/Forums/Topic1298058-338-1.aspxI'm using SQL Server Management Studio 2005. I was experimenting with the idea of using UNPIVOT, but when I went to use it, I get the error:Msg 170, Level 15, State1, Line 18Line 18: Incorrect syntax near 'UNPIVOT'So, obviously it's not a recognized function...but I don't understand why. I have older databases, but the server I'm connected to is SQL Server 8.0.2282. Any ideas?Here's the code:[code="sql"]-- Create Sample Data using a Table VarableCreate Table #Orders (orderid int, GiftCard int, TShirt int, Shipping int)-- Load Sample DataINSERT INTO #Orders VALUES (1, 2, NULL, 1)INSERT INTO #Orders VALUES (2, 2, 2, 2)select * from #orders--Query to Retrieve Desired DataSELECT OrderID, convert(varchar(15), ProductName) [ProductName], ProductQtyFROM(SELECT OrderID, GiftCard, TShirt, Shipping FROM #Orders) pUNPIVOT (ProductQty FOR ProductName IN ([GiftCard], [TShirt], [Shipping])) as unpvt[/code]Thu, 10 May 2012 11:22:41 GMTthemangoagenthttp://www.sqlservercentral.com/Forums/Topic1297682-338-1.aspxHi,I am trying to get data from a web page (stock market site have top gainers/losers table).Created a web query in excel, and imported the excel data to sql table, using DTS packages, and schedules jobs to run every 3 minutes.But If have the Excel running to refresh data very 1 minute, my scheduled jobs fail.But if the excel is closed, the jobs are running fine, but then there will not be any data updates.How can i have an automated job to get the web data to my sql table.Thanks.Thu, 10 May 2012 02:14:07 GMTwrathyimphttp://www.sqlservercentral.com/Forums/Topic1297328-338-1.aspxI know i can add a trace flag to allow me to track and resolve deadlocks however i have a site who is running entity framework and it's doing three separate inserts. One of em is failing due to truncation issues. We've got the front end notifying us when it occurs however i dont get the actual column its failing on. Is there a way i can force all sql errors to the log?Wed, 09 May 2012 12:12:03 GMTSnargableshttp://www.sqlservercentral.com/Forums/Topic1297029-338-1.aspxHi All,WOuld be grateful if you could help out explaing the difference between the following queries and which one is technically better or correct. 1. This SQL updates more rowsUPDATE t SET t.caseno = s.caseno, t.spellno = s.spellno FROM (SELECT s.caseno, s.spellno , s.unitnoFROM dbo.tableA AS t JOIN dbo.tableB AS s ON t.unitno = s.unitno WHERE t.procedure_date BETWEEN CAST(CAST(s.date_of_admission AS varchar(12)) AS datetime) AND CAST(CAST(s.date_of_discharge AS varchar(12)) AS datetime)) s JOIN tableA t ON t.unitno = s.unitno2. This SQL UPDATES less rows:update tset t.caseno = s.caseno,t.spellno = s.spellno from tableA tjoin tableB s ont.unitno = s.unitno where t.procedure_date >= cast(cast(s.date_of_admission as varchar(12)) as datetime) and t.procedure_date <= cast(cast(s.date_of_discharge as varchar(12)) as datetime) I think the last JOIN outside is creating an increase in the rows being updated?Thank youWed, 09 May 2012 05:27:25 GMTaarionsqlhttp://www.sqlservercentral.com/Forums/Topic1296744-338-1.aspxHello - Can someone help me and point in the right direction?? I'm trying to writing some SQL that will set rev_type = '' if the following where clause is met.[quote]select p.project, p.status_pa, p.pm_id05, rev_type, contract_type, p.crtd_datetime, *from pjproj pinner join pjprojex x on x.project = p.projectwhere p.pm_id05 = 'AR'and rev_type = 'PC'[/quote]Regards,DTue, 08 May 2012 14:12:37 GMTdavid.ostranderhttp://www.sqlservercentral.com/Forums/Topic1295464-338-1.aspxHi all,Just a quick question which I'm fairly sure I already know the answer to.Let's say I have a table which stores a clientID and the purchases the client made, as well as a table that stored the clientID and his different accounts.Also, let's say the client had multiple purchases and multiple accounts:[code]CREATE TABLE #ClientPurchases( ID INT IDENTITY PRIMARY KEY, ClientID INT, Purchase DECIMAL(18, 2))CREATE TABLE #ClientAccounts( ID INT IDENTITY PRIMARY KEY, ClientID INT, AccountNumber BIGINT)INSERT INTO #ClientPurchases (ClientID, Purchase)VALUES (1, 10)INSERT INTO #ClientPurchases (ClientID, Purchase)VALUES (1, 20)INSERT INTO #ClientPurchases (ClientID, Purchase)VALUES (1, 50)INSERT INTO #ClientPurchases (ClientID, Purchase)VALUES (1, 100)INSERT INTO #ClientPurchases (ClientID, Purchase)VALUES (2, 100)INSERT INTO #ClientPurchases (ClientID, Purchase)VALUES (2, 500)INSERT INTO #ClientPurchases (ClientID, Purchase)VALUES (2, 400)INSERT INTO #ClientPurchases (ClientID, Purchase)VALUES (2, 400)INSERT INTO #ClientAccounts (ClientID, AccountNumber)VALUES (1, 1234567)INSERT INTO #ClientAccounts (ClientID, AccountNumber)VALUES (1, 1512512)INSERT INTO #ClientAccounts (ClientID, AccountNumber)VALUES (1, 2362632)INSERT INTO #ClientAccounts (ClientID, AccountNumber)VALUES (1, 412421)INSERT INTO #ClientAccounts (ClientID, AccountNumber)VALUES (2, 235236262)INSERT INTO #ClientAccounts (ClientID, AccountNumber)VALUES (2, 141241)INSERT INTO #ClientAccounts (ClientID, AccountNumber)VALUES (2, 124124)[/code]Now let's say I want to get a count of the total number of accounts, purchases, and the total value of the purchases. Normally I'd write a request like this:[code]SELECT #ClientPurchases.ClientID, COUNT(DISTINCT AccountNumber) AS NumAccounts, COUNT(DISTINCT #ClientPurchases.ID) AS NumPurchases, SUM(DISTINCT Purchase) AS AmtPurchasesFROM #ClientPurchasesJOIN #ClientAccounts ON #ClientPurchases.ClientID = #ClientAccounts.ClientIDGROUP BY #ClientPurchases.ClientID[/code]This works for the number of accounts and purchases, but clearly doesn't work for the value of the purchases, because the SUM(DISTINCT Purchase) part is looking at a distinct value of purchases, and not a distinct purchase record.Obviously there's ways I can get this to work, such as for example splitting the request into two:[code]WITH CountAccounts AS( SELECT ClientID, COUNT(*) AS NumAccounts FROM #ClientAccounts GROUP BY ClientID),ClientPurchases AS( SELECT ClientID, COUNT(*) AS NumPurchases, SUM(Purchase) AS AmtPurchases FROM #ClientPurchases GROUP BY ClientID)SELECT ISNULL(CountAccounts.ClientID, ClientPurchases.ClientID) AS ClientID, ISNULL(NumAccounts, 0) AS NumAccounts, ISNULL(NumPurchases, 0) AS NumPurchases, ISNULL(AmtPurchases, 0) AS AmtPurchasesFROM CountAccountsFULL JOIN ClientPurchases ON CountAccounts.ClientID = ClientPurchases.ClientID[/code]My question is just whether there is a simpler way of doing this that I'm forgetting about, since my real query is considerably bigger and I'd rather not take the time to figure out how to split it up. Basically is there a simple way I can get a SUM of distinct values, but where the "distinct-itude" is not decided by the value, but rather by a different key.Fri, 04 May 2012 13:29:08 GMTkramaswamyhttp://www.sqlservercentral.com/Forums/Topic936587-338-1.aspxHello friends,I use this query to insert data into a table in remote server using a linked server and the source table is in my local server.Here is the query:insert openquery(MY_linkedserver,'select name, num, email, ssn' from remote_table)select name,num,email,ssn from local_table.This query used to run perfect previously.But, this time it gave me an error.ERROR:OLE DB provider "MSDASQL" for linked server "MY_linkedserver" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Msg 7343, Level 16, State 2, Line 1The OLE DB provider "MSDASQL" for linked server "MY_linkedserver" could not INSERT INTO table "[MSDASQL]". Can some one help me in decoding this error.I have no clue of where to check it out.Is this the problem with the query(I dont think because, it worked gud during my last executions)or do you think some has changed in destination table properties.Sun, 13 Jun 2010 19:58:53 GMTstriker-babahttp://www.sqlservercentral.com/Forums/Topic1296529-338-1.aspxDear,I'm struggling with a recursive query.The bill of material of our products is stored in a table 'REQUIREMENTS'.In this table you have the column 'WORKORDER_BASE_ID' which is the assembly andyou have the column 'PART_ID' which are the parts that are used for the assembly.e.g. WORKORDER_BASE_ID PART_ID Assembly 1 P1 Assembly 1 P2So Assembly 1 exists of P1 and P2Now is it possible that P1 from the example is also a assembly of his own.e.g. WORKORDER_BASE_ID PART_ID Assembly 1 P1 Assembly 1 P2 P1 P3 P1 P4The outcome that I would like to have is a recordset of all the parts with their highest assemblyso for the example : P1 => Assembly 1 but P3 => Assembly 1 (via P1)So a typical example of a recursive query but I can't work out the code :(sideremark : the reason for WORKORDER_TYPE = 'M' => not only the default BOM is stored in that table but also the produced work orders and their materials are stored there so I have to filter the defaults BOM by filtering on type 'M')WITH MaxMaster as(SELECT DISTINCT REQUIREMENT.WORKORDER_BASE_ID as PART, REQUIREMENT_1.WORKORDER_BASE_ID as MASTERS FROM REQUIREMENT LEFT JOIN REQUIREMENT AS REQUIREMENT_1 ON (REQUIREMENT.WORKORDER_TYPE = REQUIREMENT_1.WORKORDER_TYPE) AND (REQUIREMENT.WORKORDER_BASE_ID = REQUIREMENT_1.PART_ID) WHERE (((REQUIREMENT.WORKORDER_TYPE)='M') AND ((REQUIREMENT.PART_ID) Is Not Null) AND ((REQUIREMENT_1.WORKORDER_BASE_ID) Is Null)) UNION ALLSELECT PART_ID AS Part, WORKORDER_BASE_ID AS Masters FROM REQUIREMENT INNER JOIN MaxMaster ON (REQUIREMENT.WORKORDER_TYPE='M' AND REQUIREMENT.PART_ID Is Not Null AND WORKORDER_BASE_ID = MaxMaster.Part)) Select * from MaxMasterTue, 08 May 2012 08:54:41 GMTjvdb 65877http://www.sqlservercentral.com/Forums/Topic1296168-338-1.aspxI have a CSV file that contains about 2,000 customer accounts. Unfortunately, the Customer IDs do not start from 1 and are not in order. As an example the Customers IDs are in this order after sorting (2, 3, 12, 19, 23, etc.)Is it possible to write some SQL statement to fill the gap not necessarily updating the table! I just want to be able to export the data to Excel so I can further work on the data.[b]Customer Table Structure:[/b][code="sql"]USE [implantdirect]GO/****** Object: Table [dbo].[Customers] Script Date: 05/07/2012 16:42:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Customers]( [CustomerID] [int] IDENTITY(1,1) NOT NULL, [AccessKey] [varchar](1) NULL, [Password] [varchar](64) NULL, [FirstName] [varchar](30) NULL, [LastName] [varchar](40) NULL, [CompanyName] [varchar](100) NULL, [BillingAddress1] [varchar](75) NULL, [BillingAddress2] [varchar](75) NULL, [City] [varchar](45) NULL, [State] [varchar](30) NULL, [PostalCode] [varchar](15) NULL, [Country] [varchar](30) NULL, [PhoneNumber] [varchar](35) NULL, [FaxNumber] [varchar](30) NULL, [EmailAddress] [varchar](75) NULL, [PaysStateTax] [varchar](3) NULL, [TaxID] [varchar](64) NULL, [EmailSubscriber] [varchar](1) NULL, [CatalogSubscriber] [varchar](1) NULL, [LastLogin] [smalldatetime] NULL, [LastModified] [smalldatetime] NULL, [PercentDiscount] [float] NULL, [WebsiteAddress] [varchar](150) NULL, [DiscountLevel] [varchar](1) NULL, [FirstDateVisited] [smalldatetime] NULL, [FirstOrderDate] [smalldatetime] NULL, [CustomerType] [varchar](1) NULL, [LoginAttempts] [int] NULL, [LastLoginAttempt] [smalldatetime] NULL, [LastModBy] [int] NULL, [Customer_IsAnonymous] [varchar](1) NULL, [IsSuperAdmin] [varchar](1) NULL, [news1] [varchar](1) NULL, [news2] [varchar](1) NULL, [news3] [varchar](1) NULL, [news4] [varchar](1) NULL, [news5] [varchar](1) NULL, [news6] [varchar](1) NULL, [news7] [varchar](1) NULL, [news8] [varchar](1) NULL, [news9] [varchar](1) NULL, [news10] [varchar](1) NULL, [news11] [varchar](1) NULL, [news12] [varchar](1) NULL, [news13] [varchar](1) NULL, [news14] [varchar](1) NULL, [news15] [varchar](1) NULL, [news16] [varchar](1) NULL, [news17] [varchar](1) NULL, [news18] [varchar](1) NULL, [news19] [varchar](1) NULL, [news20] [varchar](1) NULL, [Allow_Access_To_Private_Sections] [varchar](1) NULL, [Checkbox_For_New_Customers] [varchar](1) NULL, [Customer_Notes] [text] NULL, [SalesRep_CustomerID] [int] NULL, [ID_Customers_Groups] [int] NULL, [Custom_Field_Custom1] [varchar](50) NULL, [Custom_Field_Custom2] [varchar](50) NULL, [Custom_Field_Custom3] [varchar](50) NULL, [Custom_Field_Custom4] [varchar](50) NULL, [Custom_Field_Custom5] [varchar](50) NULL, [Custom_Field_Custom7] [varchar](50) NULL, [Custom_Field_Experience] [varchar](50) NULL, [Custom_Field_test] [varchar](10) NULL, [Custom_Field_License] [varchar](30) NULL, [Custom_Field_Custom8] [varchar](255) NULL, [Custom_Field_Custom9] [varchar](255) NULL, [Custom_Field_Custom10] [varchar](255) NULL, [Custom_Field_ACK] [varchar](50) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [CustomerID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGO[/code]Mon, 07 May 2012 17:50:20 GMTfawadafrhttp://www.sqlservercentral.com/Forums/Topic1214273-338-1.aspxI need to JOIN information from the following Linked Server Table to a Table named Mytable in a SQL Server Database named MyDatabase in the dbo Schema.I don't remember the syntax.[code="sql"]SELECT *FROM OPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00')[/code]Any help would be greatly appreciatedWed, 30 Nov 2011 15:13:24 GMTWelsh Corgihttp://www.sqlservercentral.com/Forums/Topic1295406-338-1.aspxThis has got to be easy.. I just can not seem to find how to do .. used Google.Looking to take a date field (2012-02-16) and converting for two new columns on my SQL statemet. One that will show Year/Month (2012-02) and a second one that will show the Quarter (2012-1).Fri, 04 May 2012 11:07:39 GMTdwilliscphttp://www.sqlservercentral.com/Forums/Topic1295413-338-1.aspxDoes anyone know where MS is storing the Job Category Name? So I could link to the table and convert the category_id to its name?SELECT [job_id] ,[category_id]FROM [msdb].[dbo].[sysjobs]Fri, 04 May 2012 11:55:00 GMTdwilliscp