Database

Re: speed up read and write on access table with ado

31-Jan-09 8:15

There's not much on your post to work with but few things you should check:

Is the connect slow. If the connect is slow, you should try to find a way to speed up finding the server and the share. Since you use names for server, perhaps DNS is responding slowly etc. Talk with the people responsible for your network. Also you can try to use IP-address instead of server name.

Slow selects, how much data do you transfer. If you need for example just one row in your application, do you fetch all rows from a table in database and then filter the data in the application. Always use the filtering abilities of the database. Also do not select columns you don't need.

Slow modifications, try to make all modifications at once. For example avoid situations where you first insert a row and then later update it. If you have such operations, prepare the data as much as you can before transfering it to the database.

Modeling, perhaps some operations are slow because of the data modeling. Review your relational model critically and for example eliminate duplicate data, normalize etc.

And of course check that the network is working correctly. For example copy a big file to that directory and see if it's copied in reasonable time.

The need to optimize rises from a bad design.My articles[^]

Rob Graham1-Feb-09 5:55

Rob Graham

1-Feb-09 5:55

There is little you can do other than use a different DBMS. Microsoft Access is a "file share" based database, as opposed to a server dbms, so all the data delivery is accomplished using the underlying file sharing. As a result, MSAccess is a real dog in any shared multi-user scenarios. (or even a single user network share scenario). Try converting the database to use Sql Server Express, MySql, Firebird, or any other server based dbms.

Report Builder - Reporting Services 2005

cbe_pav31-Jan-09 2:03

cbe_pav

31-Jan-09 2:03

Hi All...

Im a new to Report Builder - SQL reporting services 2005. I need to use a Query in Report builder to generate the reports.

Currently,im using a Single table to generate a report...

Is there any way to use more than one tables in Report Builder...

If its possible to use Query,Where do i ve to write it???

Any pls suggest some solution...

Thanks in Advance...

Damn database wizard!

Muammar©31-Jan-09 1:52

31-Jan-09 1:52

Hey,
Can you guide me in brief and tell me how can I make the simplest form of a master-details form using the boody .Net database wizard.. you know the add new data source thing from the data menu.. God, I feel really terrible about this when I thought I dont have to go through all the trouble doing it the hard way, the point is, how do you usually handle it people?? Am I the only one that spends about a week to get such a simple database form designed and working with update, insert and delete functions?? Isn't there any easy way to handle this??

For God's sake help me if you have any clue cuz it's getting me crazy!!

ps. Hi Mika $Shucks | :-\$

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Wendelius31-Jan-09 2:55

31-Jan-09 2:55

Hi,

I actually never use typed datasets since in my opinion they lack very much functionality when you want to do anything "more complex" (but I had to give it a try in this case Smile | :)

).

I understood that the problem isn't in creating the typed dataset, but how to build the form so that when you click on the first datagridview (or some other control) which represents the parent table, how to design the UI so that the child datagridview automatically shows the records corresponding to the selected parent row?

If that was correct, the trick is that when designing the second datagridview you define the datasource as the foreign key from the first binding source.

First bind the parent datagridview normally. Then when binding the child datagridview open the datasource combo and you should see a plus sign before the previously created bindingsource (for the parent datagridview) object. When you expand that, you should see the foreign key name defined in the typed dataset. Select it as datasource. After that you're able to select stuff from the child table and it should work.

The need to optimize rises from a bad design.My articles[^]

Muammar©31-Jan-09 5:57

31-Jan-09 5:57

Thanks Mika, I should try that now and if it works, it is exactly what I need so thanks again mate Smile | :)

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Wendelius31-Jan-09 6:09

31-Jan-09 6:09

No problem Smile | :)

If you have time after testing it, drop a post and tell how it went.

The need to optimize rises from a bad design.My articles[^]

Muammar©31-Jan-09 20:02

31-Jan-09 20:02

Hey Mika,
It's finally working! Thanks to you and a couple of modifications.. You know what, I think I'll have to share my experience in an article cuz it's a bit tricky to explain without pictures, I think what makes it difficult is that database I'm working with is Oracle God I'm sorry Mika but I think my bird is dying!! I'll let you know when I have this article ready!

