okay, Im frustrated....how do I

Greetings, all!

I've searched the MS SQL Server Books Online and this group.

I've been doing Oracle on UNIX for 12+ years, and now I have some SQL
Server databases.

I've discovered the INFORMATION_SCHEMA tables (just like the good ol'
dba_* views in
Oracle)

I've figured out how to use SQL2005 Management Studio.

What'd I'd like to know is - can SQL Server be used to write SQL like
Oracle can; e.g:

spool csyn.sql
set heading off
set pagesize 6000

select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
SYSADM.'||TABLE_NAME||';'
from dba_tables where owner='SYSADM';

spool off

In SQL*Plus, the above will create a script to create public synonyms
for tables in a PeopleSoft database (tables in PeopleSoft multiply like
Tribbles in a storage bin of quadrotriticale...).

Yah, GUI's are fine...until you have thousands and thousands of tables
to deal with!

Thanks ever so mucha_dba_used_to_oracle wrote:

Quote:

Originally Posted by

Greetings, all!
>
>
I've searched the MS SQL Server Books Online and this group.
>
I've been doing Oracle on UNIX for 12+ years, and now I have some SQL
Server databases.
>
I've discovered the INFORMATION_SCHEMA tables (just like the good ol'
dba_* views in
Oracle)
>
I've figured out how to use SQL2005 Management Studio.
>
What'd I'd like to know is - can SQL Server be used to write SQL like
Oracle can; e.g:
>
spool csyn.sql
set heading off
set pagesize 6000
>
select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
SYSADM.'||TABLE_NAME||';'
from dba_tables where owner='SYSADM';
>
spool off
>
In SQL*Plus, the above will create a script to create public synonyms
for tables in a PeopleSoft database (tables in PeopleSoft multiply like
Tribbles in a storage bin of quadrotriticale...).
>
Yah, GUI's are fine...until you have thousands and thousands of tables
to deal with!
>
Thanks ever so much

Try:

SELECT 'CREATE SYNONYM dbo.'
+QUOTENAME(table_name)+' FOR '
+QUOTENAME(table_schema)+'.'
+QUOTENAME(table_name)
FROM information_schema.tables
WHERE table_schema = 'SYSADM' ;

This example assumes you have a schema called SYSADM and that you want
the synonym to belong to the dbo schema. You can do without the
QUOTENAME functions if your table names are such that they don't need
quoting (essentially that means no spaces, punctuation or reserved
words.

Run the script in Text mode (CTRL+T or click the "Results to Text"
button on the toolbar). Then either cut-and-paste the result into the
query window or save it to a file (Right-click, Save As).

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

Offline Resources

I have taken SQL Server resources OFFLINE. Will the SQL Server resources be
brought ONLINE automatically during failover or will it be in the OFFLINE
state until brought ONLINE manually?
When you manually bring a group offline, it will NOT come back online during
a failover or a restart. You have to bring it online manually.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Samuel" <Samuel@.discussions.microsoft.com> wrote in message
news:0154DAAB-E305-4101-96DE-FD0280D092A1@.microsoft.com...
>I have taken SQL Server resources OFFLINE. Will the SQL Server resources be
> brought ONLINE automatically during failover or will it be in the OFFLINE
> state until brought ONLINE manually?

Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:
> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece of
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without the
> need for OLEDB drivers.
>
>|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:
>> I don't know where to begin with this. We designed an online product
>> catalogue who now want to put it on a CD. Is there any third party piece
>> of
>> software that could convert an sql server or access db into standalone
>> program that the client could put on a CD without he need for Access
>> Forms,
>> OLEDB etc.
>> If I could put the db in XML could I point the Access runtime, which the
>> developer version of Office can export, at that as a datasource without
>> the
>> need for OLEDB drivers.
>>

Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.
Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:

> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece of
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without the
> need for OLEDB drivers.
>
>
|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...[vbcol=seagreen]
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:

Offline database

I don't know where to begin with this. We designed an online product
catalogue who now want to put it on a CD. Is there any third party piece of
software that could convert an sql server or access db into standalone
program that the client could put on a CD without he need for Access Forms,
OLEDB etc.
If I could put the db in XML could I point the Access runtime, which the
developer version of Office can export, at that as a datasource without the
need for OLEDB drivers.Chris
You can use data transformation services (DTS) to write out the data in lots
of different formats, one of which should be suitable for your purposes.
Maybe write it out so it can be read into an excel spread sheet?
Have a look into what you can do with it, I'm sure it will be the answer.
Regards
John
"Chris Kennedy" wrote:

> I don't know where to begin with this. We designed an online product
> catalogue who now want to put it on a CD. Is there any third party piece o
f
> software that could convert an sql server or access db into standalone
> program that the client could put on a CD without he need for Access Forms
,
> OLEDB etc.
> If I could put the db in XML could I point the Access runtime, which the
> developer version of Office can export, at that as a datasource without th
e
> need for OLEDB drivers.
>
>|||Doing it like that wouldn't be a problem but there is the possiblity the
clients won't have office. I need some thing like an exe that will run on
any Windows machine. This is a bit OT but....
I was thinking if I could point the exported access runtime at an xml file
that I could stick it on a disc and not worry about what software they have
installed but does the Access runtime allow for querying?
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9040D3F6-81A7-4DF6-A794-B1644086413D@.microsoft.com...[vbcol=seagreen]
> Chris
> You can use data transformation services (DTS) to write out the data in
> lots
> of different formats, one of which should be suitable for your purposes.
> Maybe write it out so it can be read into an excel spread sheet?
> Have a look into what you can do with it, I'm sure it will be the answer.
> Regards
> John
> "Chris Kennedy" wrote:
>

Offline Client

Hi
I need to develop an app in vb 2005 which can work in both offline and
online modes and will be running on client laptops. I have listened to the
following web cast;
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
What I need to know is, do the laptops need to have a local version of sql
server (mobile, express etc.) to accomplish the offline mode?
Thanks
RegardsJohn wrote:
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened
> to the following web cast;
> http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
> What I need to know is, do the laptops need to have a local version
> of sql server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
You can use Microsoft SQL Server 2005 Express for the clients. That
version of SQL Server is free.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John,
It depends on what you want to be able to do in offline mode.
1. Are you planning on using SQL-Server merge replication to subscribe for
the data so that its easy to offline and then merge any changes later?
Note: this may involve a large investment in time to get it right.
2. Are you planning on keeping some data in a local database and still
support editing? MSDE or the upcoming SQL-Server Express might be the
solution here. Note: there are some catches to using MSDE
3. Are you planning on keeping some data in local storage, but only require
read-only access. Depending on the amount of data, MSDE or SQL-Server
Express might be overkill. For small amounts of data you might consider
storing the data in Access or even persisting the a DataSet to XML and using
something like the QueryADataSet assembly I've been developing for providing
query capability while offline.
Depsite Microsoft's best intentions, MSDE and/or SQL-Server Express edition
aren't always the best solution to a problem.
Hope this helps
Adrian Moore
http://www.queryadataset.com
"John" <John@.nospam.infovis.co.uk> wrote in message
news:uYezXwqbFHA.2736@.TK2MSFTNGP12.phx.gbl...
> Hi
> I need to develop an app in vb 2005 which can work in both offline and
> online modes and will be running on client laptops. I have listened to the
> following web cast;
> http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032271522&EventCategory=3&culture=en-US&CountryCode=US
> What I need to know is, do the laptops need to have a local version of sql
> server (mobile, express etc.) to accomplish the offline mode?
> Thanks
> Regards
>sql

Office Move

My company located in Delware,is moving to a new office and I need to
keep our production database online by moving it onto servers in our
Las Vegas office. I'm looking for suggested ways to move the
databases
as seamlessly as possible to our Vegas office.
We conduct business Monday through Saturday. The way I understand the
move, after close of business on Saturday we want the data center to
be moved to Vegas and available on Monday morning, which doesn't
provide much time to get the db up and running.
We currently have our production database on an active/passive
cluster
running SQL 2005 with transactional replication in place to support
reporting for our enterprise application.
What options are available besides copying a backup across my WAN and
restoring the database?
What limitations are imposed by Log Shipping given the physical
distance betwen locations, etc.
If anyone can help point me in the proper direction I would greatly
appreciate the help.
NC3
Consider database mirroring. Once you have done the initial synch, then
just failover to the new site and dismantle the old one.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:cc76a479-5b61-41ba-8f5c-95e98883d924@.z17g2000hsg.googlegroups.com...
My company located in Delware,is moving to a new office and I need to
keep our production database online by moving it onto servers in our
Las Vegas office. I'm looking for suggested ways to move the
databases
as seamlessly as possible to our Vegas office.
We conduct business Monday through Saturday. The way I understand the
move, after close of business on Saturday we want the data center to
be moved to Vegas and available on Monday morning, which doesn't
provide much time to get the db up and running.
We currently have our production database on an active/passive
cluster
running SQL 2005 with transactional replication in place to support
reporting for our enterprise application.
What options are available besides copying a backup across my WAN and
restoring the database?
What limitations are imposed by Log Shipping given the physical
distance betwen locations, etc.
If anyone can help point me in the proper direction I would greatly
appreciate the help.
NC3
|||Hi
You don't say if the hardware is moving or not or if when it moves any
configuration changes will be made? If the kit is moving do you have
alternate kit as a backup in case of problems?
If you are moving to new/alternate kit then you can test the procedures
before hand so you will know if they are feasable or take too long. You
should also look at how you can minimise the actual work needed when you do
make the move, such as having everything in place and tested on a copy of the
database so there is only a more up-to-date version of the database to slot
in.
John
"NC3" wrote:

> My company located in Delware,is moving to a new office and I need to
> keep our production database online by moving it onto servers in our
> Las Vegas office. I'm looking for suggested ways to move the
> databases
> as seamlessly as possible to our Vegas office.
> We conduct business Monday through Saturday. The way I understand the
> move, after close of business on Saturday we want the data center to
> be moved to Vegas and available on Monday morning, which doesn't
> provide much time to get the db up and running.
>
> We currently have our production database on an active/passive
> cluster
> running SQL 2005 with transactional replication in place to support
> reporting for our enterprise application.
>
> What options are available besides copying a backup across my WAN and
> restoring the database?
>
> What limitations are imposed by Log Shipping given the physical
> distance betwen locations, etc.
>
> If anyone can help point me in the proper direction I would greatly
> appreciate the help.
>
> NC3
>
|||On Feb 20, 3:47Xpm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Consider database mirroring. XOnce you have done the initial synch, then
> just failover to the new site and dismantle the old one.
> --
> X XTom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON X Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:cc76a479-5b61-41ba-8f5c-95e98883d924@.z17g2000hsg.googlegroups.com...
> My company located in Delware,is moving to a new office and I need to
> keep our production database online by moving it onto servers in our
> Las Vegas office. I'm looking for suggested ways to move the
> databases
> as seamlessly as possible to our Vegas office.
> We conduct business Monday through Saturday. The way I understand the
> move, after close of business on Saturday we want the data center to
> be moved to Vegas and available on Monday morning, which doesn't
> provide much time to get the db up and running.
> We currently have our production database on an active/passive
> cluster
> running SQL 2005 with transactional replication in place to support
> reporting for our enterprise application.
> What options are available besides copying a backup across my WAN and
> restoring the database?
> What limitations are imposed by Log Shipping given the physical
> distance betwen locations, etc.
> If anyone can help point me in the proper direction I would greatly
> appreciate the help.
> NC3
How would Mirroring effect transactional replication and can I mirror
a SQL Cluster
|||Not sure about replication but you can mirror between clusters:
http://www.microsoft.com/technet/technetmag/issues/2007/03/sqlclusters/default.aspx?loc=en
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:833e7401-6825-4586-ae4a-b0a25de9b52f@.p25g2000hsf.googlegroups.com...
On Feb 20, 3:47 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Consider database mirroring. Once you have done the initial synch, then
> just failover to the new site and dismantle the old one.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:cc76a479-5b61-41ba-8f5c-95e98883d924@.z17g2000hsg.googlegroups.com...
> My company located in Delware,is moving to a new office and I need to
> keep our production database online by moving it onto servers in our
> Las Vegas office. I'm looking for suggested ways to move the
> databases
> as seamlessly as possible to our Vegas office.
> We conduct business Monday through Saturday. The way I understand the
> move, after close of business on Saturday we want the data center to
> be moved to Vegas and available on Monday morning, which doesn't
> provide much time to get the db up and running.
> We currently have our production database on an active/passive
> cluster
> running SQL 2005 with transactional replication in place to support
> reporting for our enterprise application.
> What options are available besides copying a backup across my WAN and
> restoring the database?
> What limitations are imposed by Log Shipping given the physical
> distance betwen locations, etc.
> If anyone can help point me in the proper direction I would greatly
> appreciate the help.
> NC3
How would Mirroring effect transactional replication and can I mirror
a SQL Cluster
|||On Feb 21, 3:46Xpm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Not sure about replication but you can mirror between clusters:
> http://www.microsoft.com/technet/technetmag/issues/2007/03/sqlcluster...
> --
> X XTom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON X Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:833e7401-6825-4586-ae4a-b0a25de9b52f@.p25g2000hsf.googlegroups.com...
> On Feb 20, 3:47 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
> How would Mirroring effect transactional replication and can I mirror
> a SQL Cluster- Hide quoted text -
> - Show quoted text -
What do you thing of having 2 subscribers for the same publisher. Do
think that's something that might work?
|||Not sure what you mean. You can have as many subscribers to a publication
that you want.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:991ea951-2d8c-44bd-93e2-10871e6d4bce@.i7g2000prf.googlegroups.com...
On Feb 21, 3:46 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Not sure about replication but you can mirror between clusters:
> http://www.microsoft.com/technet/technetmag/issues/2007/03/sqlcluster...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:833e7401-6825-4586-ae4a-b0a25de9b52f@.p25g2000hsf.googlegroups.com...
> On Feb 20, 3:47 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
>
>
>
>
>
>
> How would Mirroring effect transactional replication and can I mirror
> a SQL Cluster- Hide quoted text -
> - Show quoted text -
What do you thing of having 2 subscribers for the same publisher. Do
think that's something that might work?

Office Move

My company located in Delware,is moving to a new office and I need to
keep our production database online by moving it onto servers in our
Las Vegas office. I'm looking for suggested ways to move the
databases
as seamlessly as possible to our Vegas office.
We conduct business Monday through Saturday. The way I understand the
move, after close of business on Saturday we want the data center to
be moved to Vegas and available on Monday morning, which doesn't
provide much time to get the db up and running.
We currently have our production database on an active/passive
cluster
running SQL 2005 with transactional replication in place to support
reporting for our enterprise application.
What options are available besides copying a backup across my WAN and
restoring the database?
What limitations are imposed by Log Shipping given the physical
distance betwen locations, etc.
If anyone can help point me in the proper direction I would greatly
appreciate the help.
NC3Consider database mirroring. Once you have done the initial synch, then
just failover to the new site and dismantle the old one.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:cc76a479-5b61-41ba-8f5c-95e98883d924@.z17g2000hsg.googlegroups.com...
My company located in Delware,is moving to a new office and I need to
keep our production database online by moving it onto servers in our
Las Vegas office. I'm looking for suggested ways to move the
databases
as seamlessly as possible to our Vegas office.
We conduct business Monday through Saturday. The way I understand the
move, after close of business on Saturday we want the data center to
be moved to Vegas and available on Monday morning, which doesn't
provide much time to get the db up and running.
We currently have our production database on an active/passive
cluster
running SQL 2005 with transactional replication in place to support
reporting for our enterprise application.
What options are available besides copying a backup across my WAN and
restoring the database?
What limitations are imposed by Log Shipping given the physical
distance betwen locations, etc.
If anyone can help point me in the proper direction I would greatly
appreciate the help.
NC3|||Hi
You don't say if the hardware is moving or not or if when it moves any
configuration changes will be made? If the kit is moving do you have
alternate kit as a backup in case of problems?
If you are moving to new/alternate kit then you can test the procedures
before hand so you will know if they are feasable or take too long. You
should also look at how you can minimise the actual work needed when you do
make the move, such as having everything in place and tested on a copy of the
database so there is only a more up-to-date version of the database to slot
in.
John
"NC3" wrote:
> My company located in Delware,is moving to a new office and I need to
> keep our production database online by moving it onto servers in our
> Las Vegas office. I'm looking for suggested ways to move the
> databases
> as seamlessly as possible to our Vegas office.
> We conduct business Monday through Saturday. The way I understand the
> move, after close of business on Saturday we want the data center to
> be moved to Vegas and available on Monday morning, which doesn't
> provide much time to get the db up and running.
>
> We currently have our production database on an active/passive
> cluster
> running SQL 2005 with transactional replication in place to support
> reporting for our enterprise application.
>
> What options are available besides copying a backup across my WAN and
> restoring the database?
>
> What limitations are imposed by Log Shipping given the physical
> distance betwen locations, etc.
>
> If anyone can help point me in the proper direction I would greatly
> appreciate the help.
>
> NC3
>|||On Feb 20, 3:47=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Consider database mirroring. =A0Once you have done the initial synch, then=
> just failover to the new site and dismantle the old one.
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:cc76a479-5b61-41ba-8f5c-95e98883d924@.z17g2000hsg.googlegroups.com...
> My company located in Delware,is moving to a new office and I need to
> keep our production database online by moving it onto servers in our
> Las Vegas office. I'm looking for suggested ways to move the
> databases
> as seamlessly as possible to our Vegas office.
> We conduct business Monday through Saturday. The way I understand the
> move, after close of business on Saturday we want the data center to
> be moved to Vegas and available on Monday morning, which doesn't
> provide much time to get the db up and running.
> We currently have our production database on an active/passive
> cluster
> running SQL 2005 with transactional replication in place to support
> reporting for our enterprise application.
> What options are available besides copying a backup across my WAN and
> restoring the database?
> What limitations are imposed by Log Shipping given the physical
> distance betwen locations, etc.
> If anyone can help point me in the proper direction I would greatly
> appreciate the help.
> NC3
How would Mirroring effect transactional replication and can I mirror
a SQL Cluster|||Not sure about replication but you can mirror between clusters:
http://www.microsoft.com/technet/technetmag/issues/2007/03/sqlclusters/default.aspx?loc=en
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:833e7401-6825-4586-ae4a-b0a25de9b52f@.p25g2000hsf.googlegroups.com...
On Feb 20, 3:47 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Consider database mirroring. Once you have done the initial synch, then
> just failover to the new site and dismantle the old one.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:cc76a479-5b61-41ba-8f5c-95e98883d924@.z17g2000hsg.googlegroups.com...
> My company located in Delware,is moving to a new office and I need to
> keep our production database online by moving it onto servers in our
> Las Vegas office. I'm looking for suggested ways to move the
> databases
> as seamlessly as possible to our Vegas office.
> We conduct business Monday through Saturday. The way I understand the
> move, after close of business on Saturday we want the data center to
> be moved to Vegas and available on Monday morning, which doesn't
> provide much time to get the db up and running.
> We currently have our production database on an active/passive
> cluster
> running SQL 2005 with transactional replication in place to support
> reporting for our enterprise application.
> What options are available besides copying a backup across my WAN and
> restoring the database?
> What limitations are imposed by Log Shipping given the physical
> distance betwen locations, etc.
> If anyone can help point me in the proper direction I would greatly
> appreciate the help.
> NC3
How would Mirroring effect transactional replication and can I mirror
a SQL Cluster|||On Feb 21, 3:46=A0pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Not sure about replication but you can mirror between clusters:
> http://www.microsoft.com/technet/technetmag/issues/2007/03/sqlcluster...
> --
> =A0 =A0Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON =A0 Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau=
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:833e7401-6825-4586-ae4a-b0a25de9b52f@.p25g2000hsf.googlegroups.com...
> On Feb 20, 3:47 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
> > Consider database mirroring. Once you have done the initial synch, then
> > just failover to the new site and dismantle the old one.
> > --
> > Tom
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > "NC3" <ncolem...@.yahoo.com> wrote in message
> >news:cc76a479-5b61-41ba-8f5c-95e98883d924@.z17g2000hsg.googlegroups.com...=
> > My company located in Delware,is moving to a new office and I need to
> > keep our production database online by moving it onto servers in our
> > Las Vegas office. I'm looking for suggested ways to move the
> > databases
> > as seamlessly as possible to our Vegas office.
> > We conduct business Monday through Saturday. The way I understand the
> > move, after close of business on Saturday we want the data center to
> > be moved to Vegas and available on Monday morning, which doesn't
> > provide much time to get the db up and running.
> > We currently have our production database on an active/passive
> > cluster
> > running SQL 2005 with transactional replication in place to support
> > reporting for our enterprise application.
> > What options are available besides copying a backup across my WAN and
> > restoring the database?
> > What limitations are imposed by Log Shipping given the physical
> > distance betwen locations, etc.
> > If anyone can help point me in the proper direction I would greatly
> > appreciate the help.
> > NC3
> How would Mirroring effect transactional replication and can I mirror
> a SQL Cluster- Hide quoted text -
> - Show quoted text -
What do you thing of having 2 subscribers for the same publisher. Do
think that's something that might work?|||Not sure what you mean. You can have as many subscribers to a publication
that you want.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"NC3" <ncoleman3@.yahoo.com> wrote in message
news:991ea951-2d8c-44bd-93e2-10871e6d4bce@.i7g2000prf.googlegroups.com...
On Feb 21, 3:46 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
> Not sure about replication but you can mirror between clusters:
> http://www.microsoft.com/technet/technetmag/issues/2007/03/sqlcluster...
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> "NC3" <ncolem...@.yahoo.com> wrote in message
> news:833e7401-6825-4586-ae4a-b0a25de9b52f@.p25g2000hsf.googlegroups.com...
> On Feb 20, 3:47 pm, "Tom Moreau" <t...@.dont.spam.me.cips.ca> wrote:
>
>
> > Consider database mirroring. Once you have done the initial synch, then
> > just failover to the new site and dismantle the old one.
> > --
> > Tom
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
> > "NC3" <ncolem...@.yahoo.com> wrote in message
> >news:cc76a479-5b61-41ba-8f5c-95e98883d924@.z17g2000hsg.googlegroups.com...
> > My company located in Delware,is moving to a new office and I need to
> > keep our production database online by moving it onto servers in our
> > Las Vegas office. I'm looking for suggested ways to move the
> > databases
> > as seamlessly as possible to our Vegas office.
> > We conduct business Monday through Saturday. The way I understand the
> > move, after close of business on Saturday we want the data center to
> > be moved to Vegas and available on Monday morning, which doesn't
> > provide much time to get the db up and running.
> > We currently have our production database on an active/passive
> > cluster
> > running SQL 2005 with transactional replication in place to support
> > reporting for our enterprise application.
> > What options are available besides copying a backup across my WAN and
> > restoring the database?
> > What limitations are imposed by Log Shipping given the physical
> > distance betwen locations, etc.
> > If anyone can help point me in the proper direction I would greatly
> > appreciate the help.
> > NC3
> How would Mirroring effect transactional replication and can I mirror
> a SQL Cluster- Hide quoted text -
> - Show quoted text -
What do you thing of having 2 subscribers for the same publisher. Do
think that's something that might work?sql

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

Odd error message in Microsoft Query

I am using an online ODBC Database.

The fun thing is that I'm running into a circumstance where one SQL statement that works in Visual Studio accessing the same database works, but then I try to use the same statement in Microsoft Query (running to Excel) and it refuses to admit its a valid statement.

The SQL statement I'm using is this.

SELECT MLNumber, StreetNumber, StreetName, StreetDirection, ListingOfficeMLSID, Status, Bedrooms, Bathrooms, City
FROM "data: Property:RESI"
WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

This works fine inside of VS 2005.

The same statement used within Microsoft Query returns this error message.

SQL SYNTAX ERROR - Unexpected char: '?'

Working with the people that work with that database regularly, they say that I need to write a METADATA-LOOKUPTYPE call that should tell me how the City name 'Boiling Spring Lakes' is actually formatted.

I, of course, have no clue how to do that. But my thought is, if it works in VS2005 and returns a valid result, why does the same SQL statement return an error message through Excel?

HELP!

Thanks in advance for your time.

Are you sure that the problem is exactly with the City=... clause?

Does the query work if you remove it?

|||

You need to replace the double quotes around [Boiling Spring Lakes] with single quotes.

SQL Server needs single quotes to delimit a string. Visual Basic uses double quotes.

|||

Actually it depends on how quoted_identifier is set.

If you execute SET QUOTED_IDENTIFIER OFF you can use double quotes for literals afterwards. If quoted_identifier is on the double quotes signify identifiers. The default is usually ON.

|||

Yes, the query works just fine in Excel without 'City' in the WHERE statement.

The Double Quotes were what enabled the query to work properly within Visual Studios. It was also suggested that I use SQDQ CITY NAME DQSQ ('"Oak Island"') as a possible work around. That didn't work, but just the double quotes did. Again, that worked in VS2005.

I need it to work in Microsoft Query as well so I can use the same data to create a form with both Database items and manual entered items not found on the Database.

Anyway to create a form like that within VS2005? Just to remain in house, not to deploy to the web.

Hmm, I'll give the 'Quoted Identifier' a shot. I'll dig through Microsoft Query to find it. Or where would I execute that?

|||Bump. Anyone found a workaround for this? Or can direct me to where and how I merge database-fed forms with manual entered forms within Visual Studios?|||

I'm still confused about the single/double quotes issues.

You indicate using both single quotes and double quotes in the WHERE clause, and that is very suspicious to me.

WHERE (ListingOfficeMLSID = '550000020' OR
ListingOfficeMLSID = '550001760') AND (MLNumber > 1) AND (City = "Boiling Spring Lakes")

Please verify that the query DOES NOT work if the double quotes around "Boiling Spring Lakes" are replaced with single quotes.

|||

The single and double quote issue. Sorry I forgot to post that the answer worked.

The single and double quotes from above were used as reccomended to sorta force the SQL to read Boiling Spring Lakes as a complete string. That works just fine in .NET 2.0 and VS 2005.

However, MS Query doesn't translate it the same way. Turned out that the problem wasn't on my end, it was on the database side of it and faulty handling of the shortdesc and longdesc.

thank you all for your time and trouble.

Jack

ODBC tracing! No Info

Hi I know I should be able to find this out myself , but both book I have don't seem to know and the online helps isn't much better.

I want to find out if ODBC tracing is on or off, and if it is on I want to be able to turn it off.

How can I do this? I just can't find any Info on it!

Thanks Ed

P.S. If their was a Icon for simpleton, I would use it!ODBC Tracing? I'm not sure what you are talking about. You can get server and connection information with these commands:

sp_configure
SQLGetConnectAttr
SQLSetConnectAttr

blindman|||Originally posted by blindman
ODBC Tracing? I'm not sure what you are talking about. You can get server and connection information with these commands:

sp_configure
SQLGetConnectAttr
SQLSetConnectAttr

blindman



My orginal problem was that EM runs really slowely when looking a propeties. So I searched through the existing threads to try and find out what the problem and one of the suggestion was that I may have ODBC tracing turned on and that could be slowing down the system, but I could not find a way see if ODBC tracing was on or off.

I thought it was on a setting withine the database , now figured it out by talking to the other in the office, sorry, thanks anyway!|||odbc tracing is in admin tools/datasource configuration. it comes very handy by allowing to see what actual statements are issued by the driver manager. poobah probably never used it :)|||Nope. I haven't. My expertise is in database design, transact sql programming, and query optimization. I know a fair amount about database tuning and administration, but in general I leave those posts to others to answer.

Like nixies, I couldn't find anything in Books Online referencing this configuration. Is it strictly middle-tier or interface resident?

blindman|||all sql server client tools are using odbc driver manager one way or the other. below is a sample set of entries from the log file if you turn tracing on in your data sources tracing tab:

SQL Server Ente 7b4-794 ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 01B471D4

SQL Server Ente 7b4-794 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0x01B471D4 ( 0x01a31540)

SQL Server Ente 7b4-794 ENTER SQLSetEnvAttr
SQLHENV 01A31540
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003
SQLINTEGER -5

if tracing is turned on the performance of that workstation deteriorates. as an example i turned it on and opened em and expanded a server (in a sense connected to it). within a minute after it i turned tracing off. the resulting file size was 472K. mind you i didn't do anything other than what i described, - connected to the server and nothing else.|||Now I see. So its a workstation setting, not a server setting.

Thanks

blindman|||not necessarily, you can do the same thing on the server.