I have a couple performance counters logging data to a sql 2000 database.
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use the
DATEADD function and the BOL reference states that character data can be used
in the DATEADD function if it is in a date format. The format of the date is
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get around
it? I have no control over the setup of the table that holds the performance
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!
phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:
> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform any
> date functions against this column in my sql queries. I am trying to use the
> DATEADD function and the BOL reference states that character data can be used
> in the DATEADD function if it is in a date format. The format of the date is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get around
> it? I have no control over the setup of the table that holds the performance
> counter data. It is created automatically when the counter is set to log to
> a database. Thanks for any help!
|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your test
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However if
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
[vbcol=seagreen]
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does your
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your test
> supports that. I used query analyzer to return a small amount of data from
> the column in question, and in the results pane it looks correct. However if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance counter
> seems to be doing something funny as it logs data, but I'm not sure what or
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying to
> run it as a select statement in query analyzer. Thanks for any other ideas!
> "Michael Evanchik" wrote:
|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, if
I go to that cell and delete whatever extra is at the end, I can then perform
date functions. It does not appear to simply be a space, because I can add a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
[vbcol=seagreen]
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pasting
> into notepad or wordpad, no data will be lost that way. Also you can paste
> special i think in excel as pure text but im no excel expert. also does your
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Yeah, I realized the Excel test was no good after I made my last post. I've
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counter,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of the
> dates to a different table that I created, I get the same error. However, if
> I go to that cell and delete whatever extra is at the end, I can then perform
> date functions. It does not appear to simply be a space, because I can add a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to cleanup
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
[vbcol=seagreen]
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
sql
Showing posts with label perform. Show all posts
Showing posts with label perform. Show all posts
Friday, March 23, 2012
Performance Counter Data
I have a couple performance counters logging data to a sql 2000 database.
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use th
e
DATEADD function and the BOL reference states that character data can be use
d
in the DATEADD function if it is in a date format. The format of the date i
s
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get aroun
d
it? I have no control over the setup of the table that holds the performanc
e
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:
> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform an
y
> date functions against this column in my sql queries. I am trying to use
the
> DATEADD function and the BOL reference states that character data can be u
sed
> in the DATEADD function if it is in a date format. The format of the date
is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEA
DD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get aro
und
> it? I have no control over the setup of the table that holds the performa
nce
> counter data. It is created automatically when the counter is set to log
to
> a database. Thanks for any help!|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your tes
t
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However i
f
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
[vbcol=seagreen]
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
>|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does you
r
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your t
est
> supports that. I used query analyzer to return a small amount of data fro
m
> the column in question, and in the results pane it looks correct. However
if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance count
er
> seems to be doing something funny as it logs data, but I'm not sure what o
r
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying
to
> run it as a select statement in query analyzer. Thanks for any other idea
s!
> "Michael Evanchik" wrote:
>|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, i
f
I go to that cell and delete whatever extra is at the end, I can then perfor
m
date functions. It does not appear to simply be a space, because I can add
a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
[vbcol=seagreen]
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pastin
g
> into notepad or wordpad, no data will be lost that way. Also you can past
e
> special i think in excel as pure text but im no excel expert. also does y
our
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
>|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Yeah, I realized the Excel test was no good after I made my last post. I'
ve
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counte
r,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of t
he
> dates to a different table that I created, I get the same error. However,
if
> I go to that cell and delete whatever extra is at the end, I can then perf
orm
> date functions. It does not appear to simply be a space, because I can ad
d a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to clean
up
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
>|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
[vbcol=seagreen]
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
>|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
>
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use th
e
DATEADD function and the BOL reference states that character data can be use
d
in the DATEADD function if it is in a date format. The format of the date i
s
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get aroun
d
it? I have no control over the setup of the table that holds the performanc
e
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:
> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform an
y
> date functions against this column in my sql queries. I am trying to use
the
> DATEADD function and the BOL reference states that character data can be u
sed
> in the DATEADD function if it is in a date format. The format of the date
is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEA
DD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get aro
und
> it? I have no control over the setup of the table that holds the performa
nce
> counter data. It is created automatically when the counter is set to log
to
> a database. Thanks for any help!|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your tes
t
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However i
f
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
[vbcol=seagreen]
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
>|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does you
r
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your t
est
> supports that. I used query analyzer to return a small amount of data fro
m
> the column in question, and in the results pane it looks correct. However
if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance count
er
> seems to be doing something funny as it logs data, but I'm not sure what o
r
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying
to
> run it as a select statement in query analyzer. Thanks for any other idea
s!
> "Michael Evanchik" wrote:
>|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, i
f
I go to that cell and delete whatever extra is at the end, I can then perfor
m
date functions. It does not appear to simply be a space, because I can add
a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
[vbcol=seagreen]
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pastin
g
> into notepad or wordpad, no data will be lost that way. Also you can past
e
> special i think in excel as pure text but im no excel expert. also does y
our
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
>|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Yeah, I realized the Excel test was no good after I made my last post. I'
ve
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counte
r,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of t
he
> dates to a different table that I created, I get the same error. However,
if
> I go to that cell and delete whatever extra is at the end, I can then perf
orm
> date functions. It does not appear to simply be a space, because I can ad
d a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to clean
up
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
>|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
[vbcol=seagreen]
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
>|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
>
Wednesday, March 21, 2012
Performance Counter Data
I have a couple performance counters logging data to a sql 2000 database.
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use the
DATEADD function and the BOL reference states that character data can be used
in the DATEADD function if it is in a date format. The format of the date is
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get around
it? I have no control over the setup of the table that holds the performance
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:
> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform any
> date functions against this column in my sql queries. I am trying to use the
> DATEADD function and the BOL reference states that character data can be used
> in the DATEADD function if it is in a date format. The format of the date is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get around
> it? I have no control over the setup of the table that holds the performance
> counter data. It is created automatically when the counter is set to log to
> a database. Thanks for any help!|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your test
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However if
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
> > I have a couple performance counters logging data to a sql 2000 database.
> > The date column has a data type of varchar and I am not able to perform any
> > date functions against this column in my sql queries. I am trying to use the
> > DATEADD function and the BOL reference states that character data can be used
> > in the DATEADD function if it is in a date format. The format of the date is
> > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > function, I receive an error: "error converting datetime from character
> > string". Does anyone know what might be causing this or how I can get around
> > it? I have no control over the setup of the table that holds the performance
> > counter data. It is created automatically when the counter is set to log to
> > a database. Thanks for any help!|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does your
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your test
> supports that. I used query analyzer to return a small amount of data from
> the column in question, and in the results pane it looks correct. However if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance counter
> seems to be doing something funny as it logs data, but I'm not sure what or
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying to
> run it as a select statement in query analyzer. Thanks for any other ideas!
> "Michael Evanchik" wrote:
> >
> >
> > phavel,
> >
> > i created a table with one field varchar 50.
> > created a view doing select dateadd(day,1,myfield) and it worked fine.
> >
> > can you elaborate on your code and your schema?
> >
> > Mike
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > I have a couple performance counters logging data to a sql 2000 database.
> > > The date column has a data type of varchar and I am not able to perform any
> > > date functions against this column in my sql queries. I am trying to use the
> > > DATEADD function and the BOL reference states that character data can be used
> > > in the DATEADD function if it is in a date format. The format of the date is
> > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > function, I receive an error: "error converting datetime from character
> > > string". Does anyone know what might be causing this or how I can get around
> > > it? I have no control over the setup of the table that holds the performance
> > > counter data. It is created automatically when the counter is set to log to
> > > a database. Thanks for any help!|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, if
I go to that cell and delete whatever extra is at the end, I can then perform
date functions. It does not appear to simply be a space, because I can add a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pasting
> into notepad or wordpad, no data will be lost that way. Also you can paste
> special i think in excel as pure text but im no excel expert. also does your
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
> > Thanks for the response. This appears to be something specific to the way
> > the performance counter logs the data. BOL states that there should be no
> > problem using date functions against columns of character data, and your test
> > supports that. I used query analyzer to return a small amount of data from
> > the column in question, and in the results pane it looks correct. However if
> > I copy some of the dates out of query analyzer and paste them into Excel,
> > they lose the data and only the minutes are pasted. The performance counter
> > seems to be doing something funny as it logs data, but I'm not sure what or
> > if there's any way to work around it. The VIEW I'm trying to create is
> > essentially the same as what you wrote. I get the same error when trying to
> > run it as a select statement in query analyzer. Thanks for any other ideas!
> >
> > "Michael Evanchik" wrote:
> >
> > >
> > >
> > > phavel,
> > >
> > > i created a table with one field varchar 50.
> > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > >
> > > can you elaborate on your code and your schema?
> > >
> > > Mike
> > >
> > > www.michaelevanchik.com
> > >
> > > "phavel" wrote:
> > >
> > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > The date column has a data type of varchar and I am not able to perform any
> > > > date functions against this column in my sql queries. I am trying to use the
> > > > DATEADD function and the BOL reference states that character data can be used
> > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > function, I receive an error: "error converting datetime from character
> > > > string". Does anyone know what might be causing this or how I can get around
> > > > it? I have no control over the setup of the table that holds the performance
> > > > counter data. It is created automatically when the counter is set to log to
> > > > a database. Thanks for any help!|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Yeah, I realized the Excel test was no good after I made my last post. I've
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counter,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of the
> dates to a different table that I created, I get the same error. However, if
> I go to that cell and delete whatever extra is at the end, I can then perform
> date functions. It does not appear to simply be a space, because I can add a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to cleanup
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
> > phavel,
> >
> > i wouldnt paste into excel if you really want to look at data. try pasting
> > into notepad or wordpad, no data will be lost that way. Also you can paste
> > special i think in excel as pure text but im no excel expert. also does your
> > convert work if you specifiy one column in one row ? or does it only error
> > when you try to convert the whole table of that field?
> >
> > Michael Evanchik
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > Thanks for the response. This appears to be something specific to the way
> > > the performance counter logs the data. BOL states that there should be no
> > > problem using date functions against columns of character data, and your test
> > > supports that. I used query analyzer to return a small amount of data from
> > > the column in question, and in the results pane it looks correct. However if
> > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > they lose the data and only the minutes are pasted. The performance counter
> > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > if there's any way to work around it. The VIEW I'm trying to create is
> > > essentially the same as what you wrote. I get the same error when trying to
> > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > >
> > > "Michael Evanchik" wrote:
> > >
> > > >
> > > >
> > > > phavel,
> > > >
> > > > i created a table with one field varchar 50.
> > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > >
> > > > can you elaborate on your code and your schema?
> > > >
> > > > Mike
> > > >
> > > > www.michaelevanchik.com
> > > >
> > > > "phavel" wrote:
> > > >
> > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > function, I receive an error: "error converting datetime from character
> > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > it? I have no control over the setup of the table that holds the performance
> > > > > counter data. It is created automatically when the counter is set to log to
> > > > > a database. Thanks for any help!|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
> > Yeah, I realized the Excel test was no good after I made my last post. I've
> > figured this out to a certain extent, but I'm still not sure of a good way
> > around it. When the date is placed in the table by the performance counter,
> > it is putting something "extra" at the end of the value. It appears that
> > this is why I cannot perform date functions. If I copy and paste one of the
> > dates to a different table that I created, I get the same error. However, if
> > I go to that cell and delete whatever extra is at the end, I can then perform
> > date functions. It does not appear to simply be a space, because I can add a
> > trailing space manually and it still works fine. Any ideas on what can be
> > done? I guess I can have a separate query that runs periodically to cleanup
> > the date data, but this could get messy with timing. Any suggestions
> > welcome. Thanks!
> >
> > "Michael Evanchik" wrote:
> >
> > > phavel,
> > >
> > > i wouldnt paste into excel if you really want to look at data. try pasting
> > > into notepad or wordpad, no data will be lost that way. Also you can paste
> > > special i think in excel as pure text but im no excel expert. also does your
> > > convert work if you specifiy one column in one row ? or does it only error
> > > when you try to convert the whole table of that field?
> > >
> > > Michael Evanchik
> > >
> > > www.michaelevanchik.com
> > >
> > > "phavel" wrote:
> > >
> > > > Thanks for the response. This appears to be something specific to the way
> > > > the performance counter logs the data. BOL states that there should be no
> > > > problem using date functions against columns of character data, and your test
> > > > supports that. I used query analyzer to return a small amount of data from
> > > > the column in question, and in the results pane it looks correct. However if
> > > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > > they lose the data and only the minutes are pasted. The performance counter
> > > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > > if there's any way to work around it. The VIEW I'm trying to create is
> > > > essentially the same as what you wrote. I get the same error when trying to
> > > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > > >
> > > > "Michael Evanchik" wrote:
> > > >
> > > > >
> > > > >
> > > > > phavel,
> > > > >
> > > > > i created a table with one field varchar 50.
> > > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > > >
> > > > > can you elaborate on your code and your schema?
> > > > >
> > > > > Mike
> > > > >
> > > > > www.michaelevanchik.com
> > > > >
> > > > > "phavel" wrote:
> > > > >
> > > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > > function, I receive an error: "error converting datetime from character
> > > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > a database. Thanks for any help!|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
> > dateadd(day,1,substring(field,1,12))
> >
> > Michael Evanchik
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > Yeah, I realized the Excel test was no good after I made my last post. I've
> > > figured this out to a certain extent, but I'm still not sure of a good way
> > > around it. When the date is placed in the table by the performance counter,
> > > it is putting something "extra" at the end of the value. It appears that
> > > this is why I cannot perform date functions. If I copy and paste one of the
> > > dates to a different table that I created, I get the same error. However, if
> > > I go to that cell and delete whatever extra is at the end, I can then perform
> > > date functions. It does not appear to simply be a space, because I can add a
> > > trailing space manually and it still works fine. Any ideas on what can be
> > > done? I guess I can have a separate query that runs periodically to cleanup
> > > the date data, but this could get messy with timing. Any suggestions
> > > welcome. Thanks!
> > >
> > > "Michael Evanchik" wrote:
> > >
> > > > phavel,
> > > >
> > > > i wouldnt paste into excel if you really want to look at data. try pasting
> > > > into notepad or wordpad, no data will be lost that way. Also you can paste
> > > > special i think in excel as pure text but im no excel expert. also does your
> > > > convert work if you specifiy one column in one row ? or does it only error
> > > > when you try to convert the whole table of that field?
> > > >
> > > > Michael Evanchik
> > > >
> > > > www.michaelevanchik.com
> > > >
> > > > "phavel" wrote:
> > > >
> > > > > Thanks for the response. This appears to be something specific to the way
> > > > > the performance counter logs the data. BOL states that there should be no
> > > > > problem using date functions against columns of character data, and your test
> > > > > supports that. I used query analyzer to return a small amount of data from
> > > > > the column in question, and in the results pane it looks correct. However if
> > > > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > > > they lose the data and only the minutes are pasted. The performance counter
> > > > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > > > if there's any way to work around it. The VIEW I'm trying to create is
> > > > > essentially the same as what you wrote. I get the same error when trying to
> > > > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > > > >
> > > > > "Michael Evanchik" wrote:
> > > > >
> > > > > >
> > > > > >
> > > > > > phavel,
> > > > > >
> > > > > > i created a table with one field varchar 50.
> > > > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > > > >
> > > > > > can you elaborate on your code and your schema?
> > > > > >
> > > > > > Mike
> > > > > >
> > > > > > www.michaelevanchik.com
> > > > > >
> > > > > > "phavel" wrote:
> > > > > >
> > > > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > > > function, I receive an error: "error converting datetime from character
> > > > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > > a database. Thanks for any help!
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use the
DATEADD function and the BOL reference states that character data can be used
in the DATEADD function if it is in a date format. The format of the date is
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get around
it? I have no control over the setup of the table that holds the performance
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:
> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform any
> date functions against this column in my sql queries. I am trying to use the
> DATEADD function and the BOL reference states that character data can be used
> in the DATEADD function if it is in a date format. The format of the date is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get around
> it? I have no control over the setup of the table that holds the performance
> counter data. It is created automatically when the counter is set to log to
> a database. Thanks for any help!|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your test
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However if
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
> > I have a couple performance counters logging data to a sql 2000 database.
> > The date column has a data type of varchar and I am not able to perform any
> > date functions against this column in my sql queries. I am trying to use the
> > DATEADD function and the BOL reference states that character data can be used
> > in the DATEADD function if it is in a date format. The format of the date is
> > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > function, I receive an error: "error converting datetime from character
> > string". Does anyone know what might be causing this or how I can get around
> > it? I have no control over the setup of the table that holds the performance
> > counter data. It is created automatically when the counter is set to log to
> > a database. Thanks for any help!|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does your
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your test
> supports that. I used query analyzer to return a small amount of data from
> the column in question, and in the results pane it looks correct. However if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance counter
> seems to be doing something funny as it logs data, but I'm not sure what or
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying to
> run it as a select statement in query analyzer. Thanks for any other ideas!
> "Michael Evanchik" wrote:
> >
> >
> > phavel,
> >
> > i created a table with one field varchar 50.
> > created a view doing select dateadd(day,1,myfield) and it worked fine.
> >
> > can you elaborate on your code and your schema?
> >
> > Mike
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > I have a couple performance counters logging data to a sql 2000 database.
> > > The date column has a data type of varchar and I am not able to perform any
> > > date functions against this column in my sql queries. I am trying to use the
> > > DATEADD function and the BOL reference states that character data can be used
> > > in the DATEADD function if it is in a date format. The format of the date is
> > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > function, I receive an error: "error converting datetime from character
> > > string". Does anyone know what might be causing this or how I can get around
> > > it? I have no control over the setup of the table that holds the performance
> > > counter data. It is created automatically when the counter is set to log to
> > > a database. Thanks for any help!|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, if
I go to that cell and delete whatever extra is at the end, I can then perform
date functions. It does not appear to simply be a space, because I can add a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pasting
> into notepad or wordpad, no data will be lost that way. Also you can paste
> special i think in excel as pure text but im no excel expert. also does your
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
> > Thanks for the response. This appears to be something specific to the way
> > the performance counter logs the data. BOL states that there should be no
> > problem using date functions against columns of character data, and your test
> > supports that. I used query analyzer to return a small amount of data from
> > the column in question, and in the results pane it looks correct. However if
> > I copy some of the dates out of query analyzer and paste them into Excel,
> > they lose the data and only the minutes are pasted. The performance counter
> > seems to be doing something funny as it logs data, but I'm not sure what or
> > if there's any way to work around it. The VIEW I'm trying to create is
> > essentially the same as what you wrote. I get the same error when trying to
> > run it as a select statement in query analyzer. Thanks for any other ideas!
> >
> > "Michael Evanchik" wrote:
> >
> > >
> > >
> > > phavel,
> > >
> > > i created a table with one field varchar 50.
> > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > >
> > > can you elaborate on your code and your schema?
> > >
> > > Mike
> > >
> > > www.michaelevanchik.com
> > >
> > > "phavel" wrote:
> > >
> > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > The date column has a data type of varchar and I am not able to perform any
> > > > date functions against this column in my sql queries. I am trying to use the
> > > > DATEADD function and the BOL reference states that character data can be used
> > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > function, I receive an error: "error converting datetime from character
> > > > string". Does anyone know what might be causing this or how I can get around
> > > > it? I have no control over the setup of the table that holds the performance
> > > > counter data. It is created automatically when the counter is set to log to
> > > > a database. Thanks for any help!|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Yeah, I realized the Excel test was no good after I made my last post. I've
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counter,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of the
> dates to a different table that I created, I get the same error. However, if
> I go to that cell and delete whatever extra is at the end, I can then perform
> date functions. It does not appear to simply be a space, because I can add a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to cleanup
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
> > phavel,
> >
> > i wouldnt paste into excel if you really want to look at data. try pasting
> > into notepad or wordpad, no data will be lost that way. Also you can paste
> > special i think in excel as pure text but im no excel expert. also does your
> > convert work if you specifiy one column in one row ? or does it only error
> > when you try to convert the whole table of that field?
> >
> > Michael Evanchik
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > Thanks for the response. This appears to be something specific to the way
> > > the performance counter logs the data. BOL states that there should be no
> > > problem using date functions against columns of character data, and your test
> > > supports that. I used query analyzer to return a small amount of data from
> > > the column in question, and in the results pane it looks correct. However if
> > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > they lose the data and only the minutes are pasted. The performance counter
> > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > if there's any way to work around it. The VIEW I'm trying to create is
> > > essentially the same as what you wrote. I get the same error when trying to
> > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > >
> > > "Michael Evanchik" wrote:
> > >
> > > >
> > > >
> > > > phavel,
> > > >
> > > > i created a table with one field varchar 50.
> > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > >
> > > > can you elaborate on your code and your schema?
> > > >
> > > > Mike
> > > >
> > > > www.michaelevanchik.com
> > > >
> > > > "phavel" wrote:
> > > >
> > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > function, I receive an error: "error converting datetime from character
> > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > it? I have no control over the setup of the table that holds the performance
> > > > > counter data. It is created automatically when the counter is set to log to
> > > > > a database. Thanks for any help!|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
> > Yeah, I realized the Excel test was no good after I made my last post. I've
> > figured this out to a certain extent, but I'm still not sure of a good way
> > around it. When the date is placed in the table by the performance counter,
> > it is putting something "extra" at the end of the value. It appears that
> > this is why I cannot perform date functions. If I copy and paste one of the
> > dates to a different table that I created, I get the same error. However, if
> > I go to that cell and delete whatever extra is at the end, I can then perform
> > date functions. It does not appear to simply be a space, because I can add a
> > trailing space manually and it still works fine. Any ideas on what can be
> > done? I guess I can have a separate query that runs periodically to cleanup
> > the date data, but this could get messy with timing. Any suggestions
> > welcome. Thanks!
> >
> > "Michael Evanchik" wrote:
> >
> > > phavel,
> > >
> > > i wouldnt paste into excel if you really want to look at data. try pasting
> > > into notepad or wordpad, no data will be lost that way. Also you can paste
> > > special i think in excel as pure text but im no excel expert. also does your
> > > convert work if you specifiy one column in one row ? or does it only error
> > > when you try to convert the whole table of that field?
> > >
> > > Michael Evanchik
> > >
> > > www.michaelevanchik.com
> > >
> > > "phavel" wrote:
> > >
> > > > Thanks for the response. This appears to be something specific to the way
> > > > the performance counter logs the data. BOL states that there should be no
> > > > problem using date functions against columns of character data, and your test
> > > > supports that. I used query analyzer to return a small amount of data from
> > > > the column in question, and in the results pane it looks correct. However if
> > > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > > they lose the data and only the minutes are pasted. The performance counter
> > > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > > if there's any way to work around it. The VIEW I'm trying to create is
> > > > essentially the same as what you wrote. I get the same error when trying to
> > > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > > >
> > > > "Michael Evanchik" wrote:
> > > >
> > > > >
> > > > >
> > > > > phavel,
> > > > >
> > > > > i created a table with one field varchar 50.
> > > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > > >
> > > > > can you elaborate on your code and your schema?
> > > > >
> > > > > Mike
> > > > >
> > > > > www.michaelevanchik.com
> > > > >
> > > > > "phavel" wrote:
> > > > >
> > > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > > function, I receive an error: "error converting datetime from character
> > > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > a database. Thanks for any help!|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
> > dateadd(day,1,substring(field,1,12))
> >
> > Michael Evanchik
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > Yeah, I realized the Excel test was no good after I made my last post. I've
> > > figured this out to a certain extent, but I'm still not sure of a good way
> > > around it. When the date is placed in the table by the performance counter,
> > > it is putting something "extra" at the end of the value. It appears that
> > > this is why I cannot perform date functions. If I copy and paste one of the
> > > dates to a different table that I created, I get the same error. However, if
> > > I go to that cell and delete whatever extra is at the end, I can then perform
> > > date functions. It does not appear to simply be a space, because I can add a
> > > trailing space manually and it still works fine. Any ideas on what can be
> > > done? I guess I can have a separate query that runs periodically to cleanup
> > > the date data, but this could get messy with timing. Any suggestions
> > > welcome. Thanks!
> > >
> > > "Michael Evanchik" wrote:
> > >
> > > > phavel,
> > > >
> > > > i wouldnt paste into excel if you really want to look at data. try pasting
> > > > into notepad or wordpad, no data will be lost that way. Also you can paste
> > > > special i think in excel as pure text but im no excel expert. also does your
> > > > convert work if you specifiy one column in one row ? or does it only error
> > > > when you try to convert the whole table of that field?
> > > >
> > > > Michael Evanchik
> > > >
> > > > www.michaelevanchik.com
> > > >
> > > > "phavel" wrote:
> > > >
> > > > > Thanks for the response. This appears to be something specific to the way
> > > > > the performance counter logs the data. BOL states that there should be no
> > > > > problem using date functions against columns of character data, and your test
> > > > > supports that. I used query analyzer to return a small amount of data from
> > > > > the column in question, and in the results pane it looks correct. However if
> > > > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > > > they lose the data and only the minutes are pasted. The performance counter
> > > > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > > > if there's any way to work around it. The VIEW I'm trying to create is
> > > > > essentially the same as what you wrote. I get the same error when trying to
> > > > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > > > >
> > > > > "Michael Evanchik" wrote:
> > > > >
> > > > > >
> > > > > >
> > > > > > phavel,
> > > > > >
> > > > > > i created a table with one field varchar 50.
> > > > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > > > >
> > > > > > can you elaborate on your code and your schema?
> > > > > >
> > > > > > Mike
> > > > > >
> > > > > > www.michaelevanchik.com
> > > > > >
> > > > > > "phavel" wrote:
> > > > > >
> > > > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > > > function, I receive an error: "error converting datetime from character
> > > > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > > a database. Thanks for any help!
Saturday, February 25, 2012
Perform upgrade method
hi
I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .
Thanks
Mani
Explanation about PerformUpgrade and a sample code can be found in book online or at:
http://msdn2.microsoft.com/ko-kr/library/microsoft.sqlserver.dts.pipeline.pipelinecomponent.performupgrade.aspx
Hope this helps,
Ovidiu Burlacu
hi
thanks i got the solution the perform upgrade method only works if the current version property, that is stored in the package is less than the currentversion property of the component on the local computer.
Regards
Mani
|||hi
If i removed the old DLL from the GAC i am getting errors while opening the package saved with the old version DLL
these are the errors ...
Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package1.dtsx 0 0
Error 2 Error loading Package1.dtsx: The "component "Oracle Destination" (67)" failed to cache the component metadata object and returned error code 0x80131600. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 3 Error loading Package1.dtsx: Component "component "Oracle Destination" (67)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 4 Error loading Package1.dtsx: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. d:\testproject\integration services project5\Package1.dtsx 1 1
how can i correct these errors
thanks
Mani
|||You should not remo0ve the old DLL from the GAC becuase the component can no longer be instantiated. The new DLL should be able to live in the GAC with the old DLL because they have different versions (file or assembly or even the PublicTokenKey)
Thanks,
Ovidiu
Ovidiu, I don't see how you can do side by side with SSIS components in the GAC. File versions do not differentiate between assemblies, so you cannot use that to installside by side. If you change the assembly version or the key (and therefore the public key token) you can side by side them, but then your are stuffed since SSIS stores the full strong name in the package. Any change in the string name means you get the nice error about not being able to load the assembly. Hacking the XML would seem to be the only solution, shame it is not a supported one :)
|||Indeed you are right. I think is time for me to go in vacation 
In fact you can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.
Here's how:
1. Remove the old assembly from GAC.
2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.
3. In the new component override PerformUpgrade and in it make sure to include the following:
ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;
This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.
4. Install the new assembly to the GAC.
5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx
Now when you open packages with the old version the pattern above will automatically upgrade to the new version.
Milen
Perform upgrade method
hi
I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .
Thanks
Mani
Explanation about PerformUpgrade and a sample code can be found in book online or at:
http://msdn2.microsoft.com/ko-kr/library/microsoft.sqlserver.dts.pipeline.pipelinecomponent.performupgrade.aspx
Hope this helps,
Ovidiu Burlacu
hi
thanks i got the solution the perform upgrade method only works if the current version property, that is stored in the package is less than the currentversion property of the component on the local computer.
Regards
Mani
|||hi
If i removed the old DLL from the GAC i am getting errors while opening the package saved with the old version DLL
these are the errors ...
Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package1.dtsx 0 0
Error 2 Error loading Package1.dtsx: The "component "Oracle Destination" (67)" failed to cache the component metadata object and returned error code 0x80131600. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 3 Error loading Package1.dtsx: Component "component "Oracle Destination" (67)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 4 Error loading Package1.dtsx: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. d:\testproject\integration services project5\Package1.dtsx 1 1
how can i correct these errors
thanks
Mani
|||You should not remo0ve the old DLL from the GAC becuase the component can no longer be instantiated. The new DLL should be able to live in the GAC with the old DLL because they have different versions (file or assembly or even the PublicTokenKey)
Thanks,
Ovidiu
Ovidiu, I don't see how you can do side by side with SSIS components in the GAC. File versions do not differentiate between assemblies, so you cannot use that to installside by side. If you change the assembly version or the key (and therefore the public key token) you can side by side them, but then your are stuffed since SSIS stores the full strong name in the package. Any change in the string name means you get the nice error about not being able to load the assembly. Hacking the XML would seem to be the only solution, shame it is not a supported one :)
|||Indeed you are right. I think is time for me to go in vacation
In fact you can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.
Here's how:
1. Remove the old assembly from GAC.
2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.
3. In the new component override PerformUpgrade and in it make sure to include the following:
ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;
This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.
4. Install the new assembly to the GAC.
5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx
Now when you open packages with the old version the pattern above will automatically upgrade to the new version.
Milen
Perform several operations in 1 ALTER TABLE?
Hi there. Does anyone know of a why to perform several operations on a table within only 1 ALTER TABLE statement? I haven't found anything to date and don't even know if it's possible.
Thanks,
Angelwhat's the point? are you worried about performance? you shouldn't be, because you shouldn't be altering your tables at all in production, other than to fix bugs or release new versions of a product. it shouldn't be an everyday thing.|||I'm almost certain that this is a homework question. If you post a URL to the assignment, or a scanned image of the handout you got in class we can provide you with better help.
Yes, you can make more than one change in a single ALTER TABLE statement. No, you shouldn't do it because of the reasons cited by Jezemine and your present uncertainty in the process.
-PatP
Thanks,
Angelwhat's the point? are you worried about performance? you shouldn't be, because you shouldn't be altering your tables at all in production, other than to fix bugs or release new versions of a product. it shouldn't be an everyday thing.|||I'm almost certain that this is a homework question. If you post a URL to the assignment, or a scanned image of the handout you got in class we can provide you with better help.
Yes, you can make more than one change in a single ALTER TABLE statement. No, you shouldn't do it because of the reasons cited by Jezemine and your present uncertainty in the process.
-PatP
perform floating point addition in SQL stored procedure
Hi..
Is there any way to add the value of 4 column and at the same time print the result together with another column?
Is there any way to add the value of 4 column and at the same time print the result together with another column?
I have this stored procedure:
CREATE PROCEDURE sp_queuelist AS
BEGIN
DECLARE @.temp1 As Decimal
DECLARE @.temp2 As Decimal
DECLARE @.cash As Decimal
DECLARE @.cheque As Decimal
DECLARE @.card As Decimal
DECLARE @.nets As Decimal
DECLARE @.bill As Decimal
DECLARE @.company As Decimal
SELECT
@.cash=Cash,@.cheque=Cheque,@.card=Card,@.nets=Nets,
@.bill=Bill,@.company=Company
FROM QUEUE
ORDER BY QNo
SET @.temp1 = @.cash + @.cheque + @.card + @.nets
SET @.temp2 = @.bill +@.company
Select QNO,
PCNo,
PName,
@.temp1 As totalCash,
@.temp2 As totalContract,
Doctor
FROM QUEUE
ORDER BY QNo
END
GO
Basically I want to add 4 columns: cash+cheque+card+nets into totalCash and bill+company to totalContract.
All the 6 field types are decimal.
I want to calculate temp1 and temp2 and then select the rest of the column to be displayed in the datagrid.
However, this stored procedure gives me 2 problems:
1. It gives the rounding result of the addition of decimal number, not the decimal itself.
2. All the rows in datagrid display the same result for totalCash and totalContract, which is the total from the last row in the table.
I seldom use stored procedure.
Is there any way to solve this problem?
Any suggestion is most welcomed.
Thank you in advanced.
Sincerely
Agustina(1) you can try changing the decimal to float.
(2) in the design of your table, you can set the formula for the column as sum of the other 4 columns. that way you dont need to worry abt doing the addition. anytime you make any change in any of the columns, the computed column is automatically updated.
if you need more help in this approach, let me know.
HTH.
Perform calculation using sql script
Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.
If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.
If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>
Perform calculation using sql script
Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrea
se)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>
Perform calculation using sql script
Is it possible to multiply a vaiable and a set column and update table with
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>
new data? I need to estimate future net income using a predetermined
percentage against the previous years net income numbers. I use this to run
projection calculations against estimated annual net income. The formula
would look like this:
Projected Net Income = Previous Net Income + (Previous Net Income * Percent
Increase)
Table1: INCOME
Fields: NetIncome, DataYear, ProjectedNetIncome
Table2: PROJECTION
ProjectedYr, PercentIncrease
I would like to use PercentIncrease as a variable (this number can change
year to year and give me the ability to change percentage.)
Below is what i have been trying ... i know the code is incorrect and its at
the set statement (multipling variable against netincome field).
This is logically what I want to do but I know the code is all off...
any suggestions on how to correct?
/* Declare Variables */
DECLARE @.CurrentYr Numeric(9)
DECLARE @.IncomeIncrease Numeric(9)
SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
/* Calculate Projected Income Amt for Year Entered into Projected Year Field
*/
UPDATE Income
SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
WHERE DataYear = @.CurrentYr
thanks in advance for any help
rob
Any suggestions would be great.If you could post DDL and a little sample data + your expected results, it
would help immensely. This assumes that the INCOME and PROJECTION Tables
are related via the DataYear and ProjectYear columns. If the INCOME table
has a row for DataYear = 2003, it will only update if the PROJECTION table
has a row for DataYear = 2003.
UPDATE INCOME SET ProjectedNetIncome = i.NetIncome + (i.NetIncome *
p.PercentIncrease)
FROM INCOME i, PROJECTION p
WHERE i.DataYear = p.ProjectYear
But it's hard to be certain without knowing where you're starting or where
you want to end up...
"Rob" <temp@.dstek.com> wrote in message
news:eOm9mEDTFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it possible to multiply a vaiable and a set column and update table
> with
> new data? I need to estimate future net income using a predetermined
> percentage against the previous years net income numbers. I use this to
> run
> projection calculations against estimated annual net income. The formula
> would look like this:
> Projected Net Income = Previous Net Income + (Previous Net Income *
> Percent
> Increase)
> Table1: INCOME
> Fields: NetIncome, DataYear, ProjectedNetIncome
> Table2: PROJECTION
> ProjectedYr, PercentIncrease
> I would like to use PercentIncrease as a variable (this number can change
> year to year and give me the ability to change percentage.)
> Below is what i have been trying ... i know the code is incorrect and its
> at
> the set statement (multipling variable against netincome field).
> This is logically what I want to do but I know the code is all off...
> any suggestions on how to correct?
> /* Declare Variables */
> DECLARE @.CurrentYr Numeric(9)
> DECLARE @.IncomeIncrease Numeric(9)
> SET @.CurrentYr=(SELECT ProjectedYr FROM Projections)
> SET @.IncomeIncrease=(SELECT PercentIncrease FROM Projections)
> /* Calculate Projected Income Amt for Year Entered into Projected Year
> Field
> */
> UPDATE Income
> SET ProjectedNetIncome = ((NetIncome)*((NetIncome)*(@.IncomeIncrease)))
> WHERE DataYear = @.CurrentYr
>
> thanks in advance for any help
> rob
> Any suggestions would be great.
>
>
perform calculation for only desired rows
hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.
as an example
nsty nsly percentage
total: 15 7 X
A: 5 4 25%
B: 5 3 66%
C: 5 0 N/A
the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?
am i on the right track by researching filters and expressions, or is this a matter for the query
thanks in advance for any help
HiAn expression will be able to handle this fine.
Use the IIF function to only include nsty if nsly is greater than zero.
For your total % sum use something like this:
= (Sum(IIF(Fields!nsly.value > 0,Fields!nsty.value,0))/Sum(Fields!nsly.Value)) -1
Hope this helps
Cheers
Mark
|||
thanks for the response; i think i still need to expand on the expression a little bit but at least i know how to go about it now.
Perform an experssion in desing mode
Hi all,
SSIS, I would like to perform/validate an experssion in design mode. In particular, i would like to see the value of the varibles before running the whole package. I tried several time in Command Windows or Immedite Window unsuccessfully. Do those windows can only be used in running mode? Or how can I do that in other way?
Thanks
Where are you performing the expression? In the Control Flow or Data Flow.
This BOL entry describes how to evaluate an expression at design time in the Control Flow: http://msdn2.microsoft.com/en-us/library/ms141698.aspx
Expressions in the data flow are validated automatically at design time and any errors will be raised - but they can not be evaluated at design time.
Debug windows are only available when the package is running in the debugger.
Donald Farmer
|||That's mean I must create varibles in Control flow or date flow even though i just want to see the result of 1+1? Can't it like in VB or C# IDE?
|||
I'm still not clear where you are using the expression, so it's difficult to help in detail. You can evaluate Property Expressions, but not expressions in the data flow.
Donald
perform all the actions in one pass rather than take serveral call
You're going to spend far less time writing a stored procedure that creates
one account and iterating through your array in your client application
calling the stored proc.
"E B via webservertalk.com" wrote:
> My application has one web form where admin can add as many users as
> desired and at the end when he press SaveChanges I want to add all of them
> to database, There should be some way to pass arrays to stored procedure..
I
> am looking for it or something similar to this...
> I want to pass an array into a stored procedure so that it can perform all
> the actions in one pass rather than take serveral calls to the server.
> I'm working with (SQL Server, .NET)
> --
> Message posted via http://www.webservertalk.com
>Alien2_51 wrote:
> You're going to spend far less time writing a stored procedure that
> creates one account and iterating through your array in your client
> application calling the stored proc.
>
bs
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||to Bob Barrows [MVP] what does it mean ?
Message posted via http://www.webservertalk.com|||E B via webservertalk.com wrote:
> to Bob Barrows [MVP] what does it mean ?
Oh, I'm sorry. it's short for:
I don't believe that what you said is necessarily correct.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Ok, however i think i solve the problem , i used the link:
http://www.sommarskog.se/arrays-in-sql.html
what do u think'
Message posted via http://www.webservertalk.com|||E B via webservertalk.com wrote:
> Ok, however i think i solve the problem , i used the link:
> http://www.sommarskog.se/arrays-in-sql.html
> what do u think'
Well, there are several options in that link. Hopefully you satisfied
yourself that you picked the optimum one for your situation.
BTW, my comment was not directed at you. it was directed at the comment that
looping through the array in the client and making multiple calls to a
procedure ... across processes ... would be quicker than making a single
call to a stored procedure.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
one account and iterating through your array in your client application
calling the stored proc.
"E B via webservertalk.com" wrote:
> My application has one web form where admin can add as many users as
> desired and at the end when he press SaveChanges I want to add all of them
> to database, There should be some way to pass arrays to stored procedure..
I
> am looking for it or something similar to this...
> I want to pass an array into a stored procedure so that it can perform all
> the actions in one pass rather than take serveral calls to the server.
> I'm working with (SQL Server, .NET)
> --
> Message posted via http://www.webservertalk.com
>Alien2_51 wrote:
> You're going to spend far less time writing a stored procedure that
> creates one account and iterating through your array in your client
> application calling the stored proc.
>
bs
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||to Bob Barrows [MVP] what does it mean ?
Message posted via http://www.webservertalk.com|||E B via webservertalk.com wrote:
> to Bob Barrows [MVP] what does it mean ?
Oh, I'm sorry. it's short for:
I don't believe that what you said is necessarily correct.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Ok, however i think i solve the problem , i used the link:
http://www.sommarskog.se/arrays-in-sql.html
what do u think'
Message posted via http://www.webservertalk.com|||E B via webservertalk.com wrote:
> Ok, however i think i solve the problem , i used the link:
> http://www.sommarskog.se/arrays-in-sql.html
> what do u think'
Well, there are several options in that link. Hopefully you satisfied
yourself that you picked the optimum one for your situation.
BTW, my comment was not directed at you. it was directed at the comment that
looping through the array in the client and making multiple calls to a
procedure ... across processes ... would be quicker than making a single
call to a stored procedure.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Perform aggregate functions on uniqueidentifiers
For some reason, [on sql2k] one cannot perform "Count(X)" where X is of type
uniqueidentifier. Will future versions of sql server suffer from this
limitation? 2003 or 2005?
We came across this problem when we had to execute a query with multiple
table joins.
Hasani,
The workaround that I use is to store them as BINARY(16).
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%233sMM$flEHA.3564@.TK2MSFTNGP14.phx.gbl...
> For some reason, [on sql2k] one cannot perform "Count(X)" where X is of
type
> uniqueidentifier. Will future versions of sql server suffer from this
> limitation? 2003 or 2005?
> We came across this problem when we had to execute a query with multiple
> table joins.
>
|||clever, i'll tell my supervisor tomorrow.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Oc23mQglEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hasani,
> The workaround that I use is to store them as BINARY(16).
>
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%233sMM$flEHA.3564@.TK2MSFTNGP14.phx.gbl...
> type
>
|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%23gbFEiglEHA.2892@.tk2msftngp13.phx.gbl...
> clever, i'll tell my supervisor tomorrow.
If you want to get even tricker, you can experiment with doing something
like this when you store the GUID:
SELECT CONVERT(BINARY(6), GETDATE()) + CONVERT(BINARY(10), NEWID()) AS
DateGUID
This reduces the uniqueness a bit (removes 6 of the 16 bytes), but not
too much because there are only so many rows you can insert every 3
milliseconds. The upside is that you can now cluster on your GUID column
without destroying INSERT performance.
|||Will sql server allow binary columntypes as primary keys?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb$oqkglEHA.3712@.TK2MSFTNGP15.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%23gbFEiglEHA.2892@.tk2msftngp13.phx.gbl...
> If you want to get even tricker, you can experiment with doing
> something
> like this when you store the GUID:
> SELECT CONVERT(BINARY(6), GETDATE()) + CONVERT(BINARY(10), NEWID()) AS
> DateGUID
> This reduces the uniqueness a bit (removes 6 of the 16 bytes), but not
> too much because there are only so many rows you can insert every 3
> milliseconds. The upside is that you can now cluster on your GUID column
> without destroying INSERT performance.
>
|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:eP7D5uglEHA.712@.TK2MSFTNGP09.phx.gbl...
> Will sql server allow binary columntypes as primary keys?
Yes. When I have used GUIDs as primary keys (rarely, I don't think it's
a great idea most of the time), I have used the BINARY(16) technique. More
recently I've used the date concatenation technique in a project and it
worked out very well.
|||What are you reasons for not using a guid as a primary key?
We currently use integers as a primary key, but we use a stored procedure to
generate a unqiue random non-sequential integer, and we store this value in
a table to stop duplicates. In that scenario, I'm arguing that we should
just use uniqueidentifier types because we seem to just be reinventing the
wheel, but then someone mention the aggregate function thing with
uniqueidentifier types. I'm not aware of any penalties associated with using
uniqueidentifier types though, other than, it will require more bytes per
column, than an int.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23OvcnxglEHA.596@.tk2msftngp13.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:eP7D5uglEHA.712@.TK2MSFTNGP09.phx.gbl...
> Yes. When I have used GUIDs as primary keys (rarely, I don't think
> it's
> a great idea most of the time), I have used the BINARY(16) technique.
> More
> recently I've used the date concatenation technique in a project and it
> worked out very well.
>
|||Hasani (remove nospam from address) wrote:
> What are you reasons for not using a guid as a primary key?
> We currently use integers as a primary key, but we use a stored
> procedure to generate a unqiue random non-sequential integer, and we
> store this value in a table to stop duplicates. In that scenario, I'm
> arguing that we should just use uniqueidentifier types because we
> seem to just be reinventing the wheel, but then someone mention the
> aggregate function thing with uniqueidentifier types. I'm not aware
> of any penalties associated with using uniqueidentifier types though,
> other than, it will require more bytes per column, than an int.
You're right in that it's a lot more bytes per row using a UID as
opposed to an INT IDENTITY. Four times the storage, which translates to
a much larger index when using a uniqueidentifier. And as Adam
eloquently mentioned, using a UID as a clustered key does not work well
because you get a lot of page splitting and head movement on the drives.
Adding a date component as a prefix to the UID prevents much of th epage
splitting, increasing insert performance. However, using a UID as
clustered key means propagating that key to all non-clustered indexes,
making them much larger as well.
If you can, I would stick with an INT IDENTITY column for a PK.
David G.
|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OUyrc5glEHA.2892@.tk2msftngp13.phx.gbl...
> What are you reasons for not using a guid as a primary key?
> We currently use integers as a primary key, but we use a stored procedure
to
> generate a unqiue random non-sequential integer, and we store this value
in
> a table to stop duplicates. In that scenario, I'm arguing that we should
> just use uniqueidentifier types because we seem to just be reinventing the
> wheel, but then someone mention the aggregate function thing with
> uniqueidentifier types. I'm not aware of any penalties associated with
using
> uniqueidentifier types though, other than, it will require more bytes per
> column, than an int.
I think David G pointed out most of the issues in his post, so I'll
instead refer to the only times I have had to use a GUID, which is when the
application itself was responsible for creating the key. Applications
cannot reliably create unique integers, so GUIDs are pretty much the only
choice (or natural primary keys, if there's one available).
Also, why would you want to use a non-sequential random integer instead
of an IDENTITY?
|||Maybe I contradicted myself when I said non-sequential random...
We essentially need a random number generator to use as a primary key value.
I don't know if sql supports it. All I've seen is a unique number generator
that increments by one on every insert. It's unique but not random. The
problem is is, this value is going to be made public and we don't want to
make it obvious that it's just an incrementing value (think cookies and
websessions).
What we currently do (sometimes) is have 2 columns, I that's an
autoincrementing int that's a primary key, and the other is a
uniqueidentifer column that isn't a primary key (but may have a constraint
to make sure there are no duplicates), and we would make the uniqueidentifer
value public so in a cookie, it would always look random.
I don't feel comfortable in the scenario because you have 2 columns that are
doing the same thing (preserving/ensuring uniqueness). So I'm trying to look
at all the tradeoffs of using a uniqueidentifier instead of an int, and vice
versa.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23IUly9mlEHA.1652@.TK2MSFTNGP09.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:OUyrc5glEHA.2892@.tk2msftngp13.phx.gbl...
> to
> in
> using
> I think David G pointed out most of the issues in his post, so I'll
> instead refer to the only times I have had to use a GUID, which is when
> the
> application itself was responsible for creating the key. Applications
> cannot reliably create unique integers, so GUIDs are pretty much the only
> choice (or natural primary keys, if there's one available).
> Also, why would you want to use a non-sequential random integer instead
> of an IDENTITY?
>
uniqueidentifier. Will future versions of sql server suffer from this
limitation? 2003 or 2005?
We came across this problem when we had to execute a query with multiple
table joins.
Hasani,
The workaround that I use is to store them as BINARY(16).
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%233sMM$flEHA.3564@.TK2MSFTNGP14.phx.gbl...
> For some reason, [on sql2k] one cannot perform "Count(X)" where X is of
type
> uniqueidentifier. Will future versions of sql server suffer from this
> limitation? 2003 or 2005?
> We came across this problem when we had to execute a query with multiple
> table joins.
>
|||clever, i'll tell my supervisor tomorrow.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Oc23mQglEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hasani,
> The workaround that I use is to store them as BINARY(16).
>
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%233sMM$flEHA.3564@.TK2MSFTNGP14.phx.gbl...
> type
>
|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%23gbFEiglEHA.2892@.tk2msftngp13.phx.gbl...
> clever, i'll tell my supervisor tomorrow.
If you want to get even tricker, you can experiment with doing something
like this when you store the GUID:
SELECT CONVERT(BINARY(6), GETDATE()) + CONVERT(BINARY(10), NEWID()) AS
DateGUID
This reduces the uniqueness a bit (removes 6 of the 16 bytes), but not
too much because there are only so many rows you can insert every 3
milliseconds. The upside is that you can now cluster on your GUID column
without destroying INSERT performance.
|||Will sql server allow binary columntypes as primary keys?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb$oqkglEHA.3712@.TK2MSFTNGP15.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%23gbFEiglEHA.2892@.tk2msftngp13.phx.gbl...
> If you want to get even tricker, you can experiment with doing
> something
> like this when you store the GUID:
> SELECT CONVERT(BINARY(6), GETDATE()) + CONVERT(BINARY(10), NEWID()) AS
> DateGUID
> This reduces the uniqueness a bit (removes 6 of the 16 bytes), but not
> too much because there are only so many rows you can insert every 3
> milliseconds. The upside is that you can now cluster on your GUID column
> without destroying INSERT performance.
>
|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:eP7D5uglEHA.712@.TK2MSFTNGP09.phx.gbl...
> Will sql server allow binary columntypes as primary keys?
Yes. When I have used GUIDs as primary keys (rarely, I don't think it's
a great idea most of the time), I have used the BINARY(16) technique. More
recently I've used the date concatenation technique in a project and it
worked out very well.
|||What are you reasons for not using a guid as a primary key?
We currently use integers as a primary key, but we use a stored procedure to
generate a unqiue random non-sequential integer, and we store this value in
a table to stop duplicates. In that scenario, I'm arguing that we should
just use uniqueidentifier types because we seem to just be reinventing the
wheel, but then someone mention the aggregate function thing with
uniqueidentifier types. I'm not aware of any penalties associated with using
uniqueidentifier types though, other than, it will require more bytes per
column, than an int.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23OvcnxglEHA.596@.tk2msftngp13.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:eP7D5uglEHA.712@.TK2MSFTNGP09.phx.gbl...
> Yes. When I have used GUIDs as primary keys (rarely, I don't think
> it's
> a great idea most of the time), I have used the BINARY(16) technique.
> More
> recently I've used the date concatenation technique in a project and it
> worked out very well.
>
|||Hasani (remove nospam from address) wrote:
> What are you reasons for not using a guid as a primary key?
> We currently use integers as a primary key, but we use a stored
> procedure to generate a unqiue random non-sequential integer, and we
> store this value in a table to stop duplicates. In that scenario, I'm
> arguing that we should just use uniqueidentifier types because we
> seem to just be reinventing the wheel, but then someone mention the
> aggregate function thing with uniqueidentifier types. I'm not aware
> of any penalties associated with using uniqueidentifier types though,
> other than, it will require more bytes per column, than an int.
You're right in that it's a lot more bytes per row using a UID as
opposed to an INT IDENTITY. Four times the storage, which translates to
a much larger index when using a uniqueidentifier. And as Adam
eloquently mentioned, using a UID as a clustered key does not work well
because you get a lot of page splitting and head movement on the drives.
Adding a date component as a prefix to the UID prevents much of th epage
splitting, increasing insert performance. However, using a UID as
clustered key means propagating that key to all non-clustered indexes,
making them much larger as well.
If you can, I would stick with an INT IDENTITY column for a PK.
David G.
|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OUyrc5glEHA.2892@.tk2msftngp13.phx.gbl...
> What are you reasons for not using a guid as a primary key?
> We currently use integers as a primary key, but we use a stored procedure
to
> generate a unqiue random non-sequential integer, and we store this value
in
> a table to stop duplicates. In that scenario, I'm arguing that we should
> just use uniqueidentifier types because we seem to just be reinventing the
> wheel, but then someone mention the aggregate function thing with
> uniqueidentifier types. I'm not aware of any penalties associated with
using
> uniqueidentifier types though, other than, it will require more bytes per
> column, than an int.
I think David G pointed out most of the issues in his post, so I'll
instead refer to the only times I have had to use a GUID, which is when the
application itself was responsible for creating the key. Applications
cannot reliably create unique integers, so GUIDs are pretty much the only
choice (or natural primary keys, if there's one available).
Also, why would you want to use a non-sequential random integer instead
of an IDENTITY?
|||Maybe I contradicted myself when I said non-sequential random...
We essentially need a random number generator to use as a primary key value.
I don't know if sql supports it. All I've seen is a unique number generator
that increments by one on every insert. It's unique but not random. The
problem is is, this value is going to be made public and we don't want to
make it obvious that it's just an incrementing value (think cookies and
websessions).
What we currently do (sometimes) is have 2 columns, I that's an
autoincrementing int that's a primary key, and the other is a
uniqueidentifer column that isn't a primary key (but may have a constraint
to make sure there are no duplicates), and we would make the uniqueidentifer
value public so in a cookie, it would always look random.
I don't feel comfortable in the scenario because you have 2 columns that are
doing the same thing (preserving/ensuring uniqueness). So I'm trying to look
at all the tradeoffs of using a uniqueidentifier instead of an int, and vice
versa.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23IUly9mlEHA.1652@.TK2MSFTNGP09.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:OUyrc5glEHA.2892@.tk2msftngp13.phx.gbl...
> to
> in
> using
> I think David G pointed out most of the issues in his post, so I'll
> instead refer to the only times I have had to use a GUID, which is when
> the
> application itself was responsible for creating the key. Applications
> cannot reliably create unique integers, so GUIDs are pretty much the only
> choice (or natural primary keys, if there's one available).
> Also, why would you want to use a non-sequential random integer instead
> of an IDENTITY?
>
Perform aggregate functions on uniqueidentifiers
For some reason, [on sql2k] one cannot perform "Count(X)" where X is of type
uniqueidentifier. Will future versions of sql server suffer from this
limitation? 2003 or 2005?
We came across this problem when we had to execute a query with multiple
table joins.Hasani,
The workaround that I use is to store them as BINARY(16).
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%233sMM$flEHA.3564@.TK2MSFTNGP14.phx.gbl...
> For some reason, [on sql2k] one cannot perform "Count(X)" where X is of
type
> uniqueidentifier. Will future versions of sql server suffer from this
> limitation? 2003 or 2005?
> We came across this problem when we had to execute a query with multiple
> table joins.
>|||clever, i'll tell my supervisor tomorrow.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Oc23mQglEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hasani,
> The workaround that I use is to store them as BINARY(16).
>
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%233sMM$flEHA.3564@.TK2MSFTNGP14.phx.gbl...
>> For some reason, [on sql2k] one cannot perform "Count(X)" where X is of
> type
>> uniqueidentifier. Will future versions of sql server suffer from this
>> limitation? 2003 or 2005?
>> We came across this problem when we had to execute a query with multiple
>> table joins.
>>
>|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%23gbFEiglEHA.2892@.tk2msftngp13.phx.gbl...
> clever, i'll tell my supervisor tomorrow.
If you want to get even tricker, you can experiment with doing something
like this when you store the GUID:
SELECT CONVERT(BINARY(6), GETDATE()) + CONVERT(BINARY(10), NEWID()) AS
DateGUID
This reduces the uniqueness a bit (removes 6 of the 16 bytes), but not
too much because there are only so many rows you can insert every 3
milliseconds. The upside is that you can now cluster on your GUID column
without destroying INSERT performance.|||Will sql server allow binary columntypes as primary keys?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb$oqkglEHA.3712@.TK2MSFTNGP15.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%23gbFEiglEHA.2892@.tk2msftngp13.phx.gbl...
>> clever, i'll tell my supervisor tomorrow.
> If you want to get even tricker, you can experiment with doing
> something
> like this when you store the GUID:
> SELECT CONVERT(BINARY(6), GETDATE()) + CONVERT(BINARY(10), NEWID()) AS
> DateGUID
> This reduces the uniqueness a bit (removes 6 of the 16 bytes), but not
> too much because there are only so many rows you can insert every 3
> milliseconds. The upside is that you can now cluster on your GUID column
> without destroying INSERT performance.
>|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:eP7D5uglEHA.712@.TK2MSFTNGP09.phx.gbl...
> Will sql server allow binary columntypes as primary keys?
Yes. When I have used GUIDs as primary keys (rarely, I don't think it's
a great idea most of the time), I have used the BINARY(16) technique. More
recently I've used the date concatenation technique in a project and it
worked out very well.|||What are you reasons for not using a guid as a primary key?
We currently use integers as a primary key, but we use a stored procedure to
generate a unqiue random non-sequential integer, and we store this value in
a table to stop duplicates. In that scenario, I'm arguing that we should
just use uniqueidentifier types because we seem to just be reinventing the
wheel, but then someone mention the aggregate function thing with
uniqueidentifier types. I'm not aware of any penalties associated with using
uniqueidentifier types though, other than, it will require more bytes per
column, than an int.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23OvcnxglEHA.596@.tk2msftngp13.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:eP7D5uglEHA.712@.TK2MSFTNGP09.phx.gbl...
>> Will sql server allow binary columntypes as primary keys?
> Yes. When I have used GUIDs as primary keys (rarely, I don't think
> it's
> a great idea most of the time), I have used the BINARY(16) technique.
> More
> recently I've used the date concatenation technique in a project and it
> worked out very well.
>|||Hasani (remove nospam from address) wrote:
> What are you reasons for not using a guid as a primary key?
> We currently use integers as a primary key, but we use a stored
> procedure to generate a unqiue random non-sequential integer, and we
> store this value in a table to stop duplicates. In that scenario, I'm
> arguing that we should just use uniqueidentifier types because we
> seem to just be reinventing the wheel, but then someone mention the
> aggregate function thing with uniqueidentifier types. I'm not aware
> of any penalties associated with using uniqueidentifier types though,
> other than, it will require more bytes per column, than an int.
You're right in that it's a lot more bytes per row using a UID as
opposed to an INT IDENTITY. Four times the storage, which translates to
a much larger index when using a uniqueidentifier. And as Adam
eloquently mentioned, using a UID as a clustered key does not work well
because you get a lot of page splitting and head movement on the drives.
Adding a date component as a prefix to the UID prevents much of th epage
splitting, increasing insert performance. However, using a UID as
clustered key means propagating that key to all non-clustered indexes,
making them much larger as well.
If you can, I would stick with an INT IDENTITY column for a PK.
David G.|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OUyrc5glEHA.2892@.tk2msftngp13.phx.gbl...
> What are you reasons for not using a guid as a primary key?
> We currently use integers as a primary key, but we use a stored procedure
to
> generate a unqiue random non-sequential integer, and we store this value
in
> a table to stop duplicates. In that scenario, I'm arguing that we should
> just use uniqueidentifier types because we seem to just be reinventing the
> wheel, but then someone mention the aggregate function thing with
> uniqueidentifier types. I'm not aware of any penalties associated with
using
> uniqueidentifier types though, other than, it will require more bytes per
> column, than an int.
I think David G pointed out most of the issues in his post, so I'll
instead refer to the only times I have had to use a GUID, which is when the
application itself was responsible for creating the key. Applications
cannot reliably create unique integers, so GUIDs are pretty much the only
choice (or natural primary keys, if there's one available).
Also, why would you want to use a non-sequential random integer instead
of an IDENTITY?|||Maybe I contradicted myself when I said non-sequential random...
We essentially need a random number generator to use as a primary key value.
I don't know if sql supports it. All I've seen is a unique number generator
that increments by one on every insert. It's unique but not random. The
problem is is, this value is going to be made public and we don't want to
make it obvious that it's just an incrementing value (think cookies and
websessions).
What we currently do (sometimes) is have 2 columns, I that's an
autoincrementing int that's a primary key, and the other is a
uniqueidentifer column that isn't a primary key (but may have a constraint
to make sure there are no duplicates), and we would make the uniqueidentifer
value public so in a cookie, it would always look random.
I don't feel comfortable in the scenario because you have 2 columns that are
doing the same thing (preserving/ensuring uniqueness). So I'm trying to look
at all the tradeoffs of using a uniqueidentifier instead of an int, and vice
versa.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23IUly9mlEHA.1652@.TK2MSFTNGP09.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:OUyrc5glEHA.2892@.tk2msftngp13.phx.gbl...
>> What are you reasons for not using a guid as a primary key?
>> We currently use integers as a primary key, but we use a stored procedure
> to
>> generate a unqiue random non-sequential integer, and we store this value
> in
>> a table to stop duplicates. In that scenario, I'm arguing that we should
>> just use uniqueidentifier types because we seem to just be reinventing
>> the
>> wheel, but then someone mention the aggregate function thing with
>> uniqueidentifier types. I'm not aware of any penalties associated with
> using
>> uniqueidentifier types though, other than, it will require more bytes per
>> column, than an int.
> I think David G pointed out most of the issues in his post, so I'll
> instead refer to the only times I have had to use a GUID, which is when
> the
> application itself was responsible for creating the key. Applications
> cannot reliably create unique integers, so GUIDs are pretty much the only
> choice (or natural primary keys, if there's one available).
> Also, why would you want to use a non-sequential random integer instead
> of an IDENTITY?
>|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%23Aar%23RnlEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Maybe I contradicted myself when I said non-sequential random...
> We essentially need a random number generator to use as a primary key
value.
> I don't know if sql supports it. All I've seen is a unique number
generator
> that increments by one on every insert. It's unique but not random. The
> problem is is, this value is going to be made public and we don't want to
> make it obvious that it's just an incrementing value (think cookies and
> websessions).
If you're only generating one at a time, why not just use RAND()?|||We'll there's a stored procedure someone created that uses RAND to create a
unique integer, by storing all values created by the stored proc in a table,
to stop duplicates, but, unfortunately, when a record is deleted that has a
value generated by the stored procedure, it doesn't remove the generated
value from the lookup table used by the stored procedure. That's the only
reason why I'm was RAND, but I can modify the code to make sure deleted
records 'release' the generated RAND value. But I do like the uid because
it's alphanumeric, which is secure more in a cookie, well in cracking time,
than an all numeric cookie.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OGss9inlEHA.748@.TK2MSFTNGP15.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%23Aar%23RnlEHA.1356@.TK2MSFTNGP09.phx.gbl...
>> Maybe I contradicted myself when I said non-sequential random...
>> We essentially need a random number generator to use as a primary key
> value.
>> I don't know if sql supports it. All I've seen is a unique number
> generator
>> that increments by one on every insert. It's unique but not random. The
>> problem is is, this value is going to be made public and we don't want to
>> make it obvious that it's just an incrementing value (think cookies and
>> websessions).
> If you're only generating one at a time, why not just use RAND()?
>|||Hasani (remove nospam from address) wrote:
> We'll there's a stored procedure someone created that uses RAND to
> create a unique integer, by storing all values created by the stored
> proc in a table, to stop duplicates, but, unfortunately, when a
> record is deleted that has a value generated by the stored procedure,
> it doesn't remove the generated value from the lookup table used by
> the stored procedure. That's the only reason why I'm was RAND, but I
> can modify the code to make sure deleted records 'release' the
> generated RAND value. But I do like the uid because it's
> alphanumeric, which is secure more in a cookie, well in cracking
> time, than an all numeric cookie.
>
You could add a computed column to the table to do the same thing (which
would eliminate the overhead of using a uniqueidentifier altogether).
And you can start the identity value higher if you don't want it to
start at 0.
Something like:
Create Table #test (
ID INT IDENTITY NOT NULL,
SomeText nvarchar(10),
"CookieID" as N'ALPHA-STUFF' + RIGHT(N'0000000000' + CAST(ID as
NVARCHAR(10)), 10))
Insert into #test values ('ABC')
Insert into #test values ('ABC')
Insert into #test values ('ABC')
Insert into #test values ('ABC')
Insert into #test values ('ABC')
Select * from #test
David G.
uniqueidentifier. Will future versions of sql server suffer from this
limitation? 2003 or 2005?
We came across this problem when we had to execute a query with multiple
table joins.Hasani,
The workaround that I use is to store them as BINARY(16).
"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%233sMM$flEHA.3564@.TK2MSFTNGP14.phx.gbl...
> For some reason, [on sql2k] one cannot perform "Count(X)" where X is of
type
> uniqueidentifier. Will future versions of sql server suffer from this
> limitation? 2003 or 2005?
> We came across this problem when we had to execute a query with multiple
> table joins.
>|||clever, i'll tell my supervisor tomorrow.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Oc23mQglEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hasani,
> The workaround that I use is to store them as BINARY(16).
>
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%233sMM$flEHA.3564@.TK2MSFTNGP14.phx.gbl...
>> For some reason, [on sql2k] one cannot perform "Count(X)" where X is of
> type
>> uniqueidentifier. Will future versions of sql server suffer from this
>> limitation? 2003 or 2005?
>> We came across this problem when we had to execute a query with multiple
>> table joins.
>>
>|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%23gbFEiglEHA.2892@.tk2msftngp13.phx.gbl...
> clever, i'll tell my supervisor tomorrow.
If you want to get even tricker, you can experiment with doing something
like this when you store the GUID:
SELECT CONVERT(BINARY(6), GETDATE()) + CONVERT(BINARY(10), NEWID()) AS
DateGUID
This reduces the uniqueness a bit (removes 6 of the 16 bytes), but not
too much because there are only so many rows you can insert every 3
milliseconds. The upside is that you can now cluster on your GUID column
without destroying INSERT performance.|||Will sql server allow binary columntypes as primary keys?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb$oqkglEHA.3712@.TK2MSFTNGP15.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%23gbFEiglEHA.2892@.tk2msftngp13.phx.gbl...
>> clever, i'll tell my supervisor tomorrow.
> If you want to get even tricker, you can experiment with doing
> something
> like this when you store the GUID:
> SELECT CONVERT(BINARY(6), GETDATE()) + CONVERT(BINARY(10), NEWID()) AS
> DateGUID
> This reduces the uniqueness a bit (removes 6 of the 16 bytes), but not
> too much because there are only so many rows you can insert every 3
> milliseconds. The upside is that you can now cluster on your GUID column
> without destroying INSERT performance.
>|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:eP7D5uglEHA.712@.TK2MSFTNGP09.phx.gbl...
> Will sql server allow binary columntypes as primary keys?
Yes. When I have used GUIDs as primary keys (rarely, I don't think it's
a great idea most of the time), I have used the BINARY(16) technique. More
recently I've used the date concatenation technique in a project and it
worked out very well.|||What are you reasons for not using a guid as a primary key?
We currently use integers as a primary key, but we use a stored procedure to
generate a unqiue random non-sequential integer, and we store this value in
a table to stop duplicates. In that scenario, I'm arguing that we should
just use uniqueidentifier types because we seem to just be reinventing the
wheel, but then someone mention the aggregate function thing with
uniqueidentifier types. I'm not aware of any penalties associated with using
uniqueidentifier types though, other than, it will require more bytes per
column, than an int.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23OvcnxglEHA.596@.tk2msftngp13.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:eP7D5uglEHA.712@.TK2MSFTNGP09.phx.gbl...
>> Will sql server allow binary columntypes as primary keys?
> Yes. When I have used GUIDs as primary keys (rarely, I don't think
> it's
> a great idea most of the time), I have used the BINARY(16) technique.
> More
> recently I've used the date concatenation technique in a project and it
> worked out very well.
>|||Hasani (remove nospam from address) wrote:
> What are you reasons for not using a guid as a primary key?
> We currently use integers as a primary key, but we use a stored
> procedure to generate a unqiue random non-sequential integer, and we
> store this value in a table to stop duplicates. In that scenario, I'm
> arguing that we should just use uniqueidentifier types because we
> seem to just be reinventing the wheel, but then someone mention the
> aggregate function thing with uniqueidentifier types. I'm not aware
> of any penalties associated with using uniqueidentifier types though,
> other than, it will require more bytes per column, than an int.
You're right in that it's a lot more bytes per row using a UID as
opposed to an INT IDENTITY. Four times the storage, which translates to
a much larger index when using a uniqueidentifier. And as Adam
eloquently mentioned, using a UID as a clustered key does not work well
because you get a lot of page splitting and head movement on the drives.
Adding a date component as a prefix to the UID prevents much of th epage
splitting, increasing insert performance. However, using a UID as
clustered key means propagating that key to all non-clustered indexes,
making them much larger as well.
If you can, I would stick with an INT IDENTITY column for a PK.
David G.|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:OUyrc5glEHA.2892@.tk2msftngp13.phx.gbl...
> What are you reasons for not using a guid as a primary key?
> We currently use integers as a primary key, but we use a stored procedure
to
> generate a unqiue random non-sequential integer, and we store this value
in
> a table to stop duplicates. In that scenario, I'm arguing that we should
> just use uniqueidentifier types because we seem to just be reinventing the
> wheel, but then someone mention the aggregate function thing with
> uniqueidentifier types. I'm not aware of any penalties associated with
using
> uniqueidentifier types though, other than, it will require more bytes per
> column, than an int.
I think David G pointed out most of the issues in his post, so I'll
instead refer to the only times I have had to use a GUID, which is when the
application itself was responsible for creating the key. Applications
cannot reliably create unique integers, so GUIDs are pretty much the only
choice (or natural primary keys, if there's one available).
Also, why would you want to use a non-sequential random integer instead
of an IDENTITY?|||Maybe I contradicted myself when I said non-sequential random...
We essentially need a random number generator to use as a primary key value.
I don't know if sql supports it. All I've seen is a unique number generator
that increments by one on every insert. It's unique but not random. The
problem is is, this value is going to be made public and we don't want to
make it obvious that it's just an incrementing value (think cookies and
websessions).
What we currently do (sometimes) is have 2 columns, I that's an
autoincrementing int that's a primary key, and the other is a
uniqueidentifer column that isn't a primary key (but may have a constraint
to make sure there are no duplicates), and we would make the uniqueidentifer
value public so in a cookie, it would always look random.
I don't feel comfortable in the scenario because you have 2 columns that are
doing the same thing (preserving/ensuring uniqueness). So I'm trying to look
at all the tradeoffs of using a uniqueidentifier instead of an int, and vice
versa.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23IUly9mlEHA.1652@.TK2MSFTNGP09.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:OUyrc5glEHA.2892@.tk2msftngp13.phx.gbl...
>> What are you reasons for not using a guid as a primary key?
>> We currently use integers as a primary key, but we use a stored procedure
> to
>> generate a unqiue random non-sequential integer, and we store this value
> in
>> a table to stop duplicates. In that scenario, I'm arguing that we should
>> just use uniqueidentifier types because we seem to just be reinventing
>> the
>> wheel, but then someone mention the aggregate function thing with
>> uniqueidentifier types. I'm not aware of any penalties associated with
> using
>> uniqueidentifier types though, other than, it will require more bytes per
>> column, than an int.
> I think David G pointed out most of the issues in his post, so I'll
> instead refer to the only times I have had to use a GUID, which is when
> the
> application itself was responsible for creating the key. Applications
> cannot reliably create unique integers, so GUIDs are pretty much the only
> choice (or natural primary keys, if there's one available).
> Also, why would you want to use a non-sequential random integer instead
> of an IDENTITY?
>|||"Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com> wrote
in message news:%23Aar%23RnlEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Maybe I contradicted myself when I said non-sequential random...
> We essentially need a random number generator to use as a primary key
value.
> I don't know if sql supports it. All I've seen is a unique number
generator
> that increments by one on every insert. It's unique but not random. The
> problem is is, this value is going to be made public and we don't want to
> make it obvious that it's just an incrementing value (think cookies and
> websessions).
If you're only generating one at a time, why not just use RAND()?|||We'll there's a stored procedure someone created that uses RAND to create a
unique integer, by storing all values created by the stored proc in a table,
to stop duplicates, but, unfortunately, when a record is deleted that has a
value generated by the stored procedure, it doesn't remove the generated
value from the lookup table used by the stored procedure. That's the only
reason why I'm was RAND, but I can modify the code to make sure deleted
records 'release' the generated RAND value. But I do like the uid because
it's alphanumeric, which is secure more in a cookie, well in cracking time,
than an all numeric cookie.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OGss9inlEHA.748@.TK2MSFTNGP15.phx.gbl...
> "Hasani (remove nospam from address)" <hblackwell@.n0sp4m.popstick.com>
> wrote
> in message news:%23Aar%23RnlEHA.1356@.TK2MSFTNGP09.phx.gbl...
>> Maybe I contradicted myself when I said non-sequential random...
>> We essentially need a random number generator to use as a primary key
> value.
>> I don't know if sql supports it. All I've seen is a unique number
> generator
>> that increments by one on every insert. It's unique but not random. The
>> problem is is, this value is going to be made public and we don't want to
>> make it obvious that it's just an incrementing value (think cookies and
>> websessions).
> If you're only generating one at a time, why not just use RAND()?
>|||Hasani (remove nospam from address) wrote:
> We'll there's a stored procedure someone created that uses RAND to
> create a unique integer, by storing all values created by the stored
> proc in a table, to stop duplicates, but, unfortunately, when a
> record is deleted that has a value generated by the stored procedure,
> it doesn't remove the generated value from the lookup table used by
> the stored procedure. That's the only reason why I'm was RAND, but I
> can modify the code to make sure deleted records 'release' the
> generated RAND value. But I do like the uid because it's
> alphanumeric, which is secure more in a cookie, well in cracking
> time, than an all numeric cookie.
>
You could add a computed column to the table to do the same thing (which
would eliminate the overhead of using a uniqueidentifier altogether).
And you can start the identity value higher if you don't want it to
start at 0.
Something like:
Create Table #test (
ID INT IDENTITY NOT NULL,
SomeText nvarchar(10),
"CookieID" as N'ALPHA-STUFF' + RIGHT(N'0000000000' + CAST(ID as
NVARCHAR(10)), 10))
Insert into #test values ('ABC')
Insert into #test values ('ABC')
Insert into #test values ('ABC')
Insert into #test values ('ABC')
Insert into #test values ('ABC')
Select * from #test
David G.
perform aggregate function & group by
Hello, anyone can help?
below SQL works in sybase but fail in sql2000. sql2000 show error on the sum
of netweight (cannot perform aggregate function) and group by commodity_code
(invalid column name !!)
SELECT
(select HTS
from bur_inv_item_list
where item = material
and plantcode = plant) as commodity_code,
(select DESCRIPTION
from bur_inv_item_list
where item = material
and plantcode = plant) as description,
uom as uom,
(select Orig
from bur_inv_item_list
where item = material
and plantcode = plant) as coo,
netweight =
sum(case when plant = '0014' or plant = '0024' then
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant), 2)
else
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant) /2.20462, 2)
end),
sum(ext_cost) as cost
from bur_inv_cntr_list
group by
commodity_code,
description,
uom,
coo
order by
commodity_code,
description,
uom,
coo
;The logical evaluation order of a SELECT statement is (top to bottom):
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP
As you see, the SELECT hasn't happened yet when the GROUP BY is performed. T
his mean that you cannot
refer to any column alias name in the GROUP BY clause. Some product diverts
from the ANSI SQL
standard behavior, SQL Server does not (in this regard). So push the express
ions in a derived table
and work against that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Hello, anyone can help?
>
> below SQL works in sybase but fail in sql2000. sql2000 show error on the s
um of netweight (cannot
> perform aggregate function) and group by commodity_code (invalid column na
me !!)
>
> SELECT
> (select HTS
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as commodity_code,
> (select DESCRIPTION
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as description,
> uom as uom,
> (select Orig
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as coo,
> netweight =
> sum(case when plant = '0014' or plant = '0024' then
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant), 2)
> else
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant) /2.20462, 2)
> end),
> sum(ext_cost) as cost
> from bur_inv_cntr_list
> group by
> commodity_code,
> description,
> uom,
> coo
> order by
> commodity_code,
> description,
> uom,
> coo
> ;
>|||hi tibor,
thanks for your info. do you have any sample/case. I want to see the sample
to modify below script.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
> The logical evaluation order of a SELECT statement is (top to bottom):
> FROM
> WHERE
> GROUP BY
> HAVING
> SELECT
> ORDER BY
> TOP
> As you see, the SELECT hasn't happened yet when the GROUP BY is performed.
> This mean that you cannot refer to any column alias name in the GROUP BY
> clause. Some product diverts from the ANSI SQL standard behavior, SQL
> Server does not (in this regard). So push the expressions in a derived
> table and work against that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Here's a very simple example where a derived table is used so you don't have
to repeat the DATEPART
expression:
USE pubs
SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
FROM titles
GROUP BY DATEPART(mm, pubdate)
SELECT pub_month, COUNT(*) AS titles
FROM
(
SELECT DATEPART(mm, pubdate) AS pub_month
FROM titles
) AS i
GROUP BY pub_month
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> hi tibor,
> thanks for your info. do you have any sample/case. I want to see the sampl
e to modify below
> script.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Many Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebF1yHSaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Here's a very simple example where a derived table is used so you don't
> have to repeat the DATEPART expression:
> USE pubs
> SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
> FROM titles
> GROUP BY DATEPART(mm, pubdate)
> SELECT pub_month, COUNT(*) AS titles
> FROM
> (
> SELECT DATEPART(mm, pubdate) AS pub_month
> FROM titles
> ) AS i
> GROUP BY pub_month
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>
below SQL works in sybase but fail in sql2000. sql2000 show error on the sum
of netweight (cannot perform aggregate function) and group by commodity_code
(invalid column name !!)
SELECT
(select HTS
from bur_inv_item_list
where item = material
and plantcode = plant) as commodity_code,
(select DESCRIPTION
from bur_inv_item_list
where item = material
and plantcode = plant) as description,
uom as uom,
(select Orig
from bur_inv_item_list
where item = material
and plantcode = plant) as coo,
netweight =
sum(case when plant = '0014' or plant = '0024' then
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant), 2)
else
round(qty * (select weight
from bur_inv_item_list
where item = material
and plantcode = plant) /2.20462, 2)
end),
sum(ext_cost) as cost
from bur_inv_cntr_list
group by
commodity_code,
description,
uom,
coo
order by
commodity_code,
description,
uom,
coo
;The logical evaluation order of a SELECT statement is (top to bottom):
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
TOP
As you see, the SELECT hasn't happened yet when the GROUP BY is performed. T
his mean that you cannot
refer to any column alias name in the GROUP BY clause. Some product diverts
from the ANSI SQL
standard behavior, SQL Server does not (in this regard). So push the express
ions in a derived table
and work against that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Hello, anyone can help?
>
> below SQL works in sybase but fail in sql2000. sql2000 show error on the s
um of netweight (cannot
> perform aggregate function) and group by commodity_code (invalid column na
me !!)
>
> SELECT
> (select HTS
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as commodity_code,
> (select DESCRIPTION
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as description,
> uom as uom,
> (select Orig
> from bur_inv_item_list
> where item = material
> and plantcode = plant) as coo,
> netweight =
> sum(case when plant = '0014' or plant = '0024' then
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant), 2)
> else
> round(qty * (select weight
> from bur_inv_item_list
> where item = material
> and plantcode = plant) /2.20462, 2)
> end),
> sum(ext_cost) as cost
> from bur_inv_cntr_list
> group by
> commodity_code,
> description,
> uom,
> coo
> order by
> commodity_code,
> description,
> uom,
> coo
> ;
>|||hi tibor,
thanks for your info. do you have any sample/case. I want to see the sample
to modify below script.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
> The logical evaluation order of a SELECT statement is (top to bottom):
> FROM
> WHERE
> GROUP BY
> HAVING
> SELECT
> ORDER BY
> TOP
> As you see, the SELECT hasn't happened yet when the GROUP BY is performed.
> This mean that you cannot refer to any column alias name in the GROUP BY
> clause. Some product diverts from the ANSI SQL standard behavior, SQL
> Server does not (in this regard). So push the expressions in a derived
> table and work against that.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:emgGyQOaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>|||Here's a very simple example where a derived table is used so you don't have
to repeat the DATEPART
expression:
USE pubs
SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
FROM titles
GROUP BY DATEPART(mm, pubdate)
SELECT pub_month, COUNT(*) AS titles
FROM
(
SELECT DATEPART(mm, pubdate) AS pub_month
FROM titles
) AS i
GROUP BY pub_month
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> hi tibor,
> thanks for your info. do you have any sample/case. I want to see the sampl
e to modify below
> script.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:u3R1jsPaGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||Hi Tibor,
Many Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ebF1yHSaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Here's a very simple example where a derived table is used so you don't
> have to repeat the DATEPART expression:
> USE pubs
> SELECT DATEPART(mm, pubdate) AS pub_month, COUNT(*) AS no_of_titles
> FROM titles
> GROUP BY DATEPART(mm, pubdate)
> SELECT pub_month, COUNT(*) AS titles
> FROM
> (
> SELECT DATEPART(mm, pubdate) AS pub_month
> FROM titles
> ) AS i
> GROUP BY pub_month
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OqUKBdRaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>
Subscribe to:
Posts (Atom)