Database

Problem with GetSchemaTable

30-Oct-07 2:18

Thanks for reply...already senu gandhi sent me that solution...I solved that problem ...
Thanks..

Rajendran

stofel30-Oct-07 0:22

stofel

30-Oct-07 0:22

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

Re: Problem with GetSchemaTable

andyharman30-Oct-07 7:21

andyharman

30-Oct-07 7:21

Does it work if you specify a table alias:
Select * from (select name from employee) a
That should make the SQL legal.

Re: Problem with GetSchemaTable

stofel30-Oct-07 21:50

stofel

30-Oct-07 21:50

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

Need query

micydon30-Oct-07 0:01

micydon

30-Oct-07 0:01

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

Re: Need query

r a j u u30-Oct-07 0:24

30-Oct-07 0:24

Select * from Specmast where specification not in(select specification from specem,specdes)
u will try this query....

Rajendran

Re: Need query

Senu Gandhi30-Oct-07 0:32

30-Oct-07 0:32

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

Timeout expired

Senu Gandhi29-Oct-07 23:36

29-Oct-07 23:36

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

Nic Rowan29-Oct-07 23:57

Nic Rowan

29-Oct-07 23:57

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.

Senu Gandhi30-Oct-07 0:23

30-Oct-07 0:23

Thanks for your reply.

You are right. I too tried the same what u said.
But it doesn't work out well.

Senthil

Nic Rowan30-Oct-07 0:29

Nic Rowan

30-Oct-07 0:29

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?

r a j u u30-Oct-07 0:08

30-Oct-07 0:08

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

r a j u u30-Oct-07 0:10

30-Oct-07 0:10

better give commandtimeout=100(some avlue) it will work

Rajendran

Senu Gandhi30-Oct-07 0:26

30-Oct-07 0:26

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

GuyThiebaut30-Oct-07 3:36

GuyThiebaut

30-Oct-07 3:36

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.

SQL UDF Remoting

Nic Rowan29-Oct-07 23:02

Nic Rowan

29-Oct-07 23:02

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?

Re: SQL UDF Remoting

andyharman30-Oct-07 7:15

andyharman

30-Oct-07 7:15

Can you configure a linked-server connection to your SQL Server 2000 box, then use OPENQUERY to run a remote query?

Cursor_sql server 2005

r a j u u29-Oct-07 21:51

29-Oct-07 21:51

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

neeraj_indianic29-Oct-07 22:33

neeraj_indianic

29-Oct-07 22:33

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

r a j u u29-Oct-07 23:30

29-Oct-07 23:30

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

Navdeep Bhardwaj30-Oct-07 0:10

Navdeep Bhardwaj

30-Oct-07 0:10

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

r a j u u30-Oct-07 0:30