23 Replies Latest reply on Aug 8, 2008 10:16 AM by emmim44

    cfexcute

    emmim44 Level 1
      The following doesnt copy the data from csv into db table... why? Any help appreciated...My CF and SQL server are on my local machine.
        • 1. Re: cfexcute
          Kronin555 Level 1
          Where is the file "scorecard_30_import_Saves" located?
          • 2. Re: cfexcute
            Daverms Level 3
            Hi,

            Did you encounter any errors?... Have you checked the application logs?...
            • 3. Re: cfexcute
              emmim44 Level 1
              The new error is :
              DTSRun: Loading... Error: -2147217900 (80040E14); Provider Error: 14262 (37B6) Error string: The specified DTS Package ('Name = 'scorecard_30_import_Saves'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0
              • 4. Re: cfexcute
                Kronin555 Level 1
                So I ask again:
                Where is the file "scorecard_30_import_Saves" located?

                This line of code:
                <cfloop index="i" list="Saves">
                loops over a list. However, if "Saves" is a variable containing a list, you need to enclose it in pound signs:
                <cfloop index="i" list="#Saves#">
                Without the pound signs, ColdFusion thinks you're passing the string "Saves" in as the list, which, as a comma-delimited list, only contains one element, "Saves".
                • 5. Re: cfexcute
                  emmim44 Level 1
                  <cfset saves = "Saves">
                  <cfloop index="i" list="#saves#">
                  it is saved on my document folder as 'scorecard_30_import_saves'
                  Still same error...help
                  • 6. Re: cfexcute
                    Kronin555 Level 1
                    Give it a full path to that file. You also need to be sure that the user that ColdFusion is running under can see that file.
                    • 7. Re: cfexcute
                      emmim44 Level 1
                      Still same error:
                      <cfset saves = "Saves">
                      <cfloop index="i" list="#saves#">
                      <cfscript>
                      targ = structNew();

                      targ.Exe = "c:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe";
                      targ.server = "NYCWSITSSALEEM";
                      targ.verBase= "30";
                      </cfscript>

                      <cfexecute name = "#targ.Exe#"
                      arguments = "/S #targ.server# /N C:/Documents and Settings/vozturk/My Documents/scorecard_30_import_saves.dtsx /U cf_user /P 1 /A DateToUse:8=#DateFormat(now(), 'MM/DD/YYYY')#"
                      timeOut = "300"><!--- 5 minutes --->
                      </cfexecute>
                      </cfloop>
                      • 8. Re: cfexcute
                        Kronin555 Level 1
                        You also need to be sure that the user that ColdFusion is running under can see that file.

                        What user is ColdFusion running as? Can that user see that file?
                        • 9. Re: cfexcute
                          emmim44 Level 1
                          cf_user is a SQL 05 admin user....
                          • 10. Re: cfexcute
                            Kronin555 Level 1
                            ....right....
                            but can that user access the file?
                            • 11. Re: cfexcute
                              emmim44 Level 1
                              cf_user doesnt exist on windows environment, it is a SQL 05 power user. As I mention, CF and SQL 05 are located on my local machine...

                              I still dont understand how this thing will work !
                              I looked at the security of that dtsx file ...That user doesnt exist in the list
                              • 12. Re: cfexcute
                                Kronin555 Level 1
                                OK, I guess I need to make myself clearer.

                                When Coldfusion runs a command via cfexecute, it runs as the user that the Coldfusion service is running as.

                                To find that out, open up Task Manager and find the coldfusion process. What username is that process running as? Hint: it can't be a SQL Server user.
                                • 13. Re: cfexcute
                                  emmim44 Level 1
                                  I already checked that.. it runs on 'local system account'
                                  • 14. Re: cfexcute
                                    Kronin555 Level 1
                                    The error you're getting is still this?
                                    DTSRun: Loading... Error: -2147217900 (80040E14); Provider Error: 14262 (37B6) Error string: The specified DTS Package ('Name = 'C:/Documents and Settings/vozturk/My Documents/scorecard_30_import_Saves'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0

                                    Since there's a space in the path to that file, you might need to put quotes around it:
                                    <cfset saves = "Saves">
                                    <cfloop index="i" list="#saves#">
                                    <cfscript>
                                    targ = structNew();

                                    targ.Exe = "c:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe";
                                    targ.server = "NYCWSITSSALEEM";
                                    targ.verBase= "30";
                                    </cfscript>

                                    <cfexecute name = "#targ.Exe#"
                                    arguments = "/S #targ.server# /N 'C:/Documents and Settings/vozturk/My Documents/scorecard_30_import_saves.dtsx' /U cf_user /P 1 /A DateToUse:8=#DateFormat(now(), 'MM/DD/YYYY')#"
                                    timeOut = "300"><!--- 5 minutes --->
                                    </cfexecute>
                                    </cfloop>

                                    Can you run this command from a dos prompt?
                                    C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe /S NYCWSITSSALEEM /N 'C:/Documents and Settings/vozturk/My Documents/scorecard_30_import_saves.dtsx' /U cf_user /P 1 /A DateToUse:8=08/07/2008
                                    • 15. Re: cfexcute
                                      emmim44 Level 1
                                      The run results :
                                      NYCWSITSSALEEM /N 'C:/Documents and Settings/vozturk/My Documents/scorecard_30_import_saves.dtsx' /U cf_user /P 1 /A DateToUse:8=08/07/2008
                                      • 16. Re: cfexcute
                                        emmim44 Level 1
                                        Sorry this is run results:
                                        Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
                                        Error string: The specified DTS Package ('Name = ''C:/Documents and Settings
                                        /vozturk/My Documents/scorecard_30_import_saves.dtsx''; ID.VersionID = {[not sp
                                        ecified]}.{[not specified]}') does not exist.
                                        Error source: Microsoft OLE DB Provider for SQL Server
                                        Help file:
                                        Help context: 0
                                        • 17. Re: cfexcute
                                          Kronin555 Level 1
                                          This will take forever if I have to ask the same question multiple times:
                                          Can you run this command from a DOS prompt?
                                          C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe /S NYCWSITSSALEEM /N 'C:/Documents and Settings/vozturk/My Documents/scorecard_30_import_saves.dtsx' /U cf_user /P 1 /A DateToUse:8=08/07/2008

                                          What is the output of running that command, from the DOS prompt? If it's the same, DTS package does not exist, are you positive you are using the correct filename?
                                          • 18. Re: cfexcute
                                            emmim44 Level 1
                                            'scorecard_30_import_saves.dtsx' file name and location is same as above... Yes I am getting the error I am posting...
                                            Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
                                            Error string: The specified DTS Package ('Name = ''C:/Documents and Settings
                                            /vozturk/My Documents/scorecard_30_import_saves.dtsx''; ID.VersionID = {[not sp
                                            ecified]}.{[not specified]}') does not exist.
                                            Error source: Microsoft OLE DB Provider for SQL Server
                                            Help file:
                                            Help context: 0
                                            • 19. Re: cfexcute
                                              Kronin555 Level 1
                                              Is that the output of running the command from the DOS prompt?
                                              • 20. Re: cfexcute
                                                emmim44 Level 1
                                                YES
                                                • 21. Re: cfexcute
                                                  emmim44 Level 1
                                                  Yes sir
                                                  • 22. Re: cfexcute
                                                    Kronin555 Level 1
                                                    Well then, it has nothing to do with Coldfusion. You need to get that command working in the DOS prompt first. Then, and only then (because you know it's working), you can move it into CF.

                                                    A quick google search yields:
                                                    http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=2947781&SiteID=1
                                                    http://support.microsoft.com/kb/308801
                                                    http://www.mydatabasesupport.com/forums/sqlserver-faq/228365-help-dtsrun-dts-package.html
                                                    http://support.microsoft.com/kb/299354
                                                    http://www.dbforums.com/showthread.php?t=1622934
                                                    http://www.developmentnow.com/g/103_2006_9_0_0_827238/DTSrun-gives-Package-does-not-exist- error.htm

                                                    I'm not sure what will fix your problem at this point, but based on those links, you might want to just try changing /N to /F and seeing if that takes care of it.
                                                    • 23. Re: cfexcute
                                                      emmim44 Level 1
                                                      None of the posted links help.. Any way thank you for the help