>The way how I do it is to create the SP always new on upgrading. This way I can be sure always to have the latest version of the stored procedure if something changes.
>
>DROP PROCEDURE ExportToExcelData
>
>Then execute the query starting from CREATE PROCEDURE...
>
>Also your commenting of the GO commands is FoxPro syntax, so SQL Server will generate an error there. You have to write purely SQL syntax inside of the TEXT/ENDTEXT block.
I always recommend writing purely SQL syntax even in a VFP/Fox query. Instead of
SELECT FIELDLIST FROM TABLE WHERE BETWEEN(datefield,date1,date2)
That's making use of the Fox BETWEEN function, which is mixing languages.
write
SELECT FIELDLIST FROM TABLE WHERE datefield BETWEEN date1 and date2
That way you can move among SQL versions with less confusion.
>
>
>>Hello Comunity,
>>
>>I try to create an SQL Server Stored Procedure from my VFP desktop Application, then i write all my SP inside TEXT..TO VFP.
>>The first problems was the [GO], then i comment all, but i have an error near PROCEDURE ??
>>
>>I paste the code here:
>>
>>
>> Text to msel textmerge noshow pretext 7
>> IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
>> WHERE ROUTINE_NAME = 'ExportToExcelData'
>> AND ROUTINE_TYPE = 'PROCEDURE')
>> BEGIN
>> PRINT 'Existe'
>> END
>> ELSE
>> BEGIN
>> USE [MyDatabase]
>> *!*GO
>> /****** Object: StoredProcedure [dbo].[ExportToExcelData] Script Date: 19/11/2017 21:37:04 ******/
>> SET ANSI_NULLS ON
>> *!*GO
>> SET QUOTED_IDENTIFIER ON
>> *!*GO
>>
>> CREATE PROCEDURE [dbo].[ExportToExcelData] ( @dbName varchar(100) = 'master', @sql varchar(5000) = '', @fullFileName varchar(100) = '' )
>> as
>> if @sql = '' or @fullFileName = ''
>> begin
>> select 0 as ReturnValue -- failure
>> return
>> end
>> --if DB isn't passed in set it to master
>> select @dbName = 'use ' + @dbName + ';'
>> if object_id('##TempExportData') is not null
>> drop table ##TempExportData
>> if object_id('##TempExportData2') is not null
>> drop table ##TempExportData2
>> -- insert data into a global temp table
>> declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
>> select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' + substring(@sql, charindex('from', @sql)-1, len(@sql))
>> exec(@dbName + @tempSQL)
>>
>> if @@error > 0
>> begin
>> select 0 as ReturnValue -- failure
>> return
>> END
>>
>> SELECT @columnNames = COALESCE( @columnNames + ',', '') + '['+ column_name + ']', @columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),' + '['+ column_name + ']'
>> + case when data_type in ('datetime', 'smalldatetime') then ',121'
>> when data_type in ('numeric', 'decimal') then ',128'
>> when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
>> when data_type in ('datetime', 'smalldatetime') then ',120' else '' end + ') as ' + '[' + column_name + ']'
>> FROM tempdb.INFORMATION_SCHEMA.Columns WHERE table_name = '##TempExportData'
>>
>> SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(replace(replace(@columnNames,'[',''),']',''), ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
>> exec (@sql)
>>
>> -- build full BCP query
>> SELECT @sql = 'bcp " select * from ##TempExportData2" queryout "' + @fullFileName + '" -T -c -CRAW' -- execute BCP
>> EXEC master..xp_cmdshell @sql
>>
>> IF @@error > 0
>> BEGIN
>> SELECT 0 AS ReturnValue -- failure
>> RETURN
>> END
>> DROP TABLE ##TempExportData
>> DROP TABLE ##TempExportData2
>> SELECT 1 as ReturnValue -- success ---
>> END
>> Endtext
>>
>> If u_sqlexec(msel)
>> Else
>> MSG(msel)
>> Endif
>>>>
>>I hope someone could give me some hepl to understand why this error.
>>
>>Many Thanks,
>>Luis Santos
