Clarification on ALT Snapshot Folder Location

I have 2 sql boxes setup in a VM environment, with neither of them knowing each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder location (Where I copied the Snapshot files to) c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file 'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch' due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription replication....
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum security I need to give everyone when I try & do this exact same setup when using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but recalling giving Everyone Full Control may be a bad thing (But it's just this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder location (Where I copied the Snapshot files to) c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file 'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch' due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription replication....
|||Its read and list files and folders.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ub7HLgO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum
security I need to give everyone when I try & do this exact same setup when
using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt
Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way
from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but
recalling giving Everyone Full Control may be a bad thing (But it's just
this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing
each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as
follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot
location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder
location (Where I copied the Snapshot files to)
c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file
'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch'
due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription
replication....
|||THANX!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uGIyPwO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Its read and list files and folders.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ub7HLgO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum
security I need to give everyone when I try & do this exact same setup when
using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt
Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way
from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but
recalling giving Everyone Full Control may be a bad thing (But it's just
this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing
each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as
follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot
location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder
location (Where I copied the Snapshot files to)
c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file
'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch'
due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription
replication....

Checking if DB Connection is active or not

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata NarayanaIn the above one, please read it as SELECT 1 instead of SELECT * from 1

Venkata Narayana wrote:

Quote:

Originally Posted by

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.
>
This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)
>
My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.
>
a) Create Table
b) Use SELECT query
c) Drop table
>
You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.
>
One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.
>
So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?
>
Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.
>
Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,
>
Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});
>
The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.
>
What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:
>
try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}
>
This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).
>
Any comments on this would be appreciated.
>
Regards,
Venkata Narayana

|||Venkata Narayana wrote:

Quote:

Originally Posted by

In the above one, please read it as SELECT 1 instead of SELECT * from 1
>
Venkata Narayana wrote:

Quote:

Originally Posted by

Hi,
You all may be knowing that Connection.isClosed() does not tells us
if the underying DB connection is active or not; it only checks if
Connection.close() had been previously called or not.
One sure shot way to find out this is by executing some dummy SELECT
query and catching it via SQLException.

This could be done in various DB's as follows:
SELECT * from 1 (MS SQL)
SELECT * from DUAL(Oracle)

My question is what if you use some other DB , which is not famous as
the above.
This could still be achieved by creating dummy table with one column
and querying it. One pitfall of doing this approach is we may not have
create permissions to create table. Even if we have permissions to
create table, you need to do the following, if you need to check DB
Connection every time.

a) Create Table
b) Use SELECT query
c) Drop table

You may ask me why we need to use drop table. This is because, we can
not create many tables and keep them alive if we were to check (DB
Conn) it for 100 times. One way is we can use IF NOT EXISTS along with
Create table. Unfortunately, this command is not supported by all DB
vendors. So, this is ruled out.

One more way of doing is writing simple stored procedure that returns
plain constant. Unfortunatley the syntax for Stored procedures is
different for different DB Vendors.

So, do we have a correct way of finding if DB connection is active,
that would work on all DB's ?

Fortunately, there is a way to do this.
We could use Connection.getMetaData().getTables(null,null,null, null).
We could use this way as this would surely get the number of tables
present at that moment. How many tables are present in a DB will not
be cached as this may change dynamically. One disadvantage of using
this approach is performance. What if a DB has 1000 tables, it tries to
get the names of 1000 tables and it is performance hit.

Is there a solution for this?. Yes, we can use getTables method by
invoking only against the SYSTEM table types. I am sure any DB will
not have many system tables.
So, our call would be,

Conn.getMetaData().getTables(null,null,null,new String[]{"SYSTEM
TABLE"});

The above statement is expected to give whether connection is active;
if connection is not active, then it throws SQLException. And best part
is it will work on all DB Drivers.

What if some JDBC driver does not implement the above getTables() call,
then we would get some AbstractMethodError that can be caught using
LinkageError. So, finally code for checking if connection is active or
not is as follows:

try {
ResultSet rs = conn.getMetaData().getTables(null,null,null,new
String[]{"SYSTEM TABLE"});
} catch (SQLException e) {
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}catch(LinkageError e){
conn.close();// use try catch block here to catch SQLException for
Conn.close();
//call to open new DB connection.
getNewConnection();
}
}

This limitation (if it can be called) is going to be fixed for JDBC
4.0 implemented drivers(if they implement it in right way).

Any comments on this would be appreciated.

Regards,
Venkata Narayana

Whatever you do to test a connection, you want it fast and non-taxing
of DBMS resources, so even if you have table-create permissions, you
don't want to do that. The fast thing is a DBMS-specific query:

Sybase, MS: select 1
Oracle: begin null; end; or select 1 from dual
DB2: select 1 from sysdummy
etc.

You can always call DatabaseMetaData.getDatabaseProductVersion()
to figure out what DBMS-specific SQL to send.
If you really must be DBMS-neutral, you can call DatabaseMetaData
getTables() with arguments that define a non-existent table. The DBMS
will still have to look, but the search for a single table
'NONEXISTENT'
won't be too bad.
Lastly, note that whatever you use to test a connection, the
connection
may fail the very instant after your test succeeds, so your subsequent
code will have to be able to deal with a broken connection anyway. In
practice you would only want to test connections that had been sitting
idle for a significant period.

Joe Weinstein at BEA Systems