String concatenation has been around as long as SQL has. But different dialects have different syntax.
Oracle uses '||', Sql Server uses '+', most implementations can use 'concat(string1, string2)' but not SQL Server AFAIK. Could be wrong about that though.
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.
GO/****** Object: StoredProcedure [dbo].[sp_BD_PermConflicts] Script Date: 09/10/2012 15:55:45 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOALTERPROCEDURE [dbo].[sp_BD_PermConflicts_Chassis_Only]
ASDeclare @Data TABLE(
CreateTABLE #Chas (
set nocount oninsertinto #Chas (Chassis_No) values (@Chassis_Search)
insertinto @Data (Chassis, ItemC, Location, Panel, Part)
selectdistinct WorkPlacementorder.salesorderno, workplacementorder.itemcode, workplacementorder.location, workplacementorder.panel,
from #Chas, workplacementorder
on workplacementorder.itemcode = itembompn.itemcode
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_No
SELECT b_d_Dashpanel.dbo.workplacementorder.ItemCode, b_d_Dashpanel.dbo.items.itemdescription
FROM #Chas, B_D_DashPanel.dbo.WorkPlacementOrder join b_d_Dashpanel.dbo.items on b_d_Dashpanel.dbo.workplacementorder.itemcode = b_d_Dashpanel.dbo.items.itemcode
where salesorderno = Chassis_No and b_d_DashPanel.dbo.workplacementorder.itemcode notin (select itemc from @Data)
Logic dictates that there is a difference between @Chassis_Search passed in as a nvarchar and the format stored in the table. Can you do a select from itembompn where 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
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.
Yup. That was exactly what I was going to suggest. I ran into that problem where I had forgotten to close and dispose my SQL connections and the problem didn't show up until I had more people using the system.
We are using SqlHelper.cs so there is no need to open and close connection explicitly.
I think the problem is from "For Loop"
here im writing code to insert into table. at this point SqlHelper open and close connection 1000 times and im getting the above error, it will work fine upto 130 iteration without error.
Everything I have read says that operations within a stored procedure are executed in the order in which they are encountered, and the semi-colons and BEGIN-END blocks can be used to ensure that statements are executed before subsequent statements are performed.
However I am stumped by what is happening in my script. The intent of the script is to copy an order record and all associated table entries for the order from one database to another. I have stripped down the script code below to just the order table itself (and removed some passwords, server names etc).
Here is the code:
IF@@SERVERNAME <> 'Server1'BEGINEXEC SP_ADDLINKEDSERVER
'passwordX'ENDgoDECLARE @mode VARCHAR(1);
DECLARE @orderNum INT;
SET @orderNum = 1338464;
SET @mode = 'R'-- set to 'R' for Report or 'U' for update
BEGINTRANSACTIONDECLARE @sourceName VARCHAR(8);
DECLARE @okay CHAR(1);
DECLARE @user VARCHAR(20);
SET @sourceName = 'liveDB';
SET @user = SYSTEM_USER;
CREATE synonym sourcesummary FOR [Server1].[liveDB].[dbo].[order_summary];
SET @okay = 'Y'IF @okay = 'Y'AND@@SERVERNAME = 'Server1'BEGINSELECT'Cannot execute on live server - retry on the sever hosting the db that is to be copied to' [error]
SET @okay = 'N'ENDIF @okay = 'Y'ANDNOTEXISTS (SELECT order_num
WHERE order_num = @orderNum)
BEGINSELECT'Unable to find order' [error],@sourceName [source db],@orderNum [order number];
SET @okay = 'N';
ENDIF @okay = 'Y'BEGINIFEXISTS (SELECT order_num
WHERE ordr_num = @orderNum)
WHERE order_num = @orderNum;
SELECT [order_num],[order_date], etc, etc, etc...
WHERE sourcesummary.order_num = @orderNum;
ENDPRINT'retreiving copied order_summary:';
WHERE order_num = @orderNum;
ENDDROP synonym sourcesummary;
IF @mode = 'U'BEGINCOMMITTRANSACTIONSELECT'Changes have been applied to the database' [notice]
ENDELSEBEGINROLLBACKTRANSACTIONSELECT'All changes have been rolled back' [notice]
And here is the result from MSSQL's Messages window:
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server 'Server1' already exists.
(1 row(s) affected)
(1 row(s) affected)
retreiving copied order_summary:
(0 row(s) affected)
(1 row(s) affected)
The error at the top of the output (re Server1 already exists) I understand and that's okay.
However, notice that copying order_summary says that 1 row was affected, but the very next operation to display that row obtains no records. This is what I don't understand, and I can't move forward until I get this row to exist in the db.
Can anyone see some blatantly newbie faux-pas that is hidden to my blatantly newbie eyes?
my query is
<prelang="SQL">create proc sp_emprecord
select * from employe
error_message() as errormessage,
error_number() as erronumber,
error_state() as errorstate,
error_procedure() as errorprocedure,
error_line() as errorline;
Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Please stand in front of my pistol, smile and wait for the flash - JSOP 2012