ExecSQL Task Truncates Ouput Parameter

I am successfully passing several parameters into a SP using the ExecSQL Task. I have a single parameter that is an OUTPUT parameter in the SP.

I always recieve the first character of the OUTPUT string. I have tried chaning the data type of the parameter mapping [AnsiString, String, etc.] and I have tried changing the data type of the OUTPUT parameter [varchar, char, nvarchar, nchar]

In each case, only the frist character of the output value is returned and subsequently passed to the next SSIS component / task.

Any ideas why this value is truncated?It is a String.

One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.

John|||

jloper wrote:

One of the previous replies indicated that there is a max lenght on the variable, but I do not see where to set this length. Any further information on the issue is appreciated.

John

Ignore that, I was wrong. Sorry!

Note to self: Check out your answers before posting them!

-Jamie

exec(select...), how supress the output?

Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)

(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow

Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?

EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)

> (2) Can I use the result of this query in another query somehow? e.g.
> select

INSERT #tmp (...)
EXEC('...')

> (3) Can I control the size of the columns in the output somehow

This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.

But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)

worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway

> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')

sorry, don't understand this? Can you elaborate?

> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.

create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable

I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?

You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.

>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser

I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.

But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

exec sp with output parameter

I have the following sp:
ALTER PROCEDURE myspPrepareTextForHTML
@.MessageContent nvarchar(1400),
@.returnval nvarchar(1400) output
AS
BEGIN
SET NOCOUNT ON;
SET @.returnval='something'
RETURN @.returnval
END

When I try this:

EXEC myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnvalOUTPUT

print @.returnval

I get the error:
Must declare the scalar variable "@.returnval".

How can I get this to work?

You need to set the value of an output parameter. In this case, you jus want to pass in your current variable.

Just like other parameters, the left side of = means the name of the parameter, right side means the value, or return value (return variable).

EXEC myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnval = @.returnvalOUTPUT

|||now I get the error:

Msg 245, Level 16, State 1, Procedure myspPrepareTextForHTML, Line 17

Conversion failed when converting the nvarchar value 'something' to data type int.

|||

Peter Smith:

now I get the error:

Msg 245, Level 16, State 1, Procedure myspPrepareTextForHTML, Line 17

Conversion failed when converting the nvarchar value 'something' to data type int.

Most likely, the calling proc or SQL statement declaredits@.returnval as an int, rather than an nvarchar of the same size as declared in the myspPrepareTextForHTML proc.

|||This is everything I have so far...I dont see why I still get that int error:

ALTER

PROCEDURE [dbo].[myspPrepareTextForHTML]

@.MessageContent

nvarchar(1400),

@.returnval

nvarchar(1400)output

AS

BEGIN

SETNOCOUNTON;SET @.returnval='something'RETURN @.returnval

END

declare

@.returnvalnvarchar(1400)

EXEC

myspPrepareTextForHTML @.MessageContent='dfgsdfgdf', @.returnval= @.returnvalOUTPUT

print

@.returnval|||Because anything you return with RETURN can only be of INT type. So SQL Server is trying to convert the 'something' to int. If you are returning the value through the OUTPUT parameter you dont need to use the RETURN statement. So remove theRETURN @.returnval statement in your proc and you should be fine.|||

Sorry, I missed the fact that you are trying to return an output value. Any value returned with the RETURN statement must be an int. This is why there are OUTPUT variables, which allow us to essentially 'return' values of any type (like using reference/ByRef variables in .NET).

Change your code to the following, and save the RETURN statement to return something similar to an error code:

ALTER PROCEDURE [dbo].[myspPrepareTextForHTML] @.MessageContentnvarchar(1400), @.returnvalnvarchar(1400)output ASBEGIN SET NOCOUNT ON;SET @.returnval='something'RETURN 0-- no errorEND

|||like a charm!

EXEC @SQLString with Output Results

Hello, I have been working around this issue, but couldn't yet find any solution.
I have a stored procedure that calls a method to do a certain repetitive work.
In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.
for example, there is an input for a function called "Id"
Inside the function,
if Id = 111
I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.
Now, inside the function, I need to return a value by executing the above @.SQLString as follows:
EXEC @.SQLString
When I need is something like
EXEC @.SQLString, @.Total Output
Return (@.Total)
Are there any ideas ?
regardsProblem Solved
regardssql

Exception

