I am in the middle of writing a console application that acquires data from dynamic odbc connections and inserts the results into a MSSQL database.
I'm currently using a datareader to generate multiple calls to a stored procedure and batch execute them by means of a simple counter; this works fine.
However, I'm a little concerned that it seems to take so long to execute the sql stored procs and was wondering if anyone may know of any methods to help speed it up either on the app side or sql, or both.
I had a quick word with our dba who spoke briefly about some kind of process where by the application fires the request across to the database and carries on leaving the db to queue the request or something to that effect. He ran away before I could get any sort of sense out of him.
I've recently been given the task to maintain a website that runs off mssql using vb and asp .net. One of the main task is to improve the mssql access time. I am fairly new to mssql, can anyone give me some insight on the procedure to improve access time to mssql. Obviously sql query plays a big part, but what about database config, asp/vb .net commands or design patterns (for large amount of data pulling and join?)
Any suggestions or pointer will be greatly appreciated.
If I have an application running on MSSQL is it possible that if an action occurs on the application side that a stored procedure can execute a batch script?
I have a data gathering application written in MSVC++ 6 that uses ADO to insert large amounts of data into a table. Currently I have a stored procedure that inserts a single row at a time and I call it everytime I have more data to insert. However this can often fully load SQL Server - I often have 10's or 100's of inserts a second for short periods and load goes up to 100%...
Does anyone know a way of making the inserts more effiicient without resorting to dynamic SQL?
For example is there a way of batching up these inserts such as passing 10 at a time to the sp and inserting them all at once with an "insert into <table> select ..."?
Or would modifying my C++ and wrapping a block of inserts to the single insert sp in a transaction help?
A collegue suggested writing the data to a temporary text file then using bulk insert at regular intervals but that would then involve writing a file management system as well and seems to be a bit of a hack!
I'm using ASP.net to do a Select and I want to insert all the results into a table that is stored locally I can put an SP local but cant put on the other DB
How would i achieve this batch insert? is it possible?
Hi can any one help me with the skeleton script (sample one)of running Bulk insert in batches........ I need to do it in batches as the input data is huge.....
The logic is I have to insert thru bcp in fact table...
After that batch execution for 50,000 thousand record.... wise.... if any of the batch failes i need to identify and have to rerun from that point onwards...... this is OLAP thing...
I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?
How do you prevent this:"If you import a very large number of rows, dividing the data into batches can offer advantages. After each batch complete, the transaction is logged. If, for any reason, a bulk-import operation terminates before completion, only the current transaction (batch) is rolled back." How can I let the whole batch rollback, instead only the current transaction (batch) rolling back?
I'm using MS JDBC driver to connect to SQLServer 2005 and trying to perform batch insert. Here is the code i'm using:
Code Snippet
try { // Assume we have a valid con con.setAutoCommit(false);
// this is the simplified SQL for the purposes of this example StringBuffer sql = new StringBuffer(); sql.append("INSERT INTO temp_batchOpt ("); sql.append("temp)"); sql.append(" VALUES (?)");
// the correct number of insertions are made if(r != null) for(int x: r) System.out.println("inserted "+x);
SQLServerResultSet keyRS = (SQLServerResultSet)insertStatement.getGeneratedKeys(); while (keyRS.next()) { int id = keyRS.getInt(1); System.out.println("**filling id ="+id); } con.commit(); } catch (BatchUpdateException bue) { bue.printStackTrace(); } catch(SQLException sqle){ sqle.printStackTrace(); } finally { // close the connection... }
I'm getting the following exception:
Code Snippetinserted 1 inserted 1 inserted -2 inserted 1 inserted -2 inserted 1 inserted -2 inserted 1 inserted -2 inserted 1 com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be run before the generated keys are available. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(Unknown Source) at com.x.U.m.Transformer.prefetchData(Transformer.java:285) at com.x.U.m.Transformer.init(Transformer.java:51) at com.x.U.m.XMLParse.main(XMLParse.java:215)
I can get the getGeneratedKeys() to work fine with single update (not usign batch update). Is batch + getGenerated key not supported?
I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...
I have a full backup scheduled at 12.00AM ET and have a batch import on 11.55PM(5 min before full backup) which takes 30min to complete .Will the backup cover the data which is being imported?
I want to Insert 10000 rows at a time and commit in sql server , Isthere a way to do it if the source tables have no id fields ?What would be the most efficient method?ThanksAjay
I€™m facing a SQL batch insert issue on Windows 64 bit (IA-64) platform. I€™m using €˜Column-Wise Binding€™ of parameters. The example program hosted on Microsoft site (http://msdn2.microsoft.com/en-us/library/ms709287.aspx) returns error of SQL_NEED_DATA for batch insert on Windows 64 bit platform. The same code works fine on 32 bit platform. I€™m using SQL Server 2000 as the database and tried using both native SQL server ODBC driver and Merant 5.2 ODBC drivers. Any help on this will be greatly appreciated.
Hi,I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.What is the most efficient way to do this using C#2005 and mssql2000?Any good article or tutorial?
Hello Everybody,In my web application I have to insert html from editor. I have assigned the filed as "nvarchar". But after running the insert query it throws an error "A potentially dangerous Request.Form value was detected from the client (oEdit1="<P><SPAN style="FONT..."). " I'm using WYSWYG editor. I'm now quite bit confused about the the field and the query. If you have any idea about the problem please help me out of this problem.Thanks in option.Regards--Arindam
HelloCan anyone help me translate this from access so that it can work in mssql(i need to get next value, but cannot use identity as if row is deleted,another must get new next column number which would be same as deleted one)Access;INSERT INTO tableSELECT(IIF(code<>Null,MAX(code)+1,1) AS code,0 AS usercodeFROM tableI tried this in mssql but will not work:INSERT INTO tableSELECTCASEWHEN code IS NULL THEN 1ELSE MAX(code)+1ENDAS code,0 AS usercodeFROM table
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) If Page.IsPostBack Then binddata() End If End Sub Sub binddata() baglanti = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|datagoldbar.mdf;Integrated Security=True;User Instance=True") End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim cmd As New SqlCommand("INSERT INTO db " & _ "(ip,sehir,mesaj,email) VALUES (@ip, " & _ "@sehir,@mesaj,@email)", baglanti) cmd.Parameters.Add("@ip", SqlDbType.NVarChar, 50).Value = isim.Text cmd.Parameters.Add("@sehir", SqlDbType.NChar, 50).Value = sehir.Text cmd.Parameters.Add("@mesaj", SqlDbType.NText).Value = mesaj.Text cmd.Parameters.Add("@email", SqlDbType.NVarChar, 150).Value = email.Text baglanti.Open() cmd.ExecuteNonQuery() binddata()
End Sub Server Error in Object reference not set to an instance of an object. where is mistake there?
Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert. Can some one Help me how can i do it.. THX Sorry for my bad englisht
Hello there,I'm to asp.net, so please be patient :DMy question is, how do I simply add some data to my database? - With vb.net code, not a grid view or something like that..I want to connect to my database, insert some data to a table.It shouldn't be that hard?- Hope someone will take the 5 minutes, and help me :)Regards Jeppe
I created a link from Oracle to SQL server. There is a trigger with insert statment in Oracle side. I got error message as follow when trigger is invoked:
SQL> insert into test1 values ('zerbra','brazi'); insert into test1 values ('zerbra','brazi') * ERROR at line 1: ORA-02047: cannot join the distributed transaction in progress ORA-06512: at "GGWEB.TRIGGER2", line 4 ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'
my simple trigger is as follows: create or replace TRIGGER TESTRI AFTER INSERT ON TEST1 FOR EACH ROW BEGIN insert into test1@sqlserver (city,state) values ('what','nine');
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
anyone knows if there's an easy way to insert new GUIDs into uniqueidentifier cells when editing data in table on Management Studio Express? or Visual Studio 2005 Server Explorer? I can't find a shortcut, and copying from external tools is pain..
I suppose it's possible to build a VB macro in Visual Studio, would it work when editing table data?
I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.
I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.
Have this problem solution? May be, I must use another method to get my data?
P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:
sqlcmd -S (local)SQLExpress -i C:CreateDB.sql
This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
i was planning to create a database migration tool .. its a certain database of a DMS (document management system) to another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
