I am having an issue when converting some of my reports to SQL reporting
services 2005. All of my reports use an OLEDB connection that point to a UDL
file so that we can easily change connection strings as needed without having
to touch the report server. They all use stored procedures to obtain the
data. The issues I am running into is with stored procedures that use temp
tables. Anything that is done in a stored procedure after the temp table is
created is not shown in the report designer on the data tab within visual
studio 2005. Iâ've read a few posts about this problem but it seems like no
one at Microsoft has been able to reproduce it. I have been able to
reproduce it with a simple example using the NorthWind database. I will
include the stored procedure and the rdl file below. I set the report to
prompt for credentials instead of using the UDL file but the same problem
occurs. It will occur with Windows authentication as well. If I change the
connection type to â'Microsoft SQL Serverâ' then it will work correctly the
problem is that I will not be able to use UDL or DSN files. My method worked
just fine with RS 2000. What has changed with RS 2005 to cause this issue?
Any ideas on how I might get this to work?
Thanks,
-Nathan
SP Script-
use northwind
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
AS
Select FirstName
into #EmployeeTemp
FROM Employees
--*NOTE It doesnâ't matter what you do here it will not be returned in the
Report Designer Data tab.
SELECT FirstName
FROM #EmployeeTemp
--Select 'Test'
RDL File-
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<Prompt>Specify a user name and password for data source
DataSource1</Prompt>
<ConnectString>Provider=SQLOLEDB.1;Data Source=saintdevsql1;Initial
Catalog=Northwind</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>0.75in</Height>
</Body>
<rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Northwind">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>Pr_TestingRSProblem</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>I meant to mention that I tried the sp with and without explicitly dropping
the table at the end. Neither way worked.
"Nathan" wrote:
> I am having an issue when converting some of my reports to SQL reporting
> services 2005. All of my reports use an OLEDB connection that point to a UDL
> file so that we can easily change connection strings as needed without having
> to touch the report server. They all use stored procedures to obtain the
> data. The issues I am running into is with stored procedures that use temp
> tables. Anything that is done in a stored procedure after the temp table is
> created is not shown in the report designer on the data tab within visual
> studio 2005. Iâ've read a few posts about this problem but it seems like no
> one at Microsoft has been able to reproduce it. I have been able to
> reproduce it with a simple example using the NorthWind database. I will
> include the stored procedure and the rdl file below. I set the report to
> prompt for credentials instead of using the UDL file but the same problem
> occurs. It will occur with Windows authentication as well. If I change the
> connection type to â'Microsoft SQL Serverâ' then it will work correctly the
> problem is that I will not be able to use UDL or DSN files. My method worked
> just fine with RS 2000. What has changed with RS 2005 to cause this issue?
> Any ideas on how I might get this to work?
> Thanks,
> -Nathan
> SP Script-
> use northwind
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> AS
> Select FirstName
> into #EmployeeTemp
> FROM Employees
> --*NOTE It doesnâ't matter what you do here it will not be returned in the
> Report Designer Data tab.
> SELECT FirstName
> FROM #EmployeeTemp
> --Select 'Test'
>
> RDL File-
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="DataSource1">
> <ConnectionProperties>
> <Prompt>Specify a user name and password for data source
> DataSource1</Prompt>
> <ConnectString>Provider=SQLOLEDB.1;Data Source=saintdevsql1;Initial
> Catalog=Northwind</ConnectString>
> <DataProvider>OLEDB</DataProvider>
> </ConnectionProperties>
> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <Height>0.75in</Height>
> </Body>
> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="Northwind">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandType>StoredProcedure</CommandType>
> <CommandText>Pr_TestingRSProblem</CommandText>
> <DataSourceName>DataSource1</DataSourceName>
> </Query>
> </DataSet>
> </DataSets>
> <Width>6.5in</Width>
> <InteractiveHeight>11in</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>1in</TopMargin>
> </Report>
>|||First, do not explicitly drop the table.
Second, exactly what is happening? No data? No field list? Any error?
I just had a case where I would get an error about a temp table (I use temp
tables all the time, why this particular SP had a problem I don't know).
What worked for me was to refresh the fields (button is to the right of the
...). I then had a field list. After that I could execute the query in the
data tab and I got data back.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> I meant to mention that I tried the sp with and without explicitly
> dropping
> the table at the end. Neither way worked.
> "Nathan" wrote:
>> I am having an issue when converting some of my reports to SQL reporting
>> services 2005. All of my reports use an OLEDB connection that point to a
>> UDL
>> file so that we can easily change connection strings as needed without
>> having
>> to touch the report server. They all use stored procedures to obtain the
>> data. The issues I am running into is with stored procedures that use
>> temp
>> tables. Anything that is done in a stored procedure after the temp table
>> is
>> created is not shown in the report designer on the data tab within visual
>> studio 2005. I've read a few posts about this problem but it seems like
>> no
>> one at Microsoft has been able to reproduce it. I have been able to
>> reproduce it with a simple example using the NorthWind database. I will
>> include the stored procedure and the rdl file below. I set the report to
>> prompt for credentials instead of using the UDL file but the same problem
>> occurs. It will occur with Windows authentication as well. If I change
>> the
>> connection type to "Microsoft SQL Server" then it will work correctly the
>> problem is that I will not be able to use UDL or DSN files. My method
>> worked
>> just fine with RS 2000. What has changed with RS 2005 to cause this
>> issue?
>> Any ideas on how I might get this to work?
>> Thanks,
>> -Nathan
>> SP Script-
>> use northwind
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER OFF
>> GO
>> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> AS
>> Select FirstName
>> into #EmployeeTemp
>> FROM Employees
>> --*NOTE It doesn't matter what you do here it will not be returned in the
>> Report Designer Data tab.
>> SELECT FirstName
>> FROM #EmployeeTemp
>> --Select 'Test'
>>
>> RDL File-
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="DataSource1">
>> <ConnectionProperties>
>> <Prompt>Specify a user name and password for data source
>> DataSource1</Prompt>
>> <ConnectString>Provider=SQLOLEDB.1;Data
>> Source=saintdevsql1;Initial
>> Catalog=Northwind</ConnectString>
>> <DataProvider>OLEDB</DataProvider>
>> </ConnectionProperties>
>> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <Height>0.75in</Height>
>> </Body>
>> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="Northwind">
>> <Query>
>> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> <CommandType>StoredProcedure</CommandType>
>> <CommandText>Pr_TestingRSProblem</CommandText>
>> <DataSourceName>DataSource1</DataSourceName>
>> </Query>
>> </DataSet>
>> </DataSets>
>> <Width>6.5in</Width>
>> <InteractiveHeight>11in</InteractiveHeight>
>> <Language>en-US</Language>
>> <TopMargin>1in</TopMargin>
>> </Report>|||Bruce,
The simple answer is there is no data, no field list, and no error. I
should have mentioned that I have tried refreshing the field list several
times and no fields are returned. I've tried this on two separate machines.
The field list is empty and when I try to run the sp within the data tab it
returns no results. Were you not able to reproduce the problem with an OLEDB
connection type? What I found strange was that anything in the sp before the
temp table declaration worked fine. It's as if the meta data describing the
table structure isn't returned. Any ideas? Let me know if I can provide you
with any more information. Thanks for the quick reply.
-Nathan
"Bruce L-C [MVP]" wrote:
> First, do not explicitly drop the table.
> Second, exactly what is happening? No data? No field list? Any error?
> I just had a case where I would get an error about a temp table (I use temp
> tables all the time, why this particular SP had a problem I don't know).
> What worked for me was to refresh the fields (button is to the right of the
> ...). I then had a field list. After that I could execute the query in the
> data tab and I got data back.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> >
> > I meant to mention that I tried the sp with and without explicitly
> > dropping
> > the table at the end. Neither way worked.
> >
> > "Nathan" wrote:
> >
> >> I am having an issue when converting some of my reports to SQL reporting
> >> services 2005. All of my reports use an OLEDB connection that point to a
> >> UDL
> >> file so that we can easily change connection strings as needed without
> >> having
> >> to touch the report server. They all use stored procedures to obtain the
> >> data. The issues I am running into is with stored procedures that use
> >> temp
> >> tables. Anything that is done in a stored procedure after the temp table
> >> is
> >> created is not shown in the report designer on the data tab within visual
> >> studio 2005. I've read a few posts about this problem but it seems like
> >> no
> >> one at Microsoft has been able to reproduce it. I have been able to
> >> reproduce it with a simple example using the NorthWind database. I will
> >> include the stored procedure and the rdl file below. I set the report to
> >> prompt for credentials instead of using the UDL file but the same problem
> >> occurs. It will occur with Windows authentication as well. If I change
> >> the
> >> connection type to "Microsoft SQL Server" then it will work correctly the
> >> problem is that I will not be able to use UDL or DSN files. My method
> >> worked
> >> just fine with RS 2000. What has changed with RS 2005 to cause this
> >> issue?
> >> Any ideas on how I might get this to work?
> >>
> >> Thanks,
> >> -Nathan
> >>
> >> SP Script-
> >>
> >> use northwind
> >>
> >> set ANSI_NULLS ON
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >>
> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> >> AS
> >>
> >> Select FirstName
> >> into #EmployeeTemp
> >> FROM Employees
> >> --*NOTE It doesn't matter what you do here it will not be returned in the
> >> Report Designer Data tab.
> >> SELECT FirstName
> >> FROM #EmployeeTemp
> >> --Select 'Test'
> >>
> >>
> >> RDL File-
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="DataSource1">
> >> <ConnectionProperties>
> >> <Prompt>Specify a user name and password for data source
> >> DataSource1</Prompt>
> >> <ConnectString>Provider=SQLOLEDB.1;Data
> >> Source=saintdevsql1;Initial
> >> Catalog=Northwind</ConnectString>
> >> <DataProvider>OLEDB</DataProvider>
> >> </ConnectionProperties>
> >>
> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <Height>0.75in</Height>
> >> </Body>
> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> >> <LeftMargin>1in</LeftMargin>
> >> <DataSets>
> >> <DataSet Name="Northwind">
> >> <Query>
> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> >> <CommandType>StoredProcedure</CommandType>
> >> <CommandText>Pr_TestingRSProblem</CommandText>
> >> <DataSourceName>DataSource1</DataSourceName>
> >> </Query>
> >> </DataSet>
> >> </DataSets>
> >> <Width>6.5in</Width>
> >> <InteractiveHeight>11in</InteractiveHeight>
> >> <Language>en-US</Language>
> >> <TopMargin>1in</TopMargin>
> >> </Report>
> >>
>
>|||I don't have northwind, I have AdventureWorks. Here is my stored procedure:
create PROCEDURE [dbo].[Pr_TestingRSProblem]
AS
Select FirstName
into #TEMP
FROM person.contact
SELECT distinct FirstName
FROM #TEMP order by firstname
return
I did not use the wizard. I added a report. I went to the data tab. Added a
new dataset. My shared data source credentials were windows authentication.
When creating the dataset I changed the data type to stored procedure and
put in the name of the stored procedure as the query string (do not put in
exec, just put in the name of the procedure). I get an error and no field
list. I click on refresh the field list I get a field list and now I can
execute the query and get data back in the data tab.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> I meant to mention that I tried the sp with and without explicitly
> dropping
> the table at the end. Neither way worked.
> "Nathan" wrote:
>> I am having an issue when converting some of my reports to SQL reporting
>> services 2005. All of my reports use an OLEDB connection that point to a
>> UDL
>> file so that we can easily change connection strings as needed without
>> having
>> to touch the report server. They all use stored procedures to obtain the
>> data. The issues I am running into is with stored procedures that use
>> temp
>> tables. Anything that is done in a stored procedure after the temp table
>> is
>> created is not shown in the report designer on the data tab within visual
>> studio 2005. I've read a few posts about this problem but it seems like
>> no
>> one at Microsoft has been able to reproduce it. I have been able to
>> reproduce it with a simple example using the NorthWind database. I will
>> include the stored procedure and the rdl file below. I set the report to
>> prompt for credentials instead of using the UDL file but the same problem
>> occurs. It will occur with Windows authentication as well. If I change
>> the
>> connection type to "Microsoft SQL Server" then it will work correctly the
>> problem is that I will not be able to use UDL or DSN files. My method
>> worked
>> just fine with RS 2000. What has changed with RS 2005 to cause this
>> issue?
>> Any ideas on how I might get this to work?
>> Thanks,
>> -Nathan
>> SP Script-
>> use northwind
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER OFF
>> GO
>> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> AS
>> Select FirstName
>> into #EmployeeTemp
>> FROM Employees
>> --*NOTE It doesn't matter what you do here it will not be returned in the
>> Report Designer Data tab.
>> SELECT FirstName
>> FROM #EmployeeTemp
>> --Select 'Test'
>>
>> RDL File-
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="DataSource1">
>> <ConnectionProperties>
>> <Prompt>Specify a user name and password for data source
>> DataSource1</Prompt>
>> <ConnectString>Provider=SQLOLEDB.1;Data
>> Source=saintdevsql1;Initial
>> Catalog=Northwind</ConnectString>
>> <DataProvider>OLEDB</DataProvider>
>> </ConnectionProperties>
>> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <Height>0.75in</Height>
>> </Body>
>> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="Northwind">
>> <Query>
>> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> <CommandType>StoredProcedure</CommandType>
>> <CommandText>Pr_TestingRSProblem</CommandText>
>> <DataSourceName>DataSource1</DataSourceName>
>> </Query>
>> </DataSet>
>> </DataSets>
>> <Width>6.5in</Width>
>> <InteractiveHeight>11in</InteractiveHeight>
>> <Language>en-US</Language>
>> <TopMargin>1in</TopMargin>
>> </Report>|||Whoops, didn't use OLEDB. Yep, I can duplicate. My only suggestion is to not
use OLEDB. I tried ODBC (which is what I use with Sybase) but also had a
proble with that. Against SQL Server I don't use OLEDB or ODBC, just ODBC
against Sybase (for which I don't have this problem). Weird. Sorry, other
than duplicating the problem there isn't much more I can do to help.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:CCF681B4-BB0F-4027-8672-7CDFFE14ECE1@.microsoft.com...
> Bruce,
> The simple answer is there is no data, no field list, and no error. I
> should have mentioned that I have tried refreshing the field list several
> times and no fields are returned. I've tried this on two separate
> machines.
> The field list is empty and when I try to run the sp within the data tab
> it
> returns no results. Were you not able to reproduce the problem with an
> OLEDB
> connection type? What I found strange was that anything in the sp before
> the
> temp table declaration worked fine. It's as if the meta data describing
> the
> table structure isn't returned. Any ideas? Let me know if I can provide
> you
> with any more information. Thanks for the quick reply.
> -Nathan
>
> "Bruce L-C [MVP]" wrote:
>> First, do not explicitly drop the table.
>> Second, exactly what is happening? No data? No field list? Any error?
>> I just had a case where I would get an error about a temp table (I use
>> temp
>> tables all the time, why this particular SP had a problem I don't know).
>> What worked for me was to refresh the fields (button is to the right of
>> the
>> ...). I then had a field list. After that I could execute the query in
>> the
>> data tab and I got data back.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
>> >
>> > I meant to mention that I tried the sp with and without explicitly
>> > dropping
>> > the table at the end. Neither way worked.
>> >
>> > "Nathan" wrote:
>> >
>> >> I am having an issue when converting some of my reports to SQL
>> >> reporting
>> >> services 2005. All of my reports use an OLEDB connection that point
>> >> to a
>> >> UDL
>> >> file so that we can easily change connection strings as needed without
>> >> having
>> >> to touch the report server. They all use stored procedures to obtain
>> >> the
>> >> data. The issues I am running into is with stored procedures that use
>> >> temp
>> >> tables. Anything that is done in a stored procedure after the temp
>> >> table
>> >> is
>> >> created is not shown in the report designer on the data tab within
>> >> visual
>> >> studio 2005. I've read a few posts about this problem but it seems
>> >> like
>> >> no
>> >> one at Microsoft has been able to reproduce it. I have been able to
>> >> reproduce it with a simple example using the NorthWind database. I
>> >> will
>> >> include the stored procedure and the rdl file below. I set the report
>> >> to
>> >> prompt for credentials instead of using the UDL file but the same
>> >> problem
>> >> occurs. It will occur with Windows authentication as well. If I
>> >> change
>> >> the
>> >> connection type to "Microsoft SQL Server" then it will work correctly
>> >> the
>> >> problem is that I will not be able to use UDL or DSN files. My method
>> >> worked
>> >> just fine with RS 2000. What has changed with RS 2005 to cause this
>> >> issue?
>> >> Any ideas on how I might get this to work?
>> >>
>> >> Thanks,
>> >> -Nathan
>> >>
>> >> SP Script-
>> >>
>> >> use northwind
>> >>
>> >> set ANSI_NULLS ON
>> >> set QUOTED_IDENTIFIER OFF
>> >> GO
>> >>
>> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> >> AS
>> >>
>> >> Select FirstName
>> >> into #EmployeeTemp
>> >> FROM Employees
>> >> --*NOTE It doesn't matter what you do here it will not be returned in
>> >> the
>> >> Report Designer Data tab.
>> >> SELECT FirstName
>> >> FROM #EmployeeTemp
>> >> --Select 'Test'
>> >>
>> >>
>> >> RDL File-
>> >>
>> >> <?xml version="1.0" encoding="utf-8"?>
>> >> <Report
>> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> >> <DataSources>
>> >> <DataSource Name="DataSource1">
>> >> <ConnectionProperties>
>> >> <Prompt>Specify a user name and password for data source
>> >> DataSource1</Prompt>
>> >> <ConnectString>Provider=SQLOLEDB.1;Data
>> >> Source=saintdevsql1;Initial
>> >> Catalog=Northwind</ConnectString>
>> >> <DataProvider>OLEDB</DataProvider>
>> >> </ConnectionProperties>
>> >>
>> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> >> </DataSource>
>> >> </DataSources>
>> >> <BottomMargin>1in</BottomMargin>
>> >> <RightMargin>1in</RightMargin>
>> >> <rd:DrawGrid>true</rd:DrawGrid>
>> >> <InteractiveWidth>8.5in</InteractiveWidth>
>> >> <rd:SnapToGrid>true</rd:SnapToGrid>
>> >> <Body>
>> >> <Height>0.75in</Height>
>> >> </Body>
>> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> >> <LeftMargin>1in</LeftMargin>
>> >> <DataSets>
>> >> <DataSet Name="Northwind">
>> >> <Query>
>> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> >> <CommandType>StoredProcedure</CommandType>
>> >> <CommandText>Pr_TestingRSProblem</CommandText>
>> >> <DataSourceName>DataSource1</DataSourceName>
>> >> </Query>
>> >> </DataSet>
>> >> </DataSets>
>> >> <Width>6.5in</Width>
>> >> <InteractiveHeight>11in</InteractiveHeight>
>> >> <Language>en-US</Language>
>> >> <TopMargin>1in</TopMargin>
>> >> </Report>
>> >>
>>|||I tried the example you have given with the adventure works database. I am
able to reproduce what you have with the connection type of â'Microsoft SQL
Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
having. I need to use this because of my requirements of using a dsn or udl
file.
Thanks again,
-Nathan
"Bruce L-C [MVP]" wrote:
> I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> create PROCEDURE [dbo].[Pr_TestingRSProblem]
> AS
> Select FirstName
> into #TEMP
> FROM person.contact
> SELECT distinct FirstName
> FROM #TEMP order by firstname
> return
> I did not use the wizard. I added a report. I went to the data tab. Added a
> new dataset. My shared data source credentials were windows authentication.
> When creating the dataset I changed the data type to stored procedure and
> put in the name of the stored procedure as the query string (do not put in
> exec, just put in the name of the procedure). I get an error and no field
> list. I click on refresh the field list I get a field list and now I can
> execute the query and get data back in the data tab.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> >
> > I meant to mention that I tried the sp with and without explicitly
> > dropping
> > the table at the end. Neither way worked.
> >
> > "Nathan" wrote:
> >
> >> I am having an issue when converting some of my reports to SQL reporting
> >> services 2005. All of my reports use an OLEDB connection that point to a
> >> UDL
> >> file so that we can easily change connection strings as needed without
> >> having
> >> to touch the report server. They all use stored procedures to obtain the
> >> data. The issues I am running into is with stored procedures that use
> >> temp
> >> tables. Anything that is done in a stored procedure after the temp table
> >> is
> >> created is not shown in the report designer on the data tab within visual
> >> studio 2005. I've read a few posts about this problem but it seems like
> >> no
> >> one at Microsoft has been able to reproduce it. I have been able to
> >> reproduce it with a simple example using the NorthWind database. I will
> >> include the stored procedure and the rdl file below. I set the report to
> >> prompt for credentials instead of using the UDL file but the same problem
> >> occurs. It will occur with Windows authentication as well. If I change
> >> the
> >> connection type to "Microsoft SQL Server" then it will work correctly the
> >> problem is that I will not be able to use UDL or DSN files. My method
> >> worked
> >> just fine with RS 2000. What has changed with RS 2005 to cause this
> >> issue?
> >> Any ideas on how I might get this to work?
> >>
> >> Thanks,
> >> -Nathan
> >>
> >> SP Script-
> >>
> >> use northwind
> >>
> >> set ANSI_NULLS ON
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >>
> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> >> AS
> >>
> >> Select FirstName
> >> into #EmployeeTemp
> >> FROM Employees
> >> --*NOTE It doesn't matter what you do here it will not be returned in the
> >> Report Designer Data tab.
> >> SELECT FirstName
> >> FROM #EmployeeTemp
> >> --Select 'Test'
> >>
> >>
> >> RDL File-
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="DataSource1">
> >> <ConnectionProperties>
> >> <Prompt>Specify a user name and password for data source
> >> DataSource1</Prompt>
> >> <ConnectString>Provider=SQLOLEDB.1;Data
> >> Source=saintdevsql1;Initial
> >> Catalog=Northwind</ConnectString>
> >> <DataProvider>OLEDB</DataProvider>
> >> </ConnectionProperties>
> >>
> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <Height>0.75in</Height>
> >> </Body>
> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> >> <LeftMargin>1in</LeftMargin>
> >> <DataSets>
> >> <DataSet Name="Northwind">
> >> <Query>
> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> >> <CommandType>StoredProcedure</CommandType>
> >> <CommandText>Pr_TestingRSProblem</CommandText>
> >> <DataSourceName>DataSource1</DataSourceName>
> >> </Query>
> >> </DataSet>
> >> </DataSets>
> >> <Width>6.5in</Width>
> >> <InteractiveHeight>11in</InteractiveHeight>
> >> <Language>en-US</Language>
> >> <TopMargin>1in</TopMargin>
> >> </Report>
> >>
>
>|||I must not have refreshed before sending my last reply. Looks like you did
try the OLEDB. Well, thanks for trying. At least I know I'm not going crazy
and it is reproducible and may actually be an issue with reporting services.
The funny thing is that if you try making the same report in the previous
version of RS within VS2003 it works fine. That is with it connecting to the
same DB server (SQL 2005). This seems like an unintended â'featureâ' to meâ?¦
Anyone else out there have any ideas?
Thanks,
-Nathan
"Nathan" wrote:
> I tried the example you have given with the adventure works database. I am
> able to reproduce what you have with the connection type of â'Microsoft SQL
> Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
> having. I need to use this because of my requirements of using a dsn or udl
> file.
> Thanks again,
> -Nathan
>
> "Bruce L-C [MVP]" wrote:
> > I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> > create PROCEDURE [dbo].[Pr_TestingRSProblem]
> >
> > AS
> >
> > Select FirstName
> >
> > into #TEMP
> >
> > FROM person.contact
> >
> > SELECT distinct FirstName
> >
> > FROM #TEMP order by firstname
> >
> > return
> >
> > I did not use the wizard. I added a report. I went to the data tab. Added a
> > new dataset. My shared data source credentials were windows authentication.
> > When creating the dataset I changed the data type to stored procedure and
> > put in the name of the stored procedure as the query string (do not put in
> > exec, just put in the name of the procedure). I get an error and no field
> > list. I click on refresh the field list I get a field list and now I can
> > execute the query and get data back in the data tab.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> > >
> > > I meant to mention that I tried the sp with and without explicitly
> > > dropping
> > > the table at the end. Neither way worked.
> > >
> > > "Nathan" wrote:
> > >
> > >> I am having an issue when converting some of my reports to SQL reporting
> > >> services 2005. All of my reports use an OLEDB connection that point to a
> > >> UDL
> > >> file so that we can easily change connection strings as needed without
> > >> having
> > >> to touch the report server. They all use stored procedures to obtain the
> > >> data. The issues I am running into is with stored procedures that use
> > >> temp
> > >> tables. Anything that is done in a stored procedure after the temp table
> > >> is
> > >> created is not shown in the report designer on the data tab within visual
> > >> studio 2005. I've read a few posts about this problem but it seems like
> > >> no
> > >> one at Microsoft has been able to reproduce it. I have been able to
> > >> reproduce it with a simple example using the NorthWind database. I will
> > >> include the stored procedure and the rdl file below. I set the report to
> > >> prompt for credentials instead of using the UDL file but the same problem
> > >> occurs. It will occur with Windows authentication as well. If I change
> > >> the
> > >> connection type to "Microsoft SQL Server" then it will work correctly the
> > >> problem is that I will not be able to use UDL or DSN files. My method
> > >> worked
> > >> just fine with RS 2000. What has changed with RS 2005 to cause this
> > >> issue?
> > >> Any ideas on how I might get this to work?
> > >>
> > >> Thanks,
> > >> -Nathan
> > >>
> > >> SP Script-
> > >>
> > >> use northwind
> > >>
> > >> set ANSI_NULLS ON
> > >> set QUOTED_IDENTIFIER OFF
> > >> GO
> > >>
> > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> > >> AS
> > >>
> > >> Select FirstName
> > >> into #EmployeeTemp
> > >> FROM Employees
> > >> --*NOTE It doesn't matter what you do here it will not be returned in the
> > >> Report Designer Data tab.
> > >> SELECT FirstName
> > >> FROM #EmployeeTemp
> > >> --Select 'Test'
> > >>
> > >>
> > >> RDL File-
> > >>
> > >> <?xml version="1.0" encoding="utf-8"?>
> > >> <Report
> > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > >> <DataSources>
> > >> <DataSource Name="DataSource1">
> > >> <ConnectionProperties>
> > >> <Prompt>Specify a user name and password for data source
> > >> DataSource1</Prompt>
> > >> <ConnectString>Provider=SQLOLEDB.1;Data
> > >> Source=saintdevsql1;Initial
> > >> Catalog=Northwind</ConnectString>
> > >> <DataProvider>OLEDB</DataProvider>
> > >> </ConnectionProperties>
> > >>
> > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> > >> </DataSource>
> > >> </DataSources>
> > >> <BottomMargin>1in</BottomMargin>
> > >> <RightMargin>1in</RightMargin>
> > >> <rd:DrawGrid>true</rd:DrawGrid>
> > >> <InteractiveWidth>8.5in</InteractiveWidth>
> > >> <rd:SnapToGrid>true</rd:SnapToGrid>
> > >> <Body>
> > >> <Height>0.75in</Height>
> > >> </Body>
> > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> > >> <LeftMargin>1in</LeftMargin>
> > >> <DataSets>
> > >> <DataSet Name="Northwind">
> > >> <Query>
> > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > >> <CommandType>StoredProcedure</CommandType>
> > >> <CommandText>Pr_TestingRSProblem</CommandText>
> > >> <DataSourceName>DataSource1</DataSourceName>
> > >> </Query>
> > >> </DataSet>
> > >> </DataSets>
> > >> <Width>6.5in</Width>
> > >> <InteractiveHeight>11in</InteractiveHeight>
> > >> <Language>en-US</Language>
> > >> <TopMargin>1in</TopMargin>
> > >> </Report>
> > >>
> >
> >
> >|||Iâ'm thinking of starting a support ticket with Microsoft about this issue.
Before I do does anyone know of a reason why the behavior of the OLEDB
connection would change between rs2000 and rs2005? Does anyone know of any
hot fixes or SPs that might already fix this issue? Iâ'm at a loss at what to
do at this point. This is kind of a show stopper for us with moving to
rs2005.
Thanks,
-Nathan
"Nathan" wrote:
> I must not have refreshed before sending my last reply. Looks like you did
> try the OLEDB. Well, thanks for trying. At least I know I'm not going crazy
> and it is reproducible and may actually be an issue with reporting services.
> The funny thing is that if you try making the same report in the previous
> version of RS within VS2003 it works fine. That is with it connecting to the
> same DB server (SQL 2005). This seems like an unintended â'featureâ' to meâ?¦
> Anyone else out there have any ideas?
> Thanks,
> -Nathan
>
> "Nathan" wrote:
> > I tried the example you have given with the adventure works database. I am
> > able to reproduce what you have with the connection type of â'Microsoft SQL
> > Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
> > having. I need to use this because of my requirements of using a dsn or udl
> > file.
> >
> > Thanks again,
> > -Nathan
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> > > create PROCEDURE [dbo].[Pr_TestingRSProblem]
> > >
> > > AS
> > >
> > > Select FirstName
> > >
> > > into #TEMP
> > >
> > > FROM person.contact
> > >
> > > SELECT distinct FirstName
> > >
> > > FROM #TEMP order by firstname
> > >
> > > return
> > >
> > > I did not use the wizard. I added a report. I went to the data tab. Added a
> > > new dataset. My shared data source credentials were windows authentication.
> > > When creating the dataset I changed the data type to stored procedure and
> > > put in the name of the stored procedure as the query string (do not put in
> > > exec, just put in the name of the procedure). I get an error and no field
> > > list. I click on refresh the field list I get a field list and now I can
> > > execute the query and get data back in the data tab.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> > > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> > > >
> > > > I meant to mention that I tried the sp with and without explicitly
> > > > dropping
> > > > the table at the end. Neither way worked.
> > > >
> > > > "Nathan" wrote:
> > > >
> > > >> I am having an issue when converting some of my reports to SQL reporting
> > > >> services 2005. All of my reports use an OLEDB connection that point to a
> > > >> UDL
> > > >> file so that we can easily change connection strings as needed without
> > > >> having
> > > >> to touch the report server. They all use stored procedures to obtain the
> > > >> data. The issues I am running into is with stored procedures that use
> > > >> temp
> > > >> tables. Anything that is done in a stored procedure after the temp table
> > > >> is
> > > >> created is not shown in the report designer on the data tab within visual
> > > >> studio 2005. I've read a few posts about this problem but it seems like
> > > >> no
> > > >> one at Microsoft has been able to reproduce it. I have been able to
> > > >> reproduce it with a simple example using the NorthWind database. I will
> > > >> include the stored procedure and the rdl file below. I set the report to
> > > >> prompt for credentials instead of using the UDL file but the same problem
> > > >> occurs. It will occur with Windows authentication as well. If I change
> > > >> the
> > > >> connection type to "Microsoft SQL Server" then it will work correctly the
> > > >> problem is that I will not be able to use UDL or DSN files. My method
> > > >> worked
> > > >> just fine with RS 2000. What has changed with RS 2005 to cause this
> > > >> issue?
> > > >> Any ideas on how I might get this to work?
> > > >>
> > > >> Thanks,
> > > >> -Nathan
> > > >>
> > > >> SP Script-
> > > >>
> > > >> use northwind
> > > >>
> > > >> set ANSI_NULLS ON
> > > >> set QUOTED_IDENTIFIER OFF
> > > >> GO
> > > >>
> > > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> > > >> AS
> > > >>
> > > >> Select FirstName
> > > >> into #EmployeeTemp
> > > >> FROM Employees
> > > >> --*NOTE It doesn't matter what you do here it will not be returned in the
> > > >> Report Designer Data tab.
> > > >> SELECT FirstName
> > > >> FROM #EmployeeTemp
> > > >> --Select 'Test'
> > > >>
> > > >>
> > > >> RDL File-
> > > >>
> > > >> <?xml version="1.0" encoding="utf-8"?>
> > > >> <Report
> > > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> > > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > > >> <DataSources>
> > > >> <DataSource Name="DataSource1">
> > > >> <ConnectionProperties>
> > > >> <Prompt>Specify a user name and password for data source
> > > >> DataSource1</Prompt>
> > > >> <ConnectString>Provider=SQLOLEDB.1;Data
> > > >> Source=saintdevsql1;Initial
> > > >> Catalog=Northwind</ConnectString>
> > > >> <DataProvider>OLEDB</DataProvider>
> > > >> </ConnectionProperties>
> > > >>
> > > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> > > >> </DataSource>
> > > >> </DataSources>
> > > >> <BottomMargin>1in</BottomMargin>
> > > >> <RightMargin>1in</RightMargin>
> > > >> <rd:DrawGrid>true</rd:DrawGrid>
> > > >> <InteractiveWidth>8.5in</InteractiveWidth>
> > > >> <rd:SnapToGrid>true</rd:SnapToGrid>
> > > >> <Body>
> > > >> <Height>0.75in</Height>
> > > >> </Body>
> > > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> > > >> <LeftMargin>1in</LeftMargin>
> > > >> <DataSets>
> > > >> <DataSet Name="Northwind">
> > > >> <Query>
> > > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > >> <CommandType>StoredProcedure</CommandType>
> > > >> <CommandText>Pr_TestingRSProblem</CommandText>
> > > >> <DataSourceName>DataSource1</DataSourceName>
> > > >> </Query>
> > > >> </DataSet>
> > > >> </DataSets>
> > > >> <Width>6.5in</Width>
> > > >> <InteractiveHeight>11in</InteractiveHeight>
> > > >> <Language>en-US</Language>
> > > >> <TopMargin>1in</TopMargin>
> > > >> </Report>
> > > >>
> > >
> > >
> > >|||This is the first I have heard of it on the newsgroups. I am not aware of
any hot fixes (SP1 makes no difference).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:18D44662-1B40-41D9-B4A0-049F8A893B76@.microsoft.com...
> I'm thinking of starting a support ticket with Microsoft about this issue.
> Before I do does anyone know of a reason why the behavior of the OLEDB
> connection would change between rs2000 and rs2005? Does anyone know of
> any
> hot fixes or SPs that might already fix this issue? I'm at a loss at what
> to
> do at this point. This is kind of a show stopper for us with moving to
> rs2005.
> Thanks,
> -Nathan
>
> "Nathan" wrote:
>> I must not have refreshed before sending my last reply. Looks like you
>> did
>> try the OLEDB. Well, thanks for trying. At least I know I'm not going
>> crazy
>> and it is reproducible and may actually be an issue with reporting
>> services.
>> The funny thing is that if you try making the same report in the previous
>> version of RS within VS2003 it works fine. That is with it connecting to
>> the
>> same DB server (SQL 2005). This seems like an unintended "feature" to
>> me.
>> Anyone else out there have any ideas?
>> Thanks,
>> -Nathan
>>
>> "Nathan" wrote:
>> > I tried the example you have given with the adventure works database.
>> > I am
>> > able to reproduce what you have with the connection type of "Microsoft
>> > SQL
>> > Server". However, if you select "OLEDB" you will encounter the problem
>> > I am
>> > having. I need to use this because of my requirements of using a dsn
>> > or udl
>> > file.
>> >
>> > Thanks again,
>> > -Nathan
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > I don't have northwind, I have AdventureWorks. Here is my stored
>> > > procedure:
>> > > create PROCEDURE [dbo].[Pr_TestingRSProblem]
>> > >
>> > > AS
>> > >
>> > > Select FirstName
>> > >
>> > > into #TEMP
>> > >
>> > > FROM person.contact
>> > >
>> > > SELECT distinct FirstName
>> > >
>> > > FROM #TEMP order by firstname
>> > >
>> > > return
>> > >
>> > > I did not use the wizard. I added a report. I went to the data tab.
>> > > Added a
>> > > new dataset. My shared data source credentials were windows
>> > > authentication.
>> > > When creating the dataset I changed the data type to stored procedure
>> > > and
>> > > put in the name of the stored procedure as the query string (do not
>> > > put in
>> > > exec, just put in the name of the procedure). I get an error and no
>> > > field
>> > > list. I click on refresh the field list I get a field list and now I
>> > > can
>> > > execute the query and get data back in the data tab.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > >
>> > > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> > > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
>> > > >
>> > > > I meant to mention that I tried the sp with and without explicitly
>> > > > dropping
>> > > > the table at the end. Neither way worked.
>> > > >
>> > > > "Nathan" wrote:
>> > > >
>> > > >> I am having an issue when converting some of my reports to SQL
>> > > >> reporting
>> > > >> services 2005. All of my reports use an OLEDB connection that
>> > > >> point to a
>> > > >> UDL
>> > > >> file so that we can easily change connection strings as needed
>> > > >> without
>> > > >> having
>> > > >> to touch the report server. They all use stored procedures to
>> > > >> obtain the
>> > > >> data. The issues I am running into is with stored procedures that
>> > > >> use
>> > > >> temp
>> > > >> tables. Anything that is done in a stored procedure after the
>> > > >> temp table
>> > > >> is
>> > > >> created is not shown in the report designer on the data tab within
>> > > >> visual
>> > > >> studio 2005. I've read a few posts about this problem but it
>> > > >> seems like
>> > > >> no
>> > > >> one at Microsoft has been able to reproduce it. I have been able
>> > > >> to
>> > > >> reproduce it with a simple example using the NorthWind database.
>> > > >> I will
>> > > >> include the stored procedure and the rdl file below. I set the
>> > > >> report to
>> > > >> prompt for credentials instead of using the UDL file but the same
>> > > >> problem
>> > > >> occurs. It will occur with Windows authentication as well. If I
>> > > >> change
>> > > >> the
>> > > >> connection type to "Microsoft SQL Server" then it will work
>> > > >> correctly the
>> > > >> problem is that I will not be able to use UDL or DSN files. My
>> > > >> method
>> > > >> worked
>> > > >> just fine with RS 2000. What has changed with RS 2005 to cause
>> > > >> this
>> > > >> issue?
>> > > >> Any ideas on how I might get this to work?
>> > > >>
>> > > >> Thanks,
>> > > >> -Nathan
>> > > >>
>> > > >> SP Script-
>> > > >>
>> > > >> use northwind
>> > > >>
>> > > >> set ANSI_NULLS ON
>> > > >> set QUOTED_IDENTIFIER OFF
>> > > >> GO
>> > > >>
>> > > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> > > >> AS
>> > > >>
>> > > >> Select FirstName
>> > > >> into #EmployeeTemp
>> > > >> FROM Employees
>> > > >> --*NOTE It doesn't matter what you do here it will not be returned
>> > > >> in the
>> > > >> Report Designer Data tab.
>> > > >> SELECT FirstName
>> > > >> FROM #EmployeeTemp
>> > > >> --Select 'Test'
>> > > >>
>> > > >>
>> > > >> RDL File-
>> > > >>
>> > > >> <?xml version="1.0" encoding="utf-8"?>
>> > > >> <Report
>> > > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> > > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> > > >> <DataSources>
>> > > >> <DataSource Name="DataSource1">
>> > > >> <ConnectionProperties>
>> > > >> <Prompt>Specify a user name and password for data source
>> > > >> DataSource1</Prompt>
>> > > >> <ConnectString>Provider=SQLOLEDB.1;Data
>> > > >> Source=saintdevsql1;Initial
>> > > >> Catalog=Northwind</ConnectString>
>> > > >> <DataProvider>OLEDB</DataProvider>
>> > > >> </ConnectionProperties>
>> > > >>
>> > > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> > > >> </DataSource>
>> > > >> </DataSources>
>> > > >> <BottomMargin>1in</BottomMargin>
>> > > >> <RightMargin>1in</RightMargin>
>> > > >> <rd:DrawGrid>true</rd:DrawGrid>
>> > > >> <InteractiveWidth>8.5in</InteractiveWidth>
>> > > >> <rd:SnapToGrid>true</rd:SnapToGrid>
>> > > >> <Body>
>> > > >> <Height>0.75in</Height>
>> > > >> </Body>
>> > > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> > > >> <LeftMargin>1in</LeftMargin>
>> > > >> <DataSets>
>> > > >> <DataSet Name="Northwind">
>> > > >> <Query>
>> > > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> > > >> <CommandType>StoredProcedure</CommandType>
>> > > >> <CommandText>Pr_TestingRSProblem</CommandText>
>> > > >> <DataSourceName>DataSource1</DataSourceName>
>> > > >> </Query>
>> > > >> </DataSet>
>> > > >> </DataSets>
>> > > >> <Width>6.5in</Width>
>> > > >> <InteractiveHeight>11in</InteractiveHeight>
>> > > >> <Language>en-US</Language>
>> > > >> <TopMargin>1in</TopMargin>
>> > > >> </Report>
>> > > >>
>> > >
>> > >
>> > >
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Issueing a CREATE TABLE statement within a transaction
Sorry new to the newsgroups so please point me to where I need to be if this is the wrong group. Below is a chunck of code that I have written to play around with transactions in Visual Studion 2005. I want to be able to create a table, the columns in it, and add rows of data and if any of those statements fail I woudl like to rollback the entire transaction. My testing has shown that when the CREATE TABLE sql is issued it automatcially commits. In my example below the VER_INFO table is immediately created when the ExecuteNonQuery() statement is invoked. The next 2 insert statements are held correctly in the transaction, however when the second table, WADES_TEST is created that causes the 2 rows to be inserted and the second table is immediately created. So my commit statement is useless. Is this normal? Is there a way to force the system to keep the CREATE TABLE statement in the transaction so I can roll it back if needed?
OracleCommand cmd = new OracleCommand();
OracleConnection wConnection = new OracleConnection(wConnString);
wConnection.Open();
OracleTransaction trans = wConnection.BeginTransaction();
string wQry = "CREATE TABLE VER_INFO (VER NVARCHAR2(25))";
cmd.CommandText = wQry;
cmd.Connection = wConnection;
cmd.Transaction = trans;
try
{
cmd.ExecuteNonQuery();
wQry = "INSERT INTO VER_INFO VALUES ('INITIALIZED')";
OracleCommand cmd4 = new OracleCommand(wQry, wConnection, trans);
cmd4.ExecuteNonQuery();
wQry = "INSERT INTO VER_INFO VALUES ('testss')";
OracleCommand cmd2 = new OracleCommand(wQry, wConnection, trans);
cmd2.ExecuteNonQuery();
wQry = "CREATE TABLE WADES_TEST (WADE NVARCHAR2(25))";
OracleCommand cmd3 = new OracleCommand(wQry, wConnection, trans);
cmd3.ExecuteNonQuery();
trans.Commit();
wConnection.Dispose();
}
catch
{
trans.Rollback();
wConnection.Close();
}
Thanks in advance,
Wade Sharp
This might be more appropriate to be posted to an Oracle forum or newsgroup. Or to an ADO.NET forum.Friday, March 9, 2012
Issue with ReportViewer dll and accessing reports
Question,
I have an external web server that uses the ReportViewer dll to point to a
Report Server located internally - Not visible to the outside world just to
the intranet. I get a page not found error when trying to view a report via
the reportviewer dll. I have a feeling this is being caused because the
serverurl property is set to point to the server inside the firewall. I can
run the web app just fine from the intranet but whenever I try externally I
get that page not found problem. I was under the impression that the Report
Server Dll was an ASP.Net server control and would run on the server but it
seems as if it is somehow tied to the client. The reason I think this is it
seems as if the client is looking for localhost which of course doesnt exist
on the clients end. Any help and or tips would be appreciated.
Thanks,
Scott EguiresHI, Scott:
I have the same issue. I have a external web cluster to host my website. And
I use the reportview web control to embed the report to my website. My report
server is internally. It works ok from inside, but from outside, I get the
result "The page can not be display". I think it can not find the server.
Did you solve your problem finally? And how did you solve it?
Thanks a lot.
-summer
"Scott Eguires" wrote:
> Question,
> I have an external web server that uses the ReportViewer dll to point to a
> Report Server located internally - Not visible to the outside world just to
> the intranet. I get a page not found error when trying to view a report via
> the reportviewer dll. I have a feeling this is being caused because the
> serverurl property is set to point to the server inside the firewall. I can
> run the web app just fine from the intranet but whenever I try externally I
> get that page not found problem. I was under the impression that the Report
> Server Dll was an ASP.Net server control and would run on the server but it
> seems as if it is somehow tied to the client. The reason I think this is it
> seems as if the client is looking for localhost which of course doesnt exist
> on the clients end. Any help and or tips would be appreciated.
> Thanks,
> Scott Eguires|||Yes I did. I wrote my own report viewer control to view the reports
externally without having to pay for an extra SQL Server license. It was a
real pain but it works beautifully and has a fairly nice interface. I still
need to add security to it though.
Scott Eguires
"summer" wrote:
> HI, Scott:
> I have the same issue. I have a external web cluster to host my website. And
> I use the reportview web control to embed the report to my website. My report
> server is internally. It works ok from inside, but from outside, I get the
> result "The page can not be display". I think it can not find the server.
> Did you solve your problem finally? And how did you solve it?
> Thanks a lot.
> -summer
> "Scott Eguires" wrote:
> > Question,
> >
> > I have an external web server that uses the ReportViewer dll to point to a
> > Report Server located internally - Not visible to the outside world just to
> > the intranet. I get a page not found error when trying to view a report via
> > the reportviewer dll. I have a feeling this is being caused because the
> > serverurl property is set to point to the server inside the firewall. I can
> > run the web app just fine from the intranet but whenever I try externally I
> > get that page not found problem. I was under the impression that the Report
> > Server Dll was an ASP.Net server control and would run on the server but it
> > seems as if it is somehow tied to the client. The reason I think this is it
> > seems as if the client is looking for localhost which of course doesnt exist
> > on the clients end. Any help and or tips would be appreciated.
> >
> > Thanks,
> >
> > Scott Eguires|||Do I need an extra SQL license if I only install the Reoprt server on the web
server, and keep the Report database on another machine with already has a
SQL license?
Do you mind to tell me how you build your own report viewer? Did you use SOAP?
Thanks a lot.
"Scott Eguires" wrote:
> Yes I did. I wrote my own report viewer control to view the reports
> externally without having to pay for an extra SQL Server license. It was a
> real pain but it works beautifully and has a fairly nice interface. I still
> need to add security to it though.
> Scott Eguires
> "summer" wrote:
> > HI, Scott:
> >
> > I have the same issue. I have a external web cluster to host my website. And
> > I use the reportview web control to embed the report to my website. My report
> > server is internally. It works ok from inside, but from outside, I get the
> > result "The page can not be display". I think it can not find the server.
> >
> > Did you solve your problem finally? And how did you solve it?
> >
> > Thanks a lot.
> >
> > -summer
> > "Scott Eguires" wrote:
> >
> > > Question,
> > >
> > > I have an external web server that uses the ReportViewer dll to point to a
> > > Report Server located internally - Not visible to the outside world just to
> > > the intranet. I get a page not found error when trying to view a report via
> > > the reportviewer dll. I have a feeling this is being caused because the
> > > serverurl property is set to point to the server inside the firewall. I can
> > > run the web app just fine from the intranet but whenever I try externally I
> > > get that page not found problem. I was under the impression that the Report
> > > Server Dll was an ASP.Net server control and would run on the server but it
> > > seems as if it is somehow tied to the client. The reason I think this is it
> > > seems as if the client is looking for localhost which of course doesnt exist
> > > on the clients end. Any help and or tips would be appreciated.
> > >
> > > Thanks,
> > >
> > > Scott Eguires|||Yes you do need another SQL Server license in this scenario. That is why I
wrote my own Report Manager/Viewer. I can give you more information on
Monday or Tuesday of next week as I am currently out of town on a business
trip. You can email me at scott@.eguires.net for further discussions if you
wish and I can show you what I have done thus far and you can give me your
opinion on it.
Thanks,
Scott Eguires
"summer" wrote:
> Do I need an extra SQL license if I only install the Reoprt server on the web
> server, and keep the Report database on another machine with already has a
> SQL license?
> Do you mind to tell me how you build your own report viewer? Did you use SOAP?
> Thanks a lot.
> "Scott Eguires" wrote:
> > Yes I did. I wrote my own report viewer control to view the reports
> > externally without having to pay for an extra SQL Server license. It was a
> > real pain but it works beautifully and has a fairly nice interface. I still
> > need to add security to it though.
> >
> > Scott Eguires
> >
> > "summer" wrote:
> >
> > > HI, Scott:
> > >
> > > I have the same issue. I have a external web cluster to host my website. And
> > > I use the reportview web control to embed the report to my website. My report
> > > server is internally. It works ok from inside, but from outside, I get the
> > > result "The page can not be display". I think it can not find the server.
> > >
> > > Did you solve your problem finally? And how did you solve it?
> > >
> > > Thanks a lot.
> > >
> > > -summer
> > > "Scott Eguires" wrote:
> > >
> > > > Question,
> > > >
> > > > I have an external web server that uses the ReportViewer dll to point to a
> > > > Report Server located internally - Not visible to the outside world just to
> > > > the intranet. I get a page not found error when trying to view a report via
> > > > the reportviewer dll. I have a feeling this is being caused because the
> > > > serverurl property is set to point to the server inside the firewall. I can
> > > > run the web app just fine from the intranet but whenever I try externally I
> > > > get that page not found problem. I was under the impression that the Report
> > > > Server Dll was an ASP.Net server control and would run on the server but it
> > > > seems as if it is somehow tied to the client. The reason I think this is it
> > > > seems as if the client is looking for localhost which of course doesnt exist
> > > > on the clients end. Any help and or tips would be appreciated.
> > > >
> > > > Thanks,
> > > >
> > > > Scott Eguires
I have an external web server that uses the ReportViewer dll to point to a
Report Server located internally - Not visible to the outside world just to
the intranet. I get a page not found error when trying to view a report via
the reportviewer dll. I have a feeling this is being caused because the
serverurl property is set to point to the server inside the firewall. I can
run the web app just fine from the intranet but whenever I try externally I
get that page not found problem. I was under the impression that the Report
Server Dll was an ASP.Net server control and would run on the server but it
seems as if it is somehow tied to the client. The reason I think this is it
seems as if the client is looking for localhost which of course doesnt exist
on the clients end. Any help and or tips would be appreciated.
Thanks,
Scott EguiresHI, Scott:
I have the same issue. I have a external web cluster to host my website. And
I use the reportview web control to embed the report to my website. My report
server is internally. It works ok from inside, but from outside, I get the
result "The page can not be display". I think it can not find the server.
Did you solve your problem finally? And how did you solve it?
Thanks a lot.
-summer
"Scott Eguires" wrote:
> Question,
> I have an external web server that uses the ReportViewer dll to point to a
> Report Server located internally - Not visible to the outside world just to
> the intranet. I get a page not found error when trying to view a report via
> the reportviewer dll. I have a feeling this is being caused because the
> serverurl property is set to point to the server inside the firewall. I can
> run the web app just fine from the intranet but whenever I try externally I
> get that page not found problem. I was under the impression that the Report
> Server Dll was an ASP.Net server control and would run on the server but it
> seems as if it is somehow tied to the client. The reason I think this is it
> seems as if the client is looking for localhost which of course doesnt exist
> on the clients end. Any help and or tips would be appreciated.
> Thanks,
> Scott Eguires|||Yes I did. I wrote my own report viewer control to view the reports
externally without having to pay for an extra SQL Server license. It was a
real pain but it works beautifully and has a fairly nice interface. I still
need to add security to it though.
Scott Eguires
"summer" wrote:
> HI, Scott:
> I have the same issue. I have a external web cluster to host my website. And
> I use the reportview web control to embed the report to my website. My report
> server is internally. It works ok from inside, but from outside, I get the
> result "The page can not be display". I think it can not find the server.
> Did you solve your problem finally? And how did you solve it?
> Thanks a lot.
> -summer
> "Scott Eguires" wrote:
> > Question,
> >
> > I have an external web server that uses the ReportViewer dll to point to a
> > Report Server located internally - Not visible to the outside world just to
> > the intranet. I get a page not found error when trying to view a report via
> > the reportviewer dll. I have a feeling this is being caused because the
> > serverurl property is set to point to the server inside the firewall. I can
> > run the web app just fine from the intranet but whenever I try externally I
> > get that page not found problem. I was under the impression that the Report
> > Server Dll was an ASP.Net server control and would run on the server but it
> > seems as if it is somehow tied to the client. The reason I think this is it
> > seems as if the client is looking for localhost which of course doesnt exist
> > on the clients end. Any help and or tips would be appreciated.
> >
> > Thanks,
> >
> > Scott Eguires|||Do I need an extra SQL license if I only install the Reoprt server on the web
server, and keep the Report database on another machine with already has a
SQL license?
Do you mind to tell me how you build your own report viewer? Did you use SOAP?
Thanks a lot.
"Scott Eguires" wrote:
> Yes I did. I wrote my own report viewer control to view the reports
> externally without having to pay for an extra SQL Server license. It was a
> real pain but it works beautifully and has a fairly nice interface. I still
> need to add security to it though.
> Scott Eguires
> "summer" wrote:
> > HI, Scott:
> >
> > I have the same issue. I have a external web cluster to host my website. And
> > I use the reportview web control to embed the report to my website. My report
> > server is internally. It works ok from inside, but from outside, I get the
> > result "The page can not be display". I think it can not find the server.
> >
> > Did you solve your problem finally? And how did you solve it?
> >
> > Thanks a lot.
> >
> > -summer
> > "Scott Eguires" wrote:
> >
> > > Question,
> > >
> > > I have an external web server that uses the ReportViewer dll to point to a
> > > Report Server located internally - Not visible to the outside world just to
> > > the intranet. I get a page not found error when trying to view a report via
> > > the reportviewer dll. I have a feeling this is being caused because the
> > > serverurl property is set to point to the server inside the firewall. I can
> > > run the web app just fine from the intranet but whenever I try externally I
> > > get that page not found problem. I was under the impression that the Report
> > > Server Dll was an ASP.Net server control and would run on the server but it
> > > seems as if it is somehow tied to the client. The reason I think this is it
> > > seems as if the client is looking for localhost which of course doesnt exist
> > > on the clients end. Any help and or tips would be appreciated.
> > >
> > > Thanks,
> > >
> > > Scott Eguires|||Yes you do need another SQL Server license in this scenario. That is why I
wrote my own Report Manager/Viewer. I can give you more information on
Monday or Tuesday of next week as I am currently out of town on a business
trip. You can email me at scott@.eguires.net for further discussions if you
wish and I can show you what I have done thus far and you can give me your
opinion on it.
Thanks,
Scott Eguires
"summer" wrote:
> Do I need an extra SQL license if I only install the Reoprt server on the web
> server, and keep the Report database on another machine with already has a
> SQL license?
> Do you mind to tell me how you build your own report viewer? Did you use SOAP?
> Thanks a lot.
> "Scott Eguires" wrote:
> > Yes I did. I wrote my own report viewer control to view the reports
> > externally without having to pay for an extra SQL Server license. It was a
> > real pain but it works beautifully and has a fairly nice interface. I still
> > need to add security to it though.
> >
> > Scott Eguires
> >
> > "summer" wrote:
> >
> > > HI, Scott:
> > >
> > > I have the same issue. I have a external web cluster to host my website. And
> > > I use the reportview web control to embed the report to my website. My report
> > > server is internally. It works ok from inside, but from outside, I get the
> > > result "The page can not be display". I think it can not find the server.
> > >
> > > Did you solve your problem finally? And how did you solve it?
> > >
> > > Thanks a lot.
> > >
> > > -summer
> > > "Scott Eguires" wrote:
> > >
> > > > Question,
> > > >
> > > > I have an external web server that uses the ReportViewer dll to point to a
> > > > Report Server located internally - Not visible to the outside world just to
> > > > the intranet. I get a page not found error when trying to view a report via
> > > > the reportviewer dll. I have a feeling this is being caused because the
> > > > serverurl property is set to point to the server inside the firewall. I can
> > > > run the web app just fine from the intranet but whenever I try externally I
> > > > get that page not found problem. I was under the impression that the Report
> > > > Server Dll was an ASP.Net server control and would run on the server but it
> > > > seems as if it is somehow tied to the client. The reason I think this is it
> > > > seems as if the client is looking for localhost which of course doesnt exist
> > > > on the clients end. Any help and or tips would be appreciated.
> > > >
> > > > Thanks,
> > > >
> > > > Scott Eguires
Subscribe to:
Posts (Atom)