I'm receiving an,
"The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope."
Error whenever I use the fields from my similar running total query
SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Anyone run into a similar problem?Just in case anyone else has this problem
if in your select you use a Sum and it will automatically convert it to a
name like AS expr1, Change the expr to that field.
Ex:
Select Sum(Distinct Something.FieldName) As Expr1
Change Expr1 to FieldName and it should work in the graph.
Don't know why I was getting this error.
"Sean Edwards" wrote:
> I'm receiving an,
> "The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
> Report item expressions can only refer to fields within the current data set
> scope or, if inside an aggregate, the specified data set scope."
> Error whenever I use the fields from my similar running total query
> SELECT a.DayCount,
> a.Sales,
> SUM(b.Sales)
> FROM Sales a
> CROSS JOIN Sales b
> WHERE (b.DayCount <= a.DayCount) AS RunningTotal
> GROUP BY a.DayCount,a.Sales
> ORDER BY a.DayCount,a.Sales
> Anyone run into a similar problem?
Showing posts with label receiving. Show all posts
Showing posts with label receiving. Show all posts
Sunday, February 19, 2012
Friday, February 10, 2012
changinh DB of stored procedure
I have a stored procedure that as one of parameters receiving the database.
I need to change the database that SQL statement in store procedure working
on according to parameter.
I cant find any solution except fill naming and rewiring whole statement
depend on parameter or building SQL statement as string and then running it
in store procedure.
I know what there are USE { database } statement in TSQL , but it's doesn't
work in stored procedure.
Please, help if you have any solution of this problem.
Thank you, David Potahinsky.David,
You can't use a parameter after USE, but you can do something like
this, I believe, if you know the names of all the databases that might
be chosen:
if @.db = 'somedatabase'
use somedatabase
else if @.db = 'differentdatabase'
use differentdatabase
else if ...
If you wish, you can also return silently or throw an error if the value
passed for @.db is not one you've taken care of.
SK
David Potahisnsky wrote:
Put the stored procedure with the main logic in all databases. In the
master you can say:
DECLARE @.logic_sp sysname
SELECT @.logic_sp = @.db + '.dbo.your_sp'
EXEC @.err = @.logic_sp @.par1, @.par2, ...
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is there a way to avoid creation of same stored procedures for each database
as it makes maintenance a hell, but to have for ex. REPORTS database with
all stored procedures that can access tables in other DBs on demand by
parameter?
Something that is logically equivalent to:
CREATE PROCEDURE REPORT_TEST1 (@.DB VARCHAR(100)) AS
BEGIN
USE @.DB
SELECT * FROM TABLE1
-- should get records from @.db.dbo.table1
...
END
But the problem is that T-SQL doesn't allow USE in PROCEDURE.
I'm aware that use of dynamic queries in style EXEC ('SELECT * FROM
'+@.DB+'.dbo.table1') is possible but barely acceptable again because of
maintenance pains.
Any other alternative solutions?
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns945BF1298BDD1Yazorman@.127.0.0.1...
Actually there is: this is the way the system stored procedure works.
However, while you can add your own system stored procedures, this is
deprecated and unsupported, and also has security issues.
And anyway the scheme breaks down the day the databases are spread over
more than one server. In this case, you have no choice than to deploy
your procedure on at least every server.
So the real answer to your problem is to devise a distribution
mechanism, so that you easily can distribute updates to your code to
your databases, no matter they are all on the same server, or spread
out on several customer sites. Obviously for this you need a master
to deliver from, but this master database is not an SQL database -
it's a version control system (which may use SQL Server as its storage
engine, but that's another story).
Such a distribution can be assembled in different ways. The heart of the
procedure is always that in your version-control system you set a
baseline. The terminology changes between version-control systems,
but often you talk about "labels". Then you assemble the changes between
two labels. This can be performed by manually building a BAT-fil which
uses OSQL to install the stored procedures, or it can be more elaborate.
There are several third-party tools on the market. Popular is SQL Compare
from Red Gate, www.red-gate.com, although it works from a different
perspective than I've outlined here. Myself, I have made the tool we
use in our shop available as freeware, see http://www.abaris.se/abaperls/.
Thus, what you have is a configuration-management problem, not one of
writing stored procedures. It may seem inflexible that stored procedures
are tied to one certain database, but there are some good reasons for
this. The most important is that although the logic in the stored
procedure may be the same in two databases, the query plan that SQL Server
should use to retrieve the data may not, because the data is different
in the two databases.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||we use views successfully here. write your stored procedures to access views
instead of base tables. upon deployment you can literally generate the cross
database views from a table in your main db (table with metadata). an sp can
produce multiple of these:
IF OBJECT_ID('v_<foreign_db>_<base_table_name>') IS NOT NULL DROP VIEW
v_<foreign_db>_<base_table_name>
GO
CREATE VIEW v_<foreign_db>_<base_table_name>
AS
SELECT *
FROM <foreign_db>.dbo.<base_table_name>
GO
where <foreign_db> is a logical name of a db in your system e.g. "rep" for
reporting (not the actual deployment specific sql db name) and can be passed
as a paramter to the sp or even better -- can be persisted in a setup table.
of course using * for column_list is not "production" enough so you might
want to store view's column lists in you "metadata cross-db view definition"
table as well.
we use multiple prefixes for views names like this:
v_<host_db>_<foreign_db>_<foreign_view_name> to allow different column list
for each <host_db> that is each sub-system of the solution. so we end up
with several views pointing to a single base table (but from different
sub-systems).
cheers,
</wqw>
"Myrth" <m0sh3_NO@.SPAM_hotmail.com> wrote in message
news:epkBLBnyDHA.2032@.TK2MSFTNGP10.phx.gbl...
database
I need to change the database that SQL statement in store procedure working
on according to parameter.
I cant find any solution except fill naming and rewiring whole statement
depend on parameter or building SQL statement as string and then running it
in store procedure.
I know what there are USE { database } statement in TSQL , but it's doesn't
work in stored procedure.
Please, help if you have any solution of this problem.
Thank you, David Potahinsky.David,
You can't use a parameter after USE, but you can do something like
this, I believe, if you know the names of all the databases that might
be chosen:
if @.db = 'somedatabase'
use somedatabase
else if @.db = 'differentdatabase'
use differentdatabase
else if ...
If you wish, you can also return silently or throw an error if the value
passed for @.db is not one you've taken care of.
SK
David Potahisnsky wrote:
quote:|||David Potahisnsky (david.potashinsky@.kabbalah.com) writes:
>I have a stored procedure that as one of parameters receiving the database.
>I need to change the database that SQL statement in store procedure working
>on according to parameter.
>I cant find any solution except fill naming and rewiring whole statement
>depend on parameter or building SQL statement as string and then running it
>in store procedure.
>
>I know what there are USE { database } statement in TSQL , but it's doesn't
>work in stored procedure.
>
>Please, help if you have any solution of this problem.
>
>Thank you, David Potahinsky.
>
>
>
quote:
> I have a stored procedure that as one of parameters receiving the
> database. I need to change the database that SQL statement in store
> procedure working on according to parameter.
> I cant find any solution except fill naming and rewiring whole statement
> depend on parameter or building SQL statement as string and then running
> it in store procedure.
Put the stored procedure with the main logic in all databases. In the
master you can say:
DECLARE @.logic_sp sysname
SELECT @.logic_sp = @.db + '.dbo.your_sp'
EXEC @.err = @.logic_sp @.par1, @.par2, ...
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is there a way to avoid creation of same stored procedures for each database
as it makes maintenance a hell, but to have for ex. REPORTS database with
all stored procedures that can access tables in other DBs on demand by
parameter?
Something that is logically equivalent to:
CREATE PROCEDURE REPORT_TEST1 (@.DB VARCHAR(100)) AS
BEGIN
USE @.DB
SELECT * FROM TABLE1
-- should get records from @.db.dbo.table1
...
END
But the problem is that T-SQL doesn't allow USE in PROCEDURE.
I'm aware that use of dynamic queries in style EXEC ('SELECT * FROM
'+@.DB+'.dbo.table1') is possible but barely acceptable again because of
maintenance pains.
Any other alternative solutions?
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns945BF1298BDD1Yazorman@.127.0.0.1...
quote:|||Myrth (m0sh3_NO@.SPAM_hotmail.com) writes:
> David Potahisnsky (david.potashinsky@.kabbalah.com) writes:
> Put the stored procedure with the main logic in all databases. In the
> master you can say:
> DECLARE @.logic_sp sysname
> SELECT @.logic_sp = @.db + '.dbo.your_sp'
> EXEC @.err = @.logic_sp @.par1, @.par2, ...
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
quote:
> Is there a way to avoid creation of same stored procedures for each
> database as it makes maintenance a hell, but to have for ex. REPORTS
> database with all stored procedures that can access tables in other DBs
> on demand by parameter?
Actually there is: this is the way the system stored procedure works.
However, while you can add your own system stored procedures, this is
deprecated and unsupported, and also has security issues.
And anyway the scheme breaks down the day the databases are spread over
more than one server. In this case, you have no choice than to deploy
your procedure on at least every server.
So the real answer to your problem is to devise a distribution
mechanism, so that you easily can distribute updates to your code to
your databases, no matter they are all on the same server, or spread
out on several customer sites. Obviously for this you need a master
to deliver from, but this master database is not an SQL database -
it's a version control system (which may use SQL Server as its storage
engine, but that's another story).
Such a distribution can be assembled in different ways. The heart of the
procedure is always that in your version-control system you set a
baseline. The terminology changes between version-control systems,
but often you talk about "labels". Then you assemble the changes between
two labels. This can be performed by manually building a BAT-fil which
uses OSQL to install the stored procedures, or it can be more elaborate.
There are several third-party tools on the market. Popular is SQL Compare
from Red Gate, www.red-gate.com, although it works from a different
perspective than I've outlined here. Myself, I have made the tool we
use in our shop available as freeware, see http://www.abaris.se/abaperls/.
Thus, what you have is a configuration-management problem, not one of
writing stored procedures. It may seem inflexible that stored procedures
are tied to one certain database, but there are some good reasons for
this. The most important is that although the logic in the stored
procedure may be the same in two databases, the query plan that SQL Server
should use to retrieve the data may not, because the data is different
in the two databases.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||we use views successfully here. write your stored procedures to access views
instead of base tables. upon deployment you can literally generate the cross
database views from a table in your main db (table with metadata). an sp can
produce multiple of these:
IF OBJECT_ID('v_<foreign_db>_<base_table_name>') IS NOT NULL DROP VIEW
v_<foreign_db>_<base_table_name>
GO
CREATE VIEW v_<foreign_db>_<base_table_name>
AS
SELECT *
FROM <foreign_db>.dbo.<base_table_name>
GO
where <foreign_db> is a logical name of a db in your system e.g. "rep" for
reporting (not the actual deployment specific sql db name) and can be passed
as a paramter to the sp or even better -- can be persisted in a setup table.
of course using * for column_list is not "production" enough so you might
want to store view's column lists in you "metadata cross-db view definition"
table as well.
we use multiple prefixes for views names like this:
v_<host_db>_<foreign_db>_<foreign_view_name> to allow different column list
for each <host_db> that is each sub-system of the solution. so we end up
with several views pointing to a single base table (but from different
sub-systems).
cheers,
</wqw>
"Myrth" <m0sh3_NO@.SPAM_hotmail.com> wrote in message
news:epkBLBnyDHA.2032@.TK2MSFTNGP10.phx.gbl...
quote:
> Is there a way to avoid creation of same stored procedures for each
database
quote:
> as it makes maintenance a hell, but to have for ex. REPORTS database with
> all stored procedures that can access tables in other DBs on demand by
> parameter?
> Something that is logically equivalent to:
> CREATE PROCEDURE REPORT_TEST1 (@.DB VARCHAR(100)) AS
> BEGIN
> USE @.DB
> SELECT * FROM TABLE1
> -- should get records from @.db.dbo.table1
> ...
> END
> But the problem is that T-SQL doesn't allow USE in PROCEDURE.
> I'm aware that use of dynamic queries in style EXEC ('SELECT * FROM
> '+@.DB+'.dbo.table1') is possible but barely acceptable again because of
> maintenance pains.
> Any other alternative solutions?
>
> "Erland Sommarskog" <sommar@.algonet.se> wrote in message
> news:Xns945BF1298BDD1Yazorman@.127.0.0.1...
statement[QUOTE]
running[QUOTE]
>
Subscribe to:
Posts (Atom)