CREATE PROCEDURE [dbo].[spM2MUpdateJodrtgDate] 
    @Jobno char(10), @M2MfLastLab varchar(25)
     
 AS

Declare @SQLString varchar(8000) 
Declare @SQLString2 varchar(8000)
Declare @DQ varchar (1) 
Declare @SQ varchar(1) 

SET @DQ = CHAR(34) 
-- Double Quotes 

SET @SQ = CHAR(39) 
-- Single Quotes 

-- always use select * - do not use field names
--this will be called PRD_DATA instead of TST_M2M when this sp is put into production. (name of dsn on server)

Set @SQLString = 'Update openquery([TST_M2M],'
 +@SQ + 'Select * from jodrtg WHERE fjobno = ' +@DQ+rtrim(@Jobno) +@DQ + @SQ +')'


set @SQLString2 = ' set flastlab = ' 
+@SQ + @m2mflastlab + @SQ

--+@DQ+ rtrim(@M2MFlastLab) +@DQ 
--above line is previous code

set @SQLString = @SQLString + @SQLString2

exec (@SQLString )

Declare @Error as int
Declare @Rowcount as int

Select @Error = @@Error,  @Rowcount = @@Rowcount

IF @Error = 0 
    Begin
	IF @Rowcount = 0 
	   Begin
		Set @Error = 99 --No records updated/inserted
                 End
    End 
Return(@Error)
GO"

quote:

---

Gretchen writes "I am trying to pass a date from a table in SQL server to update a table in VFP6. I have tried to use a datetime data type, varchar, etc., and when I run the stored procedure in the query analyzer all I get is the message (0 row(s) affected). The field in foxpro is a datetime field, and sometimes I get the message "cannot convert varchar to datetime."

When I print my sql statement it looks like this:

Update openquery([TST_M2M],'Select * from jodrtg WHERE fjobno = "12150"') set flastlab = '11/14/2001 1:30:59 PM'



My stored procedure looks like this:

CREATE PROCEDURE [dbo].[spM2MUpdateJodrtgDate] 
    @Jobno char(10), @M2MfLastLab varchar(25)
     
 AS

Declare @SQLString varchar(8000) 
Declare @SQLString2 varchar(8000)
Declare @DQ varchar (1) 
Declare @SQ varchar(1) 

SET @DQ = CHAR(34) 
-- Double Quotes 

SET @SQ = CHAR(39) 
-- Single Quotes 

-- always use select * - do not use field names
--this will be called PRD_DATA instead of TST_M2M when this sp is put into production. (name of dsn on server)

Set @SQLString = 'Update openquery([TST_M2M],'
 +@SQ + 'Select * from jodrtg WHERE fjobno = ' +@DQ+rtrim(@Jobno) +@DQ + @SQ +')'


set @SQLString2 = ' set flastlab = ' 
+@SQ + @m2mflastlab + @SQ

--+@DQ+ rtrim(@M2MFlastLab) +@DQ 
--above line is previous code

set @SQLString = @SQLString + @SQLString2

exec (@SQLString )

Declare @Error as int
Declare @Rowcount as int

Select @Error = @@Error,  @Rowcount = @@Rowcount

IF @Error = 0 
    Begin
	IF @Rowcount = 0 
	   Begin
		Set @Error = 99 --No records updated/inserted
                 End
    End 
Return(@Error)
GO"

---