SQL Server Performance Forum – Threads Archive

Problem with Stored Procedures

Hi,
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 a.*
FROM OPENROWSET(‘SQLOLEDB’,’Server1′;’sa’;”,
‘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, 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. here is my stored procedures: 1. select_savesumes1: ——————— CREATE procedure select_saveresumes1 ( @rescnt bigint ) as begin set nocount on 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 > @rescnt and site<>’computerwork’ end 2. insert_247touniversal1: ————————– CREATE procedure insert_247touniversal1 ( @id bigint, @site varchar(20), @link varchar(2000), @resume text, @cname varchar(200), @email varchar(200), @saved_dt datetime, @phone1 varchar(40), @phone2 varchar(40), @jobtitle varchar(200), @education varchar(200), @salary bigint, @state varchar(5), @zipcode varchar(10), @relocation varchar(3), @relstates varchar(300), @workaut varchar(3), @emptype varchar(10), @securitycle varchar(10), @notes varchar(450), @saveddt_str varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @posting_dt datetime, @res text, @updated varchar(1), @type varchar(5), @result int output ) as begin set nocount on declare @pdate datetime SET ANSI_WARNINGS OFF set @result=0 if @email<>” begin if exists(select ‘true’ from resumes where [email protected]) begin select @pdate=posting_dt from resumes where [email protected] if @posting_dt > @pdate begin update resumes set [email protected], [email protected], [email protected],[email protected], [email protected],[email protected]_dt,[email protected], [email protected], [email protected], [email protected],[email protected], [email protected], [email protected], [email protected], [email protected], [email protected],[email protected], [email protected],[email protected], [email protected]_str,[email protected]_dt, [email protected], [email protected], [email protected] where [email protected] set @result=1 end else begin set @result=0 end end else begin 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) 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) Set @result=2 end end else begin if exists(select ‘true’ from resumes where [email protected] and [email protected]) begin select @pdate=posting_dt from resumes where [email protected] and [email protected] if @posting_dt > @pdate begin update resumes set [email protected], [email protected], [email protected],[email protected], [email protected],[email protected],[email protected]_dt,[email protected], [email protected], [email protected], [email protected],[email protected], [email protected], [email protected], [email protected], [email protected], 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] set @result=1 end else begin set @result=0 end end else begin 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) 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) set @result=2 end end end and here is my vb code: Set objcmd1 = New ADODB.Command Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient Set params1 = objcmd1.Parameters objcmd1.ActiveConnection = cn objcmd1.CommandType = adCmdStoredProc objcmd1.CommandText = "select_saveresumes1" params1.Append objcmd1.CreateParameter("@rescnt", adBigInt, adParamInput) With objcmd1 .Parameters("@rescnt") = rescnt Set rs = .Execute End With DoEvents If rs.EOF = False Then Dim myarr myarr = rs.GetRows rs.Close If UBound(myarr, 2) >= 0 Then For i = 0 To UBound(myarr, 2) DoEvents str1 = myarr(3, i) site = myarr(1, i) cnt = myarr(0, i) Call getdetails(str1, site, res1) res1 = Replace(res1, "", "") res1 = Replace(res1, "", "") res1 = Replace(res1, "", "") res1 = Removespan(res1) res1 = Removespan1(res1) res1 = Replace(res1, "", "") res1 = Replace(res1, "", "") len1 = Len(str1) len2 = Len(res1) Set objCmd = New ADODB.Command objCmd.ActiveConnection = cn1 objCmd.CommandType = adCmdStoredProc objCmd.CommandText = "insert_247touniversal1" Set params = objCmd.Parameters If len2 > 0 Then params.Append objCmd.CreateParameter("@id", adBigInt, adParamInput) params.Append objCmd.CreateParameter("@site", adLongVarChar, adParamInput, 20) params.Append objCmd.CreateParameter("@link", adLongVarChar, adParamInput, 2000) params.Append objCmd.CreateParameter("@resume", adLongVarChar, adParamInput, Val(len1)) params.Append objCmd.CreateParameter("@cname", adLongVarChar, adParamInput, 200) params.Append objCmd.CreateParameter("@email", adLongVarChar, adParamInput, 200) params.A ppend objCmd.CreateParameter("@saved_dt", adDBTimeStamp, adParamInput) params.Append objCmd.CreateParameter("@phone1", adLongVarChar, adParamInput, 40) params.Append objCmd.CreateParameter("@phone2", adLongVarChar, adParamInput, 40) params.Append objCmd.CreateParameter("@jobtitle", adLongVarChar, adParamInput, 200) params.Append objCmd.CreateParameter("@education", adLongVarChar, adParamInput, 200) params.Append objCmd.CreateParameter("@salary", adBigInt, adParamInput) params.Append objCmd.CreateParameter("@state", adLongVarChar, adParamInput, 5) params.Append objCmd.CreateParameter("@zipcode", adLongVarChar, adParamInput, 10) params.Append objCmd.CreateParameter("@relocation", adLongVarChar, adParamInput, 3) params.Append objCmd.CreateParameter("@relstates", adLongVarChar, adParamInput, 300) params.Append objCmd.CreateParameter("@workaut", adLongVarChar, adParamInput, 3) params.Append objCmd.CreateParameter("@emptype", adLongVarChar, adParamInput, 10) params.Append objCmd.CreateParameter("@securitycle", adLongVarChar, adParamInput, 10) params.Append objCmd.CreateParameter("@notes", adLongVarChar, adParamInput, 450) params.Append objCmd.CreateParameter("@saveddt_str", adLongVarChar, adParamInput, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> params.Append objCmd.CreateParameter("@posting_dt", adDBTimeStamp, adParamInput) params.Append objCmd.CreateParameter("@res", adLongVarChar, adParamInput, Val(len2)) params.Append objCmd.CreateParameter("@updated", adLongVarChar, adParamInput, 1) params.Append objCmd.CreateParameter("@type", adLongVarChar, adParamInput, 5) params.Append objCmd.CreateParameter("@result", adInteger, adParamReturnValue, 1) With objCmd .Parameters("@id") = cnt .Parameters("@site") = Trim(site) .Parameters("@link") = myarr(2, i) .Parameters("@resume") = Trim(str1) .Parameters("@cname") = myarr(4, i) .Parameters("@email") = myarr(5, i) .Parameters("@saved_dt") = Trim(Now) If myarr(7, i) <> "" Then .Parameters("@phone1") = myarr(7, i) Else .Parameters("@phone1") = "" End If If myarr(12, i) <> "" Then .Parameters("@phone2") = myarr(12, i) Else .Parameters("@phone2") = "" End If .Parameters("@jobtitle") = myarr(8, i) .Parameters("@education") = myarr(9, i) If myarr(10, i) <> "" Then .Parameters("@salary") = myarr(10, i) Else .Parameters("@salary") = 0 End If .Parameters("@state") = myarr(13, i) .Parameters("@zipcode") = myarr(14, i) .Parameters("@relocation") = myarr(15, i) .Parameters("@relstates") = myarr(16, i) .Parameters("@workaut") = myarr(17, i) .Parameters("@emptype") = myarr(18, i) .Parameters("@securitycle") = myarr(19, i) .Parameters("@notes") = myarr(11, i) .Parameters("@saveddt_str") = myarr(21, i) .Parameters("@posting_dt") = myarr(6, i) .Parameters("@res") = Trim(res1) .Parameters("@updated") = 1 .Parameters("@type") = "247" .Execute result1 = .Parameters("@result") End With End If Set objCmd = Nothing Command1.Caption = j j = j + 1 s2: Next Regards, Lalitha.c

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.

Hi,
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.
hsGoswami
[email protected]

Hi,
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

Madam,
please read the document and plz follow the guide lines given in the article , sure you will be benefited . http://www.sql-server-performance.com/visual_basic_performance.asp
thanx. hsGoswami
[email protected]

]]>