|
Two initial things to check:
1. Do you have a clustered index / primary key on the DATETIME column?
2. Do you have an index on the IP address?
Also, make sure you're not avoiding the index by using a function on the column - try converting the between clause to use dates.
Lastly, try group only what you want by using a where clause, so:
SELECT DATE_FORMAT(Accessi.Data, '%Y/%m/%d') AS Giorno, Count(DATE_FORMAT(Accessi.Data, '%Y/%m/%d')) AS Accessi
FROM Accessi
WHERE Giorno BETWEEN convert(datetime,'2002/12/1') AND convert(datetime,'2002/12/31')
GROUP BY Giorno
Please let me know if this helps.
|
|
|
|
|
Well, I was using mySQL, not SQL Server, so the convert function is not available to me... but I figured out how to solve it.
SELECT DayOfYear(Accessi.Data) as numGiorno, Accessi.Data, Count(Accessi.Data) AS Accessi
FROM Accessi GROUP BY numGiorno
HAVING numGiorno BETWEEN DayOfYear('2002/12/1') AND DayOfYear('2002/12/31') AND Year(Accessi.Data) = 2002
Thanks for your effort anyway! Bye!
Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)
|
|
|
|
|
Environemnt: VC++6,Access DB via ODBC.
How would one determine if a table exists in the database.
I have tried "SELECT * FROM TABLE_NAME"
where TABLE_NAME is the table I want to check for existence.
Thinking I would get an error or invalid return, Yet the the error gets trap and an caught before I can get to it or test for a return condition?
There has to be an easy way to check for table existance. RIGHT?
Thanks for any help
--Errol
|
|
|
|
|
Try this:
IF EXISTS (SELECT name FROM sysobjecs WHERE id = object_id(N'tablename')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
SELECT * FROM tablename
Or to get a list of tables (using this query in the EXISTS clause above also works), this is even easier:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
"Well, I wouldn't say I've been missing it, Bob." - Peter Gibbons
|
|
|
|
|
I have a remote MySQL database and I'm trying to connect to it via MFC. I've tried using CDatabase but whatever I try, it never connects. I'm really new at this. Any help is appreciated.
Thanks in advanced,
Steve
|
|
|
|
|
is there a way of getting all but One column values for a table rather than enumerating the entire list of parameters .
|
|
|
|
|
|
try 2.20 instead of 2,20
cheers,
_____________
stefan bornuz
|
|
|
|
|
We can create users to databases in MS SQL, apart from the default username 'sa'. I want users to login using a form that validates the username and password not from a table but from the users created for that particular database. How should I go about it using ADO.
Sreejit
|
|
|
|
|
Why don´t you use it in the User ID and Password properties of the connection object ? If you got an error when you access the database, the name and password would probably be wrong.
Mauricio Ritter - Brazil
Sonorking now: 100.13560 MRitter
Life is a mixture of painful separations from your loved ones and joyful reunions, without those two we'd just be animals I guess. The more painful the separation, that much more wonderful will be the reunion - Nish
"Th@ langwagje is screwed! It has if's but no end if's!! Stupid php cant even do butuns on forms! VISHAUL BASICS ARE THE FUTSHURE!" - Simon Walton
|
|
|
|
|
thank you,
I never thought of that. That shuld wrk. I will try it out.
Sreejit
|
|
|
|
|
Hi:
I have this challenge that i am sure many of us would have faced.
I want to write a stored procedure that has to list out all company names based on whether one of the characteristic has value 1..
For eg:
create procedure (proc_name) @paramcharacteristic varchar(40) as
select * from tblcompany where @paramCharacteristic=1
Now this @paramcharacteristic will always match one of the column names in the table like Performance,Satisfaction,Profits and so on.But this does not seem to work.
When i run the stored procedure from an application i get the error
Cannot convert varchar 'Satisfaction' to integer.So i think what it does is try to compare the value of @paramcharacteristic variable and 1.But instead i want SQL to understand the statement as
select * from tblcompany where Satisfaction=1
Where do u think should i make the change.In the definition of paramcharacteristic in the stored procedure??Pls give me some suggestions.On how to deal this situation..
Thanks in advance for the help..
Regards
Pradhip.S
If a Building is Completed then why do they call it BUILDING ??
|
|
|
|
|
One option is to build up the T-SQL statement dynamically, like:
exec('select * from sysobjects where ' + @myColumnName + ' >5')
Just watch out for SQL injection problems.
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
You just saved me at least 3 days work.
Cheers mate! 
|
|
|
|
|
Hi all..
I use SQL Server 2000 and i wonder how to connect to a remote SQL server (on a site not network) using query analyzer..
can I make that just the same way i do when i connect to a server on a network ?
|
|
|
|
|
You need to go into Client Network Utility and enter the connection parametes for the remote server ie.; TCP IP, IP address,server name) then go back into Query Analyzer and select the server from the list.
|
|
|
|
|
thank you a lot .. but it did not work 
can i use the name of server not the ip ?
such as www.server.com ?
|
|
|
|
|
I give in, after two days of trying to resolve a problem i need your guys help. First i have a microsoft complaint.
Why does microsoft give hundreds and hundreds of (the same) example of how to move through a bound DataTable one by one,
ie. this.BindingContext[dsPubs1, "authors"].Position += 1;
but not for my scenario below.
My Question:
I have a customer details form with numerous textboxes all bound to the same datatable. Now if a user enters an Account Number i go and retrieve the related rows information. How do i get the textBoxes to reflect this change in row.
I'm sorry i cannot give you an idea of how i have tried to get round the problem as i have tried alot of ways, with no luck. The textBoxes are bound at runtime.
Any help would save me going bold.
Thanks
Matt G
|
|
|
|
|
I am trying to createn an app in VB6.0 and Win98 to edit a simple csv (comma separated values)-file. The csv is not very big, so memory is no problem.
But I wanted to make life easy by using SQL-commands, so I decided to use ADOdc and MSHFlexGrid(e.g.)
I figured out some solutions, but noone works. If somebody could help me to get just one to work I would be glad.
(I thought using ADO woudl be RAD, but if I needed a long time to understand that db-Stuff. If I not succeed fastly, I will use a ListView and populate it "by hand" )
First Idea: (Without ADO)
MSData-Control and MSDBGrid. I can display the Data in the Grid. ok.
But when I try to edit the data I get:
"[Microsoft][ODBC Text Driver] ISAM doesn't support updating Data in a connected Tabel" (or sth like this, I translate from German)
1) How to solve this?
2) What (the hell) ist ISAM?
Second Idea: (With ADO)
ADOdc: Provider: MS OLE DB Provider for ODBC Drivers,
ADOdc: DataSource Name: Text-Files ...
ADOdc: Test connection: ok!
But also same message like above after editing:
"[Microsoft][ODBC Text Driver] ISAM doesn't support updating data in a connected Tabel" (or sth like this, I translate from German)
(MY FAVOURITE IDEA!!)
1) (Questions like above)
Another idea:
creating a db1.mdb (MS Access), creating a macro to import the my.csv, creating another macro to export it.
Idea: Start Import, then access the mdb by ADOdc-control, finally export.
1) How to get rid of fixed path in MS Access file-import?
2) The .mdb-File can be used without MS Access installed on the machine. Am I right?
3) How can I start MS-Access-macros from my VB app??
And another idea:
.udl | Provider "OLE DB for MS Directory Services" has worked with MSData Control, but just under W2K. In W98 the checkboxes for "Share Deny None" etc are greyed, and it doesn't work.
1) Whats MS Directory Services? (This "Active Directory??)
2) Does this work on WinNT? (its the SYS to run on) Are there some "Packets" to install "MS Directory Services" to get it to work?
My last idea:
Do some registy-manipulation (I havn't done yet and want to avoid this):
http://support.microsoft.com/default.aspx?scid=kb;de;245524
Friedrich
|
|
|
|
|
Hi,
I am creating an MSAccess DB using ADOX in C++/MFC.
I don't seem to be able to set the "Allow Zero Length" field to "Yes" (as seen in Access design view) from my code.
Does anyone know how to do this?
I've tried the adColNullable and adColFixed Attributes, but they seem to have no effect on an adWChar field.
Thanks....
Blade[DMS]
|
|
|
|
|
Hi
anyone care to point me at a SQL optimization 101 type FAQ etc eg dos and don't of tables/views/queries server config etc
Thanks
Shaun
|
|
|
|
|
Well, a good site on SQL Server optimization is http://www.sql-server-performance.com/[^]. Maybe it has too much information for you to read, so I recommend you to read some introduction first.
[shameless plug]
For the DO's and DON'Ts part, I can recommend you my article, SQL Server Do's and Dont's[^]
[/shameless plug]
I see dumb people
|
|
|
|
|
thanks that site is a great help - confirmed somethings I'd already picked up by trial and error and taught me 1000x as much more and I still don't feel I read more than 1% of the site
Technically speaking the dictionary would define Visual Basic users as programmers.
But here again, a very generalized, liberal definition is being employed and it's wrong
- just plain wrong - Tom Archer 5/12/02
|
|
|
|
|
i am using the following SQL statement to populate a datagrid in VB, it executes fine when i test it in access but it does not work in vb - what is the problem? thank you!
Dim strCompany As String
Dim strLikeCompany As String
strCompany = txtCompany.Text
strLikeCompany = strCompany & "*"
Dim findSQL As String
findSQL = " Select ClientCode, Company, ContactName " & _
" from Client " & _
" Where Company LIKE '" & strLikeCompany & "'"
Set RS = Conn.Execute(findSQL)
|
|
|
|
|
Are you definitely using the same strLikeCompany value when testing on Access and VB?
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
