and its producing the error Msg 102, Level 15, State 1, Line 250
Incorrect syntax near 's'.
im sorry I did show the incorrect line earlier as well the correct line is below
Yuck. You've got spaces in your database names. YUCK.
select 'exec sp_attach_single_file_db @dbname = ''[' + left(output, charindex('.mdf', output)-1) + ']'', @physname = ''' + @path + output + ''''
from #t
where output is not null
You'll probably need double quotes added around @path. Let me know.
I'm sorry I posted the incorrect line in my screenshot before, I updated the screenshot in my previous post, its giving me an error for line 250, this apostrophe... gave me the same error with the updated code, I believe its the only line with an apostrophe, I dont mind dealing with it manually unless its something easy to incorporate into the code
Yeah deal with it manually by adding an extra apostrophe: Noah''s Ark
looks like everything is going correct, I'll make sure I update you later on final results. You've been of great help thanks so much!
1 Like
You're welcome! Glad to help.
could deleting the ldfs cause permission issues?
File permissions? Or permissions inside the database? In either case, my answer would be no but I'm more confident on the database permissions part.
What error are you getting?
Well I get all the databases attached, it creates the ldfs for the databases and everything seems fine on sql's side. So I open Law and attempt to open lets say case A and I get an error that says
So in order to test if deleting the ldfs is my issue or if I perhaps did something wrong, I delete a database, copy back that database with the original ldf to the data folder, reattach the database and attempt to open case A within Law, at this point I'm able to open the case without error confirming that for some reason deleting this ldf and having it reattached in said manor is doing or isnt doing something.
I checked the permissions as well and nothing is different, I'm going to do research on it in the meantime.
Did you transfer the logins? And also unorphan the users?
Here's the script I use to unorphan, it has to be run in each database:
DECLARE @SQL VARCHAR(100)
DECLARE curSQL CURSOR FOR
SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name + ''', ''' + name + ''''
FROM sysusers
WHERE issqluser = 1 AND name NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest', 'dbo')
OPEN curSQL
FETCH curSQL INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@SQL)
FETCH curSQL INTO @SQL
END
CLOSE curSQL
DEALLOCATE curSQL
You could wrap it with sp_MSforeachdb to run it in each database.
The error isn't related to the ldfs. It's a login/user configuration issue.
do you mind elaborating on wrapping it with sp_MSforeachdb
I figured since I've recreated the users that this issue wouldnt happen
I'm pressed for time and will share an article on it: http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx
I don't understand SQL code at this level. I've been trying to mess with this to see what I can come up with although I'm sure what I'm doing is incorrect. My best guess was attempting to add EXECUTE master.sys.sp_MSforeachdb 'USE [?]; before the exec sp_change_users_login
I also tried: EXEC sp_MSforeachdb
@command1='INSERTED YOUR SCRIPT HERE'
both came with errors as I'm sure you're well aware of. It's a shame that reattaching databases in mass quantity has to be such a pain, Especially considering sql use is almost null here. It's simply a requirement for allowing more than one user to access a case at a time, or to make multiple pc's process the same activity together.... the only option we have other than SQL is Microsoft Access database, which is where we get limited to one user "Making changes" at a time
Try the script that's about halfway down this page in the "Doing this for all databases" section: https://thelonedba.wordpress.com/2013/02/22/restoring-into-a-different-environment-security-problem-orphaned-users/
after running the code below it did said Query executed Successfully, Although I still have the same permission error.
sp_msforeachdb
'USE [?]
DECLARE @OrphanedUsers TABLE
(
UserName SYSNAME ,
UserSID VARBINARY(85)
)
INSERT INTO @OrphanedUsers
EXEC sp_change_users_Login ''report''
IF EXISTS ( SELECT *
FROM @OrphanedUsers )
BEGIN
DECLARE @FixMe NVARCHAR(MAX)
SELECT @FixMe = ''''
SELECT @FixMe = @FixMe + ''
exec sp_change_users_login ''''update_one'''', '''''' + UserName + '''''' ,
'''''' + UserName + '''''' ''
FROM @OrphanedUsers
WHERE UserName COLLATE SQL_Latin1_General_CP1_CI_AS IN (
SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM master.dbo.syslogins )
PRINT @FixMe
--EXEC (@FixMe)
END
'
I haven;t been following the conversation closely, but the bit I have quoted is commented out so will NOT have run. I presume that the intention is to run the script, as you have, and check the output from the
PRINT @FixMe
and then if, and ONLY if, the output looks OK you comment-IN the
EXEC (@FixMe)
and re-run it to actually make the changes.
Normal safety net procedures apply! Take a backup first, put on lucky underware, etc. 
this is a backup system I'm working with and thanks! Where is that output going to?
--update
same message same error
Permission error or cannot open database error?