reduce databse size: procedure

Showing posts with label procedure. Show all posts

Friday, March 30, 2012

Regarding Creating Stored Procedures If One Does Not Exist

Hello,
Very quick question (I hope) regarding SQL Server:
I want to create a query that basically says that if a stored procedure (say
sproc_foo) does not exist, then create the stored procedure. I know that
there are easy ways to do this with creating tables/databases if they don't
exist, but how is this possible with stored procedures?
Regards,
James Simpson
Straightway Technologies Inc.Hi James,
You can try something like this
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
AND type in (N'P', N'PC'))
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
...
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"James Simpson" wrote:
> Hello,
> Very quick question (I hope) regarding SQL Server:
> I want to create a query that basically says that if a stored procedure (say
> sproc_foo) does not exist, then create the stored procedure. I know that
> there are easy ways to do this with creating tables/databases if they don't
> exist, but how is this possible with stored procedures?
> Regards,
> James Simpson
> Straightway Technologies Inc.
>|||Ben,
Alas, that will not work as is, since CREATE PROCEDURE must be the first
statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
check turned on produces:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'
CREATE PROC [dbo].[MyProc]
@.Parm1 char(6)
AS
-- Do Something'
So, as you can see it is a 'dynamic SQL' implementation. (I believe that
there is a CONNECT request for something better than this.)
RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> Hi James,
> You can try something like this
> IF NOT EXISTS (SELECT * FROM sys.objects
> WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> AND type in (N'P', N'PC'))
> BEGIN
> CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> ...
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "James Simpson" wrote:
>> Hello,
>> Very quick question (I hope) regarding SQL Server:
>> I want to create a query that basically says that if a stored procedure
>> (say
>> sproc_foo) does not exist, then create the stored procedure. I know that
>> there are easy ways to do this with creating tables/databases if they
>> don't
>> exist, but how is this possible with stored procedures?
>> Regards,
>> James Simpson
>> Straightway Technologies Inc.|||Dear Ben/Russel,
Ben - I tried the solution you gave and for whatever reason it does not
appear to work. I have also seen this type of example online and this query
fails miserably on SQL Server 2005 Express. Russel, the solution you gave
works perfectly, and hopefully Microsoft resolves this issue.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi Russell,
I agree with your note but that was not intended to be a final and complete
solution and my primary purpose was to show the IF NOT EXISTS part.
Perhaps it is like saying that your code does not work as is either, because
you have BEGIN but END is missing :-)
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben,
> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> statement in a batch. Scripting from SQL Server 2005 with the If Not Exists
> check turned on produces:
> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> BEGIN
> EXEC dbo.sp_executesql @.statement = N'
> CREATE PROC [dbo].[MyProc]
> @.Parm1 char(6)
> AS
> -- Do Something'
> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> there is a CONNECT request for something better than this.)
> RLF
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >
> > Hi James,
> >
> > You can try something like this
> >
> > IF NOT EXISTS (SELECT * FROM sys.objects
> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> > AND type in (N'P', N'PC'))
> > BEGIN
> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> > ...
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "James Simpson" wrote:
> >
> >> Hello,
> >> Very quick question (I hope) regarding SQL Server:
> >> I want to create a query that basically says that if a stored procedure
> >> (say
> >> sproc_foo) does not exist, then create the stored procedure. I know that
> >> there are easy ways to do this with creating tables/databases if they
> >> don't
> >> exist, but how is this possible with stored procedures?
> >>
> >> Regards,
> >>
> >> James Simpson
> >> Straightway Technologies Inc.
> >>
>
>|||Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
not work as expected. - RLF
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> Hi Russell,
> I agree with your note but that was not intended to be a final and
> complete
> solution and my primary purpose was to show the IF NOT EXISTS part.
> Perhaps it is like saying that your code does not work as is either,
> because
> you have BEGIN but END is missing :-)
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Russell Fields" wrote:
>> Ben,
>> Alas, that will not work as is, since CREATE PROCEDURE must be the first
>> statement in a batch. Scripting from SQL Server 2005 with the If Not
>> Exists
>> check turned on produces:
>> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =>> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
>> BEGIN
>> EXEC dbo.sp_executesql @.statement = N'
>> CREATE PROC [dbo].[MyProc]
>> @.Parm1 char(6)
>> AS
>> -- Do Something'
>> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
>> there is a CONNECT request for something better than this.)
>> RLF
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
>> >
>> > Hi James,
>> >
>> > You can try something like this
>> >
>> > IF NOT EXISTS (SELECT * FROM sys.objects
>> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
>> > AND type in (N'P', N'PC'))
>> > BEGIN
>> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
>> > ...
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "James Simpson" wrote:
>> >
>> >> Hello,
>> >> Very quick question (I hope) regarding SQL Server:
>> >> I want to create a query that basically says that if a stored
>> >> procedure
>> >> (say
>> >> sproc_foo) does not exist, then create the stored procedure. I know
>> >> that
>> >> there are easy ways to do this with creating tables/databases if they
>> >> don't
>> >> exist, but how is this possible with stored procedures?
>> >>
>> >> Regards,
>> >>
>> >> James Simpson
>> >> Straightway Technologies Inc.
>> >>
>>|||Russell, I think you were right. Thanks for the additional explanation.
I also got the code using the SQL Server Scripts Wizard and the 'Include if
NOT EXISTS' option turned on. The problem was that I replaced EXEC
dbo.sp_executesql with CREATE PROCEDURE to make the code easier to read and
understand.
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Russell Fields" wrote:
> Ben, OK. Sorry. I was just trying to emphasize that the 'natural' way will
> not work as expected. - RLF
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:3C07E41D-E259-4887-ABCE-2C403FD6929A@.microsoft.com...
> >
> > Hi Russell,
> >
> > I agree with your note but that was not intended to be a final and
> > complete
> > solution and my primary purpose was to show the IF NOT EXISTS part.
> >
> > Perhaps it is like saying that your code does not work as is either,
> > because
> > you have BEGIN but END is missing :-)
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Russell Fields" wrote:
> >
> >> Ben,
> >>
> >> Alas, that will not work as is, since CREATE PROCEDURE must be the first
> >> statement in a batch. Scripting from SQL Server 2005 with the If Not
> >> Exists
> >> check turned on produces:
> >>
> >> IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id => >> OBJECT_ID(N'[dbo].[MyProc]') AND type in (N'P', N'PC'))
> >> BEGIN
> >> EXEC dbo.sp_executesql @.statement = N'
> >> CREATE PROC [dbo].[MyProc]
> >> @.Parm1 char(6)
> >> AS
> >> -- Do Something'
> >>
> >> So, as you can see it is a 'dynamic SQL' implementation. (I believe that
> >> there is a CONNECT request for something better than this.)
> >>
> >> RLF
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:36E61DF6-EBAB-45E8-B0E5-A91610CA5D46@.microsoft.com...
> >> >
> >> > Hi James,
> >> >
> >> > You can try something like this
> >> >
> >> > IF NOT EXISTS (SELECT * FROM sys.objects
> >> > WHERE object_id = OBJECT_ID(N'[dbo].[uspGetBillOfMaterials]')
> >> > AND type in (N'P', N'PC'))
> >> > BEGIN
> >> > CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
> >> > ...
> >> >
> >> > Hope this helps,
> >> >
> >> > Ben Nevarez
> >> > Senior Database Administrator
> >> > AIG SunAmerica
> >> >
> >> >
> >> >
> >> > "James Simpson" wrote:
> >> >
> >> >> Hello,
> >> >> Very quick question (I hope) regarding SQL Server:
> >> >> I want to create a query that basically says that if a stored
> >> >> procedure
> >> >> (say
> >> >> sproc_foo) does not exist, then create the stored procedure. I know
> >> >> that
> >> >> there are easy ways to do this with creating tables/databases if they
> >> >> don't
> >> >> exist, but how is this possible with stored procedures?
> >> >>
> >> >> Regards,
> >> >>
> >> >> James Simpson
> >> >> Straightway Technologies Inc.
> >> >>
> >>
> >>
> >>
>
>

