sqlcmd question

Question

sqlcmd question

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
June 10, 2008 at 7:01 pm

#186670

I am not sure if this is the correct forum for this but I have a issue with sqlcmd and I can't seem to get it resolved.
I am trying to execute the following sqlcmd and I keep getting a syntax error:
sqlcmd -S HQTST102D\DBA -i Listtables.sql -v server="HQTST102D\DBA" database="Adventureworks"
This is the content of the input file
SELECT name
FROM $(server).$(database).dbo.sysobjects
Where xType = 'U'
GO
This is the error:
C:\Work\LabFiles>sqlcmd -S HQTST102D\DBA -i Listtables.sql -v server="HQTST102D\DBA" database="Adventureworks"
Msg 102, Level 15, State 1, Server HQTST102D\DBA, Line 2
Incorrect syntax near '\'.
Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

I dont think you need the server variable since you are already connected to that instance. That is what is causing the syntax error. You can not put servername\instance in a query.

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 2

alorenzini (6/10/2008)
I am not sure if this is the correct forum for this but I have a issue with sqlcmd and I can't seem to get it resolved.
I am trying to execute the following sqlcmd and I keep getting a syntax error:
sqlcmd -S HQTST102D\DBA -i Listtables.sql -v server="HQTST102D\DBA" database="Adventureworks"
This is the content of the input file
SELECT name
FROM $(server).$(database).dbo.sysobjects
Where xType = 'U'
GO
This is the error:
C:\Work\LabFiles>sqlcmd -S HQTST102D\DBA -i Listtables.sql -v server="HQTST102D\DBA" database="Adventureworks"
Msg 102, Level 15, State 1, Server HQTST102D\DBA, Line 2
Incorrect syntax near '\'.
Any ideas?

You don't need to define explicit scripting variables using -v switch here. SQLCMD contains its own scripting variables such as SQLCMDSERVER for -S, SQLCMDDBNAME for -d and so on (check BOL)

so your SQLCMD should be

sqlcmd -S HQTST102D\DBA -i Listtables.sql database="Adventureworks"

and script Liststables.sql

SELECT cast(name as varchar(30))

FROM [$(SQLCMDSERVER)].$(SQLCMDDBNAME).dbo.sysobjects

Where xType = 'U'

Regards
Shrikant Kulkarni

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

OK, I tried that example and recieved the following error:

sqlcmd -S hqtst102D\DBA -i ListTables.SQL database="Adventureworks"

Sqlcmd: 'database=': Invalid filename.

Then I tried it like this:

sqlcmd -S hqtst102D\DBA -i ListTables.SQL -v database="Adventureworks"

and recieved this error:

Msg 7313, Level 16, State 1, Server HQTST102D\DBA, Line 1

An invalid schema or catalog was specified for the provider "Local Server" for linked server "(null)".

The input file now contains the following script:

SELECT cast(name as varchar(30))

FROM [$(SQLCMDSERVER)].$(SQLCMDDBNAME).dbo.sysobjects

Where xType = 'U'

GO

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 4

alorenzini (6/11/2008)
OK, I tried that example and recieved the following error:
sqlcmd -S hqtst102D\DBA -i ListTables.SQL database="Adventureworks"
Sqlcmd: 'database=': Invalid filename.
Then I tried it like this:
sqlcmd -S hqtst102D\DBA -i ListTables.SQL -v database="Adventureworks"
and recieved this error:
Msg 7313, Level 16, State 1, Server HQTST102D\DBA, Line 1
An invalid schema or catalog was specified for the provider "Local Server" for linked server "(null)".
The input file now contains the following script:
SELECT cast(name as varchar(30))
FROM [$(SQLCMDSERVER)].$(SQLCMDDBNAME).dbo.sysobjects
Where xType = 'U'
GO

ahhh!!! sorry..Not sure from where did I copied database parameter in the SQLCMD...my mistake:w00t:

This should work

sqlcmd -S HQTST102D\DBA -i Listtables.sql -d "Adventureworks"

Regards
Shrikant Kulkarni

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 5

That worked. Thanks a lot.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!