Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Urgent help needed! Unable to to paste in query designer

Hi Experts,

I have made some changes to a column in SQL using a different editor, and when trying to paste back it's unavailable!

See attached!
Untitled.png
Avatar of Serena Hsi
Serena Hsi
Flag of United States of America image

Have you tried pasting what you copied into a text file? Maybe the copy/paste action got lost somewhere...
Avatar of bfuchs

ASKER

Hi,

No, just tried pasting it in notepad and works.

This is a long text, perhaps there is a limit how much it can paste back?

Thanks,
Ben
Avatar of bfuchs

ASKER

This is what I've trying to paste.

dbo.EmployeesMissingDocs('', dbo.Employeestbl.ID, dbo.Employeestbl.Title, dbo.Employeestbl.LicenseNumSignedYN, dbo.Employeestbl.LicenseExpires, dbo.Employeestbl.BclsExpires, dbo.Employeestbl.BclsSignedYN, dbo.Employeestbl.BclsLetterDate, dbo.Employeestbl.MalpracticeExpires, dbo.Employeestbl.MalLevelOK, dbo.Employeestbl.AclsExpires, dbo.Employeestbl.AclsSignedYN, dbo.Employeestbl.AclsLetterDate, dbo.Employeestbl.NalsExpires, dbo.Employeestbl.NalsSignedYN, dbo.Employeestbl.NalsLetterDate, dbo.Employeestbl.PalsExpires, dbo.Employeestbl.PalsSignedYN, dbo.Employeestbl.PalsLetterDate, dbo.Employeestbl.Other_Cert_Exp, dbo.Employeestbl.Other_Cert, dbo.Employeestbl.Physical, dbo.Employeestbl.PhisycalPPD_Date, dbo.Employeestbl.PhisycalPPD_Res, dbo.Employeestbl.ResumeYN, dbo.Employeestbl.MaskFitTestDate, dbo.Employeestbl.OrientationDocumentation, dbo.Facilitiestbl.Name, dbo.Employeestbl.PPD2ndStepDate, dbo.Employeestbl.PPD2ndStepRes, dbo.Employeestbl.TBQDate, dbo.Employeestbl.TBQResults, dbo.Employeestbl.ChestXRayDate, dbo.Employeestbl.ChestXRayRes, dbo.Employeestbl.MeaslesRubeolaDate, dbo.Employeestbl.Birthdate, dbo.Employeestbl.RubellaDate, dbo.Employeestbl.VaricellaDate, dbo.Employeestbl.HepBVac, dbo.Employeestbl.HepBWaiver, CoreMandatory_1.CorePartIDate, CoreMandatory_1.CorePartIIDate, CoreMandatory_1.CorePartIIIDate, dbo.Employeestbl.I9Complete, dbo.Employeestbl.W4, dbo.Employeestbl.LSFormYN, dbo.Employeestbl.LSOnApplyingDate, dbo.Employeestbl.Performance_Eval_Comp, dbo.Employeestbl.Reference1, dbo.Employeestbl.Reference2, dbo.Employeestbl.Application, dbo.Employeestbl.OrientationCheckList, dbo.Employeestbl.RecOfEmploy, dbo.Employeestbl.HIPAADate, dbo.Employeestbl.Tetanus, dbo.Employeestbl.WGDrugScreenDate, dbo.Employeestbl.MeaslesRubeolaRes, dbo.Employeestbl.MumpsDate, dbo.Employeestbl.MumpsRes, dbo.Employeestbl.RubellaRes, dbo.Employeestbl.VaricellaRes, dbo.Employeestbl.MeaslesRubeolaLabReports, dbo.Employeestbl.MumpsLabReports, dbo.Employeestbl.RubellaLabReports, dbo.Employeestbl.VaricellaLabReports, dbo.Employeestbl.SkillsChecklistDate, dbo.Employeestbl.DrugScreen, dbo.Employeestbl.OP_Date, dbo.Employeestbl.OP_Results, dbo.Employeestbl.Chauncey_Date, dbo.Employeestbl.Chauncey_Results, dbo.Employeestbl.ID_A, dbo.Employeestbl.ID_A_Expires, dbo.Employeestbl.BackgroundCheckDate, dbo.Employeestbl.AttestationFormDate, dbo.Employeestbl.AttestationFormSigned, dbo.Employeestbl.CPR, dbo.Employeestbl.FluShutDate, dbo.Employeestbl.FluExempt, dbo.CoreMandatory.DementiaExams, dbo.CoreMandatory.CNA_DementiaCare, dbo.Employeestbl.OrientationDocumentationFacility, dbo.Employeestbl.ORTVerificationDate, dbo.FacilitiesDocumentsRequirements.AttestationForm, dbo.FacilitiesDocumentsRequirements.MalpracticeInsurance, dbo.FacilitiesDocumentsRequirements.AhaBLS, dbo.Employeestbl.MaskFitTest, dbo.FacilitiesDocumentsRequirements.TwoStepPPD, dbo.FacilitiesDocumentsRequirements.Tetanus, dbo.FacilitiesDocumentsRequirements.MMRVaricellaTiters, dbo.FacilitiesDocumentsRequirements.FluVaccine, dbo.FacilitiesDocumentsRequirements.OrientationDocuementation, dbo.FacilitiesDocumentsRequirements.SkillsCheckList, dbo.FacilitiesDocumentsRequirements.DimentiaExams, dbo.FacilitiesDocumentsRequirements.MalpracticeReq, dbo.FacilitiesDocumentsRequirements.DrugScreenReq)

Open in new window


As you see its not that simple, and I have a couple of such fields..therefore the best way of doing it was copying field by field and modify in another editor where I can see clear the picture, and then paste back..

If that doesn't work what are my alternatives?

Thanks,
Ben
Avatar of bfuchs

ASKER

OK I see there is indeed a limit (at least in my version 2005/2008).

I did it peace by peace, looks like the max of characters allowed for pasting back is around 8 full lines of a reg notepad doc.

However after finishing all my copy/paste I get the attached error.

While I know this msg cant be true, unless the copy/paste is playing tricks on me..

Any help please!!

Thanks,
Ben
Untitled.png
I have made some changes to a column in SQL using a different editor, and when trying to paste back it's unavailable!

is that your machine out of resources? or is there some other software running at background which affected the clipboard (copy and paste)?

will a reboot help resolved the issue?

Is this a View object you trying to amend? do you have enough permission to amend it?

are you able to amend it via Alter View command in a script file instead?
Hi Ben,
Query designer is basically used to create query using UI. We are here directly copy paste data from outside. Clearly it has limitations when we can copy from outside. Honestly I have never used and in MSDN document I am not getting like how many may characters they allow.!

Hope it helps!
Avatar of bfuchs

ASKER

@Ryan,

Dont think any of those reasons mentioned are the case here, as I'm able to do it from same pc using a later version of SSMS as stated above.

However the alter view you mentioned is something I was thinking of, but not for this case, just for the following
https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009
As for there its enough I get the whole SQL pasted, while here I need to work saparately column by column.

BTW, whats the syntax of alter view?

Thanks,
Ben
Hi Ben,

I could be wrong for my previous comment....

for your ref

SQL Server: ALTER TABLE Statement
https://www.techonthenet.com/sql_server/tables/alter_table.php

check back your initial error of Function Argument Count Error, I think you provided wrong number of arguments into your function?

you can try create a simple query that using function: EmployeesMissingDocs and make sure it's correctly returning the value before paste it into your query that more complex?

Function argument count error
https://technet.microsoft.com/en-us/library/ms163402(v=sql.105).aspx
Avatar of bfuchs

ASKER

Hi,

