|
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
|
|
|
|
|
Hi Sebastien. Well, I'm gleaning from your question that you may be interested in the SQL Server CASE function, which you can put in the SELECT statement for the view. Look it up in the SQL Server books online (specifically the T/SQL documentation) and see if that's what you're looking for.
Here's an article about using the CASE statement:
http://www.4guysfromrolla.com/webtech/102704-1.shtml[^]
|
|
|
|
|
I have a sample code of insert/retrieve binary file into/from MySQL, it uses FileStream to implement the function. Seems FileStream doesn't work for text file.
Can anybody tell what function should I use to retrieve text file from MySQL? Or if you could suggest a sampel code, that will be very helpful.
Thanks!
|
|
|
|
|
I don't know why a FileStream wouldn't work for text data but would for binary. Can you post your code that doesn't work so we can have a look?
Affordable and reliable hosting? Click here!
|
|
|
|
|
Hi, Davey:
Thanks for your response. Here are my code for text file:
String * NCFILE_NAME = "test10.nc";
FileStream * tfs = new FileStream(NCFILE_NAME, FileMode::Open, FileAccess::Read);
Char ncbuffer[] = new Char[(int)tfs->Length];
tfs->Read(ncbuffer, 0, ncbuffer->Length);
My intention is to read the text from "test10.nc" and write to Char Array ncbuffer[], but
tfs->Read(ncbuffer, 0, ncbuffer->Length);
cause the error like this:
cannot convert parameter 1 from '__wchar_t __gc[]' to 'unsigned char __gc[]'
When I did exactly the same for binary file, and define as:
FileStream* fs = new FileStream(FILE_NAME, FileMode::Open, FileAccess::Read);
Byte buffer[] = new Byte[(int)fs->Length];
fs->Read(buffer,0,buffer->Length);
String* extension = "jpg"; // for image file
It works fine.
So I guess maybe I should use something other than FileStream. But what is it?
Could you please give me some ideas about this?
|
|
|
|
|
Hi, Davey:
Thanks for your response. Here are my code for text file:
String * NCFILE_NAME = "test10.nc";
FileStream * tfs = new FileStream(NCFILE_NAME, FileMode::Open, FileAccess::Read);
Char ncbuffer[] = new Char[(int)tfs->Length];
tfs->Read(ncbuffer, 0, ncbuffer->Length);
My intention is to read the text from "test10.nc" and write to Char Array ncbuffer[], but
tfs->Read(ncbuffer, 0, ncbuffer->Length);
cause the error like this:
cannot convert parameter 1 from '__wchar_t __gc[]' to 'unsigned char __gc[]'
When I did exactly the same for binary file, and define as:
FileStream* fs = new FileStream(FILE_NAME, FileMode::Open, FileAccess::Read);
Byte buffer[] = new Byte[(int)fs->Length];
fs->Read(buffer,0,buffer->Length);
String* extension = "jpg"; // for image file
It works fine.
So I guess maybe I should use something other than FileStream. But what is it?
Could you please give me some ideas about this?
Regards,
Kevin
|
|
|
|
|
Always read the data into a Byte[] rather than a Char[] and it will work for both binary and text files. You shouldn't really need to read it into a char[].
Database FAQ
|
|
|
|
|
I wanted to bind the elements in a ComboBox to a column in a DataTable, but wanted them to be sorted. This application is targeted for the Compact Framework, and I didn't see a way to do that directly.
I decided to create a DataView object from the DataTable with the particular sort I was looking for. I set the DataSource of the ComboBox to the DataView, and setup the Display and Value members as needed by the app. Everything seemed to be working as expected, but I have run into one snag I can't seem to solve. If I change the name of one of the colunmns in the DataTable (ultimately the DisplayMember in the ComboBox), it doesn't propogate to the ComboBox by way of the DataView. On the other hand if I add or delete a row to the DataTable, everything does propogate to the ComboBox, even the row where I only changed the name!
Is this the expected behaviour?
Is there something I can do to force the ComboBox to reload it's drop down list?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
