-
Stored Procedure from SQL 6.5 to SQL 7.0
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.
-
Stored Procedure from SQL 6.5 to SQL 7.0 (reply)
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.
-
Stored Procedure from SQL 6.5 to SQL 7.0 (reply)
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' 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 <> ""
IF @DateRange <> ""
BEGIN
SELECT @sql3 = @DateRange + " AND "
SELECT @sql4 = @Universal
SELECT @sql5 = " AND ((short_name) NOT LIKE '%C-F%'"
END
ELSE
BEGIN
SELECT @sql3 = @Universal
SELECT @sql4 = " AND ((short_name) NOT LIKE '%C-F%'"
SELECT @sql5 = ""
END
ELSE
BEGIN
SELECT @sql3 = "((short_name) NOT LIKE '%C-F%'"
SELECT @sql4 = ""
SELECT @sql5 = ""
END
PRINT @sql
PRINT @sql2
PRINT @sql3
PRINT @sql4
PRINT @sql5
DELETE facility_analysis_fac
EXEC(@sql + @sql2 + @sql3 + @sql4 + @sql5)
select @msg = "Results for facility_analysis_fac"
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'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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|