I needed to write function with table parameter.
an example:
CREATE FUNCTION getParentByBrandList
( @_BrandList TABLE(
BR_ID INT, BR_Name NVARCHAR(150), BR_ParentBrandID INT, BR_MasterBrandID INT, BR_Role INT,
BR_State INT, BR_OwnerID INT, BR_OwnerIP NVARCHAR(50), BR_CreateDate DATETIME, BR_UpdaterID INT,
BR_UpdaterIP NVARCHAR(50), BR_UpdateDate DATETIME
)
)
How can I do?
Thanx
Hi Friends I have small doubt in sql server how to solve this issue Table: emp
id| name| sal
1| abc| 100
2| ha| 200
1| abc| 100
1| abc| 100
1| abc| 100
2| ha| 200
2| ha| 200
3 hai| 400
based on this data I want give data duplicate or not in the table for that status i out show output. I tried like below
select count(*)as status
from [Test].[dbo].[emp]
group by [id]
,[name]
,[sal]
having count(*)>=1
order by count(*) desc
that time i got output
status
4
3
1
I donot want get output like above way.
I want show output like below
Status
1
when data comes unique in table that time status shows : 0 values. 1 means duplicate data and o means unique records.please tell me how to get singe status values to achive this issue.
We have loads of XML files which we have to load, parse and save into multiple tables of a massive legacy database.
I have written code which does it using EF and it works perfectly with 1 thread. However I need to speed this process up so I need to process multiple XML files concurrently and therefore I use multiple threads. When I use multiple threads which have their own instance of DbContext, I constantly get deadlocks:
Transaction (Process ID 426) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
I searched the internet and found a solution which needed us to set the isolation level of the database to Snapshot however because it may have massive negative impacts on the legacy systems which use the same database it's not an option.
To avoid the deadlocks, I wrapped every dbContext.SaveChanges() in a lock() statement. This prevents the deadlocks but then the process becomes very slow again as if everything is done using one thread.
Can you please help me with this problem and guide me as to how I can update the DB using multiple instances of ObjectContext concurrently without causing deadlocks?
p.s. We can not use TransactionScope for some reasons!
p.p.s. We use SQL Server 2005
Hi this one is a bit strange so bare with me.
I have a table in SQL Server 2005 with a field called lock which will lock users from making changes to their data, this lock can be activated at various points throughout the app I am designing, however I have another column which means cut off date, basically the data can be changed between two dates. The start date is when the admins decide to open it out to everyone. I was wondering instead of using jobs, or a service is there a way to make sql server check its time against the cut of date time and mark the flag locked automatically. Almost like a file system watcher for sql server, baring in mind that I will have quite a few records all with different cut off dates.
Thank you very much for all your help and it something I think can be useful if it can be done that is.
When I trying to execute following stored procedure it gives this error
Msg 156, Level 15, State 1, Procedure TT_SP_UpdateTask, Line 43 Incorrect syntax near the keyword 'end'.
The End refers to the end which near to if(@Status='Developing') in the following procedure.
I checked the order of begins and ends again and again, but I couldn't find the reason for the error.
This is my stored procedure.
ALTER PROCEDURE [dbo].[TT_SP_UpdateTask]
@EstimateTime decimal(18,2),
@Status varchar(10),
@TaskAssignId varchar(30),
@IsDone bit
AS
BEGIN
if(@IsDone = 1)
begin
UPDATE [TT_TaskAssign]
SET
DevFinish = getdate(),
Status = @Status,
IsDone = @IsDone
WHERE AssignID = @TaskAssignId
UPDATE dbo.TT_TaskAsignKPI
SET
actTime = (select convert(varchar(5),DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())/3600)+':'+convert(varchar(5),DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())%3600/60)+':'+convert(varchar(5),(DateDiff(s, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), getdate())%60)) as [hh:mm:ss],
actTimeNum= (SELECT DATEDIFF(SECOND, (SELECT DevStart FROM dbo.TT_TaskAssign WHERE AssignID=@TaskAssignId), GETDATE()) AS SecondDiff,
KPINum= (SELECT DATEDIFF(SECOND,(SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE()) AS SecondsKPI,
KPI= (select convert(varchar(5),DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())/3600)+':'+convert(varchar(5),DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())%3600/60)+':'+convert(varchar(5),(DateDiff(s, (SELECT actAsignTime FROM dbo.TT_TaskAsignKPI WHERE AssignID=@TaskAssignId), GETDATE())%60)) as [HH:MM:SS]
WHERE AssignID=@TaskAssignId
end --(This is the end which cause for the error)
if(@Status='Developing')
begin
UPDATE [TT_TaskAssign]
SET
EstimateTime = @EstimateTime,
Status = @Status,
IsDone = @IsDone,
DevStart = getdate()
WHERE AssignID = @TaskAssignId
UPDATE dbo.TT_TaskAsignKPI
SET
actAsignTime = getdate()+@EstimateTime/24
WHERE AssignID=@TaskAssignId
end
END
I created a Web Service method that calls a Stored Procedure, which accepts an XML input parameter, in MSSQL 2005.
I've created one in MSSQL 2008 and got it working but not in MSSQL 2005. The GridView shows the correct number of rows and columns but all are blank.
c# for generating XML:
DataSet CollectedReceivingData = new DataSet("CollectedReceivingData");
DataTable SR_Header = CollectedReceivingData.Tables.Add("SR_Header");
DataTable SR_Details = CollectedReceivingData.Tables.Add("SR_Details");
SR_Header.Columns.Add("receiving_id", Type.GetType("System.Int32"));
SR_Header.Columns.Add("job_no", Type.GetType("System.Int32"));
SR_Header.Columns.Add("item_id", Type.GetType("System.Int32"));
SR_Header.Columns.Add("expiry_date", typeof(DateTime));
SR_Header.Columns.Add("remarks", typeof(string));
SR_Header.Columns.Add("rcv_qty", Type.GetType("System.Int32"));
SR_Header.Rows.Add("327051", "0381021", "21848", "02/03/2016", "This is a remarks test.", "50");
SR_Header.Rows.Add("327052", "0381021", "21849", "02/05/2016", "This is a remarks test for item 2", "25");
SR_Details.Columns.Add("receiving_id", Type.GetType("System.Int32"));
SR_Details.Columns.Add("pallet_id", typeof(string));
SR_Details.Columns.Add("qty_in_pallet", Type.GetType("System.Int32"));
SR_Details.Columns.Add("expiry_date", typeof(DateTime));
SR_Details.Columns.Add("remarks", typeof(string));
SR_Details.Columns.Add("warehouse_id", Type.GetType("System.Int32"));
SR_Details.Columns.Add("location_id", Type.GetType("System.Int32"));
SR_Details.Rows.Add("327051", "327051-001", "24", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-002", "5", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-003", "5", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-004", "15", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327052", "327052-001", "5", "02/03/2016", "This is a remarks test.", "3", "2");
SR_Details.Rows.Add("327052", "327052-002", "15", "02/03/2016", "This is a remarks test.", "3", "2");
SR_Details.Rows.Add("327052", "327052-003", "5", "02/03/2016", "This is a remarks test.", "3", "2");
DataSet ds = new DataSet();
WebService1 wsobject = new WebService1();
ds = wsobject.UpdateCollectedReceivingData(CollectedReceivingData);
gvReceiving.DataSource = ds.Tables[0];
gvReceiving.DataBind();
gvReceiving2.DataSource = ds.Tables[1];
gvReceiving2.DataBind();
Web Method:
[WebMethod]
public DataSet UpdateCollectedReceivingData(DataSet ds)
{
DataSet ds_result = new DataSet();
XmlDocument xmlDoc = new XmlDocument();
string connectionString = ConfigurationManager.ConnectionStrings["ConnStringWS"].ConnectionString;
SqlConnection objConn = new SqlConnection(connectionString);
DataSet dsResult = new DataSet();
objConn.Open();
using (SqlCommand cmd = new SqlCommand("[dbo].[SP_Receive]"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = objConn;
cmd.Parameters.Add("@x", SqlDbType.Xml).Value = ds.GetXml();
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(ds_result);
}
catch
{
}
}
objConn.Close();
return ds_result;
}
Stored Procedure:
CREATE PROCEDURE [dbo].[SP_Receive]
(
@x XML
)
AS
BEGIN
DECLARE @t1 TABLE (
[receiving_id] [int] NULL,
[job_no] [int] NULL,
[item_id] [int] NULL,
[expiry_date] [datetime] NULL,
[remarks] [nvarchar](255) NULL,
[rcv_qty] [int] NULL
)
DECLARE @t2 TABLE(
[receiving_id] [int] NULL,
[pallet_id] [nvarchar](20) NULL,
[qty_in_pallet] [int] NULL,
[expiry_date] [datetime] NULL,
[remarks] [nvarchar](255) NULL,
[warehouse_id] [int] NULL,
[location_id] [int] NULL
)
INSERT INTO @t1 (
[receiving_id],
[job_no],
[item_id],
[expiry_date],
[remarks],
[rcv_qty]
)
SELECT
xmlVals.rowvals.value('@receiving_id','int') as [receiving_id],
xmlVals.rowvals.value('@job_no','int') as [job_no],
xmlVals.rowvals.value('@item_id','int') as [item_id],
xmlVals.rowvals.value('@expiry_date','datetime') as [expiry_date],
xmlVals.rowvals.value('@remarks','nvarchar(255)') as [remarks],
xmlVals.rowvals.value('@rcv_qty','int') as [rcv_qty]
FROM
@x.nodes('//CollectedReceivingData/SR_Header') as xmlVals(rowvals)
INSERT INTO @t2 (
[receiving_id],
[pallet_id],
[qty_in_pallet],
[expiry_date],
[remarks],
[warehouse_id],
[location_id]
)
SELECT
xmlVals.rowvals.value('@receiving_id','int') as [receiving_id],
xmlVals.rowvals.value('@pallet_id','nvarchar(20)') as [job_no],
xmlVals.rowvals.value('@qty_in_pallet','int') as [item_id],
xmlVals.rowvals.value('@expiry_date','datetime') as [expiry_date],
xmlVals.rowvals.value('@remarks','nvarchar(255)') as [remarks],
xmlVals.rowvals.value('@warehouse_id','int') as [warehouse_id],
xmlVals.rowvals.value('@location_id','int') as [location_id]
FROM
@x.nodes('//CollectedReceivingData/SR_Details') as xmlVals(rowvals)
SELECT * INTO #tmp1 FROM
(
SELECT t1.receiving_id as receiving_id,
error_message = CASE
WHEN t2.receiving_id IS NOT NULL THEN 'Item has already been received.'
WHEN total_rcv_qty > t1.rcv_qty THEN 'Total received quantity exceeded the quantity to be received.'
ELSE NULL
END
FROM (
SELECT SD.receiving_id, SH.job_no, SH.item_id, SH.rcv_qty, SUM(qty_in_pallet) as total_rcv_qty
FROM @t2 SD
INNER JOIN @t1 SH ON SH.receiving_id = SD.receiving_id
GROUP BY SD.receiving_id, SH.job_no, SH.item_id, SH.rcv_qty, SD.receiving_id ) t1
LEFT JOIN StockIN_Header t2 ON t1.receiving_id = t2.receiving_id
LEFT JOIN StorBest.dbo.item_master IM ON t1.item_id = IM.itemid
) AS tmp1
SELECT * INTO #tmp2 FROM
(SELECT SD.receiving_id, SD.pallet_id,
error_message = CASE
WHEN WLV.warehouseid IS NULL THEN 'Invalid location_id.'
WHEN W.warehouseid IS NULL THEN 'Invalid warehouse_id.'
WHEN SD2.pallet_id IS NOT NULL THEN 'Pallet has already been received'
ELSE NULL
END
FROM @t2 SD
LEFT JOIN StorBest.dbo.Warehouse_Loc_VIEW WLV ON SD.location_id = WLV.LocationID
LEFT JOIN StorBest.dbo.Warehouse W ON SD.warehouse_id = W.warehouseid
LEFT JOIN StockIN_Details SD2 ON SD.pallet_id = SD2.pallet_id
) AS tmp2
SELECT * FROM @t1
SELECT * FROM @t2
-- IF (SELECT COUNT(*) FROM (SELECT error_message FROM #tmp1 UNION ALL SELECT error_message FROM #tmp2) AS t WHERE error_message IS NOT NULL) = 0
-- BEGIN
-- INSERT INTO StockIN_Header SELECT * FROM @t1
-- INSERT INTO StockIN_Details SELECT * FROM @t2
-- END
END
