A curious error message, local temp vs. global temp tables?!?!?

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @.cmd varchar(256)
DECLARE @.LargestEventSize int
DECLARE @.Width int, @.Msg varchar(128)
SELECT @.LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @.cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @.LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @.Width = @.LargestEventSize + 1
SET @.Msg = 'Here is the junk you asked about' + CHAR(13) + '---------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@.WhoKnows.com',
@.query = @.cmd,
@.no_header= 'TRUE',
@.width = @.Width,
@.dbuse = 'MyDB',
@.subject='none of your darn business',
@.message= @.Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?I would still like to hear if anyone knows anything different, but while looking into Des' sendmail problem, I found this lil' tidbit in BOL for xp_sendmail If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail. I suspect the "separate connection to SQL Server" is the issue here?!?!?! Hmmmm...perhaps the local temp table can be seen by child processes called by the proc that creates the table EXCEPT in xp_sendmail, etc.|||You hit the problem right on the head... xp_sendmail does execute the query in a different context, meaning that it can't see local variables, settings, or temp tables. You can think of it almost as though xp_sendmail were cranking up OSQL.EXE to execute your query (that isn't what actually happens, but it is logically pretty close).

-PatP

A connection cannot be made to Analysis Services 2005

Please help!

I have been trying to deploy an Analysis Services 2005 cube to a server from a local PC. I have had no luck. This was working yesterday and I cannot work out what might be different. This is the error message I am getting:

The project could not be deployed to the 'qccatsvr1' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

By the way this works on another PC that is set up the same way...the only thing I can see different is the Novell user account I used to log in to the PC.

This is what I have tried so far:

Checked that the Analysis Services service is running on the server - it is. Check the target server - this is ok. Re-install Analysis Services 2005 - no problems occurred during this Tried all different changes to the security log on in the Analysis Services service - nothing worked. Tried to open the AS solution file from the actual server and deploy - no luck.

So you can see that I am now getting quite frustrated. I know (hope) it is something simple that I have missed.

Does anyone have any ideas/solutions for me.

Thanks in advance!!!

Steve

SSAS uses windows authentication exclusively. It will not authenticate using a Novell account, but there should be a setting in your Novell client which controls how the user is logged on to the local machine. It is the local windows account that needs to have administrator priviledges in SSAS in order to be able to deploy a new database.

Were you using the same user account when you logged on to the server and tried to deploy the solution? If you use the same account that was used to install SSAS you should not have any issues.

If you want to deploy a solution from a remote machine on a Novell network you will need to setup a local account on the remote workstation which is set to log on to the local machine when the Novell user logs in and then you will need to set up an identical local account (with an identical password) on the server machine and assign the account on the server to the server administration role in SSAS.

|||

I have setup exactly the same account on the server as the windows username used to logon to the remote PC. The account also has exactyl the same password and has been given full access to SSAS. However this does not work either.

The things that has got me confused is that this user was able to deploy a few days before I posted this thread with no problems...that was without a username and password set up on the server with access to SSAS.

Do you have any aother ideas?

|||

I thought you mentioned in your original post that you were now using a different user account. Which is what made me think that it must be something to do with the Novell account/local account connection.

You could have a look at the following link, it contains information on troubleshooting SSAS connection issues: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

|||

Darren,

Thank-you for your advice. I got onto that document and tried a couple of things. The one that helped the most was the SQL Server Profiler. This showed me what was happening with particular accounts and I was able to change the account permission accordingly.

Everything is now ok.

Thanks again for your help with this one...

A connection cannot be made to Analysis Services 2005

Please help!

I have been trying to deploy an Analysis Services 2005 cube to a server from a local PC. I have had no luck. This was working yesterday and I cannot work out what might be different. This is the error message I am getting:

The project could not be deployed to the 'qccatsvr1' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

By the way this works on another PC that is set up the same way...the only thing I can see different is the Novell user account I used to log in to the PC.

This is what I have tried so far:

Checked that the Analysis Services service is running on the server - it is.

Check the target server - this is ok.

Re-install Analysis Services 2005 - no problems occurred during this

Tried all different changes to the security log on in the Analysis Services service - nothing worked.

Tried to open the AS solution file from the actual server and deploy - no luck.

So you can see that I am now getting quite frustrated. I know (hope) it is something simple that I have missed.

Does anyone have any ideas/solutions for me.

Thanks in advance!!!

Steve

SSAS uses windows authentication exclusively. It will not authenticate using a Novell account, but there should be a setting in your Novell client which controls how the user is logged on to the local machine. It is the local windows account that needs to have administrator priviledges in SSAS in order to be able to deploy a new database.

Were you using the same user account when you logged on to the server and tried to deploy the solution? If you use the same account that was used to install SSAS you should not have any issues.

If you want to deploy a solution from a remote machine on a Novell network you will need to setup a local account on the remote workstation which is set to log on to the local machine when the Novell user logs in and then you will need to set up an identical local account (with an identical password) on the server machine and assign the account on the server to the server administration role in SSAS.

|||

I have setup exactly the same account on the server as the windows username used to logon to the remote PC. The account also has exactyl the same password and has been given full access to SSAS. However this does not work either.

The things that has got me confused is that this user was able to deploy a few days before I posted this thread with no problems...that was without a username and password set up on the server with access to SSAS.

Do you have any aother ideas?

|||

I thought you mentioned in your original post that you were now using a different user account. Which is what made me think that it must be something to do with the Novell account/local account connection.

You could have a look at the following link, it contains information on troubleshooting SSAS connection issues: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

|||

Darren,

Thank-you for your advice. I got onto that document and tried a couple of things. The one that helped the most was the SQL Server Profiler. This showed me what was happening with particular accounts and I was able to change the account permission accordingly.

Everything is now ok.

Thanks again for your help with this one...

500 INTERNAL ERROR

I install sql server delvelop ed. i used as same account
of window i also used the mixed window. since the server
is local in code i put empty;
during the run it said it can't find the file and 500
internal error. what should I do I beleive I can connect
but my prog doesnt' work
christy,
Where and under waht circumstances are you seeing this error message? Try
connecting to the server using OSQL and ISQL, do you get the same error?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

500 INTERNAL ERROR

I install sql server delvelop ed. i used as same account
of window i also used the mixed window. since the server
is local in code i put empty;
during the run it said it can't find the file and 500
internal error. what should I do I beleive I can connect
but my prog doesnt' workchristy,
Where and under waht circumstances are you seeing this error message? Try
connecting to the server using OSQL and ISQL, do you get the same error?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

401 authorization denied error for web service proxy

all-
When I have a SQLXML 3.0 virtual directory set up on the same web server as the one that my application is in on my local machine, I can run the application. When I move the web reference (and the web service) to a different server, I can access the
application on my machine with http://localhost but not http://<machinename>.
I have tried setting various properties from the System.Web.Services library in addition to credentials. Here are the the things I have tried:
proxy.UnsafeAuthenticatedConnectionSharing = true;
proxy.Credentials = CredentialCache.DefaultCredentials;
proxy.PreAuthenticate = true;
Maybe the problem is with IIS, but the username does seem to be passed in correctly when it is localhost.
to recap
SAME BOX LOCAL WEB, REMOTE SQLXML BOTH REMOTE
http://localhost YES YES NO
http://<machinename> YES NO NO
After further troubleshooting I can say with some confidence that this is a problem with server-to-server communication.
I have found KB articles on ServerXMLHTTP and how to pass default credentials from server to server, but no examples in .NET.
I also tried every syntax of proxycfg that I could think of, all the way down to
proxycfg -d
Now all requests bypass the proxy server and I still can't pass default credentials.
Any help would be greatly appreciated.