Combining transactional and snapshot replication

Hi

I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)

We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)

When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.

If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.

Can I combine the two replication schemes to deliver updates to the same target database:
- transactional replication delivering realtime updates to the tables with PKs during the day and
- snapshot replication updating all the tables once per 24hrs at night?

Or is there a better way of doing this?

I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...

TIACertainly, you can setup two publication - one for those tables with no PK and one for those tables with PK. They will have the same subscriber and destination but the one will be snapshot and one will be transactional. The other option you have is to create a surrogate key for each table without PK. I don't know how feasible it is since your database schema is controlled by the person other than you. You can also consider Log Shipping if the function of the subscriber is read-only.|||Thank you for your quick response. I'll set up two publications as you suggest. For some reason I was hung up on including the PK tables in both publications and I was wondering why I was getting errors... <doh!>

Regarding log shipping, I've had a quick look around for info. It seems that it is not as easy as "standard" replication to set up and keep tabs on. Also, in my environment, although the replicated database will be read only on the target, one of the target SQL boxes will have a database which will capture input from web-connected users. Will this make a difference? Do you think that log shipping will be the better solution for me?

I appreciate your help

combining 2 queries?

HI I have two queries I am trying to combine
the first one simply returns several integers
for the second part I am trying to insert these integers into another table.
Code below does not work together. Do I need an integer array for @.logids
SELECT @.logids field1 FROM dbo.table1
WHERE POC_ID = 15
INSERT INTO table2
(field2)
VALUES
(@.logids)
--
Paul G
Software engineer.You got it backwards, try
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2;
"Paul" wrote:
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Paul,
If I understand your question correctly, it would be just
insert into table2 (field2)
SELECT field1 FROM dbo.table1
WHERE POC_ID = 15
hth
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another
> table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Hi thanks for the response,
tried this below, but nothing is getting inserted,
SET @.pocid = 15
INSERT INTO table2
(field2 )
SELECT field1 FROM table1 WHERE POC_ID = @.pocid
since I have no Nulls allowed for table to I get the error can not insert
NULL.
so it is trying to insert a NULL
"Ash" wrote:
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.|||Hi thanks for the response. I tried this but it tries to insert NULL
get error statement Cannot insert the value NULL into column 'field2', table
'table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
When I try the select statement without the insert it returns 83 values in
the
database output.
"Quentin Ran" wrote:
> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another
> > table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.
>
>|||It is working now thanks for the help.
"Ash" wrote:
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.|||it is working now thanks for the help.
"Quentin Ran" wrote:
> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another
> > table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.
>
>|||You need to make sure that if the field you are inserting into doesnt allow
nulls that the field you are selecting from also doesn't allow null.
Or unique...etc (ex. Primarykey)
INSERT into table2 (field2) SELECT field1 FROM dbo.table1 WHERE POC_ID = 15
If you have for example in table2 'field1' that is a PK then you need to
consider that.
ex INSERT into table2 (field1,field2) SELECT field1,field2 FROM dbo.table1
WHERE POC_ID = 15
But what you did is that you tried poplulating a row without assigning the
PK value.
"Paul" wrote:
> Hi thanks for the response. I tried this but it tries to insert NULL
> get error statement Cannot insert the value NULL into column 'field2', table
> 'table2'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> When I try the select statement without the insert it returns 83 values in
> the
> database output.
> "Quentin Ran" wrote:
> > Paul,
> >
> > If I understand your question correctly, it would be just
> >
> > insert into table2 (field2)
> > SELECT field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > hth
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > > HI I have two queries I am trying to combine
> > > the first one simply returns several integers
> > > for the second part I am trying to insert these integers into another
> > > table.
> > > Code below does not work together. Do I need an integer array for @.logids
> > >
> > > SELECT @.logids field1 FROM dbo.table1
> > > WHERE POC_ID = 15
> > >
> > > INSERT INTO table2
> > > (field2)
> > > VALUES
> > > (@.logids)
> > >
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >sqlsql

combining 2 queries?

HI I have two queries I am trying to combine
the first one simply returns several integers
for the second part I am trying to insert these integers into another table.
Code below does not work together. Do I need an integer array for @.logids
SELECT @.logids field1 FROM dbo.table1
WHERE POC_ID = 15
INSERT INTO table2
(field2)
VALUES
(@.logids)
Paul G
Software engineer.
You got it backwards, try
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2;
"Paul" wrote:

> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.
|||Paul,
If I understand your question correctly, it would be just
insert into table2 (field2)
SELECT field1 FROM dbo.table1
WHERE POC_ID = 15
hth
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another
> table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.
|||Hi thanks for the response,
tried this below, but nothing is getting inserted,
SET @.pocid = 15
INSERT INTO table2
(field2 )
SELECT field1 FROM table1 WHERE POC_ID = @.pocid
since I have no Nulls allowed for table to I get the error can not insert
NULL.
so it is trying to insert a NULL
"Ash" wrote:
[vbcol=seagreen]
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
|||Hi thanks for the response. I tried this but it tries to insert NULL
get error statement Cannot insert the value NULL into column 'field2', table
'table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
When I try the select statement without the insert it returns 83 values in
the
database output.
"Quentin Ran" wrote:

> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
>
>
|||It is working now thanks for the help.
"Ash" wrote:
[vbcol=seagreen]
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
|||it is working now thanks for the help.
"Quentin Ran" wrote:

> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
>
>
|||You need to make sure that if the field you are inserting into doesnt allow
nulls that the field you are selecting from also doesn't allow null.
Or unique...etc (ex. Primarykey)
INSERT into table2 (field2) SELECT field1 FROM dbo.table1 WHERE POC_ID = 15
If you have for example in table2 'field1' that is a PK then you need to
consider that.
ex INSERT into table2 (field1,field2) SELECT field1,field2 FROM dbo.table1
WHERE POC_ID = 15
But what you did is that you tried poplulating a row without assigning the
PK value.
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response. I tried this but it tries to insert NULL
> get error statement Cannot insert the value NULL into column 'field2', table
> 'table2'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> When I try the select statement without the insert it returns 83 values in
> the
> database output.
> "Quentin Ran" wrote:

combining 2 queries?

HI I have two queries I am trying to combine
the first one simply returns several integers
for the second part I am trying to insert these integers into another table.
Code below does not work together. Do I need an integer array for @.logids
SELECT @.logids field1 FROM dbo.table1
WHERE POC_ID = 15
INSERT INTO table2
(field2)
VALUES
(@.logids)
Paul G
Software engineer.You got it backwards, try
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2;
"Paul" wrote:

> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another tabl
e.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Paul,
If I understand your question correctly, it would be just
insert into table2 (field2)
SELECT field1 FROM dbo.table1
WHERE POC_ID = 15
hth
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another
> table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Hi thanks for the response,
tried this below, but nothing is getting inserted,
SET @.pocid = 15
INSERT INTO table2
(field2 )
SELECT field1 FROM table1 WHERE POC_ID = @.pocid
since I have no Nulls allowed for table to I get the error can not insert
NULL.
so it is trying to insert a NULL
"Ash" wrote:
[vbcol=seagreen]
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
>|||Hi thanks for the response. I tried this but it tries to insert NULL
get error statement Cannot insert the value NULL into column 'field2', table
'table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
When I try the select statement without the insert it returns 83 values in
the
database output.
"Quentin Ran" wrote:

> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
>
>|||It is working now thanks for the help.
"Ash" wrote:
[vbcol=seagreen]
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
>|||it is working now thanks for the help.
"Quentin Ran" wrote:

> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
>
>|||You need to make sure that if the field you are inserting into doesnt allow
nulls that the field you are selecting from also doesn't allow null.
Or unique...etc (ex. Primarykey)
INSERT into table2 (field2) SELECT field1 FROM dbo.table1 WHERE POC_ID = 15
If you have for example in table2 'field1' that is a PK then you need to
consider that.
ex INSERT into table2 (field1,field2) SELECT field1,field2 FROM dbo.table1
WHERE POC_ID = 15
But what you did is that you tried poplulating a row without assigning the
PK value.
"Paul" wrote:
[vbcol=seagreen]
> Hi thanks for the response. I tried this but it tries to insert NULL
> get error statement Cannot insert the value NULL into column 'field2', tab
le
> 'table2'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> When I try the select statement without the insert it returns 83 values in
> the
> database output.
> "Quentin Ran" wrote:
>

Colors for Line Chart

Hi,
I have created a line chart with 3 different datafields. I want them to
have the colors yellow for the first, red for the second and blue for the
third. Is there anyway we can manipulate the colors on the chart? If there
is, can you kindly tell me how or point me in the direction of how to do
that?
Thank You,
RickyYou will need to have SP1 of RS 2000 installed on the server and designer
machines. In particular, read the following section of the SP1 readme file
about using colors in charts:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ricky" <kmeas1@.gmail.com> wrote in message
news:Oj5gfVHPFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have created a line chart with 3 different datafields. I want them to
> have the colors yellow for the first, red for the second and blue for the
> third. Is there anyway we can manipulate the colors on the chart? If
> there is, can you kindly tell me how or point me in the direction of how
> to do that?
> Thank You,
> Ricky
>

Collocating

Hello : )
This Thursday 21st, I have a second interview for a DBA position. I have
been looking for an opportunity like this for awhile now and I hope I don't
blow it.
My question is regarding replication/synching for SQL Servers at different
locations. One of the first tasks (if I am hired) will be to find a location
to install and set up redundant SQL Servers. The reason they want to do this
is because they recently had an extended power loss and their servers where
down for several hours.
I am sure I will be asked during the interview how I will go about doing
this. Does anyone have any advice on how to answer?
I have been working with single Production servers at several companies over
the past 7 years, but
I have no expirence with collocation, and only "playing around" expierence
with replication. I am not afraid of this task and am looking forward to a
challange like this.
I want to be sure that I answer the question intellegently.
Thanks in advance for your support!
Aaron
Key points:
Replication is not always a good way to implement disaster recovery...it
makes changed to your database and servers (as well as adding a whole new
layer of DBA effort)
Log shipping is an option (easy if you have Enterprise Edition)
3rd party tools such as Double-take from http://www.nsisoftware.com/ may
also be an option.
It really depends on their tolerance for downtime, willingness to pay for a
solution and size of the database (its much easier to set up DR for 20mb
than 20TB)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Next" <aeverett99@.newsgroup.nospam> wrote in message
news:904B5E63-ACB4-4CE1-9986-36C9F9698ACC@.microsoft.com...
> Hello : )
> This Thursday 21st, I have a second interview for a DBA position. I have
> been looking for an opportunity like this for awhile now and I hope I
> don't
> blow it.
> My question is regarding replication/synching for SQL Servers at different
> locations. One of the first tasks (if I am hired) will be to find a
> location
> to install and set up redundant SQL Servers. The reason they want to do
> this
> is because they recently had an extended power loss and their servers
> where
> down for several hours.
> I am sure I will be asked during the interview how I will go about doing
> this. Does anyone have any advice on how to answer?
> I have been working with single Production servers at several companies
> over
> the past 7 years, but
> I have no expirence with collocation, and only "playing around" expierence
> with replication. I am not afraid of this task and am looking forward to a
> challange like this.
> I want to be sure that I answer the question intellegently.
> Thanks in advance for your support!
> Aaron
>