SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error Message:Cannot open database requested in login ''databasename''. Login fails.


Error Message:Cannot open database requested in login ''databasename''. Login fails.

Author
Message
Bagath
Bagath
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 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.... :-)



sushila
sushila
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8543 Visits: 639
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 !!!**
Sreejith Sreedharan
Sreejith Sreedharan
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1409 Visits: 89

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

Thanks

Sreejith


AMIT GUPTA-263376
AMIT GUPTA-263376
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 Visits: 147

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)


Bagath
Bagath
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 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....?


Bagath
Bagath
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 205

Thank You....

Can u provide any example by using openrowset?

which one gives the better performence?

Thanks,


Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25995 Visits: 12477

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


Bagath
Bagath
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 205
I Will practice with those and let u know....Thanks allot.....
Bagath
Bagath
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 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




thomas 73726
thomas 73726
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search