July 22, 2013 at 1:50 pm
Hello,
I have a cursor that I use to alter stored procedures to fix bugs that has always worked for me until today. Below is the cursor:
RECONFIGURE
GO
SET NOCOUNT ON
DECLARE @dbname varchar(128), @SQL varchar(1000)
DECLARE my_cursor CURSOR FOR
SELECT name
FROM sysdatabases
ORDER BY name
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @dbname
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @SQL = 'USE ' + @dbname + '
IF EXISTS (SELECT * FROM sys.procedures WHERE name = ''example'')
BEGIN
EXECUTE xp_cmdshell ''OSQL -U -P -d' + @dbname + ' -iC:\example.sql''
END'
EXECUTE(@SQL)
FETCH NEXT FROM my_cursor INTO @dbname
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
sp_configure 'xp_cmdshell', '0'
RECONFIGURE
Below is the error/s that I am getting:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40> 41> 42> 43> 44> 45> 46> 47> 48> 49> 50> 51> 52> 53> 54> 55> 56> 57> 58> 59> 60> 61> 62> 63> 64> 65> 66>
67> 68> 69> 70> 71> 72> 73> 74> 75> 76> 77> 78> 79> 80> 81> 82> 83> 84> 85> 86> 87> 88> 89> 90> 91> 92> 93> 94> 95> 96> 97> 98> 99> 100> 101> 102> 103> 104> 105> 106> 107> 108> 109> 110> 111> 112> 113> 114> 115> 116> 117> 118> 119> 120> 121> 122> 123> 124
> 125> 126> 127> 128> 129> 130> 131> 132> 133> 134> 135> 136> 137> 138> 139> 140> 141> 142> 143> 144> 145> 146> 147> 148> 149> 150> 151> 152> 153> 154> 155> 156> 157> 158> 159> 160> 161> 162> 163> 164> 165> 166> 167> 168> 169> 170> 171> 172> 173> 174> 175
> 176> 177> 178> 179> 180> 181> 182> 183> 184> 185> 186> 187> 188> 189> 190> 191> 192> 193> 194> 195> 196> 197> 198> 199> 200> 201> 202> 203> 204> 205> 206> 207> 208> 209> 210> 211> 212> 213> 214> 215> 216> 217> 218> 219> 220> 221> 222> 223> 224> 225> 226
> 227> 228> 229> 230> 231> 232> 233> 234> 235> 236> 237> 238> 239> 240> 241> 242> 243> 244> 245> 246> 247> 248> 249> 250> 251> 252> 253> 254> 255> 256> 257> 258> 259> 260> 261> 262> 263> 264> 265> 266> 267> 268> 269> 270> 271> 272> 273> 274> 275> 276> 277
> 278> 279> 280> 281> 282> 283> 284> 285> 286> 287> 288> 289> 290> 291> 292> 293> 294> 295> 296> 297> 298> 299> 300> 301> 302> 303> 304> 305> 306> 307> 308> 309> 310> 311> 312> 313> 314> 315> 316> 317> 318> 319> 320> 321> 322> 323> 324> 325> 326> 327> 328
> 329> 330> 331> 332> 333> 334> 335> 336> 337> 338> 339> 340> 341> 342> 343> 344> 345> 346> 347> 348> 349> 350> 351> 352> 353> 354> 355> Msg 102, Level 15, State 1, Server server, Line 1
Incorrect syntax near '∩'.
Msg 111, Level 15, State 1, Server server, Line 54
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
NULL
I have never seen the ∩ before so not sure what that is about. The SQL file I am running is a standard ALTER PROCEDURE script.
Thanks
July 22, 2013 at 1:56 pm
Most likely whatever is in your .sql is missing a go somewhere.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 2:00 pm
Thanks, I was able to get rid of the Create/Alter error with a GO in the .sql file. I am still getting the strange "Incorrect syntax near 'n'." error. The bizarre thing about both errors is I never had an issue before today.
July 22, 2013 at 2:08 pm
matt-996958 (7/22/2013)
Thanks, I was able to get rid of the Create/Alter error with a GO in the .sql file. I am still getting the strange "Incorrect syntax near 'n'." error. The bizarre thing about both errors is I never had an issue before today.
I don't think the issue is in your code, it is in the file you are executing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 2:13 pm
When I execute the contents of the file on a single DB without the cursor, it alters with no issues.
July 22, 2013 at 2:15 pm
I copied the contents directly from a DB and created a new file and it works. Thanks for all of the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply