Problem with Stored Procedures | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem with Stored Procedures

In my vb program i am inserting data from one database to another database (both are remote databases) by using two stored procedures. One for selection and another one for insertion.
In my insertion stored procedure i am using two fields with text data type and no other person is using both the databases.
My database size is 37667.31 MB (which i am inserting data). Now it is not inserting data into that table.
When i am trying to execute that stored procedure it is giving Timeout Expired Error.
I am not able to under stand that problem. How to solve that problem? Plz help me out. Thanks in Advance Lalitha.C

Are you trying to export data from One server to other?
Then you can try this. Create sp having this code Insert into yourtable –Server2
‘SELECT * FROM Question.dbo.CQuest’ ) AS a –Server1 If this does not help your case, Post the code you used at the sp Madhivanan Failing to plan is Planning to fail
I think the problem may be bcoz of hard disk full.
Try to select records from the table in which You are inserting records.
Check whether that return records or not just to verify its full or not.
Hi,<br /> I have to check whether that record exsists in that database or not because previously i inserted records from other databases also. So to prevent the duplicates i need to check and i have more space in hard disk because we use one server for only database.<br /><br />here is my stored procedures:<br /><br />1. select_savesumes1:<br />———————<br />CREATE procedure select_saveresumes1 <br />( <br />@rescnt bigint <br />) <br />as <br />begin <br /> set nocount on <br /> select top 10 id,site,link,resume,cname,email,posting_dt,phone,jobtitle,education,salary,notes,phone2,state,zipcode,relocation,relstates,workaut,emptype, securitycle,updated,postingdt_str from saveresumes where id &gt; @rescnt and site&lt;&gt;’computerwork’ <br /> <br />end <br /><br />2. insert_247touniversal1:<br />————————–<br />CREATE procedure insert_247touniversal1 <br />( <br />@id bigint, <br />@site varchar(20), <br />@link varchar(2000), <br />@resume text, <br />@cname varchar(200), <br />@email varchar(200), <br />@saved_dt datetime, <br />@phone1 varchar(40), <br />@phone2 varchar(40), <br />@jobtitle varchar(200), <br />@education varchar(200), <br />@salary bigint, <br />@state varchar(5), <br />@zipcode varchar(10), <br />@relocation varchar(3), <br />@relstates varchar(300), <br />@workaut varchar(3), <br />@emptype varchar(10), <br />@securitycle varchar(10), <br />@notes varchar(450), <br />@saveddt_str varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, <br />@posting_dt datetime, <br />@res text, <br />@updated varchar(1), <br />@type varchar(5), <br />@result int output <br />) <br />as <br />begin <br /> set nocount on <br /> declare @pdate datetime <br /> SET ANSI_WARNINGS OFF <br /> set @result=0 <br /> if @email&lt;&gt;” <br /> begin <br /> if exists(select ‘true’ from resumes where [email protected]) <br /> begin <br /> select @pdate=posting_dt from resumes where [email protected] <br /> if @posting_dt &gt; @pdate <br /> begin <br /> update resumes set [email protected], [email protected], [email protected],[email protected], [email protected],[email protected]_dt,[email protected], [email protected], <br /> [email protected], [email protected],[email protected], [email protected], [email protected], [email protected], [email protected], <br /> [email protected],[email protected], [email protected],[email protected], [email protected]_str,[email protected]_dt, [email protected], [email protected], [email protected] where [email protected] <br /> set @result=1 <br /> end <br /> else <br /> begin <br /> set @result=0 <br /> end <br /> end <br /> else <br /> begin <br /> insert into resumes(id,site,link,resume,cname,email,saved_dt,phone1,phone2,jobtitle,education,salary,state,zipcode,relocation,relstates,workaut,emptype,securitycle,notes,saveddt_str,posting_dt,res,updated,type) <br /> values (@id,@site,@link,@resume,@cname,@email,@saved_dt,@phone1,@phone2,@jobtitle,@education,@salary,@state,@zipcode,@relocation,@relstates,@workaut,@emptype,@securitycle,@notes,@saveddt_str,@posting_dt,@res,@updated,@type) <br /> Set @result=2 <br /> end <br /> end <br /> else <br /> begin <br /> if exists(select ‘true’ from resumes where [email protected] and [email protected]) <br /> begin <br /> select @pdate=posting_dt from resumes where [email protected] and [email protected] <br /> if @posting_dt &gt; @pdate <br /> begin <br /> update resumes set [email protected], [email protected], [email protected],[email protected], [email protected],[email protected],[email protected]_dt,[email protected], [email protected], <br /> [email protected], [email protected],[email protected], [email protected], [email protected], [email protected], [email protected], <br /> workau[email protected],[email protected], [email protected],[email protected], [email protected]_str,[email protected]_dt, [email protected], [email protected], [email protected] where [email protected] and [email protected] <br /> set @result=1 <br /> end <br /> else <br /> begin <br /> set @result=0 <br /> end <br /> end <br /> else <br /> begin <br /> insert into resumes(id,site,link,resume,cname,email,saved_dt,phone1,phone2,jobtitle,education,salary,state,zipcode,relocation,relstates,workaut,emptype,securitycle,notes,saveddt_str,posting_dt,res,updated,type) <br /> values (@id,@site,@link,@resume,@cname,@email,@saved_dt,@phone1,@phone2,@jobtitle,@education,@salary,@state,@zipcode,@relocation,@relstates,@workaut,@emptype,@securitycle,@notes,@saveddt_str,@posting_dt,@res,@updated,@type) <br /> set @result=2 <br /> end <br /> end <br />end <br /><br />and here is my vb code:<br /><br />Set objcmd1 = New ADODB.Command<br /> Set rs = New ADODB.Recordset<br /> rs.CursorLocation = adUseClient<br /> Set params1 = objcmd1.Parameters<br /> objcmd1.ActiveConnection = cn<br /> objcmd1.CommandType = adCmdStoredProc<br /> objcmd1.CommandText = "select_saveresumes1"<br /> params1.Append objcmd1.CreateParameter("@rescnt", adBigInt, adParamInput)<br /> With objcmd1<br /> .Parameters("@rescnt") = rescnt<br /> Set rs = .Execute<br /> End With<br /> DoEvents<br /> If rs.EOF = False Then<br /> Dim myarr<br /> myarr = rs.GetRows<br /> rs.Close<br /> If UBound(myarr, 2) &gt;= 0 Then<br /> For i = 0 To UBound(myarr, 2)<br /> DoEvents<br /> str1 = myarr(3, i)<br /> site = myarr(1, i)<br /> cnt = myarr(0, i)<br /> Call getdetails(str1, site, res1)<br /> res1 = Replace(res1, "&lt;b&gt;&lt;span style=""color:black;background:yellow""&gt;", "")<br /> res1 = Replace(res1, "&lt;b&gt;&lt;span style=’color:black;background:yellow’&gt;", "")<br /> res1 = Replace(res1, "&lt;/span&gt;&lt;/b&gt;", "")<br /> res1 = Removespan(res1)<br /> res1 = Removespan1(res1)<br /> res1 = Replace(res1, "&lt;/span&gt;", "")<br /> res1 = Replace(res1, "&lt;/SPAN&gt;", "")<br /> len1 = Len(str1)<br /> len2 = Len(res1)<br /> Set objCmd = New ADODB.Command<br /> objCmd.ActiveConnection = cn1<br /> objCmd.CommandType = adCmdStoredProc<br /> objCmd.CommandText = "insert_247touniversal1"<br /> Set params = objCmd.Parameters<br /> <br /> If len2 &gt; 0 Then<br /> params.Append objCmd.CreateParameter("@id", adBigInt, adParamInput)<br /> params.Append objCmd.CreateParameter("@site", adLongVarChar, adParamInput, 20)<br /> params.Append objCmd.CreateParameter("@link", adLongVarChar, adParamInput, 2000)<br /> params.Append objCmd.CreateParameter("@resume", adLongVarChar, adParamInput, Val(len1))<br /> params.Append objCmd.CreateParameter("@cname", adLongVarChar, adParamInput, 200)<br /> params.Append objCmd.CreateParameter("@email", adLongVarChar, adParamInput, 200)<br /> params.A ppend objCmd.CreateParameter("@saved_dt", adDBTimeStamp, adParamInput)<br /> params.Append objCmd.CreateParameter("@phone1", adLongVarChar, adParamInput, 40)<br /> params.Append objCmd.CreateParameter("@phone2", adLongVarChar, adParamInput, 40)<br /> params.Append objCmd.CreateParameter("@jobtitle", adLongVarChar, adParamInput, 200)<br /> params.Append objCmd.CreateParameter("@education", adLongVarChar, adParamInput, 200)<br /> params.Append objCmd.CreateParameter("@salary", adBigInt, adParamInput)<br /> params.Append objCmd.CreateParameter("@state", adLongVarChar, adParamInput, 5)<br /> params.Append objCmd.CreateParameter("@zipcode", adLongVarChar, adParamInput, 10)<br /> params.Append objCmd.CreateParameter("@relocation", adLongVarChar, adParamInput, 3)<br /> params.Append objCmd.CreateParameter("@relstates", adLongVarChar, adParamInput, 300)<br /> params.Append objCmd.CreateParameter("@workaut", adLongVarChar, adParamInput, 3)<br /> params.Append objCmd.CreateParameter("@emptype", adLongVarChar, adParamInput, 10)<br /> params.Append objCmd.CreateParameter("@securitycle", adLongVarChar, adParamInput, 10)<br /> params.Append objCmd.CreateParameter("@notes", adLongVarChar, adParamInput, 450)<br /> params.Append objCmd.CreateParameter("@saveddt_str", adLongVarChar, adParamInput, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /> params.Append objCmd.CreateParameter("@posting_dt", adDBTimeStamp, adParamInput)<br /> params.Append objCmd.CreateParameter("@res", adLongVarChar, adParamInput, Val(len2))<br /> params.Append objCmd.CreateParameter("@updated", adLongVarChar, adParamInput, 1)<br /> params.Append objCmd.CreateParameter("@type", adLongVarChar, adParamInput, 5)<br /> params.Append objCmd.CreateParameter("@result", adInteger, adParamReturnValue, 1)<br /> With objCmd<br /> .Parameters("@id") = cnt<br /> .Parameters("@site") = Trim(site)<br /> .Parameters("@link") = myarr(2, i)<br /> .Parameters("@resume") = Trim(str1)<br /> .Parameters("@cname") = myarr(4, i)<br /> .Parameters("@email") = myarr(5, i)<br /> .Parameters("@saved_dt") = Trim(Now)<br /> If myarr(7, i) &lt;&gt; "" Then<br /> .Parameters("@phone1") = myarr(7, i)<br /> Else<br /> .Parameters("@phone1") = ""<br /> End If<br /> If myarr(12, i) &lt;&gt; "" Then<br /> .Parameters("@phone2") = myarr(12, i)<br /> Else<br /> .Parameters("@phone2") = ""<br /> End If<br /> .Parameters("@jobtitle") = myarr(8, i)<br /> .Parameters("@education") = myarr(9, i)<br /> If myarr(10, i) &lt;&gt; "" Then<br /> .Parameters("@salary") = myarr(10, i)<br /> Else<br /> .Parameters("@salary") = 0<br /> End If<br /> .Parameters("@state") = myarr(13, i)<br /> .Parameters("@zipcode") = myarr(14, i)<br /> .Parameters("@relocation") = myarr(15, i)<br /> .Parameters("@relstates") = myarr(16, i)<br /> .Parameters("@workaut") = myarr(17, i)<br /> .Parameters("@emptype") = myarr(18, i)<br /> .Parameters("@securitycle") = myarr(19, i)<br /> .Parameters("@notes") = myarr(11, i)<br /> .Parameters("@saveddt_str") = myarr(21, i)<br /> .Parameters("@posting_dt") = myarr(6, i)<br /> .Parameters("@res") = Trim(res1)<br /> .Parameters("@updated") = 1<br /> .Parameters("@type") = "247"<br /> .Execute<br /> result1 = .Parameters("@result")<br /> End With<br /> End If<br /> Set objCmd = Nothing<br /> Command1.Caption = j<br /> j = j + 1<br />s2: Next<br /><br />Regards,<br /><br />Lalitha.c <br /><br />
if the timeout error occures while inserting data it might be due to network cognision or is configuration of SQL … have u tried to set time out seconds ? try it… hsGoswami
[email protected]
Do u have any indexing on resume table in which you are inserting the records.
I don’t have any indexing, but one field is auto generation filed. Lalitha.C
you have a criteria on field id and site …. and if you dont have a index on the same field create index and it will booost your performance.
[email protected]
No need to create, we created index by using third party controls according to our requirement. I think that is not the problem. Plz give me the solution for my problem. Thanking you, Lalitha.C
please read the document and plz follow the guide lines given in the article , sure you will be benefited .
thanx. hsGoswami
[email protected]