|
Try the option, CONVERT(VARCHAR(10),'fieldname',112)
|
|
|
|
|
Try replying to the op next time, he will not get notification when you reply to me!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
try this :
DECLARE @DurationSeconds INT
DECLARE @DurationDays INT
SET @DurationSeconds = 62110
SET @DurationDays = @DurationSeconds /86400
Select CASE WHEN @DurationDays > 0 THEN
Convert(VarChar,@DurationDays)+ ':'+
Convert(VarChar, DateAdd(S, (@DurationSeconds-(@DurationDays*86400)),0), 108)
ELSE
Convert(VarChar, DateAdd(S, @DurationSeconds, 0), 108)
END
|
|
|
|
|
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
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_BD_PermConflicts_Chassis_Only]
@Chassis_Search nvarchar(50)
AS
Declare @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_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 not in (select itemc from @Data)
drop table #Chas
|
|
|
|
|
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
RAH
|
|
|
|
|
I would go one step further, don't prefix.
|
|
|
|
|
I still prefix views and functions I gave up on procs many versions ago.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Remove [#Chas] from the entire query. Change your where statements to:
chassisdb.dbo.tblopenjit.chassisnbr LIKE @Chassis_Search
WorkPlacementorder.salesorderno LIKE @Chassis_Search
salesorderno = @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.
|
|
|
|
|
I am getting following server error in asp.net application, even i mentioned Max Pool Size="100" in web.config file
Error : "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may occurred because all pooled connections were in use and max pool size was reached."
|
|
|
|
|
Are you closing and disposing your connections once you execute a query?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
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"
for(i=0;i<1000;i++)
{
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.
}
|
|
|
|
|
without seeing the code directly, I can't say for sure if that is your problem, but why don't you just code it by hand. (not using the helper).
The basic idea is:
Open a connection to the database
create a SQLcommand which takes parameters
Load the parameters
while loop(more data to load)
execute SQL command
load new values in paramters
end while
close and dispose of connection and command object
The idea is that you have made one connection to the database, created one command and loop around the command just changing the parameters.
Just a thought.
|
|
|
|
|
Hello everyone.
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'
BEGIN
EXEC SP_ADDLINKEDSERVER
'Server1'
EXEC SP_ADDLINKEDSRVLOGIN
'Server1',
'False',
NULL,
'userX',
'passwordX'
END
go
DECLARE @mode VARCHAR(1);
DECLARE @orderNum INT;
SET @orderNum = 1338464;
SET @mode = 'R' -- set to 'R' for Report or 'U' for update
BEGIN TRANSACTION
DECLARE @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'
BEGIN
SELECT 'Cannot execute on live server - retry on the sever hosting the db that is to be copied to' [error]
SET @okay = 'N'
END
IF @okay = 'Y'
AND NOT EXISTS (SELECT order_num
FROM sourcesummary
WHERE order_num = @orderNum)
BEGIN
SELECT 'Unable to find order' [error],@sourceName [source db],@orderNum [order number];
SET @okay = 'N';
END
IF @okay = 'Y'
BEGIN
IF EXISTS (SELECT order_num
FROM order_summary
WHERE ordr_num = @orderNum)
BEGIN
PRINT 'deleting order_summary:';
DELETE order_summary
WHERE order_num = @orderNum;
END
BEGIN
PRINT 'copying order_summary:';
INSERT INTO order_summary
SELECT [order_num],[order_date], etc, etc, etc...
FROM sourcesummary
WHERE sourcesummary.order_num = @orderNum;
END
PRINT 'retreiving copied order_summary:';
SELECT 'copied' [order_summary],*
FROM order_summary
WHERE order_num = @orderNum;
END
DROP synonym sourcesummary;
IF @mode = 'U'
BEGIN
COMMIT TRANSACTION
SELECT 'Changes have been applied to the database' [notice]
END
ELSE
BEGIN
ROLLBACK TRANSACTION
SELECT 'All changes have been rolled back' [notice]
END
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.
deleting order_summary:
(1 row(s) affected)
copying order_summary:
(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?
Please?
Clive Pottinger
Victoria, BC
modified 6-Sep-12 14:28pm.
|
|
|
|
|
Try committing the transaction after deleting and inserting the rows into the database, but before retrieving them, I'm assuming that's what causing the problem
Full-fledged Java/.NET lover, full-fledged PHP hater.
Full-fledged Google/Microsoft lover, full-fledged Apple hater.
Full-fledged Skype lover, full-fledged YM hater.
|
|
|
|
|
Or add the WITH(NOLOCK) hint.
I also recall others having similar trouble when using GO , but that doesn't appear to be the problem here.
|
|
|
|
|
Thank you gentlemen. I found the cause of the issue.
The section that reads
PRINT 'copying order_summary:';
INSERT INTO order_summary
SELECT [order_num],[order_date], etc, etc, etc...
FROM sourcesummary
WHERE sourcesummary.order_num = @orderNum;
should have been
PRINT 'copying order_summary:';
SET IDENTITY_INSERT order_summary ON;
INSERT INTO order_summary ([order_num],[order_date], etc, etc, etc...)
SELECT [order_num],[order_date], etc, etc, etc...
FROM sourcesummary
WHERE sourcesummary.order_num = @orderNum;
SET IDENTITY_INSERT order_summary OFF;
"... to become an SQL expert? Oh, I figure 3 or 4 days reading. A week tops!"
Clive Pottinger
Victoria, BC
modified 12-Sep-12 12:05pm.
|
|
|
|
|
|
my query is
<pre lang="SQL">create proc sp_emprecord
as begin
select * from employe
end
begin try
execute sp_emprecord
end try
begin catch
select
error_message() as errormessage,
error_number() as erronumber,
error_state() as errorstate,
error_procedure() as errorprocedure,
error_line() as errorline;
end catch</pre>
|
|
|
|
|
You have already asked this vague question in QA
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
|
|
|
|
|
Use Raiserror [^] with a security-level lower than 20.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
That's probably not worth the effort; just catch the Exception in the calling code.
And don't prefix your procedures with sp_ -- that's for System Procedures.
|
|
|
|
|
Good day,
I want to retrieve records from sql server 2005 that belongs to today,
my datatype in sqlserver is : datetime and store date like : 9/5/2012 12:55:26 PM
So i want to know how can I write a query to retrieve only today's records.
Thanks in advance
|
|
|
|
|
0) I hope you're using a DATETIME field and not storing dates as strings. Otherwise you're hosed.
1) If this is a recent version of SQL Server you can try WHERE datefield >= CAST(GETDATE() AS DATE)
modified 5-Sep-12 23:28pm.
|
|
|
|