Many thanks Mika.. Please pray for my bird Cry | :((

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Wendelius1-Feb-09 7:10

1-Feb-09 7:10

Muammar© wrote:
I think what makes it difficult is that database I'm working with is Oracle

Don't quite understand why it would be any more difficult than with SQL Server or any other database because you're still using datasets. Perhaps something in the data model?

Muammar© wrote:
I'll let you know when I have this article ready

That would be nice Smile | :)

Muammar© wrote:
Many thanks Mika..

No problem at all.

Muammar© wrote:
Please pray for my bird

I will!

The need to optimize rises from a bad design.My articles[^]

Muammar©7-Feb-09 4:15

How to pass table name dynamically for stored procedure

7-Feb-09 4:15

Hey Mika, just wanted to share the solution with you.. Never thought it would be that stupid Smile | :)

http://www.codeproject.com/script/Forums/View.aspx?fid=1649&msg=2915012[^]

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

ashok_rgm30-Jan-09 19:12

ashok_rgm

30-Jan-09 19:12

Hi All,

I have two tables like Casual_Employee_Details, Permanent_Employee_Table and the columns in both tables are same.
I have a stored procedure as follows

CREATE PROCEDURE getEmpId
(
@Eid int,
@EName varchar(50)
)

AS

insert into Casual_Employee_Details(n_EmpId,c_EmpName)values(@EmpId,@EmpName)
GO

Now i want to pass table name also dynamically how can it be done.
help me out with code if possible..

So that if if i execute it should as follows
Execute dbo.getEmpId 'tableName',Eid,'Ename'

Thanks,
Ashok

ashok

Re: How to pass table name dynamically for stored procedure

Wendelius30-Jan-09 19:36

Re: How to pass table name dynamically for stored procedure

30-Jan-09 19:36

you have to concatenate the whole sql string to a variable like