Regarding Creating Stored Procedures If One Does Not Exist

Wednesday, March 28, 2012

reg Stored procedure Performance

Hi ,
I am facing a typical problem with Stored procedure performance. When i
excute my stored procedure first time it is taking around 60 seconds which i
s
not meeting expectations. when i excute the same sp second time with same
scenario it takes around 30 to 35 seconds. after this this behaviour is not
consistent. again if you ran sometimes it takes 50 seconds and sometimes it
takes 35 seconds like this. with this kind of behaviour i was not able to
figure it out my sp execution time exactly. my sp is having lot of dynamic
sql also.
Could anybody have any thoughts why the sp execution time is not consistent
for the same scenario.
Thanks in advance
BhaskarBhaskar wrote:
> Hi ,
> I am facing a typical problem with Stored procedure performance. When
> i excute my stored procedure first time it is taking around 60
> seconds which is not meeting expectations. when i excute the same sp
> second time with same scenario it takes around 30 to 35 seconds.
> after this this behaviour is not consistent. again if you ran
> sometimes it takes 50 seconds and sometimes it takes 35 seconds like
> this. with this kind of behaviour i was not able to figure it out my
> sp execution time exactly. my sp is having lot of dynamic sql also.
> Could anybody have any thoughts why the sp execution time is not
> consistent for the same scenario.
> Thanks in advance
> Bhaskar
SP duration should not be your primary source of performance tuning.
While it's important, it should follow examination of the execution
plans generated by the SP and the overall CPU consumed. Duration is
dependent on many factors like overall system CPU, lock contention,
physical vs. logical disk reads, etc. CPU, Reads, and Execution Plans
should be consistent across executions with the same parameters.
What you probably have is a poorly tuned procedure that is causing a lot
of reads, likely because of missing indexes or non-sargable expressions
in the queries. If that's the case, you'll see slower execution times
when SQL Server has to go to disk to read data as opposed to getting the
same from memory.
To properly tunes the query, examine the execution plan from Profiler or
Query Analyzer. You can use Profiler to see CPU, Duration, and Reads for
the individual statements and for the overall SP. 30 and 50 seconds are
both way too long for anything but a nightly batch process. You should
strive for times in the <100ms or better if possible.
If you see Table Scan or Clustered Index Scan operations, that's likely
the problem. Examine the query and see why indexes are not used. Could
be because there are no indexes available for the query to use or
because the expressions in the query are no optimizable.
For example:
WHERE LEFT(MyTable.MyCol, 1) = 'T'
is not optimizable even if an index exists on the MyCol column. Whereas,
WHERE MyTable.MyCol = 'T'
is optimizable when a MyCol index exists (still may not be used, but it
could be).
Post the DDL for your tables, indexes, and procedure if you need
specific help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 25 Aug 2005 21:17:02 -0700, "Bhaskar"
<Bhaskar@.discussions.microsoft.com> wrote:
>I am facing a typical problem with Stored procedure performance. When i
>excute my stored procedure first time it is taking around 60 seconds which
is
>not meeting expectations. when i excute the same sp second time with same
>scenario it takes around 30 to 35 seconds. after this this behaviour is no
t
>consistent. again if you ran sometimes it takes 50 seconds and sometimes i
t
>takes 35 seconds like this. with this kind of behaviour i was not able to
>figure it out my sp execution time exactly. my sp is having lot of dynamic
>sql also.
>Could anybody have any thoughts why the sp execution time is not consistent
>for the same scenario.
There are a lot of reasons.
The most obvious is if other people are using the server, which has
only so much horsepower to split between users. Are we talking a
server-class machine here, RAID5 for the data, separate disk for the
log, gigabytes of RAM, dual processors or better?
The slow first run is because (a) the SP needs to be compiled, because
(b) the plan is not already in cache, and (c) the data is not yet
cached, either. A few physical reads and your performance goes right
out the window.
As Dave suggests, the road to wisdom starts with running profiler,
looking at plans, looking at statistics - and getting away from
dynamic SQL! And OF COURSE, making certain you have the proper
indexes.
But when you tell me the time varies, that's a pretty strong sign that
other users are contending with your performance, and likely a sign
that your server is too busy or too small.
J.

Monday, March 26, 2012

refreshview removes user defined functions from sysdepends

When I create a procedure that references a user defined procedure it
appears in sysdepends, but disapears after being refreshed. Is this
expected and is there an alternative to sp_refreshview that correctly
refreshes sysdepends
--0--0--
print 'drop depenencies'
go
drop view depends_test
go
print 'create view'
go
create view depends_test as
select
dbo.ufJsmTranslate('test') As test_column
from
(select 1 one) test
go
print 'check depenencies'
go
sp_depends depends_test
go
print 'refresh'
go
sp_refreshview depends_test
go
print 'check depenencies'
go
sp_depends depends_test
--0--0--
drop depenencies
create view
check depenencies
In the current database, the specified object references the following:
name type updated selected column
-- -- -- -- --
dbo.ufJsmTranslate scalar function no no
refresh
check depenencies
Object does not reference any object, and no objects reference it.
--0--0--May be this is a bug, It seems that sp_refreshview does not update reference
s
to udfs. Here I have a script with the same problem. I also tried to create
the udf and view with the SCHEMABINDING property, everything was ok, except
that sp_refresh is giving me an error when I try to refresh the view.
-- same problem
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
as
begin
return (@.i)
end
go
create view myview
as
select dbo.ufn_funct1(colA) as colA from t
go
exec sp_depends myview
go
exec sp_refreshview myview
go
exec sp_depends myview
go
drop view myview
go
drop function dbo.ufn_funct1
go
drop table t
go
-- Tried to fix it using schemabinfing
-- sp_refreshview is giving me an error
use northwind
go
create table t(colA int)
go
create function dbo.ufn_funct1 (
@.i int
)
returns int
with schemabinding
as
begin
return (@.i)
end
go
create view dbo.myview
with schemabinding
as
select dbo.ufn_funct1(colA) as colA from dbo.t
go
exec sp_depends 'dbo.myview'
go
exec sp_refreshview 'dbo.myview'
go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_depends 'dbo.myview'
go
drop view dbo.myview
go
drop function dbo.ufn_funct1
go
drop table t
go
AMB
"bilbo.baggins@.freesurf.ch" wrote:

> When I create a procedure that references a user defined procedure it
> appears in sysdepends, but disapears after being refreshed. Is this
> expected and is there an alternative to sp_refreshview that correctly
> refreshes sysdepends
> --0--0--
> print 'drop depenencies'
> go
> drop view depends_test
> go
> print 'create view'
> go
> create view depends_test as
> select
> dbo.ufJsmTranslate('test') As test_column
> from
> (select 1 one) test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> go
> print 'refresh'
> go
> sp_refreshview depends_test
> go
> print 'check depenencies'
> go
> sp_depends depends_test
> --0--0--
> drop depenencies
> create view
> check depenencies
> In the current database, the specified object references the following:
> name type updated selected column
> -- -- -- -- --
> dbo.ufJsmTranslate scalar function no no
> refresh
> check depenencies
> Object does not reference any object, and no objects reference it.
> --0--0--
>|||Correction,

> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
..., except that sp_refreshview ...

> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
-- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
-- Invalid object name 'dbo.myview'.
exec sp_refreshview 'dbo.myview'
go
AMB
"Alejandro Mesa" wrote:
> May be this is a bug, It seems that sp_refreshview does not update referen
ces
> to udfs. Here I have a script with the same problem. I also tried to creat
e
> the udf and view with the SCHEMABINDING property, everything was ok, excep
t
> that sp_refresh is giving me an error when I try to refresh the view.
> -- same problem
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> as
> begin
> return (@.i)
> end
> go
> create view myview
> as
> select dbo.ufn_funct1(colA) as colA from t
> go
> exec sp_depends myview
> go
> exec sp_refreshview myview
> go
> exec sp_depends myview
> go
> drop view myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
> -- Tried to fix it using schemabinfing
> -- sp_refreshview is giving me an error
> use northwind
> go
> create table t(colA int)
> go
> create function dbo.ufn_funct1 (
> @.i int
> )
> returns int
> with schemabinding
> as
> begin
> return (@.i)
> end
> go
> create view dbo.myview
> with schemabinding
> as
> select dbo.ufn_funct1(colA) as colA from dbo.t
> go
> exec sp_depends 'dbo.myview'
> go
> exec sp_refreshview 'dbo.myview'
> go
> -- Server: Msg 208, Level 16, State 8, Procedure sp_refreshview, Line 1
> -- Invalid object name 'dbo.myview'.
> exec sp_depends 'dbo.myview'
> go
> drop view dbo.myview
> go
> drop function dbo.ufn_funct1
> go
> drop table t
> go
>
> AMB
>
> "bilbo.baggins@.freesurf.ch" wrote:
>

Refreshing Tables In Database

I have a stored procedure that creates a table. It works fine except until
I
refresh the database, the table doesn't appear ... and views and reports
can't find it.
Does anyone know how I can do this automatically with SQL or VBA (I'm using
an Access project as my front end)?
Thanks!
HIf I had to guess, I would say that you create the table as one user and the
n
try to access it as another - just a guess
"Howard Brody" wrote:

> I have a stored procedure that creates a table. It works fine except unti
l I
> refresh the database, the table doesn't appear ... and views and reports
> can't find it.
> Does anyone know how I can do this automatically with SQL or VBA (I'm usin
g
> an Access project as my front end)?
> Thanks!
> H|||Howard Brody wrote:
> I have a stored procedure that creates a table. It works fine except
> until I refresh the database, the table doesn't appear ... and views
> and reports can't find it.
> Does anyone know how I can do this automatically with SQL or VBA (I'm
> using an Access project as my front end)?
> Thanks!
> H
This seems to be an Access related issue. I would post the question to
an Access group and see what they say.
David Gugick
Imceda Software
www.imceda.com|||I dunno ... the table doesn't appear until the database is refreshed whether
I run the code from a stored procedure or the query analyzer - which has
nothing to do the Access front end.
I would think that SQL would have a command or function for refreshing your
database. I just haven't found it yet.
H
"David Gugick" wrote:

> This seems to be an Access related issue. I would post the question to
> an Access group and see what they say.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Howard Brody wrote:
> I dunno ... the table doesn't appear until the database is refreshed
> whether I run the code from a stored procedure or the query analyzer
> - which has nothing to do the Access front end.
> I would think that SQL would have a command or function for
> refreshing your database. I just haven't found it yet.
> H
>
There's really no such thing as refreshing a database. Seeing the
objects in a list, for example, is a client issue (Access in this case
or could just as well be Query Analyzer). Once you create an object in
SQL Server, it's there, whether you see it in the user-interface of an
application or not. There's no real live-feed of database objects like
you have when viewing file, for instance, in Explorer - which keeps an
eye on folders for changes - most times. Even in QA, you don't need to
see the object in the Object Browser to run a query against it. Whereas,
I suspect, you need to see the object in Access to create a query or
open up the table in the Access UI. That was my reason for suggesting
you post to the Access group because possibly there is a feature of
Access that can mitigate this problem somewhat.
David Gugick
Imceda Software
www.imceda.com

Refreshing data in the cursor.

Hello everybody,

I wrote a stored procedure for SqlServer 2000 and i am using it for paging purpose.
The procedure is as follows :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[OLAP_PagedRows]
(
@.SelectFields nVarchar(2000) =NULL,

@.GroupByFields nvarchar(1000) =NULL,

@.BaseTable varchar(100),

@.KeyColumn nvarchar(200)=NULL ,

@.JoinTables varchar(500) =NULL,

@.ConditionalClause varchar(1000) =NULL,

@.Pagesize int = 10,

@.PageNumber int =1,

@.SortExpression nvarchar(200)=NULL,

@.SearchText nvarchar(200)=NULL

)

BEGIN

DECLARE @.SQLSTMT NVarchar(4000)

DECLARE @.SQLSTMT1 NVarchar(4000)

SET @.SQLSTMT1 = ''

--check whether page size is given null or not, if so set to default value

IF @.Pagesize IS NULL OR @.Pagesize = ''

BEGIN

SET @.Pagesize =10

END

--check whether page number is given null or not, if so set to default value

IF @.PageNumber IS NULL OR @.PageNumber = ''

BEGIN

SET @.PageNumber =1

END

--Start constructing the query --

SET @.SQLSTMT = 'SELECT '

SET @.SQLSTMT1 = 'DECLARE @.CountValue INT SELECT @.CountValue = count(*) From '+@.BaseTable

SET @.SQLSTMT = @.SQLSTMT + @.SelectFields + ' FROM '+@.BaseTable

If @.JoinTables Is Not Null

BEGIN

SET @.SQLSTMT = @.SQLSTMT + ' ' +@.JoinTables

SET @.SQLSTMT1 = @.SQLSTMT1 + ' ' +@.JoinTables

END

DECLARE @.StmtWhereClause nvarchar(500)

SET @.StmtWhereClause =''

Get where conditional clause

If (@.SearchText Is Not Null AND RTRIM(LTRIM(@.SearchText))<>'')

BEGIN

SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.SearchText

END

If @.ConditionalClause Is Not Null AND RTRIM(LTRIM(@.ConditionalClause))<>''

BEGIN

IF (@.StmtWhereClause <> '')

BEGIN

SET @.StmtWhereClause= @.StmtWhereClause + 'AND ' +@.ConditionalClause

END

ELSE

BEGIN

SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.ConditionalClause

END

SET @.SQLSTMT = @.SQLSTMT + @.StmtWhereClause

SET @.SQLSTMT1 = @.SQLSTMT1 + @.StmtWhereClause

If @.GroupByFields Is Not Null And RTRIM(LTRIM(@.GroupByFields))<>''

BEGIN

SET @.SQLSTMT = @.SQLSTMT + ' Group By ' +@.GroupByFields

SET @.SQLSTMT1 = @.SQLSTMT1 + ' Group By ' +@.GroupByFields

END

IF @.SortExpression Is Not Null AND RTRIM(LTRIM(@.SortExpression))<>''

BEGIN

SET @.SortExpression = LTRIM(RTRIM(' Order By '+ @.SortExpression))

SET @.SQLSTMT = @.SQLSTMT +' '+ @.SortExpression

SET @.SQLSTMT1 = @.SQLSTMT1 +' '+ @.SortExpression

END

SET @.SQLSTMT1= @.SQLSTMT1+' SELECT @.CountValue As MyRows '

--SELECT @.SQLSTMT1

--SELECT @.SQLSTMT

DECLARE @.StartRow INT

SET @.SQLSTMT = ' DECLARE temp_Cursor CURSOR SCROLL FOR '+@.SQLSTMT

EXECUTE SP_EXECUTESQL @.SQLSTMT

Open temp_Cursor

DECLARE @.RowCount INT

SET @.RowCount = 1

SET @.startRow = (@.PageSize * (@.PageNumber-1))+@.RowCount

--SELECT @.startRow as 'Current Row'

WHILE @.RowCount <= @.PageSize

BEGIN

--Select @.StartRow 'as @.StartRow'

FETCH ABSOLUTE @.startRow From temp_Cursor

SET @.RowCount= @.RowCount+1

SET @.StartRow = @.startRow + 1

END

deallocate temp_Cursor

EXECUTE SP_EXECUTESQL @.SQLSTMT1

END

It is working fine but I have problem with this kind of paging. I need to load the whole data into the cursor and i have to fetch records. The problem is that my table's contains more than Half a million records in it. If I have to load each time this cursor it will be a very big problem on the server side.

Probably it may not be a best solution, but sqlserver 2000 cannot provide more help than this. If I use sub-query for this like using Top <Number> it adversly effecting the nature of the data retrieval.

One solution that I am thinking is Load cursor once and whenever some updations performed on those tables from which cursor is getting data should be automatically reflect the changes.

Is this possible? Please help me.

Regards

Andy Rogers

hi Andy Rogers
why you use cursor with more data?
please try do'nt use cursor.
you can use temp table for sorting data.
if your data wholud'nt changes, you can use static data.
order on the 0.5 millions records has overloading on the sql server 2000.
sort your data and then use that with SELECT TOP X for best performance.
good luck

|||Because there is no "BOTTOM" command in the select, this is very tricky to do. This is how I have done it in the past.

I modified your code a little. They way you were doing it, returns 1 record set with 1 record for every record. This method returns all records in 1 recordset.

Basically it does a "SELECT TOP @.startRow+PageSize", so you get the smallest set from top to bottom, into a temp table, then deletes everything before the @.startRow and returns the rest. This is the best method I have found.

DECLARE @.SQLSTMT NVarchar(4000)
DECLARE @.SQLSTMT1 NVarchar(4000)

SET @.SQLSTMT1 = ''

--check whether page size is given null or not, if so set to default value

IF @.Pagesize IS NULL OR @.Pagesize = ''

BEGIN
SET @.Pagesize =10
END

--check whether page number is given null or not, if so set to default value

IF @.PageNumber IS NULL OR @.PageNumber = ''
BEGIN
SET @.PageNumber =1
END

DECLARE @.StartRow INT
SET @.startRow = (@.PageSize * (@.PageNumber-1))+ 1

--Start constructing the query --

SET @.SQLSTMT1 = 'DECLARE @.CountValue INT SELECT @.CountValue = count(*) From '+@.BaseTable

SET @.SQLSTMT = 'SELECT TOP ' + CAST(@.startRow+@.PageSize AS VARCHAR(10)) + ' IntRowNum = IDENTITY(int,1,1), '

SET @.SQLSTMT = @.SQLSTMT + @.SelectFields + ' INTO #temptable FROM '+@.BaseTable

If @.JoinTables Is Not Null
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' ' +@.JoinTables
SET @.SQLSTMT1 = @.SQLSTMT1 + ' ' +@.JoinTables
END

DECLARE @.StmtWhereClause nvarchar(500)

SET @.StmtWhereClause =''

Get where conditional clause

If (@.SearchText Is Not Null AND RTRIM(LTRIM(@.SearchText))<>'')

BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.SearchText
END

If @.ConditionalClause Is Not Null AND RTRIM(LTRIM(@.ConditionalClause))<>''
BEGIN
IF (@.StmtWhereClause <> '')
BEGIN
SET @.StmtWhereClause= @.StmtWhereClause + 'AND ' +@.ConditionalClause
END
ELSE
BEGIN
SET @.StmtWhereClause = @.StmtWhereClause + ' WHERE ' + @.ConditionalClause

END
END

SET @.SQLSTMT = @.SQLSTMT + @.StmtWhereClause

SET @.SQLSTMT1 = @.SQLSTMT1 + @.StmtWhereClause

If @.GroupByFields Is Not Null And RTRIM(LTRIM(@.GroupByFields))<>''
BEGIN
SET @.SQLSTMT = @.SQLSTMT + ' Group By ' +@.GroupByFields
SET @.SQLSTMT1 = @.SQLSTMT1 + ' Group By ' +@.GroupByFields
END

IF @.SortExpression Is Not Null AND RTRIM(LTRIM(@.SortExpression))<>''
BEGIN
SET @.SortExpression = LTRIM(RTRIM(' Order By '+ @.SortExpression))
SET @.SQLSTMT = @.SQLSTMT +' '+ @.SortExpression
SET @.SQLSTMT1 = @.SQLSTMT1 +' '+ @.SortExpression
END

SET @.SQLSTMT = @.SQLSTMT + ' DELETE FROM #temptable WHERE IntRowNum < ' + CAST(@.startRow AS VARCHAR(10)) + ' SELECT * FROM #temptable DROP TABLE #temptable '

SET @.SQLSTMT1= @.SQLSTMT1+' SELECT @.CountValue As MyRows '

--SELECT @.SQLSTMT1
--SELECT @.SQLSTMT

EXECUTE SP_EXECUTESQL @.SQLSTMT

EXECUTE SP_EXECUTESQL @.SQLSTMT1

Refreshing Data

Hi All,
I have A Report inwhich Data is Populated from a stored procedure,
When I add a new record in the database that value is not replicated
immediately in the reports.It does replicate after i close the browser and
reopen it.
Cant Understand where Im going wrong'
Can any1 help me out'
Thanks in advance...u can try hitting the refresh button...
other solution could be change the AutoRefresh property
The report doesn't show the new row because is not a "live" report, it needs
to be refreshed to show new data
Hope it helps,
Pablo Diaz
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> Hi All,
> I have A Report inwhich Data is Populated from a stored procedure,
> When I add a new record in the database that value is not replicated
> immediately in the reports.It does replicate after i close the browser and
> reopen it.
> Cant Understand where Im going wrong'
> Can any1 help me out'
> Thanks in advance...|||By default, we do not re-execute the query unless you click the refresh
button or ctrl-refresh in the browser. This is to maintain consistency
between actions like print, pagination, etc.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
>u can try hitting the refresh button...
> other solution could be change the AutoRefresh property
> The report doesn't show the new row because is not a "live" report, it
> needs to be refreshed to show new data
> Hope it helps,
> Pablo Diaz
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
>> Hi All,
>> I have A Report inwhich Data is Populated from a stored procedure,
>> When I add a new record in the database that value is not replicated
>> immediately in the reports.It does replicate after i close the browser
>> and
>> reopen it.
>> Cant Understand where Im going wrong'
>> Can any1 help me out'
>> Thanks in advance...
>|||But even after I clicked the browser refresh button, it still did not work. I
could see from SQL profiler that the stored proc for the report did not get
executed again.
"Brian Welcker [MSFT]" wrote:
> By default, we do not re-execute the query unless you click the refresh
> button or ctrl-refresh in the browser. This is to maintain consistency
> between actions like print, pagination, etc.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
> news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
> >u can try hitting the refresh button...
> > other solution could be change the AutoRefresh property
> > The report doesn't show the new row because is not a "live" report, it
> > needs to be refreshed to show new data
> >
> > Hope it helps,
> > Pablo Diaz
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> >> Hi All,
> >> I have A Report inwhich Data is Populated from a stored procedure,
> >> When I add a new record in the database that value is not replicated
> >> immediately in the reports.It does replicate after i close the browser
> >> and
> >> reopen it.
> >> Cant Understand where Im going wrong'
> >> Can any1 help me out'
> >>
> >> Thanks in advance...
> >
> >
>
>|||Not the browser refresh button, you want the the refresh button to the right
of export or you have to do a ctrl-F5 (F5 is the browser refresh, Ctrl-F5 is
refresh without caching).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A Fu" <AFu@.discussions.microsoft.com> wrote in message
news:C4601584-0D4B-4C48-9739-18A857B0C7D7@.microsoft.com...
> But even after I clicked the browser refresh button, it still did not
work. I
> could see from SQL profiler that the stored proc for the report did not
get
> executed again.
>
> "Brian Welcker [MSFT]" wrote:
> > By default, we do not re-execute the query unless you click the refresh
> > button or ctrl-refresh in the browser. This is to maintain consistency
> > between actions like print, pagination, etc.
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
> > news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > >u can try hitting the refresh button...
> > > other solution could be change the AutoRefresh property
> > > The report doesn't show the new row because is not a "live" report, it
> > > needs to be refreshed to show new data
> > >
> > > Hope it helps,
> > > Pablo Diaz
> > >
> > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> > >> Hi All,
> > >> I have A Report inwhich Data is Populated from a stored
procedure,
> > >> When I add a new record in the database that value is not replicated
> > >> immediately in the reports.It does replicate after i close the
browser
> > >> and
> > >> reopen it.
> > >> Cant Understand where Im going wrong'
> > >> Can any1 help me out'
> > >>
> > >> Thanks in advance...
> > >
> > >
> >
> >
> >|||Unfortunately the application does not use the built-in report viewer at all.
It directly renders the reports in PDF. The solution I'm using right now is
create a dummy report param, the app will pass a random number to this param,
so the URL will be different each time. Not a great solution.
"Bruce L-C [MVP]" wrote:
> Not the browser refresh button, you want the the refresh button to the right
> of export or you have to do a ctrl-F5 (F5 is the browser refresh, Ctrl-F5 is
> refresh without caching).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A Fu" <AFu@.discussions.microsoft.com> wrote in message
> news:C4601584-0D4B-4C48-9739-18A857B0C7D7@.microsoft.com...
> > But even after I clicked the browser refresh button, it still did not
> work. I
> > could see from SQL profiler that the stored proc for the report did not
> get
> > executed again.
> >
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> > > By default, we do not re-execute the query unless you click the refresh
> > > button or ctrl-refresh in the browser. This is to maintain consistency
> > > between actions like print, pagination, etc.
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
> > > news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > > >u can try hitting the refresh button...
> > > > other solution could be change the AutoRefresh property
> > > > The report doesn't show the new row because is not a "live" report, it
> > > > needs to be refreshed to show new data
> > > >
> > > > Hope it helps,
> > > > Pablo Diaz
> > > >
> > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> > > >> Hi All,
> > > >> I have A Report inwhich Data is Populated from a stored
> procedure,
> > > >> When I add a new record in the database that value is not replicated
> > > >> immediately in the reports.It does replicate after i close the
> browser
> > > >> and
> > > >> reopen it.
> > > >> Cant Understand where Im going wrong'
> > > >> Can any1 help me out'
> > > >>
> > > >> Thanks in advance...
> > > >
> > > >
> > >
> > >
> > >
>
>|||Add this to your URL:
rs:ClearSession=true
How things are done really depends on how you are using it (URL Integration,
Web Services, Report Manager).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A Fu" <AFu@.discussions.microsoft.com> wrote in message
news:D702D576-34FC-4760-9952-E18539108432@.microsoft.com...
> Unfortunately the application does not use the built-in report viewer at
all.
> It directly renders the reports in PDF. The solution I'm using right now
is
> create a dummy report param, the app will pass a random number to this
param,
> so the URL will be different each time. Not a great solution.
>
> "Bruce L-C [MVP]" wrote:
> > Not the browser refresh button, you want the the refresh button to the
right
> > of export or you have to do a ctrl-F5 (F5 is the browser refresh,
Ctrl-F5 is
> > refresh without caching).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "A Fu" <AFu@.discussions.microsoft.com> wrote in message
> > news:C4601584-0D4B-4C48-9739-18A857B0C7D7@.microsoft.com...
> > > But even after I clicked the browser refresh button, it still did not
> > work. I
> > > could see from SQL profiler that the stored proc for the report did
not
> > get
> > > executed again.
> > >
> > >
> > > "Brian Welcker [MSFT]" wrote:
> > >
> > > > By default, we do not re-execute the query unless you click the
refresh
> > > > button or ctrl-refresh in the browser. This is to maintain
consistency
> > > > between actions like print, pagination, etc.
> > > >
> > > > --
> > > > Brian Welcker
> > > > Group Program Manager
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "Pablo" <pabloda@.NOSPAMtimovil.com> wrote in message
> > > > news:udejkBM%23EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > > > >u can try hitting the refresh button...
> > > > > other solution could be change the AutoRefresh property
> > > > > The report doesn't show the new row because is not a "live"
report, it
> > > > > needs to be refreshed to show new data
> > > > >
> > > > > Hope it helps,
> > > > > Pablo Diaz
> > > > >
> > > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > > news:CFD08605-E9FF-45FB-8986-08B047826590@.microsoft.com...
> > > > >> Hi All,
> > > > >> I have A Report inwhich Data is Populated from a stored
> > procedure,
> > > > >> When I add a new record in the database that value is not
replicated
> > > > >> immediately in the reports.It does replicate after i close the
> > browser
> > > > >> and
> > > > >> reopen it.
> > > > >> Cant Understand where Im going wrong'
> > > > >> Can any1 help me out'
> > > > >>
> > > > >> Thanks in advance...
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Friday, March 23, 2012

refresh fields button does not work?

