connection problem

One customer reported they were having problem with their Access database
that is
linked to our SQL Server. They had to relink the tables every 30 minutes
when working on the db. Where should I look on the SQL server to determine
if this is SQL server related or network related?
Thanks,
Bing"bing" <bing@.discussions.microsoft.com> wrote in message
news:6C9ADFB9-51B5-451C-A921-540624570C1F@.microsoft.com...
> One customer reported they were having problem with their Access database
> that is
> linked to our SQL Server. They had to relink the tables every 30 minutes
> when working on the db. Where should I look on the SQL server to
determine
> if this is SQL server related or network related?
What problem were they having? And, what versions of products are being
used? More information is better in this case ;)
Steve|||This is SQL server 2000 with SP3a installed on Windows 2003.
I just checked the Application logs in the Even Viewer on the SQL server and
found the following errors repeated every 30 minutes that seems to match the
problem symtom the customer is having. I've searched on the web and looks
like the problem has something to do with group policy.
Could these errors be the cause of the problem? Are they that bad that can
break connection between the client and the server?
==========Type Date Time Source Category Event User Computer
Error 8/24/2004 3:54:12 PM Userenv None 1085 SYSTEM pc100
Error 8/24/2004 3:54:12 PM Userenv None 1091 SYSTEM pc100
==========
Would anybody give any insight on this? Thanks much in advance.
Bing
"Steve Thompson" wrote:
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:6C9ADFB9-51B5-451C-A921-540624570C1F@.microsoft.com...
> > One customer reported they were having problem with their Access database
> > that is
> > linked to our SQL Server. They had to relink the tables every 30 minutes
> > when working on the db. Where should I look on the SQL server to
> determine
> > if this is SQL server related or network related?
> What problem were they having? And, what versions of products are being
> used? More information is better in this case ;)
> Steve
>
>|||According to the following article those errors occur if you are enforcing
IPSec. And, there is a hotfix for this issue -- not sure if it's related to
your linked tables issue but it's a start.
http://support.microsoft.com/default.aspx?scid=kb;en-us;823608&Product=winsvr2003
Steve
"bing" <bing@.discussions.microsoft.com> wrote in message
news:F749D976-A9F3-4E64-913C-92F38E020F5C@.microsoft.com...
> This is SQL server 2000 with SP3a installed on Windows 2003.
> I just checked the Application logs in the Even Viewer on the SQL server
and
> found the following errors repeated every 30 minutes that seems to match
the
> problem symtom the customer is having. I've searched on the web and
looks
> like the problem has something to do with group policy.
> Could these errors be the cause of the problem? Are they that bad that
can
> break connection between the client and the server?
> ==========> Type Date Time Source Category Event User Computer
> Error 8/24/2004 3:54:12 PM Userenv None 1085 SYSTEM pc100
> Error 8/24/2004 3:54:12 PM Userenv None 1091 SYSTEM pc100
> ==========> Would anybody give any insight on this? Thanks much in advance.
> Bing
> "Steve Thompson" wrote:
> > "bing" <bing@.discussions.microsoft.com> wrote in message
> > news:6C9ADFB9-51B5-451C-A921-540624570C1F@.microsoft.com...
> > > One customer reported they were having problem with their Access
database
> > > that is
> > > linked to our SQL Server. They had to relink the tables every 30
minutes
> > > when working on the db. Where should I look on the SQL server to
> > determine
> > > if this is SQL server related or network related?
> >
> > What problem were they having? And, what versions of products are being
> > used? More information is better in this case ;)
> >
> > Steve
> >
> >
> >|||I thought hotfixes for specific issues were free of charge? If they are
telling you that you need to pay for this -- get back to me and we'll figure
out a way to get it to you.
Steve
"bing" <bing@.discussions.microsoft.com> wrote in message
news:1CC2B1D1-7950-4E8D-9CB9-B1B44871F8FE@.microsoft.com...
> Ok, I'm stuck on get Microsoft Product Support Services. After taking
some
> time finding the product ID of the SQL server, MS said this ID cannot get
> no-charge support. Does that mean we have to pay to get this hotfix?
> Bing
> "Steve Thompson" wrote:
> > According to the following article those errors occur if you are
enforcing
> > IPSec. And, there is a hotfix for this issue -- not sure if it's related
to
> > your linked tables issue but it's a start.
> >
http://support.microsoft.com/default.aspx?scid=kb;en-us;823608&Product=winsvr2003
> >
> > Steve
> > "bing" <bing@.discussions.microsoft.com> wrote in message
> > news:F749D976-A9F3-4E64-913C-92F38E020F5C@.microsoft.com...
> > > This is SQL server 2000 with SP3a installed on Windows 2003.
> > >
> > > I just checked the Application logs in the Even Viewer on the SQL
server
> > and
> > > found the following errors repeated every 30 minutes that seems to
match
> > the
> > > problem symtom the customer is having. I've searched on the web
and
> > looks
> > > like the problem has something to do with group policy.
> > >
> > > Could these errors be the cause of the problem? Are they that bad
that
> > can
> > > break connection between the client and the server?
> > >
> > > ==========> > > Type Date Time Source Category Event User Computer
> > >
> > > Error 8/24/2004 3:54:12 PM Userenv None 1085 SYSTEM pc100
> > > Error 8/24/2004 3:54:12 PM Userenv None 1091 SYSTEM pc100
> > > ==========> > >
> > > Would anybody give any insight on this? Thanks much in advance.
> > >
> > > Bing
> > >
> > > "Steve Thompson" wrote:
> > >
> > > > "bing" <bing@.discussions.microsoft.com> wrote in message
> > > > news:6C9ADFB9-51B5-451C-A921-540624570C1F@.microsoft.com...
> > > > > One customer reported they were having problem with their Access
> > database
> > > > > that is
> > > > > linked to our SQL Server. They had to relink the tables every 30
> > minutes
> > > > > when working on the db. Where should I look on the SQL server to
> > > > determine
> > > > > if this is SQL server related or network related?
> > > >
> > > > What problem were they having? And, what versions of products are
being
> > > > used? More information is better in this case ;)
> > > >
> > > > Steve
> > > >
> > > >
> > > >
> >
> >
> >|||My suggestion would be call into the queue, request the specific hotfix
noted in 823608, tell the support person that you need that specific hotfix
to solve your issue.
There is a note on that page:
"Note In special cases, charges that are ordinarily incurred for support
calls may be canceled if a Microsoft Support Professional determines that a
specific update will resolve your problem. "
As long as you do not bring up any other issue as part of that support call
it appears you would qualify.
Steve
"bing" <bing@.discussions.microsoft.com> wrote in message
news:28E58896-12A6-4A8E-ABED-08BD1FCC7856@.microsoft.com...
> Thanks much, Steve, for getting back to this track. It's not any person
in
> MS who told me that. Following the link provided in the KB article you
> pointed out, I got on the support site. Next entered my personal passport
> email and password, selected product SQL server, selected No-Charge
Support,
> input the Product ID number for our SQL Server. Then I got the following
> message back:
> Products purchased under a Select License Agreement or Software Assurance
> Home Use Program are not eligible for no-charge support. You may go back
and
> choose another support option or enter another Product ID number.
> I did not know how I should proceed from there. Any advices would be
> greatly appreciated. A couple of more questions. The KB article with the
> title 'Event ID 1091 and Event ID 1085 Appear in the Application Event
Log'
> says:
> " if you are not severely affected by this problem, Microsoft recommends
> that you wait for the next Windows Server 2003 service pack that contains
> this fix."
> Is there any more detailed information that tells what bad impacts these
> events could cause? I can not fully get what 'severely affected' really
> means. Any time table for when the next Windows Server 2003 service pack
> that contains this fix will be released?
> Bing
> "Steve Thompson" wrote:
> > I thought hotfixes for specific issues were free of charge? If they are
> > telling you that you need to pay for this -- get back to me and we'll
figure
> > out a way to get it to you.
> >
> > Steve
> >
> > "bing" <bing@.discussions.microsoft.com> wrote in message
> > news:1CC2B1D1-7950-4E8D-9CB9-B1B44871F8FE@.microsoft.com...
> > > Ok, I'm stuck on get Microsoft Product Support Services. After taking
> > some
> > > time finding the product ID of the SQL server, MS said this ID cannot
get
> > > no-charge support. Does that mean we have to pay to get this hotfix?
> > >
> > > Bing
> > >
> > > "Steve Thompson" wrote:
> > >
> > > > According to the following article those errors occur if you are
> > enforcing
> > > > IPSec. And, there is a hotfix for this issue -- not sure if it's
related
> > to
> > > > your linked tables issue but it's a start.
> > > >
> >
http://support.microsoft.com/default.aspx?scid=kb;en-us;823608&Product=winsvr2003
> > > >
> > > > Steve
> > > > "bing" <bing@.discussions.microsoft.com> wrote in message
> > > > news:F749D976-A9F3-4E64-913C-92F38E020F5C@.microsoft.com...
> > > > > This is SQL server 2000 with SP3a installed on Windows 2003.
> > > > >
> > > > > I just checked the Application logs in the Even Viewer on the SQL
> > server
> > > > and
> > > > > found the following errors repeated every 30 minutes that seems to
> > match
> > > > the
> > > > > problem symtom the customer is having. I've searched on the
web
> > and
> > > > looks
> > > > > like the problem has something to do with group policy.
> > > > >
> > > > > Could these errors be the cause of the problem? Are they that bad
> > that
> > > > can
> > > > > break connection between the client and the server?
> > > > >
> > > > > ==========> > > > > Type Date Time Source Category Event User
Computer
> > > > >
> > > > > Error 8/24/2004 3:54:12 PM Userenv None 1085 SYSTEM pc100
> > > > > Error 8/24/2004 3:54:12 PM Userenv None 1091 SYSTEM pc100
> > > > > ==========> > > > >
> > > > > Would anybody give any insight on this? Thanks much in advance.
> > > > >
> > > > > Bing
> > > > >
> > > > > "Steve Thompson" wrote:
> > > > >
> > > > > > "bing" <bing@.discussions.microsoft.com> wrote in message
> > > > > > news:6C9ADFB9-51B5-451C-A921-540624570C1F@.microsoft.com...
> > > > > > > One customer reported they were having problem with their
Access
> > > > database
> > > > > > > that is
> > > > > > > linked to our SQL Server. They had to relink the tables every
30
> > > > minutes
> > > > > > > when working on the db. Where should I look on the SQL server
to
> > > > > > determine
> > > > > > > if this is SQL server related or network related?
> > > > > >
> > > > > > What problem were they having? And, what versions of products
are
> > being
> > > > > > used? More information is better in this case ;)
> > > > > >
> > > > > > Steve
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Bing,
Sorry for the delay in responding -- comments in-line.
Steve
"bing" <bing@.discussions.microsoft.com> wrote in message
news:28E58896-12A6-4A8E-ABED-08BD1FCC7856@.microsoft.com...
> Thanks much, Steve, for getting back to this track. It's not any person
in
> MS who told me that. Following the link provided in the KB article you
> pointed out, I got on the support site. Next entered my personal passport
> email and password, selected product SQL server, selected No-Charge
Support,
> input the Product ID number for our SQL Server. Then I got the following
> message back:
> Products purchased under a Select License Agreement or Software Assurance
> Home Use Program are not eligible for no-charge support. You may go back
and
> choose another support option or enter another Product ID number.
Please respond with a valid email address and I'll see if I can get you a
copy (no promises and no other requests!).
> I did not know how I should proceed from there. Any advices would be
> greatly appreciated. A couple of more questions. The KB article with the
> title 'Event ID 1091 and Event ID 1085 Appear in the Application Event
Log'
> says:
> " if you are not severely affected by this problem, Microsoft recommends
> that you wait for the next Windows Server 2003 service pack that contains
> this fix."
> Is there any more detailed information that tells what bad impacts these
> events could cause?
That disclaimer is actually Microsoft normal -- they are stating that a
hotifx has not had the extensive regression testing that a service pack has
experienced. While it usually fixes the specific problem as advertised, they
cannot know of any and all colateral side effects that may be introduced. My
methodology has been, test in a lab, if it works, roll it out to QA then
production..
> I can not fully get what 'severely affected' really
> means.
Again, disclaimer time -- what is your threshold of pain ;) if you cannot
live without this hotfix then apply it.
>Any time table for when the next Windows Server 2003 service pack
> that contains this fix will be released?
Sources indicate that Windows Server 2003 SP1 will be available early next
year. Release dates are subject to change, etc, etc, etc.
Steve
> Bing
> "Steve Thompson" wrote:
> > I thought hotfixes for specific issues were free of charge? If they are
> > telling you that you need to pay for this -- get back to me and we'll
figure
> > out a way to get it to you.
> >
> > Steve
> >
> > "bing" <bing@.discussions.microsoft.com> wrote in message
> > news:1CC2B1D1-7950-4E8D-9CB9-B1B44871F8FE@.microsoft.com...
> > > Ok, I'm stuck on get Microsoft Product Support Services. After taking
> > some
> > > time finding the product ID of the SQL server, MS said this ID cannot
get
> > > no-charge support. Does that mean we have to pay to get this hotfix?
> > >
> > > Bing
> > >
> > > "Steve Thompson" wrote:
> > >
> > > > According to the following article those errors occur if you are
> > enforcing
> > > > IPSec. And, there is a hotfix for this issue -- not sure if it's
related
> > to
> > > > your linked tables issue but it's a start.
> > > >
> >
http://support.microsoft.com/default.aspx?scid=kb;en-us;823608&Product=winsvr2003
> > > >
> > > > Steve
> > > > "bing" <bing@.discussions.microsoft.com> wrote in message
> > > > news:F749D976-A9F3-4E64-913C-92F38E020F5C@.microsoft.com...
> > > > > This is SQL server 2000 with SP3a installed on Windows 2003.
> > > > >
> > > > > I just checked the Application logs in the Even Viewer on the SQL
> > server
> > > > and
> > > > > found the following errors repeated every 30 minutes that seems to
> > match
> > > > the
> > > > > problem symtom the customer is having. I've searched on the
web
> > and
> > > > looks
> > > > > like the problem has something to do with group policy.
> > > > >
> > > > > Could these errors be the cause of the problem? Are they that bad
> > that
> > > > can
> > > > > break connection between the client and the server?
> > > > >
> > > > > ==========> > > > > Type Date Time Source Category Event User
Computer
> > > > >
> > > > > Error 8/24/2004 3:54:12 PM Userenv None 1085 SYSTEM pc100
> > > > > Error 8/24/2004 3:54:12 PM Userenv None 1091 SYSTEM pc100
> > > > > ==========> > > > >
> > > > > Would anybody give any insight on this? Thanks much in advance.
> > > > >
> > > > > Bing
> > > > >
> > > > > "Steve Thompson" wrote:
> > > > >
> > > > > > "bing" <bing@.discussions.microsoft.com> wrote in message
> > > > > > news:6C9ADFB9-51B5-451C-A921-540624570C1F@.microsoft.com...
> > > > > > > One customer reported they were having problem with their
Access
> > > > database
> > > > > > > that is
> > > > > > > linked to our SQL Server. They had to relink the tables every
30
> > > > minutes
> > > > > > > when working on the db. Where should I look on the SQL server
to
> > > > > > determine
> > > > > > > if this is SQL server related or network related?
> > > > > >
> > > > > > What problem were they having? And, what versions of products
are
> > being
> > > > > > used? More information is better in this case ;)
> > > > > >
> > > > > > Steve
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Bing,
Check your mail.
Steve
"bing" <bing@.discussions.microsoft.com> wrote in message
news:C9D33B0B-09F9-4F4E-BB1C-160AA055A735@.microsoft.com...
> Thanks Steve for all the help. The valid email address is
bdu@.iastate.edu.
> Bing
> "Steve Thompson" wrote:
> > Hi Bing,
> >
> > Sorry for the delay in responding -- comments in-line.
> >
> > Steve
> >
> > "bing" <bing@.discussions.microsoft.com> wrote in message
> > news:28E58896-12A6-4A8E-ABED-08BD1FCC7856@.microsoft.com...
> > > Thanks much, Steve, for getting back to this track. It's not any
person
> > in
> > > MS who told me that. Following the link provided in the KB article
you
> > > pointed out, I got on the support site. Next entered my personal
passport
> > > email and password, selected product SQL server, selected No-Charge
> > Support,
> > > input the Product ID number for our SQL Server. Then I got the
following
> > > message back:
> > >
> > > Products purchased under a Select License Agreement or Software
Assurance
> > > Home Use Program are not eligible for no-charge support. You may go
back
> > and
> > > choose another support option or enter another Product ID number.
> >
> > Please respond with a valid email address and I'll see if I can get you
a
> > copy (no promises and no other requests!).
> >
> > > I did not know how I should proceed from there. Any advices would be
> > > greatly appreciated. A couple of more questions. The KB article with
the
> > > title 'Event ID 1091 and Event ID 1085 Appear in the Application Event
> > Log'
> > > says:
> > >
> > > " if you are not severely affected by this problem, Microsoft
recommends
> > > that you wait for the next Windows Server 2003 service pack that
contains
> > > this fix."
> > >
> > > Is there any more detailed information that tells what bad impacts
these
> > > events could cause?
> >
> > That disclaimer is actually Microsoft normal -- they are stating that a
> > hotifx has not had the extensive regression testing that a service pack
has
> > experienced. While it usually fixes the specific problem as advertised,
they
> > cannot know of any and all colateral side effects that may be
introduced. My
> > methodology has been, test in a lab, if it works, roll it out to QA then
> > production..
> >
> > > I can not fully get what 'severely affected' really
> > > means.
> >
> > Again, disclaimer time -- what is your threshold of pain ;) if you
cannot
> > live without this hotfix then apply it.
> >
> > >Any time table for when the next Windows Server 2003 service pack
> > > that contains this fix will be released?
> >
> > Sources indicate that Windows Server 2003 SP1 will be available early
next
> > year. Release dates are subject to change, etc, etc, etc.
> >
> > Steve
> >
> > > Bing
> > >
> > > "Steve Thompson" wrote:
> > >
> > > > I thought hotfixes for specific issues were free of charge? If they
are
> > > > telling you that you need to pay for this -- get back to me and
we'll
> > figure
> > > > out a way to get it to you.
> > > >
> > > > Steve
> > > >
> > > > "bing" <bing@.discussions.microsoft.com> wrote in message
> > > > news:1CC2B1D1-7950-4E8D-9CB9-B1B44871F8FE@.microsoft.com...
> > > > > Ok, I'm stuck on get Microsoft Product Support Services. After
taking
> > > > some
> > > > > time finding the product ID of the SQL server, MS said this ID
cannot
> > get
> > > > > no-charge support. Does that mean we have to pay to get this
hotfix?
> > > > >
> > > > > Bing
> > > > >
> > > > > "Steve Thompson" wrote:
> > > > >
> > > > > > According to the following article those errors occur if you are
> > > > enforcing
> > > > > > IPSec. And, there is a hotfix for this issue -- not sure if it's
> > related
> > > > to
> > > > > > your linked tables issue but it's a start.
> > > > > >
> > > >
> >
http://support.microsoft.com/default.aspx?scid=kb;en-us;823608&Product=winsvr2003
> > > > > >
> > > > > > Steve
> > > > > > "bing" <bing@.discussions.microsoft.com> wrote in message
> > > > > > news:F749D976-A9F3-4E64-913C-92F38E020F5C@.microsoft.com...
> > > > > > > This is SQL server 2000 with SP3a installed on Windows 2003.
> > > > > > >
> > > > > > > I just checked the Application logs in the Even Viewer on the
SQL
> > > > server
> > > > > > and
> > > > > > > found the following errors repeated every 30 minutes that
seems to
> > > > match
> > > > > > the
> > > > > > > problem symtom the customer is having. I've searched on
the
> > web
> > > > and
> > > > > > looks
> > > > > > > like the problem has something to do with group policy.
> > > > > > >
> > > > > > > Could these errors be the cause of the problem? Are they that
bad
> > > > that
> > > > > > can
> > > > > > > break connection between the client and the server?
> > > > > > >
> > > > > > > ==========> > > > > > > Type Date Time Source Category Event User
> > Computer
> > > > > > >
> > > > > > > Error 8/24/2004 3:54:12 PM Userenv None 1085 SYSTEM
pc100
> > > > > > > Error 8/24/2004 3:54:12 PM Userenv None 1091 SYSTEM
pc100
> > > > > > > ==========> > > > > > >
> > > > > > > Would anybody give any insight on this? Thanks much in
advance.
> > > > > > >
> > > > > > > Bing
> > > > > > >
> > > > > > > "Steve Thompson" wrote:
> > > > > > >
> > > > > > > > "bing" <bing@.discussions.microsoft.com> wrote in message
> > > > > > > > news:6C9ADFB9-51B5-451C-A921-540624570C1F@.microsoft.com...
> > > > > > > > > One customer reported they were having problem with their
> > Access
> > > > > > database
> > > > > > > > > that is
> > > > > > > > > linked to our SQL Server. They had to relink the tables
every
> > 30
> > > > > > minutes
> > > > > > > > > when working on the db. Where should I look on the SQL
server
> > to
> > > > > > > > determine
> > > > > > > > > if this is SQL server related or network related?
> > > > > > > >
> > > > > > > > What problem were they having? And, what versions of
products
> > are
> > > > being
> > > > > > > > used? More information is better in this case ;)
> > > > > > > >
> > > > > > > > Steve
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

connection from virtual ip address to firewall?

Hi,
We try to connect from a same named instance to a linked sql server in the
DMZ. The firewall has been set up to allow connections from the virtual ip
address of the instance to pass through and the linked server has been set
up correctly to connect through TCP/IP and the correct port. However, we
cannot connect and we see on the firewall that the requests are coming from
the active cluster node, not from the virtual ip address (even if we open a
Remote Desktop connection to the virtual server name). How can we solve
this?
Hans
This is how clustering works. Make sure you have the Node's IP defined for
access through the firewall.
Cheers,
Rod
"HVE" <eylenh@.hotmail.com> wrote in message
news:1e3yhnhwapvac.q9iklq79zipt$.dlg@.40tude.net...
> Hi,
> We try to connect from a same named instance to a linked sql server in the
> DMZ. The firewall has been set up to allow connections from the virtual ip
> address of the instance to pass through and the linked server has been set
> up correctly to connect through TCP/IP and the correct port. However, we
> cannot connect and we see on the firewall that the requests are coming
from
> the active cluster node, not from the virtual ip address (even if we open
a
> Remote Desktop connection to the virtual server name). How can we solve
> this?
> Hans

Connection from MS Access MDB

This is a cross-post (I originally posted in the ODBC forum).
I have an Access MDB with linked tables to a database that is running in 200
5
SQLExpress.
We created a new instance of SQLExpress and loaded the database from a
backup. We did a full restore of the d/b onto the new instance.
The new SQLExpress instance is using dynamic ports versus the static
(standard) port of 1433 that the old instance of SQLExpress was set to.
The MDB is used for some reporting. The linking of tables and production
of the reports works if run by a user with domain level administrator
permissions.
In the old instance of SQLExpress, a regular domain user would fail and the
pop-up window would appear allowing a change of credentials to produce the
report. In the new instance, this does not appear; only messages of an
exception occurring or if I run the report from the MDB Report section,
versus the menu that was built, I get an ODBC error, generic 3146 message
about being on a network.
I have used sql tracing from the ODBC Data Sources and I see (for both
regular and admin users) the attempted sql connections with the "admin"
account from the MDB and the domain user id; both fail; both are recorded in
the server Event Log.
In the case of a domain admin user running the reports, the log continues
and shows successful access to the database. In looking at the server Event
Log, the success connection is shown as a 'trusted' connection.
Is there a setting within SQLExpress for the server or database to cause the
pop up window to appear and allow the changing of credentials? I have tried
to create an account within the MDB to match an account within the database
and the server but I can not get the MDB to use that account; it always
defaults to the 'admin' account.
Thank you.Which ODBC forum? There are many of them.
It's not clear from your description if you want the credential window to
appear each time or not or if you want to use a domain (or windows or
"trusted") or a standard (or sql-server) user account.
If you are using a standard sql-server account, make sure that the
SQL-Server is setup for mixed authentification (Windows + SQL-Server
account) because only windows accounts are allowed by default. For the
standard accounts themselves, if you are trying to use accounts that were
created before the restoration, make sure that they are correctly mapped to
their SID by using the sp_change_users_login procedure (or better yet:
delete and recreate them). See
http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
If you want to use integrated security, make sure that the accounts that you
want to use are mapped as logins on the SQL-Server: the fact that an account
can log on a windows server doesn't mean that it can log on the sql-server
itself.
If you still have problem, then delete all links and recreate them using
either a standard sql-server account (account + password) or a windows
account (ie., integrated security or "trusted" account). If you want the
mdb file to retain the password, then check the option "Save Password" when
(re-)creating the links.
If these links are created programmatically (using vba code), then don't
forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
keep the password. Of course, you don't have to use this attribute for
windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html .
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
> This is a cross-post (I originally posted in the ODBC forum).
> I have an Access MDB with linked tables to a database that is running in
> 2005
> SQLExpress.
> We created a new instance of SQLExpress and loaded the database from a
> backup. We did a full restore of the d/b onto the new instance.
> The new SQLExpress instance is using dynamic ports versus the static
> (standard) port of 1433 that the old instance of SQLExpress was set to.
> The MDB is used for some reporting. The linking of tables and production
> of the reports works if run by a user with domain level administrator
> permissions.
> In the old instance of SQLExpress, a regular domain user would fail and
> the
> pop-up window would appear allowing a change of credentials to produce the
> report. In the new instance, this does not appear; only messages of an
> exception occurring or if I run the report from the MDB Report section,
> versus the menu that was built, I get an ODBC error, generic 3146 message
> about being on a network.
> I have used sql tracing from the ODBC Data Sources and I see (for both
> regular and admin users) the attempted sql connections with the "admin"
> account from the MDB and the domain user id; both fail; both are recorded
> in
> the server Event Log.
> In the case of a domain admin user running the reports, the log continues
> and shows successful access to the database. In looking at the server
> Event
> Log, the success connection is shown as a 'trusted' connection.
> Is there a setting within SQLExpress for the server or database to cause
> the
> pop up window to appear and allow the changing of credentials? I have
> tried
> to create an account within the MDB to match an account within the
> database
> and the server but I can not get the MDB to use that account; it always
> defaults to the 'admin' account.
> Thank you.
>|||Sylvain:
Thank you for this information; it is putting me in the correct direction.
Some comments and more questions:
1. I posted in the SQL Server Open Database Connectivity (ODBC) forum first
but saw similar answer of yours here; that is why I reposted.
2. I am set up for mixed authentication and I would prefer to have the
credential window appear each time so that the generic, read-only, account i
s
used to generate the report. If I could set the MDB to use the generic
account without the credential window, that would be good but that might
prevent administrative debugging or changing of the MDB without a relink of
tables.
3. I believe that I understand the sp_change_users_login procedure but I
have not done that yet. I did a REPORT and it shows 2 accounts are not
linked but there is another account that "must be" linked as it did not
appear in the report. I may still do the change on the account that appears
to be linked to ensure that it is linked. The accounts, the one not
reported, were created in the d/b and server AFTER the restore; so the chang
e
may be needed.
4. I am not sure how to get the MDB to retain the password as you
mentioned. I created a DSN for the linking of the MDB to the database. I
used the SA account for the linking process because any other account failed
.
Did I miss something here?
5. Unfortunately, we do not have in-house VBA expertise to use the
DSNlesslink that you mentioned; that makes me a little reluctant to go that
route.
F W Green
"Sylvain Lafontaine" wrote:

> Which ODBC forum? There are many of them.
> It's not clear from your description if you want the credential window to
> appear each time or not or if you want to use a domain (or windows or
> "trusted") or a standard (or sql-server) user account.
> If you are using a standard sql-server account, make sure that the
> SQL-Server is setup for mixed authentification (Windows + SQL-Server
> account) because only windows accounts are allowed by default. For the
> standard accounts themselves, if you are trying to use accounts that were
> created before the restoration, make sure that they are correctly mapped t
o
> their SID by using the sp_change_users_login procedure (or better yet:
> delete and recreate them). See
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
> If you want to use integrated security, make sure that the accounts that y
ou
> want to use are mapped as logins on the SQL-Server: the fact that an accou
nt
> can log on a windows server doesn't mean that it can log on the sql-server
> itself.
> If you still have problem, then delete all links and recreate them using
> either a standard sql-server account (account + password) or a windows
> account (ie., integrated security or "trusted" account). If you want the
> mdb file to retain the password, then check the option "Save Password" whe
n
> (re-)creating the links.
> If these links are created programmatically (using vba code), then don't
> forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
> keep the password. Of course, you don't have to use this attribute for
> windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html
.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
>
>|||First, sorry for the late response.
Second, I haven't used MDB with ODBC linked tables since many years, so I
forgot nearly everything about all these little naughty details; so you will
have to make your own little tests in order to see what works and what
don't.
The sp_change_users_login procedure is only for accounts that were created
before the restoration and only in the case when the master database has not
been restored or is from another installation/instance. Not usefull for new
accounts. Also, you see that the use of roles is a better idea than to
directly assign permission to an user account because with roles, it's
pretty easy and straightforward to recreate the old user accounts and
(re-)associate them with their respective roles. (BTW, I don't remember if
you have to use the sp_change_users_login procedure for these cases.)
For the point 4., some things can change when you are using a DSN instead of
a DSN-less connection but again, it's something that I've forgotten a long
time ago and you will have to make your own tests. I'm surprised however
that only the sa account is working for you. I suppose that you may have
forgot to associate these other accounts to their databases.
In all cases and excerpt maybe for the saving of the credentials - which I
don't remember the details -, both methods (DSN or DSN-less) should work as
well as each other.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...[vbcol=seagreen]
> Sylvain:
> Thank you for this information; it is putting me in the correct direction.
> Some comments and more questions:
> 1. I posted in the SQL Server Open Database Connectivity (ODBC) forum
> first
> but saw similar answer of yours here; that is why I reposted.
> 2. I am set up for mixed authentication and I would prefer to have the
> credential window appear each time so that the generic, read-only, account
> is
> used to generate the report. If I could set the MDB to use the generic
> account without the credential window, that would be good but that might
> prevent administrative debugging or changing of the MDB without a relink
> of
> tables.
> 3. I believe that I understand the sp_change_users_login procedure but I
> have not done that yet. I did a REPORT and it shows 2 accounts are not
> linked but there is another account that "must be" linked as it did not
> appear in the report. I may still do the change on the account that
> appears
> to be linked to ensure that it is linked. The accounts, the one not
> reported, were created in the d/b and server AFTER the restore; so the
> change
> may be needed.
> 4. I am not sure how to get the MDB to retain the password as you
> mentioned. I created a DSN for the linking of the MDB to the database. I
> used the SA account for the linking process because any other account
> failed.
> Did I miss something here?
> 5. Unfortunately, we do not have in-house VBA expertise to use the
> DSNlesslink that you mentioned; that makes me a little reluctant to go
> that
> route.
> F W Green
> "Sylvain Lafontaine" wrote:
>|||Sylvain:
Thank you very much for your assistance. I believe that I have solved the
issue mostly through trying many things; not because I really know much abou
t
SQL Server.
The SQL server and instance are new; therefore, there is a new master db. I
used SQL Mgmt Studio to restore the d/b from a backup from the old server.
Therefore, the issues of linking between the server and the d/b were real.
No matter what I did, I could not resolve the State 5 error which was an
invalid user. The server logs showed the MDB ADMIN attempted connection and
then my user account without domain preface attempted connection. Both
failed many times when the MDB report was being generated. If a non-Admin
user was running the report, it failed. If a domain Admin user (me) ran the
report, it worked.
In all of my searching, this link,
http://www.webservertalk.com/archiv...0-1710650.html, mentioned 2
things - a) SQLCMD and b) Builtin/Users.
I used the sqlcmd command on the Windows server where the new SQLExpress
instance was running and the logins were okay. Does not prove much but at
least the accounts and passwords to the server were correct.
I checked and in the new instance of SQLExpress, Builtin/Users did NOT have
'dbreader' access to my database. Builtin/Users did not exist in the old
instance but it had been upgraded to SQLExpress from MDSE. In the new
instance, I granted 'dbreader' access to Builtin/Users and now every person
in the firm can run the reports without issue and without the pop-up
authentication window. I am not too concerned about security as the MDB is
strictly a reporting tool and any tweaks will be done by my staff.
Thanks again.
F W Green
"Sylvain Lafontaine" wrote:

> First, sorry for the late response.
> Second, I haven't used MDB with ODBC linked tables since many years, so I
> forgot nearly everything about all these little naughty details; so you wi
ll
> have to make your own little tests in order to see what works and what
> don't.
> The sp_change_users_login procedure is only for accounts that were created
> before the restoration and only in the case when the master database has n
ot
> been restored or is from another installation/instance. Not usefull for n
ew
> accounts. Also, you see that the use of roles is a better idea than to
> directly assign permission to an user account because with roles, it's
> pretty easy and straightforward to recreate the old user accounts and
> (re-)associate them with their respective roles. (BTW, I don't remember i
f
> you have to use the sp_change_users_login procedure for these cases.)
> For the point 4., some things can change when you are using a DSN instead
of
> a DSN-less connection but again, it's something that I've forgotten a long
> time ago and you will have to make your own tests. I'm surprised however
> that only the sa account is working for you. I suppose that you may have
> forgot to associate these other accounts to their databases.
> In all cases and excerpt maybe for the saving of the credentials - which I
> don't remember the details -, both methods (DSN or DSN-less) should work a
s
> well as each other.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...
>
>

Connection from MS Access MDB

This is a cross-post (I originally posted in the ODBC forum).
I have an Access MDB with linked tables to a database that is running in 2005
SQLExpress.
We created a new instance of SQLExpress and loaded the database from a
backup. We did a full restore of the d/b onto the new instance.
The new SQLExpress instance is using dynamic ports versus the static
(standard) port of 1433 that the old instance of SQLExpress was set to.
The MDB is used for some reporting. The linking of tables and production
of the reports works if run by a user with domain level administrator
permissions.
In the old instance of SQLExpress, a regular domain user would fail and the
pop-up window would appear allowing a change of credentials to produce the
report. In the new instance, this does not appear; only messages of an
exception occurring or if I run the report from the MDB Report section,
versus the menu that was built, I get an ODBC error, generic 3146 message
about being on a network.
I have used sql tracing from the ODBC Data Sources and I see (for both
regular and admin users) the attempted sql connections with the "admin"
account from the MDB and the domain user id; both fail; both are recorded in
the server Event Log.
In the case of a domain admin user running the reports, the log continues
and shows successful access to the database. In looking at the server Event
Log, the success connection is shown as a 'trusted' connection.
Is there a setting within SQLExpress for the server or database to cause the
pop up window to appear and allow the changing of credentials? I have tried
to create an account within the MDB to match an account within the database
and the server but I can not get the MDB to use that account; it always
defaults to the 'admin' account.
Thank you.
Which ODBC forum? There are many of them.
It's not clear from your description if you want the credential window to
appear each time or not or if you want to use a domain (or windows or
"trusted") or a standard (or sql-server) user account.
If you are using a standard sql-server account, make sure that the
SQL-Server is setup for mixed authentification (Windows + SQL-Server
account) because only windows accounts are allowed by default. For the
standard accounts themselves, if you are trying to use accounts that were
created before the restoration, make sure that they are correctly mapped to
their SID by using the sp_change_users_login procedure (or better yet:
delete and recreate them). See
http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
If you want to use integrated security, make sure that the accounts that you
want to use are mapped as logins on the SQL-Server: the fact that an account
can log on a windows server doesn't mean that it can log on the sql-server
itself.
If you still have problem, then delete all links and recreate them using
either a standard sql-server account (account + password) or a windows
account (ie., integrated security or "trusted" account). If you want the
mdb file to retain the password, then check the option "Save Password" when
(re-)creating the links.
If these links are created programmatically (using vba code), then don't
forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
keep the password. Of course, you don't have to use this attribute for
windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html .
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
> This is a cross-post (I originally posted in the ODBC forum).
> I have an Access MDB with linked tables to a database that is running in
> 2005
> SQLExpress.
> We created a new instance of SQLExpress and loaded the database from a
> backup. We did a full restore of the d/b onto the new instance.
> The new SQLExpress instance is using dynamic ports versus the static
> (standard) port of 1433 that the old instance of SQLExpress was set to.
> The MDB is used for some reporting. The linking of tables and production
> of the reports works if run by a user with domain level administrator
> permissions.
> In the old instance of SQLExpress, a regular domain user would fail and
> the
> pop-up window would appear allowing a change of credentials to produce the
> report. In the new instance, this does not appear; only messages of an
> exception occurring or if I run the report from the MDB Report section,
> versus the menu that was built, I get an ODBC error, generic 3146 message
> about being on a network.
> I have used sql tracing from the ODBC Data Sources and I see (for both
> regular and admin users) the attempted sql connections with the "admin"
> account from the MDB and the domain user id; both fail; both are recorded
> in
> the server Event Log.
> In the case of a domain admin user running the reports, the log continues
> and shows successful access to the database. In looking at the server
> Event
> Log, the success connection is shown as a 'trusted' connection.
> Is there a setting within SQLExpress for the server or database to cause
> the
> pop up window to appear and allow the changing of credentials? I have
> tried
> to create an account within the MDB to match an account within the
> database
> and the server but I can not get the MDB to use that account; it always
> defaults to the 'admin' account.
> Thank you.
>
|||Sylvain:
Thank you for this information; it is putting me in the correct direction.
Some comments and more questions:
1. I posted in the SQL Server Open Database Connectivity (ODBC) forum first
but saw similar answer of yours here; that is why I reposted.
2. I am set up for mixed authentication and I would prefer to have the
credential window appear each time so that the generic, read-only, account is
used to generate the report. If I could set the MDB to use the generic
account without the credential window, that would be good but that might
prevent administrative debugging or changing of the MDB without a relink of
tables.
3. I believe that I understand the sp_change_users_login procedure but I
have not done that yet. I did a REPORT and it shows 2 accounts are not
linked but there is another account that "must be" linked as it did not
appear in the report. I may still do the change on the account that appears
to be linked to ensure that it is linked. The accounts, the one not
reported, were created in the d/b and server AFTER the restore; so the change
may be needed.
4. I am not sure how to get the MDB to retain the password as you
mentioned. I created a DSN for the linking of the MDB to the database. I
used the SA account for the linking process because any other account failed.
Did I miss something here?
5. Unfortunately, we do not have in-house VBA expertise to use the
DSNlesslink that you mentioned; that makes me a little reluctant to go that
route.
F W Green
"Sylvain Lafontaine" wrote:

> Which ODBC forum? There are many of them.
> It's not clear from your description if you want the credential window to
> appear each time or not or if you want to use a domain (or windows or
> "trusted") or a standard (or sql-server) user account.
> If you are using a standard sql-server account, make sure that the
> SQL-Server is setup for mixed authentification (Windows + SQL-Server
> account) because only windows accounts are allowed by default. For the
> standard accounts themselves, if you are trying to use accounts that were
> created before the restoration, make sure that they are correctly mapped to
> their SID by using the sp_change_users_login procedure (or better yet:
> delete and recreate them). See
> http://msdn2.microsoft.com/en-us/library/ms174378.aspx .
> If you want to use integrated security, make sure that the accounts that you
> want to use are mapped as logins on the SQL-Server: the fact that an account
> can log on a windows server doesn't mean that it can log on the sql-server
> itself.
> If you still have problem, then delete all links and recreate them using
> either a standard sql-server account (account + password) or a windows
> account (ie., integrated security or "trusted" account). If you want the
> mdb file to retain the password, then check the option "Save Password" when
> (re-)creating the links.
> If these links are created programmatically (using vba code), then don't
> forget to use the attributes DB_ATTACHSAVEPWD if you want the new links to
> keep the password. Of course, you don't have to use this attribute for
> windows accounts. See http://www.accessmvp.com/djsteele/DSNLessLinks.html .
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:F32D516E-285B-4F08-B115-8A27F8177350@.microsoft.com...
>
>
|||First, sorry for the late response.
Second, I haven't used MDB with ODBC linked tables since many years, so I
forgot nearly everything about all these little naughty details; so you will
have to make your own little tests in order to see what works and what
don't.
The sp_change_users_login procedure is only for accounts that were created
before the restoration and only in the case when the master database has not
been restored or is from another installation/instance. Not usefull for new
accounts. Also, you see that the use of roles is a better idea than to
directly assign permission to an user account because with roles, it's
pretty easy and straightforward to recreate the old user accounts and
(re-)associate them with their respective roles. (BTW, I don't remember if
you have to use the sp_change_users_login procedure for these cases.)
For the point 4., some things can change when you are using a DSN instead of
a DSN-less connection but again, it's something that I've forgotten a long
time ago and you will have to make your own tests. I'm surprised however
that only the sa account is working for you. I suppose that you may have
forgot to associate these other accounts to their databases.
In all cases and excerpt maybe for the saving of the credentials - which I
don't remember the details -, both methods (DSN or DSN-less) should work as
well as each other.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...[vbcol=seagreen]
> Sylvain:
> Thank you for this information; it is putting me in the correct direction.
> Some comments and more questions:
> 1. I posted in the SQL Server Open Database Connectivity (ODBC) forum
> first
> but saw similar answer of yours here; that is why I reposted.
> 2. I am set up for mixed authentication and I would prefer to have the
> credential window appear each time so that the generic, read-only, account
> is
> used to generate the report. If I could set the MDB to use the generic
> account without the credential window, that would be good but that might
> prevent administrative debugging or changing of the MDB without a relink
> of
> tables.
> 3. I believe that I understand the sp_change_users_login procedure but I
> have not done that yet. I did a REPORT and it shows 2 accounts are not
> linked but there is another account that "must be" linked as it did not
> appear in the report. I may still do the change on the account that
> appears
> to be linked to ensure that it is linked. The accounts, the one not
> reported, were created in the d/b and server AFTER the restore; so the
> change
> may be needed.
> 4. I am not sure how to get the MDB to retain the password as you
> mentioned. I created a DSN for the linking of the MDB to the database. I
> used the SA account for the linking process because any other account
> failed.
> Did I miss something here?
> 5. Unfortunately, we do not have in-house VBA expertise to use the
> DSNlesslink that you mentioned; that makes me a little reluctant to go
> that
> route.
> F W Green
> "Sylvain Lafontaine" wrote:
|||Sylvain:
Thank you very much for your assistance. I believe that I have solved the
issue mostly through trying many things; not because I really know much about
SQL Server.
The SQL server and instance are new; therefore, there is a new master db. I
used SQL Mgmt Studio to restore the d/b from a backup from the old server.
Therefore, the issues of linking between the server and the d/b were real.
No matter what I did, I could not resolve the State 5 error which was an
invalid user. The server logs showed the MDB ADMIN attempted connection and
then my user account without domain preface attempted connection. Both
failed many times when the MDB report was being generated. If a non-Admin
user was running the report, it failed. If a domain Admin user (me) ran the
report, it worked.
In all of my searching, this link,
http://www.webservertalk.com/archive132-2006-10-1710650.html, mentioned 2
things - a) SQLCMD and b) Builtin/Users.
I used the sqlcmd command on the Windows server where the new SQLExpress
instance was running and the logins were okay. Does not prove much but at
least the accounts and passwords to the server were correct.
I checked and in the new instance of SQLExpress, Builtin/Users did NOT have
'dbreader' access to my database. Builtin/Users did not exist in the old
instance but it had been upgraded to SQLExpress from MDSE. In the new
instance, I granted 'dbreader' access to Builtin/Users and now every person
in the firm can run the reports without issue and without the pop-up
authentication window. I am not too concerned about security as the MDB is
strictly a reporting tool and any tweaks will be done by my staff.
Thanks again.
F W Green
"Sylvain Lafontaine" wrote:

> First, sorry for the late response.
> Second, I haven't used MDB with ODBC linked tables since many years, so I
> forgot nearly everything about all these little naughty details; so you will
> have to make your own little tests in order to see what works and what
> don't.
> The sp_change_users_login procedure is only for accounts that were created
> before the restoration and only in the case when the master database has not
> been restored or is from another installation/instance. Not usefull for new
> accounts. Also, you see that the use of roles is a better idea than to
> directly assign permission to an user account because with roles, it's
> pretty easy and straightforward to recreate the old user accounts and
> (re-)associate them with their respective roles. (BTW, I don't remember if
> you have to use the sp_change_users_login procedure for these cases.)
> For the point 4., some things can change when you are using a DSN instead of
> a DSN-less connection but again, it's something that I've forgotten a long
> time ago and you will have to make your own tests. I'm surprised however
> that only the sa account is working for you. I suppose that you may have
> forgot to associate these other accounts to their databases.
> In all cases and excerpt maybe for the saving of the credentials - which I
> don't remember the details -, both methods (DSN or DSN-less) should work as
> well as each other.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "F W Green" <FWGreen@.discussions.microsoft.com> wrote in message
> news:9B799CE8-E732-4DF5-8D9C-023C72ABD890@.microsoft.com...
>
>

connection err

hi

i use sql 2005 and i create view from linked server

when i select from the view it's o.k

but in the app. i get thes messge :

42000 [Microsoft][SQL Native Client][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

how can i resolve this problam

thenk's

You could try doing:

set ANSI_NULLS on

go

set ANSI_WARNINGS on

go

<select from your view>

instead of just:

<select from your view>

hope that helps,

John

|||

hi

i do this

but no

when i run the proc. or select on the view it is o.k

i get the err when i use the app. (uniface)

it is look like the odbc

thenks

|||

When you execute the QUERY from the application, SET the ANSI settings appropriately, something like this:

SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; SELECT Col1, Col2, etc FROM MyTable WHERE {criteria}

Try the entire line above as the command string. (Or put it into a stored procedure and just call the stored procedure.)

|||

---

This is sometimes based on the problem that the views are not created with the right ANSI settings, so use

SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO

CREATE VIEW SOMEVIEW....

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

Connecting to SQL Server with an alias

We have a soon-to-be legacy Sql Server called X. We have several queries on
various other servers configured to talk to X thru a linked server that go
something like:
SELECT [columnName] FROM [X].[DBName].[dbo].[TableName]
X will now be hosted on a new machine called Y.
So my question is, is there a way of aliasing our new server called "Y" so
that it can be refered to as "X"?
TIA//
I believe that you can do this using sp_addlinkedserver. Check out Books Online
(sp_addlinkedserver), the second example in the table. This suggests that you can specify some other
name for the linked server than the network name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Newby" <Chris.Newby@.Rockcreekglobal.com> wrote in message
news:%23LXjZH6FGHA.1676@.TK2MSFTNGP09.phx.gbl...
> We have a soon-to-be legacy Sql Server called X. We have several queries on various other servers
> configured to talk to X thru a linked server that go something like:
> SELECT [columnName] FROM [X].[DBName].[dbo].[TableName]
> X will now be hosted on a new machine called Y.
> So my question is, is there a way of aliasing our new server called "Y" so that it can be refered
> to as "X"?
> TIA//
>
|||So you are using a linked server to achive the connection to the server
X. Defining a server alias within the network client tool should do the
trick.
HTH, jens Suessmeyer.

Connecting to SQL Server with an alias

We have a soon-to-be legacy Sql Server called X. We have several queries on
various other servers configured to talk to X thru a linked server that go
something like:
SELECT [columnName] FROM [X].[DBName].[dbo].[TableName]
X will now be hosted on a new machine called Y.
So my question is, is there a way of aliasing our new server called "Y" so
that it can be refered to as "X"?
TIA//I believe that you can do this using sp_addlinkedserver. Check out Books Online
(sp_addlinkedserver), the second example in the table. This suggests that you can specify some other
name for the linked server than the network name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Newby" <Chris.Newby@.Rockcreekglobal.com> wrote in message
news:%23LXjZH6FGHA.1676@.TK2MSFTNGP09.phx.gbl...
> We have a soon-to-be legacy Sql Server called X. We have several queries on various other servers
> configured to talk to X thru a linked server that go something like:
> SELECT [columnName] FROM [X].[DBName].[dbo].[TableName]
> X will now be hosted on a new machine called Y.
> So my question is, is there a way of aliasing our new server called "Y" so that it can be refered
> to as "X"?
> TIA//
>|||So you are using a linked server to achive the connection to the server
X. Defining a server alias within the network client tool should do the
trick.
HTH, jens Suessmeyer.

Connecting to SQL Server with an alias

We have a soon-to-be legacy Sql Server called X. We have several queries on
various other servers configured to talk to X thru a linked server that go
something like:
SELECT [columnName] FROM [X].[DBName].[dbo].[TableName]
X will now be hosted on a new machine called Y.
So my question is, is there a way of aliasing our new server called "Y" so
that it can be refered to as "X"?
TIA//I believe that you can do this using sp_addlinkedserver. Check out Books Onl
ine
(sp_addlinkedserver), the second example in the table. This suggests that yo
u can specify some other
name for the linked server than the network name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Newby" <Chris.Newby@.Rockcreekglobal.com> wrote in message
news:%23LXjZH6FGHA.1676@.TK2MSFTNGP09.phx.gbl...
> We have a soon-to-be legacy Sql Server called X. We have several queries o
n various other servers
> configured to talk to X thru a linked server that go something like:
> SELECT [columnName] FROM [X].[DBName].[dbo].[TableName
]
> X will now be hosted on a new machine called Y.
> So my question is, is there a way of aliasing our new server called "Y" so
that it can be refered
> to as "X"?
> TIA//
>|||So you are using a linked server to achive the connection to the server
X. Defining a server alias within the network client tool should do the
trick.
HTH, jens Suessmeyer.