|
Hi all,
I have an admin sproc that I've used for a long time; but apparently I never noticed that sp_depends doesn't return a failure value. I suppose over time I have just never checked a sproc that had no dependencies which is how I discovered this.
sp_depends raises an error if there are no dependencies unfortunately the return Value is 0 for all cases. Therefore using checking the return value is of no use. Since I use a return Value and a retCode in my sproc I was wondering if there is some system variable I can use to get the last error raised when exec a sproc from a sproc??
Currently I am checking for the table count and if it is 0 then I display the same message sp_depends would. I would rather the error number be returned in my return code so I can look up the mesage or have a custom message of my own.
Thanks
Pamela Reinskou
VersusLaw Inc.
|
|
|
|
|
The @@ERROR variable holds the last error number.
|
|
|
|
|
Yes, this is true; but in this case it is overwritten as soon as the next statement in the sp_depends sproc is executed. Therefore by the time my sproc receives the output the @@Error is 0 again.
I was thinking maybe there was some undocumented system function(s) that handles scope much like the identity functions, Scope_Identity and @@Ident_current.
You can check this for yourself by comparing the results of sp_depends on an object that has dependencies and one that doesn't, set a local var to return the returnValue and also try to grab the @@Error and you will see in all cases the returnValue is 0 and @@Error is 0. For now I am trapping it and using table count that seems to be the only way to figure it out.
Thanks.
Pamela Reinskou
VersusLaw Inc.
|
|
|
|
|
When I try to access "MS Access" .mdb database using "native" Microsoft.Jet.OLEDB.4.0 provider I'm experiencing a problem with deleting of multiple records from previously retrieved recordset.
Here is an example:
// first, I open database in very usual way using ATL-based classes (mostly generated by OLEDB consumer-template wizard in VS)
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
...................................
dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR(""));
dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, (LPOLESTR)szDBName);
dbinit.AddProperty(DBPROP_INIT_MODE, (long)16);
hr = db.Open(_T("Microsoft.Jet.OLEDB.4.0"), &dbinit);
if( SUCCEEDED(hr) )
{
hr = m_session.Open( db );
return hr;
}
// then I use generated accessor class
(something like class CProducts : public CCommand<caccessor<cproductsaccessor> >)
to open a rowset as a result of some SQL statement:
CProducts rs;
rs.Open();
// When I iterate through received recordset, processing data and deleting records using some criteria (or just all of them) like:
while( S_OK == rs.MoveNext() )
{
..................... // do something here
rs.Delete();
}
I receive "DB_S_ENDOFROWSET" just right after the second deletion, even though there is a lot more records in the recordset - without such deletion inside a loop I can successfully traverse entire recordset (all records) with no problems at all.
When I open DataSource using "MDASQL" as a provider instead of "Microsoft.Jet.OLEDB.4.0" everything works just fine (as it is supposed to work).
Initially blamed msjetoledb40.dll module, but installing the latest MSJETOLEDB40 Service Pack SP8 did not solve the problem.
Did I do something wrong, or somebody else had such problem and knew good workaround?
Best regards,
Andrey
|
|
|
|
|
I haven't seen this problem, but that's probably because I'm fairly new to this. Generally I've had good luck with a slightly different structure, though. Try
while (!rs.EOF)
{
.............//do stuff
rs.Delete();
rs.MoveNext();
}
and see what happens. According to MSDN,
"After a successful deletion, the recordset's field data members are set to a Null value, and you must explicitly call one of the Move functions in order to move off the deleted record. "
I know that your existing while loop is supposed to do this, but it may be that the Jet Database Engine is looking for something more explicit.
"Another day done - All targets met; all systems fully operational; all customers satisfied; all staff keen and well motivated; all pigs fed and ready to fly" - Jennie A.
|
|
|
|
|
I want to rename fields in the existing data table.
I am totally confused with ADO Fields deletions process I am using.
I delete all records from table - using SQL DELETE. The table may be empty already.
Than I close the current empty recordset - fields->delete wont't work on an open recordset (per documentation-I have not tried it with open recordset!)
The code ptrFields->Delete(_variant_t(Index)) appears to delete the field not "mark it" for deletion as the documentation states. If I advance the Index in the loop it skips every other field!
Before I attempt to do update I re-open the recordset.
When I do prtFields->Update() to actually delete the field I am getting "Not implemented " error. What does that mean?
When I do "recordset->Update" I get an info that I do not have a current record. ( Do I need a dummy record to get rid of this error?)
Can anybody point me to the right way to do this?
Thanks for your help. Vaclav
|
|
|
|
|
I need to update a database(B) with values from a stored procedure in another database(A). Both these databases reside on the same machine (apps). The closest example I found was using linked server, but that did not work for me, as I probably set it up wrong. The table I want to insert values to in database B is called tracking.
How is it possible to do this?
|
|
|
|
|
|
Hi
I'm trying to access some data from a table that has fields defined as 200 character long strings. Problem is that it appears as if the 200 characters are being used as a byte array with either 0xFF or 0x00 stored as the values. The presence of the 0x00 values effectively terminates the string if you try and read the field using a string variable so you only ever get the first part up to the first 0x00 character.
How can I read this field as an array of unsigned chars instead?
I am at present coding in C++, using MFC with an ODBC connection to the database which is a Btrieve database.
Many thanks for any help that you are able to provide.
Andrew
|
|
|
|
|
Sorry if this is the wrong place to post. I know this is more of a sql admin question then a programming question, but I figured the giant intellect that is code project should have a solution. We have a ms-sql server which is currently
getting an import of 2+ million records a day. the import takes any where from 1 to 2 hours. Which btw
is very impressive, but this requireds table locks
for that speed. Problem is that the data comes in
at a time early in the day, but is required for the
days processing. So users are getting locked out
of the db. Is this the best performance I can expect to get or are there other solutions. Also the
table has indexes, Ive heard this could be slowing it down a bit for the inserts. The machine has 2 gigs of ram and dual cpu's.
Thank you in advance
Take Care
Clay
|
|
|
|
|
There must be a better way, 1-2 hours is everything but impressive. Dont know much about SQLServer, but a small import like that is a matter of minutes on a properly configured Oracle.
First, turn off all indexes or drop them before starting the import, they definitely slow down record insertion and updates.
Second, try another interface / tool, but that strongly depends on the form of data you import...
Hope it helps,
dirk
|
|
|
|
|
It very much depends how you're doing it. For most large loading tasks, you should probably be using bulk loads, using the T-SQL BULK INSERT command or the bcp command line tool.
If your file isn't in a suitable state for BULK INSERT, maybe you should consider writing a program to convert it to a suitable state, then BULK INSERT.
If that doesn't work for you, you should at least reduce client-server communication as far as possible. Try to do as much in a single batch as you can. Perhaps you could use the SQLXML features - look up OPENXML in Books Online.
|
|
|
|
|
We are using bulk inserts, must be
the indexes, or something else. We will try your
suggestions. Thanks for offering your time
and suggestions.
Take care,
Clay
|
|
|
|
|
Lately I had a problem with a stored procedure (not written by me  ). It took ages to execute it.
After many hours in front of a computer I disovered that every time the SP was run the transaction log file grew to 10 GB (the whole DB was two times smaller!).
And it was all because there was a single INSERT command that inserted over 40 million fields. And SQL Server starts a transaction for every INSERT command.
modified on Monday, August 30, 2010 6:38 AM
|
|
|
|
|
Hi
I run the following SQL- statement in my VB.NET
application to export the results of a query to Excel:
g.CommandText = "SELECT * INTO [Excel
8.0;DATABASE=C:\test.xls;HDR=NO;].Table1 FROM [MyTable]
WHERE... 'g is a valid OleDbCommand object
This statement fails, if the xls- file already exists.
I've heard that the INSERT INTO statement could deal with this - could anyone
provide me some SQL- string that does the trick??
Thanks a lot!
Can anyone help me?
Gordon
|
|
|
|
|
See if the following works for you:
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Orders.xls;Extended Properties=""Excel 8.0;HDR=NO"""
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString )
ExcelConnection.Open()
Dim ExcelCommand As System.Data.OleDb.OleDbCommand = ExcelConnection.CreateCommand()
ExcelCommand.CommandText = "SELECT * INTO [Orders] FROM [Orders] IN '' [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_ Connection=yes];"
ExcelCommand.CommandType = CommandType.Text
ExcelCommand.ExecuteNonQuery()
ExcelConnection.Close()
|
|
|
|
|
Dear friends:
My team has the following task to complete.A solution that consists of 3 projects :
1-Win app that connected to a local DB server(SQL server) .
1-Web app that connected to a remotre server(SQL server).
1-Mobile app for that connected to tha same remote server of the web app.
The Details:
1-The win app deals with a DB(Local DB SQL server) for updating ,retriving,inserting and deleting.
2-The web & the mobile app deal with a DB(Remote DB SQL server) for tha same purpose.(updating ,retriving,inserting and deleting)
The Goal:
1-The Win app will connect one time per day to update the Remote SQL server with the new data from the Local SQL server.
2-The Web and mobile app connected to the Remote SQL server will update ,retrieve ,delete and insert records in it.
The Questions:
1-If I can use Asp.net web services ,How can I use it?
2-How can i make daily transaction to update the Remote SQL server with the new data at the Local SQL server??
3-How can i make daily transaction to update the Local SQL server with the new data at Remote SQL server ??
4-How can i manage the DB Transactions ?
5-how can i use the same methods(code) for dealing with the DB ,for the win app , the web app and the mobile app?
5-What is the best way to complete this solution?
Thanks for reading to this line.
I hope you can help me.
thanks again.
Best Regards
Ahmed Gaser
FCSIS
IS Dept.
|
|
|
|
|
Not much difference between the two, I am assuming you may not know how to get to the remote server.
Use three config keys in your web.config file.
Define the regular server name for the local server and use the IP addresses of the remote server.
You can also set the servers up as linked servers and then one can talk to the other.
I believe you could look into subscription service as well.
As for using the same classes you can by compiling a separate dll and importing it into all three apps. Keep the class limited to only returning data or manipulating data and you should be fine.
At the application level check to see which application you are using or set a constant that you use when obtaining the data source name.
Hope I understood the question and that this helps. I need a break anyway.
Pamela Reinskou
VersusLaw Inc.
|
|
|
|
|
I am trying to delete all records from a table and I am getting "Sytax error in FROM clause" using the following SQLs:
CString strSQL = "DELETE [table];";
CString strSQL = "DELETE FROM [table];";
CString strSQL = "DELETE * FROM [table];";
CString strSQL = "DELETE * FROM [table] WHERE [table].[field];";
CString strSQL = "DELETE * FROM [table] WHERE [table].[field] >= 0};";
I am using jet 4.0 ( Access 2000) ODBC and ADO. I have no other problems accessing and manipulating my database.
According to SQL VC6 help the first SQL statement should delete all records from tasble and the FROM is optional. After searching the Internet I have discovered that the WHERE clause need to be used in similar SQL's. But none of the statements I have tried worked. I am using Open method with the SQL string. What am I missing?
Thanks for your help. Vaclav
Corrections:
Forgot to change parameter adCmdTable to adCmdText in the Open method I have been using to access tables! Feel pretty stupid about that. However, the SQL will not work without using FROM. I got "DELETE FROM [table]" to delete all records from table.
|
|
|
|
|
remove the first semicolon in the second line...
DELETE * FROM [table]
This should work
Free your mind...
|
|
|
|
|
hi,
The problem arises if amount of data is too large. The problem is of memory.
if you are using adodb recordset then this problem arises.
Instead ty this:
if cn is the connection
cn.execute "DELETE * FROM [table];"
--- this may be one of the reason--------
The other reason may be...........
CString strSQL = "DELETE * FROM [table] WHERE [table].[field] >= 0};";
is the last semi colon required.
warm regards
Winay Shetty
|
|
|
|
|
Hi,
I have a stored procedure problem. I have a C++ application that calls a stored procedure. However, many times the application just terminates as the stored procedure times out. It does not happen all the times, but quite frequently (~30%). It never happens when I run the stored procedure from the SQL server osql console app. I do not understand why it should time out. Any suggestions on this would be highly appreciated.
Here is the stored procedure:
--------------------------------------------------------------------
CREATE PROCEDURE DeleteTicketData
@ticketNum int AS
declare @testVorgangID int
-- delete errors
DELETE FROM TestVorgangEinleseFehler_T WHERE TicketNumber = @ticketNum
Select @testVorgangID = TestVorgangID from TestTickets_T WHERE TestTicketID = @ticketNum
IF (@testVorgangID IS NOT NULL)
BEGIN
declare @testID int
DELETE From TestVorgaenge_T where TestVorgangID = @testVorgangID
DECLARE tests_cursor CURSOR FOR
SELECT TestID FROM Tests_T WHERE TestVorgangID = @testVorgangID
Open tests_cursor
-- Perform the first fetch
FETCH NEXT FROM tests_cursor INTO @testID
-- as long as there are more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM TestAktionen_T Where TestID = @testID
DELETE FROM TestDSPACETable_T Where TestID = @testID
DELETE FROM TestEreignisse_T Where TestID = @testID
DELETE FROM TestZustaende_T Where TestID = @testID DELETE Tests_T WHERE CURRENT OF tests_cursor
FETCH NEXT FROM tests_cursor INTO @testID
END
CLOSE tests_cursor
DEALLOCATE tests_cursor
END
GO
--------------------------------------------------------------------
I am totally puzzled as to why it should time out some of the times. Any suggestions would be really appreciated.
Thanks
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Just a quick idea.
I did not analyze your code now, since I have no time right now.
But I had similar problem when calling a SP from VB app on Win98 (it worked fine in Query Analyzer). It disappeared after reinstallation of MDAC.
modified on Monday, August 30, 2010 6:31 AM
|
|
|
|
|
Hi Pankaj
Your SP would probably run faster if written as:
-----------------------------------------
CREATE PROCEDURE DeleteTicketData @ticketNum INT
AS BEGIN
DECLARE @testVorgangID INT
DELETE FROM TestVorgangEinleseFehler_T
WHERE TicketNumber = @ticketNum
SELECT @testVorgangID = TestVorgangID
FROM TestTickets_T
WHERE TestTicketID = @ticketNum
IF (@testVorgangID IS NOT NULL) BEGIN
DELETE FROM TestVorgaenge_T
WHERE TestVorgangID = @testVorgangID
DELETE FROM TestAktionen_T WHERE TestID IN (
SELECT TestID FROM Tests_T
WHERE TestVorgangID = @testVorgangID)
DELETE FROM TestDSPACETable_T Where TestID IN (
SELECT TestID FROM Tests_T
WHERE TestVorgangID = @testVorgangID)
DELETE FROM TestEreignisse_T Where TestID IN (
SELECT TestID FROM Tests_T
WHERE TestVorgangID = @testVorgangID)
DELETE FROM TestZustaende_T Where TestID IN (
SELECT TestID FROM Tests_T
WHERE TestVorgangID = @testVorgangID)
DELETE FROM Tests_T
WHERE TestVorgangID = @testVorgangID
END
END
GO--------------------------------------------------------
SQL-Server tends to hand set-operations much faster than cursor-based operations.
Additionally, you might want to look at the "timeout" property of your command object. This defines how long your client will wait before assuming that your stored-procedure has taken too long.
Hope this helps.
Andy
|
|
|
|
|
Hi Andy,
You and Mike are right on target. The stored procedure performs much faster with your suggested improvement and the ADO Command object had a CommandTimeOut property that I was not aware of.
Thanks a mega ton
Pankaj
Without struggle, there is no progress
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
