Wednesday, May 22, 2013

[T-SQL] Error when trying to call Stored Procedure from batch file

[T-SQL] Error when trying to call Stored Procedure from batch file

Error when trying to call Stored Procedure from batch file

Posted: 22 May 2013 01:18 AM PDT

Im running the following in a batch filesqlcmd -Q "exec dbo.MySproc" -S "MyServer" -d "MyDatabase"and Im getting the following error:Named Pipes Provider: Could not open a connection to SQL Server [121]Any idea what Im doing wrong?

SELECT INTO ignoring IDENTITY column

Posted: 21 May 2013 06:06 AM PDT

Hello,I want to know if anyone knows of an easy way to do a SELECT INTO from a table with an identity column, but I do not want the Identity column to be marked as an identity column in the new table. I do want the data from the identity column, but I don't want to have the identity property.The best I can come up with is using the union operator:[code="sql"]with t1 as (select * from table union select * from table)select * into newtable from t1[/code]

Date and Hour Time from DateTime

Posted: 21 May 2013 11:43 PM PDT

Hello EveryoneI am working on a query that I know can be written to be more efficient.I have a column that is of datetime data type.[code="sql"]DECLARE @Dates TABLE( CreateDate datetime)INSERT INTO @Dates(CreateDate)SELECT '2013-03-12 08:44:12.420' UNION ALL -- this oneSELECT '2013-03-12 08:38:46.103' UNION ALL -- this oneSELECT '2013-03-12 07:37:17.693' UNION ALLSELECT '2013-03-12 07:56:50.697' UNION ALLSELECT '2013-03-12 08:43:20.623' UNION ALL -- this oneSELECT '2013-03-12 08:44:12.297' UNION ALL -- this oneSELECT '2013-03-12 10:06:57.353' UNION ALLSELECT '2013-03-12 09:10:54.390' UNION ALLSELECT '2013-03-12 10:03:58.770' UNION ALLSELECT '2013-03-12 07:56:59.817'[/code] I would like to query all the rows for a specific day, '2013-03-12', and only the for the hour of 0800If I were to select the count, I should have only 4 rows returnedThanks in advance for your assistance, suggestions and commentsAndrew SQLDBA

Trigger referring twice to a table doesn't work properly

Posted: 21 May 2013 11:53 PM PDT

Hi all,I have a problem with a trigger and I hope some expert can help me on this ...This is the trigger[code="other"]ALTER TRIGGER [dbo].[updt_order_date] ON [dbo].[FUND_MANAGEMENT_DETAILS]after insert AS BEGIN set nocount on declare @dt datetime select @dt = getdate() update fmd set order_date = (case f.pricing_frequency when 2 then -- weekly (select min(c.dt) from calendar cc, calendar c where 1 = 1 and cc.dt = @dt and c.dw_lissia = f.PRICING_DAY and c.id_fisa_bsn_day >= cc.id_fisa_bsn_day + f.FWD_PRICE_REPORT_DAYS ) else '' end) from FUND_MANAGEMENT_DETAILS fmd inner join inserted i on i.FMD_ID = fmd.FMD_ID inner join FUNDS f on f.FDS_ID = fmd.FUNDEND [/code]This code doesn't work! The case "when 2" is never executed although it should be for some rows.However, if I rem out all reference to calendar cc, which gives following code, it works; meaning the results are wrong but the "case 2 " gets executed.[code="other"]ALTER TRIGGER [dbo].[updt_order_date] ON [dbo].[FUND_MANAGEMENT_DETAILS]after insert AS BEGIN update fmd set order_date = (case f.pricing_frequency when 2 then -- weekly (select min(c.dt) from --calendar cc, calendar c where 1 = 1 --and cc.dt = @dt and c.dw_lissia = f.PRICING_DAY and c.id_fisa_bsn_day >= 98 + f.FWD_PRICE_REPORT_DAYS ) else '' end) from FUND_MANAGEMENT_DETAILS fmd inner join inserted i on i.FMD_ID = fmd.FMD_ID inner join FUNDS f on f.FDS_ID = fmd.FUNDEND [/code]I tried many scenarii since yesterday, but none of them succeeds and to be honest I run against the wall now.I'd be grateful if anybody with a good knowledge about triggers could help me.ThanksrvEDIT: to paste missing code

Need Alternate Solution For Cumulative Sum

Posted: 21 May 2013 06:27 PM PDT

