Wednesday, August 28, 2013

[T-SQL] Wrong datatype for SP?

[T-SQL] Wrong datatype for SP?

Wrong datatype for SP?

Posted: 28 Aug 2013 12:56 AM PDT

When I try to execute a SP as shown below:EXEC eusp_e5_eSM_AS01_MaterialItemContainerlabelReport 'MC-00000002-13-0001','MC-00000002-13-0002'The following error message is shown:Msg 8144, Level 16, State 2, Procedure eusp_e5_eSM_AS01_MaterialItemContainerlabelReport, Line 0Procedure or function eusp_e5_eSM_AS01_MaterialItemContainerlabelReport has too many arguments specified.I guess the error is due to wrong dataype of SP parameter. The code is below:ALTER PROCEDURE [dbo].[eusp_e5_eSM_AS01_MaterialItemContainerlabelReport]@containerCodes nvarchar(MAX)What should this nvarchar(MAX) be changed to accommodate the comma seprated values?

scripting in table

Posted: 27 Aug 2013 07:13 AM PDT

hithis is my tableCREATE TABLE [dbo].[emp_new]( [empid] [int] NULL, [primary1] [varchar](20) NULL, [primaryinten1] [int] NULL, [primary2] [varchar](20) NULL, [primaryinten2] [int] NULL, [primary3] [varchar](20) NULL, [primaryinten3] [int] NULL, [primary4] [varchar](20) NULL, [primaryinten4] [int] NULL, [primary5] [varchar](20) NULL, [primaryinten5] [int] NULL, [primary6] [varchar](20) NULL, [primaryinten6] [int] NULL, [primary7] [varchar](20) NULL, [primaryinten7] [int] NULL) ON [PRIMARY]---------------------------------------------------------insert into emp_new(empid,primary1,primaryinten1)values(1,'ws',1)insert into emp_new(empid,primary2,primaryinten2)values(1,'gh',1)insert into emp_new(empid,primary2,primaryinten2)values(1,'lg',3)insert into emp_new(empid,primary2,primaryinten2)values(1,'fd',1)insert into emp_new(empid,primary2,primaryinten2)values(1,'hj',1)----------------------------------------------------------------------empid primary1 primaryinten1 primary2 primaryinten2 primary3 primaryinten3 primary4 primaryinten4 primary5 primaryinten5 primary6 primaryinten6 primary7 primaryinten71 ws 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL1 NULL NULL gh 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL1 NULL NULL lg 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL1 NULL NULL fd 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL1 NULL NULL hj 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL----------------------------------------------------------------------------------------whati want is everything in 1 line.empid primary1 primaryinten1 primary2 primaryinten2 primary3 primaryinten3 primary4 primaryinten4 primary5 primaryinten5 primary6 primaryinten6 primary7 primaryinten71 ws 1 gh 1 lg 3 fd 1 hj 1 null null null null------------------------------------------------------------------------------------------and the rows rows should be delete.there should be line where primary1 is not null ,that line should be filled with all other condition and other rows should be delete.how to do it

Query Help

Posted: 27 Aug 2013 11:39 AM PDT

HiI have a requirement like below. I have to give the flavours to the application.We have thousands of records to update.Please help me. Actual tableAPP|||COLOUR|||FLAVOURap1|||GREEENap1|||YELLOWap1|||YELLOWap1|||GREEENap2|||YELLOwap2|||YELLOWap2|||YELLOWap3|||GREEENap3|||YELLOWap3|||RED ap3|||GREEENap4|||GREENap4|||GREENap4|||GREENOutput would look like belowAPP|||COLOUR|||FLAVOURap1|||GREEEN|||YELLOWAap1|||YELLOW|||YELLOWAap1|||YELLOW|||YELLOWAap1|||GREEEN|||YELLOWAap2|||YELLOW|||YELLOWBap2|||YELLOW|||YELLOWBap2|||YELLOW|||YELLOWBap3|||GREEEN|||REDap3|||YELLOW|||REDap3|||RED |||REDap3|||GREEEN|||REDap4|||GREEN|||GREENAap4|||GREEN|||GREENAap4|||GREEN|||GREENAlet me know if you need additional informationThanksAswin

Is there a way to sort the table after records are inserted?

Posted: 27 Aug 2013 09:33 PM PDT

Is there a way to sort the table after records are inserted?Declare @tab Table(name varchar(10), value varchar(10))Declare @mystring varchar(10)set @mystring='AA'insert into @tab select 'A', 1 unionselect 'AA',2select @mystring=REPLACE(@mystring,name,value) from @tab order by LEN(name) descselect @mystringHere is my problem1. I create a table with two cols Name and Value2. I insert records into this table using a union statement. (Ex records : {A,1},{AA,2})3. Now I have a string which I need to be replaced. Ex: if my string is AA, I need it to be replaced by 2. But now what happen is since in the table A is before AA, it replaces it with two 1's , ie 11

Insert on condition in Trigger

Posted: 27 Aug 2013 04:51 PM PDT

Hi,Here is the code that I have written and its working fine. But now I have to put a condition while Insertion and I am not getting how to do that.Scenario is: Currently if made any changes DeviationRequestDetails a new row gets inserted into CLCProcessUnitDetailsCompany if it not exists previously. But now what I want is that if I made any change in column DeviationStatus of Source then only new rows should get inserted in Target else if changes made in any other column of Source then no rows should get inserted into Target.Hope the scenario is clear to you now....[code="sql"]ALTER Trigger [dbo].[Trg_DeviationRequestDetails_Ins]ON [dbo].[DeviationRequestDetails]After INSERT, UPDATE ASBEGIN Declare @deviationstatus int SET @deviationstatus = (SELECT D.DeviationStatus from DELETED D) MERGE INTO CLCProcessUnitDetailsCompany T USING INSERTED as S ON S.ProjectID = T.ProjectID AND S.ProposalID = T.ProposalID AND S.SolutionId = T.SolutionId AND S.UnitID = T.UnitID WHEN MATCHED AND S.DeviationStatus <> @deviationstatus THEN UPDATE SET T.DevDateChanged = Getdate() WHEN NOT MATCHED THEN INSERT ( ProjectID, ProposalID, SolutionID, UnitID, DevDateChanged, QuotDateChanged, ApprovalDateChanged, AddedBy, DateAdded, ChangedBy, DateChanged ) VALUES ( S.ProjectID, S.ProposalID, S.SolutionID, S.UnitID, Getdate(), NULL, NULL, S.AddedBy, S.DateAdded, S.ChangedBy, S.DateChanged );END[/code]

Difficult Sorting

Posted: 27 Aug 2013 07:35 AM PDT

Using SQL Server 2008 R2, but also have access to SQL Server 2012I have this sample data.[b][u]ItemNo[/u][/b] [b][u]DueDate[/u][/b] [b][u]Cham[/u][/b]121117 2013-09-05 121098 2013-09-05 Y333456 2013-09-07 Y347545 2013-09-07798665 2013-09-07 982389 2013-09-08 908465 2013-09-08 Y985551 2013-09-09 Y432568 2013-09-09 874378 2013-09-10647849 2013-09-10098357 2013-09-10673467 2013-09-13098355 2013-09-13 Y237678 2013-09-13 Y984474 2013-09-17 Y093409 2013-09-17003434 2013-09-18The first sort must be on "Due Date". The second sort must be on "Cham". When days are spanned, I want to have the Cham field as continuous as possible. I realize this will not be possible for every day span. It seems the Cham field must be sometimes sorted Asc and other times sorted Desc.Thanks.

Controlling Transaction log size in Simple Mode

Posted: 27 Aug 2013 06:53 PM PDT

Never really got to grips with Transaction log size in Simple Recovery mode. I originally had a MERGE statement within a stored procedure. When this ran it looked at a table with approx 100 m records as source and the target was a table of the same size. When running this we found the transaction log file grew and we ran out of space.The first solutionWe split the stored procedure into two MERGE statements. The MERGE's do inserts and updates only. I split it into two in the hope that once the first statement completes then the second would reuse the space in the transaction log. So Log siize would increase when executing the first statement. When statement one completes and statement two starts the transaction log would reuse the space it had assigned to statement one.This did not appear to work. The log kept growing. Now looked at the settings for the Log. It has Auttogrowth enabled and restricted growth to the default etting of incredibly large. So what went wrong?Did the transaction log not reuse the available space that was ready for reuse because it was allowed to keep growing to a very very large size? Would it help if I restricted it to 40GB forcing it to reuse the space?Did I need to explicitly BEGIN and COMMIT transactions for each statement.Your help gratefully appreciated this is not the first time this has happened to me and I would really like to be able to manage transaction log files to a reasonable size when in Simple Recovery ModeOur next attempt will be to use transactions around the MERGE statements and a smaller log max size.ThanksE:w00t:

trigger to restrict access for changing login permissions in sql server 2008

Posted: 27 Aug 2013 02:51 AM PDT

Hi Friends,Let us assume I have 2 logins XXX & YYY. XXX-->sysadminYYY-->all databases reader permission.XXX is trying to change the permission for YYY login to sysadmin or db_owner for all databases. Is there a way to restrict the access instead of removing the sysadmin privilege for XXX login.Thanks in advance..

sp_getrecords to get all records from a table

Posted: 27 Aug 2013 05:17 PM PDT

Hi, I have tried and created one sp which will get the table name as input and will return all the records in that table. I will map this sp in the SSMS keyboard shortcut (just like Alt + F1 to return the table structure) so that i can use this shortcut to get the records from that table instead of writing some thing like SELECT * FROM tablename. this sp works fine and helped me a lot. however, if the table is belong to a schema then we have to pass the table name along with schema like schema.tablename. In this case, the sp is failing. If i put single quotes between the table name like 'schema.tablename' it is working fine. But is there any option to tweak the sp so that we dont have to pass the table name in quotes.The sp iscreate PROC [dbo].[sp_getrecords] ( @TableName varchar(4000) ) AS exec('select * from '+@TableName+'')

Scripting CMS

Posted: 27 Aug 2013 02:19 AM PDT

I've looked but perhaps my GoogleFu isn't good enough. Is there a way to script a CMS server group and script servers into it?Regards,Erin

Query Save Help

Posted: 27 Aug 2013 02:04 AM PDT

Hi Guys, I google but couldn't any find help. Here is the issue. From SSMS if I want to save any sql to .sql from File==>Save SqlQuery.Sql==>ask destination where I am gonna save and at last hit OK. It should save my Query in .SQL, right? for some reason its saving my query as .txt. Is anyone can help me why he doing this or which option do I have to change. Thank You....
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog