Click here to Skip to main content
12,402,666 members (72,298 online)
   

Database

 
AnswerRe: oracle error with winserver2008 Pin
Bernhard Hiller5-Feb-13 4:57
memberBernhard Hiller5-Feb-13 4:57 
Questionchar(255) vs varchar(255) vs varchar(MAX) Pin
devvvy1-Feb-13 18:22
memberdevvvy1-Feb-13 18:22 
AnswerRe: char(255) vs varchar(255) vs varchar(MAX) Pin
Richard MacCutchan1-Feb-13 23:07
mvpRichard MacCutchan1-Feb-13 23:07 
GeneralRe: char(255) vs varchar(255) vs varchar(MAX) Pin
devvvy2-Feb-13 15:39
memberdevvvy2-Feb-13 15:39 
AnswerRe: char(255) vs varchar(255) vs varchar(MAX) Pin
PIEBALDconsult2-Feb-13 5:22
memberPIEBALDconsult2-Feb-13 5:22 
GeneralRe: char(255) vs varchar(255) vs varchar(MAX) Pin
devvvy2-Feb-13 23:30
memberdevvvy2-Feb-13 23:30 
GeneralRe: char(255) vs varchar(255) vs varchar(MAX) Pin
jschell3-Feb-13 6:36
memberjschell3-Feb-13 6:36 
QuestionSP Unexpected Behavior Pin
eddieangel1-Feb-13 7:19
membereddieangel1-Feb-13 7:19 
I have a procedure that checks to see if a user added address exists in a remote database. If the address exists, the procedure retrieves the id and updates the local DB with that id. If it does not exist, the procedure adds the address to the remote database, retrieves the new id, and updates the local database with the new id. Unfortunately, without any apparent pattern some times several addresses are lumped together into one single new address id. Can someone give me a second pair of eyes on this procedure to see where it might be doing this? I am also open to improvements.

USE [RemoteCapture]
GO
/****** Object:  StoredProcedure [dbo].[usp_resolveAddressProblems]    Script Date: 02/01/2013 09:51:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
/****** Object:  StoredProcedure [dbo].[usp_resolveAddressProblems]    Script Date: 10/06/2009 12:41:21 ******/
 
ALTER proc [dbo].[usp_resolveAddressProblems] (@servername varchar(100))
 
as
 
declare @query nvarchar(650)
declare @myAddId int
declare @myAddress varchar(100)
declare @newId int
declare @cursorString varchar(2000)
declare @intCaseId int
declare @remoteId int = 0
 
set @cursorString = 'declare myCursor cursor for select intAddressId, txtAddress, intCaseId from tbl_cdf_address where bitUserAdded = 1'
exec(@cursorString)
open myCursor
fetch next from myCursor into @myAddId, @myAddress, @intCaseId
while @@FETCH_STATUS = 0
begin
	get_address:
	set @remoteId = 0
	set @query = N'select @remoteId = intAddressId from "' + @servername + '".depo.dbo.tbl_cdf_address where txtAddress = ''' + @myAddress + ''' and intCaseId = ' + CAST(@intCaseId as varchar(10))
	
	exec sp_executesql @query, N'@remoteId int output', @remoteId output
	if @remoteId != 0 
	begin
		set @query = N'update tbl_cdf_observation set intAddressId = ' + cast(@remoteId as varchar(10)) + ' where intAddressId = ' + CAST(@myAddId as varchar(10)) + ' and intCaseId = ' + CAST(@intCaseId as varchar(10))
		exec(@query)
		set @query = N'update tbl_cdf_inspectionNotes set intAddressId = ' + cast(@remoteId as varchar(10)) + ' where intAddressId = ' + CAST(@myAddId as varchar(10)) + ' and intCaseId = ' + CAST(@intCaseId as varchar(10))
		exec(@query)
		print 'Updated ' + @myAddress
	end
	else
	begin
		set @query = N'insert "' + @servername + '".depo.dbo.tbl_cdf_address (intCaseId, txtAddress, bitActive) values (' + CAST(@intCaseId as varchar(50)) + ',''' + @myAddress + ''',1)' 
		exec(@query)
		print 'Added ' + @myAddress + ' to database.'
		goto get_address
	end
fetch next from myCursor into @myAddId, @myAddress, @intCaseId
end
close myCursor
deallocate myCursor
 
set @query = N'delete tbl_cdf_address'
exec(@query)
return

Cheers, --EA
QuestionEncrypt a field in the database. Pin
vanikanc1-Feb-13 5:31
membervanikanc1-Feb-13 5:31 
AnswerRe: Encrypt a field in the database. Pin
Eddy Vluggen1-Feb-13 7:02
mvpEddy Vluggen1-Feb-13 7:02 
Questioncheck before insert Pin
vanikanc31-Jan-13 4:55
membervanikanc31-Jan-13 4:55 
AnswerRe: check before insert Pin
Eddy Vluggen31-Jan-13 5:00
mvpEddy Vluggen31-Jan-13 5:00 
GeneralRe: check before insert Pin
Eddy Vluggen31-Jan-13 10:35
mvpEddy Vluggen31-Jan-13 10:35 
AnswerRe: check before insert Pin
mark merrens31-Jan-13 8:10
membermark merrens31-Jan-13 8:10 
GeneralRe: check before insert Pin
vanikanc31-Jan-13 8:21
membervanikanc31-Jan-13 8:21 
GeneralRe: check before insert Pin
mark merrens31-Jan-13 10:09
membermark merrens31-Jan-13 10:09 
GeneralRe: check before insert Pin
Chris Meech31-Jan-13 10:24
memberChris Meech31-Jan-13 10:24 
GeneralRe: check before insert Pin
mark merrens31-Jan-13 10:25
membermark merrens31-Jan-13 10:25 
AnswerRe: check before insert Pin
djj551-Feb-13 7:50
memberdjj551-Feb-13 7:50 
GeneralRe: check before insert Pin
vanikanc1-Feb-13 7:55
membervanikanc1-Feb-13 7:55 
GeneralRe: check before insert Pin
djj551-Feb-13 8:25
memberdjj551-Feb-13 8:25 
QuestionIdentify DML changes between two databases Pin
Member 313707830-Jan-13 20:56
memberMember 313707830-Jan-13 20:56 
AnswerRe: Identify DML changes between two databases Pin
Mycroft Holmes30-Jan-13 21:11
memberMycroft Holmes30-Jan-13 21:11 
GeneralRe: Identify DML changes between two databases Pin
Member 313707830-Jan-13 21:26
memberMember 313707830-Jan-13 21:26 
QuestionUpdate a flag based on portion of data avialble in other column Pin
yuvachandra30-Jan-13 10:20
memberyuvachandra30-Jan-13 10:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 28 Jul 2016
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid