Create tables with Sqlcmd - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Create tables with Sqlcmd

Author

Topic

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-12 : 10:46:18

Hi all.

I'm having some problems with Sqlcmd. I started with it yesterday, and I don't know how to create tables by using existing sql files.

The scenery is:
I have a lot of SQL files with CREATE TABLE scripts. The files are on different directories, and they can't be moved.
How do I use them to create tables on a database that I've created with sqlcmd using the command below:
- sqlcmd -S srv-tsbd\sql2k -i D:\Scripts\create_db_2k.sql

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 11:22:55

What kind of error message are you getting? The sqlcmd itself looks ok, assuming that you are using Windows authentication and that the SQL script has the commands to change to the appropriate database.

If you can post the error messages and the SQL script, people on this forum may be able to offer more useful suggestions.

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-12 : 12:32:51

I think you didn't understand.
On this sqlcmd command that I posted I'm just creating the database. There is no error.

I need to know how do I create tables on the database that I created. And the scripts to create the tables are on different files that CAN'T be moved from there folders.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 13:15:25

If the script to create the tables is in another folder or server, you would run the sqlcmd a second time (or as many times as there are files), but replacing the filename and path with the specifics of the file(s) that contains the create table scripts.

If the files are on a different server, you can use UNC path names in the filename/path. For example:


sqlcmd -S srv-tsbd\sql2k -i D:\Scripts\create_tables.sql

or

sqlcmd -S srv-tsbd\sql2k -i \\anotherServer\D$\Scripts\create_tables.sql

May be that I still not have understood what you are asking. Hopefully someone else will chime in.

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-12 : 13:44:31

But how do I set the script to create the tables on the database that I just created?

Because there are a lot of Databases running on my server.

To let it more clear:
SQL Server - srv-tsbd\sql2k
Database - TS_F_SQL2K_JO1

If I run what you told me to
sqlcmd -S srv-tsbd\sql2k -i D:\Scripts\create_tables.sql
It will create the tables where?

Thanks for your attention.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 13:54:58

Two ways to do it. You probably want to go with the first approach if you can't modify the script files:


sqlcmd -S srv-tsbd\sql2k -d YourDatabaseNameHere -i D:\Scripts\create_tables.sql

The second approach is to insert two lines at the beginning of the script like this:


use YourDatabaseNameHere
GO

If you have both - ie., the command line parameter, and the use statement in the script, the use statement will be the one that will be in force.

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-13 : 07:08:00

Hello sunitabeck. Thanks for your attention.

I used the following command to create table, and it worked fine!
sqlcmd -S srv-tsbd\sql2k -d TS_F_SQL2K_JO1 -U homol -P tssql2k -i D:\Scripts\Scripts_Criacao_Banco\01-LGComponentes\SQLServer\1.LGComponentes_CREATE_SQLSERVER.sql

Now I'll create a script to pass through all my folders and use all the scripts that I've got.

Again, thanks for your help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 07:38:54

If you were able to create the database with Windows Authentication (i.e., no user name and password supplied), you should have been able to do the creation of tables also with windows authentication, unless there was something really unusual about the permissions set up.

Regardless, glad it worked out!

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-13 : 08:01:30

Another problem. Hope there is no problem using the same topic.

How do I ignore blank spaces?

I need to use this: -i D:\SQL Server\Script.sql

Is there anyway to ignore the blank space between "SQL" and "Server" or I'll have to rename the folder?

Ps.: a lot of folders

Here is what happens:

quote:
Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\joffre.mota>sqlcmd -S srv-tsbd\sql2k -d TS_F_SQL2K_JO1
-U homol -P tssql2k -i D:\Scripts\Scripts_Criacao_Banco\01-LGComponentes\SQL Se
rver\1.LGComponentes_CREATE_SQLSERVER.sql
Sqlcmd: 'D:\Scripts\Scripts_Criacao_Banco\01-LGComponentes\SQL': Invalid filename.

C:\Documents and Settings\joffre.mota>

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 08:17:18

You can escape using double quotes as in:


sqlcmd -S srv-tsbd\sql2k 
   -d TS_F_SQL2K_JO1
   -U homol 
   -P tssql2k 
   -i "D:\Scripts\Scripts_Criacao_Banco\01-LGComponentes\SQL Server\1.LGComponentes_CREATE_SQLSERVER.sql"

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-13 : 08:46:26

It worked. I checked on SQL Management, and the tables were created. Thanks.

quote:

C:\>sqlcmd -S srv-tsbd\sql2k -d TS_F_SQL2K_JO1 -U homol -P tssql2k -i "D:\Script
s\Scripts_Criacao_Banco\01-LGComponentes\SQL Server\1.LGComponentes_Create_SQL S
ERVER.sql"

C:\>

But everything that I try to do using more than one line returns an error.
If I create the database using just one line, it works. When I try to use more than one line, it doesn't works. Take a look at the quotes..
Working:

quote:
C:\>sqlcmd -S srv-tsbd\sql2k -i D:\Scripts\create_db_2k.sql
The CREATE DATABASE process is allocating 1.00 MB on disk 'TS_F_SQL2K_JO1'.
The CREATE DATABASE process is allocating 1.00 MB on disk 'TS_F_SQL2K_JO1_log'.
DBCC execution completed. If DBCC printed error messages, contact your system ad
ministrator.
Changed database context to 'TS_F_SQL2K_JO1'.

C:\>

Doesn't work:

quote:
C:\>sqlcmd -S srv-tsbd\sql2k
1> -i D:\Scripts\create_db_2k.sql
2> Go
Msg 170, Level 15, State 1, Server SRV-TSBD\SQL2K, Line 1
Line 1: Incorrect syntax near '-'.
1>

Am I doing something wrong?
But what I need is working fine.
Thanks a lot.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 09:16:48

In interactive mode, you can use only sqlcmd commands (those that start with colon (:)) or t-sql commands. The -i option is specifically for batch commands, where you have all your t-sql scripts in a file. Take a look at these pages:
http://msdn.microsoft.com/en-us/library/ms162773.aspx
http://msdn.microsoft.com/en-us/library/ms188714.aspx
http://msdn.microsoft.com/en-us/library/ms180944.aspx

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-26 : 13:21:09

Hi again. My scripts are creating the tables but the log isn't being generated. Just the Create and Alter Table scripts. My Insert/Drop/Delete/Update scripts are creating log files normally. Any idea about why it isn't working?

Here is an example of command that I'm giving:

sqlcmd -S srv-tsbd\sql2k -d TS_F_SQL2K_20112 -U SA -P sqlts2k -i D:\Scripts\Scripts_Criacao_Banco_2k\7.TG\SQLServer\2.sql -o D:\Scripts\Logs2k\7-2.txt

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-27 : 07:24:11

Anybody?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 07:57:21

Could it be because if the operation is successful, no message is sent back? I looked at the MSDN page for sqlcmd (http://msdn.microsoft.com/en-us/library/ms162773.aspx) and there does not seem to be any "verbose" option. If you run the following two lines of code, for example, it does write to the log file. But if I don't have the select statement in the first line that creates the script file, the log file is empty.


echo create table dbo.ATestTable (id int); select 1; > test.sql
sqlcmd -S (local) -d MyDB -i test.sql -o test.log

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-27 : 08:14:10

So it is normal that the Log File is empty when Create/Alter Table is successful?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 08:24:25

That is the behavior I am observing. I have never before tried to run queries that create/drop/alter tables from SQLCMD. In my tests, the tables seem to be created correctly, and no messages sent back. If there is an error, for example, if the table already exists, I do get the error message in the log file.

Joffre Mota
Starting Member

10 Posts

Posted - 2011-05-27 : 09:42:38

Hey sunitabeck,

I was expecting that log would save what Query Analyzer brings back to me when I create a table. On that case, when some CREATE command is completed, the query returns the message "The command(s) completed successfully".
Any idea about why it doesn't happens when using sqlcmd?

Thanks for your attention.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 12:28:18

I also would have expected the message of "command completed successfully" to be displayed. There may be some option or setting that would result in more verbose messages - but I couldn't find any when I quickly looked through the MSDN page.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources