It doesnt execute my assemblies.

Hello I just did 2 stored procedures, they dont have errors and they deployed succesfully but after I tried to exec them it doesnt happen anything.
I waited for 10 minutes and nothing

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void getSalesOrdersHeaders()

{

// Put your code here

SqlPipe sp = SqlContext.Pipe;

using (SqlConnection conn = new SqlConnection(@."Data Source=ESTACION15\SQL2005; User Id=pruebas; Password=pruebas;database=AdventureWorks;"))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = "select * from sales.SalesOrderHeader";

SqlDataReader rdr = cmd.ExecuteReader();

sp.Send(rdr);

}

}

};

I tried to run it from visual studio .net and I get A TimeOut error

Error 1 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pubs|||

How did you run it from Visual Studio?
From server explorer, test scripts or database project script

Thanks,
-Vineet.

|||Hello. It suddently began working after restarting my machine. Strange behavior.|||same here. It works after restarting my machine. Is this really normal? Any hints on this one?|||dunno if this is relevant or what you wrote was intended, but by creating the connection like you do you're creating a new connection from the connection your sproc's already using.

SqlConnection conn = new SqlConnection("context connection = true");

will get a reference to the existing one.
more info @. http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx

It doesnt execute my assemblies.

Hello I just did 2 stored procedures, they dont have errors and they deployed succesfully but after I tried to exec them it doesnt happen anything.
I waited for 10 minutes and nothing

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void getSalesOrdersHeaders()

{

// Put your code here

SqlPipe sp = SqlContext.Pipe;

using (SqlConnection conn = new SqlConnection(@."Data Source=ESTACION15\SQL2005; User Id=pruebas; Password=pruebas;database=AdventureWorks;"))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = "select * from sales.SalesOrderHeader";

SqlDataReader rdr = cmd.ExecuteReader();

sp.Send(rdr);

}

}

};

I tried to run it from visual studio .net and I get A TimeOut error

Error 1 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pubs|||

How did you run it from Visual Studio?
From server explorer, test scripts or database project script

Thanks,
-Vineet.

|||Hello. It suddently began working after restarting my machine. Strange behavior.|||same here. It works after restarting my machine. Is this really normal? Any hints on this one?|||dunno if this is relevant or what you wrote was intended, but by creating the connection like you do you're creating a new connection from the connection your sproc's already using.

SqlConnection conn = new SqlConnection("context connection = true");

will get a reference to the existing one.
more info @. http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx

Issues with temp tables in stored procedures using OLEDB connectio

I am having an issue when converting some of my reports to SQL reporting
services 2005. All of my reports use an OLEDB connection that point to a UDL
file so that we can easily change connection strings as needed without having
to touch the report server. They all use stored procedures to obtain the
data. The issues I am running into is with stored procedures that use temp
tables. Anything that is done in a stored procedure after the temp table is
created is not shown in the report designer on the data tab within visual
studio 2005. Iâ've read a few posts about this problem but it seems like no
one at Microsoft has been able to reproduce it. I have been able to
reproduce it with a simple example using the NorthWind database. I will
include the stored procedure and the rdl file below. I set the report to
prompt for credentials instead of using the UDL file but the same problem
occurs. It will occur with Windows authentication as well. If I change the
connection type to â'Microsoft SQL Serverâ' then it will work correctly the
problem is that I will not be able to use UDL or DSN files. My method worked
just fine with RS 2000. What has changed with RS 2005 to cause this issue?
Any ideas on how I might get this to work?
Thanks,
-Nathan
SP Script-
use northwind
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
AS
Select FirstName
into #EmployeeTemp
FROM Employees
--*NOTE It doesnâ't matter what you do here it will not be returned in the
Report Designer Data tab.
SELECT FirstName
FROM #EmployeeTemp
--Select 'Test'
RDL File-
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<Prompt>Specify a user name and password for data source
DataSource1</Prompt>
<ConnectString>Provider=SQLOLEDB.1;Data Source=saintdevsql1;Initial
Catalog=Northwind</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>0.75in</Height>
</Body>
<rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Northwind">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>Pr_TestingRSProblem</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>I meant to mention that I tried the sp with and without explicitly dropping
the table at the end. Neither way worked.
"Nathan" wrote:
> I am having an issue when converting some of my reports to SQL reporting
> services 2005. All of my reports use an OLEDB connection that point to a UDL
> file so that we can easily change connection strings as needed without having
> to touch the report server. They all use stored procedures to obtain the
> data. The issues I am running into is with stored procedures that use temp
> tables. Anything that is done in a stored procedure after the temp table is
> created is not shown in the report designer on the data tab within visual
> studio 2005. Iâ've read a few posts about this problem but it seems like no
> one at Microsoft has been able to reproduce it. I have been able to
> reproduce it with a simple example using the NorthWind database. I will
> include the stored procedure and the rdl file below. I set the report to
> prompt for credentials instead of using the UDL file but the same problem
> occurs. It will occur with Windows authentication as well. If I change the
> connection type to â'Microsoft SQL Serverâ' then it will work correctly the
> problem is that I will not be able to use UDL or DSN files. My method worked
> just fine with RS 2000. What has changed with RS 2005 to cause this issue?
> Any ideas on how I might get this to work?
> Thanks,
> -Nathan
> SP Script-
> use northwind
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> AS
> Select FirstName
> into #EmployeeTemp
> FROM Employees
> --*NOTE It doesnâ't matter what you do here it will not be returned in the
> Report Designer Data tab.
> SELECT FirstName
> FROM #EmployeeTemp
> --Select 'Test'
>
> RDL File-
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="DataSource1">
> <ConnectionProperties>
> <Prompt>Specify a user name and password for data source
> DataSource1</Prompt>
> <ConnectString>Provider=SQLOLEDB.1;Data Source=saintdevsql1;Initial
> Catalog=Northwind</ConnectString>
> <DataProvider>OLEDB</DataProvider>
> </ConnectionProperties>
> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <Height>0.75in</Height>
> </Body>
> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="Northwind">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandType>StoredProcedure</CommandType>
> <CommandText>Pr_TestingRSProblem</CommandText>
> <DataSourceName>DataSource1</DataSourceName>
> </Query>
> </DataSet>
> </DataSets>
> <Width>6.5in</Width>
> <InteractiveHeight>11in</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>1in</TopMargin>
> </Report>
>|||First, do not explicitly drop the table.
Second, exactly what is happening? No data? No field list? Any error?
I just had a case where I would get an error about a temp table (I use temp
tables all the time, why this particular SP had a problem I don't know).
What worked for me was to refresh the fields (button is to the right of the
...). I then had a field list. After that I could execute the query in the
data tab and I got data back.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> I meant to mention that I tried the sp with and without explicitly
> dropping
> the table at the end. Neither way worked.
> "Nathan" wrote:
>> I am having an issue when converting some of my reports to SQL reporting
>> services 2005. All of my reports use an OLEDB connection that point to a
>> UDL
>> file so that we can easily change connection strings as needed without
>> having
>> to touch the report server. They all use stored procedures to obtain the
>> data. The issues I am running into is with stored procedures that use
>> temp
>> tables. Anything that is done in a stored procedure after the temp table
>> is
>> created is not shown in the report designer on the data tab within visual
>> studio 2005. I've read a few posts about this problem but it seems like
>> no
>> one at Microsoft has been able to reproduce it. I have been able to
>> reproduce it with a simple example using the NorthWind database. I will
>> include the stored procedure and the rdl file below. I set the report to
>> prompt for credentials instead of using the UDL file but the same problem
>> occurs. It will occur with Windows authentication as well. If I change
>> the
>> connection type to "Microsoft SQL Server" then it will work correctly the
>> problem is that I will not be able to use UDL or DSN files. My method
>> worked
>> just fine with RS 2000. What has changed with RS 2005 to cause this
>> issue?
>> Any ideas on how I might get this to work?
>> Thanks,
>> -Nathan
>> SP Script-
>> use northwind
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER OFF
>> GO
>> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> AS
>> Select FirstName
>> into #EmployeeTemp
>> FROM Employees
>> --*NOTE It doesn't matter what you do here it will not be returned in the
>> Report Designer Data tab.
>> SELECT FirstName
>> FROM #EmployeeTemp
>> --Select 'Test'
>>
>> RDL File-
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="DataSource1">
>> <ConnectionProperties>
>> <Prompt>Specify a user name and password for data source
>> DataSource1</Prompt>
>> <ConnectString>Provider=SQLOLEDB.1;Data
>> Source=saintdevsql1;Initial
>> Catalog=Northwind</ConnectString>
>> <DataProvider>OLEDB</DataProvider>
>> </ConnectionProperties>
>> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <Height>0.75in</Height>
>> </Body>
>> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="Northwind">
>> <Query>
>> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> <CommandType>StoredProcedure</CommandType>
>> <CommandText>Pr_TestingRSProblem</CommandText>
>> <DataSourceName>DataSource1</DataSourceName>
>> </Query>
>> </DataSet>
>> </DataSets>
>> <Width>6.5in</Width>
>> <InteractiveHeight>11in</InteractiveHeight>
>> <Language>en-US</Language>
>> <TopMargin>1in</TopMargin>
>> </Report>|||Bruce,
The simple answer is there is no data, no field list, and no error. I
should have mentioned that I have tried refreshing the field list several
times and no fields are returned. I've tried this on two separate machines.
The field list is empty and when I try to run the sp within the data tab it
returns no results. Were you not able to reproduce the problem with an OLEDB
connection type? What I found strange was that anything in the sp before the
temp table declaration worked fine. It's as if the meta data describing the
table structure isn't returned. Any ideas? Let me know if I can provide you
with any more information. Thanks for the quick reply.
-Nathan
"Bruce L-C [MVP]" wrote:
> First, do not explicitly drop the table.
> Second, exactly what is happening? No data? No field list? Any error?
> I just had a case where I would get an error about a temp table (I use temp
> tables all the time, why this particular SP had a problem I don't know).
> What worked for me was to refresh the fields (button is to the right of the
> ...). I then had a field list. After that I could execute the query in the
> data tab and I got data back.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> >
> > I meant to mention that I tried the sp with and without explicitly
> > dropping
> > the table at the end. Neither way worked.
> >
> > "Nathan" wrote:
> >
> >> I am having an issue when converting some of my reports to SQL reporting
> >> services 2005. All of my reports use an OLEDB connection that point to a
> >> UDL
> >> file so that we can easily change connection strings as needed without
> >> having
> >> to touch the report server. They all use stored procedures to obtain the
> >> data. The issues I am running into is with stored procedures that use
> >> temp
> >> tables. Anything that is done in a stored procedure after the temp table
> >> is
> >> created is not shown in the report designer on the data tab within visual
> >> studio 2005. I've read a few posts about this problem but it seems like
> >> no
> >> one at Microsoft has been able to reproduce it. I have been able to
> >> reproduce it with a simple example using the NorthWind database. I will
> >> include the stored procedure and the rdl file below. I set the report to
> >> prompt for credentials instead of using the UDL file but the same problem
> >> occurs. It will occur with Windows authentication as well. If I change
> >> the
> >> connection type to "Microsoft SQL Server" then it will work correctly the
> >> problem is that I will not be able to use UDL or DSN files. My method
> >> worked
> >> just fine with RS 2000. What has changed with RS 2005 to cause this
> >> issue?
> >> Any ideas on how I might get this to work?
> >>
> >> Thanks,
> >> -Nathan
> >>
> >> SP Script-
> >>
> >> use northwind
> >>
> >> set ANSI_NULLS ON
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >>
> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> >> AS
> >>
> >> Select FirstName
> >> into #EmployeeTemp
> >> FROM Employees
> >> --*NOTE It doesn't matter what you do here it will not be returned in the
> >> Report Designer Data tab.
> >> SELECT FirstName
> >> FROM #EmployeeTemp
> >> --Select 'Test'
> >>
> >>
> >> RDL File-
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="DataSource1">
> >> <ConnectionProperties>
> >> <Prompt>Specify a user name and password for data source
> >> DataSource1</Prompt>
> >> <ConnectString>Provider=SQLOLEDB.1;Data
> >> Source=saintdevsql1;Initial
> >> Catalog=Northwind</ConnectString>
> >> <DataProvider>OLEDB</DataProvider>
> >> </ConnectionProperties>
> >>
> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <Height>0.75in</Height>
> >> </Body>
> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> >> <LeftMargin>1in</LeftMargin>
> >> <DataSets>
> >> <DataSet Name="Northwind">
> >> <Query>
> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> >> <CommandType>StoredProcedure</CommandType>
> >> <CommandText>Pr_TestingRSProblem</CommandText>
> >> <DataSourceName>DataSource1</DataSourceName>
> >> </Query>
> >> </DataSet>
> >> </DataSets>
> >> <Width>6.5in</Width>
> >> <InteractiveHeight>11in</InteractiveHeight>
> >> <Language>en-US</Language>
> >> <TopMargin>1in</TopMargin>
> >> </Report>
> >>
>
>|||I don't have northwind, I have AdventureWorks. Here is my stored procedure:
create PROCEDURE [dbo].[Pr_TestingRSProblem]
AS
Select FirstName
into #TEMP
FROM person.contact
SELECT distinct FirstName
FROM #TEMP order by firstname
return
I did not use the wizard. I added a report. I went to the data tab. Added a
new dataset. My shared data source credentials were windows authentication.
When creating the dataset I changed the data type to stored procedure and
put in the name of the stored procedure as the query string (do not put in
exec, just put in the name of the procedure). I get an error and no field
list. I click on refresh the field list I get a field list and now I can
execute the query and get data back in the data tab.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> I meant to mention that I tried the sp with and without explicitly
> dropping
> the table at the end. Neither way worked.
> "Nathan" wrote:
>> I am having an issue when converting some of my reports to SQL reporting
>> services 2005. All of my reports use an OLEDB connection that point to a
>> UDL
>> file so that we can easily change connection strings as needed without
>> having
>> to touch the report server. They all use stored procedures to obtain the
>> data. The issues I am running into is with stored procedures that use
>> temp
>> tables. Anything that is done in a stored procedure after the temp table
>> is
>> created is not shown in the report designer on the data tab within visual
>> studio 2005. I've read a few posts about this problem but it seems like
>> no
>> one at Microsoft has been able to reproduce it. I have been able to
>> reproduce it with a simple example using the NorthWind database. I will
>> include the stored procedure and the rdl file below. I set the report to
>> prompt for credentials instead of using the UDL file but the same problem
>> occurs. It will occur with Windows authentication as well. If I change
>> the
>> connection type to "Microsoft SQL Server" then it will work correctly the
>> problem is that I will not be able to use UDL or DSN files. My method
>> worked
>> just fine with RS 2000. What has changed with RS 2005 to cause this
>> issue?
>> Any ideas on how I might get this to work?
>> Thanks,
>> -Nathan
>> SP Script-
>> use northwind
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER OFF
>> GO
>> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> AS
>> Select FirstName
>> into #EmployeeTemp
>> FROM Employees
>> --*NOTE It doesn't matter what you do here it will not be returned in the
>> Report Designer Data tab.
>> SELECT FirstName
>> FROM #EmployeeTemp
>> --Select 'Test'
>>
>> RDL File-
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="DataSource1">
>> <ConnectionProperties>
>> <Prompt>Specify a user name and password for data source
>> DataSource1</Prompt>
>> <ConnectString>Provider=SQLOLEDB.1;Data
>> Source=saintdevsql1;Initial
>> Catalog=Northwind</ConnectString>
>> <DataProvider>OLEDB</DataProvider>
>> </ConnectionProperties>
>> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <Height>0.75in</Height>
>> </Body>
>> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="Northwind">
>> <Query>
>> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> <CommandType>StoredProcedure</CommandType>
>> <CommandText>Pr_TestingRSProblem</CommandText>
>> <DataSourceName>DataSource1</DataSourceName>
>> </Query>
>> </DataSet>
>> </DataSets>
>> <Width>6.5in</Width>
>> <InteractiveHeight>11in</InteractiveHeight>
>> <Language>en-US</Language>
>> <TopMargin>1in</TopMargin>
>> </Report>|||Whoops, didn't use OLEDB. Yep, I can duplicate. My only suggestion is to not
use OLEDB. I tried ODBC (which is what I use with Sybase) but also had a
proble with that. Against SQL Server I don't use OLEDB or ODBC, just ODBC
against Sybase (for which I don't have this problem). Weird. Sorry, other
than duplicating the problem there isn't much more I can do to help.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:CCF681B4-BB0F-4027-8672-7CDFFE14ECE1@.microsoft.com...
> Bruce,
> The simple answer is there is no data, no field list, and no error. I
> should have mentioned that I have tried refreshing the field list several
> times and no fields are returned. I've tried this on two separate
> machines.
> The field list is empty and when I try to run the sp within the data tab
> it
> returns no results. Were you not able to reproduce the problem with an
> OLEDB
> connection type? What I found strange was that anything in the sp before
> the
> temp table declaration worked fine. It's as if the meta data describing
> the
> table structure isn't returned. Any ideas? Let me know if I can provide
> you
> with any more information. Thanks for the quick reply.
> -Nathan
>
> "Bruce L-C [MVP]" wrote:
>> First, do not explicitly drop the table.
>> Second, exactly what is happening? No data? No field list? Any error?
>> I just had a case where I would get an error about a temp table (I use
>> temp
>> tables all the time, why this particular SP had a problem I don't know).
>> What worked for me was to refresh the fields (button is to the right of
>> the
>> ...). I then had a field list. After that I could execute the query in
>> the
>> data tab and I got data back.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
>> >
>> > I meant to mention that I tried the sp with and without explicitly
>> > dropping
>> > the table at the end. Neither way worked.
>> >
>> > "Nathan" wrote:
>> >
>> >> I am having an issue when converting some of my reports to SQL
>> >> reporting
>> >> services 2005. All of my reports use an OLEDB connection that point
>> >> to a
>> >> UDL
>> >> file so that we can easily change connection strings as needed without
>> >> having
>> >> to touch the report server. They all use stored procedures to obtain
>> >> the
>> >> data. The issues I am running into is with stored procedures that use
>> >> temp
>> >> tables. Anything that is done in a stored procedure after the temp
>> >> table
>> >> is
>> >> created is not shown in the report designer on the data tab within
>> >> visual
>> >> studio 2005. I've read a few posts about this problem but it seems
>> >> like
>> >> no
>> >> one at Microsoft has been able to reproduce it. I have been able to
>> >> reproduce it with a simple example using the NorthWind database. I
>> >> will
>> >> include the stored procedure and the rdl file below. I set the report
>> >> to
>> >> prompt for credentials instead of using the UDL file but the same
>> >> problem
>> >> occurs. It will occur with Windows authentication as well. If I
>> >> change
>> >> the
>> >> connection type to "Microsoft SQL Server" then it will work correctly
>> >> the
>> >> problem is that I will not be able to use UDL or DSN files. My method
>> >> worked
>> >> just fine with RS 2000. What has changed with RS 2005 to cause this
>> >> issue?
>> >> Any ideas on how I might get this to work?
>> >>
>> >> Thanks,
>> >> -Nathan
>> >>
>> >> SP Script-
>> >>
>> >> use northwind
>> >>
>> >> set ANSI_NULLS ON
>> >> set QUOTED_IDENTIFIER OFF
>> >> GO
>> >>
>> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> >> AS
>> >>
>> >> Select FirstName
>> >> into #EmployeeTemp
>> >> FROM Employees
>> >> --*NOTE It doesn't matter what you do here it will not be returned in
>> >> the
>> >> Report Designer Data tab.
>> >> SELECT FirstName
>> >> FROM #EmployeeTemp
>> >> --Select 'Test'
>> >>
>> >>
>> >> RDL File-
>> >>
>> >> <?xml version="1.0" encoding="utf-8"?>
>> >> <Report
>> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> >> <DataSources>
>> >> <DataSource Name="DataSource1">
>> >> <ConnectionProperties>
>> >> <Prompt>Specify a user name and password for data source
>> >> DataSource1</Prompt>
>> >> <ConnectString>Provider=SQLOLEDB.1;Data
>> >> Source=saintdevsql1;Initial
>> >> Catalog=Northwind</ConnectString>
>> >> <DataProvider>OLEDB</DataProvider>
>> >> </ConnectionProperties>
>> >>
>> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> >> </DataSource>
>> >> </DataSources>
>> >> <BottomMargin>1in</BottomMargin>
>> >> <RightMargin>1in</RightMargin>
>> >> <rd:DrawGrid>true</rd:DrawGrid>
>> >> <InteractiveWidth>8.5in</InteractiveWidth>
>> >> <rd:SnapToGrid>true</rd:SnapToGrid>
>> >> <Body>
>> >> <Height>0.75in</Height>
>> >> </Body>
>> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> >> <LeftMargin>1in</LeftMargin>
>> >> <DataSets>
>> >> <DataSet Name="Northwind">
>> >> <Query>
>> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> >> <CommandType>StoredProcedure</CommandType>
>> >> <CommandText>Pr_TestingRSProblem</CommandText>
>> >> <DataSourceName>DataSource1</DataSourceName>
>> >> </Query>
>> >> </DataSet>
>> >> </DataSets>
>> >> <Width>6.5in</Width>
>> >> <InteractiveHeight>11in</InteractiveHeight>
>> >> <Language>en-US</Language>
>> >> <TopMargin>1in</TopMargin>
>> >> </Report>
>> >>
>>|||I tried the example you have given with the adventure works database. I am
able to reproduce what you have with the connection type of â'Microsoft SQL
Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
having. I need to use this because of my requirements of using a dsn or udl
file.
Thanks again,
-Nathan
"Bruce L-C [MVP]" wrote:
> I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> create PROCEDURE [dbo].[Pr_TestingRSProblem]
> AS
> Select FirstName
> into #TEMP
> FROM person.contact
> SELECT distinct FirstName
> FROM #TEMP order by firstname
> return
> I did not use the wizard. I added a report. I went to the data tab. Added a
> new dataset. My shared data source credentials were windows authentication.
> When creating the dataset I changed the data type to stored procedure and
> put in the name of the stored procedure as the query string (do not put in
> exec, just put in the name of the procedure). I get an error and no field
> list. I click on refresh the field list I get a field list and now I can
> execute the query and get data back in the data tab.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> >
> > I meant to mention that I tried the sp with and without explicitly
> > dropping
> > the table at the end. Neither way worked.
> >
> > "Nathan" wrote:
> >
> >> I am having an issue when converting some of my reports to SQL reporting
> >> services 2005. All of my reports use an OLEDB connection that point to a
> >> UDL
> >> file so that we can easily change connection strings as needed without
> >> having
> >> to touch the report server. They all use stored procedures to obtain the
> >> data. The issues I am running into is with stored procedures that use
> >> temp
> >> tables. Anything that is done in a stored procedure after the temp table
> >> is
> >> created is not shown in the report designer on the data tab within visual
> >> studio 2005. I've read a few posts about this problem but it seems like
> >> no
> >> one at Microsoft has been able to reproduce it. I have been able to
> >> reproduce it with a simple example using the NorthWind database. I will
> >> include the stored procedure and the rdl file below. I set the report to
> >> prompt for credentials instead of using the UDL file but the same problem
> >> occurs. It will occur with Windows authentication as well. If I change
> >> the
> >> connection type to "Microsoft SQL Server" then it will work correctly the
> >> problem is that I will not be able to use UDL or DSN files. My method
> >> worked
> >> just fine with RS 2000. What has changed with RS 2005 to cause this
> >> issue?
> >> Any ideas on how I might get this to work?
> >>
> >> Thanks,
> >> -Nathan
> >>
> >> SP Script-
> >>
> >> use northwind
> >>
> >> set ANSI_NULLS ON
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >>
> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> >> AS
> >>
> >> Select FirstName
> >> into #EmployeeTemp
> >> FROM Employees
> >> --*NOTE It doesn't matter what you do here it will not be returned in the
> >> Report Designer Data tab.
> >> SELECT FirstName
> >> FROM #EmployeeTemp
> >> --Select 'Test'
> >>
> >>
> >> RDL File-
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="DataSource1">
> >> <ConnectionProperties>
> >> <Prompt>Specify a user name and password for data source
> >> DataSource1</Prompt>
> >> <ConnectString>Provider=SQLOLEDB.1;Data
> >> Source=saintdevsql1;Initial
> >> Catalog=Northwind</ConnectString>
> >> <DataProvider>OLEDB</DataProvider>
> >> </ConnectionProperties>
> >>
> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <Height>0.75in</Height>
> >> </Body>
> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> >> <LeftMargin>1in</LeftMargin>
> >> <DataSets>
> >> <DataSet Name="Northwind">
> >> <Query>
> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> >> <CommandType>StoredProcedure</CommandType>
> >> <CommandText>Pr_TestingRSProblem</CommandText>
> >> <DataSourceName>DataSource1</DataSourceName>
> >> </Query>
> >> </DataSet>
> >> </DataSets>
> >> <Width>6.5in</Width>
> >> <InteractiveHeight>11in</InteractiveHeight>
> >> <Language>en-US</Language>
> >> <TopMargin>1in</TopMargin>
> >> </Report>
> >>
>
>|||I must not have refreshed before sending my last reply. Looks like you did
try the OLEDB. Well, thanks for trying. At least I know I'm not going crazy
and it is reproducible and may actually be an issue with reporting services.
The funny thing is that if you try making the same report in the previous
version of RS within VS2003 it works fine. That is with it connecting to the
same DB server (SQL 2005). This seems like an unintended â'featureâ' to meâ?¦
Anyone else out there have any ideas?
Thanks,
-Nathan
"Nathan" wrote:
> I tried the example you have given with the adventure works database. I am
> able to reproduce what you have with the connection type of â'Microsoft SQL
> Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
> having. I need to use this because of my requirements of using a dsn or udl
> file.
> Thanks again,
> -Nathan
>
> "Bruce L-C [MVP]" wrote:
> > I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> > create PROCEDURE [dbo].[Pr_TestingRSProblem]
> >
> > AS
> >
> > Select FirstName
> >
> > into #TEMP
> >
> > FROM person.contact
> >
> > SELECT distinct FirstName
> >
> > FROM #TEMP order by firstname
> >
> > return
> >
> > I did not use the wizard. I added a report. I went to the data tab. Added a
> > new dataset. My shared data source credentials were windows authentication.
> > When creating the dataset I changed the data type to stored procedure and
> > put in the name of the stored procedure as the query string (do not put in
> > exec, just put in the name of the procedure). I get an error and no field
> > list. I click on refresh the field list I get a field list and now I can
> > execute the query and get data back in the data tab.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> > >
> > > I meant to mention that I tried the sp with and without explicitly
> > > dropping
> > > the table at the end. Neither way worked.
> > >
> > > "Nathan" wrote:
> > >
> > >> I am having an issue when converting some of my reports to SQL reporting
> > >> services 2005. All of my reports use an OLEDB connection that point to a
> > >> UDL
> > >> file so that we can easily change connection strings as needed without
> > >> having
> > >> to touch the report server. They all use stored procedures to obtain the
> > >> data. The issues I am running into is with stored procedures that use
> > >> temp
> > >> tables. Anything that is done in a stored procedure after the temp table
> > >> is
> > >> created is not shown in the report designer on the data tab within visual
> > >> studio 2005. I've read a few posts about this problem but it seems like
> > >> no
> > >> one at Microsoft has been able to reproduce it. I have been able to
> > >> reproduce it with a simple example using the NorthWind database. I will
> > >> include the stored procedure and the rdl file below. I set the report to
> > >> prompt for credentials instead of using the UDL file but the same problem
> > >> occurs. It will occur with Windows authentication as well. If I change
> > >> the
> > >> connection type to "Microsoft SQL Server" then it will work correctly the
> > >> problem is that I will not be able to use UDL or DSN files. My method
> > >> worked
> > >> just fine with RS 2000. What has changed with RS 2005 to cause this
> > >> issue?
> > >> Any ideas on how I might get this to work?
> > >>
> > >> Thanks,
> > >> -Nathan
> > >>
> > >> SP Script-
> > >>
> > >> use northwind
> > >>
> > >> set ANSI_NULLS ON
> > >> set QUOTED_IDENTIFIER OFF
> > >> GO
> > >>
> > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> > >> AS
> > >>
> > >> Select FirstName
> > >> into #EmployeeTemp
> > >> FROM Employees
> > >> --*NOTE It doesn't matter what you do here it will not be returned in the
> > >> Report Designer Data tab.
> > >> SELECT FirstName
> > >> FROM #EmployeeTemp
> > >> --Select 'Test'
> > >>
> > >>
> > >> RDL File-
> > >>
> > >> <?xml version="1.0" encoding="utf-8"?>
> > >> <Report
> > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > >> <DataSources>
> > >> <DataSource Name="DataSource1">
> > >> <ConnectionProperties>
> > >> <Prompt>Specify a user name and password for data source
> > >> DataSource1</Prompt>
> > >> <ConnectString>Provider=SQLOLEDB.1;Data
> > >> Source=saintdevsql1;Initial
> > >> Catalog=Northwind</ConnectString>
> > >> <DataProvider>OLEDB</DataProvider>
> > >> </ConnectionProperties>
> > >>
> > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> > >> </DataSource>
> > >> </DataSources>
> > >> <BottomMargin>1in</BottomMargin>
> > >> <RightMargin>1in</RightMargin>
> > >> <rd:DrawGrid>true</rd:DrawGrid>
> > >> <InteractiveWidth>8.5in</InteractiveWidth>
> > >> <rd:SnapToGrid>true</rd:SnapToGrid>
> > >> <Body>
> > >> <Height>0.75in</Height>
> > >> </Body>
> > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> > >> <LeftMargin>1in</LeftMargin>
> > >> <DataSets>
> > >> <DataSet Name="Northwind">
> > >> <Query>
> > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > >> <CommandType>StoredProcedure</CommandType>
> > >> <CommandText>Pr_TestingRSProblem</CommandText>
> > >> <DataSourceName>DataSource1</DataSourceName>
> > >> </Query>
> > >> </DataSet>
> > >> </DataSets>
> > >> <Width>6.5in</Width>
> > >> <InteractiveHeight>11in</InteractiveHeight>
> > >> <Language>en-US</Language>
> > >> <TopMargin>1in</TopMargin>
> > >> </Report>
> > >>
> >
> >
> >|||Iâ'm thinking of starting a support ticket with Microsoft about this issue.
Before I do does anyone know of a reason why the behavior of the OLEDB
connection would change between rs2000 and rs2005? Does anyone know of any
hot fixes or SPs that might already fix this issue? Iâ'm at a loss at what to
do at this point. This is kind of a show stopper for us with moving to
rs2005.
Thanks,
-Nathan
"Nathan" wrote:
> I must not have refreshed before sending my last reply. Looks like you did
> try the OLEDB. Well, thanks for trying. At least I know I'm not going crazy
> and it is reproducible and may actually be an issue with reporting services.
> The funny thing is that if you try making the same report in the previous
> version of RS within VS2003 it works fine. That is with it connecting to the
> same DB server (SQL 2005). This seems like an unintended â'featureâ' to meâ?¦
> Anyone else out there have any ideas?
> Thanks,
> -Nathan
>
> "Nathan" wrote:
> > I tried the example you have given with the adventure works database. I am
> > able to reproduce what you have with the connection type of â'Microsoft SQL
> > Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
> > having. I need to use this because of my requirements of using a dsn or udl
> > file.
> >
> > Thanks again,
> > -Nathan
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> > > create PROCEDURE [dbo].[Pr_TestingRSProblem]
> > >
> > > AS
> > >
> > > Select FirstName
> > >
> > > into #TEMP
> > >
> > > FROM person.contact
> > >
> > > SELECT distinct FirstName
> > >
> > > FROM #TEMP order by firstname
> > >
> > > return
> > >
> > > I did not use the wizard. I added a report. I went to the data tab. Added a
> > > new dataset. My shared data source credentials were windows authentication.
> > > When creating the dataset I changed the data type to stored procedure and
> > > put in the name of the stored procedure as the query string (do not put in
> > > exec, just put in the name of the procedure). I get an error and no field
> > > list. I click on refresh the field list I get a field list and now I can
> > > execute the query and get data back in the data tab.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> > > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> > > >
> > > > I meant to mention that I tried the sp with and without explicitly
> > > > dropping
> > > > the table at the end. Neither way worked.
> > > >
> > > > "Nathan" wrote:
> > > >
> > > >> I am having an issue when converting some of my reports to SQL reporting
> > > >> services 2005. All of my reports use an OLEDB connection that point to a
> > > >> UDL
> > > >> file so that we can easily change connection strings as needed without
> > > >> having
> > > >> to touch the report server. They all use stored procedures to obtain the
> > > >> data. The issues I am running into is with stored procedures that use
> > > >> temp
> > > >> tables. Anything that is done in a stored procedure after the temp table
> > > >> is
> > > >> created is not shown in the report designer on the data tab within visual
> > > >> studio 2005. I've read a few posts about this problem but it seems like
> > > >> no
> > > >> one at Microsoft has been able to reproduce it. I have been able to
> > > >> reproduce it with a simple example using the NorthWind database. I will
> > > >> include the stored procedure and the rdl file below. I set the report to
> > > >> prompt for credentials instead of using the UDL file but the same problem
> > > >> occurs. It will occur with Windows authentication as well. If I change
> > > >> the
> > > >> connection type to "Microsoft SQL Server" then it will work correctly the
> > > >> problem is that I will not be able to use UDL or DSN files. My method
> > > >> worked
> > > >> just fine with RS 2000. What has changed with RS 2005 to cause this
> > > >> issue?
> > > >> Any ideas on how I might get this to work?
> > > >>
> > > >> Thanks,
> > > >> -Nathan
> > > >>
> > > >> SP Script-
> > > >>
> > > >> use northwind
> > > >>
> > > >> set ANSI_NULLS ON
> > > >> set QUOTED_IDENTIFIER OFF
> > > >> GO
> > > >>
> > > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> > > >> AS
> > > >>
> > > >> Select FirstName
> > > >> into #EmployeeTemp
> > > >> FROM Employees
> > > >> --*NOTE It doesn't matter what you do here it will not be returned in the
> > > >> Report Designer Data tab.
> > > >> SELECT FirstName
> > > >> FROM #EmployeeTemp
> > > >> --Select 'Test'
> > > >>
> > > >>
> > > >> RDL File-
> > > >>
> > > >> <?xml version="1.0" encoding="utf-8"?>
> > > >> <Report
> > > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> > > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > > >> <DataSources>
> > > >> <DataSource Name="DataSource1">
> > > >> <ConnectionProperties>
> > > >> <Prompt>Specify a user name and password for data source
> > > >> DataSource1</Prompt>
> > > >> <ConnectString>Provider=SQLOLEDB.1;Data
> > > >> Source=saintdevsql1;Initial
> > > >> Catalog=Northwind</ConnectString>
> > > >> <DataProvider>OLEDB</DataProvider>
> > > >> </ConnectionProperties>
> > > >>
> > > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> > > >> </DataSource>
> > > >> </DataSources>
> > > >> <BottomMargin>1in</BottomMargin>
> > > >> <RightMargin>1in</RightMargin>
> > > >> <rd:DrawGrid>true</rd:DrawGrid>
> > > >> <InteractiveWidth>8.5in</InteractiveWidth>
> > > >> <rd:SnapToGrid>true</rd:SnapToGrid>
> > > >> <Body>
> > > >> <Height>0.75in</Height>
> > > >> </Body>
> > > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> > > >> <LeftMargin>1in</LeftMargin>
> > > >> <DataSets>
> > > >> <DataSet Name="Northwind">
> > > >> <Query>
> > > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > >> <CommandType>StoredProcedure</CommandType>
> > > >> <CommandText>Pr_TestingRSProblem</CommandText>
> > > >> <DataSourceName>DataSource1</DataSourceName>
> > > >> </Query>
> > > >> </DataSet>
> > > >> </DataSets>
> > > >> <Width>6.5in</Width>
> > > >> <InteractiveHeight>11in</InteractiveHeight>
> > > >> <Language>en-US</Language>
> > > >> <TopMargin>1in</TopMargin>
> > > >> </Report>
> > > >>
> > >
> > >
> > >|||This is the first I have heard of it on the newsgroups. I am not aware of
any hot fixes (SP1 makes no difference).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:18D44662-1B40-41D9-B4A0-049F8A893B76@.microsoft.com...
> I'm thinking of starting a support ticket with Microsoft about this issue.
> Before I do does anyone know of a reason why the behavior of the OLEDB
> connection would change between rs2000 and rs2005? Does anyone know of
> any
> hot fixes or SPs that might already fix this issue? I'm at a loss at what
> to
> do at this point. This is kind of a show stopper for us with moving to
> rs2005.
> Thanks,
> -Nathan
>
> "Nathan" wrote:
>> I must not have refreshed before sending my last reply. Looks like you
>> did
>> try the OLEDB. Well, thanks for trying. At least I know I'm not going
>> crazy
>> and it is reproducible and may actually be an issue with reporting
>> services.
>> The funny thing is that if you try making the same report in the previous
>> version of RS within VS2003 it works fine. That is with it connecting to
>> the
>> same DB server (SQL 2005). This seems like an unintended "feature" to
>> me.
>> Anyone else out there have any ideas?
>> Thanks,
>> -Nathan
>>
>> "Nathan" wrote:
>> > I tried the example you have given with the adventure works database.
>> > I am
>> > able to reproduce what you have with the connection type of "Microsoft
>> > SQL
>> > Server". However, if you select "OLEDB" you will encounter the problem
>> > I am
>> > having. I need to use this because of my requirements of using a dsn
>> > or udl
>> > file.
>> >
>> > Thanks again,
>> > -Nathan
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > I don't have northwind, I have AdventureWorks. Here is my stored
>> > > procedure:
>> > > create PROCEDURE [dbo].[Pr_TestingRSProblem]
>> > >
>> > > AS
>> > >
>> > > Select FirstName
>> > >
>> > > into #TEMP
>> > >
>> > > FROM person.contact
>> > >
>> > > SELECT distinct FirstName
>> > >
>> > > FROM #TEMP order by firstname
>> > >
>> > > return
>> > >
>> > > I did not use the wizard. I added a report. I went to the data tab.
>> > > Added a
>> > > new dataset. My shared data source credentials were windows
>> > > authentication.
>> > > When creating the dataset I changed the data type to stored procedure
>> > > and
>> > > put in the name of the stored procedure as the query string (do not
>> > > put in
>> > > exec, just put in the name of the procedure). I get an error and no
>> > > field
>> > > list. I click on refresh the field list I get a field list and now I
>> > > can
>> > > execute the query and get data back in the data tab.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > >
>> > > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> > > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
>> > > >
>> > > > I meant to mention that I tried the sp with and without explicitly
>> > > > dropping
>> > > > the table at the end. Neither way worked.
>> > > >
>> > > > "Nathan" wrote:
>> > > >
>> > > >> I am having an issue when converting some of my reports to SQL
>> > > >> reporting
>> > > >> services 2005. All of my reports use an OLEDB connection that
>> > > >> point to a
>> > > >> UDL
>> > > >> file so that we can easily change connection strings as needed
>> > > >> without
>> > > >> having
>> > > >> to touch the report server. They all use stored procedures to
>> > > >> obtain the
>> > > >> data. The issues I am running into is with stored procedures that
>> > > >> use
>> > > >> temp
>> > > >> tables. Anything that is done in a stored procedure after the
>> > > >> temp table
>> > > >> is
>> > > >> created is not shown in the report designer on the data tab within
>> > > >> visual
>> > > >> studio 2005. I've read a few posts about this problem but it
>> > > >> seems like
>> > > >> no
>> > > >> one at Microsoft has been able to reproduce it. I have been able
>> > > >> to
>> > > >> reproduce it with a simple example using the NorthWind database.
>> > > >> I will
>> > > >> include the stored procedure and the rdl file below. I set the
>> > > >> report to
>> > > >> prompt for credentials instead of using the UDL file but the same
>> > > >> problem
>> > > >> occurs. It will occur with Windows authentication as well. If I
>> > > >> change
>> > > >> the
>> > > >> connection type to "Microsoft SQL Server" then it will work
>> > > >> correctly the
>> > > >> problem is that I will not be able to use UDL or DSN files. My
>> > > >> method
>> > > >> worked
>> > > >> just fine with RS 2000. What has changed with RS 2005 to cause
>> > > >> this
>> > > >> issue?
>> > > >> Any ideas on how I might get this to work?
>> > > >>
>> > > >> Thanks,
>> > > >> -Nathan
>> > > >>
>> > > >> SP Script-
>> > > >>
>> > > >> use northwind
>> > > >>
>> > > >> set ANSI_NULLS ON
>> > > >> set QUOTED_IDENTIFIER OFF
>> > > >> GO
>> > > >>
>> > > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> > > >> AS
>> > > >>
>> > > >> Select FirstName
>> > > >> into #EmployeeTemp
>> > > >> FROM Employees
>> > > >> --*NOTE It doesn't matter what you do here it will not be returned
>> > > >> in the
>> > > >> Report Designer Data tab.
>> > > >> SELECT FirstName
>> > > >> FROM #EmployeeTemp
>> > > >> --Select 'Test'
>> > > >>
>> > > >>
>> > > >> RDL File-
>> > > >>
>> > > >> <?xml version="1.0" encoding="utf-8"?>
>> > > >> <Report
>> > > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> > > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> > > >> <DataSources>
>> > > >> <DataSource Name="DataSource1">
>> > > >> <ConnectionProperties>
>> > > >> <Prompt>Specify a user name and password for data source
>> > > >> DataSource1</Prompt>
>> > > >> <ConnectString>Provider=SQLOLEDB.1;Data
>> > > >> Source=saintdevsql1;Initial
>> > > >> Catalog=Northwind</ConnectString>
>> > > >> <DataProvider>OLEDB</DataProvider>
>> > > >> </ConnectionProperties>
>> > > >>
>> > > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> > > >> </DataSource>
>> > > >> </DataSources>
>> > > >> <BottomMargin>1in</BottomMargin>
>> > > >> <RightMargin>1in</RightMargin>
>> > > >> <rd:DrawGrid>true</rd:DrawGrid>
>> > > >> <InteractiveWidth>8.5in</InteractiveWidth>
>> > > >> <rd:SnapToGrid>true</rd:SnapToGrid>
>> > > >> <Body>
>> > > >> <Height>0.75in</Height>
>> > > >> </Body>
>> > > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> > > >> <LeftMargin>1in</LeftMargin>
>> > > >> <DataSets>
>> > > >> <DataSet Name="Northwind">
>> > > >> <Query>
>> > > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> > > >> <CommandType>StoredProcedure</CommandType>
>> > > >> <CommandText>Pr_TestingRSProblem</CommandText>
>> > > >> <DataSourceName>DataSource1</DataSourceName>
>> > > >> </Query>
>> > > >> </DataSet>
>> > > >> </DataSets>
>> > > >> <Width>6.5in</Width>
>> > > >> <InteractiveHeight>11in</InteractiveHeight>
>> > > >> <Language>en-US</Language>
>> > > >> <TopMargin>1in</TopMargin>
>> > > >> </Report>
>> > > >>
>> > >
>> > >
>> > >

Issues with 1.1 framework calling SQL 2005 CLR sprocs?

Are there any issues calling SQL 2005 CLR bases stored procedures and functions from a web application which uses the dotnet 1.1 framework?

I assume not, but would like advice from those who've been there...

Thanks,

Marie

Hi,

no there are nor problems. Even calling SP from other program with the MDAC stack does not bring any problems. You will not be able to use certain features as they are only supportedby the ADO.NET 2.0 framework (like query notification) but there any no problems with the "old" functionality.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks Jens!

Issues with 1.1 framework calling SQL 2005 CLR sprocs?

Are there any issues calling SQL 2005 CLR bases stored procedures and functions from a web application which uses the dotnet 1.1 framework?

I assume not, but would like advice from those who've been there...

Thanks,

Marie

Hi,

no there are nor problems. Even calling SP from other program with the MDAC stack does not bring any problems. You will not be able to use certain features as they are only supportedby the ADO.NET 2.0 framework (like query notification) but there any no problems with the "old" functionality.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Thanks Jens!

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615
> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_columns_odb c_view'));
select * from sys.sql_modules where object_id =
object_id('spt_sproc_columns_odbc_view');
select * from sys.system_sql_modules where object_name(object_id) =
'spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_name')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron
|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>
|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
>
|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO

Issue with incorrect metadata ?

We're running SQL2k5 and I've got some stored procedures which all have the
last parameter as optional with a default value of zero i.e.
create procedure myproc
@.Parm1 int,
@.Parm2 int=0
when I query the system catalogs on this proc the rows returned do not
indicate the parameter as having a default value....I was planning to use
this information but cannot seem to figure out why this is wrong. The
sys.parameters column "has_default_value" is zero for every parameter in all
of our databases....in sys.syscolumns the cdefault is zero as well.
Is there somewhere else to find this data and be able to depend on it? I'm
really stuck here the whole team is waiting on me and I'm supposed to be
providing a home grown solution for automated building of .NET SqlCommand
objects based on this information.
select * from sys.parameters where object_id=2056602615
select * from sys.syscolumns where id=2056602615> when I query the system catalogs on this proc the rows returned do not
> indicate the parameter as having a default value....I was planning to use
> this information but cannot seem to figure out why this is wrong. The
> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
This is true, the information is not stored there (nor in
sys.all_parameters).
I ran a profiler trace and monitored expanding the parameters node under a
stored procedure in Management Studio (which shows "default" / "no default"
but not the actual value). Ignoring names/ids that are specific to my
environment, I saw this (my most relevant observation highlighted on line
13):
SELECT 'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(db_name(),'''')
+ ']' + '/StoredProcedure[@.Name=' + quotename(sp.name,'''')
+ ' and @.Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''')
+ ']' + '/Param[@.Name=' + quotename(param.name,'''') + ']' AS [Urn],
param.name AS [Name],
ISNULL(baset.name, N'') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar')
AND param.max_length <> -1 THEN param.max_length/2 ELSE
param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale],
null AS [DefaultValue], -- *********** NOTICE THIS ************
param.is_output AS [IsOutputParameter],
sp.object_id AS [IDText],
db_name() AS [DatabaseName],
param.name AS [ParamName],
CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [ParentSysObj],
1 AS [Number]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param
ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS baset
ON baset.user_type_id = param.system_type_id
and baset.user_type_id = baset.system_type_id
WHERE
(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')
and(sp.name=N'fakeProcedure'
and SCHEMA_NAME(sp.schema_id)=N'dbo')
ORDER BY
param.parameter_id ASC
Nothing more promising showed up in the trace when scripting the object as
create to new window, or using the modify context menu option. Both seem to
just grab the code from sys.sql_modules and, in the case of modify, change
CREATE to ALTER -- without even bothering with the parameter list at all.
I looked at sp_sproc_columns, which I have spotted in profiler from time to
time, coming from an application that uses ODBC to call stored procedures.
But this procedure does not yield any information about default values. It
gets column_def from spt_sproc_columns_odbc_view (which I can't figure out
how to query directly) but it looks to be always null. I also tried to find
the source for spt_sproc_columns_odbc_view but it seems this may be locked
away in mssqlsystemresource db. The following yielded nothing:
use master;
go
select * from sys.all_objects where name = 'spt_sproc_columns_odbc_view';
select object_definition(object_id('spt_sproc_columns_odbc_view'));
select * from sys.sql_modules where object_id =object_id('spt_sproc_columns_odbc_view');
select * from sys.system_sql_modules where object_name(object_id) ='spt_sproc_columns_odbc_view';
Frankly, I think that SQL Server only stores this value in the text in
syscomments / sys.sql_modules. And when the node I mentioned above expands
it must parse the stored procedure text to see whether the parameter
declarations have = signs next to them or not. I couldn't find any other
way to get this information, and I remember it coming up during the beta and
I'm pretty sure it was closed as "won't fix." So unfortunately I think you
are stuck in the same boat; parsing
object_definition(object_id('procedure_name')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html
And this BOL article for SQL Server 2005,
http://msdn2.microsoft.com/en-us/library/ms190340.aspx
Which says:
"SQL Server only maintains default values for CLR objects in this catalog
view; therefore, this column has a value of 0 for Transact-SQL objects. To
view the default value of a parameter in a Transact-SQL object, query the
definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
I have submitted a request for more clarification, and will follow up if I
get any useful information.
Cheers,
Aaron|||> sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
I have submitted a suggestion to Microsoft regarding this issue through
"official" channels.
If you have a passport / Windows Live ID, you can see my feedback here, and
vote if you feel strongly enough about it:
http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143|||Books Online is pretty clear on this. Here's a quote from sys.parameters,
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm :
"SQL Server only maintains default values for CLR objects in this catalog view; therefore, this
column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a
Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the
OBJECT_DEFINITION system function."
It has always been the case that we cannot get the default values of parameters in SQL Server. Seems
we now can get it for CLR procedures, but still not for TSQL objects. So same applies as for earlier
versions: parse the source code. You might want to post an enhancement request at:
http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We're running SQL2k5 and I've got some stored procedures which all have the last parameter as
> optional with a default value of zero i.e.
> create procedure myproc
> @.Parm1 int,
> @.Parm2 int=0
> when I query the system catalogs on this proc the rows returned do not indicate the parameter as
> having a default value....I was planning to use this information but cannot seem to figure out
> why this is wrong. The sys.parameters column "has_default_value" is zero for every parameter in
> all of our databases....in sys.syscolumns the cdefault is zero as well.
> Is there somewhere else to find this data and be able to depend on it? I'm really stuck here the
> whole team is waiting on me and I'm supposed to be providing a home grown solution for automated
> building of .NET SqlCommand objects based on this information.
> select * from sys.parameters where object_id=2056602615
> select * from sys.syscolumns where id=2056602615
>|||Thanks for that reference...gives me alot to go on...
I wasn't trying to get the default value for a parameter...just the
knowledge that a parameter has a default value and can be considered
optional for input....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Op7OOSY$GHA.1220@.TK2MSFTNGP04.phx.gbl...
> Books Online is pretty clear on this. Here's a quote from sys.parameters,
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/24e2764b-c8e5-4322-97a4-7407d8b8a92b.htm
> :
> "SQL Server only maintains default values for CLR objects in this catalog
> view; therefore, this column has a value of 0 for Transact-SQL objects. To
> view the default value of a parameter in a Transact-SQL object, query the
> definition column of the sys.sql_modules catalog view, or use the
> OBJECT_DEFINITION system function."
> It has always been the case that we cannot get the default values of
> parameters in SQL Server. Seems we now can get it for CLR procedures, but
> still not for TSQL objects. So same applies as for earlier versions: parse
> the source code. You might want to post an enhancement request at:
> http://connect.microsoft.com/site/sitehome.aspx?SiteID=68
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:epO1UuT$GHA.4704@.TK2MSFTNGP04.phx.gbl...
>> We're running SQL2k5 and I've got some stored procedures which all have
>> the last parameter as optional with a default value of zero i.e.
>> create procedure myproc
>> @.Parm1 int,
>> @.Parm2 int=0
>> when I query the system catalogs on this proc the rows returned do not
>> indicate the parameter as having a default value....I was planning to
>> use this information but cannot seem to figure out why this is wrong.
>> The sys.parameters column "has_default_value" is zero for every parameter
>> in all of our databases....in sys.syscolumns the cdefault is zero as
>> well.
>> Is there somewhere else to find this data and be able to depend on it?
>> I'm really stuck here the whole team is waiting on me and I'm supposed to
>> be providing a home grown solution for automated building of .NET
>> SqlCommand objects based on this information.
>> select * from sys.parameters where object_id=2056602615
>> select * from sys.syscolumns where id=2056602615
>>
>|||Voted!!!
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23AyWj3X$GHA.2328@.TK2MSFTNGP02.phx.gbl...
>> sys.parameters column "has_default_value" is zero for every parameter in
>> all of our databases....in sys.syscolumns the cdefault is zero as well.
> I have submitted a suggestion to Microsoft regarding this issue through
> "official" channels.
> If you have a passport / Windows Live ID, you can see my feedback here,
> and vote if you feel strongly enough about it:
> http://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=234143
>|||Here is a workaround for the time being (also posting it to the issue on
Connect).
I am also working on a version that retrieves the explicit default value,
but that is proving more cumbersome if the default value is a string and
contains a comma (but I am close).
ALTER PROCEDURE dbo.sys_GetParameters
@.object_name NVARCHAR(511)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.object_id INT,
@.paramID INT,
@.paramName SYSNAME,
@.definition NVARCHAR(MAX),
@.t NVARCHAR(MAX),
@.loc1 INT,
@.loc2 INT,
@.loc3 INT,
@.loc4 INT,
@.has_default_value BIT;
SET @.object_id = OBJECT_ID(@.object_name);
IF (@.object_id IS NOT NULL)
BEGIN
SELECT @.definition = OBJECT_DEFINITION(@.object_id);
CREATE TABLE #params
(
parameter_id INT PRIMARY KEY,
has_default_value BIT NOT NULL DEFAULT (0)
);
DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT
parameter_id,
[name]
FROM
sys.parameters
WHERE
[object_id] = @.object_id;
OPEN c;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT
@.t = SUBSTRING
(
@.definition,
CHARINDEX(@.paramName, @.definition),
4000
),
@.has_default_value = 0;
SET @.loc1 = COALESCE(NULLIF(CHARINDEX('''', @.t), 0), 4000);
SET @.loc2 = COALESCE(NULLIF(CHARINDEX(',', @.t), 0), 4000);
SET @.loc3 = NULLIF(CHARINDEX('OUTPUT', @.t), 0);
SET @.loc4 = NULLIF(CHARINDEX('AS', @.t), 0);
SET @.loc1 = CASE WHEN @.loc2 < @.loc1 THEN @.loc2 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc3 < @.loc1 THEN @.loc3 ELSE @.loc1 END;
SET @.loc1 = CASE WHEN @.loc4 < @.loc1 THEN @.loc4 ELSE @.loc1 END;
IF CHARINDEX('=', LTRIM(RTRIM(SUBSTRING(@.t, 1, @.loc1)))) > 0
SET @.has_default_value = 1;
INSERT #params
(
parameter_id,
has_default_value
)
SELECT
@.paramID,
@.has_default_value;
FETCH NEXT FROM c INTO @.paramID, @.paramName;
END
SELECT
sp.[object_id],
[object_name] = @.object_name,
param_name = sp.[name],
sp.parameter_id,
type_name = UPPER(st.[name]),
sp.max_length,
sp.[precision],
sp.scale,
sp.is_output,
p.has_default_value
FROM
sys.parameters sp
INNER JOIN
#params p
ON
sp.parameter_id = p.parameter_id
INNER JOIN
sys.types st
ON
sp.user_type_id = st.user_type_id
WHERE
sp.[object_id] = @.object_id;
CLOSE c;
DEALLOCATE c;
DROP TABLE #params;
END
END
GO