|
connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csDir +
";Extended Properties=\"text;HDR=Yes;FMT=Delimited;\";"
where csDir is the full path (excluding the file name) to the file
HDR = YES if first line is column names, FMT=Delimited to indicate comman delimited,
note the escaped embeded quotes.
Query: "select * from [myfile.txt]" - USE THE FULL FILE NAME ENCLOSED IN BRACKETS
Hope this helps...
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Thank you Rob, I *think* that helped... I now get a different error message "No value given for one or more required parameters (1)" which sound more like it could be a problem with my query than actually accessing the file, is this right? Do you know what this error means? The query I am using is:
SELECT [Language] FROM [myFile.txt];
And the first line of the text file is:
"Student","Language"
Also, it's rather unfortunate for me that getting the query to work requires having the .txt on the end of the file name. My app needs to work with either an MSDE or a CSV db and I was hoping I could just reuse the queries. Is there any smarter way to do this than using a macro or calling a function inside every query, like this or something?
<br />
CString from(CString table) {<br />
#ifdef MSDE<br />
return _T("FROM ") + table;<br />
#else CSV<br />
return _T("FROM ") + table + _T(".txt");<br />
}<br />
|
|
|
|
|
Sadly, Microsoft messed this up, SQL Access, Excel, and CSV all follow different rules in some of the details (Excel wants a $ appended to the worksheet name for the 'Table' name ), so conditional code seems the only answer.
Not sure about the error message, sounds like it is interpreting [Language] as a parameter name. Does your connection string include the HDR=1 part to indicate that the first row is column names?
What happens if you "Select *" instead of specifying the fields?
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Select * works.
If I change the name of the Language column to UnlikelyToBeAKeyword and then select on that, with or without square brackets, it still gives the same error.
I have HDR=Yes but HDR=1 gives the same result.
Actually it seems I can put whatever rubbish I want in the Extended Properties string and it just gives that "No value give for one or more required parameters" error. I don't think that string is working properly.
Here my connection string is again in case I've done something daft:
connection = _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myTextDB\\;Extended Properties=\"text;HDR=1;FMT=Delimited;\";");
Can you see anything wrong with it?
|
|
|
|
|
if select * works, then the connection string is likely ok.
Possibly something in a 'data' row? an '@' or '?'character perhaps?
try adding IMEX=1 to the extended properties section to force all column datatypes to become strings (IMportEXport flag). Normally the provider scans the first 8 rows to determine 'numeric' columns, and could get tricked. Forcing all string datatypes gets around the 'too smart' provider. This is usually needed only for excel files, but may apply to csv as well...
One last possibility: for CSV fies, the provider normally expects an extension of .csv, or at least anything other than .txt. The reason for this is that it looks for an identically named .txt file for 'schema' definitions to use. Perhaps the filename is causing some confusion, select * works because the code takes a different path...
Looking back at code I have used in the past, your connection string looks fine (I have used 1 and YES interchangably as well).
The only thing I see different is that I always used .csv for the extension, and when writing, deleted any .txt files to avoid having the schema file overriding user changes on subsequent reads. If it exists, colum definitions in the .csv file must match those defined in the .txt file exactly or read and write fails...
At any rate it sounds like a bug.
Wish I could help more, but I am a bit mistified too.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
You don't think the fact that I can add "foo" into the extended properties is a bad sign?
I do have question marks in some of my strings. Is this a problem? MSDE didn't seem to care.
A file with .csv extension behaves the same as .txt. IMEX=1 makes no difference either.
|
|
|
|
|
wilsonian wrote:
You don't think the fact that I can add "foo" into the extended properties is a bad sign?
At least one could hope it was just gracefully ignoring things it did not understand in the connection string. 
wilsonian wrote:
do have question marks in some of my strings. Is this a problem? MSDE didn't seem to care.
I would hope that ? marks in data would be ok, in field names or elsewhere in the query they could be mistaken for parameter placeholders.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Ok, I've worked it out. Sorry, I don't blame you for not guessing since I caompletely failed to mention the pertinent detail: My files are in Unicode format. I've found the CharacterSet=UNICODE extended property and it works fine now.
Thanks for all your help, I'd probably still be stuck on that first error message without it.
|
|
|
|
|
I have the query below which contains 2 functions which return tables, fn_SamplesUserProducts & fn_SamplesUserOffices. The query itself I want to set the rowcount to say 10. However, my functions will return more than 10 rows and obviously they don't due to the set RowCount. I tried to set the RowCount in the function but an error occurs when attempting to compile: Invalid use of 'UNKNOWN TOKEN' within a function. Is there a work-around?
The query is the first part of a complex cross-tab w/o reporting services installed.
SELECT s.SampleID,
convert(CHAR(10), s.PullDate, 111) + ' ' + IsNull(S.TripNumber, rtrim(convert(varchar(25), 'N/A' + rtrim(convert(varchar(8), S.SampleID))))) as ColumnName,
s.PullDate,
s.TripNumber,
s.Quantity,
convert(CHAR(10), s.PullDate, 112) + IsNull(S.TripNumber, rtrim(convert(varchar(25), 'N/A'))) + rtrim(convert(varchar(8), S.SampleID))
FROM tblSamplesSample s,
tblSamplesSampleTerminalPortWellLocation stp,
fn_SamplesUserProducts(@UID) fnp,
fn_SamplesUserOffices(@UID) fno
WHERE S.SampleID *= stp.SampleID
AND (S.Deleted = 0 or S.Deleted IS NULL)
AND (stp.Deleted = 0 or stp.Deleted IS NULL)
AND fnp.ProductID = s.ProductID
AND fno.OfficeID = s.OfficeID
AND ((@DateRangeBegin IS Null AND @DateRangeEnd IS Null)
OR PullDate BETWEEN @DateRangeBegin AND @DateRangeEnd
)
AND (@TerminalPortWellLocationID IS Null
OR stp.TerminalPortWellLocationID = @TerminalPortWellLocationID
)
AND (@CustomerCompanyID IS NULL
OR s.CustomerCompanyID = @CustomerCompanyID
)
AND (@ProductID IS NULL
OR S.ProductID = @ProductID
)
AND (@CustomerProductReportID IS NULL
OR s.CustomerProductReportID = @CustomerProductReportID
)
AND (@DomesticInternational IS NULL
OR s.DomesticInternational = @DomesticInternational
)
AND S.StatusID = 1 --Finalized
GROUP BY S.SampleID, S.TripNumber, S.PullDate, s.Quantity
HAVING Count(stp.TerminalPortWellLocationID) = 1
ORDER BY s.PullDate DESC, TripNumber ASC, s.SampleID
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)e and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I declared the following table variables and used them in FROM clause as a work-around. The table variables are populated prior to the SET RowCount command.
However, is there a way around doing it this way?
DECLARE @SamplesUserProducts TABLE(ProductID int)
DECLARE @SamplesUserOffices TABLE(OfficeID int)
INSERT INTO @SamplesUserProducts SELECT ProductID FROM fn_SamplesUserProducts(592)
INSERT INTO @SamplesUserOffices SELECT OfficeID FROM fn_SamplesUserOffices(592)
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
dear all,
i have the following problem, i would like to insert some data to many tables and there are some triggers on that tables so does anybody know how can i stop the triggers while am inserting that data and how can i delay that triggers till the transaction commited
i have tried the next code but it did not work:
<br />
create database test<br />
go<br />
use test<br />
go<br />
create table tab <br />
(<br />
id int identity ( 1,1 ) ,<br />
name nvarchar ( 100 ) <br />
)<br />
go <br />
create trigger trg on tab after insert <br />
as<br />
(<br />
select <br />
tab.name <br />
from <br />
tab , inserted<br />
where tab.id = inserted.id<br />
)<br />
go<br />
<br />
begin transaction <br />
set transaction isolation level read uncommitted<br />
insert into tab ( name ) values ( 'New Name' ) <br />
select 'Transaction Done...'<br />
commit transaction <br />
i would like to see the execution as the following :
Transaction Done...
New Name
Thanks for your help
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
ALTER TABLE tab DISABLE TRIGGER trg
Then when you are done
ALTER TABLE tab ENABLE TRIGGER trg
This will enable and disable the triggers.
If you really need to, you can try to put a WAITFOR or a WHILE loop in your trigger if you need it to delay, but then it will run with the delay everytime.
I would recommend running the select query that is in your trigger on it's own after the transaction is complete, THEN create the trigger.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
When I use the command line arguments
REINSTALL=All
and
REINSTALLMODE=omus
to cause the MSDE 2000A setup to reinstall an existing instance, it still fails with the error message:
"The instance name specified is invalid."
I know it is already running just fine, it was a brand new, clean install of the instance not a moment earlier with this result:
"Microsoft SQL Server Desktop Engine -- Installation operation completed successfully."
What gives?
|
|
|
|
|
While using MemoryStream in the context of SQLXML3.0
sxcmd.commandText="Name of stored proc on database"
<br />
sxcmd.RootTag = "FullName"<br />
Line 36: Dim MemSt As MemoryStream<br />
Line 37: MemSt = sxcmd.ExecuteStream()---->Problem on this line<br />
Line 38: Response.Write(MemSt.Length)<br />
Line 39: Dim strRdr As New StreamReader(MemSt)<br />
System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x80040E21.
|
|
|
|
|
If you know your SQL Syntax is good, then you should check permissions. You might consider trying to run it with SA priviledges on the database to see if it works. If so, you know where your problem is.
Also if you are using XML AUTO in your query try XML RAW and see if it goes away.
Otherwise I'm not sure. But that's where I'd start trying to troubleshoot.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
It is not a permissions problem.
|
|
|
|
|
Hello,
I need to do something like this:
SELECT ID_user, Name FROM Users WHERE (Permissions & 0x1000) > 0 I want to get all the records that have a specific bit set in a field. What is the operator to do it? Or do I have to get all the records and filter them by code?
Thanks,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
Your code should work as written.
Note: You may have to put some brackets around the field names because of key work conflicts.
SELECT ID_user, [Name] FROM Users WHERE ([Permissions] & 0x1000) > 0
|
|
|
|
|
I'm using a OleDb Connection and Command in C# to retrieve data from directories of .csv files. The .csv files (each month a new one) contain a months worth of data written from PLC's in my workplace.
The Problem:
All the files Schema's are the same for the files in each directory, but the file names are all different. Hence I can't put a 'known' filename in my schema.ini file that I'd like to use when retrieving the data.
Question:
Is there a flag or command I can use or put in my Schema.ini file, so that it will be used on any file read from the same directory? (ie a generic schema.ini file?).
Thanks Guys.
MongooseNX
|
|
|
|
|
Hi, folks.
[UPDATE]
I managed to figure it out on my own. First problem was using insert. I should have been using update. Second was figuring out how to assign a single value from the select statement. Here's what finally worked:
update data_75Q set elapsed_seconds = es<br />
from (select idnum, <br />
cast(left(elapsed_time, patindex('%:%', elapsed_time)-1) as int)*60 + <br />
cast(right(elapsed_time, 2) as int) as es<br />
from data_75Q) as t1<br />
where data_75Q.idnum = t1.idnum
Anyway, I had no answers from CodeProject, but just posting the question was enough to get me to think harder about the problem. For that, I thank all of you!
[/UPDATE]
I have a MS SQL database that has a problem. One of the columns is supposed to be the elapsed time for a given process and has a varchar datatype. The process inserts the elapsed time value as a string, 'mm:ss', which means I can't perform time-related math on it. For instance, if I return a query into a spreadsheet and try to calculate statistics, it won't work.
So, I want to convert the string 'mm:ss' entries into time values. There does not seem to be a time datatype, so I want to convert the entries into an int datatype (which I can then call seconds).
I can write a query that selects the strings and converts them into int seconds:
select top 5 elapsed_time, cast<br />
(left(elapsed_time, patindex('%:%', elapsed_time)-1) as int)*60 + <br />
cast(right(elapsed_time, 2) as int) as secs<br />
from data_75Q
returns:
elapsed_time secs <br />
-------------------------------------------------- ----------- <br />
10:30 630<br />
02:27 147<br />
03:44 224<br />
13:05 785<br />
13:52 832<br />
<br />
(5 row(s) affected)
This seems to work ok.
However, I can't figure out how to get the converted values back into the table. I added another column, elapsed_seconds as int, but I can't seem to figure out the proper syntax to use this in an INSERT statement. Here's what I've tried:
insert into data_75Q (elapsed_seconds) <br />
values <br />
select <br />
cast(left(elapsed_time, patindex('%:%', elapsed_time)-1) as int)*60 + <br />
cast(right(elapsed_time, 2) as int)<br />
from data_75Q
Although I've used the same select statement, Query Analyzer reports "Incorrect syntax near the keyword 'select'".
Thanks in advance for any help.
'til next we type...
HAVE FUN!! -- Jesse
|
|
|
|
|
hello
how can i deal with multivalued fields (field type = adVariant)
i need to get data from an ado recordset field with type adVariant and
convert it to string or array of strings (it is data from the ActiveDirectory
and not from sql sever !!!- the provider is 'ADSDSOObject')
i am using c++ code and working with ado on win xp
*i have found a sample how to do it in vb but can't find a way implement it in c++!!!
thank's
stanley
|
|
|
|
|
Hi All,
I have a project where I am trying to become familiar with a database app. I'd like to log the queries made against the database (an access database accessed through ADO). What's a good utility for logging database activity?
|
|
|
|
|
If I were tackling this I would try this.
With Access you can use VBA in a Trigger. (I'm not an access guru, but I know it's possible.)
Create the trigger to occur on selection
Create a FileSystemObject (Scripting.FileSystemObject) in the trigger.
Open a text file using the FileSystemObject.TextStream
Write the contents of the query to the the text file.
I'm not sure if you can pull the actual query string so you'll need to check.
I'm also not sure if this will work, but it's a good place to start.
Torin Blair
'In the immortal words of Socrates - "I drank what?".'
|
|
|
|
|
Hello, I currently have a form and within this form there is a listbox control that is displaying data from a table. The fields within this table are records in other tables in the database. I want the user to be able to double click a record in the listbox and a for will open and it will have a drop down for each column in the record and the user will be able to change the value for that column via drop down boxes that have all possible values for that field. When they are done I want to take all their new selections for the fields of this record and save it back to the table. Can anyone give me any ideas or pointers. Thanks in advance!
Frank Lepkowski
cout << "Hello World!" << endl;
|
|
|
|
|
I need to do a view to export data from SQL Server to FileMaker. So far everything is ok, but now I need to convert a value of a string to another.
I also need to do some Select case, If statement in the view to be compatible with FileMaker. Is there a way to do that in a View ?
sebastien.lachance.blogspot.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
