SQLCMD syntax problem - 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

General SQL Server Forums

New to SQL Server Programming

SQLCMD syntax problem

Author

Topic

jallah_88
Starting Member

12 Posts

Posted - 2011-02-09 : 05:38:41

sqlcmd -S Servername -d dbname -E -i driveletter:\file.file\sql.commands\testing.sql -o driveletter:\file.file\results.csv -w 999 -s","

Hi guys

I am trying to export an sql file to csv file using SQLCMD. I have come up with this so far, but keep getting error

msg102, Level 15, State 1, Server (servername), Line 1
Incorrect syntax near '-'

Please help me :)

Cheers,
Martin

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-09 : 06:14:43

I think you will need to post the content of the testing.sql file. The error seems to be coming from the database engine...

- Lumbago
My blog-> www.thefirstsql.com

jallah_88
Starting Member

12 Posts

Posted - 2011-02-09 : 08:24:23

Okay.. thanks for the quick reply :) Oh and my intention is to find missing numbers in a column and i found this the easiest way.
csv (collected data) -> sql (do the necessary queries) -> csv (results)

use Monitor

-- Creating permanent table to put csv file data into
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(
N'importcsv') AND type = (N'U'))
DROP TABLE importcsv

CREATE TABLE importcsv
(
action varchar(10),
src varchar(80),
dst varchar(80),
service varchar(80),
fwrule int,
xlatesrc varchar(40)
)

-- Insert csv data to importcsv table
BULK INSERT importcsv FROM 'Driveletter:\file.file\test.csv'
WITH
(
DATAFILETYPE = 'char',
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
MAXERRORS = 1,
ERRORFILE = 'Driveletter:\file.file\Error.txt',
KEEPNULLS
)

-- Table containing numbers from 1-700 used to find missing fw rules
DECLARE @AllRulesTable TABLE (n INT NOT NULL PRIMARY KEY) ;
SET NOCOUNT ON
DECLARE @Index INT ;
SET @Index = 0 ;
WHILE @Index <= 700
BEGIN
INSERT INTO @AllRulesTable(n) VALUES(@Index) ;
SET @Index = @Index + 1 ;
END

-- Table containing present fw rules from csv file, where only one of each
--fw rule is present
DECLARE @PresentRules table (id int)

INSERT INTO @PresentRules (id)
SELECT fwrule
FROM importcsv
WHERE fwrule>0
GROUP BY fwrule
having count(*) > 1
ORDER BY fwrule

if object_id('testing') IS NOT NULL
drop table testing

SELECT A.*
INTO testing
FROM (
SELECT A.n AS I FROM @AllRulesTable A
EXCEPT
SELECT A.id AS I FROM @PresentRules A) AS A

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-09 : 09:08:06

Have you tried to run the code in the .sql-file in a query windows in SSMS? Your sqlcmd-syntax is fine, I just tried it and you should probably add the -W switch as well to trim trailing spaces but the syntax works.

Try to replace the contents of the testing.sql file with "select top 10 * from INFORMATION_SCHEMA.TABLES" instead just to make sure that you can at least get something out to the csv.

- Lumbago
My blog-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources