Re: Optimizing crystal report - Database Discussion Boards

Pradip Kishore21-May-07 1:36

21-May-07 1:36

hi all
i am having a stored procedure in my database which will take 2 parameters and return a resultset which i want to display in a crystal report.when i am executing the store proc. alone with the paramaters in the query analyser of sql server 2000,it is taking around 2.5 min.but when i pass the parameters from a vb.net page page,it z showing an error:{"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}

please help me out to solve this problm..

thanks in advance

i am Pradip Kishore

Re: Optimizing crystal report

kubben21-May-07 2:32

kubben

21-May-07 2:32

The command timeout defaults to like 30 seconds, so you would need to change this so you don't get a timeout error. There is a property off of the sqlCommand object called CommandTimeout. It is in seconds.

Hope that helps.
Ben

Re: Optimizing crystal report

Paul Conrad21-May-07 5:14

Paul Conrad

21-May-07 5:14

Have you looked at optimizing the stored procedure?

Re: Optimizing crystal report

Pradip Kishore21-May-07 22:04

Pradip Kishore

21-May-07 22:04

hi
thanks for your response

IS there any way to optimize a stored procedure for quick data retrival..

please help me

thanks in advance..

i m pradip kishore

Re: Optimizing crystal report

Paul Conrad22-May-07 5:37

Paul Conrad

22-May-07 5:37

Pradip Kishore wrote:
IS there any way to optimize a stored procedure for quick data retrival..

In short, you need to look at where bottlenecks are. Without having any idea how your database is set up, I can't really help.

Any queries you have in the stored proc, analyze it through query analyzer...

Paul

Re: Optimizing crystal report

Dave Kreskowiak21-May-07 6:45

Dave Kreskowiak

21-May-07 6:45

Pradip Kishore wrote:
but when i pass the parameters from a vb.net page page,it z showing an error:{"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}

The default CommandTimeout for an SqlCommand object is 30 seconds. If your query doesn't return a result set in that time, you get this error.

Either you have to optimize the stored procedure and/or table structures (indexes!), and/or up the timeout in the SqlCommand object.

If it's taking 2.5 minutes to return a set, are you sure that this is as fast as your query can execute?

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: Optimizing crystal report

Pradip Kishore21-May-07 22:10

Pradip Kishore

21-May-07 22:10

Hi
thanks for your response..

but can you show me some sample code or any links by which i can be able to do this..?

thanks in advance..

pradip kishore

Re: Optimizing crystal report

Dave Kreskowiak22-May-07 12:04

Dave Kreskowiak

22-May-07 12:04

I already gave it to you. The CommandTimeout property of your SqlCommand objects you created!

As far as optimization goes. That's done with experience and trial and error. There is no code that will just "do it for you". You have to understand precisely how SQL Server works, how Indexes work and why. What are the consequences of using them and overusing them. And on and on and on...

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

SQL Server Self referencing looping through

K.P.Kannan20-May-07 22:02

K.P.Kannan

20-May-07 22:02

We have a table which is a self referencing. The childs may go up to any level.

For eg: PID SubPID
1 3
3 4

We are using cursors to get the ProjectIDs and loop through to 'n' level. Is there any optimized way to improve the performance or is there any keyword like connect by prior as in Oracle

Thanks,
K.P.Kannan

Re: SQL Server Self referencing looping through

Harini N K20-May-07 22:24

Harini N K

20-May-07 22:24

Hi
I think this is possible in SQL 2005 (Yukon) but if you are using SQL 2000. Then check this link

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm[^]

Harini

Re: SQL Server Self referencing looping through

K.P.Kannan20-May-07 22:51

K.P.Kannan

20-May-07 22:51

Thanks. The link was quiet useful. I am trying to implement that

SSIS Package trouble

steffw20-May-07 21:28

steffw

20-May-07 21:28

Hello everyone,

Lately i'm working on a SSIS Package that downloads some data from one database to anohter.
Their is one problem, when i run the package their is nothing wrong. When i try to put it in an job it fails. I've checked the logs but they don't give me some usefull information, the only thin that i've got is: Job failed at step 'my package name'.

I've also checked the permissions, their good (i think) and when i do a connection test in visual studio their is nothing wrong with both connections. When i execute the package in visual studio, nothing fails and the data is pumped over.

Does anyone have an idea about this?

Stef

Opening SQL Enterprise manager

tasnim520-May-07 20:49

tasnim5

20-May-07 20:49

Hi,

I want to open SQL Enterprise Manager from RUN command in Start Menu. I can open it by creating short cut and then after giving a name i can put it in system32 folder. But i want to know that is there any command to open it from from Run command.

Can some one tell the command to open it.?

Thank you.

Ahmed

Re: Opening SQL Enterprise manager

blackjack215020-May-07 21:05

blackjack2150

20-May-07 21:05

If you can create a shortcut, then do Right Click -> Properties on the shortcut icon. Inside the target textbox you'll find the path to the exe file.

Re: Opening SQL Enterprise manager

tasnim520-May-07 23:21

tasnim5

20-May-07 23:21

Hi,

Thank you for replying. I can do it. But i want to know is there any shortcut command for that. like we have inetmgr to open IIS.

Please help me.

Thank you,
Regards,

Ahmed

Re: Opening SQL Enterprise manager

sam#21-May-07 0:18

sam#

21-May-07 0:18

type the exe name in run like ssmsee.exe

execute sql statements [modified]

kuwl_mark20-May-07 20:24

kuwl_mark

20-May-07 20:24

how to prepare and execute any statemaent in sqlserver2000

i tried following but it is not working

DECLARE @mak varchar(50)
set @mak ='select * from authors'

EXEC SQL PREPARE @mak;
EXEC SQL EXECUTE @mak;

***but in mysql my this statement is working fine*******
set @sql='select * from brands'
prepare stmt from @sql;
execute stmt;

-- modified at 3:12 Monday 21st May, 2007

Re: execute sql statements

Colin Angus Mackay20-May-07 20:58

Colin Angus Mackay

20-May-07 20:58

Spot the difference:

kuwl_mark wrote:
set @mak ='makarand * from employee'

kuwl_mark wrote:
set @sql='select * from brands'

In your SQL Server version you don't seem to have written a SELECT in your statement.

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: execute sql statements

kuwl_mark20-May-07 21:14

kuwl_mark

20-May-07 21:14

DECLARE @mak varchar(50)
set @mak ='select * from authors'

EXEC SQL PREPARE @mak;
EXEC SQL EXECUTE @mak;

I am using database as pubs but still it is not working

Re: execute sql statements

Colin Angus Mackay21-May-07 1:01

Colin Angus Mackay

21-May-07 1:01

EXEC @mak;

Re: execute sql statements

Harini N K20-May-07 23:24

Harini N K

20-May-07 23:24

Hi

Prepared sql statements must be give within opening and closing brackets as given below:

DECLARE @mak varchar(50)<br />
set @mak ='select * from categories'<br />
exec (@mak)

Harini

Re: execute sql statements

kuwl_mark21-May-07 1:19

kuwl_mark

21-May-07 1:19

thanks

Can you tell me where I am going wrong when I try to write to a table?

Khoramdin20-May-07 11:25

Khoramdin

20-May-07 11:25

Hello everyone,

I have a program which connects to an internet database(MySQL) using MyODBC. I am using the following code to write to "imagestorage" table in the "test" database. When I run this I get an Error message saying:

ERROR [HY000] [MySQL][MyODBC 5.00.11][MySQL] 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FileName, NULL,

ConStr = "DRIVER={MySQL Connector/ODBC v5};SERVER= XXXXXXXX; PORT=3306; DATABASE=; UID=XXXXXXX; PWD= XXXXXXXX; OPTION=3";                   <br />
        OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);<br />
        OdbcCon.Open();<br />
        <br />
        OdbcCom = new System.Data.Odbc.OdbcCommand("USE test;", OdbcCon);<br />
        OdbcCom.ExecuteNonQuery();<br />
        <br />
        OdbcCom.CommandText = "INSERT INTO imagestorage VALUES(NULL, ?FileName, NULL, NULL);";<br />
        OdbcCom.Parameters.Clear();<br />
        OdbcCom.Prepare();<br />
<br />
        OdbcCom.Parameters.Add("?FileName", System.Data.Odbc.OdbcType.VarChar);<br />
        OdbcCom.Parameters["?FileName"].Value = "Khoramdin";<br />
<br />
        OdbcCom.ExecuteNonQuery();

Can someone be kind enough to tell me where I am going wrong?

Thank you very much and have a great day.

Khoramdin

Re: Can you tell me where I am going wrong when I try to write to a table?

Dave Kreskowiak20-May-07 14:04

Dave Kreskowiak

20-May-07 14:04

You SQL statement doesn't specify the columns you want to update. You provide the values, but the statement does know which values go in which colums.

INSERT INTO table (columnName1, columnName2, columnName3, ...) VALUES (value1, value2, value3, ...)

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Export Data to text file

FriendlySoluations20-May-07 4:57

FriendlySoluations

20-May-07 4:57

How can i export data from sql table to a text file

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.