|
Use the Format() method to format it in whatever way you want. Like Format("%Y-%m-%d") formats it to Year, Month and Day format. Format method is member of CTime class.
|
|
|
|
|
Hi there,
I have generated some SQL script-files using the SQL-Enterprise Manager (for
creation of tables, stored procedures, etc.).
When running those script files through the isql or osql utility, I have the
possibility to capture the output to a file, which is sort of importand to
me.
What I'd like to do is to write a small utility (Windows app in c#) which
will execute my sql-script-files on the SQL server. Again, I need to capture
the output. Can this be done using an SqlCommand-object providing the
contents of each individual script-file as the command text or do I have to
use isql. In both ways I'd like to know how it is possible to capture the
output.
The output is actually only required if there have been errors during
batch-execution.
Any help is greatly appreceated. Thanks in advance.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
isql has output parameter : /o
public int Exec( string sCommand )
{
int nResult = 0;
m_oInstallProcess = new Process();
m_oInstallProcessInfo = new ProcessStartInfo("cmd");
m_oInstallProcessInfo.UseShellExecute = false;
m_oInstallProcessInfo.RedirectStandardInput = true;
m_oInstallProcessInfo.RedirectStandardOutput = true;
m_oInstallProcessInfo.RedirectStandardError = true;
m_oInstallProcessInfo.CreateNoWindow = true;
m_oInstallProcess.StartInfo = m_oInstallProcessInfo;
m_oInstallProcess.Start();
m_oCommandWriter = m_oInstallProcess.StandardInput;
m_oResultReader = m_oInstallProcess.StandardOutput;
m_oErrorReader = m_oInstallProcess.StandardError;
m_oCommandWriter.AutoFlush = true;
m_oCommandWriter.WriteLine( sCommand );
m_oCommandWriter.Close();
m_oCommandWriter = null;
string s = m_oResultReader.ReadToEnd();
m_sErrorMsg = m_oErrorReader.ReadToEnd();
if( s.IndexOf("Cannot open input file") > 0 )
throw new Exception( "Command:\r\n"+sCommand+"\r\nError:\r\nCannot open input file!" );
else if( m_sErrorMsg.Trim().Length > 0 )
throw new Exception( "Command:\r\n"+sCommand+"\r\nError:\r\n"+m_sErrorMsg+"" );
else
System.Threading.Thread.Sleep( 10 );
return nResult ;
}
D!shan
|
|
|
|
|
Hi D!shan,
thanks for your reply! Your example, which I find extremely interesting is unfortunately not, what I was looking for, because it assumes, that the person running my tool is required to have osql installed on his/her machine.
So I was actually looking for a way to do this through an SqlCommand object. What I've done now is that I've written a parser for SQL script files, which creates a queue of individual commands which can actually be executed through the SqlCommand. Now I'm looking for a way to hook up the output from Sql-Server when doing ExecuteNonQuery()...
Regardless of that, I find your code very useful for many other situations. Be sure of a good rating and thanks a lot.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Hi!
I'm being plagued by the dreaded Jet error 3218 "Couldn't update; Currently locked" from time to time.
My application is a windows service that instatiates a server object that's being published for remoting using RemotingServices.Marshal(myObj, myURL).
This server object receives requests from remoting clients and reads and writes to an Access 2000 database using OleDb. In addition, the server object performs some actions at the tick of a System.Timers.Timer, reading and writing to the DB as well.
Now sometimes I get this error 3218, even when there are just 1 or 2 clients connected and I can't explain why this is happening.
I'm not using any transactions, so I can't see why any recordset should be locked for a longer period of time.
I also Dispose() of all OleDbConnections as soon as the query is through, thus accumulation of open DB connections shouldn't be an issue either.
Does anyone have an explanation or, even better, a clue on how to avoid this error?
TIA,
mav
|
|
|
|
|
|
Thank you Anders for your reply!
I'm closing all connections already. In fact I only closed the connections in the beginning without disposing and then read somewhere that this might be the reason. So I disposed of all connections after closing them, but to no avail.
I know it might sound silly, but just to make sure I'm not misinterpreting the whole situation, can somebody confirm this:
1) Several threads are accessing the database, so there's the possibility that two or more queries are started simultaneously.
2) I set the locking mode to row level locking in the connect string, so a collision should ONLY be possible when two threads try to access the same row in the same table and at least one of them is writing.
3) When such a collision occurs the DB retries for the number of times specified in the access db (I set it to 10), waiting for the period specified (250ms) every time.
4) So the writing update is taking > 2,5 seconds to complete and at the same time there's a reading access to the same row?
If these assumptions of mine are correct, then reducing the tries/interval should increase the probability of getting a 3218, right?
mav
|
|
|
|
|
Hi
I have a table CandidateTab Which contain the CandidateId and Skill fields and data in this table are in following format.
CandidateID Skill
---------------------------
10 VB.net
10 ASP.net
10 C#
11 Oracle
11 SQL SERVER
How can I make a query to get result in following format
CandidateID Skill
10 VB.net,ASP.net,C#
11 Oracle,SQL SERVER
Thanks
Arvind
|
|
|
|
|
Is not easy to get the result in one simple query! . At least you must write a cursor to doing this..
D!shan
|
|
|
|
|
Try this also.. its little bit matching to ur prob.. OR is it ok to doing this through cursors?
USE pubs
GO
DECLARE @title_ids varchar(150), @delimiter char
SET @delimiter = ','
SELECT @title_ids = COALESCE(@title_ids + @delimiter, '') + title_id FROM titles
SELECT @title_ids AS [List of Title IDs]
D!shan
|
|
|
|
|
THANKS,I will try it
Arvind
Arvind
|
|
|
|
|
Im trying to do the following query on a view in access db:
"SELECT List Totals.Expr1,List Totals.Order ID FROM List Totals"
this is the view definition that i got from the db:
"SELECT Sum([Orders Detail].[Unit Price]*[Orders Detail].[Quantity]) AS Expr1, Orders.[Order ID]\r\nFROM Orders INNER JOIN [Orders Detail] ON Orders.[Order ID] = [Orders Detail].[Order ID]\r\nGROUP BY Orders.[Order ID];\r\n"
and i get syntax error missing operator in query expression List Totals.Expr1
how do i do this right?
|
|
|
|
|
|
Thanks ,this is a step forward , what does the square
brackets mean?
|
|
|
|
|
blankg wrote:
Thanks ,this is a step forward , what does the square
brackets mean?
It's a quote char, so you can use spaces as part of field and table names. If you don't use spaces on field and table names, you can safely ommit them. Actually, most programmers use the "_" char instead of space exactly for this reason.
Yes, even I am blogging now!
|
|
|
|
|
ive been creating a query using date as one of its parameters. the date is of CTime data type.
my code is like this:
CString dbWorkName;
CString dbPICName;
CString dbElementName;
CTime dbDate;
CString readSQL;
double ReviewActualHour = 0;
CString strReviewActualHour;
CString ProjName;
CString strReviewDay;
SYSTEMTIME date;
dbDate.GetAsSystemTime(date);
strReviewDay.Format("%04d-%02d-%02d",date.wYear,date.wMonth,date.wDay);
_bstr_t bstrQuery;
_variant_t getFiled;
_variant_t vRecsAffected;
vRecsAffected = 0L;
readSQL = "SELECT L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd, Sum(L_EveryDay.difference)/60 AS TOTALHOUR ";
readSQL += "FROM T_OccasionName INNER JOIN (T_ProjectDetailName INNER JOIN (T_ProjectName INNER JOIN L_EveryDay ON T_ProjectName.seq = L_EveryDay.project) ON T_ProjectDetailName.seq = L_EveryDay.projectdetail) ON T_OccasionName.seq = L_EveryDay.occasion ";
readSQL += "GROUP BY L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd ";
readSQL += "HAVING (((L_EveryDay.username)=";
readSQL += '"';
readSQL += dbPICName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_ProjectName.project) LIKE ";
readSQL += '"';
readSQL += '%';
readSQL += ProjName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_ProjectDetailName.detail)=";
readSQL += '"';
readSQL += dbWorkName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_OccasionName.occasion)=";
readSQL += '"';
readSQL += dbElementName;
readSQL += '"';
readSQL += "AND ((L_EveryDay.ymd)=";
readSQL += "#";
readSQL += strReviewDay;
readSQL += "#";
readSQL += "));";
bstrQuery = readSQL;
MessageBox(NULL, readSQL, "Test", MB_OK);
try
{
recordSet_ = connection_->Execute(bstrQuery,&vRecsAffected,adOptionUnspecified);
}
catch( _com_error &errMessage )
{
MessageBox(NULL, "failed", "Test", MB_OK);
TRACE(errMessage.Description());
TRACE(errMessage.ErrorMessage());
TRACE(bstrQuery);
}
When the program executes the query, runtime error appears. im having difficulty on how to format the date during the query. In MS Access, the date is enclosed with #. So, i tried the same way too. I tried so many times on formatting the date but no one returns the good result.. so for those, who have encountered the same problem as I am, please help me with this...
what should I do with my query?
thanks in advance
|
|
|
|
|
Use parameters. You'll need a Command object. Parameters send the values directly to the database engine with no translation to and from ambiguous string formats.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Like Mike said, using parameters is best way to do it.
But if you want to build your own SQL statement, try to format the date into yyyy-mm-dd or yyyy/mm/dd format in the SQL statement, e.g. for Access it is #1999/05/24#
By using this you'll:
1. Remove ambiguity between date and month.
2. Remove error when passing date into SQL statement.
Cheers,
Edbert P.
|
|
|
|
|
what is the syntax to perform SELECT query on a stored View in an Access Data Base?
|
|
|
|
|
I am not too familiar with MS Access, but I wouldn't have thought it would be any different from a table:
SELECT * FROM view_name WHERE blah=blah
SELECT * FROM table_name WHERE blah=blah
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
I tried this way ,it didnt work (it works fine with sql server) .
thanks anyway , does anyone have a diffrent way?
|
|
|
|
|
I think it is the same syntax as the above submitted comment. It might be the same between a view in sql server and view in access. Because view in access is using access probject and view is store in the sql server. But some issue you should consern about such as:
1. Does your system connect to sql server database every time? and make sure that it is not interrup.
2. When create database object (table, query, view...) try to use naming convention. (Ex: if you want to create table name "Customers", name it "tblCustomers" instead of "Customers). Naming convention help you to identify the right database object in your SELECT sql statement.
3. Use bracket if your database object name has a space in the middle. (Ex: you have table named "Customer name", in SELECT statement use the bracket to [Customer name], SELECT * FROM [Customer name])
Check it again and told us if it still have a probme. Post an error message when you challenges with it again.
Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Problem solved, thanks a lot to both u guys.
|
|
|
|
|
How could you solve it? Do you found any other solution to solve it or get the solution from our idea? I just want to know, if our idea is right then I will help the other people who have the same problem like you. If it is wrong I will try to research on it. Any way, I still try all of my best to help you and other people. 
Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Using the brackets solved the exception in my query.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
