Server 'myserver' is not configured for RPC

Here's an interesting issue. I wrote a stored proc that kills processes on any server for any database with optional loginame or datetime sequence. On 2 of the production servers i got the below error. all other servers, it works fine including other prod servers. the below procs alleviate the issue by configuring the linkedserver for RPC. i'll need to add these for a new proc for creating linkedservers!!!

Problem: Server 'myserver' is not configured for RPC

Solution: Problem is most likely that RPC is not configured for your linked server. That is not a default option, after all. You can see what settings are configured with exec sp_helpserver.

If 'rpc,rpc out' is not in your results, then the the linked server isn't configured for RPC. To do so:

exec sp_serveroption @server='myserver', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='myserver', @optname='rpc out', @optvalue='true'

Dropping logins fail - login granted perms to other logins/users

This drove me insane yesterday!!! Simple task of dropping logins from one domain after adding accounts from another domain became a chore when 2 accounts failed to dropped because:

Login 'XXXX\XXXXXXX.xxxxxxx' has granted one or more
permission(s). Revoke the permission(s) before dropping the login.


I tried so many things like revoking granting ability from the acccount after searching who the accounts granted rights to (me!?!) Revokes rights from my account and on and on with no luck. I got frustrated and went home in disgust!!! SO I read that if all fails, drop the account (MINE!!!) that perms were granted to and voila the accounts dropped!!! Buzha!!!

ACCESS and BIGINTs

SQL Server introduced the BIGINT datatype in 2005 to keep up with Oracle. This is a cool thing especially for data warehousing. Unfortunately, ACCESS has the Integer and Long Integer datatypes. The Long Integer is equivalent to SQLs INT but no equivalent to SQLs BIGINT. This causes issues with ACCESS. If you link to a SQL table where the primary key is a BIGINT, the linked table will display #DELETED! in each cell. NO ITS NOT A SECURITY ISSUE!!! So if you have customers that need to view SQL data thru Access linked tables, there are work arounds BUT the BIGINTs will need to be changed to INT. Create an interface NOT in Access for your customers to view data if need be MOSS 07 is a quick solution if you're using it, else try VS.NET or JAVA web apps. yes its time consuming but you can't give all users Admin rights and SQL Server to play with!!!

Here's a link to your work arounds:

EXTRACTING A DATABASE USER'S LOGIN NAME THATS NOT STORED IN THE SYSLOGIN TABLE

I'M WRITING SCRIPTS FOR HANDLING A DOMAIN ACCOUNT CHANGE OVER. DID YOU KNOW WHEN YOU ENTER A NEW DATABASE USER AND GIVE IT A DOMAIN ACCOUNT LOGIN NAME, THAT DOES NOT EXIST IN THE INSTANCE\SECURITY\USERS LIST, THAT IT DOES NOT REGISTER IT IN THE SYSLOGIN TABLE? SO IF YOU NEED TO PULL THAT LOGIN NAME, YOU CAN USE SUSER_SNAME() BY
PASSING THE .SYS.SYSUSER.SID VALUE FOR THE DATABASE USER ACCOUNT IN QUESTION. I ORIGINALLY THOUGHT I WAS GOING TO HAVE TO ACCESS ACTIVE DIRECTORY DATA THROUGH A SMALL VB APP. THE AD ADMIN GROUP WILL SURELY NOT GIVE THAT ACCESS TO ANYBODY AND CAN FORESEE LOTS OF POLITICS AND PAPERWORK INVOLVED IN EVEN A FAIR SIZED ORG OF SAY A FEW HUNDRED PEOPLE! HOPE THIS SAVES YOU ALL SOME TIME!

How to change databases within a stored procedure or function

This one was a royal pain in the ASS!!!! Not fully correct info on the web or people saying it CAN'T be done. NEVER NEVER NEVER tell me it CAN'T be done!!!!

here is the revelant code from an SP for truncing logs. rest your weary head!!!

USE [RIXTOOLBOX]
ALTER PROCEDURE [dbo].[spTruncateLogs]

@DBName SYSNAME

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @command varchar(500), @LogFile VARCHAR(500), @LOGSTR VARCHAR(10);
SET @LOGSTR = '%LOG%'
SET @command = 'DECLARE c CURSOR FOR
SELECT
NAME
FROM
[' + @DBName + '].SYS.SYSFILES
WHERE
name LIKE ''' + @LOGSTR + ''';'

EXEC (@COMMAND)
OPEN c;
FETCH NEXT FROM c INTO @LogFile;

SET @COMMAND =
'ALTER DATABASE "' + @DBName + '" SET RECOVERY SIMPLE';
EXEC (@COMMAND);
CHECKPOINT

BACKUP LOG @DBName WITH NO_LOG

SET @command =
'
USE ' + @DBName + ';
DBCC SHRINKFILE (' + @LogFile + ', 1);
';

EXEC (@COMMAND);
CLOSE c
DEALLOCATE c

SET @COMMAND =
'ALTER DATABASE "' + @DBName + '" SET RECOVERY FULL';
EXEC (@COMMAND);

SET @command =
'SELECT
*
FROM
[' + @DBName + '].SYS.SYSFILES
WHERE
name LIKE ''' + @LOGSTR + ''';'

EXEC (@COMMAND);

END

A bit of SSIS confusion with server names

When registering a new Integration Service in SSMS, you're can only enter a server name. It won't register the instance. When you are importing/exporting a package to/from one instance to another, enter the server name AND instance in the server list even though it may only supply just the server name. Add the "\" and instance name to the server name as you would say registering a new server instance. You'll get an "OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages" error. You're not able to log into the server because its name is incomplete.

i had found ONE listing that pointed to the correct resolution: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/8da717ef-05e0-4a59-9a91-a96e29d4dfa6/ Read down about midway to get the answer.

When a database is stuck in restore mode

A vendor was working on one of our (their) DEV DBs. They were restoring it with something and did something to interupt it or it just got corrupt. I've tried a variety of things that could stop with no luck. finally upon searching for a solution, in a post of some forum foudn from googling, someone stated that you can:

"If no recovery option is specified in your command then it defaults to WITH RECOVERY...

I don't see any problem deleted the db and restoring it...but you can restore on top of db which is in loading status..."

So, therefore, I did a RESTORE, which I can now do after restarting the SQL and Agent services, WITH RECOVERY successfully! If you can not restore which I had previously tried to do getting an error stating it was still in progress of a restore, restart the services first then re-restore with recovery!

Good Luck!