Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Error Message:Cannot open database requested in login ''databasename''. Login fails. Expand / Collapse
Author
Message
Posted Wednesday, September 20, 2006 4:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 1, 2013 5:22 AM
Points: 165, Visits: 205


While i was trying to execute the stored procedure  have got  the below error and terminated the execution.

Server: Msg 4060, Level 11, State 1, Line 1
Cannot open database requested in login 'TomHolzer'. Login fails.


More Info
----------------
Have one table with all servers info including ip.
Connecting to the remote server using OSQL using cursor with that  above table info.
Creating and adding linked server and executing the dynomic queries.
For all the above i have used two cursors.
So if the login fails that should be go to the next server(Means next statements).But that is not going on.Simply it is  terminating the execution.
How can i handle this situation?
Any suggestion would be helpful to me....


Thanks inadvance.... 

 


 

Post #309868
Posted Wednesday, September 20, 2006 5:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:37 AM
Points: 2,553, Visits: 567
Bagath - you'd be better off posting the whole code so someone can take a look at it and possibly identify the error spot...







**ASCII stupid question, get a stupid ANSI !!!**
Post #309872
Posted Wednesday, September 20, 2006 7:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 1:57 PM
Points: 394, Visits: 83

Can yiu explain why would u be Creating and adding linked server on the fly?

Thanks

Sreejith

Post #309914
Posted Wednesday, September 20, 2006 9:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 19, 2009 4:02 PM
Points: 223, Visits: 108

Hi,

This type of problem exists when you are using the linked queries that reference the remote server which could not be login on a trusted connection coz, Sql uses the trusted connection to create a link between the servers.

You can use the OpenRowset function in your procedure where you are using the linked  queries.

Thanks.

Amit Gupta (MVP)

 

Post #309984
Posted Wednesday, September 20, 2006 10:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 1, 2013 5:22 AM
Points: 165, Visits: 205

After connecting to the remote server have to executive some queries in the remote server.

For that i have created some dynamic queries and executing those thru the linked server.

Is there any alternative....?

Post #310113
Posted Wednesday, September 20, 2006 10:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 1, 2013 5:22 AM
Points: 165, Visits: 205

Thank You....

Can u provide any example  by using openrowset?

which one gives the better performence?

 

Thanks,

Post #310114
Posted Wednesday, September 20, 2006 11:05 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 4,576, Visits: 8,342

You can find extended explanation and bunch of examples by just pressing "F1" in QA or EM.

 

Post #310118
Posted Wednesday, September 20, 2006 11:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 1, 2013 5:22 AM
Points: 165, Visits: 205
I Will practice with those and let u know....Thanks allot.....
Post #310121
Posted Thursday, September 21, 2006 11:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 1, 2013 5:22 AM
Points: 165, Visits: 205

Hello,

 

The below is the code.I am getting the above error while executing this.

Just What i want is if the error comes then it will go to the next server and repeat the execution..

Can any one help me and

create PROCEDURE SP_Link 
AS 
DECLARE @IP VARCHAR(100)   
DECLARE @Password VARCHAR(100)   
DECLARE @DBName VARCHAR(100)   
DECLARE @SQL_Password VARCHAR(100)   
DECLARE @OSQL VARCHAR(100)   
BEGIN 
 DECLARE CUR_FETCH1 CURSOR FOR SELECT SERVER_IP,SQL_PASSWORD,DB_NAME FROM RemoteInfo 
 OPEN CUR_FETCH1 
 FETCH NEXT FROM CUR_FETCH1 INTO @IP,@SQL_Password,@DBName 
 WHILE @@FETCH_STATUS=0 
 BEGIN 
  SET @OSQL='osql -S '+@IP +' -U sa -P '+@SQL_Password +' -d master -Q"SELECT Name FROM sysdatabases"'  
  IF EXISTS (SELECT * FROM SYSOBJECTS WHERE TYPE='U' AND NAME='DBases')   
  BEGIN 
   drop table DBases         
  END  --2   
  CREATE TABLE DBases    
  (    
      dbname SYSNAME NULL    
   );    
  INSERT  into DBases   
  EXEC master..xp_cmdshell @OSQL   
  IF EXISTS(SELECT 1 FROM DBases WHERE LTRIM(RTRIM(DbName))=N'master') 
  BEGIN 
   DECLARE @DROPSERVER VARCHAR(50) 
   DECLARE @DQUERY VARCHAR(800) 
   DECLARE @CNT INT 
   SET @DROPSERVER=('SP_DROPSERVER ''L_REMOTE'',''DROPLOGINS''') 
   IF EXISTS(SELECT * FROM MASTER.DBO.SYSSERVERS WHERE SRVNAME LIKE 'L_REMOTE') 
   EXEC(@DROPSERVER) 
   EXEC SP_ADDLINKEDSERVER 
   @SERVER='L_REMOTE', 
   @SRVPRODUCT='', 
   @PROVIDER='SQLOLEDB', 
   @CATALOG=@DBname, 
   @DATASRC=@IP 
   EXEC SP_ADDLINKEDSRVLOGIN 
   @RMTSRVNAME='L_REMOTE', 
   @USESELF='False', 
   @RMTUSER='sa', 
   @RMTPASSWORD=@SQL_Password 
   SET @DQUERY='' 
   IF @DBName LIKE '%crm%' 
   BEGIN 
    SET @DQUERY='SELECT MAX(RO)AS DATE FROM SERVICE WHERE RO BETWEEN GETDATE()-5 AND GETDATE()' 
   END 
   ELSE 
   BEGIN 
    SET @DQUERY='SELECT MAX(DEAL)AS DATE FROM PURCHASE WHERE DEAL BETWEEN GETDATE()-5 AND GETDATE()' 
   END 
              
       IF EXISTS (SELECT * FROM TempDB..SYSOBJECTS WHERE TYPE='U' AND NAME='temp_data') 
                    BEGIN 
                        DROP TABLE TempDB..temp_data
      end
   SET @DQUERY='SELECT * INTO TEMPDB.DBO.temp_data FROM OPENQUERY(L_REMOTE,'''+@DQUERY+''')' 
   EXEC(@DQUERY) 
   SELECT * FROM TEMPDB.DBO.temp_data  
   SELECT @CNT=COUNT(*) FROM TEMPDB.DBO.temp_data  
   IF @CNT>=1 
   BEGIN 
    DECLARE @D_DATE VARCHAR(100) 
    DECLARE CUR_TEMPDATA CURSOR FOR SELECT * FROM TEMPDB.DBO.temp_data  
    OPEN CUR_TEMPDATA 
    FETCH CUR_TEMPDATA INTO @D_DATE 
    WHILE @@FETCH_STATUS=0 
    BEGIN 
     INSERT INTO Final_Data SELECT @D_DATE,@DBname 
     FETCH CUR_TEMPDATA INTO @D_DATE  
    END 
   END 
   CLOSE CUR_TEMPDATA 
   DEALLOCATE CUR_TEMPDATA 
  END  
  ELSE 
  BEGIN 
   PRINT 'No Connection To The Remote Server' 
  END 
  FETCH NEXT FROM CUR_FETCH1 INTO @IP,@SQL_Password,@DBName 
 END 
 
END 
CLOSE CUR_FETCH1 
DEALLOCATE CUR_FETCH1 
 
    
 

Post #310463
Posted Wednesday, April 10, 2013 5:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 2:19 AM
Points: 2, Visits: 4
I've received that same error message when performing a simple delete-statement ("delete from rpt_reports where createddate<'2013-04-01'") - the message I get is:

Msg 4060, Level 11, State 1, Line 65536
Cannot open database "xxx" requested by the login. The login failed.
Msg 18456, Level 14, State 1, Line 65536
Login failed for user 'xxx'.

I tried to execute the command form server management studio, and I was logged with integrated security. I had no problems executing other commands like "select count(*) from rpt_reports") in the very same window?!?

...any ideas?
Post #1440745
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse