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
>

Difficult Query, with dynamically updated data between rows....

Hi Everybody,
I'm looking for some help putting together a stored procedure to do a
report for my shipping department. What they are looking for is a
report listing a SalesOrder (SO), some of its pertinent information,
and then a list of the inventory we have for that part. Here's the
catch. If the same part is to ship on two seperate SO's, the inventory
must change between them. For example:
SONum Part QTYReq QtyAvail LotNum
1 A 100 120 100k5
2 B 500 800 120j4
3 B 200 300 120j4
4 C 55 50 121b4
You'll notice that for part 'B', we had 800 in stock for the first so,
took off 500, leaving us with 300 to report for the second.
The problem is that I don't really know how to handle this. I don't
even know what to title this post!
So far, I have created temp tables to capture a snapshot of my
inventory (so that I can modify it without hurting the real data) and
to hold the list of SO's data.
My basic plan is to create a cursor on the SO Data, updating the
available inventory for that SO item, then updating the inventory
snapshot, so that on subsequent checks, the new, lower values are ready
to be used.
Is there a better way to do this? Will my plan be likely to succeed?
Thanks for any input you can offer! It is honestly appreciated.
Brian.you could use something like this (untested):
select
SONum, Part, QTYReq,
(QtyAvail - (select sum(t1.QTYReq) from your_table t1
where t1.part=t.part and t1.sonum<t.sonum)) QtyAvail ,
LotNum
from your_table t|||Hrm, I see how that goes...
Lets throw another wrench into the mix.
Add multiple lot numbers for a part so the output looks now something
like:
SONum Part QTYReq QtyAvail LotN
1 A 100 120 100k
2 B 500 360 120j
2 B 500 280 121j
2 B 500 198 122j
3 B 200 0 120j
3 B 200 140 121j
3 B 200 198 122j
4 C 55 50 21b4
How would you work that out? I'll keep trying on my own to see if I
can get it, but if you can suggest, I'd be most grateful.
Thanks,
Brian.|||using row_number(), it's a snap.
without OLAP functions, still doable, something like this:
drop table #lots
go
drop table #requests
go
create table #lots(LotN char(4), part char(1), qty smallint)
insert into #lots values('120j', 'B', 360)
insert into #lots values('121j', 'B', 280)
insert into #lots values('122j', 'B', 122)
create table #requests(SOnum smallint, part char(1), qty smallint)
insert into #requests values(2, 'B', 500)
insert into #requests values(3, 'B', 200)
go
select sonum, part, QTYReq,
case
when prev_reqs<prev_lots then QtyAvail
when prev_reqs between prev_lots and (prev_lots + QtyAvail) then
QtyAvail - prev_reqs + prev_lots
else 0 end QtyAvail,
lotN
from(
select r.sonum, r.part, r.qty QTYReq, l.qty QtyAvail, l.lotN,
isnull((select sum(qty) from #lots l1 where l1.part = l.part and
l1.lotN < l.lotN), 0) prev_lots,
isnull((select sum(qty) from #requests r1 where r1.part = r.part and
r1.SOnum < r.SOnum), 0) prev_reqs
from #lots l, #requests r where r.part = l.part
) t
order by sonum, lotN
note that you need more test data|||Okay! That looks promising...
I'll give that a try tomorrow morning.
Thanks for the response. It was more detailed than I had hoped for,
considering my lack of proper ddl and such.
Thanks again,
Brian|||Alexander (or anybody else),
I've taken your suggestion, and applied it to my situation. It very
nearly does the trick, but something about it just isn't right...I'll
pase the code following, but I'll talk about it up here...
With this particular dataset (ddl included this time :) ), what I have
is 3 seperate sales orders for a part, and 2 lots in inventory. Each
SO is for 400 parts, and the lots are 500 each.
For the first SO in the list it displays correctly. We have 2 lots of
500 to take the 400 out of...
But for the second SO, we should see that we have one lot of 100, and
another of 500, instead of both listing at 100.
The third SO, of course, should list one lot as empty, and the other as
having 200, instead of both listing at 0.
At this point, are we out of options as far as set operations go? I'm
still pondering the Cursor in the back of my mind, but I'd rather avoid
that if its possible.
Thanks again for aiding me. It is appreciated.
==== CODE BLOCK ====
drop table #lots
go
drop table #requests
go
create table #lots
(
customerID varchar(55)
, LotNumber char(44)
, PartNumber char(11)
, Quantity float
, lotPKey int
, departmentCode varchar(22)
, qtyReleased float
, qtyHold float
)
create table #requests
(
customerID varchar(55)
, shipDate datetime
, sortDate datetime
, SONumber varchar(22)
, SOLine varchar(22)
, customerPO varchar(22)
, partNumber varchar(11)
, partXRef varchar(22)
, descText varchar(99)
, qtyOpen float
, sortKey int IDENTITY(1,1)
)
-- -- generate the request data...
-- INSERT INTO #requests (
-- customerID
-- , shipDate
-- , SONumber
-- , SOLine
-- , customerPO
-- , partNumber
-- , partXRef
-- , descText
-- , qtyOpen
-- )
-- SELECT
-- UPPER(SOH.CustomerID)
-- , SOD.ScheduledShipDate
-- , SOH.SONumber
-- , SOD.SOLine
-- , SOH.CustomerPO
-- , SOD.PartNumber
-- , SOD.PartXReference
-- , PM.DescText
-- , SOD.QuantityOrdered - (SOD.QuantityShipped + SOD.QuantityReturned)
as QtyOpen
-- FROM SOHeader SOH
-- INNER JOIN SODetail SOD ON SOH.SONumber = SOD.SONumber
-- INNER JOIN PartMaster PM ON SOD.PartNumber = PM.PartNumber
-- WHERE SOD.ScheduledShipDate >= '1/1/2000' AND SOD.ScheduledShipDate
<= '11/13/2005'
-- AND SOD.PartNumber LIKE '600879%' --600879
-- AND SOH.ClosedFlag <> 1
-- AND SOD.ClosedFlag <> 1
-- ORDER BY
-- UPPER(SOH.CustomerID)
-- , SOD.PartNumber
-- , SOD.ScheduledShipDate
-- , SOH.SONumber
-- , SOD.SOLine
--
INSERT INTO #requests
(customerID , shipDate, SONumber , SOLine, customerPO , partNumber ,
partXRef , descText , qtyOpen)
VALUES ('ACME', '11/01/2005', 'SO0510001', '001', 'xyz', '600111',
'z987', 'ACME Foot Creme', 400)
INSERT INTO #requests
(customerID , shipDate, SONumber , SOLine, customerPO , partNumber ,
partXRef , descText , qtyOpen)
VALUES ('ACME', '11/11/2005', 'SO0510001', '001', 'xyz', '600111',
'z987', 'ACME Foot Creme', 400)
INSERT INTO #requests
(customerID , shipDate, SONumber , SOLine, customerPO , partNumber ,
partXRef , descText , qtyOpen)
VALUES ('ACME', '11/21/2005', 'SO0510001', '001', 'xyz', '600111',
'z987', 'ACME Foot Creme', 400)
-- --generate the lots data
-- INSERT INTO #lots
-- (
-- LotNumber
-- , PartNumber
-- , Quantity
-- , lotPKey
-- , departmentCode
-- , customerID
-- )
-- SELECT
-- IL.SNLotNumber
-- , IL.PartNumber
-- , IL.Quantity
-- , IL.InventoryLots_PKey
-- , IL.DepartmentCode
-- , P.SUOCode
-- FROM InventoryLots IL
-- INNER JOIN PartMaster P ON IL.PartNumber = P.PartNumber
-- WHERE IL.PartNumber LIKE '6%'
INSERT INTO #lots
(LotNumber, PartNumber, Quantity, lotPKey, departmentCode, CustomerID)
VALUES ('123j4' , '600111', 500, 1, 'FGINR', 'ACME')
INSERT INTO #lots
(LotNumber, PartNumber, Quantity, lotPKey, departmentCode, CustomerID)
VALUES ('124j4' , '600111', 500, 1, 'FGINR', 'ACME')
UPDATE #lots
SET qtyReleased = Quantity
FROM #lots
WHERE DepartmentCode = 'FGINR'
AND CustomerID <> 'CP'
UPDATE #lots
SET qtyReleased = Quantity
FROM #lots
WHERE DepartmentCode = 'FGINP'
AND CustomerID = 'CP'
UPDATE #lots
SET qtyHold = Quantity
FROM #lots
WHERE DepartmentCode = 'HOLD FGIN'
AND CustomerID <> 'CP'
UPDATE #lots
SET qtyHold = Quantity
FROM #lots
WHERE DepartmentCode = 'HOLDN FGIN'
AND CustomerID = 'CP'
--set the sort date for all shipments...
UPDATE #requests
SET sortDate = Z.minshipdate
FROM #requests R
INNER JOIN
(
SELECT PartNumber, MIN(shipDate) as minshipdate FROM #requests
GROUP BY PartNumber
) Z ON R.PartNumber = Z.PartNumber
select
customerID
, shipDate
, sortDate
, SONumber
, SOLine
, customerPO
, partNumber
, partXRef
, descText
, qtyOpen
--, qtyReleased
, case
when prev_reqs < prev_lots then qtyReleased
when prev_reqs between prev_lots and (prev_lots + qtyReleased) then
qtyReleased - prev_reqs + prev_lots
else 0
end qtyReleased
, qtyHold
, lotNumber
from(
select
r.customerID
, r.shipDate
, r.sortDate
, r.SONumber
, r.SOLine
, r.customerPO
, r.partNumber
, r.partXRef
, r.descText
, ISNULL(r.qtyOpen , 0) qtyOpen
, ISNULL(l.qtyReleased , 0) qtyReleased
, ISNULL(l.qtyHold , 0) qtyHold
, l.lotNumber
, isnull(
(select sum(qtyReleased)
from #lots l1
where
l1.partNumber = l.partNumber and
l1.lotPKey < l.lotPKey
), 0) prev_lots
, isnull(
(select sum(qtyOpen)
from #requests r1
where r1.partNumber = r.partNumber
and r1.sortKey < r.sortKey
), 0) prev_reqs
from #lots l, #requests r
where r.partNumber = l.partNumber
) t
--order by sonum, lotN|||On 10 Nov 2005 08:08:50 -0800, Brian Ackermann wrote:

