Hi...
I have this small four tables
people People_Department Department nacionality
-peopleID -People -DepartmentID -nacionID
-NamePeople -Department -NameDepartment -nameNacion
-NacionPeople
and my stored procedure is this..
select NamePeople, NameDepartment, namenacion from people
inner join nacionality on Nacion_People=nacionID
inner join People_Department on peopleID = People
iinner join Department on DepartmentID = Department
Only i obtain a row of result, and in the table People Department i have a person that works in two departments...
i need this kind of result:
Name Department
-- --
Peter Financial
Peter Computers
but i only obtain, if i search for peter:
Name Department
-- --
Peter Financial
Thanks in advance.
Josema.I'd check the data and the constraints. I have some tables in a database of
mine that have similar relationships (2 tables with a domain relationship,
one of which is connected through a linking table to another table) and it
works just fine. Here was my query with the following tables:
tb_clarify_repl = people
tb_facility_info = nacionality
tb_pkg_audit_rollup = People_Department
tb_pkg_info = department
select t.machine_name, i.pkg_name, f.facility_name
from tb_clarify_repl t inner join tb_facility_info f
on t.facility_id = f.facility_id
inner join tb_pkg_audit_rollup r
on t.machine_id = r.machine_id
inner join tb_pkg_info i
on r.pkg_id = i.pkg_id
Check the data and the relationships and make sure they are what you think.
I think what you have should work.
Christian Smith
"josema" <anonymous@.discussions.microsoft.com> wrote in message
news:0ACCF892-FA22-4ACF-8189-79CAE6F586A5@.microsoft.com...
> Hi...
> I have this small four tables
> people People_Department Department
nacionality
> -peopleID -People -DepartmentID
-nacionID
> -NamePeople -Department -NameDepartment -na
meNacion
> -NacionPeople
> and my stored procedure is this..
> select NamePeople, NameDepartment, namenacion from people
> inner join nacionality on Nacion_People=nacionID
> inner join People_Department on peopleID = People
> iinner join Department on DepartmentID = Department
> Only i obtain a row of result, and in the table People Department i have a
person that works in two departments...
> i need this kind of result:
> Name Department
> -- --
> Peter Financial
> Peter Computers
> but i only obtain, if i search for peter:
> Name Department
> -- --
> Peter Financial
>
> Thanks in advance.
> Josema.
>
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Wednesday, March 28, 2012
Its been a looooong day ! Need help
Ok .. here's another problem I am facing ...
Its been a long day and my mind is really not working
I have three tables lets say
tab1
---
boss
month
Bonus
tab2
---
boss
employee
Each Boss is given some bonus amount ... he has to distribute it evenly among his employees. data for each boss monthly bonus is availible.
But what i need is amount per employee per day (and remember amount being recieved will vary upon bonus amount with boss aND NO OF day in month). I need to generate a tabe for the whole year containing records
taboutput
----
boss
employee
date
amount
Any Ideas ??Is the bonus distributed evenly to all the boss's employees regardless of the number of days they were employed?|||Thats another story ... its just a problematic representation of what i ve to do ...
A better analogy would be a target for the territory, and i need a daily volume for salespersons working in that territory ...
Lets say The data is given at Territorywise , Month level for the year and i need to split it at Salesperson, Day level for the year
So the problem now stands as
Tab1
--
Territory
Month
Target
Tab2
---
Territory
Sales_Person
Result needed as
TabOutput
-----
Territory
Sales_person
Date
Target
Its like i should be able to get the target till date for the salesperson for any date specified.|||Yes, but its important to know whether the number of salespeople in a territory can change from month to month, and where this information is stored. I suspect the reasons this is giving you trouble are
1) You are dead beat tired
and
2) You don't have all the information required to solve the problem
I worked in a sales organization for three years developing territory and compensation models for sale people. Their territories would change several times each year.|||Originally posted by blindman
Yes, but its important to know whether the number of salespeople in a territory can change from month to month, and where this information is stored. I suspect the reasons this is giving you trouble are
1) You are dead beat tired
and
2) You don't have all the information required to solve the problem
I worked in a sales organization for three years developing territory and compensation models for sale people. Their territories would change several times each year.
It was reason no 1... did have to take 7 cups of coffee to keep myself awake during the night ... Thanks .. have solved the problem for now :)|||Up all night, and then still went to (stayed at) work?
I hope you're hourly...|||Hmm .. would love to do hourly ... but my bad luck .. am on a fixed salary :(sql
Its been a long day and my mind is really not working
I have three tables lets say
tab1
---
boss
month
Bonus
tab2
---
boss
employee
Each Boss is given some bonus amount ... he has to distribute it evenly among his employees. data for each boss monthly bonus is availible.
But what i need is amount per employee per day (and remember amount being recieved will vary upon bonus amount with boss aND NO OF day in month). I need to generate a tabe for the whole year containing records
taboutput
----
boss
employee
date
amount
Any Ideas ??Is the bonus distributed evenly to all the boss's employees regardless of the number of days they were employed?|||Thats another story ... its just a problematic representation of what i ve to do ...
A better analogy would be a target for the territory, and i need a daily volume for salespersons working in that territory ...
Lets say The data is given at Territorywise , Month level for the year and i need to split it at Salesperson, Day level for the year
So the problem now stands as
Tab1
--
Territory
Month
Target
Tab2
---
Territory
Sales_Person
Result needed as
TabOutput
-----
Territory
Sales_person
Date
Target
Its like i should be able to get the target till date for the salesperson for any date specified.|||Yes, but its important to know whether the number of salespeople in a territory can change from month to month, and where this information is stored. I suspect the reasons this is giving you trouble are
1) You are dead beat tired
and
2) You don't have all the information required to solve the problem
I worked in a sales organization for three years developing territory and compensation models for sale people. Their territories would change several times each year.|||Originally posted by blindman
Yes, but its important to know whether the number of salespeople in a territory can change from month to month, and where this information is stored. I suspect the reasons this is giving you trouble are
1) You are dead beat tired
and
2) You don't have all the information required to solve the problem
I worked in a sales organization for three years developing territory and compensation models for sale people. Their territories would change several times each year.
It was reason no 1... did have to take 7 cups of coffee to keep myself awake during the night ... Thanks .. have solved the problem for now :)|||Up all night, and then still went to (stayed at) work?
I hope you're hourly...|||Hmm .. would love to do hourly ... but my bad luck .. am on a fixed salary :(sql
Iteration through many tables to perform update
...Ok so I have one field that exists in an many ARCHIVE tables(approx 25).
The field name is the same throughout these tables and each table is created
in weekly intervals.
I want to be able to update the field for all the rows in each of these
table in one lump of an update sProc.
My initial ideas areto query sysobjects for the tables I want and then
iterate through them one by one until the update is complete (maybe using
dynamic sql). This is a once off update and performance time is the key.
Any ideas / examples?>I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete th
e
update.
"marcmc" wrote:
> ...Ok so I have one field that exists in an many ARCHIVE tables(approx 25)
.
> The field name is the same throughout these tables and each table is creat
ed
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>|||Are all these tables of the same structure, i.e. exact same column names and
data types across all tables? If so, it sounds like a partitioned view.
You could then update the particular column.
Briefly, you create CHECK constraints on the partitioning column on each
table. The partitioning column must be part of the primary key. Then you
create a view like:
create view MyView
as
select * from MyTable1
union all
select * from MyTable2
union all
...
go
update MyView
set
MyCol = 'XYZ'
As for your window, large updates take time. You may want to do a
background iterative method, as long as logical consistency is not an issue.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8661C481-F4F0-4ED4-97E2-74BB47D70F4A@.microsoft.com...
I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete
the
update.
"marcmc" wrote:
> ...Ok so I have one field that exists in an many ARCHIVE tables(approx
> 25).
> The field name is the same throughout these tables and each table is
> created
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>
The field name is the same throughout these tables and each table is created
in weekly intervals.
I want to be able to update the field for all the rows in each of these
table in one lump of an update sProc.
My initial ideas areto query sysobjects for the tables I want and then
iterate through them one by one until the update is complete (maybe using
dynamic sql). This is a once off update and performance time is the key.
Any ideas / examples?>I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete th
e
update.
"marcmc" wrote:
> ...Ok so I have one field that exists in an many ARCHIVE tables(approx 25)
.
> The field name is the same throughout these tables and each table is creat
ed
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>|||Are all these tables of the same structure, i.e. exact same column names and
data types across all tables? If so, it sounds like a partitioned view.
You could then update the particular column.
Briefly, you create CHECK constraints on the partitioning column on each
table. The partitioning column must be part of the primary key. Then you
create a view like:
create view MyView
as
select * from MyTable1
union all
select * from MyTable2
union all
...
go
update MyView
set
MyCol = 'XYZ'
As for your window, large updates take time. You may want to do a
background iterative method, as long as logical consistency is not an issue.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8661C481-F4F0-4ED4-97E2-74BB47D70F4A@.microsoft.com...
I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete
the
update.
"marcmc" wrote:
> ...Ok so I have one field that exists in an many ARCHIVE tables(approx
> 25).
> The field name is the same throughout these tables and each table is
> created
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>
Iterating through tables
I want to create a stored procedure that will list all the tables for a
given database. I can get the databases using the sp_database function, but
the sp_tables requires that I be in database to use it. I want to create a
stored procedure that will take in the database name as a variable and
return a list of the tables for that database. I need to do this so as new
databases and tables are added and removed my program will have the up to
date data for the database. thanks.
JohnDECLARE @.dbname NVARCHAR(128), @.sql NVARCHAR(4000)
SET @.dbname = 'pubs'
SET @.sql =
'SELECT [table_name]
FROM '+QUOTENAME(@.dbname)+'.[information_schema].[tables]'
EXEC (@.sql)
David Portas
SQL Server MVP
--|||>> take in the database name as a variable and return a list of the tables f
or that database. <<
Have you considered that this is a METADATA problem and should be done
with sp_ routines and never in your own stored procedure? This will
lead to kludgfes with dynamic SQL, etc.
Gee, do you do that a lot? Do your users get to create tables, columns
or entire databases on the fly? Your whole approach sounds screwed up.
Would you like to expalin what you are doing so you can get a few
thousand dollars of freee consulting or do you just want a stinking
dirty little kludge?
No, your data dictionary should contain this kinfd of information, not
the programs.
given database. I can get the databases using the sp_database function, but
the sp_tables requires that I be in database to use it. I want to create a
stored procedure that will take in the database name as a variable and
return a list of the tables for that database. I need to do this so as new
databases and tables are added and removed my program will have the up to
date data for the database. thanks.
JohnDECLARE @.dbname NVARCHAR(128), @.sql NVARCHAR(4000)
SET @.dbname = 'pubs'
SET @.sql =
'SELECT [table_name]
FROM '+QUOTENAME(@.dbname)+'.[information_schema].[tables]'
EXEC (@.sql)
David Portas
SQL Server MVP
--|||>> take in the database name as a variable and return a list of the tables f
or that database. <<
Have you considered that this is a METADATA problem and should be done
with sp_ routines and never in your own stored procedure? This will
lead to kludgfes with dynamic SQL, etc.
Gee, do you do that a lot? Do your users get to create tables, columns
or entire databases on the fly? Your whole approach sounds screwed up.
Would you like to expalin what you are doing so you can get a few
thousand dollars of freee consulting or do you just want a stinking
dirty little kludge?
No, your data dictionary should contain this kinfd of information, not
the programs.
Iterating
Hi,
I have a 6 different textboxes in my web application. I have 6 different tables in my database such as tbl1,tbl2,tbl3 etc.
When the user clicks the submit button I have to check whether the values in the textboxes match the value in the database. (if in txt1 the user enters 3 I need to go to tbl1 and check if there is such a value).
What is the most efficient way to perform such a check? Will I need to write 6 select statements or can I use a loop and if I can use a loop I would appreciate an example
Thanks
Why do you need to do this?
If you are loading the value into the textbox to begin with, and you simply want to check whether the text has changed, there is a text changed event raised by the textbox. If that doesn't work for you, you could always store the initial value of the textbox in a custom property when you populate the textbox:
myTextBox1.Attributes.Add('initValue',myValue)
Then simply check to see whether it has changed.
If you MUST check these against values from the database (i.e.; for data volatility reasons), you should retrieve all six from the db in a single SQL query, if possible. It would be more efficient...
|||I need to do this coz the user enters a code in the textboxes and I need to check if such a code exists in the db and if there is no such a code then the user must select another code.
How can I retrieve all 6 values in a single query from 6 different tables?
Monday, March 26, 2012
Item count for Full-text Catalog is zero
After creating several full-text indexes from tables to a Full-text
Catalog, I can see the names of the tables for those indexes. However,
when double-clicking the Full-text Catalog, the the item count was still
zero.
I am sure the Microsoft Search Service started and there was a Full-Text
Search in EM's Support Services folder. My SQL Server databses is the
back-end database for a front-end SharePoint Services server.
Is something wrong with the configuration on my SQL Server databases, or
the SharePoint Services server?
TIA,
Jeffrey
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Jeffrey,
Most likely as this is a FAQ in this newsgroup... Review all of the
following KB articles:
317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/default...b;en-us;317746
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services]
http://support.microsoft.com/default...B;EN-US;277549
837367 How to turn on full-text search (FTS) in WSS (SharePoint) on a
Windows SBS 2003-based computer
http://support.microsoft.com/?kbid=837367
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeffrey Wang" <cjeffwang@.gmail.com> wrote in message
news:#ABhKdSIFHA.1528@.TK2MSFTNGP09.phx.gbl...
> After creating several full-text indexes from tables to a Full-text
> Catalog, I can see the names of the tables for those indexes. However,
> when double-clicking the Full-text Catalog, the the item count was still
> zero.
> I am sure the Microsoft Search Service started and there was a Full-Text
> Search in EM's Support Services folder. My SQL Server databses is the
> back-end database for a front-end SharePoint Services server.
> Is something wrong with the configuration on my SQL Server databases, or
> the SharePoint Services server?
> TIA,
> Jeffrey
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Catalog, I can see the names of the tables for those indexes. However,
when double-clicking the Full-text Catalog, the the item count was still
zero.
I am sure the Microsoft Search Service started and there was a Full-Text
Search in EM's Support Services folder. My SQL Server databses is the
back-end database for a front-end SharePoint Services server.
Is something wrong with the configuration on my SQL Server databases, or
the SharePoint Services server?
TIA,
Jeffrey
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Jeffrey,
Most likely as this is a FAQ in this newsgroup... Review all of the
following KB articles:
317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/default...b;en-us;317746
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services]
http://support.microsoft.com/default...B;EN-US;277549
837367 How to turn on full-text search (FTS) in WSS (SharePoint) on a
Windows SBS 2003-based computer
http://support.microsoft.com/?kbid=837367
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeffrey Wang" <cjeffwang@.gmail.com> wrote in message
news:#ABhKdSIFHA.1528@.TK2MSFTNGP09.phx.gbl...
> After creating several full-text indexes from tables to a Full-text
> Catalog, I can see the names of the tables for those indexes. However,
> when double-clicking the Full-text Catalog, the the item count was still
> zero.
> I am sure the Microsoft Search Service started and there was a Full-Text
> Search in EM's Support Services folder. My SQL Server databses is the
> back-end database for a front-end SharePoint Services server.
> Is something wrong with the configuration on my SQL Server databases, or
> the SharePoint Services server?
> TIA,
> Jeffrey
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Wednesday, March 21, 2012
Issues with temp tables in stored procedures using OLEDB connectio
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>
>> > > >>
>> > >
>> > >
>> > >
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>
>> > > >>
>> > >
>> > >
>> > >
Issues with LEFT join on four tables
Hi Folks,
Lets assume I have three tables. Their layout is as follows. Please
note that tblPeople does not have an entry for Denver (this is my
problem)
Lets assume I have three tables. Their layout is as follows. Please
note that tblPeople does not have an entry for Denver (this is my
problem)
tblCity
_________________
CityName OCID
LA 1
Denver 2
tblCars
_________________
OCID CarVolume
1 300,000
2 200,000
tblPeople
_________________
OCID PeopleVolume
1 1,200,345
tblDogs
_________________
OCID DogVolume
1 234,987
2 445,987
I'd like to run a quiery that returns the following data set:
CityName OCID CarVolume PeopleVolume DogVolume
LA 1 300000 1200345 234987
Denver 2 200000 null or 0 445997
My problem is the people table. Since there not entry for Denver, my
query is returning only a row for LA, nothing for Denver. Here's wht
my query looks like
Select tblCity.CityName, tblCity.OCID, tblCars.CarVolume,
tblPeople.PeopleVolume, tblDogs.DogVolume
FROM tblCity
LEFT JOIN tblCars on tblCity.OCID = tblCars.OCID
JOIN tblPeople on tblCars.OCID = tblPeople.OCID
JOIN tblDogs on tblPeople.OCID = tblDogs.OCID
This returns the following:
CityName OCID CarVolume PeopleVolume DogVolume
LA 1 300000 1200345 234987
I need this query to return a row for Denver as well. Any thoughts
anyone?
Your insight is greatly appreciated. ericlangland at hotmail.comEric,
If tblCity is your base (or primary) table, then you should be able to do
the following without problems. It appears you are very close to this
already.
select ci.ocid, ci.cityname, ca.carvolumn, p.peoplevolumn, d.dogvolumn
from tblcity ci
left join tblcars ca on ci.ocid = ca.ocid
left join tblpeople p on ci.ocid = p.ocid
left join tbldogs d on ci.ocid = d.ocid
HTH,
Greg
"Eric" <ericlangland@.hotmail.com> wrote in message
news:5372437.0403041742.6259dfad@.posting.google.co m...
> Hi Folks,
> Lets assume I have three tables. Their layout is as follows. Please
> note that tblPeople does not have an entry for Denver (this is my
> problem)
> tblCity
> _________________
> CityName OCID
> LA 1
> Denver 2
> tblCars
> _________________
> OCID CarVolume
> 1 300,000
> 2 200,000
> tblPeople
> _________________
> OCID PeopleVolume
> 1 1,200,345
> tblDogs
> _________________
> OCID DogVolume
> 1 234,987
> 2 445,987
> I'd like to run a quiery that returns the following data set:
> CityName OCID CarVolume PeopleVolume DogVolume
> LA 1 300000 1200345 234987
> Denver 2 200000 null or 0 445997
> My problem is the people table. Since there not entry for Denver, my
> query is returning only a row for LA, nothing for Denver. Here's wht
> my query looks like
> Select tblCity.CityName, tblCity.OCID, tblCars.CarVolume,
> tblPeople.PeopleVolume, tblDogs.DogVolume
> FROM tblCity
> LEFT JOIN tblCars on tblCity.OCID = tblCars.OCID
> JOIN tblPeople on tblCars.OCID = tblPeople.OCID
> JOIN tblDogs on tblPeople.OCID = tblDogs.OCID
> This returns the following:
> CityName OCID CarVolume PeopleVolume DogVolume
> LA 1 300000 1200345 234987
> I need this query to return a row for Denver as well. Any thoughts
> anyone?
> Your insight is greatly appreciated. ericlangland at hotmail.com
Monday, March 19, 2012
Issues passing Table Name as a parameter to a Stored Procedure
Hi Friends,
I use the stored procedure pr_Set_Min_Max_Ind to set some indicators in certain type of tables in my databases.
CREATE Procedure [dbo].[pr_Set_Min_Max_Ind] ( @.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName )
................
................
...
...
...
................
End
The procedure pr_Set_Min_Max_Ind takes five parameters ( @.t, @.k1, @.k2, @.r1, @.r2 ) of which
@.t is the table name in which I want to set some indicators and
the rest of the parameters (@.k1, @.k2, @.r1, @.r2) are column names in the table @.t
Say, I have this procedure stored in database A. Then to execute the procedure on table TestTable_A in database A , I write the statement as:
exec pr_Set_Min_Max_Ind TestTable_A, Column1, Column2, Column3, Column4 (statement-1)
Now, if I want to execute this procedure on a table TestTable_B in a different database B, I am NOT able to issue a statement like:
exec pr_Set_Min_Max_Ind B.dbo.TestTable_B, Column1, Column2, Column3, Column4 (statement-2)
The only way I now know to accomplish this is to save a copy of this procedure in database B and write a statement similar to statement-1. But, for some reasons I DO NOT want to do that. I just want to have the procedure stored in only one database and be able to pass the table names from other databases in the format database_name.dbo.table_name ;So, guys please let me know how I can pass the table name in the format shown as in statement-2 above.
Thanks in advance for taking time to give me a solution.
With Best Regards,
-Ram.
Depends on how you create your SQL statements based on the table names and column names that you pass to your stored procedure but you should be able to do a SELECT statement from another database in the same SQL Server. Try posting your SP so that we will be able to see where the problem could be.
SQL Server Helper
http://www.sql-server-helper.com
Hi,
The following is the SP that I was referring to.
Thank you.
--
CREATE Procedure [dbo].[pr_Set_Min_Max_Ind](@.t SysName, @.k1 SysName, @.k2 SysName, @.r1 SysName, @.r2 SysName)
As
Declare @.SQL Varchar(8000),
@.x SysName
Set @.x = '##_' + @.t + '_' + Replace(Convert(Char,GetDate(),114),':','')
Begin
-- Tables which have a Min_Ind column
IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Min_Ind') <> 0)
Begin
-- Rows where the very first column does not have Min_Ind set to Y
Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 + ' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ',' + @.r2 + ') = 1 Then ''Y'' Else ''N'' End New_Min_Ind, IsNull(Min_Ind,''-'') Old_Min_Ind From ' + @.t +') t Where Old_Min_Ind <> New_Min_Ind And New_Min_Ind = ''Y'' '
Print @.Sql
Exec(@.SQL)
-- Update any record based on key fields and set the Min_Ind to N if it is not N
Set @.SQL = 'Update a Set Min_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Min_Ind,''-'') <> ''N'' '
Print @.Sql
Exec(@.SQL)
-- Update table and set the Min_Ind to Y based on key and rank fields
Set @.SQL = 'Update a Set Min_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 +' And b.k2 = a.' + @.k2 + ' And b.r1 = a.' + @.r1 + ' And b.r2 = a.' + @.r2
Print @.Sql
Exec(@.SQL)
Exec('Drop Table ' + @.x)
End
-- Tables which have a Max_Ind column
IF ((Select Count(*) From Information_Schema.Columns Where Table_Name = @.t And Column_Name = 'Max_Ind') <> 0)
Begin
-- Rows where the very first column does not have Min_Ind set to Y
Set @.SQL = 'Select * Into ' + @.x + ' From ( Select ' + @.k1 + ' k1,' + @.k2 + ' k2,' + @.r1 + ' r1,' + @.r2 +' r2, Case When Rank() Over (Partition By ' + @.k1 + ',' + @.k2 + ' Order By ' + @.r1 + ' Desc,' + @.r2 + ' Desc) = 1 Then ''Y'' Else ''N'' End New_Max_Ind, IsNull(Max_Ind,''-'') Old_Max_Ind From ' + @.t +') t Where Old_Max_Ind <> New_Max_Ind And New_Max_Ind = ''Y'' '
Print @.sql
Exec(@.SQL)
-- Update any record based on key fields and set the Max_Ind to N if it is not N
Set @.SQL = 'Update a Set Max_Ind = ''N'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = a.' + @.k1 + ' And b.k2 = a.' + @.k2 + ' And IsNull(Max_Ind,''-'') <> ''N'''
Print @.sql
Exec(@.SQL)
-- Update table and set the Max_Ind to Y based on key and rank fields
Set @.SQL = 'Update a Set Max_Ind = ''Y'' From ' + @.x + ' b, ' + @.t + ' a Where b.k1 = ' + @.k1 +' And b.k2 = ' + @.k2 + ' And b.r1 = ' + @.r1 + ' And b.r2 = ' + @.r2
Print @.sql
Exec(@.SQL)
Exec('Drop Table ' + @.x)
End
Return @.@.Error
End
|||
You will need to prefix your If ... from Information_Schema.Columns with the name of the database passed from your @.t variable...
Something like this
declare @.DBName varchar(50)
declare @.TblName varhcar(50)
Set @.DBName = parsename(@.t, 3)
Set @.TblName = parsename(@.t, 1)
-- 1=object name, 2=schema name, 3=database name, 4= server name
Then you'll need to change your If count(*)... from Information_Schema.Columns statement into dynamic SQL (so that the database name gets interpretted)...
I would use sp_executesql to return your count (from the If statement against information_schema). See BOL on how to return a value using sp_executesql.
Note, just a partial example...
SET @.SQLString = N'Select @.retCountOut = Count(*) From ' + @.dbname + '.Information_Schema.Columns Where Table_Name = ''' + @.tblname + ''' Column_Name = 'Min_Ind''
SET @.ParamDef = N'@.retCountOut int Output, @.dbname varchar(50), @.tblname varchar(50)';
EXECUTE sp_executesql @.SQLString, @.ParamDef, @.dbname = @.dbname, @.tblname = @.tblname, @.retCountout=@.retCount OUTPUT;
If @.retCount<> 0
Begin.....
Friday, March 9, 2012
Issue with Multiple Fact Tables
Hi,
I am having an issue with Multiple Fact Tables..
I have 2 fact tables with some common dimensions and some independent to each fact table.
When I execute a mdx query which fetches data only from the 1st fact table, for some reason it look’s to the second fact table and display the results accordingly.
For Example
Record-Id Fact table 1 Fact table 2
Measure -[Cut In] Measure -[Zro Cut In]
1 1 -
2 12 10
3 10 5
4 2 -
5 20 -
6 0 -
7 Null -
8 0 -
If I execute Query 1; I get record-Id 2 & 3 only ( I use Nonempty function), but when I execute query 2 (Without Nonempty) , I get all the 8 record-Id’s. Any idea what it could be and why it’s dependent on measure from another fact table which is not being used in the query ?
Query 1
SELECT
{
[Measures].[Cut In]
} ON COLUMNS,
{
ORDER(
nonempty([Dim Product].[Principal Id].children)
,[Measures].[Cut In],
desc
)
} ON ROWS
FROM [SIFDW]
WHERE (
{ [Dim Period].[Period].&[200601] },
{ [Dim Period].[Period Type].&[M] },
{ [Dim Store].[organization name].&[CORERP]},
{ [Dim Product].[Team ID].&[1]}
)
Query 2
SELECT
{
[Measures].[Cut In]
} ON COLUMNS,
{
ORDER (
[Dim Product].[Principal Id].children
,[Measures].[Cut In],
desc
)
} ON ROWS
FROM [SIFDW]
WHERE (
{ [Dim Period].[Period].&[200601] },
{ [Dim Period].[Period Type].&[M] },
{ [Dim Store].[organization name].&[CORERP]},
{ [Dim Product].[Team ID].&[1]}
)
Thank you,
Manish
ManishNShah wrote:
If I execute Query 1; I get record-Id 2 & 3 only ( I use Nonempty function), but when I execute query 2 (Without Nonempty) , I get all the 8 record-Id’s. Any idea what it could be and why it’s dependent on measure from another fact table which is not being used in the query ?
Since you didn't explictly specify a measure for the NonEmpty(), it may have defaulted to a measure in the other measure, group, so you could try adding the measure in Query 1:
Code Snippet
SELECT
{
[Measures].[Cut In]
} ON COLUMNS,
{
ORDER(
nonempty([Dim Product].[Principal Id].children,
{[Measures].[Cut In]})
,[Measures].[Cut In],
desc
)
} ON ROWS
FROM [SIFDW]
WHERE (
{ [Dim Period].[Period].&[200601] },
{ [Dim Period].[Period Type].&[M] },
{ [Dim Store].[organization name].&[CORERP]},
{ [Dim Product].[Team ID].&[1]}
|||Hi Deepak,
Thank you for your quick response, but still have couple of issues with it.
If a cube has multiple measure groups, and each group has few measures; but the mdx query is just using measures from one measure group, they why it's looking for measures in other measure group which is not mentioned in the MDX Query?
Also, let's say we have 2 measure group (Each measure group has 1 measure in it), sometime it's empty in first and at time it's empty in 2nd, how can we mimic Left outer join, right outer join and Inner join considering 2 measure groups as 2 tables?
Thank you,
Manish
|||
but still have couple of issues with it.
But did it work - that would be useful to know, as well?
but the mdx query is just using measures from one measure group, they why it's looking for measures in other measure group
The query applies to the cube, not just to a specific measure group. So if a measure is not specified, explicitly or by context, the default cube measure would be used, which could be from another measure group:
SQL Server 2005 Books Online
Key Concepts in MDX (MDX)
...
The default measure is the first measure specified in the cube, unless a default measure is explicitly defined. For more information, see Defining a Default Member and DefaultMember (MDX).
...
Also, let's say we have 2 measure group (Each measure group has 1 measure in it), sometime it's empty in first and at time it's empty in 2nd, how can we mimic Left outer join, right outer join and Inner join considering 2 measure groups as 2 tables?
You might want to start a separate thread for this - but keep in mind that extrapolating from SQL may not always be the best approach in MDX.
Subscribe to:
Posts (Atom)