Help me with the error messages please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help me with the error messages please

Author

Topic

mandarpowale
Starting Member

3 Posts

Posted - 2009-01-16 : 08:37:48

Guys

I don't know the syntax/ language used to program in sql and i'm stuck with a data warehousing project. Please help me find the error in the code, and suggest the correction.

Thanks in advance,
Mandar
I'm using MS SQL 2005

Here is the code

CREATE PROCEDURE load_dim_time (
@dim_table_name VARCHAR(255),
@start_date_dt SMALLDATETIME,
@end_date_dt SMALLDATETIME
)
AS
SET NOCOUNT ON
DECLARE
@sql_string NVARCHAR(1024)
, @time_member_key INT
, @calendar_date_dt SMALLDATETIME
, @calendar_day_of_week_num INT
, @calendar_day_of_week_name VARCHAR(10)
, @calendar_day_of_month_num INT
, @calendar_day_of_year_num INT
, @calendar_week_num INT
, @calendar_month_num INT
, @calendar_month_name VARCHAR(10)
, @calendar_quarter_num INT
, @calendar_year_num INT

SET @calendar_date_dt = @start_date_dt
WHILE (@calendar_date_dt <= @end_date_dt)
BEGIN
IF NOT EXISTS
(
SELECT time_member_key
FROM dim_time
WHERE calendar_date_dt = @calendar_date_dt
)
BEGIN
SELECT
@calendar_day_of_week_num = DATEPART(dw, @calendar_date_dt)
, @calendar_day_of_week_name = DATENAME(WEEKDAY, @calendar_date_dt)
, @calendar_day_of_month_num = DATEPART(DD, @calendar_date_dt)
, @calendar_day_of_year_num = DATEPART(DY, @calendar_date_dt)
, @calendar_week_num = DATEPART(WK, @calendar_date_dt)
, @calendar_month_num = DATEPART(M, @calendar_date_dt)
, @calendar_month_name = DATENAME(MONTH, @calendar_date_dt)
, @calendar_quarter_num = DATEPART(QQ, @calendar_date_dt)
, @calendar_year_num = DATEPART(YYYY, @calendar_date_dt)
, @time_member_key =
CAST(
CAST(@calendar_year_num AS VARCHAR) +
RIGHT(’00’ + CAST(@calendar_day_of_year_num AS VARCHAR), 3)
AS INT)
SELECT @sql_string =
’INSERT INTO ’ + @dim_table_name +
’ (’ +
’time_member_key, ’ +
’calendar_date_dt, ’ +
’calendar_day_of_week_num,’ +
’calendar_day_of_week_name,’ +
’calendar_day_of_month_num,’ +
’calendar_day_of_year_num,’ +
’calendar_week_num,’ +
’calendar_month_num,’ +
’calendar_month_name,’ +
’calendar_quarter_num, ’ +
’calendar_year_num’ +
’) ’ +
’VALUES ’ +
’(’ +
CHAR(39) + CAST(@time_member_key AS VARCHAR) + CHAR(39) + ’,’ +
CHAR(39) + CAST(@calendar_date_dt AS VARCHAR) + CHAR(39) + ’,’ +
CAST(@calendar_day_of_week_num AS VARCHAR) + ’,’ +
CHAR(39) + @calendar_day_of_week_name + CHAR(39) + ’,’ +
CAST(@calendar_day_of_month_num AS VARCHAR) + ’,’ +
CAST(@calendar_day_of_year_num AS VARCHAR) + ’,’ +
CAST(@calendar_week_num AS VARCHAR) + ’,’ +
CAST(@calendar_month_num AS VARCHAR) + ’,’ +
CHAR(39) + @calendar_month_name + CHAR(39) + ’,’ +
CAST(@calendar_quarter_num AS VARCHAR) + ’,’ +
CAST(@calendar_year_num AS VARCHAR) + ’)’

EXEC sp_executesql @sql_string
END

Error messages shown below

