Stored Procedure from SQL 6.5 to SQL 7.0

[John Bernbrock]

We recently upgraded from SQL 6.5 to SQL 7.0. I attended classes
and we all prepared a lot, and everything went smoothly.

Everything that is, except my stored procedures. They're giving me
fits.

1. When I use the stored procedure sp_FacilityAnalysis called as an odbc
passthrough query def from vb, I get errors. I try running the sp by itself
passing parameters, and I can only get the SELECT portion to function if
I precede it with a SET ANSI_DEFAULTS OFF. But even then, my INSERT
command fails. It says it completes, but nothing ends up populated in the
table.

2. When I try to take an existing script that worked in 6.5 such as billing_update.sql
and feed that into a new stored procedure, I get all kinds of errors related to the
local temp table #inv_sum. In one case, it tells me there is already a table object by
that name. In another case, it tells me #inv_num is an invalid object.

Any ideas? I've been scouring the documentation online and MSDN for days regarding
backward compatability, and though I've learned a lot, I haven't found the secret to keeping
some of my old 6.5 stored procedures and scripts without having to start fresh through the
7.0 wizard. Any help would be, per usual, appreciated.

[Mike John]

Can you post some code, error message details and ddl of the tables being used to give us a fighting chance?

Mike John

KnowledgePool Ltd


------------
John Bernbrock at 5/23/00 6:32:21 PM

We recently upgraded from SQL 6.5 to SQL 7.0. I attended classes
and we all prepared a lot, and everything went smoothly.

Everything that is, except my stored procedures. They're giving me
fits.

1. When I use the stored procedure sp_FacilityAnalysis called as an odbc
passthrough query def from vb, I get errors. I try running the sp by itself
passing parameters, and I can only get the SELECT portion to function if
I precede it with a SET ANSI_DEFAULTS OFF. But even then, my INSERT
command fails. It says it completes, but nothing ends up populated in the
table.

2. When I try to take an existing script that worked in 6.5 such as billing_update.sql
and feed that into a new stored procedure, I get all kinds of errors related to the
local temp table #inv_sum. In one case, it tells me there is already a table object by
that name. In another case, it tells me #inv_num is an invalid object.

Any ideas? I've been scouring the documentation online and MSDN for days regarding
backward compatability, and though I've learned a lot, I haven't found the secret to keeping
some of my old 6.5 stored procedures and scripts without having to start fresh through the
7.0 wizard. Any help would be, per usual, appreciated.

[John Bernbrock]

Sure...here is one that's giving me fits. I'm guessing that it has something
to do with the sp_dpoptions settings (maybe ansi defaults?) Nonetheless, on
the first INSERT...SELECT sequence right off the bat, it populates 0 rows
into the table facility_analysis_fac. The data is there if I run that same
portion of code through the query optimizer with SET ANSI_DEFAULTS OFF...but
when I embed it in the sp, it still fails. It's rather lengthy...so I'll just send up to a point for what it's worth...here it is... Thanks again for any help.


/****** Object: Stored Procedure dbo.sp_FacilityAnalysis Script Date: 05/12/2000 11:11:38 PM ******/
CREATE PROCEDURE sp_FacilityAnalysis

@Universal varchar(255) = "",
@DateRange varchar(255) = "",
@FromDate datetime,
@ToDate datetime,
@ReportFromDate datetime,
@ReportToDate datetime
AS

DECLARE @msg varchar(255),
@sql varchar(255),
@sql2 varchar(255),
@sql3 varchar(255),
@sql4 varchar(255),
@sql5 varchar(255),
@TempEarned money


SET QUOTED_IDENTIFIER OFF
SET ANSI_PADDING ON






IF (object_id('tempdb..#mytemp&#39 is not null)
drop table #mytemp

PRINT 'Input Parameters:'
SELECT @msg = 'Universal: ' + @Universal
PRINT @msg
SELECT @msg = 'DateRange: ' + @DateRange
PRINT @msg
SELECT @msg = 'FromDate: ' + CONVERT(varchar, @FromDate)
PRINT @msg
SELECT @msg = 'ToDate: ' + CONVERT(varchar, @ToDate)
PRINT @msg
SELECT @msg = 'ReportFromDate: ' + CONVERT(varchar, @ReportFromDate)
PRINT @msg
SELECT @msg = 'ReportToDate: ' + CONVERT(varchar, @ReportToDate)
PRINT @msg
PRINT ' '


/*
Select subset of facility data
*/

SELECT @sql = "INSERT facility_analysis_fac (
facility_key, division, status, inception_date,
expiration_date, cancellation_date, short_name) "

SELECT @sql2 = "SELECT facility_key, division, status, inception_date,
expiration_date, cancellation_date, short_name
FROM facility WHERE "

IF @Universal <> &#34;&#34;

IF @DateRange <> &#34;&#34;
BEGIN
SELECT @sql3 = @DateRange + &#34; AND &#34;
SELECT @sql4 = @Universal
SELECT @sql5 = &#34; AND ((short_name) NOT LIKE &#39;%C-F%&#39&#34;
END
ELSE
BEGIN
SELECT @sql3 = @Universal
SELECT @sql4 = &#34; AND ((short_name) NOT LIKE &#39;%C-F%&#39&#34;
SELECT @sql5 = &#34;&#34;
END
ELSE
BEGIN
SELECT @sql3 = &#34;((short_name) NOT LIKE &#39;%C-F%&#39&#34;

SELECT @sql4 = &#34;&#34;
SELECT @sql5 = &#34;&#34;
END


PRINT @sql
PRINT @sql2

PRINT @sql3
PRINT @sql4
PRINT @sql5

DELETE facility_analysis_fac

EXEC(@sql + @sql2 + @sql3 + @sql4 + @sql5)

select @msg = &#34;Results for facility_analysis_fac&#34;
print @msg
select * from facility_analysis_fac



/*
Execute SQL statement to join facility_analysis_fac and endorsement

inserts data into fac_1A
*/

------------
Mike John at 5/24/00 6:18:35 AM

Can you post some code, error message details and ddl of the tables being used to give us a fighting chance?

Mike John

KnowledgePool Ltd


------------
John Bernbrock at 5/23/00 6:32:21 PM

We recently upgraded from SQL 6.5 to SQL 7.0. I attended classes
and we all prepared a lot, and everything went smoothly.

Everything that is, except my stored procedures. They&#39;re giving me
fits.

1. When I use the stored procedure sp_FacilityAnalysis called as an odbc
passthrough query def from vb, I get errors. I try running the sp by itself
passing parameters, and I can only get the SELECT portion to function if
I precede it with a SET ANSI_DEFAULTS OFF. But even then, my INSERT
command fails. It says it completes, but nothing ends up populated in the
table.

2. When I try to take an existing script that worked in 6.5 such as billing_update.sql
and feed that into a new stored procedure, I get all kinds of errors related to the
local temp table #inv_sum. In one case, it tells me there is already a table object by
that name. In another case, it tells me #inv_num is an invalid object.

Any ideas? I&#39;ve been scouring the documentation online and MSDN for days regarding
backward compatability, and though I&#39;ve learned a lot, I haven&#39;t found the secret to keeping
some of my old 6.5 stored procedures and scripts without having to start fresh through the
7.0 wizard. Any help would be, per usual, appreciated.