May 16, 2013 at 3:38 pm
I am doing some practice exercises from Microsofts training kit for 70-432 Implementation and Maintenance 2008(written by Mike Hotek) and am running the following code from page 148 (chapter 6 distributing and partitioning data) - i am however getting an error. The error is shown below. I am running this from sql2012 developer management studio.
use partitiontest
SET NOCOUNT ON
DECLARE @monthint,
@dayint
SET @month = 1
set @day = 1
while @month <= 12
BEGIN
WHILE @day <=28
begin
INSERT dbo.orders (OrderDate, OrderAmount)
SELECT cast(@month as varchar(2)) + '/' + cast(@day as varchar(2))
+ '/2005', @day = 20
SET @DAY = @DAY +1
END
set @day = 1
set @month = @month + 1
end
go
The error i get is:
Msg 141, Level 15, State 1, Line 0
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 199, Level 15, State 1, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable.
Is this because i am using sql2012 instead of 2008 ? I find it hard to imagine that what appears to be a basic error (for somebody who has written a book !) would be allowed to be published !!
May 16, 2013 at 3:43 pm
PearlJammer1 (5/16/2013)
I am doing some practice exercises from Microsofts training kit for 70-432 Implementation and Maintenance 2008(written by Mike Hotek) and am running the following code from page 148 (chapter 6 distributing and partitioning data) - i am however getting an error. The error is shown below. I am running this from sql2012 developer management studio.use partitiontest
SET NOCOUNT ON
DECLARE @monthint,
@dayint
SET @month = 1
set @day = 1
while @month <= 12
BEGIN
WHILE @day <=28
begin
INSERT dbo.orders (OrderDate, OrderAmount)
SELECT cast(@month as varchar(2)) + '/' + cast(@day as varchar(2))
+ '/2005', @day = 20
SET @DAY = @DAY +1
END
set @day = 1
set @month = @month + 1
end
go
The error i get is:
Msg 141, Level 15, State 1, Line 0
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 199, Level 15, State 1, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable.
Is this because i am using sql2012 instead of 2008 ? I find it hard to imagine that what appears to be a basic error (for somebody who has written a book !) would be allowed to be published !!
Trust the error message - It's pointing you in the correct direction - Try SELECT cast(@month as varchar(2)) + '/' + cast(@day as varchar(2))+ '/2005', @day for the insert statement. You are trying to assign a value in the select stmt to the variable @day
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
May 16, 2013 at 3:50 pm
PearlJammer1 (5/16/2013)
I am doing some practice exercises from Microsofts training kit for 70-432 Implementation and Maintenance 2008(written by Mike Hotek) and am running the following code from page 148 (chapter 6 distributing and partitioning data) - i am however getting an error. The error is shown below. I am running this from sql2012 developer management studio.use partitiontest
SET NOCOUNT ON
DECLARE @monthint,
@dayint
SET @month = 1
set @day = 1
while @month <= 12
BEGIN
WHILE @day <=28
begin
INSERT dbo.orders (OrderDate, OrderAmount)
SELECT cast(@month as varchar(2)) + '/' + cast(@day as varchar(2))
+ '/2005', @day = 20
SET @DAY = @DAY +1
END
set @day = 1
set @month = @month + 1
end
go
The error i get is:
Msg 141, Level 15, State 1, Line 0
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 199, Level 15, State 1, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable.
Is this because i am using sql2012 instead of 2008 ? I find it hard to imagine that what appears to be a basic error (for somebody who has written a book !) would be allowed to be published !!
Typesetting errors occur. The original manuscript may have the above code just fine. Could just be a QA error before publishing.
May 16, 2013 at 4:33 pm
Thanks - you where correct. I ommite the = 20 and it worked.
Thank you very much
🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply