Hii Tan,
Really count not get you.May i know how to use -S command ?
When i tried to use sqlcmd i got an error. That s y i has checking with you and need ur support.
Actually i has new for SQL server. My field totally in oracle dba. But based on my work.. i need to get some reports and from sql server. But i already have .SQL image (size is 5GB).
can u pls help on this with more details?
Thanks
sqlcmd example:
sqlcmd -E -S yourservername\instancename -d databasename -i c:\temp\somescriptfile.sql
To see more, type in sqlcmd /? in a cmd window. Or check the sqlcmd documentation online.
Do you have a snippit of the script?
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:
)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)
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?)
Hi,
I want to create new database. I have:
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?