After creating several full-text indexes from tables to a Full-text
Catalog, I can see the names of the tables for those indexes. However,
when double-clicking the Full-text Catalog, the the item count was still
zero.
I am sure the Microsoft Search Service started and there was a Full-Text
Search in EM's Support Services folder. My SQL Server databses is the
back-end database for a front-end SharePoint Services server.
Is something wrong with the configuration on my SQL Server databases, or
the SharePoint Services server?
TIA,
Jeffrey
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Jeffrey,
Most likely as this is a FAQ in this newsgroup... Review all of the
following KB articles:
317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs
http://support.microsoft.com/default...b;en-us;317746
277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify
MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component
Services]
http://support.microsoft.com/default...B;EN-US;277549
837367 How to turn on full-text search (FTS) in WSS (SharePoint) on a
Windows SBS 2003-based computer
http://support.microsoft.com/?kbid=837367
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeffrey Wang" <cjeffwang@.gmail.com> wrote in message
news:#ABhKdSIFHA.1528@.TK2MSFTNGP09.phx.gbl...
> After creating several full-text indexes from tables to a Full-text
> Catalog, I can see the names of the tables for those indexes. However,
> when double-clicking the Full-text Catalog, the the item count was still
> zero.
> I am sure the Microsoft Search Service started and there was a Full-Text
> Search in EM's Support Services folder. My SQL Server databses is the
> back-end database for a front-end SharePoint Services server.
> Is something wrong with the configuration on my SQL Server databases, or
> the SharePoint Services server?
> TIA,
> Jeffrey
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Showing posts with label zero. Show all posts
Showing posts with label zero. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Issue with SqlUserDefinedAggregate
I am using the code below but I am getting a "zero" result for
dbo.AggredIssue('Test') user defined aggregate everytime that the query
executes parallel processing and uses the "Merge" method. It seems that my
private variable "private List<string> myList" gets nullified everytime it
goes through the "Merge".
I saw other people reporting the same issue in other forums, but nobody was
able to provide a solution or explanation.
See below a simplified version of my code (posted just after the queries)
that replicates the issue.
The query below works because it does't process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund = 2
group by GroupID
The query below doesn't work because it process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund <= 20
group by GroupID
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
MaxByteSize = 1000)]
public class AggregIssue : IBinarySerialize {
private List<string> myList;
private int myResult;
public void Init() {
myList = new List<string>();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) { return; }
myList.Add(Value.ToString());
}
public void Merge(AggregIssue Other) {
if (Other.myList != null) {
if (myList == null) {
myList = Other.myList;
}
else {
myList.AddRange(Other.myList);
}
}
}
public SqlInt32 Terminate() {
return new SqlInt32(myResult);
}
public void Read(BinaryReader r) {
myResult = r.ReadInt32();
}
//The code below is simplified for posting in the forum.
//I do additional manipulation of the list and require
//the aggregation to be IBinarySerialize.
//But this code replicates the issue also
public void Write(BinaryWriter w) {
w.Write(myList.Count);
}
}"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:60C66368-EFD5-4C6B-A9EF-FC363A91C1DB@.microsoft.com...
>I am using the code below but I am getting a "zero" result for
> dbo.AggredIssue('Test') user defined aggregate everytime that the query
> executes parallel processing and uses the "Merge" method. It seems that my
> private variable "private List<string> myList" gets nullified everytime it
> goes through the "Merge".
> I saw other people reporting the same issue in other forums, but nobody
> was
> able to provide a solution or explanation.
> See below a simplified version of my code (posted just after the queries)
> that replicates the issue.
> The query below works because it does't process the query in parallel.
> SELECT GroupID, dbo.AggregIssue('Test')
> FROM MyTable
> where fund = 2
> group by GroupID
>
> The query below doesn't work because it process the query in parallel.
>
Yikes! How on earth do you test the merge method of a CLR Aggregate?
Perhaps you could cook up an appropriate plan guide?
David|||Hello Fernando,
F> I am using the code below but I am getting a "zero" result for
F> dbo.AggredIssue('Test') user defined aggregate everytime that the
F> query executes parallel processing and uses the "Merge" method. It
F> seems that my private variable "private List<string> myList" gets
F> nullified everytime it goes through the "Merge".
I don't believe BinaryRead and BinaryWrite method isn't preserving your List
<T>
as you expect it does. Here's example that serializes such a between calls
to merge.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSVBuilder : IBinarySerialize, INullable
{
List<string> _list = null;
public void Init()
{
_list = new List<string>();
}
public void Accumulate(SqlString Value)
{
_list.Add(Value.Value);
}
public void Merge(CSVBuilder Group)
{
_list.AddRange(Group._list);
}
public SqlString Terminate()
{
StringBuilder sb = new StringBuilder(8000);
foreach (string item in _list) {
sb.Append(", ");
sb.Append(item);
}
return new SqlString(sb.ToString().Substring(2));
}
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
int size = r.ReadInt32();
BinaryFormatter f = new BinaryFormatter();
_list = (List<string> )(f.Deserialize(new MemoryStream(r.ReadBytes(size))));
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
BinaryFormatter f = new BinaryFormatter();
MemoryStream ms = new MemoryStream();
f.Serialize(ms,_list);
Int32 size = (Int32)ms.Length;
w.Write(size);
w.Write(ms.ToArray(), 0, (int)size);
}
bool INullable.IsNull
{
get { return _list == null; }
}
}
It seems to work with this.
USE scratch
go
create table dbo.vs(v varchar(50));
insert into dbo.vs values ('Alpha')
insert into dbo.vs values ('Bravo')
insert into dbo.vs values ('Charlie')
insert into dbo.vs values ('Delta')
insert into dbo.vs values ('Echo')
insert into dbo.vs values ('Foxtrot')
insert into dbo.vs values ('Golf')
insert into dbo.vs values ('Hotel')
insert into dbo.vs values ('India')
insert into dbo.vs values ('Juliet')
insert into dbo.vs values ('Kilo')
insert into dbo.vs values ('Lima')
insert into dbo.vs values ('Mike')
insert into dbo.vs values ('November')
insert into dbo.vs values ('Oscar')
insert into dbo.vs values ('Papa')
insert into dbo.vs values ('Quebec')
insert into dbo.vs values ('Romeo')
insert into dbo.vs values ('Sierra')
insert into dbo.vs values ('Tango')
insert into dbo.vs values ('Uniform')
insert into dbo.vs values ('Victor')
insert into dbo.vs values ('Whiskey')
insert into dbo.vs values ('Yankee')
insert into dbo.vs values ('Zulu')
go
select dbo.csvbuilder(v) from dbo.vs
go
drop table dbo.vs
go
Returns:
Alpha, Bravo, Charlie, Delta, Echo, Foxtrot, Golf, Hotel, India, Juliet,
Kilo, Lima, Mike, November, Oscar, Papa, Quebec, Romeo, Sierra, Tango, Unifo
rm,
Victor, Whiskey, Yankee, Zulu
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thank you very much for your response. The reason I don't serialize the list
itself is because the list exceeds the limit of 8000 for maxbytesize.
I need the whole list to process the resolution of a non-linear equation, so
I cannot pre-aggregate values to save space before serializing. So what I am
trying to do is to solve the equation (using the required data from the list
)
just before serialization.
The weird thing is that it works if the code doesn't go through the merge
method.
If you can think of some other alternative...
Thank you very much,
Fernando|||"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
> Hi Kent,
> Thank you very much for your response. The reason I don't serialize the
> list
> itself is because the list exceeds the limit of 8000 for maxbytesize.
> I need the whole list to process the resolution of a non-linear equation,
> so
> I cannot pre-aggregate values to save space before serializing. So what I
> am
> trying to do is to solve the equation (using the required data from the
> list)
> just before serialization.
> The weird thing is that it works if the code doesn't go through the merge
> method.
> If you can think of some other alternative...
>
As a workaround you can always prevent a parallel plan with a MAXDOP hint.
David|||David,
Thanks for your response. The option "MAXDOP 1" worked. It would be nicer if
parallelism is enabled, but for now it a good workaround.
Thanks again,
Fernando
"David Browne" wrote:
> "Fernando" <Fernando@.discussions.microsoft.com> wrote in message
> news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
>
> As a workaround you can always prevent a parallel plan with a MAXDOP hint.
> David
>
>|||Hello Fernando,
F> The weird thing is that it works if the code doesn't go through the
F> merge method.
Right, I suspect the that the merge method if actually building instances
of the UDA on different CPUs and when has to marshal them together (merging
the threads if you will), that's when it calls the serialization stuff and
that's when you lose your values.
F> If you can think of some other alternative...
Don't use a UDA, use a procedure or function if possible.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent!
> Hello Fernando,
> F> The weird thing is that it works if the code doesn't go through the
> F> merge method.
> Right, I suspect the that the merge method if actually building instances
> of the UDA on different CPUs and when has to marshal them together (mergin
g
> the threads if you will), that's when it calls the serialization stuff and
> that's when you lose your values.
You are probably right, and that would be the reason why my code is failing.
> F> If you can think of some other alternative...
> Don't use a UDA, use a procedure or function if possible.
The solution is much simpler with UDA as the List used in the aggregation is
dynamically populated based on the items in the "Group By" of the query.
The workaround provided by David Browne (force non-parallelism) will work
for now.
Thank you very much for your help!
Fernando
>
dbo.AggredIssue('Test') user defined aggregate everytime that the query
executes parallel processing and uses the "Merge" method. It seems that my
private variable "private List<string> myList" gets nullified everytime it
goes through the "Merge".
I saw other people reporting the same issue in other forums, but nobody was
able to provide a solution or explanation.
See below a simplified version of my code (posted just after the queries)
that replicates the issue.
The query below works because it does't process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund = 2
group by GroupID
The query below doesn't work because it process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund <= 20
group by GroupID
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
MaxByteSize = 1000)]
public class AggregIssue : IBinarySerialize {
private List<string> myList;
private int myResult;
public void Init() {
myList = new List<string>();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) { return; }
myList.Add(Value.ToString());
}
public void Merge(AggregIssue Other) {
if (Other.myList != null) {
if (myList == null) {
myList = Other.myList;
}
else {
myList.AddRange(Other.myList);
}
}
}
public SqlInt32 Terminate() {
return new SqlInt32(myResult);
}
public void Read(BinaryReader r) {
myResult = r.ReadInt32();
}
//The code below is simplified for posting in the forum.
//I do additional manipulation of the list and require
//the aggregation to be IBinarySerialize.
//But this code replicates the issue also
public void Write(BinaryWriter w) {
w.Write(myList.Count);
}
}"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:60C66368-EFD5-4C6B-A9EF-FC363A91C1DB@.microsoft.com...
>I am using the code below but I am getting a "zero" result for
> dbo.AggredIssue('Test') user defined aggregate everytime that the query
> executes parallel processing and uses the "Merge" method. It seems that my
> private variable "private List<string> myList" gets nullified everytime it
> goes through the "Merge".
> I saw other people reporting the same issue in other forums, but nobody
> was
> able to provide a solution or explanation.
> See below a simplified version of my code (posted just after the queries)
> that replicates the issue.
> The query below works because it does't process the query in parallel.
> SELECT GroupID, dbo.AggregIssue('Test')
> FROM MyTable
> where fund = 2
> group by GroupID
>
> The query below doesn't work because it process the query in parallel.
>
Yikes! How on earth do you test the merge method of a CLR Aggregate?
Perhaps you could cook up an appropriate plan guide?
David|||Hello Fernando,
F> I am using the code below but I am getting a "zero" result for
F> dbo.AggredIssue('Test') user defined aggregate everytime that the
F> query executes parallel processing and uses the "Merge" method. It
F> seems that my private variable "private List<string> myList" gets
F> nullified everytime it goes through the "Merge".
I don't believe BinaryRead and BinaryWrite method isn't preserving your List
<T>
as you expect it does. Here's example that serializes such a between calls
to merge.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSVBuilder : IBinarySerialize, INullable
{
List<string> _list = null;
public void Init()
{
_list = new List<string>();
}
public void Accumulate(SqlString Value)
{
_list.Add(Value.Value);
}
public void Merge(CSVBuilder Group)
{
_list.AddRange(Group._list);
}
public SqlString Terminate()
{
StringBuilder sb = new StringBuilder(8000);
foreach (string item in _list) {
sb.Append(", ");
sb.Append(item);
}
return new SqlString(sb.ToString().Substring(2));
}
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
int size = r.ReadInt32();
BinaryFormatter f = new BinaryFormatter();
_list = (List<string> )(f.Deserialize(new MemoryStream(r.ReadBytes(size))));
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
BinaryFormatter f = new BinaryFormatter();
MemoryStream ms = new MemoryStream();
f.Serialize(ms,_list);
Int32 size = (Int32)ms.Length;
w.Write(size);
w.Write(ms.ToArray(), 0, (int)size);
}
bool INullable.IsNull
{
get { return _list == null; }
}
}
It seems to work with this.
USE scratch
go
create table dbo.vs(v varchar(50));
insert into dbo.vs values ('Alpha')
insert into dbo.vs values ('Bravo')
insert into dbo.vs values ('Charlie')
insert into dbo.vs values ('Delta')
insert into dbo.vs values ('Echo')
insert into dbo.vs values ('Foxtrot')
insert into dbo.vs values ('Golf')
insert into dbo.vs values ('Hotel')
insert into dbo.vs values ('India')
insert into dbo.vs values ('Juliet')
insert into dbo.vs values ('Kilo')
insert into dbo.vs values ('Lima')
insert into dbo.vs values ('Mike')
insert into dbo.vs values ('November')
insert into dbo.vs values ('Oscar')
insert into dbo.vs values ('Papa')
insert into dbo.vs values ('Quebec')
insert into dbo.vs values ('Romeo')
insert into dbo.vs values ('Sierra')
insert into dbo.vs values ('Tango')
insert into dbo.vs values ('Uniform')
insert into dbo.vs values ('Victor')
insert into dbo.vs values ('Whiskey')
insert into dbo.vs values ('Yankee')
insert into dbo.vs values ('Zulu')
go
select dbo.csvbuilder(v) from dbo.vs
go
drop table dbo.vs
go
Returns:
Alpha, Bravo, Charlie, Delta, Echo, Foxtrot, Golf, Hotel, India, Juliet,
Kilo, Lima, Mike, November, Oscar, Papa, Quebec, Romeo, Sierra, Tango, Unifo
rm,
Victor, Whiskey, Yankee, Zulu
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thank you very much for your response. The reason I don't serialize the list
itself is because the list exceeds the limit of 8000 for maxbytesize.
I need the whole list to process the resolution of a non-linear equation, so
I cannot pre-aggregate values to save space before serializing. So what I am
trying to do is to solve the equation (using the required data from the list
)
just before serialization.
The weird thing is that it works if the code doesn't go through the merge
method.
If you can think of some other alternative...
Thank you very much,
Fernando|||"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
> Hi Kent,
> Thank you very much for your response. The reason I don't serialize the
> list
> itself is because the list exceeds the limit of 8000 for maxbytesize.
> I need the whole list to process the resolution of a non-linear equation,
> so
> I cannot pre-aggregate values to save space before serializing. So what I
> am
> trying to do is to solve the equation (using the required data from the
> list)
> just before serialization.
> The weird thing is that it works if the code doesn't go through the merge
> method.
> If you can think of some other alternative...
>
As a workaround you can always prevent a parallel plan with a MAXDOP hint.
David|||David,
Thanks for your response. The option "MAXDOP 1" worked. It would be nicer if
parallelism is enabled, but for now it a good workaround.
Thanks again,
Fernando
"David Browne" wrote:
> "Fernando" <Fernando@.discussions.microsoft.com> wrote in message
> news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
>
> As a workaround you can always prevent a parallel plan with a MAXDOP hint.
> David
>
>|||Hello Fernando,
F> The weird thing is that it works if the code doesn't go through the
F> merge method.
Right, I suspect the that the merge method if actually building instances
of the UDA on different CPUs and when has to marshal them together (merging
the threads if you will), that's when it calls the serialization stuff and
that's when you lose your values.
F> If you can think of some other alternative...
Don't use a UDA, use a procedure or function if possible.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent!
> Hello Fernando,
> F> The weird thing is that it works if the code doesn't go through the
> F> merge method.
> Right, I suspect the that the merge method if actually building instances
> of the UDA on different CPUs and when has to marshal them together (mergin
g
> the threads if you will), that's when it calls the serialization stuff and
> that's when you lose your values.
You are probably right, and that would be the reason why my code is failing.
> F> If you can think of some other alternative...
> Don't use a UDA, use a procedure or function if possible.
The solution is much simpler with UDA as the List used in the aggregation is
dynamically populated based on the items in the "Group By" of the query.
The workaround provided by David Browne (force non-parallelism) will work
for now.
Thank you very much for your help!
Fernando
>
Issue with SqlUserDefinedAggregate
I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge".
I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation.
See below a simplified version of my code (posted just after the queries) that replicates the issue.
The query below works because it does't process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund = 2
group by GroupID
The query below doesn't work because it process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund <= 20
group by GroupID
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
MaxByteSize = 1000)]
public class AggregIssue : IBinarySerialize {
private List<string> myList;
private int myResult;
public void Init() {
myList = new List<string>();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) { return; }
myList.Add(Value.ToString());
}
public void Merge(AggregIssue Other) {
if (Other.myList != null) {
if (myList == null) {
myList = Other.myList;
}
else {
myList.AddRange(Other.myList);
}
}
}
public SqlInt32 Terminate() {
return new SqlInt32(myResult);
}
public void Read(BinaryReader r) {
myResult = r.ReadInt32();
}
//The code below is simplified for posting in the forum.
//I do additional manipulation of the list and require
//the aggregation to be IBinarySerialize.
//But this code replicates the issue also
w.Write(myList.Count);
}
}
? If you want to maintain the list properly you should serialize/deserialize the list itself -- and not its count -- in the Read and Write methods. They may be called more than one during the course of aggregation. Your current code is probably failing because it's making assumptions about how and when these will be called. You should instead do something like: public void Write(BinaryWriter w) { w.Write(myList.Count); foreach (string theString in myList) w.Write(theString); } public void Read(BinaryReader r) { this.myList = new List<string>(); int numStrings = r.ReadInt32(); for (int i = 0; i<numStrings; i++) myList.Add(r.ReadString()); } ... Then, in your Terminate method: public SqlInt32 Terminate() { return new SqlInt32(myList.Count); } -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <FernandoT@.discussions..microsoft.com> wrote in message news:e4178255-6dd9-4b48-b5ae-eb072c88e314_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge". I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation. See below a simplified version of my code (posted just after the queries) that replicates the issue. The query below works because it does't process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund = 2 group by GroupID The query below doesn't work because it process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund <= 20 group by GroupID [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = true, MaxByteSize = 1000)] public class AggregIssue : IBinarySerialize { private List<string> myList; private int myResult; public void Init() { myList = new List<string>(); } public void Accumulate(SqlString Value) { if (Value.IsNull) { return; } myList.Add(Value.ToString()); } public void Merge(AggregIssue Other) { if (Other.myList != null) { if (myList == null) { myList = Other.myList; } else { myList.AddRange(Other.myList); } } } public SqlInt32 Terminate() { return new SqlInt32(myResult); } public void Read(BinaryReader r) { myResult = r.ReadInt32(); } //The code below is simplified for posting in the forum. //I do additional manipulation of the list and require //the aggregation to be IBinarySerialize. //But this code replicates the issue also public void Write(BinaryWriter w) { w.Write(myList.Count); } }|||
Hi Adam,
Thank you very much for your response.
The reason that I don't serialize the list itself is because of the MaxByteSize limitation of 8096. My list will easily go beyond that limitation. I test your solution and it works for a small list, but not for long ones.
I noticed that the list is loosing the information in the merge. If the query doesn't go through parallel threads, it works fine.
My assumption is that the serialization will happen before terminate and final output of aggregate value for each row of the result set.
Any other ideas?
Thanks!!
Fernando
|||Hi, Fernando,
I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.
About Merge() stuff, to my limited understanding:
If single thread (no parallel op):
Init() -> Accumulate() -> Terminate()
If multi threads (parallel plan):
T1: Init() -> Accumulate()
T2: Init() -> Accumulate() -> Merge( with T1)
T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()
This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.
In Adam's book Chapter 6, p. 195, I quote:
"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"
I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?
Regards,
Dong Xie
|||? Actually, that quote from the book is not quite accurate anymore -- when I wrote it against an earlier CTP it appeared to be true, but now it does not. I believe that it has been changed in a later printing of the book, but I'll check today with Apress to make sure. Thanks for pointing it out! Anyway, the correct phrasing at this point should be "the intermediate result can be serialized and deserialized up to one time per row of aggregated data" In other words, the SQL Server engine may not do it for every row (or even nearly that often in most cases), but you need to code your aggregate as if it will. There is not necessarily a direct/stated relationship between Merge and Read/Write, but it appears that when Merge is called the engine also does a round of serialization/deserialization. I'm not sure why, though. Hopefully Steven Hemingray or one of the other dataworks guys will show up in this thread and clarify! -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Dong Xie@.discussions.microsoft.com> wrote in message news:c5fafeee-262b-43a4-9295-10a7cb07d92a@.discussions.microsoft.com...Hi, Fernando,I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.About Merge() stuff, to my limited understanding:If single thread (no parallel op):Init() -> Accumulate() -> Terminate()If multi threads (parallel plan):T1: Init() -> Accumulate()T2: Init() -> Accumulate() -> Merge( with T1)T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.In Adam's book Chapter 6, p. 195, I quote:"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?Regards,Dong Xie|||
It seems that you are right and serialization/deserialization happens when merge is called.
For now I have a workaround that was suggested in another forum to use "MAXDOP=1" and it works as merge is never executed.
It is not ideal, as parallelism cannot be leveraged, but it is a workaround.
Thanks to all for the responses!!
Fernando
|||There seems to be a few issues within the thread:
1) Why is the private field myList nullified?
Whenever serialization takes place (Read/Write), a new instance is instantiated and it is up to your serialization code to fill the instance. Since your Write() only sets myResult, myList will be remain on the default value of null.
2) Why is MaxByteSize not always enforced?
MaxByteSize is enforced during serialization. You could cause instances to become much larger than 8k over Accumulate() calls and then not serialize out 8k.
With this said, there should not be a reason to build UDAggs that become larger than 8k but do not serialize out 8k. Read/write should serialize all necessary information for the UDAggs.
The UDAgg code within this thread is a good example of a contrived case for this. Since Terminate() only returns the count, there is not a need to accumulate the actual strings, but Accumulate() could increment a counter instead.
3) When is Read/Write called?
Within SQL Server 2005, serialization takes place during Merge() and before Terminate() is called. If the UDAgg provided accessed the myList within Terminate(), you would see a NullReferenceException from within Terminate() (since Write() does not reconstruct myList and null is its default reference).
As pointed out within this thread, Read/Write is not called for every row. However, please write your UDAgg with proper serialization semantics as if it were called every row.
Hope that helps!
-Jason
|||Jason,
Thanks for all your explanation. It makes sense to me now how all this work.
But I still have one issue, which is the limitation of 8K. The list that I am building cannot be contrived until it is complete just before "Terminate". In the example I am passing the count and I could do that in the merge also. But in my real case, I need the complete list to be used in the resolution of a non-linear equation, so I cannot do anything with it until I pass it to a iterative algorithm in order to solve the equation. But this list can grow bigger than 8K.
The only way to avoid so far is to limit the degree of parallelism to 1, so the code doesn't go to merge.
Any thoughts on this?
Thanks,
Fernando
|||Fernando-
Merge is also called in some more complex queries (such as GROUP BY WITH CUBE).
This is an interesting workaround to the 8k limitation for your scenario. I do worry that your workaround leaves your UDAgg prone to internal changes within SQL Server causes serialization to be more frequently performed or eliminated. For this reason, I'd recommend always using an approach where instances created from Read/Write are no different than the original.
I cannot recommend using this approach for these reasons, but limiting DOP to 1 should eliminate Merge() calls for the most part. If you absolutely must use this workaround, I'd recommend safeguarding against your assumptions (Merge never called, serialization takes place once after all Accumulate calls but before Terminate):
Always throw an exception within your Merge() call so that in the case that Merge() is invoked, you'll know what the issue is and you can try to simplify the query so that Merge is not called.|||Jason,
Thanks for all your help and explanations.
Fernando
Friday, March 9, 2012
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
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
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
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'Serverna
me')
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_c
olumns_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_n
ame')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-...-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/feedba...edbackID=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 vi
ew; 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 cata
log view, or use the
OBJECT_DEFINITION system function."
It has always been the case that we cannot get the default values of paramet
ers in SQL Server. Seems
we now can get it for CLR procedures, but still not for TSQL objects. So sam
e applies as for earlier
versions: parse the source code. You might want to post an enhancement reque
st at:
http://connect.microsoft.com/site/s...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 th
e 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 ind
icate the parameter as
> having a default value....I was planning to use this information but cann
ot 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-97a
4-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/s...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 mess
age
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/feedba...edbackID=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
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'Serverna
me')
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_c
olumns_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_n
ame')).
For further information you can see the following article written by me
before SQL Server 2005 was released:
http://databases.aspfaq.com/schema-...-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/feedba...edbackID=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 vi
ew; 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 cata
log view, or use the
OBJECT_DEFINITION system function."
It has always been the case that we cannot get the default values of paramet
ers in SQL Server. Seems
we now can get it for CLR procedures, but still not for TSQL objects. So sam
e applies as for earlier
versions: parse the source code. You might want to post an enhancement reque
st at:
http://connect.microsoft.com/site/s...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 th
e 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 ind
icate the parameter as
> having a default value....I was planning to use this information but cann
ot 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-97a
4-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/s...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 mess
age
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/feedba...edbackID=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
Subscribe to:
Posts (Atom)