Hi,
I managed to enter into SQLCMD mode then i tried to run my .sql file.
But i got an error. screen shot attached for your reference.
Pls help. how to run my file. my file located in c drive only.
It's because you hit enter before you typed the whole command. What I posted needs to be on a single line. If you see "1>" that means you are now connected to the instance and can't complete your sqlcmd command. You could start typing SQL commands there, but that is not your intention. Your intention is to run a .sql file.
Here's my summary of the situation:
- You have a HUGE .SQL file, which you want to execute
- You have installed SQL Express - presumably you have not created any Database(s) so it is an "empty" install
- You have no knowledge about how to use MS SQL on your machine, nor how to install it, nor how to use any tools to work with SQL. (Not a criticism, we all started somewhere
) - You have not provided any information on what the SQL file contains, so we are guessing as to what you are trying to achieve.
In order for the SQL file to execute it will have to:
-
Create a database. Provided that is the very first command in the file that will be fine ... but ... a Create Database command usually specifies the Drive / Folder in which the database should be created etc. and that may not be valid on your machine.
-
Create Tables and Insert Rows into the tables.
If the script does anything else (e.g. assume a database already exists, and is either the currently connected database OR the script expects the database to have a SPECIFIC name) it won't work. Also if the script attempts to do any other actions - e.g. SELECT data from a table (before it has INSERTed any, or instead of inserting any) it won't work.
Given that your knowledge of MS SQL and Tools is very limited I think it extremely unlikely that this route will work because almost certainly some alteration of teh SQL file will be needed in order to create the database, in the appropriate folder(s), etc. The person who provided teh SQL script file to you could help with all those issues, as they will know what the purpose of the script is and what "Settings" it contains. Please correct me if I have made any assumptions incorrectly.
If the SQL File you have is supposed to contain "data" (that is to say that it should INSERT each row of data into a Table, in the SQL database) then I think it would be better that you obtain that data by a different method from the person who provided you with the SQL script. (I will make a suggestion below)
Are you sure that the SQL script is for Microsoft SQL? IME it is very unusual [for people using MS SQL] to exchange data using a SQL Script. However, it is quite common for people using MySQL - is it possible that the script was for MySQL instead? if so it is extremely unlikely that the script will work with MS SQL [without major modification]
IF the data did come from an MS SQL database my suggestion would be that you ask for a BACKUP FILE, of the database, instead of the SQL script. You need to be sure that the version of SQL that the other person is using will be compatible with the version you are using. It is NOT possible to take a backup from "any" version of MS SQL and restore onto "any other version". Usually it is possible to restore a backup if the target version is the same major version, or up to TWO versions newer than the Source machine's SQL version.
The RESTORE process will let you indicate which Drive and Folder you want to restore the backup file to. It is NOT necessary for any database to exist on your machine, RESTORE is able to create a new database, so that would work well for your current situation.
One thing to be aware of, given the size of your SQL Script file, is that SQL Express has a size limit on the database. It is quite generous, but if the person sending you a Backup File is using the full version of SQL and if their Database File is bigger than the SQL Express limit, then you will not be able to restore the file. The sender may be able to "shrink" their database, and thus may be able to make it small enough that it is smaller than the SQL Express size limit (but it is not best practice to Shrink a Production database)
1 Like
Hi Tara,
Yes you are right. my intention is to run the .sql file by using sqlcmd. But according to your knowledge, i could not completed my command. Means that,may i know how to write query or how to execute the .sql file from here.
once connected with sqlcmd goes to
1>
then
i entered command as:
1> sqlcmd -S myServer\instanceName -i C:\myScript.sql
means my exact command:
1> sqlcmd -S LPCSI0046\SQLEXPRESS -I C:\test.sql
After that i got an error instead of output.
Can you pls kindly assist on this.
Thank you.
Hi Tara,
Yes you are right. my intention is to run the .sql file by using sqlcmd. But according to your knowledge, i could not completed my command. Means that,may i know how to write query or how to execute the .sql file from here.
once connected with sqlcmd goes to
1>
then
i entered command as:
1> sqlcmd -S myServer\instanceName -i C:\myScript.sql
means my exact command:
1> sqlcmd -S LPCSI0046\SQLEXPRESS -I C:\test.sql
2> go
After that i got an error instead of output.
Can you pls kindly assist on this.
Thank you.
You need to exit out of that as you can't type the sqlcmd once you are at the 1> 2> prompts. Type exit and hit enter to get back to the cmd prompt. It should say some variation of C:\yada yada yada>
Now type in the sqlcmd command but do not hit enter until the very end of the command. See my sqlcmd example above for the entire command. If you get 1>, then you didn't do it right.
Actually what i did will tell you, after that pls give me a suggestion. Then will rectify
Actual
c:\users\703803> sqlcmd -S
1> sqlcmd -S -i C:<myfile>.sql
2> go
got an error:
Msg 102, Level 15, state 1, Server , Line 1
Incorrect syntax near 'S'.
1>
Give me a suggestion what i need to do. If not my file has located in c drive.
how to exactly i can execute and get result in excel.Ii really need your kind support.
Pls give me a exact query or sample template for my reference.
I m really new for sqlcmd. i do not have an any idea.
Really appreciate your support.
Thanks
Tara did that for you already here: http://forums.sqlteam.com/t/download-ms-sql-server-management-studio-express/4708/35
Please see my earlier post http://forums.sqlteam.com/t/download-ms-sql-server-management-studio-express/4708/32
Why are you hitting enter after the "-S"? You need to type the ENTIRE command on the same line. Your screen should look like this BEFORE you hit enter:
c:\users\703803> sqlcmd -S LPCSI0046\SQLEXPRESS -i C:\test.sql
Do NOT type this command after 1>, 2>, etc. It MUST be on the c:\users\703803> line.
Hi,
Thank for your info. Perfect now i can able to run my file. But i would like to extract data to excel file.
May i know what commend i have to use for extract to excel. attached my screen shot for your reference.
Like in our SQL>prompt we will use like spool.
which command have to use for sqlcmd?
Can u pls advise on this.
Thanks
Hi Expert,
I would like to re-create new database in MS SQL by using .sql file.
My .sql file size has 5GB. May i know how to create new database and what are the steps i need to follow.
can you pls advise on this.
Thanks.
You have to know the name of the Table(s) that were created / inserted into your database by the script.
Presumably your SQL script already did that, otherwise you would have seen errors when the script ran (but it appears from your screen shot that you have "1 rows affected" messages, which probably means that a row was inserted into a database's table.
You probably were not able to see any error messages during the running of the script ... so you won't know if all rows were successfully inserted. However, the fact that the script finished showing all "1 rows affected" messages is a good sign - the script most likely did create a database.
Start your SQL tool and use this command to view the database names:
SELECT name
FROM sys.databases
ORDER BY name
GO
ignore "master", "tempdb", "model" and "msdb". Hopefully there is just one other name, and that will be your database. I'll call it "YourDatabaseName"
Then you can "connect" to that database with:
USE YourDatabaseName
GO
Once you are connected to the right database you can see what schema and tables it contains:
SELECT [TableName] = S.name + '.' + T.name
FROM sys.tables AS T
JOIN sys.schemas AS S
ON S.schema_id = T.schema_id
ORDER BY [TableName]
GO
If you see a schema / table that you like the look of type:
SELECT TOP 100 *
FROM dbo.YourTableName
GO
which will show you the first 100 rows (i.e. as s sample)
Once you have decided on which tables you are interested in then you can export them to Excel. Probably best to use the SSIS tool for that if you have that installed? Other people here may have suggests on how you can best export your database to Excel, given that you have zero knowledge of the tools available and how to use them it would clearly help if you have the "easiest" method, starting from that base.
NOTE: I presume you were not able to see any error messages when you ran your script, as they would have scroll up / off the screen very quickly. Thus you should review the data (once you have exported it to Excel) to make sure nothing is missing.
You can also do:
SELECT COUNT(*)
FROM dbo.YourTableName
GO
which will tell you how many rows are in the table. You could use that to check that you have exactly the right number (compared to the original database that your script was created from)
Also beware that your script was very big, and it might be that there are too many rows in the table to be able to export to Excel. (I've forgotten what the size limit is in Excel, it used to be 32K, maybe it is "huge" now?)
1 Like
Hi,
I want to create new database. I have:
- .sql file (image size 5GB).
- Installed microsoft sql server
how to create new database, what are the requirements needed for this, except above 2.
Pls advise me asap.
Really appreciation for your kind assistance.
Hi,
If i use the link: https://msdn.microsoft.com/en-sg/library/ms186312.aspx
i can`t able to create new database and also im not able to import my .sql file.
For another method:
When i tried to import .sqlfile to toad and tried to create new database, get an error as:
Out of memory.
May i know how to import through toad or Microsoft SQL management studio?
Please assist on this.
Why you can't create new database ? How are you doing it ? Using Mangement Studio or Transact SQL ?
Any error message ?
Post the SQL code. Or are you the one with the HUGE sql file?
Hi,
Yes. I have the huge .SQL file. But i would like to create database using that huge .sql file in microsoft sql server management studio.
Pls assist on this.
Thank you.
You haven't replied to either of the messages I posted to help you earlier. Please do so.
Your previous attempt appeared to work using SQLCMD - but as you did not save the output to a file, if there were any errors you won't have been able to see them as they scrolled up the screen and you can't check the file, retrospectively, to check for any errors. (In any event the output would have been huge, so trying to find any errors in such a file would be a "challenge")
I think it is very unlikely that, without more knowledge about MS SQL than you have, that you will be able to "just use" the SQL script that you have. Therefore I suggest that you either ask the person who sent you the SQL script to, instead, send you a Full Backup File (which it will be easy for people here to help you restore onto your computer), or you ask someone with more MS SQL knowledge to modify your script so that it will run safely on your machine and create the database etc.
The reason I say this is that it is likely that your script uses a specifically named database, and creates it on a specifically named drive in specifically named folders (all/any of which may not exist on your computer). There are a whole host of similar things which could go wrong which someone with a bit of MS SQL knowledge will easily be able to solve but if you do it on your own, with the help of a forum like this, it will take "for ever" and may well destroy something useful (e.g. by overwriting it, or deleting it) on your computer.
Links to my earlier posts:
forums.sqlteam.com/t/download-ms-sql-server-management-studio-express/4708/41
forums.sqlteam.com/t/download-ms-sql-server-management-studio-express/4708/32
1 Like
Hi All,
Thank you for your everyones kind support. But i did not get the info.what im expected.
Actually i would like to create new database in microsoft sql server management studio 214 using .sql file (its size was 5.89GB).
I need to know the proper steps and procedures..If i import this file to MSSQL server management studio getting error, if not I used the below script:
USE master ;
GO
CREATE DATABASE maximo_2
ON
( NAME = Sales_dat,
FILENAME = 'c:\testtdatt.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = maximo_log,
FILENAME = 'c:\testtlogg.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
comment executed successfully.But i could not able to view my database, .mdf and .ldf file could not be executed too.
Im really not too sure how to get my new database in proper manner.
Dear Experts please kindly give me a suggestion. Really need your great help.
Thanks.