Problems installing/accessing SQL server via web

Hello,
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to connect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald Wheeler
What error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I get the standard server not found or access denied error - both via ASP calls and the SQL Server Query analyzer. I don't think that it's an authentication problem because I've tried using both valid and invalid passwords, as well as trying to the connec
t to the Northwind database, and received the same error
Donald
|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 database on a machine that already has a set of active IIS directories for webmail. I think the firewall is ok but the connections are getting 'swallowed' by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler
sql

Problems installing/accessing SQL server via web

Hello,
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to c
onnect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald WheelerWhat error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I get the standard server not found or access denied error - both via ASP ca
lls and the SQL Server Query analyzer. I don't think that it's an authentica
tion problem because I've tried using both valid and invalid passwords, as w
ell as trying to the connec
t to the Northwind database, and received the same error
Donald|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 dat
abase on a machine that already has a set of active IIS directories for webm
ail. I think the firewall is ok but the connections are getting 'swallowed'
by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler

Problems installing SRS

I'm trying to install Reporting Services on a new XP Pro SP2 IIS 6
development box. The local database is SQL Server 2000 sp3.
At the end of the installation I get an error message indicating that
the 'Setup could not initialize the report server' and that I'll have
to manually initialize the Report Server.
After running rsactive I got the message indicating that the "Report
Server Web Service has not generated a public key".
Looking in the log files is less-than-helpful (hundreds of lines
indicating that some -unknown- file is missing):
===============================================================================
aspnet_wp!library!a4!12/01/2004-10:15:58:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details. --> System.IO.FileNotFoundException: The system cannot
find the file specified.
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo)
at RSManagedCrypto.RSCrypto.ExportPublicKey()
at Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
at Microsoft.ReportingServices.Library.Storage.get_Connection()
at Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
storedProcedureName)
at Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
key)
at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
name)
at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
name)
at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
name)
at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
source)
at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path, Boolean validate, Boolean convert, Boolean translate)
at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path)
at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
-- End of inner exception stack trace --
a
===============================================================================
When I navigate to the http://localhost/ReportServer I get the
following error message:
An internal error occurred on the report server. (again not very
helpful).
IIS is listening on port 80 (and there are no other apps on the port).
SO... I'm running out of things to try and patience. Is there anyone
out there who can help me get over this problem? I really need to
get this installed ASAP and there doesn't seem to be any documentation
anywhere that explains what is going wrong!!!
Any help appreciated.
GlennAfter much trail-and-error and finally a call to Microsoft I was able to
install the SRS and get everything working. I'm posting this in hopes that my
solution will help someone in a similar situation.
Ultimately the problem was solved by:
1) accessing the machine.config file
2) find the user name in the process model section (by default it's set
to 'machine')
3) change this to 'system' (temporarily)
4) using EnterpriseManager open the Keys table of the ReportServer
database (three are probably 2 keys)
5) delete the NON-ZERO key (don't delete the ZERO key)
6) browse to http:/localhost/ReportServer (should no longer produce any
errors!!!)
7) browse to http:/localhost/Reposrts
Hope this helps...
Glenn
"Glenn" wrote:
> I'm trying to install Reporting Services on a new XP Pro SP2 IIS 6
> development box. The local database is SQL Server 2000 sp3.
> At the end of the installation I get an error message indicating that
> the 'Setup could not initialize the report server' and that I'll have
> to manually initialize the Report Server.
> After running rsactive I got the message indicating that the "Report
> Server Web Service has not generated a public key".
> Looking in the log files is less-than-helpful (hundreds of lines
> indicating that some -unknown- file is missing):
> ===============================================================================> aspnet_wp!library!a4!12/01/2004-10:15:58:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details., ;
> Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details. --> System.IO.FileNotFoundException: The system cannot
> find the file specified.
> at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo)
> at RSManagedCrypto.RSCrypto.ExportPublicKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
> at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
> at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
> storedProcedureName)
> at Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
> key)
> at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
> name)
> at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
> name)
> at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
> name)
> at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
> at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
> source)
> at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path, Boolean validate, Boolean convert, Boolean translate)
> at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path)
> at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
> at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()
> -- End of inner exception stack trace --
> a
> ===============================================================================> When I navigate to the http://localhost/ReportServer I get the
> following error message:
> An internal error occurred on the report server. (again not very
> helpful).
> IIS is listening on port 80 (and there are no other apps on the port).
>
> SO... I'm running out of things to try and patience. Is there anyone
> out there who can help me get over this problem? I really need to
> get this installed ASAP and there doesn't seem to be any documentation
> anywhere that explains what is going wrong!!!
> Any help appreciated.
> Glenn
>

Problems installing SP4 on MSDE 2000 (SP3)

Hi there,

I have some trouble installing SP4 onto an existing MSDE 2000 (with SP3).
I run setup like described in the readme: "setup /upgradesp sqlrun" => Setup starts and I get: "Product already installed ... "
Im using the MSDE version of the SP4 setup, the MSSQL version Im running is 8.00.760
Ive also tried to use the UPGRADEUSER and UPGRADEPWD arguments, but with the same results.

Any ideas?

Thank you

I guess you attempted couple of times SP4 install.

You need to modify following registry key and rerun setup.

after running successfull you need to change it back to original values.

Registry key is:

Local Machine\software\Microsoft\MSSQLServer\MSSqlServer\CurrentVersion

change 8.00.194 to something else [8.00.888]

change 8.00.761 to something else [8.00.999]

Thanks!

Problems insalling SP3

Hi everyone,
I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
during that process an error ocurred:
"Error running script: sp3_serv_uni.sql(1)"
When I clicked on the ok button the setup shut off and I tried to
restart the service (I stopped it before begining installation) but i
couldn't, so i restarted the computer and everything seemed normal.
What should i do? should i try to reinstall SP3?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!I had this problem. I had to enable only named pipes in the enabled
protocols of the SQL server network utility and the SQL client network
utility and after the install enabled TCP/IP again. It may work with a
combination, but that is how I got around it (not my brilliance, I found it
with a google search somewhere).
<anonymous@.devdex.com> wrote in message
news:#wYJSMpdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
> during that process an error ocurred:
> "Error running script: sp3_serv_uni.sql(1)"
> When I clicked on the ok button the setup shut off and I tried to
> restart the service (I stopped it before begining installation) but i
> couldn't, so i restarted the computer and everything seemed normal.
> What should i do? should i try to reinstall SP3?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Problems insalling SP3

Hi everyone,
I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
during that process an error ocurred:
"Error running script: sp3_serv_uni.sql(1)"
When I clicked on the ok button the setup shut off and I tried to
restart the service (I stopped it before begining installation) but i
couldn't, so i restarted the computer and everything seemed normal.
What should i do? should i try to reinstall SP3?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I had this problem. I had to enable only named pipes in the enabled
protocols of the SQL server network utility and the SQL client network
utility and after the install enabled TCP/IP again. It may work with a
combination, but that is how I got around it (not my brilliance, I found it
with a google search somewhere).
<anonymous@.devdex.com> wrote in message
news:#wYJSMpdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
> during that process an error ocurred:
> "Error running script: sp3_serv_uni.sql(1)"
> When I clicked on the ok button the setup shut off and I tried to
> restart the service (I stopped it before begining installation) but i
> couldn't, so i restarted the computer and everything seemed normal.
> What should i do? should i try to reinstall SP3?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Problems insalling SP3

Hi everyone,
I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
during that process an error ocurred:
"Error running script: sp3_serv_uni.sql(1)"
When I clicked on the ok button the setup shut off and I tried to
restart the service (I stopped it before begining installation) but i
couldn't, so i restarted the computer and everything seemed normal.
What should i do? should i try to reinstall SP3?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I had this problem. I had to enable only named pipes in the enabled
protocols of the SQL server network utility and the SQL client network
utility and after the install enabled TCP/IP again. It may work with a
combination, but that is how I got around it (not my brilliance, I found it
with a google search somewhere).
<anonymous@.devdex.com> wrote in message
news:#wYJSMpdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> I was installing the SP3 to the RTM version of the SQL SERVER 2000 and
> during that process an error ocurred:
> "Error running script: sp3_serv_uni.sql(1)"
> When I clicked on the ok button the setup shut off and I tried to
> restart the service (I stopped it before begining installation) but i
> couldn't, so i restarted the computer and everything seemed normal.
> What should i do? should i try to reinstall SP3?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Problems after SP4 - 100%CPU time

We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of the
databases runs at 100% CPU when ever a query it made. Nothing else has
changed! Before SP4 the queries are quite small and took only 1-2 seconds to
run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
If I copy the database to another server with SP3 on it, it works fine. If I
then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds.
The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN with
RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
temp, msdb, etc And monitoring them there seems to be no disk problems,
queues are short, etc
Can anyone help? either hot fix, uninstall (don't have SP3 backup of master,
etc), sp changes.
Cheers
nigelDid you run update_statistics , reindex database.
Maybe recompile stored procedures!
Greetz
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Nigel" wrote:

> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel|||Thanks.
I've run these manually and as part of the daily maintenance plan. The
database also has Auto update and create statistics.
At present the part where it all goes mad does not use any SP, but is called
via a JDBC Java client directly.
Task Manager view can be seen here.
http://www.callacomp.co.uk/sql/orion%20cpu.jpg
Performance monitor SP3 (live)
http://www.callacomp.co.uk/sql/sp3mon.jpg
Performance monitor SP4(test machine)
http://www.callacomp.co.uk/sql/sp4mon.jpg
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Hate_orphaned_users" wrote:

> Did you run update_statistics , reindex database.
> Maybe recompile stored procedures!
> Greetz|||Nigel wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel
Have you reviewed the execution plan for one of the problem queries?
Did you update statistics (manually, not automatically) when you moved
the database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It sounds like the query plan has changed so you may need to tune or change
that query slightly so that it gets a better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:85602962-D3D7-4951-8E08-4C70D67C4855@.microsoft.com...
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of
> the
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
> to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
> I
> then install SP4 on this machine it now runs at 100% CPU for 30-50
> seconds.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN
> with
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of
> master,
> etc), sp changes.
> Cheers
> nigel|||I've updated statistics -sp_updatestats
I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
The process uses no SP all queries are suppled from the client using JDBC,
doesn't this mean that the query plan is updated as and when. Certainly afte
r
a reboot? Or am I wrong?
I've run PSSDiag but I'm no expert at looking at the traces files. Either a
little help or lots of reading to do?
Regatds
NIgel|||It may very well generate a new query plan each and every time you execute
it. But that does not mean it will generate a good one or even a different
one. If it generated a bad plan the last time you executed it and the
conditions or parameters have not changed chances are it will generate a bad
one again and again and again. Some times the conditions are such or the
data is such that the optimizer thinks it has the right plan but ends up
with a bad one for your conditions. By changing the query slightly ( an IN
to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
and in this case hopefully better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
> I've updated statistics -sp_updatestats
> I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
> The process uses no SP all queries are suppled from the client using JDBC,
> doesn't this mean that the query plan is updated as and when. Certainly
> after
> a reboot? Or am I wrong?
> I've run PSSDiag but I'm no expert at looking at the traces files. Either
> a
> little help or lots of reading to do?
> Regatds
> NIgel|||OK Cheers, I start looking into this. So does this mean that SP4 has changed
how query plans are calculated?
--
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Andrew J. Kelly" wrote:

> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a b
ad
> one again and again and again. Some times the conditions are such or the
> data is such that the optimizer thinks it has the right plan but ends up
> with a bad one for your conditions. By changing the query slightly ( an IN
> to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
> and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
>|||Every service pack and or edition has the potential to change query plans to
some extent and Sp4 is no exception. Most of the time it is due to fixing
bugs and occasionally a query plan may be affected by it. This does not mean
all of them will change.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:0DD973D6-5D6C-4633-B533-350115E0477C@.microsoft.com...[vbcol=seagreen]
> OK Cheers, I start looking into this. So does this mean that SP4 has
> changed
> how query plans are calculated?
> --
> Nigel
> Underpaid and working for the NHS.
> So help me and save lives!
>
> "Andrew J. Kelly" wrote:
>|||Hi, you need at least apply postSp hotfix (2187) and check again.
Regards,
Oleg.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOSI3EpPHHA.1252@.TK2MSFTNGP02.phx.gbl...
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a
> bad one again and again and again. Some times the conditions are such or
> the data is such that the optimizer thinks it has the right plan but ends
> up with a bad one for your conditions. By changing the query slightly ( an
> IN to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a
> different and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
> "Nigel" <Nigel@.discussions.microsoft.com> wrote in message
> news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
>

Problem with xp_sendmail and @query parameter

I am having a bit of trouble with a new SQLServer 2000 installation. I am
in the process of swapping out a server (sql sp3) and replacing it with a
new server (sql sp3a). Everything went smoothly until I hit a brick wall
with xp_sendmail.
The problem is when xp_sendmail tries to attach a query (with the @.query
parameter), and the SQL for the query runs on a linked server. Under the
old server, this was never a problem. But now I receive the following error
message:
ODBC error 7410 (42000) Remote access not allowed for Windows NT user
activated by SETUSER.
Also, here is the actual sql stmt which is running:
exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
test',@.subject='Test 1', @.attach_results=true,@.query='select * from
server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
The links to the other server are identical on both the old and new
machines. Any assistance would be greatly appreciated.
Thanks,
BobMost probably the job is owned by someone who isn't sysadmin, and some stuff
aren't allowed to do when the jobs owner isn't sysadmin (stuff at the server
level, as Agent uses SETUSER to emulate the jobowner's user context *in that
particular database*).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bob R." <bobr2@.earthlink.net> wrote in message
news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> I am having a bit of trouble with a new SQLServer 2000 installation. I am
> in the process of swapping out a server (sql sp3) and replacing it with a
> new server (sql sp3a). Everything went smoothly until I hit a brick wall
> with xp_sendmail.
> The problem is when xp_sendmail tries to attach a query (with the @.query
> parameter), and the SQL for the query runs on a linked server. Under the
> old server, this was never a problem. But now I receive the following
error
> message:
> ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> activated by SETUSER.
> Also, here is the actual sql stmt which is running:
> exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> The links to the other server are identical on both the old and new
> machines. Any assistance would be greatly appreciated.
> Thanks,
> Bob
>|||Thanks, but this is not a job. The problem happens in Query Analyzer. If I
run the same SQL Stmt against the old server in Query Analyzer, it works
fine.
-Bob
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Most probably the job is owned by someone who isn't sysadmin, and some
stuff
> aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
server
> level, as Agent uses SETUSER to emulate the jobowner's user context *in
that
> particular database*).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bob R." <bobr2@.earthlink.net> wrote in message
> news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > I am having a bit of trouble with a new SQLServer 2000 installation. I
am
> > in the process of swapping out a server (sql sp3) and replacing it with
a
> > new server (sql sp3a). Everything went smoothly until I hit a brick
wall
> > with xp_sendmail.
> >
> > The problem is when xp_sendmail tries to attach a query (with the @.query
> > parameter), and the SQL for the query runs on a linked server. Under
the
> > old server, this was never a problem. But now I receive the following
> error
> > message:
> >
> > ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> > activated by SETUSER.
> >
> > Also, here is the actual sql stmt which is running:
> >
> > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> >
> > The links to the other server are identical on both the old and new
> > machines. Any assistance would be greatly appreciated.
> >
> > Thanks,
> >
> > Bob
> >
> >
>|||Perhaps one is executed to text and the other to grid. Grid is faster, and possibly also the text
handling can have been improved in some sp of QA (not sure about this, though). Also, does SET
NOCOUNT ON help.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bob R." <bobr2@.earthlink.net> wrote in message news:uL3%23d3drDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Thanks, but this is not a job. The problem happens in Query Analyzer. If I
> run the same SQL Stmt against the old server in Query Analyzer, it works
> fine.
> -Bob
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Most probably the job is owned by someone who isn't sysadmin, and some
> stuff
> > aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
> server
> > level, as Agent uses SETUSER to emulate the jobowner's user context *in
> that
> > particular database*).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Bob R." <bobr2@.earthlink.net> wrote in message
> > news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > I am having a bit of trouble with a new SQLServer 2000 installation. I
> am
> > > in the process of swapping out a server (sql sp3) and replacing it with
> a
> > > new server (sql sp3a). Everything went smoothly until I hit a brick
> wall
> > > with xp_sendmail.
> > >
> > > The problem is when xp_sendmail tries to attach a query (with the @.query
> > > parameter), and the SQL for the query runs on a linked server. Under
> the
> > > old server, this was never a problem. But now I receive the following
> > error
> > > message:
> > >
> > > ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> > > activated by SETUSER.
> > >
> > > Also, here is the actual sql stmt which is running:
> > >
> > > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> > >
> > > The links to the other server are identical on both the old and new
> > > machines. Any assistance would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > > Bob
> > >
> > >
> >
> >
>|||I ran a trace on both versions, and I can see that the new version (sp3a) is
running "sp_setuserbylogid" before executing the query. The older machine
is on sp3, and does not do this. I looked at the dll for xp_sendmail
between both machines, and it looks like sp3a updated sqlmap70.dll. (This
is not mentioned anywhere in the release notes) I swapped out the dll for
the one from sp3, and everything works fine. However, since I could find no
documentation concerning changes to xp_sendmail in sp3a, I am not sure if
this is a good idea.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%236bGUserDHA.372@.TK2MSFTNGP11.phx.gbl...
> Perhaps one is executed to text and the other to grid. Grid is faster, and
possibly also the text
> handling can have been improved in some sp of QA (not sure about this,
though). Also, does SET
> NOCOUNT ON help.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bob R." <bobr2@.earthlink.net> wrote in message
news:uL3%23d3drDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Thanks, but this is not a job. The problem happens in Query Analyzer.
If I
> > run the same SQL Stmt against the old server in Query Analyzer, it works
> > fine.
> >
> > -Bob
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > Most probably the job is owned by someone who isn't sysadmin, and some
> > stuff
> > > aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
> > server
> > > level, as Agent uses SETUSER to emulate the jobowner's user context
*in
> > that
> > > particular database*).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Bob R." <bobr2@.earthlink.net> wrote in message
> > > news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > > I am having a bit of trouble with a new SQLServer 2000 installation.
I
> > am
> > > > in the process of swapping out a server (sql sp3) and replacing it
with
> > a
> > > > new server (sql sp3a). Everything went smoothly until I hit a brick
> > wall
> > > > with xp_sendmail.
> > > >
> > > > The problem is when xp_sendmail tries to attach a query (with the
@.query
> > > > parameter), and the SQL for the query runs on a linked server.
Under
> > the
> > > > old server, this was never a problem. But now I receive the
following
> > > error
> > > > message:
> > > >
> > > > ODBC error 7410 (42000) Remote access not allowed for Windows NT
user
> > > > activated by SETUSER.
> > > >
> > > > Also, here is the actual sql stmt which is running:
> > > >
> > > > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > > > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > > > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> > > >
> > > > The links to the other server are identical on both the old and new
> > > > machines. Any assistance would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > > Bob
> > > >
> > > >
> > >
> > >
> >
> >
>