|
sorry ....i was read that hidden part thats Y replied again....i 'll correct it..
thanks
Rajendran
|
|
|
|
|
R a jendran wrote: Y
That is the penutimate letter of the modern English alphabet. It is not a word per se.
|
|
|
|
|
Ok
This is work fine
select DATENAME(MONTH,GETDATE()) + '/' + Cast(DatePart(year,getdate()) as varchar)
Have nice time
|
|
|
|
|
Thanks for reply...already senu gandhi sent me that solution...I solved that problem ...
Thanks..
Rajendran
|
|
|
|
|
Hi,
I have a problem with a sql-Statement. I’m developing an application in which a user can type in a sql-statement, e.g. "Select * from (select name from employee)" . This statement is not very reasonable but it is syntactically correct.
My code is
OracleCommand cmd = new OracleCommand("Select * from (select name from employee)", myOracleConnection);
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
System.Data.DataTable colinfo = rdr.GetSchemaTable();
At this staement the application crashed with an exception: "InvalidOperationException: Identifier expected or identifier in quotes expected" . (The message is in germaan: "Syntaxfehler: Erwartet wird ein Bezeichner bzw. Bezeichner in Anführungszeichen.", but i try to tranlate it in english)
I want to use GetSchemaTable to get all the columninformation I needed to display the result. So how can I solve the problem ?
Thanks in advances
Willibert
|
|
|
|
|
Does it work if you specify a table alias:
Select * from (select name from employee) a
That should make the SQL legal.
|
|
|
|
|
Hi,
Select * from (select name from employee) is a legal statement, OracleClient and Toad could execute it.
I could by-pass it, I change
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);
into
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
But their I don't get the basistablename even in a normal select.
Tanks
Willibert
|
|
|
|
|
Hi
My requirement is i hve 4 tables
Specmast(specification,wgt,min)
specgen(specification,wgt,min)
specem(specification,wgt,min,em)
specdes(specification,wgt,min,desg).
All the specification i hve stored in the first table. Now what i want is in specgen i hve to get those specification details from specmast which are not in specem and specdes. How can i give query for this
Pls help me this is very important
Thanks in advance
Regards
Micydon
|
|
|
|
|
Select * from Specmast where specification not in(select specification from specem,specdes)
u will try this query....
Rajendran
|
|
|
|
|
Micydon ,
Select specification,wgt,min
from Specmast
Where specification not in (Select e.specification
from specem e Inner join specdes d on
e.specification = d.specification )
This will work
Senthil
|
|
|
|
|
I am working on .Net 2.0 and SQL 2000.
While running my application, during dataset fill I get the following error
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"
I tried setting connection timeout to 0 and command timeout to 0.
This is the code:
public DataSet FillDatasetSQL(string strQuery)
{
try
{
//-----Connection open
sqlConn = new SqlConnection(strSqlConn);
sqlConn.Open();
//-----Fill Dataset
DataSet ds = new DataSet();
sqlAdap = new SqlDataAdapter(strQuery, sqlConn);
sqlAdap.Fill(ds, "Tablename"); <=========== error occurs here
//-----Connection close
sqlConn.Close();
sqlConn.Dispose();
return ds;
}
catch(Exception ex)
{
if (sqlConn.State == ConnectionState.Open) sqlConn.Close(); sqlConn.Dispose();
throw ex;
}
}
Please help
Senthil
|
|
|
|
|
How and where did you try set the command timeout?
Did you try set it in the connection string or did you do something like:
sqlAdap = new SqlDataAdapter(strQuery, sqlConn);<br />
sqlAdap.SelectCommand.CommandTimeout = 120;<br />
sqlAdap.Fill(ds, "Tablename");
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
|
|
|
|
|
Thanks for your reply.
You are right. I too tried the same what u said.
But it doesn't work out well.
Senthil
|
|
|
|
|
How much data are you trying to bring back?
It might be that the sql statement is running fine but it's timing out trying to fill the dataset.
If you run the SQL statement in Query Analyser or Management Studio, how many rows do you get back and how big (data wise) are the rows?
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
|
|
|
|
|
I think no need to set connection timeout=0 and Cammand timeout=0..
plz check the connection string and type the following code in web.config
<customerrors mode="Off">
<compilation debug="true"> not sure..try this...
Rajendran
|
|
|
|
|
better give commandtimeout=100(some avlue) it will work
Rajendran
|
|
|
|
|
Thanks for your reply.
I had tried this already. but no use.
One more information
I am using 2 connections
1 for Insert / Update / Delete with transaction
1 for Select without transaction
I suspect whether it is a deadlock
Senthil
|
|
|
|
|
I just want to double check with you that you have done everything you need to do.
So here is an example from my own code that works:
oconn.ConnectionTimeout = 0
oconn.CommandTimeout = 0
oconn.Open "Driver={SQL Server};Server=Hal2000;Database=Europa;Uid=sa; Pwd= :P ;timeout=0;"
</code>
One final thing do you need to set up an ODBC connection on the computer, and if you do have you set this up?
You always pass failure on the way to success.
|
|
|
|
|
Hi there,
I have a two SQL server databases (one 2000 and the other 2005) linked to each other.
I need to run a stored procedure on the 2005 DB that will access data from both databases and UNION them into one table. I can select data from the 2000 DB's tables from the 2005 machine so I know the linking is correct. I've also sorted out collations and enabled RPC calls on both DB's.
The problem is I cant seem to call a udf on the 2000 machine from the 2005 machine. Here's a section of the stored proc. (I've changed the database name in this sample to LINKEDDB)
SELECT
QueueGroupName COLLATE Latin1_General_CI_AS,
dbo.udfQueueGroupAnsweredCalls(@StartDate, @EndDate, QueueGroupName) AS AnsweredCalls,
dbo.udfQueueGroupLostCalls(@StartDate, @EndDate, QueueGroupName) AS LostCalls
FROM
QueueGroup
UNION
SELECT
QueueGroupName COLLATE Latin1_General_CI_AS,
LINKEDDB.idb.dbo.udfQueueGroupAnsweredCalls(@StartDate, @EndDate, QueueGroupName) AS AnsweredCalls,
LINKEDDB.idb.dbo.udfQueueGroupLostCalls(@StartDate, @EndDate, QueueGroupName) AS LostCalls
FROM
LINKEDDB.idb.dbo.QueueGroup
ORDER BY
QueueGroupName COLLATE Latin1_General_CI_AS
It's giving me the error: Invalid column name 'LINKEDDB' when I try call the UDF. Anybody got any ideas?
Dad always thought laughter was the best medicine, which I guess is why several of us died of tuberculosis.
I can picture in my mind a world without war, a world without hate. And I can picture us attacking that world, because they'd never expect it.
|
|
|
|
|
Can you configure a linked-server connection to your SQL Server 2000 box, then use OPENQUERY to run a remote query?
|
|
|
|
|
hi,
I am learning stage of cursor.here date format is displaying like 2007-06-22 01:54:02.000. I am Struggling for getting normal date format like 11/12/2005...Plz See the following Code and reply me
ALTER procedure [dbo].[WCReport] as
begin
create table #temp21
(
Ason datetime ,SubScriberEmail varchar(50),SvcActivationDate datetime,
PkgActivationDate datetime,RenewalDate datetime
)
DECLARE @cursorVar CURSOR
SET @cursorVar = CURSOR Forward_Only FOR
SELECT regdate,email,wct.dt
from wc_transaction as wct,wc_user as wcu WHERE wct.PID=wcu.ID
--open the cursor
OPEN @cursorVar
DECLARE @regdate datetime, @email varchar(50),@dt datetime
--fetches the current row, which is the first
FETCH FROM @cursorVar INTO @regdate,@email,@dt
WHILE @@fetch_status = 0
BEGIN
insert into #TEMP21(Ason,SubscriberEmail,SvcActivationDate,PkgActivationDate,RenewalDate) VALUES(@regdate,@email,@regdate,@regdate,@dt)
FETCH Next FROM @cursorVar INTO @regdate,@email,@dt
END
begin
select * from #temp21
return end
CLOSE @cursorVar
DEALLOCATE @cursorVar
end
exec WCReport
Rajendran
|
|
|
|
|
Instead of using select * from #temp21 specify the each column name and in the date column use convert function. now your select query will be like this.
select convert(varchar,Ason,101),SubScriberEmailfrom,<br />
convert(varchar,SvcActivationDate,101),convert(varchar,PkgActivationDate,101),<br />
convert(varchar,RenewalDate,101) from #temp21
|
|
|
|
|
Hi,
I have used your query Its working fine....Thanks alot.
I have another doubt....actually I am generating reports using this cursor.here I have to give dublicate column name like Status(if condition is true)..So I want to know how to provide an alias name for that Status(Status is not their in Database)....If u know plz reply me...
thanks
Rajendran
|
|
|
|
|
Hi
Try something like
SELECT
CASE WHEN Dt < '2006-12-31' THEN 'Archived'
WHEN Dt < '2007-06-30' THEN 'OLD'
ELSE 'New' END AS [Status]
FROM MyTable
Nav
|
|
|
|
|
Thanks alot....its working ..
Rajendran
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
