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
>

Dimensional Arrays in MSSQL 2000

I am converting a database from Clarion to MSSQL 2000. In the Clarion files we use dimensional arrays. I've seen dimensional arrays mentioned on the forum, but can't find how to set one up in MSSQL 2000. Can anyone help me find some kind of documentation on this, or point me in the right direction?

MartyRefer to this Clarion link (http://www.clarionmag.com/cmag/v1/v1n4convertingtosql.html) for information and also refer to books online for 'sp_OAMethod' and other referenced topics.

HTH|||honea, not sure I know what dimesional arrays are with regards to Calrion. As best as I remember Clarion used flat files. If you can provide an explination I will be glad to help if I can.|||Satya,

Thank you for the response. I did look up the 'sp_OAMethod', and while it talks about referencing the arrays, I have found no place that talks about actually setting the array up. How do I put an array in the table?

Marty|||Paul,

In Clarion you would declare a field type and then dimension it. Example:

mydimlong Long Dim(2,36)

This would give you a long data type that has 72 possible places to store data. You would then retrieve or put data in by referencing it like this...

mydimlong[1,1] = 'Male'
mydimlong[2,1] = 'Female'
mydimlong[1,2] = somedata
mydimlong[2,2] = somemoredata

ETC...

Marty|||To my klnowledge there is nothing comperable in MSSQL Server. I would probably set this up as a child table and move on.|||Thank you Paul,

I will probably keep the table in clarion for now.

Marty