I start a job (the job successfully executes) but I notice in a trace that an
Exception occurs when the job starts. This is the output for the SQL:
BatchCompleted. Any idea why this would throw an exception? It appears as
though it is all SQL Server generated code.
create table #tmp_sp_help_category
(category_id int null, category_type tinyint null, name nvarchar(128) null)
insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
SELECT
sv.name AS [Name],
CAST(sv.enabled AS bit) AS [IsEnabled],
tshc.name AS [Category],
null AS [CurrentRunStatus],
null AS [CurrentRunStep],
null AS [HasSchedule],
null AS [HasStep],
null AS [HasServer],
null AS [LastRunDate],
null AS [NextRunDate],
null AS [LastRunOutcome],
CAST(sv.job_id AS nvarchar(100)) AS [job_id]
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id = tshc.category_id
WHERE
(sv.name=N'Trace Duration Job 1')
drop table #tmp_sp_help_category
--
Message posted via http://www.sqlmonster.comHi
You may want to look at batch/statement starting to find what is causing
this rather than what has completed.
John
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5eeb1211a50ab@.uwe...
>I start a job (the job successfully executes) but I notice in a trace that
>an
> Exception occurs when the job starts. This is the output for the SQL:
> BatchCompleted. Any idea why this would throw an exception? It appears as
> though it is all SQL Server generated code.
>
> create table #tmp_sp_help_category
> (category_id int null, category_type tinyint null, name nvarchar(128)
> null)
> insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
> SELECT
> sv.name AS [Name],
> CAST(sv.enabled AS bit) AS [IsEnabled],
> tshc.name AS [Category],
> null AS [CurrentRunStatus],
> null AS [CurrentRunStep],
> null AS [HasSchedule],
> null AS [HasStep],
> null AS [HasServer],
> null AS [LastRunDate],
> null AS [NextRunDate],
> null AS [LastRunOutcome],
> CAST(sv.job_id AS nvarchar(100)) AS [job_id]
> FROM
> msdb.dbo.sysjobs_view AS sv
> INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id => tshc.category_id
> WHERE
> (sv.name=N'Trace Duration Job 1')
> drop table #tmp_sp_help_category
> --
> Message posted via http://www.sqlmonster.com

Exception

I start a job (the job successfully executes) but I notice in a trace that a
n
Exception occurs when the job starts. This is the output for the SQL:
BatchCompleted. Any idea why this would throw an exception? It appears as
though it is all SQL Server generated code.
create table #tmp_sp_help_category
(category_id int null, category_type tinyint null, name nvarchar(128) null)
insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
SELECT
sv.name AS [Name],
CAST(sv.enabled AS bit) AS [IsEnabled],
tshc.name AS [Category],
null AS [CurrentRunStatus],
null AS [CurrentRunStep],
null AS [HasSchedule],
null AS [HasStep],
null AS [HasServer],
null AS [LastRunDate],
null AS [NextRunDate],
null AS [LastRunOutcome],
CAST(sv.job_id AS nvarchar(100)) AS [job_id]
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id = tshc.category_i
d
WHERE
(sv.name=N'Trace Duration Job 1')
drop table #tmp_sp_help_category
Message posted via http://www.droptable.comHi
You may want to look at batch/statement starting to find what is causing
this rather than what has completed.
John
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5eeb1211a50ab@.uwe...
>I start a job (the job successfully executes) but I notice in a trace that
>an
> Exception occurs when the job starts. This is the output for the SQL:
> BatchCompleted. Any idea why this would throw an exception? It appears as
> though it is all SQL Server generated code.
>
> create table #tmp_sp_help_category
> (category_id int null, category_type tinyint null, name nvarchar(128)
> null)
> insert into #tmp_sp_help_category exec msdb.dbo.sp_help_category
> SELECT
> sv.name AS [Name],
> CAST(sv.enabled AS bit) AS [IsEnabled],
> tshc.name AS [Category],
> null AS [CurrentRunStatus],
> null AS [CurrentRunStep],
> null AS [HasSchedule],
> null AS [HasStep],
> null AS [HasServer],
> null AS [LastRunDate],
> null AS [NextRunDate],
> null AS [LastRunOutcome],
> CAST(sv.job_id AS nvarchar(100)) AS [job_id]
> FROM
> msdb.dbo.sysjobs_view AS sv
> INNER JOIN #tmp_sp_help_category AS tshc ON sv.category_id =
> tshc.category_id
> WHERE
> (sv.name=N'Trace Duration Job 1')
> drop table #tmp_sp_help_category
> --
> Message posted via http://www.droptable.com

Excel task has no output columns

I created an Excel Source and used a query to get the data,i.e

SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]

The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?

Here is some information that may answer your question:

1. In the Excel Source Editor, navigate to the Columns page.

You can modify the output column names that are listed in the External Column/Output Column table by clicking in the field and typing a new name.|||

Hi,

The problem is there's no output columns listed, and you cannot manually add them. The problem is when I specify a query instead of an entire worksheet, the External Column meta-data is not added like it is when you select a worksheet by name.

Dave

|||

Hi,

