June 29, 2015 at 9:55 am
Ok so I have some dynamic sql to delete a file that is created via sql earlier on. It is to provision a copy of a database to an instance on link server. Everything works great and the files used to delete. Now, with no code changes it is throwing a syntax error. I do a print of what the dynamic sql is creating before executing and then I copy / paste what was generated into command prompt and guess what!! The file deletes.
Here is the result on screen:
@DeleteBackupFileStatement: DEL \\adas16.clients.advance.local\wip$\AvionteAP_Template_893.bak /Q
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
Here is the code that creates the statement:
SET @DeleteBackupFileStatement = NULL
BEGIN
SET @DeleteBackupFileStatement = 'DEL ' + LTRIM(RTRIM(@BackupFile)) + ' /Q'
END
PRINT '@DeleteBackupFileStatement: ' + cast(@DeleteBackupFileStatement as varchar(400))
BEGIN
EXEC adasdb.master.sys.Sp_executesql
@DeleteBackupFileStatement
END
END
The value of @BackupFile is simply the path of the file with the file name, everything in the prepared statement with the exception of DEL and the switch at the end.
Any ideas?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 29, 2015 at 1:13 pm
is there a possibility that the filename @BackupFile has a space in it?
this would be valid:
DEL \\adas16.clients.advance.local\wip$\AvionteAP_Template_893.bak /Q
but something like would fail:
DEL \\adas16.clients.advance.local\wip$\AvionteAP - Full03-25-2015.bak /Q
can you change your code to dblquote the path?
SET @DeleteBackupFileStatement = 'DEL "' + LTRIM(RTRIM(@BackupFile)) + '" /Q'
Lowell
June 29, 2015 at 1:17 pm
Nope. The statement that my sql creates, if I copy paste it into a command prompt it deletes the file.
Furthermore the same proc actually creates that file to begin with; it is a backup of a db and that value is stored in a variable and simply appended to the path, which is another variable. And I don't know if it is stranger that the same exact thing pasted into a command line WORKS or that it actually worked in this proc until this past Friday.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 29, 2015 at 1:51 pm
I saw the problem and thought of the exact same thing. Because it's giving you the error it's giving you, I think a change in permissions is off the table. I'm going back to the name. Did you try enclosing your path/file in double quotes?
June 29, 2015 at 1:55 pm
No, it worked before... but you know what!! I may as well try that. If it works I am throwing my work station across the room 😉
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 29, 2015 at 2:00 pm
Jeffery Williams (6/29/2015)
No, it worked before... but you know what!! I may as well try that. If it works I am throwing my work station across the room 😉
Don't throw just yet. Domain administrators apply updates to servers from time to time, so even if you made no changes, the environment can still change. Besides, the procedure runs on a server, not your workstation. 😛
June 29, 2015 at 2:15 pm
Right 🙂 It is a dev server so I bounced it; well opened a ticket to bounce let you guys know if that helped.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 29, 2015 at 2:37 pm
Quotes did not work. Bouncing the service did not work. Bouncing the server did not work.
I am on the 4th floor... I wonder if a bounce out the window will help? Just thinking out loud.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 30, 2015 at 8:05 am
Jeffery Williams (6/29/2015)
Quotes did not work. Bouncing the service did not work. Bouncing the server did not work.I am on the 4th floor... I wonder if a bounce out the window will help? Just thinking out loud.
Just for ha-ha's (aka gits and shiggles) -- try changing DEL to it's fully spelled out equivalent: DELETE
Then add quotes to that as well if that doesn't work. Then let us know and we can have a bounce party and we'll toss the server and just hope for a good bounce after it leaves the window... knowing in advance that we'll all celebrate a perfectly wonderful and satisfying "THUD" or "CRUNCH" sound from impact....
just kidding...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 30, 2015 at 8:16 am
Alas I tried that as well already, did not like that at all! LOL.. Again the really courious thing is it was working and now it is not. AND nothing changed... How can I be certain? Well aside from TFS (Source Control) I am the only one with access to the environment and I am pretty darn sure I would know if I made a change.
I will keep looking for SOMETHING that will solve this. Being that I am dealing with one line of code, very very simple code... I can't see where I could be missing something but you never know.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 30, 2015 at 8:33 am
Okay, some of these are reaching, but I have no other ideas to offer.
1. I just noticed in your OP that you're using sp_executesql. If this is a DOS command, you should be using xp_cmdshell. If no changes were made, then this might be an error in posting the question.
2. Is the string variable a varchar or nvarchar? Do you have any weird or non-printing characters in your command that would cause it to fail? This is unlikely if it worked before but not now.
3. Did the network admins install anything between the time when it did work and when it stopped working? I'm talking about Windows Updates or any new software. How about SQL Server patches?
June 30, 2015 at 8:39 am
sp_configure 'show advanced options',1
go
sp_configure 'xp_cmdshell',1
go
reconfigure
go
declare @DeleteBackupFileStatement Nvarchar(400)= NULL,@BackupFile nvarchar(300)= '\\mypath\myfile.docx'
BEGIN
SET @DeleteBackupFileStatement = 'DEL ' + LTRIM(RTRIM(@BackupFile)) +' /Q'
END
PRINT '@DeleteBackupFileStatement: ' + cast(@DeleteBackupFileStatement as Nvarchar(400))
BEGIN
print @DeleteBackupFileStatement
exec xp_cmdshell @DeleteBackupFileStatement
END
June 30, 2015 at 8:40 am
this is a real stretch, but maybe the a nother dba or the network team changed the service account, and that account doesn't have permissions to the share the way the previous account did?
if you run this, is the data blank/null?
DECLARE @Results table(
ID int identity(1,1) NOT NULL,
TheOutput varchar(1000))
insert into @Results (TheOutput)
exec master..xp_cmdshell 'whoami' --nt authority\system for example
insert into @Results (TheOutput)
exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.
select * from @Results
Lowell
June 30, 2015 at 1:38 pm
This is what happens when you get tired. I was calling sp_ExecuteSQL
This is a file system function:
Changed to
adasdb.master..Xp_cmdshell
All is right in the universe.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 30, 2015 at 2:07 pm
Jeffery Williams (6/30/2015)
This is what happens when you get tired. I was calling sp_ExecuteSQLThis is a file system function:
Changed to
adasdb.master..Xp_cmdshell
All is right in the universe.
Excellent. Glad to help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply