|
You almost got it read BOl on datetime type and convert method. Blues reply actually gives you the answer but read BOL anyway.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use the DATENAME function, eg.
SELECT DATENAME(mm, GETDATE())
selects the name of the month in the culture of the server instance.
|
|
|
|
|
We have a number of stored procedures which process hundreds, sometimes thousands of records at a crunch. When these are called from our front-end data management app, we want to prevent the user from doing anything but wait; we also want to provide feedback so the user knows things are being done.
In our earlier front-end, written in VB6, this was done by calling RdoQueryObj.Execute rdAsynchEnable and polling RdoQueryObj.StillExecuting. Every one hundred records processed, the stored procedure would update an entry in a table, InterfaceCounter. In each iteration of the RdoQueryObj.StillExecuting loop, the front-end reads this entry and updates a display showing the user how many records have been processed. This worked great.
We are updating this VB6 app to VB.Net, using Visual Studio 2008. This process has been translated to get an IAsyncResult object from SqlCommand.BeginExecuteNonQuery and polling IAsyncResultObj.IsComplete. Inside the loop, the process remains essentially the same.
The problem is that the first time the stored procedure makes an entry into InterfaceCounter, IAsyncResultObj.IsComplete gets set to True. This passes control to the line SqlCommandObj.EndExecuteNonQuery(IAsyncResultObj) like it should. But since the stored procedure is not really finished, the application hangs and the user is left wondering what happened.
This is an example of one of the stored procedures. As you can see, it occurs as part of a cursor.
OPEN CURS1
FETCH CURS1 INTO @KEE
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE PROCESS @KEE
SET @COUNTER = @COUNTER + 1
IF @COUNTER % 100 = 0
BEGIN
UPDATE INTERFACECOUNTER
SET COUNTER = @COUNTER
WHERE JOBNAME = @JOBNAME
AND USERNAME = @USERNAME
END
FETCH CURS1 INTO @KEE
END I have verified that the UPDATE is, indeed, where IsComplete is being set.
This is the code I'm using in the application, trimmed down to the minimum needed to replicate the problem.
Dim Conn As SqlConnection = Nothing
Dim Cmd As SqlCommand = Nothing
Dim Result As IAsyncResult = Nothing
Dim Dream As New TimeSpan(0, 0, 1)
Conn = New SqlConnection(Db.ConnectionString)
Cmd = New SqlCommand
Cmd.Connection = Conn
Conn.Open()
Cmd.CommandText = CmdText
Cmd.CommandType = CommandType.StoredProcedure
Cmd.CommandTimeout = 1800
If SPParamList IsNot Nothing Then Cmd.Parameters.AddRange(SPParamList.ToArray)
Result = Cmd.BeginExecuteNonQuery()
Do While Not Result.IsCompleted
System.Threading.Thread.Sleep(Dream)
Debug.Write(".")
Loop
Cmd.EndExecuteNonQuery(Result)Additional information, in case anyone wants to know:
App: VB.Net 3.5
DB: MSSQL Server 2003
The connection string includes the flag Asynchronous Processing=true.
Again, the VB6 version is running just fine, same as it has for years. The problem exists only for the VB.Net version running on the same database.
Any suggestions?
Added: I also wanted to add that the line EXECUTE PROCESS @KEE in the SQL code above does a number of updates itself and executes a number of calls to other stored procedures which also do updates. Each time PROCESS is executed, at least a dozen updates get done. The problem occurs only in the update within the main cursor loop, after doing 100 records.
modified on Thursday, September 11, 2008 1:48 PM
|
|
|
|
|
I'll state upfront that I don't understand why it behaves as you describe and thus cannot really offer anything towards fixing the bug, if there is one. But I do think you can work around the problem by using multiple threads instead of asynchronous processes. By creating a worker thread (using the BackgroundWorker is an easy way to accomplish this - it uses an event-driven model with a DoWork event, allowing you to simply write the handler and it will then run on the background thread, and will notify you when the thread stops running) your application can avoid freezing and report progress as the procedure executes.
|
|
|
|
|
By the way, multiple threads are of course used with your current technique, and perhaps you do not need any async flag in your connection string - could be worth trying at least.
We make fairly heavy use of background threads to execute long-running queries in our product, though we create the threads explicitly and have implemented a polling system that makes it easy to run any code in the background and poll a business object for information. We have to use a polling model because it's a web application, ie. a client-server solution, and with this in place the reusable bit takes care of thread management (including queueing), generating polls from the client and performing a callback to a client-side function so we can update the user interface on the fly. We've had lots of bugs of course but they've always been logical and due to errors we made in our code, not the result of any quirks or bugs elsewhere.
|
|
|
|
|
As I said above, the call to EXECUTE PROCESS executes a number of other stored procedures. All of them, like the root procedure, have SET NOCOUNT ON as the first line of code and SET NOCOUNT OFF as the last line of code. Removing these two lines from the sub procedures fixed the problem.
As best we can figure out, the scope of the NOCOUNT flag is global to the original procedure and not to the current procedure, like you would think. The net result was that when the UPDATE in the original procedure was run, the NOCOUNT flag was off and the server obligingly provided feedback that one row had been updated. That was enough for the IAsynchResult object to consider the process done. 
So now, everything works just the way we want it to work.
|
|
|
|
|
I'm trying to count data in a table. I was able to do it, however, when a data exists more than once, it shows it again and counts it again
for example:
column_name Count
string 2
another string 1
same string 2
This is my query:
SELECT table_1,
(SELECT COUNT(column_1) FROM Records as t1 WHERE t1.column_1 = table_1.column_1) AS Count
FROM table_1
Can anyone suggest an approach that would solve such redundancy? Thanks!
|
|
|
|
|
try this
select distinct c1,count(c1) from table1 group by c1
Bob
Ashfield Consultants Ltd
|
|
|
|
|
oh shucks! stupid me! thanks! 
|
|
|
|
|
No problem 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Do use the distinct keyword as suggested by Mr. Ashfield
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Hi,
I have three jobs dependent on each other,ie i get my data in my application only if all the three jobs are executed.
The details of the three jobs are below.
1.It transfers the data from our client server to our server.
2.Process the data.
3.Loads the data in the cubes.
If i run the jobs manually then the jobs are executed successfully,but if i schedule the jobs then 1 or 3 fails.
I am not using any access permissions to run them manually or in scheduling.
So can any one tell the possible reasons for the failure of my job so that i can check it out.
|
|
|
|
|
You are going to have to find out why the jobs fail, we cannot help you there. If you have set the logging on the jobs then you can use that.
Presumably there are dependencies between the jobs (I thought you would need SSIS for that) and 2 cannot run until 1 is complete.
[edit]
So you did not like the answers you got last time you posted - idiot
When you run manually you use YOUR permissions
When the Job is run you use SQL Job service permissions - they are not the same, check it out.
Apply some logging
When you say you will get back with the details of the error DO THAT.
Bloody time wasting fool, can't take advise and reposts the same bloody question a few hours later, mutter, mutter
[/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
code1 code2 code3 code4
2233229 411.80 2233231 1
2233229 411.80 2233232 1
999708 298.20 960269 1
960268 210.50 999710 1
960268 210.50 999710 2
I want to get the the max of colonne code 2, the min of colonne code 3, the min of colonne 4
Ordred By Col 2 after the querry i want such result.
2233229 411.80 2233231 1
999708 298.20 960269 1
960268 210.50 999710 2
Coul anyone help?? thanks!!
|
|
|
|
|
If you get min of column4 (code4) then you have this result
2233229 411.80 2233231 1
960268 210.50 999710 1
999708 298.20 960269 1
but if you need result which u have post then you must find max of column4 (code4).
Query which gives result by getting min of column 4:
select code1, max(code2) as code2 ,min(code3) as code3,min(code4) as code4<br />
from mtable<br />
group by code1<br />
order by code1
Query which gives result by getting max of column 4:
select code1, max(code2) as code2 ,min(code3) as code3,max(code4) as code4<br />
from mtable<br />
group by code1<br />
order by code1
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hello,
i am using OLEDB (MFC - VS2005) to read data from Excel File. I used class CTables and CColumns to get respectivly Sheet and Columns datarows:
pTableset->Open(session, NULL, NULL, NULL, _T("TABLE"));
pColumnset->Open(session, NULL, NULL, pTableset->m_szName);
Now i would know how i can get Rows and how i could use
Open method to get Type Cell.
Thanks for your support.
Best Regards,
Carmine.
|
|
|
|
|
Hi,
I have written a query to extract data from a table whose created date lies between current date and 30 days ago.in PL/SQL
I have written query as follows:
select creat_dt from shpt
where shpt.creat_dt between trunc(sysdate) and trunc(sysdate-30)
Although it is having records for which created date falls between current date and 30 days prior to current date, it still shows me no records extracted.
Help me with this.
|
|
|
|
|
Don't know about Oracle, but in SQL Server that wouldn't work, you have to put the earliest date first.
where shpt.creat_dt between trunc(sysdate-30) and trunc(sysdate)
It's not logical, but thats how it works in SQL Server and I suspect Oracle!
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Yap, this applies to Oracle also.
Based on ANSI standard between is converted to:
Field >= StartValue AND Field <= EndValue
Logical, isn't it
|
|
|
|
|
hi,
try swap the dates
between 20-10-2000 and 25-10-2001
between 25-10-2001 and 20-10-2000
it has to work no reason for otherwise
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi friends,
When i schedule my jobs they fail after executing for few seconds,but when i execute the same job manually its executed.I am unable to find the reason ..can any one help..
Regards,
Annu
|
|
|
|
|
User permissions?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
no...i don't hv any thing like user permission..
Actually i am new to SQL and Database..
So any thing else i am missing out...????
|
|
|
|
|
When you run it its running with your security settings and permissions, when it runs as a scheduled job it isn't. So, does the account the scheduler is running under have the necessary pemissions in the database to perform what ever it is your sql does?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
No we dont hv any security settings and permission.when run manually or scheduled.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
