Backing up SSRS objects

I'm using sqlserver 2005 developer edition.
Unlike databases, there appears to be no way, in Management Studio, to back
up ReportSever.
How is it done?
--
Regards,
Gary BlakelyHi Gary,
Thank you for your posting!
You could backup the ReportServer database as you backup other databases.
In the databases, right-click ReportServer and then click Back up. Then you
could backup the Reporty Server Database.
Here is an article for your reference:
Back Up Database (General Page)
http://msdn2.microsoft.com/en-us/library/ms183383(d=ide).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Background Color for SQL Server Management Console

I am new to SQL server 2005 and I was wondering if there was a way to visually tell what server you are connected to other than the tab loacted at the top of the page. Something like changing the background color of the editor to red if you are connected to a production server so hopefully the user will not make unapproved changes in production. Please let me know if there is some feature like this out there. Thanks.There is no such feature.|||

Funnily enough, I was thinking along exactly the same lines as Jason. If no feature exists is it possible to do it programmatically, as an add-in? eg create an event handler that would set the background colour whenever a connection is made or changed, depending on the server or database connected to?

This strikes me as more the sort of thing for an add-in than a core feature.|||

Please post your feature requests at:

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

If enough folks like the idea, it may be incorporated in a feature set.

sql

Background Color for SQL Server Management Console

I am new to SQL server 2005 and I was wondering if there was a way to visually tell what server you are connected to other than the tab loacted at the top of the page. Something like changing the background color of the editor to red if you are connected to a production server so hopefully the user will not make unapproved changes in production. Please let me know if there is some feature like this out there. Thanks.There is no such feature.|||

Funnily enough, I was thinking along exactly the same lines as Jason. If no feature exists is it possible to do it programmatically, as an add-in? eg create an event handler that would set the background colour whenever a connection is made or changed, depending on the server or database connected to?

This strikes me as more the sort of thing for an add-in than a core feature.|||

Please post your feature requests at:

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

If enough folks like the idea, it may be incorporated in a feature set.

What is Query Analyzer's replacement in SQL Server 2005?

Both Query Analyzer and Enterprise Manager are gone from SQL Server 2005. In their place is a single tool, SQL Server Management Studio. This tool has most of the features of its predecessors, but with an upgraded UI and a lot of improved functionality. I think that most DBAs will find it to be a good upgrade. If you'd like more information on some of the new features with this tool, read my article on SQL Server Management Studio client tool enhancements.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1146801,00.htmlPAE allows the OS to use more than 4GB of memory. AWE allows the
application to use more than 4GB.
Andrew J. Kelly SQL MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
> guys,
> what is the difference between AWE and PAE?
|||for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
"Andrew J. Kelly" wrote:

> PAE allows the OS to use more than 4GB of memory. AWE allows the
> application to use more than 4GB.
> --
> Andrew J. Kelly SQL MVP
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:7D318157-43B8-49AD-9DF7-F38F6ABEEA03@.microsoft.com...
>
>
|||AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
want SQL Server to utilize > 4GB memory, you need both settings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...[vbcol=seagreen]
> for SQL server, shd i enable PAE or AWE? Can both be enabled at the same time?
> "Andrew J. Kelly" wrote:
|||in that case...in a server with 5G of ram
should i put the /AWE /PAE swith in the same line in the boot.ini?
For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
how do i check it has 5G? Also, is there any significant on it? The
performance shd be better i suppose
Thank you
"Tibor Karaszi" wrote:

> AWE is a SQL Server setting (sp_configure) and PAE is an operating system setting (BOOT.INI). If you
> want SQL Server to utilize > 4GB memory, you need both settings.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:82A723DF-8EEA-431B-8669-79146E1DCA1D@.microsoft.com...
>
>
|||boot.ini should have something like this:
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
Enterprise" /fastdetect /pae /3gb
(The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
provide a little more to the apps, i.e. SQL Server.)
To check the amount of physical RAM the OS is seeing you can just check
the Performance tab in task manager.
To turn on AWE memory for SQL Server you use the sp_configure stored
proc (in Query Analyzer for example):
exec sp_configure 'awe enabled', 1
reconfigure
go
Then you have to restart the SQL instance as the AWE setting only takes
affect on server startup. Also, when SQL Server is using AWE memory, it
cannot use dynamic memory management. It *will not swap pages out of
memory *if another app requests memory and the OS doesn't have enough to
satisfy the request (unlike the SQL dynamic memory manager). So you
should specify a "max server memory" amount with sp_configure. For
example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
remaining 1GB to the OS & other apps, you would do this in QA:
exec sp_configure "max server memory", 5120
reconfigure
go
All this is documented in SQL BOL:
http://msdn.microsoft.com/library/de...onfig_3stg.asp
To see how much memory SQL Server is currently consuming you can open
the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
AWE & memory management.
HTH
*mike hodgson*
/ mallesons stephen jaques/
blog: http://sqlnerd.blogspot.com
rupart wrote:
[vbcol=seagreen]
>in that case...in a server with 5G of ram
>should i put the /AWE /PAE swith in the same line in the boot.ini?
>For the OS, i can see under system mgmt that 5G is enabled. How abt for sql?
>how do i check it has 5G? Also, is there any significant on it? The
>performance shd be better i suppose
>Thank you
>"Tibor Karaszi" wrote:
>
|||does /3g means the system will allocate 3g for system and the rest for
sql(that is after enabling thru AWE, rite?)?
yeah, good link...thank you
"Mike Hodgson" wrote:

> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just check
> the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only takes
> affect on server startup. Also, when SQL Server is using AWE memory, it
> cannot use dynamic memory management. It *will not swap pages out of
> memory *if another app requests memory and the OS doesn't have enough to
> satisfy the request (unlike the SQL dynamic memory manager). So you
> should specify a "max server memory" amount with sp_configure. For
> example, on your 5GB box, if you wanted to allocate 4GB to SQL and the
> remaining 1GB to the OS & other apps, you would do this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/de...onfig_3stg.asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff on
> AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:
>
|||The other way around. 3 GB for the application and 1 GB for the system.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:4944B057-1DC7-4638-BB88-9040FDCC9B9F@.microsoft.com...[vbcol=seagreen]
> does /3g means the system will allocate 3g for system and the rest for
> sql(that is after enabling thru AWE, rite?)?
> yeah, good link...thank you
> "Mike Hodgson" wrote:
|||Oops - slight typo in my "max server memory" statement. To set a max
server memory of 4GB you would run:
exec sp_configure "max server memory", 4096
reconfigure
go
The 5120 figure I included in my previous post would try to set it at
5GB (not 4GB).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> boot.ini should have something like this:
> multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windo ws Server 2003,
> Enterprise" /fastdetect /pae /3gb
> (The /3gb switch is not necessary, but for a box with 5gb of RAM it'll
> provide a little more to the apps, i.e. SQL Server.)
> To check the amount of physical RAM the OS is seeing you can just
> check the Performance tab in task manager.
> To turn on AWE memory for SQL Server you use the sp_configure stored
> proc (in Query Analyzer for example):
> exec sp_configure 'awe enabled', 1
> reconfigure
> go
> Then you have to restart the SQL instance as the AWE setting only
> takes affect on server startup. Also, when SQL Server is using AWE
> memory, it cannot use dynamic memory management. It *will not swap
> pages out of memory *if another app requests memory and the OS doesn't
> have enough to satisfy the request (unlike the SQL dynamic memory
> manager). So you should specify a "max server memory" amount with
> sp_configure. For example, on your 5GB box, if you wanted to allocate
> 4GB to SQL and the remaining 1GB to the OS & other apps, you would do
> this in QA:
> exec sp_configure "max server memory", 5120
> reconfigure
> go
> All this is documented in SQL BOL:
> http://msdn.microsoft.com/library/de...nfig_3stg..asp
> To see how much memory SQL Server is currently consuming you can open
> the System Monitor (perfmon.exe) and add the counter: SQLServer:Memory
> Manager | Total Server Memory (KB). SQL BOL has a lot of good stuff
> on AWE & memory management.
> HTH
> --
> *mike hodgson*
> / mallesons stephen jaques/
> blog: http://sqlnerd.blogspot.com
>
> rupart wrote:

avoid using BCP for snapshot

sql2k sp3
Howdy kids. So, now that management has made up they're
minds(again, pulling my hair out now) Im back to part of
where I was yesterday. At least since then though Ive
learned a bit more about Replication and what will be
required to make it work in my environment.
Well you've all heard this first part a few times now. The
Subscriber has more columns than the Publisher. Im now
back to needing to do a snapshot to populate the
Subscriber. I can write Subscriber Insert and Update procs
to handle individual data modifications AFTER the initial
snapshot is done. But what about that initial snapshot? It
gets BCP'd into the Subscriber table which wont work here
beacuase of the different schemas. I know how to use
format files for normal BCP but it wont work for this
scenario. So, how to I do that initial snapshot without
using BCP? Im guessing DTS but am hoping to be able to use
the procs I have to create anyways to accomodate for the
different schema unless DTS is a whole lot faster?
TIA, ChrisR
post the schemas for the table on the publisher and subscriber here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:7f6c01c4848a$4c95b750$a601280a@.phx.gbl...
> sql2k sp3
> Howdy kids. So, now that management has made up they're
> minds(again, pulling my hair out now) Im back to part of
> where I was yesterday. At least since then though Ive
> learned a bit more about Replication and what will be
> required to make it work in my environment.
> Well you've all heard this first part a few times now. The
> Subscriber has more columns than the Publisher. Im now
> back to needing to do a snapshot to populate the
> Subscriber. I can write Subscriber Insert and Update procs
> to handle individual data modifications AFTER the initial
> snapshot is done. But what about that initial snapshot? It
> gets BCP'd into the Subscriber table which wont work here
> beacuase of the different schemas. I know how to use
> format files for normal BCP but it wont work for this
> scenario. So, how to I do that initial snapshot without
> using BCP? Im guessing DTS but am hoping to be able to use
> the procs I have to create anyways to accomodate for the
> different schema unless DTS is a whole lot faster?
> TIA, ChrisR
|||Chris,
the fastest way is BCP out the data, transfer it over to teh subscriber,
create the table there and then use BULK INSERT on the subscriber.
Alternatively, to make things easier, I would create the table on the
subscriber and use the view mentioned in your earlier post with a linked
server to insert the records. DTS would do the same thing if you used an
ExecuteSQL task. However if you are concerned about errors on the insert,
then I would use DTS - the transform data task with error logging.
HTH,
Paul Ibison
|||the fastest way is to dts it over. with bcp you have to bcp into the file
system and then bcp from the file system to the server.
for a single table with DTS is is blasted over as a rowset. For multiple
tables it is bcp'd into the file system and then bcp'd from the file system
to the server.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uZ9DqxIhEHA.1568@.TK2MSFTNGP09.phx.gbl...
> Chris,
> the fastest way is BCP out the data, transfer it over to teh subscriber,
> create the table there and then use BULK INSERT on the subscriber.
> Alternatively, to make things easier, I would create the table on the
> subscriber and use the view mentioned in your earlier post with a linked
> server to insert the records. DTS would do the same thing if you used an
> ExecuteSQL task. However if you are concerned about errors on the insert,
> then I would use DTS - the transform data task with error logging.
> HTH,
> Paul Ibison
>
|||Also included Insert proc for Subscriber. Is there a way I
can just use that IF its as quick as DTS?
--Publisher
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransDate] [smalldatetime] NOT NULL ,
[TransAmt] [money] NOT NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Subscriber
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Insert proc on Subscriber
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure sp_msIns_TransDtl
@.TransDtlKey int ,
@.CustomerKey int ,
@.SerialNbr char (10) ,
@.TranCode char (4) ,
@.TransDate smalldatetime ,
@.TransAmt money ,
@.RefNbr char (23) ,
@.MerchName varchar (25) ,
@.City varchar (15) ,
@.State varchar (3) ,
@.RejectReason varchar (15) ,
@.PostDate datetime ,
@.CreateDate datetime ,
@.MerchSIC char (4)
as
declare
@.TransDateShort char (10) ,
@.TransDateMonth tinyint ,
@.TransDateYear smallint ,
@.PostDateShort char (10) ,
@.PostDateMonth tinyint ,
@.PostDateYear smallint
select @.TransDateShort = Convert(varchar(10), @.TransDate,
101)-- from TransDTL
select @.TransDateMonth = Month(@.TransDate)-- from TransDTL
select @.TransDateYear = Year(@.TransDate)-- from TransDTL
select @.PostDateShort = Convert(varchar(10), @.PostDate,
101)-- from TransDTL
select @.PostDateMonth = Month(@.PostDate)-- from TransDTL
select @.PostDateYear = Year(@.PostDate)-- from TransDTL
insert into TransDTL
(
TransDtlKey ,
CustomerKey ,
SerialNbr ,
TranCode ,
TransDate ,
TransDateShort ,
TransDateMonth ,
TransDateYear ,
TransAmt ,
RefNbr ,
MerchName ,
City ,
State ,
RejectReason ,
PostDate ,
PostDateShort ,
PostDateMonth ,
PostDateYear ,
CreateDate ,
MerchSIC
)
values
(
@.TransDtlKey ,
@.CustomerKey ,
@.SerialNbr ,
@.TranCode ,
@.TransDate ,
@.TransDateShort ,
@.TransDateMonth ,
@.TransDateYear ,
@.TransAmt ,
@.RefNbr ,
@.MerchName ,
@.City ,
@.State ,
@.RejectReason ,
@.PostDate ,
@.PostDateShort ,
@.PostDateMonth ,
@.PostDateYear ,
@.CreateDate ,
@.MerchSIC
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

>--Original Message--
>post the schemas for the table on the publisher and
subscriber here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7f6c01c4848a$4c95b750$a601280a@.phx.gbl...
The[vbcol=seagreen]
procs[vbcol=seagreen]
initial[vbcol=seagreen]
It[vbcol=seagreen]
here[vbcol=seagreen]
use
>
>.
>
|||Thanks Paul. I dont think Bulk Insert would work because
of the schema differences. Even with format files. Look @.
the insert proc in my reply to Hilary and it will make
more sense.
Thanks again.

>--Original Message--
>Chris,
>the fastest way is BCP out the data, transfer it over to
teh subscriber,
>create the table there and then use BULK INSERT on the
subscriber.
>Alternatively, to make things easier, I would create the
table on the
>subscriber and use the view mentioned in your earlier
post with a linked
>server to insert the records. DTS would do the same thing
if you used an
>ExecuteSQL task. However if you are concerned about
errors on the insert,
>then I would use DTS - the transform data task with error
logging.
>HTH,
>Paul Ibison
>
>.
>
|||Fair point - I'd done things like this because we also saved audit copies of
tables. The bcp files were zipped up before transferring, but I assume that
DTS is still faster in this case.
Finally, Insert Into... Select... using a linked server directly is
presumably faster?
Regards,
Paul Ibison
|||I am not sure about this. I'd have to test it. You can set up a DTS package
so that it will do a non logged insert which should be faster than the
insert select which is logged.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u3mklsJhEHA.1184@.TK2MSFTNGP12.phx.gbl...
> Fair point - I'd done things like this because we also saved audit copies
of
> tables. The bcp files were zipped up before transferring, but I assume
that
> DTS is still faster in this case.
> Finally, Insert Into... Select... using a linked server directly is
> presumably faster?
> Regards,
> Paul Ibison
>
|||you may be running into a bug. it looks like you have to use character mode
to handle the money data type correctly.
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
drop view CustomSyncObject
go
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
--inserting data into publisher
go
select * from transdtl
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--startup snapshot agent
select * from transdtl
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
select * from transdtl where transdtlkey=1
GO
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:7a2b01c4848f$8c8961e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Also included Insert proc for Subscriber. Is there a way I
> can just use that IF its as quick as DTS?
> --Publisher
> CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TranCode] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TransDate] [smalldatetime] NOT NULL ,
> [TransAmt] [money] NOT NULL ,
> [RefNbr] [char] (23) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MerchName] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [City] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [MerchSIC] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> --Subscriber
> CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> --Insert proc on Subscriber
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER procedure sp_msIns_TransDtl
> @.TransDtlKey int ,
> @.CustomerKey int ,
> @.SerialNbr char (10) ,
> @.TranCode char (4) ,
> @.TransDate smalldatetime ,
> @.TransAmt money ,
> @.RefNbr char (23) ,
> @.MerchName varchar (25) ,
> @.City varchar (15) ,
> @.State varchar (3) ,
> @.RejectReason varchar (15) ,
> @.PostDate datetime ,
> @.CreateDate datetime ,
> @.MerchSIC char (4)
> as
> declare
> @.TransDateShort char (10) ,
> @.TransDateMonth tinyint ,
> @.TransDateYear smallint ,
> @.PostDateShort char (10) ,
> @.PostDateMonth tinyint ,
> @.PostDateYear smallint
>
> select @.TransDateShort = Convert(varchar(10), @.TransDate,
> 101)-- from TransDTL
> select @.TransDateMonth = Month(@.TransDate)-- from TransDTL
> select @.TransDateYear = Year(@.TransDate)-- from TransDTL
> select @.PostDateShort = Convert(varchar(10), @.PostDate,
> 101)-- from TransDTL
> select @.PostDateMonth = Month(@.PostDate)-- from TransDTL
> select @.PostDateYear = Year(@.PostDate)-- from TransDTL
> insert into TransDTL
> (
> TransDtlKey ,
> CustomerKey ,
> SerialNbr ,
> TranCode ,
> TransDate ,
> TransDateShort ,
> TransDateMonth ,
> TransDateYear ,
> TransAmt ,
> RefNbr ,
> MerchName ,
> City ,
> State ,
> RejectReason ,
> PostDate ,
> PostDateShort ,
> PostDateMonth ,
> PostDateYear ,
> CreateDate ,
> MerchSIC
> )
> values
> (
> @.TransDtlKey ,
> @.CustomerKey ,
> @.SerialNbr ,
> @.TranCode ,
> @.TransDate ,
> @.TransDateShort ,
> @.TransDateMonth ,
> @.TransDateYear ,
> @.TransAmt ,
> @.RefNbr ,
> @.MerchName ,
> @.City ,
> @.State ,
> @.RejectReason ,
> @.PostDate ,
> @.PostDateShort ,
> @.PostDateMonth ,
> @.PostDateYear ,
> @.CreateDate ,
> @.MerchSIC
> )
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
>
>
>
> subscriber here.
> message
> The
> procs
> initial
> It
> here
> use

Auto-refresh the output of the 'Open Table" action

I checked the options in SQL 2005 Management Studio and it does not appear to be an
available functionality.
Is it possible to have the output of the "Open table" action to refresh the output at a
specified interval?
Thanks.
Probably not as it was never designed for that. There are few reasons to
use that functionality in the first place. The query editior is far more
flexible and does not run the risk of editing data by mistake just from
viewing the data.
Andrew J. Kelly SQL MVP
"Gaetan" <me@.somewhere.com> wrote in message
news:ge2h125kt8381rls43qv4ie3181j1c981r@.4ax.com...
>I checked the options in SQL 2005 Management Studio and it does not appear
>to be an
> available functionality.
> Is it possible to have the output of the "Open table" action to refresh
> the output at a
> specified interval?
> Thanks.
|||I understand the limitations of "Open table". The Query Editor is far more flexible but
unless I missed something, it too does not allow the SQL statements to be auto-rerun at
configurable interval.
On Wed, 15 Mar 2006 19:52:00 -0500, "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote:

>Probably not as it was never designed for that. There are few reasons to
>use that functionality in the first place. The query editior is far more
>flexible and does not run the risk of editing data by mistake just from
>viewing the data.
|||Not as a built in feature but a simple while loop will do it for you. This
will rerun the query every to seconds for 10 loops. Or what ever you need
it to do.
DECLARE @.X INT
SET @.X = 0
WHILE @.x < 10
BEGIN
SELECT *****
WAITFOR DELAY '00:00:10'
END
Andrew J. Kelly SQL MVP
"Gaetan" <me@.somewhere.com> wrote in message
news:6kej12dd7b63n4cq7tr61j26iejd9g7pv9@.4ax.com... [vbcol=seagreen]
>I understand the limitations of "Open table". The Query Editor is far more
>flexible but
> unless I missed something, it too does not allow the SQL statements to be
> auto-rerun at
> configurable interval.
>
> On Wed, 15 Mar 2006 19:52:00 -0500, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote: