problem with insert into in scheduled job pleas help ASAP

Hello MSDN

I am using SQL 2005 and trying to INSTERT data in to a table

When I am using my command from SQL query windows it works fine,

INSERT INTO "tbl.FTPuploads" ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")

SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

But when I am trying to put that command in to a scheduled job I get this error

Executed as user: GW\fmnlasa. Incorrect syntax near 'tbl.FTPuploads'. [SQLSTATE 42000] (Error 102).The step failed.

I have changed the command to this, I have removed the quotes from the table name.

INSERT INTO tbl.FTPuploads ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")

SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

And now I get this error

Executed as user: GW\fmnlasa. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).The step failed.

When you execute from the query, it will use your credential to connect the remote server. But the scheduled query might use the ServiceAccount. Check which service account used in the current context & give the required permission on the remote server. Or change the service account context to NT Credential.|||

Bascially the issues is of Security Context under which the Job run. Check these links

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012759&SiteID=1

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/44111/Access-to-the-remote-server-is-denied-because-the-current-security

Madhu

|||

Thank you, for such a quick reply

I was using the same account in Job to as I was using in Query window

I am not sure but I think that the problem was in my table and column names

I have created new table with the name tblFTPuploads the old one was tbl.FTPuploads

After that I got erros for may column names so I have changed the first column name to FTPFileNames the old one was FTPfile_name

And the last step I did was to remove the quotes from the table and column names.

So the end script is like this

INSERT INTO tblFTPuploads (FTPFileNames, FTPGS, FTPST, FTPJOB, FTPDN, FTPSTATUS, FTPDATE, FTPTIME)

SELECT FTPFileNames, FTPGS, FTPST, FTPJOB, FTPDN, FTPSTATUS, FTPDATE, FTPTIME

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

And it is working now no problems.

Thanks again for a quick reply

Best regards,

Artavazd ASLANYAN

Network Administrator