Have you tried naming the columns in your excel source as some meaningful names and then try to connect your excel source. It will give you all the names ,after that you can select the ones you need and dont tick the checkboxes of the ones you dont need.

Regards,

Vikram Kansal

Excel Output Missing Last row

My excel outout is missing the last row of the recordset. HTML, PDF, and the
other outputs are ok. This is a simple report with no grouping. Please help.Derek,
Have you tried to add a blank footer row to the table?
This way if the bug does occur it will just drop a blank footer and not your
data. (I know this is idea is just a band-aid not a fix but MS seems to be
ignoring this problem.)
I have not tested this theory myself because I have only seen this happen a
few times and each time I tried to dup the results everything worked fine.
Do you have a report that has a regular problem that you could test my theory
for me?
Tim
"Derek Howard" wrote:
> My excel outout is missing the last row of the recordset. HTML, PDF, and the
> other outputs are ok. This is a simple report with no grouping. Please help.

Excel Output

I want to run a stored procedure which will run a specific query and put out
a monthly file each month. The name of the file with be "jan.xls" for Jan
data and then the next month it will be "feb.xls" for Feb. In addition, I
want to place the output in a folder which cooresponds month it is.
If I run a query, is there a way for me to put out an xls file? I see that
I can do this via DTS, but is this the only way?
I am guessing that I can invoke a DTS package from within a stored
procedure, but I am not sure how I would vary the output name. Maybe I woul
d
put out a standard name and then within the stored procedure generate the do
s
commands to rename and move the output as desired. Seems fairly complex for
something which seems to be fairly simple and common.
So I am kind of new to SQL, so I am just trying to get up to speed.
What approach would be recommended?
Thanks in advance for your assistance!!!I reckon the easiest way to do all this is with a ActiveX task within a DTS
package. You could use a SQL Task or ADO to extract the information to a
recordset, the Excel object library to manipulate and save the data and the
FileSystemObject object library to move/create files and directories. Within
VB Script you could build your filename dynamically too.
You would probably want to do some performance testing though.
"Jim Heavey" wrote:

> I want to run a stored procedure which will run a specific query and put o
ut
> a monthly file each month. The name of the file with be "jan.xls" for Jan
> data and then the next month it will be "feb.xls" for Feb. In addition, I
> want to place the output in a folder which cooresponds month it is.
> If I run a query, is there a way for me to put out an xls file? I see tha
t
> I can do this via DTS, but is this the only way?
> I am guessing that I can invoke a DTS package from within a stored
> procedure, but I am not sure how I would vary the output name. Maybe I wo
uld
> put out a standard name and then within the stored procedure generate the
dos
> commands to rename and move the output as desired. Seems fairly complex f
or
> something which seems to be fairly simple and common.
> So I am kind of new to SQL, so I am just trying to get up to speed.
> What approach would be recommended?
> Thanks in advance for your assistance!!!

Excel Output

I want to run a stored procedure which will run a specific query and put out
a monthly file each month. The name of the file with be "jan.xls" for Jan
data and then the next month it will be "feb.xls" for Feb. In addition, I
want to place the output in a folder which cooresponds month it is.
If I run a query, is there a way for me to put out an xls file? I see that
I can do this via DTS, but is this the only way?
I am guessing that I can invoke a DTS package from within a stored
procedure, but I am not sure how I would vary the output name. Maybe I would
put out a standard name and then within the stored procedure generate the dos
commands to rename and move the output as desired. Seems fairly complex for
something which seems to be fairly simple and common.
So I am kind of new to SQL, so I am just trying to get up to speed.
What approach would be recommended?
Thanks in advance for your assistance!!!I reckon the easiest way to do all this is with a ActiveX task within a DTS
package. You could use a SQL Task or ADO to extract the information to a
recordset, the Excel object library to manipulate and save the data and the
FileSystemObject object library to move/create files and directories. Within
VB Script you could build your filename dynamically too.
You would probably want to do some performance testing though.
"Jim Heavey" wrote:
> I want to run a stored procedure which will run a specific query and put out
> a monthly file each month. The name of the file with be "jan.xls" for Jan
> data and then the next month it will be "feb.xls" for Feb. In addition, I
> want to place the output in a folder which cooresponds month it is.
> If I run a query, is there a way for me to put out an xls file? I see that
> I can do this via DTS, but is this the only way?
> I am guessing that I can invoke a DTS package from within a stored
> procedure, but I am not sure how I would vary the output name. Maybe I would
> put out a standard name and then within the stored procedure generate the dos
> commands to rename and move the output as desired. Seems fairly complex for
> something which seems to be fairly simple and common.
> So I am kind of new to SQL, so I am just trying to get up to speed.
> What approach would be recommended?
> Thanks in advance for your assistance!!!