It depends what you want to do. Perhaps you could populate an in-memory store of the data to prevent the round-trip each time.
Are you sure that cannot be achieved with LOOKUP component?
-Jamie
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Tuesday, March 27, 2012
access datasource in script component
I'm trying to access a group of rows in a data source in my script component (transformation). Currently im using the connection manager in the script component, acquire the connection, and issue a query command to get the data. This will happen on every rows being pass to the script component from the data source. Is there a better way to access the a group of rows from the data source?
Sunday, March 25, 2012
Access Crashes with CONTAINS in ServerFilter
Please excuse the crosspost from the Access forms group.
We are trying to implement a keyword search functionality in an Access 2002
project. We have set up SQL Server 2000 for full-text searching. Testing
SQL Server through Query Analyzer produces the expected results.
In a search tab on a form in an Access project, we dynamically construct a
WHERE clause for a subform. A keyword search is implemented by adding the
CONTAINS predicate to the ServerFilter string.
Once constructed, the ServerFilter is then applied to the subform and the
subform requeried to display the search's results. In testing, when we enter
criteria into the keyword field, Access crashes with no preceeding error
message. The crash occurs on the Requery statement for the subform. On the
screen, the subform fields all go blank, one gets the #Name error appearing
in it, and then the "Access has encountered . . . " message appears. An
example of an exact ServerFilter string producing the error is
"CONTAINS(*,'data')".
The documentation with Access says that the ServerFilter property is a WHERE
clause (without the WHERE). Is there an undocumented limitation that
predicates like CONTAINS cannot be used in the ServerFilter string, or am I
missing something? Any help appreciated.
can you run profiler on SQL Server to see what the sql command access is
running on SQL Server is.
if you can run this command in query analyzer, the problem is not with SQL
Server FTS, but rather with Access.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Smith" <PGCS@.online.nospam> wrote in message
news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> Please excuse the crosspost from the Access forms group.
> We are trying to implement a keyword search functionality in an Access
2002
> project. We have set up SQL Server 2000 for full-text searching.
Testing
> SQL Server through Query Analyzer produces the expected results.
> In a search tab on a form in an Access project, we dynamically construct a
> WHERE clause for a subform. A keyword search is implemented by adding the
> CONTAINS predicate to the ServerFilter string.
> Once constructed, the ServerFilter is then applied to the subform and the
> subform requeried to display the search's results. In testing, when we
enter
> criteria into the keyword field, Access crashes with no preceeding error
> message. The crash occurs on the Requery statement for the subform. On
the
> screen, the subform fields all go blank, one gets the #Name error
appearing
> in it, and then the "Access has encountered . . . " message appears. An
> example of an exact ServerFilter string producing the error is
> "CONTAINS(*,'data')".
> The documentation with Access says that the ServerFilter property is a
WHERE
> clause (without the WHERE). Is there an undocumented limitation that
> predicates like CONTAINS cannot be used in the ServerFilter string, or am
I
> missing something? Any help appreciated.
|||The culprit is definitely Access. About that there was really no question.
I am hoping someone who uses SQL Server full text searching may have
encountered this problem with Access and know how to resolve it. Access is
producing a string of SQL SELECT statements (54 to be exact) which are in
the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
With each successive call by Access, an additional set of parentheses is
added around the CONTAINS predicate.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> can you run profiler on SQL Server to see what the sql command access is
> running on SQL Server is.
> if you can run this command in query analyzer, the problem is not with SQL
> Server FTS, but rather with Access.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Smith" <PGCS@.online.nospam> wrote in message
> news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> 2002
> Testing
a[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
> enter
> the
> appearing
> WHERE
am
> I
>
|||Paul,
To the best of my knowledge, no one has reported a similar problem via this
newsgroup and I've been replying and monitoring it since it's inception in
late 2000. I'd recommend that you open a support case with Microsoft PSS SQL
Server &/or Access support to see if they have had past customers who have
encountered this possible bug. If you do, could you post back here what the
result was?
Other than the multiple additional set of parentheses that Access is adding,
can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
'string') via Access and if so do you get the same error? Is your goal to
issue SQL FTS queries to SQL Server as the backend to an Access front-end?
Thanks,
John
"Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> The culprit is definitely Access. About that there was really no
question.
> I am hoping someone who uses SQL Server full text searching may have
> encountered this problem with Access and know how to resolve it. Access
is[vbcol=seagreen]
> producing a string of SQL SELECT statements (54 to be exact) which are in
> the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
> With each successive call by Access, an additional set of parentheses is
> added around the CONTAINS predicate.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...
SQL[vbcol=seagreen]
construct[vbcol=seagreen]
> a
> the
> the
we[vbcol=seagreen]
error[vbcol=seagreen]
On[vbcol=seagreen]
An
> am
>
|||For anyone else who might encounter this problem: we seem to have resolved
the problem here. One of our developers noticed that we had gotten angled
quotation mark characters rather than the typical straight apostrophe as a
string delimiter. We aren't quite sure how this happened, but once this was
corrected, things seemed to start working correctly.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23Ic$xQxiEHA.2812@.tk2msftngp13.phx.gbl...
> Paul,
> To the best of my knowledge, no one has reported a similar problem via
this
> newsgroup and I've been replying and monitoring it since it's inception in
> late 2000. I'd recommend that you open a support case with Microsoft PSS
SQL
> Server &/or Access support to see if they have had past customers who have
> encountered this possible bug. If you do, could you post back here what
the
> result was?
> Other than the multiple additional set of parentheses that Access is
adding,[vbcol=seagreen]
> can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
> 'string') via Access and if so do you get the same error? Is your goal to
> issue SQL FTS queries to SQL Server as the backend to an Access front-end?
> Thanks,
> John
>
> "Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
> news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> question.
> is
in[vbcol=seagreen]
is[vbcol=seagreen]
> SQL
Access[vbcol=seagreen]
> construct
adding[vbcol=seagreen]
and[vbcol=seagreen]
> we
> error
> On
> An
a[vbcol=seagreen]
that[vbcol=seagreen]
or
>
We are trying to implement a keyword search functionality in an Access 2002
project. We have set up SQL Server 2000 for full-text searching. Testing
SQL Server through Query Analyzer produces the expected results.
In a search tab on a form in an Access project, we dynamically construct a
WHERE clause for a subform. A keyword search is implemented by adding the
CONTAINS predicate to the ServerFilter string.
Once constructed, the ServerFilter is then applied to the subform and the
subform requeried to display the search's results. In testing, when we enter
criteria into the keyword field, Access crashes with no preceeding error
message. The crash occurs on the Requery statement for the subform. On the
screen, the subform fields all go blank, one gets the #Name error appearing
in it, and then the "Access has encountered . . . " message appears. An
example of an exact ServerFilter string producing the error is
"CONTAINS(*,'data')".
The documentation with Access says that the ServerFilter property is a WHERE
clause (without the WHERE). Is there an undocumented limitation that
predicates like CONTAINS cannot be used in the ServerFilter string, or am I
missing something? Any help appreciated.
can you run profiler on SQL Server to see what the sql command access is
running on SQL Server is.
if you can run this command in query analyzer, the problem is not with SQL
Server FTS, but rather with Access.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Smith" <PGCS@.online.nospam> wrote in message
news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> Please excuse the crosspost from the Access forms group.
> We are trying to implement a keyword search functionality in an Access
2002
> project. We have set up SQL Server 2000 for full-text searching.
Testing
> SQL Server through Query Analyzer produces the expected results.
> In a search tab on a form in an Access project, we dynamically construct a
> WHERE clause for a subform. A keyword search is implemented by adding the
> CONTAINS predicate to the ServerFilter string.
> Once constructed, the ServerFilter is then applied to the subform and the
> subform requeried to display the search's results. In testing, when we
enter
> criteria into the keyword field, Access crashes with no preceeding error
> message. The crash occurs on the Requery statement for the subform. On
the
> screen, the subform fields all go blank, one gets the #Name error
appearing
> in it, and then the "Access has encountered . . . " message appears. An
> example of an exact ServerFilter string producing the error is
> "CONTAINS(*,'data')".
> The documentation with Access says that the ServerFilter property is a
WHERE
> clause (without the WHERE). Is there an undocumented limitation that
> predicates like CONTAINS cannot be used in the ServerFilter string, or am
I
> missing something? Any help appreciated.
|||The culprit is definitely Access. About that there was really no question.
I am hoping someone who uses SQL Server full text searching may have
encountered this problem with Access and know how to resolve it. Access is
producing a string of SQL SELECT statements (54 to be exact) which are in
the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
With each successive call by Access, an additional set of parentheses is
added around the CONTAINS predicate.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> can you run profiler on SQL Server to see what the sql command access is
> running on SQL Server is.
> if you can run this command in query analyzer, the problem is not with SQL
> Server FTS, but rather with Access.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Smith" <PGCS@.online.nospam> wrote in message
> news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> 2002
> Testing
a[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
> enter
> the
> appearing
> WHERE
am
> I
>
|||Paul,
To the best of my knowledge, no one has reported a similar problem via this
newsgroup and I've been replying and monitoring it since it's inception in
late 2000. I'd recommend that you open a support case with Microsoft PSS SQL
Server &/or Access support to see if they have had past customers who have
encountered this possible bug. If you do, could you post back here what the
result was?
Other than the multiple additional set of parentheses that Access is adding,
can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
'string') via Access and if so do you get the same error? Is your goal to
issue SQL FTS queries to SQL Server as the backend to an Access front-end?
Thanks,
John
"Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> The culprit is definitely Access. About that there was really no
question.
> I am hoping someone who uses SQL Server full text searching may have
> encountered this problem with Access and know how to resolve it. Access
is[vbcol=seagreen]
> producing a string of SQL SELECT statements (54 to be exact) which are in
> the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
> With each successive call by Access, an additional set of parentheses is
> added around the CONTAINS predicate.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...
SQL[vbcol=seagreen]
construct[vbcol=seagreen]
> a
> the
> the
we[vbcol=seagreen]
error[vbcol=seagreen]
On[vbcol=seagreen]
An
> am
>
|||For anyone else who might encounter this problem: we seem to have resolved
the problem here. One of our developers noticed that we had gotten angled
quotation mark characters rather than the typical straight apostrophe as a
string delimiter. We aren't quite sure how this happened, but once this was
corrected, things seemed to start working correctly.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23Ic$xQxiEHA.2812@.tk2msftngp13.phx.gbl...
> Paul,
> To the best of my knowledge, no one has reported a similar problem via
this
> newsgroup and I've been replying and monitoring it since it's inception in
> late 2000. I'd recommend that you open a support case with Microsoft PSS
SQL
> Server &/or Access support to see if they have had past customers who have
> encountered this possible bug. If you do, could you post back here what
the
> result was?
> Other than the multiple additional set of parentheses that Access is
adding,[vbcol=seagreen]
> can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
> 'string') via Access and if so do you get the same error? Is your goal to
> issue SQL FTS queries to SQL Server as the backend to an Access front-end?
> Thanks,
> John
>
> "Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
> news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> question.
> is
in[vbcol=seagreen]
is[vbcol=seagreen]
> SQL
Access[vbcol=seagreen]
> construct
adding[vbcol=seagreen]
and[vbcol=seagreen]
> we
> error
> On
> An
a[vbcol=seagreen]
that[vbcol=seagreen]
or
>
Tuesday, March 20, 2012
Access 200x SQL Server 2000 Comparison
Hi All,
This might not be the right discussion group but I'm having diffuclty
finding documentation, so here is the question. My company uses Access 2000
extensively to create local databases to support some data manipulation and
conversion. Sorry I can't go into detail. Recently we decided to migrate
all of the applications to .NET and therefore it seems logical to migrate to
SQL Server, but other than MSDE the costs would be prohibitive, since we
currently have hundreds of Access databases barring a process change, which
is not likely in the near-term. Needless to say the challenge is defending a
recommendation to move to SQL Server.
Is there a documented comparison between SQL Server and Access other than
the obvious concurrent user, maximum physical memory and database size
advantages you gain with SQL Server?
Any help or guidance is greatly appreciated.
Thanks,
EE_Developer
None that aren't *heavily* biased towards SQL Server. We're working on it
though.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
> 2000
> extensively to create local databases to support some data manipulation
> and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
> to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
> which
> is not likely in the near-term. Needless to say the challenge is
> defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer
|||Hundreds of Access database? It is most likely that many of those hundreds
Access databases have the same or similar table structure and can ans should
be merged. I cannot imagine a business need that many different databases
(if they are not designed duplicated), unless the business is huge one (not
the one that cannot afford SQL Server for $10000).
So, if your business really need many different database (say, 10 to 20),
and each has not too many user access, MSDE may serve your very well. OTH,
after careful analysis, those hundreds small database can be merged into a
couple of central database and you have lot of users (hsay, undreds, or
thousands) then SQL Server is the choice over Access. Take the time needed
to develop and migrate into account, the cost on SQL Server itself really a
very small portion of the cost. Even you do not go with SQL Server, you
probably still need to change the "hundreds Access databases" situation with
a few shared Access databases (again, not knowing your business's size, not
sure Access is still viable option, but possibility is there), the cost of
developing/transferring is far more than SQL Server cost.
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
2000
> extensively to create local databases to support some data manipulation
and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
which
> is not likely in the near-term. Needless to say the challenge is
defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer
|||Hi
The route through here maybe to use the new sql express 2005 (still in
beta) this is free and has quite a large db size limitation, as yet no
user count limit, this information could change in the future, but I
think the move here is to get almost everyone over to a SQL based
engine rather than access type storage. You will struggle to find any
documentation that is as Graham has said not biased toward SQL. I spend
a lot of my time moving access databases into SQL server, and my two
main selling points are performance and reliability. I use lines like
'After the upgrade you won't need me comming in to fix this anymore'
and I get the 'WOW factor' (if I deliver a new app I expect a WOW
that's so much faster than the old version). I know that this does not
answer your question but I can say that the move has always had a very
positive effect on the users who use the systems that I upgrade to SQL.
Its like driving to work in a Mini when you have a Rolls Royce in the
drive.
Regards
Alex
|||Hello EE_Developer:
You wrote in conference
microsoft.public.access.adp.sqlserver,microsoft.pu blic.sqlserver.server on
Fri, 15 Apr 2005 07:09:13 -0700:
ED> Hi All,
ED> This might not be the right discussion group but I'm having
ED> diffuclty finding documentation, so here is the question. My company
ED> uses Access 2000 extensively to create local databases to support some
ED> data manipulation and conversion. Sorry I can't go into detail.
ED> Recently we decided to migrate all of the applications to .NET and
ED> therefore it seems logical to migrate to SQL Server, but other than
ED> MSDE the costs would be prohibitive, since we currently have hundreds
ED> of Access databases barring a process change, which is not likely in
ED> the near-term. Needless to say the challenge is defending a
ED> recommendation to move to SQL Server.
ED> Is there a documented comparison between SQL Server and Access other
ED> than the obvious concurrent user, maximum physical memory and database
ED> size advantages you gain with SQL Server?
I would start with calculation of the company expenses on supporting those
hundreds of Access databases. Hundreds of them most likely have thousands of
duplicate data instances and hundreds of applications that synchronize the
data between them, which you need to support. Must be quite a nightmare,
thus possible to defend not only moving to sql server, but major rethinking
and redesign of the core processes.
Vadim Rapp
|||tr65
"Vadim Rapp" <vr@.myrealbox.nospam.com> wrote in message
news:eix4gc7QFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hello EE_Developer:
> You wrote in conference
> microsoft.public.access.adp.sqlserver,microsoft.pu blic.sqlserver.server
on
> Fri, 15 Apr 2005 07:09:13 -0700:
> ED> Hi All,
> ED> This might not be the right discussion group but I'm having
> ED> diffuclty finding documentation, so here is the question. My
company
> ED> uses Access 2000 extensively to create local databases to support
some
> ED> data manipulation and conversion. Sorry I can't go into detail.
> ED> Recently we decided to migrate all of the applications to .NET and
> ED> therefore it seems logical to migrate to SQL Server, but other than
> ED> MSDE the costs would be prohibitive, since we currently have hundreds
> ED> of Access databases barring a process change, which is not likely in
> ED> the near-term. Needless to say the challenge is defending a
> ED> recommendation to move to SQL Server.
> ED> Is there a documented comparison between SQL Server and Access other
> ED> than the obvious concurrent user, maximum physical memory and
database
> ED> size advantages you gain with SQL Server?
> I would start with calculation of the company expenses on supporting those
> hundreds of Access databases. Hundreds of them most likely have thousands
of
> duplicate data instances and hundreds of applications that synchronize the
> data between them, which you need to support. Must be quite a nightmare,
> thus possible to defend not only moving to sql server, but major
rethinking
> and redesign of the core processes.
> Vadim Rapp
>
This might not be the right discussion group but I'm having diffuclty
finding documentation, so here is the question. My company uses Access 2000
extensively to create local databases to support some data manipulation and
conversion. Sorry I can't go into detail. Recently we decided to migrate
all of the applications to .NET and therefore it seems logical to migrate to
SQL Server, but other than MSDE the costs would be prohibitive, since we
currently have hundreds of Access databases barring a process change, which
is not likely in the near-term. Needless to say the challenge is defending a
recommendation to move to SQL Server.
Is there a documented comparison between SQL Server and Access other than
the obvious concurrent user, maximum physical memory and database size
advantages you gain with SQL Server?
Any help or guidance is greatly appreciated.
Thanks,
EE_Developer
None that aren't *heavily* biased towards SQL Server. We're working on it
though.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
> 2000
> extensively to create local databases to support some data manipulation
> and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
> to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
> which
> is not likely in the near-term. Needless to say the challenge is
> defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer
|||Hundreds of Access database? It is most likely that many of those hundreds
Access databases have the same or similar table structure and can ans should
be merged. I cannot imagine a business need that many different databases
(if they are not designed duplicated), unless the business is huge one (not
the one that cannot afford SQL Server for $10000).
So, if your business really need many different database (say, 10 to 20),
and each has not too many user access, MSDE may serve your very well. OTH,
after careful analysis, those hundreds small database can be merged into a
couple of central database and you have lot of users (hsay, undreds, or
thousands) then SQL Server is the choice over Access. Take the time needed
to develop and migrate into account, the cost on SQL Server itself really a
very small portion of the cost. Even you do not go with SQL Server, you
probably still need to change the "hundreds Access databases" situation with
a few shared Access databases (again, not knowing your business's size, not
sure Access is still viable option, but possibility is there), the cost of
developing/transferring is far more than SQL Server cost.
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
2000
> extensively to create local databases to support some data manipulation
and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
which
> is not likely in the near-term. Needless to say the challenge is
defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer
|||Hi
The route through here maybe to use the new sql express 2005 (still in
beta) this is free and has quite a large db size limitation, as yet no
user count limit, this information could change in the future, but I
think the move here is to get almost everyone over to a SQL based
engine rather than access type storage. You will struggle to find any
documentation that is as Graham has said not biased toward SQL. I spend
a lot of my time moving access databases into SQL server, and my two
main selling points are performance and reliability. I use lines like
'After the upgrade you won't need me comming in to fix this anymore'
and I get the 'WOW factor' (if I deliver a new app I expect a WOW
that's so much faster than the old version). I know that this does not
answer your question but I can say that the move has always had a very
positive effect on the users who use the systems that I upgrade to SQL.
Its like driving to work in a Mini when you have a Rolls Royce in the
drive.
Regards
Alex
|||Hello EE_Developer:
You wrote in conference
microsoft.public.access.adp.sqlserver,microsoft.pu blic.sqlserver.server on
Fri, 15 Apr 2005 07:09:13 -0700:
ED> Hi All,
ED> This might not be the right discussion group but I'm having
ED> diffuclty finding documentation, so here is the question. My company
ED> uses Access 2000 extensively to create local databases to support some
ED> data manipulation and conversion. Sorry I can't go into detail.
ED> Recently we decided to migrate all of the applications to .NET and
ED> therefore it seems logical to migrate to SQL Server, but other than
ED> MSDE the costs would be prohibitive, since we currently have hundreds
ED> of Access databases barring a process change, which is not likely in
ED> the near-term. Needless to say the challenge is defending a
ED> recommendation to move to SQL Server.
ED> Is there a documented comparison between SQL Server and Access other
ED> than the obvious concurrent user, maximum physical memory and database
ED> size advantages you gain with SQL Server?
I would start with calculation of the company expenses on supporting those
hundreds of Access databases. Hundreds of them most likely have thousands of
duplicate data instances and hundreds of applications that synchronize the
data between them, which you need to support. Must be quite a nightmare,
thus possible to defend not only moving to sql server, but major rethinking
and redesign of the core processes.
Vadim Rapp
|||tr65
"Vadim Rapp" <vr@.myrealbox.nospam.com> wrote in message
news:eix4gc7QFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hello EE_Developer:
> You wrote in conference
> microsoft.public.access.adp.sqlserver,microsoft.pu blic.sqlserver.server
on
> Fri, 15 Apr 2005 07:09:13 -0700:
> ED> Hi All,
> ED> This might not be the right discussion group but I'm having
> ED> diffuclty finding documentation, so here is the question. My
company
> ED> uses Access 2000 extensively to create local databases to support
some
> ED> data manipulation and conversion. Sorry I can't go into detail.
> ED> Recently we decided to migrate all of the applications to .NET and
> ED> therefore it seems logical to migrate to SQL Server, but other than
> ED> MSDE the costs would be prohibitive, since we currently have hundreds
> ED> of Access databases barring a process change, which is not likely in
> ED> the near-term. Needless to say the challenge is defending a
> ED> recommendation to move to SQL Server.
> ED> Is there a documented comparison between SQL Server and Access other
> ED> than the obvious concurrent user, maximum physical memory and
database
> ED> size advantages you gain with SQL Server?
> I would start with calculation of the company expenses on supporting those
> hundreds of Access databases. Hundreds of them most likely have thousands
of
> duplicate data instances and hundreds of applications that synchronize the
> data between them, which you need to support. Must be quite a nightmare,
> thus possible to defend not only moving to sql server, but major
rethinking
> and redesign of the core processes.
> Vadim Rapp
>
Access 200x SQL Server 2000 Comparison
Hi All,
This might not be the right discussion group but I'm having diffuclty
finding documentation, so here is the question. My company uses Access 200
0
extensively to create local databases to support some data manipulation and
conversion. Sorry I can't go into detail. Recently we decided to migrate
all of the applications to .NET and therefore it seems logical to migrate to
SQL Server, but other than MSDE the costs would be prohibitive, since we
currently have hundreds of Access databases barring a process change, which
is not likely in the near-term. Needless to say the challenge is defending
a
recommendation to move to SQL Server.
Is there a documented comparison between SQL Server and Access other than
the obvious concurrent user, maximum physical memory and database size
advantages you gain with SQL Server?
Any help or guidance is greatly appreciated.
--
Thanks,
EE_DeveloperNone that aren't *heavily* biased towards SQL Server. We're working on it
though.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
--
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
> 2000
> extensively to create local databases to support some data manipulation
> and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
> to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
> which
> is not likely in the near-term. Needless to say the challenge is
> defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer|||Hundreds of Access database? It is most likely that many of those hundreds
Access databases have the same or similar table structure and can ans should
be merged. I cannot imagine a business need that many different databases
(if they are not designed duplicated), unless the business is huge one (not
the one that cannot afford SQL Server for $10000).
So, if your business really need many different database (say, 10 to 20),
and each has not too many user access, MSDE may serve your very well. OTH,
after careful analysis, those hundreds small database can be merged into a
couple of central database and you have lot of users (hsay, undreds, or
thousands) then SQL Server is the choice over Access. Take the time needed
to develop and migrate into account, the cost on SQL Server itself really a
very small portion of the cost. Even you do not go with SQL Server, you
probably still need to change the "hundreds Access databases" situation with
a few shared Access databases (again, not knowing your business's size, not
sure Access is still viable option, but possibility is there), the cost of
developing/transferring is far more than SQL Server cost.
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
2000
> extensively to create local databases to support some data manipulation
and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
which
> is not likely in the near-term. Needless to say the challenge is
defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer|||Hi
The route through here maybe to use the new sql express 2005 (still in
beta) this is free and has quite a large db size limitation, as yet no
user count limit, this information could change in the future, but I
think the move here is to get almost everyone over to a SQL based
engine rather than access type storage. You will struggle to find any
documentation that is as Graham has said not biased toward SQL. I spend
a lot of my time moving access databases into SQL server, and my two
main selling points are performance and reliability. I use lines like
'After the upgrade you won't need me comming in to fix this anymore'
and I get the 'WOW factor' (if I deliver a new app I expect a WOW
that's so much faster than the old version). I know that this does not
answer your question but I can say that the move has always had a very
positive effect on the users who use the systems that I upgrade to SQL.
Its like driving to work in a Mini when you have a Rolls Royce in the
drive.
Regards
Alex|||Hello EE_Developer:
You wrote in conference
microsoft.public.access.adp.sqlserver,microsoft.public.sqlserver.server on
Fri, 15 Apr 2005 07:09:13 -0700:
ED> Hi All,
ED> This might not be the right discussion group but I'm having
ED> diffuclty finding documentation, so here is the question. My company
ED> uses Access 2000 extensively to create local databases to support some
ED> data manipulation and conversion. Sorry I can't go into detail.
ED> Recently we decided to migrate all of the applications to .NET and
ED> therefore it seems logical to migrate to SQL Server, but other than
ED> MSDE the costs would be prohibitive, since we currently have hundreds
ED> of Access databases barring a process change, which is not likely in
ED> the near-term. Needless to say the challenge is defending a
ED> recommendation to move to SQL Server.
ED> Is there a documented comparison between SQL Server and Access other
ED> than the obvious concurrent user, maximum physical memory and database
ED> size advantages you gain with SQL Server?
I would start with calculation of the company expenses on supporting those
hundreds of Access databases. Hundreds of them most likely have thousands of
duplicate data instances and hundreds of applications that synchronize the
data between them, which you need to support. Must be quite a nightmare,
thus possible to defend not only moving to sql server, but major rethinking
and redesign of the core processes.
Vadim Rapp
This might not be the right discussion group but I'm having diffuclty
finding documentation, so here is the question. My company uses Access 200
0
extensively to create local databases to support some data manipulation and
conversion. Sorry I can't go into detail. Recently we decided to migrate
all of the applications to .NET and therefore it seems logical to migrate to
SQL Server, but other than MSDE the costs would be prohibitive, since we
currently have hundreds of Access databases barring a process change, which
is not likely in the near-term. Needless to say the challenge is defending
a
recommendation to move to SQL Server.
Is there a documented comparison between SQL Server and Access other than
the obvious concurrent user, maximum physical memory and database size
advantages you gain with SQL Server?
Any help or guidance is greatly appreciated.
--
Thanks,
EE_DeveloperNone that aren't *heavily* biased towards SQL Server. We're working on it
though.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
--
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
> 2000
> extensively to create local databases to support some data manipulation
> and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
> to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
> which
> is not likely in the near-term. Needless to say the challenge is
> defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer|||Hundreds of Access database? It is most likely that many of those hundreds
Access databases have the same or similar table structure and can ans should
be merged. I cannot imagine a business need that many different databases
(if they are not designed duplicated), unless the business is huge one (not
the one that cannot afford SQL Server for $10000).
So, if your business really need many different database (say, 10 to 20),
and each has not too many user access, MSDE may serve your very well. OTH,
after careful analysis, those hundreds small database can be merged into a
couple of central database and you have lot of users (hsay, undreds, or
thousands) then SQL Server is the choice over Access. Take the time needed
to develop and migrate into account, the cost on SQL Server itself really a
very small portion of the cost. Even you do not go with SQL Server, you
probably still need to change the "hundreds Access databases" situation with
a few shared Access databases (again, not knowing your business's size, not
sure Access is still viable option, but possibility is there), the cost of
developing/transferring is far more than SQL Server cost.
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
2000
> extensively to create local databases to support some data manipulation
and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
which
> is not likely in the near-term. Needless to say the challenge is
defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer|||Hi
The route through here maybe to use the new sql express 2005 (still in
beta) this is free and has quite a large db size limitation, as yet no
user count limit, this information could change in the future, but I
think the move here is to get almost everyone over to a SQL based
engine rather than access type storage. You will struggle to find any
documentation that is as Graham has said not biased toward SQL. I spend
a lot of my time moving access databases into SQL server, and my two
main selling points are performance and reliability. I use lines like
'After the upgrade you won't need me comming in to fix this anymore'
and I get the 'WOW factor' (if I deliver a new app I expect a WOW
that's so much faster than the old version). I know that this does not
answer your question but I can say that the move has always had a very
positive effect on the users who use the systems that I upgrade to SQL.
Its like driving to work in a Mini when you have a Rolls Royce in the
drive.
Regards
Alex|||Hello EE_Developer:
You wrote in conference
microsoft.public.access.adp.sqlserver,microsoft.public.sqlserver.server on
Fri, 15 Apr 2005 07:09:13 -0700:
ED> Hi All,
ED> This might not be the right discussion group but I'm having
ED> diffuclty finding documentation, so here is the question. My company
ED> uses Access 2000 extensively to create local databases to support some
ED> data manipulation and conversion. Sorry I can't go into detail.
ED> Recently we decided to migrate all of the applications to .NET and
ED> therefore it seems logical to migrate to SQL Server, but other than
ED> MSDE the costs would be prohibitive, since we currently have hundreds
ED> of Access databases barring a process change, which is not likely in
ED> the near-term. Needless to say the challenge is defending a
ED> recommendation to move to SQL Server.
ED> Is there a documented comparison between SQL Server and Access other
ED> than the obvious concurrent user, maximum physical memory and database
ED> size advantages you gain with SQL Server?
I would start with calculation of the company expenses on supporting those
hundreds of Access databases. Hundreds of them most likely have thousands of
duplicate data instances and hundreds of applications that synchronize the
data between them, which you need to support. Must be quite a nightmare,
thus possible to defend not only moving to sql server, but major rethinking
and redesign of the core processes.
Vadim Rapp
Access 200x SQL Server 2000 Comparison
Hi All,
This might not be the right discussion group but I'm having diffuclty
finding documentation, so here is the question. My company uses Access 2000
extensively to create local databases to support some data manipulation and
conversion. Sorry I can't go into detail. Recently we decided to migrate
all of the applications to .NET and therefore it seems logical to migrate to
SQL Server, but other than MSDE the costs would be prohibitive, since we
currently have hundreds of Access databases barring a process change, which
is not likely in the near-term. Needless to say the challenge is defending a
recommendation to move to SQL Server.
Is there a documented comparison between SQL Server and Access other than
the obvious concurrent user, maximum physical memory and database size
advantages you gain with SQL Server?
Any help or guidance is greatly appreciated.
--
Thanks,
EE_DeveloperNone that aren't *heavily* biased towards SQL Server. We're working on it
though.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
--
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
> 2000
> extensively to create local databases to support some data manipulation
> and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
> to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
> which
> is not likely in the near-term. Needless to say the challenge is
> defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer|||Hundreds of Access database? It is most likely that many of those hundreds
Access databases have the same or similar table structure and can ans should
be merged. I cannot imagine a business need that many different databases
(if they are not designed duplicated), unless the business is huge one (not
the one that cannot afford SQL Server for $10000).
So, if your business really need many different database (say, 10 to 20),
and each has not too many user access, MSDE may serve your very well. OTH,
after careful analysis, those hundreds small database can be merged into a
couple of central database and you have lot of users (hsay, undreds, or
thousands) then SQL Server is the choice over Access. Take the time needed
to develop and migrate into account, the cost on SQL Server itself really a
very small portion of the cost. Even you do not go with SQL Server, you
probably still need to change the "hundreds Access databases" situation with
a few shared Access databases (again, not knowing your business's size, not
sure Access is still viable option, but possibility is there), the cost of
developing/transferring is far more than SQL Server cost.
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
2000
> extensively to create local databases to support some data manipulation
and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
which
> is not likely in the near-term. Needless to say the challenge is
defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer|||Hi
The route through here maybe to use the new sql express 2005 (still in
beta) this is free and has quite a large db size limitation, as yet no
user count limit, this information could change in the future, but I
think the move here is to get almost everyone over to a SQL based
engine rather than access type storage. You will struggle to find any
documentation that is as Graham has said not biased toward SQL. I spend
a lot of my time moving access databases into SQL server, and my two
main selling points are performance and reliability. I use lines like
'After the upgrade you won't need me comming in to fix this anymore'
and I get the 'WOW factor' (if I deliver a new app I expect a WOW
that's so much faster than the old version). I know that this does not
answer your question but I can say that the move has always had a very
positive effect on the users who use the systems that I upgrade to SQL.
Its like driving to work in a Mini when you have a Rolls Royce in the
drive.
Regards
Alex|||Hello EE_Developer:
You wrote in conference
microsoft.public.access.adp.sqlserver,microsoft.public.sqlserver.server on
Fri, 15 Apr 2005 07:09:13 -0700:
ED> Hi All,
ED> This might not be the right discussion group but I'm having
ED> diffuclty finding documentation, so here is the question. My company
ED> uses Access 2000 extensively to create local databases to support some
ED> data manipulation and conversion. Sorry I can't go into detail.
ED> Recently we decided to migrate all of the applications to .NET and
ED> therefore it seems logical to migrate to SQL Server, but other than
ED> MSDE the costs would be prohibitive, since we currently have hundreds
ED> of Access databases barring a process change, which is not likely in
ED> the near-term. Needless to say the challenge is defending a
ED> recommendation to move to SQL Server.
ED> Is there a documented comparison between SQL Server and Access other
ED> than the obvious concurrent user, maximum physical memory and database
ED> size advantages you gain with SQL Server?
I would start with calculation of the company expenses on supporting those
hundreds of Access databases. Hundreds of them most likely have thousands of
duplicate data instances and hundreds of applications that synchronize the
data between them, which you need to support. Must be quite a nightmare,
thus possible to defend not only moving to sql server, but major rethinking
and redesign of the core processes.
Vadim Rapp|||tr65
"Vadim Rapp" <vr@.myrealbox.nospam.com> wrote in message
news:eix4gc7QFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hello EE_Developer:
> You wrote in conference
> microsoft.public.access.adp.sqlserver,microsoft.public.sqlserver.server
on
> Fri, 15 Apr 2005 07:09:13 -0700:
> ED> Hi All,
> ED> This might not be the right discussion group but I'm having
> ED> diffuclty finding documentation, so here is the question. My
company
> ED> uses Access 2000 extensively to create local databases to support
some
> ED> data manipulation and conversion. Sorry I can't go into detail.
> ED> Recently we decided to migrate all of the applications to .NET and
> ED> therefore it seems logical to migrate to SQL Server, but other than
> ED> MSDE the costs would be prohibitive, since we currently have hundreds
> ED> of Access databases barring a process change, which is not likely in
> ED> the near-term. Needless to say the challenge is defending a
> ED> recommendation to move to SQL Server.
> ED> Is there a documented comparison between SQL Server and Access other
> ED> than the obvious concurrent user, maximum physical memory and
database
> ED> size advantages you gain with SQL Server?
> I would start with calculation of the company expenses on supporting those
> hundreds of Access databases. Hundreds of them most likely have thousands
of
> duplicate data instances and hundreds of applications that synchronize the
> data between them, which you need to support. Must be quite a nightmare,
> thus possible to defend not only moving to sql server, but major
rethinking
> and redesign of the core processes.
> Vadim Rapp
>
This might not be the right discussion group but I'm having diffuclty
finding documentation, so here is the question. My company uses Access 2000
extensively to create local databases to support some data manipulation and
conversion. Sorry I can't go into detail. Recently we decided to migrate
all of the applications to .NET and therefore it seems logical to migrate to
SQL Server, but other than MSDE the costs would be prohibitive, since we
currently have hundreds of Access databases barring a process change, which
is not likely in the near-term. Needless to say the challenge is defending a
recommendation to move to SQL Server.
Is there a documented comparison between SQL Server and Access other than
the obvious concurrent user, maximum physical memory and database size
advantages you gain with SQL Server?
Any help or guidance is greatly appreciated.
--
Thanks,
EE_DeveloperNone that aren't *heavily* biased towards SQL Server. We're working on it
though.
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
--
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
> 2000
> extensively to create local databases to support some data manipulation
> and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
> to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
> which
> is not likely in the near-term. Needless to say the challenge is
> defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer|||Hundreds of Access database? It is most likely that many of those hundreds
Access databases have the same or similar table structure and can ans should
be merged. I cannot imagine a business need that many different databases
(if they are not designed duplicated), unless the business is huge one (not
the one that cannot afford SQL Server for $10000).
So, if your business really need many different database (say, 10 to 20),
and each has not too many user access, MSDE may serve your very well. OTH,
after careful analysis, those hundreds small database can be merged into a
couple of central database and you have lot of users (hsay, undreds, or
thousands) then SQL Server is the choice over Access. Take the time needed
to develop and migrate into account, the cost on SQL Server itself really a
very small portion of the cost. Even you do not go with SQL Server, you
probably still need to change the "hundreds Access databases" situation with
a few shared Access databases (again, not knowing your business's size, not
sure Access is still viable option, but possibility is there), the cost of
developing/transferring is far more than SQL Server cost.
"EE_Developer" <hfcd@.newsgroup.nospam> wrote in message
news:708DBFC7-36CC-4000-B4AF-58C85E4085EB@.microsoft.com...
> Hi All,
> This might not be the right discussion group but I'm having diffuclty
> finding documentation, so here is the question. My company uses Access
2000
> extensively to create local databases to support some data manipulation
and
> conversion. Sorry I can't go into detail. Recently we decided to migrate
> all of the applications to .NET and therefore it seems logical to migrate
to
> SQL Server, but other than MSDE the costs would be prohibitive, since we
> currently have hundreds of Access databases barring a process change,
which
> is not likely in the near-term. Needless to say the challenge is
defending a
> recommendation to move to SQL Server.
> Is there a documented comparison between SQL Server and Access other than
> the obvious concurrent user, maximum physical memory and database size
> advantages you gain with SQL Server?
> Any help or guidance is greatly appreciated.
> --
> Thanks,
> EE_Developer|||Hi
The route through here maybe to use the new sql express 2005 (still in
beta) this is free and has quite a large db size limitation, as yet no
user count limit, this information could change in the future, but I
think the move here is to get almost everyone over to a SQL based
engine rather than access type storage. You will struggle to find any
documentation that is as Graham has said not biased toward SQL. I spend
a lot of my time moving access databases into SQL server, and my two
main selling points are performance and reliability. I use lines like
'After the upgrade you won't need me comming in to fix this anymore'
and I get the 'WOW factor' (if I deliver a new app I expect a WOW
that's so much faster than the old version). I know that this does not
answer your question but I can say that the move has always had a very
positive effect on the users who use the systems that I upgrade to SQL.
Its like driving to work in a Mini when you have a Rolls Royce in the
drive.
Regards
Alex|||Hello EE_Developer:
You wrote in conference
microsoft.public.access.adp.sqlserver,microsoft.public.sqlserver.server on
Fri, 15 Apr 2005 07:09:13 -0700:
ED> Hi All,
ED> This might not be the right discussion group but I'm having
ED> diffuclty finding documentation, so here is the question. My company
ED> uses Access 2000 extensively to create local databases to support some
ED> data manipulation and conversion. Sorry I can't go into detail.
ED> Recently we decided to migrate all of the applications to .NET and
ED> therefore it seems logical to migrate to SQL Server, but other than
ED> MSDE the costs would be prohibitive, since we currently have hundreds
ED> of Access databases barring a process change, which is not likely in
ED> the near-term. Needless to say the challenge is defending a
ED> recommendation to move to SQL Server.
ED> Is there a documented comparison between SQL Server and Access other
ED> than the obvious concurrent user, maximum physical memory and database
ED> size advantages you gain with SQL Server?
I would start with calculation of the company expenses on supporting those
hundreds of Access databases. Hundreds of them most likely have thousands of
duplicate data instances and hundreds of applications that synchronize the
data between them, which you need to support. Must be quite a nightmare,
thus possible to defend not only moving to sql server, but major rethinking
and redesign of the core processes.
Vadim Rapp|||tr65
"Vadim Rapp" <vr@.myrealbox.nospam.com> wrote in message
news:eix4gc7QFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hello EE_Developer:
> You wrote in conference
> microsoft.public.access.adp.sqlserver,microsoft.public.sqlserver.server
on
> Fri, 15 Apr 2005 07:09:13 -0700:
> ED> Hi All,
> ED> This might not be the right discussion group but I'm having
> ED> diffuclty finding documentation, so here is the question. My
company
> ED> uses Access 2000 extensively to create local databases to support
some
> ED> data manipulation and conversion. Sorry I can't go into detail.
> ED> Recently we decided to migrate all of the applications to .NET and
> ED> therefore it seems logical to migrate to SQL Server, but other than
> ED> MSDE the costs would be prohibitive, since we currently have hundreds
> ED> of Access databases barring a process change, which is not likely in
> ED> the near-term. Needless to say the challenge is defending a
> ED> recommendation to move to SQL Server.
> ED> Is there a documented comparison between SQL Server and Access other
> ED> than the obvious concurrent user, maximum physical memory and
database
> ED> size advantages you gain with SQL Server?
> I would start with calculation of the company expenses on supporting those
> hundreds of Access databases. Hundreds of them most likely have thousands
of
> duplicate data instances and hundreds of applications that synchronize the
> data between them, which you need to support. Must be quite a nightmare,
> thus possible to defend not only moving to sql server, but major
rethinking
> and redesign of the core processes.
> Vadim Rapp
>
Sunday, March 11, 2012
Accesing Oracle from an assembly!
Hi Group!
Iâ've a big problem with MS Reporting Services, i try to access an oracle
database from a custom assembly, in desing view all work nice, but in
production environment, the call to the class fail!.
What i can do to correct this problem?
I added the dll file to de bin folder or reportserver and report designer,
but donâ't work.
Really I need help.
Thanks for the helpCarlos,
It sounds like a CAS issue (Code Access Security)
Make sure you define (in the RS Policy file) definitions of the location and
the name of the Custome Assembly DLL's and what Rights you want to assign to
it.
RS provides very limited default "trust" of Custom Assemblies, you'll need
to define specific additions to this to allow access to the database.
Btw: Although it can be done, it's a little unusual to access the database
from a Custom Assembly - I normally see it done as an extension like a data
procesing extension.
HTH
- peteZ
"Carlos López." <CarlosLpez@.discussions.microsoft.com> wrote in message
news:A591ABD5-0838-4B99-88A3-43FBBE27FD49@.microsoft.com...
> Hi Group!
> I've a big problem with MS Reporting Services, i try to access an oracle
> database from a custom assembly, in desing view all work nice, but in
> production environment, the call to the class fail!.
> What i can do to correct this problem?
> I added the dll file to de bin folder or reportserver and report designer,
> but don't work.
> Really I need help.
> Thanks for the help
>|||You will need to assert FullTrust permissions to use the Oracle data
provider from a custom assembly. Below is an example of how to assert full
trust in your custom assembly using an attribute on the method that opens an
Oracle connection:
[PermissionSet(SecurityAction.Assert, Unrestricted=true)]
public foo()
{
// your code
strConn = "Data Source=TSD01;User ID=SchemaName;password=password;";
OracleConnection cn = new OracleConnection(strConn);
cn.Open();
// ...
}
Note: in addition to changing the code in the custom assembly, you will need
to modify the code policy config files to give your custom assembly full
trust permissions.
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
There is a reason why you will need to assert FullTrust permissions: If you
read the MSDN documentation for the OraclePermission class and the
SqlClientPermission class and compare them, you will notice the following
statement:
"This class [i.e. OraclePermission] is intended for future use when the .NET
Framework Data Provider for Oracle is enabled for partial trust scenarios.
The provider currently requires FullTrust permission."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclepermissionclasstopic.asp
I.e. asserting the OraclePermission won't help you get around the Security
exception, because the .NET data provider for Oracle _must_ have full trust
and does not work in a partially trusted environment. This is inherit to the
current design of the data provider and the Oracle client. Note: The managed
provider for SQL Server is enabled for partial trust scenarios, therefore it
is sufficient (and advisable) to just assert the SqlClientPermission when
using it in custom assemblies.
HTH,
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"PeteZ" <peteZ@.aol.com> wrote in message
news:OyiJok40EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Carlos,
> It sounds like a CAS issue (Code Access Security)
> Make sure you define (in the RS Policy file) definitions of the location
and
> the name of the Custome Assembly DLL's and what Rights you want to assign
to
> it.
> RS provides very limited default "trust" of Custom Assemblies, you'll need
> to define specific additions to this to allow access to the database.
> Btw: Although it can be done, it's a little unusual to access the database
> from a Custom Assembly - I normally see it done as an extension like a
data
> procesing extension.
> HTH
> - peteZ
>
> "Carlos López." <CarlosLpez@.discussions.microsoft.com> wrote in message
> news:A591ABD5-0838-4B99-88A3-43FBBE27FD49@.microsoft.com...
> > Hi Group!
> >
> > I've a big problem with MS Reporting Services, i try to access an oracle
> > database from a custom assembly, in desing view all work nice, but in
> > production environment, the call to the class fail!.
> > What i can do to correct this problem?
> >
> > I added the dll file to de bin folder or reportserver and report
designer,
> > but don't work.
> >
> > Really I need help.
> >
> > Thanks for the help
> >
>
Iâ've a big problem with MS Reporting Services, i try to access an oracle
database from a custom assembly, in desing view all work nice, but in
production environment, the call to the class fail!.
What i can do to correct this problem?
I added the dll file to de bin folder or reportserver and report designer,
but donâ't work.
Really I need help.
Thanks for the helpCarlos,
It sounds like a CAS issue (Code Access Security)
Make sure you define (in the RS Policy file) definitions of the location and
the name of the Custome Assembly DLL's and what Rights you want to assign to
it.
RS provides very limited default "trust" of Custom Assemblies, you'll need
to define specific additions to this to allow access to the database.
Btw: Although it can be done, it's a little unusual to access the database
from a Custom Assembly - I normally see it done as an extension like a data
procesing extension.
HTH
- peteZ
"Carlos López." <CarlosLpez@.discussions.microsoft.com> wrote in message
news:A591ABD5-0838-4B99-88A3-43FBBE27FD49@.microsoft.com...
> Hi Group!
> I've a big problem with MS Reporting Services, i try to access an oracle
> database from a custom assembly, in desing view all work nice, but in
> production environment, the call to the class fail!.
> What i can do to correct this problem?
> I added the dll file to de bin folder or reportserver and report designer,
> but don't work.
> Really I need help.
> Thanks for the help
>|||You will need to assert FullTrust permissions to use the Oracle data
provider from a custom assembly. Below is an example of how to assert full
trust in your custom assembly using an attribute on the method that opens an
Oracle connection:
[PermissionSet(SecurityAction.Assert, Unrestricted=true)]
public foo()
{
// your code
strConn = "Data Source=TSD01;User ID=SchemaName;password=password;";
OracleConnection cn = new OracleConnection(strConn);
cn.Open();
// ...
}
Note: in addition to changing the code in the custom assembly, you will need
to modify the code policy config files to give your custom assembly full
trust permissions.
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
There is a reason why you will need to assert FullTrust permissions: If you
read the MSDN documentation for the OraclePermission class and the
SqlClientPermission class and compare them, you will notice the following
statement:
"This class [i.e. OraclePermission] is intended for future use when the .NET
Framework Data Provider for Oracle is enabled for partial trust scenarios.
The provider currently requires FullTrust permission."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoracleclientoraclepermissionclasstopic.asp
I.e. asserting the OraclePermission won't help you get around the Security
exception, because the .NET data provider for Oracle _must_ have full trust
and does not work in a partially trusted environment. This is inherit to the
current design of the data provider and the Oracle client. Note: The managed
provider for SQL Server is enabled for partial trust scenarios, therefore it
is sufficient (and advisable) to just assert the SqlClientPermission when
using it in custom assemblies.
HTH,
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"PeteZ" <peteZ@.aol.com> wrote in message
news:OyiJok40EHA.3584@.TK2MSFTNGP11.phx.gbl...
> Carlos,
> It sounds like a CAS issue (Code Access Security)
> Make sure you define (in the RS Policy file) definitions of the location
and
> the name of the Custome Assembly DLL's and what Rights you want to assign
to
> it.
> RS provides very limited default "trust" of Custom Assemblies, you'll need
> to define specific additions to this to allow access to the database.
> Btw: Although it can be done, it's a little unusual to access the database
> from a Custom Assembly - I normally see it done as an extension like a
data
> procesing extension.
> HTH
> - peteZ
>
> "Carlos López." <CarlosLpez@.discussions.microsoft.com> wrote in message
> news:A591ABD5-0838-4B99-88A3-43FBBE27FD49@.microsoft.com...
> > Hi Group!
> >
> > I've a big problem with MS Reporting Services, i try to access an oracle
> > database from a custom assembly, in desing view all work nice, but in
> > production environment, the call to the class fail!.
> > What i can do to correct this problem?
> >
> > I added the dll file to de bin folder or reportserver and report
designer,
> > but don't work.
> >
> > Really I need help.
> >
> > Thanks for the help
> >
>
Thursday, March 8, 2012
ABS function issue
Hi Onibuzz,
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
Hi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query :)
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
My old query - >
I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd = ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd = ER1.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query :)
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
> ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
> asc),0),0),0))), 4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> I have two tables Expense_Addl and ExchangeRates
> These are the scripts
>
> CREATE TABLE Expense_Addl (
> FileId int,
> HostAmt money,
> HostCurrencyCd char(4),
> LockDt smalldatetime
> )
>
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
>
> In the above table HostCurrencycd can be any currenycode like 'USD',
> 'INR', YEN'
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
> INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd from table ExchangeRates
> for the nearest date like this
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 because
> lockdt 01st jan is closet to 14th june
>
> I am trying like below but not getting the correct results.
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
> * 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd => ER1.CurrencyCd
> INNER JOIN
> (SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
> ER.ExchangeRateDate <= EA.lockdt
> GROUP BY ER.CurrencyCd
> ) T1
> ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd => ER1.CurrencyCd
>
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
Hi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query :)
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
My old query - >
I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd = ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd = ER1.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query :)
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
> ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
> asc),0),0),0))), 4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> I have two tables Expense_Addl and ExchangeRates
> These are the scripts
>
> CREATE TABLE Expense_Addl (
> FileId int,
> HostAmt money,
> HostCurrencyCd char(4),
> LockDt smalldatetime
> )
>
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
>
> In the above table HostCurrencycd can be any currenycode like 'USD',
> 'INR', YEN'
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
> INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd from table ExchangeRates
> for the nearest date like this
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 because
> lockdt 01st jan is closet to 14th june
>
> I am trying like below but not getting the correct results.
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FLOAT, EA.HostAmt))
> * 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd => ER1.CurrencyCd
> INNER JOIN
> (SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
> ER.ExchangeRateDate <= EA.lockdt
> GROUP BY ER.CurrencyCd
> ) T1
> ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd => ER1.CurrencyCd
>
ABS function issue
Hi Onibuzz,
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
Hi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
My old query - >
I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER1.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
> ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
> asc),0),0),0))), 4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> I have two tables Expense_Addl and ExchangeRates
> These are the scripts
>
> CREATE TABLE Expense_Addl (
> FileId int,
> HostAmt money,
> HostCurrencyCd char(4),
> LockDt smalldatetime
> )
>
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
>
> In the above table HostCurrencycd can be any currenycode like 'USD',
> 'INR', YEN'
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
> INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd from table ExchangeRates
> for the nearest date like this
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 because
> lockdt 01st jan is closet to 14th june
>
> I am trying like below but not getting the correct results.
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
> * 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
> ER1.CurrencyCd
> INNER JOIN
> (SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
> ER.ExchangeRateDate <= EA.lockdt
> GROUP BY ER.CurrencyCd
> ) T1
> ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
> ER1.CurrencyCd
>
One month ago i have posted one topic on
microsoft.public.sqlserver.programming group and u have posted a reply
to it.
Your reply was a great help to me but i am stuck with another small
issue in the same query. I would greatly appreciate if you help me
in finding the solution.
My old query and ur reply is there in this same mail. I want little
modification in the query.
My new query is if the date in the table 'Expense_addl values' is
suppose '2006-05-20' (LockDt)
and i have exchangerate available for dates '2006-05-21' and
'2006-05-19' then i want the exchangerate for the previous date
i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
for the older date.
i am currently using ABS function as suggested by u :-
abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
but this is giving exchangerate for the new date i.e. '2006-05-21'
Could you plese help me with this ?
Thanks in advance
Regards,
Rajeev Rajput
From: Omnibuzz - view profile
Date: Thurs, Jun 22 2006 4:01 pm
Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
Groups: microsoft.public.sqlserver.programming
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
Hi Rajeev,
I don't know what you do that convert and nullif and all.
But I have substituted a query that will give you the exchange rate.
this update works. But no where clause for your Expense_addl.
I didn't try to figure out from your query
UPDATE Expense_Addl
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
Expense_Addl.HostAmt))
* 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
ER1
where
ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
asc),0),0),0))), 4)
Hope this helps.
--
-Omnibuzz (The SQL GC)
My old query - >
I have two tables Expense_Addl and ExchangeRates
These are the scripts
CREATE TABLE Expense_Addl (
FileId int,
HostAmt money,
HostCurrencyCd char(4),
LockDt smalldatetime
)
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
In the above table HostCurrencycd can be any currenycode like 'USD',
'INR', YEN'
CREATE TABLE ExchangeRates
(CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
SMALLDATETIME)
INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
Now i have to update the HostAmt in Expense_Addl table taking
exchangerate for the hostcurrencycd from table ExchangeRates
for the nearest date like this
For e.g. HOstAmt in first row should take exchangerate as 0.40 because
lockdt 15th june is closet to 14th june
Similarly HOstAmt in second row should take exchangerate as 0.45
because lockdt 19th june is closet to 21st june
Similarly HOstAmt in third row should take exchangerate as 0.40 because
lockdt 01st jan is closet to 14th june
I am trying like below but not getting the correct results.
UPDATE EA
SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
* 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
FROM Expense_Addl EA(NOLOCK)
INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
ER1.CurrencyCd
INNER JOIN
(SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
ER.ExchangeRateDate <= EA.lockdt
GROUP BY ER.CurrencyCd
) T1
ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
ER1.CurrencyCdIf you desire previous dates only, then the use of ABS should be removed,
and the possible dates limited to negative values only.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1155128270.682995.67550@.n13g2000cwa.googlegroups.com...
> Hi Onibuzz,
> One month ago i have posted one topic on
> microsoft.public.sqlserver.programming group and u have posted a reply
> to it.
> Your reply was a great help to me but i am stuck with another small
> issue in the same query. I would greatly appreciate if you help me
> in finding the solution.
> My old query and ur reply is there in this same mail. I want little
> modification in the query.
> My new query is if the date in the table 'Expense_addl values' is
> suppose '2006-05-20' (LockDt)
> and i have exchangerate available for dates '2006-05-21' and
> '2006-05-19' then i want the exchangerate for the previous date
> i.e. '2006-05-19' i.e. if the newer or older gap is same then i want
> for the older date.
> i am currently using ABS function as suggested by u :-
> abs(datediff(dd,ER1.ExchangeRateDate,LockDt)) asc)
> but this is giving exchangerate for the new date i.e. '2006-05-21'
> Could you plese help me with this ?
> Thanks in advance
> Regards,
> Rajeev Rajput
>
> From: Omnibuzz - view profile
> Date: Thurs, Jun 22 2006 4:01 pm
> Email: Omnibuzz <Omnib...@.discussions.microsoft.com>
> Groups: microsoft.public.sqlserver.programming
> Not yet ratedRating:
> show options
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Report Abuse | Find messages by this author
>
> Hi Rajeev,
> I don't know what you do that convert and nullif and all.
> But I have substituted a query that will give you the exchange rate.
> this update works. But no where clause for your Expense_addl.
> I didn't try to figure out from your query
> UPDATE Expense_Addl
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT,
> Expense_Addl.HostAmt))
> * 100) / NULLIF(ISNULL((select top 1 ExchangeRate from ExchangeRates
> ER1
> where
> ER1.CurrencyCd = Expense_Addl.HostCurrencyCD
> order by abs(datediff(dd,ER1.ExchangeRateDate,LockDt))
> asc),0),0),0))), 4)
>
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
>
> My old query - >
> I have two tables Expense_Addl and ExchangeRates
> These are the scripts
>
> CREATE TABLE Expense_Addl (
> FileId int,
> HostAmt money,
> HostCurrencyCd char(4),
> LockDt smalldatetime
> )
>
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-15')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2006-06-19')
> INSERT INTO Expense_addl values (1001, 500, 'USD', '2002-01-01')
>
> In the above table HostCurrencycd can be any currenycode like 'USD',
> 'INR', YEN'
>
> CREATE TABLE ExchangeRates
> (CurrencyCd CHAR(4), ExchangeRate MONEY, ExchangeRateDate
> SMALLDATETIME)
>
> INSERT INTO ExchangeRates values ('USD', 0.45, '2006-06-21')
> INSERT INTO ExchangeRates values ('USD', 0.40, '2006-06-14')
>
> Now i have to update the HostAmt in Expense_Addl table taking
> exchangerate for the hostcurrencycd from table ExchangeRates
> for the nearest date like this
>
> For e.g. HOstAmt in first row should take exchangerate as 0.40 because
> lockdt 15th june is closet to 14th june
> Similarly HOstAmt in second row should take exchangerate as 0.45
> because lockdt 19th june is closet to 21st june
> Similarly HOstAmt in third row should take exchangerate as 0.40 because
> lockdt 01st jan is closet to 14th june
>
> I am trying like below but not getting the correct results.
>
> UPDATE EA
> SET HostAmt = ROUND(CONVERT(MONEY,(ISNULL(((CONVERT(FL
OAT, EA.HostAmt))
> * 100) / NULLIF(ISNULL(ER1.ExchangeRate,0),0),0))), 4)
> FROM Expense_Addl EA(NOLOCK)
> INNER JOIN ExchangeRates ER1(NOLOCK) ON EA.HostCurrencyCd =
> ER1.CurrencyCd
> INNER JOIN
> (SELECT CurrencyCd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,Expense_Addl EA(NOLOCK) Where
> ER.ExchangeRateDate <= EA.lockdt
> GROUP BY ER.CurrencyCd
> ) T1
> ON T1.ExchangeRateDate = ER1.ExchangeRateDate AND T1.CurrencyCd =
> ER1.CurrencyCd
>
Friday, February 24, 2012
ABOUT sql server 2005 partition
hello,
We are writting an erp for group,and want to use partition of sql server 2005 for large data,our environment is window 2003 server,8g memory.my one solution will create many physical files(about 500-600) to fit 500-600 partitions,how about performance? or I will use another arithmetic in order to reduce partitions?
Hi,
Wondering why you will need that many partitioons for. How big your table is likely to be ? how many rows? What are planning to use as you partition key?
Jag
|||hello,
Thank you for your attention,following are our case:
we wrote an erp for a group,it have five child companys,three factorys,six trademark,we finish distribution sale,supply chain,production(not running),our database size now is 19G on sql server 2000,the system is slow,we now upgrading hardware and software,database will use sql server 2005.
I analyse the system, if I use partition to divide trademark,customer data and warehouse data and supplier data,producing,and every child company also will use partition,I think it will be better more,if a company have 10 partitons, five companys will reach 50 partitions,I really not know how many partitions will right,and performace is best?
I also want to do an online system for small company,if using design above,50 companys will up to 500 partitions,I think this case,the arithmetic will different from above,it will not grow with company amount,is right?
Thank you,again.
|||It really seems like that is a lot of partitions, especially for a database that is only 19gb. Really, the idea behind using partitions is somewhat two-fold. First, you can segreate your archive data from your live data for performance reasons. Also, you can segment your historical or archive data itself for performance benefit. You can also move data from partition to partition as needed. However, there is a limit the number of partitions you can create, and as the number of partitions grows, the amount of administration grows. This leads to the type of hardware you are using.....are you using a SAN or some sort of high-performance shared disk storage? If so, what RAID level are you using? If this design is optimal, it might be time to look at how your application is written. Most likely, you will see the highest performance gain by slight design changes and query optimizations as compared to hardware upgrades and partitioning.
Tim
|||hello,
thank you for your suggest,I learn a lot.
The company have two system,one for sale,one for purchase,we now will incorporate these system,incorporation size are at least 30g,retail system are working recently,all shops are about 400 amount,so data will grow faster than before,at next quater,the production system will begin,also will produce so much data,we use raid5,donn't use SAN,Perhaps I need study more knowledge about SAN.
we upgrade hardware and software,the old system is slow,often happen concurrentcy problem,the erp first is bought ,but couldn't fit the company,the architecture is very bad,we do a great lot of redevelopping,now I redesign architecture,consider optimization at transation,query,analyse,and in order to predigest the communication between company, customer, warehouse,factory,supplyer, I put some table together to one table,using partition to divide them,I think it will have good performance for transation, and persormance for group report,my design have consider about partition administrator, but as you have mentioned,partition for history data, yes,it will good for performance,there is another solution, I want to design an OLAP system for histotry analysing,is it right?
at last,how much about partition limit? thank you
Sunday, February 19, 2012
about Semiadditive
About Semiadditive the olap server just intruduce its concept,i don't know how to setting Semiadditive,for example ,some mesures in mesures group just want to be aggregated along geography dimension,not time dimension,how to do it?
Semiaddtive measures have special aggregation behavior only along the time dimension and are additive along all other dimensions. I am not sure I understand your specific requirements. Generally speaking, if you want special aggregation rules along non-time dimensions you can use unary operators or custom rollups.
Thursday, February 16, 2012
about migrating to 2005 from 2000.
Hi, since havenot done migration yet, my company is planning to migrate from sql 2000 to sql 2005 server. and i am in group for capacity planning. can anybody suggest me the steps i should be really taking in order to migrate to 2005, if anyone has done the it in real world with database up to 50g.
thanks
Dereki would start with running the upgrade advisor. this does not catch everything. one thing i found annoying is that old stored procedures with words in them that became new keywords in 2K5 broke all over the place.
thanks
Dereki would start with running the upgrade advisor. this does not catch everything. one thing i found annoying is that old stored procedures with words in them that became new keywords in 2K5 broke all over the place.
Monday, February 13, 2012
About Group BY Function in sql 2005
Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting tableAbout Group BY Function in sql 2005
Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting tableAbout Group BY Function in sql 2005
Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting table
Subscribe to:
Posts (Atom)