Refreshing the field list

Hi all,
running RS with the serve on sp2 - have a question about refreshing the
field list in the data tab of the designer.
I have copied (and changed slightly) a stored procedure that one of our
reports runs on. All of the field names are the same, that didn't change...I
can run the SP in the designer, and was attempting to change the SP the
report uses to the newer version. I can get no fields in the field list, and
if I try to preview the report I have boatloads of errors (one for every
field, because it doesn't know where the data is).
If you create a new sp for a report to run on, but the new sp has all of the
same field names, etc. , shouldn't you be able to 'plug and play') - the
field names being returned when I run the query in the RS data tab are the
same as the fieldd names from the original query.
Cheers,Execute the SP from the Data source area and the fields will be refreshed..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:CA47B9E1-8F90-41DA-91B5-34D5F27641C2@.microsoft.com...
> Hi all,
> running RS with the serve on sp2 - have a question about refreshing the
> field list in the data tab of the designer.
> I have copied (and changed slightly) a stored procedure that one of our
> reports runs on. All of the field names are the same, that didn't
> change...I
> can run the SP in the designer, and was attempting to change the SP the
> report uses to the newer version. I can get no fields in the field list,
> and
> if I try to preview the report I have boatloads of errors (one for every
> field, because it doesn't know where the data is).
> If you create a new sp for a report to run on, but the new sp has all of
> the
> same field names, etc. , shouldn't you be able to 'plug and play') - the
> field names being returned when I run the query in the RS data tab are the
> same as the fieldd names from the original query.
>
> Cheers,
>
>

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 report problem

Hi,
I did my report under a schema called billing and the new data is in schema called inteltest. Even i connect to the crystal reports using inteltest in the sql query it shows schema as billing4 . please help meHi, In the report goto File and see "Save Data wtih Report" option. If it is checked then uncheck it and save the report

Madhivanan

Refreshing Report Data in ReportViewer

Hi All,
I am using reportviewer component to present data,
On Clicking report button report is generated with data , now if one makes
some changes into database. and on cliking the view report button again
(second time or n times), it actually does not go to database and get the
latest update, Same old data is displayed with out updating with new records.
How to get the latest data from database on click of Report button when
there is change in the data in database
To over the above problem i followed two steps as follows.
1. In the Report Manager -> Property tab -> Execution Linke
the option "Render this report with the most recent data" is selected.
But this doen't work.
2. I don't want to check the AutoFresh Property of Report in VS.NET ( Reprot
Designer).
I am sure this is common problem which all might have faced .
It's very urgent, Pls Help
Thanks is Advance.
Regards,
Ajay Kumar
Thanks,
Jim.If you are doing this manually you can click the refresh button in the
report viewer. I have found that the "view report" button doesn't
refresh the report...it generally works off of a cached copy even if
you aren't caching. There is a small button just to the right of the
export area that will refresh the report.|||Yes, i know that there is refresh button but i need automatically. Any how
i have solved this by adding a line in reportviewer control.
Thanks
Ajay
"Luke" wrote:
> If you are doing this manually you can click the refresh button in the
> report viewer. I have found that the "view report" button doesn't
> refresh the report...it generally works off of a cached copy even if
> you aren't caching. There is a small button just to the right of the
> export area that will refresh the report.
>

refreshing report data

I found a post by someone who was having a problem with their reports
refreshing and the solution they got was...
"Please make sure that Report property "Autorefresh" is set to 0
(disabled)"
Where is this report property' I can't find it and I need this to
refresh my report...It's in the Report Properties dialog -> General tab.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:3d6ebe80.0409290348.6e07573b@.posting.google.com...
> I found a post by someone who was having a problem with their reports
> refreshing and the solution they got was...
> "Please make sure that Report property "Autorefresh" is set to 0
> (disabled)"
> Where is this report property' I can't find it and I need this to
> refresh my report...|||this isn't critical but I'm just curious at this stage - that option
isn't there for me... I've SP1 installed - does that make a
difference? Here's what I see on my report properties - general
tab...
Modified Date: 29/09/2004 15:58
Modified By: DOMAIN\user
Creation Date: 29/09/2004 15:58
Created By: DOMAIN\user
Size: 77 KB
Properties ----
--
Name: |______________________________|
Description: ---
| |
| |
| |
| |
|___________________________________________|
--
|_| Hide in list view
Report Definition ---
Edit Update
-- --
----
-- -- -- --
|Apply| |Create Linked Report| |Delete| |Move|
-- -- -- --
Create a linked report when you want to use different security or
parameters with the report.
"Ravi Mumulla \(Microsoft\)" <ravimu@.online.microsoft.com> wrote in message news:<#GhnpIjpEHA.2684@.TK2MSFTNGP11.phx.gbl>...
> It's in the Report Properties dialog -> General tab.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Gearoid" <gearoid_healy@.yahoo.com> wrote in message
> news:3d6ebe80.0409290348.6e07573b@.posting.google.com...
> > I found a post by someone who was having a problem with their reports
> > refreshing and the solution they got was...
> >
> > "Please make sure that Report property "Autorefresh" is set to 0
> > (disabled)"
> >
> > Where is this report property' I can't find it and I need this to
> > refresh my report...sql

Refreshing Parameter values when a higher level Parameter changes

Hi All.

I have a parameter (hidden) that gets its value using an expression base on another parameter.
When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.

When I change the parent parameter again, the value of the child parameter does not seem to change.

How can I make this parameter change automatically when the parent is changed ?

Any help will be appreciated.

Thanks,
Neil

I know a little about this, mainly because i have spent a lot of time doing the reverse - breaking the linkage so that parameters don't update when another changes.

The linkage between parameters is achieved via the ParameterDefinition nodes under the Dataset/Query/rd:MdxQuery/Query/Statement* node. These can be added, but can be hard to get right (it is easy to take them out, hard to put them in). What can be the easiest thing to do is create a new report with just the parent and child (hidden) parameters, by default the designer will make the child one dependant on the parent parameter, and you can then check the generated rdl to see how it is done.

Hope this helps!

*this is based on a report that uses a cube and MDX, i don't have a SQL one to hand that i can give an example from.

|||Thank you!

well, this was a point into the right direction.. I landed up setting the available values to the parent parameter and then whenever the parent parameter changed, so did this child one...

Thanks for the help!!

Refreshing links in frontend

Hi everyone,
Could someone explain to me how to refresh the links to my sql server?
Each time i modify or create a field in a table, the modifications are
not implented in the front-end. How can i resolve this.
Greetings,
StefanieAre you selecting from views with SELECT *? In this case, check out
sp_refreshview in the Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Stefanie Pindew" <spindew@.yahoo.com> wrote in message
news:ehJkMpZXDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hi everyone,
> Could someone explain to me how to refresh the links to my sql server?
> Each time i modify or create a field in a table, the modifications are
> not implented in the front-end. How can i resolve this.
> Greetings,
> Stefanie
>|||Or are you referring to an Access front end? In that case,
you can use the Linked Table Manager or do it
programmatically with the refresh method of the tabledefs
collection.
--Sue
On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
<spindew@.yahoo.com> wrote:
>Hi everyone,
>Could someone explain to me how to refresh the links to my sql server?
>Each time i modify or create a field in a table, the modifications are
>not implented in the front-end. How can i resolve this.
>Greetings,
>Stefanie|||>--Original Message--
>Or are you referring to an Access front end? In that case,
>you can use the Linked Table Manager or do it
>programmatically with the refresh method of the tabledefs
>collection.
>--Sue
>On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
><spindew@.yahoo.com> wrote:
>>Hi everyone,
>>Could someone explain to me how to refresh the links to
my sql server?
>>Each time i modify or create a field in a table, the
modifications are
>>not implented in the front-end. How can i resolve this.
>>Greetings,
>>Stefanie
>.
>Hi,
I want it do it programmatically in the access front-end
and in the access adp front-end|||Stefanie,
I've just checked the following code and it works:
Dim tdLoop As TableDef
For Each tdLoop In CurrentDb.TableDefs
tdLoop.RefreshLink
Next
However, I can remember having problems when using the
.RefreshLink method... Give it a try though.
Regards,
Danny
"Stefanie Pindew" <spindew@.lollo.com> wrote in message news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
> >--Original Message--
> >Or are you referring to an Access front end? In that case,
> >you can use the Linked Table Manager or do it
> >programmatically with the refresh method of the tabledefs
> >collection.
> >
> >--Sue
> >
> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
> ><spindew@.yahoo.com> wrote:
> >
> >>Hi everyone,
> >>
> >>Could someone explain to me how to refresh the links to
> my sql server?
> >>Each time i modify or create a field in a table, the
> modifications are
> >>not implented in the front-end. How can i resolve this.
> >>
> >>Greetings,
> >>
> >>Stefanie
> >
> >.
> >Hi,
> I want it do it programmatically in the access front-end
> and in the access adp front-end|||>--Original Message--
>Stefanie,
>I've just checked the following code and it works:
>Dim tdLoop As TableDef
>For Each tdLoop In CurrentDb.TableDefs
> tdLoop.RefreshLink
>Next
>However, I can remember having problems when using the
>..RefreshLink method... Give it a try though.
>Regards,
>Danny
>
>"Stefanie Pindew" <spindew@.lollo.com> wrote in message
news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
>> >--Original Message--
>> >Or are you referring to an Access front end? In that
case,
>> >you can use the Linked Table Manager or do it
>> >programmatically with the refresh method of the
tabledefs
>> >collection.
>> >
>> >--Sue
>> >
>> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
>> ><spindew@.yahoo.com> wrote:
>> >
>> >>Hi everyone,
>> >>
>> >>Could someone explain to me how to refresh the links
to
>> my sql server?
>> >>Each time i modify or create a field in a table, the
>> modifications are
>> >>not implented in the front-end. How can i resolve
this.
>> >>
>> >>Greetings,
>> >>
>> >>Stefanie
>> >
>> >.
>> >Hi,
>> I want it do it programmatically in the access front-end
>> and in the access adp front-end
>
>.
>He thanks man, it works for me too|||Stefanie,
There are some issues with using .RefreshLink with Access97,
but I can't remember what they are. In case you have any
problems, I ended up implementing a solution that deleted the
linked tables from within Access and then re-linked them.
Both tasks can be accomplished using the DoCmd object:
DoCmd.DeleteObject
DoCmd.TransferDatabase
Email me if you want some sample code.
Regards,
Danny
"Stefanie Pindew" <spindew@.lollo.com> wrote in message news:066501c35ffe$995740b0$a301280a@.phx.gbl...
> >--Original Message--
> >Stefanie,
> >
> >I've just checked the following code and it works:
> >
> >Dim tdLoop As TableDef
> >For Each tdLoop In CurrentDb.TableDefs
> > tdLoop.RefreshLink
> >Next
> >
> >However, I can remember having problems when using the
> >..RefreshLink method... Give it a try though.
> >
> >Regards,
> >Danny
> >
> >
> >"Stefanie Pindew" <spindew@.lollo.com> wrote in message
> news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
> >>
> >> >--Original Message--
> >> >Or are you referring to an Access front end? In that
> case,
> >> >you can use the Linked Table Manager or do it
> >> >programmatically with the refresh method of the
> tabledefs
> >> >collection.
> >> >
> >> >--Sue
> >> >
> >> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
> >> ><spindew@.yahoo.com> wrote:
> >> >
> >> >>Hi everyone,
> >> >>
> >> >>Could someone explain to me how to refresh the links
> to
> >> my sql server?
> >> >>Each time i modify or create a field in a table, the
> >> modifications are
> >> >>not implented in the front-end. How can i resolve
> this.
> >> >>
> >> >>Greetings,
> >> >>
> >> >>Stefanie
> >> >
> >> >.
> >> >Hi,
> >>
> >> I want it do it programmatically in the access front-end
> >> and in the access adp front-end
> >
> >
> >.
> >He thanks man, it works for me too

Refreshing Links in Access doesn't allow me to Add Records!

Hi
I have an application in Access2003 with linked tables. When I create the
links manually (picking DNS etc) I have no problems. When I then try to
recreate the links programatically (different users) I am not able to add an
y
records to any of the tables! If I erase all the linked tables and reconnect
manually with the other user I have no problems!
I am using the relink code from
http://support.microsoft.com/defaul...kb;en-us;159691
Any and all suggestions Welcome!!!
Regards
Meir
Suggestions?There is a seperate newgroup for acceess/adp/sql server integration.
Post there
--
Regards
R.D
--Knowledge gets doubled when shared
"mrrcomp" wrote:

> Hi
> I have an application in Access2003 with linked tables. When I create the
> links manually (picking DNS etc) I have no problems. When I then try to
> recreate the links programatically (different users) I am not able to add
any
> records to any of the tables! If I erase all the linked tables and reconne
ct
> manually with the other user I have no problems!
> I am using the relink code from
> http://support.microsoft.com/defaul...kb;en-us;159691
>
> Any and all suggestions Welcome!!!
> Regards
> Meir
>
> Suggestions?
>|||As long as the user has permissions in the database and you have a
primary key defined on the table, inserts an dupdates shouldn't be a
problem. However, instead of using a DSN, you can supply connection
information in your code. This should get you started:
Public Sub LinkODBConnectionString()
Dim strConnection As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
' Specify the driver, the server, and the connection
strConnection = "ODBC;Driver={SQL Server};" & _
" Server=(local);Database=SqlDbName;Truste
d_Connection=Yes"
' Specifying a SQLS user/password instead of integrated security
' strConnection = "ODBC;Driver={SQL Server};" & _
' " Server=(Local);Database=SqlDbName;UID=Us
erName;PWD=password"
' Create Linked Table. The LinkedTableName and the
' ServerTableName can be the same.
Set tdf = db.CreateTableDef("LinkedTableName")
tdf.Connect = strConnection
tdf.SourceTableName = "ServerTableName"
db.TableDefs.Append tdf
Set tdf = Nothing
End Sub
On Fri, 7 Oct 2005 05:17:03 -0700, "mrrcomp"
<mrrcomp@.discussions.microsoft.com> wrote:

>Hi
>I have an application in Access2003 with linked tables. When I create the
>links manually (picking DNS etc) I have no problems. When I then try to
>recreate the links programatically (different users) I am not able to add a
ny
>records to any of the tables! If I erase all the linked tables and reconnec
t
>manually with the other user I have no problems!
>I am using the relink code from
>http://support.microsoft.com/defaul...kb;en-us;159691
>
>Any and all suggestions Welcome!!!
>Regards
>Meir
>
>Suggestions?sql

refreshing index after updates to indexed column in database?

I have just begun to experiment with the full text searching capabilities in
SQL2000. Very happy so far! Nice work. I like the INFLECTIONAL results.
When the user makes a change or addition to a row's full-text-indexed column
(e.g. adds the phrase "now available in brushed aluminum" to the Description
column) what needs to be done so that the full-text indexes reflect the
change/addition and the row will be found in subsequent searches? Is there
a daemon that does reindexing periodically that needs to be configured, or
can updates be made to appear in real time, or does the index have to be
rebuilt in toto manually?
Thanks!
TR
Change tracking does near real time updates. To enable change tracking use the following proc
sp_fulltext_table 'TableName','start_change_tracking'
sp_fulltext_table 'TableName','start_background_updateindex'
You do not need a timestamp column on your table.
If you want to do an incremental index and your table has a time stamp column you have to manually kick off the indexing process. If your table does not have a timestamp column a full population will be run, and again you have to manually kick it off.
use
sp_fulltext_table 'TableName','start_incremental' -- for an incremental population
or
sp_fulltext_table 'TableName','start_full' -- for a full population
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
-- TR wrote: --
I have just begun to experiment with the full text searching capabilities in
SQL2000. Very happy so far! Nice work. I like the INFLECTIONAL results.
When the user makes a change or addition to a row's full-text-indexed column
(e.g. adds the phrase "now available in brushed aluminum" to the Description
column) what needs to be done so that the full-text indexes reflect the
change/addition and the row will be found in subsequent searches? Is there
a daemon that does reindexing periodically that needs to be configured, or
can updates be made to appear in real time, or does the index have to be
rebuilt in toto manually?
Thanks!
TR
|||Thanks!

refreshing database before restart

i am running a game from a dedi box .. ms sql..

and every time i restart and put it back up ,,,,it rewinds the database

the only way i can prevent this is if i restart the server

wait a phew hours then put it back up ,,"which makes it the same way it was when i logged off"

i was told that sql database servers refresh every hour.

is there a way i can make it refresh before i restart it so then i can put the server straight back up

\

\

\

if i restart the server back up straight away it does a rewind for some reason.

\

\

tech ::

the server does a rewind because it wouldn't have saved the game so to speak - as in updated everyone's characters. So yes waiting is the only way as far as I know to save the game.

This must be wrapped up in the game's logic where it keeps one database transaction open for a long time (like an hour).

Proper database behavior is that if an update transaction is never committed by the application, the updates are rolled back (rewound) if for any reason the connection is broken.

There is NOTHING in SQL that limits updates to that kind of frequency.

Unfortunately, you need to talk to whoever made/runs the game. They can chat with us and we'll help them.

|||ok thanks for ypur help anyway

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

)

AS

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

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 in Hyperion Essbase Cubes

Hi all,

Normally I would be using SSAS but our finance department make use of Hyperion Essbase. I was wondering whether it was possible to upload data into an Essbase cube using SSIS in the same way that you can use the Analysis Services processing task? I realise there are no specific task for Essbase, but are there any suggestions about what would be the best way of going about this?

regards

Colin

Phew, not sure about this one. Is there an API for Hyperion? if so, perhaps you could call it from a Script Task?

-Jamie

|||Even less sure, but is there an OLE-DB provider which could be use?|||

I suggest asking on the Hyperion Developer network. http://dev.hyperion.com/

If it is possible to update a cube using Visual Basic .Net, then you can do this using script in SSIS. It should be possible through their API: http://dev.hyperion.com/techdocs/essbase/essbase_61/Docs/api/api_vb/mavb/homemav.htm

In the future, there will be tighter integration between SSIS and Hyperion. http://www.microsoft.com/presspass/press/2006/apr06/04-25HyperionBIPR.mspx

Donald

Refreshing data in GridView

Hi,

I’m new to SQL Express, but I have created a table and a stored proc to populate it.

I have dragged the table into a form so I can view the data in a GridView.

I have added a button to add new rows to the table.

All the above works fine, except when I hit add, the data gets added, but the GridView doesn’t update and show the new data. Is there some code I can add to the add button that also refreshed the GridView?

Thanks

Mike

You can use Response.Redirect(yourpage.aspx)|||There was a mistake above. That should be Response.Redirect("yourpage.aspx")sql

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...
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Refreshing a User Defined Type

Hi,
Using a User Define Type (a class written in C# for use in SQL 2005).
I have added my assembly and created a new Type and tested inserting and
selecting.
I now need to add a new field to my class.
How can I update the assembly and the type without disrupting the data
that is currently stored under the UDT.
Many Thanks in Advance
Stuart
*** Sent via Developersdex http://www.examnotes.net ***Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:exAtpR7OGHA.3856@.TK2MSFTNGP12.phx.gbl:

> Using a User Define Type (a class written in C# for use in SQL 2005).
> I have added my assembly and created a new Type and tested inserting
> and selecting.
> I now need to add a new field to my class.
> How can I update the assembly and the type without disrupting the data
> that is currently stored under the UDT.
>
Unless you have created the UDT with Format.UserDefined you can't. If
you have created it with UserDefined you need to, in your Read method,
cater for the fact that the new field may not be available. Then you run
ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns9777AABB8FC2Bnielsbdevelopcom@.20
7.46.248.16...
> Unless you have created the UDT with Format.UserDefined you can't. If
> you have created it with UserDefined you need to, in your Read method,
> cater for the fact that the new field may not be available. Then you run
> ALTER ASSEMBLY and you use the WITH UNCHECKED DATA option.
Doing this is not a good idea for a number of reasons. It will only work if
your UDT is IsFixedLength = false. Any DBCC CheckTable command on your UDT
will now fail. If your UDT is IsByteOrdered=True, you will end up with 2
udts that have the same value but with different byte representation,
potentially returning wrong results in queries that filter on your UDT.
Additional reasons this is a bad idea are left unspecified.
A better approach is to make a new column and copy your UDT data into it in
either binary or string format. Drop the type and create it again from your
new assembly and reparse the old udt data into the new UDT column. This may
seem like a lot more work, but it's the only way to do it right.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:

> Doing this is not a good idea for a number of reasons. It will only
> work if your UDT is IsFixedLength = false. Any DBCC CheckTable
> command on your UDT will now fail.
Well, I never said it was a good idea :-)

> If your UDT is IsByteOrdered=True,
> you will end up with 2 udts that have the same value but with
> different byte representation, potentially returning wrong results in
> queries that filter on your UDT.
Hmm, can you elaborate on that; if my read method caters for missing a
field, how can two udt's with the same value have different byte
representations?
[SNIP]
> A better approach is to make a new column and copy your UDT data into
> it in either binary or string format. Drop the type and create it
> again from your new assembly and reparse the old udt data into the new
> UDT column. This may seem like a lot more work, but it's the only way
> to do it right.
OK, so when you say re=parse the data, I assume you mean that the user
has to - before inserting the old data into the new column - either
update the old data so it contains some bogus value for the missing
field, or have the write method handle it?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Thanks for the replies guys.
However how would i handle the case where a field was removed from the
UDT, would i then have to copy all the fields individually when copying
the class to another column ?
Stuart
*** Sent via Developersdex http://www.examnotes.net ***|||Stuart Ferguson <stuart_ferguson1@.btinternet.com> wrote in
news:u1v2IoGPGHA.740@.TK2MSFTNGP12.phx.gbl:

> However how would i handle the case where a field was removed from the
> UDT, would i then have to copy all the fields individually when
> copying the class to another column ?
>
Well, as I wrote in my original reply, the easiest way is that you
handle it in your read and write methods. However, if you copy the data
to a separate table, then - in this scenario - you should copy it as the
string representation. Then you would need to update that data and take
away the removed field. Finally you insert the data back into the table
with the updated type.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns97786A60D5CDAnielsbdevelopcom@.20
7.46.248.16...
> "Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
> news:e$Y15v9OGHA.3164@.TK2MSFTNGP11.phx.gbl:
> Hmm, can you elaborate on that; if my read method caters for missing a
> field, how can two udt's with the same value have different byte
> representations?
What if you run Alter Assembly and your read method doesn't get called
again? When queries refer to your IsByteOrdered udt, the udt isn't
instantiated so your read method isn't called.
--Assume Point is Format.UserDefined, IsByteOrdered=True,
IsFixedLength=False, and MaxByteSize leaves enough room for the alter
assembly expansion
CREATE TABLE DataPoints(c1 Point)
go
insert into DataPoints values(convert(Point, '(2,2)'))
go
--now alter the assembly that contains Point to add a new field to the type
ALTER ASSEMBLY Point_Assembly
go
--This won't return any rows because the bytes of the new udt Point for
(2,2) are different than the bytes for the old udt Point (2,2)
select * from DataPoints where c1 = convert(Point, '(2,2)')
In order for this to work, you'd need to write a stored-proc to go through
and 'refresh' your udt every place it is persisted. If you're going to do
this, you might as well do it the safe way and drop the type to ensure that
you catch all cases and don't hit any surprises.

> OK, so when you say re=parse the data, I assume you mean that the user
> has to - before inserting the old data into the new column - either
> update the old data so it contains some bogus value for the missing
> field, or have the write method handle it?
I think you mean the Read method, but that's correct. If the String
representation of the type doesn't change, then the user could go through
the Parse method instead without requiring additional work.
Obviously, changing the serialization of your UDT when it's already in use
is something to be avoided if at all possible.
Steven|||"Steven Hemingray [MSFT]" <stevehem@.online.microsoft.com> wrote in
news:OEBRH7IPGHA.2440@.TK2MSFTNGP11.phx.gbl:

> What if you run Alter Assembly and your read method doesn't get called
> again? When queries refer to your IsByteOrdered udt, the udt isn't
> instantiated so your read method isn't called.
> --Assume Point is Format.UserDefined, IsByteOrdered=True,
> IsFixedLength=False, and MaxByteSize leaves enough room for the alter
> assembly expansion
> CREATE TABLE DataPoints(c1 Point)
> go
> insert into DataPoints values(convert(Point, '(2,2)'))
> go
> --now alter the assembly that contains Point to add a new field to the
> type ALTER ASSEMBLY Point_Assembly
> go
> --This won't return any rows because the bytes of the new udt Point
> for (2,2) are different than the bytes for the old udt Point (2,2)
> select * from DataPoints where c1 = convert(Point, '(2,2)')
>
Hmm, I see your point (pun intended).
Quick question, if this is now the case, is there any reason for ALTER
ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
always move the data to some other table and then re-populate.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns977952A6CDE45nielsbdevelopcom@.20
7.46.248.16...
> Quick question, if this is now the case, is there any reason for ALTER
> ASSEMBLY ... WITH UNCHECKED DATA? I.e., when changing a type we should
> always move the data to some other table and then re-populate.
Yep - Alter Assembly's primary use case is for fixing bugs in your code.
With UDTs, you may have a bug in one of the methods that you want to fix or
you may need to add a new method. With Format.Native UDTs, you can do this
without requiring WITH UNCHECKED DATA even when your type is persisted
because the server can verify that the serialization did not change.
(UNCHECKED DATA might still be required in cases where a UDT method is
persisted somewhere). With Format.UserDefined UDTs, you could change the
serialization and the server would never know it so WITH UNCHECKED DATA is
required even if you are not modifying the serialization.
Steven

Refreshing a report

I use a javascript to close a window and refresh another window that contains
a report. I'm trying to actually refresh the report and have it get new data
from a table.
For some reason I refresh the browser and the report doesn't refresh until
the 2nd time it's done. Anyone know why or how to fix this?Hi John,
The problem probably is that the report gets data from cache. You can
disable this in URL you are using in the Report Rendering with
"rs:ClearSession=true" option. For example I use
http://localhost/reportserver?/MyReport&rs:Command=Render&rs:ClearSession=true
Hope solve your problem.
"John" wrote:
> I use a javascript to close a window and refresh another window that contains
> a report. I'm trying to actually refresh the report and have it get new data
> from a table.
> For some reason I refresh the browser and the report doesn't refresh until
> the 2nd time it's done. Anyone know why or how to fix this?
>|||This works perfectly. Thank you so much!
"ata" wrote:
> Hi John,
> The problem probably is that the report gets data from cache. You can
> disable this in URL you are using in the Report Rendering with
> "rs:ClearSession=true" option. For example I use
> http://localhost/reportserver?/MyReport&rs:Command=Render&rs:ClearSession=true
> Hope solve your problem.
>
> "John" wrote:
> > I use a javascript to close a window and refresh another window that contains
> > a report. I'm trying to actually refresh the report and have it get new data
> > from a table.
> >
> > For some reason I refresh the browser and the report doesn't refresh until
> > the 2nd time it's done. Anyone know why or how to fix this?
> >|||ClearSession never work for us. Any ideas?
"ata" wrote:
> Hi John,
> The problem probably is that the report gets data from cache. You can
> disable this in URL you are using in the Report Rendering with
> "rs:ClearSession=true" option. For example I use
> http://localhost/reportserver?/MyReport&rs:Command=Render&rs:ClearSession=true
> Hope solve your problem.
>
> "John" wrote:
> > I use a javascript to close a window and refresh another window that contains
> > a report. I'm trying to actually refresh the report and have it get new data
> > from a table.
> >
> > For some reason I refresh the browser and the report doesn't refresh until
> > the 2nd time it's done. Anyone know why or how to fix this?
> >

Refreshing a development database with production data

Hello,

I am trying to refresh a test database with data from a production database. Both database structures are identical, e.g. constraints, stored procs, PK, etc. I am trying to create a package in SSIS that accomplishes this task and I am having extensive problems. The import export wizard is out of the question because the constaints are not carried over, plus when I try to refresh the data using the import export wizard, it fails on 1 specific table because of a column in that table named "Error code". I think "Error code" is a micrsoft keyword, so it fails on this column. Does anyone know a workaround that I can do to accomplish this simple task, that could be completed in minutes using DTS. I understand that SSIS is not as straight forward as DTS, but this task is something that DBA's do on a regular basis and therefore should not be this difficult.

Any help would be appreciated,

David

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1118044&SiteID=1

Refreshing a CSV file using DTS

I have a DTS job that populates a CSV file on a regular basis.
It appears that by default, each time the job runs the contents of the file
are overwritten with the new data from SQL Server.
Can anyone that confirm that this is the case?
And is it possible to have DTS append to rather than overwrite the contents
of the file?
That's correct...the default is to overwrite.
The append option isn't supported with the text file
provider. The workaround is generally to generate a second
file and then use FileSystemObject to create a single file
or a dos copy command to combine the two file into a third
file.
-Sue
On Thu, 24 Feb 2005 07:22:52 -0800, "Dave" <dave@.nospam.ru>
wrote:

>I have a DTS job that populates a CSV file on a regular basis.
>It appears that by default, each time the job runs the contents of the file
>are overwritten with the new data from SQL Server.
>Can anyone that confirm that this is the case?
>And is it possible to have DTS append to rather than overwrite the contents
>of the file?
>
sql

Refreshing a CSV file using DTS

I have a DTS job that populates a CSV file on a regular basis.
It appears that by default, each time the job runs the contents of the file
are overwritten with the new data from SQL Server.
Can anyone that confirm that this is the case?
And is it possible to have DTS append to rather than overwrite the contents
of the file?That's correct...the default is to overwrite.
The append option isn't supported with the text file
provider. The workaround is generally to generate a second
file and then use FileSystemObject to create a single file
or a dos copy command to combine the two file into a third
file.
-Sue
On Thu, 24 Feb 2005 07:22:52 -0800, "Dave" <dave@.nospam.ru>
wrote:

>I have a DTS job that populates a CSV file on a regular basis.
>It appears that by default, each time the job runs the contents of the file
>are overwritten with the new data from SQL Server.
>Can anyone that confirm that this is the case?
>And is it possible to have DTS append to rather than overwrite the contents
>of the file?
>

Refreshing a CSV file using DTS

I have a DTS job that populates a CSV file on a regular basis.
It appears that by default, each time the job runs the contents of the file
are overwritten with the new data from SQL Server.
Can anyone that confirm that this is the case?
And is it possible to have DTS append to rather than overwrite the contents
of the file?That's correct...the default is to overwrite.
The append option isn't supported with the text file
provider. The workaround is generally to generate a second
file and then use FileSystemObject to create a single file
or a dos copy command to combine the two file into a third
file.
-Sue
On Thu, 24 Feb 2005 07:22:52 -0800, "Dave" <dave@.nospam.ru>
wrote:
>I have a DTS job that populates a CSV file on a regular basis.
>It appears that by default, each time the job runs the contents of the file
>are overwritten with the new data from SQL Server.
>Can anyone that confirm that this is the case?
>And is it possible to have DTS append to rather than overwrite the contents
>of the file?
>