HELP- need to convert SQL 6.5 Back Up to 2000
I have made enterprise manager back ups of two databases
on a SQL 6.5 server. The back ups are .dat files. I
want/need to convert these to SQL 2000 or SQL 7.0. I have
a SQL 2000 server. I tried to create a new database on
the SQL 2000 server and then restore the back ups to the
SQL 2000 server's new database I just created. I get an
error saying that the back up is not valid.
What is the procedure for getting this .dat 6.5 back up to
SQL 2000?
mick2767@.hotmail.comWhat you should do is to use the Upgrade Wizard... If you have the SQL 6.5
server with the dbs installed... Simply go to program files -> SQL Server
and you will find the upgrade wizard... That's the way to go.!
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<mick2767@.hotmail.com> wrote in message
news:0cc201c3adf9$da109e50$a501280a@.phx.gbl...
> HELP- need to convert SQL 6.5 Back Up to 2000
> I have made enterprise manager back ups of two databases
> on a SQL 6.5 server. The back ups are .dat files. I
> want/need to convert these to SQL 2000 or SQL 7.0. I have
> a SQL 2000 server. I tried to create a new database on
> the SQL 2000 server and then restore the back ups to the
> SQL 2000 server's new database I just created. I get an
> error saying that the back up is not valid.
> What is the procedure for getting this .dat 6.5 back up to
> SQL 2000?
> mick2767@.hotmail.com|||When you do your restore, make sure that under options...Force restore
over existing database is checked. That might solve your problem.
"Wayne Snyder" <wsnyder@.ikon.com> wrote in message news:<e#z$IxfrDHA.1880@.TK2MSFTNGP09.phx.gbl>...
> What you should do is to use the Upgrade Wizard... If you have the SQL 6.5
> server with the dbs installed... Simply go to program files -> SQL Server
> and you will find the upgrade wizard... That's the way to go.!
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <mick2767@.hotmail.com> wrote in message
> news:0cc201c3adf9$da109e50$a501280a@.phx.gbl...
> > HELP- need to convert SQL 6.5 Back Up to 2000
> >
> > I have made enterprise manager back ups of two databases
> > on a SQL 6.5 server. The back ups are .dat files. I
> > want/need to convert these to SQL 2000 or SQL 7.0. I have
> > a SQL 2000 server. I tried to create a new database on
> > the SQL 2000 server and then restore the back ups to the
> > SQL 2000 server's new database I just created. I get an
> > error saying that the back up is not valid.
> >
> > What is the procedure for getting this .dat 6.5 back up to
> > SQL 2000?
> >
> > mick2767@.hotmail.com|||As Wayne said in his post: you cannot restore a 6.5 database into 7.0 or 2000.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Indigo Montoya" <Indigo_Montoya_2@.hotmail.com> wrote in message
news:f18452ec.0311181645.5b561bc7@.posting.google.com...
> When you do your restore, make sure that under options...Force restore
> over existing database is checked. That might solve your problem.
>
> "Wayne Snyder" <wsnyder@.ikon.com> wrote in message news:<e#z$IxfrDHA.1880@.TK2MSFTNGP09.phx.gbl>...
> > What you should do is to use the Upgrade Wizard... If you have the SQL 6.5
> > server with the dbs installed... Simply go to program files -> SQL Server
> > and you will find the upgrade wizard... That's the way to go.!
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Computer Education Services Corporation (CESC), Charlotte, NC
> > www.computeredservices.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> > <mick2767@.hotmail.com> wrote in message
> > news:0cc201c3adf9$da109e50$a501280a@.phx.gbl...
> > > HELP- need to convert SQL 6.5 Back Up to 2000
> > >
> > > I have made enterprise manager back ups of two databases
> > > on a SQL 6.5 server. The back ups are .dat files. I
> > > want/need to convert these to SQL 2000 or SQL 7.0. I have
> > > a SQL 2000 server. I tried to create a new database on
> > > the SQL 2000 server and then restore the back ups to the
> > > SQL 2000 server's new database I just created. I get an
> > > error saying that the back up is not valid.
> > >
> > > What is the procedure for getting this .dat 6.5 back up to
> > > SQL 2000?
> > >
> > > mick2767@.hotmail.com
Showing posts with label convert. Show all posts
Showing posts with label convert. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
Help me to understand this SQL sentence
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')what is that sql sentence supposed to be doing?
it looks like it's trying to be recursive|||If we are trying to get some value into the variable , then this query is no good.
"Set @.mSQL = 'SELECT Max([AccountID] "
should actuallu read
"SELECT @.mSQL = Max([AccountID] "
Hope this helps...|||First we must identify the subject, then the verb, and if they exist, the direct object and the indirect object....Oh, sorry.
Any ideas what the value of @.mSQL was before this assignment? Maybe the original programmer was trying to reduce the number of variables he had? (OK, I am reaching, there)|||It looks to me like the code is creating a "Superkey", a concatenation of multiple natural values to fabricate a single unique semi-surrogate key.
Superkeys are database abominations frequently found in legacy systems or in applications created by noob developers.|||Hi all,
The original procedure as follow
--Repaired 03/11/2005
CREATE Procedure IV_spStockReportSummary
(
@.mName Varchar(50),
@.mtxtDate DateTime,
@.moptName TinyInt,
@.mchkReport Bit
)
As
Declare @.mSQL Varchar(3000)
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + @.mName + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
If @.mchkReport=0
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End) AS CategoryName,
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End) AS Unit, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblItemList I INNER JOIN CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN
IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND
LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON C.AccountID = LD.AccountID ON I.ItemID = LD.ItemID INNER JOIN IV_tblUnitOfMeasureList U ON
I.InvUnitOfMeasr = U.UMID INNER JOIN IV_tblCategoryList CI ON I.CategoryID = CI.CategoryID INNER JOIN IV_tblStoreHouseList S ON
LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End), LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End),
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End), (Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End), L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
End
Else
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
LD.ItemID, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount INTO [xIV_tblStockSumTmp' + @.mName + ']
FROM CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND
LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON
C.AccountID = LD.AccountID INNER JOIN IV_tblStoreHouseList S ON LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End),
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
LD.ItemID, L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
Set @.mSQL = 'SELECT S.AccountID, S.AccountName, S.StorehouseID, S.StoreHouseName, S.ItemID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U1.UMName Else U1.UMName_Secn End) AS Unit,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then C.CategoryName Else C.CategoryName_Secn End) AS CategoryName, S.LotNo, S.ExpireDate,
(S.OnHand * (Case When V.ConvFactor IS Null Then 1 Else V.ConvFactor End)) AS OnHand, S.Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblUnitOfMeasureList U1 LEFT JOIN IV_tblUMConversion V ON U1.UMID = V.UMToID RIGHT JOIN IV_tblUnitOfMeasureList U ON
V.UMFromID = U.UMID RIGHT JOIN IV_tblItemList I ON U1.UMID = I.PrintUnitOfMeasr AND U.UMID = I.InvUnitOfMeasr LEFT JOIN
IV_tblCategoryList C ON I.CategoryID = C.CategoryID RIGHT JOIN [xIV_tblStockSumTmp' + @.mName + '] S ON I.ItemID = S.ItemID')
End
Return
GO|||i can't believe that runs
and the guy that wrote it should be shot
it no longer looks like it's trying to be recursive
but there's a dangling ) after the first Exec, just before If @.mchkReport=0|||i can't believe that runs
...
but there's a dangling ) after the first Exec, just before If @.mchkReport=0Nor me. There look to be a lot of dangly things.
dangquanghai - are you saying that this actually works?
--Repaired 03/11/2005
Doesn't look like it from here|||I m sure It run smoothly.|||I can give you the examble from SQL server book online
C. Use EXECUTE 'tsql_string' with a variable
This example shows how EXECUTE handles dynamically built strings containing variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables (type = U).
Note This example is shown for illustrative purposes only.
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @.tablename sysname
FETCH NEXT FROM tables_cursor INTO @.tablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
/* A @.@.FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @.tablename)
FETCH NEXT FROM tables_cursor INTO @.tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor|||Uh-Oh... you said the forbidden word... "cursor"...|||Oh sorry friends !!!
this sentence
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
contain two sentences
1. Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
2.'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
The coder typed it at the same row so it make me confuse
Now, It is so clear
Thank for your consideration|||Uh-Oh... you said the forbidden word... "cursor"...I think that was to explain to us what EXEC does :)|||Ah. So that is what EXEC does. I had no idea. Apparently it is a convenient method for f***ing up an application. The posted code demonstrates it clearly.
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')what is that sql sentence supposed to be doing?
it looks like it's trying to be recursive|||If we are trying to get some value into the variable , then this query is no good.
"Set @.mSQL = 'SELECT Max([AccountID] "
should actuallu read
"SELECT @.mSQL = Max([AccountID] "
Hope this helps...|||First we must identify the subject, then the verb, and if they exist, the direct object and the indirect object....Oh, sorry.
Any ideas what the value of @.mSQL was before this assignment? Maybe the original programmer was trying to reduce the number of variables he had? (OK, I am reaching, there)|||It looks to me like the code is creating a "Superkey", a concatenation of multiple natural values to fabricate a single unique semi-surrogate key.
Superkeys are database abominations frequently found in legacy systems or in applications created by noob developers.|||Hi all,
The original procedure as follow
--Repaired 03/11/2005
CREATE Procedure IV_spStockReportSummary
(
@.mName Varchar(50),
@.mtxtDate DateTime,
@.moptName TinyInt,
@.mchkReport Bit
)
As
Declare @.mSQL Varchar(3000)
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + @.mName + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
If @.mchkReport=0
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End) AS CategoryName,
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End) AS Unit, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblItemList I INNER JOIN CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN
IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND
LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON C.AccountID = LD.AccountID ON I.ItemID = LD.ItemID INNER JOIN IV_tblUnitOfMeasureList U ON
I.InvUnitOfMeasr = U.UMID INNER JOIN IV_tblCategoryList CI ON I.CategoryID = CI.CategoryID INNER JOIN IV_tblStoreHouseList S ON
LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End), LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End),
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End), (Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End), L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
End
Else
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
LD.ItemID, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount INTO [xIV_tblStockSumTmp' + @.mName + ']
FROM CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND
LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON
C.AccountID = LD.AccountID INNER JOIN IV_tblStoreHouseList S ON LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End),
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
LD.ItemID, L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
Set @.mSQL = 'SELECT S.AccountID, S.AccountName, S.StorehouseID, S.StoreHouseName, S.ItemID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U1.UMName Else U1.UMName_Secn End) AS Unit,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then C.CategoryName Else C.CategoryName_Secn End) AS CategoryName, S.LotNo, S.ExpireDate,
(S.OnHand * (Case When V.ConvFactor IS Null Then 1 Else V.ConvFactor End)) AS OnHand, S.Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblUnitOfMeasureList U1 LEFT JOIN IV_tblUMConversion V ON U1.UMID = V.UMToID RIGHT JOIN IV_tblUnitOfMeasureList U ON
V.UMFromID = U.UMID RIGHT JOIN IV_tblItemList I ON U1.UMID = I.PrintUnitOfMeasr AND U.UMID = I.InvUnitOfMeasr LEFT JOIN
IV_tblCategoryList C ON I.CategoryID = C.CategoryID RIGHT JOIN [xIV_tblStockSumTmp' + @.mName + '] S ON I.ItemID = S.ItemID')
End
Return
GO|||i can't believe that runs
and the guy that wrote it should be shot
it no longer looks like it's trying to be recursive
but there's a dangling ) after the first Exec, just before If @.mchkReport=0|||i can't believe that runs
...
but there's a dangling ) after the first Exec, just before If @.mchkReport=0Nor me. There look to be a lot of dangly things.
dangquanghai - are you saying that this actually works?
--Repaired 03/11/2005
Doesn't look like it from here|||I m sure It run smoothly.|||I can give you the examble from SQL server book online
C. Use EXECUTE 'tsql_string' with a variable
This example shows how EXECUTE handles dynamically built strings containing variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables (type = U).
Note This example is shown for illustrative purposes only.
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @.tablename sysname
FETCH NEXT FROM tables_cursor INTO @.tablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
/* A @.@.FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @.tablename)
FETCH NEXT FROM tables_cursor INTO @.tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor|||Uh-Oh... you said the forbidden word... "cursor"...|||Oh sorry friends !!!
this sentence
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
contain two sentences
1. Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
2.'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
The coder typed it at the same row so it make me confuse
Now, It is so clear
Thank for your consideration|||Uh-Oh... you said the forbidden word... "cursor"...I think that was to explain to us what EXEC does :)|||Ah. So that is what EXEC does. I had no idea. Apparently it is a convenient method for f***ing up an application. The posted code demonstrates it clearly.
Subscribe to:
Posts (Atom)