SQL Server: ALTER TABLE Statement
What I need there is how to modify a view's SQL property, not altering a table.
check back your initial error of Function Argument Count Error, I think you provided wrong number of arguments into your function?
The same mentioned applies to this error as well, since I was able to do it in a later version shows that something is a prob with the copy/paste that's causing this issue, thus resulting in any time of errors including wrong number of args..

Perhaps if you reply to the alter view question, do it in the post mentioned above, so if that works I can accept your comment.

Thanks,
Ben
What I need there is how to modify a view's SQL property, not altering a table.
sorry, try refer to this link instead.

ALTER VIEW (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms173846.aspx

Perhaps if you reply to the alter view question, do it in the post mentioned above, so if that works I can accept your comment
I guess the issue is within the function: EmployeesMissingDocs, but ALTER VIEW is definitely a way to update your View.
Avatar of bfuchs

ASKER

@Rayn,

I guess the issue is within the function: EmployeesMissingDocs,
Still unsure whats the issue with that function as it also ends up working when saved in later version.

but ALTER VIEW is definitely a way to update your View.

Tested and it works, but I cannot accept here as an answer as explained, the question here is how to use the copy/paste feature per column.

However if you post you answer in following link, I will accept it.

https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009

Thanks,
Ben
Still unsure whats the issue with that function as it also ends up working when saved in later version.
can you post the script of function: EmployeesMissingDocs here?

we just need to make sure if that function required 10 parameters, we are passing 10 parameters with valid values to it, etc.
Avatar of bfuchs

ASKER

There you go..(I spent dozens of hours on it and here you're requesting it for free-:)

USE [PlacementNP]
GO
/****** Object:  UserDefinedFunction [dbo].[EmployeesMissingDocs]    Script Date: 12/26/2016 23:11:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER FUNCTION [dbo].[EmployeesMissingDocs] (
@ResultsType varchar(50) = null,
@EmployeeID int,
@Title varchar(50) = null,
@LicenseNumSignedYN bit = null,
@LicenseExpires DATETIME= null,
@BclsExpires DATETIME= null ,
@BclsSignedYN bit = null,
@BclsLetterDate datetime = null,
@MalpracticeExpires DATETIME= null,
@MalLevelOK bit = null,
@AclsExpires datetime = null,
@AclsSignedYN bit = null,
@AclsLetterDate datetime = null,
@NalsExpires datetime = null,
@NalsSignedYN bit = null,
@NalsLetterDate datetime = null,
@PalsExpires datetime = null,
@PalsSignedYN bit = null,
@PalsLetterDate datetime = null,
@Other_Cert_Exp datetime = null,
@Other_Cert varchar(50) = null,
@Physical datetime = null,
@PhisycalPPD_Date datetime = null,
@PhisycalPPD_Res varchar(50) = null,
@ResumeYN bit = null,
@MaskFitTestDate datetime = null,
@OrientationDocumentation bit = null,
@FacName varchar(75) = null,
@PPD2ndStepDate datetime = null,
@PPD2ndStepRes varchar(50) = null,
@TBQDate datetime = null,
@TBQResults varchar(5) = null,
@ChestXRayDate datetime = null,
@ChestXRayRes varchar(50) = null,
@MeaslesRubeolaDate datetime = null,
@Birthdate datetime = null,
@RubellaDate datetime = null,
@VaricellaDate datetime = null,
@HepBVac datetime = null,
@HepBWaiver datetime = null,
@CorePartIDate datetime = null,
@CorePartIIDate datetime = null,
@CorePartIIIDate datetime = null,
@I9Complete bit = null,
@W4 bit = null,
@LSFormYN bit = null,
@LSOnApplyingDate datetime = null,
@Performance_Eval_Comp datetime = null,
@Reference1 bit = null,
@Reference2 bit = null,
@Application bit = null,
@OrientationCheckList bit = null,
@RecOfEmploy bit = null,
@HIPAADate datetime = null,
@Tetanus varchar(250) = null,
@WGDrugScreenDate datetime = null,
@MeaslesRubeolaRes varchar(50) = null,
@MumpsDate datetime = null,
@MumpsRes varchar(50)=null,
@RubellaRes varchar(50)=null,
@VaricellaRes varchar(50) = null,
@MeaslesRubeolaLabReports as bit,
@MumpsLabReports as bit,
@RubellaLabReports as bit,
@VaricellaLabReports as bit,

@SkillsChecklistDate datetime = null,
@DrugScreenDate datetime = null,
@OP_Date as datetime,
@OP_Results varchar(25) = null,
@Chauncey_Date as datetime,
@Chauncey_Results as varchar (25) = null,
@ID_A varchar (50) = null,
@ID_A_Expires dateTime,
@BackgroundCheckDate as datetime = null,
@AttestationFormDate datetime = null,
@AttestationFormSigned as bit = null,
@CPR as bit = null,
@FluShutDate as datetime,
@FluExempt as datetime,
@DementiaExams as datetime,
@CNA_DementiaCare as datetime,
@OrientationDocumentationFacility varchar(150) = null,
@ORTVerificationDate as datetime,


@Fac_AttestationForm as bit = null,
@Fac_MalpracticeInsurance as bit = null,
@Fac_AhaBLS as bit = null,
@Fac_MaskFitTest as bit = null,
@Fac_TwoStepPPD as bit = null,
@Fac_Tetanus as bit = null,
@Fac_MMRVaricellaTiters as bit = null,
--@Fac_WGDrugScreen as bit = null,
--@Fac_DrugScreenAnually as bit = null,
--@Fac_DrugScreenBiAnually as bit = null,
@Fac_FluVaccine as bit = null,
@Fac_OrientationDocuementation as bit = null,
@Fac_SkillsCheckList as bit = null,
@Fac_DimentiaExams as bit = null,
@Fac_MalpracticeReq varchar(50) = null,
@Fac_DrugScreenReq Varchar(50) = null 


) RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @MissingDocs varchar(2000)
declare @ITestCount as int
declare @IsHHCFac as bit 
declare @Count as int
DECLARE @Missing varchar(1000)
DECLARE @Expired varchar(1000)
DECLARE @WillExpire varchar(1000)
DECLARE @NotSigned varchar(1000)
DECLARE @Other varchar(1000)
DECLARE @Due varchar(1000)
Declare @DrugScreenLatest datetime 
Declare @IsBclsDate as bit
Declare @IsAclsDate as bit
Declare @IsNalsDate as bit
Declare @IsPalsDate as bit
Declare @IsAttestDate as bit
Declare @IsMalDate	as bit
declare @IsMeaslesDate as bit
Declare @IsMumpsDate as bit
Declare @IsRubellaDate bit
Declare @IsVaricellaDate as bit
--Declare @LastHireDate as datetime
Declare @Ck_OPDate as bit
Declare @Ck_ChaunceyDate as bit
Declare @Ck_I9 as bit
declare @FluMaxDate	as datetime
declare @Priority1Miss as bit
declare @Priority2Miss as bit
declare @MissingPriority varchar(2000)
declare @DocMissWithStatus varchar(3000)
declare @EmpReqDocs varchar(2000)
declare @CkMalReq as bit


Declare @Mth int


SELECT @Missing =''
SELECT @Expired =''
SELECT @WillExpire =''
SELECT @NotSigned =''
SELECT @Other =''
SELECT @Due =''
select @EmpReqDocs = ''

select @IsBclsDate = 0
select @IsAclsDate = 0
select @IsNalsDate = 0
select @IsPalsDate = 0

Set @Mth = Datepart(Month, getdate())

 
if @LicenseExpires is not null and @Title in('rn','lpn') select @Ck_OPDate = 1
if @LicenseExpires is not null and @Title = 'cna' select @Ck_ChaunceyDate = 1
if @ID_A in ('EA PHOTO','FPP I-551','FPP I-94') select @CK_I9 = 1
if @Fac_MalpracticeReq = 'Not required'
 or (@Fac_MalpracticeReq = 'Only required for RN/LPN/NP' and @Title not in ('RN','LPN','NP'))
	or (@Fac_MalpracticeReq = 'Only required for CNA/RN/NP/LPN' and @Title not in ('RN','LPN','NP','CNA'))
		or (@Fac_MalpracticeReq = 'Not required for PCA/PCT/Sitters' and @Title in ('PCA','PCT','Sitter'))
			or @Title = 'Clerk'
		select @CkMalReq = 0
else
		select @CkMalReq = 1
--if @WGDrugScreenDate is not null select @DrugScreenLatest = @WGDrugScreenDate
--if @DrugScreenDate > @WGDrugScreenDate select @DrugScreenLatest = @DrugScreenDate

--select @DrugScreenLatest = CASE WHEN @WGDrugScreenDate > @DrugScreenDate THEN @WGDrugScreenDate ELSE isnull(@DrugScreenDate,@WGDrugScreenDate) END
select @DrugScreenLatest = case when @Fac_DrugScreenReq = 'WG Drug Screen' then @WGDrugScreenDate else CASE WHEN @WGDrugScreenDate > @DrugScreenDate THEN @WGDrugScreenDate ELSE isnull(@DrugScreenDate,@WGDrugScreenDate) END end
if @FacName in('COLER GOLDWATER SPECIALTY HOSP','DR. SUSAN SMITH MCKINNEY','EAST NY DIAGNOSTIC & TREATMENT','ELMHURST HOSPITAL','GOUVERNEUR NURSING FACILITY','HARLEM HOSPITAL','KINGS COUNTY HOSPITAL CENTER','LINCOLN MEDICAL CENTER','METROPOLITAN HOSPITAL CENTER','QUEENS HOSPITAL CENTER','RENAISSANCE HEALTH CARE NETWOR','SEGUNDO RUIZ BELVIS D&T CENTER','WOODHULL MEDICAL CENTER') select @IsHHCFac = 1

select @FluMaxDate = CASE WHEN @FluShutDate > @FluExempt THEN @FluShutDate ELSE isnull(@FluExempt,@FluShutDate) END

if @LicenseExpires is null and @Title in ('NP', 'RN', 'LPN', 'CNA')	select @Missing = @Missing + ', License'


if @CkMalReq = 1 and @MalpracticeExpires is null
	begin
		select @Missing = @Missing + ', Malpractice'
		select @IsMalDate = 1
	end

if @BclsExpires is null
	begin
		select @Missing = @Missing + ', Bcls'
		select @IsBclsDate = 1
	end
		
	if @Physical is null select @Missing = @Missing + ', Physical'
	
	if @PhisycalPPD_Res is null select @Missing = @Missing + ', PPD_Res'
	
	if @PhisycalPPD_Res = 'N' and @PhisycalPPD_Date is null select @Missing = @Missing + ', PPD Annual'
	
	if @PhisycalPPD_Res = 'P'
		begin 
			if @TBQDate is null select @Missing = @Missing + ', TB Questionnaire'
			if @ChestXRayDate is null select @Missing = @Missing + ', Chest X-Ray'
		end
	
	
	if @MaskFitTestDate is null and @Fac_MaskFitTest = 1 select @Missing = @Missing + ', Mask Fit Test'
	
	if @MeaslesRubeolaDate is null and isnull(@BirthDate,getdate()) > '12/31/56'
	begin
		select @Missing = @Missing + ', Measles' 
		select @IsMeaslesDate = 1
	end
	
	if @MeaslesRubeolaDate is not null and @MeaslesRubeolaRes is null
	begin
		select @Missing = @Missing + ', Measles Immune result' 
		select @IsMeaslesDate = 1
	end
	
	if @RubellaDate is null
		begin
			select @Missing = @Missing + ', Rubella' 
			select @IsRubellaDate =	1
		end
	if @RubellaDate is not null  and @RubellaRes is null
		begin
			select @Missing = @Missing + ', Rubella immune result' 
			select @IsRubellaDate =	1
		end
	if @VaricellaDate is null
		begin
			select @Missing = @Missing + ', Varicella' 
			select @IsVaricellaDate =	1
		end
	if @VaricellaDate is not null and @VaricellaRes is null 
		begin
			select @Missing = @Missing + ', Varicella Immune result' 
			select @IsVaricellaDate =	1
		end
		
	if @MeaslesRubeolaRes = 'N' or @MumpsRes = 'N' or @RubellaRes = 'N' or @VaricellaRes = 'N' select @Missing = @Missing + ', Titers not immune' 
	
	
	if @Fac_MMRVaricellaTiters=1 
		begin 
			if isnull(@MeaslesRubeolaLabReports,0) = 0 and isnull(@IsMeaslesDate,0)=0 select @missing = @missing + ', Measles_lab'
			if isnull(@MumpsLabReports,0) = 0 and isnull(@IsMumpsDate,0)=0 select @missing = @missing + ', Mumps_lab'
			if isnull(@RubellaLabReports,0) = 0 and isnull(@IsRubellaDate,0)=0 select @missing = @missing + ', Rubella_lab'
			if isnull(@VaricellaLabReports,0) = 0 and isnull(@IsVaricellaDate,0)=0 select @missing = @missing + ', Varicella_lab'
		end
		
		
		
	if @HepBVac is null  and @HepBWaiver is null select @Missing = @Missing + ', Hepatitis B' 
	if @CorePartIDate is null  select @Missing = @Missing + ', Annual Mandatory Part 1'
	--if isnull(@CorePartIIDate,getdate()-1) <getdate() and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Missing = @Missing + ', Annual Mandatory Part 2'
	--if isnull(@CorePartIIIDate,getdate()-1) <getdate() and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Missing = @Missing + ', Annual Mandatory Part 3'
	if @CorePartIIDate is null and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Missing = @Missing + ', Annual Mandatory Part 2'
	if @CorePartIIIDate is null and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Missing = @Missing + ', Annual Mandatory Part 3'
	if isnull(@I9Complete,0)=0 select @Missing = @Missing + ', I-9 Form ' 
	if isnull(@W4,0)=0 select @Missing = @Missing + ', Form W4' 
	if isnull(@Reference1,0)=0 or isnull(@Reference2,0)=0 select @Missing = @Missing + ', Reference' 
	if isnull(@Application,0)=0 select @Missing = @Missing + ', Application form' 
	if isnull(@OrientationCheckList,0)=0 select @Missing = @Missing + ', Orientation Checklist' 
	if isnull(@ResumeYN,0)=0  and isnull(@RecOfEmploy,0)=0  select @Missing = @Missing + ', Resume / Record of Employment form' 
	if @Fac_OrientationDocuementation = 1 
		begin
			if isnull(@OrientationDocumentation,0)=0  or @FacName <> isnull(@OrientationDocumentationFacility,'') select @Missing = @Missing + ', Orientation Documentation' 
		end
	if @HIPAADate is null select @Missing = @Missing + ', HIPAA Form' 
	--if @IsHHCFac = 1 and @Tetanus is null select @Missing = @Missing + ', Tetanus immune'
	
	
	if isnull(@Fac_DrugScreenReq,'')<>'Not Required'
	begin
		if @Fac_DrugScreenReq = 'WG Drug Screen'
			begin
			if @WGDrugScreenDate is null  select @Missing = @Missing + ', Drug Screen WG'
			end
		else
			begin
			if @DrugScreenLatest is null  select @Missing = @Missing + ', Drug Screen'
			end
	End		
	
	if  @MumpsDate is not null and @MumpsRes is null
		begin
			select @Missing = @Missing + ', Mumps immune result'
			select @IsMumpsDate = 1
		end
	If isnull(@SkillsChecklistDate,0) = 0  SELECT @Missing = @Missing + ', AnnualSkillsChecklist ' 
	
	if @Ck_OPDate = 1 
		begin
			if @OP_Date is null 
				select @Missing = @Missing + ', OP'
			else
				if @OP_Results is null select @missing = @missing + ', OP Results'
		end
	if @Ck_ChaunceyDate = 1 
		begin
			if @Chauncey_Date is null 
				select @Missing = @Missing + ', Chauncey'
			else
				if @Chauncey_Results is null select @missing = @missing + ', Chauncey Results'
			if @Chauncey_Results <> 'Registered' select @missing = @missing + ', License'
		end
	if @BackgroundCheckDate is null select @Missing = @Missing + ', crim chk'
	if @OP_Results <> 'Registered' select @missing = @missing + ', License'

	if  @Fac_AttestationForm = 1 and @AttestationFormDate is null
		begin
			select @missing = @missing + ', Attestation Form'
			select @IsAttestDate = 1
		end
	--if  @Fac_MaskFitTest = 1 and @MaskFitTestDate is null select @missing = @missing + ', Mask Fit test'
	if  @Fac_TwoStepPPD = 1 and @PhisycalPPD_Res = 'N' and @PPD2ndStepDate is null select @missing = @missing + ', PPD2'
	if  @Fac_Tetanus = 1 and @Tetanus is null select @missing = @missing + ', Tetanus'
	
	if @Fac_FluVaccine = 1 and @FluMaxDate is null and @Mth in (10, 11, 12, 1, 2, 3) select @missing = @missing + ', Flu Shot'
	
	if @Fac_DimentiaExams = 1
		begin
			if @Title in('rn','lpn','cna') and @DementiaExams is null select @missing = @missing + ', Dementia'
			--if @Title = 'cna' and @CNA_DementiaCare is null select @missing = @missing + ', Dementia'
		end
	if @Title = 'ORT' and @ORTVerificationDate is null select @missing = @missing + ', ORT Ver'
--select @other = 'test' --+ cast (@Fac_AttestationForm as varchar)
----------------------------------------------Expired


if @LicenseExpires < getdate() and @Title <> 'ORT'
	begin
		if @LicenseExpires < getdate() select @Expired = @Expired + ', License'
		select @EmpReqDocs = @EmpReqDocs + ' License'
	end
if @CkMalReq = 1  
	begin
		if @MalpracticeExpires < getdate() 
			begin
				select @Expired = @Expired + ', Malpractice'
				select @IsMalDate = 1
			end
		select @EmpReqDocs = @EmpReqDocs + ' Malpractice'
	end
	
if @Physical < getdate()-365 select @Expired = @Expired + ', Physical'
select @EmpReqDocs = @EmpReqDocs + ' Physical'

if @PhisycalPPD_Res = 'N' 
	begin
		if @PhisycalPPD_Date < getdate()-365 select @Expired = @Expired + ', PPD Annual'
		select @EmpReqDocs = @EmpReqDocs + ' PPD Annual'
	end
if @PhisycalPPD_Res = 'P'
	begin
		if @TBQDate < getdate()-365 select @Expired = @Expired + ', TB Questionnaire'
		select @EmpReqDocs = @EmpReqDocs + ' TB Questionnaire'
	end

if  @Fac_MaskFitTest = 1
	begin
		if @MaskFitTestDate < getdate()-365 select @Expired = @Expired + ', Mask Fit Test'
		select @EmpReqDocs = @EmpReqDocs + ' Mask Fit Test'
	end
if @Fac_SkillsCheckList = 1 
	if @SkillsChecklistDate < getdate()-365 select @Expired = @Expired + ', AnnualSkillsChecklist'
else
	if @SkillsChecklistDate < getdate()-730 select @Expired = @Expired + ', AnnualSkillsChecklist'

select @EmpReqDocs = @EmpReqDocs + ' AnnualSkillsChecklist'

if isnull(@Fac_DrugScreenReq,'')<>'Not Required'
begin

	if @Fac_DrugScreenReq = 'WG Drug Screen'
		begin
			if @WGDrugScreenDate < getdate()-730 select @Expired = @Expired + ', Drug Screen WG'
			select @EmpReqDocs = @EmpReqDocs + ' Drug Screen WG'
		end
	else
		if @Fac_DrugScreenReq = 'Anually' 
			begin
				if @DrugScreenLatest < getdate()-365 select @Expired = @Expired + ', Drug Screen'
				select @EmpReqDocs = @EmpReqDocs + ' Drug Screen'
			end 
		else
			if @Fac_DrugScreenReq = 'Bi-Anually' 
				begin
					if @DrugScreenLatest < getdate()-730 select @Expired = @Expired + ', Drug Screen'
					select @EmpReqDocs = @EmpReqDocs + ' Drug Screen'
				end 
end
			
if @Ck_OPDate = 1
	begin
		if @OP_Date < getdate()-365  select @Expired = @Expired + ', OP'
		select @EmpReqDocs = @EmpReqDocs + ' OP'
	end
	
if @Ck_ChaunceyDate = 1
	begin
		if @Chauncey_Date < getdate()-365  select @Expired = @Expired + ', Chauncey'
		select @EmpReqDocs = @EmpReqDocs + ' Chauncey'
	end
	
if @CK_I9 = 1 
	begin
		if @ID_A_Expires < getdate() select @Expired = @Expired + ', I-9/ID_A'
		select @EmpReqDocs = @EmpReqDocs + ' I-9/ID_A'
	end
	



if @BclsExpires < getdate()
	begin
		select @Expired = @Expired + ', Bcls'
		select @IsBclsDate = 1
	end
if @AclsExpires < getdate()
	begin
		select @Expired = @Expired + ', Acls'
		select @IsAclsDate = 1
	end
if @NalsExpires < getdate()
	begin
		select @Expired = @Expired + ', Nals'
		select @IsNalsDate = 1
	end
if @PalsExpires < getdate()
	begin
		select @Expired = @Expired + ', Pals'
		select @IsPalsDate = 1
	end	
select @EmpReqDocs = @EmpReqDocs + ' Bcls Acls Nals Pals'
			
if  @Fac_AttestationForm = 1  
	begin 
		if @AttestationFormDate < getdate()-365
			begin
				select @Expired = @Expired + ', Attestation Form'
				select @IsAttestDate = 1
			end
		select @EmpReqDocs = @EmpReqDocs + ' Attestation Form'
	end 
	if @Fac_FluVaccine = 1 
		begin
			if @FluMaxDate < getdate()-365 select @expired = @expired + ', Flu Shot'
			select @EmpReqDocs = @EmpReqDocs + ' Flu Shot'
		end
	if @Fac_DimentiaExams = 1 and @Title in('rn','lpn','cna')
		begin
			if @DementiaExams < getdate() select @expired = @expired + ', Dementia'
			select @EmpReqDocs = @EmpReqDocs + ' Dementia'
		end
		
if  @CorePartIDate < getdate() select @Expired = @Expired + ', Annual Mandatory'	
	
if @ORTVerificationDate < getdate()-365 select @Expired = @Expired + ', ORT Ver'

select @EmpReqDocs = @EmpReqDocs + ' Annual Mandatory ORT Ver'
	
----------------------------------------Will Expire


if @LicenseExpires between getdate() and getdate()+ 60 and @Title <> 'ORT' select @WillExpire = @WillExpire + ', License'
if @CkMalReq = 1 and @MalpracticeExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Malpractice'
		select @IsMalDate = 1
	end
--if @AclsExpires between getdate() and getdate()+ 60 select @WillExpire = @WillExpire + ', Acls'
--if @NalsExpires between getdate() and getdate()+ 60 select @WillExpire = @WillExpire + ', NRP'
--if @PalsExpires between getdate() and getdate()+ 60 select @WillExpire = @WillExpire + ', Pals'
if @Physical between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Physical'
if @PhisycalPPD_Res = 'N' and @PhisycalPPD_Date between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', PPD Annual'
if @PhisycalPPD_Res = 'P' and @TBQDate between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', TB Questionnaire'
if @Fac_MaskFitTest = 1 and @MaskFitTestDate between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Mask Fit Test'

if @Fac_SkillsCheckList = 1
	if @SkillsChecklistDate between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', AnnualSkillsChecklist'
else
	if @SkillsChecklistDate between getdate()-730 and getdate()- 670 select @WillExpire = @WillExpire + ', AnnualSkillsChecklist'

if isnull(@Fac_DrugScreenReq,'')<>'Not Required'
begin
	if @Fac_DrugScreenReq = 'WG Drug Screen'
		begin
			if @WGDrugScreenDate between getdate()-730 and getdate()- 670 select @WillExpire = @WillExpire + ', Drug Screen WG'
		end
	else
		if @Fac_DrugScreenReq = 'Anually' 
			begin
				if @DrugScreenLatest between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Drug Screen'
			end 
		else
			if @Fac_DrugScreenReq = 'Bi-Anually' 
				begin
					if @DrugScreenLatest between getdate()-730 and getdate()- 670 select @WillExpire = @WillExpire + ', Drug Screen'
				end
end
 
if @Ck_OPDate = 1 and @OP_Date between getdate()-365 and getdate()- 305  select @WillExpire = @WillExpire + ', OP'
if @Ck_ChaunceyDate = 1 and @Chauncey_Date between getdate()-365 and getdate()- 305  select @WillExpire = @WillExpire + ', Chauncey'
if @CK_I9 = 1 and @ID_A_Expires between getdate() and  getdate()+90 select @WillExpire = @WillExpire + ', I-9/ID_A'

if @BclsExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Bcls'
		select @IsBclsDate = 1
	end
if @AclsExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Acls'
		select @IsAclsDate = 1
	end
if @NalsExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Nals'
		select @IsNalsDate = 1
	end
if @PalsExpires between getdate() and getdate()+ 60
	begin
		select @WillExpire = @WillExpire + ', Pals'
		select @IsPalsDate = 1
	end			

if @Fac_FluVaccine = 1 and @FluMaxDate between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Flu Shot'
if @Fac_DimentiaExams = 1
	begin
		if @Title in('rn','lpn','cna') and @DementiaExams between getdate() and getdate()+30 select @WillExpire = @WillExpire + ', Dementia'
		--if @Title = 'cna' and @CNA_DementiaCare between getdate() and getdate()+30 select @WillExpire = @WillExpire + ', Dementia'
	end
	
if @CorePartIDate between getdate() and getdate()+ 60 select @WillExpire = @WillExpire + ', Annual Mandatory Part 1'
--if @CorePartIIDate between getdate() and getdate()+ 60 and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Due = @Due + ', Annual Mandatory Part 2'
--if @CorePartIIIDate between getdate() and getdate()+ 60 and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Due = @Due  + ', Annual Mandatory Part 3'
if @Performance_Eval_Comp < getdate()-670 select @WillExpire = @WillExpire + ', Performance Evaluation' 

-------------------------------------Not Signed


if @LicenseExpires >= getdate() +60 and isnull(@LicenseNumSignedYN,0) = 0 and @Title <> 'ORT' select @NotSigned = @NotSigned + ', License'

if @IsBclsDate <> 1
	Begin
		if @BclsLetterDate is not null
			select @Missing = @Missing + ', Bcls Card'
		else
			if isnull(@BclsSignedYN,0) = 0 select @NotSigned = @NotSigned + ', Bcls'
	End
if @IsAclsDate <> 1
	Begin
		if @AclsLetterDate is not null
			select @Missing = @Missing + ', Acls Card'
		else
			if isnull(@AclsSignedYN,0) = 0  and @AclsExpires is not null select @NotSigned = @NotSigned + ', Acls'
	End
if @IsNalsDate <> 1
	Begin
		if @NalsLetterDate is not null
			select @Missing = @Missing + ', Nals Card'
		--else
			--if isnull(@NalsSignedYN,0) = 0  and @NalsExpires is not null select @NotSigned = @NotSigned + ', Nals'
	End
if @IsPalsDate <> 1
	Begin
		if @PalsLetterDate is not null
			select @Missing = @Missing + ', Pals Card'
		else
			if isnull(@PalsSignedYN,0) = 0 and @PalsExpires is not null select @NotSigned = @NotSigned + ', Pals'
	End
	
if  @Fac_AttestationForm = 1 and isnull(@AttestationFormSigned,0) = 0 and isnull(@IsAttestDate,0)= 0 select @NotSigned = @NotSigned + ', Attestation Form'


-------------------------------------Due


--select @LastHireDate=(select Max(HireDate) from dbo.EmployeesFacilities where Employeeid = @EmployeeID)
--if @LastHireDate is null or @LastHireDate < getdate()-180 select @Due = @Due + ', Performance Evaluation' 


SELECT @MissingDocs =''



	if @CkMalReq = 1 and @Fac_MalpracticeReq = 'Required 3M-6M' and @MalpracticeExpires >= getdate() +60 and isnull(@MalLevelOK,0) = 0 and isnull(@IsMalDate,0) = 0
		begin
			select @Other = @Other + ', Malpractice level of insurance insufficient'
			select @IsMalDate = 1
		end
	

	select @count=(select count(*) from dbo.EmployeesOtherCertificates where Employeeid = @EmployeeID and CertificateExpires < getdate()+60)
	if @Count >0
	begin
		declare @CertificateName varchar(50)
		declare @CertificateExpires as datetime
		declare FirstCursor cursor for select CertificateName, CertificateExpires from dbo.employeesOtherCertificates where Employeeid = @EmployeeID and CertificateExpires < getdate()+60
		open FirstCursor 
		while @count>0
		begin
		fetch FirstCursor into @CertificateName,@CertificateExpires
		if @CertificateExpires < getdate() select @Expired = @Expired + ', ' + @CertificateName 
		if @CertificateExpires >= getdate() select @WillExpire = @WillExpire + ', ' + @CertificateName 
		set @count=@count-1
		end
		close FirstCursor 
		deallocate FirstCursor 
	end







	if isnull(@LSFormYN,0) = 0 
	begin
		select @count=(select count(*) from dbo.EmployeesLSForms where Employeeid = @EmployeeID)
		if @Count = 0 select @Missing = @Missing + ', LS Form'
	end
	
--'Other	
if @PhisycalPPD_Res = 'P' and @ChestXRayRes = 'P' select @Other = @Other + ', Chest X-Ray positive'
if @CkMalReq = 1 and @Fac_MalpracticeReq = 'Required 3M-6M' and @MalpracticeExpires is not null and isnull(@MalLevelOK,0) = 0 and isnull(@IsMalDate,0) = 0 select @Other = @Other + ', Malpractice insufficient funds'
if @Fac_AhaBLS = 1 and @CPR = 1 and isnull(@IsBclsDate,0) = 0 select @other = @other + ', BLS not AHA cert'


	
	if isnull(@Missing,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Missing: ' + SUBSTRING(@Missing,3, LEN(@Missing))
	if isnull(@Expired,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Expired: ' + SUBSTRING(@Expired,3, LEN(@Expired))
	if isnull(@WillExpire,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Will Expire: ' + SUBSTRING(@WillExpire,3, LEN(@WillExpire))
	if isnull(@NotSigned,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Not Signed: ' + SUBSTRING(@NotSigned,3, LEN(@NotSigned))
	if isnull(@Due,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Due: ' + SUBSTRING(@Due,3, LEN(@Due))
	if isnull(@Other,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Other: ' + SUBSTRING(@Other,3, LEN(@Other))



	IF LEN(@MissingDocs) > 0
	
		begin
		 
			if  isnull(@ResultsType,'')='' 				
				select  @MissingDocs= SUBSTRING(@MissingDocs,5, LEN(@MissingDocs))
				
			else if @ResultsType = 'EmpReqDocs' 
				select @MissingDocs = @EmpReqDocs
				
			else if @resultsType = 'MissingDocsWithStatus' 
			begin
				select @DocMissWithStatus=''
				if isnull(@Missing,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@Missing,',',' ;Missing')
				if isnull(@Expired,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@Expired,',',' ;Expired')
				if isnull(@WillExpire,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@WillExpire,',',' ;Will Expire')
				if isnull(@NotSigned,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@NotSigned,',',' ;Not Signed')
				if isnull(@Due,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@Due,',',' ;Due')
				select @DocMissWithStatus = @DocMissWithStatus + ' ;'
				select  @MissingDocs=  'abc'
				select @MissingDocs = @DocMissWithStatus
			end
			
			if @ResultsType = 'PriorityType' 
				begin
				select @MissingPriority=@Missing + ' ' + @Expired 
				
				if @Title in ('NP', 'RN', 'LPN', 'CNA')
				begin
					
					if charindex('license',@MissingPriority)> 0 or charindex('I-9',@MissingPriority)> 0
					 or charindex('BCLS',@MissingPriority)> 0 or  charindex('Physical',@MissingPriority)> 0
					 or charindex('PPD',@MissingPriority)> 0 or charindex('Drug',@MissingPriority)> 0
					 or charindex('BackgroundCheck',@MissingPriority)> 0 or charindex('Titers',@MissingPriority)> 0 
					 or (charindex('Acls',@MissingPriority)> 0 and @AclsExpires is not null)
					 or (charindex('Nals',@MissingPriority)> 0 and @NalsExpires is not null)
					 or (charindex('Pals',@MissingPriority)> 0 and @PalsExpires is not null)
					 or charindex('Malpractice',@MissingPriority)> 0  
					  Select @Priority1Miss = 1
					 
					 if charindex('Application',@MissingPriority)> 0 or charindex('Resume',@MissingPriority)> 0
					 or charindex('Reference',@MissingPriority)> 0 or  charindex('W4',@MissingPriority)> 0
					 or charindex('HIPAA',@MissingPriority)> 0 or charindex('SkillsChecklist',@MissingPriority)> 0
					 or charindex('Annual Mandatory',@MissingPriority)> 0 or charindex('Hepatitis B',@MissingPriority)> 0 
					 or (charindex('Mask Fit Test',@MissingPriority)> 0 and @MaskFitTestDate is not null)
					 or charindex('TB Questionnaire',@MissingPriority)> 0
					   Select @Priority2Miss = 1
				end 
				if @Title in ('PCA', 'PCT', 'ORT') or @Title like '%tech%'
				begin
					if  charindex('I-9',@MissingPriority)> 0
					 or charindex('BCLS',@MissingPriority)> 0 or  charindex('Physical',@MissingPriority)> 0
					 or charindex('PPD',@MissingPriority)> 0 --or charindex('Drug',@MissingPriority)> 0
					 or @MissingPriority like '%Drug%'
					 or charindex('BackgroundCheck',@MissingPriority)> 0 or charindex('Titers',@MissingPriority)> 0 
					 or (charindex('Malpractice',@MissingPriority)> 0 and @MalpracticeExpires is not null)
					  Select @Priority1Miss = 1
					 
					 if charindex('Application',@MissingPriority)> 0 or charindex('Resume',@MissingPriority)> 0
					 or charindex('Reference',@MissingPriority)> 0 or  charindex('W4',@MissingPriority)> 0
					 or charindex('HIPAA',@MissingPriority)> 0 or charindex('SkillsChecklist',@MissingPriority)> 0
					 or charindex('Annual Mandatory',@MissingPriority)> 0 or charindex('Hepatitis B',@MissingPriority)> 0 
					 or (charindex('Mask Fit Test',@MissingPriority)> 0 and @MaskFitTestDate is not null)
					 or charindex('TB Questionnaire',@MissingPriority)> 0
					  Select @Priority2Miss = 1
				end 
				if @Title in ('Sitter')
				begin
					if  charindex('I-9',@MissingPriority)> 0 or  charindex('Physical',@MissingPriority)> 0
					 or charindex('PPD',@MissingPriority)> 0 or charindex('Drug',@MissingPriority)> 0
					 or charindex('BackgroundCheck',@MissingPriority)> 0 or charindex('Titers',@MissingPriority)> 0 
					  Select @Priority1Miss = 1
					 
					 if charindex('Application',@MissingPriority)> 0 or charindex('Resume',@MissingPriority)> 0
					 or charindex('Reference',@MissingPriority)> 0 or  charindex('W4',@MissingPriority)> 0
					 or charindex('HIPAA',@MissingPriority)> 0 or charindex('SkillsChecklist',@MissingPriority)> 0
					 or charindex('Annual Mandatory',@MissingPriority)> 0 or charindex('Hepatitis B',@MissingPriority)> 0 
					 or (charindex('Mask Fit Test',@MissingPriority)> 0 and @MaskFitTestDate is not null)
					 or charindex('TB Questionnaire',@MissingPriority)> 0
					  Select @Priority2Miss = 1
				end 
				if @Title in ('Clerk')
				begin
					if  charindex('I-9',@MissingPriority)> 0 or  charindex('Physical',@MissingPriority)> 0
					 or charindex('PPD',@MissingPriority)> 0 or charindex('Drug',@MissingPriority)> 0
					 or charindex('BackgroundCheck',@MissingPriority)> 0 or charindex('Titers',@MissingPriority)> 0 
					  Select @Priority1Miss = 1
					 
					 if charindex('Application',@MissingPriority)> 0 or charindex('Resume',@MissingPriority)> 0
					 or charindex('Reference',@MissingPriority)> 0 or  charindex('W4',@MissingPriority)> 0
					 or charindex('HIPAA',@MissingPriority)> 0 or charindex('SkillsChecklist',@MissingDocs)> 0
					 or charindex('Annual Mandatory',@MissingPriority)> 0 or charindex('Hepatitis B',@MissingPriority)> 0 
					 or (charindex('Mask Fit Test',@MissingPriority)> 0 and @MaskFitTestDate is not null)
					 or charindex('TB Questionnaire',@MissingPriority)> 0
					  Select @Priority2Miss = 1
				end 
					
				 if @Priority1Miss = 1 and @Priority2Miss = 1
					set  @MissingDocs= 'Both'
				 else if @Priority1Miss = 1
					set  @MissingDocs= 'One'
				 else if @Priority2Miss = 1
					set  @MissingDocs= 'Two'
				 else
					set  @MissingDocs= 'None'
				 end
		End

RETURN @MissingDocs
END

Open in new window


Thanks,
Ben
Ok, seems that you got a long list for the parameters!

if not wrong total parameters for the function: 96 ?

few comments from quick observations:

in your case:

see the parameters below as they do not have a default value.

@EmployeeID int,
@MeaslesRubeolaLabReports as bit,
@MumpsLabReports as bit,
@RubellaLabReports as bit,
@VaricellaLabReports as bit,
@OP_Date as datetime,
@Chauncey_Date as datetime,
@ID_A_Expires dateTime,
@FluShutDate as datetime,
@FluExempt as datetime,
@DementiaExams as datetime,
@CNA_DementiaCare as datetime,
@ORTVerificationDate as datetime,

it's advised either to set all parameters with a default value,

OR put those parameters without a default values before those parameters with a default value, like:

ALTER FUNCTION [dbo].[yourFunction] (
@Para1 int,
@Para2 int,
@Para3 varchar(50) = null,
@Para4  varchar(50) = null
) RETURNS VARCHAR(2000)
AS
...

Open in new window

so that when you calling the function, you only need to call the function by providing 2 parameters (Para1 and Para2)

if you got something like this:

ALTER FUNCTION [dbo].[yourFunction] (
@Para1 int,
@Para3 varchar(50) = null,
@Para4  varchar(50) = null,
@Para2 int
) RETURNS VARCHAR(2000)
AS
...

Open in new window

I believe you need to pass 4 parameters into the function.

And there could be the reason why you getting the Function Argument Count error, because the function's parameters are not arranged correctly.
Avatar of bfuchs

ASKER

Good point Ryan, and I may try to apply default to all params.
And there could be the reason why you getting the Function Argument Count error, because the function's parameters are not arranged correctly
Well that should only be the case if I'm omitting some param values, while here believe I have them all (see below), and again this ended up working so there's no reason to think something was wrong with the list of params.

FYI- the following is what I was trying to paste in OP.

dbo.EmployeesMissingDocs('', dbo.Employeestbl.ID,
dbo.Employeestbl.Title,
dbo.Employeestbl.LicenseNumSignedYN, 
dbo.Employeestbl.LicenseExpires, dbo.Employeestbl.BclsExpires, dbo.Employeestbl.BclsSignedYN, 
dbo.Employeestbl.BclsLetterDate, dbo.Employeestbl.MalpracticeExpires, dbo.Employeestbl.MalLevelOK, 
dbo.Employeestbl.AclsExpires, dbo.Employeestbl.AclsSignedYN, dbo.Employeestbl.AclsLetterDate, dbo.Employeestbl.NalsExpires, 
dbo.Employeestbl.NalsSignedYN, dbo.Employeestbl.NalsLetterDate, dbo.Employeestbl.PalsExpires, dbo.Employeestbl.PalsSignedYN, 

dbo.Employeestbl.PalsLetterDate, dbo.Employeestbl.Other_Cert_Exp, dbo.Employeestbl.Other_Cert, dbo.Employeestbl.Physical, 

dbo.Employeestbl.PhisycalPPD_Date,dbo.Employeestbl.PhisycalPPD_Res, dbo.Employeestbl.ResumeYN, 

dbo.Employeestbl.MaskFitTestDate, dbo.Employeestbl.OrientationDocumentation, dbo.Facilitiestbl.Name, 

dbo.Employeestbl.PPD2ndStepDate, dbo.Employeestbl.PPD2ndStepRes, dbo.Employeestbl.TBQDate, dbo.Employeestbl.TBQResults, 

dbo.Employeestbl.ChestXRayDate, dbo.Employeestbl.ChestXRayRes, dbo.Employeestbl.MeaslesRubeolaDate, 

dbo.Employeestbl.Birthdate, dbo.Employeestbl.RubellaDate, dbo.Employeestbl.VaricellaDate, dbo.Employeestbl.HepBVac, 

dbo.Employeestbl.HepBWaiver, CoreMandatory_1.CorePartIDate, CoreMandatory_1.CorePartIIDate, CoreMandatory_1.CorePartIIIDate, 

dbo.Employeestbl.I9Complete, dbo.Employeestbl.W4, dbo.Employeestbl.LSFormYN, dbo.Employeestbl.LSOnApplyingDate, 

dbo.Employeestbl.Performance_Eval_Comp, dbo.Employeestbl.Reference1, dbo.Employeestbl.Reference2, 
dbo.Employeestbl.Application, dbo.Employeestbl.OrientationCheckList, dbo.Employeestbl.RecOfEmploy, 
dbo.Employeestbl.HIPAADate, dbo.Employeestbl.Tetanus, dbo.Employeestbl.WGDrugScreenDate, dbo.Employeestbl.MeaslesRubeolaRes, 
dbo.Employeestbl.MumpsDate, dbo.Employeestbl.MumpsRes, dbo.Employeestbl.RubellaRes, dbo.Employeestbl.VaricellaRes, 
dbo.Employeestbl.MeaslesRubeolaLabReports, dbo.Employeestbl.MumpsLabReports, dbo.Employeestbl.RubellaLabReports, 
dbo.Employeestbl.VaricellaLabReports, dbo.Employeestbl.SkillsChecklistDate, dbo.Employeestbl.DrugScreen, 
dbo.Employeestbl.OP_Date, dbo.Employeestbl.OP_Results, dbo.Employeestbl.Chauncey_Date, dbo.Employeestbl.Chauncey_Results,
dbo.Employeestbl.ID_A, dbo.Employeestbl.ID_A_Expires, dbo.Employeestbl.BackgroundCheckDate, 
dbo.Employeestbl.AttestationFormDate, dbo.Employeestbl.AttestationFormSigned, dbo.Employeestbl.CPR, 
dbo.Employeestbl.FluShutDate, dbo.Employeestbl.FluExempt, dbo.CoreMandatory.DementiaExams, 
dbo.CoreMandatory.CNA_DementiaCare, dbo.Employeestbl.OrientationDocumentationFacility, dbo.Employeestbl.ORTVerificationDate, 
dbo.FacilitiesDocumentsRequirements.AttestationForm, dbo.FacilitiesDocumentsRequirements.MalpracticeInsurance, 
dbo.FacilitiesDocumentsRequirements.AhaBLS, dbo.Employeestbl.MaskFitTest, dbo.FacilitiesDocumentsRequirements.TwoStepPPD, 
dbo.FacilitiesDocumentsRequirements.Tetanus, dbo.FacilitiesDocumentsRequirements.MMRVaricellaTiters, 
dbo.FacilitiesDocumentsRequirements.FluVaccine, dbo.FacilitiesDocumentsRequirements.OrientationDocuementation, 
dbo.FacilitiesDocumentsRequirements.SkillsCheckList, dbo.FacilitiesDocumentsRequirements.DimentiaExams, 
dbo.FacilitiesDocumentsRequirements.MalpracticeReq, dbo.FacilitiesDocumentsRequirements.DrugScreenReq)

Open in new window


Thanks,
Ben
Avatar of bfuchs

ASKER

Good night ryan,

guess will have to finalize that tom as becoming extremely late for me..

Thanks,
Ben
Well that should only be the case if I'm omitting some param values, while here believe I have them all (see below)
Yup, hence I believe the original error of Function Argument Count has been successfully resolved?

no worries let's continue tomorrow and just let us know if you need further assistance here cheers
Avatar of bfuchs

ASKER

Hi Ryan,
Yup, hence I believe the original error of Function Argument Count has been successfully resolved?
Actually since there are two questions posted looking similar (and both referencing the same query), lets do a recap of what was posted and whats being already answered.
1- In this post here, I was trying to copy the contents of a particular column and paste it in another editor, then do changes there and paste back in query designer, while the first part went fine, the final process got stuck (see pic OP), and as stated above I was trying to overcome this prob by doing it step by step and everything went fine until..I was trying to leave the column, then is when I got the Function Argument Count error, and as you see (ID: 41938475) there is no wrong # of columns, just an error with copy/paste feature.
Now for this issue I'm still wondering what is the solution as alter view will not substitute this functionality.

2-  After being forced to do my modification in the query designer's SQL pane, when was about to save I was facing a diff issue, that with the syntax error https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009
and there is where your suggestion to use alter view comes in handy..

Let me know if its clear.

Thanks,
Ben
hi Ben,

Well, there should be a reason why the method(s) we are using doesn't work. Probably due to the way we conduct it or it's the limitation of the tools itself that we are using.

But it's hard for me to imagine your issues and how you conduct it.

if this is really urgent, you may ask experts for help in Live or Gigs.
Avatar of bfuchs

ASKER

Hi,
But it's hard for me to imagine your issues and how you conduct it.
Well if you have my version of SSMS its very easy to reproduce it, just copy the function EmployeesMissingDocs and then try to work with the designer modifying the SQL I posted.

if this is really urgent,
At the time I posted this was really urgent, however as I found a work around (using 2014) this became less important, now if you think there is really no other solution to this guess its time to close this question?

Thanks,
Ben
Made some modification and no error for me.
User generated image
Avatar of bfuchs

ASKER

You have the same version, great!

Actually It would not be enough just the function as you would need all the table structures in order to work with my SQL.

However you can test it with any query having a column being that long as this

dbo.EmployeesMissingDocs('', dbo.Employeestbl.ID,
dbo.Employeestbl.Title,
dbo.Employeestbl.LicenseNumSignedYN,
dbo.Employeestbl.LicenseExpires, dbo.Employeestbl.BclsExpires, dbo.Employeestbl.BclsSignedYN, 
dbo.Employeestbl.BclsLetterDate, dbo.Employeestbl.MalpracticeExpires, dbo.Employeestbl.MalLevelOK, 
dbo.Employeestbl.AclsExpires, dbo.Employeestbl.AclsSignedYN, dbo.Employeestbl.AclsLetterDate, dbo.Employeestbl.NalsExpires, 
dbo.Employeestbl.NalsSignedYN, dbo.Employeestbl.NalsLetterDate, dbo.Employeestbl.PalsExpires, dbo.Employeestbl.PalsSignedYN, 

dbo.Employeestbl.PalsLetterDate, dbo.Employeestbl.Other_Cert_Exp, dbo.Employeestbl.Other_Cert, dbo.Employeestbl.Physical, 

dbo.Employeestbl.PhisycalPPD_Date, dbo.Employeestbl.PhisycalPPD_Res, dbo.Employeestbl.ResumeYN, 

dbo.Employeestbl.MaskFitTestDate, dbo.Employeestbl.OrientationDocumentation, dbo.Facilitiestbl.Name, 

dbo.Employeestbl.PPD2ndStepDate, dbo.Employeestbl.PPD2ndStepRes, dbo.Employeestbl.TBQDate, dbo.Employeestbl.TBQResults, 

dbo.Employeestbl.ChestXRayDate, dbo.Employeestbl.ChestXRayRes, dbo.Employeestbl.MeaslesRubeolaDate, 

dbo.Employeestbl.Birthdate, dbo.Employeestbl.RubellaDate, dbo.Employeestbl.VaricellaDate, dbo.Employeestbl.HepBVac, 

dbo.Employeestbl.HepBWaiver, CoreMandatory_1.CorePartIDate, CoreMandatory_1.CorePartIIDate, CoreMandatory_1.CorePartIIIDate, 

dbo.Employeestbl.I9Complete, dbo.Employeestbl.W4, dbo.Employeestbl.LSFormYN, dbo.Employeestbl.LSOnApplyingDate, 

dbo.Employeestbl.Performance_Eval_Comp, dbo.Employeestbl.Reference1, dbo.Employeestbl.Reference2,
dbo.Employeestbl.Application, dbo.Employeestbl.OrientationCheckList, dbo.Employeestbl.RecOfEmploy, 

dbo.Employeestbl.HIPAADate, dbo.Employeestbl.Tetanus, dbo.Employeestbl.WGDrugScreenDate, dbo.Employeestbl.MeaslesRubeolaRes, 

dbo.Employeestbl.MumpsDate, dbo.Employeestbl.MumpsRes, dbo.Employeestbl.RubellaRes, dbo.Employeestbl.VaricellaRes, 

dbo.Employeestbl.MeaslesRubeolaLabReports, dbo.Employeestbl.MumpsLabReports, dbo.Employeestbl.RubellaLabReports, 

dbo.Employeestbl.VaricellaLabReports, dbo.Employeestbl.SkillsChecklistDate, dbo.Employeestbl.DrugScreen, 

dbo.Employeestbl.OP_Date, dbo.Employeestbl.OP_Results, dbo.Employeestbl.Chauncey_Date, dbo.Employeestbl.Chauncey_Results, 

dbo.Employeestbl.ID_A, dbo.Employeestbl.ID_A_Expires, dbo.Employeestbl.BackgroundCheckDate, 

dbo.Employeestbl.AttestationFormDate, dbo.Employeestbl.AttestationFormSigned, dbo.Employeestbl.CPR, 

dbo.Employeestbl.FluShutDate, dbo.Employeestbl.FluExempt, dbo.CoreMandatory.DementiaExams, 

dbo.CoreMandatory.CNA_DementiaCare, dbo.Employeestbl.OrientationDocumentationFacility, dbo.Employeestbl.ORTVerificationDate, 

dbo.FacilitiesDocumentsRequirements.AttestationForm, dbo.FacilitiesDocumentsRequirements.MalpracticeInsurance, 

dbo.FacilitiesDocumentsRequirements.AhaBLS, dbo.Employeestbl.MaskFitTest, dbo.FacilitiesDocumentsRequirements.TwoStepPPD, 

dbo.FacilitiesDocumentsRequirements.Tetanus, dbo.FacilitiesDocumentsRequirements.MMRVaricellaTiters, 

dbo.FacilitiesDocumentsRequirements.FluVaccine, dbo.FacilitiesDocumentsRequirements.OrientationDocuementation, 

dbo.FacilitiesDocumentsRequirements.SkillsCheckList, dbo.FacilitiesDocumentsRequirements.DimentiaExams, 

dbo.FacilitiesDocumentsRequirements.MalpracticeReq, dbo.FacilitiesDocumentsRequirements.DrugScreenReq)

Open in new window

then in designer when you select that column, copy & paste somewhere else, modify something and try to paste back, you will see the paste feature disabled (like shown in picture OP).

It probably doesn't need to be that big, as this is about 3000 characters while in my experience after 1000 characters it couldn't handle anymore.

Thanks,
Ben
Do you mean I have to create a new View via the designer? like below?
User generated imageI can't execute it successfully with your scripts provided as I don't have the objects you defined in your database....
Another way is to.... reduce the no of parameters to be passed into your Function, IF this can be simplified by pulling the relevant data from other table(s) instead.
Avatar of bfuchs

ASKER

I can't execute it successfully with your scripts..
You dont need to, just try to copy the content of the column from the grid, paste it in notepad and modify something, then try to paste back, you will get stuck right there..

Thanks,
Ben
hi Ben,
I did as what you had mentioned, but No error from my end...
Avatar of bfuchs

ASKER

I see yours is 2008 R2 while mine is not, perhaps this is making the diff..

I guess best would be to upgrade mine, and maybe get latest service packs as well?

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Thank you Rayn!