March 5, 2016 at 5:28 am
Hi there,
I am new to SQL Server I am trying to create a new database in SQL Server via cmd on my laptop. However I am getting the message invalid filename. I am using sql server express 2014. The code I have written is:-
C:\Joes2Pros>SQLCMD -S MSI\sqlexpress -E -iCreateDBMovie.sql
I am not sure why this keeps happening as the folder Joes2Pros exists. Any help would be much appreciated.
Regards,
James Elwell
March 5, 2016 at 6:39 am
A frequent confusion for people who are starting to learn SQL Server is to think about what happens from their own perspective. That is not the case. From your perspective, you are submitting commands to a service that might as well be running on a different server. So they reference a folder on their own computer and expect SQL Server to find it - but it doesn't because that folder does not exist on the computer that SQL Server runs on.
In your case, I think (reading between the lines of your post) that the SQL Server service is actually installed and running on your own laptop, so the above does not apply. However, there is a second thing. SQL Server runs as a service on the operating system, and in order to do so has logged on as a service using a "service account" - a special Windows account that can be used to log on as a service but not to log on as a user, but that otherwise is very similar to user accounts. When you ask SQL Server to do something, it will be done in the security context of that account. So if (as I am currently assuming) you request SQL Server to create a database at a specific location on the file system, and that location is not the default location for creating databases, then the SQL Server service account will need to have permissions to create files in that location.
Now the above is a bit speculative, since I have no idea what you are actually asking SQL Server to do. The command you post has some elements - first SQLCMD, which is a tool for sending commands to SQL Server. (For interactive work SQL Server Management Studio is the prefered tool; sqlcmd.exe is usually used to execute commands stored in a script).
Second, the command-line parameters tell it to connect to a SQL Server service called sqlexpress and installed on a server called MSI; I assume that's your laptop. Third, the last parameter tells sqlcmd.exe to read the script stored in a file CreateDBMovie.sql in the current directory, and send the contents of that script of SQL commands to SQL Server.
Since sqlcmd.exe runs on your local laptop under your own permissions, this is not where the error happens. The C:\Joes2Pros> prompt before the command shows that you are in that directory, and I assume that you already checked that a file named CreateDBMovie.sql exists in that same directory. It will be read (using the permissions of your own account) and sent to SQL Server. I expect no problems there.
The actual problem is caused by something IN the script. I assume, based on the script name, that the script creates a new database. And based on the error you see, I assume that the statement to create the database overrides the default file location for databases to something else. And that's where the above starts to apply, because now the SQL Server service account tries to access that location and gets an error message that tells it that the requested location either doesn't exist or is not permitted.
Does the above make sense? I realize it's a lot to take in when you are just starting and I don't understand why a book that is targeted at beginners would supply a script that requires you to set up permissions instead of just allocating the demo database at the default location.
If you need further help, then you should perhaps attach a copy of the CreateDBMovie.sql script here - allthough I am not sure if I should really ask that; I happen to know that Joes2Pros is a book publisher and I do not want to ask you to commit pircay by posting copyrighted material on a public forum.
March 5, 2016 at 8:15 am
Thank you so much I have got everything to work through the cmd window now. Basically i didn't understand that the '-i CreateDBMovie' was referring to a script. I thought the code would just create a new database call 'DBMovie'. I therefore wrote a script, saved it in the appropriate directory and then referred to it through the command and it worked!
July 8, 2016 at 3:10 am
Kudos, Thanks a lot Hugo. Great help.:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply