TableAdapters results differ from Stored Procedure results, C# - SQL 2000
-
Using Sql 2000 and C#. I have a stored procedure where I pass in my identifier and use it to select a set of rows from several tables. When I execute this SP in query analyzer it works great, returning only the rows that do not exist in @Data. When executed in C# it is returning the entire set of data relevant to that identifier. I'm at a loss on this. I've spent hours changing the SP around trying to get different results. I've tried both temp tables and var tables with the same results. Again, it always works in QA but never in C#. Any thoughts? I've shortened some of the repetitive data from this query to save on screen space (and your eyes) but let me know if there is anything else i can show that could help. Also, here's the weird part. I have a near exact duplicate of this query that works in both C# and QA. The only difference is that #Chas is populated via a select statement that returns a range of values instead of just being populated by the passed in value. I know i don't have to insert this value into #Chas but instead could just compare directly against it. It was like this just to make it as identical to the original as possible since the original works.
USE [B_D_DashPanel]
GO
/****** Object: StoredProcedure [dbo].[sp_BD_PermConflicts] Script Date: 09/10/2012 15:55:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_BD_PermConflicts_Chassis_Only]
@Chassis_Search nvarchar(50)
ASDeclare @Data TABLE(
Chassis nvarchar(50),
ItemC nvarchar(50),
Location nvarchar(50),
Panel nvarchar(13),
Part nvarchar(50)
)Create TABLE #Chas (
Chassis_No nvarchar(50)
)set nocount on
insert into #Chas (Chassis_No) values (@Chassis_Search)
insert into @Data (Chassis, ItemC, Location, Panel, Part)
select distinct WorkPlacementorder.salesorderno, workplacementorder.itemcode, workplacementorder.location, workplacementorder.panel,
itembompn.partnumber1
from #Chas, workplacementorder
join itembompn
on workplacementorder.itemcode = itembompn.itemcode
inner join chassisdb.dbo.tblopenjit
on workplacementorder.salesorderno = chassisdb.dbo.tblopenjit.chassisnbr
where chassisdb.dbo.tblopenjit.chassisnbr like #Chas.Chassis_No and chassisdb.dbo.tblopenjit.itemid = itembompn.partnumber1
and WorkPlacementorder.salesorderno like #Chas.Chassis_NoSELECT b_d_Dashpanel.dbo.workplacementorder.ItemCode, b_d_Dashpanel.dbo.items.itemdescription
FROM #Chas, B_D_DashPanel.dbo.WorkPlacementOrder join -
Using Sql 2000 and C#. I have a stored procedure where I pass in my identifier and use it to select a set of rows from several tables. When I execute this SP in query analyzer it works great, returning only the rows that do not exist in @Data. When executed in C# it is returning the entire set of data relevant to that identifier. I'm at a loss on this. I've spent hours changing the SP around trying to get different results. I've tried both temp tables and var tables with the same results. Again, it always works in QA but never in C#. Any thoughts? I've shortened some of the repetitive data from this query to save on screen space (and your eyes) but let me know if there is anything else i can show that could help. Also, here's the weird part. I have a near exact duplicate of this query that works in both C# and QA. The only difference is that #Chas is populated via a select statement that returns a range of values instead of just being populated by the passed in value. I know i don't have to insert this value into #Chas but instead could just compare directly against it. It was like this just to make it as identical to the original as possible since the original works.
USE [B_D_DashPanel]
GO
/****** Object: StoredProcedure [dbo].[sp_BD_PermConflicts] Script Date: 09/10/2012 15:55:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_BD_PermConflicts_Chassis_Only]
@Chassis_Search nvarchar(50)
ASDeclare @Data TABLE(
Chassis nvarchar(50),
ItemC nvarchar(50),
Location nvarchar(50),
Panel nvarchar(13),
Part nvarchar(50)
)Create TABLE #Chas (
Chassis_No nvarchar(50)
)set nocount on
insert into #Chas (Chassis_No) values (@Chassis_Search)
insert into @Data (Chassis, ItemC, Location, Panel, Part)
select distinct WorkPlacementorder.salesorderno, workplacementorder.itemcode, workplacementorder.location, workplacementorder.panel,
itembompn.partnumber1
from #Chas, workplacementorder
join itembompn
on workplacementorder.itemcode = itembompn.itemcode
inner join chassisdb.dbo.tblopenjit
on workplacementorder.salesorderno = chassisdb.dbo.tblopenjit.chassisnbr
where chassisdb.dbo.tblopenjit.chassisnbr like #Chas.Chassis_No and chassisdb.dbo.tblopenjit.itemid = itembompn.partnumber1
and WorkPlacementorder.salesorderno like #Chas.Chassis_NoSELECT b_d_Dashpanel.dbo.workplacementorder.ItemCode, b_d_Dashpanel.dbo.items.itemdescription
FROM #Chas, B_D_DashPanel.dbo.WorkPlacementOrder joinLogic dictates that there is a difference between
@Chassis_Searchpassed in as a nvarchar and the format stored in the table. Can you do a select fromitembompnwhere field = @Chassis_Search. It may be a character set incompatibility. Try changing the field to varchar! Also never use sp_ to prefix your procedures, this forces SQL Server to search all the system procedures as well as your own - it can be a real performance hit.Never underestimate the power of human stupidity RAH
-
Logic dictates that there is a difference between
@Chassis_Searchpassed in as a nvarchar and the format stored in the table. Can you do a select fromitembompnwhere field = @Chassis_Search. It may be a character set incompatibility. Try changing the field to varchar! Also never use sp_ to prefix your procedures, this forces SQL Server to search all the system procedures as well as your own - it can be a real performance hit.Never underestimate the power of human stupidity RAH
I would go one step further, don't prefix.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I would go one step further, don't prefix.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
I still prefix views and functions I gave up on procs many versions ago.
Never underestimate the power of human stupidity RAH
-
Using Sql 2000 and C#. I have a stored procedure where I pass in my identifier and use it to select a set of rows from several tables. When I execute this SP in query analyzer it works great, returning only the rows that do not exist in @Data. When executed in C# it is returning the entire set of data relevant to that identifier. I'm at a loss on this. I've spent hours changing the SP around trying to get different results. I've tried both temp tables and var tables with the same results. Again, it always works in QA but never in C#. Any thoughts? I've shortened some of the repetitive data from this query to save on screen space (and your eyes) but let me know if there is anything else i can show that could help. Also, here's the weird part. I have a near exact duplicate of this query that works in both C# and QA. The only difference is that #Chas is populated via a select statement that returns a range of values instead of just being populated by the passed in value. I know i don't have to insert this value into #Chas but instead could just compare directly against it. It was like this just to make it as identical to the original as possible since the original works.
USE [B_D_DashPanel]
GO
/****** Object: StoredProcedure [dbo].[sp_BD_PermConflicts] Script Date: 09/10/2012 15:55:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_BD_PermConflicts_Chassis_Only]
@Chassis_Search nvarchar(50)
ASDeclare @Data TABLE(
Chassis nvarchar(50),
ItemC nvarchar(50),
Location nvarchar(50),
Panel nvarchar(13),
Part nvarchar(50)
)Create TABLE #Chas (
Chassis_No nvarchar(50)
)set nocount on
insert into #Chas (Chassis_No) values (@Chassis_Search)
insert into @Data (Chassis, ItemC, Location, Panel, Part)
select distinct WorkPlacementorder.salesorderno, workplacementorder.itemcode, workplacementorder.location, workplacementorder.panel,
itembompn.partnumber1
from #Chas, workplacementorder
join itembompn
on workplacementorder.itemcode = itembompn.itemcode
inner join chassisdb.dbo.tblopenjit
on workplacementorder.salesorderno = chassisdb.dbo.tblopenjit.chassisnbr
where chassisdb.dbo.tblopenjit.chassisnbr like #Chas.Chassis_No and chassisdb.dbo.tblopenjit.itemid = itembompn.partnumber1
and WorkPlacementorder.salesorderno like #Chas.Chassis_NoSELECT b_d_Dashpanel.dbo.workplacementorder.ItemCode, b_d_Dashpanel.dbo.items.itemdescription
FROM #Chas, B_D_DashPanel.dbo.WorkPlacementOrder joinRemove [#Chas] from the entire query. Change your where statements to:
chassisdb.dbo.tblopenjit.chassisnbr LIKE @Chassis_Search
WorkPlacementorder.salesorderno LIKE @Chassis_Searchsalesorderno = @Chassis_Search
I question the last where clause - why isn't it also a LIKE? Seems like it would fail when wildcards are used.
-
Remove [#Chas] from the entire query. Change your where statements to:
chassisdb.dbo.tblopenjit.chassisnbr LIKE @Chassis_Search
WorkPlacementorder.salesorderno LIKE @Chassis_Searchsalesorderno = @Chassis_Search
I question the last where clause - why isn't it also a LIKE? Seems like it would fail when wildcards are used.
I've tried it both ways, with @chassis_search and using #Chas. The tried it because the other SP selects a large range of values into it and then runs the exact same from the first insert on out. It works, this one doesn't. I block wildcards on the software side so I'm not to concerned about wildcard entries. I'm used to visual studio using = when a parameter is required.