SQL query works inside SSMS but not when using osql command in cmd file

Question

Im having trouble getting a certain query to work outside SSMS. The query gathers all the user defined data types and creates IF EXISTS statements and CREATE TYPE commands for easy transferring of all the user-defined data types. The query to do so is as follows (not written by me but found at https://stackoverflow.com/a/12761027/865893)

SET NOCOUNT ON

SELECT 'USE ' + QUOTENAME(DB_NAME(), '[') + '
GO';

SELECT '
IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N''' + st.[name] + ''' AND ss.name = N''' + ss.[name] + ''')
    DROP TYPE ' + QUOTENAME(ss.name, '[') + '.' + QUOTENAME(st.name, '[') + '
GO

CREATE TYPE ' + QUOTENAME(ss.name, '[') + '.' + QUOTENAME(st.name, '[') + ' FROM ' + 
QUOTENAME(bs.[name], '[') + 
    CASE bs.[name]
        WHEN 'char' THEN (CASE ISNULL(st.max_length, 0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE '(' + convert(varchar(10), st.max_length) + ')' END)
        WHEN 'nchar' THEN (CASE ISNULL(st.max_length, 0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE '(' + convert(varchar(10), st.max_length/2) + ')' END)
        WHEN 'varchar' THEN (CASE ISNULL(st.max_length, 0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE '(' + convert(varchar(10), st.max_length) + ')' END)
        WHEN 'nvarchar' THEN (CASE ISNULL(st.max_length, 0) WHEN 0 THEN '' WHEN -1 THEN '(MAX)' ELSE '(' + convert(varchar(10), st.max_length/2) + ')' END)
        WHEN 'numeric' THEN (CASE ISNULL(st.[precision], 0) WHEN 0 THEN '' ELSE '(' + convert(varchar(10), st.[precision]) + ', ' + convert(varchar(10), st.[scale]) + ')' END)
        WHEN 'decimal' THEN (CASE ISNULL(st.[precision], 0) WHEN 0 THEN '' ELSE '(' + convert(varchar(10), st.[precision]) + ', ' + convert(varchar(10), st.[scale]) + ')' END)
        WHEN 'varbinary' THEN (CASE st.max_length WHEN -1 THEN '(max)' ELSE '(' + convert(varchar(10), st.max_length) + ')' END)
        ELSE ''
    END + 
'
GO
'
FROM sys.types st 
    INNER JOIN sys.schemas ss ON st.[schema_id] = ss.[schema_id]
    INNER JOIN sys.types bs ON bs.[user_type_id] = st.[system_type_id]
WHERE st.[is_user_defined] = 1 -- exclude system types
ORDER BY st.[name], ss.[name]

This works like a charm inside SSMS, but id like to not have to go inside SSMS everytime and export this to a file myself. When i run the query through an osql command in a batch file as follows.

@echo off
cls

set /p SName=Server Name :
set /p UName=User Name :
set /p Pwd=Password :
set /p DbName=Database Name :

set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?

if '%choice%'=='y' goto begin
goto end

:begin
if exist _Deploy.txt del _Deploy.txt

@echo on

DEL addUserDefinedTypes.txt
osql -n -S %SName% -d master -U %UName% -i "getTypesSelectQuery.sql" -o addUserDefinedTypes.txt -P %Pwd%

@notepad addUserDefinedTypes.txt

:end

This however gives me the output of:

Msg 105, Level 15, State 1, Server US1524NB, Line 8
Unclosed quotation mark after the character string '
'.
Msg 102, Level 15, State 1, Server US1524NB, Line 8
Incorrect syntax near '
'.
Msg 102, Level 15, State 1, Server US1524NB, Line 2
Incorrect syntax near ' + QUOTENAME(ss.name, '.
Msg 105, Level 15, State 1, Server US1524NB, Line 14
Unclosed quotation mark after the character string '
'.
Msg 105, Level 15, State 1, Server US1524NB, Line 1
Unclosed quotation mark after the character string '
FROM sys.types st 
INNER JOIN sys.schemas ss ON st.[schema_id] = ss.[schema_id]
INNER JOIN sys.types bs ON bs.[user_type_id] = st.[system_type_id]
WHERE st.[is_user_defined] = 1 -- exclude system types
ORDER BY st.[name], ss.[name]
'.

Any clues would be great. Ive looked around google for a while and cant figure out what to change/how to fix this. Removing or closing the quotes like it says breaks the thing. Thanks.

Have you tried to add backslashes `\ ` before every quote `'` in your `getTypesSelectQuery.sql` file? Or maybe replace them with double quotes `"`? — Alex Filipovici, Jan 10 '13 at 21:36
didnt try that out, the sqlcmd (below) worked for me, but thanks anyways — Hub3rt, Jan 10 '13 at 22:01

score 1 · Accepted Answer · answered Jan 10 '13 at 21:29

1

What version of SQL Server are you using?

As of SQL Server 2005, the preferred way to execute SQL scripts from the command line would be SQLCMD instead of osql.

See the MSDN SQL Server Books Online topic on SQLCMD for more details on the available cmd line switches.

answered Jan 10 '13 at 21:29

marc_s

732,580
175
1,330
1,459

GOLDEN! That worked like a charm. Dont know how i overlooked that one. But thanks a million! – Hub3rt Jan 10 '13 at 21:45
2

@Peter: *given enough eyeballs, all problems become shallow* :-) That's the power of community at work :-) Glad I could help – marc_s Jan 10 '13 at 21:46

SQL query works inside SSMS but not when using osql command in cmd file

1 Answers1