>Alexander (or anybody else),
>I've taken your suggestion, and applied it to my situation. It very
>nearly does the trick, but something about it just isn't right...I'll
>pase the code following, but I'll talk about it up here...
>With this particular dataset (ddl included this time :) ), what I have
>is 3 seperate sales orders for a part, and 2 lots in inventory. Each
>SO is for 400 parts, and the lots are 500 each.
>For the first SO in the list it displays correctly. We have 2 lots of
>500 to take the 400 out of...
>But for the second SO, we should see that we have one lot of 100, and
>another of 500, instead of both listing at 100.
>The third SO, of course, should list one lot as empty, and the other as
>having 200, instead of both listing at 0.
>At this point, are we out of options as far as set operations go? I'm
>still pondering the Cursor in the back of my mind, but I'd rather avoid
>that if its possible.
>Thanks again for aiding me. It is appreciated.
Hi Brian,
Thabks for posting DDL and sample data. However, I'm not sure if your
data is correct. You mention three sales orders and two lots in your
post, yet I see only one SONumber and one LotNumber in the output!
Anyway - this kind of problem CAN be tackled with a setbased operation,
but they often perform very bad. Because they require some correlated
subqueries and/or self-joins, the typical query plan often involves
multiple table scans. If you can find a cursor-based solution that only
needs to iterate over all rows once, it'll probably be faster than a
set-based version.
If you still want to try a set-based solution, I'll try to help you. But
not now - it's past midnight here; I'd just make errors. Please try to
explain me how your data holds three sales orders and two lot numbers,
even though I see only one of each. Or correct your data if you made a
mistake. I'll take a jab at a set-based solution later (after seeing an
explanation or a correction of your test data).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You might want to look at a pair of articles I have posted on
DBAzine.com on inventory control queries.sql

Difficult Combining Rows queston...followup

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in grouped chunks, like below
30 minute chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:29:59, 5, 32, 19
1, 2005-10-05 06:30, 2005-10-05 06:59:59, 5, 29, 20
1, 2005-10-05 07:00, 2005-10-05 07:29:59, 5, 25, 36
1, 2005-10-05 07:30, 2005-10-05 07:59:59, 5, 34, 98
1 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
2 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
Since originally posting my question, I have learned from John Bell
that I can use this to solve the 1 hour problem at hand.
SELECT GROUPID,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime) AS StartTime,
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime),
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
This makes sense for the hour instance... The hour instance also seems
to be the easiest one to solve. This query simply selects and groups
the start times to starttime-it's own minutes, end time to
starttime-it's own minutes + 1 hour. That works our very well for the
one hour case.
When I move to a two hour time window, we run into problems. Using that
exact query replacing "hour,1" for "hour, 2" will produce results that
look like this
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
1, 2005-10-05 07:00, 2005-10-05 08:59:59, 7, 32, 150
1, 2005-10-05 08:00, 2005-10-05 09:59:59, 6, 36, 172
This is where it gets confusing. The problem with that is that the
group by is still grouping in one hour chunks, because the 'adjusted'
start time for the second hour is not the same as the 'adjusted' start
time for the first hour. The start and end time might look ok, but the
rest of the data does not. Also, since this is a GROUP BY clause, a
working query should not produce overlapping results(in this case,
seemingly overlapping).
So for this 2 hour case (and upwards) I am looking for a solution to
get that start time query to go back
It's almost like i need to do something like IF statments in my
SELECT... not sure if that is possible or not.
Similar problems occur when you go to do half hour groupings. All four
15 minute chunks get floored and grouped together, even though you can
easily get the end time to report x:29:29.
What a mess, it seems like I am trying to do the impossible.
Any suggestions? Please ask me to clarify if necessary.
Jason
Got it. Well part of it. Here is the multiple hour verison. Yay for
integer division.
--SELECT in TWO HOUR INCREMENTS
SELECT groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))
AS Startime,
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
AS Endtime,
MIN([Min]) as MinSpeed,
MAX([Max]) as MaxSpeed,
SUM([Points]) as Total
FROM TABLE_NAME
WHERE groupID='1'
GROUP BY groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime)),
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
This will work for 1,2,3,4, if you replace all instances of 2 with
1,2,3,4, etc. which can be done in code very easily.
Now to figure out the 30 minute version.
This query is so repetetive, I wish I could use variable names instead
of rewriting the whole thing... the EndTime calculation uses the whole
start time calculation. and the GROUP BY clauses are copies of what is
above. Using AS does not seem to work in these cases. Oh well.
Jason
|||On 10 Nov 2005 12:35:53 -0800, jasonsgeiger@.gmail.com wrote:

>Greetings,
>I'm working to combine rows based on a time window and I am hoping to
>be able to write a stored procedure to do this for me, rather than have
>parse through all this data in my program. I'm not very well versed
>with T-SQL syntax.. just enough to get by selecting using inner joins,
>updating and inserting... thats about it. (Hence why I am here.)
(snip)
Hi Jason,
I just posted a reply to your message in the original thread (in
microsoft.public.sqlserver.programming).
Please don't post multiple copies of the same question. I'd hate to see
someone else spend time to figure this out, becuase he or she is not
aware that I have already answered the question.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Difficult Combining Rows queston...followup

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in grouped chunks, like below
30 minute chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:29:59, 5, 32, 19
1, 2005-10-05 06:30, 2005-10-05 06:59:59, 5, 29, 20
1, 2005-10-05 07:00, 2005-10-05 07:29:59, 5, 25, 36
1, 2005-10-05 07:30, 2005-10-05 07:59:59, 5, 34, 98
1 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
2 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
Since originally posting my question, I have learned from John Bell
that I can use this to solve the 1 hour problem at hand.
SELECT GROUPID,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime) AS StartTime,
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Startt
ime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime),
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Startt
ime),Starttime)))
This makes sense for the hour instance... The hour instance also seems
to be the easiest one to solve. This query simply selects and groups
the start times to starttime-it's own minutes, end time to
starttime-it's own minutes + 1 hour. That works our very well for the
one hour case.
When I move to a two hour time window, we run into problems. Using that
exact query replacing "hour,1" for "hour, 2" will produce results that
look like this
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
1, 2005-10-05 07:00, 2005-10-05 08:59:59, 7, 32, 150
1, 2005-10-05 08:00, 2005-10-05 09:59:59, 6, 36, 172
This is where it gets confusing. The problem with that is that the
group by is still grouping in one hour chunks, because the 'adjusted'
start time for the second hour is not the same as the 'adjusted' start
time for the first hour. The start and end time might look ok, but the
rest of the data does not. Also, since this is a GROUP BY clause, a
working query should not produce overlapping results(in this case,
seemingly overlapping).
So for this 2 hour case (and upwards) I am looking for a solution to
get that start time query to go back
It's almost like i need to do something like IF statments in my
SELECT... not sure if that is possible or not.
Similar problems occur when you go to do half hour groupings. All four
15 minute chunks get floored and grouped together, even though you can
easily get the end time to report x:29:29.
What a mess, it seems like I am trying to do the impossible.
Any suggestions? Please ask me to clarify if necessary.
JasonGot it. Well part of it. Here is the multiple hour verison. Yay for
integer division.
--SELECT in TWO HOUR INCREMENTS
SELECT groupID,
DATEADD(hour,- (DATEPART(hour,StartTime))+(DATEPART(hou
r,StartTime)/2)*2,DATE
ADD(minute,-DATEPART(minute,StartTime),StartTime))
AS Startime,
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime
))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime)
,StartTime))))
AS Endtime,
MIN([Min]) as MinSpeed,
MAX([Max]) as MaxSpeed,
SUM([Points]) as Total
FROM TABLE_NAME
WHERE groupID='1'
GROUP BY groupID,
DATEADD(hour,- (DATEPART(hour,StartTime))+(DATEPART(hou
r,StartTime)/2)*2,DATE
ADD(minute,-DATEPART(minute,StartTime),StartTime)),
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime
))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime)
,StartTime))))
This will work for 1,2,3,4, if you replace all instances of 2 with
1,2,3,4, etc. which can be done in code very easily.
Now to figure out the 30 minute version.
This query is so repetetive, I wish I could use variable names instead
of rewriting the whole thing... the EndTime calculation uses the whole
start time calculation. and the GROUP BY clauses are copies of what is
above. Using AS does not seem to work in these cases. Oh well.
Jason|||On 10 Nov 2005 12:35:53 -0800, jasonsgeiger@.gmail.com wrote:

>Greetings,
>I'm working to combine rows based on a time window and I am hoping to
>be able to write a stored procedure to do this for me, rather than have
>parse through all this data in my program. I'm not very well versed
>with T-SQL syntax.. just enough to get by selecting using inner joins,
>updating and inserting... thats about it. (Hence why I am here.)
(snip)
Hi Jason,
I just posted a reply to your message in the original thread (in
microsoft.public.sqlserver.programming).
Please don't post multiple copies of the same question. I'd hate to see
someone else spend time to figure this out, becuase he or she is not
aware that I have already answered the question.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Difficult Combining Rows queston...followup

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in grouped chunks, like below
30 minute chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:29:59, 5, 32, 19
1, 2005-10-05 06:30, 2005-10-05 06:59:59, 5, 29, 20
1, 2005-10-05 07:00, 2005-10-05 07:29:59, 5, 25, 36
1, 2005-10-05 07:30, 2005-10-05 07:59:59, 5, 34, 98
1 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
2 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
Since originally posting my question, I have learned from John Bell
that I can use this to solve the 1 hour problem at hand.
SELECT GROUPID,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime) AS StartTime,
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime),
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
This makes sense for the hour instance... The hour instance also seems
to be the easiest one to solve. This query simply selects and groups
the start times to starttime-it's own minutes, end time to
starttime-it's own minutes + 1 hour. That works our very well for the
one hour case.
When I move to a two hour time window, we run into problems. Using that
exact query replacing "hour,1" for "hour, 2" will produce results that
look like this
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
1, 2005-10-05 07:00, 2005-10-05 08:59:59, 7, 32, 150
1, 2005-10-05 08:00, 2005-10-05 09:59:59, 6, 36, 172
This is where it gets confusing. The problem with that is that the
group by is still grouping in one hour chunks, because the 'adjusted'
start time for the second hour is not the same as the 'adjusted' start
time for the first hour. The start and end time might look ok, but the
rest of the data does not. Also, since this is a GROUP BY clause, a
working query should not produce overlapping results(in this case,
seemingly overlapping).
So for this 2 hour case (and upwards) I am looking for a solution to
get that start time query to go back
It's almost like i need to do something like IF statments in my
SELECT... not sure if that is possible or not.
Similar problems occur when you go to do half hour groupings. All four
15 minute chunks get floored and grouped together, even though you can
easily get the end time to report x:29:29.
What a mess, it seems like I am trying to do the impossible.
Any suggestions? Please ask me to clarify if necessary.
JasonGot it. Well part of it. Here is the multiple hour verison. Yay for
integer division.
--SELECT in TWO HOUR INCREMENTS
SELECT groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))
AS Startime,
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
AS Endtime,
MIN([Min]) as MinSpeed,
MAX([Max]) as MaxSpeed,
SUM([Points]) as Total
FROM TABLE_NAME
WHERE groupID='1'
GROUP BY groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime)),
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTime)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
This will work for 1,2,3,4, if you replace all instances of 2 with
1,2,3,4, etc. which can be done in code very easily.
Now to figure out the 30 minute version.
This query is so repetetive, I wish I could use variable names instead
of rewriting the whole thing... the EndTime calculation uses the whole
start time calculation. and the GROUP BY clauses are copies of what is
above. Using AS does not seem to work in these cases. Oh well.
Jason|||On 10 Nov 2005 12:35:53 -0800, jasonsgeiger@.gmail.com wrote:
>Greetings,
>I'm working to combine rows based on a time window and I am hoping to
>be able to write a stored procedure to do this for me, rather than have
>parse through all this data in my program. I'm not very well versed
>with T-SQL syntax.. just enough to get by selecting using inner joins,
>updating and inserting... thats about it. (Hence why I am here.)
(snip)
Hi Jason,
I just posted a reply to your message in the original thread (in
microsoft.public.sqlserver.programming).
Please don't post multiple copies of the same question. I'd hate to see
someone else spend time to figure this out, becuase he or she is not
aware that I have already answered the question.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

differnce between stored Procedure and stored Functions?

Hi all,
What is the Difference between Stored Procedure and Inline Quries..... Is Stored Procedure is faster then Inline Query...
What is the professional Approch...
I m using Inline Quries in my Professional Project with Microsoft Application Block.... Someone Said me this is not a professional approch... use stored procedure instead of Inline Quries... i m so puzzled...
plz guide me.... :)
Thanx
Sajjadwhat is the differnce between stored Procedure and stored Functions ????|||please check first in any search engine.|||Functions are easier to fold into SELECT statements, as they return scalar or table values.
Stored procedures have fewer programming limitations than user-defined functions, but must be run independently and not as part of a select statement.sql

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.
Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database is identical, to the
point that one is a backup or attach of the other? If not, things like statistics can cause
different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegr oups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>
|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database
is identical, to the
point that one is a backup or attach of the other? If not, things like stati
stics can cause
different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegroups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database is identical, to the
point that one is a backup or attach of the other? If not, things like statistics can cause
different execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegroups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Different UPDATE behaviors across servers

Hi!

I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:

Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.

Stored procedure:

BEGIN TRANSACTION

--step 1
TRUNCATE myTable

--step 2
INSERT INTO myTable VALUES ('myValues')

--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns

COMMIT TRANSACTION

The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:

select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <0
order by waittime desc

select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <0)

select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <0)
order by waittime desc

When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).

What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?

I am at a loss! Any help would be greatly appreciated.Dmitri (nienna.gaia@.gmail.com) writes:

Quote:

Originally Posted by

I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
>
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.

Apparently the query plans are different. This could be because there
are differences in statistics between the databases. Fragmentation
could also matter. I would recommend that you run DBCC DBREINDEX on
the tables in both environments. If you are lucky, the query runs
quickly in both databases. If you are less lucky, the query will now
run slowly in both databases.

If the machines has a different number of processors, this could also
matter. Maybe one machine is a single-CPU machine, whereas the other
is an 8-way box, so there is a parallel plan on server and a non-parallel
plan on the other. Parallel plans are sometimes really amazing -
either amazingly fast or amazingly slow.

Quote:

Originally Posted by

--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns

Not that it matters for the discussion since I don't see the table
definition and the indexes, but this syntax is not legal.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

different searches for web matrix and sql server

Hi Everyone. I have created a procedure on sql to obtain information about two tables ( bookinfo and author). The procedure is :

CREATE PROCEDURE FindBookByAuthor
@.Aname char
AS

select b.libcode, b.title, b.subject, a.authorname, b.instock
from bookinfo b join authorinfo a
on b.authorid = a.authorid
where a.authorname like '%' + @.aname + '%'
GO

I have tested this procedure changing the variable @.aname for the surname of the author. This works fine. The problem comes when I execute this procedure from web matrix. I use a textbox to obtain the data for the variable @.aname. I send the data. What happens is that when I run the web page and look for a book through the author the select statement behaves differently and gives me a different output.

I would be glad to know why this happens

ThanksShow some code. Failing that, verify in SQL Profiler that the SQL/Parameters you think you are executing is in fact what is being executed.|||well, the asp.net code is :
--------------------

Sub Button2_Click(sender As Object, e As EventArgs)
Dim objConn As SqlConnection
 Dim dataReader As SqlDataReader
 dim constring as string
 Dim objCmd As SqlCommand
 constring = "server='(local)'; trusted_connection=true; database='dbjaime'"
 objConn = New sqlconnection(constring)
 dim strsql as string
 strsql = "EXECUTE findtitle '" & textboxtitle.text & "'"
 objCmd = New SqlCommand(strSQL, objConn)
 objConn.Open()
 dataReader = objCmd.ExecuteReader()
 'Bind to DataGrid
 dtgbooks.DataSource = dataReader
 dtgbooks.DataBind()
 objConn.Close()
 objConn.Dispose()
End Sub
--------------------

Where "findtitle" is a procedure I showed you before.|||Use SQL Profiler and see if what you are sending is what you think you are in both cases (where it works and where it does not).|||I have tried to use the sql profiler, which I had never used before. It shows me the last executions. Then I have loaded my web page and execute the event "button click" I send you before. Now what I see on the sql profile is:

exec sp_executesql N'EXECUTE findtitle ''design'''

among other stuff. I this what I should get?|||The findtitle line is what I was having you look for.

Execute the code in the way that it works, as well as the way that it does not work, and compare what appears in SQL Profiler. You are saying running the code with Web Matrix produces different results. I am looking to have you isolate exactly why the results are different.|||ok. I'll take a look. Thanks for being so quick.|||I'm not sure if I'm passing the parameters correctly to the procedure findtitle on the line:

strsql = "EXECUTE findtitle ' " & txt1.text & " ' "

I say that they give me different results because when I copy the select statement from the procedure into the SQL analyzer I subtitute the variabe @.btitle for a word such as 'database'. And then, when I execute the web page I insert in the textbox the word database.

am I doing something wrong?

Thanks

Different results when executing from .NET component compare to executing from SQL Managem

Hi all,

I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.
This started to really annoying me, any thoughts or solution?
Thanks very much guys

I'm interested in some more details: what's the meaning of different results? Returning different data (rows)? Or same data (rows) in different orders? Can you post the stored proceudre if it does not contain too many statements?

Since recomplie the stored procedure can solve the issue, it seems SQL optimizer chooses different execution plans, thus may lead to different results. One work around is to call the stored procedure with recomplie:

EXECUTEyourSPName WITH RECOMPILE

However this is not a so good solution, as RECOMPLIE at each execution will impact SQL performance.

|||

The result that come back seems to be from the old version of the stored procedure, or could be it's joining differently.
I am suspecting that .NET SQL provider has a seperate query plan cache. The actual select statement is like this, I removed few lines in the where clause and column selections.
SELECT
main.*,fa.*
FROM [NetFare] main
INNER JOIN dbo.Agent fa
ON main.FareId = fa.FareId
LEFT OUTER JOIN travel.dbo.currencies c
ON c.Code = main.FareCurrency
Where

AND (PriceReturnType = @.ReturnType)
And main.PortSetID in (Select Distinct PortSetID
From FarePortSetMember Where PortCode = @.DestinationPort Or PortCode = @.DestinationCity)
And main.OriginPortSetId in (Select Distinct FarePortSetId
From FarePortSetMember Where PortCode = @.OriginPort Or PortCode = @.OriginCity)
AND ((DateFrom <= @.EarliestDepartureDate) AND (DateTo >= @.EarliestDepartureDate))
AND (DATEDIFF(day, TicketDateFrom,GetDate()) >= 0 AND DATEDIFF(day, TicketDateTo, getdate()) <=0)
And status in ('Active','Updating')

Option (KEEPFIXED PLAN)

I just added the OPTION (KEEPFIXED PLAN) and have been monitoring to see if the problem happens again.
So there is no fancy stuff in the query, but it seems to me that the .NET SQL provider are using different query plan.

Could MVP dudes verify this for us, please?

I am using SQL 2000 by the way and .NET 1.1 component called from ASP.NET 2.0

different results using ISO-8859-1 vs utf-8 with sp_xml_preparedDocument

We have a stored procedure which is passed an xml string (text datatype) and that string is passed on to sp_xml_prepared_Document (btw its a Sql 2000 server). We've had no problems until yesterday when a really long xml string (>18,000) was passed in and all of sudden the proc stopped working with an error message of:

"Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 9

XML parsing error: An invalid character was found in text content."

So after doing a lot of googling i found a post stating the following:

"I found that I have to place an xml declaration of

<?xml version="1.0" encoding="ISO-8859-1"?>in my XML string so the sp_xml_prepared_Document stored procedure will treatthe data as UTF-8 and not the Database's code page."

The encoding value I had always used was utf-8. Sure enough when I changed the encoding value to ISO-8859-1, the proc worked as expected. So I would like a better explanation as to what is going on here.

Why do shorter strings work fine when the encoding value is "utf-8" and longer ones only work when ISO-8859-1 is used?

Should I always use ISO-8859-1 as my encoding value?

Thanks for your help!

Thanks to Michael Rys and his blog for helping explain this. The article below was lifted from his site and gives a good explanation of whats happening. I think we will try changing the datatype to NText and see if that alleviates any future headaches.

Recently, I received several customer reports, that sp_xml_preparedocument started rasing the following errors after upgrading their XML-based application from SQL Server 2000 SP3a (or earlier) to either SP4 or SQL Server 2005:

Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is `An invalid character was found in text content.`.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

This error is now raised because of a stricter error-discovery during parsing. When we moved from MSXML 2.6 to MSXMLSQL for SQL Server 2000 SP4 and SQL Server 2005, we fixed a couple of bugs in the parser that could lead to data corruption (invalid data being parsed). As a consequence, you are now receiving this error code instead of having invalid characters accepted.

The consequence is that one has to be more explicit with setting the encoding. This will also help mitigate against involuntary data corruption (see below for an example). There are two ways to fix an application (besides moving to an XML datatype and the nodes() method):

1. Make sure that the XML document when passed in a TEXT or (VAR)CHAR argument is compatible with the default code page of the database (or the string type) by either

1. setting the encoding property in the XML declaration (e.g., when the code page is ISO-Latin1), or

2. by making sure that the code page of the database (or string type) can preserve all UTF-8 code points that will ever be passed in through the string.

2. Change the type of the argument to NTEXT (or N(VAR)CHAR) and pass in the XML in UTF-16 encoding (an XML declaration is optional).

Here are the technical details:

sp_xml_preparedocument takes either a single-byte character string (TEXT, (VAR)CHAR) or a two-byte character string (NTEXT, N(VAR)CHAR). In the first case, the string is associated with a code page (normally the databases default code page). In the second case the string is assumed to be either UCS-2 or UTF-16 encoded. Sp_xml_preparedocument (unlike the newer XML datatype parser) will only pick up the strings code page in the second case to detect the encoding of the XML document, but not in the first case. Instead it will look at the string and follow the XML 1.0 spec detection rules. That means that for a single-byte character string unless there is an XML declaration saying otherwise, the data will be parsed as UTF-8. This works fine as long as your instance documents happen to only use characters that share the same code points on the UTF-8 and the database code page (e.g., the ASCII range in ISO-Latin1 and UTF-8), but it will lead to problems, if you use characters that are mapped to different code pages.

For example, the character (Unicode U+00AE, represented as 0xAE00 in SQL Server) will be represented in an ISO-LATIN1 code page (SQL_Latin1_General_CP1_CS_AS) as 0xAE. However if you do not specify an encoding on the XML document that contains the character and pass it as a single-byte character string, the XML parser will interpret the code point 0xAE not as the character but as an invalid starting character of a multi-byte UTF-8 encoding: UTF-8 characters bit sequences start either with 0 (1-byte encoded characters), 110 (two byte encoded characters), 1110 (three byte encoded characters), or 11110 (4-byte encoded surrogate pairs), while 0xAE is 10101110.

In MSXML 2.6 (and thus SQL Server 2000 SP3 and earlier), the XML parser would preserve such invalid characters and thus corrupt the data, while in MSXML 3.0/MSXMLSQL (and thus SQL Server 2000 SP4 and SQL Server 2005), the parser will reject them.

Note that you will still have to watch out for data corruption if the ISO-Latin characters make up a valid UTF-8 encoded character (solutions are as outlined above). For example,

declare @.h int
exec sp_xml_preparedocument @.h output, ``
select * from openxml(@.h, `/root`) with (attr nvarchar(200) `@.attr`)
exec sp_xml_removedocument @.h

will parse in SQL Server 2005 but return the character ? (U+05D1) since the two input characters (0xD791 in an ISO-Latin1 encoding) form the bit stream: 11010111 10010001 which is translated into 00000101 11010001 according to the UTF-8 encoding rules which is a valid UTF-8 2-byte encoded character representing U+05D1.

I hope you agree, that detecting such issues is better, even if we break "backwards-bug-compatibility".