I have a dataset that calls a stored procedure. it's command type is set to
stored procedure.
The stored procedure returns 1 result set from a temp table (like select *
from #temp). In sql query analyzer it only returns 1 grid so I know it's not
returning multiple result sets.
when I hit the ! button I fill out my parameters and run the stored
procedure. It returns all the columns with some data. good. but the only
field I see is one called ID of type database field.
I hit the refresh fields button and nothing changes. The dataset is
returning 1 result set with the columns and data. WhyOWhy are the fields not
being filled out?What backend are you going against (SQL Server, OLEDB, ODBC?).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> I have a dataset that calls a stored procedure. it's command type is set
to
> stored procedure.
> The stored procedure returns 1 result set from a temp table (like select *
> from #temp). In sql query analyzer it only returns 1 grid so I know it's
not
> returning multiple result sets.
> when I hit the ! button I fill out my parameters and run the stored
> procedure. It returns all the columns with some data. good. but the only
> field I see is one called ID of type database field.
> I hit the refresh fields button and nothing changes. The dataset is
> returning 1 result set with the columns and data. WhyOWhy are the fields
not
> being filled out?|||I figured it out!
in my stored procedure I was doing this
create table [#whatever]
select * from #whatever
this runs fine from sql query analyzer but does not return a list of fields
in report designer gui.
but changing my stored procedure to
create table [#whatever]
select * from [#whatever]
fixed the problem. I now get the entire list of fields in the gui.
I'm not sure if I would call that a bug or not but it sure was anoying
trying to figure it out over the last several hours.
"Bruce L-C [MVP]" wrote:
> What backend are you going against (SQL Server, OLEDB, ODBC?).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > I have a dataset that calls a stored procedure. it's command type is set
> to
> > stored procedure.
> >
> > The stored procedure returns 1 result set from a temp table (like select *
> > from #temp). In sql query analyzer it only returns 1 grid so I know it's
> not
> > returning multiple result sets.
> >
> > when I hit the ! button I fill out my parameters and run the stored
> > procedure. It returns all the columns with some data. good. but the only
> > field I see is one called ID of type database field.
> >
> > I hit the refresh fields button and nothing changes. The dataset is
> > returning 1 result set with the columns and data. WhyOWhy are the fields
> not
> > being filled out?
>
>|||Hmmm, you must have had some special characters in it. I create temporary
tables and do a select * from it without have to put [] around it. I'll
remember that though since from time to time I have been unable to help
people who do not get the field list. Learn something new every day.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> I figured it out!
> in my stored procedure I was doing this
> create table [#whatever]
> select * from #whatever
> this runs fine from sql query analyzer but does not return a list of
fields
> in report designer gui.
> but changing my stored procedure to
> create table [#whatever]
> select * from [#whatever]
> fixed the problem. I now get the entire list of fields in the gui.
> I'm not sure if I would call that a bug or not but it sure was anoying
> trying to figure it out over the last several hours.
>
> "Bruce L-C [MVP]" wrote:
> > What backend are you going against (SQL Server, OLEDB, ODBC?).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > > I have a dataset that calls a stored procedure. it's command type is
set
> > to
> > > stored procedure.
> > >
> > > The stored procedure returns 1 result set from a temp table (like
select *
> > > from #temp). In sql query analyzer it only returns 1 grid so I know
it's
> > not
> > > returning multiple result sets.
> > >
> > > when I hit the ! button I fill out my parameters and run the stored
> > > procedure. It returns all the columns with some data. good. but the
only
> > > field I see is one called ID of type database field.
> > >
> > > I hit the refresh fields button and nothing changes. The dataset is
> > > returning 1 result set with the columns and data. WhyOWhy are the
fields
> > not
> > > being filled out?
> >
> >
> >|||all right I take it back after more testing I can reproduce this behavior but
I was not aware it worked like this.
forget what I said before it was just a fluke I must have made a mistake
while testing it.
here is the reproducable behavior.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE
dbo.whatever
@.param_me int
as
-- end results temp table
create table [#whatever]
(
[key] int not null,
)
if (@.param_me is null)
begin
select 'how did you get here?'
end
else
begin
insert into [#whatever]([key])values(1)
insert into [#whatever]([key])values(2)
insert into [#whatever]([key])values(3)
end
select * from [#whatever]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create a dataset that calls whatever passing a param value
it looks like even though the select 'how did you get here?' never gets run
it somehow gets returned as the first result set?
bruce could you give this a whirl and see if you see the same result? I
would appreciate it thanks.
"Bruce L-C [MVP]" wrote:
> Hmmm, you must have had some special characters in it. I create temporary
> tables and do a select * from it without have to put [] around it. I'll
> remember that though since from time to time I have been unable to help
> people who do not get the field list. Learn something new every day.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> > I figured it out!
> >
> > in my stored procedure I was doing this
> > create table [#whatever]
> > select * from #whatever
> >
> > this runs fine from sql query analyzer but does not return a list of
> fields
> > in report designer gui.
> >
> > but changing my stored procedure to
> > create table [#whatever]
> > select * from [#whatever]
> >
> > fixed the problem. I now get the entire list of fields in the gui.
> >
> > I'm not sure if I would call that a bug or not but it sure was anoying
> > trying to figure it out over the last several hours.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > > > I have a dataset that calls a stored procedure. it's command type is
> set
> > > to
> > > > stored procedure.
> > > >
> > > > The stored procedure returns 1 result set from a temp table (like
> select *
> > > > from #temp). In sql query analyzer it only returns 1 grid so I know
> it's
> > > not
> > > > returning multiple result sets.
> > > >
> > > > when I hit the ! button I fill out my parameters and run the stored
> > > > procedure. It returns all the columns with some data. good. but the
> only
> > > > field I see is one called ID of type database field.
> > > >
> > > > I hit the refresh fields button and nothing changes. The dataset is
> > > > returning 1 result set with the columns and data. WhyOWhy are the
> fields
> > > not
> > > > being filled out?
> > >
> > >
> > >
>
>|||I think what is happening is that it is considering the first select to be
the first return result. You could temporarily remove that part, get your
field list and design your report and then put it back in.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> all right I take it back after more testing I can reproduce this behavior
> but
> I was not aware it worked like this.
> forget what I said before it was just a fluke I must have made a mistake
> while testing it.
> here is the reproducable behavior.
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE
> dbo.whatever
> @.param_me int
> as
> -- end results temp table
> create table [#whatever]
> (
> [key] int not null,
> )
> if (@.param_me is null)
> begin
> select 'how did you get here?'
> end
> else
> begin
> insert into [#whatever]([key])values(1)
> insert into [#whatever]([key])values(2)
> insert into [#whatever]([key])values(3)
> end
> select * from [#whatever]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> create a dataset that calls whatever passing a param value
> it looks like even though the select 'how did you get here?' never gets
> run
> it somehow gets returned as the first result set?
> bruce could you give this a whirl and see if you see the same result? I
> would appreciate it thanks.
>
> "Bruce L-C [MVP]" wrote:
>> Hmmm, you must have had some special characters in it. I create temporary
>> tables and do a select * from it without have to put [] around it. I'll
>> remember that though since from time to time I have been unable to help
>> people who do not get the field list. Learn something new every day.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
>> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
>> > I figured it out!
>> >
>> > in my stored procedure I was doing this
>> > create table [#whatever]
>> > select * from #whatever
>> >
>> > this runs fine from sql query analyzer but does not return a list of
>> fields
>> > in report designer gui.
>> >
>> > but changing my stored procedure to
>> > create table [#whatever]
>> > select * from [#whatever]
>> >
>> > fixed the problem. I now get the entire list of fields in the gui.
>> >
>> > I'm not sure if I would call that a bug or not but it sure was anoying
>> > trying to figure it out over the last several hours.
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
>> > > message
>> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
>> > > > I have a dataset that calls a stored procedure. it's command type
>> > > > is
>> set
>> > > to
>> > > > stored procedure.
>> > > >
>> > > > The stored procedure returns 1 result set from a temp table (like
>> select *
>> > > > from #temp). In sql query analyzer it only returns 1 grid so I
>> > > > know
>> it's
>> > > not
>> > > > returning multiple result sets.
>> > > >
>> > > > when I hit the ! button I fill out my parameters and run the stored
>> > > > procedure. It returns all the columns with some data. good. but
>> > > > the
>> only
>> > > > field I see is one called ID of type database field.
>> > > >
>> > > > I hit the refresh fields button and nothing changes. The dataset
>> > > > is
>> > > > returning 1 result set with the columns and data. WhyOWhy are the
>> fields
>> > > not
>> > > > being filled out?
>> > >
>> > >
>> > >
>>|||Yes I think thatâ's become obvious; the first result set that is tripping the
refresh fields button up. Because if I give the select 'how did you get
here?' as error_column. I see error_column in the list of fields, even
though that result set did not get returned and the data for the other result
set is really being displayed.
It's just confusing. All of the tools I'm using query analyzer, query
designer don't return this result set when I run the stored procedure with a
param value other than null.
Whatever the refresh fields button does it's not refresh fields, it's should
be called "return first potential result field list regardless of what
columns are really returned"
It's not a show stopper there are lots of easy work a rounds. It just sucks
using a version 1 product with so many of these little time wasters. But I
already know you think rs is super duper, I don't think so just yet.
try using rs.exe to create a datasource, publish a report and set the
published report datasource in your rs.exe script. It does not work I opened
up a support case 2 weeks ago with microsoft and they are still trying to
figure out why it does not work. lots of little time wasters.
ok I'm done complaining now.
"Bruce L-C [MVP]" wrote:
> I think what is happening is that it is considering the first select to be
> the first return result. You could temporarily remove that part, get your
> field list and design your report and then put it back in.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> > all right I take it back after more testing I can reproduce this behavior
> > but
> > I was not aware it worked like this.
> >
> > forget what I said before it was just a fluke I must have made a mistake
> > while testing it.
> >
> > here is the reproducable behavior.
> >
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > CREATE PROCEDURE
> > dbo.whatever
> > @.param_me int
> >
> > as
> >
> > -- end results temp table
> > create table [#whatever]
> > (
> > [key] int not null,
> > )
> >
> > if (@.param_me is null)
> > begin
> > select 'how did you get here?'
> > end
> > else
> > begin
> > insert into [#whatever]([key])values(1)
> > insert into [#whatever]([key])values(2)
> > insert into [#whatever]([key])values(3)
> > end
> > select * from [#whatever]
> >
> > GO
> > SET QUOTED_IDENTIFIER OFF
> > GO
> > SET ANSI_NULLS ON
> > GO
> >
> > create a dataset that calls whatever passing a param value
> >
> > it looks like even though the select 'how did you get here?' never gets
> > run
> > it somehow gets returned as the first result set?
> >
> > bruce could you give this a whirl and see if you see the same result? I
> > would appreciate it thanks.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Hmmm, you must have had some special characters in it. I create temporary
> >> tables and do a select * from it without have to put [] around it. I'll
> >> remember that though since from time to time I have been unable to help
> >> people who do not get the field list. Learn something new every day.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> >> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> >> > I figured it out!
> >> >
> >> > in my stored procedure I was doing this
> >> > create table [#whatever]
> >> > select * from #whatever
> >> >
> >> > this runs fine from sql query analyzer but does not return a list of
> >> fields
> >> > in report designer gui.
> >> >
> >> > but changing my stored procedure to
> >> > create table [#whatever]
> >> > select * from [#whatever]
> >> >
> >> > fixed the problem. I now get the entire list of fields in the gui.
> >> >
> >> > I'm not sure if I would call that a bug or not but it sure was anoying
> >> > trying to figure it out over the last several hours.
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
> >> > > message
> >> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> >> > > > I have a dataset that calls a stored procedure. it's command type
> >> > > > is
> >> set
> >> > > to
> >> > > > stored procedure.
> >> > > >
> >> > > > The stored procedure returns 1 result set from a temp table (like
> >> select *
> >> > > > from #temp). In sql query analyzer it only returns 1 grid so I
> >> > > > know
> >> it's
> >> > > not
> >> > > > returning multiple result sets.
> >> > > >
> >> > > > when I hit the ! button I fill out my parameters and run the stored
> >> > > > procedure. It returns all the columns with some data. good. but
> >> > > > the
> >> only
> >> > > > field I see is one called ID of type database field.
> >> > > >
> >> > > > I hit the refresh fields button and nothing changes. The dataset
> >> > > > is
> >> > > > returning 1 result set with the columns and data. WhyOWhy are the
> >> fields
> >> > > not
> >> > > > being filled out?
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Glad you got it to work out. Yes I am a supporter of RS but I don't deny
there are things that need to get better (it is version 1). That said, I
think any product or development work has these types of frustrations. I
just spent a day mucking around with linked databases and finally found that
the issue comes down to the oledb/odbc provider for Sybase. So, day wasted
and figure out some other way to do it. I just try to get people to be able
to get done what they are trying to do.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
news:D3C917F1-06B6-4B03-92DC-7D6D261EFAC4@.microsoft.com...
> Yes I think that's become obvious; the first result set that is tripping
the
> refresh fields button up. Because if I give the select 'how did you get
> here?' as error_column. I see error_column in the list of fields, even
> though that result set did not get returned and the data for the other
result
> set is really being displayed.
> It's just confusing. All of the tools I'm using query analyzer, query
> designer don't return this result set when I run the stored procedure with
a
> param value other than null.
> Whatever the refresh fields button does it's not refresh fields, it's
should
> be called "return first potential result field list regardless of what
> columns are really returned"
> It's not a show stopper there are lots of easy work a rounds. It just
sucks
> using a version 1 product with so many of these little time wasters. But
I
> already know you think rs is super duper, I don't think so just yet.
> try using rs.exe to create a datasource, publish a report and set the
> published report datasource in your rs.exe script. It does not work I
opened
> up a support case 2 weeks ago with microsoft and they are still trying to
> figure out why it does not work. lots of little time wasters.
> ok I'm done complaining now.
>
> "Bruce L-C [MVP]" wrote:
> > I think what is happening is that it is considering the first select to
be
> > the first return result. You could temporarily remove that part, get
your
> > field list and design your report and then put it back in.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in message
> > news:BE164DC8-D6C8-4601-8C3B-E10B1B07B6F3@.microsoft.com...
> > > all right I take it back after more testing I can reproduce this
behavior
> > > but
> > > I was not aware it worked like this.
> > >
> > > forget what I said before it was just a fluke I must have made a
mistake
> > > while testing it.
> > >
> > > here is the reproducable behavior.
> > >
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > > CREATE PROCEDURE
> > > dbo.whatever
> > > @.param_me int
> > >
> > > as
> > >
> > > -- end results temp table
> > > create table [#whatever]
> > > (
> > > [key] int not null,
> > > )
> > >
> > > if (@.param_me is null)
> > > begin
> > > select 'how did you get here?'
> > > end
> > > else
> > > begin
> > > insert into [#whatever]([key])values(1)
> > > insert into [#whatever]([key])values(2)
> > > insert into [#whatever]([key])values(3)
> > > end
> > > select * from [#whatever]
> > >
> > > GO
> > > SET QUOTED_IDENTIFIER OFF
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > >
> > > create a dataset that calls whatever passing a param value
> > >
> > > it looks like even though the select 'how did you get here?' never
gets
> > > run
> > > it somehow gets returned as the first result set?
> > >
> > > bruce could you give this a whirl and see if you see the same result?
I
> > > would appreciate it thanks.
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> Hmmm, you must have had some special characters in it. I create
temporary
> > >> tables and do a select * from it without have to put [] around it.
I'll
> > >> remember that though since from time to time I have been unable to
help
> > >> people who do not get the field list. Learn something new every day.
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
message
> > >> news:6D9998C0-B7E2-4900-A800-B93504EF8EF6@.microsoft.com...
> > >> > I figured it out!
> > >> >
> > >> > in my stored procedure I was doing this
> > >> > create table [#whatever]
> > >> > select * from #whatever
> > >> >
> > >> > this runs fine from sql query analyzer but does not return a list
of
> > >> fields
> > >> > in report designer gui.
> > >> >
> > >> > but changing my stored procedure to
> > >> > create table [#whatever]
> > >> > select * from [#whatever]
> > >> >
> > >> > fixed the problem. I now get the entire list of fields in the gui.
> > >> >
> > >> > I'm not sure if I would call that a bug or not but it sure was
anoying
> > >> > trying to figure it out over the last several hours.
> > >> >
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > What backend are you going against (SQL Server, OLEDB, ODBC?).
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > > "letuce dance" <letucedance@.discussions.microsoft.com> wrote in
> > >> > > message
> > >> > > news:1D8DF4D7-56D3-4A9A-923B-63A7F82CA886@.microsoft.com...
> > >> > > > I have a dataset that calls a stored procedure. it's command
type
> > >> > > > is
> > >> set
> > >> > > to
> > >> > > > stored procedure.
> > >> > > >
> > >> > > > The stored procedure returns 1 result set from a temp table
(like
> > >> select *
> > >> > > > from #temp). In sql query analyzer it only returns 1 grid so I
> > >> > > > know
> > >> it's
> > >> > > not
> > >> > > > returning multiple result sets.
> > >> > > >
> > >> > > > when I hit the ! button I fill out my parameters and run the
stored
> > >> > > > procedure. It returns all the columns with some data. good.
but
> > >> > > > the
> > >> only
> > >> > > > field I see is one called ID of type database field.
> > >> > > >
> > >> > > > I hit the refresh fields button and nothing changes. The
dataset
> > >> > > > is
> > >> > > > returning 1 result set with the columns and data. WhyOWhy are
the
> > >> fields
> > >> > > not
> > >> > > > being filled out?
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >

Wednesday, March 21, 2012

Referring to another DB in a stored procedure.

Hey,
I have a stored procedure in a SQL Server DB that I need to access a
table in a differant DB on the same server. Can someone help me with
the syntax for this? I am lost.
EX - I am in DB "XYZ" using stored procedure "QQQ" and in this stored
procedure I want to reference a table "account" on another DB "123".
What is the syntax to do this?
Right now I have 123.account but that does not work.
Thanks for the help
BrianBrian,
Try:
DATABASE.OWNER.OBJECT
so
123.dbo.account
HTH
Jerry
"blinky44" <briandunderhill@.hotmail.com> wrote in message
news:1129310332.718912.225520@.f14g2000cwb.googlegroups.com...
> Hey,
> I have a stored procedure in a SQL Server DB that I need to access a
> table in a differant DB on the same server. Can someone help me with
> the syntax for this? I am lost.
> EX - I am in DB "XYZ" using stored procedure "QQQ" and in this stored
> procedure I want to reference a table "account" on another DB "123".
> What is the syntax to do this?
> Right now I have 123.account but that does not work.
> Thanks for the help
> Brian
>|||perfect, thanks! God I hate being a newbie! :)|||Gotta start somewhere.
Andrew J. Kelly SQL MVP
"blinky44" <briandunderhill@.hotmail.com> wrote in message
news:1129310914.913612.76840@.g14g2000cwa.googlegroups.com...
> perfect, thanks! God I hate being a newbie! :)
>sql

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
--
Thanks and Regards,
PrasanthHI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
> > Hi,
> > I am facing a problem while reading user database tables from a procedure in
> > the master database.
> > Example.
> > create procedure sp_Test
> > as
> > select count(*) from syscolumns
> > select count(*) from UserTable
> > go
> >
> > I granted public access to this procedure in the master database.
> > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > the record counts from the master tables.
> > When I execute it for some other user database, it returns the count of
> > records from syscolumn tables of that local database, but still takes the
> > count from UserTable of master database. It does not take the count from the
> > local database.
> >
> > Could anyone help me know, if there is any setting, to point to the local
> > database instead of master while refering user tables (for system tables it
> > takes from local databases). Version : SQL Server 2000
> > --
> > Thanks and Regards,
> > Prasanth|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:
> > HI
> > USE master
> > GO
> > ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> > as
> >
> > DECLARE @.strSQL as nvarchar(100)
> >
> > set @.strSQL = 'select count(*) from ' + @.UserTable
> >
> > select count(*) from syscolumns
> > EXECUTE sp_executesql @.strSQL
> >
> > GO
> >
> > USE pubs
> > GO
> > sp_test 'pubs', 'employee'
> > GO
> >
> > Andras Jakus MCDBA
> >
> > "Prasanth" wrote:
> >
> > > Hi,
> > > I am facing a problem while reading user database tables from a procedure in
> > > the master database.
> > > Example.
> > > create procedure sp_Test
> > > as
> > > select count(*) from syscolumns
> > > select count(*) from UserTable
> > > go
> > >
> > > I granted public access to this procedure in the master database.
> > > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > > the record counts from the master tables.
> > > When I execute it for some other user database, it returns the count of
> > > records from syscolumn tables of that local database, but still takes the
> > > count from UserTable of master database. It does not take the count from the
> > > local database.
> > >
> > > Could anyone help me know, if there is any setting, to point to the local
> > > database instead of master while refering user tables (for system tables it
> > > takes from local databases). Version : SQL Server 2000
> > > --
> > > Thanks and Regards,
> > > Prasanthsql

Friday, March 30, 2012

Regarding Creating Stored Procedures If One Does Not Exist

Regarding Creating Stored Procedures If One Does Not Exist

Regarding Creating Stored Procedures If One Does Not Exist

Wednesday, March 28, 2012

reg Stored procedure Performance

Monday, March 26, 2012

refreshview removes user defined functions from sysdepends

Refreshing Tables In Database

Refreshing data in the cursor.

Refreshing Data

Friday, March 23, 2012

refresh fields button does not work?

Wednesday, March 21, 2012

Referring to another DB in a stored procedure.

Refering Local DB Tables from Master Procedures

Refering Local DB Tables from Master Procedures

reduce databse size

Blog Archive

About Me