How to improve speed of query MSDE 2000

Hi All
I am having trouble with query timeouts in a VB6 program using MSDE 2000 (on
local PC)
It only happens when querying between certain dates ( surprisingly it occurs
when the dates are closer together and hence less records exists)
e.g if date range is 1/8/05 to 1/9/05 query doesn't timeout
if date range 25/8/05 to 1/9/05 it does?
code.....
sql = "select [prod_code], sum(qty) as amount, sum(qty * price) as myprice
from [idetail] where [inv_num] in (Select inv_num from Invoice where
inv_date between '" & Format(DT1.Value, "YYYY-MM-DD") & "' and '" &
Format(DT2.Value, "YYYY-MM-DD") & "') group by prod_code order by prod_code"
rs.Open sql, cn, adOpenKeyset, adLockReadOnly
I have no primary keys or indexes on the idetail table
Any ideas
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am having trouble with query timeouts in a VB6 program using MSDE
> 2000 (on local PC)
> It only happens when querying between certain dates ( surprisingly it
> occurs when the dates are closer together and hence less records
> exists)
> e.g if date range is 1/8/05 to 1/9/05 query doesn't timeout
> if date range 25/8/05 to 1/9/05 it does?
> code.....
> sql = "select [prod_code], sum(qty) as amount, sum(qty * price) as
> myprice from [idetail] where [inv_num] in (Select inv_num from
> Invoice where inv_date between '" & Format(DT1.Value, "YYYY-MM-DD") &
> "' and '" & Format(DT2.Value, "YYYY-MM-DD") & "') group by prod_code
> order by prod_code"
> rs.Open sql, cn, adOpenKeyset, adLockReadOnly
> I have no primary keys or indexes on the idetail table
>
a proper indexing schema (as long as a proper primary key, which is always
usefull :D) should always help... but this should not be a related
problem... your actual plan should always be a table scan, followed by a
sort operation, but again, should not be related...
do you have actual lock pending on the table during the failing projections?
try executing
EXEC sp_lock
http://msdn.microsoft.com/library/de...la-lz_6cdn.asp
and
EXEC sp_who
http://msdn.microsoft.com/library/de...wa-wz_3v8v.asp
to determin eventual lock conditions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

how to import data from a vb program

hi. i hope you could help me with this problem.

the data i will be needing to show is from my vb program and not from the database. on the crystal report, it should look something like this.

name amount
betty 200
cristina 345
daniel 230
preston 300

but the number of list of names and amounts varies. depend on what is entered on an unbound grid in the vb program.

thanks for any help you could extend.the VB program could be modified to pass the data, but, if it isn't stored somewhere, how would CR know about it?|||oh sorry, it is temporarily stored on a data grid.|||You could try to get data from tables in your VB program and store it to a data set, then you could send this data to your CR, where you have to tell the CR which tables you are using.
If you forget to get data from any table you are triing to show in CR the report won't work.

something like this:
dim cr as dataset
dim X as new crX
dim Y as ReportForm
x.SetDataSource(cr)
Y=new ReportForm(x)

where cr is the dataset where you have all tables you are triing to show.

hope this will help|||i'll try that one. thanks a lot.|||i already made a dataset with three tables. but when i try to set it as the data source for the crystal report, it says that the report does not have tables.

How to import an mdf-file

Hi,on my developement PC i have installed MS Sql server. A customer with my program using msde sent me his .mdf file so that i can check his data. How do i import or connect to his database file for debugging?
Regards,
Olav
Try attaching the file using sp_attach_single_file_db. You
can find more information on using this stored procedure in
books online.
-Sue
On Tue, 25 May 2004 04:26:04 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,on my developement PC i have installed MS Sql server. A customer with my program using msde sent me his .mdf file so that i can check his data. How do i import or connect to his database file for debugging?
>Regards,
>Olav
|||You can try the stored procedure sp_attach_single_file_db (see Books Online
for usage and syntax). Without a log file, if the database wasn't cleanly
detached, it's not a guaranteed move. You should try to get the user to
send you a synchronized and properly detached MDF+LDF pair.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Olav" <anonymous@.discussions.microsoft.com> wrote in message
news:E7DC9CD6-1952-4896-9C62-5D9C311674A0@.microsoft.com...
> Hi,on my developement PC i have installed MS Sql server. A customer with
> my program using msde sent me his .mdf file so that i can check his data.
> How do i import or connect to his database file for debugging?
> Regards,
> Olav
|||Thanks a lot, it worked!
Is there a better way to connect a customer database?
Regards,
Olav
|||If all you have is an mdf, it seems the only choice you'd
have would be sp_attach_single_file_db.
The customer should make sure to use sp_detach_db if you are
going to attach a database using sp_attach_db or
sp_attach_single_file_db.
Another option would be for the customer to give you a
backup file. You can restore this backup to your server.
-Sue
On Tue, 25 May 2004 05:21:05 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:

>Thanks a lot, it worked!
>Is there a better way to connect a customer database?
>Regards,
>Olav

How to import an mdf-file

Hi,on my developement PC i have installed MS Sql server. A customer with my program using msde sent me his .mdf file so that i can check his data. How do i import or connect to his database file for debugging
Regards
OlavTry attaching the file using sp_attach_single_file_db. You
can find more information on using this stored procedure in
books online.
-Sue
On Tue, 25 May 2004 04:26:04 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,on my developement PC i have installed MS Sql server. A customer with my program using msde sent me his .mdf file so that i can check his data. How do i import or connect to his database file for debugging?
>Regards,
>Olav|||You can try the stored procedure sp_attach_single_file_db (see Books Online
for usage and syntax). Without a log file, if the database wasn't cleanly
detached, it's not a guaranteed move. You should try to get the user to
send you a synchronized and properly detached MDF+LDF pair.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Olav" <anonymous@.discussions.microsoft.com> wrote in message
news:E7DC9CD6-1952-4896-9C62-5D9C311674A0@.microsoft.com...
> Hi,on my developement PC i have installed MS Sql server. A customer with
> my program using msde sent me his .mdf file so that i can check his data.
> How do i import or connect to his database file for debugging?
> Regards,
> Olav|||Thanks a lot, it worked
Is there a better way to connect a customer database
Regards,
Olav|||If all you have is an mdf, it seems the only choice you'd
have would be sp_attach_single_file_db.
The customer should make sure to use sp_detach_db if you are
going to attach a database using sp_attach_db or
sp_attach_single_file_db.
Another option would be for the customer to give you a
backup file. You can restore this backup to your server.
-Sue
On Tue, 25 May 2004 05:21:05 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks a lot, it worked!
>Is there a better way to connect a customer database?
>Regards,
>Olav

How to import an mdf-file

Hi,on my developement PC i have installed MS Sql server. A customer with my
program using msde sent me his .mdf file so that i can check his data. How
do i import or connect to his database file for debugging?
Regards,
OlavTry attaching the file using sp_attach_single_file_db. You
can find more information on using this stored procedure in
books online.
-Sue
On Tue, 25 May 2004 04:26:04 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,on my developement PC i have installed MS Sql server. A customer with m
y program using msde sent me his .mdf file so that i can check his data. How
do i import or connect to his database file for debugging?
>Regards,
>Olav|||You can try the stored procedure sp_attach_single_file_db (see Books Online
for usage and syntax). Without a log file, if the database wasn't cleanly
detached, it's not a guaranteed move. You should try to get the user to
send you a synchronized and properly detached MDF+LDF pair.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Olav" <anonymous@.discussions.microsoft.com> wrote in message
news:E7DC9CD6-1952-4896-9C62-5D9C311674A0@.microsoft.com...
> Hi,on my developement PC i have installed MS Sql server. A customer with
> my program using msde sent me his .mdf file so that i can check his data.
> How do i import or connect to his database file for debugging?
> Regards,
> Olav|||Thanks a lot, it worked!
Is there a better way to connect a customer database?
Regards,
Olav|||If all you have is an mdf, it seems the only choice you'd
have would be sp_attach_single_file_db.
The customer should make sure to use sp_detach_db if you are
going to attach a database using sp_attach_db or
sp_attach_single_file_db.
Another option would be for the customer to give you a
backup file. You can restore this backup to your server.
-Sue
On Tue, 25 May 2004 05:21:05 -0700, "Olav"
<anonymous@.discussions.microsoft.com> wrote:

>Thanks a lot, it worked!
>Is there a better way to connect a customer database?
>Regards,
>Olav

How to Identify a User's Permissions to SQL Server Tables via VB 6.0 Application

I'm writing an application using VB 6.0 as the front-end GUI, and the
MSDE version of SQL Server as the back-end (it's a program for a
really small # of users -- less then 3-4).
I'm trying to determine, through the Visual Basic interface, the
permissions of each user that's using the application on his/her
machine.
For example, let's say I'm user "Michael" that's sitting down at my
machine using the app. I've written. The security for logging into
SQL Server will be setup using Windows Security (Trusted Connection)
as opposed to Windows & SQL Server security. When Michael accesses a
particular form in the VB 6.0 GUI, I want to run some code that
automatically checks Michael's permission levels on the underlying
table (actually, a stored procedure supplying the data from the table)
that supplies the data to the form he's looking at and then give him
some feedback on the form as to what type of permissions he has while
he's browsing through the data shown in the form.
For example, Michael opens a particular form, code in the background
is run to identify that this is Michael accessing the form, the code
returns a value that identifies what type of permissions he has on the
data in the form, and a text box on the form informs Michael (for
example) that he only has read-only permissions to the data he is
viewing and cannot edit any of the data.
As another example, user Karen sits down at her computer, logs into
the application, opens the same form that Michael just opened, the
code is run in VB to detect the level of permissions she has on the
data being displayed in the form, and the text box on the form informs
her that she has editing permissions on the data in the underlying
table.
Etc...
If anyone can post an example of the code they use in accomplishing
this task in an application they've written, I'd really appreciate a
point in the right direction or a real-world example that's been
implemented by one of you. I've written several apps. thus far using
MSDE as the back-end, but the previous apps. I've written were for
clients that didn't care about restricting access to the
application... everyone could pretty much use the application as they
desired and do anything they desired to the data.
The current client I'm writing the app. mentioned here for wants to
have security in place to where various users access the application
with various levels of permissions to do stuff (or *not* do stuff) to
the data in the application.
Thanks very much in advance for any assistance / code provided!
Sincerely,
Brad McCollum
bmccoll1@.midsouth.rr.com"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0410131014.25af0f1c@.posting.google.com...
> I'm writing an application using VB 6.0 as the front-end GUI, and the
> MSDE version of SQL Server as the back-end (it's a program for a
> really small # of users -- less then 3-4).
> I'm trying to determine, through the Visual Basic interface, the
> permissions of each user that's using the application on his/her
> machine.
> For example, let's say I'm user "Michael" that's sitting down at my
> machine using the app. I've written. The security for logging into
> SQL Server will be setup using Windows Security (Trusted Connection)
> as opposed to Windows & SQL Server security. When Michael accesses a
> particular form in the VB 6.0 GUI, I want to run some code that
> automatically checks Michael's permission levels on the underlying
> table (actually, a stored procedure supplying the data from the table)
> that supplies the data to the form he's looking at and then give him
> some feedback on the form as to what type of permissions he has while
> he's browsing through the data shown in the form.
> For example, Michael opens a particular form, code in the background
> is run to identify that this is Michael accessing the form, the code
> returns a value that identifies what type of permissions he has on the
> data in the form, and a text box on the form informs Michael (for
> example) that he only has read-only permissions to the data he is
> viewing and cannot edit any of the data.
> As another example, user Karen sits down at her computer, logs into
> the application, opens the same form that Michael just opened, the
> code is run in VB to detect the level of permissions she has on the
> data being displayed in the form, and the text box on the form informs
> her that she has editing permissions on the data in the underlying
> table.
> Etc...
> If anyone can post an example of the code they use in accomplishing
> this task in an application they've written, I'd really appreciate a
> point in the right direction or a real-world example that's been
> implemented by one of you. I've written several apps. thus far using
> MSDE as the back-end, but the previous apps. I've written were for
> clients that didn't care about restricting access to the
> application... everyone could pretty much use the application as they
> desired and do anything they desired to the data.
> The current client I'm writing the app. mentioned here for wants to
> have security in place to where various users access the application
> with various levels of permissions to do stuff (or *not* do stuff) to
> the data in the application.
> Thanks very much in advance for any assistance / code provided!
> Sincerely,
> Brad McCollum
> bmccoll1@.midsouth.rr.com
See my response in comp.database.ms-sqlserver - please don't multipost.
Simon