InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 01:03:52 GMT20http://www.sqlservercentral.com/Forums/Topic815495-338-1.aspxHello all. First off, im not sure if this is the forum i should be posting this on. If it's not im sorry and please direct me to the correct one.The scenario im in right now got me puzzled as to what to do (and boy, i like being puzzled).Ok, so i have 5 .csv files to import, all separated by TAB (\t) with different amount of columns.The application we have in the company import the files into a single table with the amount of columns i specify in the application, but the problem is: i need to specify the maximum amount of columns(22) but instead of inserting nulls for the columns missing on the files that have lesser columns the app just keeps inserting and filling those columns, which completly ruins queries i need to run later on.Now, i can tell the application to import everything into a single varchar column.what i did then was to add a new column to the table to know which file is which worked well if a cursor.i then added a CHAR(9)(tab) before and after each line so i could separate every field with a tally tableso now i got a 1 column table with everything i need separated by file type, but i need to pivot it back into actual columns so i can create 5 temp tables to work with.im not sure this is the correct approach, perhaps something simpler can be done but at this moment i cant think of any and would love some ideasThiago DantasSat, 07 Nov 2009 17:16:56 GMTdant12http://www.sqlservercentral.com/Forums/Topic815278-338-1.aspxI would like to backup certain databases using jobs and then copy them back to a server server001. This involves T-SQL and I have accomplished the first part for backing up, but the second one (I am doing manually) which has DOS command like copy / move as key player acts like road block. Any suggestions ?:angry:Fri, 06 Nov 2009 19:46:10 GMTFrancis Yee-483501http://www.sqlservercentral.com/Forums/Topic812673-338-1.aspxI have a code where I execute a transaction and I need to log an error if it occurs. I execute my transaction in TRY block, and catch an error in CATCH block.Here is a sample of my code:First, I create a table to insert some values in a transaction:if object_id('t','u') is not nulldrop table tgocreate table t (c1 int)go-- Next, I create an error log table:if object_id('error_log', 'u') is not nulldrop table error_loggocreate table error_log(err_num int,err_message varchar(100))go--And here is my code where I try to log my error into error_log table:declare @error_number int,@error_message varchar(100)set xact_abort onbegin try begin transaction -- here I intentionally enter number that's larger than int: insert into t values (67896789867); insert into t values (5); if xact_state() = 1 begin commit transaction endend trybegin catch select @error_number = error_number(), @error_message = error_message() print @error_number print @error_message insert into error_log select @error_number, @error_message rollback transactionend catchUpon execution of this batch I get an error: "Msg 3930, Level 16, State 1, Line 21The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."If I comment lines which insert into error_log table, I don't receive an error. But the problem is that I DO need to log errors.Any suggestions please ?Mon, 02 Nov 2009 17:27:36 GMTSQL Guy-482125http://www.sqlservercentral.com/Forums/Topic814628-338-1.aspxI need to create a table where number of records will be restricted only to one record. How it is possible to do with check constraint or something ?Thanks.Thu, 05 Nov 2009 17:30:33 GMTSQL Guy-482125http://www.sqlservercentral.com/Forums/Topic815472-338-1.aspxHey Guys I'm back with another date question. As always the dates I deal with are stored as varchar(8); yyyymmdd.Here is the problem. I have to pull all records from the Mstr with a DOB > 71 yrs. Here is what I came up with (but I'm running into conversion problems).[code="plain"]SELECT PROD, OFFR, Cast(DOB as Bigint) as DOBINTO #TmpFROM D_Mstr LEFT JOIN NAM ON Mprod = [NProd]WHERE PROD IN ('000100', '000200', '000300')AND DateDiff(m, Convert(Varchar(8),DOB ,112), getdate())<=861[/code]Thanks so much,TrudyeSat, 07 Nov 2009 13:16:52 GMTtrudye10http://www.sqlservercentral.com/Forums/Topic815335-338-1.aspxTable : EmployeeEmployeeId Name MemoStatus1 a N 2 b N3 c Y4 d N5 e y6 f N Table MemoDetail :EmpoyeeID iSSuedBy Date 3 Edwin 2009-01-05 3 Elsa 2009-02-10 5 Rocky 2009-03-03 5 bela 2009-02-01i have to update Employee Memostatus to 'N' based on records not exist on Memodetail table.Suppose employee has at least one memo record in memeoDetail table we should not update 'N' , it should be remain 'Y'how to achive this using a update command ?Fri, 06 Nov 2009 23:48:00 GMTvarshinihttp://www.sqlservercentral.com/Forums/Topic815295-338-1.aspxHi,I want to combine the output of multiple stored procedures in a single result set.The output columns of all the stored procedures will be same.Ex:EXEC sp_getClientsListUNION ALLEXEC sp_getClientsListFri, 06 Nov 2009 21:15:55 GMTdeepakknhttp://www.sqlservercentral.com/Forums/Topic815391-338-1.aspxHi friendsI had a quiet strange behavior with joining a table valued function last week.Business case:We've got a distributed system. To keep data in sync we use export and import processes. We have to import files with several thousand rows (using XML). After parsing the file we have to update existing data and add new rows. (I must not use any technology like BCP, SSIS or SS2k8 MERGE.)To avoid thousands of single selects I used a inline table-valued function which gets all concatenated keys. Internally, this function calls a T-SQL split function (like Jeff Moden's function). I concatenate all keys within the client process, send the keys to the server and JOIN the split function to the data table.My first split function used a inline Numbers table created with CROSS JOINS:[code]---========================================================-- create a Jeff Moden style string split function-- using a inline Numbers tableIF (OBJECT_ID('SplitStringInlineNumbers') IS NULL) EXECUTE ('CREATE FUNCTION SplitStringInlineNumbers () RETURNS TABLE AS RETURN SELECT 1 A');GOALTER FUNCTION SplitStringInlineNumbers ( @text VARCHAR(MAX) ,@separator CHAR(1))RETURNS TABLEASRETURN WITH n1 (Num) AS (SELECT 1 UNION ALL SELECT 1), -- 2 n2 (Num) AS (SELECT 1 FROM n1 CROSS JOIN n1 b), -- 4 n3 (Num) AS (SELECT 1 FROM n2 CROSS JOIN n2 b), -- 16 n4 (Num) AS (SELECT 1 FROM n3 CROSS JOIN n3 b), -- 256 n5 (Num) AS (SELECT 1 FROM n4 CROSS JOIN n4 b), -- 65536 -- 4294967296 Numbers (Num) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM n5 CROSS JOIN n5 b ) SELECT SUBSTRING( @text ,t.Num + 1 ,CHARINDEX(@separator, @text, t.Num + 1) - t.Num - 1 ) AS Item FROM Numbers t WHERE t.Num < LEN(@text) AND SUBSTRING(@text, t.Num, 1) = @separatorGO[/code]Everything worked fine, though, as I looked into the ERRORLOG I noticed that SQL Server has no clue about SQL :-D. There have been 32 "Missing Join Predicate" warnings to tell me that my cool function appears to be scrap whenever SQL Server created a execution plan for this function.Next thought was, no problem use a database Numbers table. (Bad idea...). I slightly changed the function and removed the inline Numbers table:[code]---========================================================-- create a Jeff Moden style string split function-- using a database Numbers tableIF (OBJECT_ID('SplitStringDbNumbers') IS NULL) EXECUTE ('CREATE FUNCTION SplitStringDbNumbers () RETURNS TABLE AS RETURN SELECT 1 A');GOALTER FUNCTION SplitStringDbNumbers ( @text VARCHAR(MAX) ,@separator CHAR(1))RETURNS TABLEASRETURN SELECT SUBSTRING( @text ,t.Num + 1 ,CHARINDEX(@separator, @text, t.Num + 1) - t.Num - 1 ) AS Item FROM Numbers t WHERE t.Num < LEN(@text) AND SUBSTRING(@text, t.Num, 1) = @separator[/code]Warnings gone, [b]but[/b] ends up with a completely strange execution plan using a huge index spool and table spool. The old (potentially bad warned) function took 110ms for thousand keys, the new function took about 85,000ms!?!I noticed both functions are marked as non-deterministic (why?), so I changed my previously used TVFs to a procedure which executes the split function into a table variable and joins this table to the data. Finally everything works fine.Anyway, why are both functions marked as non-deterministic? Why does the inline-numbers table work 850 times faster than the database-numbers table?In respect to help you to help me, here is a sample environment which can be used as copy-paste. (Also copy the previous two functions into tempdb.)[b]Setup tables and data on tempdb[/b][code]SET NOCOUNT ON;USE tempdb;GO---========================================================-- drop existing test tablesIF (OBJECT_ID('TestReferences') IS NOT NULL) DROP TABLE TestReferences;IF (OBJECT_ID('TestData') IS NOT NULL) DROP TABLE TestData;IF (OBJECT_ID('Numbers') IS NOT NULL) DROP TABLE Numbers;GO---========================================================-- create test tables-- we need a numbers tableCREATE TABLE Numbers( Num INT NOT NULL PRIMARY KEY CLUSTERED WITH (FILLFACTOR = 100));-- any sample dataCREATE TABLE TestData( Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED ,SomeInt INT);-- several references which relate to the test dataCREATE TABLE TestReferences( Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED ,DataId INT NOT NULL REFERENCES TestData (Id) ,Reference VARCHAR(36));-- reference column is indexedCREATE INDEX IX_TestReferences_Reference ON TestReferences (Reference);GO---========================================================-- data-- create a numbers table with 50,000 rowsINSERT INTO Numbers SELECT TOP(50000) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2;-- create 10,000 data rowsINSERT INTO TestData SELECT TOP(10000) Num FROM Numbers-- create two references for each data rowINSERT INTO TestReferences SELECT d.Id ,CONVERT(VARCHAR(36), NEWID()) FROM TestData d CROSS JOIN (SELECT 1 A UNION ALL SELECT 2) blahGO[/code][b]Call the functions[/b][code]SET NOCOUNT ON;USE tempdb;GO--DBCC FREEPROCCACHE;---==================================================================-- call the inline-numbers function joined to the data table-- get some references from TestReferences table to searchDECLARE @keys VARCHAR(MAX);SELECT @keys = CHAR(9) + (SELECT TOP(1000) Reference + CHAR(9) FROM TestReferences FOR XML PATH(''));--SET STATISTICS XML ON;--SET STATISTICS TIME ON;-- select all TestData related to the specified keysSELECT DISTINCT d.* FROM SplitStringInlineNumbers(@keys, CHAR(9)) keys JOIN TestReferences r ON keys.Item = r.Reference JOIN TestData d ON r.DataId = d.Id;--SET STATISTICS TIME OFF;--SET STATISTICS XML OFF;GO---==================================================================-- call the database-numbers function joined to the data table-- get some references from TestReferences table to searchDECLARE @keys VARCHAR(MAX);SELECT @keys = CHAR(9) + (SELECT TOP(1000) Reference + CHAR(9) FROM TestReferences FOR XML PATH(''));--SET STATISTICS XML ON;--SET STATISTICS TIME ON;-- select all TestData related to the specified keysSELECT DISTINCT d.* FROM SplitStringDbNumbers(@keys, CHAR(9)) keys JOIN TestReferences r ON keys.Item = r.Reference JOIN TestData d ON r.DataId = d.Id;--SET STATISTICS TIME OFF;--SET STATISTICS XML OFF;GO---==================================================================-- call the database-numbers function into a table variable and -- join this to the data table-- get some references from TestReferences table to searchDECLARE @keys VARCHAR(MAX);SELECT @keys = CHAR(9) + (SELECT TOP(1000) Reference + CHAR(9) FROM TestReferences FOR XML PATH(''));-- copy result of split function into a table variableDECLARE @lookup TABLE (Item VARCHAR(36) PRIMARY KEY CLUSTERED);INSERT INTO @lookup SELECT Item FROM SplitStringDbNumbers(@keys, CHAR(9));-- select all data by joining the lookup tableSELECT DISTINCT d.* FROM @lookup keys JOIN TestReferences r ON keys.Item = r.Reference JOIN TestData d ON r.DataId = d.Id;[/code]First part calls the inline-numbers split function joined to the data table.Second part calls the database-numbers split function joined to the data table. [b]BAD PART![/b]Third part calls the database-numbers split function into a table-variable and joins it. (Performs good).(I cannot attach the execution plans, since I get an error while upload)I'd be glad about any suggestions about the WHY?ThanksFlo[i]PS to the regulars: Splitting will never end :hehe:[/i]Sat, 07 Nov 2009 05:40:02 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic815230-338-1.aspxIs it possible to have a GROUP BY CLAUSE on only some selected fields?For example:SELECT col1, col2, col3FROM TableAGROUP BY col1Fri, 06 Nov 2009 15:54:31 GMTramadesai108http://www.sqlservercentral.com/Forums/Topic809640-338-1.aspxI've got a table that is populated from the OUTPUT from an update statement:[code="sql"]declare @Update TABLE ( RowID INT, Column1_old DECIMAL(2,0), Column1_new DECIMAL(2,0), Column2_old DATETIME, Column2_new DATETIME, Column3_old BIT, Column3_new BIT)insert into @Updateselect 1, 25, 52, '20090101', '20091001', 1, 1 UNIONselect 2, 82, 56, '20090101', '20090101', 1, 0 UNIONselect 3, 25, 25, '20091001', '20091001', 1, 1-- what the incoming data looks likeselect * from @Update[/code]I'd like to unpivot the different columns so that it looks like this:[code="sql"]-- desired results:declare @Updates TABLE (RowID INT, Column_nm varchar(30), old_value varchar(30), new_value varchar(30))insert into @Updatesselect 1, 'Column1', 25, 52 UNIONselect 1, 'Column2', '20090101','20091001' UNIONselect 1, 'Column3', 1, 1 UNIONselect 2, 'Column1', 82, 56 UNIONselect 2, 'Column2', '20090101','20090101' UNIONselect 2, 'Column3', 1, 0 UNIONselect 3, 'Column1', 25, 25 UNIONselect 3, 'Column2', '20090101','20091001' UNIONselect 3, 'Column3', 1, 1 select * from @Updates order by RowID, Column_nm[/code]The only solution that I've been able to come up with uses a select for each pair of columns, all unioned together:[code="sql"]declare @Updates2 TABLE (RowID INT, Column_nm varchar(30), old_value varchar(30), new_value varchar(30))insert into @Updates2select RowID, 'Column1', convert(varchar(30),column1_old), convert(varchar(30), column1_new) from @Update UNIONselect RowID, 'Column2', convert(char(8), column2_old, 112), convert(char(8), column2_new, 112) from @Update UNIONselect RowID, 'Column3', convert(char(1),column3_old), convert(char(1),column3_new) from @Update select * from @Updates2 order by RowID, Column_nm[/code]But, I'd like to avoid the repetitive hits against the table (especially when the actual output is about 30 pairs of columns).Do you have any idea of if this can be accomplished, and if so, how?Thanks!!!Tue, 27 Oct 2009 15:58:37 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic814024-338-1.aspxCan any one explain me what this means :-)Sum(ISNULL(GSTExclusiveAmt,0))Regards,PrathyushaWed, 04 Nov 2009 23:18:06 GMTprathyushaahttp://www.sqlservercentral.com/Forums/Topic814691-338-1.aspxFollowing is the code in which we are getting the error.Step 1: Created Linked Server EXEC sp_addlinkedserver 'Reddevil','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=190.12.34.23;UID=sa;PWD=sa;'Step 2:DECLARE @ip VARCHAR(25)SET @ip = '190.12.34.23'EXEC sp_setnetname 'RedDevil',@ip SELECT * FROM RedDevil.DatabaseName.dbo.clientsERROR:You cannot set network name on server 'RedDevil' because it is not a linked SQL Server.Thu, 05 Nov 2009 22:00:52 GMTdeepakknhttp://www.sqlservercentral.com/Forums/Topic814177-338-1.aspxHiI am trying to import a CSVs file using OPENROWSET and import this into a databasemy problem is the CSV files may contain a varying number of columnsi have tried using SELECT BulkColumn FROM OPENROWSET (BULK '\\10.1.2.107\rp_uploaded_files\file.csv', SINGLE_CLOB) MyFile but this puts all the values into one fieldsi have also triedset @string ='BULK INSERT CSVTest FROM ''\\10.1.2.107\rp_uploaded_files\'+@file+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = '''')'exec (@string)but this requires the destintation table to have the same number of columns as the import fileCan anyone advisethanksSimonThu, 05 Nov 2009 06:05:15 GMTSimon Parryhttp://www.sqlservercentral.com/Forums/Topic814625-338-1.aspxI have 3 tables:test1id name1 1 value1test2id name2 1 value2test3id name3 3 value3_01 4 value3_02I want to display all records of table test1 with values of table test2 (test1.id = test2.id) and values of test3 (if test.id match with test1.id) - in this case test3 has no common ids with test1 so NULLs are displayed.How to make sql query to display:id1 name1 name2 name3 1 value1 value2 NULLI constructed query:SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)ON (t1.id=t2.id and t1.id=t3.id)but it gives me:id1 name1 name2 name3 1 value1 NULL NULLname2 is NULL instead of desired "value2". WHY?LEFT JOIN DEFINITION:SQL LEFT JOIN KeywordThe LEFT JOIN keyword returns all rows from the left table, even if there are no matches in the right table.There are no matches in test3 table when joining test1 table so name3 from table3 is NULL in result.BUT There are matches in table test2 (test2.id=1 and test1.id=1) so why null is displayed?Hope anyone can help me. I am in big trouble. Thanks in advace.TomThu, 05 Nov 2009 17:04:24 GMTpc131http://www.sqlservercentral.com/Forums/Topic814479-338-1.aspxWe are exporting data from SQL Server using EXEC xp_cmdshell bcp command. Question is if anyone know if you can append to the same file if the file exist out there instead of over writing. Or do you have a better method doing this process. The text will not always be out there because it will processed by other system and remove the the folder. Only time the file will stay if the other system does not process it. This process of exporting the data will be scheduled on SQL Server.Thanks for your help and input in this matter. :-)Thu, 05 Nov 2009 12:02:05 GMTBridget Elise Nelsonhttp://www.sqlservercentral.com/Forums/Topic814785-338-1.aspxI am trying to populate a temp table from the sysjobhistory of various servers based on a parameter in a procedure. I thought it would be fairly straightforward:[code="sql"]DECLARE @server sysnameSET @server = 'bovril'IF (@server = 'bovril') BEGIN SELECT * INTO #hist FROM msdb.dbo.sysjobhistoryENDELSE IF (@server = 'lowenbrau') BEGIN SELECT * INTO #hist FROM lowenbrau.msdb.dbo.sysjobhistoryENDELSE IF (@server = 'sfqcnt2') BEGIN SELECT * INTO #hist FROM sfqcnt2.msdb.dbo.sysjobhistoryEND[/code]Unfortunately I get:Msg 2714, Level 16, State 1, Line 12There is already an object named '#hist' in the database.Msg 2714, Level 16, State 1, Line 17There is already an object named '#hist' in the database.It seems to be trying to run, the code even when the condition is not met. I have an easy enough solution:[code="sql"]SELECT *INTO #histFROM msdb.dbo.sysjobhistoryWHERE 1=2SET INDENTITY_INSERT #hist ONIF (@server = 'bovril') BEGIN INSERT INTO #hist(.....) SELECT * FROM msdb.dbo.sysjobhistoryENDELSE IF.....[/code]I remain exceedingly puzzled as to the behaviour however, and was hoping someone could explain to further my understanding.Fri, 06 Nov 2009 03:21:54 GMTRob Fiskhttp://www.sqlservercentral.com/Forums/Topic814545-338-1.aspxI want to use a script or stored procedure to capture running totals per employee.Basically Col 1 is the employee number, column 2 is the hours, column 3 is the total for that day per employee.See attachment.Thanks for any help or pointers.Thu, 05 Nov 2009 13:43:50 GMTwizard5203http://www.sqlservercentral.com/Forums/Topic815018-338-1.aspxHi All, I have a need to have a case statement in the where clause as follows:WHERE (CASE WHEN EndDate < CURRENT_TIMESTAMP THEN (StartDate > CURRENT_TIMESTAMP)ELSE (EndDate >= CURRENT_TIMESTAMP ANDStartDate <= CURRENT_TIMESTAMP) END)But it complains on the ">" in the second line with "Incorrect syntax near '>' "How do I do this?Thanks.Fri, 06 Nov 2009 09:36:48 GMTramadesai108http://www.sqlservercentral.com/Forums/Topic814930-338-1.aspxI had a table (tblStop) containing a list of serial numbers, which were a subset of records in another table (tblCard) and was asked to set a flag and date on records in tblCard where the serial number existed in tblStop. There were 150 records in tblStop, and 65,000 records in tblCardI created this update query:[code]UPDATE tblCardSET Stopped = 1, StoppedDate = Getdate()WHERE tblCard.SerialNumber IN (Select SerialNumber from tblStop)[/code]When I ran the query, it reported [b]65,000[/b] records updated!I then realised that the column in tblStop was called [b]CardSerialNumber[/b], not [b]SerialNumber[/b]I would have expected an error to be generated, as it would if I just ran [b]Select SerialNumber FROM tblStop[/b], but no error, and every single record in the target table was updated! The first words out of my mouth were Oh 5h1t! Luckily I was able to set the records back to their previous status using a backup, but I was shocked when it happened.Can anyone explain why it happened?Fri, 06 Nov 2009 07:48:38 GMTChris Quinn-821458http://www.sqlservercentral.com/Forums/Topic814990-338-1.aspxI have a query that runs fine, but I need to get the matching loan number from another DB.select zone, data_date, loan, altloannumber, oldloannumber from loandatawhere data_date > '2009-11-06'order by loanI need to get the matching loan # (called borrowerid) from a different DB on the same server called LPB_ReportingServices. Not sure of the syntax..Fri, 06 Nov 2009 09:13:54 GMTkrypto69http://www.sqlservercentral.com/Forums/Topic814999-338-1.aspxHi, I need to write a trigger to write to a specific environment variable in a table. How can I do without using xp_cmdshell ?ThankFri, 06 Nov 2009 09:21:55 GMTgdraghettihttp://www.sqlservercentral.com/Forums/Topic814597-338-1.aspxHere's the situation simplified:I have two tables: a reference table and a mapping table. The mapping table is populated with the PK from the reference table plus another IDReference TableKeyId, Code1, A2, B3, CMapping TableKeyId, MappingId1, 102, 101, 253, 25The query I'm trying to build has to return mapped Key Ids and any missing Key Ids for EACH mapping. A simple LEFT JOIN/IS NULL query will not work since all KeyIds are in the mapping table (for the example), but not necessarily used in every mapping.Example Final Resultset:MappingId, KeyId, IsMapped10, 1, Yes10, 2, Yes10, 3, No25, 1, Yes25, 2, No25, 3, YesI've been trying UNIONS, CTEs and such, but I'm still baffled. This should not be that uncommon. Any help in kick starting this would be extremely helpful.Thanks,SteveThu, 05 Nov 2009 15:16:05 GMTSteve Kumbskyhttp://www.sqlservercentral.com/Forums/Topic814968-338-1.aspxI would like to create an app that would compare procs in a VSS environment with actual production procs and check for differences. Has anyone found a 3rd party tool that does this or know a way (in .net I presume) to build this? I would like to run it every day and make sure all is in sync. Not everyone has the habit of using VSS.Fri, 06 Nov 2009 08:48:51 GMTstevoid1970http://www.sqlservercentral.com/Forums/Topic814805-338-1.aspxDear friends; Pls help this I run the qry in sqlserver 2005select max(smd.Yearpassed) [ExamSession],max(SMD.ASESSIONCODE),max(SMD.COURSEID),SM.REGISTERNO,max(SMD.GRADE),max(Cmas.CID),max(CMas.CNAME),max(SM.STUDNAME) [NAME],DM.DEGNAME + SPACE(1) +BM.Branchname [PROGRAMME NAME],max(SMD.ASESSIONCODE),dbo.GetStudentAttempt(SM.RegisterNo,SMD.CourseId,Yearpassed) as Attempt,MAX(gpc.sgpa),MAX(gpc.cgpa),MAX(gac.Gacid),MAX(cm.coursetype) from Studentmarkdetails smdinner join Studentmaster sm on sm.registerno=smd.registerno and sm.statusflag not in ('D')inner join TempgacidGeneration gac on gac.registerno=smd.registernoinner join coursemaster cm on cm.courseid=smd.courseidinner join collegemaster cmas on cmas.cid=substring(smd.registerno,7,3)inner join degreemaster dm on dm.degcode=substring(smd.registerno,3,2)inner join Branchmaster bm on bm.Bcode=substring(smd.registerno,3,4)inner join gradepointcalculation gpc on gpc.registerno=smd.registernowhere smd.appstatus in('A','E') and smd.publishflag='Y'and smd.registerno='078002100023' and gac.gacid='A00201A09D59'-- and smd.Examscheduleid='19N07A'group by smd.Yearpassed,SMD.ASESSIONCODE,SM.REGISTERNO,SMD.GRADE,Cmas.CID,CMas.CNAME,SM.STUDNAME ,DM.DEGNAME + SPACE(1) +BM.Branchname,SMD.ASESSIONCODE,dbo.GetStudentAttempt(SM.RegisterNo,SMD.CourseId,Yearpassed),gpc.sgpa,gpc.cgpa,gac.Gacid,cm.coursetypeTo run the qry to throw the following errorMsg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.How to solve the problem ;I trace out the error is The two Duplication column of Yearofpassed in Two times LikeYear of Passed Jan -09Jan -09How can i Sove the problem;Thanks & Regards;A.FaijurrahumanFri, 06 Nov 2009 03:56:38 GMTfaijurrahuman17http://www.sqlservercentral.com/Forums/Topic814718-338-1.aspxHi i created a test proc, i am getting error while executingALTER PROC TESTPROC(@VAR NVARCHAR(50) OUTPUT)ASBEGINSELECT @VAR= 'test'END-- execute TESTPROC @VAR outputerror: Msg 137, Level 15, State 2, Line 1Must declare the scalar variable "@VAR".can any one guide me on this oneThanks Rock..Thu, 05 Nov 2009 23:47:04 GMTrockingadminhttp://www.sqlservercentral.com/Forums/Topic814768-338-1.aspxHello comunity I need help on this script, because i need to join 2 derived table. My script is :select ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5) as 'DocTesouraria', SUM(valowbincid.Bincid) as 'BincidDocTesouraria', SUM(valorIVA.IVAdoctes)FROM(SELECT SUM(evalor) AS 'Bincid' , owstamp FROM owli where (oldesc like '68%' or oldesc like '129%') GROUP BY owstamp) valowbincid INNER JOIN ow ON valowbincid.owstamp = ow.owstamp INNER JOIN (SELECT SUM(evalor) AS 'IVAdoctes' , owstamp FROM owli where (oldesc like '2430%') GROUP BY owstamp) valorIVA inner join valowbincid on valowbincid.owstamp = valorIVA.owstamp AND valorIVA.owstamp =ow.owstampWHERE OW.DATA>='20090116' AND ow.data<='20090131' GROUP BY ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5)if i use only one derived table , like script above everything works fine :select ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5) as 'DocTesouraria', SUM(valowbincid.Bincid) as 'BincidDocTesouraria'FROM(SELECT SUM(evalor) AS 'Bincid' , owstamp FROM owli where (oldesc like '68%' or oldesc like '129%') GROUP BY owstamp) valowbincid INNER JOIN ow ON valowbincid.owstamp = ow.owstamp WHERE OW.DATA>='20090116' AND ow.data<='20090131' GROUP BY ow.data,left(ow.docnome,3)+' nº'+right('00000'+ow.intid,5)Many thanks and best regardsLuis SantosFri, 06 Nov 2009 02:42:40 GMTluissantoshttp://www.sqlservercentral.com/Forums/Topic814634-338-1.aspxHi i was trying to create flat file which as the following format 10 firstname lastname 10 Address1 Address2 10 state Country 20 firstname lastname 20 Address1 Address2 20 state Country 30 firstname lastname 30 Address1 Address2 30 state Countrynumber 10 belogs to first person information ,20 belongs to second person etc...all these records are sitting in one table in fallowing format 10 firstname1 lastname1 Address1 Address2 state Country 20 firstname2 lastname2 Address1 Address2 state Country 30 firstname3 lastname3 Address1 Address2 state Country what iam thinking to use is UNIONall please give me ideas Thu, 05 Nov 2009 18:03:24 GMTamitaryan21http://www.sqlservercentral.com/Forums/Topic814702-338-1.aspxHello Everybody...I have designed a query which contains groupings,aggregates and Joins. Now When I execute this query a value from a joined table comes repeatedly because of other groups. Now I want to get this value as a single. Example: A.Code B.Name C.Production D.Issued X.Opening Qty------------------------------------------------------------------------------AAA BBB 10000 5200 [b]5000[/b]AAA BBA 7800 3000 [b]5000[/b]AAA CCC 4500 1250 [b]5000[/b]===> X.Opening Qty table contains only single entry per month===> Table A,B,C and D contains contains entry per day. ===> How can we get that x.OpeningQty of 5000 As a Single, It should not display more than once. Because of I want to use this query in Report. Thanks In Advance. ßhavin.Thu, 05 Nov 2009 22:25:36 GMTBhavin_Bhatt25http://www.sqlservercentral.com/Forums/Topic814679-338-1.aspxi have to preestimate the given table size based on the number of records.example: if i populate 100 records how much size the table will increase(data+index_size).i have to preestimate the size value and i have to warn the user that DB size will increase to this much.Can someone help on this.thanks in advance.Thu, 05 Nov 2009 21:25:36 GMTpravallika.mca06http://www.sqlservercentral.com/Forums/Topic814682-338-1.aspxFollowing is the code in which we are getting the error.Step 1: Created Linked Server EXEC sp_addlinkedserver 'Reddevil','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=190.12.34.23;UID=sa;PWD=sa;'Step 2:DECLARE @ip VARCHAR(25)SET @ip = '190.12.34.23'EXEC sp_setnetname 'RedDevil',@ip SELECT * FROM RedDevil.DatabaseName.dbo.clientsERROR:You cannot set network name on server 'RedDevil' because it is not a linked SQL Server.Thu, 05 Nov 2009 21:34:18 GMTdeepakknhttp://www.sqlservercentral.com/Forums/Topic813757-338-1.aspxThis is the issue i need to solveCan ayone one explain what this means and how to change the codeI attached the code also1. Fix DMS process to update Derived ODS table for LAST_SPONSOR_DATE and FIRST_SPONSOR_DATE --Our ETL calls a stored procedure DWSODS01.DWP11200_IMC_DMS_MAIN You will need to update the second merge statement into DWSODS01.DWT00102_DERV_IMC_MISC. FIRST_SPON_DT_KEY_NO = CASE WHEN (load.FIRST_SPON_DT_KEY_NO = 19000101 or (load.INMKT_FIRST_SPON_DT_KEY_NO is null)) then stg.INMKT_FIRST_SPON_DT_KEY_NO else load.FIRST_SPON_DT_KEY_NO END LAST_SPON_DT_KEY_NO = stg.INMKT_LAST_SPON_DT_KEY_NO --We will need you to also write a script that will fix/update the data in DWSODS01.DWT00102_DERV_IMC_MISC. I believe our initial load scripts loaded this table correctly back in June when we created it, but our new etl wasn't maintaining it for legacy (DMS) data feeds. The data is correct in the DWSODS01.DWT00002_IMC_DMS_MAIN table. You can use the following logic in your update statement:LAST_SPON_DT_KEY_NO =DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_KEY_NOFIRST_SPON_DT_KEY_NO =CASE WHEN (DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO = 19000101 or (DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO is null)) then DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO else DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO END All of this occurs in our DWSODS01 schema on our Oracle database.Wed, 04 Nov 2009 11:30:38 GMTrahulsony111http://www.sqlservercentral.com/Forums/Topic814643-338-1.aspxHi,The following is sql query some times i get result of two rows and some times one am i missing someting i have two rows in table with 12/11/2009 but i get randomly one or two rows returned SELECT * FROM table1 WHERE Convert(VARCHAR,dbo.table1.StartDateTime,103)=@StartDateHow can i get correct result,please suggest Thanks for the helpThu, 05 Nov 2009 18:43:17 GMTforum memberhttp://www.sqlservercentral.com/Forums/Topic814493-338-1.aspxHi all,I have a table that gets items added to it everyday through a SQL AGent Job. I would like to be able to create a report that shows the difference between todays item and yesterdays item. I'm not sure how to write the query.The table has a Type, Cnt and Date column. So the records would like thisType Cnt DateFoo 206 11/04/2009Foo 509 11/05/2009Basically what I want the query to tell me the difference between todays count and yesterdays. (303). I'm really just learning tSQL so the syntax on this one has me a little stumped. Addition I can do but subtraction not so much. Any help would be greatly appreciated.Thanks,RhondaThu, 05 Nov 2009 12:33:53 GMTcudlbughttp://www.sqlservercentral.com/Forums/Topic814610-338-1.aspxI have a project that makes use of several XSD files for various operations. Each of the XSD files uses common data type definitions. My plan was to create an XSD with the type definitions and include it in all of the other XSD files. Being that <xs:include> is not supported in 2005, I am attempting to use import. The import statement appears to be fine, but the creation of the collection fails not finding my type definitions.The XSD file with my type definitions is of the format:<?xml version="1.0" ?><xs:schema attributeFormDefault="unqualified"xmlns:tns="http://schemas.xmlsoap.org/wsdl/"targetNamespace="mySpace"xmlns="mySpace"xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:simpleType name="personDef"> <xs:restriction base="xs:string"/> </xs:simpleType> ..... more type definitions........</xs:schema>My XSD file in which I want to use the definition those type definitions is of the format:<?xml version="1.0" ?><xs:schema attributeFormDefault="unqualified"xmlns:tns="http://schemas.xmlsoap.org/wsdl/"xmlns:t="mySpace"xmlns:xs="http://www.w3.org/2001/XMLSchema"><xs:import namespace="mySpace"/> <xs:element minOccurs="0" name=person type="t:personDef"/></xs:schema>The error is: Reference to an undefined name 'personDef' within namespace 'mySpace'I do not have too much experience with import usage, but I thought this syntax was correct. Any help would be appreciated.Thu, 05 Nov 2009 16:13:25 GMTmcginnhttp://www.sqlservercentral.com/Forums/Topic814590-338-1.aspxHi, I have an XML file that has image as well as Video information there . I need to import these data into two tables one for the images and the other for Video in the database separately . I need to use a Stored proc for this .Any help appreciated .Thanks,Thu, 05 Nov 2009 14:59:55 GMTPSBhttp://www.sqlservercentral.com/Forums/Topic814458-338-1.aspxI have a database and need to delete from 4 tables starting with the first and ending with the last ie:Table1pk_ColumnAColumnBColumnCTable2pk_ColumnA1fk_ColumnAColumnB1ColumnC1Table3pk_ColumnA2fk_ColumnA1ColumnB2ColumnC2Table4fk_ColumnA2ColumnB3ColumnC3When I delete the pk_ColumnA row, I'd like it to cascade all the way to fk_ColumnA2 in the 4th table.I've looked online for a few hours and read through all the books I have and can't seem to figure this out. I think I need to create triggers from the top-down, but the example I tried didn't work, though I think it was for MySQL and not MSSSQL.Any help would be much appreciated! Thanks!Thu, 05 Nov 2009 11:38:35 GMTjohnsonjj22http://www.sqlservercentral.com/Forums/Topic723433-338-1.aspxI have data like the following format:caseID Code1 AAA1 BBB1 CCC1 DDD2 CCC3 AAA3 BBB3 CCC3 DDD3 EEEI want to pivot it so that it displays as such:CaseID Code1 Code2 Code3 Code4 Code51 AAA BBB CCC DDD2 CCC3 AAA BBB CCC DDD EEEHow can I do this? I was trying to use the Pivot Transform in ssis but it seems to need an aggregate. Tue, 26 May 2009 10:56:21 GMTdndaughteryhttp://www.sqlservercentral.com/Forums/Topic813950-338-1.aspxHi is there a limit on what the concat command can do in SQL 2005. I can use sql query and do a long description of text which is 1000 characters and insert into nvarchar(max).This is coming from application and it seems that anything over 1000 it will not insert.Anyone had any problems concat...text lines together.Any advice on type of column other than nvarchar(max) let me know.Wed, 04 Nov 2009 18:55:22 GMTTRACEY-320982http://www.sqlservercentral.com/Forums/Topic813890-338-1.aspxHi, I'm trying to do some math, then convert the results to a percentage, but I come up with 0. Here's the select:select CONVERT(varchar,((76075/119027) *100)) + ' %' from master.sys.databases where database_id=1The above should show me 63.91%, but instead I get 0 %.Thanks!Wed, 04 Nov 2009 14:51:06 GMTsmithsp17http://www.sqlservercentral.com/Forums/Topic814341-338-1.aspxGuys,Would appreciate your help!I'm trying to convert a ddmmyyyy (23052008) integer to a datetime field using CAST and CONVERT but I'm getting an out of range datetime value? Could anyone shed any light on this?Many thanks,GaryThu, 05 Nov 2009 09:09:54 GMTWardyWonderland