set @sql = 'insert into ' + QUOTENAME(@tablename)
                 + ' (n_EmpId,c_EmpName)values(' 
                 + convert(varchar(50), @EmpId) 
                 + ',' + QUOTENAME(@EmpName, '''') + ')'
exec (@sql)

However it's not typically a good idea to use dynamic sql this way. It may lead to several problems.

Modification, added QUOTENAME to prevent injections.

The need to optimize rises from a bad design.My articles[^]

Colin Angus Mackay31-Jan-09 1:20

Colin Angus Mackay

31-Jan-09 1:20

Mika Wendelius wrote:
However it's not typically a good idea to use dynamic sql this way

It is posslble to do some verification before you start the concatenation. For instance. you can look up INFORMATION_SCHEMA.TABLES to ensure that the a table with the given name exists. If it doesn't you can exit the sproc.

You can also pass parameters to dynamic SQL by using sp_executesql[^] instead of EXEC. That way you are not concatenating potentially dangerous data into the SQL String, you're passing it as a parameter as normal.

* Developer Day Scotland 2 - Free community conference
* The Blog of Colin Angus Mackay

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

Re: How to pass table name dynamically for stored procedure

Wendelius31-Jan-09 1:40

Re: How to pass table name dynamically for stored procedure

31-Jan-09 1:40

You're right, there are numerous ways to perform checks and prevent injections etc. but since I wasn't sure why the OP wanted to use dynamic sql for the insert (it didn't quite make sense since for example columns were hardcoded) I thought it would be wise if he thinks about it once more.

If dynamic handling for the table name is still required, at least it's thoroughly considered.

I just noticed that I forgot to use QUOTENAME. Think I should add it to the answer. Another way is to use sp_executesql as you mentioned.

The need to optimize rises from a bad design.My articles[^]

ashok_rgm31-Jan-09 3:20

ashok_rgm

31-Jan-09 3:20

Hi all,

Can anyone explain other possible mechanisms to handle this scenario in detail.
Any references could be helpful..

thanks,
Ashok

ashok

Re: How to pass table name dynamically for stored procedure

Wendelius31-Jan-09 3:28

Re: How to pass table name dynamically for stored procedure

31-Jan-09 3:28

ashok_rgm wrote:
Can anyone explain other possible mechanisms to handle this scenario in detail

As Colin and I wrote you can use either sp_executesql or exec to execute a dynamic sql statement. Both will do as long as you prevent yourself from sql injections.

In your original post you described that you want to pass the table name as a parameter and use it in your sql statement. However your column names were hardcoded in the statement so it makes me wonder, why do you want to change the table name. First impression is that you have several tables with the same structure. If that's correct then again, why?

The need to optimize rises from a bad design.My articles[^]

ashok_rgm1-Feb-09 23:57

ashok_rgm

1-Feb-09 23:57

My issue is something like this
i have a table Master_Info with columns(RecId(PK),Emp_Name,DOB) where RecId is primary key and increments by 1 for each entry.

RecId Emp_Name DOB
1 abc 01/01/2009
2 xyz 02/02/2009

Now for each Employee I have separate table to track hi daily details Employee_Details_(RecId) with columns (Working_Date,No_of_Hours)
So for user "abc" the table name will be Employee_Details_1,for "xyz" it will be Employee_Details_2

Now I want to insert the records for particular user in Employee_Details_(RecId) table using the stored procedure.

What are the suggested ways to do this task?

Thanks,
Ashok

ashok

Re: How to pass table name dynamically for stored procedure

Wendelius2-Feb-09 5:36

Audit Log In and Audit logout problems in sql server 2005 after XML import to sql server 2005

2-Feb-09 5:36

ashok_rgm wrote:
Now for each Employee I have separate table to track hi daily details Employee_Details

Is there really a very good reason to do it this way?

Normally you would have a table structures like:

Master_Info
- RecId (primary key)
- Emp_Name 
- DOB

Employee_Details
- ID (primary key)
- RecId (foreign key to Master_Info)
- Working_Date
- No_of_Hours

So in Employee detail you store details for all employees and the RecId column tells which employee is referenced.

The need to optimize rises from a bad design.My articles[^]

Vista ODBC Questions

Richard Andrew x6430-Jan-09 17:06

Richard Andrew x64

30-Jan-09 17:06

I am running 64-bit Vista Ultimate.

1. Do 32-bit applications require 32-bit ODBC drivers, and 64-bit applications require 64-bit ODBC drivers?

2. How can I administer the 32-bit ODBC drivers on my system? Whenever I run the ODBC Administrator applet, it only shows me the 64-bit drivers.

Thanks

Re: Vista ODBC Questions

Aman Bhullar31-Jan-09 5:34

Aman Bhullar

31-Jan-09 5:34

Richard Andrew x64 wrote:
1. Do 32-bit applications require 32-bit ODBC drivers, and 64-bit applications require 64-bit ODBC drivers?

In case of ODBC drivers, 32-bit require 32-bit odbc drivers only and same for 64 bit.

Richard Andrew x64 wrote:
2. How can I administer the 32-bit ODBC drivers on my system?

You need to figure it out manually

Pankaj Garg30-Jan-09 2:21

Pankaj Garg

30-Jan-09 2:21

I have a stored procedure , that imports the data to sql server 2005, For that I send the XML
to database, there I extract the data and insert/update it in one query.

I am operating the same thing at multi user level and starts multi users start the import
for the central localized database, there I am watching the execution in sql profiler,
and now my problem is
The Audit logout/login takes lot of time to execute,

Suppose two users are simultaneously doing the import process, if the first user reaches the
database first, there after the completion of the import for the first user,
still the request of first user does not complete , it
remains there for several minutes until audit logs out.
and due to this the second user keep waiting for the import execution.
please provide suggestion for this performance issue.

If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!

Re: Audit Log In and Audit logout problems in sql server 2005 after XML import to sql server 2005

Wendelius30-Jan-09 3:08

saving/recalling image to/from sql server 2005 using asp.net

30-Jan-09 3:08

Is there some reason why you suspect that audit log is the rason for waiting? If both of the users are adding data to the same table, I would guess that the second session is waiting for exclusive locks to release from the first session. Have you used current activity window in Management Studio to see what the second session waits?

The need to optimize rises from a bad design.My articles[^]

roachae29-Jan-09 10:38

roachae

29-Jan-09 10:38

I'm a beginner C#/ASP.NET programmer (long time C/C++ in Unix programmer) having problems with storing an image on my webform to sql then retrieving it. Problem is, I've got the image on my webform along with other information in textboxes that need to be stored in the database. All the textbox info as well as two images on the webform (the images are signatures captured from signature pads) need to be stored then later recalled to bring up a print preview of the information that was earlier saved. All my textbox info is being stored and recalled properly, but I'm having issues with the images of the signatures. There's no real file for the image to upload via a dialog box, just the captured image. Can anyone help me out?
Thanks

Re: saving/recalling image to/from sql server 2005 using asp.net

Wendelius29-Jan-09 10:51