MSSql - Retrieve file information with xp_cmdshell

[fap]

I will retrieve the file size & datetime of an file which is located on MSSqlServer (2008).
Somehow it doesn't work the way I would like to....

My Code looks like this:

set sCmd "if object_id('#output') is not null and objectproperty(object_id('#output'), 'IsTable') = 1drop table #output"
set bOk = SqlPrepareAndExecute(hSql, sCmd)
bOk is TRUE


Set sCmd = 'Create Table #output (output varchar(250))'
Set bOk = SqlPrepareAndExecute(hSql, sCmd)
bOk is TRUE

sTemp = 'C:\\TestFile.TXT'
Set sCmd="insert into #output EXEC master.dbo.xp_cmdshell 'dir \"" || sTemp || "\"'"
Set bOk = SqlPrepareAndExecute(hSql, sCmd)

bOk ==> FALSE!!!???

Somebody an idea why the insert doesn't work?

SqlDatabase = 'master'

[Jeff Luther]

Somehow it doesn't work the way I would like to....

Couple of things in reply:
* This msg. should be in a TD section somewhere, depending on your version of TD. This is not a 'general discussion' issue -- it is an issue about TD and a SQL query using SqlPrepareAndExecute().

* "doesn't work" -- how doesn't it work? Do you get a SQL error? If so, what is the error information?
If the Sql function returns FALSE there is -- or should be -- a SQL error somewhere, though this might be a TD error that is caught in the router before the statement is transmitted to the DB because the router, during its parsing of the string, does not understand the syntax.

* You have 3 SQL statements there:
1 - "if ..." -- almost certainly this will fail. That's a conditional and TD/router won't understand the token 'If'

2 - "output " -- ditto, likely the router (or the DB?) don't understand this token

3 - "sTemp = 'C:\\TestFile.TXT'
Set sCmd="insert into #output EXEC master.dbo.xp_cmdshell 'dir \"" || sTemp || "\"'"

That looks like you are trying to send a SQL command for a file on the server-side and again "insert... EXEC..." is likely a syntax that the router won't understand.

I will retrieve the file size & datetime of an file which is located on MSSqlServer (2008)

You might have to do that using a stored procedure and call the SP from within TD.

FYI, for an issue like -- or a problem if you try a SP -- you will need to provide us a complete test case that we can here to test and/or debug.
I am moving this msg. topic to the TD v5.2 section, Fap.

[fap]

I will get SqlError when calling the insert statement (insert into #output EXEC master.dbo.xp_cmdshell....) Error 8180 statement couldn't be executed

You have mentioned that the SqlCommand (...EXEC...) won’t be understood from the router,
but following command is understood be the router:

set SqlCom = "EXEC master..xp_cmdshell 'md \"" || sSaveDir || "\"'"
Call SqlPrepareAndExecute(hSql, sSqCom)

Is "insert" the problem?

[Jeff Luther]

Is "insert" the problem?

Not that I see. You are comparing apples & oranges:
WORKS: "EXEC ..." -- because EXEC is the first token and TD correctly parses this

WRONG: "insert into #output EXEC..." -- because EXEC is not the first token, INSERT INTO is and based on the syntax for INSERT INTO TD expects a VALUES token next and TD does not understand "EXEC" in this context.

[fap]

solved wit ha stored-procedures. Anyway thank's for the hints