|
Member 10263519 wrote: all data type varchar(50)
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi Everyone!
All of a sudden we are seeing the above error message, in one of our automated jobs. This job runs at 3am everyday.
This error occurs on random days, not on a regular basis.
There is not much out on this particular error.
The temp table is defined in a stored procedure.
Thanks!!!
|
|
|
|
|
Not enough information to diagnose.
|
|
|
|
|
vkEE wrote: All of a sudden we are seeing the above error message, in one of our automated jobs. I've never encountered a PC that "suddenly" interprets code in a different way.
vkEE wrote: This error occurs on random days, not on a regular basis. ..nor does it pick random days to crash. Find out what those days have in common; check if there's exceptional heavy traffic on the network during those days. Check your firewall-log. Check the schedule of your backups, automatic updates and virusscans; are they similarly "random"?
Are you sure you got the correct type of temporary table? There's subtle differences between #tmpTable, ##tmpTable and @tmpTable.
vkEE wrote: The temp table is defined in a stored procedure. Post the code of the stored procedure and you'll get more and better replies.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you for your response.
The job runs at 3am just for that reason, so we don't have resource issues. It has crashed 3 times the past few months, but our seniors want this matter researched since it is a new bug in their test environment. They don't want this bug to migrate to production environment.
I got this error message of the log file, and it is defined with single hash, #.
|
|
|
|
|
vkEE wrote: <layer>our seniors want this matter researched since it is a new bug in their test environment. They don't want this bug to migrate to production environment. Unless you are in full control of your environment and able to debug every device-driver, some bugs will remain unsolved.
vkEE wrote: It has crashed 3 times the past few months Do you know at what time? Anything in the event-log at that specific time? Does it run under .NET 4? If yes, you could consider making a minidump.
vkEE wrote: and it is defined with single hash, #. I was hinting at researching the three. Whether it's the correct one will depend on the context of it's use in the stored procedure. You will have to verify the things mentioned, and again, you'd get a more usefull answer if you provide the code for the stored procedure.
There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server. ..and it becomes more confusing[^] if you are EXECUTE ing dynamic SQL.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
vkEE wrote: The temp table is defined in a stored procedure.
You have an execution path that is skipping the creation of the table but then attempting to use it.
|
|
|
|
|
Hi all,
I have a little confusion about creating index on Oracle, which is the difference between creating index online and creating them in not an online way (offline).
I looked it on internet and I did not find sth straight about it but I guess that :
Online : The RDBMS will create the Index without using table locks (for DML statements when creating the index).
Offline : create with the use of tables lock.
Am I correct ?
Thank you.
"The Ultimate Limit Is Only Your Imagination."
|
|
|
|
|
Yes.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
when i wanna to get script from my DB in sql server management 2008 i get this error :
There is already an open DataReader associated with this Command which must be closed first.....what is meann of this error
note :
1.even i stop start the instance.
2.copy the DB to other system also but same error !
thanks in advnaced !
|
|
|
|
|
This is not a sql error, it is in your code. You are opening a second connection before closing the first connection either using async calls or creating a new instance of your DAL class.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You are opening a second connection before closing the first connection
A second connection wouldn't cause a problem. It's opening a second DataReader on the same connection which causes this error.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You are trying to open two DataReader instances (by calling ExecuteReader ) on the same connection at the same time. You need to make sure that every DataReader is closed properly by wrapping it in a using block, and that you close one before trying to open another.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You can if you're using multiple Active Result Sets[^] on an SQLServer, and on Oracle you can anyway[^].
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
One of my Stored Procedures was using ۀ in its name and when i am getting script it was giving error!
|
|
|
|
|
I wrote a Stored Procedure that retrieves parameters from my dropdown list and a txtbox that I use for my where like % clause
I got everything to work except for the 'Where like clause' It looks like it should work but obviously it doesn't. Any help would be appreciated.
My Code:
ALTER PROCEDURE [dbo].[SearchEmpRecords_Sp2]
@SearchBy varchar(50) = '',
@SearchVal varchar(50) = ''
AS
BEGIN
DECLARE <a href="/Members/value">@Value</a> varchar(50)
SET @SearchBy = @SearchBy + ','
IF REPLACE (@SearchBy, ',', 'ALL') <> ''
BEGIN
SET <a href="/Members/value">@Value</a> = (@SearchBy)
IF <a href="/Members/value">@Value</a> <> ''
BEGIN
SELECT EmployeeID,Username, State, AreaNumber FROM Employees
WHERE
*** The line below is what I'm trying to get to work.
*** Whenever I ran this line I get my custom error saying:
Records Not Found
-- <a href="/Members/value">@Value</a> LIKE '%' + @SearchVal + '%' ----
*** These lines work but I would like to replace them with the line above ***
Username LIKE '%' + @SearchVal + '%'
OR State LIKE '%' + @SearchVal + '%'
OR AreaNumber LIKE '%' + @SearchVal + '%'
END
END
END
|
|
|
|
|
Should we assume SQL Server?
That sort of thing requires a string variable and EXECUTE :
DECLARE @sql VARCHAR(MAX)
SET @sql = blah blah blah
EXECUTE ( @sql )
(Don't leave off the parentheses.)
|
|
|
|
|
Thanks for your help but, I don't know what to put in blah blah blah. I'm very new at this. The only thing in my code that doesn't work is the 'Where Like clause' If you could give me more details I would appreciate it. Thanks
|
|
|
|
|
Commish13 wrote: but, I don't know what to put in blah blah blah.
Construct your statement, manually on a piece of paper, as it would look like if your proc was called and it ran. So for example
select column1 from mytable where name like '%acme%'
Then in the above in your stored proc you want to create that as a string. So you must concatenate the values together. (Following is an EXAMPLE and not what you need specifically.)
set @var = 'select column1 from mytable where name like ''%' +@likeValue + '%''';
The multiple ticks in the above is intentional as it tells the database that you actually want a tick in your string.
|
|
|
|
|
Ok. I'm trying to replace the Username or State or AreaNumber with 1 of those values when selected from web form. I got the ''%' +likeValue + '%''' part to work. But I would like to do the same thing for the three options via: Username,State or AreaNumber. In my code above I'm using @Value because I think it's holding the value when selected from web form. But when I run my program I get my custom message that 'No records Found' Please refer to my example code above for reference.
Thanks for your help
|
|
|
|
|
I don't understand your comment.
If you have 3 options for the where clause then you must construct three concatenation expressions.
If you are doing that and get no results then the data that you passed doesn't match anything.
|
|
|
|
|
Create a variable to place your whole statement in - when I say whole I mean whole as in everything that is going to be executed.
Stick a print statement in there to show the contents of the variable just before the query is run.
Run the SP and look at what is returned in the print statement.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Commish13 wrote: SET @SearchBy = @SearchBy + ','
IF REPLACE (@SearchBy, ',', 'ALL') <> ''
what is the purpose of above statement and a condition?
|
|
|
|
|
For What the constraints Folder present above triggers folder in every table in sql server??
What kind of data will it store??
|
|
|
|
|
Do you know anything about SQL server? Those things are not folders and they do not store anything!
Please read a basic introduction to databases!
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|