|
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
|
|
|
|
|
Firstly, Query Analyzer never times out - it keeps the connection open until SQL Server finishes responding. I believe osql is the same.
You don't say what API you're using to talk to SQL Server - ADO, OLE DB, ODBC, ... There's probably an option in the API to set a general timeout for communications with the database. You could extend this for a long-running query.
You should become familiar with the set-based versions of DELETE . The DELETE statement is very powerful and can handle any condition in the WHERE clause, not just equalities. I would rewrite your procedure as
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
DELETE FROM TestVorgaenge_T WHERE TestVorgangID = @testVorgangID
-- You could create a temporary table or table variable
-- for the SELECT part here
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 )
-- repeat for other tables
DELETE FROM Tests_T WHERE TestVorgangID = @testVorgangID
END
GO In general, set-based operations run far faster than cursor-based operations.
|
|
|
|
|
Hi,
You have been of (super-duper * 1000) help. Not only did you point me in the right direction about restructuring the SQL, but you were right about the CommandTimeOut property. I thought the setting on the SQL Server side was enough, but as usual incomplete knowledge proved dangerous
Thanks a lot,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
I wanna make a dbf file (foxpro database) from mdf(sql server database) with all data that are in it.
Is it possible? if yes how can I do that?
Thanks
|
|
|
|
|
You can use "Import AND Export Wizard" if you have SQL SERver2000.
Mazy
No sig. available now.
|
|
|
|
|
In Developer Studio Enterprise Architect you can run SQL scripts right from within the UI. In Dev Studio Professional, you can only run single statements in the query editor, and the "SQL View" completely re-formats the SQL each time you run it.
My question is, is there any way to get Dev Studio Professional to let you run full SQL scripts (from an SQL file rather than from that query view) like in the Enterprise Architect version without upgrading to the higher version? Is there an expansion that Microsoft (or anyone else) might sell just to allow that one feature?
Thanks!
~Steve
|
|
|
|
|
Hi All,
When we double click a .udl file it opens the configuration dialog for connectionstring. What if i would like to open this dialog box at runtime using C# or VB.net?
Thanx in advance.
sorry for my bad English.
|
|
|
|
|
Write click on your .udl file and choose OPEN WITH--->CHOOSE PROGRAM and then choose VS.NET to open it.
Mazy
No sig. available now.
|
|
|
|
|
Hi,
Infact i wana say that i would like to openthe UDL dialog on soem event e.g OnButton_Click eent i would like to Open this dialog and retrieve the connection string.
Can u provide some clue.
Thanx in Advance
sorry for my bad English.
|
|
|
|
|
I think this will work:
Process.Start("path to VS.NET","path to your .udl file");
But I'm not exactly sure ,maybe you need to pass file path in first argument too.
Mazy
No sig. available now.
|
|
|
|
|
Actually, he just wants to open the file with its associated program / shell handler, so the following should be used:
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "path/to/file.udl";
psi.UseShellExecute = true;
Process.Start(psi); ShellExecute(Ex) is what is executed when you double-click a file (or do whatever is the default action based on shell settings). You can also pass different verbs, like "open", "edit", "print", etc., so long as they are supported by the file type association in the registry.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
Take a look at: HOW TO: Build a Connection String Programmatically in ADO.NET by Using Visual C# .NET[^]
For a more reusable sample, try this code:
using System;
using System.ComponentModel;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using ADODB;
using MSDASC;
public sealed class DataLinkDialog : CommonDialog
{
private string _connectionString;
public DataLinkDialog(string connectionString)
{
_connectionString = connectionString;
}
public DataLinkDialog()
{
_connectionString = string.Empty;
}
public string ConnectionString
{
get { return _connectionString; }
set { _connectionString = value; }
}
public override void Reset()
{
_connectionString = string.Empty;
}
protected override bool RunDialog(IntPtr hwndOwner)
{
DataLinks dl = new DataLinksClass();
try
{
if (null == _connectionString || 0 == _connectionString.Length)
{
object ret = dl.PromptNew();
if (null == ret)
{
return false;
}
else
{
_connectionString = ((ADODB._Connection)ret).ConnectionString;
Marshal.ReleaseComObject(ret);
return true;
}
}
else
{
Connection cn = new ConnectionClass();
object ocn = cn;
try
{
cn.ConnectionString = _connectionString;
if (dl.PromptEdit(ref ocn))
{
_connectionString = cn.ConnectionString;
return true;
}
else
{
return false;
}
}
finally
{
Marshal.ReleaseComObject(cn);
}
}
}
finally
{
Marshal.ReleaseComObject(dl);
}
}
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
I am working on optimizing an application that uses stored procedures heavily.
Almost every procedure starts with:
SET DATEFORMAT dmy which causes a recompilation during the procedure execution. It takes about 5 seconds.
Is there any way to set the dmy as a default setting on the startup of the application? Any other ideas how to solve this problem?
I use MS SQL Server 2000.
modified on Monday, August 30, 2010 6:32 AM
|
|
|
|