Hi Friends...I Having Table For The Following Structure..( Sample Data )[code="sql"]Create Table EmpProcessDetail ( EmpId varchar(25), EmpName varchar(50),ProcessName varchar(30),GroupCode varchar(5),PlannedDays int,ActualDays int, DelayedDays int,TotalDelayDays int) insert into EmpProcessDetail values ('DS_1','Saran','Purchase','A',15,11,4,NULL), ('DS_1','Saran','Measurement','B',18,15,3,NULL), ('DS_1','Saran','Cutting','C',12,15,-3,NULL), ('DS_1','Saran','Stitching','D',20,27,-7,NULL), ('DS_1','Saran','Ironing','E',14,19,-5,NULL), ('DS_1','Saran','Packing','F',15,13,2,NULL), ('DS_1','Saran','Checking','G',18,12,6,NULL), ('DS_1','Saran','Delivery','H',22,33,-11,NULL), ('DS_2','Arun','Purchase','A',12,13,-1,NULL), ('DS_2','Arun','Measurement','B',15,19,-4,NULL), ('DS_2','Arun','Cutting','C',22,29,-7,NULL), ('DS_2','Arun','Stitching','D',41,43,-2,NULL), ('DS_2','Arun','Ironing','E',27,26,-1,NULL), ('DS_2','Arun','Packing','F',18,13,5,NULL), ('DS_2','Arun','Checking','G',11,14,-3,NULL), ('DS_2','Arun','Delivery','H',17,24,-7,NULL), ('DS_3','Kumar','Purchase','A',41,43,-2,NULL), ('DS_3','Kumar','Measurement','B',23,26,-3,NULL), ('DS_3','Kumar','Cutting','C',22,29,-7,NULL), ('DS_3','Kumar','Stitching','D',41,43,-2,NULL), ('DS_3','Kumar','Ironing','E',12,13,-1,NULL), ('DS_3','Kumar','Packing','F',14,19,-5,NULL), ('DS_3','Kumar','Checking','G',12,14,-2,NULL), ('DS_3','Kumar','Delivery','H',18,13,5,NULL) select * from EmpProcessDetail[/code]Below I have mentioned The my Required Table Data [code="sql"]EmpId EmpName ProcessName GroupCode PlannedDays ActualDays DelayedDays TotalDelayDays------------------------- -------------------------------------------------- ------------------------------ --------- ----------- ----------- ----------- --------------DS_1 Saran Purchase A 15 11 4 4DS_1 Saran Measurement B 18 15 3 7DS_1 Saran Cutting C 12 15 -3 4DS_1 Saran Stitching D 20 27 -7 -3DS_1 Saran Ironing E 14 19 -5 -8DS_1 Saran Packing F 15 13 2 -6DS_1 Saran Checking G 18 12 6 0DS_1 Saran Delivery H 22 33 -11 -11DS_2 Arun Purchase A 12 13 -1 -1DS_2 Arun Measurement B 15 19 -4 -5DS_2 Arun Cutting C 22 29 -7 -12DS_2 Arun Stitching D 41 43 -2 -14DS_2 Arun Ironing E 27 26 -1 -15DS_2 Arun Packing F 18 13 5 -10DS_2 Arun Checking G 11 14 -3 -13DS_2 Arun Delivery H 17 24 -7 -20DS_3 Kumar Purchase A 41 43 -2 -2DS_3 Kumar Measurement B 23 26 -3 -5DS_3 Kumar Cutting C 22 29 -7 -12DS_3 Kumar Stitching D 41 43 -2 -14DS_3 Kumar Ironing E 12 13 -1 -15DS_3 Kumar Packing F 14 19 -5 -20DS_3 Kumar Checking G 12 14 -2 -22DS_3 Kumar Delivery H 18 13 5 -17[/code]My Requirement :1) I want to be Fill the TotalDelayDays Column..2)The Cumulative Sum Based on the DelayedDays..3) The Cumulative Sum Should be Calculated on EmpID once it will finished for particular EmpIDthen Cumulative Sum should start from Initial for second EmpID ...I Got The Result For the Following Method...[code="sql"]; WITH BASE AS ( SELECT EmpId, EmpName, ProcessName, GroupCode, PlannedDays, ActualDays, DelayedDays, rnk = DENSE_RANK() OVER (PARTITION BY EmpID ORDER BY GroupCode) FROM EmpProcessDetail) SELECT EmpId, EmpName, ProcessName, GroupCode, PlannedDays, ActualDays, DelayedDays, x.TotalDelayDaysFROMBASE bCROSS APPLY (SELECT TotalDelayDays = SUM(DelayedDays) FROM BASE WHERE EmpID = b.EmpID AND rnk <= b.rnk) x[/code]But That Method Takes Over time To Produce The Result for 40000 Records takes around 25 minutes.I want to avoid the Performance Issue..So i Need Another Method to calculate The Cumulative Sum...(for 40000 Records)Thanks & Regards..Saravanan.D

SQL Query Help

Posted: 21 May 2013 12:07 PM PDT

Create Table Test1( ID INT, VALUE VARCHAR(20), MAXVAL VARCHAR(20), VALUETYPE INT, OutCome int) --select * from Test1 INSERT INTO Test1VALUES ('1','FirstValue','140/90','1','4')INSERT INTO Test1VALUES ('2','SecondValue','140/90','1','4')INSERT INTO Test1VALUES ('3','ThirdValue',null,'4','3')INSERT INTO Test1VALUES ('4','FourthValue',null,'4','3') Create Table Test2( ID INT, MAXVAL VARCHAR(20), MINVAL VARCHAR(20), Type int) INSERT INTO Test2VALUES ('1','139','89','1')INSERT INTO Test2VALUES ('2','141','95','1')INSERT INTO Test2VALUES ('3','141','95','4') Select * from test1 t1 Inner Join Test2 t2 ON T1.ID = T2.ID WHERE ((SUBSTRING(isnull(t1.MaxVal,''),1,CHARINDEX('/',ISNULL(T1.MAXVAL,''))-1))> T2.MAXVAL AND LTRIM(RIGHT(ISNULL(t1.MaxVal,''), CHARINDEX('/', ISNULL(t1.MaxVal,'') + '/')-2) ) > T2.MINVAL) or (T1.VALUETYPE <= T2.Type) Here is the error that I am getting"Invalid length parameter passed to the LEFT or SUBSTRING function"Please Advice.Thanks in advance.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog