September 20, 2006 at 4:46 am
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.... 🙂
September 20, 2006 at 5:10 am
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 !!!**
September 20, 2006 at 7:09 am
Can yiu explain why would u be Creating and adding linked server on the fly?
Thanks
Sreejith
September 20, 2006 at 9:12 am
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)
September 20, 2006 at 10:45 pm
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....?
September 20, 2006 at 10:48 pm
Thank You....
Can u provide any example by using openrowset?
which one gives the better performence?
Thanks,
September 20, 2006 at 11:05 pm
You can find extended explanation and bunch of examples by just pressing "F1" in QA or EM.
_____________
Code for TallyGenerator
September 20, 2006 at 11:30 pm
I Will practice with those and let u know....Thanks allot.....
September 21, 2006 at 11:14 pm
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
April 10, 2013 at 5:36 am
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?
April 10, 2013 at 5:47 am
Check Distributed Transaction Coordinator status.
_____________
Code for TallyGenerator
April 10, 2013 at 6:31 am
Ah, yes - good point! The DTC seems to be up'n'running, but I can see some logs in the event viewer, that the DTC had stopped a transaction..and that prior to that, there had been some memory allocation issues.
Hmm, can see I need to learn stuff about the DTC - I'm a complete newbie on that part, so I don't get why it bothers with my delete statement, that I ran in the query window of management studio without a "begin transaction"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply