October 3, 2006 at 7:22 am
Hi all,
I have a scenario where i have to create an SP ( Chose using SP for the ease of getting called by the UI ), which creates Logins and Users for databases accross multiple servers. The Servername,loginname,dbname etc are available in server tables. The only way i could see it possible was using osql as i will need multiple connections over multiple servers which is not possible without osql directly from the SP. So i finally decided to put the login,user creation DDLs in a string which is passed to xp_cmdshell in a function with Servername,loginname,dbname as input parameters. I am calling this func iteratively for each row of a recordset in the SP.
Now the problem i am facing is i am not able to catch the error from the osql commands to the SP. The xp_cmdshell always shows success as it successfully passes the control to db engine for osql but the errors occur at the dbengine due to some unpredictible cases.My queries are:
The solution i am implementing, is it the best solution for this scenario?
How do i get the error from osql to the SP i am calling the function from ?
Thanks
October 3, 2006 at 8:40 am
I'd imagine you could do this all from one SP without the need to xp_cmdShell et al. Have you tried setting them up as linked servers and then using the 4 part naming to run your SQL statements against? Like On Server1 there is a linked servers to server2 on server1 you'd execute...
SELECT * FROM server2.mydb.dbo.mytable
October 4, 2006 at 6:07 am
Hi Luke,
Thanks for replying.
Adding the servers as linked server is not an option here as the server names will be kept on changing in the tables on frequent basis. Adding the server as linked server and removing them at this rate is not an option i have. This SP to create Logins etc for multiple servers etc will be run hundreds of times in a day...
I agree with you that i can do it from the same SP itself without using the function (which i used for separating the functionality logically). But i cant use linked servers here.
i am trying to run this query :
SET
@CMDStr=N'osql -S '+@ServerName+' -E -d '+@DatabaseName+' -Q "
DECLARE @RetVal BIT
SET @RetVal=0
IF NOT EXISTS(SELECT [name] FROM sys.server_principals WHERE [name]='''+@UserName+''')
CREATE LOGIN ['+@UserName+'] FROM WINDOWS
IF (@@ERROR<>0)
BEGIN
SET @RetVal = 1
GOTO ERROR
END
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name ='''+@UserName+''')
CREATE USER ['+@UserName+'] FOR LOGIN ['+@UserName+']
IF (@@ERROR<>0)
BEGIN
SET @RetVal = 1
GOTO ERROR
END
EXEC SP_ADDROLEMEMBER '''+@UserRole+''', '''+@UserName+'''
IF (@@ERROR<>0)
BEGIN
SET @RetVal = 1
GOTO ERROR
END
ERROR:
EXIT /b (SELECT @RetVal)"'
EXEC
xp_cmdshell @CMDSTR
Here i am trying to reflect the @RetVal as exitcode to be caught outsite osql ( i still have to figure out how to do that). Then i was thinking about using this value to know if there is an error or not. But while running this query, i got the following error:
Msg 156, Level 15, State 1, Server MyServer, Line 1
Incorrect syntax near the keyword 'EXIT'.
Any suggestions ?
Thanks
October 4, 2006 at 6:59 am
I think it's taking issue with the /b following the EXIT command, although I can't swear to it as I'm not terribly familiar with scripting osql commands. It may also be having issues with a label in your script. Just out of curiosity, what do you see when you print @cmdstr? Copy the printed message back into QA and you should be able to more easily troubleshoot your syntax error?
Also, have you though about perhaps using a RaiseError and trying to catch that in your procedure?
October 4, 2006 at 7:26 am
This format:
osql -S ... -Q "EXIT(declare @x int set @x=1 select 5)"
will set the errorlevel to 5.
Try and wrap your entire command with EXIT()
EXIT( <all of your SQL code here> SELECT @retval)
October 4, 2006 at 7:44 am
Actually, try the example below. @stat will contain the final return value from the osql query (5 in this example, but your would replace that with @RetVal):
DECLARE @stat int
, @cmd varchar(1000)
SET @cmd = 'osql -S YOURSERVER -d TEST -E -Q "EXIT(declare @x int set @x=1 select 5)"'
EXEC @stat = master..xp_cmdshell @cmd , no_output
PRINT @stat
October 4, 2006 at 9:01 am
Hi Web,
You don't say how you are running your OSQL command. You can always pipe the output of OSQL to a file and run something to analyse the output.
Too bad you can't just open connections to your servers and execute the SP live.
I have had good luck using DSN-less connections. My app has a database of server names, user ID, and password. I make the connection, do the process, close the connection, and move on.
ATBCharles Kincaid
October 5, 2006 at 5:14 am
I tried to wrap the code in EXIT() and run it but the error still persists.
@CMDSTR looks follows after wrapping it in EXIT() :
osql -S MYSERVER -E -d MYDB -Q " EXIT /b (DECLARE @RetVal BIT SET @RetVal=0 IF NOT EXISTS(SELECT [name] FROM sys.server_principals WHERE [name]='UserDomain\UserName') CREATE LOGIN [UserDomain\UserName] FROM WINDOWS IF (@@ERROR<>0) BEGIN SET @RetVal = 1 GOTO Error END IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name ='UserDomain\UserName') CREATE USER [UserDomain\UserName] FOR LOGIN [UserDomain\UserName] IF (@@ERROR<>0) BEGIN SET @RetVal = 1 GOTO Error END EXEC SP_ADDROLEMEMBER 'db_datareader', 'UserDomain\UserName' IF (@@ERROR<>0) BEGIN SET @RetVal = 1 GOTO Error END Error: SELECT @RetVal ) "
Without wrapping it in EXIT it looks like :
osql -S MYSERVER -E -d MYDB -Q " DECLARE @RetVal BIT SET @RetVal=0 IF NOT EXISTS(SELECT [name] FROM sys.server_principals WHERE [name]='UserDomain\UserName') CREATE LOGIN [UserDomain\UserName] FROM WINDOWS IF (@@ERROR<>0) BEGIN SET @RetVal = 1 GOTO Error END IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name ='UserDomain\UserName') CREATE USER [UserDomain\UserName] FOR LOGIN [UserDomain\UserName] IF (@@ERROR<>0) BEGIN SET @RetVal = 1 GOTO Error END EXEC SP_ADDROLEMEMBER 'db_datareader', 'UserDomain\UserName' IF (@@ERROR<>0) BEGIN SET @RetVal = 1 GOTO Error END Error: EXIT /b (SELECT @RetVal) "
In both the cases it giving the same error... ( removing /b doesnt make any difference )
RAISERROR with state of 127 sets the DOS ERRORLEVEL variable to the error message ID but i dont know how to retrieve the ERRORLEVEL from xp_cmdshell.
Charles if you are making a connection , doing the process, closing the connection , then are you doing it manually\individually for all servers or is it an automated process by using some script of yours ?
Yes i can pipe the output of OSQL to a file but if 1000 such queries are run in a day wouldnt it be a perf hit on the system, 1000 files created, scanned, acted upon and then deleted ???
Thanks
October 5, 2006 at 5:36 am
1. Please don't use the /b - that is not valid T-SQL. Look up "osql utility" in BOL for the syntax of EXIT().
2. Look at my earlier example. To get the errorlevel, use this format:
EXEC @stat = master..xp_cmdshell @cmd
When you use Exit(), the last SELECT actually sets the errorlevel.
3. Other than the /b, I don't see anything wrong, but I am going to experiment a bit.
October 5, 2006 at 5:50 am
I converted your query slightly to test it, and it works as designed. In fact, I forgot to change the server name YOURSERVER to one of my actual server names, and it returned 1. Changing it to a valid server name return 0.
This is the code I used (I'm using the sysusers table in my TEST database):
DECLARE @cmd varchar(8000)
, @status int
SET @cmd = 'osql -S YOURSERVER -d TEST -E -Q "EXIT(
DECLARE @RetVal BIT, @dummy int
SET @RetVal = 0
IF NOT EXISTS(SELECT [name] FROM sysusers WHERE [name]=''Public'')
SET @dummy = 1
-- CREATE LOGIN [UserDomain\UserName] FROM WINDOWS
IF (@@ERROR<>0)
BEGIN
SET @RetVal = 1
GOTO Error
END
IF NOT EXISTS (SELECT * FROM sysusers WHERE name =''Public'')
SET @dummy = 1
-- CREATE USER [UserDomain\UserName] FOR LOGIN [UserDomain\UserName]
IF (@@ERROR<>0)
BEGIN
SET @RetVal = 1
GOTO Error
END
-- EXEC SP_ADDROLEMEMBER ''db_datareader'', ''UserDomain\UserName''
IF (@@ERROR<>0)
BEGIN
SET @RetVal = 1
GOTO Error
END
Error:
SELECT @RetVal
)"'
--PRINT @cmd
EXEC @status = master..xp_cmdshell @cmd , no_output
PRINT 'Return code: ' + CONVERT(varchar(10), @status)
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply