I am having an issue when converting some of my reports to SQL reporting
services 2005. All of my reports use an OLEDB connection that point to a UDL
file so that we can easily change connection strings as needed without having
to touch the report server. They all use stored procedures to obtain the
data. The issues I am running into is with stored procedures that use temp
tables. Anything that is done in a stored procedure after the temp table is
created is not shown in the report designer on the data tab within visual
studio 2005. Iâ've read a few posts about this problem but it seems like no
one at Microsoft has been able to reproduce it. I have been able to
reproduce it with a simple example using the NorthWind database. I will
include the stored procedure and the rdl file below. I set the report to
prompt for credentials instead of using the UDL file but the same problem
occurs. It will occur with Windows authentication as well. If I change the
connection type to â'Microsoft SQL Serverâ' then it will work correctly the
problem is that I will not be able to use UDL or DSN files. My method worked
just fine with RS 2000. What has changed with RS 2005 to cause this issue?
Any ideas on how I might get this to work?
Thanks,
-Nathan
SP Script-
use northwind
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
AS
Select FirstName
into #EmployeeTemp
FROM Employees
--*NOTE It doesnâ't matter what you do here it will not be returned in the
Report Designer Data tab.
SELECT FirstName
FROM #EmployeeTemp
--Select 'Test'
RDL File-
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<Prompt>Specify a user name and password for data source
DataSource1</Prompt>
<ConnectString>Provider=SQLOLEDB.1;Data Source=saintdevsql1;Initial
Catalog=Northwind</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>0.75in</Height>
</Body>
<rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Northwind">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>Pr_TestingRSProblem</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>I meant to mention that I tried the sp with and without explicitly dropping
the table at the end. Neither way worked.
"Nathan" wrote:
> I am having an issue when converting some of my reports to SQL reporting
> services 2005. All of my reports use an OLEDB connection that point to a UDL
> file so that we can easily change connection strings as needed without having
> to touch the report server. They all use stored procedures to obtain the
> data. The issues I am running into is with stored procedures that use temp
> tables. Anything that is done in a stored procedure after the temp table is
> created is not shown in the report designer on the data tab within visual
> studio 2005. Iâ've read a few posts about this problem but it seems like no
> one at Microsoft has been able to reproduce it. I have been able to
> reproduce it with a simple example using the NorthWind database. I will
> include the stored procedure and the rdl file below. I set the report to
> prompt for credentials instead of using the UDL file but the same problem
> occurs. It will occur with Windows authentication as well. If I change the
> connection type to â'Microsoft SQL Serverâ' then it will work correctly the
> problem is that I will not be able to use UDL or DSN files. My method worked
> just fine with RS 2000. What has changed with RS 2005 to cause this issue?
> Any ideas on how I might get this to work?
> Thanks,
> -Nathan
> SP Script-
> use northwind
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> AS
> Select FirstName
> into #EmployeeTemp
> FROM Employees
> --*NOTE It doesnâ't matter what you do here it will not be returned in the
> Report Designer Data tab.
> SELECT FirstName
> FROM #EmployeeTemp
> --Select 'Test'
>
> RDL File-
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="DataSource1">
> <ConnectionProperties>
> <Prompt>Specify a user name and password for data source
> DataSource1</Prompt>
> <ConnectString>Provider=SQLOLEDB.1;Data Source=saintdevsql1;Initial
> Catalog=Northwind</ConnectString>
> <DataProvider>OLEDB</DataProvider>
> </ConnectionProperties>
> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <Height>0.75in</Height>
> </Body>
> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="Northwind">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandType>StoredProcedure</CommandType>
> <CommandText>Pr_TestingRSProblem</CommandText>
> <DataSourceName>DataSource1</DataSourceName>
> </Query>
> </DataSet>
> </DataSets>
> <Width>6.5in</Width>
> <InteractiveHeight>11in</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>1in</TopMargin>
> </Report>
>|||First, do not explicitly drop the table.
Second, exactly what is happening? No data? No field list? Any error?
I just had a case where I would get an error about a temp table (I use temp
tables all the time, why this particular SP had a problem I don't know).
What worked for me was to refresh the fields (button is to the right of the
...). I then had a field list. After that I could execute the query in the
data tab and I got data back.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> I meant to mention that I tried the sp with and without explicitly
> dropping
> the table at the end. Neither way worked.
> "Nathan" wrote:
>> I am having an issue when converting some of my reports to SQL reporting
>> services 2005. All of my reports use an OLEDB connection that point to a
>> UDL
>> file so that we can easily change connection strings as needed without
>> having
>> to touch the report server. They all use stored procedures to obtain the
>> data. The issues I am running into is with stored procedures that use
>> temp
>> tables. Anything that is done in a stored procedure after the temp table
>> is
>> created is not shown in the report designer on the data tab within visual
>> studio 2005. I've read a few posts about this problem but it seems like
>> no
>> one at Microsoft has been able to reproduce it. I have been able to
>> reproduce it with a simple example using the NorthWind database. I will
>> include the stored procedure and the rdl file below. I set the report to
>> prompt for credentials instead of using the UDL file but the same problem
>> occurs. It will occur with Windows authentication as well. If I change
>> the
>> connection type to "Microsoft SQL Server" then it will work correctly the
>> problem is that I will not be able to use UDL or DSN files. My method
>> worked
>> just fine with RS 2000. What has changed with RS 2005 to cause this
>> issue?
>> Any ideas on how I might get this to work?
>> Thanks,
>> -Nathan
>> SP Script-
>> use northwind
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER OFF
>> GO
>> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> AS
>> Select FirstName
>> into #EmployeeTemp
>> FROM Employees
>> --*NOTE It doesn't matter what you do here it will not be returned in the
>> Report Designer Data tab.
>> SELECT FirstName
>> FROM #EmployeeTemp
>> --Select 'Test'
>>
>> RDL File-
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="DataSource1">
>> <ConnectionProperties>
>> <Prompt>Specify a user name and password for data source
>> DataSource1</Prompt>
>> <ConnectString>Provider=SQLOLEDB.1;Data
>> Source=saintdevsql1;Initial
>> Catalog=Northwind</ConnectString>
>> <DataProvider>OLEDB</DataProvider>
>> </ConnectionProperties>
>> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <Height>0.75in</Height>
>> </Body>
>> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="Northwind">
>> <Query>
>> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> <CommandType>StoredProcedure</CommandType>
>> <CommandText>Pr_TestingRSProblem</CommandText>
>> <DataSourceName>DataSource1</DataSourceName>
>> </Query>
>> </DataSet>
>> </DataSets>
>> <Width>6.5in</Width>
>> <InteractiveHeight>11in</InteractiveHeight>
>> <Language>en-US</Language>
>> <TopMargin>1in</TopMargin>
>> </Report>|||Bruce,
The simple answer is there is no data, no field list, and no error. I
should have mentioned that I have tried refreshing the field list several
times and no fields are returned. I've tried this on two separate machines.
The field list is empty and when I try to run the sp within the data tab it
returns no results. Were you not able to reproduce the problem with an OLEDB
connection type? What I found strange was that anything in the sp before the
temp table declaration worked fine. It's as if the meta data describing the
table structure isn't returned. Any ideas? Let me know if I can provide you
with any more information. Thanks for the quick reply.
-Nathan
"Bruce L-C [MVP]" wrote:
> First, do not explicitly drop the table.
> Second, exactly what is happening? No data? No field list? Any error?
> I just had a case where I would get an error about a temp table (I use temp
> tables all the time, why this particular SP had a problem I don't know).
> What worked for me was to refresh the fields (button is to the right of the
> ...). I then had a field list. After that I could execute the query in the
> data tab and I got data back.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> >
> > I meant to mention that I tried the sp with and without explicitly
> > dropping
> > the table at the end. Neither way worked.
> >
> > "Nathan" wrote:
> >
> >> I am having an issue when converting some of my reports to SQL reporting
> >> services 2005. All of my reports use an OLEDB connection that point to a
> >> UDL
> >> file so that we can easily change connection strings as needed without
> >> having
> >> to touch the report server. They all use stored procedures to obtain the
> >> data. The issues I am running into is with stored procedures that use
> >> temp
> >> tables. Anything that is done in a stored procedure after the temp table
> >> is
> >> created is not shown in the report designer on the data tab within visual
> >> studio 2005. I've read a few posts about this problem but it seems like
> >> no
> >> one at Microsoft has been able to reproduce it. I have been able to
> >> reproduce it with a simple example using the NorthWind database. I will
> >> include the stored procedure and the rdl file below. I set the report to
> >> prompt for credentials instead of using the UDL file but the same problem
> >> occurs. It will occur with Windows authentication as well. If I change
> >> the
> >> connection type to "Microsoft SQL Server" then it will work correctly the
> >> problem is that I will not be able to use UDL or DSN files. My method
> >> worked
> >> just fine with RS 2000. What has changed with RS 2005 to cause this
> >> issue?
> >> Any ideas on how I might get this to work?
> >>
> >> Thanks,
> >> -Nathan
> >>
> >> SP Script-
> >>
> >> use northwind
> >>
> >> set ANSI_NULLS ON
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >>
> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> >> AS
> >>
> >> Select FirstName
> >> into #EmployeeTemp
> >> FROM Employees
> >> --*NOTE It doesn't matter what you do here it will not be returned in the
> >> Report Designer Data tab.
> >> SELECT FirstName
> >> FROM #EmployeeTemp
> >> --Select 'Test'
> >>
> >>
> >> RDL File-
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="DataSource1">
> >> <ConnectionProperties>
> >> <Prompt>Specify a user name and password for data source
> >> DataSource1</Prompt>
> >> <ConnectString>Provider=SQLOLEDB.1;Data
> >> Source=saintdevsql1;Initial
> >> Catalog=Northwind</ConnectString>
> >> <DataProvider>OLEDB</DataProvider>
> >> </ConnectionProperties>
> >>
> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <Height>0.75in</Height>
> >> </Body>
> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> >> <LeftMargin>1in</LeftMargin>
> >> <DataSets>
> >> <DataSet Name="Northwind">
> >> <Query>
> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> >> <CommandType>StoredProcedure</CommandType>
> >> <CommandText>Pr_TestingRSProblem</CommandText>
> >> <DataSourceName>DataSource1</DataSourceName>
> >> </Query>
> >> </DataSet>
> >> </DataSets>
> >> <Width>6.5in</Width>
> >> <InteractiveHeight>11in</InteractiveHeight>
> >> <Language>en-US</Language>
> >> <TopMargin>1in</TopMargin>
> >> </Report>
> >>
>
>|||I don't have northwind, I have AdventureWorks. Here is my stored procedure:
create PROCEDURE [dbo].[Pr_TestingRSProblem]
AS
Select FirstName
into #TEMP
FROM person.contact
SELECT distinct FirstName
FROM #TEMP order by firstname
return
I did not use the wizard. I added a report. I went to the data tab. Added a
new dataset. My shared data source credentials were windows authentication.
When creating the dataset I changed the data type to stored procedure and
put in the name of the stored procedure as the query string (do not put in
exec, just put in the name of the procedure). I get an error and no field
list. I click on refresh the field list I get a field list and now I can
execute the query and get data back in the data tab.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> I meant to mention that I tried the sp with and without explicitly
> dropping
> the table at the end. Neither way worked.
> "Nathan" wrote:
>> I am having an issue when converting some of my reports to SQL reporting
>> services 2005. All of my reports use an OLEDB connection that point to a
>> UDL
>> file so that we can easily change connection strings as needed without
>> having
>> to touch the report server. They all use stored procedures to obtain the
>> data. The issues I am running into is with stored procedures that use
>> temp
>> tables. Anything that is done in a stored procedure after the temp table
>> is
>> created is not shown in the report designer on the data tab within visual
>> studio 2005. I've read a few posts about this problem but it seems like
>> no
>> one at Microsoft has been able to reproduce it. I have been able to
>> reproduce it with a simple example using the NorthWind database. I will
>> include the stored procedure and the rdl file below. I set the report to
>> prompt for credentials instead of using the UDL file but the same problem
>> occurs. It will occur with Windows authentication as well. If I change
>> the
>> connection type to "Microsoft SQL Server" then it will work correctly the
>> problem is that I will not be able to use UDL or DSN files. My method
>> worked
>> just fine with RS 2000. What has changed with RS 2005 to cause this
>> issue?
>> Any ideas on how I might get this to work?
>> Thanks,
>> -Nathan
>> SP Script-
>> use northwind
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER OFF
>> GO
>> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> AS
>> Select FirstName
>> into #EmployeeTemp
>> FROM Employees
>> --*NOTE It doesn't matter what you do here it will not be returned in the
>> Report Designer Data tab.
>> SELECT FirstName
>> FROM #EmployeeTemp
>> --Select 'Test'
>>
>> RDL File-
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="DataSource1">
>> <ConnectionProperties>
>> <Prompt>Specify a user name and password for data source
>> DataSource1</Prompt>
>> <ConnectString>Provider=SQLOLEDB.1;Data
>> Source=saintdevsql1;Initial
>> Catalog=Northwind</ConnectString>
>> <DataProvider>OLEDB</DataProvider>
>> </ConnectionProperties>
>> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <Height>0.75in</Height>
>> </Body>
>> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="Northwind">
>> <Query>
>> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> <CommandType>StoredProcedure</CommandType>
>> <CommandText>Pr_TestingRSProblem</CommandText>
>> <DataSourceName>DataSource1</DataSourceName>
>> </Query>
>> </DataSet>
>> </DataSets>
>> <Width>6.5in</Width>
>> <InteractiveHeight>11in</InteractiveHeight>
>> <Language>en-US</Language>
>> <TopMargin>1in</TopMargin>
>> </Report>|||Whoops, didn't use OLEDB. Yep, I can duplicate. My only suggestion is to not
use OLEDB. I tried ODBC (which is what I use with Sybase) but also had a
proble with that. Against SQL Server I don't use OLEDB or ODBC, just ODBC
against Sybase (for which I don't have this problem). Weird. Sorry, other
than duplicating the problem there isn't much more I can do to help.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:CCF681B4-BB0F-4027-8672-7CDFFE14ECE1@.microsoft.com...
> Bruce,
> The simple answer is there is no data, no field list, and no error. I
> should have mentioned that I have tried refreshing the field list several
> times and no fields are returned. I've tried this on two separate
> machines.
> The field list is empty and when I try to run the sp within the data tab
> it
> returns no results. Were you not able to reproduce the problem with an
> OLEDB
> connection type? What I found strange was that anything in the sp before
> the
> temp table declaration worked fine. It's as if the meta data describing
> the
> table structure isn't returned. Any ideas? Let me know if I can provide
> you
> with any more information. Thanks for the quick reply.
> -Nathan
>
> "Bruce L-C [MVP]" wrote:
>> First, do not explicitly drop the table.
>> Second, exactly what is happening? No data? No field list? Any error?
>> I just had a case where I would get an error about a temp table (I use
>> temp
>> tables all the time, why this particular SP had a problem I don't know).
>> What worked for me was to refresh the fields (button is to the right of
>> the
>> ...). I then had a field list. After that I could execute the query in
>> the
>> data tab and I got data back.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
>> >
>> > I meant to mention that I tried the sp with and without explicitly
>> > dropping
>> > the table at the end. Neither way worked.
>> >
>> > "Nathan" wrote:
>> >
>> >> I am having an issue when converting some of my reports to SQL
>> >> reporting
>> >> services 2005. All of my reports use an OLEDB connection that point
>> >> to a
>> >> UDL
>> >> file so that we can easily change connection strings as needed without
>> >> having
>> >> to touch the report server. They all use stored procedures to obtain
>> >> the
>> >> data. The issues I am running into is with stored procedures that use
>> >> temp
>> >> tables. Anything that is done in a stored procedure after the temp
>> >> table
>> >> is
>> >> created is not shown in the report designer on the data tab within
>> >> visual
>> >> studio 2005. I've read a few posts about this problem but it seems
>> >> like
>> >> no
>> >> one at Microsoft has been able to reproduce it. I have been able to
>> >> reproduce it with a simple example using the NorthWind database. I
>> >> will
>> >> include the stored procedure and the rdl file below. I set the report
>> >> to
>> >> prompt for credentials instead of using the UDL file but the same
>> >> problem
>> >> occurs. It will occur with Windows authentication as well. If I
>> >> change
>> >> the
>> >> connection type to "Microsoft SQL Server" then it will work correctly
>> >> the
>> >> problem is that I will not be able to use UDL or DSN files. My method
>> >> worked
>> >> just fine with RS 2000. What has changed with RS 2005 to cause this
>> >> issue?
>> >> Any ideas on how I might get this to work?
>> >>
>> >> Thanks,
>> >> -Nathan
>> >>
>> >> SP Script-
>> >>
>> >> use northwind
>> >>
>> >> set ANSI_NULLS ON
>> >> set QUOTED_IDENTIFIER OFF
>> >> GO
>> >>
>> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> >> AS
>> >>
>> >> Select FirstName
>> >> into #EmployeeTemp
>> >> FROM Employees
>> >> --*NOTE It doesn't matter what you do here it will not be returned in
>> >> the
>> >> Report Designer Data tab.
>> >> SELECT FirstName
>> >> FROM #EmployeeTemp
>> >> --Select 'Test'
>> >>
>> >>
>> >> RDL File-
>> >>
>> >> <?xml version="1.0" encoding="utf-8"?>
>> >> <Report
>> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> >> <DataSources>
>> >> <DataSource Name="DataSource1">
>> >> <ConnectionProperties>
>> >> <Prompt>Specify a user name and password for data source
>> >> DataSource1</Prompt>
>> >> <ConnectString>Provider=SQLOLEDB.1;Data
>> >> Source=saintdevsql1;Initial
>> >> Catalog=Northwind</ConnectString>
>> >> <DataProvider>OLEDB</DataProvider>
>> >> </ConnectionProperties>
>> >>
>> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> >> </DataSource>
>> >> </DataSources>
>> >> <BottomMargin>1in</BottomMargin>
>> >> <RightMargin>1in</RightMargin>
>> >> <rd:DrawGrid>true</rd:DrawGrid>
>> >> <InteractiveWidth>8.5in</InteractiveWidth>
>> >> <rd:SnapToGrid>true</rd:SnapToGrid>
>> >> <Body>
>> >> <Height>0.75in</Height>
>> >> </Body>
>> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> >> <LeftMargin>1in</LeftMargin>
>> >> <DataSets>
>> >> <DataSet Name="Northwind">
>> >> <Query>
>> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> >> <CommandType>StoredProcedure</CommandType>
>> >> <CommandText>Pr_TestingRSProblem</CommandText>
>> >> <DataSourceName>DataSource1</DataSourceName>
>> >> </Query>
>> >> </DataSet>
>> >> </DataSets>
>> >> <Width>6.5in</Width>
>> >> <InteractiveHeight>11in</InteractiveHeight>
>> >> <Language>en-US</Language>
>> >> <TopMargin>1in</TopMargin>
>> >> </Report>
>> >>
>>|||I tried the example you have given with the adventure works database. I am
able to reproduce what you have with the connection type of â'Microsoft SQL
Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
having. I need to use this because of my requirements of using a dsn or udl
file.
Thanks again,
-Nathan
"Bruce L-C [MVP]" wrote:
> I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> create PROCEDURE [dbo].[Pr_TestingRSProblem]
> AS
> Select FirstName
> into #TEMP
> FROM person.contact
> SELECT distinct FirstName
> FROM #TEMP order by firstname
> return
> I did not use the wizard. I added a report. I went to the data tab. Added a
> new dataset. My shared data source credentials were windows authentication.
> When creating the dataset I changed the data type to stored procedure and
> put in the name of the stored procedure as the query string (do not put in
> exec, just put in the name of the procedure). I get an error and no field
> list. I click on refresh the field list I get a field list and now I can
> execute the query and get data back in the data tab.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> >
> > I meant to mention that I tried the sp with and without explicitly
> > dropping
> > the table at the end. Neither way worked.
> >
> > "Nathan" wrote:
> >
> >> I am having an issue when converting some of my reports to SQL reporting
> >> services 2005. All of my reports use an OLEDB connection that point to a
> >> UDL
> >> file so that we can easily change connection strings as needed without
> >> having
> >> to touch the report server. They all use stored procedures to obtain the
> >> data. The issues I am running into is with stored procedures that use
> >> temp
> >> tables. Anything that is done in a stored procedure after the temp table
> >> is
> >> created is not shown in the report designer on the data tab within visual
> >> studio 2005. I've read a few posts about this problem but it seems like
> >> no
> >> one at Microsoft has been able to reproduce it. I have been able to
> >> reproduce it with a simple example using the NorthWind database. I will
> >> include the stored procedure and the rdl file below. I set the report to
> >> prompt for credentials instead of using the UDL file but the same problem
> >> occurs. It will occur with Windows authentication as well. If I change
> >> the
> >> connection type to "Microsoft SQL Server" then it will work correctly the
> >> problem is that I will not be able to use UDL or DSN files. My method
> >> worked
> >> just fine with RS 2000. What has changed with RS 2005 to cause this
> >> issue?
> >> Any ideas on how I might get this to work?
> >>
> >> Thanks,
> >> -Nathan
> >>
> >> SP Script-
> >>
> >> use northwind
> >>
> >> set ANSI_NULLS ON
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >>
> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> >> AS
> >>
> >> Select FirstName
> >> into #EmployeeTemp
> >> FROM Employees
> >> --*NOTE It doesn't matter what you do here it will not be returned in the
> >> Report Designer Data tab.
> >> SELECT FirstName
> >> FROM #EmployeeTemp
> >> --Select 'Test'
> >>
> >>
> >> RDL File-
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="DataSource1">
> >> <ConnectionProperties>
> >> <Prompt>Specify a user name and password for data source
> >> DataSource1</Prompt>
> >> <ConnectString>Provider=SQLOLEDB.1;Data
> >> Source=saintdevsql1;Initial
> >> Catalog=Northwind</ConnectString>
> >> <DataProvider>OLEDB</DataProvider>
> >> </ConnectionProperties>
> >>
> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <Height>0.75in</Height>
> >> </Body>
> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> >> <LeftMargin>1in</LeftMargin>
> >> <DataSets>
> >> <DataSet Name="Northwind">
> >> <Query>
> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> >> <CommandType>StoredProcedure</CommandType>
> >> <CommandText>Pr_TestingRSProblem</CommandText>
> >> <DataSourceName>DataSource1</DataSourceName>
> >> </Query>
> >> </DataSet>
> >> </DataSets>
> >> <Width>6.5in</Width>
> >> <InteractiveHeight>11in</InteractiveHeight>
> >> <Language>en-US</Language>
> >> <TopMargin>1in</TopMargin>
> >> </Report>
> >>
>
>|||I must not have refreshed before sending my last reply. Looks like you did
try the OLEDB. Well, thanks for trying. At least I know I'm not going crazy
and it is reproducible and may actually be an issue with reporting services.
The funny thing is that if you try making the same report in the previous
version of RS within VS2003 it works fine. That is with it connecting to the
same DB server (SQL 2005). This seems like an unintended â'featureâ' to meâ?¦
Anyone else out there have any ideas?
Thanks,
-Nathan
"Nathan" wrote:
> I tried the example you have given with the adventure works database. I am
> able to reproduce what you have with the connection type of â'Microsoft SQL
> Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
> having. I need to use this because of my requirements of using a dsn or udl
> file.
> Thanks again,
> -Nathan
>
> "Bruce L-C [MVP]" wrote:
> > I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> > create PROCEDURE [dbo].[Pr_TestingRSProblem]
> >
> > AS
> >
> > Select FirstName
> >
> > into #TEMP
> >
> > FROM person.contact
> >
> > SELECT distinct FirstName
> >
> > FROM #TEMP order by firstname
> >
> > return
> >
> > I did not use the wizard. I added a report. I went to the data tab. Added a
> > new dataset. My shared data source credentials were windows authentication.
> > When creating the dataset I changed the data type to stored procedure and
> > put in the name of the stored procedure as the query string (do not put in
> > exec, just put in the name of the procedure). I get an error and no field
> > list. I click on refresh the field list I get a field list and now I can
> > execute the query and get data back in the data tab.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> > >
> > > I meant to mention that I tried the sp with and without explicitly
> > > dropping
> > > the table at the end. Neither way worked.
> > >
> > > "Nathan" wrote:
> > >
> > >> I am having an issue when converting some of my reports to SQL reporting
> > >> services 2005. All of my reports use an OLEDB connection that point to a
> > >> UDL
> > >> file so that we can easily change connection strings as needed without
> > >> having
> > >> to touch the report server. They all use stored procedures to obtain the
> > >> data. The issues I am running into is with stored procedures that use
> > >> temp
> > >> tables. Anything that is done in a stored procedure after the temp table
> > >> is
> > >> created is not shown in the report designer on the data tab within visual
> > >> studio 2005. I've read a few posts about this problem but it seems like
> > >> no
> > >> one at Microsoft has been able to reproduce it. I have been able to
> > >> reproduce it with a simple example using the NorthWind database. I will
> > >> include the stored procedure and the rdl file below. I set the report to
> > >> prompt for credentials instead of using the UDL file but the same problem
> > >> occurs. It will occur with Windows authentication as well. If I change
> > >> the
> > >> connection type to "Microsoft SQL Server" then it will work correctly the
> > >> problem is that I will not be able to use UDL or DSN files. My method
> > >> worked
> > >> just fine with RS 2000. What has changed with RS 2005 to cause this
> > >> issue?
> > >> Any ideas on how I might get this to work?
> > >>
> > >> Thanks,
> > >> -Nathan
> > >>
> > >> SP Script-
> > >>
> > >> use northwind
> > >>
> > >> set ANSI_NULLS ON
> > >> set QUOTED_IDENTIFIER OFF
> > >> GO
> > >>
> > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> > >> AS
> > >>
> > >> Select FirstName
> > >> into #EmployeeTemp
> > >> FROM Employees
> > >> --*NOTE It doesn't matter what you do here it will not be returned in the
> > >> Report Designer Data tab.
> > >> SELECT FirstName
> > >> FROM #EmployeeTemp
> > >> --Select 'Test'
> > >>
> > >>
> > >> RDL File-
> > >>
> > >> <?xml version="1.0" encoding="utf-8"?>
> > >> <Report
> > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > >> <DataSources>
> > >> <DataSource Name="DataSource1">
> > >> <ConnectionProperties>
> > >> <Prompt>Specify a user name and password for data source
> > >> DataSource1</Prompt>
> > >> <ConnectString>Provider=SQLOLEDB.1;Data
> > >> Source=saintdevsql1;Initial
> > >> Catalog=Northwind</ConnectString>
> > >> <DataProvider>OLEDB</DataProvider>
> > >> </ConnectionProperties>
> > >>
> > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> > >> </DataSource>
> > >> </DataSources>
> > >> <BottomMargin>1in</BottomMargin>
> > >> <RightMargin>1in</RightMargin>
> > >> <rd:DrawGrid>true</rd:DrawGrid>
> > >> <InteractiveWidth>8.5in</InteractiveWidth>
> > >> <rd:SnapToGrid>true</rd:SnapToGrid>
> > >> <Body>
> > >> <Height>0.75in</Height>
> > >> </Body>
> > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> > >> <LeftMargin>1in</LeftMargin>
> > >> <DataSets>
> > >> <DataSet Name="Northwind">
> > >> <Query>
> > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > >> <CommandType>StoredProcedure</CommandType>
> > >> <CommandText>Pr_TestingRSProblem</CommandText>
> > >> <DataSourceName>DataSource1</DataSourceName>
> > >> </Query>
> > >> </DataSet>
> > >> </DataSets>
> > >> <Width>6.5in</Width>
> > >> <InteractiveHeight>11in</InteractiveHeight>
> > >> <Language>en-US</Language>
> > >> <TopMargin>1in</TopMargin>
> > >> </Report>
> > >>
> >
> >
> >|||Iâ'm thinking of starting a support ticket with Microsoft about this issue.
Before I do does anyone know of a reason why the behavior of the OLEDB
connection would change between rs2000 and rs2005? Does anyone know of any
hot fixes or SPs that might already fix this issue? Iâ'm at a loss at what to
do at this point. This is kind of a show stopper for us with moving to
rs2005.
Thanks,
-Nathan
"Nathan" wrote:
> I must not have refreshed before sending my last reply. Looks like you did
> try the OLEDB. Well, thanks for trying. At least I know I'm not going crazy
> and it is reproducible and may actually be an issue with reporting services.
> The funny thing is that if you try making the same report in the previous
> version of RS within VS2003 it works fine. That is with it connecting to the
> same DB server (SQL 2005). This seems like an unintended â'featureâ' to meâ?¦
> Anyone else out there have any ideas?
> Thanks,
> -Nathan
>
> "Nathan" wrote:
> > I tried the example you have given with the adventure works database. I am
> > able to reproduce what you have with the connection type of â'Microsoft SQL
> > Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
> > having. I need to use this because of my requirements of using a dsn or udl
> > file.
> >
> > Thanks again,
> > -Nathan
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> > > create PROCEDURE [dbo].[Pr_TestingRSProblem]
> > >
> > > AS
> > >
> > > Select FirstName
> > >
> > > into #TEMP
> > >
> > > FROM person.contact
> > >
> > > SELECT distinct FirstName
> > >
> > > FROM #TEMP order by firstname
> > >
> > > return
> > >
> > > I did not use the wizard. I added a report. I went to the data tab. Added a
> > > new dataset. My shared data source credentials were windows authentication.
> > > When creating the dataset I changed the data type to stored procedure and
> > > put in the name of the stored procedure as the query string (do not put in
> > > exec, just put in the name of the procedure). I get an error and no field
> > > list. I click on refresh the field list I get a field list and now I can
> > > execute the query and get data back in the data tab.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> > > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> > > >
> > > > I meant to mention that I tried the sp with and without explicitly
> > > > dropping
> > > > the table at the end. Neither way worked.
> > > >
> > > > "Nathan" wrote:
> > > >
> > > >> I am having an issue when converting some of my reports to SQL reporting
> > > >> services 2005. All of my reports use an OLEDB connection that point to a
> > > >> UDL
> > > >> file so that we can easily change connection strings as needed without
> > > >> having
> > > >> to touch the report server. They all use stored procedures to obtain the
> > > >> data. The issues I am running into is with stored procedures that use
> > > >> temp
> > > >> tables. Anything that is done in a stored procedure after the temp table
> > > >> is
> > > >> created is not shown in the report designer on the data tab within visual
> > > >> studio 2005. I've read a few posts about this problem but it seems like
> > > >> no
> > > >> one at Microsoft has been able to reproduce it. I have been able to
> > > >> reproduce it with a simple example using the NorthWind database. I will
> > > >> include the stored procedure and the rdl file below. I set the report to
> > > >> prompt for credentials instead of using the UDL file but the same problem
> > > >> occurs. It will occur with Windows authentication as well. If I change
> > > >> the
> > > >> connection type to "Microsoft SQL Server" then it will work correctly the
> > > >> problem is that I will not be able to use UDL or DSN files. My method
> > > >> worked
> > > >> just fine with RS 2000. What has changed with RS 2005 to cause this
> > > >> issue?
> > > >> Any ideas on how I might get this to work?
> > > >>
> > > >> Thanks,
> > > >> -Nathan
> > > >>
> > > >> SP Script-
> > > >>
> > > >> use northwind
> > > >>
> > > >> set ANSI_NULLS ON
> > > >> set QUOTED_IDENTIFIER OFF
> > > >> GO
> > > >>
> > > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> > > >> AS
> > > >>
> > > >> Select FirstName
> > > >> into #EmployeeTemp
> > > >> FROM Employees
> > > >> --*NOTE It doesn't matter what you do here it will not be returned in the
> > > >> Report Designer Data tab.
> > > >> SELECT FirstName
> > > >> FROM #EmployeeTemp
> > > >> --Select 'Test'
> > > >>
> > > >>
> > > >> RDL File-
> > > >>
> > > >> <?xml version="1.0" encoding="utf-8"?>
> > > >> <Report
> > > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> > > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > > >> <DataSources>
> > > >> <DataSource Name="DataSource1">
> > > >> <ConnectionProperties>
> > > >> <Prompt>Specify a user name and password for data source
> > > >> DataSource1</Prompt>
> > > >> <ConnectString>Provider=SQLOLEDB.1;Data
> > > >> Source=saintdevsql1;Initial
> > > >> Catalog=Northwind</ConnectString>
> > > >> <DataProvider>OLEDB</DataProvider>
> > > >> </ConnectionProperties>
> > > >>
> > > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> > > >> </DataSource>
> > > >> </DataSources>
> > > >> <BottomMargin>1in</BottomMargin>
> > > >> <RightMargin>1in</RightMargin>
> > > >> <rd:DrawGrid>true</rd:DrawGrid>
> > > >> <InteractiveWidth>8.5in</InteractiveWidth>
> > > >> <rd:SnapToGrid>true</rd:SnapToGrid>
> > > >> <Body>
> > > >> <Height>0.75in</Height>
> > > >> </Body>
> > > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> > > >> <LeftMargin>1in</LeftMargin>
> > > >> <DataSets>
> > > >> <DataSet Name="Northwind">
> > > >> <Query>
> > > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > >> <CommandType>StoredProcedure</CommandType>
> > > >> <CommandText>Pr_TestingRSProblem</CommandText>
> > > >> <DataSourceName>DataSource1</DataSourceName>
> > > >> </Query>
> > > >> </DataSet>
> > > >> </DataSets>
> > > >> <Width>6.5in</Width>
> > > >> <InteractiveHeight>11in</InteractiveHeight>
> > > >> <Language>en-US</Language>
> > > >> <TopMargin>1in</TopMargin>
> > > >> </Report>
> > > >>
> > >
> > >
> > >|||This is the first I have heard of it on the newsgroups. I am not aware of
any hot fixes (SP1 makes no difference).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:18D44662-1B40-41D9-B4A0-049F8A893B76@.microsoft.com...
> I'm thinking of starting a support ticket with Microsoft about this issue.
> Before I do does anyone know of a reason why the behavior of the OLEDB
> connection would change between rs2000 and rs2005? Does anyone know of
> any
> hot fixes or SPs that might already fix this issue? I'm at a loss at what
> to
> do at this point. This is kind of a show stopper for us with moving to
> rs2005.
> Thanks,
> -Nathan
>
> "Nathan" wrote:
>> I must not have refreshed before sending my last reply. Looks like you
>> did
>> try the OLEDB. Well, thanks for trying. At least I know I'm not going
>> crazy
>> and it is reproducible and may actually be an issue with reporting
>> services.
>> The funny thing is that if you try making the same report in the previous
>> version of RS within VS2003 it works fine. That is with it connecting to
>> the
>> same DB server (SQL 2005). This seems like an unintended "feature" to
>> me.
>> Anyone else out there have any ideas?
>> Thanks,
>> -Nathan
>>
>> "Nathan" wrote:
>> > I tried the example you have given with the adventure works database.
>> > I am
>> > able to reproduce what you have with the connection type of "Microsoft
>> > SQL
>> > Server". However, if you select "OLEDB" you will encounter the problem
>> > I am
>> > having. I need to use this because of my requirements of using a dsn
>> > or udl
>> > file.
>> >
>> > Thanks again,
>> > -Nathan
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > I don't have northwind, I have AdventureWorks. Here is my stored
>> > > procedure:
>> > > create PROCEDURE [dbo].[Pr_TestingRSProblem]
>> > >
>> > > AS
>> > >
>> > > Select FirstName
>> > >
>> > > into #TEMP
>> > >
>> > > FROM person.contact
>> > >
>> > > SELECT distinct FirstName
>> > >
>> > > FROM #TEMP order by firstname
>> > >
>> > > return
>> > >
>> > > I did not use the wizard. I added a report. I went to the data tab.
>> > > Added a
>> > > new dataset. My shared data source credentials were windows
>> > > authentication.
>> > > When creating the dataset I changed the data type to stored procedure
>> > > and
>> > > put in the name of the stored procedure as the query string (do not
>> > > put in
>> > > exec, just put in the name of the procedure). I get an error and no
>> > > field
>> > > list. I click on refresh the field list I get a field list and now I
>> > > can
>> > > execute the query and get data back in the data tab.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > >
>> > > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> > > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
>> > > >
>> > > > I meant to mention that I tried the sp with and without explicitly
>> > > > dropping
>> > > > the table at the end. Neither way worked.
>> > > >
>> > > > "Nathan" wrote:
>> > > >
>> > > >> I am having an issue when converting some of my reports to SQL
>> > > >> reporting
>> > > >> services 2005. All of my reports use an OLEDB connection that
>> > > >> point to a
>> > > >> UDL
>> > > >> file so that we can easily change connection strings as needed
>> > > >> without
>> > > >> having
>> > > >> to touch the report server. They all use stored procedures to
>> > > >> obtain the
>> > > >> data. The issues I am running into is with stored procedures that
>> > > >> use
>> > > >> temp
>> > > >> tables. Anything that is done in a stored procedure after the
>> > > >> temp table
>> > > >> is
>> > > >> created is not shown in the report designer on the data tab within
>> > > >> visual
>> > > >> studio 2005. I've read a few posts about this problem but it
>> > > >> seems like
>> > > >> no
>> > > >> one at Microsoft has been able to reproduce it. I have been able
>> > > >> to
>> > > >> reproduce it with a simple example using the NorthWind database.
>> > > >> I will
>> > > >> include the stored procedure and the rdl file below. I set the
>> > > >> report to
>> > > >> prompt for credentials instead of using the UDL file but the same
>> > > >> problem
>> > > >> occurs. It will occur with Windows authentication as well. If I
>> > > >> change
>> > > >> the
>> > > >> connection type to "Microsoft SQL Server" then it will work
>> > > >> correctly the
>> > > >> problem is that I will not be able to use UDL or DSN files. My
>> > > >> method
>> > > >> worked
>> > > >> just fine with RS 2000. What has changed with RS 2005 to cause
>> > > >> this
>> > > >> issue?
>> > > >> Any ideas on how I might get this to work?
>> > > >>
>> > > >> Thanks,
>> > > >> -Nathan
>> > > >>
>> > > >> SP Script-
>> > > >>
>> > > >> use northwind
>> > > >>
>> > > >> set ANSI_NULLS ON
>> > > >> set QUOTED_IDENTIFIER OFF
>> > > >> GO
>> > > >>
>> > > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> > > >> AS
>> > > >>
>> > > >> Select FirstName
>> > > >> into #EmployeeTemp
>> > > >> FROM Employees
>> > > >> --*NOTE It doesn't matter what you do here it will not be returned
>> > > >> in the
>> > > >> Report Designer Data tab.
>> > > >> SELECT FirstName
>> > > >> FROM #EmployeeTemp
>> > > >> --Select 'Test'
>> > > >>
>> > > >>
>> > > >> RDL File-
>> > > >>
>> > > >> <?xml version="1.0" encoding="utf-8"?>
>> > > >> <Report
>> > > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> > > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> > > >> <DataSources>
>> > > >> <DataSource Name="DataSource1">
>> > > >> <ConnectionProperties>
>> > > >> <Prompt>Specify a user name and password for data source
>> > > >> DataSource1</Prompt>
>> > > >> <ConnectString>Provider=SQLOLEDB.1;Data
>> > > >> Source=saintdevsql1;Initial
>> > > >> Catalog=Northwind</ConnectString>
>> > > >> <DataProvider>OLEDB</DataProvider>
>> > > >> </ConnectionProperties>
>> > > >>
>> > > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> > > >> </DataSource>
>> > > >> </DataSources>
>> > > >> <BottomMargin>1in</BottomMargin>
>> > > >> <RightMargin>1in</RightMargin>
>> > > >> <rd:DrawGrid>true</rd:DrawGrid>
>> > > >> <InteractiveWidth>8.5in</InteractiveWidth>
>> > > >> <rd:SnapToGrid>true</rd:SnapToGrid>
>> > > >> <Body>
>> > > >> <Height>0.75in</Height>
>> > > >> </Body>
>> > > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> > > >> <LeftMargin>1in</LeftMargin>
>> > > >> <DataSets>
>> > > >> <DataSet Name="Northwind">
>> > > >> <Query>
>> > > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> > > >> <CommandType>StoredProcedure</CommandType>
>> > > >> <CommandText>Pr_TestingRSProblem</CommandText>
>> > > >> <DataSourceName>DataSource1</DataSourceName>
>> > > >> </Query>
>> > > >> </DataSet>
>> > > >> </DataSets>
>> > > >> <Width>6.5in</Width>
>> > > >> <InteractiveHeight>11in</InteractiveHeight>
>> > > >> <Language>en-US</Language>
>> > > >> <TopMargin>1in</TopMargin>
>> > > >> </Report>
>> > > >>
>> > >
>> > >
>> > >
Showing posts with label converting. Show all posts
Showing posts with label converting. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Issue with T-SQL and SQL 7 to SQL2000 conversion
I'm in the process of converting a database from SQL 7 to SQL 2000 and
have come up against a problem. The following code executes correctly in
the existing SQL7 database and runs to completion in about 1 minute.
In the 2000 database, it runs until I cancel execution -- I've let it
run up to 30 minutes without showing any signs of finishing.
I am using the identical code to populate the tables in both databases
-- the data actually gets fed in from text files via a BULK INSERT
command, and that runs correctly in the both databases. The indexes and
primary keys are created via T-SQL code, and that runs correctly in both
databases.
I have deleted and re-created the stored procedure containing this code
to no avail. It will still run until I cancel execution.
Did something change between SQL7 and 2000 with the UPDATE command, or
am I missing something else?
Any help will be appreciated --
Carl
UPDATE tblProcedureHistory
SET tblProcedureHistory.Status_A = [derived].MaxPostingDate
FROM
(
SELECT T1.OFFICE_NUM,
T1.PatientID,
T1.PatientType,
T1.StudentID,
T1.ProcedureID,
T1.ProcedureSuffix,
T1.Tooth,
T1.Surface,
MAX(T1.PostingDate) AS MaxPostingDate
FROM tbl_AHSTDN AS T1
INNER JOIN tblProcedureHistory AS T2
ON T1.OFFICE_NUM = T2.OFFICE_NUM
AND T1.PatientID = T2.PatientID
AND T1.PatientType = T2.PatientType
AND T1.StudentID = T2.StudentID
AND T1.ProcedureID = T2.ProcedureID
AND T1.ProcedureSuffix = T2.ProcedureSuffix
AND T1.Tooth = T2.Tooth
AND T1.Surface = T2.Surface
AND T1.Status = 'A'
GROUP BY
T1.OFFICE_NUM,
T1.PatientID,
T1.PatientType,
T1.StudentID,
T1.ProcedureID,
T1.ProcedureSuffix,
T1.Tooth,
T1.Surface
)
AS [derived]
WHERE
tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM
AND tblProcedureHistory.PatientID = [derived].PatientID
AND tblProcedureHistory.PatientType = [derived].PatientType
AND tblProcedureHistory.StudentID = [derived].StudentID
AND tblProcedureHistory.ProcedureID = [derived].ProcedureID
AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix
AND tblProcedureHistory.Tooth = [derived].Tooth
AND tblProcedureHistory.Surface = [derived].SurfaceLet's see your DDL including Primary Keys and Indexes.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23di7RAWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> I'm in the process of converting a database from SQL 7 to SQL 2000 and
> have come up against a problem. The following code executes correctly in
> the existing SQL7 database and runs to completion in about 1 minute.
> In the 2000 database, it runs until I cancel execution -- I've let it run
> up to 30 minutes without showing any signs of finishing.
> I am using the identical code to populate the tables in both databases --
> the data actually gets fed in from text files via a BULK INSERT command,
> and that runs correctly in the both databases. The indexes and primary
> keys are created via T-SQL code, and that runs correctly in both
> databases.
> I have deleted and re-created the stored procedure containing this code to
> no avail. It will still run until I cancel execution.
> Did something change between SQL7 and 2000 with the UPDATE command, or am
> I missing something else?
> Any help will be appreciated --
> Carl
> UPDATE tblProcedureHistory
> SET tblProcedureHistory.Status_A = [derived].MaxPostingDate
> FROM
> (
> SELECT T1.OFFICE_NUM,
> T1.PatientID,
> T1.PatientType,
> T1.StudentID,
> T1.ProcedureID,
> T1.ProcedureSuffix,
> T1.Tooth,
> T1.Surface,
> MAX(T1.PostingDate) AS MaxPostingDate
> FROM tbl_AHSTDN AS T1
> INNER JOIN tblProcedureHistory AS T2
> ON T1.OFFICE_NUM = T2.OFFICE_NUM
> AND T1.PatientID = T2.PatientID
> AND T1.PatientType = T2.PatientType
> AND T1.StudentID = T2.StudentID
> AND T1.ProcedureID = T2.ProcedureID
> AND T1.ProcedureSuffix = T2.ProcedureSuffix
> AND T1.Tooth = T2.Tooth
> AND T1.Surface = T2.Surface
> AND T1.Status = 'A'
> GROUP BY
> T1.OFFICE_NUM,
> T1.PatientID,
> T1.PatientType,
> T1.StudentID,
> T1.ProcedureID,
> T1.ProcedureSuffix,
> T1.Tooth,
> T1.Surface
> )
> AS [derived]
> WHERE
> tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM
> AND tblProcedureHistory.PatientID = [derived].PatientID
> AND tblProcedureHistory.PatientType = [derived].PatientType
> AND tblProcedureHistory.StudentID = [derived].StudentID
> AND tblProcedureHistory.ProcedureID = [derived].ProcedureID
> AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix
> AND tblProcedureHistory.Tooth = [derived].Tooth
> AND tblProcedureHistory.Surface = [derived].Surface|||Here's the DDL for table/PK/index creation:
The table data gets sucked out of an AS/400 every morning into text
files and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is
not subject to updates; ie, it's a static table.
Thanks in advance --
Carl
CREATE TABLE [dbo].[tbl_AHSTDN] (
[DNHSTD] [char] (1) NULL ,
[PatientID] [int] NULL ,
[PatientType] [int] NULL ,
[DNTYY] [int] NULL ,
[DNTMM] [int] NULL ,
[DNTDD] [int] NULL ,
[DNSEQ] [int] NULL ,
[DNID] [int] NULL ,
[DNIDTY] [char] (1) NULL ,
[TicketID] [int] NULL ,
[ProcedureID] [int] NULL ,
[ProcedureSuffix] [int] NULL ,
[DNTICX] [int] NULL ,
[Discipline] [varchar] (100) NULL ,
[SessionID] [int] NULL ,
[Grade] [int] NULL ,
[DNMTHS] [int] NULL ,
[StudentID] [char] (3) NULL ,
[DNCGCD] [char] (1) NULL ,
[DOCMASID] [int] NULL ,
[DollarAmount] [money] NULL ,
[DNIN01] [int] NULL ,
[DNDAT1] [int] NULL ,
[DNIN02] [int] NULL ,
[DNDAT2] [int] NULL ,
[DNCLM_NUM] [char] (5) NULL ,
[Status] [char] (1) NULL ,
[DNFILE] [char] (1) NULL ,
[DNSEQN] [int] NULL ,
[DNBK06] [char] (6) NULL ,
[DNFLAG] [char] (1) NULL ,
[BatchID] [int] NULL ,
[Tooth] [char] (2) NULL ,
[Surface] [char] (5) NULL ,
[DNTTH2] [char] (2) NULL ,
[DNSUR2] [char] (5) NULL ,
[DNTTH3] [char] (2) NULL ,
[DNSUR3] [char] (5) NULL ,
[DNTTH4] [char] (2) NULL ,
[DNSUR4] [char] (5) NULL ,
[DNTTH5] [char] (2) NULL ,
[DNSUR5] [char] (5) NULL ,
[Location] [char] (4) NULL ,
[DNCDAT] [int] NULL ,
[User] [varchar] (10) NULL ,
[DNUQID] [int] NULL ,
[DNBL19] [varchar] (19) NULL ,
[DNPTS] [real] NULL ,
[DNGRP] [int] NULL ,
[DNCMDT] [int] NULL ,
[OFFICE_NUM] [int] NULL ,
[TransactionDate] [datetime] NULL ,
[CompletionDate] [datetime] NULL ,
[PostingDate] [datetime] NULL
) ON [PRIMARY]
-- PRIMARY KEY
ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
(
[PatientID],
[PatientType],
[DNTYY],
[DNTMM],
[DNTDD],
[DNSEQ],
[OFFICE_NUM]
) ON [PRIMARY]
-- INDEXES
CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
[PRIMARY]
CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
[PRIMARY]
CREATE INDEX [PROCEDURESUFFIX] ON
[dbo].[tbl_AHSTDN]([ProcedureSuffix]) ON [PRIMARY]
CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
[PRIMARY]
CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
CREATE INDEX [TRANSACTIONDATE] ON
[dbo].[tbl_AHSTDN]([TransactionDate]) ON [PRIMARY]
CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate])
ON [PRIMARY]
CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
[PRIMARY]
-- added on 7 December 2004 to improve performance on clinic attendance
stored procedures
CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
-- added on 20 January 2005 to improve performance on ticket count
reports for PBO
CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
KEY columns are all nullable!? :( I don't know if it's just me, but I also
don't see a clustered index on this table anywhere either :(.
Anyways, here's one suggestion - before the BULK INSERT drop all indexes
(except the clustered index should you decide to add one), do the BULK
INSERT, and then rebuild the indexes.
At the very least I would imagine the table could stand to be reindexed big
time. One more quick suggestion - check to see if the database size is near
the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSERT
process? If so, resize the database to make it bigger. And if possible use
the simple recovery model for this database.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23g6sjYWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Here's the DDL for table/PK/index creation:
> The table data gets sucked out of an AS/400 every morning into text files
> and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not
> subject to updates; ie, it's a static table.
> Thanks in advance --
> Carl
> CREATE TABLE [dbo].[tbl_AHSTDN] (
> [DNHSTD] [char] (1) NULL ,
> [PatientID] [int] NULL ,
> [PatientType] [int] NULL ,
> [DNTYY] [int] NULL ,
> [DNTMM] [int] NULL ,
> [DNTDD] [int] NULL ,
> [DNSEQ] [int] NULL ,
> [DNID] [int] NULL ,
> [DNIDTY] [char] (1) NULL ,
> [TicketID] [int] NULL ,
> [ProcedureID] [int] NULL ,
> [ProcedureSuffix] [int] NULL ,
> [DNTICX] [int] NULL ,
> [Discipline] [varchar] (100) NULL ,
> [SessionID] [int] NULL ,
> [Grade] [int] NULL ,
> [DNMTHS] [int] NULL ,
> [StudentID] [char] (3) NULL ,
> [DNCGCD] [char] (1) NULL ,
> [DOCMASID] [int] NULL ,
> [DollarAmount] [money] NULL ,
> [DNIN01] [int] NULL ,
> [DNDAT1] [int] NULL ,
> [DNIN02] [int] NULL ,
> [DNDAT2] [int] NULL ,
> [DNCLM_NUM] [char] (5) NULL ,
> [Status] [char] (1) NULL ,
> [DNFILE] [char] (1) NULL ,
> [DNSEQN] [int] NULL ,
> [DNBK06] [char] (6) NULL ,
> [DNFLAG] [char] (1) NULL ,
> [BatchID] [int] NULL ,
> [Tooth] [char] (2) NULL ,
> [Surface] [char] (5) NULL ,
> [DNTTH2] [char] (2) NULL ,
> [DNSUR2] [char] (5) NULL ,
> [DNTTH3] [char] (2) NULL ,
> [DNSUR3] [char] (5) NULL ,
> [DNTTH4] [char] (2) NULL ,
> [DNSUR4] [char] (5) NULL ,
> [DNTTH5] [char] (2) NULL ,
> [DNSUR5] [char] (5) NULL ,
> [Location] [char] (4) NULL ,
> [DNCDAT] [int] NULL ,
> [User] [varchar] (10) NULL ,
> [DNUQID] [int] NULL ,
> [DNBL19] [varchar] (19) NULL ,
> [DNPTS] [real] NULL ,
> [DNGRP] [int] NULL ,
> [DNCMDT] [int] NULL ,
> [OFFICE_NUM] [int] NULL ,
> [TransactionDate] [datetime] NULL ,
> [CompletionDate] [datetime] NULL ,
> [PostingDate] [datetime] NULL
> ) ON [PRIMARY]
> -- PRIMARY KEY
> ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
> (
> [PatientID],
> [PatientType],
> [DNTYY],
> [DNTMM],
> [DNTDD],
> [DNSEQ],
> [OFFICE_NUM]
> ) ON [PRIMARY]
> -- INDEXES
> CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
> CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
> [PRIMARY]
> CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
> CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
> [PRIMARY]
> CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix])
> ON [PRIMARY]
> CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
> CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
> CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
> [PRIMARY]
> CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
> CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate])
> ON [PRIMARY]
> CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON
> [PRIMARY]
> CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
> [PRIMARY]
> -- added on 7 December 2004 to improve performance on clinic attendance
> stored procedures
> CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
> TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
> -- added on 20 January 2005 to improve performance on ticket count reports
> for PBO
> CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
> Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||Hi Mike --
Thanks for your reply. Actually, this stored procedure has been running
without a hitch for so long that I had to go back and refresh my memory
about the columns, NULLs, etc.
Here's what happens:
1) The table is dropped and recreated every morning with no indexes or
primary keys
2) The data is fed in from text files via BULK INSERT
3) The nullable columns in the primary key are modified to be NOT NULL
4) The primary key is added
5) The indexes are added
No clustered index -- I need to rectify that one. Thanks for catching
it. And, since the bulk insert is done and then indexes are added, do I
need to do a reindex?
Anyway, I appreciate your time -- I will keep at it to figure out why it
works in SQL7 but not in SQL2000
Carl
Mike C# wrote:
> First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
> KEY columns are all nullable!? :( I don't know if it's just me, but I als
o
> don't see a clustered index on this table anywhere either :(.
> Anyways, here's one suggestion - before the BULK INSERT drop all indexes
> (except the clustered index should you decide to add one), do the BULK
> INSERT, and then rebuild the indexes.
> At the very least I would imagine the table could stand to be reindexed bi
g
> time. One more quick suggestion - check to see if the database size is ne
ar
> the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSE
RT
> process? If so, resize the database to make it bigger. And if possible u
se
> the simple recovery model for this database.
>|||This can't be the actual DDL. You cannot put the primary key on a NULLable
column:
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table
'tbl_AHSTDN'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
HTH
Kalen Delaney, SQL Server MVP
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23g6sjYWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Here's the DDL for table/PK/index creation:
> The table data gets sucked out of an AS/400 every morning into text files
> and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not
> subject to updates; ie, it's a static table.
> Thanks in advance --
> Carl
> CREATE TABLE [dbo].[tbl_AHSTDN] (
> [DNHSTD] [char] (1) NULL ,
> [PatientID] [int] NULL ,
> [PatientType] [int] NULL ,
> [DNTYY] [int] NULL ,
> [DNTMM] [int] NULL ,
> [DNTDD] [int] NULL ,
> [DNSEQ] [int] NULL ,
> [DNID] [int] NULL ,
> [DNIDTY] [char] (1) NULL ,
> [TicketID] [int] NULL ,
> [ProcedureID] [int] NULL ,
> [ProcedureSuffix] [int] NULL ,
> [DNTICX] [int] NULL ,
> [Discipline] [varchar] (100) NULL ,
> [SessionID] [int] NULL ,
> [Grade] [int] NULL ,
> [DNMTHS] [int] NULL ,
> [StudentID] [char] (3) NULL ,
> [DNCGCD] [char] (1) NULL ,
> [DOCMASID] [int] NULL ,
> [DollarAmount] [money] NULL ,
> [DNIN01] [int] NULL ,
> [DNDAT1] [int] NULL ,
> [DNIN02] [int] NULL ,
> [DNDAT2] [int] NULL ,
> [DNCLM_NUM] [char] (5) NULL ,
> [Status] [char] (1) NULL ,
> [DNFILE] [char] (1) NULL ,
> [DNSEQN] [int] NULL ,
> [DNBK06] [char] (6) NULL ,
> [DNFLAG] [char] (1) NULL ,
> [BatchID] [int] NULL ,
> [Tooth] [char] (2) NULL ,
> [Surface] [char] (5) NULL ,
> [DNTTH2] [char] (2) NULL ,
> [DNSUR2] [char] (5) NULL ,
> [DNTTH3] [char] (2) NULL ,
> [DNSUR3] [char] (5) NULL ,
> [DNTTH4] [char] (2) NULL ,
> [DNSUR4] [char] (5) NULL ,
> [DNTTH5] [char] (2) NULL ,
> [DNSUR5] [char] (5) NULL ,
> [Location] [char] (4) NULL ,
> [DNCDAT] [int] NULL ,
> [User] [varchar] (10) NULL ,
> [DNUQID] [int] NULL ,
> [DNBL19] [varchar] (19) NULL ,
> [DNPTS] [real] NULL ,
> [DNGRP] [int] NULL ,
> [DNCMDT] [int] NULL ,
> [OFFICE_NUM] [int] NULL ,
> [TransactionDate] [datetime] NULL ,
> [CompletionDate] [datetime] NULL ,
> [PostingDate] [datetime] NULL
> ) ON [PRIMARY]
> -- PRIMARY KEY
> ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
> (
> [PatientID],
> [PatientType],
> [DNTYY],
> [DNTMM],
> [DNTDD],
> [DNSEQ],
> [OFFICE_NUM]
> ) ON [PRIMARY]
> -- INDEXES
> CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
> CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
> [PRIMARY]
> CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
> CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
> [PRIMARY]
> CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix])
> ON [PRIMARY]
> CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
> CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
> CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
> [PRIMARY]
> CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
> CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate])
> ON [PRIMARY]
> CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON
> [PRIMARY]
> CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
> [PRIMARY]
> -- added on 7 December 2004 to improve performance on clinic attendance
> stored procedures
> CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
> TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
> -- added on 20 January 2005 to improve performance on ticket count reports
> for PBO
> CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
> Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||In adddition to adding the clustered index, if this database has been
updated from SQL7 to SQL2K, you should update the statistics (see the
sp_updatestats stored procedure documentation in BOL) after the conversion.
If you have not already done this, you should. WARNING, if you have a large
database, this can take a considerable length of time.
Also, look at the query plans for the updates in both databases and see if
there is any differences. If there are, you may need different indexes on
SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
consider using index hints if that signifigantly improves performance.
As an aside, it seems a shame to be moving to SQL2K at this time, expecially
if you are having trouble with it. Any posibility of going directly to SQL
2005?
Tom
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:us5wC1WlGHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hi Mike --
> Thanks for your reply. Actually, this stored procedure has been running
> without a hitch for so long that I had to go back and refresh my memory
> about the columns, NULLs, etc.
> Here's what happens:
> 1) The table is dropped and recreated every morning with no indexes or
> primary keys
> 2) The data is fed in from text files via BULK INSERT
> 3) The nullable columns in the primary key are modified to be NOT NULL
> 4) The primary key is added
> 5) The indexes are added
> No clustered index -- I need to rectify that one. Thanks for catching it.
> And, since the bulk insert is done and then indexes are added, do I need
> to do a reindex?
> Anyway, I appreciate your time -- I will keep at it to figure out why it
> works in SQL7 but not in SQL2000
> Carl
> Mike C# wrote:|||Tom --
Thanks for your help. I updated the statistics -- no improvement.
I will look at the query plans and check for differences.
I used the index tuning wizard in SQL2K and it suggested an additional
index on tbl_AHSTDN.Status (that column is already indexed, but only in
conjunction with other columns in a composite index). I tried that with
no success.
I will also check out index hints to see if that makes a difference.
And as far as SQL2005 goes, I would like to, but need to get a little
more up-to-speed with it first.
Thanks again -- I appreciate your time.
Carl
Tom Cooper wrote:
> In adddition to adding the clustered index, if this database has been
> updated from SQL7 to SQL2K, you should update the statistics (see the
> sp_updatestats stored procedure documentation in BOL) after the conversion
.
> If you have not already done this, you should. WARNING, if you have a lar
ge
> database, this can take a considerable length of time.
> Also, look at the query plans for the updates in both databases and see if
> there is any differences. If there are, you may need different indexes on
> SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
> consider using index hints if that signifigantly improves performance.
> As an aside, it seems a shame to be moving to SQL2K at this time, expecial
ly
> if you are having trouble with it. Any posibility of going directly to SQ
L
> 2005?
> Tom
>|||sp_updatestats is weak, IMHO. Try doing an UPDATE STATISTICS WITH FULLSCAN.
That made a huge difference for me.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:OYuDJOYlGHA.380@.TK2MSFTNGP05.phx.gbl...
Tom --
Thanks for your help. I updated the statistics -- no improvement.
I will look at the query plans and check for differences.
I used the index tuning wizard in SQL2K and it suggested an additional
index on tbl_AHSTDN.Status (that column is already indexed, but only in
conjunction with other columns in a composite index). I tried that with
no success.
I will also check out index hints to see if that makes a difference.
And as far as SQL2005 goes, I would like to, but need to get a little
more up-to-speed with it first.
Thanks again -- I appreciate your time.
Carl
Tom Cooper wrote:
> In adddition to adding the clustered index, if this database has been
> updated from SQL7 to SQL2K, you should update the statistics (see the
> sp_updatestats stored procedure documentation in BOL) after the
> conversion.
> If you have not already done this, you should. WARNING, if you have a
> large
> database, this can take a considerable length of time.
> Also, look at the query plans for the updates in both databases and see if
> there is any differences. If there are, you may need different indexes on
> SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
> consider using index hints if that signifigantly improves performance.
> As an aside, it seems a shame to be moving to SQL2K at this time,
> expecially
> if you are having trouble with it. Any posibility of going directly to
> SQL
> 2005?
> Tom
>|||Hmmm... I thought the table was static - and that new records were just
being appended to the end. If you're dropping and recreating it each time,
then no need to reindex. Just create a clustered index before you start the
BULK INSERT, and create your other indexes after the fact. I'm not sure
where the speed problem is coming from. You don't happen to be pulling the
file from across the network (mapped drive or something)? And your database
is large enough to accomodate the new data (i.e., it's not autoshrinking and
autogrowing)?
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:us5wC1WlGHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hi Mike --
> Thanks for your reply. Actually, this stored procedure has been running
> without a hitch for so long that I had to go back and refresh my memory
> about the columns, NULLs, etc.
> Here's what happens:
> 1) The table is dropped and recreated every morning with no indexes or
> primary keys
> 2) The data is fed in from text files via BULK INSERT
> 3) The nullable columns in the primary key are modified to be NOT NULL
> 4) The primary key is added
> 5) The indexes are added
> No clustered index -- I need to rectify that one. Thanks for catching it.
> And, since the bulk insert is done and then indexes are added, do I need
> to do a reindex?
> Anyway, I appreciate your time -- I will keep at it to figure out why it
> works in SQL7 but not in SQL2000
> Carl
> Mike C# wrote:
have come up against a problem. The following code executes correctly in
the existing SQL7 database and runs to completion in about 1 minute.
In the 2000 database, it runs until I cancel execution -- I've let it
run up to 30 minutes without showing any signs of finishing.
I am using the identical code to populate the tables in both databases
-- the data actually gets fed in from text files via a BULK INSERT
command, and that runs correctly in the both databases. The indexes and
primary keys are created via T-SQL code, and that runs correctly in both
databases.
I have deleted and re-created the stored procedure containing this code
to no avail. It will still run until I cancel execution.
Did something change between SQL7 and 2000 with the UPDATE command, or
am I missing something else?
Any help will be appreciated --
Carl
UPDATE tblProcedureHistory
SET tblProcedureHistory.Status_A = [derived].MaxPostingDate
FROM
(
SELECT T1.OFFICE_NUM,
T1.PatientID,
T1.PatientType,
T1.StudentID,
T1.ProcedureID,
T1.ProcedureSuffix,
T1.Tooth,
T1.Surface,
MAX(T1.PostingDate) AS MaxPostingDate
FROM tbl_AHSTDN AS T1
INNER JOIN tblProcedureHistory AS T2
ON T1.OFFICE_NUM = T2.OFFICE_NUM
AND T1.PatientID = T2.PatientID
AND T1.PatientType = T2.PatientType
AND T1.StudentID = T2.StudentID
AND T1.ProcedureID = T2.ProcedureID
AND T1.ProcedureSuffix = T2.ProcedureSuffix
AND T1.Tooth = T2.Tooth
AND T1.Surface = T2.Surface
AND T1.Status = 'A'
GROUP BY
T1.OFFICE_NUM,
T1.PatientID,
T1.PatientType,
T1.StudentID,
T1.ProcedureID,
T1.ProcedureSuffix,
T1.Tooth,
T1.Surface
)
AS [derived]
WHERE
tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM
AND tblProcedureHistory.PatientID = [derived].PatientID
AND tblProcedureHistory.PatientType = [derived].PatientType
AND tblProcedureHistory.StudentID = [derived].StudentID
AND tblProcedureHistory.ProcedureID = [derived].ProcedureID
AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix
AND tblProcedureHistory.Tooth = [derived].Tooth
AND tblProcedureHistory.Surface = [derived].SurfaceLet's see your DDL including Primary Keys and Indexes.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23di7RAWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> I'm in the process of converting a database from SQL 7 to SQL 2000 and
> have come up against a problem. The following code executes correctly in
> the existing SQL7 database and runs to completion in about 1 minute.
> In the 2000 database, it runs until I cancel execution -- I've let it run
> up to 30 minutes without showing any signs of finishing.
> I am using the identical code to populate the tables in both databases --
> the data actually gets fed in from text files via a BULK INSERT command,
> and that runs correctly in the both databases. The indexes and primary
> keys are created via T-SQL code, and that runs correctly in both
> databases.
> I have deleted and re-created the stored procedure containing this code to
> no avail. It will still run until I cancel execution.
> Did something change between SQL7 and 2000 with the UPDATE command, or am
> I missing something else?
> Any help will be appreciated --
> Carl
> UPDATE tblProcedureHistory
> SET tblProcedureHistory.Status_A = [derived].MaxPostingDate
> FROM
> (
> SELECT T1.OFFICE_NUM,
> T1.PatientID,
> T1.PatientType,
> T1.StudentID,
> T1.ProcedureID,
> T1.ProcedureSuffix,
> T1.Tooth,
> T1.Surface,
> MAX(T1.PostingDate) AS MaxPostingDate
> FROM tbl_AHSTDN AS T1
> INNER JOIN tblProcedureHistory AS T2
> ON T1.OFFICE_NUM = T2.OFFICE_NUM
> AND T1.PatientID = T2.PatientID
> AND T1.PatientType = T2.PatientType
> AND T1.StudentID = T2.StudentID
> AND T1.ProcedureID = T2.ProcedureID
> AND T1.ProcedureSuffix = T2.ProcedureSuffix
> AND T1.Tooth = T2.Tooth
> AND T1.Surface = T2.Surface
> AND T1.Status = 'A'
> GROUP BY
> T1.OFFICE_NUM,
> T1.PatientID,
> T1.PatientType,
> T1.StudentID,
> T1.ProcedureID,
> T1.ProcedureSuffix,
> T1.Tooth,
> T1.Surface
> )
> AS [derived]
> WHERE
> tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM
> AND tblProcedureHistory.PatientID = [derived].PatientID
> AND tblProcedureHistory.PatientType = [derived].PatientType
> AND tblProcedureHistory.StudentID = [derived].StudentID
> AND tblProcedureHistory.ProcedureID = [derived].ProcedureID
> AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix
> AND tblProcedureHistory.Tooth = [derived].Tooth
> AND tblProcedureHistory.Surface = [derived].Surface|||Here's the DDL for table/PK/index creation:
The table data gets sucked out of an AS/400 every morning into text
files and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is
not subject to updates; ie, it's a static table.
Thanks in advance --
Carl
CREATE TABLE [dbo].[tbl_AHSTDN] (
[DNHSTD] [char] (1) NULL ,
[PatientID] [int] NULL ,
[PatientType] [int] NULL ,
[DNTYY] [int] NULL ,
[DNTMM] [int] NULL ,
[DNTDD] [int] NULL ,
[DNSEQ] [int] NULL ,
[DNID] [int] NULL ,
[DNIDTY] [char] (1) NULL ,
[TicketID] [int] NULL ,
[ProcedureID] [int] NULL ,
[ProcedureSuffix] [int] NULL ,
[DNTICX] [int] NULL ,
[Discipline] [varchar] (100) NULL ,
[SessionID] [int] NULL ,
[Grade] [int] NULL ,
[DNMTHS] [int] NULL ,
[StudentID] [char] (3) NULL ,
[DNCGCD] [char] (1) NULL ,
[DOCMASID] [int] NULL ,
[DollarAmount] [money] NULL ,
[DNIN01] [int] NULL ,
[DNDAT1] [int] NULL ,
[DNIN02] [int] NULL ,
[DNDAT2] [int] NULL ,
[DNCLM_NUM] [char] (5) NULL ,
[Status] [char] (1) NULL ,
[DNFILE] [char] (1) NULL ,
[DNSEQN] [int] NULL ,
[DNBK06] [char] (6) NULL ,
[DNFLAG] [char] (1) NULL ,
[BatchID] [int] NULL ,
[Tooth] [char] (2) NULL ,
[Surface] [char] (5) NULL ,
[DNTTH2] [char] (2) NULL ,
[DNSUR2] [char] (5) NULL ,
[DNTTH3] [char] (2) NULL ,
[DNSUR3] [char] (5) NULL ,
[DNTTH4] [char] (2) NULL ,
[DNSUR4] [char] (5) NULL ,
[DNTTH5] [char] (2) NULL ,
[DNSUR5] [char] (5) NULL ,
[Location] [char] (4) NULL ,
[DNCDAT] [int] NULL ,
[User] [varchar] (10) NULL ,
[DNUQID] [int] NULL ,
[DNBL19] [varchar] (19) NULL ,
[DNPTS] [real] NULL ,
[DNGRP] [int] NULL ,
[DNCMDT] [int] NULL ,
[OFFICE_NUM] [int] NULL ,
[TransactionDate] [datetime] NULL ,
[CompletionDate] [datetime] NULL ,
[PostingDate] [datetime] NULL
) ON [PRIMARY]
-- PRIMARY KEY
ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
(
[PatientID],
[PatientType],
[DNTYY],
[DNTMM],
[DNTDD],
[DNSEQ],
[OFFICE_NUM]
) ON [PRIMARY]
-- INDEXES
CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
[PRIMARY]
CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
[PRIMARY]
CREATE INDEX [PROCEDURESUFFIX] ON
[dbo].[tbl_AHSTDN]([ProcedureSuffix]) ON [PRIMARY]
CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
[PRIMARY]
CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
CREATE INDEX [TRANSACTIONDATE] ON
[dbo].[tbl_AHSTDN]([TransactionDate]) ON [PRIMARY]
CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate])
ON [PRIMARY]
CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
[PRIMARY]
-- added on 7 December 2004 to improve performance on clinic attendance
stored procedures
CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
-- added on 20 January 2005 to improve performance on ticket count
reports for PBO
CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
KEY columns are all nullable!? :( I don't know if it's just me, but I also
don't see a clustered index on this table anywhere either :(.
Anyways, here's one suggestion - before the BULK INSERT drop all indexes
(except the clustered index should you decide to add one), do the BULK
INSERT, and then rebuild the indexes.
At the very least I would imagine the table could stand to be reindexed big
time. One more quick suggestion - check to see if the database size is near
the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSERT
process? If so, resize the database to make it bigger. And if possible use
the simple recovery model for this database.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23g6sjYWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Here's the DDL for table/PK/index creation:
> The table data gets sucked out of an AS/400 every morning into text files
> and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not
> subject to updates; ie, it's a static table.
> Thanks in advance --
> Carl
> CREATE TABLE [dbo].[tbl_AHSTDN] (
> [DNHSTD] [char] (1) NULL ,
> [PatientID] [int] NULL ,
> [PatientType] [int] NULL ,
> [DNTYY] [int] NULL ,
> [DNTMM] [int] NULL ,
> [DNTDD] [int] NULL ,
> [DNSEQ] [int] NULL ,
> [DNID] [int] NULL ,
> [DNIDTY] [char] (1) NULL ,
> [TicketID] [int] NULL ,
> [ProcedureID] [int] NULL ,
> [ProcedureSuffix] [int] NULL ,
> [DNTICX] [int] NULL ,
> [Discipline] [varchar] (100) NULL ,
> [SessionID] [int] NULL ,
> [Grade] [int] NULL ,
> [DNMTHS] [int] NULL ,
> [StudentID] [char] (3) NULL ,
> [DNCGCD] [char] (1) NULL ,
> [DOCMASID] [int] NULL ,
> [DollarAmount] [money] NULL ,
> [DNIN01] [int] NULL ,
> [DNDAT1] [int] NULL ,
> [DNIN02] [int] NULL ,
> [DNDAT2] [int] NULL ,
> [DNCLM_NUM] [char] (5) NULL ,
> [Status] [char] (1) NULL ,
> [DNFILE] [char] (1) NULL ,
> [DNSEQN] [int] NULL ,
> [DNBK06] [char] (6) NULL ,
> [DNFLAG] [char] (1) NULL ,
> [BatchID] [int] NULL ,
> [Tooth] [char] (2) NULL ,
> [Surface] [char] (5) NULL ,
> [DNTTH2] [char] (2) NULL ,
> [DNSUR2] [char] (5) NULL ,
> [DNTTH3] [char] (2) NULL ,
> [DNSUR3] [char] (5) NULL ,
> [DNTTH4] [char] (2) NULL ,
> [DNSUR4] [char] (5) NULL ,
> [DNTTH5] [char] (2) NULL ,
> [DNSUR5] [char] (5) NULL ,
> [Location] [char] (4) NULL ,
> [DNCDAT] [int] NULL ,
> [User] [varchar] (10) NULL ,
> [DNUQID] [int] NULL ,
> [DNBL19] [varchar] (19) NULL ,
> [DNPTS] [real] NULL ,
> [DNGRP] [int] NULL ,
> [DNCMDT] [int] NULL ,
> [OFFICE_NUM] [int] NULL ,
> [TransactionDate] [datetime] NULL ,
> [CompletionDate] [datetime] NULL ,
> [PostingDate] [datetime] NULL
> ) ON [PRIMARY]
> -- PRIMARY KEY
> ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
> (
> [PatientID],
> [PatientType],
> [DNTYY],
> [DNTMM],
> [DNTDD],
> [DNSEQ],
> [OFFICE_NUM]
> ) ON [PRIMARY]
> -- INDEXES
> CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
> CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
> [PRIMARY]
> CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
> CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
> [PRIMARY]
> CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix])
> ON [PRIMARY]
> CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
> CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
> CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
> [PRIMARY]
> CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
> CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate])
> ON [PRIMARY]
> CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON
> [PRIMARY]
> CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
> [PRIMARY]
> -- added on 7 December 2004 to improve performance on clinic attendance
> stored procedures
> CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
> TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
> -- added on 20 January 2005 to improve performance on ticket count reports
> for PBO
> CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
> Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||Hi Mike --
Thanks for your reply. Actually, this stored procedure has been running
without a hitch for so long that I had to go back and refresh my memory
about the columns, NULLs, etc.
Here's what happens:
1) The table is dropped and recreated every morning with no indexes or
primary keys
2) The data is fed in from text files via BULK INSERT
3) The nullable columns in the primary key are modified to be NOT NULL
4) The primary key is added
5) The indexes are added
No clustered index -- I need to rectify that one. Thanks for catching
it. And, since the bulk insert is done and then indexes are added, do I
need to do a reindex?
Anyway, I appreciate your time -- I will keep at it to figure out why it
works in SQL7 but not in SQL2000
Carl
Mike C# wrote:
> First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
> KEY columns are all nullable!? :( I don't know if it's just me, but I als
o
> don't see a clustered index on this table anywhere either :(.
> Anyways, here's one suggestion - before the BULK INSERT drop all indexes
> (except the clustered index should you decide to add one), do the BULK
> INSERT, and then rebuild the indexes.
> At the very least I would imagine the table could stand to be reindexed bi
g
> time. One more quick suggestion - check to see if the database size is ne
ar
> the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSE
RT
> process? If so, resize the database to make it bigger. And if possible u
se
> the simple recovery model for this database.
>|||This can't be the actual DDL. You cannot put the primary key on a NULLable
column:
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table
'tbl_AHSTDN'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
HTH
Kalen Delaney, SQL Server MVP
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23g6sjYWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Here's the DDL for table/PK/index creation:
> The table data gets sucked out of an AS/400 every morning into text files
> and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not
> subject to updates; ie, it's a static table.
> Thanks in advance --
> Carl
> CREATE TABLE [dbo].[tbl_AHSTDN] (
> [DNHSTD] [char] (1) NULL ,
> [PatientID] [int] NULL ,
> [PatientType] [int] NULL ,
> [DNTYY] [int] NULL ,
> [DNTMM] [int] NULL ,
> [DNTDD] [int] NULL ,
> [DNSEQ] [int] NULL ,
> [DNID] [int] NULL ,
> [DNIDTY] [char] (1) NULL ,
> [TicketID] [int] NULL ,
> [ProcedureID] [int] NULL ,
> [ProcedureSuffix] [int] NULL ,
> [DNTICX] [int] NULL ,
> [Discipline] [varchar] (100) NULL ,
> [SessionID] [int] NULL ,
> [Grade] [int] NULL ,
> [DNMTHS] [int] NULL ,
> [StudentID] [char] (3) NULL ,
> [DNCGCD] [char] (1) NULL ,
> [DOCMASID] [int] NULL ,
> [DollarAmount] [money] NULL ,
> [DNIN01] [int] NULL ,
> [DNDAT1] [int] NULL ,
> [DNIN02] [int] NULL ,
> [DNDAT2] [int] NULL ,
> [DNCLM_NUM] [char] (5) NULL ,
> [Status] [char] (1) NULL ,
> [DNFILE] [char] (1) NULL ,
> [DNSEQN] [int] NULL ,
> [DNBK06] [char] (6) NULL ,
> [DNFLAG] [char] (1) NULL ,
> [BatchID] [int] NULL ,
> [Tooth] [char] (2) NULL ,
> [Surface] [char] (5) NULL ,
> [DNTTH2] [char] (2) NULL ,
> [DNSUR2] [char] (5) NULL ,
> [DNTTH3] [char] (2) NULL ,
> [DNSUR3] [char] (5) NULL ,
> [DNTTH4] [char] (2) NULL ,
> [DNSUR4] [char] (5) NULL ,
> [DNTTH5] [char] (2) NULL ,
> [DNSUR5] [char] (5) NULL ,
> [Location] [char] (4) NULL ,
> [DNCDAT] [int] NULL ,
> [User] [varchar] (10) NULL ,
> [DNUQID] [int] NULL ,
> [DNBL19] [varchar] (19) NULL ,
> [DNPTS] [real] NULL ,
> [DNGRP] [int] NULL ,
> [DNCMDT] [int] NULL ,
> [OFFICE_NUM] [int] NULL ,
> [TransactionDate] [datetime] NULL ,
> [CompletionDate] [datetime] NULL ,
> [PostingDate] [datetime] NULL
> ) ON [PRIMARY]
> -- PRIMARY KEY
> ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
> (
> [PatientID],
> [PatientType],
> [DNTYY],
> [DNTMM],
> [DNTDD],
> [DNSEQ],
> [OFFICE_NUM]
> ) ON [PRIMARY]
> -- INDEXES
> CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
> CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
> [PRIMARY]
> CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
> CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
> [PRIMARY]
> CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix])
> ON [PRIMARY]
> CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
> CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
> CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
> [PRIMARY]
> CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
> CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate])
> ON [PRIMARY]
> CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON
> [PRIMARY]
> CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
> [PRIMARY]
> -- added on 7 December 2004 to improve performance on clinic attendance
> stored procedures
> CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
> TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
> -- added on 20 January 2005 to improve performance on ticket count reports
> for PBO
> CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
> Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||In adddition to adding the clustered index, if this database has been
updated from SQL7 to SQL2K, you should update the statistics (see the
sp_updatestats stored procedure documentation in BOL) after the conversion.
If you have not already done this, you should. WARNING, if you have a large
database, this can take a considerable length of time.
Also, look at the query plans for the updates in both databases and see if
there is any differences. If there are, you may need different indexes on
SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
consider using index hints if that signifigantly improves performance.
As an aside, it seems a shame to be moving to SQL2K at this time, expecially
if you are having trouble with it. Any posibility of going directly to SQL
2005?
Tom
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:us5wC1WlGHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hi Mike --
> Thanks for your reply. Actually, this stored procedure has been running
> without a hitch for so long that I had to go back and refresh my memory
> about the columns, NULLs, etc.
> Here's what happens:
> 1) The table is dropped and recreated every morning with no indexes or
> primary keys
> 2) The data is fed in from text files via BULK INSERT
> 3) The nullable columns in the primary key are modified to be NOT NULL
> 4) The primary key is added
> 5) The indexes are added
> No clustered index -- I need to rectify that one. Thanks for catching it.
> And, since the bulk insert is done and then indexes are added, do I need
> to do a reindex?
> Anyway, I appreciate your time -- I will keep at it to figure out why it
> works in SQL7 but not in SQL2000
> Carl
> Mike C# wrote:|||Tom --
Thanks for your help. I updated the statistics -- no improvement.
I will look at the query plans and check for differences.
I used the index tuning wizard in SQL2K and it suggested an additional
index on tbl_AHSTDN.Status (that column is already indexed, but only in
conjunction with other columns in a composite index). I tried that with
no success.
I will also check out index hints to see if that makes a difference.
And as far as SQL2005 goes, I would like to, but need to get a little
more up-to-speed with it first.
Thanks again -- I appreciate your time.
Carl
Tom Cooper wrote:
> In adddition to adding the clustered index, if this database has been
> updated from SQL7 to SQL2K, you should update the statistics (see the
> sp_updatestats stored procedure documentation in BOL) after the conversion
.
> If you have not already done this, you should. WARNING, if you have a lar
ge
> database, this can take a considerable length of time.
> Also, look at the query plans for the updates in both databases and see if
> there is any differences. If there are, you may need different indexes on
> SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
> consider using index hints if that signifigantly improves performance.
> As an aside, it seems a shame to be moving to SQL2K at this time, expecial
ly
> if you are having trouble with it. Any posibility of going directly to SQ
L
> 2005?
> Tom
>|||sp_updatestats is weak, IMHO. Try doing an UPDATE STATISTICS WITH FULLSCAN.
That made a huge difference for me.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:OYuDJOYlGHA.380@.TK2MSFTNGP05.phx.gbl...
Tom --
Thanks for your help. I updated the statistics -- no improvement.
I will look at the query plans and check for differences.
I used the index tuning wizard in SQL2K and it suggested an additional
index on tbl_AHSTDN.Status (that column is already indexed, but only in
conjunction with other columns in a composite index). I tried that with
no success.
I will also check out index hints to see if that makes a difference.
And as far as SQL2005 goes, I would like to, but need to get a little
more up-to-speed with it first.
Thanks again -- I appreciate your time.
Carl
Tom Cooper wrote:
> In adddition to adding the clustered index, if this database has been
> updated from SQL7 to SQL2K, you should update the statistics (see the
> sp_updatestats stored procedure documentation in BOL) after the
> conversion.
> If you have not already done this, you should. WARNING, if you have a
> large
> database, this can take a considerable length of time.
> Also, look at the query plans for the updates in both databases and see if
> there is any differences. If there are, you may need different indexes on
> SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
> consider using index hints if that signifigantly improves performance.
> As an aside, it seems a shame to be moving to SQL2K at this time,
> expecially
> if you are having trouble with it. Any posibility of going directly to
> SQL
> 2005?
> Tom
>|||Hmmm... I thought the table was static - and that new records were just
being appended to the end. If you're dropping and recreating it each time,
then no need to reindex. Just create a clustered index before you start the
BULK INSERT, and create your other indexes after the fact. I'm not sure
where the speed problem is coming from. You don't happen to be pulling the
file from across the network (mapped drive or something)? And your database
is large enough to accomodate the new data (i.e., it's not autoshrinking and
autogrowing)?
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:us5wC1WlGHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hi Mike --
> Thanks for your reply. Actually, this stored procedure has been running
> without a hitch for so long that I had to go back and refresh my memory
> about the columns, NULLs, etc.
> Here's what happens:
> 1) The table is dropped and recreated every morning with no indexes or
> primary keys
> 2) The data is fed in from text files via BULK INSERT
> 3) The nullable columns in the primary key are modified to be NOT NULL
> 4) The primary key is added
> 5) The indexes are added
> No clustered index -- I need to rectify that one. Thanks for catching it.
> And, since the bulk insert is done and then indexes are added, do I need
> to do a reindex?
> Anyway, I appreciate your time -- I will keep at it to figure out why it
> works in SQL7 but not in SQL2000
> Carl
> Mike C# wrote:
Subscribe to:
Posts (Atom)