Hi Tan,
i could not execute whole .sql file using sqlcmd. since i m not too sure the correct query to write.
Any way i tried to execute through SQL server profiler, can able to run the program.. but I`m not able to view the exact output.
Can you pls assist..how to execute and how to get outputlarge .sql file through sql server profiler.
Note: i wanted the output in excel format.
Your assistance would be much appreciated.
not sure what / how do you with profiler. But that is not the right tool to execute a sql script.
Do you have any idea what the sql script is doing ?
Where did you get the sql script from ? Probably you should refer to to the source of sql script.
In management studio, go to tools, select sql server profiler.
In which to select the .sql file to open. But i could not able to get output here.
Any way how we can to exeute the large .sql file and need to get output from sql server managemnt studio or from sql server profiler.
Which tool we are using is not an issue..bt i nee the output in excel format.
This is what i need it.
Pls help.
sqlcmd -S myServer\instanceName -i C:\myScript.sql
reference
Using the sqlcmd Utility (SQL Server Express)
Lesson 2: Running Transact-SQL Script Files by Using sqlcmd
Btw may i know how to open sqlcmd.
When i tried to open sqlcmd in my command prompt i got an error
Attached screen shot for your reference.
how to open sqlcmd apart of this issue.
Pls help
.
use the -S command switch to specify your server name
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.
