Increase The Speed Of Insert Statment
Hi all
i'm using sqlserver 2005
this statment take 1:30 min to execute
****insert into temotable (select key from table1)
if i used a select statment alone it takes 4 sec
but with insert statment it take 1:30min
by the way i put indexes on the table1
plz how i can increase the speed of insert statment.
thanks in advance
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Database Optimization (Increase Speed)
Well good morning/afternoon to everyone. It's been a while sinse I've posted here and it seems that the site is a lot faster now. Good to see. :) Anyways, I'm working a current problem here at work with our database being quite slow. I've done some research already and will continue to do so but i wanted to get some of your opinions. Right now, I've run the 'DBCC SHOWCONTIG' command and it is telling the following in the first 3 system tables: DBCC SHOWCONTIG scanning 'sysobjects' table... Table: 'sysobjects' (1); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 34 - Extents Scanned..............................: 12 - Extent Switches..............................: 33 - Avg. Pages per Extent........................: 2.8 - Scan Density [Best Count:Actual Count].......: 14.71% [5:34] - Logical Scan Fragmentation ..................: 41.18% - Extent Scan Fragmentation ...................: 83.33% - Avg. Bytes Free per Page.....................: 2303.6 - Avg. Page Density (full).....................: 71.54% DBCC SHOWCONTIG scanning 'sysindexes' table... Table: 'sysindexes' (2); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 72 - Extents Scanned..............................: 16 - Extent Switches..............................: 59 - Avg. Pages per Extent........................: 4.5 - Scan Density [Best Count:Actual Count].......: 15.00% [9:60] - Logical Scan Fragmentation ..................: 50.00% - Extent Scan Fragmentation ...................: 81.25% - Avg. Bytes Free per Page.....................: 4184.9 - Avg. Page Density (full).....................: 48.30% DBCC SHOWCONTIG scanning 'syscolumns' table... Table: 'syscolumns' (3); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 323 - Extents Scanned..............................: 50 - Extent Switches..............................: 299 - Avg. Pages per Extent........................: 6.5 - Scan Density [Best Count:Actual Count].......: 13.67% [41:300] - Logical Scan Fragmentation ..................: 48.61% - Extent Scan Fragmentation ...................: 96.00% - Avg. Bytes Free per Page.....................: 4527.0 - Avg. Page Density (full).....................: 44.07% DBCC SHOWCONTIG scanning 'systypes' table... Table: 'systypes' (4); index ID: 1, database ID: 6 TABLE level scan performed. - Pages Scanned................................: 1 - Extents Scanned..............................: 1 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 100.00% [1:1] - Logical Scan Fragmentation ..................: 100.00% - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 6712.0 - Avg. Page Density (full).....................: 17.07% According to the DBCC SHOWCONTIG command documentation, there should be no fragmentation at all. Some questions: 1. would system performance be severly negatively reduced with the above fragmentation (logical and extent)? 2. can the 'DBCC INDEXDEFRAG(dbname, tablename, indexname)' command be issued against those system tables without consequences? 3. is there some other command that can defrag the entire database without having to specify which tables? Also, I have also used the index tuning wizard after a profile trace but that failed with some unknown error. Thats it for now, please let me know if you have some info I could use to help speed up my database.
View Replies !
View Related
Sql Insert Statment
i have 3 tables in sql the relation between them is one to one person ==> employee ==> sales_department_stuff& another relation ( one to one )between person(the same as above ) ==> customer i want to put person id in employee not repeated in customer becase all (employee and customer is a person ) sooooooo i put this sql statment to try to insert person id in employee then in sales_department_stuff table to complet his ordersinsert into Person (Person_Name_Ar,Person_Name_En) values ('aaaaa',' ssssssss')select @@identity from Personinsert into Employee values (@@identity,'1') select @@identity from Personinsert into sales_department_Staff values (@@identity,'1')select @@identity from Personinsert into Driver values (@@identity,'2','2222','1/1/2005','5') /* SET NOCOUNT ON */ RETURN the first 3 statment is run and success but underline stetment return error that he can insert null value in id field ,,,,,,,,,,,,,,,, he can compile @@identity in these statment ?????????????? plz help me
View Replies !
View Related
INSERT STATMENT
Hi All, I have a table "Person" that has two columns "FirstName" and "LastName". How can I insert multiple rows into this table using the INSERT stsmt. I want to use just one insert statement. Thanks in advance, Vishal S
View Replies !
View Related
Insert Statment For A One-to-one Relationship
I have two tables that have a one-to-one relationship. The reason they are two table is that one table has user data, whereas the other table has security information. The security information will be in a different security zone. How do I do an insert into the two tables so that I do not get an integrity error?
View Replies !
View Related
Subqueries In Insert Statment
Hello, How can I do something like: Insert Into Displayed(snpshot_id, user_id, user_domain, ddisplay, iWidth, iHeight, disp_size, disp_format, watermark, frc_update, creditcost) Values ((Select snpsht_id from SiteIndex Where user_domain like '%domain'), 1000, (Select domain_id from UserDomains Where user_domain like 'domain'), GetDate(), 480, 360, 2, 1, 1, 0, 3) Im getting an error: Subqueries are not allowed in this context. Only scalar expressions are allowed. Thanks!
View Replies !
View Related
Insert Statment Not Working
I know this is a sin in dbforums to jump forums to ask other forum questions, but I just had to do it..... it's actually related to foxpro dbf tables. Here is the case: I am opening this existing DBF file in Microsoft Visual Fox Pro 6.0 . In the command window, select, update and even including delete statements works . What is getting on my nerves is the "insert" statement. It always prompts "syntax error". But the @#$@#$ error message just didn't help much. The funny thing is, if I use the "Append Mode" and add data directly via the GUI, it works!. Here is the insert statement, just a very simple one: <code> INSERT INTO ASSET (ACCNO) values '2000/141'</code> You can reply me here , or go to the real thread to reply me if you can help out..thanks http://www.dbforums.com/t1058508.html
View Replies !
View Related
Question On Insert Statment
Hello, I have a table (SQL Server 2000) which contains data and I want to create insert statements based on this data. The reason for this is to create a .sql file to run this against another database to insert the same data. I know I can do an import but was trying to do this via a .sql script. Is there any way to generate these insert statement automatically using some SQL tool? Thanks
View Replies !
View Related
Passing A Querystring To A Insert Statment.
Hi, I'm having problems passing a querystring from the datanavigateurlformatstring to be used in a insert sp on a webform. Please can someone take a look at my code and point me in the right direction. At the moment I'm gettingthis error messageCompiler Error Message: BC30451: Name 'userid' is not declared. but in the url i can see the querystring I've passed from the previous page. Thanks in advance Dave 1 Protected Sub Execute_Clicked(ByVal sender As Object, ByVal e As EventArgs) 2 3 Dim objConn As New System.Data.SqlClient.SqlConnection() 4 objConn.ConnectionString = "My connection string" 5 objConn.Open() 6 Dim objCmd As New System.Data.SqlClient.SqlCommand("test_insert", objConn) 7 objCmd.CommandType = System.Data.CommandType.StoredProcedure 8 9 10 Dim puserid As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@userid", System.Data.SqlDbType.Int) 11 Dim pdate As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@date", System.Data.SqlDbType.DateTime) 12 Dim papplicants As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@applicants", System.Data.SqlDbType.Int) 13 Dim pclients As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@clients", System.Data.SqlDbType.Int) 14 Dim pcontacts As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@contacts", System.Data.SqlDbType.Int) 15 16 17 18 19 20 puserid.Direction = System.Data.ParameterDirection.Input 21 puserid.Value = Convert.ToInt32(userid.QueryString) 22 pdate.Direction = System.Data.ParameterDirection.Input 23 pdate.Value = Convert.ToDateTime([date].Text) 24 papplicants.Direction = System.Data.ParameterDirection.Input 25 papplicants.Value = Convert.ToInt16(applicants.Text) 26 pclients.Direction = System.Data.ParameterDirection.Input 27 pclients.Value = Convert.ToInt16(clients.Text) 28 pcontacts.Direction = System.Data.ParameterDirection.Input 29 pcontacts.Value = Convert.ToInt16(contacts.Text) 30 31 32 33 command.ExecuteNonQuery() 34 35 36 objConn.Close() 37 38 End Sub
View Replies !
View Related
Function Call In Insert Statment
Hi i m trying to call a function in insert statment Insert Into (value, value1) Value(@value, dbo.function(@value1) dbo.function returns a value, when i test the function in querry builder all goes fine. In my program i become a error "Parameterized Query '' ' expects parameter @value1 , which was not supplied." I m using visual studio , tableadapter.update function to insert datarecords in db thx for help
View Replies !
View Related
Insert Into Statment Kills My Database Connection?
Hi Simple statment is giving me a logout on the server INSERT INTO Angebot (Nummer,Variante,Bearbeiter,Datum,Geld,Kurs,Language,Flag,AngebotStatus,TStatus) VALUES (90360,3,'Admin',DEFAULT,'CPI_BE-D',1,'CPI_AG-1',4,NULL,59) The statment works fine on production, however i have played a backup from production to my laptop to test something and as soon as I add a record to the database my connection to the database is droped (the data is not added to the table). the error message I get is double, however I execute the statement only once. Msg 0, Level 11, State 0, Line 0 Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse. Msg 0, Level 20, State 0, Line 0 Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse. Sugestions anyone? PS: the message in English is: Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
View Replies !
View Related
How Can I Speed Up My Multiple INSERT's?
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! Any help much appreciated.
View Replies !
View Related
How To Speed Up Batch Insert MSSQL
Hi, 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. Any help greatly appreciated Thanks
View Replies !
View Related
SQL UPDATE/INSERT Statement Speed
Hello, Server hardware: Intel core 2 duo, 2Gb ram, SATA 1500Gb, SQL Server 2005 I have big table (~ from 50000 to 500000 rows) : Code Snippet CREATE TABLE [dbo].[CommonPointValues]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [pointID] [bigint] NOT NULL, [pointValue] [numeric](10, 2) NOT NULL, [qualityID] [int] NOT NULL, [dateFrom] [datetime] NULL, [dateTo] [datetime] NULL, CONSTRAINT [PK_PointValues] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] ) ON [PRIMARY] UPDATE statement: Code SnippetUPDATE dbo.CommonPointValues SET dateTo = @last_update WHERE ID = @lastValID 2000 rows update takes about 1 sec. INSERT statement: Code Snippet INSERT INTO dbo.CommonPointValues (pointID, pointValue, qualityID, dateFrom, dateTo ) VALUES (@point_id, @value_new, @quality_new, @last_update, @last_update ) Speed with INSERT is similar like with UPDATE. I need about 10000 updates per 1 second and 10000 inserts per 1 second or together 5000 inserts and 5000 updates per 1 sec. Should I update hardware or try some improvements to table structure.
View Replies !
View Related
How To Replace Single Quote In INSERT Or UPDATE Statment Using SqlDatSource?
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior. For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this? Thank you
View Replies !
View Related
Should I Install Oracle 64 Bit OLE DB Drivers To Speed Up Data Flow Speed.?
I'm experiencing extremely slow transfers of data from oracle to SQL in my DTS package. I'm using a data flow task and regular OLE DB connections for source and destination. Linked Servers: I'm trying to time the amount of time it takes to run the query against the oracle server. There is only one Oracle driver in the list of drivers for creating a new linked server "Oracle Provider for OLE DB" which is weird since I have both the 32 bit and 64 bit ORaclient drivers installed (I'm assuming visual studio can only detect the 32 bit drivers) If I create an Oracle linked server using this driver, the query seems to work but then produces a weird error. Msg 9803, Level 16, State 1, Line 1 Invalid data for type "numeric". However creating a linked server with the "Microsoft OLE DB Provider Oracle" ...works but is still extremely slow. Are there any settings/property (increase buffer/increase packet size) changes I can make to the OLE DB Oracle source/package/workflow task to speed things up? There are no indexes in the table I am importing into. I am using a simple sql select statement (select * from table). Pulls from sql are over 11 times faster than pulls from Oracle and I need to pull around 23 million rows. Has anyone come across the linked server driver issues? I'm using a 64 bit server with windows server 2003 64 bit version. Please advise. Thanks
View Replies !
View Related
¿What Improves SQL Server Performance? HD Speed, Processor Speed Or Ram?
Hi, 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... Thanks, Luis Luevano
View Replies !
View Related
Increase
Choose an EEO-1 Classification: Increase all employees salaries that have: the selected EEO-1 classification by 10%. Increase all employees salaries by 5% Hi! I am new to SQL, but trying to teach myself. I have had lots of help from you guys and appreciate it very much. Today, I was trying to do the following: In my table of "Employees", I have a column called classifications. I want to increase the salaries of the employees with a classification of EE0-1 by 10%, but I have not figured out to do so. My second question is how would I increase all employees' salaries by 5%? Anyone can help? Thanks! Scott
View Replies !
View Related
Increase Number By 1
Hello all, I have, what i think, is a unique problem that i'm hoping some of you can help me on. I need to create a record number that is incremented by 1 whenever someone adds a new record to the database. For example, records numbering 1,2,3 are in the database. When the users adds a new record, SQL takes the last recordno, 3 in this case, and adds 1 to it thus producing 4. Also, i need to have the ability to replace deleted record numbers with new ones. Using the example above, say a user deletes record number 2. Whenever someone adds a new record, sql would see the missing number and assign the new record that number. I hope i'm making sense here. Does anyone have any ideas about this? Any articles on the web that someone could point me to? Thanks. Richard M.
View Replies !
View Related
Error Log Increase From 6
I tried to increase the error log value from 6 to great than 99. I did this by adding a registry entry however it does not seem to work. I tested this by stopping the server and restarting it, forcing it to hopefully add an additional error log to the 6 that were already there, but nothing happened. How can I verify that the registry addition took effect, as Enterprise Manager won't give me the correct error log value.
View Replies !
View Related
DB Size Increase
Hello, I have observed an unusual happening in the past couple of weeks. I added two rows to a table in the database called shift_code and shift_name. Both were of type varchar and 14 and 7 in size, with Nulls allowed. Since this addition my Database has soubled in size from 100 to 200MB in size. The transaction Log is dumped every 12 hours and a full backup is done every 24 hours. After 5 days the DB returned to it original size but then after archiving data it has returned to 200MB in size and has remained that size for over a week now. Can anyone explian what is happening here. There are 1,117,064 rows in total in my Database of average length 80chars. Any comments or explainations would be greatly appreciated. Regards Alan Shinnors
View Replies !
View Related
Sp To Increase Value Of Index
Can someone tell me how I can write a stored procedure that will automatically increment the value of the index by 001? I am attempting to build a table for a menu system and I need to increment the index value of the document by 001 when submitted to the database. I also need to have the script obtain the last index value of the node before inserting new value. All ideas appreciated. Sincerely, Tim If interested, this requested is based upon an article written by Michael Feranda: http://www.pscode.com/vb/scripts/ShowCode.asp?txtCodeId=7321&lngWId=4
View Replies !
View Related
How Do You Increase The Value For: DTS_I_CHOSENMAXEXECUTABLES
We are getting the following error on an SSIS package: "54 Diagnostic VirtualSQLName DomainUserName Load Daily 859CF005-CB7F-47D8-8432-AE7C074B343C 1A986F18-343F-4424-ABAB-AC6575187DF3 2007-02-14 10:05:42.000 2007-02-14 10:05:42.000 0 0x Based on the system configuration, the maximum concurrent executables are set to 4. " Basically it is saying the we have reached the maximum amount of executables. How can we increase this value and where? Thanks
View Replies !
View Related
How To Increase The DB Size
hi, I have one DB in SQL Server 2005.When I was creating it takes 7813 MB.Now the databse size almost full.Now I want to extend the DB Size.At my HDD has more space(near by 160 GB).But I don't know how to extend the Size.More over I had one doubt.Am executing the exec sp_helpdb command.It shows the Datafile maxsize is Unlimited.I want to know when the data is full,whether it automatically takes the size from the HDD or not. Please Help me out to this Problem.
View Replies !
View Related
Increase Scope Of CTE?
I am really starting to like CTEs. My only qualm is that you can only use them within the first statement after you declare them. I wish they would remain active for the duration of the sql batch just like everything else (variables,local temp tables, ect). Is there any trick so that you can use them multiple times? Maybe define them as a string and do dynamic sql or something like that? Hopefully (i have not researched the new version of sql server) in SQL Server 2008 the scope has increase. Anyways any help would be appreciated. thanks, Ncage
View Replies !
View Related
How To Increase The SQL Server Memory
We are using SQL server 7.0, our users use to connect to SQL server remotely using Visual Basic Application. When all the users are connected it keeps on increasing the memory utilization and at 1,836,848 KB memory utilization it stops increasing the memory i.e memory utilization become stangnet. The actual amount of physical Memory is around 5GB but SQL server dont increase it after the treshhold level and the performance of the server get affected badly. Any one please suggest me the possible solution.
View Replies !
View Related
How To Increase Size Of Datatype...
Dear I am using varchar data type in my table.it provides maximum 8000 character. but i want more than that. how to increase size more than 8000 i want to use text datatype but when i type in length it doenst type.it displays only 16.i cant change to another value. please help me out Waiting for reply Regards, ASIF
View Replies !
View Related
Increase Segment Size
Hi all, I encountered error 1105 (Can`t allocate space for object .....) while running a query. I had dump transaction with no log sucessfully and retry running my query but still encounter error 1105. I had checked my database size and following are the info: Datasize : 2500MB Database size available : 753.39 Log space : 500MB Log space available : 500MB (this was acheive after after dump trans. twice. it was around 480MB before) Thus looks like the data segment is full!!! I expanded the size of the disk device D on which the segment device A is on by 50MB. When I did a sp_helpdevice D, the expansion is reflected. Next I run sp_extendsegement, A, D. However running sp_helpsegment A doesn`t show that segment A is expanded. Please help!!! How do I expand the size of the segment? (without creating a new disk device, is it possible) Any other way to resolve this problem? Also how to check if a particular segment is full? eg. the %usage of data/log segment. Thanks in advance.
View Replies !
View Related
How Do I Increase Font Size In 6.5?
I seem to remember that the font size and type can be controlled in SQL Server 6.5 and the process controlls the fonts displayed throughout the program - Enterprise Manager, Query Tool, etc. I have just re-installed 6.5 and the fonts are way too small. Can anyone tell me how this is done? Thanks. Jack
View Replies !
View Related
How To Increase The Maximum No. Of DB Processes?
I am receiving an error from my ODBC driver “Maximum number of DBPROCESSes already allocated.” I confirmed that there are 25 connections and that this is the default. This is caused by error message 10029, SQLEDBPS, when the maximum number of simultaneously open DBPROCESS structures exceeds the current setting. I would like to increase this maximum. I have found only two ways to do this. One is using dbsetmaxprocs using C and the other is using SqlSetMaxProcs using Visual Basic. My problem is that I am interfacing to SQL Server using a third party tool that is doing the lower level programming. Is there some way that I can increase the maximum number of DB processes for all databases that are part of the SQL Server 7 environment, or can I set this value using a program that is called from a stored procedure? Any ideas in this area will be greatly appreciated.
View Replies !
View Related
How To Increase Objects' Numbers In MTS
Hi everybody: I did post this in MTS Discussion Group and I am also seeking help here. I used Microsoft Management Console to monitor the Objects of the Components. The maximum numbers of Objects I can monitor is 20 and I can not see any more of that even our Web visitors incresing from 100 to 500. Can anybody tell me what type of setting I should do to increase the limit of Objects in MTS? Thank you very much. Rich
View Replies !
View Related
Cursor Will Increase Performance Or Not
"Cursor provide row-by-row level processing and it will store the result sets in 'TEMPDB' database". (Because of this) or (By using Cursor in Triggers or Stored Procedures) the performance will increase or performance will come down?. I am thankful if I get a good reason for this? Srinivasan
View Replies !
View Related
Increase 'View' Performance
Hi, I created a view on two huge tables. I tried to run a simple SELECT statement on this view and it took me several hours to obtain the result. How can I improve the performance of a view? The view should make use of the indexes built in both table, am I right? Thanks.
View Replies !
View Related
How To Increase SSIS Performance
Hello again, I'll just throw my question: how could I increase SSIS-performance? I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking). Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took. Any ideas? Greets, Tom
View Replies !
View Related
Increase Transaction Log Size
Hi, How can I increase the transaction log size of my sql server 2005 database? i can execute the shrink and backup log which can shrink the .ldf file to 1MB. However, since my dts involves a lot of inserts and updates, the my transaction log grows to 700MB in the middle of my dts execution. How can i increase the transaction log size maximum capacity to probably 1.5GB just so it can accommodate my full dts execution? BTW, this is my commands to shrink the log file: DBCC SHRINKFILE(<dbname>_log, 1) BACKUP LOG <dbname> WITH NO_LOG DBCC SHRINKFILE(<dbname>_log, 1) cherriesh
View Replies !
View Related
Cant Increase Column Size
hey i've a db running sql server express sp2. the db size now is 1.1 gb i've a table with a varchar column of size 20 . when i try to increase the column size to 50 i get a timeout exception, and the the cloumn size is unchanged. this table has 2.5 million records i use sql server management studio express to do the changes is there a way to increase this timeout or whtever i can do to update this column size? thx in advance
View Replies !
View Related
Rapid Increase In Connections
HI guys, I have a very simple application which uses SQL server 2000. the application is developed in .Net 1.1 and has a class which access the Database services. for your convience i attach the code snippet of one of the methods which retrieves records from the table Public Function ExecuteQuery(ByVal SpName As String, ByRef ParaCollection As SqlParameter()) As Object Dim RetVal As Object Dim cmdQuery As New SqlCommand Dim Parameter As SqlParameter cmdQuery.Connection = m_cnDB cmdQuery.CommandText = SpName cmdQuery.CommandType = CommandType.StoredProcedure For Each Parameter In ParaCollection cmdQuery.Parameters.Add(Parameter) Next Parameter m_cnDB.Open() Try RetVal = cmdQuery.ExecuteNonQuery() Catch ex As Exception DBUtilities.WriteError(ex) Throw ex Finally If (m_cnDB.State = ConnectionState.Open) Then m_cnDB.Close() cmdQuery.Dispose() m_cnDB.Dispose() End Try ExecuteQuery = RetVal End Function The problem that i face is the number of connection increases rapidly dispite my code closing the connection and later disposing it as well. this has become a big porblem since once the max conections are reached the Database doesnt allow any more users to connect. Do any of u'll have any idea as to why it is happening
View Replies !
View Related
|
TRACKER
