Strange error - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Strange error

Author

Topic

bjornh
Yak Posting Veteran

87 Posts

Posted - 2002-10-23 : 05:33:51

Hi there,

I have created this query:

declare @siteID int
declare @paginaID int
declare @v_datum datetime
declare @t_datum datetime
declare @interval decimal(9,1)
declare @SQl varchar(1000)

set @siteID = 258679
set @paginaID = 2
set @v_datum = '1-1-2002'
set @t_datum = '12-12-2002'
set @interval = 10

select @SQL = 'select
naam = (SELECT naam from pagina_' + convert(varchar(20),@siteID) + ' WHERE id = ' + convert(varchar(20),@paginaID) + '),
alias = (SELECT alias from pagina_' + convert(varchar(20),@siteID) + ' WHERE id = ' + convert(varchar(20),@paginaID) + '),
total_b = (select sum(bezoekers) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + '),
total_p = (select sum(pageviews) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + '),
gemiddeld = (SELECT sum(bezoekers)/' + convert(varchar(20),@interval) + ' from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + '),
dagTopD = (SELECT datum from log2_' + convert(varchar(20),@siteID) + ' WHERE bezoekers in (SELECT max(Bezoekers) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + ')),
DagTopB = (SELECT max(bezoekers) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + ')'

exec(@SQL)

This one works fine, but when I try to add another line, he give an error.

This one doesn't work:

declare @siteID int
declare @paginaID int
declare @v_datum datetime
declare @t_datum datetime
declare @interval decimal(9,1)
declare @SQl varchar(1000)

set @siteID = 258679
set @paginaID = 2
set @v_datum = '1-1-2002'
set @t_datum = '12-12-2002'
set @interval = 10

select @SQL = 'select
naam = (SELECT naam from pagina_' + convert(varchar(20),@siteID) + ' WHERE id = ' + convert(varchar(20),@paginaID) + '),
alias = (SELECT alias from pagina_' + convert(varchar(20),@siteID) + ' WHERE id = ' + convert(varchar(20),@paginaID) + '),
total_b = (select sum(bezoekers) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + '),
total_p = (select sum(pageviews) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + '),
gemiddeld = (SELECT sum(bezoekers)/' + convert(varchar(20),@interval) + ' from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + '),
dagTopD = (SELECT datum from log2_' + convert(varchar(20),@siteID) + ' WHERE bezoekers in (SELECT max(Bezoekers) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + ')),
dagTopD2 = (SELECT datum from log2_' + convert(varchar(20),@siteID) + ' WHERE bezoekers in (SELECT max(Bezoekers) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + ')),
DagTopB = (SELECT max(bezoekers) from log2_' + convert(varchar(20),@siteID) + ' where datum between ''' + convert(varchar(20),@V_datum) + ''' AND ''' + convert(varchar(20),@t_datum) + ''' AND paginaID = ' + convert(varchar(20),@paginaID) + ')'

exec(@SQL)

With the error message:

Server: Msg 105, Level 15, State 1, Line 9
Unclosed quotation mark before the character string 'J'.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'J'.

What is wrong with this approach of SQL?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-10-23 : 06:08:15

Do a select @sql instead of exec (@sql) and you'll see what the statement looks like. Maybe it's truncated because it gets longer than 1000 chars?? It's hard to say.

bjornh
Yak Posting Veteran

87 Posts

Posted - 2002-10-23 : 09:04:11

mmm it was the lenght of @SQL var...

*shame* *shame* *shame*

thanks anyway..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources