LonnieJohnson
Programmer
I have established a linked server and want to an update query in a stored procedure. When I do I get this following error message.
[tt][highlight]
TSD4001: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
[/highlight][/tt]
I put the SET ANSI_NULLS and SET ANSI_WARNINGS at the top of my stored procedure but it still gives me the error. I went to the properties of my LinkedServer and did not see where I could do this for "the connection".
Here is my SQL if it matters...
ProDev, Builders of Affordable Software Applications
Visit me at ==>
May God bless you beyond your imagination!!!
[tt][highlight]
TSD4001: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
[/highlight][/tt]
I put the SET ANSI_NULLS and SET ANSI_WARNINGS at the top of my stored procedure but it still gives me the error. I went to the properties of my LinkedServer and did not see where I could do this for "the connection".
Here is my SQL if it matters...
Code:
INSERT INTO [Mas90\Lytec_SQL].[Lytec Tutorial].dbo.Patient
([Chart Number]
,[Last Name]
,[First Name]
,[Middle Initial]
,[Address 1]
,[Address 2]
,[City]
,[State]
,[Zip Code]
,[Zip Plus 4]
,[Home Phone]
,[Work Phone]
,[Social Security]
,[Birth Date]
,[Marital Status]
,[Sex]
,[Responsible Is]
,[Responsible Party - Patient]
,[Responsible Party - Guarantor]
,[Employer Code]
,[Employer Name]
,[Employer Address 1]
,[Employer Address 2]
,[Employer City]
,[Employer State]
,[Employer Zip]
,[Employer Zip Plus 4]
,[Provider Code]
,[Balance]
,[Charges]
,[Payments]
,[Primary Code]
,[Primary Type]
,[Primary Policy]
,[Primary ID]
,[Primary Auto Bill]
,[Primary Accept Assig]
,[Primary Auth]
,[Primary Insured Is]
,[Primary Insured - Patient]
,[Primary Insured - Guarantor]
,[Primary Ins Relation]
,[Prim PCA Claim Num]
,[Primary Insurance - Effective Date From]
,[Primary Insurance - Effective Date To]
,[Secondary Code]
,[Secondary Type]
,[Secondary Policy]
,[Secondary ID]
,[Secondary Auto Bill]
,[Second Accept Assign]
,[Secondary Auth]
,[Secondary Insred Is]
,[Secondary Insured - Patient]
,[Secondary Insured - Guarantor]
,[Second Ins Relation]
,[Sec PCA Claim Num]
,[Secondary Insurance - Effective Date From]
,[Secondary Insurance - Effective Date To]
,[Tertiary Code]
,[Tertiary Type]
,[Tertiary Policy]
,[Tertiary ID]
,[Tertiary Auto Bill]
,[Tert Accept Assig]
,[Tertiary Auth]
,[Tertiary Insred Is]
,[Tertiary Insured - Patient]
,[Tertiary Insured - Guarantor]
,[Tert Ins Relation]
,[Ter PCA Claim Num]
,[Tertiary Insurance - Effective Date From]
,[Tertiary Insurance - Effective Date To]
,[Last Payment Date]
,[Last Xray]
,[Symptom Type]
,[Symptom Date]
,[Consultation 1]
,[Consultation 2]
,[Return Work Type]
,[Return Work Date]
,[Accident Type]
,[Accident State]
,[Accident Date]
,[Total Disabilty 1]
,[Total Disabilty 2]
,[Part Disability 1]
,[Part Disability 2]
,[Hospitalization 1]
,[Hospitalization 2]
,[Status]
,[Death Date]
,[Facility Code]
,[Lab Charge]
,[Lab Charge Amount]
,[Ref Physician Code]
,[Similar Symptom]
,[Employment Related]
,[Emergency]
,[Percent Disability]
,[Employment Status]
,[Student Status]
,[Signature On File]
,[Release Info]
,[EPSDT]
,[Family Planning]
,[Third Party Liable]
,[Service Branch]
,[Service Grade]
,[Service Crd Eff 1]
,[Service Crd Eff 2]
,[Service Status]
,[Service Handicap]
,[Ambulatory Surgery]
,[Attorney Code]
,[Months Treated]
,[Non Available]
,[Last Payment]
,[Subluxation 1]
,[Subluxation 2]
,[Similar Date]
,[Perm Diagnosis 1]
,[Perm Diagnosis 2]
,[Perm Diagnosis 3]
,[Perm Diagnosis 4]
,[Perm Diagnosis 5]
,[Hold Code 1]
,[Hold Code 2]
,[Hold Code 3]
,[Hold Code 4]
,[Hold Code 5]
,[Patient Code]
,[Patient Type]
,[Work Extension]
,[Last Visit]
,[Ref Patient Code]
,[Managed Care]
,[Managed Care Code]
,[Managed Care Amount]
,[Inactive]
,[Auto Bill Patient]
,[Copay]
,[Release Info Date]
,[Resubmission No]
,[Original Ref No]
,[Last Seen PCP]
,[Primary Care Provide]
,[Condition Nature]
,[Complication Ind]
,[EPSDT Findings]
,[EPSDT Referral Ind]
,[External Acc Cause]
,[Podiatry Type]
,[Systemic Condition]
,[Class Findings]
,[New Patient Date]
,[Note Reminder]
,[Created Date]
,[Modified Date]
,[Main Phone]
,[Fax Phone]
,[Mobile Phone]
,[Pager Phone]
,[Other Phone]
,[Home Email]
,[Work Email]
,[Contact Name]
,[Contact Phone]
,[Contact Note]
,[Patient Weight]
,[Weight Units]
,[Referral Date]
,[Pregnancy Indicator]
,[Estimated Date Birth]
,[Prescription Date]
,[Last Worked Date]
,[Date Assumed Care]
,[Date Released Care]
,[Ref ID Qualifier]
,[Srvc Auth Excpt Code]
,[Homebound Indicator]
,[Supervising Phys]
,[IDE Number]
,[Fee Schedule Type]
,[Collection Status]
,[Entity Type]
,[Images Reminder]
,[Patient Nick Name]
,[KVC_CSNBR])
SELECT CSNBR --[Chart Number]
,LNAME --[Last Name]
,FNAME --[First Name]
,ISNULL(MI,'') --[Middle Initial]
,ISNULL(PLCADDRESS,'') --[Address 1]
,'' --[Address 2]
,ISNULL(PLCCITY,'') --[City]
,ISNULL(PLCSTATE,'') --[State]
,ISNULL(LEFT(PLCZIP,5),'') --[Zip Code]
,'' --[Zip Plus 4]
,ISNULL(PLCPHONE,'') --[Home Phone]
,'' --[Work Phone]
,SSN --[Social Security]
,DOB --[Birth Date]
,'' --[Marital Status]
,
CASE --[Sex]
WHEN MASTERFILE.SEX = 'F' THEN 1
WHEN MASTERFILE.SEX = 'M' THEN 0
WHEN MASTERFILE.SEX = '' THEN ''
END
,'' --[Responsible Is]
,'' --[Responsible Party - Patient]
,'' --[Responsible Party - Guarantor]
,'' --[Employer Code]
,'' --[Employer Name]
,'' --[Employer Address 1]
,'' --[Employer Address 2]
,'' --[Employer City]
,'' --[Employer State]
,'' --[Employer Zip]
,'' --[Employer Zip Plus 4]
,'' --[Provider Code]
,0 --[Balance]
,0 --[Charges]
,0 --[Payments]
,'' --[Primary Code]
,'' --[Primary Type]
,'' --[Primary Policy]
,'' --[Primary ID]
,'' --[Primary Auto Bill]
,'' --[Primary Accept Assig]
,'' --[Primary Auth]
,'' --[Primary Insured Is]
,'' --[Primary Insured - Patient]
,'' --[Primary Insured - Guarantor]
,'' --[Primary Ins Relation]
,'' --[Prim PCA Claim Num]
,'' --[Primary Insurance - Effective Date From]
,'' --[Primary Insurance - Effective Date To]
,'' --[Secondary Code]
,'' --[Secondary Type]
,'' --[Secondary Policy]
,'' --[Secondary ID]
,'' --[Secondary Auto Bill]
,'' --[Second Accept Assign]
,'' --[Secondary Auth]
,'' --[Secondary Insred Is]
,'' --[Secondary Insured - Patient]
,'' --[Secondary Insured - Guarantor]
,'' --[Second Ins Relation]
,'' --[Sec PCA Claim Num]
,'' --[Secondary Insurance - Effective Date From]
,'' --[Secondary Insurance - Effective Date To]
,'' --[Tertiary Code]
,'' --[Tertiary Type]
,'' --[Tertiary Policy]
,'' --[Tertiary ID]
,'' --[Tertiary Auto Bill]
,'' --[Tert Accept Assig]
,'' --[Tertiary Auth]
,'' --[Tertiary Insred Is]
,'' --[Tertiary Insured - Patient]
,'' --[Tertiary Insured - Guarantor]
,'' --[Tert Ins Relation]
,'' --[Ter PCA Claim Num]
,'' --[Tertiary Insurance - Effective Date From]
,'' --[Tertiary Insurance - Effective Date To]
,'' --[Last Payment Date]
,'' --[Last Xray]
,'' --[Symptom Type]
,'' --[Symptom Date]
,'' --[Consultation 1]
,'' --[Consultation 2]
,'' --[Return Work Type]
,'' --[Return Work Date]
,'' --[Accident Type]
,'' --[Accident State]
,'' --[Accident Date]
,'' --[Total Disabilty 1]
,'' --[Total Disabilty 2]
,'' --[Part Disability 1]
,'' --[Part Disability 2]
,'' --[Hospitalization 1]
,'' --[Hospitalization 2]
,'' --[Status]
,'' --[Death Date]
,'' --[Facility Code]
,'' --[Lab Charge]
,0 --[Lab Charge Amount]
,'' --[Ref Physician Code]
,'' --[Similar Symptom]
,'' --[Employment Related]
,'' --[Emergency]
,'' --[Percent Disability]
,'' --[Employment Status]
,'' --[Student Status]
,'' --[Signature On File]
,'' --[Release Info]
,'' --[EPSDT]
,'' --[Family Planning]
,'' --[Third Party Liable]
,'' --[Service Branch]
,'' --[Service Grade]
,'' --[Service Crd Eff 1]
,'' --[Service Crd Eff 2]
,'' --[Service Status]
,'' --[Service Handicap]
,'' --[Ambulatory Surgery]
,'' --[Attorney Code]
,'' --[Months Treated]
,'' --[Non Available]
,0 --[Last Payment]
,'' --[Subluxation 1]
,'' --[Subluxation 2]
,'' --[Similar Date]
,'' --[Perm Diagnosis 1]
,'' --[Perm Diagnosis 2]
,'' --[Perm Diagnosis 3]
,'' --[Perm Diagnosis 4]
,'' --[Perm Diagnosis 5]
,'' --[Hold Code 1]
,'' --[Hold Code 2]
,'' --[Hold Code 3]
,'' --[Hold Code 4]
,'' --[Hold Code 5]
,'' --[Patient Code]
,'' --[Patient Type]
,'' --[Work Extension]
,'' --[Last Visit]
,'' --[Ref Patient Code]
,'' --[Managed Care]
,'' --[Managed Care Code]
,0 --[Managed Care Amount]
,'' --[Inactive]
,'' --[Auto Bill Patient]
,0 --[Copay]
,'' --[Release Info Date]
,'' --[Resubmission No]
,'' --[Original Ref No]
,'' --[Last Seen PCP]
,'' --[Primary Care Provide]
,'' --[Condition Nature]
,'' --[Complication Ind]
,'' --[EPSDT Findings]
,'' --[EPSDT Referral Ind]
,'' --[External Acc Cause]
,'' --[Podiatry Type]
,'' --[Systemic Condition]
,'' --[Class Findings]
,'' --[New Patient Date]
,'' --[Note Reminder]
,'' --[Created Date]
,'' --[Modified Date]
,'' --[Main Phone]
,'' --[Fax Phone]
,'' --[Mobile Phone]
,'' --[Pager Phone]
,'' --[Other Phone]
,'' --[Home Email]
,'' --[Work Email]
,'' --[Contact Name]
,'' --[Contact Phone]
,'' --[Contact Note]
,'' --[Patient Weight]
,'' --[Weight Units]
,'' --[Referral Date]
,'' --[Pregnancy Indicator]
,'' --[Estimated Date Birth]
,'' --[Prescription Date]
,'' --[Last Worked Date]
,'' --[Date Assumed Care]
,'' --[Date Released Care]
,'' --[Ref ID Qualifier]
,'' --[Srvc Auth Excpt Code]
,'' --[Homebound Indicator]
,'' --[Supervising Phys]
,'' --[IDE Number]
,'' --[Fee Schedule Type]
,'' --[Collection Status]
,'' --[Entity Type]
,'' --[Images Reminder]
,'' --[Patient Nick Name]
,CSNBR --[KVC_CSNBR]
FROM MASTERFILE
LEFT JOIN [Mas90\Lytec_SQL].[Lytec Tutorial].dbo.Patient AS LYTEC
ON MASTERFILE.SSN = LYTEC.[SOCIAL SECURITY]
WHERE DISCHDATE IS NULL
AND SSN IS NOT NULL AND SSN <> '' AND SSN > 0 AND SSN <> '999999999'
AND LYTEC.[SOCIAL SECURITY] IS NULLProDev, Builders of Affordable Software Applications
Visit me at ==>
May God bless you beyond your imagination!!!
