It means you aren't connected to SQL Server.
If your system databases are in that folder, you're going to want to exclude them from the list as they are already attached. Whatever databases are already attached need to be excluded. You can do that in the WHERE clause.
I just realized I left in sp_attach_db in the code so I edited that post to remove it. I decided on sp_attach_single_file_db to save me time and not have to deal with the ldfs.
i get the same error as I did the first time I tried running your code before adjusting it and adding the mdf
It's working fine for me. I even created a directory with a dot in it so that I could match your environment.
Show me the exact code you are running and the error.
Hi guys, new here. Was just reading your posts. Came across them in a Google search. Just wanted to let you know that your posts caught my interest enough to sign up. Miss Tara seems to know her stuff! I'm jealous! I recently had to move hosts with our site at work with absolutely no login or User info because our Web Master has been 'unavailable'. I first tried restoring backups to the new server which worked fine except absolutely nothing connected to the database would work afterwards because of permissions and users and the like. Next I tried running the script generator to recreate the database independently. It worked very very well however I still had to recreate the logins. I'm not sure if you can do multiple databases at the same time though. That is a very wierd way to set a system up as Tara has already mentioned. Anyways, thank you both for the interesting thread. I will keep reading.
2 Likes
Here is a copy of what I have entered, as well as a screenshot to show the code along with the error.
declare @path varchar(500), @cmd varchar(1000)
set @path = 'S:\DoNotDelete\LexisNexisMainDB\MSSQL11.LEXISNEXISMAINDB\MSSQL\DATA'
set @cmd = 'dir ' + @path + '*.mdf /B'
create table #t (output varchar(500))
insert into #t
exec master.dbo.xp_cmdshell @cmd
select 'exec sp_attach_single_file_db @dbname = ''' + left(output, charindex('.mdf', output)-1) + ''', @physname = ''' + @path + output + ''''
from #t
where output is not null
drop table #t
p.s. hi simms
It's because you didn't specify the ending slash for @path. This was just quick code and didn't handle that situation.
set @path = 'S:\DoNotDelete\LexisNexisMainDB\MSSQL11.LEXISNEXISMAINDB\MSSQL\DATA'
Wow... My only task was to enter the path correctly..... So this produces all the commands for me to attach all of these databases? I'm assuming I copy paste the message field into a new query and execute
Yes and yes. 
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.