Accessing linked servers dynamically

We have a view in one database that consists of the union of all the
rows in a set of tables located on a number of remote linked servers.
If I hard-code the remote server names in the view, it will fail if
any of the remote servers is unavailable. To make this more robust, I
would like it to only query those servers which are available. So I am
maintaining a list of available servers in a table in my main
database. My idea then is to replace the view with a function that
returns a table variable. This function will query all the remote
servers that are available, inserting rows into the table variable.
So within the function I have to generate a piece of dynamic SQL (in
the format 'SELECT ... FROM server.database.dbo.table') to access the
linked server. This works fine, but I can't find a way to get the
results of this query into my table variable. If I run a piece of
dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
the scope of the dynamic SQL is outside the scope of the function. And
if I use a temporary table I'll get concurrency problems. I have tried
using OPENQUERY and OPENROWSET, but it seems you can't pass string
variables as the parameters to either of these, so effectively I'm
back to hard-coding the server names.
So my question is (finally!): Does anyone know of a way to access a
linked server whose name I have in a string without using dynamic SQL?
Or is there a better way to achieve this?
Thanks in advance!James,
variable would not qualify as table name in a query, whether that variable
represents a local or linked server.
A way around for your situation may be using global temp table. It's not
that much different from a variable, in some situation offers more and in
others less advantage.
hth
Quentin
"James Bosworth" <james.bosworth@.triadgroup.plc.uk> wrote in message
news:1967a78c.0307250500.68dc377e@.posting.google.com...
> We have a view in one database that consists of the union of all the
> rows in a set of tables located on a number of remote linked servers.
> If I hard-code the remote server names in the view, it will fail if
> any of the remote servers is unavailable. To make this more robust, I
> would like it to only query those servers which are available. So I am
> maintaining a list of available servers in a table in my main
> database. My idea then is to replace the view with a function that
> returns a table variable. This function will query all the remote
> servers that are available, inserting rows into the table variable.
> So within the function I have to generate a piece of dynamic SQL (in
> the format 'SELECT ... FROM server.database.dbo.table') to access the
> linked server. This works fine, but I can't find a way to get the
> results of this query into my table variable. If I run a piece of
> dynamic SQL with 'INSERT INTO @.tablevariable' it won't work because
> the scope of the dynamic SQL is outside the scope of the function. And
> if I use a temporary table I'll get concurrency problems. I have tried
> using OPENQUERY and OPENROWSET, but it seems you can't pass string
> variables as the parameters to either of these, so effectively I'm
> back to hard-coding the server names.
> So my question is (finally!): Does anyone know of a way to access a
> linked server whose name I have in a string without using dynamic SQL?
> Or is there a better way to achieve this?
> Thanks in advance!

Accessing Different Servers

If I have 2 registered servers, what syntax allows me to access both servers
from the same query window? For example, if I say "use mydb1" and mydb1
resides on server1, I can execute sql against it.
I'd like to execute another statement after finishing with mydb1 that
accesses mydb1 that resides on server2. Any help with proper syntax?Look at the :connect command in SQLCMD mode in the query editor (assuming
this is SSMS).
The help topic which should give some help is "Editing SQLCMD scripts with
Query Editor"

> If I have 2 registered servers, what syntax allows me to access both
> servers from the same query window? For example, if I say "use mydb1"
> and mydb1 resides on server1, I can execute sql against it.
> I'd like to execute another statement after finishing with mydb1 that
> accesses mydb1 that resides on server2. Any help with proper syntax?
>|||i'm just trying to find syntax like "use mydb" that would take the name of
the server.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||can you point me a little better to the help article? i don't know what
SQLCMD mode is and don't know what SSMS is.
"Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
> Look at the :connect command in SQLCMD mode in the query editor (assuming
> this is SSMS).
> The help topic which should give some help is "Editing SQLCMD scripts with
> Query Editor"
>
>|||Hi Scott
Bruce is assuming you are working with SQL Server 2005, since you didn't
state the version. SQLCMD is the text interface, replacing osql. SSMS is SQL
Server Management Studio, replacing both Query Analyzer and Enterprise
Manager. Please always state what version you are using at the beginning of
every request for help.
There is no equivalent to USE to connect to another server.
You can access tables on another server by setting up a linked server and
using a 4-part name to access the tables:
SELECT ...
FROM servername.dbname.object_owner.object
WHERE ...
You can read about linked servers in BOL (Books Online, in both SQL 2000 and
SQL 2005).
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
> can you point me a little better to the help article? i don't know what
> SQLCMD mode is and don't know what SSMS is.
>
> "Bruce Prang [MSFT]" <bruce.prang@.microsoft.com> wrote in message
> news:adbd29d54e938c8068e8b5d8673@.msnews.microsoft.com...
>
>|||I am using 2005 and I found the help topic, but would appreciate a little
extra info ...
1. Can I use SQLCMD commands within a SPROC?
2. Is it possible to detach/re-attach databases on different servers from 1
SPROC or SQL commands from a single query window?
Do you have any good books to recommend on SQLCMD?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi Scott
> Bruce is assuming you are working with SQL Server 2005, since you didn't
> state the version. SQLCMD is the text interface, replacing osql. SSMS is
> SQL Server Management Studio, replacing both Query Analyzer and Enterprise
> Manager. Please always state what version you are using at the beginning
> of every request for help.
> There is no equivalent to USE to connect to another server.
> You can access tables on another server by setting up a linked server and
> using a 4-part name to access the tables:
> SELECT ...
> FROM servername.dbname.object_owner.object
> WHERE ...
> You can read about linked servers in BOL (Books Online, in both SQL 2000
> and SQL 2005).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:%239ghUIlOGHA.2696@.TK2MSFTNGP14.phx.gbl...
>
>|||Scott
SQLCMD is an operating system utility, not a TSQL command, so the only way
to use it within a sproc is to use xp_cmdshell which allow you to invoke an
OS command.
You can run stored procedures or other commands on other servers if you have
set the other servers up as linked servers. As I already suggested, please
read about linked servers in the BOL.
While in SSMS, you can change the server you are connected to without using
linked servers. So you could run one command against server1, then click the
button to change your connection, and connect to server 2, where you could
run a different command without changing your query window. I'm sorry, I
don't have a SQL 2005 active right now to tell you where the button to
change connections is.
I'm not familiar with any of the SQL 2005 that are out already, but I
wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
best place to look.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
> I am using 2005 and I found the help topic, but would appreciate a little
> extra info ...
> 1. Can I use SQLCMD commands within a SPROC?
> 2. Is it possible to detach/re-attach databases on different servers from
> 1 SPROC or SQL commands from a single query window?
> Do you have any good books to recommend on SQLCMD?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OWtGmMlOGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||I get the idea. Do you think that SQLCMD is the only way to detach/atach
databases on different servers from 1 query window? I just though there must
be a way to do this without going into SQLCMD.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uUVVM$xOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Scott
> SQLCMD is an operating system utility, not a TSQL command, so the only way
> to use it within a sproc is to use xp_cmdshell which allow you to invoke
> an OS command.
> You can run stored procedures or other commands on other servers if you
> have set the other servers up as linked servers. As I already suggested,
> please read about linked servers in the BOL.
> While in SSMS, you can change the server you are connected to without
> using linked servers. So you could run one command against server1, then
> click the button to change your connection, and connect to server 2, where
> you could run a different command without changing your query window. I'm
> sorry, I don't have a SQL 2005 active right now to tell you where the
> button to change connections is.
> I'm not familiar with any of the SQL 2005 that are out already, but I
> wouldn't imagine there would be a whole book on SQLCMD. BOL would be the
> best place to look.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eTq4KvnOGHA.3864@.TK2MSFTNGP10.phx.gbl...
>
>

Accessing Database on another server

Hi All,
I have application where databases/tables spread between two
servers(different building, wireless connection). During data entries from
front-end, the application must access the tables on both servers
interactively.
The question is :
- is Linked Server is the only way to access tables on another server ?
- How is the performance of accessing tables on local server compared to
accessing tables on linked server ?
Thanks for your comments,
KristTristant,
"tristant" <krislioe@.cbn.net.id> wrote in message news:OpzPgQRnDHA.1408@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I have application where databases/tables spread between two
> servers(different building, wireless connection). During data entries from
> front-end, the application must access the tables on both servers
> interactively.
> The question is :
> - is Linked Server is the only way to access tables on another server ?
No. You can replicate the data from one server to the other, or use
openrowset for an adhoc connection
> - How is the performance of accessing tables on local server compared to
> accessing tables on linked server ?
As you would expect - much worse.
Especially with WiFi, where you don't get all the bandwidth claimed anyway
and should be using VPN or other overhead to keep the link safe.
That said, you are not normally puting a whole lot of data through the link, so it
is still perfectly useable, my preference would be to have a client connection to
only one server, and use replication. But you don't say anything about updates,
or data volumes so it's a bit hard to comment.
Regards
AJ

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?
Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:

> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
> execute a query on SERVER_A referencing a table on SERVER_B is that possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?
|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/de..._adda_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?
|||Hey thanks - its working great
"Jerry Spivey" wrote:

> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/de..._adda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/de..._adda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
>
>

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible
?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:

> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
to
> execute a query on SERVER_A referencing a table on SERVER_B is that possib
le?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/d... />
a_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Hey thanks - its working great
"Jerry Spivey" wrote:

> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/d...>
dda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
>
>

Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
> execute a query on SERVER_A referencing a table on SERVER_B is that possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Hey thanks - its working great
"Jerry Spivey" wrote:
> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> > If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> > to
> > execute a query on SERVER_A referencing a table on SERVER_B is that
> > possible?
> >
> > Example
> >
> > First Server: SERVER_A, Database: Nuts, Table: Goodness
> >
> > Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> >
> > From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> > where I need to get some data from SERVER_A , database 'Nuts', table
> > 'Goodness'.
> >
> > Is that possible? How can I do it? Would I use a linked server? If so can
> > anyone show me a good resource on how to set up a linked server?
>
>

Accessing 2 Sql databases concurrently ?

Hi,
I was wondering how, if possible you are able to access 2
databases on 2 different sql servers (7, 2000)
concurrently?
Please do not multi-post. I answered your question in .server.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Chad" <anonymous@.discussions.microsoft.com> wrote in message
news:1f9c01c485fa$9cfb2740$a301280a@.phx.gbl...
Hi,
I was wondering how, if possible you are able to access 2
databases on 2 different sql servers (7, 2000)
concurrently?

Access to sql database from two app servers

Hi - I have a single database server "database" and two app servers
written in VB.NET trying to access this database. Lets say the app
servers are "serverA" and "serverB".
I get a "sql server does not exist or access denied" error during
the connect part of my code from serverB.
There is no problem with connecting from serverA. I checked the
connection string used and they are similar and no difference except
the workstation id=" " which has the respective values. Both the
servers try connecting to the same database port.
One thing that our firm uses is a service id or custom id for the
domain that the database provides access to.
Has anyone experienced such a problem before. Please help me in
this.
Thanks in advance.
Anush
The database is sql 2000.
On Jan 23, 10:27 pm, iaman...@.gmail.com wrote:
> Hi - I have a single database server "database" and two app servers
> written in VB.NET trying to access this database. Lets say the app
> servers are "serverA" and "serverB".
> I get a "sql server does not exist or access denied" error during
> the connect part of my code from serverB.
> There is no problem with connecting from serverA. I checked the
> connection string used and they are similar and no difference except
> the workstation id=" " which has the respective values. Both the
> servers try connecting to the same database port.
> One thing that our firm uses is a service id or custom id for the
> domain that the database provides access to.
> Has anyone experienced such a problem before. Please help me in
> this.
> Thanks in advance.
> Anush
|||Hi,
try to solve the problem on a physical layer first. Is the machine
reachable from the app server ? Is it pingable ? Is any firewall
activated or no rule implemented to let the app Server talk to the SQL
Server ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Hi
Is it linked server? Have you tried to connect to SQL_B with the same user
but using Query Analyzer?
<iamanush@.gmail.com> wrote in message
news:1169616422.571959.132600@.h3g2000cwc.googlegro ups.com...
> Hi - I have a single database server "database" and two app servers
> written in VB.NET trying to access this database. Lets say the app
> servers are "serverA" and "serverB".
> I get a "sql server does not exist or access denied" error during
> the connect part of my code from serverB.
> There is no problem with connecting from serverA. I checked the
> connection string used and they are similar and no difference except
> the workstation id=" " which has the respective values. Both the
> servers try connecting to the same database port.
> One thing that our firm uses is a service id or custom id for the
> domain that the database provides access to.
> Has anyone experienced such a problem before. Please help me in
> this.
> Thanks in advance.
> Anush
>
|||HI Jens - The machine is pingable from this serverB and I can also see
the connection coming in to the db box. I see the log registering the
incoming connection, however it fails in the db and logs an exception
on the log. so its not the physical layer i believe.
Thx.
Anush
On Jan 24, 12:21 am, "Jens" <J...@.sqlserver2005.de> wrote:
> Hi,
> try to solve the problem on a physical layer first. Is the machine
> reachable from the app server ? Is it pingable ? Is any firewall
> activated or no rule implemented to let the app Server talk to the SQL
> Server ?
> HTH, Jens K. Suessmeyer.
> --http://www.sqlserver2005.de
> --
|||i havent tried this yet. Will try now.
Anush
On Jan 24, 12:32 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> Is it linked server? Have you tried to connect to SQL_B with the same user
> but using Query Analyzer?
> <iaman...@.gmail.com> wrote in messagenews:1169616422.571959.132600@.h3g2000cwc.go oglegroups.com...
>
>
>
|||Ok, can you please post the connectring here ? You can obfuscate it a
bit to cover your secrets. WHat is the database, is it a service or a
Windows 7 Console application ? There might apply the (Access denied
part) to it and you are providing the wrong credentials. You can track
the unsuccessfull connections in profiler and can see which user is
currently accessing the database, but perhaps the post of the
connectionstring might light up the situation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

Access to sql database from two app servers

Hi - I have a single database server "database" and two app servers
written in VB.NET trying to access this database. Lets say the app
servers are "serverA" and "serverB".
I get a "sql server does not exist or access denied" error during
the connect part of my code from serverB.
There is no problem with connecting from serverA. I checked the
connection string used and they are similar and no difference except
the workstation id=" " which has the respective values. Both the
servers try connecting to the same database port.
One thing that our firm uses is a service id or custom id for the
domain that the database provides access to.
Has anyone experienced such a problem before. Please help me in
this.
Thanks in advance.
AnushThe database is sql 2000.
On Jan 23, 10:27 pm, iaman...@.gmail.com wrote:
> Hi - I have a single database server "database" and two app servers
> written in VB.NET trying to access this database. Lets say the app
> servers are "serverA" and "serverB".
> I get a "sql server does not exist or access denied" error during
> the connect part of my code from serverB.
> There is no problem with connecting from serverA. I checked the
> connection string used and they are similar and no difference except
> the workstation id=" " which has the respective values. Both the
> servers try connecting to the same database port.
> One thing that our firm uses is a service id or custom id for the
> domain that the database provides access to.
> Has anyone experienced such a problem before. Please help me in
> this.
> Thanks in advance.
> Anush|||Hi,
try to solve the problem on a physical layer first. Is the machine
reachable from the app server ? Is it pingable ? Is any firewall
activated or no rule implemented to let the app Server talk to the SQL
Server ?
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hi
Is it linked server? Have you tried to connect to SQL_B with the same user
but using Query Analyzer?
<iamanush@.gmail.com> wrote in message
news:1169616422.571959.132600@.h3g2000cwc.googlegroups.com...
> Hi - I have a single database server "database" and two app servers
> written in VB.NET trying to access this database. Lets say the app
> servers are "serverA" and "serverB".
> I get a "sql server does not exist or access denied" error during
> the connect part of my code from serverB.
> There is no problem with connecting from serverA. I checked the
> connection string used and they are similar and no difference except
> the workstation id=" " which has the respective values. Both the
> servers try connecting to the same database port.
> One thing that our firm uses is a service id or custom id for the
> domain that the database provides access to.
> Has anyone experienced such a problem before. Please help me in
> this.
> Thanks in advance.
> Anush
>|||HI Jens - The machine is pingable from this serverB and I can also see
the connection coming in to the db box. I see the log registering the
incoming connection, however it fails in the db and logs an exception
on the log. so its not the physical layer i believe.
Thx.
Anush
On Jan 24, 12:21 am, "Jens" <J...@.sqlserver2005.de> wrote:
> Hi,
> try to solve the problem on a physical layer first. Is the machine
> reachable from the app server ? Is it pingable ? Is any firewall
> activated or no rule implemented to let the app Server talk to the SQL
> Server ?
> HTH, Jens K. Suessmeyer.
> --http://www.sqlserver2005.de
> --|||i havent tried this yet. Will try now.
Anush
On Jan 24, 12:32 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> Is it linked server? Have you tried to connect to SQL_B with the same user
> but using Query Analyzer?
> <iaman...@.gmail.com> wrote in messagenews:1169616422.571959.132600@.h3g2000cwc.googlegroups.com...
> > Hi - I have a single database server "database" and two app servers
> > written in VB.NET trying to access this database. Lets say the app
> > servers are "serverA" and "serverB".
> > I get a "sql server does not exist or access denied" error during
> > the connect part of my code from serverB.
> > There is no problem with connecting from serverA. I checked the
> > connection string used and they are similar and no difference except
> > the workstation id=" " which has the respective values. Both the
> > servers try connecting to the same database port.
> > One thing that our firm uses is a service id or custom id for the
> > domain that the database provides access to.
> > Has anyone experienced such a problem before. Please help me in
> > this.
> > Thanks in advance.
> > Anush|||Ok, can you please post the connectring here ? You can obfuscate it a
bit to cover your secrets. WHat is the database, is it a service or a
Windows 7 Console application ? There might apply the (Access denied
part) to it and you are providing the wrong credentials. You can track
the unsuccessfull connections in profiler and can see which user is
currently accessing the database, but perhaps the post of the
connectionstring might light up the situation.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Access to sql database from two app servers

Hi - I have a single database server "database" and two app servers
written in VB.NET trying to access this database. Lets say the app
servers are "serverA" and "serverB".
I get a "sql server does not exist or access denied" error during
the connect part of my code from serverB.
There is no problem with connecting from serverA. I checked the
connection string used and they are similar and no difference except
the workstation id=" " which has the respective values. Both the
servers try connecting to the same database port.
One thing that our firm uses is a service id or custom id for the
domain that the database provides access to.
Has anyone experienced such a problem before. Please help me in
this.
Thanks in advance.
AnushThe database is sql 2000.
On Jan 23, 10:27 pm, iaman...@.gmail.com wrote:
> Hi - I have a single database server "database" and two app servers
> written in VB.NET trying to access this database. Lets say the app
> servers are "serverA" and "serverB".
> I get a "sql server does not exist or access denied" error during
> the connect part of my code from serverB.
> There is no problem with connecting from serverA. I checked the
> connection string used and they are similar and no difference except
> the workstation id=" " which has the respective values. Both the
> servers try connecting to the same database port.
> One thing that our firm uses is a service id or custom id for the
> domain that the database provides access to.
> Has anyone experienced such a problem before. Please help me in
> this.
> Thanks in advance.
> Anush|||Hi,
try to solve the problem on a physical layer first. Is the machine
reachable from the app server ? Is it pingable ? Is any firewall
activated or no rule implemented to let the app Server talk to the SQL
Server ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Hi
Is it linked server? Have you tried to connect to SQL_B with the same user
but using Query Analyzer?
<iamanush@.gmail.com> wrote in message
news:1169616422.571959.132600@.h3g2000cwc.googlegroups.com...
> Hi - I have a single database server "database" and two app servers
> written in VB.NET trying to access this database. Lets say the app
> servers are "serverA" and "serverB".
> I get a "sql server does not exist or access denied" error during
> the connect part of my code from serverB.
> There is no problem with connecting from serverA. I checked the
> connection string used and they are similar and no difference except
> the workstation id=" " which has the respective values. Both the
> servers try connecting to the same database port.
> One thing that our firm uses is a service id or custom id for the
> domain that the database provides access to.
> Has anyone experienced such a problem before. Please help me in
> this.
> Thanks in advance.
> Anush
>|||HI Jens - The machine is pingable from this serverB and I can also see
the connection coming in to the db box. I see the log registering the
incoming connection, however it fails in the db and logs an exception
on the log. so its not the physical layer i believe.
Thx.
Anush
On Jan 24, 12:21 am, "Jens" <J...@.sqlserver2005.de> wrote:
> Hi,
> try to solve the problem on a physical layer first. Is the machine
> reachable from the app server ? Is it pingable ? Is any firewall
> activated or no rule implemented to let the app Server talk to the SQL
> Server ?
> HTH, Jens K. Suessmeyer.
> --http://www.sqlserver2005.de
> --|||i havent tried this yet. Will try now.
Anush
On Jan 24, 12:32 am, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> Hi
> Is it linked server? Have you tried to connect to SQL_B with the same user
> but using Query Analyzer?
> <iaman...@.gmail.com> wrote in messagenews:1169616422.571959.132600@.h3g2000
cwc.googlegroups.com...
>
>
>
>
>
>|||Ok, can you please post the connectring here ? You can obfuscate it a
bit to cover your secrets. WHat is the database, is it a service or a
Windows 7 Console application ? There might apply the (Access denied
part) to it and you are providing the wrong credentials. You can track
the unsuccessfull connections in profiler and can see which user is
currently accessing the database, but perhaps the post of the
connectionstring might light up the situation.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--

Access to several SQL Servers implementing trusted connection

Being an IT consultant, I have to visit a number of customers having applications running on SQL Servers that implement trusted connection security model. In addition, all of these companies have their own DOMAIN settings, and security implementation.

How can I access to databases without changing the DOMAIN settings of MY notebook? I do not want them to switch to SQL security model, and I do not want to create any security hole in those companies.

What shall I want from the system administrator so that I can login into their domain without changing my domain settings?

Thanks,

EIO.

Hi,

you can store user network crednetials for your account on the notebook which will be used if connecting to a remote server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

May I ask: How?

I understand that:

1. I have to ask all the administrators in those separate companies to create a user account for me, in their local domains, that has access to the SQL Server databases. That is easy.

2. I have to register those separate domain accounts in my notebook (how?)

3. After I connect to the company network and login to my notebook with my own username, those registrations will make me be acquainted with the company network.

Is this correct? And how can I complete Step 2?

Thanks in advance,

EIO.

|||

Yes you can ask those companies to create a standard account with your name and you can access the SQL Server using Enterprise manager by regsitering those instances. I have done this previously without any problem, where the account name (username) should match for authentication.

On Step2 even though it says cannot connect to server you can still register on your EM lists.

Access to different databases in different domains

Hi everybody,

there are several SQL-Server 2000 databases within a company located
on different servers in different domains. On every database you can
find the same table X.

I want to merge these tables X (UNION query) and print the result with
a Crystal Report.

Unfortunately I only have little knowledge on security, domains,
distributed applications.

Thanks for help.Have you checked Linked Servers?

--

Jack Vamvas
___________________________________
Need an IT job? http://www.itjobfeed.com
"Josef Meierhofer" <jmeierhofer@.gmx.comwrote in message
news:hga623l6c1v4fbghk7rbmp7omuaplp6tn0@.4ax.com...

Quote:

Originally Posted by

Hi everybody,
>
there are several SQL-Server 2000 databases within a company located
on different servers in different domains. On every database you can
find the same table X.
>
I want to merge these tables X (UNION query) and print the result with
a Crystal Report.
>
Unfortunately I only have little knowledge on security, domains,
distributed applications.
>
Thanks for help.
>
>

|||Josef Meierhofer (jmeierhofer@.gmx.com) writes:

Quote:

Originally Posted by

there are several SQL-Server 2000 databases within a company located
on different servers in different domains. On every database you can
find the same table X.
>
I want to merge these tables X (UNION query) and print the result with
a Crystal Report.
>
Unfortunately I only have little knowledge on security, domains,
distributed applications.

The query as such is not tricky:

SELECT ...
FROM SERVER1.db.dbo.tbl
WHERE ...
UNION ALL
SELECT ...
FROM SERVER2.db.dbo.tbl
WHERE ...
UNION ALL
...

The problem may be with setting up the linked servers. You use
sp_addlinkedserver and sp_addlinkedsrvlogin. Supposedly there is a DBA
around with whom you can discuss how to do this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Access Denied when setting up a Subscriber for replication

i verified that the server settings and authentication is
the same for both servers/
i am trying to configure a windows 2003 server running sql
server 2000 to be a subscriber to a publisher runnign
win2k and sql 2000.
when I try to register the server, i get access denied.
any thoughts?
thanks
Jeff,
can you test network connectivity using PING and NET VIEW
(http://www.microsoft.com/resources/d...2003/standard/
proddocs/en-us/Default.asp?url=/resources/documentation/WindowsServ/2003/sta
ndard/proddocs/en-us/sag_TCPIP_pro_PingConnect.asp).
Can you also use QA on hte subscriber and test this tool, using a SQL user
(assuming mixed mode authentication).
Can you check that the SQL Server Service is started on the publisher
(sounds obvious but it's better if I ask).
Regards,
Paul Ibison
|||Hi Paul,
i can connect to the server using QA and specifying the IP
of the server. I can also ping using host name and IP.
thanjks

>--Original Message--
>Jeff,
>can you test network connectivity using PING and NET VIEW
>
(http://www.microsoft.com/resources/d...tion/WindowsSe
rv/2003/standard/
>proddocs/en-us/Default.asp?
url=/resources/documentation/WindowsServ/2003/sta
>ndard/proddocs/en-us/sag_TCPIP_pro_PingConnect.asp).
>Can you also use QA on hte subscriber and test this tool,
using a SQL user
>(assuming mixed mode authentication).
>Can you check that the SQL Server Service is started on
the publisher
>(sounds obvious but it's better if I ask).
>Regards,
>Paul Ibison
>
>.
>
|||Jeff,
can you connect using QA and the host name with trusted / non-trusted
security (logging into windows using the same account as the sql server
agent).
If you can connect using trusted security and host name then check that the
Account is in the PAL for the publication.
Finally, I did read in a thread that DTC needs enabling, which it isn't by
default in Win 2003
(http://support.microsoft.com/default...5bLN%5d;817064). I
understand the need for DTC in immediate updating subscribers but haven't
testes whether this thread was correct or not. Anyway, if it's not enabled
and the above comments don't lead to the solution, please try this also.
HTH,
Paul Ibison
|||I'd check your Client Network Utility Settings and verify that they are
pointing to the correct 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:u1BBBRLaEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Jeff,
> can you connect using QA and the host name with trusted / non-trusted
> security (logging into windows using the same account as the sql server
> agent).
> If you can connect using trusted security and host name then check that
the
> Account is in the PAL for the publication.
> Finally, I did read in a thread that DTC needs enabling, which it isn't by
> default in Win 2003
> (http://support.microsoft.com/default...5bLN%5d;817064). I
> understand the need for DTC in immediate updating subscribers but haven't
> testes whether this thread was correct or not. Anyway, if it's not enabled
> and the above comments don't lead to the solution, please try this also.
> HTH,
> Paul Ibison
>
|||Hilary and Paul,
i have looked at the client network util. and verified
they are pointing to the right server.
The issue now is, I cannot even register the server from
the Publisher in the ACL list. This is getting weirder by
the minute. I can verify connectivity by ping with no
issues.
In the Client util, i configure the server name on the
alias and the IP and also specify the port the SQL server
is listening on at the Subscriber. So at this point, i am
not sure what the issue is...
thanks for your help

>--Original Message--
>I'd check your Client Network Utility Settings and verify
that they are[vbcol=seagreen]
>pointing to the correct 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:u1BBBRLaEHA.4092@.TK2MSFTNGP11.phx.gbl...
trusted / non-trusted[vbcol=seagreen]
as the sql server[vbcol=seagreen]
then check that[vbcol=seagreen]
>the
enabling, which it isn't by[vbcol=seagreen]
5bLN%5d;817064). I[vbcol=seagreen]
subscribers but haven't[vbcol=seagreen]
if it's not enabled[vbcol=seagreen]
please try this also.
>
>.
>