Server: Msg 170, Level 15, State 1, Procedure load_dim_time, Line 45
Line 45: Incorrect syntax near '’'.
Server: Msg 170, Level 15, State 1, Procedure load_dim_time, Line 48
Line 48: Incorrect syntax near '’'.
Server: Msg 170, Level 15, State 1, Procedure load_dim_time, Line 77
Line 77: Incorrect syntax near 'END'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 08:49:48

use PRINT(@sql_string) instead of last EXEC and post back result

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-16 : 08:52:17

[code]CREATE PROCEDURE load_dim_time (
@dim_table_name VARCHAR(255),
@start_date_dt SMALLDATETIME,
@end_date_dt SMALLDATETIME
)
AS
SET NOCOUNT ON
DECLARE
@sql_string NVARCHAR(1024)
, @time_member_key INT
, @calendar_date_dt SMALLDATETIME
, @calendar_day_of_week_num INT
, @calendar_day_of_week_name VARCHAR(10)
, @calendar_day_of_month_num INT
, @calendar_day_of_year_num INT
, @calendar_week_num INT
, @calendar_month_num INT
, @calendar_month_name VARCHAR(10)
, @calendar_quarter_num INT
, @calendar_year_num INT

SET @calendar_date_dt = @start_date_dt
WHILE (@calendar_date_dt <= @end_date_dt)
BEGIN
IF NOT EXISTS
(
SELECT time_member_key
FROM dim_time
WHERE calendar_date_dt = @calendar_date_dt
)
BEGIN
set @calendar_day_of_week_num = DATEPART(dw, @calendar_date_dt)
set @calendar_day_of_week_name = DATENAME(WEEKDAY, @calendar_date_dt)
set @calendar_day_of_month_num = DATEPART(DD, @calendar_date_dt)
set @calendar_day_of_year_num = DATEPART(DY, @calendar_date_dt)
set @calendar_week_num = DATEPART(WK, @calendar_date_dt)
set @calendar_month_num = DATEPART(M, @calendar_date_dt)
set @calendar_month_name = DATENAME(MONTH, @calendar_date_dt)
set @calendar_quarter_num = DATEPART(QQ, @calendar_date_dt)
set @calendar_year_num = DATEPART(YYYY, @calendar_date_dt)
set @time_member_key =
CAST(
CAST(@calendar_year_num AS VARCHAR) +
RIGHT('00' + CAST(@calendar_day_of_year_num AS VARCHAR), 3)
AS INT)

SELECT @sql_string =
'INSERT INTO ' + @dim_table_name +
' (' +
'time_member_key, ' +
'calendar_date_dt, ' +
'calendar_day_of_week_num,' +
'calendar_day_of_week_name,' +
'calendar_day_of_month_num,' +
'calendar_day_of_year_num,' +
'calendar_week_num,' +
'calendar_month_num,' +
'calendar_month_name,' +
'calendar_quarter_num, ' +
'calendar_year_num' +
') ' +
'VALUES ' +
'(' +
CHAR(39) + CAST(@time_member_key AS VARCHAR) + CHAR(39) + ',' +
CHAR(39) + CAST(@calendar_date_dt AS VARCHAR) + CHAR(39) + ',' +
CAST(@calendar_day_of_week_num AS VARCHAR) + ',' +
CHAR(39) + @calendar_day_of_week_name + CHAR(39) + ',' +
CAST(@calendar_day_of_month_num AS VARCHAR) + ',' +
CAST(@calendar_day_of_year_num AS VARCHAR) + ',' +
CAST(@calendar_week_num AS VARCHAR) + ',' +
CAST(@calendar_month_num AS VARCHAR) + ',' +
CHAR(39) + @calendar_month_name + CHAR(39) + ',' +
CAST(@calendar_quarter_num AS VARCHAR) + ',' +
CAST(@calendar_year_num AS VARCHAR) + ')'

EXEC sp_executesql @sql_string
END

end[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 08:58:57

[code]
CREATE PROCEDURE load_dim_time (
@dim_table_name VARCHAR(255),
@start_date_dt SMALLDATETIME,
@end_date_dt SMALLDATETIME
)
AS
SET NOCOUNT ON
DECLARE
@sql_string NVARCHAR(1024)
, @time_member_key INT
, @calendar_date_dt SMALLDATETIME
, @calendar_day_of_week_num INT
, @calendar_day_of_week_name VARCHAR(10)
, @calendar_day_of_month_num INT
, @calendar_day_of_year_num INT
, @calendar_week_num INT
, @calendar_month_num INT
, @calendar_month_name VARCHAR(10)
, @calendar_quarter_num INT
, @calendar_year_num INT

SET @calendar_date_dt = @start_date_dt
WHILE (@calendar_date_dt <= @end_date_dt)
BEGIN
IF NOT EXISTS
(
SELECT time_member_key
FROM dim_time
WHERE calendar_date_dt = @calendar_date_dt
)
BEGIN
set @calendar_day_of_week_num = DATEPART(dw, @calendar_date_dt)
set @calendar_day_of_week_name = DATENAME(WEEKDAY, @calendar_date_dt)
set @calendar_day_of_month_num = DATEPART(DD, @calendar_date_dt)
set @calendar_day_of_year_num = DATEPART(DY, @calendar_date_dt)
set @calendar_week_num = DATEPART(WK, @calendar_date_dt)
set @calendar_month_num = DATEPART(M, @calendar_date_dt)
set @calendar_month_name = DATENAME(MONTH, @calendar_date_dt)
set @calendar_quarter_num = DATEPART(QQ, @calendar_date_dt)
set @calendar_year_num = DATEPART(YYYY, @calendar_date_dt)
set @time_member_key =
CAST(
CAST(@calendar_year_num AS VARCHAR) +
RIGHT('00' + CAST(@calendar_day_of_year_num AS VARCHAR), 3)
AS INT)

SELECT @sql_string =
'INSERT INTO ' + @dim_table_name +
'(time_member_key, calendar_date_dt,
calendar_day_of_week_num,
calendar_day_of_week_name,
calendar_day_of_month_num,
calendar_day_of_year_num,
calendar_week_num,
calendar_month_num,
calendar_month_name,
calendar_quarter_num,
calendar_year_num
) VALUES (' +
CHAR(39) + CAST(@time_member_key AS VARCHAR) + CHAR(39) + ',' +
CHAR(39) + CAST(@calendar_date_dt AS VARCHAR) + CHAR(39) + ',' +
CAST(@calendar_day_of_week_num AS VARCHAR) + ',' +
CHAR(39) + @calendar_day_of_week_name + CHAR(39) + ',' +
CAST(@calendar_day_of_month_num AS VARCHAR) + ',' +
CAST(@calendar_day_of_year_num AS VARCHAR) + ',' +
CAST(@calendar_week_num AS VARCHAR) + ',' +
CAST(@calendar_month_num AS VARCHAR) + ',' +
CHAR(39) + @calendar_month_name + CHAR(39) + ',' +
CAST(@calendar_quarter_num AS VARCHAR) + ',' +
CAST(@calendar_year_num AS VARCHAR) + ')'

EXEC (@sql_string)
END

end
[/code]

mandarpowale
Starting Member

3 Posts

Posted - 2009-01-16 : 09:13:18

thank you guys

sorry i made a mistake in typing the initial code , the right(& edited)code is here below and i get just one error message now.

CREATE PROCEDURE load_dim_time (
@dim_table_name VARCHAR(255),
@start_date_dt SMALLDATETIME,
@end_date_dt SMALLDATETIME
)
AS
SET NOCOUNT ON
DECLARE
@sql_string NVARCHAR(1024)
, @time_member_key INT
, @calendar_date_dt SMALLDATETIME
, @calendar_day_of_week_num INT
, @calendar_day_of_week_name VARCHAR(10)
, @calendar_day_of_month_num INT
, @calendar_day_of_year_num INT
, @calendar_week_num INT
, @calendar_month_num INT
, @calendar_month_name VARCHAR(10)
, @calendar_quarter_num INT
, @calendar_year_num INT

SET @calendar_date_dt = @start_date_dt
WHILE (@calendar_date_dt <= @end_date_dt)
BEGIN
IF NOT EXISTS
(
SELECT time_member_key
FROM dim_time
WHERE calendar_date_dt = @calendar_date_dt
)
BEGIN
SELECT
@calendar_day_of_week_num = DATEPART(dw, @calendar_date_dt)
, @calendar_day_of_week_name = DATENAME(WEEKDAY, @calendar_date_dt)
, @calendar_day_of_month_num = DATEPART(DD, @calendar_date_dt)
, @calendar_day_of_year_num = DATEPART(DY, @calendar_date_dt)
, @calendar_week_num = DATEPART(WK, @calendar_date_dt)
, @calendar_month_num = DATEPART(M, @calendar_date_dt)
, @calendar_month_name = DATENAME(MONTH, @calendar_date_dt)
, @calendar_quarter_num = DATEPART(QQ, @calendar_date_dt)
, @calendar_year_num = DATEPART(YYYY, @calendar_date_dt)
, @time_member_key =
CAST(
CAST(@calendar_year_num AS VARCHAR) +
RIGHT(00 + CAST(@calendar_day_of_year_num AS VARCHAR), 3)
AS INT)
SELECT @sql_string =
’INSERT INTO ’ + @dim_table_name +
’ (’ +
’time_member_key, ’ +
’calendar_date_dt, ’ +
’calendar_day_of_week_num,’ +
’calendar_day_of_week_name,’ +
’calendar_day_of_month_num,’ +
’calendar_day_of_year_num,’ +
’calendar_week_num,’ +
’calendar_month_num,’ +
’calendar_month_name,’ +
’calendar_quarter_num, ’ +
’calendar_year_num’ +
’) ’ +
’VALUES ’ +
’(’ +
CHAR(39) + CAST(@time_member_key AS VARCHAR) + CHAR(39) + ’,’ +
CHAR(39) + CAST(@calendar_date_dt AS VARCHAR) + CHAR(39) + ’,’ +
CAST(@calendar_day_of_week_num AS VARCHAR) + ’,’ +
CHAR(39) + @calendar_day_of_week_name + CHAR(39) + ’,’ +
CAST(@calendar_day_of_month_num AS VARCHAR) + ’,’ +
CAST(@calendar_day_of_year_num AS VARCHAR) + ’,’ +
CAST(@calendar_week_num AS VARCHAR) + ’,’ +
CAST(@calendar_month_num AS VARCHAR) + ’,’ +
CHAR(39) + @calendar_month_name + CHAR(39) + ’,’ +
CAST(@calendar_quarter_num AS VARCHAR) + ’,’ +
CAST(@calendar_year_num AS VARCHAR) + ’)’

EXEC sp_executesql @sql_string
END

SET @calendar_date_dt = @calendar_date_dt + 1
END
[i]

error message

[i]
Server: Msg 170, Level 15, State 1, Procedure load_dim_time, Line 48
Line 48: Incorrect syntax near '’'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 09:16:58

try this and see if it works


CREATE PROCEDURE load_dim_time (
@dim_table_name VARCHAR(255), 
@start_date_dt SMALLDATETIME, 
@end_date_dt SMALLDATETIME
)
AS
SET NOCOUNT ON 
DECLARE
@sql_string NVARCHAR(1024)
, @time_member_key INT
, @calendar_date_dt SMALLDATETIME
, @calendar_day_of_week_num INT
, @calendar_day_of_week_name VARCHAR(10)
, @calendar_day_of_month_num INT
, @calendar_day_of_year_num INT
, @calendar_week_num INT
, @calendar_month_num INT
, @calendar_month_name VARCHAR(10)
, @calendar_quarter_num INT
, @calendar_year_num INT

SET @calendar_date_dt = @start_date_dt
WHILE (@calendar_date_dt <= @end_date_dt) 
BEGIN
IF NOT EXISTS
(
SELECT time_member_key
FROM dim_time
WHERE calendar_date_dt = @calendar_date_dt
)
BEGIN
set @calendar_day_of_week_num = DATEPART(dw, @calendar_date_dt)
set @calendar_day_of_week_name = DATENAME(WEEKDAY, @calendar_date_dt)
set @calendar_day_of_month_num = DATEPART(DD, @calendar_date_dt)
set @calendar_day_of_year_num = DATEPART(DY, @calendar_date_dt)
set @calendar_week_num = DATEPART(WK, @calendar_date_dt)
set @calendar_month_num = DATEPART(M, @calendar_date_dt)
set @calendar_month_name = DATENAME(MONTH, @calendar_date_dt)
set @calendar_quarter_num = DATEPART(QQ, @calendar_date_dt)
set  @calendar_year_num = DATEPART(YYYY, @calendar_date_dt)
set @time_member_key =
	CAST(
	CAST(@calendar_year_num AS VARCHAR) +
	RIGHT('00' + CAST(@calendar_day_of_year_num AS VARCHAR), 3)
	AS INT)

SELECT @sql_string =
	'INSERT INTO ' + @dim_table_name +
	 '(time_member_key, calendar_date_dt, 
	calendar_day_of_week_num,
	calendar_day_of_week_name,
	calendar_day_of_month_num,
	calendar_day_of_year_num,
	calendar_week_num,
	calendar_month_num,
	calendar_month_name,
	calendar_quarter_num, 
	calendar_year_num
	) VALUES (' +
	CHAR(39) + CAST(@time_member_key AS VARCHAR) + CHAR(39) + ',' +
	CHAR(39) + CAST(@calendar_date_dt AS VARCHAR) + CHAR(39) + ',' +
	CAST(@calendar_day_of_week_num AS VARCHAR) + ',' +
	CHAR(39) + @calendar_day_of_week_name + CHAR(39) + ',' +
	CAST(@calendar_day_of_month_num AS VARCHAR) + ',' +
	CAST(@calendar_day_of_year_num AS VARCHAR) + ',' +
	CAST(@calendar_week_num AS VARCHAR) + ',' +
	CAST(@calendar_month_num AS VARCHAR) + ',' +
	CHAR(39) + @calendar_month_name + CHAR(39) + ',' +
	CAST(@calendar_quarter_num AS VARCHAR) + ',' +
	CAST(@calendar_year_num AS VARCHAR) + ')'

EXEC (@sql_string)
END
SET @calendar_date_dt = @calendar_date_dt + 1
end

also can you explain why you're passing table name as parameter. why does object change dynamically?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-16 : 09:19:25

replace those tilting quotes with normal ones. Like it is in posts above.

mandarpowale
Starting Member

3 Posts

Posted - 2009-01-16 : 09:31:30

The next line
EXEC load_dim_time dim_time, ’1/1/96’, ’1/1/99’

Indicates that the data in dim_time is to be changed over a time interval

When i try your query it runs but nothing gets loaded into the dim_time table as the section shown below is not executed
[]i
SELECT @sql_string =
'INSERT INTO ' + @dim_table_name +
'(time_member_key, calendar_date_dt,
calendar_day_of_week_num,
calendar_day_of_week_name,
calendar_day_of_month_num,
calendar_day_of_year_num,
calendar_week_num,
calendar_month_num,
calendar_month_name,
calendar_quarter_num,
calendar_year_num
) VALUES (' +
CHAR(39) + CAST(@time_member_key AS VARCHAR) + CHAR(39) + ',' +
CHAR(39) + CAST(@calendar_date_dt AS VARCHAR) + CHAR(39) + ',' +
CAST(@calendar_day_of_week_num AS VARCHAR) + ',' +
CHAR(39) + @calendar_day_of_week_name + CHAR(39) + ',' +
CAST(@calendar_day_of_month_num AS VARCHAR) + ',' +
CAST(@calendar_day_of_year_num AS VARCHAR) + ',' +
CAST(@calendar_week_num AS VARCHAR) + ',' +
CAST(@calendar_month_num AS VARCHAR) + ',' +
CHAR(39) + @calendar_month_name + CHAR(39) + ',' +
CAST(@calendar_quarter_num AS VARCHAR) + ',' +
CAST(@calendar_year_num AS VARCHAR) + ')' [/i]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 11:00:57

why? it should? did you see EXEC at end of that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources