Preventing Deadlock

Saturday, February 25, 2012

Preview has data but no columns returned

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I just ran a test on my side and it actually worked. I create a simple sp with a query (select * from adventureworks.production.productcategory) and the use it in the OLE DB. The preview worked fine and when connecting to a copy column transform I was able to see all the columns. However there is another thread going on now about the same issue and they say there are some work arounds:

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

In case you need it this is the xml code of my package

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">MARINER\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">MARINERLAPTOP14</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/4/2006 8:05:36 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">3</DTS:Property><DTS:Property DTS:Name="VersionGUID">{28EC0411-DE09-4FBE-AA35-FA11901A0511}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ETLRafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=MARINERLAPTOP14;Initial Catalog=ETLRafLabDev;Provider=SQLNCLI.1;Integrated Security=SSPI;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>2593</dwd:BoundingTop><dwd:Layout><dds>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="DTSID">{D803BA1C-C91E-408F-9D6F-1226D9333CD0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DataSourceID>ETLRafLab</dwd:DataSourceID></ConnectionManager></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="DTSID">{CDC092B4-325E-4283-8E03-88E168C82372}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>1667</dwd:BoundingTop><dwd:Layout><dds>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</connector>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="DTSID">{A8831B5B-654F-43D4-BAA9-605512FCCDB0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DestinationName>Copy Column Input</dwd:DestinationName><dwd:SourceName>OLE DB Source Output</dwd:SourceName></PipelinePath></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}-86</DTS:Property><DTS:Property DTS:Name="DTSID">{2C57A2EE-F8A6-4FD6-8BAF-54C95E9E30B7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">Exec sp_SelectFromProductCategory</property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

</outputs>

</component>

</inputs>

</outputs>

</component>

</components>

<paths>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">OLEDB source with SP</DTS:Property><DTS:Property DTS:Name="DTSID">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||yeah I have a similar package connecting to another database (and executing a stored procedure) and that works just fine. But the package that I described in my last email just refuses to get going...btw..thanks for the other link - let me check if tweaking the procedure helps - appreciate your help!!|||

Manmeet Panigrahi wrote:
In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I think the root cause of the problem here is that sprocs don't own their own metadata like tables and views do. It is theoretically possible to return completely different result sets from the same sproc depending on what parameters you pass it so there is no real metadata to bind to. Adam Machanic has a great discussion about this here:

Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)

Try the workaround that I suggested on the other thread.

-Jamie

|||

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

|||

Manmeet Panigrahi wrote:

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.

-Jamie

|||Unfortunately that didnt work too...this is what I did...I now have this variable that stores the command to execute the proc that returns the data (EXEC pr_....). (This variable is populated at run time). Also this variable becomes the Data Access Mode of my OLEDB data source. Now at design time, I have this variable populated with a select statement that returns data in the correct schema format..so that I am able to setup my source destination mappings. So when the package runs, the variable gets populated (at runtime) with the EXEC statement...but unfortunately SSIS revalidates the schema (and detects that the EXEC does not return any columns) and gives me the "VS_NEEDSNEWMETADATA" errror....sigh...Unfortunately the proc is owned by a different set of ppl and it will take a lot of time for me to convince them to change it..

Preview has data but no columns returned

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

In case you need it this is the xml code of my package

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</connector>

</ddscontrol>

</outputs>

</component>

</inputs>

</outputs>

</component>

</components>

<paths>

Manmeet Panigrahi wrote:
In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)

Try the workaround that I suggested on the other thread.

-Jamie

|||

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

|||

Manmeet Panigrahi wrote:

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.

-Jamie

Preview error

Hi,
I'm trying to use Reporting Services, I have installed the client component on my computer (Win 2K pro sp4) and I try to start to design a new report. The data source is a SQL Server developer edition Sp3 installed on the same computer.
All works fine until I try to preview the report using the preview tab from the designer. I received this message "command 'view.output' not available.
My Visual Studio version is EA enghlish version. I have installed the RS SP1 with the same results.
Anyone can help me ?
Thank's in advance and I apologize for my poor englishDo you have any add-ins installed? Can you select View\Other Windows\Output
from the View menu? What happens if you open the VS Command Window, and type
"View.Output" ?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com> wrote in
message news:79F377AF-8255-473C-9AFE-6833731E7E65@.microsoft.com...
> Hi,
> I'm trying to use Reporting Services, I have installed the client
> component on my computer (Win 2K pro sp4) and I try to start to design a
> new report. The data source is a SQL Server developer edition Sp3
> installed on the same computer.
> All works fine until I try to preview the report using the preview tab
> from the designer. I received this message "command 'view.output' not
> available.
> My Visual Studio version is EA enghlish version. I have installed the RS
> SP1 with the same results.
> Anyone can help me ?
> Thank's in advance and I apologize for my poor english|||What do you mean for add-ins ?
Yes I can select View\Other Windows\Output from the View menu.
If I open the VS Command Windows and type View.Output is not recognized as command.
"Lev Semenets [MSFT]" wrote:
> Do you have any add-ins installed? Can you select View\Other Windows\Output
> from the View menu? What happens if you open the VS Command Window, and type
> "View.Output" ?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com> wrote in
> message news:79F377AF-8255-473C-9AFE-6833731E7E65@.microsoft.com...
> > Hi,
> > I'm trying to use Reporting Services, I have installed the client
> > component on my computer (Win 2K pro sp4) and I try to start to design a
> > new report. The data source is a SQL Server developer edition Sp3
> > installed on the same computer.
> > All works fine until I try to preview the report using the preview tab
> > from the designer. I received this message "command 'view.output' not
> > available.
> > My Visual Studio version is EA enghlish version. I have installed the RS
> > SP1 with the same results.
> > Anyone can help me ?
> > Thank's in advance and I apologize for my poor english
>
>|||Please type ">View.Output"
Leading ">" is required there.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com> wrote in
message news:267039CE-E33E-42BB-8B69-E20E23F55AC0@.microsoft.com...
> What do you mean for add-ins ?
> Yes I can select View\Other Windows\Output from the View menu.
> If I open the VS Command Windows and type View.Output is not recognized as
> command.
>
> "Lev Semenets [MSFT]" wrote:
>> Do you have any add-ins installed? Can you select View\Other
>> Windows\Output
>> from the View menu? What happens if you open the VS Command Window, and
>> type
>> "View.Output" ?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com> wrote
>> in
>> message news:79F377AF-8255-473C-9AFE-6833731E7E65@.microsoft.com...
>> > Hi,
>> > I'm trying to use Reporting Services, I have installed the client
>> > component on my computer (Win 2K pro sp4) and I try to start to design
>> > a
>> > new report. The data source is a SQL Server developer edition Sp3
>> > installed on the same computer.
>> > All works fine until I try to preview the report using the preview tab
>> > from the designer. I received this message "command 'view.output' not
>> > available.
>> > My Visual Studio version is EA enghlish version. I have installed the
>> > RS
>> > SP1 with the same results.
>> > Anyone can help me ?
>> > Thank's in advance and I apologize for my poor english
>>|||I haven't understand while window you mean, I confused it with the VS Command Prompt.
So if I try to type > View. I can see in the intellisense window the Output method but when I select it i obtain this message "Comando "View.Output" non disponibile" translate in enghlish "Comand "View.Output" not available".
Do you think that the problem may be that I use VS EA in eghlish on a Windows 2000 Pro Italian machine ?
Many thanks for your help
"Lev Semenets [MSFT]" wrote:
> Please type ">View.Output"
> Leading ">" is required there.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com> wrote in
> message news:267039CE-E33E-42BB-8B69-E20E23F55AC0@.microsoft.com...
> > What do you mean for add-ins ?
> > Yes I can select View\Other Windows\Output from the View menu.
> > If I open the VS Command Windows and type View.Output is not recognized as
> > command.
> >
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> Do you have any add-ins installed? Can you select View\Other
> >> Windows\Output
> >> from the View menu? What happens if you open the VS Command Window, and
> >> type
> >> "View.Output" ?
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com> wrote
> >> in
> >> message news:79F377AF-8255-473C-9AFE-6833731E7E65@.microsoft.com...
> >> > Hi,
> >> > I'm trying to use Reporting Services, I have installed the client
> >> > component on my computer (Win 2K pro sp4) and I try to start to design
> >> > a
> >> > new report. The data source is a SQL Server developer edition Sp3
> >> > installed on the same computer.
> >> > All works fine until I try to preview the report using the preview tab
> >> > from the designer. I received this message "command 'view.output' not
> >> > available.
> >> > My Visual Studio version is EA enghlish version. I have installed the
> >> > RS
> >> > SP1 with the same results.
> >> > Anyone can help me ?
> >> > Thank's in advance and I apologize for my poor english
> >>
> >>
> >>
>
>|||This is some weird VS problem...
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com> wrote in
message news:18EA1D58-65E7-4117-8FCC-F078862D0F08@.microsoft.com...
>I haven't understand while window you mean, I confused it with the VS
>Command Prompt.
> So if I try to type > View. I can see in the intellisense window the
> Output method but when I select it i obtain this message "Comando
> "View.Output" non disponibile" translate in enghlish "Comand "View.Output"
> not available".
> Do you think that the problem may be that I use VS EA in eghlish on a
> Windows 2000 Pro Italian machine ?
> Many thanks for your help
> "Lev Semenets [MSFT]" wrote:
>> Please type ">View.Output"
>> Leading ">" is required there.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com> wrote
>> in
>> message news:267039CE-E33E-42BB-8B69-E20E23F55AC0@.microsoft.com...
>> > What do you mean for add-ins ?
>> > Yes I can select View\Other Windows\Output from the View menu.
>> > If I open the VS Command Windows and type View.Output is not recognized
>> > as
>> > command.
>> >
>> >
>> > "Lev Semenets [MSFT]" wrote:
>> >
>> >> Do you have any add-ins installed? Can you select View\Other
>> >> Windows\Output
>> >> from the View menu? What happens if you open the VS Command Window,
>> >> and
>> >> type
>> >> "View.Output" ?
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Roberto Pasqualini" <RobertoPasqualini@.discussions.microsoft.com>
>> >> wrote
>> >> in
>> >> message news:79F377AF-8255-473C-9AFE-6833731E7E65@.microsoft.com...
>> >> > Hi,
>> >> > I'm trying to use Reporting Services, I have installed the client
>> >> > component on my computer (Win 2K pro sp4) and I try to start to
>> >> > design
>> >> > a
>> >> > new report. The data source is a SQL Server developer edition Sp3
>> >> > installed on the same computer.
>> >> > All works fine until I try to preview the report using the preview
>> >> > tab
>> >> > from the designer. I received this message "command 'view.output'
>> >> > not
>> >> > available.
>> >> > My Visual Studio version is EA enghlish version. I have installed
>> >> > the
>> >> > RS
>> >> > SP1 with the same results.
>> >> > Anyone can help me ?
>> >> > Thank's in advance and I apologize for my poor english
>> >>
>> >>
>> >>
>>

Preview error

This is my first report. I used VS.NET 2003 wizard to create the report,
which consisted of a very simple select query report.
When I switch to preview tab in IDE, I am getting the following error.
Processing Errors:
An error has occurred during report processing.
Cannot create a connecttion to data source 'xxx'.
Login failed for user 'yy'
Any ideas of why this is happening? I looked at rdl file in notepad. The
credentials supplied during report creation is not stored in that file.
Please help ASAP.
Thanks,
Live_Love_LaughIf you are using a shared data source, in solution explorer, double click on
the datasource file. Select the credentials tab. The problem is likely here.
If you are using SQL authentication, either enter a password or select the
option to prompt for a password. Let me know if this helps.
"Live_Love_Laugh" wrote:
> This is my first report. I used VS.NET 2003 wizard to create the report,
> which consisted of a very simple select query report.
> When I switch to preview tab in IDE, I am getting the following error.
> Processing Errors:
> An error has occurred during report processing.
> Cannot create a connecttion to data source 'xxx'.
> Login failed for user 'yy'
> Any ideas of why this is happening? I looked at rdl file in notepad. The
> credentials supplied during report creation is not stored in that file.
> Please help ASAP.
> Thanks,
> Live_Love_Laugh
>
>|||I am not using a shared data source. On the data tab, I can see the tables
and my query. But when I switch to preview tab to fetch the data, it is
giving the error. Also on the data tab, I can run the query and see the
results.
Thanks,
Live_Love_Laugh
"vmp_pdx" <vmppdx@.discussions.microsoft.com> wrote in message
news:60D5F30E-F474-4AA3-B6B9-5805A046B898@.microsoft.com...
> If you are using a shared data source, in solution explorer, double click
on
> the datasource file. Select the credentials tab. The problem is likely
here.
> If you are using SQL authentication, either enter a password or select the
> option to prompt for a password. Let me know if this helps.
> "Live_Love_Laugh" wrote:
> > This is my first report. I used VS.NET 2003 wizard to create the report,
> > which consisted of a very simple select query report.
> > When I switch to preview tab in IDE, I am getting the following error.
> >
> > Processing Errors:
> >
> > An error has occurred during report processing.
> > Cannot create a connecttion to data source 'xxx'.
> > Login failed for user 'yy'
> >
> > Any ideas of why this is happening? I looked at rdl file in notepad. The
> > credentials supplied during report creation is not stored in that file.
> >
> > Please help ASAP.
> >
> > Thanks,
> > Live_Love_Laugh
> >
> >
> >|||Can anybody help with this?
Thanks,
Live_Love_Laugh
"Live_Love_Laugh" <OnlineSubscribe@.yahoo.com> wrote in message
news:%23NTAPMvpEHA.2588@.TK2MSFTNGP12.phx.gbl...
> I am not using a shared data source. On the data tab, I can see the tables
> and my query. But when I switch to preview tab to fetch the data, it is
> giving the error. Also on the data tab, I can run the query and see the
> results.
> Thanks,
> Live_Love_Laugh
> "vmp_pdx" <vmppdx@.discussions.microsoft.com> wrote in message
> news:60D5F30E-F474-4AA3-B6B9-5805A046B898@.microsoft.com...
> > If you are using a shared data source, in solution explorer, double
click
> on
> > the datasource file. Select the credentials tab. The problem is likely
> here.
> > If you are using SQL authentication, either enter a password or select
the
> > option to prompt for a password. Let me know if this helps.
> >
> > "Live_Love_Laugh" wrote:
> >
> > > This is my first report. I used VS.NET 2003 wizard to create the
report,
> > > which consisted of a very simple select query report.
> > > When I switch to preview tab in IDE, I am getting the following error.
> > >
> > > Processing Errors:
> > >
> > > An error has occurred during report processing.
> > > Cannot create a connecttion to data source 'xxx'.
> > > Login failed for user 'yy'
> > >
> > > Any ideas of why this is happening? I looked at rdl file in notepad.
The
> > > credentials supplied during report creation is not stored in that
file.
> > >
> > > Please help ASAP.
> > >
> > > Thanks,
> > > Live_Love_Laugh
> > >
> > >
> > >
>|||Could you email to me your RDL?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Live_Love_Laugh" <OnlineSubscribe@.yahoo.com> wrote in message
news:OZEmZy$pEHA.2900@.TK2MSFTNGP12.phx.gbl...
> Can anybody help with this?
> Thanks,
> Live_Love_Laugh
> "Live_Love_Laugh" <OnlineSubscribe@.yahoo.com> wrote in message
> news:%23NTAPMvpEHA.2588@.TK2MSFTNGP12.phx.gbl...
>> I am not using a shared data source. On the data tab, I can see the
>> tables
>> and my query. But when I switch to preview tab to fetch the data, it is
>> giving the error. Also on the data tab, I can run the query and see the
>> results.
>> Thanks,
>> Live_Love_Laugh
>> "vmp_pdx" <vmppdx@.discussions.microsoft.com> wrote in message
>> news:60D5F30E-F474-4AA3-B6B9-5805A046B898@.microsoft.com...
>> > If you are using a shared data source, in solution explorer, double
> click
>> on
>> > the datasource file. Select the credentials tab. The problem is likely
>> here.
>> > If you are using SQL authentication, either enter a password or select
> the
>> > option to prompt for a password. Let me know if this helps.
>> >
>> > "Live_Love_Laugh" wrote:
>> >
>> > > This is my first report. I used VS.NET 2003 wizard to create the
> report,
>> > > which consisted of a very simple select query report.
>> > > When I switch to preview tab in IDE, I am getting the following
>> > > error.
>> > >
>> > > Processing Errors:
>> > >
>> > > An error has occurred during report processing.
>> > > Cannot create a connecttion to data source 'xxx'.
>> > > Login failed for user 'yy'
>> > >
>> > > Any ideas of why this is happening? I looked at rdl file in notepad.
> The
>> > > credentials supplied during report creation is not stored in that
> file.
>> > >
>> > > Please help ASAP.
>> > >
>> > > Thanks,
>> > > Live_Love_Laugh
>> > >
>> > >
>> > >
>>
>

Preview Error

Hi all,

I am all the time receiving this arror message:

TITLE: Microsoft Report Designer

A connection cannot be made to the database.
Set and test the connection string.

ADDITIONAL INFORMATION:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

What should i do?

Thanks

Hi,

you are trying to logon with your datasource object as a SQL Server login, but your SQL Server doesn′t accept mixed (this includes SQL Server as well as Windows) authentication. So either switch your server to mixed authentication or switch your datasource objects security context to Windows (Integrated) Authentication.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Preview doesn't return records like it does under data tab

I have a problem where have a report that uses a shared datasource that
references a SQL Server. My dataset called a stored procedure that builds a
sql string and passes in the string to an openquery() command that queries a
DB2 database that is a linked server on that same SQL Server. The results
show up fine on the data tab when I run it, but when I try to preview the
report I get the column headers but no fields show up. The query takes time
to complete (under 2 minutes) I'm only returning 200 rows with under maybe 6
columns and the preview mode indicates from the number of pages (6) that it
knows some data should be returned. But none of the data is populating in
the table. I've been on with Microsoft Tech Support going on three weeks
now and they are clueless. Anyone have any idea why this is happening? I
can view reports that are querying the linked server database w/o the stored
proc. I get no error message just no data.Are you using a table to view the data?
Have you tried deploying the report to make sure it is not a development
environment issue?
There is a cache of the data used for preview. Look in the directory where
you report is stored. Delete the .data file for the report and see if that
makes any difference.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jennifer" <Jennifer@.discussions.microsoft.com> wrote in message
news:EABA6B8A-7EE0-4125-B0CE-947A401EBFA0@.microsoft.com...
>I have a problem where have a report that uses a shared datasource that
> references a SQL Server. My dataset called a stored procedure that builds
> a
> sql string and passes in the string to an openquery() command that queries
> a
> DB2 database that is a linked server on that same SQL Server. The results
> show up fine on the data tab when I run it, but when I try to preview the
> report I get the column headers but no fields show up. The query takes
> time
> to complete (under 2 minutes) I'm only returning 200 rows with under maybe
> 6
> columns and the preview mode indicates from the number of pages (6) that
> it
> knows some data should be returned. But none of the data is populating in
> the table. I've been on with Microsoft Tech Support going on three
> weeks
> now and they are clueless. Anyone have any idea why this is happening? I
> can view reports that are querying the linked server database w/o the
> stored
> proc. I get no error message just no data.|||Figured it out. The fields weren't populating with a stored proc. Added the
fields (case sensitive) and made sure they were mapped to the database names.
Then my data started populating on the previewed report. Microsoft figured
out for me.
"Jennifer" wrote:
> I have a problem where have a report that uses a shared datasource that
> references a SQL Server. My dataset called a stored procedure that builds a
> sql string and passes in the string to an openquery() command that queries a
> DB2 database that is a linked server on that same SQL Server. The results
> show up fine on the data tab when I run it, but when I try to preview the
> report I get the column headers but no fields show up. The query takes time
> to complete (under 2 minutes) I'm only returning 200 rows with under maybe 6
> columns and the preview mode indicates from the number of pages (6) that it
> knows some data should be returned. But none of the data is populating in
> the table. I've been on with Microsoft Tech Support going on three weeks
> now and they are clueless. Anyone have any idea why this is happening? I
> can view reports that are querying the linked server database w/o the stored
> proc. I get no error message just no data.

preview data, it gives you incorrect results

I am reposting this to a different forum, becasue I got not response...

Using VS05 SP1 Pro SQL Express…

Take a simple stored procedure like the following to return a specific column from a specific row in a data table ....

SELECT fld_IX_UserID

FROM UserIDs

WHERE (fld_UserID_Table_ID = @.USERID_TABLE_ID)

It works fine in Store Procedure, and if you create at table adapter to reference it, that works fine as well.

Here is the issue. In the store procedure (i.e. under Server Explorer), when you execute the command to test it, it gives you the correct results. In Edit with Dataset Designer, Table Adapater, if you highlight it, right click preview data, it gives you incorrect results. In code, the table adapter gives you the correct resutls.

In every case, Preview Results for a table adapter built on a stored procedure will give you the wrong results. This is clearly a bug and can result in a log of wasted time.

Am I missing something? FYI, I realize I don't need to use the Table Adapater to execute the above stored procedure, but we are using table adapters for everything to be consistent.

Thanks,

Bob

i am not good in .net ... so i cannot comment on that.. but if you are getting different result while u run the same SP means the input parameter which you pass is differ for each run. check what is the value set for @.USERID_TABLE_ID in each run

Madhu

|||

No the stored procedure returnes the correct data every time.

It is the Preview Data, in the IDE table adapter designer view, that returns incorrect data everytime, but only for Store Procedures used as table adapters.

Bob

|||

Hi Bob,

I would suggest you post this bug to http://connect.microsoft.com. You should be able to find the VS Connection and enter the issue there. That will put the issue into their tracking system. Since bad data is happening only through VS, it should be filed with VS.

Mike

Saturday, February 25, 2012

Preview has data but no columns returned

Preview has data but no columns returned

Preview error

Preview error

Preview Error

Preview doesn't return records like it does under data tab

preview data, it gives you incorrect results

Preventing Deadlock

Blog Archive

About Me