please help me,my project......... - Database Discussion Boards

coge5-Jan-04 21:11

5-Jan-04 21:11

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

Re: Millions of records per day

DiWa5-Jan-04 21:49

DiWa

5-Jan-04 21:49

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

Re: Millions of records per day

Mike Dimmick6-Jan-04 2:49

Mike Dimmick

6-Jan-04 2:49

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.

Re: Millions of records per day

coge6-Jan-04 9:53

coge

6-Jan-04 9:53

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

Re: Millions of records per day [modified]

Marek Grzenkowicz8-Jan-04 6:06

Marek Grzenkowicz

8-Jan-04 6:06

Lately I had a problem with a stored procedure (not written by me Smile | :)

). 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

Export to XLS

mosquitooth4-Jan-04 21:49

mosquitooth

4-Jan-04 21:49

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

Re: Export to XLS

chinni16-Jan-09 23:24

chinni1

6-Jan-09 23:24

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()

Smile | :)

please help me,my project.........

Ahmed Gaser3-Jan-04 23:15

Ahmed Gaser

3-Jan-04 23:15

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.

Re: please help me,my project.........

preinsko6-Jan-04 18:55

preinsko

6-Jan-04 18:55

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.

DELETE [table] gives syntax error

Vaclav3-Jan-04 12:35

Vaclav

3-Jan-04 12:35

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.

Re: DELETE [table] gives syntax error

Guillermo Rivero3-Jan-04 17:45

Guillermo Rivero

3-Jan-04 17:45

remove the first semicolon in the second line...

DELETE * FROM [table]

This should work

Free your mind...

Re: DELETE [table] gives syntax error

winay7-Jan-04 2:08

winay

7-Jan-04 2:08

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

Sql server stored procedure timing out problem

pankajdaga1-Jan-04 10:51

pankajdaga

1-Jan-04 10:51

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 Smile | :)

Sincerely,
Pankaj

Without struggle, there is no progress

Re: Sql server stored procedure timing out problem [modified]

Marek Grzenkowicz2-Jan-04 5:19

Marek Grzenkowicz

2-Jan-04 5:19

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

Re: Sql server stored procedure timing out problem

andyharman7-Jan-04 11:09

andyharman

7-Jan-04 11:09

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

Re: Sql server stored procedure timing out problem

pankajdaga15-Jan-04 2:05

pankajdaga

15-Jan-04 2:05

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 Smile | :)

Pankaj

Without struggle, there is no progress

Re: Sql server stored procedure timing out problem

Mike Dimmick8-Jan-04 5:59

Mike Dimmick

8-Jan-04 5:59

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.

Re: Sql server stored procedure timing out problem

pankajdaga15-Jan-04 1:40

pankajdaga

15-Jan-04 1:40

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 Big Grin | :-D

Thanks a lot,

Pankaj

Without struggle, there is no progress

SQL to FOX

Majid Shahabfar31-Dec-03 6:09

Majid Shahabfar

31-Dec-03 6:09

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

Re: SQL to FOX

Mazdak31-Dec-03 19:06

Mazdak

31-Dec-03 19:06

You can use "Import AND Export Wizard" if you have SQL SERver2000.

Mazy

No sig. available now.

Running SQL in Developer Studio

Steve Schaneville31-Dec-03 4:23

Steve Schaneville

31-Dec-03 4:23

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

open UDL Dialog at runtime

Itanium30-Dec-03 1:59

Itanium

30-Dec-03 1:59

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.

Re: open UDL Dialog at runtime

Mazdak31-Dec-03 19:26

Mazdak

31-Dec-03 19:26

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.

Re: open UDL Dialog at runtime

Itanium31-Dec-03 21:45

Itanium

31-Dec-03 21:45

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 Smile | :)

sorry for my bad English.

Re: open UDL Dialog at runtime

Mazdak31-Dec-03 22:01

Mazdak

31-Dec-03 22:01

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.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.