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
>

Dimension Name Column Format Property SSAS 2005

I have tried entering different forms of syntax into the 'Format' property underneath the 'Name Column' property for a specific attribute within a dimension and it never seems to change the output when I view the attribute within the 'Browser' tab of the dimension. I tested this on the Adventure Works DW and I am unable to change the attribute format. Here is an example:

1. With the Adventure Works DW, open the 'Employee' dimension and modify the 'Birth Date' attribute's format property underneath name column. I have entered "d", format("DimEmployee"."BirthDate", 'mm/dd/yyyy'), and convert(varchar, "DimEmployee"."BirthDate", 101).

2. Process the dimension and click on the browser tab and view the 'Birth Date' hierarchy.

Has anyone had any luck using this 'Format' property for the 'Name Column' of an attribute? I believe you could easily do this in AS 2000, so I am wondering what the trick is in SSAS 2005. I would think that you could use this property, but I guess I need to know what the proper syntax is. I know that I could easily modify the data source view, but I want to know how to be able to do this in the future if needed.

I have checked on the web and in BOL and haven't found any reference information for this property and how to use it. If anyone knows of any documentation please let me know. I will take a look at the SQL 2008 BOL and see if that has anything new.

Thanks.

I just got a response back from Microsoft and this is what I had kind of figured because no matter what you type in this property it never would produce an error or change the results of the text.

The "Format" string for Attribute names is a stub for a later addon and is not implemented. Attribute names will only accept WChar types. Any formatting should be done either in the data source view as a "Named Calculation" or in the source table/view on the relational source.

Dimension Name Column Format Property SSAS 2005

I have tried entering different forms of syntax into the 'Format' property underneath the 'Name Column' property for a specific attribute within a dimension and it never seems to change the output when I view the attribute within the 'Browser' tab of the dimension. I tested this on the Adventure Works DW and I am unable to change the attribute format. Here is an example:

1. With the Adventure Works DW, open the 'Employee' dimension and modify the 'Birth Date' attribute's format property underneath name column. I have entered "d", format("DimEmployee"."BirthDate", 'mm/dd/yyyy'), and convert(varchar, "DimEmployee"."BirthDate", 101).

2. Process the dimension and click on the browser tab and view the 'Birth Date' hierarchy.

Has anyone had any luck using this 'Format' property for the 'Name Column' of an attribute? I believe you could easily do this in AS 2000, so I am wondering what the trick is in SSAS 2005. I would think that you could use this property, but I guess I need to know what the proper syntax is. I know that I could easily modify the data source view, but I want to know how to be able to do this in the future if needed.

I have checked on the web and in BOL and haven't found any reference information for this property and how to use it. If anyone knows of any documentation please let me know. I will take a look at the SQL 2008 BOL and see if that has anything new.

Thanks.

I just got a response back from Microsoft and this is what I had kind of figured because no matter what you type in this property it never would produce an error or change the results of the text.

The "Format" string for Attribute names is a stub for a later addon and is not implemented. Attribute names will only accept WChar types. Any formatting should be done either in the data source view as a "Named Calculation" or in the source table/view on the relational source.

Differential Backup with RETAINDAYS

I am looking into saving differential backup files for X number of days:

I used the wizzard with RETAINDAYS here is the syntax:

BACKUP DATABASE [Northwind] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\N' WITH NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL , NAME = N'n', SKIP , STATS = 10, FORMAT

When I run it overrides the first file.

My question is should I rename the differential backup file and save it in a different folder. If so how do I do that?

Or do you know of a script that will retain a differential backup for x number of days.

Or do you know of a better solution.

Thanks in advance

Anu AhujaHi Anu,

FYI: The Database Maintenance Wizard does the same for you for Full and Transaction Backups. But for Differential Backup , you have to write a script.

The script below does the backup of the currentdatabase by adding dateand time along with databasename. So this will backup the database with new name everytime.

Example: NorthwindAug231040.bak

/* Script to Backup the database with Date and Time */
/* Owner : Varad01 */
/* Try this script first with Pubs database */

Declare
@.CurrentDateTime varchar(20),
@.dbname varchar(20),
@.dbbackupname varchar(40)
Begin

Select @.dbname=db_name()

Select @.CurrentDateTime =
substring(DATENAME(month, getdate()),1,3) +
cast(DATEPART(day, GETDATE()) as varchar(2))+
cast(DATEPART(hh, GETDATE()) as varchar(2)) +
cast(DATEPART(mi, GETDATE()) as varchar(2))

Select @.dbbackupname = @.dbname+@.CurrentDateTime

select @.dbbackupname ='C:\' + @.dbbackupname + '.bak'

backup database @.dbname to disk=@.dbbackupname WITH NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS = 10, FORMAT

End

Hope this Helps.

Have Fun :)

Varad01
MCDBA,MCSE|||Thank you very much!!!

Anu:)|||Hello again,

The above script is working great, I am able to create a unique name for the differential backup. BUT ......I would like the backup to be retained for 3 days and that is not working.

Do I add a delete?

Thanks in advance.

Anu:confused:|||Hi!

Just a suggestion... why don't you create three separate folders for your three-day retention backup files. With this, you can safely specify different destination paths for each execution. Use the script below:

/* Script using different destination folders per day*/
/* Created by Boysie Jocson, Manila, Phils. */

declare @.day_week int,
@.directory char(80)
set @.day_week = datepart(dw,getdate())
if @.day_week = 1
begin
set @.directory = '\\servername\day1\db_1stdiff.bak'
end
else if @.day_week = 2
begin
set @.directory = '\\servername\day2\db_2nddiff.bak'
end
else if @.day_week = 3
begin
set @.directory = '\\servername\day3\db_3rddiff.bak'
end

BACKUP DATABASE [dbname] TO DISK = @.directory WITH INIT , NOUNLOAD , DIFFERENTIAL , NAME = N'db_diff', SKIP , STATS = 10, DESCRIPTION = N'db differential backup', NOFORMAT

... hope this helps.|||Thank you!! This is working.

Anu|||Thank you!! This is working.

Anu:)|||Originally posted by anu
Thank you!! This is working.

Anu:)

I'm happy to hear that. You're welcome!

Differences between June and September CTP releases

Hey y'all

I read in post in this forum that there have been changes in syntax and what not between the June and September CTP releases. Is there a list, document or webpage that spells out the differences between the two releases?

Thanks
I did a survey of the dev team and they're reasonably sure there were no user-visible changes between the June and September CTP's - primarily bug fixes and perf tuning..|||Thanks
So to clarify June CTP release is Beta 2 or 3?
and September CTP release is .... what?

Differences between DISTINCT and GROUP BY?

What is the difference between the two statements, besides the syntax? And
in general what are the differences between the DISTINCT and the GROUP BY
methods? -- Thanks
select field
from table
group by field
select distinct field
from tableDISTINCT is for distinctness.
GROUP BY is for aggregation. It just so happens that part of the
aggregation process is distinctness of all non-aggregated columns, which is
why you see the same behavior when you use GROUP BY with no aggregation,
compared with DISTINCT.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote
in message news:4390be2d$0$3756$39cecf19@.news.twtelecom.net...
> What is the difference between the two statements, besides the syntax? And
> in general what are the differences between the DISTINCT and the GROUP BY
> methods? -- Thanks
> select field
> from table
> group by field
> select distinct field
> from table
>|||GROUP BY without any aggregate columns is effectively the same as DISTINCT
on the same column set.
Specifically these are the same as far as SQL Server 2005 is concerned.
They return the same results. They will be optimized with the same level of
support in pretty much every case.
SELECT DISTINCT A, B, C FROM Table;
and
SELECT A, B, C FROM Table GROUP BY A, B, C
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eK61kj49FHA.3308@.TK2MSFTNGP11.phx.gbl...
> DISTINCT is for distinctness.
> GROUP BY is for aggregation. It just so happens that part of the
> aggregation process is distinctness of all non-aggregated columns, which
> is why you see the same behavior when you use GROUP BY with no
> aggregation, compared with DISTINCT.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam>
> wrote in message news:4390be2d$0$3756$39cecf19@.news.twtelecom.net...
>|||Thanks. My habit is to throw in "DISTINCT" when I see it is going to
duplicate the results (or that it is already doing it), it's fast and
efficient.
I use GROUP BY when I need to aggregate as you say.
Although Crystal is doing all of my grouping and counting now.

> DISTINCT is for distinctness.
> GROUP BY is for aggregation. It just so happens that part of the
> aggregation process is distinctness of all non-aggregated columns, which
> is why you see the same behavior when you use GROUP BY with no
> aggregation, compared with DISTINCT.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam>
> wrote in message news:4390be2d$0$3756$39cecf19@.news.twtelecom.net...
>|||Hmm, when I see a query that returns duplicates, my first reaction is to
inspect the query, because if this happens the query is usually
incorrect. My first reaction is not to throw in a DISTINCT...
Gert-Jan
Nicolas Verhaeghe wrote:
> Thanks. My habit is to throw in "DISTINCT" when I see it is going to
> duplicate the results (or that it is already doing it), it's fast and
> efficient.
> I use GROUP BY when I need to aggregate as you say.
> Although Crystal is doing all of my grouping and counting now.
>