|
Thank you very much Gauthee, 
|
|
|
|
|
Hi everyone,
I have a relatively simple stored procedure on a MS-SQL server 2005. It runs fine if I execute it directly, but ends up timing out after the default 30 seconds from C#. I am running various other sprocs from my code in exactly the same way with no problems.
Can anyone think off the top of their head why this may be happening? I will post the code later as this will obviously help...
The only special thing about this is it has a 'nested select' ie:
SELECT * FROM table1 t1
WHERE t1.ColumnA > (SELECT ColumnC FROM table2 WHERE ColumnD = t1.ColumnB)
or seomthing to that effect (I dont expect the above code to parse)
Thanks in advance.
|
|
|
|
|
here is the code for this stored procedure. Can anyone see anything wrong with it?
select exchange_id, trade_date, trade_time,
short_name, code_buy,
capacity_buy, code_sell,
capacity_sell, trade_price,
trade_size, trade_type,
cancelled, quarter_percent_true
from trades t, reports r
where trade_price >
(select 1.02 * AVG(trade_price)
from trades
where isin = t.isin and trade_date = @trade_date
group by isin)
and trade_date = @trade_date
and t.isin = r.ti_code
and r.case_owner = @case_owner
and r.run_rylat = 'YES'
and t.cancelled = 'N'
order by short_name, trade_price DESC
|
|
|
|
|
Hi All:
I was trying to Used Distributed Transaction with Thread to allow many
Inserting operations into Different Databases in Different Server at the same
time
My Question is can we applying the Distributed Transaction and Threading
Together.
I try to apply it But the folowing Problem appeares through Open the first
Database Connection
An unhandled exception of type 'System.Runtime.InteropServices.COMException'
occurred in mscorlib.dll
Additional information: The component is configured to use synchronization
and this method call would cause a deadlock to occur.
' Some oF My The Code
Private strDBQA As String = "Data Source=QASRV; Initial Catalog=employee;
Password=sasa; User ID=sa"
Private strDBFA As String = "Data Source=FADI; Initial Catalog=Employee;
Password=sasa; User ID=sa" 'Enlist =false"
Private _Itransaction As System.EnterpriseServices.ITransaction
Private _boolQ As Boolean = False
Private _boolF As Boolean = False
Public ReadOnly Property PITransaction()
Get
Return _Itransaction
End Get
End Property
'The Main Function
Function TestThread(ByRef strError As String)
Try
_Itransaction = System.EnterpriseServices.ContextUtil.Transaction
Dim objThread As System.Threading.Thread
objThread = New System.Threading.Thread(AddressOf QServer)
objThread.Start()
objThread = New System.Threading.Thread(AddressOf Fserver)
objThread.Start()
If _boolF = True AndAlso _boolQ = True Then
ContextUtil.SetComplete()
strError = "Completed"
End If
Catch ex As Exception
ContextUtil.SetAbort()
strError = ex.Message
Catch ex As Exception
End Try
Private Sub QServer()
Try
Dim oConnectionQ As New SqlClient.SqlConnection
Dim cmdSql As New SqlClient.SqlCommand
oConnectionQ .ConnectionString = strDBQA
oConnectionQ .Open()
oConnectionQ .EnlistDistributedTransaction(PITransaction)
cmdSql.Connection = oConnectionQ
cmdSql.CommandText = "update Test set ID = ID + 10"
cmdSql.ExecuteNonQuery()
_boolQ = True
If _boolF = True AndAlso _boolQ = True Then
ContextUtil.SetComplete()
End If
Catch ex As Exception
ContextUtil.SetAbort()
End Try
End Sub
Private Sub Fserver()
Try
Dim oConnectionF As New SqlClient.SqlConnection
Dim cmdSql2 As New SqlClient.SqlCommand
oConnectionF .ConnectionString = strDBFA
'================
' in the strF oPen The Error Appear
oConnectionF .Open()
oConnectionF .EnlistDistributedTransaction(PITransaction)
cmdSql2.Connection = oConnectionF
cmdSql2.CommandText = "update Test set ID = ID + 10"
cmdSql2.ExecuteNonQuery()
_boolF = True
If _boolF = True AndAlso _boolQ = True Then
ContextUtil.SetComplete()
End If
Catch ex As Exception
ContextUtil.SetAbort()
End Try
End Sub
F.mathkour
|
|
|
|
|
Do not cross-post!
__________________________
Don't drink and derive.
Alcohol and calculus don't mix.
|
|
|
|
|
Hi, how can i autoincrement my primary key type id in mssql 2000 server?
To do this what should i need to set in enterprise manager of 2000 server. Thanks for advance help.
|
|
|
|
|
Make it an identity column. To do this, select the Identity property on the column and set it to true. The identity seed will default to 1 and the increment defaults to 1.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Fine and great. Thanks a lot.
|
|
|
|
|
My pleasure.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Select Statment:
Select * From Table
where fieldId = ?parameter(fieldId)
I keep trying to use @fieldid but I get a token unknown error.
I am using Microsoft Reporting Services and I have a datasource/data connection(ODBC) to a Firebird Database. I am unable to parse the Input parameter before the statement is sent to Firebird.
I can use Select * from Table
and I can populate output but when I declare an (@) input parameter in the query string (Text Command Type) I receive the Token uknown error (aforementioned problem)
Also, I tried to use Firebird : for parameter declaration but that didn't work because Microsoft Reporting Services doesn't recognize that.
Any Suggestions or solutions?
-- modified at 10:57 Tuesday 25th September, 2007
|
|
|
|
|
With Access?
Name the parameter @fieldId and use
Select * From Table where fieldid = @fieldid
|
|
|
|
|
I'm not using Access. I modified my problem and I listed more specifics.
|
|
|
|
|
Never used Firebird... but being in Phoenix, maybe I should? 
|
|
|
|
|
This was a company decision. I would much rather use Microsoft SQL 2005. I have searched the web with a comb and I can't find anything that relates to this type of problem. I was hoping someone has experienced this problem. It's such a simple but complex issue.
|
|
|
|
|
PIEBALDconsult wrote: Firebird...
It's okay. I've used it on a few projects in the past.
"Try asking what you want to know, rather than asking a question whose answer you know." - Christian Graus
|
|
|
|
|
I thought that "@param" named-parameters were only supported by the SQL-Server provider. Have you tried using a "?" positional-parameters in your command text?
|
|
|
|
|
Other databases do too, even Access/Jet.
|
|
|
|
|
Thanks so much. The ? worked.
|
|
|
|
|
I got select first_name from employee where emp_no=@Param0 to work as expected using the Firebird .net connector. (ODBC is so '80s.)
Thanks for giving me the opportunity add support for another database to my library.
|
|
|
|
|
Ha, anything that I can do to strike up some nostalgia.
|
|
|
|
|
Fortunately I got to skip ODBC; went from embedded SQL to ADO.net
|
|
|
|
|
I am trying to overcome the quote-problem with Unicode to Iso-8859-1
In My C# programm I add values from a ListBox to a string like
12, 45 etc.
Now my Sqlparameter should see only 12, 45. Sql Profiler Says '12, 45'.
How can I get rid off these quotes (') ?
I tried by adding quotes during the process in C#
so the string will be 19', '41 which is translated ti SqlServer 2000 in:
exec SP_GET_ConsolidatedReportMonthYears @ProgrammeID = '19'', ''41'
Why does an added Quote be doubled ?
Searching the internet shows me
http://codeedoc.com/c/hot-to-convert-string-encoding-for-database-use with the next code
<br />
public static string Iso8859_To_Unicode(string input) {<br />
Encoding iso = Encoding.GetEncoding("iso8859-1″);<br />
Encoding unicode = Encoding.UTF8;<br />
byte[] isoBytes = iso.GetBytes(input);<br />
return unicode.GetString(isoBytes);<br />
}<br />
public static string Unicode_To_Iso8859(string input) {<br />
Encoding iso = Encoding.GetEncoding("iso8859-1″);<br />
Encoding unicode = Encoding.UTF8;<br />
byte[] unicodeBytes = unicode.GetBytes(input);<br />
return iso.GetString(unicodeBytes);<br />
}<br />
Even that doesn't help me.
Sql Profiler still shows
exec SP_GET_ConsolidatedReportMonthYears @ProgrammeID = '19'', ''41'
how to avoid the '' issue? (and just have a single-quote)
-- modified at 9:12 Tuesday 25th September, 2007
|
|
|
|
|
spooky manus wrote: Now my Sqlparameter should see only 12, 45. Sql Profiler Says '12, 45'.
I don't know why you think that should be so. String values are always delimited with single quotes.
perhaps if you actually showed the code that constructs the SqlParameter from the listbox, someone might be able to help.
|
|
|
|
|
@ your service
<code>
if (SyconosBase.ListBoxHasSelectedItems(lbProgramme))
{
ddlReportMonthYear.Enabled = false;
btnRunReport.Enabled = false;
Char quote = (char)0039;
Char komma = (char)0044;
String Programmes = String.Empty;
Int16 AantalSelecties = 0;
for (int listItem = 0; listItem < lbProgramme.Items.Count; listItem++)
{
if (lbProgramme.Items[listItem].Selected)
{
AantalSelecties++;
if (!Programmes.Equals(String.Empty))
{
Programmes += quote;
Programmes += komma;
Programmes += quote;
}
Programmes += lbProgramme.Items[listItem].Value ;
}
}
LoadReportMonthYear(Programmes);
}
</code>
Method LoadReportMonthYear:
<code>
SqlParameter[] arrParameter = new SqlParameter[1];
arrParameter[0] = new SqlParameter("@ProgrammeID", SyconosBase.Unicode_To_Iso8859(Programmes));
</code>
|
|
|
|
|
Pls can someone tell me how to retrieve the correct time from an sql server and pass the value into a string to be used in an application.
IT'S GREAT TO BE HERE
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
