May 3, 2010 at 8:45 am
I support a application that has a Access front end and a SQL Server 2005 Express back end. We started using SQL Server in July 08 and since that time we have had two releases where we added columns of data to one table. During the migration process we have both the old database and the new database attached to the server. There is a stored procedure that selects the columns from the old database to insert them into the new database. The column names are hard coded in the select and insert into statements which I am not happy with. I would rather come up with some way of getting the column names dynamically but I can't get it to work by executing only a block of code that has the correct column lists. The below code is a example of the way we are trying to get it to work. The version of the old database in this example is 2.01.2009 however when you run the code it throws a error invalid column name OgigTrackingNO. If you remove the code that is inserting data in that block and only select something as output it never executes that code. It almost seems like the compiler is examining each line of code regardless of the logic. The current old database does not have OgigTrackingNO that was added later.
Example code ************************************************
Declare @sToolVersion nvarchar(55)
set @sToolVersion = (select tool_pref_value from billing_ups_old.dbo.tbl_Tool_Preferences where tool_pref_id = 'version')
select @sToolVersion as curver
If (@sToolVersion < '2.01.2009')
begin
select '< Fist SQL Server Version' as out
end
If (@sToolVersion < '2.03.2010')
begin
select '< Jan 09 Ver this is the block that executes ' as out
end
If (@sToolVersion >= '2.03.2010')
begin
select ' Jan 2010 or later this does not execute but throws compiler error invalid column name OgigTrackingNO
the current version is Jan 09 ' as out
Insert into billing_ups.dbo.tbl_eInvoice_Main(OrigTrackingNo)
select OrigTrackingNo
from billing_ups_old.dbo.tbl_eInvoice_Main
end
May 5, 2010 at 10:38 am
Perhaps, using EXEC will help you solve this. What I would suggest is something like the following:
DECLARE @sToolVersion NVARCHAR(55)
DECLARE @sqlStatement varchar(1000)
SET @sToolVersion = (SELECT tool_pref_value
FROM billing_ups_old.dbo.tbl_Tool_Preferences
WHERE tool_pref_id = 'version'
)
SELECT @sToolVersion AS curver
IF (@sToolVersion < '2.01.2009')
BEGIN
SELECT '< Fist SQL Server Version' AS out
END
IF (@sToolVersion < '2.03.2010')
BEGIN
SELECT '< Jan 09 Ver this is the block that executes ' AS OUT
SET @sqlStatement = /*Input whatever sql statement you want to run here possible the following:*/'INSERT INTO billing_ups.dbo.tbl_eInvoice_Main(OrigTrackingNo) SELECT SomethingOtherThanOrigTrackingNo FROM billing_ups_old.dbo.tbl_eInvoice_Main'
END
IF (@sToolVersion >= '2.03.2010')
BEGIN
SELECT ' Jan 2010 or later this does not execute but throws compiler error invalid column name OgigTrackingNO
the current version is Jan 09 ' AS out
SET @sqlStatement = /*Other statement would go here*/'INSERT INTO billing_ups.dbo.tbl_eInvoice_Main(OrigTrackingNo) SELECT OrigTrackingNo FROM billing_ups_old.dbo.tbl_eInvoice_Main'
END
EXEC (@sqlCommand)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply