February 8, 2007 at 3:01 pm
For Db A: I generated script of stored procedures on sql 2005. Copy script to another server using sql 2000. Using SQL Analyzer tried to create Stored Procedures for Db A. Isn't working for me.
Looking for resolution or alternative method.
February 8, 2007 at 4:13 pm
What error message are you getting? There are new functions in SQL Server 2005 that won't be backward compatible for SQL Server 2000.
Can you post the first few lines of the script you're using?
February 9, 2007 at 6:56 am
Did you create the stored procedure using the Management Studio Script Creation Wizard? If so did you make sure you have Script for Server Version as SQL Server 2000? By default it is 2005. However as rightly mentioned by David if you are using something very specifi to 2005 that is not supported by 2000. You may want to edit the script to make it compatible with 2000.
Prasad Bhogadi
www.inforaise.com
February 9, 2007 at 7:48 am
I re-gen'd the script suig SQL 2000. REceived error : Invalid sys.object
Then edited one sp from begin call to end and this was succussful. Ran a group of create sp's edited same- getting msg specific to an sp Invalid column name 'Null'
script for this sp below:
++++++++++++++++++++++++
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[pri_company] @cmpid int, @cname nvarchar(50), @ccode nvarchar(12),@banner nvarchar(50),@popbanner nvarchar(50)
AS
if @banner = "NULL" AND @popbanner = "NULL"
INSERT INTO companies
(cmpid, cname, customercode)
VALUES
(@cmpid,@cname,@ccode)
else
if @banner "NULL" AND @popbanner = "NULL"
INSERT INTO companies
(cmpid, cname, customercode,banner)
VALUES
(@cmpid,@cname,@ccode,@banner)
else
if @banner = "NULL" AND @popbanner "NULL"
INSERT INTO companies
(cmpid, cname, customercode,popbanner)
VALUES
(@cmpid,@cname,@ccode,@popbanner)
if @banner "NULL" AND @popbanner "NULL"
INSERT INTO companies
(cmpid, cname, customercode,banner,popbanner)
VALUES
(@cmpid,@cname,@ccode,@banner,@popbanner)
'
END
February 9, 2007 at 8:13 am
Continued:
SELECT * FROM sys.objects
Returns Invalid Object.
Example:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pru_assignedseats]') AND type in (N'P', N'PC'))
February 9, 2007 at 8:48 am
For SQL Server 2000, you can use sysobjects but not sys.objects.
The NULL problem probably shows up because you have 'quoted_identifiers' turned on so anything in double quotes is parsed as a column name. Add 'set quoted_identifier OFF' to the beginning of the script.
February 9, 2007 at 9:35 am
I've resolved the Stored Procedures. It was combination of issues and resolutions, some not related to the SP so I won't list here.
Thanks for all the response.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply