|
In SQLServer when I want column that start between range of a to b I use this syntax:
column LIKE '[a-b]%'
But it seems that it does not worl correctly in Access. How should I get that query in Access?
Mazy
No sig. available now.
|
|
|
|
|
try:
column LIKE '[a-b]*'
|
|
|
|
|
Nope,I found it some days before, MS ACCESS does not support range for LIKE. I should use it seprately:"[a]% AND [b]%" And I donna why '%' works but '*' doesn't work cause as you said '*' is correct. Any way, thanks for your reply.
Mazy
No sig. available now.
|
|
|
|
|
Mazdak wrote:
MS ACCESS does not support range for LIKE.
how comes ?
open the sample database northwind (comes with access) and try this query :
SELECT lastname
FROM Employees
where lastname like '[a-d]*'
results :
Last Name:
Davolio
Buchanan
Callahan
Dodsworth
|
|
|
|
|
Yes,its srtange for me too,but that really happend,maybe because my database contain Farsi charachter and windows always have problem with Farsi charachters.
Mazy
No sig. available now.
|
|
|
|
|
1. I've created a a view based on table "Orders".
2. I've been assigned role="dlladmin", let's say my login="Tom"
3. I granted jane (public) access privilege by:
grant select on vwOrders to jane
4. server admin "sa" transfered the view to dbo
sp_changeobjectowner 'Tom.vwOrders', 'dbo'
5. I checked Jane's access privilege to vwOrders by:
5a. From Jane's account:
select * from vwOrders
result: NO problem, jane can access the view. OKAY.
5b. Enterprise Manager: Choose the table, select properties>Permission
result: Jane was not assigned SELECT permission??
MY QUESTION: WHY? Is it a bug in Enterprise Manager? That privileges assigned before the view was transfered is not reflected in choose table>Properties>Permission?
Thanks in advance.
|
|
|
|
|
Hello fellow friends,
I am working on a project that needs to compare a users input string with the rest of the records in the table (MS-SQL).
E.g : User enters a string "I love .net"
Other entries in the database :
1. "I love ADO.net"
2. "I love ADO"
3. "No love"
The user entered string is to be compared with the rest of the records and displayed in % (no. of words found matching, taking users string as 100%) for each record.
For above 3 records : 1. 100%, 2. 67% 3. 34% (approximately)
The project is in C#.
What i need is a function/s. or a sql procedure or...any code thats can solve my problem.
"Merry Christmas to All"
A-La
|
|
|
|
|
I am trying to retriev table in excel file using following code, but I get connection error like : Microsoft.Jet.OleDB 4.0 not registered on local machine or installbale ISAM not found. I am using Windows 2000 Adevance server and Visual Studio.Net 2003 and FrameWork 1.1 on my computer. Can any one please help?:
===========
CODE SNIPPET
============
string FileNam = "C:\\ExcelToXml\\ExcelInfo.xml";
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB 4.0;Data Source=EXCEL.C:\\ExcelToXml\\foodgroup.xls;Extended Properties=Excel 8.0;";
OleDbConnection Conn = new OleDbConnection(strConn);
try
{
Conn.Open();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
//You must use the $ after the object you reference in the spreadsheet
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", Conn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
DataSet myDataSet = new DataSet();
try
{
objAdapter.Fill(myDataSet, "ExcelInfo");
Conn.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
myDataSet.WriteXml(FileName);
}
Bharat Gadhia
|
|
|
|
|
Bharat Gadhia wrote:
strConn = "Provider=Microsoft.Jet.OLEDB 4.0;Data Source=EXCEL.C:\\ExcelToXml\\foodgroup.xls;Extended Properties=Excel 8.0;";
does not look correct.
try:
strConn = "Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:\\ExcelToXml\\foodgroup.xls;Extended Properties=Excel 8.0;";
(no "EXCEL." before path to datasource).
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
Hi Graham,
Thank you so much for your reply. I did try using:
strConn = "Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:\\ExcelToXml\\foodgroup.xls;Extended Properties=Excel 8.0;";
Problem persists. In fact I tried various ways to write connection string and use it, but I get the same error mesages. I have on my machine entire MS Office suit installed.
With best regards
Bharat Gadhia
Help is always welcome
|
|
|
|
|
Missing . - the name of the provider is 'Microsoft.Jet.OLEDB.4.0'.
|
|
|
|
|
Thanks for your suggestion. I did try that but somehow problem persisits.
|
|
|
|
|
I have already used ADO with Visual C++.
I also used ADO.NET with Visual C#.
My questions are :
-Is it possible to use ADO.NET with Visual C++ in the same one uses ADO (I could not find any sample with the DataSet in MSDN)?
- If yes can you show me an example?
Thanks for any answer!
|
|
|
|
|
 I'm a beginner in the field of Oracle. I want to know the way on how to make the connection to Oracle with ADO format.I have no idea about all the store procedures... The only thing i experienced is connecting to MS Access with ADO(ODBC). Is it the similar way to do so with this Oracle????
wb
|
|
|
|
|
Hello,
I want to learn ADO.NET. I am an intermediate level programmer and am currently learning C#. Can anyone suggest a good book to purchase to learn ADO?
Thanx for the help.
-Flack
|
|
|
|
|
Hello again...
Well the book I suggested for ASP.NET also gives a little introduction to ADO.NET as well (in the context of a Web Application). But once your past that, for a more in depth look, go for "Professional ADO.NET programming" published by Wrox Press. Although I ought to warn you that the content is quite dense if you have no experience what so ever.
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
Enumerators in .NET: See how to customise foreach loops with C#
|
|
|
|
|
What's the purpose of Bulk Update Lock? How's it different from Exclusive locks if all it does is to prevent concurrent transaction from accessing the table to which it's loading data? Can SQL server simply assign a Exclusive lock to the table during BCP operations?
Thanks in advance.
|
|
|
|
|
Does anyone have a recommendation for a database for a windows forms (c# - ado.net) application. I'd prefer not to use MSDE, MySql, Postgres since the app will only have one user - kind of like Quicken or Money. Also, my channel will be download - and these DB's are rather large.
I've heard bad things about Microsoft Access and JET. I haven't experienced personnally, wouldn't mind hear perspectives on this as well. Other's I've been checking out are SQLite, TurboDB, Advantage and Codebase.
Which one is the best from a value, quality perspective?
|
|
|
|
|
If the app is intended for only 1 person or a very limited amount of users, then Access is just fine.
Remember that it is going to be replaced with MSDE at some stage, so bear that in mind too.
Although MSDE will take longer to deploy, it is a much, MUCH better DB to program against, and will perform faster. This will be useful if you'll need to run reports, etc...
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG blog:: brokenkeyboards
|
|
|
|
|
SimonS wrote:
If the app is intended for only 1 person or a very limited amount of users, then Access is just fine.
Remember that it is going to be replaced with MSDE at some stage, so bear that in mind too.
More accurately, Jet 4.0 is the end of the road. It will not be ported to 64-bit Windows (I think there's a little 16-bit code in there somewhere) and won't be modified, apart from service packs (currently up to SP8).
I think this is a shame, because an in-process relational database engine can be handy. Maybe the SQL Server relational and query engines could be produced as in-process components? SQL Server already uses OLE DB to couple the storage engine and the query processor...
Where Access traditionally falls down is when multiple clients access an MDB file across a network. However, I've not had any problems in the last few years - perhaps they cracked it 
|
|
|
|
|
Ive been writing an app using the Advantage DB. Since i had used it with Borland tools I was keen to check out their .NET provider and everything has been straight forward up till now.
I havent had a chance as yet to try out the stored proc or views support.
|
|
|
|
|
Here's a MCAD question.. See if any of you wizards can help me get thru this:
You need to tune the following query:
SELECT P.PolicyNumber, P.IssueState, AP.Agent FROM Policy AS P
JOIN AgentPolicy AS AP
ON (P.PolicyNumber = AP.PolicyNumber)
WHERE IssueState='NY'
AND PolicyDate BETWEEN '2/1/2001' AND '3/4/2002'
AND FaceAmount > 10000
They also presented to you the "Show Execution Plan" from Query Analyzer:
Cluster Index Scan - 95% cost --> This tell me that the index scan is the blood sucker - ie. Should work on index --> This points to option (d) and (e)
The objective is to tune the query. You have the following options:
a. Rewrite the query to eliminate BETWEEN keyword and rewrite the query without it (Okay, this is wrong answer, i guess no one have trouble with this.)
b. Add a HASH join hint
(Reference: http://www.sql-server-performance.com/hints_join.asp)
c. Add a WITH(INDEX(0)) table hint to "Policy" table (QUESTION 3: How do you do a index hint by the way??)
d. Update Statistics on "Policy" table.
e. Execute DBCC DBREINDEX on "Policy" table.
Choose one.
My shot at it would be to eliminate (a), (b) and (c) because of my faith in "Query Optimizer" that it will do a better job than me. Now, (d) and (e) can be equally correct.And I can't pick one over the other. The model answer is (d) - but it doesn't tell you why (e) is eliminated. The question itself didn't state whether the index is fragmented, but neither did it state that the statistics is up-to-date.
QUESTION 2: In addition to the question itself, is there any example in which we need to "hint" whether the query should be executed using HASH/MERGE or LOOP join? Doesn't the query optimizer do this for you and that Query Optimizer does a better job?
QUESTION 3: For option (c), their explanation for rejecting the option is as follows:
"The estimated execution plan shows that a clustered index scan is going to be used, not a seek. It'd be better if the query used an inedx seek instead. We can try to force an index seek with a table hint. However, we shold use the hint inedx(1), not the hint index(0)" -- What??? How do we write an index hint in a SQL query? Anyway, i thought this option should be eliminated because normally we rely on Query Optimizer and not to hardcode query hints.
Thanks in advance.
|
|
|
|
|
I'm not an expert DBA, just a programmer with some decent database experience, mostly from the design side, but here's my take:
Question 1:
Can a clustered index be fragmented? I always thought that a clustered index was special because the physical ordering of the rows in the data pages IS the index order. It can be expensive for inserts in the middle (think of inserting in the middle of an array-- gotta shift stuff) but it means that you have fewer levels of index to wade through to reach a record, and also that the index doesn't get fragmented.
Now, if the server didn't realize that the pages containing certain values were being accessed very frequently, it wouldn't always cache the correct things in memory AND it wouldn't be able to figure out the best way to go about fetching certain rows. You get the idea.
Question 2:
That's some freaky sh*t there. I've never used a LOOP join (and I don't even remember studying it, either), and I use SQL Server every day now to good effect. Two other SQL Server DBAs I know have always said that it's best to let the query optimizer do its thing, and I've always done that.
I am studying Oracle now, and it has much richer hint support. A colleague who has Oracle experience says that hint knowledge is essential for an Oracle DBA. In SQL Server, I've always just found it most valuable to keep a clear head when constructing queries, construct indexes correctly, keep statistics up to date, etc. About the freakiest I've gotten in my quest for performance has been to use DBCC PINTABLE in a few cases, but I think that all such tricks are rendered of negligible value in a database that's well-constructed and set up on a database that has the disks set up correctly, enough RAM, etc.
Question 3:
You got me there. I agree with you-- I've never learned how to use index hints in SQL Server, and I don't even remember seeing anything about 'em in the docs.
Regards,
Jeff Varszegi
|
|
|
|
|
Apparently, there's a lot you can do tuning queries without resorting to table/query/index hints, but, there're all those exceptions and rules. But it seems like there's no one place where they list all you need to know and the necessary information is scatter all over internet. Examples of fragmented discussion:
1: http://www.sql-server-performance.com/hints_join.asp
2: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TuningofaDifferentSort.asp
Example of a more complete treatment on performance tuning in general: http://www.sql-server-performance.com/
(I think these guys do a mch better job than BOL, but then again, not complaining - BOL is already a lot better than MSDN/MFC)
I still needs answer to my questions though, and no, for QUESTION 1, I didn't ask if a cluster can be fragmented. Thanks though, for taking the time.
|
|
|
|
|
CillyMe wrote:
for QUESTION 1, I didn't ask if a cluster can be fragmented
You were asking why they eliminated option 'e'-- I was just trying to answer! One of the main reasons you'd use DBCC DBREINDEX is to rebuild a fragmented index, but in this case that probably wouldn't help because the index can't be fragmented.
I got my MCSD recently, and then since I'd used the SQL Server test as my elective, I followed up with my MCDBA. There wasn't a single question on any sort of hints on any SQL Server test that I took, for what it's worth. I wouldn't spend too much time on it unless you're just really interested.
Regards,
Jeff Varszegi
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
