?
Solved

Input line too long

Posted on 2010-03-24
2
Medium Priority
?
1,053 Views
Last Modified: 2012-06-27
Hello,

I am encountering an error while trying to use a stored procedure to execute an SSIS package using the 32bit version of dtexec within a 64 bit environment. I run the following command using xp_cmdshell

SELECT @cmd = '""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe"" /F <path to dtsx file> /SET \Package.Variables[User::FileName].Properties[Value];<unc path> /SET \Package.Variables[User::CreateSQL].Properties[Value];"<sql create table statement>"'

exec master..xp_cmdshell @cmd
(Code is also attached)

And it results in the error "The input line is too long." I have also tried using two qoutes ("") instead of one with the same result.

Whenever I remove spaces from "sql create table statment" the execution succeeds, but the package fails because of malformed SQL. I have also tried not using quotes around the sql create statement but that fails with "Option "table" is not valid." The statements length is only around 317 characters so this is a bit puzzling. So far it seems to have something to do with the double quotes. But I believe they are needed to avoid issues with spaces in the sql.

I could try replacing spaces with underscores and then removing them in the package, but this same command works fine within 32 bit sql 2005 housed on a 32 bit windows server 2003 machine. So i would like to get it working.

Does any know exactly what the issue is?

SELECT @cmd = '""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe"" /F <path to dtsx file> /SET \Package.Variables[User::FileName].Properties[Value];<unc path> /SET \Package.Variables[User::CreateSQL].Properties[Value];"<sql create table statement>"'

exec master..xp_cmdshell @cmd

Open in new window

0
Comment
Question by:bnrtech
2 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 28469566
how have you defines @c,d

and please show us the create table data...


but why can't you just create the table outside of the package?
0
 

Accepted Solution

by:
bnrtech earned 0 total points
ID: 28476243
DECLARE @cmd varchar(500)

Create table tblTest (`id` Long, `text` LongText)

I forgot to mention that the create statement is used by the SSIS package to create columns within a newly made excel file. Thus, creating the table outside the package is not an option because of 64 bit OLEDB driver issues.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question