Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eyost
Starting Member
3 Posts |
 Posted - 2008-12-08 : 16:04:45
|
| Hi all,I am running into a situation that I was hoping you could help me with. I have a simple TSQL Statement to add a user to an existing database:Use SPCentralGoExec sp_adduser "STO\aspnet","SPSService" GOThe above statement works great and will add the user SPSService attached to the existing STO\ASPNet login.I need to take that statement and call on it through a batch file. I can use the exact same statement hand typing it through OSQL and it works without issue. However when I try to call the statement from within OSQL it indicates that the syntax is incorrect.Here's the batch file that is using a file named 'AddUser.sql' as an input file that contains the statement that works within Mgt Studio or handtyping it within OSQL.OSQL -E -S %computername%\SQL01 -i AddUser.sqlWhen I run this it indicates that Incorect syntax near ':'. Server Machine-Name\SQL01, Line 1 Incorrect syntax near 'machine-name\ASPNet'.I have tried it with surrounding the user and login names with quotation marks or apostrophes but that doesn't seem to resolve the issue. I have had success in the past using input files with OSQL so I am at a bit of a loss here.Thoughts, comments, suggestions?Thanks,Ed |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
eyost
Starting Member
3 Posts |
Posted - 2008-12-08 : 17:32:25
|
Thanks so much. I actually had some additional debris in the .sql statement that caused some issues. Eliminating that along with using sqlcmd and the apostrophes resolved the issue!On a related note, I need to be able to alter the user/login relationship for databases that our company transfers from one user to another. This is in regard to the 'MachineName'\ASPNet login. When the db is transfered from one user to another we have to run an Alter User statement to reflect the correct machine name\aspnet. I have created machine specific scripts to address this, however it would be great if I could grab the machine name and pass it into a statement to alter the user.Here is what I am currently using:Use SPNetCentralGoALTER USER SPSService WITH NAME = SPSService, LOGIN = [SPRO-DXPEY\ASPNET], DEFAULT_SCHEMA = [dbo];GoAs you can see, the ASPNet user is looking to a literal machine name which I have to change for each user. Unfortunately I cannot pass the %computername% variable in, but it would be great if I could.Any ideas on this approach?Edquote:
Originally posted by tkizer
Use single quotes instead of double quotes. If you are using SQL Server 2005, try using sqlcmd as osql is deprecated.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
eyost
Starting Member
3 Posts |
Posted - 2008-12-09 : 08:48:06
|
Tara,Excellent. This will help out dramatically. I took your code and attempted to strip out everything to the right and including the \ but am receiving a data conversion error. Is there a chance that you could give me an idea of the format of the Left function?Thanks,Edquote:
Originally posted by tkizer
You can use @@SERVERNAME, SUBSTRING/LEFT functions, and dynamic SQL to achieve this. Here's some pseudocode:DECLARE @server sysname, @sql nvarchar(4000)SET @server = @@SERVERNAME --> trim if you've got a named instance, get rid of everything after the slashSET @sql = 'ALTER USER ' ... + @server + ...EXEC (@sql)Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
|
|
|
|
|
Statement works in Mgt. Studio; not through OSQL?