Dinamic subreports

Hi there,
I want to create a "big report" including a lot of subreports.. but I want
the user can select the reports to show in the "big report" from a list of
existing reports (generally graphs reports). Each user of the application
will have his "big report" configurated as he want, and the settings will be
stored on a table.
Any suggestions?
I have to assign to each subreport the reportname property, the parameters
(I previously have all Parameters in "big report" assigned from an aspx),
and the visibility property,right? but when and where do I write this code?
thank's a lot for any suggestion
Emanuele AndrettaAny suggestion' please...
"Emanuele" <emanuele75@.telefonica.net> ha scritto nel messaggio
news:OD5FI5MeFHA.1448@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I want to create a "big report" including a lot of subreports.. but I want
> the user can select the reports to show in the "big report" from a list of
> existing reports (generally graphs reports). Each user of the application
> will have his "big report" configurated as he want, and the settings will
be
> stored on a table.
> Any suggestions?
> I have to assign to each subreport the reportname property, the parameters
> (I previously have all Parameters in "big report" assigned from an aspx),
> and the visibility property,right? but when and where do I write this
code?
> thank's a lot for any suggestion
> Emanuele Andretta
>

Dinamic SQL questions

Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
all the time I recive the following error:
Syntax error converting datetime from character string. Here is my SQL:
declare @.sql nvarchar(1024)
declare @.Desde int,@.Hasta int
declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
htDetalleNormal.HojaId, ' +
'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS Hasta
' +
'FROM personal FULL OUTER JOIN ' +
'Clasificacion ON personal.ClasificacionId =
Clasificacion.ClasificacionId FULL OUTER JOIN ' +
'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
OUTER JOIN ' +
'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId '
+
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desd e) >=
CONVERT(DATETIME,@.FDesde, 102)) ' +
'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hast a) <=
CONVERT(DATETIME,@.FHasta, 102)) for browse'
set @.Desde = 14
set @.Hasta = 14
set @.FDesde = '2004-08-01'
set @.FHasta = '2004-08-01'
exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
nchar',
@.Desde,@.Hasta,@.FDesde,@.FHasta
Well Any advisor is Wellcome
Thank
Mario
Use a language neutral datetime format in your CONVERTs and your assignments to @.FDesde and @.FHasta. See below
for info:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:%23C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId =
> Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId '
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desd e) >=
> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hast a) <=
> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>
|||Mario,
I'm a very beginner in SQL and probably I give you a wrong advice (sorry if
so), but I think you have to exclude your parameters from strings, so
instead of
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desd e) >=
CONVERT(DATETIME,@.FDesde, 102)) '
I would write:
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),' + @.Desde+N') >=
CONVERT(DATETIME,'+@.FDesde+N', 102)) '
I have a problem with a dynamic SQL too, I cannot write correct where clause
with using BETWEEN for dates. My syntax doesn't produce any error, it just
doesn't return anything.
I have this:
SET @.sql = N'SELECT dbo.[Partial].PartialNumber,
dbo.CommissionPayments.CheckDate, dbo.CommissionPayments.CheckNumber,
dbo.CommissionPayments.CommissionAmountPaid,
dbo.Salesman.Salesman, dbo.CommissionPayments.CheckAmount
FROM dbo.[Partial] RIGHT OUTER JOIN
dbo.Salesman RIGHT OUTER JOIN
dbo.CommissionPayments ON dbo.Salesman.SalesmanID =
dbo.CommissionPayments.SalesmanID ON
dbo.[Partial].PartialID =
dbo.CommissionPayments.PartialID
WHERE (dbo.CommissionPayments.RowDeleted <> 1)'
SET @.sql = @.sql + N' AND (dbo.CommissionPayments.CheckDate BETWEEN
CONVERT(DATETIME, '+@.DateMin+N') AND
CONVERT(DATETIME, '+@.DateMax+N'))'
Maybe you can see something wrong with my syntax?
Vlad
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId =
> Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desd e) >=
> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hast a) <=
> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>
|||Mario, try this. We have to figure out the correct column names at an
earlier stage.
declare @.DesdeColName sysname
declare @.HastaColName sysname
-- Figure out the correct column names first
set @.DesdeColName = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Desde)
set @.HastaColName = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Hasta)
-- Set the where-clause
set @.sql = @.sql +
'WHERE ' + @.DesdeColName + ' >= CONVERT(DATETIME,@.FDesde, 102)) ' +
'AND ' + @.HastaColName + ' <= CONVERT(DATETIME,@.FHasta, 102))'
-- Display the full sql statement so we can run it in Query Analyzer
print @.sql
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId =
> Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desd e) >=
> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hast a) <=
> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>
|||Well I thing that problem is when the SQL SERVER or ADO Library Parsen my
expression:
COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Desde) + ' >= ' +
CONVERT(DATETIME,@.FDesde)
Note: @.Desde,@.FHasta both are variables int and dbo.HtDetalleNormal is a the
table's name.
The relational algebra not is complaining with the rules. Because when the
COL_NAME(OBJECT_ID('dbo.HtDetalleNormal') Constructs return a sysname object
and then sysname equal to nvarchar(128).
And I don't know which might be another form or way for construct my Dynamic
SQL.
May be somebody know. Any idea is welcome.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId =
> Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desd e) >=
> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hast a) <=
> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>
sql

Dinamic SQL questions

Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
all the time I recive the following error:
Syntax error converting datetime from character string. Here is my SQL:
declare @.sql nvarchar(1024)
declare @.Desde int,@.Hasta int
declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
htDetalleNormal.HojaId, ' +
'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS Hasta
' +
'FROM personal FULL OUTER JOIN ' +
'Clasificacion ON personal.ClasificacionId = Clasificacion.ClasificacionId FULL OUTER JOIN ' +
'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
OUTER JOIN ' +
'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId '
+
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >= CONVERT(DATETIME,@.FDesde, 102)) ' +
'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <= CONVERT(DATETIME,@.FHasta, 102)) for browse'
set @.Desde = 14
set @.Hasta = 14
set @.FDesde = '2004-08-01'
set @.FHasta = '2004-08-01'
exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
nchar',
@.Desde,@.Hasta,@.FDesde,@.FHasta
Well Any advisor is Wellcome
Thank
MarioUse a language neutral datetime format in your CONVERTs and your assignments to @.FDesde and @.FHasta. See below
for info:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:%23C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId => Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId '
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Mario,
I'm a very beginner in SQL and probably I give you a wrong advice (sorry if
so), but I think you have to exclude your parameters from strings, so
instead of
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=CONVERT(DATETIME,@.FDesde, 102)) '
I would write:
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),' + @.Desde+N') >=CONVERT(DATETIME,'+@.FDesde+N', 102)) '
I have a problem with a dynamic SQL too, I cannot write correct where clause
with using BETWEEN for dates. My syntax doesn't produce any error, it just
doesn't return anything.
I have this:
SET @.sql = N'SELECT dbo.[Partial].PartialNumber,
dbo.CommissionPayments.CheckDate, dbo.CommissionPayments.CheckNumber,
dbo.CommissionPayments.CommissionAmountPaid,
dbo.Salesman.Salesman, dbo.CommissionPayments.CheckAmount
FROM dbo.[Partial] RIGHT OUTER JOIN
dbo.Salesman RIGHT OUTER JOIN
dbo.CommissionPayments ON dbo.Salesman.SalesmanID =dbo.CommissionPayments.SalesmanID ON
dbo.[Partial].PartialID =dbo.CommissionPayments.PartialID
WHERE (dbo.CommissionPayments.RowDeleted <> 1)'
SET @.sql = @.sql + N' AND (dbo.CommissionPayments.CheckDate BETWEEN
CONVERT(DATETIME, '+@.DateMin+N') AND
CONVERT(DATETIME, '+@.DateMax+N'))'
Maybe you can see something wrong with my syntax?
Vlad
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId => Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Mario, try this. We have to figure out the correct column names at an
earlier stage.
declare @.DesdeColName sysname
declare @.HastaColName sysname
-- Figure out the correct column names first
set @.DesdeColName = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Desde)
set @.HastaColName = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Hasta)
-- Set the where-clause
set @.sql = @.sql +
'WHERE ' + @.DesdeColName + ' >= CONVERT(DATETIME,@.FDesde, 102)) ' +
'AND ' + @.HastaColName + ' <= CONVERT(DATETIME,@.FHasta, 102))'
-- Display the full sql statement so we can run it in Query Analyzer
print @.sql
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId => Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Well I thing that problem is when the SQL SERVER or ADO Library Parsen my
expression:
COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Desde) + ' >= ' +
CONVERT(DATETIME,@.FDesde)
Note: @.Desde,@.FHasta both are variables int and dbo.HtDetalleNormal is a the
table's name.
The relational algebra not is complaining with the rules. Because when the
COL_NAME(OBJECT_ID('dbo.HtDetalleNormal') Constructs return a sysname object
and then sysname equal to nvarchar(128).
And I don't know which might be another form or way for construct my Dynamic
SQL.
May be somebody know. Any idea is welcome.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId => Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>

Dinamic SQL questions

Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
all the time I recive the following error:
Syntax error converting datetime from character string. Here is my SQL:
declare @.sql nvarchar(1024)
declare @.Desde int,@.Hasta int
declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
htDetalleNormal.HojaId, ' +
'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS Hasta
' +
'FROM personal FULL OUTER JOIN ' +
'Clasificacion ON personal.ClasificacionId =
Clasificacion.ClasificacionId FULL OUTER JOIN ' +
'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
OUTER JOIN ' +
'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId '
+
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=
CONVERT(DATETIME,@.FDesde, 102)) ' +
'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=
CONVERT(DATETIME,@.FHasta, 102)) for browse'
set @.Desde = 14
set @.Hasta = 14
set @.FDesde = '2004-08-01'
set @.FHasta = '2004-08-01'
exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
nchar',
@.Desde,@.Hasta,@.FDesde,@.FHasta
Well Any advisor is Wellcome
Thank
MarioUse a language neutral datetime format in your CONVERTs and your assignments
to @.FDesde and @.FHasta. See below
for info:
http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:%23C8SJhQMEHA.1032@.tk2msftngp13.phx
.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS Hast
a
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId =
> Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=
> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=
> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Mario,
I'm a very beginner in SQL and probably I give you a wrong advice (sorry if
so), but I think you have to exclude your parameters from strings, so
instead of
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=
CONVERT(DATETIME,@.FDesde, 102)) '
I would write:
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),' + @.Desde+N') >=
CONVERT(DATETIME,'+@.FDesde+N', 102)) '
I have a problem with a dynamic SQL too, I cannot write correct where clause
with using BETWEEN for dates. My syntax doesn't produce any error, it just
doesn't return anything.
I have this:
SET @.sql = N'SELECT dbo.[Partial].PartialNumber,
dbo.CommissionPayments.CheckDate, dbo.CommissionPayments.CheckNumber,
dbo.CommissionPayments.CommissionAmountPaid,
dbo.Salesman.Salesman, dbo.CommissionPayments.CheckAmount
FROM dbo.[Partial] RIGHT OUTER JOIN
dbo.Salesman RIGHT OUTER JOIN
dbo.CommissionPayments ON dbo.Salesman.SalesmanID =
dbo.CommissionPayments.SalesmanID ON
dbo.[Partial].PartialID =
dbo.CommissionPayments.PartialID
WHERE (dbo.CommissionPayments.RowDeleted <> 1)'
SET @.sql = @.sql + N' AND (dbo.CommissionPayments.CheckDate BETWEEN
CONVERT(DATETIME, '+@.DateMin+N') AND
CONVERT(DATETIME, '+@.DateMax+N'))'
Maybe you can see something wrong with my syntax?
Vlad
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId =
> Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=
> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=
> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Mario, try this. We have to figure out the correct column names at an
earlier stage.
declare @.DesdeColName sysname
declare @.HastaColName sysname
-- Figure out the correct column names first
set @.DesdeColName = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Desde)
set @.HastaColName = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Hasta)
-- Set the where-clause
set @.sql = @.sql +
'WHERE ' + @.DesdeColName + ' >= CONVERT(DATETIME,@.FDesde, 102)) ' +
'AND ' + @.HastaColName + ' <= CONVERT(DATETIME,@.FHasta, 102))'
-- Display the full sql statement so we can run it in Query Analyzer
print @.sql
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId =
> Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=
> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=
> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Well I thing that problem is when the SQL SERVER or ADO Library Parsen my
expression:
COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Desde) + ' >= ' +
CONVERT(DATETIME,@.FDesde)
Note: @.Desde,@.FHasta both are variables int and dbo.HtDetalleNormal is a the
table's name.
The relational algebra not is complaining with the rules. Because when the
COL_NAME(OBJECT_ID('dbo.HtDetalleNormal') Constructs return a sysname object
and then sysname equal to nvarchar(128).
And I don't know which might be another form or way for construct my Dynamic
SQL.
May be somebody know. Any idea is welcome.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId =
> Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=
> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=
> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>